Transcript MySQL Tutorial

MySQL Tutorial

MySQL is a RDBMS



A relation consists of a set of tuples

Each has values for the same set of attributes

Each attribute has a domain (data type)
View a relation as a table


Stores data in relations (or tables)
Picture the tuples as rows, the attributes as columns
But rows are unordered

To pick out rows, designate one or more columns as
constituting the primary key


Examples:

A primary key for US citizens is the Social Security number

A primary key for buildings consists of columns for the
street number, street, city, state, and country
MySQL uses the SQL language (with proprietary extensions)

SQL is case insensitive

Conventions for using different cases in different contexts
Getting Started


To interact directly (not through a PHP script) with your
MySQL server

Bring up the WAMP pop-up menu

Click MySQL  MySQL Console
Console window prompts for your password


The password set up using phpMyAdmin when you
installed WampServer—or just a carriage return (empty
string) if you didn’t set a password
After entering your password, see the prompt
mysql>

When you are finished, type exit at the command prompt

MySQL commands terminate with a semicolon

May extend over several lines

Prompt in continued lines is an indented ->

If you forget to type a semicolon, interface will wait

Move back through previous lines with the up-arrow key

SHOW DATABASES displays the databases in your account
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| claroline
|
| mysql
|
| oscommerce1
|
| phpmyadmin
|
| test
|
+--------------------+
6 rows in set (0.01 sec)

To change current database, use USE
mysql> use test;
Database changed

To see tables in current database, use SHOW TABLES
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| te
|
+----------------+

To find the current database, use the DATABASE() function
mysql> select database();
+-------------+
| database() |
+-------------+
| test
|
+-------------+

If no database selected, we’d see NULL
Creating and Selecting a Database

To create a new database, use CREATE DATABASE
mysql> create database university;

Make the new database the current database:
mysql> use university;
Database changed

To delete a database, use DROP DATABASE:
mysql> drop database unwanted;
Data Types, Attribute Modifiers, and
Creating a Table

Creating a table, we declare its columns with types and any
modifiers

3 basic data types


numeric (integers and floating-point numbers)

date and time

string
Each has various storage sizes
Numeric Types

INT (or INTEGER)—4 bytes long

Also BIGINT (8 bytes), SMALLINT (2 bytes)

FLOAT(Precision), where Precision is number of bytes

FLOAT(Width, Decimals)—4 bytes (like FLOAT(4))


Width is display width

Decimals is number of digits after the decimal point

E.g., float(6,2) gives 312.42 and __2.30
DOUBLE(Width, Decimals)—8 bytes (like FLOAT(8))
Date and Time Types

Type DATE is a date displayed as YYYY-MM-DD

TIME is a time displayed as HH:MM:SS

DATETIME is both displayed as YYYY-MM-DD HH:MM:SS

TIMESTAMP(12) is a timestamp YYMMDDHHMMSS

See transaction reporting

Reduce argument by 2 to eliminate a unit


E.g., TIMESTAMP(2) is YY
If timestamp not set manually, set by most recent operation
on the row
String Types

CHAR for fixed-length strings, VARCHAR for variable-length


Both take an argument indicating length—e.g., char(20)
TEXTs and BLOBs (binary large objects) hold about 65K bytes

For larger text

Difference: a BLOB is case sensitive.

A BLOB can hold anything (e.g., image, sound data)

Also TINYBLOB, TINYTEXT, LONGBLOB, LONGTEXT, etc.
Other Types
ENUM( String1, String2, … )

Allows exactly 1 of the string arguments or NULL
SET( String1, String2, … )

Allows 1 or more of the strings arguments or NULL
CREATE TABLE
CREATE TABLE TableName ( Column1, Column2, … )

Each column specified in the form
ColumnName Type Modifiers

Modifiers is a possibly empty list of modifiers separated by
whitespace
Modifiers

NOT NULL: every row must have a value for the column

UNSIGNED (for numbers) rules out negative values

AUTO_INCREMENT (unique to MySQL) is restricted to integer
columns

If field is blank in a row, MySQL generates a unique integer
value for it

Only one column per table

Useful for primary keys

PRIMARY KEY designates a column as the primary key

But modify at most 1 column

For other cases, as last argument to the CREATE TABLE:
primary key ( Columns )
Example of CREATE TABLE
mysql> create table student (
-> studentid int unsigned not null primary key,
-> firstname varchar(20),
-> middleinitial char(1),
-> lastname varchar(20),
-> birthdate date,
-> year enum('freshman', 'sophomore', 'junior', 'senior')
-> );

Use DESCRIBE to get table info
mysql> describe student;
+---------------+---------------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| studentid
| int(10) unsigned
| NO
| PRI | NULL
|
|
| firstname
| varchar(20)
| YES |
| NULL
|
|
| middleinitial | char(1)
| YES |
| NULL
|
|
| lastname
| varchar(20)
| YES |
| NULL
|
|
| birthdate
| date
| YES |
| NULL
|
|
| year
| enum('freshman',…') | YES |
| NULL
|
|
+---------------+---------------------+------+-----+---------+-------+

To delete a table, use DROP TABLE
mysql> drop table oldstudents;
Modifying Tables

Clean up if we mess up

Use ALTER TABLE followed by table name and an alteration
clause

Our example table:
mysql> create table books (
-> id int unsigned not null primary key,
-> name varchar(30)
-> );
ADD [COLUMN] ColumnDescription AFTER Column

ColumnDescription includes column name, type, modifiers
mysql> alter table books
-> add owner varchar(30) after name;
DROP [COLUMN] Column
mysql> alter table books drop owner;

Add several columns at end of the table:
ADD [COLUMN] (ColumnDescription1,
ColumnDescription2, … )

To change the name, type, or modifiers of a column, use
CHANGE [COLUMN] Column NewColumnDescription

New column description must include column name, type,
and modifiers even if not changed
mysql> alter table books
-> change name title varchar(23);

To change the type and modifiers without changing the name,
use
MODIFY [COLUMN] ColumnDescription
mysql> alter table books
-> modify title varchar(30);

If we’ve messed up the primary key, drop it and add a new one
DROP PRIMARY KEY
ADD PRIMARY KEY ( Column1, Column2, … )
mysql> alter table books drop primary key;
…
mysql> alter table books add primary key (title);

If we’ve messed up the name,
RENAME [AS] NewTableName
mysql> alter table books rename as volumes;
Populating a Table

Quick way to populate a table: load the data from file with


1 row per line, values separated by tabs
File with pathname C:\studentData.txt
234
172
312
471

Ed
Sue
Al
Beth
C
G
M
N
Smith
Jones
Green
Walls
1987-02-04
1985-09-21
1986-11-04
1988-08-27
sophomore
senior
junior
freshman
Use LOAD to load these rows into student table

For Windows, specify that a line is terminated with '\r\n'

Note forward slashes in pathname
mysql> load data local infile 'C:/studentData.txt'
-> into table student lines terminated by '\r\n';

Use very simple SELECT to see the rows:
mysql> select * from student;
+-----------+-----------+---------------+----------+------------+-----------+
| studentid | firstname | middleinitial | lastname | birthdate | year
|
+-----------+-----------+---------------+----------+------------+-----------+
|
172 | Sue
| G
| Jones
| 1985-09-21 | senior
|
|
234 | Ed
| C
| Smith
| 1987-02-04 | sophomore |
|
312 | Al
| M
| Green
| 1986-11-04 | junior
|
|
471 | Beth
| N
| Walls
| 1988-08-27 | freshman |
+-----------+-----------+---------------+----------+------------+-----------+

Insert data composed at the console with INSERT
INSERT [INTO] TableName VALUES Row1, Row2, …

A row is a comma-separated list of column values within
parentheses in column order
mysql> insert into student values
-> (154, 'Ed', 'J', 'Walker', '1971-06-12', 'junior'),
-> (532, 'Mary', 'C', 'Smith', '1981-05-23', 'sophomore');

Can supply values for only some columns or in a different order

Put a comma separated list of columns in parentheses just
after the table name

Values in the rows correspond by position to the columns
mysql> insert into student (firstname, lastname, studentid)
-> values
-> ('Al', 'Jones', 241);

Missing values become NULL

Variation of this uses keyword set followed by a commaseparated list of assignments to columns

E.g., redo the last as
mysql> insert into student
-> set firstname='Al',
->
lastname='Jones',
->
studentid=241;

Use DELETE to remove an unwanted row.
DELETE FROM TableName WHERE Condition

WHERE clause (see SELECT below) identifies the rows to delete

Here use just a condition key=value
mysql> delete from student where studentid=241;

To change 1 (or a few) fields in a row, use UPDATE
UPDATE TableName SET Column1=Value1,
Column2=Value2, …
WHERE Condition

Use here conditions key=value
mysql> update student set firstname='Edward'
-> where studentid=154;

Omitting the WHERE would change firstname for every
row in student to ‘Ed’
Retrieving Data from a Table

To retrieve data, use SELECT

Have seen it to retrieve all fields of all rows in a table:
mysql> select * from student;

Add WHERE clause whose condition restricts rows retrieved
mysql> select * from student where year='junior';
+-----------+-----------+---------------+----------+------------+--------+
| studentid | firstname | middleinitial | lastname | birthdate | year
|
+-----------+-----------+---------------+----------+------------+--------+
|
154 | Ed
| J
| Walker
| 1971-06-12 | junior |
|
312 | Al
| M
| Green
| 1986-11-04 | junior |
+-----------+-----------+---------------+----------+------------+--------+

Restrict columns of rows retrieved by replacing * with a
comma-separated list of columns
mysql> select lastname, studentid from student where year='junior';
+----------+-----------+
| lastname | studentid |
+----------+-----------+
| Walker
|
154 |
| Green
|
312 |
+----------+-----------+

Rename a column by following its name with “AS NewName”
mysql> select lastname, studentid as SID from student
-> where year='junior';
+----------+-----+
| lastname | SID |
+----------+-----+
| Walker
| 154 |
| Green
| 312 |
+----------+-----+
Comparison Operators

Only comparison operator (WHERE clause) we’ve seen so far
is = (not ==)

SQL comparison operators return 1 for true and 0 for false

Negation of = is <> or !=

Usual relational operators <, <=, >, and >=


With string operands, comparison is by lex order

Generally case insensitive
Test results of comparisons with SELECT followed by just the
comparison
mysql> select 'cat' < 'DOG';
+---------------+
| 'cat' < 'DOG' |
+---------------+
|
1 |
+---------------+

Force case-sensitive comparison by putting BINARY after operator
mysql> select 'cat' < binary 'Dog';


indicates a 0
When a number and a string are compared, string is coerced to
a number

0 if it does not start as a number should
mysql> select 'cat' = 0;

shows 1 (true)

LIKE matches left operand with simple SQL pattern as its right
operand

Only 2 metacharacters:

% matches any run of characters (like .*)

_ matches exactly one character
mysql> select 'Cats and Dogs' like '% and _ogs';

shows 1

Match is case insensitive unless we follow LIKE with BINARY

Negation of LIKE is NOT LIKE

More sophisticated matches with REGEXP

Left operand a string, right operand a POSIX regular expression

POSIX REs like JavaScript REs (“Perl regular expressions”)

But different predefined character classes

[:character_class:], and further bracketed in []s

For negation, put ^ after the opening outer bracket

Examples

\d becomes [[:digit:]]

\D becomes [^[:digit:]]

\s becomes [[:blank:]]

[[:alnum:]] is like \w but doesn’t include underscore
mysql> select '123-45-6789' regexp
-> '^[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}$';

returns 1 (SSNs)

Negation of REGEXP is NOT REGEXP

RLIKE is a synonym for REGEXP

To make REGEXP case sensitive, follow it with BINARY

In WHERE clause, left operands of LIKE and REGEXP generally
column names
mysql> select studentid, year from student
-> where year regexp '.*ior$';
+-----------+--------+
| studentid | year
|
+-----------+--------+
|
154 | junior |
|
172 | senior |
|
312 | junior |
+-----------+--------+

IS NULL (negation IS NOT NULL) checks if a value is NULL

IN (negation NOT IN) checks if a value is in a set of values
mysql> select studentid, year from student
-> where year in ('junior', 'senior');
Boolean Operators

SQL has 2 sets of Boolean operators with same semantics

NOT and !

AND and &&

OR and ||

Usual precedence relations

XOR with precedence between AND and OR
mysql> select studentid from student
-> where birthdate < '1987-01-01' and studentid > 200
->
or year in ('sophomore', 'junior');
Retrieving Data from Multiple Tables

First example table:
mysql> describe customers;
+--------+------------------+------+-----+---------+----------------+
| Field | Type
| Null | Key | Default | Extra
|
+--------+------------------+------+-----+---------+----------------+
| custid | int(10) unsigned | NO
| PRI | NULL
| auto_increment |
| name
| varchar(20)
| NO
|
|
|
|
+--------+------------------+------+-----+---------+----------------+
mysql> select * from customers;
+--------+------+
| custid | name |
+--------+------+
|
1 | Fred |
|
2 | Bill |
|
3 | Sue |
+--------+------+

Second example table:
mysql> describe orders;
+---------+------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+---------+------------------+------+-----+---------+----------------+
| orderid | int(10) unsigned | NO
| PRI | NULL
| auto_increment |
| custid | int(10) unsigned | NO
|
|
|
|
| amount | float(6,2)
| YES |
| NULL
|
|
+---------+------------------+------+-----+---------+----------------+
mysql> select * from orders;
+---------+--------+--------+
| orderid | custid | amount |
+---------+--------+--------+
|
1 |
1 |
5.25 |
|
2 |
2 |
1.30 |
|
3 |
3 |
3.50 |
|
4 |
1 |
2.25 |
|
5 |
2 |
2.45 |
|
6 |
3 |
4.25 |
|
7 |
1 |
6.20 |
+---------+--------+--------+

Distinguish between custid in customers and orders tables
with dot notation: customers.custid, orders.custid

When more than one table involved, good style to use this notation
for all columns
mysql> select customers.name, orders.amount
-> from customers, orders
-> where customers.custid = orders.custid;
+------+--------+
| name | amount |
+------+--------+
| Fred |
5.25 |
| Bill |
1.30 |
| Sue |
3.50 |
| Fred |
2.25 |
| Bill |
2.45 |
| Sue |
4.25 |
| Fred |
6.20 |
+------+--------+

This is an equi-join


An inner join where the condition is the equality between
values in columns in 2 different tables
Can replace comma in the WHERE clause with INNER JOIN:
-> from customers inner join orders
Ordering and Aggregating Data

View rows ordered by the value of some column by using an
ORDER BY clause
mysql> select orderid, amount from orders
-> order by amount;
+---------+--------+
| orderid | amount |
+---------+--------+
|
2 |
1.30 |
|
4 |
2.25 |
|
5 |
2.45 |
|
3 |
3.50 |
|
6 |
4.25 |
|
1 |
5.25 |
|
7 |
6.20 |
+---------+--------+

To see rows in descending order of the amount, add DESC after
amount:
mysql> select orderid, amount from orders
-> order by amount desc;
+---------+--------+
| orderid | amount |
+---------+--------+
|
7 |
6.20 |
|
1 |
5.25 |
|
6 |
4.25 |
|
3 |
3.50 |
|
5 |
2.45 |
|
4 |
2.25 |
|
2 |
1.30 |
+---------+--------+
7 rows in set (0.05 sec)

Indicate ascending order with ASC (but ascending is the default)


Get aggregate statistics from a table using various functions

Take a column name as argument

Occur just after the SELECT keyword
2 such functions

avg(), computing average of values for the column

std(), giving their standard deviation
mysql> select avg(amount) as Average,
->
std(amount) as 'Standard Deviation'
-> from orders;
+----------+--------------------+
| Average | Standard Deviation |
+----------+--------------------+
| 3.600000 |
1.619083 |
+----------+--------------------+

Similar functions: min(), max(), and sum()

count() gives number of nun-NULL values for the column

use count(*) to get a count of all rows in the table:
mysql> select count(*) from orders;

To apply these functions to rows grouped by values of a
column use a GROUP BY clause
mysql> select custid, avg(amount) from orders
-> group by custid;
+--------+-------------+
| custid | avg(amount) |
+--------+-------------+
|
1 |
4.566667 |
|
2 |
1.875000 |
|
3 |
3.875000 |
+--------+-------------+

Restrict the groups included by using a HAVING clause with a
condition

(Compare: WHERE clause)
mysql> select custid, avg(amount) from orders
-> group by custid
-> having avg(amount) > 2;
+--------+-------------+
| custid | avg(amount) |
+--------+-------------+
|
1 |
4.566667 |
|
3 |
3.875000 |
+--------+-------------+