瞧一瞧,看一看,新鲜出炉的数据库(mysql)进阶,图文并茂,书中自有黄金屋

《蝶恋花》---- 晏殊

槛(jiàn)菊愁烟兰泣露，罗幕轻寒，燕子双飞去。明月不谙(ān)离恨苦，斜光到晓穿朱户。

昨夜西风凋碧树，独上高楼，望尽天涯路。欲寄彩笺(jiān)兼尺素，山长水阔知何处？

还不学习?

一天不学习,浑身难受,今天你学习了吗

数据完整性

1.1 数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

• 实体完整性(行完整性).
• 域完整性(列完整性).
• 引用完整性(关联表完整性).

主键约束：primary key (唯一标识,就像人的身份证号,唯一且非空)

唯一约束：unique [key] (主键唯一)

非空约束：not null (非空)

默认约束：default (设置默认值)

自动增长：auto_increment (自动增长(int))

外键约束: foreign key (属性中包含另一张表的主键)

建议这些约束应该在创建表的时候设置

多个约束条件之间使用空格间隔

示例:

1.2 实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity） (在jdbc中就是一个对象)

实体完整性的作用：标识每一行数据不重复(不是同一对象)。

约束类型： 主键约束（primary key） 唯一约束 (unique) 自动增长列 (auto_increment)

主键约束（primary key)

注：每个表中要有一个主键。

特点：数据唯一，且不能为null

唯一约束 (unique)

特点：数据不能重复。

自动增长列 (auto_increment)

sqlserver数据库 (identity-标识列)

oracle数据库(sequence-序列)

给主键添加自动增长的数值，自动增长列的类型只能是整数类型

1.3 域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较(域代表当前单元格 )

域完整性约束：数据类型 非空约束（not null） 默认值约束(default)

check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)

非空约束 :

not null (使一列内容不为空)

默认值约束:

default (使一列内容有默认值)

引用完整性:

外键约束：FOREIGN KEY (存储另一张表的主键,建立两表关系)

外键列的数据类型一定要与主键的类型一致

2.多表查询

多个表之间的关系是靠外键关联和约束的

多表约束：外键列

2.1 多表的关系

一对多与多对一关系

客户和订单，分类和商品，部门和员工.

一对多/多对一建表原则：在多方创建一个字段，字段作为外键指向一方的主键. (以学生和老师为例,在学生表创建教师列(外键)表示该学生的老师,反之则不行,因为一个老师教多名学生,没法存储对应全部学生的主键)

多对多关系

学生和课程

多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

一对一关系

实际应用不多,因为一对一可以创建成一张表.

两种建表原则：

唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.

主键对应：让一对一的双方的主键进行建立关系.

在实现类中操作数据库

一对多(以老师和学生为例):在一方创建存储多方的集合(比如在老师类中创建学生对象的集合)

多对一(以老师和学生为例):在多方创建一个存储一方的数据对象(在学生类中创建老师对象)

一对一(以丈夫和妻子为例):在数据多的一方创建另一方的数据对象(在丈夫(妻子)类中创建妻子(丈夫)对象)

注意:在jdbc中外键列(属性)是不需要创建的,但有些时候可能要用,定义出来方便一点,在有中间表的多对多关系中不需要创建中间表类.

2.2多表查询

多表查询有如下几种：

合并结果集:UNION 、 UNION ALL

连接查询

1. 内连接 [INNER] JOIN ON
2. 外连接 OUTER JOIN ON
   • 左外连接 LEFT [OUTER] JOIN
   • 右外连接 RIGHT [OUTER] JOIN
   • 全外连接（MySQL不支持）FULL JOIN
3. 自然连接 NATURAL JOIN

1. 合并结果集

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

合并结果集有两种方式： (t1,t2为表1表2)

• UNION：去除重复记录，例如：

 SELECT  FROM t1 UNION SELECT   FROM t2；

• UNION ALL：不去除重复记录，例如：

SELECT  FROM t1 UNION ALL SELECT  FROM t2;

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

2.连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

t1表:丈夫表

t2表:妻子表

笛卡尔积(很明显是不符合常理的,毕竟是别人的老婆🐢):

使用主外键关系做为条件来去除无用信息 (wife表中hid存储的是husband表的主键husid,两者是等同的)

]

一：内连接

内连接，但它不是SQL标准中的查询方式，可以理解为方言！

语法：

select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 (外键列的关系 ) where..... 

等价于：

select 列名 from 表1,表2 where 表1.列名=表2.列名 and ...(其他条件) 

注:<1>表1和表2的顺序可以互换

<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。

<3>点操作符表示“的”,格式:表名.列名

<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名

示例： //查询学生表中的学生姓名和分数表中的分数 (as 后是别名即student 简称s,as可以省略)

select name,score from student as s inner join scores as c on s.studentid=c.stuid 

等价于：

 select name,score from student as s,scores as c where s.studentid=c.stuid 

三表联查： 语法： (就是建立表的关系,通过中间表的外键两两相连,即主键与外键建立等价关系)

select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 inner join 表3 on 表1或表2.列名=表3.列名 where 要求的条件

等价于:

select 列名 from 表1,表2,表3 where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名 and 要求的条件

SQL标准的内连接为：(这里e为emp的别名,as省略了)

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno; 

内连接的特点：查询结果必须满足条件。

内连接
student2 ---学生表
stuid 学员id int 主键，自增长 
stuname 学员名字 varchar(10) 
password 登录密码 varchar(10) 
birthday 出生日期 date 

subject2 ---科目表 
subjectid 科目编号 int 主键，自增长 
subjectName 科目名称 varchar(10) 

scores2 ---分数表
sid 分数主键 int 主键，自增长 
score 分数 int 
subject 科目 int 
studentid 学生编

1.显示出小明的考试成绩以及对应科目 (数据可以自己补上,这不重要)
select subjectname,score from scores2 sc,subject2 su,student2 st where sc.subject=su.subjectid and sc.studentid=st.stuid and st.stuname='小明'; 

2.显示出所有考试学生的信息
select * from scores2 sc,subject2 su,student2 st where sc.subject=su.subjectid and sc.studentid=st.stuid;

3.查询出mysql的考试信息 
select * from scores2 sc,subject2 su,student2 st where sc.subject=su.subjectid and sc.studentid=st.stuid and sujectname='mysql'; 

4.查询出考试学员的总分 每个学员的总分
-- 如果有重名要加上学号
select stuname,sum(score) from scores2,student2 where student2.stuid= scores2.studentid group by stuname;

5.查询每科的平均分 
select subjectname ,avg(score) from  scores2 sc,subject2 su where sc.subject=su.subjectid group by subjectname;

二：外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

外联查询

左外联:

select 列名 from 主表 left join 次表 on 主表.列名=次表.列名 

1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null

2.主表和次表不能随意调换位置

使用场景:一般会作为子查询的语句使用

右外联:

select 列名 from 次表 right join 主表 on 主表.列名=次表.列名 

1.左外连接

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

2.右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

外连接(还是下面那张部门和雇员表)
-- 内联查询无法展示没有员工的部门
select * from dept1 inner join emp1 on dept1.deptno=emp1.deptno;
-- left左边的是主表，主表数据全部显示，次表数据匹配显示
select * from dept1 left join emp1 on dept1.deptno=emp1.deptno;
-- 左外联主表在左边，右外联主表在右边（语法和表中都是）。
select * from emp1 right join  dept1 on dept1.deptno=emp1.deptno;

连接查询心得：

连接不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多 数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以如果不是很熟悉连接查询，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

三.自然连接

自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。

子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么 就是子查询语句了。

1.子查询出现的位置：

• where后，作为条为被查询的一条件的一部分；
• from后，作为表存在(已经筛选好的表)；

2.当子查询出现在where后作为条件时，还可以使用关键字： any 和 all .

3.子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

举个例子:雇员表emp1.

示例:

-- 一个select语句中包含另一个完整的select语句(即嵌套查询，一个语句中存在两个及以上就是子查询)
-- 出现位置：
--     1.where 
--     2.from 

select * from emp1;
-- 查询工资高于赵六的员工（实际开发或面试中不知道赵六的工资数值）
select sal from emp1 where ename='赵六';
select ename, sal from emp1 where sal>(select sal from emp1 where ename='赵六');

-- 查询与赵六同一个部门的员工
select job from emp1 where ename='赵六';
select ename from emp1 where deptno=(select deptno from emp1 where ename='赵六');

-- 工资高于30号部门所有人的员工信息
-- （高于其工资最高者即可）
select max(sal) from emp1 where deptno='30';
select * from emp1 where sal>(select max(sal) from emp1 where deptno='30');

-- 高于所有可以用all关键字
select * from emp1 where sal > all(select max(sal) from emp1 where deptno='30');

3.深入浅出(技术活)

3.1 多行新增

insert into 表名(列名) values (列值),(列值),(列值)； 

3.2 多表更新

(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件 

(2)update 表1 inner join 表2 on 表1.列名=表2.列名 set 列名=列值 where 限定条件 

示例：

update employee e,salary s set title='助工',salary=1000 where e.empid=s.empid and name='李四' 

3.3 多表删除

语法：

delete 被删除数据的表 from 删除操作中使用的表 where 限定条件 

注:多张表之间使用逗号间隔

示例： //删除人事部的信息

delete d,e,s from department d,employee e,salary s where d.depid=e.depid and s.empid=e.empid and depname='人事部' 

3.4 日期运算函数

now() 获得当前系统时间 

year(日期值) 获得日期值中的年份 

date_add(日期,interval 计算值 计算的字段); 

注：计算值大于0表示往后推日期，小于0表示往前推日期 

示例： 

date_add(now(),interval -40 year);//40年前的日期 

部门表dept1,雇员表emp1看上图:

select * from dept1;
select * from emp1;
-- 增加一行
insert into dept1(deptno,dname,loc) values(60,'a','b');

-- 多行新增
insert into dept1(deptno,dname,loc) values(30,'',''),(30,'',''),(30,'','');

-- 多表更新 把张三工作调成服务员，岗位改成服务中心部
update dept1,emp1 set dname='服务中心部',job='服务员' where emp1.deptno=dept1.deptno and ename='张三';

-- 多表删除 删除销售部的信息
delete a,b from dept1 a,emp1 b where a.deptno=b.deptno and dname='销售部';

-- 日期运算函数 year得到字段的年
select hiredate,year(hiredate) from emp1;

-- 日期增加多少秒/分/时/天/月/年(单位)
-- 正数往后负数往前
select date_add(hiredate,interval -20 day),hiredate from emp1;

-- 时间加上20小时，没有时间则为0+20
select date_add(hiredate,interval 20 hour),hiredate from emp1;
 

4.数据库优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 .

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫 描，如：

 select id from t where num is null ;

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描.

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

 select id from t where num in(1,2,3) ;

对于连续的数值，能用 between 就不要用 in 了：

  select id from t where num between 1 and 3 ;

很多时候用 exists 代替 in 是一个好的选择

整理如下:

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引.
比如不应该为了省事使用*号，开发中用哪列查哪列

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 
select id from t where num is null; 
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。 

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如： 
select id from t where num=10 or Name = 'admin' ;
可以这样查询： 
select id from t where num = 10 
union all 
select id from t where Name = 'admin' ;

5.  in 和 not in 也要慎用，否则会导致全表扫描，如： 
select id from t where num in(1,2,3) ;
对于连续的数值，能用 between 就不要用 in 了： 
select id from t where num between 1 and 3 ;
很多时候用 exists 代替 in 是一个好的选择