>> Introduction to MySQL
Web-Based Systems | Misbhauddin 2
Introduction
• Structured Query Language (SQL)– Standard Database Language– Manage Data in a DBMS (Database Management
System)• MySQL, Oracle, MS SQL Server
Web-Based Systems | Misbhauddin 3
Databases• Database– Stores data for fast and efficient access– Structured into tables
Name ID Major GPA
Abdur Rahman 312456 CIS 3.97
Ahmed 312198 CS 4.00
Mohammed 312786 CIS 2.78
Rows
Columns
RecordField
Web-Based Systems | Misbhauddin 4
MySQL
• Fast, Reliable, Easy-to-use• Multi-user• Freely available• Network Database of choice (used mostly with
PHP)– Connectivity– Speed– Security
Web-Based Systems | Misbhauddin 5
Getting Started
• Operations on a DBMS (CRUD)– Create– Retrieve– Update– Delete
• Target– Databases– Tables– Records
Note: SQL is not case sensitive
Web-Based Systems | Misbhauddin 6
CREATE
Web-Based Systems | Misbhauddin 7
Create Database
Using a database
CRUDDTR
Create a new DB called MyDBUse that databaseTRY NOW
SYNTAXCREATE DATABASE Database_Name;
USE Database_Name;
Web-Based Systems | Misbhauddin 8
Primary Key
PRIMARY KEY• uniquely identifies each record in a database table• must contain unique values• cannot contain NULL• each table should have a primary key• each table can have only ONE primary key
Web-Based Systems | Misbhauddin 9
Create Table CRUDDTR
SYNTAXCREATE TABLE Table_Name (column_specifications);
For each column• Name of the column• Data type• Size• Other constraints
int (signed, unsigned)double, floatchar, varchartextblobdate, time, datetime
Student_ID Name Major GPA
Create Table students(Student_ID int unsigned,Name varchar(50),Major varchar(3),GPA float,PRIMARY KEY(Student_ID));
TRY NOW
NOT NULLUNIQUECHECKDEFAULT
Web-Based Systems | Misbhauddin 10
Insert Record CRUDDTR
SYNTAXINSERT INTO table_name SET col_name1=value1, col_name2=value2, col_name3=value3, …
OR
Student_ID Name Major GPA
312981 Mohammed
CIS 4.00
TRY NOW
INSERT INTO table_name (col_name1, col_name2) VALUES (value1, value2);
Note: Strings in ‘ ‘(single quotes)
Web-Based Systems | Misbhauddin 11
RETRIEVE
Web-Based Systems | Misbhauddin 12
Retrieve Database
• Listing Existing Databases– show databases;
CRUDDTR
Show databases;TRY NOW
Web-Based Systems | Misbhauddin 13
Retrieve Table• Using a Database– use db-name ;
• Listing Tables in a Database– show tables;
• Table Structure– describe table-name;
CRUDDTR
Look at the table you createdTRY NOW
Web-Based Systems | Misbhauddin 14
Retrieve Record CRUDDTR
SYNTAXSELECT column_name(s)FROM table_name
Using * here will select all columns
TRY NOW
Web-Based Systems | Misbhauddin 15
Retrieve Record CRUDDTR
SELECT
SELECT column_name(s)FROM table_name
DISTINCT (Return only unique values)
WHERE column_name operator value
Operator Description
= Equal
!= Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
AND column_name2 operator value2
OR column_name2 operator value2
ORDER BY column_name(s) ASC|DESC
Web-Based Systems | Misbhauddin 16
UPDATE
Web-Based Systems | Misbhauddin 17
Update Database
• Can only rename a Database• Can be done from the DBMS (outside the
SQL)• Other way– Create new database (with new name)– Make dump of old database– Import dumped data into new database– Delete old database
CRUDDTR
Web-Based Systems | Misbhauddin 18
Alter TableCRUDDTR
1. Add a new column “DOB” with type date2. Change the new column’s datatype to year3. Delete Column GPA
TRY NOW
SYNTAXALTER TABLE table_name
ADD column_name datatypeMODIFY column_name datatype
DROP COLUMN column_name
OROR
Web-Based Systems | Misbhauddin 19
Update Record CRUDDTR
SYNTAXUPDATE table_nameSET column1=value1, column2=value2,...WHERE column=value;
RememberChoose a unique (or Primary Key) here else multiple rows will be updatedTRY NOW
Student_ID Name Major DOB
312981 Mohammed
CIS
Add “1990” here
Web-Based Systems | Misbhauddin 20
DELETE
Web-Based Systems | Misbhauddin 21
Delete Record CRUDDTR
SYNTAXDELETE FROM table_nameWHERE column=value;
Delete this record
TRY NOW
Student_ID Name Major DOB
312981 Mohammed
CIS 1990
Note: Remove this to delete all rows.
Web-Based Systems | Misbhauddin 22
Drop TableCRUDDTR
Drop the created tableTRY NOW
SYNTAXDROP TABLE Table_Name;
Web-Based Systems | Misbhauddin 23
Drop DatabaseCRUDDTR
Drop the created databaseTRY NOW
SYNTAXDROP DATABASE Database_Name;