学习笔记02——MySQL（二）

一、列的约束

1、主键约束（Primary Key）

• 每一个表格内只能有一个列被设置为主键
• 主键约束通常是用来标记表格中数据是唯一存在的
• 主键约束要求当前的列是唯一存在的，不能重复，并且不能为空

添加主键约束

alter table 表名 add constraint 约束名字 primary key (列);

发现自己定义的主键名字没有用上 可以简写为

alter table 表名 add primary key (列);

//给student表中的id列添加主键约束，约束名为pk_student
alter table student add constraint pk_student primary key (id);

设置主键自增，当主键为整型时

alter table 表名 modify 列名 int(4) auto_increment;

或者下面这样设置
alter table 表名 change 原列名 原列名 数据类型 长度 auto_increment;

注意

没有做起始值的说明，主键列的值会从1开始

alter table student modify id int(4) auto_increment;
//或者下面这样设置
alter table student change id id int(4) auto_increment;

查询数据表的描述

desc 表名;

查询数据表中的键

show keys from 表名;

desc student;
show keys from student;

结果

 

 删除主键约束

alter table myclass drop primary key;

alter table student drop primary key;

注意:删除主键约束以后   不重复的特性取消了   非空特性还在，若要设置为空，需要再把该列设置为可null
alter table 表名 modify 列名 int (4) null;

alter table student modify id int(4) null;

2、唯一约束（Unique [Key]）

• 可以为某一列添加唯一约束
• 唯一约束的列值不能重复，但可以为空
• 唯一约束可以设置在多个列上

添加唯一约束

alter table 表名 add constraint 约束名 unique (列); 

约束名用不到可简写为

alter table 表名 add unique key(列名);   //约束名默认的列名

//给student表的name列设置唯一约束，约束名为uk_student
alter table student add constraint uk_student unique [key] (name);
//简写,约束名默认的列名
alter table student add unique key(name);   

删除唯一约束

alter table 表名 drop index 约束名;

//删除约束名为uk_student的唯一约束
alter table student drop index uk_student;
//没给唯一约束起名，它的约束名默认为列名
alter table student drop index name;

3、非空约束

• 在某一列添加非空约束
• 当前的值不能为null

非空约束

alter table 表名 modify 原列名 原类型 原长度 [not] null default xxx;

或者这样设置

alter table 表名 change 原列名 原列名 原类型 原长度 [not] null default xxx;

//设置student表的name列（非）空，默认值为'光颜'
alter table student modify name varchar(10) [not] null default '光颜';
//另一种设置
alter table student change name name varchar(10) [not] null default '光颜';

4、外键约束

对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

设置外键约束

alter table 表名 add constraint 外键名 foreign key 该表列名 references 被参照表的列名；

//新建分数表
create table score(
st_id int primary key auto_increment,
cscore int(11)
);
//给score表的st_id列添加外键，关联student的id
alter table score add constraint FK_ID foreign key(st_id) REFERENCES student(id);

删除表名

alter table <表名> drop foreign key <外键约束名>；

//删除score表的外键约束
alter table score drop foreign key FK_ID;

注意

通过上述语句其实已经将外键约束删掉了，但它会自动在当前表格内添加一个新的key，需要再次手动将这个生成的key删掉，外键约束才真的删除干净
alter table 表名字 drop key 约束名字;

alter table score drop key FK_ID;

5、检查约束

用来检查列在存值时，范围是否合理

检查约束

alter table 表名 add constraint 约束名 check(条件);

//检查score表的cscore值是否在[0,100]之间
alter table score add constraint ck_cscore check(cscore>=0  and cscore<=100);

二、表格之间的关系

1、一对一关系

如人与身份证的关系。一个人只有一个身份证号。一个身份证号也对应唯一一个人。这种关系用的比较少，因为一对一的两张表往往可以合并成一张表格。

两种建表原则

• 主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 unique。
• 主表的主键和从表的主键，形成主外键关系，外键也是从表的主键primary key。

2、一对多关系

如班级与学生的关系，一个班级可以有多个学生，而一个学生只能在一个班级。

建表原则

• 在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键

3、多对多关系

如学生与选课之间的关系。一个学生可以选择多门课，一门课也能被多个学生选择。这种关系需要生成一张中间表，记录两张表的对应关系。

建表原则

• 需要创建第三张表，中间表至少两个字段，这两个字段分别作为外键指向各自一方的主键。

三、表格之间的关联查询

1、广义笛卡尔积

广义笛卡尔积将两张表格或多张表格进行无条件的拼接，在拼接后的一张大表格的基础上进行where的筛选。

广义笛卡尔积

select * from 表1，表2 where 条件；

select * from student,score where student.id=score.st_id;

2、外连接

外连接

select * from A left/right [outer] join B on 条件；

 

select * from student left join score on student.id=score.st_id;

注意

• 两张表格A和B决定那张表格的数据在左边（右边）显示，先出现的表格在左边（右边)显示
• left和right来控制以哪一个表格的数据作为基准，作为基准的表格数据必须全部显示出来，非基准的表格按照on条件与之拼接找到条件的正常显示，没找到的，则用null
• outer可以省略，因为外连接要说明左右，所以出现left或者right就已经能说明是外连接了。

3、内连接 （自连接）

内连接

select * from A inner join B on 条件

select * from student inner join score on student.id=score.st_id;

 自连接

select * from A 别名 inner join A 别名 on 条件;

注意：自连接必须给表起别名

select * from student s1 inner join student s2 on s1.id=s2.id;