MySQL的DQL应用-select数据查询单表子句

只能专门针对表中数据行进行查询

select单独使用

select @@xxx 查看系统参数

select @@port;查看端口号
select @@basedir;查看mysql软件目录
select @@datadir;查看mysql数据目录
select @@socket;查看socket文件目录
select @@server_id;
select @@log_error;
select @@log_bin_basename;
select @@log_bin;
select @@innodb_flush_log_at_trx_commit;
show variables like 'INNODB%';模糊匹配查询，show语句是mysql封装好的视图查询功能，可以进行简单调用

select函数()

select now();系统时间
select user();当前登陆的用户
select database();当前所在的数据库
select concat('hello world');打印hello world
select user,host from mysql.user;显示mysql用户信息
select concat(user,"@",host)from mysql.user;拼接语句显示mysql用户信息
select group_concat(user,"@",host)from mysql.user;拼接语句整行显示mysql用户信息

mysql内置函数说明

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

单表子句-from

语法

select 列1,列2 from表
select * from 表

查询stu中所有的数据

select * from stu;
(不要对大表(几十~几百万行以上的)进行操作)，对性能影响极深，全表扫描IO量非常大

查询stu表中,学生姓名和入学时间

select name,intime from stu;

单表子句-where

导入数据库world.sql

world.sql下载地址：链接：https://pan.baidu.com/s/1NL5yx8U22kkv0CXf-KqO6w 提取码：1ze6 
[root@db01 ~]# mysql -uroot -p <world.sql

了解目标库表的定义结构和列的大体信息

city ===>城市
country ===>国家
countrylanguage ===>国家语言
city:城市表
ID : 城市ID
NAME : 城市名
CountryCode: 国家代码，比如中国CHN 美国USA
District : 区域
Population : 人口
use world;
show tables;
desc city;查看city表的定义结构信息
show create table city;查看city表建表语句
select * from city where id<10;查看id小于10的city数据行

语法

select 列1,列2 from table where 列n 条件;

where配合等值查询

查询中国(CHN)所有城市信息 
select * from city where countrycode='CHN'; 
查询商丘市的信息 
select * from city where name='shangqiu'; 
查询河南省所有城市信息 
select * from city where district='henan';

where配合比较操作符(> < >= <= <>)

查询世界上少于100人的城市
select * from city where population<100;

where配合逻辑运算符(and or )

中国人口数量大于500w城市 
select * from city 
where countrycode='CHN' and population>5000000;
中国或美国城市信息
select * from city 
where countrycode='CHN' or countrycode='USA';
人口在900w和1500w之间的中国或美国城市（or一般配合having使用） 
select * from city 
where countrycode='CHN' or countrycode='USA' 
having population>9000000 and population<15000000;

where配合模糊查询

查询省的名字前面带guang开头的 
select * from city 
where district like 'guang%';
(guang前面不要加%，不然性能极差。前面的%匹配不走索引，因为索引遵守最左原则，从左到右匹配。)

where配合in语句

中国或美国城市信息（性能和逻辑or一样） 
SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

where配合between and

查询世界上人口数量大于100w小于200w的城市信息 
select * from city 
where population>1000000 and population<2000000;
select * from city 
where population between 1000000 and 2000000;

单表子句-group by + 常用聚合函数

概念：按照统一的条件和特点进行分组，运用聚合函数每组进行统计

常用聚合函数

**max()** ：最大值
**min()** ：最小值
**avg()** ：平均值
**sum()** ：总和
**count()** ：个数
group_concat() : 列转行

写法规则

1.遇到统计运用聚合函数
2.形容词放在group by后面
3.名词放在函数小括号中
4.select后添加要查询的列名

统计世界上每个国家的总人口数

use world;
desc city;
select countrycode,sum(population)
from city
group by countrycode;

统计中国各个省的总人口数量

select district,sum(population)
from city
where countrycode='CHN'
group by district;

统计世界上每个国家的城市数量

select countrycode,count(name)
from city
group by countrycode;

统计世界上每个国家的城市名称

错误写法：
db01 [world]>select countrycode,name from city group by countrycode;
ERROR 1055 (42000): Expression #2 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 by中，也没有在聚合函数中包含，为了防止一对多的结构出现，mysql通过设定sql_mode=only_full_group_by参数。
正确写法：
db01 [world]>select countrycode,group_concat(name) from city group by countrycode;

统计中国每个省的城市名字列表

select district,group_concat(name)
from city
where countrycode='CHN'
group by district;

统计中国每个省的城市人口平均值

select district,avg(population)
from city
where countrycode='CHN'
group by district;

单表子句-having

having 后面只能跟聚合函数，having和where功能类似，having放在group by后面,用作后过滤。where是放在group by做前置过滤。

统计中国每个省的总人口数，只打印总人口数小于100w

select district,sum(population)
from city
where countrycode='CHN'
group by district
having sum(population)<1000000;

注意：having条件不走索引，对于性能影响的大小与前置where过滤出的数据量大小成正比，一般可以用临时表解决。

单表子句order by+limit

语法

排序功能，实现先排序，by后添加条件列，在having语句后放置。
limit不走索引，建议用where查询，limit配合order by使用，LIMIT N --->显示前M行。
1.LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5
2.limit M offset N --->显示M行，跳过N行

查看中国所有的城市，并按人口数进行排序(从大到小)

select *
from city
where countrycode='CHN'
order by population desc;(默认为asc,从小到大)

统计中国各个省的总人口数量，按照总人口从大到小排序

select district,sum(population)
from city
where countrycode='CHN'
group by district
order by sum(population) desc;

统计中国每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

select district,sum(population)
from city
where countrycode='CHN'
group by district
having sum(population)>5000000
order by sum(population) desc
limit 3;

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示第6-10名

select district,sum(population)
from city
where countrycode='CHN'
group by district
having sum(population)>5000000
order by sum(population) desc
limit 5,5;

distinct：去重复

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city ;

单表子句执行顺序

select,from,where,group by,having,order by,limit.

联合查询 union all

把两个语句结果合并成一个，性能远高于where语句,一般情况下会将where的in和or语句改写成union all,来提高性能。union是去重复，union all不去重复。

select * from city where countrycode='AHN'
union all
select * from city where countrycode='USA'