Introduction to MySQL

May 2005

CS 314

References

MySQL web resource: http://www.mysql.com/ Reference manual: http://dev.mysql.com/doc/ SQL tutorial:http://www.sqlcourse.com/

May 2005

CS 314

Introduction

• MYSQL is a relational database management system (RDBMS) – essence of RDBM:

• Alternatives: – Oracle – DB2 – Microsoft SQLServer.

• It is quite popular, in no small part • because it is open source (free). May 2005

CS 314

1

Introduction

• There are various versions of MySQl available • Administrators have some flexibility when installing mysql – e.g. security considerations – features

May 2005

CS 314

Using MySQL

May 2005

CS 314

Logging in

• You need a username and password to login to mysql. (in addition to a system user id and password)

In general: voyager% mysql –u cs314xx –p Enter password:

Since your voyager id == mysql id: voyager% mysql –p Enter password: May 2005

CS 314

2

Logging in

System configuration note To load data from files at the command line, login using: voyager% mysql –p – Enter password:

May 2005

local -

infile

CS 314

Logging in

Ælogin to voyager. Ælogin to mysql voyager% mysql –p

May 2005

CS 314

Commands: Use

• mysql manages many databases on the system • Each of us has our own database -> To get started ( each time you login): mysql>

use

cs314xx;

• Semi-colons! May 2005

CS 314

3

Commands: Syntax

• Semi-colons! • multiple line commands –>

• Command buffer

May 2005

CS 314

Commands: show

• show databases mysql> show databases;

• show tables mysql> show tables;

May 2005

CS 314

Commands: create table CREATE TABLE dept ( deptno deptname mgrno PRIMARY

CHAR(3) CHAR(36) CHAR(4) KEY(deptno));

NOT NULL, NOT NULL, NOT NULL,

CREATE TABLE empl ( empno CHAR(4) firstnme VARCHAR(12) midinit CHAR(1) lastname VARCHAR(15) workdept CHAR(3) salary DECIMAL(5) PRIMARY KEY(empno));

May 2005

NOT NOT NOT NOT NOT NOT

NULL, NULL, NULL, NULL, NULL, NULL,

CS 314

4

Commands: Describe

•

To describe the structure of a table mysql> describe dept;

•

useful to recall the column names

•

Initialy the table is empty: mysql> Select * from dept;

May 2005

CS 314

Commands: Insert

• To manually insert data into a table: mysql> INSERT into dept Values ('110', 'Machine Press Room', '9999'), ('220', 'Warehouse/Purchasing', '8888'), ('330', 'Payroll', '7777');

• Note the structure/syntax – ordered tuple – data types

• Note system response. May 2005

CS 314

Commands: Insert contd

INSERT INTO empl VALUES ('9922', 'Aaron', 'R', 'Burr', '110',25000), ('9999', 'Alexander','A','Hamilton','110',52000), ('7777', 'Chester', 'A','Arthur','330',55000), ('7722', 'Tovictor','G','Spoils','330',58000), ('8888', 'Warren', 'G','Harding','220',95000), ('8822', 'Teapot','D','Scandal','220',75000);

• Loading data from files: later

May 2005

CS 314

5

Commands: Insert contd

INSERT INTO empl VALUES ('9922', 'Aaron', 'R', 'Burr', '110',25000), ('9999', 'Alexander','A','Hamilton','110',52000), ('7777', 'Chester', 'A','Arthur','330',55000), ('7722', 'Tovictor','G','Spoils','330',58000), ('8888', 'Warren', 'G','Harding','220',95000), ('8822', 'Teapot','D','Scandal','220',75000);

• Loading data from files: later

May 2005

CS 314

Simple SQL Queries

May 2005

CS 314

Select Query

•

The workhorse mysql command: Select mysql> Select * from dept;

•

result is called a result set

May 2005

CS 314

6

Database terminology

Column / field row /

record

May 2005

CS 314

Select Query

•

specific fields may be requested in a specific order:

mysql> Select mgrno, deptno, deptname from dept; •

Be careful using * in scripts

May 2005

CS 314

Select Query -order by

The records in the result set may be sorted. mysql> Select lastname, salary from dept, order by lastname;

May 2005

CS 314

7

Select Query -order by

The records in the result set may be sorted in reverse. mysql> Select lastname, salary from dept, order by lastname desc;

May 2005

CS 314

Select Query -order by

The records in the result set may be sorted within columns. mysql> Select workdept, empno, lastname, salary from empl order by workdept, empno;

May 2005

CS 314

Select Predicates

Query results can be contrained/limited by use of predicates. Æ where mysql> Select workdept, lastname, salary from empl where salary > 30000;

May 2005

CS 314

8

Select Predicates

Remember to quote character data: mysql> Select * from dept where deptno=’110’; Compound predicates are built using standard boolean operators mysql> Select * from dept where deptno=’110’ or workdept = ’220’; May 2005

CS 314

Select Predicates

IN Operator

mysql> Select workdept, lastname from empl where deptno in (’110’,’220’);

May 2005

CS 314

Select Predicates

Between Operator

Select empno, workdept, salary from empl where salary between 30000 and 40000;

May 2005

CS 314

9

Select Predicates

Like Operator Simple pattern matching for character data Select empno, lastname from empl where empno like ’%22’;

May 2005

CS 314

Select Predicates

Like Operator % is simliar to UNIX/DOS * Select empno, firstname from empl where firstname like ’W%’;

May 2005

CS 314

Select Predicates

Like Operator matching a substring Select * from dept where lower(deptname) like ’%press%’;

May 2005

CS 314

10

Select Predicates

Regexp Operator MySQL supports POSIX regular expressions Select deptname from dept where deptname regexp ’[^a -

May 2005

zA - Z]’

CS 314

More SQL Statements

May 2005

CS 314

Commands: Drop

Drop: To delete a table. show tables; drop table dept; show tables;

May 2005

CS 314

11

Commands: batch commands

• • •

Alternative to interactive, command line. place commands into a data file: myscript.sql Run the script: at login: voyager% mysql –p < myscript.sql during interactive session mysql> source myscript.sql

•

can save lots of typing

May 2005

CS 314

Commands: batch commands 1. 2.

Exit mysql: create a text file containing: CREATE TABLE dept ( deptno CHAR(3) deptname CHAR(36) mgrno CHAR(4) PRIMARY KEY(deptno));

3. 4. 5. 6. 7.

NOT NULL, NOT NULL, NOT NULL,

Save file with name: ctd.sql Login to mysql mysql> show tables; mysql> source ctd.sql; mysql> show tables;

May 2005

CS 314

Commands: LOAD from a file

To load a table from a data file. load data local infile “dept.txt” into table dept fields terminated by ‘,’; select * from dept;

May 2005

CS 314

12

Commands: delete

•

To delete specific rows from a table.

mysql > select * from dept; mysql> delete from dept where deptno = ’110’; mysql > select * from dept; •

if where clause is omitted, all rows are deleted.

May 2005

CS 314

Commands: update

• •

To change the values of specific fields of specific records Example: Warren Harding gets a raise

select * from empl where lastname=’Harding’; update empl set salary=99000 where empno=’8888’; select * from empl where lastname=’Harding’; May 2005

CS 314

Commands: update

•

Like the Delete statement, if the where clause is omitted, the update is applied to all records. update empl set salary=99000;

•

Everyone gets a new salary

May 2005

CS 314

13

Advanced SQL Statements

May 2005

CS 314

Multiple tables

•

Most (commercial grade) databases are made of multiple tables • • • •

•

levels of (security) access reduces redundancy ( Æ smaller size ) improves modularity facilitates maintainence

An important part of database design is deciding • •

how many tables to use what information to put in each

May 2005

CS 314

Multiple tables

•

The information in each table is almost always related in some way to that in other tables.

Example: what is the name of Warren Hardings dept? Where is the information needed? May 2005

CS 314

14

Multiple tables

Most join operations are constrained accomplished by the use of primary and foreign keys

May 2005

CS 314

Multiple tables

•

when the required information is spread across multiple tables, the tables must be temporarily combined.

•

This operation is called a table join

•

an unconstrained join, aka cartesian product, produces every combination of the records in each table. select * from empl, dept; May 2005

CS 314

simple join

•

join the two tables

select * from empl, dept where workdept = deptno; •

the where clause is a join predicate

May 2005

CS 314

15

simple join

•

What is the name of Warren Harding’s dept? Select ?

•

Display the name of each department and its manager. Select ? May 2005

CS 314

Scalar functions

select count(*) from empl, dept; select sum(salary) from empl; • Other functions: • • • •

avg max min many others

May 2005

CS 314

Grouping operations

select workdept, sum(salary) from empl group by workdept;

May 2005

CS 314

16

Cursors

•

some tables may use identical field names

•

leads to ambiguous references when joining the tables

•

Cursors are used to qualify the reference

May 2005

CS 314

Cursors

Problem: Find all employees who are paid more than their bosses SELECT e.lastname, e.salary, m.lastname, m.salary FROM empl e, empl m, dept d WHERE d.mgrno = m.empno AND d.deptno = e.workdept AND e.salary > m.salary;

•

e, m and d are cursors May 2005

CS 314

17