MySQL表、数据类型、约束

一、MySQL常用数据类型

• 任何一张表包含：表结构、表数据、索引

二、create 创建表

• 语法：

create table 表名(字段名1 数据类型(长度) 约束, 字段名2 数据类型(长度) 约束, ...);

drop table if exists course; 
create table course(
	cno varchar(10) COMMENT '课程号',
	cname varchar(20) COMMENT '课程名',
	credit int(11) COMMENT '学分',
	semester int(11) COMMENT '开课学期',
	PRIMARY KEY (cno)
)

三、INSERT 向表中添加数据

• 语法：

insert into 表名 (字段名...) values(值1,值2, ...)

INSERT INTO course (cno,cname,credit,semester) VALUES ('C001', '高等数学', 4, 1);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C002', '大学英语', 3, 1);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C003', '大学英语', 3, 2);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C004', '计算机文化学', 2, 2);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C005', 'Java', 2, 3);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C006', '数据库基础', 4, 5);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C007', '数据结构', 4, 4);
INSERT INTO course (cno,cname,credit,semester) VALUES ('C008', '计算机网络', 4, 4);

四、DDL 添加/删除/修改表结构

• 采用alter table来增加/删除/修改表结构，即表结构发生改变，但不影响表中的数据

1. add 添加字段

alter table course add tel varchar(40);

2. modify 修改字段

alter table course modify cname varchar(50);

3. drop 删除字段

alter table course drop tel;

五、DML 添加/删除/修改

• insert，delete，update，用于添加、删除、修改，不改变表结构

1. insert 添加表数据

语法：

insert into 表名 (字段名...) values(值1,值2, ...)

INSERT INTO course (cno,cname,credit,semester) VALUES ('C001', '高等数学', 4, 1);

字段的数量和values值的数量相同，顺序相同，数据类型相同
字段名可以省略，但不建议省略，因为当数据库表中的字段位置发生改变的时候会影响到insert语句

2. update 修改表数据

语法：

update 表名 set 字段名1=字段值1,字段名2=字段值2, ... where 条件;

没有 where 条件则整张表数据全部更新

UPDATE course SET cname = '外语' WHERE cno = 'C003';

3. delete 删除表数据

语法：

delete from 表名 where 条件;

没有 where 条件则全部删除

delete from course where cname = '外语';

4. truncate 删除大表数据

语法：

truncate table 表名 where 条件;

• 表被截断，只删数据，不可回滚 ，永久丢失
• truncate删除时，不删除表结构，但是删除的表中的数据是没办法恢复的
• drop删除时，删除表结构，且删除速度更快，但是可恢复数据

六、约束

• 使用约束保证表中数据的合法性、有效性、完整性

1. 非空约束

• not null
  针对某个字段设置其值不为null，只有列级约束

2. 唯一约束

• unique
  可以使某个字段的值不能重复，可以为null，但只能有一个值为null
  注意：一个表可以有多个唯一约束字段。
• 唯一约束可以在单独字段名后面加（列级约束），也可以在两个或多个字段名后面加（表级约束），即两个或多个字段联合起来不能重复，不能单看任一个字段（列级约束也可以表示成表级约束）

2.1 NULL讨论

• 唯一约束中多个null不是重复，因为null不是值，在数据库中，null不是一个值，代表什么也没有，为空，即空不是一个值，不能用等号衡量，表示是否为空必须使用is null 或 is not null
• ifnull() 空处理函数（单行处理函数），即可能为null的数据，被当作什么处理

3. 主键约束

• primary key

PRIMARY KEY (字段名)

主键列数据唯一且非空

• 每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的
• 所谓的一张表多个主键，称之为联合主键，但联合主键只能有一个主键自增auto_increment约束，自增类型必须是int类型和int类型相关的类型，不能是字符串类型、日期类型等等
  Oracle中的自增机制：序列（sequence）

3.1 主键的作用

• 第一范式要求任何一张表中都应该有主键，主键值是这行记录在这张表当中的唯一标识，可以保证数据的唯一性和完整性，同时通过主键检索表能够增加检索速度

3.2 主键的分类

3.2.1 根据主键字段的字段数量划分

• 单一主键（推荐使用）
• 复合主键（多个字段联合起来添加一个主键约束）

3.2.2 根据主键的性质划分

• 自然主键
• 业务主键（主键值和系统的业务挂钩）（不推荐）

4. 外键约束

• foreign key

foreign key(子表字段名) references 父表名(父表字段名)

一个表的外键如果引用的字段是主键，则外键值必须等于主键值；如果引用的字段不是主键，则至少应该具有unique唯一约束，所以外键也可以为null，但是外键为null的数据则与相关联的表就没有关系了，愧为外键的名称，所以一般又不采用外键为null的情况

• 外键主要用来维护表之间的关系，保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键
• 引用的表叫做子表，被引用的表叫做父表

1. 顺序要求

• 创建时先创建父表，再创建子表
• 删除时，先删除子表，再删除父表
• 添加数据的时候，先添加父表，再添加子表
• 删除数据的时候，先删除子表，再删除父表

5. 检查约束

• check
  Oracle数据库中有check约束，目前MySQL不支持

七、约束的指定时机

1. 建表的同时对相应的数据列指定约束
2. 建完表之后再创建，以修改表的方式增加约束，如add，modify