从零学习数据库mysql--表的操作

数据库的完整性

什么是数据的完整性

保证用户输入的数据保存到数据库中是正确的。

如何添加数据完整性

在创建表时给表中添加约束

完整性分类

1. 实体完整性
2. 域完整性
3. 参照完整性

实体完整性

1. 什么是实体完整性

表中的一行(一条记录)代表一个实体（entity）

2. 实体完整性的作用

标识每一行数据不重复。行级约束

3. 约束类型

1. 主键约束（primary key）
2. 唯一约束(unique)
3. 自动增长列(auto_increment)

4. 主键约束

特点：

1. 每个表中要有一个主键
2. 数据唯一，且不能为null

添加主键约束的方式

CREATE TABLE 表名(字段名1 数据类型 primary key,字段2 数据类型);
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(要设置主键的字段));
CREATE TABLE 表名(字段1 数据类型, 字段2 数据类型,primary key(主键1，主键2));

联合主键： 两个字段数据同时相同时，才违反联合主键约束。

1.先创建表

2.再去修改表，添加主键

ALTER TABLE student  ADD CONSTRAINT  PRIMARY  KEY (id);

5. 唯一约束：

特点：

1. 指定列的数据不能重复
2. 可以为空值

CREATE TABLE 表名(字段名1 数据类型 字段2 数据类型 UNIQUE);

6. 自动增长列

特点：

1. 指定列的数据自动增长
2. 即使数据删除，还是从删除的序号继续往下

CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE);

域完整性

限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格

域完整性约束：

1. 数据类型 ：
   数值类型、日期类型、字符串类型

2. 非空约束（not null）

   CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE not null);
   

3. 默认值约束(default)

CREATE TABLE 表名(字段名1 数据类型 PRIMARY KEY AUTO_INCREMENT ，字段2 数据类型 UNIQUE not null default '男');

​ 插入的时候，values当中的值直接给default

参照完整性

1. 什么是参照完整性

   是指表与表之间的一种对应关系
   通常情况下可以通过设置两表之间的主键、外键关系，或者编写两表的触发器来实现。
   有对应参照完整性的两张表格，在对他们进行数据插入、更新、删除的过程中，系统都会将被修改表格与另一张对应表格进行对照，从而阻止一些不正确的数据的操作。
   数据库的主键和外键类型一定要一致；
   两个表必须得要是InnoDB类型
   设置参照完整性后 ，外键当中的内值，必须得是主键当中的内容

2. 一个表设置当中的字段设置为主键，设置主键的为主表

   CREATE TABLE student(sid int PRIMARY key,name varchar(50) not null,sex varchar(10) default '男');
   

3. 创建表时，设置外键，设置外键的为子表

   CREATE TABLE score(
   sid INT,
   score DOUBLE,
   CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student(id));
   

   ​

表之间关系

1. 一对一
   一夫一妻
2. 一对多关系
   一个人可以拥有多辆汽车，要求查询某个人拥有的所有车辆。
   创建Person表
   
   创建Car表
   
3. 多对多关系

1. 学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
2. 一个学生可以有多个老师，而一个老师也可以有多个学生

创建老师表

创建学生表

创建学生与老师关系表

关系图

添加外键

多表操作

合并结果集

1. 什么是合并结果集

合并结果集就是把两个select语句的查询结果合并到一起

2. 合并结果集的两种方式

1. UNION
   合并时去除重复记录
2. UNION ALL
   合并时不去除重复记录

格式：
UNION:

SELECT * FROM 表1 UNION SELECT * FROM 表2；
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

创建表：

UNION：


UNION ALL：

注意事项：被合并的两个结果：列数、列类型必须相同。

多表联查：

1. 什么是连接查询

也可以叫跨表查询，需要关联多个表进行查询

2. 什么是笛卡尔集

假设集合A={a,b}，集合B={0,1,2}，
则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
可以扩展到多个集合的情况
同时查询两个表，出现的就是笛卡尔集结果

3. 查询时给表起别名
   
4. 多表联查，如何保证数据正确
   逐行判断，相等的留下，不相等的全不要
   

连接查询

1. 内连接

内连接
图示：

作用：查询两张表的共有部分
语句：

Select <select_list> from tableA A  Inner join  tableB B  on A.Key = B.Key

示例：

SELECT * from employee e  INNER JOIN department d on e.depart_id = d.id;

多表连接：
建表：

使用99连接法：

使用内联查询

2. 左连接

图示

作用： 把左边表的内容全部查出,右边表只查出满足条件的记录
语句：

Select <select_list> from tableA A Left Join  tableB B  on A.Key = B.Key

示例

SELECT * from employee e  LEFT JOIN department d on e.depart_id = d.id;		

3. 右连接

图示：

作用

把右边表的内容全部查出,左边表只查出满足条件的记录

语句

Select <select_list> from tableA A Right Join  tableB B  on A.Key = B.Key

示例

SELECT * from employee e  RIGHT JOIN department d on e.depart_id = d.id;

子查询

什么是子查询

一个select语句中包含另一个完整的select语句。 或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置

1. where后，把select查询出的结果当作另一个select的条件值
2. from后，把查询出的结果当作一个新表；

示例表

使用

查询与项羽同一个部门人员工

1. 先查出项羽所在的部门编号
   
2. 再根据编号查同一部门的员工
   
   把第1条查出来的结果当第2天语句的条件

查询工资高于程咬金的员工

1. 查出程咬金的工资
   
2. 再去根据查出的结果查询出大于该值的记录员工名称
   

工资高于30号部门所有人的员工信息

1. 先查出30号部门工资最高的那个人
   
2. 再到整个表中查询大于30号部门工资最高的那个人
   

查询工作和工资与妲己完全相同的员工信息

1. 先查出妲已的工作和工资
   
2. 根据查询结果当作条件再去查询工作和工资相同的员工
   由于是两个条件，使用 IN进行判断
   

有2个以上直接下属的员工信息

1. 对所有的上级编号进行分组
   

2. 找出大于2个的，大于2个说明有两个下属
   

3. 把上条的结果当作员工编号时行查询
   

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址