SQL

1. SQL Structured Query Language

2. Structured Query Language • Data Definition Language (DDL) is used to manage tableand define data structure i.e. CREATE, ALTER, DROP • Data Control Language (DCL) is used to control user’s privilege on accessing data i.e. GRANT, REVOKE • Data Manipulation Language (DML) is used to manage data record i.e. ADD, UPDATE, and DELETE. • This may include SELECT but it may be consider as a member of Data Query Language (DQL)

3. Browsing Databases • Some useful commands to begin with • show databases; • use <database_name>; • show tables; • desc <table_name>; • create database <database_name>; • Create a new database CREATE DATABASE BangNa;

4. Create a Table CREATE TABLE <table_name> ( column_name1 <col_type> ….., ............ column_name2 <col_type> ….., ............ ……………………….. ) CREATE TABLE std_phone ( STD_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, FNAME VARCHAR(64) NOT NULL , LNAME VARCHAR(64) NOT NULL , PHONE VARCHAR(12) NOT NULL )

5. Create a User • It is a good idea to create a username to manage tables in new created database • Proper privileges can be granted to a particular user so that only a user who has right access can manage the table GRANT <previledge> [(col1, col2, … colN)] ON database.[table] TO user@host IDENTIFIED BY 'passwd'; GRANT select ON webtech.student_profile TO tct IDENTIFIED BY ‘tct';

6. SELECT Statements • Select all records (rows) from a table • Select some columns of all records from a table SELECT * FROM <table_name>; SELECT * FROM std_phone; SELECT col1, col2,….coln FROM <table_name>; SELECT std_id, fname, lname FROM std_phone;

7. SELECT Statements (cont.) • Select some records from a table • WHERE clause could be any boolean expression SELECT * FROM <table_name> WHERE <condition>; SELECT * FROM std_phoneWHERE std_id < 20; SELECT * FROM std_phone WHERE std_id < 20 AND fname like ‘sor%’;

8. INSERTINTO table (col1, col2, col3) VALUES(val1, val2, val3); INSERT INTO std_phone (fname,lname,phone) VALUES(“Khaosai”,“Galaxy”,“088-123-4567”); INSERT INTO • Insert a record into a table • Insert record(s) from a table right into another table INSERT INTO std_phone (fname, lname, phone) select fname, lname, phone from std_profiles where academic_year= ‘2552’;

9. Edit a Record • Modify a record • Modify Khaosai’s phone number UPDATE <table> SET field1=’val1’,field2=’val2’,field3=’val3’WHERE <condition>; UPDATE std_phoneSET phone=‘089-123-1234’ WHERE fname= ‘Khaosai’ AND lname= ‘Galaxy’;

10. Delete Record(s) • Delete selected record(s) • Delete Khaosai’s record from the table • This will delete all records with firstname ‘Khaosai’ • This will delete all records with lastname ‘Galaxy’ DELETE FROM <table> WHERE <condition>; DELETE FROM std_phoneWHERE fname= ‘Khaosai’; DELETE FROM std_phoneWHERE lname= ‘Galaxy’;

11. Delete Record(s) • Do a better job with AND • Anyway, this would be a better choice by using primary key to locate the target record to be deleted. • Note: avoid this; • it will delete all records in the tatble DELETE FROM std_phoneWHERE fname= ‘Khaosai’ AND lname= ‘Galaxy’; DELETE FROM std_phoneWHERE std_id= 20; DELETE FROM std_phone;