MySQL Tutorial 1

1
MySQL Tutorial 1 How to Use MySQL

• CSCI 2140
• TA Jiye Li jiye_at_cs.dal.ca
• May 26, 2005
• http//flame.cs.dal.ca/jiye/CSCI2140/

2
Agenda

• Getting familiar with
• our network system
• Introducing MySQL
• SQL basics
• create, add, select, modify, delete
• Exercises (see handout)

3
How to get a UNIX account

• Sun workstation or ssh program (putty)
• Log into torch.cs.dal.ca
• Username discover
• Passwd query
• Enter your student number
• Ask Help Desk

4
How to get a Windows account

• Username the same as torch account
• Password student ID number. Dont forget B is
  capitalized.

5
What is MySQL?

• MySQL is a database management system (DBMS) for
  relational databases
• Online Manual
• http//dev.mysql.com/doc/
• MySQL is installed on torch
• Each user ID stands for a database
• Create tables under this database

6
How to Login to MySQL

• Go on torch.cs.dal.ca
• Run mysql to login
• torch mysql -p
• Enter password
• Welcome to the MySQL monitor. Commands end with
  or \g.
• Your MySQL connection id is 123943 to server
  version 4.0.12
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt

Lower case
Student ID
7
How to Logout

• Use the command exit or quit
• torch mysql -p
• Enter password
• Welcome to the MySQL monitor. Commands end with
  or \g.
• Your MySQL connection id is 123975 to server
  version 4.0.12
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt exit
• Bye
• torch

8
Use your database

• After login MySQL, use your own database before
  creating tables
• torch mysql -p
• Enter password
• Welcome to the MySQL monitor. Commands end with
  or \g.
• Your MySQL connection id is 125213 to server
  version 4.0.12
• Type 'help' or '\h' for help. Type '\c' to clear
  the buffer.
• mysqlgt use jiye
• Database changed
• mysqlgt

Your user ID
9
SQL Basics

• Suppose we would like to create a few tables,
  such as employee table, employer table and
  payment table, representing a database about
  employee information.

10
How to write commands in MySQL

• How to create a table
• Primary Keys and Foreign Keys
• How to add records
• How to select records
• How to modify records
• How to delete records

11
How to create tables

• Use create
• create table lttable_namegt (column_name data_type
  not null , column_name data_type not null,
  primary key (column_name))
• To show the structure of the table
• describe lttable_namegt

12
Example

• 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)
• )

13
Primary Key

• Primary Key is a column or set of columns
• Uniquely identifies the rest of the data in any
  given row.
• For Example in the employee table, employee
  number is the primary key.

14
Foreign Key

• A foreign key is a column in a table
• This column is a primary key of another table
• Any data in a foreign key column must have
  corresponding data in the other table
• http//dev.mysql.com/doc/mysql/en/innodb-foreign-k
  ey-constraints.html

15
Foreign Key

• The goal of using foreign keys is that, tables
  can be related without repeating data
• Note that foreign keys in SQL are used to check
  and enforce referential integrity, not to join
  tables. If you want to get results from multiple
  tables from a SELECT statement, you do this by
  performing a join between them

SELECT FROM t1, t2 WHERE t1.id t2.id
16
Example Create table with foreign keys
employee2
empno (PK) salary
100 200.85
200 129.54
300 98.17

• create table employee2 (
• empno smallint(4) not null,
• salary float,
• primary key (empno)
• ) type innodb
• create table employer (
• id smallint(4),
• employee_no smallint(4),
• index employ_ind (employee_no),
• foreign key(employee_no)
• references employee2(empno)
• on delete cascade) typeinnodb

employer
id employee_no(FK)
51 100
52 100
53 200
54 300
17
The Syntax of a Foreign Key Constraint Definition

• CONSTRAINT symbol FOREIGN KEY
  (index_col_name, ...) REFERENCES table_name
  (index_col_name, ...) ON DELETE CASCADE SET
  NULL NO ACTION RESTRICT ON UPDATE CASCADE
  SET NULL NO ACTION RESTRICT
• Both tables have to be InnoDB type
• InnoDB provides MySQL with a transaction-safe
  storage engine with commit, rollback, and crash
  recovery capabilities.
• http//dev.mysql.com/doc/mysql/en/innodb-overview
  .html

18
The Syntax of a Foreign Key Constraint Definition

• InnoDB rejects any INSERT or UPDATE operation
  that attempts to create a foreign key value in a
  child table without a matching candidate key
  value in the parent table.
• CASCADE Delete or update the row from the parent
  table and automatically delete or update the
  matching rows in the child table.
• SET NULL Delete or update the row from the
  parent table and set the foreign key column(s) in
  the child table to NULL. This is only valid if
  the foreign key columns do not have the NOT NULL
  qualifier specified.
• NO ACTION NO ACTION means no action in the sense
  that an attempt to delete or update a primary key
  value will not be allowed to proceed if there is
  a related foreign key value in the referenced
  table.
• RESTRICT Rejects the delete or update operation
  for the parent table. NO ACTION and RESTRICT are
  the same as omitting the ON DELETE or ON UPDATE
  clause. (Some database systems have deferred
  checks, and NO ACTION is a deferred check. In
  MySQL, foreign key constraints are checked
  immediately, so NO ACTION and RESTRICT are the
  same.)
• SET DEFAULT This action is recognized by the
  parser, but InnoDB rejects table definitions
  containing ON DELETE SET DEFAULT or ON UPDATE SET
  DEFAULT clauses.

19
MySQL Table Types

• If we want to use Foreign Key
• InnoDB tables
• Otherwise
• Default table type, MyISAM
• In SQL queries you can freely mix InnoDB type
  tables with other table types of MySQL, even
  within the same query.

20
How to add records

• Use insert
• insert into lttable_namegt values
• (column_value, , column_value)

21
Example

• insert into employee values (1000,'Wilson','Clrk',
  1700,10)
• insert into employee values (1001,'Smith','Slsm',2
  500,40)
• insert into employee values (1003,'Reed','Anlt',35
  00,30)
• insert into employee values (1005,'Watson','Mngr',
  4500,30)
• insert into employee values (1009,'Allen','Mngr',3
  800,40)
• insert into employee values (1010,'Turner','Clrk',
  1800,50)
• insert into employee values (2000,'Chen','Mngr',29
  00,10)
• insert into employee values (2100,'Ramirez','Mngr'
  ,3650,50)
• insert into employee values (2130,'McDonnel','Clrk
  ',1625,60)
• insert into employee values (2140,'Simpson','Drvr'
  ,825,60)

22
Example On Slide 17
insert into employee2 values (100,
200.85) insert into employee2 values (200,
129.54) insert into employee2 values (300,
98.17) insert into employer values (51,
100) insert into employer values (52,
100) insert into employer values (53,
200) insert into employer values (54, 300)
23
How to Select Records

• select from lttable_namegt
• select from lttable_namegt where ltcolumn_namegt
  ltqualifiergt
• select from lttable_namegt where ltcolumn_namegt
  ltqualifiergt order by ltcolumn_namegt
• select ltcolumn_name, gt from lttable_namegt
• select ltdistinct column_name, gt from lttable_namegt

24
Example

• select from employee
• select from employee where empno 1000
• select from employee where job 'Clrk' order
  by salary
• select name, empno from employee
• select job from employee
• select distinct job from employee

25
Example On Slide 17
select empno from employee2 select empno from
employee2 where salary gt50 and salary lt
150 select from employee2, employer select
id, empno from employer m, employee2 n where
m.employee_no n.empno
26
How to Modify Records

• Use update to modify attribute values of (some)
  tuples in a table
• update lttable_namegt set ltcolumn i gt
  ltexpression igt, , ltcolumn jgt ltexpression jgt
  where ltconditiongt

27
Example

• update employee set job Drvr, deptno 20 ,
  salary salary 1000 where name 'Reed
• update employee set salary salary 1.15 where
  deptno in (10, 40)

28
How to Delete Records

• Use delete
• delete from lttable_namegt where ltconditiongt

29
Example

• delete from employee where
• salary lt 2000
• delete from employee

Note this command will delete all the records in
the employee table.
30
Tip1 How to Load Command

• Execute a SQL script file.
• Take a file name as an argument.
• Save SQL commands into a file ltnamegt
• Execute the commands
• mysqlgt source ltnamegt

31
Example

• In file temp.sql
• select from employee
• select name from employee where salary lt 3000
• mysqlgt source temp.sql

32
Tip2 How to Save Results

• Save results
• tee ltfilegt
• Set outfile. Append everything into given
  outfile ltfilegt. All the information displayed on
  screen is stored in ltfilegt.

33
Example

• mysqlgttee result
• mysqlgt select from employee
• mysqlgt notee
• mysqlgt exit
• torch cat result
• mysqlgt select from employee
• EMPNO NAME JOB SALARY DEPTNO
• ---------- -------- ---- ---------- ----------
• 1000 Wilson Clrk 1720 10
• 1001 Smith Slsm 2500 40
• 10 rows selected.
• mysqlgt notee
• torch

34
Appendix MySQL Data Types
Type Size Description
tinyintLength 1 byte Range of 128 to 127
float 4 bytes A small number with a floating decimal point
Date 3 bytes In the format of YYYY-MM-DD
Time 3 bytes In the format of HHMMSS
varcharLength String length 1 byte A fixed-length field from 0 to 255 characters long
35
Appendix MySQL under Unix

• A few commands
• use username
• show tables
• show columns from employee
• help
• exit
• SQL commands (select, insert, delete,)

36
Appendix MySQL Control Center

• Graphical user interface (GUI) to the MySQL
  database server
• Supports interactive use, including syntax
  highlighting and tab completion
• Download from
• http//dev.mysql.com/downloads/other/mysqlcc.html
  

37
Appendix MySQL Control Center
Student ID