1 创建数据表
1.1 语法
1.1.1 语法形式
创建数据表的语句:CREATE TABLE,形式如下:
CREATE TABLE <表名>
(
字段名1 数据类型 [列级别约束条件][默认值],
字段名2 数据类型 [列级别约束条件][默认值],
... ...
[表级别约束条件]
);
注意:
(1)要创建的表的名称,不区分大小写,不能用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;