3.入门篇：走遍天下数据库的SQL、压力测试、字符集管理(SQL)
3.入门篇：走遍天下数据库的SQL、压力测试、字符集管理(SQL)
回顾
各位朋友们好,继上一章我们就完成了用户、密码、权限创建的学习,推荐大家在进行维护时单独创建一个自己的超管用户,然后配置上相应的权限进行维护及学习管理,尽量养成此类习惯,避免直接使用root进行管理,从本章开始我们就进入到入门篇的尾声了,当然这也是最最基础的一章,SQL,本章中还涉及到Mysql中字符集的管理,预生产环境进行压力测试,还有两个小的故障问题原因解析,有SQL基础的同学可以再跟老师简单过一下,没有过学习的同学最好是从第一个SQL敲到最后一个,尽量全部过一遍,总结一份自己对函数的理解笔记,日后可以做到知其然知其所以然,这样才是对SQL理解了,当然最重要的还是在生产中如何去合理运用这些函数,这些就是经验所能带来的收获了,让我们先来开始SQL部分的学习
SQL学习
SQL组成部分：
1.select
2.字段名
3.表名
4.where条件
5.group by
6.order by
来看一条最简单的SQL：查找test表中id大于10的数据并将ID进行倒序处理
select * from test where id>10 order by id desc
SQL函数：
经常使用在where后的函数：
1.between：其结果集等于id>=1 and id<=5
mysql> select * from test where id between 1 and 5;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set
2.in / not in：在某个范围的 / 不在某个范围的值
mysql> select * from test where id in(1,2,3);
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
not in 即为非1,2,3的id数据
3.like ：通配符匹配,所有包含a的name行全部展示
mysql> select * from test where
name like '%a%';
+----+------+
| id | name |
+----+------+
| 1 | a |
+----+------+
4.regexp：正则匹配^行首 &行尾 .单个任意字符 [abc,a-c]单个字符 [^]非字符 x{3,}至少重复3次 x{3,5}最少3次最多5次(bbb,bbbbb) .*任意个任意字符
mysql> select * from test where name regexp '.*a.*
';
+----+------+
| id | name |
+----+------+
| 1 | a |
+----+------+
mysql> select * from test where name regexp '[^a
]';
+----+------+
| id | name |
+----+------+
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
+----+------+
5.逻辑判断：!逻辑非 and逻辑与 or逻辑或 xor逻辑异或(两个数值 一个有值 一个0 则返回true,两个有值/两个0返回false)
mysql> select * from test where name >'c' and name <'e'
;
+----+------+
| id | name |
+----+------+
| 4 | d |
+----+------+
6.指定行数：limit
select * from xxx limit 5; 从第一行取,取五行(默认第一行ID为0)
mysql> select * from test
limit 5;
+----+------+------+
| id | name | num |
+----+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | NULL |
| 4 | d | NULL |
| 5 | e | NULL |
+----+------+------+
select * from xxx limit 5,2 / limit 2 offset 5(5.7方式); 从第五行取,取两行(第五行的ID为4)
mysql> select * from test limit 2 offset 5
;
+----+------+------+
| id | name | num |
+----+------+------+
| 6 | f | NULL |
| 7 | | NULL |
+----+------+------+
2 rows in set
mysql> select * from test limit 5,2
;
+----+------+------+
| id | name | num |
+----+------+------+
| 6 | f | NULL |
| 7 | | NULL |
+----+------+------+
7.添加统计
group by id with rollup 会在count(*)最后一行额外加一行总值
mysql> select name,count(*)
from test group by name with rollup;
+------+----------+
| name | count(*) |
+------+----------+
| | 2 |
| a | 4 |
| b | 2 |
| c | 1 |
| d | 1 |
| e | 1 |
| f | 1 |
| NULL | 12 |
+------+----------+
8.子查询：
where id > any (select id from xxx) any代表id大于select子查询中任何一个值就返回
mysql> select * from test where id > any (select id from test where id in (1,2
));
+----+------+------+
| id | name | num |
+----+------+------+
| 2 | b | NULL |
| 3 | c | NULL |
| 4 | d | NULL |
| 5 | e | NULL |
| 6 | f | NULL |
| 7 | | NULL |
| 8 | | NULL |
| 2 | b | NULL |
| 10 | a | 1.3 |
| 11 | a | 1.5 |
+----+------+------+
where id > all (select id from xxx) all代表id大于select子查询中全部的值才能返回
mysql> select * from test where id > all (select id from test where id in (1,2
));
+----+------+------+
| id | name | num |
+----+------+------+
| 3 | c | NULL |
| 4 | d | NULL |
| 5 | e | NULL |
| 6 | f | NULL |
| 7 | | NULL |
| 8 | | NULL |
| 10 | a | 1.3 |
| 11 | a | 1.5 |
+----+------+------+
9.exists:只看后面语句是否有行返回,决定是否执行主查询语句
where id exists (select id from xxx) 如果xxx表中id有数据返回(一行或任意行),则返回true,执行查询
where id not exists (select id from xxx) 如果xxx表中id没有数据返回(0行时),则返回true,执行查询,否则不执行
mysql> select * from test where exists (select id from test where id =100);
Empty set
mysql> select * from test where exists (select id from test where id =1
);
+----+------+------+
| id | name | num |
+----+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | NULL |
| 4 | d | NULL |
| 5 | e | NULL |
| 6 | f | NULL |
| 7 | | NULL |
| 8 | | NULL |
| 1