3.MySQL表的设计

my## 1.表的设计

三大范式

1 .1一对一

1.2 一对多

1.3 多对多

建立表描述

描述每个同学的每个科目的考试成绩
①创建一个表写每个同学的名字

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | 甲   |
|  2 | 乙   |
|  3 | 丙   |
|  4 | 丁   |
+----+------+
4 rows in set (0.04 sec)

②创建一个表描述每个科目的成绩

mysql> select * from course;
+----+--------+
| id | name   |
+----+--------+
|  1 | 语文   |
|  2 | 数学   |
|  3 | 英语   |
|  4 | 物理   |
+----+--------+
4 rows in set (0.00 sec)

③创建中间表描述,1对应语文4对应丁
成绩表包含学生表中学生姓名和课程表中课程名称,组成某学生某学科的成绩

mysql> create table score(courseId int,studentId int,score decimal(3,1));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into score values(1,4,88);
Query OK, 1 row affected (0.04 sec)

中间表courseid 和studentid字段设置成外键更严谨

mysql> create table score(courseId int,foreign key(courseId)references course(id),
    -> studentId int,foreign key(studentId)references student(id),score decimal(3,1));-- 加外键
Query OK, 0 rows affected (0.05 sec)
mysql>  insert into score values(1,4,88);
Query OK, 1 row affected (0.04 sec)

表示甲在语文科目上是90
由于是多对多的关系,courseid 和studentid存在重复情况

2新增 子查询

(将表1的内容直接插入到表2中)

mysql> select * from user;
+----+--------+--------------+
| id | name   | decription   |
+----+--------+--------------+
|  1 | 张三   | 语文老师     |
|  2 | 李四   | 数学老师     |
|  3 | 张花   | 英语老师     |
+----+--------+--------------+
3 rows in set (0.00 sec)

mysql> select * from user2;
Empty set (0.00 sec)

mysql> insert into user2 select name,decription from user;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user2;
+--------+--------------+
| name   | decription   |
+--------+--------------+
| 张三   | 语文老师     |
| 李四   | 数学老师     |
| 张花   | 英语老师     |
+--------+--------------+
3 rows in set (0.00 sec)

将从user中查询到的数据插入到user2中(从user中查询name,decription两列然后插入user2中)

子查询得到的列的数目,顺序,类型都得和插入的表的列的数目,顺序,类型一致,列的名字一致不一致,无所谓

3查询

3.1聚合查询

(1)聚合函数
①count:计算结果的行数

mysql> select * from user;
+----+--------+--------------+
| id | name   | decription   |
+----+--------+--------------+
|  1 | 张三   | 语文老师     |
|  2 | 李四   | 数学老师     |
|  3 | 张花   | 英语老师     |
+----+--------+--------------+
3 rows in set (0.04 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.04 sec)

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
|           3 |
+-------------+
1 row in set (0.03 sec)

!!!count不计算NULL的值
!!!注意count和列之间的不能有空格,也就是和()之间不能有空格,count本来是一个函数,如果和(name)之间存在空格,此时就相当于把count当成一个列名
②sum返回查询数据的总和,只能用于数字
③avg返回查询数据的平均值,只能用于数字
④max返回查询数据的最大值,只能用于数字
⑤min返回查询数据的最小值,只能用于数字

mysql> select * from student;
+------+--------+-------+
| id   | name   | score |
+------+--------+-------+
|    1 | 张三   |  90.0 |
|    2 | 李四   |  88.0 |
|    3 | 张花   |  96.0 |
+------+--------+-------+
3 rows in set (0.00 sec)

mysql> select sum(score) from student;
+------------+
| sum(score) |
+------------+
|      274.0 |
+------------+
1 row in set (0.04 sec)

mysql> select avg(score) from student;
+------------+
| avg(score) |
+------------+
|   91.33333 |
+------------+
1 row in set (0.03 sec)

mysql> select max(score) from student;
+------------+
| max(score) |
+------------+
|       96.0 |
+------------+
1 row in set (0.04 sec)

mysql> select min(score) from student;
+------------+
| min(score) |
+------------+
|       88.0 |
+------------+
1 row in set (0.00 sec)

mysql> select avg(score) from student where score < 90; -- 求所有分数小于90同学的平均成绩,即可以加限定条件
+------------+
| avg(score) |
+------------+
|   88.00000 |
+------------+
1 row in set (0.04 sec)

mysql>

(2)group by 子句
把得到的查询结果按照一定的规则分组(可能分成多个组),再根据分组进行查询

查询员工表中每个岗位对应的最高工资,最低工资,平均工资

mysql> select * from emp; -- 员工表
+------+--------+-----------+--------+
| id   | name   | role      | salary |
+------+--------+-----------+--------+
|    1 | 张三   | 老师      |    100 |
|    2 | 张一   | 老师      |    200 |
|    3 | 张二   | 老师      |    300 |
|    4 | 张四   | 班主任    |    300 |
|    5 | 张五   | 班主任    |    250 |
|    6 | 张六   | 班主任    |    690 |
|    7 | 张七   | 市场      |    500 |
|    8 | 张八   | 市场      |    520 |
|    9 | 张九   | 市场      |    450 |
+------+--------+-----------+--------+
9 rows in set (0.00 sec)

mysql> select role from emp group by role;
+-----------+
| role      |
+-----------+
| 市场      |
| 班主任    |
| 老师      |
+-----------+
3 rows in set (0.00 sec)

mysql> select role ,avg(salary) from emp group by role;
+-----------+-------------+
| role      | avg(salary) |
+-----------+-------------+
| 市场      |    490.0000 |
| 班主任    |    413.3333 |
| 老师      |    200.0000 |
+-----------+-------------+
3 rows in set (0.04 sec)

mysql> select role ,avg(salary),max(salary),min(salary) from emp group by role;
+-----------+-------------+-------------+-------------+
| role      | avg(salary) | max(salary) | min(salary) |
+-----------+-------------+-------------+-------------+
| 市场      |    490.0000 |         520 |         450 |
| 班主任    |    413.3333 |         690 |         250 |
| 老师      |    200.0000 |         300 |         100 |
+-----------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

(3)having子句
(自我理解,having就是定位的意思)
查找出所有平均工资高于250的岗位和平均薪资

mysql> select role,avg(salary) from emp group by role having avg(salary) > 250;
+-----------+-------------+
| role      | avg(salary) |
+-----------+-------------+
| 市场      |    490.0000 |
| 班主任    |    413.3333 |
+-----------+-------------+
2 rows in set (0.00 sec)

group by把role(也就是岗位)相同的放在一个小组里,avg(salary)求出一个组里的平均薪资,之后用having筛选出avg(salary)大于250的岗位分组
!!!having是针对group by 之后的结果进行筛选
!!!where 是针对原始表中的每条数据进行筛选

3.2多表/联合查询

多表查询的过程,先计算多个表的笛卡尔积,在基于一些条件对笛卡尔积中的记录进行筛选
如果针对两个比较大的表进行联合查询,笛卡尔积的计算开销会很大,最终查找效率较低,所以不建议生产环境上对大表进行联合查询

借助sql语句可以查看两个表的笛卡尔积结果
多表查询时,写列的时候要写成[表名].[列名]

3.2.1内连接

(1)查找名字为”许仙”的同学的所有成绩
①姓名包含在student表中,分数包含在score表中,针对两个表进行联合查询,

mysql> select student.id,student.name,score.student_id,score.score from student,score; 

②笛卡尔积只有在两个表的id一致的情况下才有意义

select student.id,student.name,score.student_id,score.score from student,score where student.id = score.student_id;

!!!where指两个表之间的连接条件,当多个表进行笛卡尔积的时候,按照啥样的条件来筛选记录
筛选的时候不能连等于比如A=B=C,只能A=Band A=C
③为了查找许仙的成绩再加一个筛选条件

mysql> select student.id,student.name,score.student_id,score.score from student,score where student.id = score.student_id and student.name = '张三';
+----+--------+------------+-------+
| id | name   | student_id | score |
+----+--------+------------+-------+
|  1 | 张三   |          1 |  70.5 |
|  1 | 张三   |          1 |  98.5 |
|  1 | 张三   |          1 |  33.0 |
|  1 | 张三   |          1 |  98.0 |
+----+--------+-----------

(2)多表查询的join on 写法

mysql> select student.id ,student.name,score.student_id,score.score from student join  score on  student.id = score.student_id and student.name = '张三';
+----+--------+------------+-------+
| id | name   | student_id | score |
+----+--------+------------+-------+
|  1 | 张三   |          1 |  70.5 |
|  1 | 张三   |          1 |  98.5 |
|  1 | 张三   |          1 |  33.0 |
|  1 | 张三   |          1 |  98.0 |
+----+--------+------------+-------+
4 rows in set (0.00 sec)

查找名字为”许仙”的同学的所有成绩
解决问题的思路:
a)先把两张表联合在一起,得到笛卡尔积
b)按照student id 对笛卡尔积的记录进行筛选,保留有意义的数据
c)再针对名字进行筛选

(3)查找所有同学的总成绩,以及该同学的基本信息
同学信息是在student中
成绩在score中
先针对student和score进行联合查询(笛卡尔积)
a)按照学生id进行筛选,去除笛卡尔积中的不必要数据
b)按照学生id进行group by ,求每个同学的总成绩
①联合查找,没有任何条件得到的就是笛卡尔积

mysql> select student.id,student.name,score.student_id,score.score from student,score;

②根据student.id进行筛选,把不必要的记录去掉

mysql> select student.id,student.name,score.student_id,score.score from student,score where student.id = score.student_id;

③按照student.id进行group by把id相同的合并

mysql> select student.id,student.name,score.student_id,score.score from student,score where student.id = score.student_id group by student.id;
+----+--------+------------+-------+
| id | name   | student_id | score |
+----+--------+------------+-------+
|  1 | 张三   |          1 |  70.5 |
|  2 | 张一   |          2 |  60.0 |
|  3 | 张二   |          3 |  33.0 |
|  4 | 张四   |          4 |  67.0 |
|  5 | 张五   |          5 |  81.0 |
|  6 | 张六   |          6 |  56.0 |
|  7 | 张七   |          7 |  80.0 |
+----+--------+------------+-------+
7 rows in set (0.00 sec)

group by 之后得到的记录比原来少,如果某一列若干行的值已经相同了,group by没有影响
如果某一列不相同,group by 最终就只剩下一条记录
④如果是要求某一个同学的总成绩,其实也不受影响

mysql> select student.id,student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;
+----+--------+------------------+
| id | name   | sum(score.score) |
+----+--------+------------------+
|  1 | 张三   |            300.0 |
|  2 | 张一   |            119.5 |
|  3 | 张二   |            200.0 |
|  4 | 张四   |            218.0 |
|  5 | 张五   |            118.0 |
|  6 | 张六   |            178.0 |
|  7 | 张七   |            172.0 |
+----+--------+------------------+
7 rows in set (0.00 sec)

注释:表是student 和score ;下来定位student.id和score.student_id相同;分组student.id相同的分到一个组里,再根据组选择列student.id,stduent.name,score.score求和
select 中如果没有指定order by 语句,得到的结果顺序都是不确定的,写代码的时候就不能依赖这个顺序

(4)查找所有同学的每一科成绩和同学的相关信息
最终的效果要显示出:同学姓名,科目名称,对应的成绩
①先针对三张表进行联合,得到一个笛卡尔积,但是笛卡尔积中大部分数据都是没有意义的

mysql> select student.id ,student.name,course.id,course.name,score.student_id,score.course_id,score.score from student,score,course;

②按照student.id和score.student_id针对笛卡尔积进行筛选

mysql> select student.id ,student.name,course.id,course.name,score.student_id,score.course_id,score.score from student,score,course where student.id =score.student_id;

③结合课程id进行筛选

mysql> select student.id ,student.name,course.id,course.name,score.student_id,score.course_id,score.score from student,score,course where student.id =score.student_id and course.id = score.course_id;

④去掉重复列,只保留有用的

mysql> select student.id,course.name,score.score from student,score,course where student.id =score.student_id and course.id = score.course_id;
+----+--------------------+-------+
| id | name               | score |
+----+--------------------+-------+
|  1 | JAVA               |  70.5 |
|  1 | 语文               |  98.5 |
|  1 | 英文               |  33.0 |
|  2 | JAVA               |  60.0 |
|  2 | 英文               |  59.5 |
|  3 | JAVA               |  33.0 |
|  3 | 语文               |  68.0 |
|  3 | 英文               |  99.0 |
|  4 | JAVA               |  67.0 |
|  4 | 语文               |  23.0 |
|  4 | 英文               |  56.0 |
|  5 | JAVA               |  81.0 |
|  5 | 英文               |  37.0 |
|  6 | 中国传统文化       |  56.0 |
|  6 | 高阶数学           |  43.0 |
|  7 | 中国传统文化       |  80.0 |
+----+--------------------+-------+
16 rows in set (0.00 sec)

student表中有8个同学,查出来只有7个同学的成绩,因为张八对应的student中的8 没有成绩

3.2.2外连接

(1)内连接
同时在student和score表中存在的数据才能查到
(2)外连接
在student表中存在,在score表中不存在的数据或者在student表中不存在,在score表中存在的数据能查到
(3)左连接
在student表中存在,在score表中不存在的数据:可以查到
在student表中不存在,在score表中存在的数据:查不到
(4)右连接
在student表中存在,在score表中不存在的数据:查不到
在student表中不存在,在score表中存在的数据:可以查到

3.2.3自连接

自连接本质上相当于把同一列中的两行记录转换成不同列的同一列记录
(1)所有计算机原理的成绩比java成绩高的同学
①先找到java和计算机原理的id
②按照课程id在分数中筛选数据

①针对score表自身进行笛卡尔积

mysql> select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.student_id,s2.score,s2.course_id from score s1,score s2;

②加上学生id限制

mysql> select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.student_id,s2.score,s2.course_id from score s1,score s2 where s1.student_id = s2.student_id;

③加上课程id限制

mysql> select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.student_id,s2.score,s2.course_id from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1;
+------------+-------+-----------+------------+------------+-------+-----------+
| student_id | score | course_id | student_id | student_id | score | course_id |
+------------+-------+-----------+------------+------------+-------+-----------+
|          1 |  98.5 |         3 |          1 |          1 |  70.5 |         1 |
|          3 |  68.0 |         3 |          3 |          3 |  33.0 |         1 |
|          4 |  23.0 |         3 |          4 |          4 |  67.0 |         1 |
+------------+-------+-----------+------------+------------+-------+-----------+

④按照分数大小比较

mysql> select s1.student_id,s1.score,s1.course_id,s2.student_id,s2.student_id,s2.score,s2.course_id from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
+------------+-------+-----------+------------+------------+-------+-----------+
| student_id | score | course_id | student_id | student_id | score | course_id |
+------------+-------+-----------+------------+------------+-------+-----------+
|          1 |  98.5 |         3 |          1 |          1 |  70.5 |         1 |
|          3 |  68.0 |         3 |          3 |          3 |  33.0 |         1 |
+------------+-------+-----------+------------+------------+-------+-----------+

⑤只留下学生id

mysql> select s1.student_id from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
+------------+
| student_id |
+------------+
|          1 |
|          3 |
+------------+
2 rows in set (0.00 sec)

3.3子查询

在其他sql中嵌入查询语句
(a)子查询结果单条
查询和”张三”的同班同学(子查询结果只有一条)

mysql> select classes_id from student where name = '张三'; -- 先查张三的班级id 是多少,即子查询结果
+------------+
| classes_id |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
mysql> select name from student where classes_id = (select classes_id from student where name = '张三');
+--------+
| name   |
+--------+
| 张三   |
| 张一   |
| 张二   |
| 张四   |
| 张五   |
+--------+

(b)子查询结果多条
查询语文和英语对应的成绩
①借助in的方式

mysql> select id from course where name = '语文' or name = '英文';
+----+
| id |
+----+
|  3 |
|  5 |
+----+
2 rows in set (0.00 sec)

mysql> select * from score where course_id in (select id from course where name = '语文' or name = '英文');
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  59.5 |          2 |         5 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  37.0 |          5 |         5 |
+-------+------------+-----------+
8 rows in set (0.00 sec)

先执行子查询,把子查询的结果保存到内存中,再进行主查询,结合子查询的结果筛选最终结果
子查询得到2个课程id,再执行主查询,看当前的课程id是否在在子查询的结果里面

!!!子查询有多个结果时,不能用"="连接,只能用in

②借助exists

mysql> select * from score where exists (select score.course_id from course where (name = '语文' or name = '英文') and course.id = score.course_id);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  59.5 |          2 |         5 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  37.0 |          5 |         5 |
+-------+------------+-----------+
8 rows in set (0.00 sec)

先执行主查询,再触发子查询,(先主查询选择score表,然后主查询的每次记录触发一次子查询选择语文和英文的课程，在连接两个表)
and之后的相当于多表连接

如果子表查询的结果集合比较小,就用in(推荐使用)
如果子表查询的结果比较大,而主表发集合小,就用exists
(5)合并查询(将几个操作合并在一起)
相当于把多个查询的结果集合合并成一个集合(需要保证多个结果集之间的字段类型和数目都一致)
查询id < 3 或者名字为’英文’的课程
以下两种方式一种使用传统方法一种借助union

mysql> select * from course where id < 3 or name = '语文';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | JAVA               |
|  2 | 中国传统文化       |
|  3 | 语文               |
+----+--------------------+
3 rows in set (0.00 sec)

mysql> select * from course where id < 3 union select * from course where name = '英文';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | JAVA               |
|  2 | 中国传统文化       |
|  5 | 英文               |
+----+--------------------+
3 rows in set (0.01 sec)

or:如果筛选条件简单使用or
union:如果查询结果中存在相同的记录,就会只保留一个,如果不想区重,可以使用union all

sql的特点不需要用户来指定一步一步该如何执行,只需要告诉数据库,最终想要啥数据
像C/JAVA需要告诉计算机每一步都需要如何执行