SQL Structured Query Language 1 Structured Query Language

SQL Structured Query Language 1

Structured Query Language • Data Definition Language (DDL) is used to manage table and 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) 2

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 Bang. Na; 3

Create a Table CREATE TABLE <table_name> ( column_name 1 <col_type> …. . , . . . column_name 2 <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 ) 4

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> [(col 1, col 2, … col. N)] ON database. [table] TO user@host IDENTIFIED BY 'passwd'; GRANT select ON webtech. student_profile TO tct IDENTIFIED BY ‘tct'; 5

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

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

INSERT INTO • Insert a record into a table INSERT INTO table (col 1, col 2, col 3) VALUES(val 1, val 2, val 3; ( INSERT INTO std_phone (fname, lname, phone) VALUES(“Khaosai”, “Galaxy”, “ 088 -123 -4567”); • 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’; 8

Edit a Record • Modify a record UPDATE <table> SET field 1=’val 1’, field 2=’val 2’, field 3=’val 3’ WHERE <condition>; • Modify Khaosai’s phone number UPDATE std_phone SET phone=‘ 089 -1234’ WHERE fname = ‘Khaosai’ AND lname = ‘Galaxy’; 9

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

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

12