在创建数据表时,需要USE <database_name>;来选中数据库,

mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)

mysql> use test_db;
Database changed

创建数据表语法规则:

CREATE TABLE <表名>
(
字段名1 数据类型 [列级别的约束条件][默认值],
字段名2 数据类型 [列级别的约束条件][默认值],
......
[表级别的约束规则]
);

Example:

mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)

mysql> use test_db;
Database changed
mysql> create table tb_emp1
-> (
-> id int(11),
-> name varchar(25),
-> deptId int(11),
-> salary float
-> );
Query OK, 0 rows affected (0.13 sec)

查看数据表是否创建成功:

SHOW TABLES;

Example:

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
+-------------------+
1 row in set (0.00 sec)

单字段主键,在定义表的时候同时指定主键:

字段名 数据类型 PRIMARY KEY

如下定义数据表tb_emp2,其主键为id,

mysql> create table tb_emp2
    -> (
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float
    -> );
Query OK, 0 rows affected (0.11 sec)

或者在定义完之后在指定主键,

[CONSTRAIN <约束名>] PRIMARY KEY [字段名]

mysql> create table tb_emp3
    -> (
    -> id int(11),
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.11 sec)

多字段联合主键,

PRIMARY KEY[字段1,字段2,...,字段n]

Example:

mysql> create table tb_emp4
    -> (
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> primary key(name,deptId)
    -> );
Query OK, 0 rows affected (0.09 sec)

创建外键约束语法:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1[,字段名2,...] REFERENCES <主键名> 主键列1[,主键列2,...]

定义数据表tb_dept1,tb_emp5,并在tb_emp5表上的deptId创建外键约束到tb_dept1的id。

mysql> create table tb_dept1
    -> (
    -> id int(11) primary key,
    -> name varchar(22) not null,
    -> location varchar(50)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> create table tb_emp5
    -> (
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.09 sec)

非空约束:

字段名 数据类型 NOT NULL

Example:

mysql> create table tb_emp6
    -> (
    -> id int(11) primary key,
    -> name varchar(25) not null
    -> );
Query OK, 0 rows affected (0.14 sec)

唯一性约束:

字段名 数据类型 UNIQUE

Example:

mysql> create table tb_dept2
    -> (
    -> id int(11) primary key,
    -> name varchar(22) unique,
    -> location varchar(50)
    -> );
Query OK, 0 rows affected (0.11 sec)

默认约束:

字段名 数据类型 DEFAULT 默认值

Example:

mysql> create table tb_emp7
    -> (
    -> id int(11) primary key,
    -> name varchar(25) not null,
    -> deptId int(11) default 1111
    -> );
Query OK, 0 rows affected (0.08 sec)

设置属性值自动增加

字段名 数据类型 AUTO_INCREMENT

Example:

mysql> create table tb_emp8
    -> (
    -> id int(11) primary key auto_increment
    -> );
Query OK, 0 rows affected (0.09 sec)

查看表的基本结构:

DESCRIBE/DESC 表名;

Example:

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

查看表的详细结构:

SHOW CREATE TABLE <表名\G>;

Example:

mysql> show create table tb_emp1\G;
*************************** 1. row ***************************
    Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

修改表名:

ALTER TABLE <旧表名> RANAME [TO] <新表名>;

Example:

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_dept2          |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp7           |
| tb_emp8           |
+-------------------+
10 rows in set (0.00 sec)

mysql> alter table tb_dept1 rename tb_department1;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_department1    |
| tb_dept2          |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp7           |
| tb_emp8           |
+-------------------+
10 rows in set (0.00 sec)

修改字段的数据类型:

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

Example:

mysql> desc tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> alter table tb_dept2 modify name varchar(30);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段名:

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

Example:

mysql> desc tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table tb_dept2 change location loc varchar(50);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  | UNI | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

添加字段:

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件][FIRST|AFTER 已存在字段名];

Example:

mysql> alter table tb_dept2 add managerId int(10);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  | UNI | NULL    |       |
| loc       | varchar(50) | YES  |     | NULL    |       |
| managerId | int(10)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> alter table tb_dept2 add manager varchar(11) not null after name;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  | UNI | NULL    |       |
| manager   | varchar(11) | NO   |     | NULL    |       |
| loc       | varchar(50) | YES  |     | NULL    |       |
| managerId | int(10)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除字段:

ALTER TABLE <表名> DROP <字段名>;

Example:

mysql> alter table tb_dept2 drop manager;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  | UNI | NULL    |       |
| loc       | varchar(50) | YES  |     | NULL    |       |
| managerId | int(10)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

修改字段排列位置:

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

Example:

mysql> alter table tb_dept2 modify managerId int(10) after id;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| managerId | int(10)     | YES  |     | NULL    |       |
| name      | varchar(30) | YES  | UNI | NULL    |       |
| loc       | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

修改表的储存引擎:

ALTER TABLE <表名> ENGINE=<更改后的引擎名>

Example:

mysql> show create table tb_dept2\G;
*************************** 1. row ***************************
    Table: tb_dept2
Create Table: CREATE TABLE `tb_dept2` (
`id` int(11) NOT NULL,
`managerId` int(10) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`loc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> alter table tb_dept2 engine=myisam;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_dept2\G;
*************************** 1. row ***************************
    Table: tb_dept2
Create Table: CREATE TABLE `tb_dept2` (
`id` int(11) NOT NULL,
`managerId` int(10) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`loc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

删除表的外键约束:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

Example:

mysql> show create table tb_emp5\G;
*************************** 1. row ***************************
    Table: tb_emp5
Create Table: CREATE TABLE `tb_emp5` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept1` (`deptId`),
CONSTRAINT `fk_emp_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_department1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql> alter table tb_emp5 drop foreign key fk_emp_dept1;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_emp5\G;
*************************** 1. row ***************************
    Table: tb_emp5
Create Table: CREATE TABLE `tb_emp5` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

删除未被关联的数据表:

DROP TABLE [IF EXISTS] 表1, 表2, ..., 表n;

Example:

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_department1    |
| tb_dept2          |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp7           |
| tb_emp8           |
+-------------------+
10 rows in set (0.00 sec)

mysql> drop table tb_dept2;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_department1    |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp7           |
| tb_emp8           |
+-------------------+
9 rows in set (0.00 sec)

若存在关联关系,先删除子表的外键才能删除父表(主表)。