MySQL知识（二）——数据表的基本操作

1 创建数据表

1.1 语法

1.1.1 语法形式

　　创建数据表的语句：CREATE TABLE，形式如下：

CREATE TABLE <表名>
（
　　字段名1　数据类型　［列级别约束条件］［默认值］，
　　字段名2　数据类型　［列级别约束条件］［默认值］，
　　... ...
　　［表级别约束条件］
）;

注意：
　　（１）要创建的表的名称，不区分大小写，不能用SQL语言中的关键字，如DROP、ALTER等。
　　（2）数据表中的每一列的名称和数据类型，如果要创建多列，要用逗号隔开。

1.1.2 例子

创建一个students表

字段名称	数据类型	备注
id	INT(11)	学生编号
name	VARCHAR(25)	学生名称
gender	VARCHAR(11)	学生性别
classId	INT(11)	学生所在班级

　　（1）选择数据库

　　USE test;

　　（2）创建表

　　CREATE TABLE students
(
　　id  INT(11),
　　name  VARCHAR(25),
　　gender  VARCHAR(11),
　　classId  INT(11)
);

1.2 约束

　　1.约束保证数据的完整性和一致性。
　　2.约束分为表级约束和列级约束。
　　3.约束类型包括：NOT NULL（非空约束）、PRIMARY KEY（主键约束）、UNIQUE KEY（默认约束）、DEFAULT（默认约束）、FOREIGN KEY（外键约束）

1.2.1 使用主键约束

　　主键，是表中一列或多列的组合。
　　主键约束要求主键列的数据唯一，并且不允许为空。
　　主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询速度。

1.2.1.1 单字段主键

（1）定义列的同时指定主键

字段名  数据类型  PRIMARY KEY [默认值]

例如：

　　CREATE TABLE students2
(
　　id  INT(11) PRIMARY KEY,
　　name  VARCHAR(25),
　　gender  VARCHAR(11),
　　classId  INT(11)
);

（2）定义完所有列之后指定主键

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

例如：

　　CREATE TABLE students2
(
　　id  INT(11),
　　name  VARCHAR(25),
　　gender  VARCHAR(11),
　　classId  INT(11),
　　PRIMARY KEY(id)
);

1.2.1.2 多字段联合主键

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

例如：

　　CREATE TABLE students2
(
　　id  INT(11),
　　name  VARCHAR(25),
　　gender  VARCHAR(11),
　　classId  INT(11),
　　PRIMARY KEY(name,classId)
);

1.2.2 使用外键约束

　　外键用来在两个表的数据之间建立链接，可以是一列或多列。
　　一个表可以有一个或多个外键。
　　外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中的主键的某个值。
　　外键的作用是保证数据引用的完整性，定义外键后，不允许删除在另外一个表中具有关联关系的行。
　　主表（父表）：主键所在的表
　　从表（子表）：外键所在的表
　　语法规则：

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

例子：
　　创建一个教室表（class）

字段名称	数据类型	备注
id	INT(11)	课室编号
location	VARCHAR(50)	课室位置

　　CREATE TABLE class
(
　　id  INT(11) PRIMARY KEY,
　　location  VARCHAR(50)
);

　　定义学生表，包含外键约束：

　　CREATE TABLE students3
(
　　id  INT(11) PRIMARY KEY,
　　name  VARCHAR(25),
　　gender  VARCHAR(11),
　　classId  INT(11),
　　CONSTRAINT fk_stu_class FOREIGN KEY (classId) REFERENCES class(id)
);

1.2.3 使用非空约束

　　非空约束（Not Null Constraint）指字段的值不能为空。
　　（1）语法规则

字段名  数据类型  not null

　　（2）例子

　　CREATE TABLE students4
(
　　id  INT(11) PRIMARY KEY,
　　name  VARCHAR(25) NOT NULL,
　　gender  VARCHAR(11),
　　classId  INT(11),
　　CONSTRAINT fk_stu_class2 FOREIGN KEY (classId) REFERENCES class(id)
);

1.2.4 使用唯一性约束

　　唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。
　　（1）定义完列之后直接指定唯一约束

字段名  数据类型  UNIQUE

　　例子：

　　CREATE TABLE students5
(
　　id  INT(11) PRIMARY KEY,
　　name  VARCHAR(25) NOT NULL,
　　gender  VARCHAR(11),
　　classId  INT(11) UNIQUE,
　　CONSTRAINT fk_stu_class3 FOREIGN KEY (classId) REFERENCES class(id)
);

　　（2）定义完所有列之后指定唯一约束

[CONSTRAINT <约束名>] UNIQUE(<字段名>)

　　例子：

　　CREATE TABLE students6
(
　　id  INT(11) PRIMARY KEY,
　　name  VARCHAR(25) NOT NULL,
　　gender  VARCHAR(11),
　　classId  INT(11),
　　CONSTRAINT sth UNIQUE(name)
);

　　（3）UNIQUE和PRIMARY KEY的区别
　　一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY声明；
　　声明为PRIMARY KEY的列布允许有空值，但是声明为UNIQUE的字段允许空值（NULL）的存在。

1.2.5 使用默认值约束

　　默认约束（Default Constraint）指定某列的默认值。
　　当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
　　语法：

字段名 数据类型 DEFAULT 默认值

　　例子：

　　CREATE TABLE students7
(
　　id  INT(11) PRIMARY KEY,
　　name  VARCHAR(25) NOT NULL,
　　gender  VARCHAR(11),
　　classId  INT(11) DEFAULT 1111,
　　CONSTRAINT sth UNIQUE(name)
);

1.3 属性值自动增加

　　每次插入新记录时，系统自动生成字段的主键值。
　　在MySQL中，AUTO_INCREMENT的初始值为1，每新增一条记录，字段值自动加1.
　　一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。
　　语法：

字段名 数据类型 AUTO_INCREMENT

　　例子：

　　CREATE TABLE students8
(
　　id  INT(11) PRIMARY KEY AUTO_INCREMENT,
　　name  VARCHAR(25) NOT NULL,
　　gender  VARCHAR(11),
　　classId  INT(11) DEFAULT 1111,
　　CONSTRAINT sth UNIQUE(name)
);

2 查看数据表结构

2.1 查看表基本结构语句 DESCRIBE

DESCRIBE 表名; 
或者
DESC 表名;
或者
show columns from 表名;

• NULL：表示该列是否可以存储NULL值
• Key：表示该列是否已编制索引。PRI表示该列是主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有则显示多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

2.2 查看表详细结构语句SHOW CREATE TABLE

SHOW CREATE TABLE <表名>;

3 修改数据表

MySQL使用ALTER TABLE语句修改表。

3.1 修改表名

　　语法：

ALTER TABLE <旧表名> RENAME <新表名>

　　例如：

ALTER TABLE students1 RENAME students99

3.2 修改字段的数据类型

　　语法：

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

　　其中“表名”指要修改数据类型的字段所在表的名称，“字段名”指需要修改的字段，“新数据类型”指修改后字段的新数据类型
　　例如：

ALTER TABLE students MODIFY name VARCHAR(30);

3.3 修改字段名

　　语法：

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

　　例如：

ALTER TABLE students1 CHANGE name na VARCHAR(20);

3.4 添加字段

　　语法：

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

　　说明：“FIRST”，将新添加的字段设置为表的第一个字段；“AFTER”将新添加的字段添加到指定的“已存在字段名”后面。

3.4.1 添加无完整性约束条件的字段

　　例如：

ALTER TABLE students ADD score INT(10);

3.4.2 添加有完整性约束条件的字段

　　例如：

ALTER TABLE students ADD score INT(10) not null;

3.4.3 在表的第一列添加一个字段

　　例如：

ALTER TABLE students ADD score INT(10) FIRST;

3.4.4 在表的指定列添加一个字段

　　例如：

ALTER TABLE students ADD score INT(10) AFTER name;

3.5 删除字段

　　语法：

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

　　例如：

ALTER TABLE students1 DROP name;

3.6 修改字段的排列位置

　　语法：

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

3.6.1 把字段修改为表的第一个字段

　　例如：

ALTER TABLE students MODIFY name VARCHAR(12) FIRST;

3.6.2 修改字段到表的指定列之后

　　例如：

ALTER TABLE students MODIFY name VARCHAR(12) AFTER classId;

3.7 更改表的存储引擎

　　语法：

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

　　例如：

ALTER TABLE students1 ENGINE=MyISAM;

3.8 删除表的外键约束

　　语法：

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

　　“外键约束名”，指在定义表时CONSTRAINT关键字后面的参数
　　例如：

ALTER TABLE students3 DROP FOREIGN KEY fk_stu_class;

4 删除数据表

4.1 删除没有被关联的表

　　语法：

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

　　如果删除的数据表不存在，则MySQL会提示一条错误信息；加上参数IF EXISTS后，如果表不存在，SQL语句可以顺利执行，但是会发出警告。
　　例如：

DROP TABLE IF EXISTS students4;

4.2 删除被其他表关联的主表

　　存在外键约束时，主表不能被直接删除。
　　①先接触关联子表的外键约束

ALTER TABLE students3 DROP FOREIGN KEY fk_stu_class;

　　②将原来的父表class删除

DROP TABLE class;