2.MySQL表的增删改查(进阶)

MySQL表的增删改查(进阶)

1.数据库约束

1.1 NOT NULL 指示某列不能存储NULL值

如果尝试往这里插入空值,就会直接报错

1.2 UNIQUE 保证某列的每行必须有唯一值

数据唯一,如果尝试插入重复的值,也会报错

1.3 DEFAULT 规定没有给列赋值时的默认值

约定一个默认值

给默认值一个值

1.4 PRIMARY KEY /NOT NULL和UNIQUE的结合.确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的特定的记录

主键约束,相当于数据的唯一身份标识,类似于身份证号码.手机号码

ps:主键不能为空不能重复

自增主键

1.5 FOREIGN KEY 保证一个表中的数据匹配另一个表中值的参照完整性.

外键约束,描述的是两张表之间的’'依赖关系"

1.6 CHECK保证列中的值符合指定条件,

2.表的设计

2.1一对一

2.2一对多

2.3多对多

3.新增

插入语句增强版,把插入和查询结合起来,从查询的结果,作为插入语句要插入的数据(列要对应)

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT,
   sn INT comment '学号',
   name VARCHAR(20) comment '姓名',
   qq_mail VARCHAR(20) comment 'QQ邮箱'
);
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄',
email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);

insert into student values(1,1000,'张三','123134@12'),(2,1001,'李四','31@');

mysql> select *from student;
+------+------+------+-----------+
| id   | sn   | name | qq_mail   |
+------+------+------+-----------+
|    1 | 1000 | 张三 | 123134@12 |
|    2 | 1001 | 李四 | 31@       |
+------+------+------+-----------+
2 rows in set (0.00 sec)

插入增强版

insert into test_user(name,email) select name,qq_mail from student;

4.查询

4.1 聚合查询

4.1.1 聚合函数

常见的聚合函数

函数	说明
count([distinct] expr)	返回查询到的数据的数量
sum([distinct] expr)	返回查询到的数据的总和,不是数字没有意义
avg([distinct] expr)	返回查询到的数据的平均值,不是数字没有意义
max([distinct] expr)	返回查询到的数据的最大值,不是数字没有意义
min([distinct] expr)	返回查询到的数据的最小值,不是数字没有意义

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);

-- 插入一条为空的记录
insert into exam_result values (8,'张无忌',null,null,null);

-- count()使用
mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> select count(name) from exam_result;
+-------------+
| count(name) |
+-------------+
|           8 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(english) from exam_result where english<60;
+----------------+
| count(english) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

-- sum()使用
mysql> select sum(math)from exam_result;
+-----------+
| sum(math) |
+-----------+
|     581.5 |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(math) from exam_result where math<60;
+-----------+
| sum(math) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.04 sec)

mysql> select sum(english) from exam_result where english < 60;
+--------------+
| sum(english) |
+--------------+
|        131.0 |
+--------------+
1 row in set (0.00 sec)

-- avg()使用
mysql> select avg(chinese + math +english) as '平均总分' from exam_result;
+-----------+
| 平均总分  |
+-----------+
| 221.42857 |
+-----------+
1 row in set (0.01 sec)

-- max()使用
mysql> select max(english) from exam_result;
+--------------+
| max(english) |
+--------------+
|         90.0 |
+--------------+
1 row in set (0.04 sec)

-- min()使用
mysql> select min(english) from exam_result;
+--------------+
| min(english) |
+--------------+
|         30.0 |
+--------------+
1 row in set (0.00 sec)

4.1.2 GROUP BY

根据行的值,对数据进行分组.把值相同的行都归为一组

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);

-- 查询每个角色的最高工资、最低工资和平均工资
mysql> select role,max(salary),min(salary),max(salary)from emp group by role;
+----------+-------------+-------------+-------------+
| role     | max(salary) | min(salary) | max(salary) |
+----------+-------------+-------------+-------------+
| 服务员   |     1000.20 |     1000.20 |     1000.20 |
| 游戏角色 |      999.11 |      333.50 |      999.11 |
| 游戏陪玩 |     2000.99 |     2000.99 |     2000.99 |
| 董事长   |    12000.66 |    12000.66 |    12000.66 |
+----------+-------------+-------------+-------------+
4 rows in set (0.00 sec)

4.1.2 HAVING

针对分组之后,得到的结果,可以通过having来进行指定条件~~

mysql> select role,avg(salary) from emp where name!='马云' group by role ;
+----------+-------------+
| role     | avg(salary) |
+----------+-------------+
| 游戏陪玩 | 2000.990000 |
| 游戏角色 | 677.646667  |
| 董事长   |12000.660000 |
+----------+-------------+
3 rows in set (0.00 sec)
-- 查询角色平均工资小于1500
mysql> select role,avg(salary) from emp group by role having avg(salary)<1500;
+----------+-------------+
| role     | avg(salary) |
+----------+-------------+
| 服务员   | 1000.200000 |
| 游戏角色 |  677.646667 |
+----------+-------------+
2 rows in set (0.00 sec)

ps: group by是可以使用where的.只不过where是在分组之前执行.如果要对分组之后的结果进行条件筛选,就需要使用having

4.2 联合查询

4.2.1内连接

把多个表的记录一起合并,一起进行查询

笛卡尔积(多表查询中的核心操作)

如何在SQL中进行笛卡尔积

最简单的做法,就是直接select,from后面跟上多个表名,表名之间使用逗号分隔

案例:

-- 对学生表和分数表进行笛卡尔积
select * from 表1,表2;

-- 内连接 语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

-- 一.查询'许仙'同学的成绩
-- 1.先想清楚,要查询的数据都来自于哪里(许仙同学选了很多课,就需要在学生表中获取都学生姓名,在分数表中获取到分数信息)
-- 2.对学生表和分数表进行笛卡尔积(如图1)
mysql> select * from student,score;

-- 3.当前笛卡尔积.里面东西太多了160行,这个时候我们就要找表与表之间的联系,仔细观察就能过发现,在当前表的这两张表里,都存在学生id这个列,这个两个id匹配才保留记录,不匹配的就属于是排列组合直接生成的无效数据

-- 4.如图2这个里面体现出了,每个学生的没门课程,分数分别是多少.接下下来要找到'许仙'分数信息就可以把其他同学给过滤掉,在加一个条件即可
mysql> select * from student,score where student.id = score.student_id;

-- 5.此时就是只包含许仙的没门课的成绩,只需要许仙的成绩其他列都不要了,只保留名字和分数
mysql> select * from student,score where student.id = score.student_id and student.name = '许仙';
+------+-------+
| name | score |
+------+-------+
| 许仙 |  67.0 |
| 许仙 |  23.0 |
| 许仙 |  56.0 |
| 许仙 |  72.0 |
+------+-------+
4 rows in set (0.00 sec)

-- ps 刚开始做的时候不要想着一步到位,这个阶段还不熟悉,一步步来是最稳妥的




-- 5.实现刚才这个多表查询,直接from多张表,是一种方法,还有就是可以用join这样的关键字,也能实现多表查询
mysql> select student.name,score.score from student join score on student.id = score.student_id and student.name = '许仙';
+------+-------+
| name | score |
+------+-------+
| 许仙 |  67.0 |
| 许仙 |  23.0 |
| 许仙 |  56.0 |
| 许仙 |  72.0 |
+------+-------+
4 rows in set (0.00 sec)

-- 二.查询所有同学的总成绩,及同学的个人信息(多表查询+聚合查询)
mysql> select * from student,score where student.id = score.student_id group by student.id;
+----+-------+------------+-----------------+------------+-------+------------+-----------+
| id | sn    | name       | qq_mail         | classes_id | score | student_id | course_id |
+----+-------+------------+-----------------+------------+-------+------------+-----------+
|  1 | 09982 | 黑旋风李逵 | xuanfeng@qq.com |          1 |  70.5 |          1 |         1 |
|  2 | 00835 | 菩提老祖   | NULL            |          1 |  60.0 |          2 |         1 |
|  3 | 00391 | 白素贞     | NULL            |          1 |  33.0 |          3 |         1 |
|  4 | 00031 | 许仙       | xuxian@qq.com   |          1 |  67.0 |          4 |         1 |
|  5 | 00054 | 不想毕业   | NULL            |          1 |  81.0 |          5 |         1 |
|  6 | 51234 | 好好说话   | say@qq.com      |          2 |  56.0 |          6 |         2 |
|  7 | 83223 | tellme     | NULL            |          2 |  80.0 |          7 |         2 |
+----+-------+------------+-----------------+------------+-------+------------+-----------+
7 rows in set (0.04 sec)

-- 加上group by之后,可以看到,记录的行数明显变少了,每个同学 只有一行数据了,分组之后,可以看到,当前这里的score列并不是总成绩,而是每个分组中的第一条记录,此时想要得到总成绩,就需要进行sum操作
mysql> select student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;
+------------+------------------+
| name       | sum(score.score) |
+------------+------------------+
| 黑旋风李逵 |            300.0 |
| 菩提老祖   |            119.5 |
| 白素贞     |            200.0 |
| 许仙       |            218.0 |
| 不想毕业   |            118.0 |
| 好好说话   |            178.0 |
| tellme     |            172.0 |
+------------+------------------+
7 rows in set (0.00 sec)
-- 别名
mysql> select student.name,sum(score.score) as '总分' from student,score where student.id = score.student_id group by student.id;
+------------+-------+
| name       | 总分  |
+------------+-------+
| 黑旋风李逵 | 300.0 |
| 菩提老祖   | 119.5 |
| 白素贞     | 200.0 |
| 许仙       | 218.0 |
| 不想毕业   | 118.0 |
| 好好说话   | 178.0 |
| tellme     | 172.0 |
+------------+-------+
7 rows in set (0.00 sec)

-- 三.查询所有同学的成绩,及同学的个人信息;
-- 由题可知,不止要查询出同学的名字,还有课程的名字,以及分数,这个时候,就涉及到三张表的联合查询了
>> 同学名字 ->学生表
>> 课程名字->课程表
>>分数=>分数表
mysql> select * from student,score,course where student.id = score.student_id and score.course_id = course.id;

mysql> select student.name,score.score,course.name from student,score,course where student.id = score.student_id and score.course_id = course.id;
+------------+-------+--------------+
| name       | score | name         |
+------------+-------+--------------+
| 黑旋风李逵 |  70.5 | Java         |
| 黑旋风李逵 |  98.5 | 计算机原理   |
| 黑旋风李逵 |  33.0 | 高阶数学     |
| 黑旋风李逵 |  98.0 | 英文         |
| 菩提老祖   |  60.0 | Java         |
| 菩提老祖   |  59.5 | 高阶数学     |
| 白素贞     |  33.0 | Java         |
| 白素贞     |  68.0 | 计算机原理   |
| 白素贞     |  99.0 | 高阶数学     |
| 许仙       |  67.0 | Java         |
| 许仙       |  23.0 | 计算机原理   |
| 许仙       |  56.0 | 高阶数学     |
| 许仙       |  72.0 | 英文         |
| 不想毕业   |  81.0 | Java         |
| 不想毕业   |  37.0 | 高阶数学     |
| 好好说话   |  56.0 | 中国传统文化 |
| 好好说话   |  43.0 | 语文         |
| 好好说话   |  79.0 | 英文         |
| tellme     |  80.0 | 中国传统文化 |
| tellme     |  92.0 | 英文         |
+------------+-------+--------------+
20 rows in set (0.00 sec)

图1

图2

图3

join

select 列 from 表1 inner join 表2 on 条件  -- 内连接
select 列 from 表1 left join 表2 on 条件	 -- 左外连接
-- 左外连接:是以左侧的表为主,会尽可能的把左侧的记录都列出来,大不了后侧的表对应列填null
select 列 from 表1 right join 表2 on 条件 -- 右外连接
-- 右外连接:是以右侧的表为主,会尽可能的把右侧的记录都列出来,大不了后侧的表对应列填null

mysql> select *from student;
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
+------+------+
3 rows in set (0.00 sec)


mysql> select * from score;
+------------+-------+
| student_id | score |
+------------+-------+
|          1 |    90 |
|          2 |    80 |
|          4 |    70 |
+------------+-------+
3 rows in set (0.00 sec)

mysql> select * from student inner join score on student.id=score.student_id;
+------+------+------------+-------+
| id   | name | student_id | score |
+------+------+------------+-------+
|    1 | 张三 |          1 |    90 |
|    2 | 李四 |          2 |    80 |
+------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from student left join score on student.id= score.student_id;
+------+------+------------+-------+
| id   | name | student_id | score |
+------+------+------------+-------+
|    1 | 张三 |          1 |    90 |
|    2 | 李四 |          2 |    80 |
|    3 | 王五 |       NULL |  NULL |
+------+------+------------+-------+
3 rows in set (0.00 sec)

mysql> select * from student right join score on student.id = score.student_id;
+------+------+------------+-------+
| id   | name | student_id | score |
+------+------+------------+-------+
|    1 | 张三 |          1 |    90 |
|    2 | 李四 |          2 |    80 |
| NULL | NULL |          4 |    70 |
+------+------+------------+-------+
3 rows in set (0.00 sec)

自连接

自己和自己进行笛卡尔积,属于sql中的一种奇淫巧技(用的不多只是用来处理一些特殊场景的问题)

什么时候需要使用自连接,自连接的本质其实是把行与行之间的比较条件,转换成列和列,sql指定条件都是按照列和列之间进行指定的

显示所有计算机原理成绩比java成绩高的成绩信息

select *from score s1 ,score s2;

 
 select *from score s1 ,score s2 where s1.student_id = s2.student_id ;
 
select *from score s1 ,score s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id=1;

select *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;

 select s1.student_id,s1.score,s2.score from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id =1 and s1.score > s2.score;

ps:自连接的关键就是可以把行转换成列来进行比较

子查询

案例:查询不想毕业同学的同班同学

-- 单行查询
1.-- 查询不想毕业同学所在班级的id
mysql> selec classes_id from student where classes_id =1;
+------------+
| classes_id |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
2.-- 根据查询到的id来查看班级的同学
mysql> select name from student where classes_id =1;
+------------+
| name       |
+------------+
| 黑旋风李逵 |
| 菩提老祖   |
| 白素贞     |
| 许仙       |
| 不想毕业   |
+------------+
5 rows in set (0.00 sec)
-- 子查询
mysql> select name from student where classes_id =(select classes_id from student where name='不想毕业');
+------------+
| name       |
+------------+
| 黑旋风李逵 |
| 菩提老祖   |
| 白素贞     |
| 许仙       |
| 不想毕业   |
+------------+
5 rows in set (0.00 sec)

-- 多行子查询

mysql> select id from course where name ='语文' or name ='英文';
+----+
| id |
+----+
|  4 |
|  6 |
+----+
mysql> select *from score where course_id in(4,6);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
5 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.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
5 rows in set (0.00 sec)

ps关键理解套娃

合并查询

合并查询也可用or来代替,但是or的使用是有条件的,需要在同一个表中,合并查询union没有这个要求所以,union的使用场景更广一点.

union和union的区别,union可以进行自动去重,unio all 是不会去重的

案例 查询id小于3,或者名字为英文的课程

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

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

5.内容重点总结

MySQL主要的难点还是在多表查询,碰到多表查询也不要慌,一步一步来