一、分组函数：
分组函数常用到以上五个函数：sum, avg, max, min, count
1、语句的执行顺序：from where group by having select order by
2、group by子句创建数组
注意：通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组
--其中GROUP BY子句指定要分组的列
--在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY
--GROUP BY 所指定的列并不是必须出现在SELECT 列表中。
例子：查询每个部门的编号，名称，平均工资
//分组查询
select emp.deptno,dname,avg(sal) from emp,dept where emp.deptno=emp.deptno group by emp.deptno;
+--------+--------+-------------+
| deptno | dname | avg(sal) |
+--------+--------+-------------+
| 1 | 开发部 | 3000.000000 |
| 2 | 开发部 | 3000.000000 |
| 10 | 开发部 | 1300.000000 |
| 20 | 开发部 | 2617.857143 |
| 30 | 开发部 | 2200.000000 |
+--------+--------+-------------+
//子查询
select d.dname, t.avgsal
-> from dept d left join (select deptno,avg(sal) avgsal from emp where deptno is not null group by deptno) t
-> on d.deptno=t.deptno;
+------------+-------------+
| dname | avgsal |
+------------+-------------+
| 开发部 | 3000.000000 |
| 研发部 | 3000.000000 |
| 人力资源部 | NULL |
| 办公部 | NULL |
| 市场 | NULL |
| 销售 | NULL |
| ACCOUNTING | 1300.000000 |
| RESEARCH | 2617.857143 |
| SALES | 2200.000000 |
| OPERATIONS | NULL |
+------------+-------------+3、按多个列进行分组
练习：（1）查询每个部门每个岗位的工资总和
（2）求各部门中工资大于2000的员工的部门编号，部门名称，平均工资
（3）求各部门中职位是salesman 的最高工资
（4）求81年以后入职的不同岗位的最低工资
//1
select deptno,job,sum(sal) from emp group by deptno,job;
select d.deptno,dname,job,sum(sal)
-> from emp e,dept d where e.deptno=d.deptno
-> group by d.deptno,dname,job;
//2
select dept.deptno,dname,avg(sal) from emp,dept where emp.deptno=dept.deptno and sal>2000 group by dept.deptno ;
select d.deptno,d.dname,ifnull(avgsal,0)
-> from dept d
-> left join (select deptno,avg(sal) avgsal from emp where sal>2000 and deptno is not null group by deptno) t
-> on d.deptno=t.deptno;
//3
select deptno ,max(sal) from emp where job="salesman"group by deptno ;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 30 | 1600.00 |
+--------+----------+
//4
select job,min(sal) from emp where hiredate > '1981-12-31' group by job;
+---------+----------+
| job | min(sal) |
+---------+----------+
| ANALYST | 3000.00 |
| CLERK | 1100.00 |
| 开发 | 3000.00 |
+---------+----------+4、使用having语句
where:分组统计前的条件，如果能用where筛选用where筛选，性能快。
having 分组统计后的条件
练习：（1）查询每个部门最高工资大于2900的部门编号，最高工资
（2）求各部门工作不是SALESMAN的员工的工资总和，要求统计工资和大于1000
（3）求员工数大于2的部门编号，部门名称和部门人数
//1
select deptno,max(sal) from emp group by deptno having max(sal) >2900;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 1 | 3000.00 |
| 2 | 3000.00 |
| 20 | 5000.00 |
| 30 | 6000.00 |
+--------+----------+
//2
select sum(sal) from emp where job !='salesman' group by deptno having sum(sal) >1000;
//3
select dept.deptno,dname,count(*) from emp,dept where emp.deptno=dept.deptno group by emp.deptno having count(*)>2;
+--------+----------+----------+
| deptno | dname | count(*) |
+--------+----------+----------+
| 20 | RESEARCH | 7 |
| 30 | SALES | 7 |
+--------+----------+----------+5、使用组函数的非法的查询
不能使用分组函数
分组聚合函数写在having后面
使用 HAVING 子句限制组
记录已经分组.
使用过组函数.
与 HAVING 子句匹配的结果才输出
二、合并查询
运行速度快。
两个查询语句的select字段顺序必须一致。
A(a,b,c) B(b,c,d)
union:取合集去掉重复的（a,b,c,d）
union all:取所有记录不去掉重复（a,b,c,b,c,d）
intersect:取交集，两个集合公共的部分（b,c）
minus:取差集，A minus B(a) B minus A(d)
1、union（相当于 or）
该操作符用于取得两个结果集的并集。当使用该操作符时，会按照第一列进行排序，自动去掉结果集中重复行
2、union all
该操作符与union 相似，但是它不会取消重复行，而且不会排序
3、intersect（相当于 and）
使用该操作符用于取得两个结果集的交集
4、minus(减法）
使用改操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不
存在第二个集合中的数据。
//union
mysql> select ename,sal,job from emp where sal >2500
-> union
-> select ename,sal,job from emp where job = 'manager';
+--------+---------+-----------+
| ename | sal | job |
+--------+---------+-----------+
| 小白 | 3000.00 | 开发 |
| 小名 | 3000.00 | 开发 |
| J_ONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| ABCD | 6000.00 | CLERK |
| CLARK | 2450.00 | MANAGER |
+--------+---------+-----------+
9 rows in set (0.02 sec)
mysql> SELECT ename, sal, job FROM emp WHERE sal >2500 or job='MANAGER' order by ename;
+--------+---------+-----------+
| ename | sal | job |
+--------+---------+-----------+
| ABCD | 6000.00 | CLERK |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
| FORD | 3000.00 | ANALYST |
| J_ONES | 2975.00 | MANAGER |
| KING | 5000.00 | PRESIDENT |
| SCOTT | 3000.00 | ANALYST |
| 小名 | 3000.00 | 开发 |
| 小白 | 3000.00 | 开发 |
+--------+---------+-----------+三、视图
1、概念
视图是逻辑上来自一个或多个表的数据集合，是一张虚拟的表。
--物理上在视图中没有保存数据。
--数据库中只存放了视图的定义，没有存放视图中的数据，这些数据存放在原来的表中。
--使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
--视图中的数据依赖于原来表中的数据，表中数据改变，显示在视图中数据也发生改变。
2、使用视图的好处（特点）
保护真实表，隐藏重要字段的数据。保护数据
在视图中的操作会映射执行到真实表中
可以手动开启只读模式 使用关键字 with read only（mysql不支持）
3、创建视图
create view 视图名 as select 对外提供的内容 from 真实表名
create view 视图名 as select 对外提供的内容 from 真实表with read only（MySQL不支持）
例如：创建一个视图，通过该视图可以查看每个部门的名称，最低工资，最高工资，平均工资
mysql> CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
-> AS SELECT d.dname, MIN(e.sal), MAX(e.sal),AVG(e.sal) FROM emp e, dept d
-> WHERE e.deptno = d.deptno GROUP BY d.dname;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept_sum_vu;
+------------+---------+---------+-------------+
| name | minsal | maxsal | avgsal |
+------------+---------+---------+-------------+
| ACCOUNTING | 1300.00 | 1300.00 | 1300.000000 |
| RESEARCH | 800.00 | 5000.00 | 2617.857143 |
| SALES | 950.00 | 6000.00 | 2200.000000 |
| 开发部 | 3000.00 | 3000.00 | 3000.000000 |
| 研发部 | 3000.00 | 3000.00 | 3000.000000 |
+------------+---------+---------+-------------+4、从视图中检索数据
例子：创建一个视图，通过该视图能查询到每个部门的部门编号，部门名称及最低工资。通过如上视图，查询每个部门工资最低的员工姓名及部门名称创建视图时，最低工资起别名
mysql> create view emp_v1(deptno,dname,minsal) as
-> select d.deptno,d.dname,min(sal)
-> from emp e,dept d
-> where e.deptno = d.deptno
-> group by d.deptno;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from emp_v1;
+--------+------------+---------+
| deptno | dname | minsal |
+--------+------------+---------+
| 1 | 开发部 | 3000.00 |
| 2 | 研发部 | 3000.00 |
| 10 | ACCOUNTING | 1300.00 |
| 20 | RESEARCH | 800.00 |
| 30 | SALES | 950.00 |
+--------+------------+---------+
5 rows in set (0.00 sec)
mysql> select ename,dname,minsal from emp e,emp_v1 e1 where e.deptno=e1.deptno and e.sal = e1.minsal;
+------------+------------+---------+
| ename | dname | minsal |
+------------+------------+---------+
| 小白 | 开发部 | 3000.00 |
| 小名 | 研发部 | 3000.00 |
| MILLER_1_2 | ACCOUNTING | 1300.00 |
| SMITH | RESEARCH | 800.00 |
| JAMES | SALES | 950.00 |
+------------+------------+---------+
5 rows in set (0.00 sec)5、删除视图
drop view 视图名
查看所有视图：show table status where comment='view';
6、修改视图
create or replace view as select 子句
例子：修改视图，为每一列添加别名
create or replace view v_emp1(员工编号,员工姓名,职位) as select empno,ename,job from emp where deptno = 10;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from v_emp1;
+----------+------------+-------+
| 员工编号 | 员工姓名 | 职位 |
+----------+------------+-------+
| 7934 | MILLER_1_2 | CLERK |
+----------+------------+-------+7、视图操作
DML：data manipulate language数据操作语言（insert，delete，update）
create or replace view empview as select * from emp1;
insert into empview(empno,ename) values(111,'dwewer');
update empview set ename="frank" where empno=111;
delete from empview where empno=7934;
select * from empview;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 1 | 小白 | 开发 | 0 | 2019-09-16 | 3000.00 | 1000.00 | 1 |
| 2 | 小名 | 开发 | 0 | 2022-07-31 | 3000.00 | 1000.00 | 2 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | aLLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | J_ONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7691 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 111 | frank | NULL | NULL | NULL | 0.00 | NULL | NULL |
+-------+--------+-----------+------+------------+---------+---------+--------+
16 rows in set (0.00 sec)视图分类：视图分为简单视图和复杂视图，最基本差别在DML（增删改）操作上。
- 通过视图删除基表中数据，只要视图中不出现以下情况：
- 分组函数；
- GROUP BY 子句；
- DISTINCT 关键字；
- 通过视图修改基表中数据，只要视图中不出现以下情况：
- GROUP函数、GROUP BY子句，DISTINCT关键字；
- 使用表达式定义的列；
- 通过视图向基表插入数据，只要视图中不出现以下情况：
- GROUP函数、GROUP BY子句，DISTINCT关键字；
- 使用表达式定义的列；
- 基表中未在视图中选择的其它列定义为非空并且没有默认值；
四、索引
1、索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度
2、好处：可以提高查询速度。
缺点：新增，删除，修改一条数据时，会带来索引维护的代价。
3、索引分类
- 普通索引 index，允许有空值，可以重复。
- 唯一性索引：unique index 允许有空值不能重复
- 全文索引： char, varchar, text.
- 单列索引: index(ename)
- 多列索引: index(lastname, firstname)
- 空间索引: myisam 引擎上有。
4、创建索引
（1）创建索引的方式：
创建表的时候、在已存在的表中创建索引、使用alter table 创建索引
在创建表的时候创建
（2）普通索引
mysql> Create table index1(
-> Id int,
-> Name varchar(20),
-> Sex boolean,
-> Index(id)
-> );
Query OK, 0 rows affected (0.03 sec)（3）创建唯一性索引
mysql> Create table index3(
-> Id int unique,
-> Name varchar(20),
-> Unique index index2_id(id)
-> );
Query OK, 0 rows affected (0.02 sec)现在只有MyISAM存储引擎支持全文索引。
mysql的两种引擎：
innodb----主流. ----支持事务
Create table index2(
-> Id int,
-> Info varchar(20),
-> Fulltext index index3_info(info)
-> )engine=myisam;（5）创建单列索引
Create table index4(
-> Id int,
-> Subject varchar(30),
-> Index index4_st(subject)
-> );（6）创建多列索引
CREATE TABLE index5 (
-> id INT NOT NULL,
-> last_name CHAR(30) NOT NULL,
-> first_name CHAR(30) NOT NULL,
-> PRIMARY KEY (id),
-> INDEX name (last_name,first_name)
-> );注意：只有使用了索引中的第一个字段时才会触发索引
select * from index5;
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 1 | a | b |
| 2 | c | d |
| 3 | e | f |
| 4 | g | h |
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
| 7 | i | k |
+----+-----------+------------+
mysql> SELECT * FROM index5 WHERE last_name='i';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 6 | i | j |
| 7 | i | k |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name='b';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
+----+-----------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name='b';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND (first_name='b' OR first_name='j');
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name>='b' AND first_name < 'k';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
+----+-----------+------------+
3 rows in set (0.00 sec)
[点击并拖拽以移动]
mysql> SELECT * FROM index5 WHERE last_name='i';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 6 | i | j |
| 7 | i | k |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name='b';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
+----+-----------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name='b';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND (first_name='b' OR first_name='j');
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name>='b' AND first_name < 'k';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
+----+-----------+------------+
3 rows in set (0.00 sec)
[点击并拖拽以移动]
mysql> SELECT * FROM index5 WHERE last_name='i';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 6 | i | j |
| 7 | i | k |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name='b';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
+----+-----------+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name='b';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND (first_name='b' OR first_name='j');
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM index5 WHERE last_name='i' AND first_name>='b' AND first_name < 'k';
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 5 | i | b |
| 8 | i | b |
| 6 | i | j |
+----+-----------+------------+
3 rows in set (0.00 sec)在已经存在的表上创建索引
（7）语法如下：
create index 索引名 on 表名(字段名)
mysql> create table index6(
-> id int primary key,
-> sanme varchar(20),
-> sex varchar(10)
-> );
Query OK, 0 rows affected (0.01 sec)
create index index_5 on index6(id);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0（8）
- 在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：
CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名
ON 表名(属性名[ (长度) ] [ ASC | DESC] );
create index index_6 on index6(sanme);（9）用ALTER TABLE语句为已存在的表一个或几个字段创建索引
语法：alter table 表名 add index 索引名(字段名)
alter table index6 add index index_7(sex);4、删除索引
DROP INDEX 索引名ON 表名;
5、索引设计原则
- 选择惟一性索引,选择重复性小的列。
性别（×）
- 为经常需要排序、分组和联合操作的字段建立索引
order by sal, group by
- 为常作为查询条件的字段建立索引
where job = ‘’
- 限制索引的数目
因为维护索引需要代价，增删改
- 尽量使用数据量少的索引（某一列的值占空间少）
个人介绍：200，数据量大，索引的意义就不大。
- 删除不再使用或者很少使用的索引
注意：主键、外键，between and用的，group by，order by，where中常用的列适合创建索引。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
