There are two types of MySQL instructions

1. There are two types of MySQL instructions (Data Definition Language) DDL: Create database, create table, alter table ,,, . (Data Manipulation Language) DML Replace , delete, insert, update , select ,,, .

2. Review • show databases; • create database New_database; • use new_database; • show tables; • create table <table_name> (column_namedata_type [not null] … ,)); • describe <table_name>; • INSERT INTO table_name SET col_name3=value3, …; • SELECT col_name1 FROM table_name; • SELECT * FROM table_name;

3. Create Database What are the current databases at the server? mysql> show databases; +--------------+ | Database | +--------------+ | mysql | mysql is a database (stores users’ password …) used by system. | test | +--------------+ Create a database (make a directory) whose name is MyDB mysql> create database MyDB; Select database to use mysql> use MyDB; Database changed What tables are currently stored in the MyDB database? mysql> show tables; Empty set (0.00 sec)

4. CREATE TABLE Table_Name(column_specifications) Example mysql> CREATE TABLE student -> ( -> student_ID INT UNSIGNED NOT NULL, -> name VARCHAR(20) NOT NULL, -> major VARCHAR(50), -> grade VARCHAR(5) -> ); Query OK, 0 rows affected (0.00 sec) Create Table

5. Display Table Structure mysql> show tables; +--------------------+ | Tables_in_MyDB | +--------------------+ | student | +--------------------+ 1 row in set (0.00 sec) mysql> describe student; +---------------+----------------------+------+------+----------+--------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+-------+-----+-----------+-------+ | student_ID | int(10) unsigned | | | 0 | | | name | varchar(20) | | | | | | major | varchar(50) | YES | | NULL | | | grade | varchar(5) | YES | | NULL | | +---------------+----------------------+-------+------+----------+-------+ 4 rows in set (0.00 sec)

6. use mysql;show tables;describe db;

7. Modify Table Structure • ALTER TABLE table_name Operations mysql> alter table student add primary key (student_ID); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe student; +---------------+--------------------- +-------+------+----------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+-------+------+----------+-------+ | student_ID | int(10) unsigned | | PRI | 0 | | | name | varchar(20) | | | | | | major | varchar(10) | YES | | NULL | | | grade | varchar(5) | YES | | NULL | | +---------------+----------------------+-------+------+-----------+-------+ 4 rows in set (0.00 sec)

8. Alter Alter table table_name Add column_nameVARCHAR(20) NOT NULL; Alter table table_name Change column_nameVARCHAR(20) NOT NULL;

9. Lab Exercise create database school; use school; create table students( s_namevarchar(15) not null, s_numbersvarchar(4) not null, s_teachers char(15) ); create table teachers( t_namesvarchar(15) not null, t_numbersvarchar(4) not null, t_studentsvarchar(15) not null); alter table students add primary key (s_numbers); alter table teachers add primary key (t_numbers); alter table students modify s_teachersvarchar(4) not null; alter table students add foreign key(s_teachers) references teachers(t_numbers); alter table students change s_names_namesvarchar(15) not null;

11. describe students;describe teachers;

12. alter table teachersdrop t_students;describe teachers;

13. Alter with (add) operation When we write : create table students( s_namevarchar(15) not null, s_numbersvarchar(4) not null ); alter table students add primary key (s_numbers); It’s the same as we write : create table students( s_namevarchar(15) not null, s_numbersvarchar(4) not null, primary key (s_numbers) );

14. To add into the tables

15. select * from students;select * from teachers;

16. I want a table of students and their teachers

17. select s_names, s_numbers, t_names , t_numbersfrom students, teacherswhere s_teachers = t_numbers;

18. OK, now I want a table of Kmal students

19. select s_names, s_numbersfrom studentswhere s_teachers = '21';

20. select s_names, s_numbersfrom studentswhere s_teachers ='21'order by s_names;

21. References • Dr. Hsiang-Fu Yu, National Taipei University of Education