1.查询可以搭配插入使用
要求:查询出来的结果的集合,列数/类型 要和插入的这个表匹配
——————————————创建两个表————————
mysql> create table student1(id int , name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> create table student2(id int , name varchar(20));
Query OK, 0 rows affected (0.03 sec)
————————student1 是有数据的————————————
mysql> select * from student1;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+--------+
3 rows in set (0.00 sec)
————————————————studnet2是无数据的————————————
mysql> insert into student2 select*from student;
ERROR 1146 (42S02): Table 'dome1.student' doesn't exist
————————查询,插入操作——————————
mysql> insert into student2 select*from student1;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
————————可以看到 student2 出现了 student1中的数据;
mysql> select * from student2;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+--------+
3 rows in set (0.00 sec)
2 .聚合查询【重点】
聚合查询; 相当于 行和行之间进行运算
表达式查询:针对 列和列之间进行运算
eg: sql中提供了一些 聚合函数 通过 聚合函数来运算
聚合函数
函数 | 说明 |
COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
SUM([DISTINCT] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
AVG([DISTINCT] expr) | 返回查询到的数据的 平均值,不是数字没有意义 |
MAX([DISTINCT] expr) | 返回查询到的数据的 最大值,不是数字没有意义 |
MIN([DISTINCT] expr) | 返回查询到的数据的 最小值,不是数字没有意义 |
1.COUNT([DISTINCT] expr)
mysql> 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);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
————————————————先执行 select * ,再针对结果集合进行统计(看有几行)
mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
2.SUM([DISTINCT] expr)
只能进行算术运算
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 唐三藏 | 67.0 | 98.0 | 56.0 |
| 2 | 孙悟空 | 87.5 | 78.0 | 77.0 |
| 3 | 猪悟能 | 88.0 | 98.5 | 90.0 |
| 4 | 曹孟德 | 82.0 | 84.0 | 67.0 |
| 5 | 刘玄德 | 55.5 | 85.0 | 45.0 |
| 6 | 孙权 | 70.0 | 73.0 | 78.5 |
| 7 | 宋公明 | 75.0 | 65.0 | 30.0 |
+------+-----------+---------+------+---------+
————————————————————-eg————————————
mysql> select sum(chinese) from exam_result;
+--------------+
| sum(chinese) |
+--------------+
| 525.0 |
+--------------+
————————————————也可以用表达式——————————
mysql> select sum(chinese + english + math) from exam_result;
+-------------------------------+
| sum(chinese + english + math) |
+-------------------------------+ -----先把列相加 ,再加行
| 1550.0 |
+-------------------------------+
mysql> select sum(name) from exam_result;
+-----------+
| sum(name) |
+-----------+ -----------并不会报错, 会有警告-------
| 0 |
+-----------+
1 row in set, 7 warnings (0.00 sec)
mysql> show warnings; ------查看警报内容----
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '唐三藏' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙悟空' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '猪悟能' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '曹孟德' | 无法 转为数字
| Warning | 1292 | Truncated incorrect DOUBLE value: '刘玄德' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙权' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '宋公明' |
+---------+------+-----------------------------------------------+
3.AVG([DISTINCT] expr)
求平均值
mysql> select avg(chinese) from exam_result;
+--------------+
| avg(chinese) |
+--------------+
| 75.00000 |
+--------------+
1 row in set (0.01 sec)
4.MAX([DISTINCT] expr)
最大值
mysql> select max(chinese) from exam_result;
+--------------+
| max(chinese) |
+--------------+
| 88.0 |
+--------------+
1 row in set (0.01 sec)
5.MIN([DISTINCT] expr)
最小值
mysql> select min(chinese) from exam_result;
+--------------+
| min(chinese) |
+--------------+
| 55.5 |
+--------------+
1 row in set (0.00 sec)
6.group by 语句
针对指定的列进行分组 一般都搭配聚合函数使用
6.1基本用法
select role,avg(列值) from 表名 group by role;
mysql> select * from emp;
+----+--------+--------+--------+
| id | name | role | salary |
+----+--------+--------+--------+
| 1 | 张三 | 三级 | 10000 |
| 2 | 李四 | 三级 | 11000 |
| 3 | 王五 | 三级 | 9000 |
| 4 | 小六 | 二级 | 9000 |
| 5 | 七海 | 二级 | 9900 |
| 6 | 八荒 | 一级 | 100000 |
+----+--------+--------+--------+
————————使用groip by 查询会把每一个先进行分组,再计算——————————
mysql> select role,avg(salary) from emp group by role;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 一级 | 100000.0000 |
| 三级 | 10000.0000 |
| 二级 | 9450.0000 |
+--------+-------------+
6.1.2如果这里不使用聚合,他就会随机给你确定一个数 如下
mysql> select role,salary from emp group by role;
+--------+--------+
| role | salary |
+--------+--------+
| 一级 | 100000 |
| 三级 | 10000 |
| 二级 | 9000 |
+--------+--------+
6.1.3排除某个值
可以直接使用 where 即可 ,where 字句 一般写在 group by 的前面
select 列名,avg(salary) from 表名 where 表达式 group by 列名;
mysql> select role,avg(salary) from emp where name != '张三' group by role;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 一级 | 100000.0000 |
| 三级 | 10000.0000 |
| 二级 | 9450.0000 |
+--------+-------------+
6.1.4查询平均值
select 列名,avg(salary) from 表名 where 表达式 group by 列名;
select role,avg(salary) from emp group by role having avg(salary) <20000;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 三级 | 10000.0000 |
| 二级 | 9450.0000 |
+--------+-------------+
6.1.5均值,排除超过一定数值的结果
mysql> select role,avg(salary) from emp group by role having avg(salary) <20000;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 三级 | 10000.0000 |
| 二级 | 9450.0000 |
+--------+-------------+
如上6.1.4 需要先排除张三 进行运算
而6.1.5 需要先进行运算,再把平均值高于 20000 的排除
同时两者也可以同时进行:
mysql> select role,avg(salary) from emp where name != '张三' group by role having avg(salary) <20000;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 三级 | 10000.0000 |
| 二级 | 9450.0000 |
+--------+-------------+
7 联合查询/多表查询【重点】
在我们日常开发中要尽量克制使用这个查询【会产生无意义的数据】
select * from 表1 表2
还可以添加部分条件
select * from 表1,表2 where 表名.列名 and 表名.列名;
select 表名.name , 表名.name from 表 where 表名,列名;
基于多表查询 跟聚合查询 :
1.先进行笛卡尔积,
select 表名.列名 ,表名.列名 from 表名 , socre where 条件
2再根据以上进行聚合
select 表名.列名 ,sum(表名.列名) from 表名 , socre where 条件 group by 列名;
上述多为内链接
内连接
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完 全显示我们就说是右外连接。
-- 左外连接,表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件; p8
自连接
select * from score as s1 ,score as s2 where s1.列名 = s2.列名 and s1.列名 = 3 and s2.列名 = 1;
合并查询
把多个 sql查询的结果集合,合并到一起;
union关键字
可以查询两个表信息,查询结果是去重的
union all 查询结果不去重
笛卡尔积:笛卡尔积是无脑的排序组合,把所有可能的情况都穷举了一遍,据包含了一些非法的 数据,和无意义的数据。
本质是通过排列组合的方式 ,得到一个更大的表;
笛卡尔积的列数 是这两个表的列数相加
笛卡尔积的行数 是这两个表的行数相乘