1、单独使用

1.1、select @@xxx 查看系统参数

MySQL 特有

  1. select @@server_id;
  2. select @@server_uuid;
  3. select @@port;
  4. select @@basedir;
  5. select @@datadir;
  6. select @@socket;
  7. select @@innodb_flush_log_at_trx_commit;
  9. #替代方法
  10. #查看所有系统参数
  11. show variables;
  13. #模糊匹配
  14. show variables like '%innodb%';

1.2、select 函数

#MySQL特有,与其它数据库产品不兼容
select database();
select now();
select user();

#兼容语法
select database() from dual;
select now() from dual;
select user() from dual;

#查看所有内置函数
help functions;

#运维高频使用函数: concat()
select concat("hello world");
select concat(user,host) from mysql.user;
select concat(user,"@",host) from mysql.user;
select concat (user,"@",host) as "数据库用户" from mysql.user;

#开发

2、单表子句

单表 select子句执行顺序

select     列
from       表
where      条件
group by   列
having     条件
order by   列
limit      条件

2.1、学习业务

world            ===>世界
city             ===>城市
country          ===>国家
countrylanguage  ===>国家语言

city: 城市表
DESC city;
ID:          城市ID
NAME:        城市名
CountryCode: 国家代码，比如中国CHN 美国USA
District:    区域
Population : 人口


#学习业务
1. comment
2. desc
3. E-R关系图
论 comment的重要性

SHOW CREATE TABLE city;
SELECT * FROM city WHERE id<10;

2.2、from 子句

select id,name,countrycode,district,population;
from world.city;

或者

select *
from city;

#不要对大表进行

2.3、where 子句

#查询中国(CHN)所有城市信息
select *
from city
where countrycode = 'CHN';

#查询人口小于100的城市信息
select *
from city
where population < 100;

2.3.1、where 配合逻辑连接符, 实现多条件过滤

#逻辑连接符
and、or、between an

#查询中国人口超过500w的所有城市信息
select *
from city
where countrycode = 'CHN' and population > 5000000;

#查询中国 或 美国的城市信息
select *
from city
where countrycode = 'CHN' or countrycode = 'USA';

或者

select *
from city
where countrycode in ('CHN','USA');

#查询人口为100w到200w, 包括两头, 城市信息
select *
from city
where population >= 1000000 and population <= 2000000;

或者

select *
from city
where population between 1000000 and 2000000;

#查询中国 或美国, 人口大于500w的城市
select *
from city
where (countrycode = 'CHN' or countrycode = 'USA') and population > 5000000;

或者

select *
from city
where countrycode in ('CHN','USA') and population > 5000000;

2.3.2、where 配合 like 语句实现模糊查询

#查询城市名 qing开头的城市信息
select *
from city
where name like 'qing%';

#查询城市名包含 qing的城市信息 
select *
from city
where name like '%qing%';

注意:
%不能放在前面, 因为不走索引, 如果业务中有很多需求, ES可以很好解决
mysql支持正则表达式匹配, 但是慎用, 因为不走索引

2.3.3、group by + 聚合函数

image.png

#聚合函数
count()            统计数量
sum()              求和
avg()              平均数
max()              最大值
min()              最小值
group_concat()     列转行

#group by 分组功能原理
1. 按照分组条件进行排序
2. 进行分组字段的去重
3. 聚合函数将其它列的结果进行聚合

#保证 group by的准确性
sql_mode=only_full_group
5.6: 取第一列
5.7: 报错

#搭配方式
group by 配合聚合函数使用
聚合函数可以单独使用

#统计city表的行数
select count(*) 
from city;

#统计中国城市个数
select count(*)
from city
where countrycode = 'CHN';

#统计中国总人口
select sum(population)
from city
where countrycode = 'CHN';

#统计每个国家的城市个数
select countrycode,count(name)
from city
group by countrycode;

#统计每个国家的总人口
select countrycode,sum(population)
from city
group by countrycode;

#统计中国每个省的城市个数,和总人口数
select district,count(name),sum(population)
from city
where countrycode = 'CHN'
group by district;

#统计中国每个省的城市个数及城市名列表
select district,count(name),`name`
from city
where countrycode = 'CHN'
group by district;

#5.7版本增加了 sql_mode=only_full_group_by限制,在 5.7会报错, 5.6取第一行数据, 结果不准确
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#使用group_concat(字段), 将多行的结果, 显示成一行
select district,count(name),group_concat(`name`)
from city
where countrycode = 'CHN'
group by district;

#面试题: 介绍sql_mode作用, only_full_group_by参数作用
1. sql_mode 是保证SQL语句执行行为, 能搞满足常识和科学逻辑, 
2. 例如日期不能为0, 除数不能为0, 通过sql_mode参数控制
3. only_full_group_by参数对group_by语句的限制
(1). selct list 没有做group_by操作
(2). 也没有聚合函数
(3). 从原理上讲: 如果不做限制, 就会出现一行对多行的情况

2.3.4、having 子句

having: 后过滤, 主要用在 gruop by 之后需要判断

#where 与 having 区别
where: 出现在group by 之前, 用于前过滤
having: 出现在 group by 之后, 用户聚合函数判断
having 性能影响: having 不走索引, 结果集小可以用, 结果集较大慎用

#统计每个国家的总人口数, 只显示总人口超过1亿人的信息
select countrycode,sum(population)
from city
group by countrycode
having sum(population) > 100000000;

2.3.5、order by 子句

#select 子句执行顺序
select
select list  :4
from         :1
where        :2 
grop by      :3 
having       :5
order by     :6
limit        :7

升序:asc
降序:desc

#查询所有城市信息, 并按照人口数排序输出 (升序)
select *
from city
order by population;

#查询中国所有的城市信息, 并按照人口数从大到小排序输出
select *
from city
where countrycode = 'CHN'
order by population desc;

#查询每个国家的总人口数,总人口超过5000w的信息,并按总人口数从大到小排序输出
select countrycode,sum(population)
from city
group by countrycode
having sum(population) > 50000000
order by sum(population) desc;

#不建议group by、having、oder by 在 MySQL处理(不走索引), 建议 mysql where范围查找, 将数据放在应用程序处理

2.3.6、limit 子句

#limit 一般结合order by 使用, 才有意义
limit m,n: 跳过m行, 显示n行
limit n, offset m: 跳过m行, 显示n行

#查询中国所有的城市信息, 并按照人口数从大到小排序输出, 只显示前十名
select *
from city
where countrycode = 'CHN'
order by population desc
limit 10;

#6 - 10名
select *
from city
where countrycode = 'CHN'
order by population desc
limit 5,5;

或者

select *
from city
where countrycode = 'CHN'
order by population desc
limit 5 offset 5;

#limit性能影响 (大表)
1. limit 500w,10, 会造成 500w条数据扫描代价
2. 可使用 where条件, 过滤到准确范围, 之后limit 10, 获取后面10行数据, 减少扫描代价

select *
from city
order by population desc
limit 5000000,10;

改写条件: 假设id从1开始自增长

select *
from city
where id = 5000000
order by population desc
limit 10;