CS 222 DATABASE MANAGEMENT SYSTEM Somchai Thangsathityangkul Create

CS 222 DATABASE MANAGEMENT SYSTEM Somchai Thangsathityangkul

Create table author ( author. ID int auto_increment primary key, first. Name varchar(20) not null, last. Name varchar(30) not null ); 2

THE INSERT STATEMENT SYNTAX Insert data should be insert data into the parent table first then insert data into child table. 3

INSERT DATA INTO THE BOOK COLLECTION TABLE This is an initial value for auto increment data type • Let insert data into Author • Insert into Author values( 200001, ’Donald’, ’Trump’ ); Just put null for the next time, this will be • Insert into Author incremented by 1. So, this value = 200002 values( null, ’James’, ’Blunt’ ); • To show data in the table use select command. • Select * from Author; 4

INSERT MULTIPLE DATA INTO THE TABLE • Suppose , we would like to insert into one table with many rows as once. For example, Entity Author with attribute : Author_id, First. Name, Last. Name • Use this : • Insert into Author values( null, ‘Jenny’, ’Lee’ ), ( null, ‘Stephen’, ‘King’ ), ( null, ‘Sunny’, ’Be cool’) ; 5

INSERT DATE TIME DATATYPE INTO TABLE My. SQL recognizes DATE values in these formats: • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. • For example, '2012 -12 -31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent. • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format • For example, '20070523' and '070523' are interpreted as '2007 -05 -23’ • As a number in either YYYYMMDD or YYMMDD format • For example, 19830905 and 830905 are interpreted as '1983 -09 -05'. 6

INSERT DATE TIME DATATYPE INTO TABLE Create table testdate( Name varchar(10), dtype date ); Insert into testdate values ( ‘ABC’, '2012/12/31’), (‘BCD’, '20070523’), (‘XYZ’, 19830905); Select * from testdate; 7

TRY THIS Finish this ERD , then create all tables and insert data in all tables with at least 5 students, 3 faculties. STUDENT ARE IN FACULTY 8

THE UPDATE STATEMENT SYNTAX 9

THE UPDATE STATEMENT Update testdate set name = ‘John’; Select * from testdate; Select * from author ; Update author set first. Name = ‘Harry’ , last. Name = ‘Potter’ where author. ID = 200004; Select * from author; 10

THE DELETE STATEMENT SYNTAX You can remove existing rows from a table by using the DELETE statement. 11

THE DELETE STATEMENT Insert into testdate values ( ‘ABCD’, ‘ 2010/10/21’), (‘BECD’, ‘ 20090925’); Select * from testdate; Delete from testdate where dtype=20121231; Select * from testdate ; Delete from testdate; Select * from testdate; 12

CREATE A SCRIPT FILE • After start text editor such as Notepad, we can type any command save file as. sql • Let do the create command. 13

CREATE A SCRIPT FILE • Create table parent ( p_id int auto_increment primary key, p_name varchar(25) ) ; Create table child ( c_id int auto_increment primary key, c_name varchar(20) not null, p_id int not null, foreign key( p_id ) references parent( p_id ) ); 14

CREATE A SCRIPT FILE • Now , save file as c: /pc. sql • In my. SQL console type command : source c: /pc. sql 15

CREATE A SCRIPT FILE Insert into parent ( p_id, p_name) values ( 1001, ‘Jerry Jone’), (null, ‘Norm Johnson’), (null, ‘Harry Potter’) ; Insert into child ( c_id, c_name, p_id ) values ( 111, ‘Sara Kim’, 1002 ), (null, ‘Kim Jo Min’, 1002 ), (null, ‘Jonny Oven’, 1001), (null, ‘Dave Crage’, 1003) ; ============================ • Now , save file as c: /pc 2. sql • In my. SQL console type command : source c: /pc 2. sql 16

CREATE DEMO DATA • Create a folder c: /temp • Copy file mysqlsampledatabase. sql to c: /temp/ • Source c: /temp/mysqlsampledatabase. sql; 17

SELECT STATEMENT SELECT is the most commonly used DML command for queries: Retrieves rows from tables in a database Returns rows as a “result set” in the form of a table 18

SELECT STATEMENT General syntax: SELECT [<clause options>] <column list> [FROM <table_name>] [<other_clauses>] column_list is a list of column names that make up the result set. Separate the items in the list with a comma separator (, ). 19

SELECT STATEMENT Desc employees ; 20

SELECT STATEMENT Select employee. Number, first. Name, last. Name From employees; 21

USING SELECT CLAUSES • Syntax example (with the order of the clauses fixed): SELECT [DISTINCT] <values_to_return> FROM <table_name> WHERE <condition> ORDER BY <how_to_sort> LIMIT <row_count>; 22

USING SELECT CLAUSES Select distinct office. Code Select office. Code from employees; From employees; 23

USING SELECT CLAUSES Select employee. Number, first. Name, last. Name From employees Limit 5; 24

USING SELECT CLAUSES Select employee. Number emp. NO, first. Name "First Name", last. Name 'Last Name' from employees; 25

USING CREATE TALBE FROM SELECT CLAUSES desc employees; 26

USING CREATE TALBE FROM SELECT CLAUSES Create table emp as Select employee. Number emp. NO, first. Name , last. Name, email, job. Title from employees; 27

USING CREATE TALBE FROM SELECT CLAUSES Select * from emp; 28

TRY THIS Using table emp , then change size of last. Name = 80 characters , size of email = 50 characters Change Barry Jones to your name in English and jobtitle is VP Marketing Tom King is no longer the employee 29