MySQL数据库和SQL语句一、数据库的多表设计
1. 单表数据的冗余和容错
（1）准备表单
create table emp (
id int primary key auto_increment,
ename varchar(30) not null,
depa varchar(30) not null,
starttime date not null
);（2）给表中添加数据
insert into emp(ename,depa,starttime)values('张三','技术部','2020-10-1');
insert into emp(ename,depa,starttime)values('李四','技术部','2020-10-1');
insert into emp(ename,depa,starttime)values('王五','技术部','2020-10-1');
insert into emp(ename,depa,starttime)values('赵六','产品部','2020-9-18');
insert into emp(ename,depa,starttime)values('田七','产品部','2020-9-18');
insert into emp(ename,depa,starttime)values('王八','产品部','2020-9-18');
（3）结论
通过结果的运行我们可以看到，表中存在大量冗余的数据内容，员工的部门数据信息有大量重复。那是因为员工和部门信息都是保存在同一张表中。
在真实的开发中，一个项目中的数据，一般都会保存在同一个数据库中，但是不能把所有类型的数据都保存在同一张数据表中，不同类型的数据需要保存在不同的数据表中。
那么在设计保存数据的数据表时，我们就要根据具体的数据进行分析，然后把同一类数据保存在同一张表中，不同的数据进行分表处理。
可是有时不同的数据之间会有一定的关系，所以把不同的数据保存在不同的数据表中之后，还需要在数据表中维护这些数据之间的关系。这时就要求设计表的人员，设计不同表之间的相互关系。
数据表之间的关系： 多对多关系、 一对多（多对一）、 一对一。
在表单的设计中，员工和部门本来就应该各有一张专门的表单，然后部门和员工表单产生关联即可。下面我们分别创建两张表单，一张员工表，一张部门表。
（1）准备员工表单
create table emp(
id int primary key auto_increment,
ename varchar(30) not null,
did int not null
);
insert into emp(ename,did)values('张三',1);
insert into emp(ename,did)values('李四',1);
insert into emp(ename,did)values('王五',2);
insert into emp(ename,did)values('赵六',11);（2）准备员工表单
create table depa(
id int primary key auto_increment,
dname varchar(30) not null,
starttime date not null
);
insert into depa(dname,starttime)values('技术部','2020-10-1');
insert into depa(dname,starttime)values('产品部','2020-9-18');（3）结论
上述的案例中，我们确实是希望员工表和部门表产生对应的关系，可是通过对上述表单的查询我们可以看到，当前是以部门的id主键作为关联联系时，产生了一个不存在的部门编号，代码虽然减少了冗余，但是却存在了出错的风险，而为了减少这种风险的产生，我们需要通过外键约束让员工表和部门表产生真正的关联。
2. 外键约束
一旦表和表之间产生了关系，那么在数据进行增删改查的操作时，我们就要考虑到维护多表之间数据的完整性，减少垃圾数据的产生。而如果想要实现维护多表的关系，就需要使用到外键约束。
外键：在一个表中去引用另外一张表的主键作为该表的字段，这个字段被称为外键，一旦有了外键，我们就可以理解为表和表之间产生了外键约束。
作用：减少数据的冗余，维护多表之间的数据的完整性，减少垃圾数据。
主表：主键被应用的表称为主表。
从表：存在外键的表称为从表。
（1）表已经存在添加外键语法
alter table 当前表单 add foreign key(当前表的列) references 被引用表名(引用表的列);
（2）创建表时添加外键语法
foreign key(当前表中的列表) references 被引用表名(被引用表的列名);
（3）给emp表中添加外键
alter table emp add foreign key(did) references depa(id);
当给emp员工表中添加depa部门表中的id作为了外键之后，emp员工表和depa部门表就产生了真实的关联，此时我们就可以通过emp员工表中的did字段来查询depa部门表中的信息。但是因为他们之间产生了主从关系，所以在给主从表中的数据做增删改的时候有需要特别的注意事项。
（4）给emp（从表）表中添加数据
# insert into emp values(6,'张三丰',3); did对应的主键中并没有3这个值，无法添加
insert into emp values(6,'张三丰',1); # 主键的值必须是存在的如果添加的外键的值在主表中是不存在的，则无法添加成功，要先添加主表数据，在添加从表数据。
（5）给emp（从表）表中更新数据
# update emp set did = 3 where id = 3; 从表外键更新的数据值，必须是主表存在的数据。
update emp set did = 1 where id = 3;如果更新的外键的值在主表中是不存在的，则无法更新成功，要先更新主表数据，在更新从表数据。
（6）给depa（主表）表中删除数据
delete from depa where id = 2; #主表中的2号id此时正在被从表的外键引用，无法删除，需要先删除从表中引用2号id的所有数据后才可以删除
如果从表的外键还在引用着主表的数据，则主表该数据不能被删除，要先删除从表中所有引用主表的数据后，才可以删除主表的数据。
（7）开发中的习惯
使用外键可以维护数据表之间的关系，所以外键必须是存在的。但是外键它属于一种约束，如果操作不当，就会报错，开发中是非常讨厌这一点的。因此有时候我们只设置外键的字段，但是不让表和表之间产生具体的关系，然后通过java代码进行控制，防止产生垃圾数据的风险。
3. 级联操作
级联操作指的是通过操作主表的数据，从而影响到从表中的数据。因此级联操作存在很大的风险，所以外键设置时，默认都是不级联操作。级联操作分为: 级联更新 和 级联删除。级联操作的设置都是设置在从表中。
级联更新 ：外键约束语句 on update cascade;
级联删除 ：外键约束语句 on delete caseade;
（1）给emp表中设置级联操作
alter table emp add foreign key(did) references depa(id) on update cascade on delete cascade;
添加了级联的操作之后，我们将主表中的数据删除，看对从表会有什么样的影响，语句如下。
delete from depa where id = 1;
通过程序的运行结果我们可以看到，当删除了主表中的数据之后，从表中和主表关联的所有数据都会被删除，这样的操作风险过高，一般开发中几乎不会使用。
4. 一对多关系
一对多的关系在开发中是相对比较常见的一种数据关系，比如员工和部门的关系，作者和作品的关系等。
员工和部门：一个员工只能入职到一个部门，一个部门可以有很多员工。
作者和作品：一个作者可以写多部作品，一部作品只能有一个作者。
在创建一对多的数据表时，需要在多的一方添加一的一方的主键作为多的一方的外键。
5. 多对多关系
多对多的关系在实际的项目开发中非常的常见，比如：程序员和项目的关系，顾客和商品的关系，老师和学生的关系等。
程序员和项目：一个程序员可以参与多个项目的开发，一个项目可以由多个程序员来开发。
顾客和商品：一个顾客可以购买多件商品，同一类商品，可以被多个顾客购买。
老师和学生：一个老师可以教多个学生，一个学生可以也可以被多个老师教。
当我们把数据之间的关系分析清楚之后，一般我们需要通过E-R图来展示。
一个java类可以对应一张数据表，一个Java对象，可以对应数据表中的一条数据，而Java中类的属性，可以对应表中的字段（列）。
而在E-R图中：
一张表，可以称为一个实体，使用矩形表示
每个实体的属性（字段，表的列），使用椭圆表示。
表和表之间的关系，使用菱形表示。
多对多案例实现
（1）创建程序员表
create table coder (
id int primary key auto_increment,
salary double,
name varchar(30)
);（2）创建项目表
create table project(
id int primary key auto_increment,
name varchar(100)
);（3）创建第三张关系表，维护程序员和项目之间的关系
create table coder_project(c_id int , p_id int);
我们在创建第三张关系表时，表中的每一列，都是在引用其他表中的列，这时我们需要对第三张表中的列进行相应的约束，当前第三张表中的列，由于都是引用其他表中的列，我们把第三张表中的这些列称为引用其他的外键约束。
（4）添加外键约束
alter table coder_project add foreign key(c_id) references coder(id);
alter table coder_project add foreign key(p_id) references project(id);
6. 一对一关系
一对一关系表在实际开发中使用的并不多，其中也是2个实体，其中A实体中的数据只能对应B实体中的一个数据，同时B实体中的数据也只能对应A实体中的一个数据。例如：人和身份证对应关系。
而一对一在建表的时候，可以在任意一方的表中添加另外一方的主键作为外键即可。
7. 学生成绩表设计案例
7.1 需求：设计学生成绩管理系统数据表
1、每个教师可以教多门课程
2、每个课程由一个老师负责
3、每门课程可以由多个学生选修
4、每个学生可以选修多门课程
5、学生选修课程要有成绩
7.2 E-R图设计
当我们拿到一个需求之后，首先应该分析这个需求中对应几个E-R图中的实体对象。
分析需求中存在的实体：学生、课程、老师。
当分析清楚具体的实体之后，那么就要考虑实体和实体之间的关系问题：
学生和课程之间：多对多关系，一个学生可以选修多门课程，一门课程可以被多个学生选修。
课程和老师之间：一对多，一个老师可以代多门课程，一门课程只能由一个老师代课。
在整个表单的设计中，课程的成绩是需要好好处理的分析的
首先：成绩不可以放在学生表中，因为学生是选修课程的，如果有某些课程学生没有选修，那么生成对应的学生数据时，对于没有选修的课程成绩就是无效且冗余的。
其次：成绩也不可以放在课程表中，同样的，课程表中的每科成绩都需要对应到一个学生数据，但是有些课程是学生没有选修的，依然会造成数据的冗余。
7.3 学生选课管理系统的表创建
（1）创建学生表
create table student(
id int primary key auto_increment,
name varchar(40)
);（2）创建教师表
create table teacher(
id int primary key auto_increment,
name varchar(40)
);（3）创建课程表
create table course(
id int primary key auto_increment,
name varchar(50),
#需要在课程表中添加教师的主键作为当前表的外键
t_id int,
#使用外键约束
foreign key(t_id) references teacher(id)
);（4）创建第三张关系表，维护学生和课程之间的关系
create table student_course(
s_id int,
c_id int,
score double,
#添加外键约束
foreign key(s_id) references student(id),
foreign key(c_id) references course(id)
);8. 数据库设计三大范式
设计范式就是指的设计原则，要求设计表的时候遵守三大范式。
第一范式
要求表的每个字段必须是不可分割的独立单元。
第二范式
在第一范式的基础上，要求每张表只表达一个意思，表的每个字段和表的主键有依赖。
第三范式
在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系，这里将的就是外键的使用。
二、多表查询
一个项目中肯定会有多张数据表，而数据表之间会存在各种各样的关系。这时我们需要的数据，可能不会直接通过一张表全部获取到，这时就需要同时查询多张数据表，得到最后想要的数据。
A表保存水果的名称
create table A(
A_id int primary key auto_increment,
A_name varchar(20) not null
);
insert into A values(1,'苹果');
insert into A values(2,'橘子');
insert into A values(3,'香蕉');B表保存水果的价格
create table B(
B_id int primary key auto_increment,
B_price double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);1. 笛卡尔积介绍
下面我们分别来查询一下A,B表中的数据。
select * from a; 查看A表的数据。
select * from b; 查看B表的数据。
select * from a,b; 联合查看A,B表中的数据信息。
笛卡尔积是指在数学中，两个集合A和B的笛卡尓积，又称直积，表示为A × B，第一个对象是A的成员而第二个对象是B的所有可能有序对的其中一个成员。
笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
多表查询中的问题：
笛卡尔积问题：笛卡儿积就是把两个（多个）表的结果集相乘，A表中的每一条数据与B表中的每一条数据匹配并呈现，数量级就是两表的成绩，属性为列相加。
笛卡尔积的数据，对程序是没有意义的，我们需要对笛卡尔积中的数据再次进行过滤。对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。
2. 内连接查询
语法一
select * from 表名1,表名2 where 表名1.列名 = 表名2.列名;
练习一：查询所有水果的价格
select * from a,b where a.a_id = b.b_id;
语法二
select * from 表名 inner join 表名 on 条件;
练习二：查询所有水果的价格
select * from a inner join b on a.a_id = b.b_id;
3. 外连接查询
外链接分为：左外连接、右外连接、全（外）连接、自连接。
（1）左外连接
用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。
语法：select * from 表1 left outer join 表2 on 条件;
练习：查询所有水果的价格
select * from a left outer join b on a.a_id = b_id;
（2）右外连接
用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示
语法：select * from 表1 right outer join 表2 on 条件;
练习：查询所有水果的价格
select * from a right outer join b on a.a_id = b.b_id;
（3）全外连接
左外连接和右外连接的结果合并，但会去掉重复的记录。
语法：select * from 表1 full outer join 表2 on 条件; # 但是mysql数据库不支持此语法。
#在sql语句全连接，其实就是左外链接和右外连接之和，并且使用union去掉重复的数据。
select * from 表1 left outer join 表2 on 条件
union all
select * from 表1 right outer join 表2 on 条件;
练习：查询所有水果的价格
select * from a left outer join b on a.a_id = b.b_id
union
select * from a right outer join b on a.a_id = b.b_id;
4. SQL关联子查询
子查询：把一个SQL语句的查询结果作为另外一个SQL语句查询的参数存在。
例如：查询价格最贵的水果名称 --- >数据表A水果表，数据表B价格表。
分析
（1）B表保存的价格，在B表中查询出最高价格 b_price
select max(b_price) from b;
（2）通过最高的价格，找对应的 b_id
select b_id from b where b_price = (select max(b_price) from b);
（3）通过第二步查询到的最贵水果价格的id，到水果表中找对应的水果名称
select * from a where a_id = (select b_id from b where b_price = (select max(b_price) from b));
三、索引
1. 索引概念
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引的作用：提高查询速度。索引的类型：单列索引和组合索引
2. 索引语法
（1）建表的时候创建索引
index(字段名称) # 普通索引 字段的值可以重复
unique index(字段名称) #唯一索引 这个字段的值不能重复
（2）准备数据
create table person(
id int primary key auto_increment,
name varchar(30) not null,
age int,
index(name)
);
insert into person values(null,'熊大',3);
insert into person values(null,'熊二',2);
insert into person values(null,'吉吉国王',8);
insert into person values(null,'关头强',31);
insert into person values(null,'老板',39);（3）演示说明
# 如果此时没有给name字段设置索引，那么会从表中的每一个字段的每一个数据中进行查找效率低
# 如果此给name字段设置索引，那么只会从name字段中进行查找
select * from person where name like '熊%';
select * from person where age > 2;#只有根据进行查询的时候，索引才会生效
（4）组合索引语法
index(字段1,字段2...)
组合索引的使用：组合索引存在最左原则，左侧的字段1如果如果在查询的时候使用则是有效索引，如果左侧的字段1没有使用，只是使用了字段2，那么索引也不会效果。
select * from person where name like '熊%';
select * from person where sname like '熊%' and age = 2;
（5）表创建后创建索引
#直接创建索引
create index 字段名 on 表名(字段名);只有根据索引字段进行查询的时候，索引才会生效
索引不是越多越好，如果所有的字段都创建索引，等于没有索引
索引一般在大数据量下才会发挥效果，如果数据量很小，可能有索引反而更慢
