MySQL Tutorial 2 How to Use MySQL

1
MySQL Tutorial 2 How to Use MySQL
CSCI 2140 TA Jiye Li jiye_at_cs.dal.ca May 31,
2005 http//flame.cs.dal.ca/jiye/CSCI2140/
2
Agenda

• SQL
• How to sort query results
• How to limit query results
• How to modify tables
• How to perform Joins
• Exercise

3
Example from Tutorial 1

• mysqlgt create table employee (
• empno smallint(4) not null auto_increment,
• name char (8) not null,
• job char (4) ,
• salary int (8) not null,
• deptno int (4) not null,
• primary key (empno)
• )

4
How to sort query results

• SQL command
• Use order by to arrange the returned order
• The default order is ascending
• Reverse the order by specifying desc
• Example
• select from employee order by salary
• select from employee order by salary desc

5
How to limit query results

• limit states how many records to return
• SQL command
• select from tablename limit 10
• Example
• select from employee order by deptno limit 2

6
How to modify tables

• Alter SQL keyword is used to modify the
  structure of a table in your database, such as
  adding, deleting, or changing the columns.
• alter table employee add column contact
  varchar(25)
• You can use after columnname description to
  indicate where in the table the new column should
  be placed.
• alter table employee add column birthday date
  after job

7
Alter Table Clauses

• ADD COLUMN Add a new column to the end of the
  table.
• alter table tablename add column column_name
  VARCHAR(40)
• CHANGE COLUMN Allows you to change the data type
  and properties of a column.
• alter table tablename change column column_name
  column_name VARCHAR(60)
• DROP COLUMN Removes a column from a table,
  including all of its data.
• alter table tablename drop column column_name
• RENAME AS Changes the name of a table.
• alter table tablename rename as new_tablename

8
How to perform Joins

• SQL queries performed by cross-referencing
  tables.
• Two most basic joins
• inner join
• outer join
• When selecting from multiple tables and columns,
  if there are columns with the same name, use the
  dot syntax (table.column) ..

9
How to perform Joins

• Builds a relation from two specified relations
  consisting of all possible concatenated pairs,
  one from each of the two relations, such that in
  each pair the two tuples satisfy some condition.
  (E.g., equal values in a given column)

Inner Join
10
Inner Join

• Retrieve all of the information from both tables
  where a match is made.
• Examples
• select from international, students where
  international.addressstudents.address

11
Outer Join

• Outer Joins are similar to PRODUCT -- but will
  leave NULLs for any row in the first table with
  no corresponding rows in the second.

12
Outer Join

• The most important consideration with outer joins
  is which table gets named first.
• For example
• select from students left join international
  on
• select from international left join
  students on
• IS DIFFERENT.

13
Outer Join - using

• When both tables in a left join have the same
  column name, you can simplify your query with
  using.

select from international left join
students using (address)
14
Summary

• The INNER JOIN returns all rows from both tables
  where there is a match. If there are rows in one
  table that do not have matches in the other
  table, those rows will not be listed.
• The OUTER JOIN returns all the rows from the
  first table, even if there are no matches in the
  second table. If there are rows in the first
  table that do not have matches in the second
  table, those rows also will be listed.

15
Summary

• Joins can be used on more than 2 tables.
• Joins can be created using conditionals involving
  any columns, not just the primary and foreign
  keys.
• Joins that do not include a where clause are
  called full joins, and will return every records
  from both tables.
• For example

select from students, international
16
Exercise on Join
create table students ( student_id tinyint(4)
NOT NULL auto_increment, first_name
varchar(100), last_name varchar(40), address
varchar(20), PRIMARY KEY (student_id) )
create table international ( student_id
tinyint(4) NOT NULL auto_increment, address
varchar(20), country varchar(20), primary key
(student_id) )
17
Exercise on Join
insert into students values (1, Bob, Smith,
Halifax) insert into students values (2,
John, Roberts, Edmonton) insert into
students values (3, Brad, Johnson,
Boston) insert into international values(1,
Halifax, Canada) insert into international
values (2, Edmonton, Canada) insert into
international values (3, Boston, US) insert
into international values (4, Beijing,
China) insert into international values (5,
Bangkok, Thailand) insert into international
valudes (6, Boston, US)
18
Exercise on Join

• select from
• international, students where
  international.addressstudents.address
• select from
• international left join students on
  international.addressstudents.address
• select from
• students left join international on
  international.addressstudents.address

19
Appendix

• When entering commands over several lines, if we
  want to cancel the command, type the '\c' command
• mysqlgt create table student (
• -gt studID int(3)
• -gt \c
• mysqlgt

20
Appendix Lazy to copy tables

• Instead of writing the same command, use
• create table emp select from employee
• alter table emp change empno empno smallint(4)
  not null auto_increment primary key