CS 222 Lecture 5 DATABASE MANAGEMENT SYSTEM Somchai

CS 222 Lecture 5 DATABASE MANAGEMENT SYSTEM Somchai Thangsathityangkul

SQL • IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory • Renamed Structured Query Language (SQL) • Language for database definition, manipulation, and control • International standard • • Oracle SQL Server DB 2 my. SQL 2

What do we need for my. SQL ? Wamp : www. wampserver. com/en/index. php After we install wampserver, we will have apache , php , My. SQL in our PC. 3

Install wamp server 4

Type directory to install 5

6

7

8

9

2 1 Left Click on the wamp icon 3 10

Press Enter 11

12

Create new database • Now , we can create any new database for our project. • Create databasename; • Suppose we want to create csdb. • Type command : Create database csdb; • Type command : Show databases ; This command will show all databases we have. • Type command : use csdb ; This command will change current database to csdb 13

14

1 2 Create database ; Show databases; databases 15

Use csdb; 16

17

18

19

20

21

22

23

24

25

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value. 26

Create Table • Create Table table_name ( column_name data type , ………. , constraints …. . ) ; 27

Create table mytable ( my_id int auto_increment primary key , my_fname varchar(20) not null, my_address tinytext , my_birthday date ) ; 28

Create table my_friend ( friend_id int auto_increment primary key, friend_fn varchar(20) not null, my_id int not null, foreign key ( my_id ) references mytable ( my_id ) ); 29

1 2 3 use csdb; Create table … ; Show tables; 30

Drop table command • Drop table is a command to delete a table that we do not use it anymore. • Lets try to drop table test 1 by type command : drop table test 1 ; 31

Create table with pirmary key constraint Create table test 3 ( id 1 int , id 2 int, name varchar(25), primary key( id 1, id 2) ); 32

Create table with pirmary key constraint 33

Create table with foreign key constraint Create table test 4 ( id 3 int , id 1 int , id 2 int, fname varchar(30), foreign key( id 1, id 2) references test 3( id 1, id 2 ) ); 34

Create table with foreign key constraint 35

Alter table command • Alter table is a command to modify the table definition • Alter table <table_name> add column <column_name> data type ; • Lets try to add a new column to test table; 36

• Desc test ; • Alter table test address varchar(30) ; Before add column After add column 37

• To add multiple column in one statement, use command : • Alter table_name add ( col 1 data_type, …, col. N data_type) ; • Alter table_name add col 1 data_type, …, add col. N data_type ; 38

39

Drop column • Alter table_name drop column_name ; Alter table test drop num 2 ; 40

• To drop multiple column, use command : • Alter table_name drop col 1, …, drop col. N ; 41

Modify column • To modify column, use command : • Alter table_name modify column_name data type ; • Alter table_name modify col 1 data type, …, modify col. N data type; 42

Alter table test 2 modify address varchar(30); 43

Alter table test 2 modify my_fname text, modifiy address char(30); 44