09_MySQL笔记-组函数-GROUP BY-LIMIT-多表连接查询-子查询-UNION-索引-视图-存储过程_groupby limit

个人博客
https://blog.csdn.net/cPen_web

---

组函数

组函数

	组函数又叫做聚集函数（aggregation function），它在一个行的集合（一组行）上进行操作，对每个组给一个结果

常用的组函数
	AVG([DISTINCT] expr)  ：求平均值
	COUNT({*|[DISTINCT] } expr) ：统计行的数量
	MAX([DISTINCT] expr)  ：求最大值
	MIN([DISTINCT] expr)  ：求最小值
	SUM([DISTINCT] expr)  ：求累加和

默认情况下，组函数忽略列值为null的行，不把它们拿来参与计算
1
2
3
4
5
6
7
8
9
10
11
12

count(*)：返回表中满足where条件的行的数量

例1：有多少球员住在Stratford？
 SELECT  count(*)
  FROM  players
  WHERE  town='Stratford'; 
1
2
3
4
5
6

count(列)：返回列值非空的行的数量

例3：一共有多少个联盟会员号码？
SELECT  count(leagueno)
  FROM  players;
1
2
3
4
5

count(distinct 列)：返回列值非空的、并且列值不重复的行的数量

例4：查询球员所居住的城市的数量
SELECT  count(DISTINCT town)
  FROM  players;
1
2
3
4
5

组函数的参数也可以是函数表达式

例5：得到出现在penalties表中的不同年份的数量
SELECT count(DISTINCT year(payment_date))
  FROM penalties;
1
2
3
4
5

一个SELECT子句中可出现多个聚集函数

例6：得到球员居住城市的数量和性别的数量
SELECT count(DISTINCT town),count(DISTINCT sex)
  FROM players;
1
2
3
4
5

MAX和MIN

例7：得到最高和最低的罚款金额
SELECT  max(amount), min(amount)
  FROM  penalties; 
1
2
3
4
5

如果统计的列中只有NULL值，那么MAX和MIN就返回NULL
1

SUM和AVG函数

例9：得到44球员罚款的总额以及平均值
SELECT  sum(amount), avg(amount)
  FROM  penalties
 WHERE  playerno=44;
1
2
3
4
5
6

例10：计算公司的平均佣金。要求拿佣金的雇员才参与计算
SELECT avg(commission_pct) 
  FROM employees;
1
2
3

例11：计算公司的平均佣金。要求所有的雇员都参与计算
SELECT avg(ifnull(commission_pct,0)) 
  FROM employees; 

要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换
1
2
3
4
5

---

GROUP BY

GROUP BY子句
	原则：只有在group by后面出现过的字段，才能在select后面出现；没有出现的 使用聚合函数
	group by子句后面进行条件过滤的话，使用having子句   (having一般搭配group by)

	GROUP BY 子句根据给定列或者表达式的每一个不同的值将表中的行分成不同的组。使用组函数返回每一组的统计信息
1
2
3
4
5

指定一个列进行分组

例1：查询每个城市的名称和球员的数量
SELECT  town, count(*)
  FROM  players
  GROUP BY  town; 

加条件：找出 城市超过三个人的城市
SELECT  town, count(*)  FROM  players  GROUP BY  town  HAVING COUNT(town) > 3;
1
2
3
4
5
6
7
8
9

例2：对于每个球队，得到编号、参加比赛的数量以及赢得的局数
SELECT  teamno,count(*), sum(won)
  FROM  matches
  GROUP BY  teamno;
1
2
3
4

使用多个分组列，形成“大组中再分小组”的分组效果

例3：统计每个球队中每个球员所赢得的总局数
SELECT teamno, playerno, sum(won)
  FROM  matches
  GROUP BY  teamno, playerno;
1
2
3
4
5
6

根据表达式分组

例4：对于penalties表中的每一年，得到支付罚款的次数
SELECT  year(payment_date), count(*)
  FROM  penalties
  GROUP BY  year(payment_date);  
1
2
3
4
5
6

带有排序的分组
	如果分组列和排序列相同，则可以合并group by和order by子句

例6：得到每个球队的编号和比赛总场数，结果按球队编号降序排序
SELECT  teamno, count(*)
  FROM  matches
  GROUP BY  teamno
  ORDER BY  teamno DESC; 

可以把desc(或者asc)包含到group by子句中简化
SELECT  teamno, count(*)
  FROM  matches
  GROUP BY  teamno DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13

GROUP BY子句的规则

	1 .出现在SELECT子句中的单独的列，必须出现在GROUP BY子句中作为分组列。这条规则适用于其它数据库，但是不适用于MYSQL

	在MYSQL中执行，不抛出异常，而是返回结果。其中town列的值随机返回。通过设置sql_mode系统变量的值为ONLY_FULL_GROUP_BY来强制mysql和其它数据库一样，遵循该规则（推荐）

	原则：只有在group by后面出现过的字段，才能在select后面出现；没有出现的 使用聚合函数
1
2
3
4
5
6
7

---

GROUP_CONCAT()函数

	MYSQL特有的组函数。该函数的值等于属于一个组的指定列的所有值。这些值一个挨一个的放置，以逗号隔开，并且以字符串表示

例8：对于每个球队，得到其编号和所有球员的编号
SELECT  teamno, group_concat(playerno)			#注：group_concat(distinct playerno)  去重
  FROM  matches
  GROUP BY  teamno; 
1
2
3
4
5
6
7
8

查看出同在一个城市的球员的名字和城市名、数量 --> 家乡(town) 相同的球员，查询出家乡名和所以的球员的名字
select town,GROUP_CONCAT(name),COUNT(NAME) from PLAYERS GROUP BY TOWN
1
2

CONCAT()函数		字符创的拼接

root@test mysql>select CONCAT(name,sex,town) from PLAYERS;
root@test mysql>select CONCAT_WS('#',name,sex,town) from PLAYERS;		#注：指定分隔符

Linux	cat命令  就是concat的缩写
1
2
3
4
5
6

---

对分组结果进行过滤

不能使用WHERE子句对分组后的结果进行过滤
不能在WHERE子句中使用组函数
因为WHERE子句比GROUP BY先执行，而组函数必须在分完组之后才执行
1
2
3

HAVING子句

	专门用来对分组后的结果进行过滤
	HAVING可以单独使用而不和GROUP BY配合
	HAVING子句中可以使用组函数
1
2
3
4
5

例1：得到那些多于一次罚款的球员的编号
SELECT  playerno
  FROM  penalties
  GROUP BY  playerno
  HAVING  count(*) > 1;
1
2
3
4
5

例2：对于罚款总额大于150元的球员，得到编号和罚款额
SELECT  playerno, sum(amount)
  FROM  penalties
  GROUP BY  playerno
  HAVING  sum(amount) > 150;
1
2
3
4
5

如果只有HAVING子句而没有GROUP BY，表中所有的行分为一组 

例3：得到所有罚款的总和，要求总和大于250元时才显示
SELECT sum(amount)
  FROM penalties
  HAVING sum(amount) > 250; 
1
2
3
4
5
6

HAVING子句中的列，要么出现在一个组函数中，要么出现在GROUP BY子句中。否则出错

#注：having子句	专门给group by 做条件过滤的，是一种组合
1
2
3

---

LIMIT

LIMIT子句

	MySQL特有的子句。它是SELECT语句中的最后一个子句（在order by后面）。它用来表示从结果集中选取最前面或最后面的几行。偏移量offset的最小值为0

	语法		limit  <获取的行数> [OFFSET <跳过的行数>]
	或者		limit [<跳过的行数>,] <获取的行数>  
1
2
3
4
5
6

取第3--6行
	limit 2,4
取前3名
	limit 3
只取第3名
	limit 2,1
1
2
3
4
5
6

例1：得到编号最大的前4个球员的编号和名字
SELECT  playerno, name
  FROM  players
  ORDER BY  playerno DESC
  LIMIT  4;
1
2
3
4
5

带偏移量的limit
	用来跳过前面的几行后再取

例4：得到球员编号最低的5个球员的编号和名字，从第4个球员开始
SELECT playerno,  name
  FROM players
  ORDER BY playerno ASC
  LIMIT  3, 5;  -- 或者limit 5 offset 3;
1
2
3
4
5
6
7
8

例 6：第3高的罚款额是多少？
SELECT  DISTINCT  amount
          FROM PENALTIES
      ORDER BY amount DESC
       LIMIT 2,1
1
2
3
4
5

---

多表连接查询

多表连接查询
	从多个表获得数据
	多表连接查询，会消耗大量性能，多表 合并与排序，大量计算，消耗内存、CPU等
1
2
3

连接类型

连接（join）：将一张表中的行按照某个条件（连接条件）和另一张表中的行连接起来形成一个新行的过程叫做连接

根据查询返回的结果，连接可以分为3大类：
	内连接（inner join）					#注：符合条件的行	显示
	外连接（outer join）					#注：一张表里面 符合条件的行显示，另外一张表里面不符合条件的行也显示
	交叉连接（cross join）

在连接查询中，一个列可能出现在多张表中。为了避免引起歧义，通常在列名前面加上表名或表别名作为前缀

使用表别名作为前缀，可以使得SQL代码较短，使用的内存更少
1
2
3
4
5
6
7
8
9
10
11
12

内连接
外连接
	左连接
	右连接
交叉连接
1
2
3
4
5

内连接（inner join）
	只返回两张表中所有满足连接条件的行

1 .使用using子句
2 .使用on子句
3 .使用where子句
1
2
3
4
5
6

使用using子句

例2:
SELECT  *
  FROM  penalties 
  JOIN  teams
  USING(playerno);						#注：playerno		两张表里都有的公共字段
 可见，连接列在结果集中只出现一次 

SELECT NAME,BIRTH_DATE,AMOUNT,PLAYERNO FROM PLAYERS JOIN PENALTIES USING(PLAYERNO);
1
2
3
4
5
6
7
8
9
10

使用ON子句

例3：查询每个球队的编号以及队长的名字
SELECT  t.teamno, p.name
  FROM  teams t
  JOIN  players p
  ON  t.playerno = p.playerno;
1
2
3
4
5
6
7

传统的连接写法

在FROM子句中列出所有要连接的表的名字，以逗号分隔。连接条件写在WHERE子句中

例4：对于每笔罚款，找出罚款编号，金额以及引起罚款的球员编号和姓名
SELECT  paymentno, pen.playerno, amount,NAME
  FROM  penalties pen, players p
  WHERE  pen.playerno = p.playerno;

另外2种写法：
SELECT paymentno,pen.playerno,amount,name FROM PENALTIES pen 
JOIN PLAYERS p 
USING(playerno)

SELECT paymentno,pen.playerno,amount,name FROM PENALTIES pen 
JOIN PLAYERS p 
ON pen.playerno = p.playerno

注意：一旦给表定义了别名，那么原始的表名就不能在出现在该语句的其它子句中了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3表连接查询

例5：查询每场比赛的编号、球员编号、球队编号、球员的名字以及球队的分级
SELECT  m.matchno,  m.playerno,  m.teamno, p.name,  t.division
  FROM matches m, players p, teams t
  WHERE  m.playerno = p.playerno
        AND  m.teamno  =  t.teamno;

表：比赛表 matches    PLAYERS    TEAMS
字段：比赛的编号、球员编号、球队编号、球员的名字以及球队的分级
条件：PLAYERNO

SELECT MATCHNO,m.PLAYERNO,m.TEAMNO,NAME,division from MATCHES m,PLAYERS p,TEAMS t
where t.teamno = m.teamno and m.playerno = p.playerno
1
2
3
4
5
6
7
8
9
10
11
12
13
14

外连接
	不满足条件的行，也可以显示出来
	左外连接
	右外连接
1
2
3
4

左外连接
	#注：不满足条件的行也显示出来，以左表为准
	除了返回两张表中所有满足连接条件的行之外，还要返回左表中所有不满足连接条件的行。所谓左表，就是写在LEFT JOIN关键字左边的表

例6：查询所有球员的编号、姓名和他引起的罚款。没有罚款的球员也要显示
SELECT  p.playerno, p.name, pen.amount
  FROM players p
  LEFT JOIN  penalties pen
  ON p.playerno = pen.playerno; 
1
2
3
4
5
6
7
8
9

右外连接
	#注：不满足条件的行也显示出来，以右表为准
	除了返回两张表中所有满足连接条件的行之外，还要返回右表中所有不满足连接条件的行。所谓右表，就是写在RIGHT  JOIN关键字右边的表

例7：查询所有球员的编号、姓名和他们是队长的球队的编号。要求没有当队长的球员也要显示
SELECT  p.playerno, p.name, t.teamno
  FROM  teams t
  RIGHT JOIN  players p
  ON t.playerno = p.playerno;

#注：可以改成左外连接，是一样的

MySQL不支持全外连接
1
2
3
4
5
6
7
8
9
10
11
12
13

交叉连接
	--> 笛卡尔积

笛卡尔积
	如果连接查询没有写任何连接条件，那么一张表中的所有行可以和另一张表中的所有行进行连接，得到的结果集中的总行数就是两张表中总行数的乘积，叫做笛卡尔积
	在实际中，应该要避免产生笛卡尔积的连接，特别是对于大表

SELECT  *
  FROM  matches, penalties;  --- 结果有12*8=96行
1
2
3
4
5
6
7
8
9

如果想专门产生笛卡尔积，可以使用交叉连接
SELECT  *
  FROM  matches 
  CROSS JOIN  penalties;
1
2
3
4

---

子查询

子查询解决的问题

子查询（inner  query）先执行，然后执行主查询（outer  query）

子查询返回的结果被用于外部查询中

子查询可出现在几乎所有的SELECT子句中，如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句等 --> 子查询可以出现在任意的部分

子查询必须放在小括号中

子查询一般放在比较操作符的右边，以增强代码可读性
1
2
3
4
5
6
7
8
9

不相关子查询

标量子查询(scalar subquery)：返回1行1列一个值 
可以使用 = > < >= <= <> 操作符对子查询的结果进行比较
1
2
3
4

例1：得到1号球队的队长的编号和姓名
SELECT playerno,NAME
  FROM players
  WHERE playerno = (
     SELECT playerno
       FROM teams
       WHERE teamno=1);

表：PLAYERS    TEAMS
字段：队长的编号(PLAYERNO) 和姓名(NAME)
条件：1号球队的队长的编号
	子查询
	SELECT PLAYERNO FROM TEAMS WHERE TEAMNO=1

	SELECT PLAYERNO,NAME FROM PLAYERS WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO=1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

例2：对于编号小于60的球员，得到他们加入俱乐部的年份和104号球员加入俱乐部的年份之间的差值
SELECT playerno,joined - (SELECT joined FROM players WHERE playerno=104)  year_interval
  FROM players
  WHERE playerno < 60;

需求分析
表：PLAYERS
字段：PLAYERNO，JOINED- (104号球员的加入俱乐部的年份)
条件：编号小于60
子查询：104号球员的加入俱乐部的年份

select playerno,joined-(select joined from PLAYERS WHERE playerno=104) as sub 
from PLAYERS where playerno<60;
1
2
3
4
5
6
7
8
9
10
11
12
13

例3：得到27和44号球员的生日
SELECT  (SELECT birth_date FROM players WHERE playerno=27) p27,
       (SELECT birth_date FROM players WHERE playerno=44) p44;

SELECT playerno,birth_date FROM PLAYERS WHERE playerno=27 OR playerno=44; 
能不使用子查询的尽量不要使用

注意：如果子查询返回空值，可能导致外部查询的where条件也为空，从而外部查询的结果集为空
1
2
3
4
5
6
7
8

例4：查询生日小于联盟队员编号为9999的球员生日的球员的编号和姓名
SELECT playerno,NAME
  FROM players
  WHERE birth_date > (
     SELECT birth_date
       FROM players
      WHERE leagueno='9999');

查询：select
表：球员表
字段：球员的编号和姓名
条件：生日小于联盟队员编号为9999的球员生日的球员
子查询：联盟队员编号为9999的球员生日

子查询
	select birth_date from PLAYERS where leagueno=9999
主查询
	select playerno,name,birth_date from PLAYERS WHERE BIRTH)DATE > (子查询的结果)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

例5：找出和7号球员住在同一个城市，并且和44号球员性别相同的每个球员的编号、城市和性别
SELECT  playerno, town, sex
  FROM  players
  WHERE (town,sex) = ((SELECT town FROM players WHERE playerno=7),
                      (SELECT sex FROM players WHERE playerno=44));

注意：(列,列,…)叫做行表达式。比较时是比较列的组合

表：PLAYERS
字段：编号、城市和性别
条件：和7号球员住在同一个城市，并且和44号球员性别相同
子查询：
	和7号球员住在同一个城市
	和44号球员性别相同
	(town,sex) = ((SELECT town FROM players WHERE playerno=7),
                      	(SELECT sex FROM players WHERE playerno=44));
		#注：这种写法更加精简
	Towm = (select town from PLAYERS where playerno=7) and sex = (select sex from PLAYERS where playerno = 44);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

行子查询(row subquery)：返回的结果集是 1 行 N 列
	使用行表达式进行比较，可以使用 = > < >= <= <>  in操作符

例6：查询和104号球员性别相同并且住在同一城市的球员的编号
SELECT playerno
  FROM players
  WHERE (sex,town) = (SELECT sex, town
                        FROM players
                        WHERE playerno=104)
  AND playerno <> 104; 

#注：<>  表示不等于
1
2
3
4
5
6
7
8
9
10
11
12

列子查询(column subquery)：返回的结果集是 N 行 1列
	必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较。其中， ANY 和 ALL 操作符不能单独使用，其前面必须加上单行比较操作符= > < >= <= <> 

	ANY	--> 和集合里所有的元素进行比较，能大于或者小于 任一一个就可以了
	ALL	--> 和集合里所有的元素进行比较，都能大于或者小于所有的	--> all 所有的
	max()
	min()
1
2
3
4
5
6
7

例8：查询至少参加了一场比赛的球员的编号和姓名
SELECT playerno,NAME
  FROM players
  WHERE playerno IN (
    SELECT playerno
      FROM matches);
#注：in	成员关系判断

表：比赛表    球员
字段：编号和姓名
条件：至少参加了一场比赛的球员
子查询：
	比赛的球员
	select distinct playerno from MATCHES --> 集合

主查询：select playerno.name from PLAYERS where playerno in (子查询集合)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

球员的名字和town，birth_date --> 查询和Wise或者Collins是老乡的球员的
	查询和Wise或者Collins是老乡的球员的名字和town，birth_date

表：球员
字段：名字和town，birth_date
条件：和Wise或者Collins是老乡
子查询：Wise或者Collins的家乡

select name,town,birth_date from PLAYERS where town in (select town from PLAYERS where name=’Wise’ or name=’Collins’) and name <> ‘Wise’ and name <> ‘Collins’;

select name,town,birth_date from PLAYERS where town in (select town from PLAYERS where name in (‘Wise’,’Collins’)) and name <> ‘Wise’ and name <> ‘Collins’;
1
2
3
4
5
6
7
8
9
10
11

例9：查询那些最老的球员的编号、姓名和生日。最老的球员指的是出生日期小于等于 所有其它球员的球员
SELECT  playerno,NAME,birth_date
  FROM  players
  WHERE  birth_date  <=ALL  (
    SELECT  birth_date
      FROM  players WHERE birth_date IS NOT NULL
  );
1
2
3
4
5
6
7

例10：查询除了最老的球员之外，所有其它球员的编号、姓名和生日 
SELECT  playerno,NAME,birth_date
  FROM  players
  WHERE  birth_date  >ANY  (
    SELECT  birth_date
      FROM  players
  );
1
2
3
4
5
6
7

SELECT PLAYERNO,AMOUNT FROM PENALTIES WHERE AMOUNT >=ALL(
SELECT DISTINCT amount FROM PENALTIES WHERE AMOUNT >40 AND AMOUNT <60)

SELECT PLAYERNO,AMOUNT FROM PENALTIES WHERE AMOUNT >=ANY(
SELECT DISTINCT amount FROM PENALTIES WHERE AMOUNT >40 AND AMOUNT <60)
1
2
3
4
5

	如果子查询的结果集中有null值，使用>ALL 和not in操作符时，必须去掉子查询结果集中的null值，否则查询结果错误

例11：找出最大的联盟会员以及相应的球员编号
SELECT leagueno,playerno
  FROM players
  WHERE leagueno >=ALL (
    SELECT leagueno
      FROM players
     WHERE leagueno IS NOT NULL);

SELECT playerno,NAME,birth_date FROM PLAYERS WHERE
birth_date <= (SELECT MIN(birth_date) FROM PLAYERS);
1
2
3
4
5
6
7
8
9
10
11
12

表子查询(table subquery)：返回的结果集是 N 行 N 列
	必须使用 IN、ANY 和 ALL 操作符对子查询返回的结果进行比较

例13：在committee_members表中，得到任职日期和卸任日期与具有Secretary职位的一行相同的所有行
SELECT *
  FROM committee_members
  WHERE (begin_date,end_date) IN (
    SELECT begin_date,end_date
       FROM committee_members
       WHERE position=‘Secretary’);
1
2
3
4
5
6
7
8
9
10

---

内联视图
	--> 中间表	还是一个临时表
	用在from子句中的子查询叫做内联视图（inline view）。它用作一个数据源，外部查询再从其中检索行
	内联视图必须要定义别名，否则出错

视图：view	可以看得到的东西 --> 是一张虚表，不是真正的存在的表，是通过其他的表查询出来的一个结果集
1
2
3
4
5
6

例14：得到编号小于10的男性球员的编号
	内联视图得到编号小于10的所有球员的编号和性别，并作为数据源
SELECT playerno
  FROM (SELECT playerno,sex
          FROM players
          WHERE playerno < 10) AS player10
  WHERE sex='M';

select playerno from PLAYERS where playerno < 10 and sex = ‘M’;
1
2
3
4
5
6
7
8
9

---

UNION

集合操作

	UNION [DISTINCT]
	UNION ALL
	union用于把两个或者多个select查询的结果集合并成一个
1
2
3
4
5

进行合并的两个查询，其SELECT列表必须在数量和对应列的数据类型上保持一致
默认会去掉两个查询结果集中的重复行
默认结果集不排序
最终结果集的列名来自于第一个查询的SELECT列表
UNION = UNION DISTINCT

例1：得到那些有罚款或者担任队长，或者两个条件都符合的球员的编号
SELECT playerno
   FROM teams
UNION
  SELECT playerno
    FROM penalties;
1
2
3
4
5
6
7
8
9
10
11
12

例3：得到那些有罚款或者担任队长，或者住在Stratford的球员的编号
SELECT playerno
  FROM teams
UNION
SELECT playerno
  FROM penalties
UNION 
SELECT playerno
  FROM players
  WHERE town='Stratford';
1
2
3
4
5
6
7
8
9
10

如果要对合并后的整个结果集进行排序，ORDER BY子句只能出现在最后面的查询中
例：
SELECT playerno
   FROM teams
UNION
SELECT playerno
   FROM penalties
   ORDER BY playerno;
1
2
3
4
5
6
7
8

UNION  ALL和UNION的区别是：前者不去掉结果集中重复的行

例：
SELECT playerno
   FROM teams
UNION ALL
  SELECT playerno
    FROM penalties;
1
2
3
4
5
6
7
8

集合运算符和NULL值
	在去重操作时，如果列值中包含NULL值，认为它们是相等的

例：最终结果集中只有1行 --> 即NULL值；NULL值会影响内容
SELECT playerno,leagueno
   FROM players
   WHERE playerno=7   
UNION
SELECT playerno,leagueno
   FROM players 
   WHERE playerno=7;
1
2
3
4
5
6
7
8
9
10
11

---

索引

常见问题：
	1 .什么是索引？
	2 .索引的好处和坏处
	3 .索引的类型 --> b+tree  hash
	4 .如何创建索引和删除索引？
	5 .如何知道查询是否走索引？
	6 .explain的作用
1
2
3
4
5
6
7

1 .什么是索引？
	全表扫描 --> 全靠运气
	索引扫描 --> 通过排好序的数据再去找内容 --> 快

	索引：index	也是一种数据，用来描述真正的数据存放的位置 --> 指针
		索引是帮助MySQL高效获取数据的数据结构
		作用：用来加快查询的速度 --> 方便查询

	mysql里的索引背后的算法是B TREE --> b+tree

	书：目录 + 内容
	目录：就是索引 --> 方便我们查找具体的知识在多少页
1
2
3
4
5
6
7
8
9
10
11
12

innodb存储引擎	文件读取	最大支持64TB
1

2 .索引的好处和坏处
	好处：查询的时候速度快
	坏处：需要额外的开销，会消耗磁盘空间，更新数据的时候，会去更新索引，会消耗很多的CPU和内存资源
1
2
3

3 .索引的类型 --> 背后的技术 b-tree、hash --> 存储层面
	innodb --> 支持b-tree
1
2

常见的类型：应用层面
	1 .主键索引 --> 创建表的时候会自动创建
	2 .复合索引 --> 多列上建立
	3 .唯一索引 --> unique
	4 .全文索引 --> fulltext 大文本内容的字段
	5 .普通索引 --> CREATE INDEX sex_idx ON t1(sex)

所有的列都可以建立索引
1
2
3
4
5
6
7
8

SHOW INDEX FROM student;		#注：查看表中的索引

主键 = not null + unique
1
2
3

在性别列上建立索引是否有价值？
	不是特别好，因为性别列只有2个值

在哪种列上建立索引是最有价值？
	有很多种不同的情况的值，非常适合建立索引 --> 方便查询
	没有重复的，高选择性的列
1
2
3
4
5
6

4 .如何创建索引和删除索引？

创建索引
	1 .建表的时候
	2 .create index

	drop index				删除索引
	show index from t1		查看表中有多少索引
1
2
3
4
5
6
7
8

例：在players表的postcode列上建一个索引
CREATE INDEX  player_pc
  ON players(postcode);
1
2
3

索引可以同时建在多个列上
例：在matches表的won和lost列上建一个复合索引
CREATE INDEX  mat_wl
  ON matches(won, lost);
1
2
3
4

例：在players表的name和initials列上建一个唯一索引
CREATE UNIQUE INDEX nameinit
  ON players(NAME, initials);
该语句执行后，就不能向表中插入列值组合完全相同的两行
1
2
3
4

在CREATE TABLE语句中创建索引
例：创建matches表，在won和lost列上建一个复合索引
CREATE TABLE matches_copy(
  matchno INTEGER PRIMARY KEY,
  teamno INTEGER NOT NULL,
  playerno INTEGER NOT NULL,
  won SMALLINT NOT NULL ,
  lost SMALLINT NOT NULL,
  INDEX mat_wl_idx (won,lost)
);
1
2
3
4
5
6
7
8
9
10

删除索引：
DROP INDEX mat_wl_idx ON matches_copy;

ALTER TABLE matches_copy DROP INDEX mat_wl_idx;
1
2
3
4

5 .如何知道查询是否走索引？ --> explain
	EXPLAIN SELECT PLAYERNO,NAME,BIRTH_DATE FROM PLAYERS WHERE PLAYERNO=6;
		--> 查看执行过程中是否使用索引
1
2
3

6 .explain的作用
SELECT PLAYERNO,NAME,BIRTH_DATE FROM PLAYERS WHERE PLAYERNO=6;
EXPLAIN SELECT PLAYERNO,NAME,BIRTH_DATE FROM PLAYERS WHERE PLAYERNO=6;
执行计划：查看SQL语句的执行过程，模拟整个执行的过程，不是真正的执行
1
2
3
4

图解MySQL索引——B-Tree（B+Tree）

索引是帮助MySQL高效获取数据的数据结构

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式
从应用层次来分：普通索引，唯一索引，复合索引
1
2
3
4

select id,name,phoneno from t2 where id = 8;
	查询语句在执行时，可能会走 主键索引

select id,name,phoneno from t2 where name = ‘cali’;
	查询语句在执行时，可能会走 复合索引
1
2
3
4
5

建立索引是对数据库进行优化 (提升了查询的效率)
非常好用的优化手段
1
2

如何给数据库做优化？

层面：
	1 .硬件优化
		cpu、内存、磁盘、网络带宽
	2 .操作系统的优化
		内核参数的优化
			--> 文件系统、内存、cpu调度、网络
			https://blog.csdn.net/u010169902/article/details/83502587
			ulimit -n 655500
			open files   (-n) 1024	一个进程能打开的文件数，默认1024
			[root@cPen ~]# ulimit -n 655500				#注：修改文件描述符的数量
			[root@cPen ~]# vim /proc/sys/vm/swappiness 	#注：交换分区
			30				#注：交换分区 (从磁盘里划分出来的空间，临时充当内存使用)
			[root@cPen ~]# echo 0 >/proc/sys/vm/swappiness	#注：什么时候开始使用交换分区
			[root@cPen ~]# vim /etc/sysctl.conf 
			vm.swappiness = 0			#注：设置swapiness参数，什么时候开始使用swap分区
			net.ipv4.ip_forward = 1		#注：开启路由功能
			net.ipv4.conf.all.rp_filter = 0
			net.ipv4.conf.default.rp_filter = 0
			vm.swappiness = 10
			kernel.sysrq = 1
			net.ipv4.neigh.default.gc_stale_time = 120
			net.ipv4.conf.all.rp_filter = 0
			net.ipv4.conf.default.rp_filter = 0
			net.ipv4.conf.default.arp_announce = 2
			net.ipv4.conf.lo.arp_announce = 2
			net.ipv4.conf.all.arp_announce = 2
			net.ipv4.tcp_max_tw_buckets = 5000
			net.ipv4.tcp_syncookies = 1
			net.ipv4.tcp_max_syn_backlog = 1024
			net.ipv4.tcp_synack_retries = 2
			[root@cPen ~]# sysctl -p	#注：让内核读取新的参数并且生效
		文件系统的优化
		网络的优化
	3 .数据库参数的优化
		https://www.cnblogs.com/gucb/p/11229607.html
		root@(none) mysql>show variables;
		[root@cPen ~]# vim /etc/my.cnf
		open_files_limit = 8192
		innodb_buffer_pool_size = 512M
		root@(none) mysql>select @@sql_mode;
	4 .创建索引
	5 .对SQL的优化
	6 .缓存
	7 .分布式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

复合索引

1 .对主键及唯一键约束，MySQL自动创建索引
2 .如果WHERE子句中包含了一个AND运算符，就可以考虑在两个列上建立复合索引
	复合索引也是b树索引，和单列索引不同的是复合索引的键值数量不是1，而是大于等于2

例如：创建表t，在a、b列上建立复合索引
DROP TABLE t;
CREATE TABLE t(
  a INT,
  b INT,
  PRIMARY KEY(a), 
  INDEX a_b_idx (a,b)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

复合索引的第二个好处：如果查询结果需要按照某个列进行排序，那么使用复合索引可以避免多做一次排序操作。例如：
CREATE TABLE buy_log(
  userid  INT UNSIGNED NOT NULL ,
  buy_date DATE) ENGINE = INNODB;
 
INSERT  INTO  buy_log  VALUES(1,'2009-01-01');
INSERT  INTO  buy_log  VALUES(2,'2009-01-01');
INSERT  INTO  buy_log  VALUES(3,'2009-01-01');
INSERT  INTO  buy_log  VALUES(1,'2009-02-01');
INSERT  INTO  buy_log  VALUES(3,'2009-03-01');
INSERT  INTO  buy_log  VALUES(1,'2009-04-01');
 
ALTER TABLE buy_log ADD KEY (userid);
ALTER TABLE buy_log ADD KEY (userid, buy_date);
两个索引中都包含user_id列

SHOW INDEX FROM buy_log				#注：查询索引信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

	如果只以userid为条件进行查询，SELECT * FROM buy_log WHERE userid=2; 两个索引都可以用，但优化器选择使用单列索引user_id，而不是复合索引，因为该索引的叶子节点只包含一个键值，所以理论上一个页能存放的索引记录应该更多，减少了io数量
	EXPLAIN  SELECT * FROM buy_log WHERE userid=2;		#注：执行计划
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
	1	SIMPLE	buy_log	\N	ref	userid,userid_2	userid_2	4	const	2	100.00	Using index
1
2
3
4

查询索引的信息

使用SHOW  INDEX  FROM语句可以查询表的相关索引的信息
SHOW INDEX FROM buy_log;
1
2
3
4

---

视图

	视图是一个逻辑表，它本身并不包含数据。通过它，可以展现基表的部分数据。用来创建视图的表叫做基表BASE  TABLE。视图是作为一个SELECT语句保存在数据字典中的

视图
	逻辑表：	其实就是假的表，不是真正存在的
			其实就是一个select语句查询出来的一个中间状态的表，里面的字段内容可能来自不同的基表
	基表：基础表	base table
			就是我们存放在数据库里的表，真正存在的表
1
2
3
4
5
6
7

视图是否是必须要使用的？
	答：不是，可以不使用视图
		可以方便其他人的操作
1
2
3

例1：创建一个视图，它包含了具有联盟会员号码的所有球员的编号和联盟会员号码
CREATE VIEW cplayers
AS SELECT playerno,leagueno
     FROM players
    WHERE leagueno IS NOT NULL; 

SELECT PLAYERNO FROM cplayers WHERE leagueno > 5000;

类似于 内联视图
SELECT PLAYERNO FROM (……) WHERE leagueno > 5000;
SELECT PLAYERNO FROM (SELECT playerno,leagueno
     FROM PLAYERS
	WHERE leagueno IS NOT NULL) AS cplayers2 WHERE leagueno > 5000;
1
2
3
4
5
6
7
8
9
10
11
12
13

视图一旦创建完毕，就可以像一个普通表那样使用。视图主要用来查询

查看视图cplayers的结构：可见，如果创建视图时不明确指定视图的列名，那么它的列名就和定义视图的select子句中的列名完全相同
	DESC cplayers;
1
2
3
4

查询视图cplayers：
	SELECT * FROM cplayers;
1
2

创建视图时，可以显式的指定视图的列名

例3：创建视图，包含了居住在stratford（斯特拉福德）的球员的编号、姓名、首字母和出生日期
CREATE VIEW stratforders(playerno,NAME,init,born)
AS SELECT playerno,NAME,initials,birth_date
     FROM players
    WHERE town='Stratford';
注意：该语法要求视图名后面的列的数量必须匹配select子句中的列的数量
1
2
3
4
5
6
7
8

创建视图，使用到多表

创建一个视图，里面有球队的等级，队长的名字，编号
表：球队、球员
字段：球队的等级，队长的名字，编号
条件：
CREATE VIEW captain AS
SELECT division,NAME,p.playerno FROM PLAYERS p JOIN TEAMS USING (playerno);

SELECT NAME FROM captain;
1
2
3
4
5
6
7
8
9
10

---

存储过程

存储过程  procedure
	存储过程就是具有名字的一段代码，完成一个特定的功能。存储过程保存在数据字典中
1
2

存储过程的创建和调用

例1：创建一个存储过程，删除给定球员参加的所有比赛
DELIMITER $$   --将语句的结束符号从分号 ; 临时改为两个$$
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    BEGIN
       DELETE FROM matches
          WHERE playerno = p_playerno;
    END$$
DELIMITER ;  -- 将语句的结束符号恢复为分号 ;

	默认情况下，存储过程和默认数据库相关联。如果想指定过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀
	在定义过程时，使用DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个$$。这使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释
1
2
3
4
5
6
7
8
9
10
11
12
13

存储过程的参数
	存储过程可以有0个或多个参数。参数有3种模式：
	IN：输入参数，表示调用者向过程传入值。可以是字面量或变量
	OUT：输出参数，表示过程向调用者传出值。只能是变量
	INOUT：输入输出参数，既表示调用者向过程传入值，又表示过程向调用者传出值。只能是变量
1
2
3
4
5

存储过程体

存储过程体包含了在过程调用时必须执行的语句。过程体总是以begin开始，以end结束。在过程体中，可以写各种语句，例如dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等
1
2
3

局部变量

在过程体中，可以声明局部变量，用来临时保存一些值
	该语句在begin end块中必须是第一条语句
	如果没有使用default关键字指定默认值，则其值为null

例：
DECLARE num1 DECIMAL(7,2);
DECLARE alpha1 VARCHAR(20);
DECLARE num2,num3 INTEGER;
1
2
3
4
5
6
7
8
9
10

例3：创建过程，为局部变量指定默认值，并调用该过程
DELIMITER $$

CREATE  PROCEDURE test(OUT num1 INTEGER)		#注：num1形参
    BEGIN     
      DECLARE num2 INTEGER DEFAULT 100;
      SET num1 = num2;
    END$$

DELIMITER ;

调用过程：
CALL  test(@num);					#注：传入num变量 到存储过程；num实参
SELECT  @num;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

SET @sg="zhangheng"					#注：@ MySQL里定义变量、引用变量
SELECT @sg
1
2

流程控制语句

条件控制
	CASE
	IF
循环控制
	ITERATE	--> continue
	LEAVE	--> break
	LOOP	--> for
	REPEAT	--> until
	RETURN
	WHILE
MySQL 不支持FOR循环
1
2
3
4
5
6
7
8
9
10
11
12
13

---

触发器

触发器  trigger
	满足某个条件后，就会触发另外的一个动作
	触发器：就像一个监控程序，一直盯着某个表，只要多DML操作，就触发
	在实际开发过程中要少用触发器
	https://www.cnblogs.com/zh-1721342390/p/9602941.html
	例子：比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志

	触发器并不是只能进行插入操作，还能执行修改，删除 --> 主要针对DML操作
1
2
3
4
5
6
7
8

---