Database Fundamentals 4 (MySQL Syntax---table)

5 Table Management

Select Database

5.1 View all tables

Mysql> Show tables; +-----------------+ | TABLES_IN_DAY15 | +-----------------+ | Student | +-----------------+ 1 row in Set (0.00 sec)

5.2 Creating a table

Mysql> CREATE TABLE Student ( -Sid int, sname varchar (20), Sage INT ); Query OK, 0 rows affected (0.01 sec)

5.3 View table structure

mysql> desc Student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Sid | Int (11) |     YES | |       NULL | | | sname | varchar (20) |     YES | |       NULL | | | Sage | Int (11) |     YES | |       NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in Set (0.01 sec)

5.4 Deleting a table

mysql> drop table student; Query OK, 0 rows affected (0.01 sec)

5.5 Modifying a table

1) Add field

Mysql> ALTER TABLE student add column Sgender varchar (2); Query OK, 0 rows affected (0.03 sec) records:0 duplicates:0 warnings:0

2) Delete field

mysql> ALTER TABLE student drop column sgender; Query OK, 0 rows affected (0.03 sec) records:0 duplicates:0 warnings:0

3) Modify the field type

mysql> ALTER TABLE student Modify column remark varchar (100); Query OK, 0 rows affected (0.07 sec) records:0 duplicates:0 warnings:0

4) Modify the field name

mysql> ALTER TABLE student change column sgender gender varchar (2); Query OK, 0 rows affected (0.03 sec) records:0 duplicates:0 warnings:0

5) Modify table name

mysql> ALTER TABLE student rename to teacher; Query OK, 0 rows affected (0.01 sec)

Database Fundamentals 4 (MySQL Syntax---table)