Introduction to

SQL and MySQL

Madam Hana
cd c:\xampp\mysql\bin
mysql.exe –u root
2nd method

mysql.exe –u root
 Intro. to SQL and MySQL

1. Choosing your column types

2. Choosing other column properties
3. Using mysql client
4. Creating databases and tables
5. Inserting records
6. Selecting data
7. Using conditional
 Intro. to SQL and MySQL

8. Using LIKE and NOT LIKE

9. Sorting query result
10.Limiting query result
11.Updating data
12.Deleting data
13.Using functions
 Intro. to SQL and MySQL

• Leave PHP a while …

• Concentrate on database
– SQL and MySQL
• Need solid understanding to write PHP script
that use SQL and interact with MySQL
 SQL

• Structured Query Language, also known

as SQL (and frequently pronounced
"sequel"), is a powerful tool for data
manipulation and has become the
standard query language for relational
database management systems.
 SQL
• Main language for relational DBMSs.
• Main characteristics:
– relatively easy to learn;
– non-procedural - you specify what information you
require, rather than how to get it;
– essentially free-format;
– consists of standard English words like SELECT,
INSERT, and UPDATE;
– can be used by range of users
 MySQL

• Most popular open source database

application
• Commonly use with php
 Choosing your column types
user s Table
COLUMN NAME EXAMPLE
user_id 520008
first_name Hana
last_name Munira
email hanamunira@miit.unikl.edu.my
password phpmysql
registration_date 2012-12-02 02:47:00

The users table will have these 6 columns to store records like the
sample data here
 First thing first

1. Create database
2. Create table
1. How many columns?
• 6 columns
2. Column name?
• user_id, first_name, last_name, email, password, reg_date
3. Column type?
• 3 primay categories
1. Text
2. Numbers
3. Dates and Times
 MySQL Data Type
Type Size Description

CHAR Length bytes A fixed length field from 0 to 255 char long

VARCHAR String length + 1 bytes A variable length field from 0 to 255 char long

TEXT String length + 2 bytes A string with a max length of 65,535 char

INT 4 bytes Range of -2,147, 483, 648 to 2,147,483,647

FLOAT 4 bytes A small number with a floating decimal point

DATE 3 bytes In the format of YYYY-MM-DD

DECIMAL Length + 1 bytes A double stored as a string, allowing for a fixed

decimal point
TIME 3 bytes In the format of HH:MM:SS

SET 1,2,3,4 Short for enumeration (each column can have

more than one of several possible values)
ENUM 1 or 2 bytes Short for enumeration (each column can have
one of several possible values)
 To select the column types:
user s Table
COLUMN NAME TYPE
user_id number
first_name text
last_name text
email text
password text
registration_date date/time

The users table with generic data types

 To select the column types:
user s Table
COLUMN NAME TYPE
user_id MEDIUMINT
first_name VARCHAR
last_name VARCHAR
email VARCHAR
password CHAR
registration_date DATETIME

The users table with more specific data types

 To select the column types:
user s Table
COLUMN NAME TYPE
user_id MEDIUMINT
first_name VARCHAR(15)
last_name VARCHAR(30)
email VARCHAR(40)
password CHAR(40)
registration_date DATETIME

The users table with set length attributes

 CHAR vs. VARCHAR

• To store STRING

• CHAR
– stored as a string the length of the column

• VARCHAR
– will be as long as the stored string itself!
– Tend to take up less disc space
Choosing other column properties

• Other than data types and sizes,

– The type can be set as NOT NULL or NULL
• NULL
– The field has no value
• NOT NULL
– To force a field to have a value
• The number types can be marked as UNSIGNED,
which limits the stored data to positive numbers and
zero.
 To select the column types:
user s Table
COLUMN NAME TYPE
user_id MEDIUMINT UNSIGNED NOT NULL
first_name VARCHAR(15) NOT NULL
last_name VARCHAR(30) NOT NULL
email VARCHAR(40) NOT NULL
password CHAR(40) NOT NULL
registration_date DATETIME NOT NULL

The final description of the users table

 Keys and AUTO_INCERMENT

• There are two types of keys:

– Primary
• Each table should have one primary key
– Foreign
• The primary key in a table is often linked as a
foreign key in another table
 Keys and AUTO_INCERMENT

• A table’s primary key is an artificial way to

refer to a record and should abide 3 rules:
1. It must always have a value
2. That value must never change
3. That value must be unique for each record in the
table
 AUTO_INCREMENT tells MySQL to use the
next-highest number as the user_id for
each added record
 Using the mysql client

• Access your system from a command-line

interface
Creating Databases & Tables
 To create databases and tables:

• Syntax to create database

– Create database databasename;
• Syntax to create tables
– Create table tablename (column1name
description, column2name description);
• Syntax to confirm existence of the database
and table
– Show databases;
– Show tables;
mysql> CREATE DATABASE sitename;
Query OK, 1 row affected (0.06 sec)

mysql> SHOW DATABASES;

+----------+
| Database |
+----------+
| mysql |
| sitename |
+----------+
2 rows in set (0.00 sec)

mysql> USE sitename;

Database changed
mysql>
mysql> CREATE TABLE users (
-> user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> first_name VARCHAR(15) NOT NULL,
-> last_name VARCHAR(30) NOT NULL,
-> email VARCHAR(40) NOT NULL,
-> password CHAR(40) NOT NULL,
-> registration_date DATETIME NOT NULL,
-> PRIMARY KEY (user_id)
-> );
Query OK, 0 rows affected (0.11 sec)

• Enclose entire list of field names between one pair of

parentheses.
• Commas are used between each field.
• A space may be used after the comma between fields.
• A comma is not used after last field.
• This, and all SQL statements, are concluded by a
semicolon ";".
mysql> SHOW TABLES;
+--------------------+
| Tables_in_sitename |
+--------------------+
| users |
+--------------------+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM users;

+-------------------+-----------------------+------+-----+---------------------+
----------------+
| Field | Type | Null | Key | Default |
Extra |
+-------------------+-----------------------+------+-----+---------------------+
----------------+
| user_id | mediumint(8) unsigned | | PRI | NULL |
auto_increment |
| first_name | varchar(15) | | | |
|
| last_name | varchar(30) | | | |
|
| email | varchar(40) | | | |
|
| password | varchar(40) | | | |
|
| registration_date | datetime | | | 0000-00-00 00:00:00 |
|
+-------------------+-----------------------+------+-----+---------------------+
----------------+
6 rows in set (0.00 sec)
 Inserting Records
 Syntax
1. INSERT INTO tablename1(column1,column2) VALUES
(‘value1’,’value2’);
 add rows of records, populating only columns that matter. Any
columns not given a value will be treated as null value or
default value, if it was defined.

2. INSERT INTO tablename1 VALUES (‘value1’,’value2’);

 Must specify a value, even if it’s a null, for every column.

3. INSERT INTO tablename1 (column1,column2) VALUES

(‘value1’,’ value2’),(‘value3’,’ value4’),(‘value5’,’ value6’);
 Insert multiple rows at one time, separating each record by a
comma
mysql> INSERT INTO users (first_name, last_name, email,
password, registration_date) VALUES ('Larry', 'Ullman',
'larryullman@php.com', SHA('password'), NOW());
Query OK, 1 row affected (0.13 sec)

This query inserts a single record into the users table

• Enclose entire list of field names between one pair of

parentheses.
• Enclose the values to be inserted between another pair
of parentheses.
• Commas are used between each field and between each
value.
• A space may be used after the comma between fields.
 Two MySQL functions

• SHA( )
one way to encrypt data
• NOW( )
 will insert the current date and
time
mysql> INSERT INTO users (first_name, last_name, email, password, registration_date)

VALUES

-> ('John', 'Lennon', 'john@beatles.com', SHA('Happin3ss'), NOW()),

-> ('Paul', 'McCartney', 'paul@beatles.com', SHA('letITbe'), NOW()),

-> ('George', 'Harrison', 'george@beatles.com', SHA('something'), NOW()),

-> ('Ringo', 'Starr', 'ringo@beatles.com', SHA('thisboy'), NOW());

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

This query inserts a multiple record into the users table

at once
 Cont.
• Quotes must go around text values.
• Standard date format is "yyyy-mm-dd".
• Standard time format is "hh:mm:ss".
• Quotes are required around the standard date
and time formats, noted above.
• Dates may also be entered as "yyyymmdd"
and times as "hhmmss". If entered in this
format, values don't need to be quoted.
 Cont.
• Numeric values do not need to be quoted.
This holds true regardless of the data type a
column is formatted to contain (e.g. text,
date, time, integer).
• MySQL has a useful command buffer. The
buffer stores the SQL statements you've
entered thus far. Using it keeps you from
having to retype the same commands over
and over.
 Selecting data

• Syntax
– SELECT which column FROM which table
• To specify the columns to be returned

– SELECT * FROM tablename;

• To view every column from selected table
mysql> SELECT * FROM users;
+---------+------------+-----------+---------------------+----------------------
--------------------+---------------------+
| user_id | first_name | last_name | email | password
| registration_date |
+---------+------------+-----------+---------------------+----------------------
--------------------+---------------------+
| 1 | Larry | Ullman | larryullman@php.com | 5baa61e4c9b93f3f06822
50b6cf8331b7ee68fd8 | 2012-02-03 10:15:01 |
| 2 | John | Lennon | john@beatles.com | 2a50435b0f512f60988db
719106a258fb7e338ff | 2012-02-03 10:24:43 |
| 3 | Paul | McCartney | paul@beatles.com | 6ae16792c502a5b47da18
0ce8456e5ae7d65e262 | 2012-02-03 10:24:43 |
| 4 | George | Harrison | george@beatles.com | 1af17e73721dbe0c40011
b82ed4bb1a7dbe3ce29 | 2012-02-03 10:24:43 |
| 5 | Ringo | Starr | ringo@beatles.com | 520f73691bcf89d508d92
3a2dbc8e6fa58efb522 | 2012-02-03 10:24:43 |
+---------+------------+-----------+---------------------+----------------------
--------------------+---------------------+
5 rows in set (0.05 sec)

The SELECT * FROM tablename query returns every

column for every record.
mysql> SELECT first_name, last_name FROM users;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Larry | Ullman |
| John | Lennon |
| Paul | McCartney |
| George | Harrison |
| Ringo | Starr |
+------------+-----------+
5 rows in set (0.00 sec)

All of the records but only two of the columns are returned
by this query
 Using conditionals
 To limit the returned data from the table using
SELECT statement
 Syntax
 SELECT * FROM tablename WHERE column=‘value’;
 Example:
 SELECT email FROM users WHERE last_name=‘Tom’;

 SELECT name FROM people WHERE birth_date=‘2003-01-

26’;
 MySQL Operators
OPERATOR MEANING
= Equals
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
!= Not equal to
IS NOT NULL Has a value
IS NULL Does not have a value
BETWEEN Within a range
NOT BETWEEN Outside of a range
OR (also ||) Where one of two conditionals is true
AND (also &&) Where both conditionals are true
NOT (also !) Where the condition is not true
mysql> SELECT first_name, last_name FROM users WHERE
password=SHA('password');
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Larry | Ullman |
+------------+-----------+
1 row in set (0.05 sec)
mysql> SELECT user_id, first_name, last_name FROM users WHERE
(user_id >= 2) AND (user_id <= 4);
+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| 2 | John | Lennon |
| 3 | Paul | McCartney |
| 4 | George | Harrison |
+---------+------------+-----------+
3 rows in set (0.00 sec)
 Using LIKE and NOT LIKE
 Using numbers, dates and NULLs in conditionals is
straightforward process but string is trickier
 Comparing strings in more liberal manner requires extra
operators and characters
 Use LIKE and NOT LIKE used primarily with strings in
conjunction with two wildcard characters:
 The underscore (-) –> matches a single character
 Percentage sign (%) –> matches zero or more characters.
 Syntax
 SELECT * FROM users WHERE last_name LIKE
‘smith%’;
 This query will perform a search on all columns whose
last_name value begins with Smith or smith.
mysql> SELECT user_id, first_name, last_name, email FROM users WHERE
email LIKE '%@beatles.com';
+---------+------------+-----------+--------------------+
| user_id | first_name | last_name | email |
+---------+------------+-----------+--------------------+
| 2 | John | Lennon | john@beatles.com |
| 3 | Paul | McCartney | paul@beatles.com |
| 4 | George | Harrison | george@beatles.com |
| 5 | Ringo | Starr | ringo@beatles.com |
+---------+------------+-----------+--------------------+
4 rows in set (0.00 sec)

mysql> SELECT user_id, first_name, last_name, email FROM users WHERE

email NOT LIKE '%@beatles.com';
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | email |
+---------+------------+-----------+---------------------+
| 1 | Larry | Ullman | larryullman@php.com |
+---------+------------+-----------+---------------------+
1 row in set (0.00 sec)
 Sorting query results
• Syntax
– SELECT * FROM tablename ORDER BY
column;
• You know that WHERE places restrictions on what
record are returned, now, the ORDER BY clause will
affect how those records are presented.
• The default order when using ORDER BY is ascending
(ASC), means number increase from small to large and
dates go from older to most recent.
• To reverse order, use DESC
mysql> SELECT user_id, first_name, last_name FROM users ORDER BY
first_name ASC;

+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| 4 | George | Harrison |
| 2 | John | Lennon |
| 1 | Larry | Ullman |
| 3 | Paul | McCartney |
| 5 | Ringo | Starr |
+---------+------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT user_id, first_name, last_name FROM users ORDER BY

first_name DESC;

+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| 5 | Ringo | Starr |
| 3 | Paul | McCartney |
| 1 | Larry | Ullman |
| 2 | John | Lennon |
| 4 | George | Harrison |
+---------+------------+-----------+
5 rows in set (0.00 sec)
 Limiting query result

• LIMIT – states how many records to be

returned
• Syntax
– SELECT * FROM tablename LIMIT 10;
• Use LIMIT with WHERE, ORDER BY
• Append to the end of query
mysql> SELECT user_id, first_name, last_name, registration_date FROM
users ORDER BY registration_date DESC;
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | registration_date |
+---------+------------+-----------+---------------------+
| 2 | John | Lennon | 2012-02-03 10:24:43 |
| 3 | Paul | McCartney | 2012-02-03 10:24:43 |
| 4 | George | Harrison | 2012-02-03 10:24:43 |
| 5 | Ringo | Starr | 2012-02-03 10:24:43 |
| 1 | Larry | Ullman | 2012-02-03 10:15:01 |
+---------+------------+-----------+---------------------+
5 rows in set (0.02 sec)

mysql> SELECT user_id, first_name, last_name, registration_date FROM

users ORDER BY registration_date DESC LIMIT 2;
+---------+------------+-----------+---------------------+
| user_id | first_name | last_name | registration_date |
+---------+------------+-----------+---------------------+
| 2 | John | Lennon | 2012-02-03 10:24:43 |
| 3 | Paul | McCartney | 2012-02-03 10:24:43 |
+---------+------------+-----------+---------------------+
2 rows in set (0.00 sec)
 Updating data
• To change existing record use update function
• Syntax
– UPDATE tablename SET column=‘value’;
• To modify one record at one time
– UPDATE tablename SET column=‘value’,
column2=‘value2’;
• To modify multiple record at one time
mysql> SELECT first_name, email FROM users where last_name = 'Ullman';
+------------+---------------------+
| first_name | email |
+------------+---------------------+
| Larry | larryullman@php.com |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE users SET

-> email = 'php@authors.com'
-> WHERE first_name = 'Larry';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT first_name, email FROM users where last_name = 'Ullman';

+------------+-----------------+
| first_name | email |
+------------+-----------------+
| Larry | php@authors.com |
+------------+-----------------+
1 row in set (0.00 sec)
 Updating data
Modify one field at a time
Again, be careful with syntax. Quote marks need to go
around text but not around numbers.

• UPDATE all rows

Give all staff a 3% pay increase
UPDATE staff
SET salary = salary*0.03;
 Updating data
• UPDATE specific rows
Give all Managers a 3% pay increase
UPDATE staff
SET salary = salary*0.03
WHERE position = ‘Manager’;

• UPDATE multiple columns

Promote David (sno=‘SG14’) to Manager and change his salary
to RM18,000
UPDATE staff
SET position = ‘Manager’, salary = 18000
WHERE sno = ‘SG14’;
 Deleting data

• Entirely remove it from database

• Syntax
– DELETE FROM tablename WHERE
column=‘value’;
• If you have deleted a record, there is no way
of retrieving it. So make sure backup your
database before deleting data.
mysql> SELECT user_id, first_name, last_name FROM users;
+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| 1 | Larry | Ullman |
| 2 | John | Lennon |
| 3 | Paul | McCartney |
| 4 | George | Harrison |
| 5 | Ringo | Starr |
+---------+------------+-----------+
5 rows in set (0.00 sec)

mysql> DELETE FROM users WHERE user_id = 1;

Query OK, 1 row affected (0.02 sec)

mysql> SELECT user_id, first_name, last_name FROM users;

+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| 2 | John | Lennon |
| 3 | Paul | McCartney |
| 4 | George | Harrison |
| 5 | Ringo | Starr |
+---------+------------+-----------+
4 rows in set (0.00 sec)
 Deleting data
• DELETE specific rows
DELETE from product
WHERE vendor-id = 123

• DELETE all rows

DELETE from product;

No WHERE clause specified, so the delete operation applies to all rows

in the table
 Time to Call it Quits

mysql> quit
Bye
 FUNCTIONS
1. TEXT FUNCTIONS
2. NUMERIC FUNCTIONS
3. DATE & TIME FUNCTIONS
4. FORMATTING THE DATE AND TIME
Text Functions
mysql> SELECT CONCAT(first_name, ' ', last_name) FROM users;
+------------------------------------+
| CONCAT(first_name, ' ', last_name) |
+------------------------------------+
| John Lennon |
| Paul McCartney |
| George Harrison |
| Ringo Starr |
+------------------------------------+
4 rows in set (0.58 sec)

This simple concatenation pulls out the users’ full names.

Make a note of the column’s heading.
mysql> SELECT CONCAT(first_name, ' ', last_name) AS Name
FROM users;
+-----------------+
| Name |
+-----------------+
| John Lennon |
| Paul McCartney |
| George Harrison |
| Ringo Starr |
+-----------------+
4 rows in set (0.00 sec)

By using an alias, the returned data is under the column

heading of Name
mysql> SELECT LENGTH(last_name) AS L,
-> last_name FROM users
-> ORDER BY L DESC
-> LIMIT 1;
+---+-----------+
| L | last_name |
+---+-----------+
| 9 | McCartney |
+---+-----------+
1 row in set (0.06 sec)

The LENGTH() function returns the length of a column’s

value
Numeric Functions
Numeric Functions
FUNCTION USAGE PURPOSE
ABS() ABS(x) Returns the absolute value of x
CEILING() CEILING(x) Returns the next-highest integer based upon
the value of x
FLOOR() FLOOR(x) Returns the integer value of x
FORMAT() FORMAT(x, y) Return the formatted as a number with y
decimal places and commas inserted every
three spaces
MOD() MOD(x, y) Returns the remainder of dividing x by y
RAND() RAND() Returns a random number between 0 and 0.1
ROUND() ROUND(x, y) Returns the number x rounded to y decimal
places
SIGN() SIGN(x) Returns a value indicating whether a number
is negative, zero or positive
SQRT() SQRT(x) Calculates the square root of x
mysql> SELECT CONCAT('$', FORMAT(5639.6, 2)) AS cost;
+-----------+
| cost |
+-----------+
| $5,639.60 |
+-----------+
1 row in set (0.05 sec)

Using an arbitrary example, this query shows how the

FORMAT() function works
Date and Time Functions
Date and Time Functions
FUNCTION USAGE PURPOSE
HOUR() HOUR(column) Returns just the hour value of a
stored date
MINUTE() MINUTE(column) Returns just the minute value of
the stored date
SECOND() SECOND(column) Returns just the second value of
the stored date
DAYNAME() DAYNAME(column) Returns the name of the day for a
date value
DAYOFMONTH() DAYOFMONTH(column) Returns the numerical day value of
a stored date
MONTHNAME() MONTHNAME(column) Returns the name of the month in
a date value
MONTH() MONTH(column) Returns the numerical month value
of a stored date
YEAR() YEAR(column) Returns just the year value of a
stored date
NOW() NOW() Returns the current date and time
mysql> SELECT CURDATE(), CURTIME();
+------------+-----------+
| CURDATE() | CURTIME() |
+------------+-----------+
| 2012-02-17 | 10:31:35 |
+------------+-----------+
1 row in set (0.05 sec)

This query, not run on any particular table, returns the

current date and time
mysql> SELECT DATE_FORMAT(NOW(), '%M %e, %Y - %l:%i');
+-----------------------------------------+
| DATE_FORMAT(NOW(), '%M %e, %Y - %l:%i') |
+-----------------------------------------+
| February 17, 2012 - 10:34 |
+-----------------------------------------+
1 row in set (0.06 sec)

The current date and time, formatted

mysql> SELECT TIME_FORMAT(CURTIME(), '%T');
+------------------------------+
| TIME_FORMAT(CURTIME(), '%T') |
+------------------------------+
| 10:35:41 |
+------------------------------+
1 row in set (0.00 sec)

The current time, in a 24-hour format

 Write a PHP program for the
following questions:
• Write a program to call a function name
myBiodata(). The function should display
your name, age and course.
• Write a program to call a function name
multiplication(). The function should
receive two numbers. Multiply the two
numbers and return the multiplication results.
 Write a PHP program for the
following questions:
• Write a program to call a function named
calculateareas(). The calculate areas is
supposed to receive two values – height and
base. The function is to calculate the area of
a triangle. Return the result of the area.
– Formula:
– Area of triangle = 0.5 * base * heights
That’s all for today.

Thank you.