MySql中表的设计

MySql中表的设计

1、表的关系：

（1）一对一：例：人——>身份证
（2）一对多：例：班级——>学生
（3）多对多：例：选课关系

2、三大范式：

（1）第一范式：确保每列数据保持原子性，所有字段不可分解。
（2）第二范式：一个表中所有的数据都必须和主键有关。
（3）第三范式：确保每列数据都和主键直接相关，而非间接相关。

3、创建表

（1）首先进行清库操作：

（2）
创建一个班级表
create table class(id int primary key auto_increment,
classname varchar(250) not null);
创建一个学生表（一定要注意email用的是反单引号（1的左边键）java中键值用（‘）单引号，列名（`）反单引号。）

create table student(id int primary key auto_increment,
sn varchar(50) not null,
username varchar(250) not null,
email varchar(250),
class_id int not null,
foreign key(class_id) references class(id));

创建一个课程表
create table course (id int primary key auto_increment,
name varchar(250) not null);

创建学生课程中间成绩表
create table course_score(id int primary key auto_increment,
student_id int not null,
course_id int not null,score
decimal(4,1),
foreign key (student_id) references student(id),
foreign key (course_id) references course(id));

（3）添加测试数据
班级表：
insert into class(id,classname) values(1,‘Java班级’),(2,‘C++班级’);
学生表：
insert into course(id,name) values(1,‘计算机’),(2,‘英语’);
课程表：
insert into student(id,sn,username,email,class_id) values(1,‘CN001’,‘张三’,‘zhangsan@qq.com’,1),(2,‘CN002’,‘李四’,‘lisi@qq.com’,2),(3,‘CN003’,‘王五’,‘wangwu@qq.com’,1);
中间成绩表：
insert into course_score(id,score,student_id,course_id) values(1,90,1,1),(2,59,1,2),(3,65,2,1),(4,NULL,2,2);

4、插入方法（一个表中插入另一个表的数据）

（1）创建一个新表（例如学生成绩表）
create table stu_score(id int primary key auto_increment,
stu_name varchar(250),
score decimal(4,1));

（2）进行插入（insert into ----select----）
insert into stu_score(stu_name) select username from student where class_id is not null;

5、聚合查询

聚合函数

1、count 函数（总数统计）
（1）查询一张表中的n条数据，可查询null和非null的数据总和

（2）可查所有数据（包括null和非null）

（3）统计当前字段中非null的数量

2、sum 函数（总和统计）
（1）select sum（字段名）from 表名；（若有null，当0加了）
（2）select sum（字段名）from 表名 where 条件;（新建了一个表sc1）

3、avg 函数
（1）select avg (字段名) from 表名；

（2）select avg (chinese + math+ english ) from sc1;

4、max 函数
select max(chinese) from sc1;

5、min 函数
select min(chinese) from sc1;

ifnull 函数

可以接受两个参数（A，B）若A不为空，返回A，若A为空，返回B；
（1）select ifnull(1,0); return 1;
（2）select ifnull(’ ',0); return ’ ';
（3）select ifnull(null,‘hello’); return hello;
应用：解决表达式中求和有关null的问题

对比图：

“group by”子句 和 having（having 配合group by 使用）

1、使用group by 进行分组查询时，select指定的字段必须是“分组依据字段”，其他字段若想出现在select 中则必须包含在聚合函数中。
语法：
select 字段1,sum(字段2),— from table group by 字段1,字段3;
测试案例：
给定一张工资表，查询每个角色的最高工资、最低工资和平均工资。
select role,max(salary),min(salary),avg(salary) from emp group by role;

2、having：
group by子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用where语句，而是要使用having
eg：显示平均薪资低于5000的角色和他的工资
select role,max(salary),min(salary),avg(salary) from emp group by role

6、联合查询

笛卡尔积：两张表的所有数据进行连接（例如有两张表A和B，表A有两条数据，表B有三条数据，则表A×表B(笛卡尔积)就是6条数据）

1、内连接

1、语法：select * from table_name1 [inner | cross] join table_name2 [on 过滤条件][where 条件] （注意：所有“[ ]”中的内容都是可以省略的）
2、例：查询张三的成绩
分析：
student表中（where name=‘张三’），
course_score表中（student_id = ‘对应张三(student表中)的id’）；
步骤：
（1）求笛卡尔积
select s.,cs. from student s join course_score cs;

（2）去掉笛卡尔积中的无效数据
第1步：select s.,cs. from student s join course_score cs on s.id=cs.student_id;

第二步： select s.,cs. from student s join（或者可以为inner join或者cross） course_score cs on s.id=cs.student_id where s.username =‘张三’;

或者不支持on:
select s.,cs. from student s join course_score cs on s.id=cs.student_id where s,id = sc.student and s.username =‘张三’;
3、练习题：
（1）查询每个人的总成绩和个人信息
分析：
每个人：分组查询
总成绩：course_score 、行数据使用聚合函数，列数据使用表达式
个人信息：student
select s.username, sum(cs.score) from student s join course_score cs on s.id = cs.student_id group by s.id;

（2）查询每个人的成绩和科目名和个人信息（三表联合查询）
分析：
每个人成绩：course_score
科目名：course
个人信息：student
补充： 多表联合查询的语法：select * from t1 join t2 [on 条件] join t3[on 条件]
select s.username,s.sn,s.email,c.name,cs.score from course_score cs join course c on cs.course_id = c.id join student s on s.id = cs.student_id;

（3） 查询学生的成绩及个人信息，根据相同的科目显示在一起，并按照成绩进行降序，并将null替换为0。
分析：
学生的成绩：course_score
个人信息：student
相同的科目显示在一起：course、order by course.name
成绩进行降序：course_score、 score desc
将null替换为0：ifnull(score,0)
select s.username,s.sn,s.email,c.name,cs.score,ifnull(cs.score,0) as score from course_score cs join student s on s.id = cs.student_id join course c on c.id = cs.course_id order by c.name,cs.score desc;

2、外连接

1、左连接
如图所示：表1 完全显示
语法：select 字段名 from 表1（主表） left join 表2 on 连接条件；

例如：select * from student s left join course_score cs on s.id = cs.student_id;

2、右连接
如图所示：表2完全显示
语法：select 字段名 from 表1 right join 表2 （主表）on 连接条件；

select * from student s right join course_score cs on s.id = cs.student_id;

3、练习题
查询所有人的信息和课程名和分数（三表联合：student、course、course_score）
select s.username,s.sn,s.email,c.name,cs.score from student s left join course_score cs on s.id = cs.student_id left join course c on c.id = cs.course_id;

4、on 和 where 的区别
（1）内连接on可以省略，而外连接不可以（若求笛卡尔积加on 1= 1（没啥意义-true））；
（2）on在内连接和外连接的执行效果不一样；
（3）在外连接中on和where不一样：在外连接查询时，若有多个查询条件，正确写法是将查询条件的表达式全部写在where中，而不是on中，在on中一般只写一个笛卡尔积无效数据的过滤条件。

3、自连接（同一张表连接自身（多表连接））

1、语法：
select * from table_name as t1,table_name as t2 where t1.id = t2.id[…];
2、练习题
查询一个人的英语成绩>计算机成绩的数据
分析：
（1）需要查询course表和course_score表
（2）进行自查询
步骤：
（1）现根据科目名称查询科目id（因课程表中只有id，name两个字段）
select id ,name from course where name =‘英语’ or name = ‘计算机’;

（2）自查询（笛卡尔积）
select * from course_score cs1,course_score cs2;

（3）去掉笛卡尔积中无意义数据
select cs1.score ‘英语成绩’, cs2.score ‘计算机成绩’ from course_score cs1,course_score cs2 where cs1.student_id = cs2.student_id;

（4）设置where条件，表1只查询英语成绩，表2只查询计算机成绩
select cs1.score ‘英语成绩’,cs2.score ‘计算机成绩’ from course_score cs1,course_score cs2 where cs1.student_id = cs2.student_id and cs1.course_id = 2 or cs2.course_id = 1;

（5）设置where多条件查询，让英语成绩>计算机成绩
select cs1.score ‘英语成绩’,cs2.score ‘计算机成绩’ from course_score cs1,course_score cs2 where cs1.student_id = cs2.student_id and cs1.course_id = 2 or cs2.course_id = 1 and cs1.score> cs2.score;

4、子查询（将当前查询结果作为另一个查询的where选项）

练习题
1、查询张三的同班同学
分析：
同班：class
同学：student
步骤：
（1）查询张三的班级id
select class_id from student where username = ‘张三’;（不包含张三的话后面直接加上 and username != ‘张三’;）

（2）据第一步查询的id在student表中查询出所有列表
select *from student where class_id= ( select class_id from student where username = ‘张三’);

2、查询计算机或英语的成绩
分析：
计算机、英语：course
成绩：course_score
步骤：
（1）查询计算机或英语科目id
select id from course where name=‘计算机’ or name =‘英语’;

（2）据第一步查询出来的科目id在course_score表中查询对应的成绩
select *from course_score where course_id in (select id from course where name=‘计算机’ or name =‘英语’);

3、查询分数比java班平均分高的学生
分析：
java班：class
平均分：course_score、avg(score)
步骤：
（1）根据java班查询出班级id
select id from class where classname = ‘java 班’;

（2）据第一步查询出来的id查询这个班级所有学生的id
select id from student where class_id = 1;

（3）根据第二步得到的学生id，查询成绩列表（Java班级所有成绩列表）
select score from course_score where student_id in (select id from student where class_id = 1);

（4）使用聚合函数（avg）得到Java班的平均成绩
select avg(score) from course_score where ( student_id in (select id from student where class_id = 1));

（5）查询成绩表，添加where条件，成绩大于平均成绩的
select * from course_score where score >( select avg(score) from course_score where ( student_id in (select id from student where class_id = 1)) );

5、合并查询

1、union：该操作符用于取得两个结果集的并集，并且会执行去重
例如：select * from student where id < 3 union select * from student where username = ‘张三’;

2，union all：合并查询，不会进行去重
例如：select * from student where id < 3 union all select * from student where username = ‘张三’;

7、补充内容

1、mysql中的语法顺序：
select> from > where >group by > having > order by > limit
2、mysql中的执行顺序：
from > where > group by > having > select（别名在这里执行）> order by > limit
3、having和where的区别：
（1）having只能用在group by 之后，对分组后的结果进行筛选；
（2）where肯定使用在group by 之前；
（3）where的条件表达式中不可以使用聚合函数，而having可以；
（4）where不可以使用别名，而order by 和having 可以使用（因为mysql5.7.5 之后做了特殊允许，group by 和having可以使用别名）；
（5）where可以使用select中未出现的列进行条件筛选，而having不行。
4、group by 和having都可以单独使用。