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)
g