/ 2019-05-23
更多内容请关注公众号:老男孩Linux
7. DQL应用(select )
-- select @@xxx 查看系统参数
SELECT @@port;
SELECT @@basedir;
SELECT @@datadir;
SELECT @@socket;
SELECT @@server_id;
-- select 函数();
NOW();
SELECT DATABASE();
SELECT USER();
SELECT CONCAT("hello world");
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
7.2 单表子句-from
SELECT 列1,列2 FROM 表
SELECT * FROM 表
例子:
-- 查询stu中所有的数据(不要对大表进行操作)
SELECT * FROM stu ;
-- 查询stu表中,学生姓名和入学时间
SELECT sname , intime FROM stu;
=====================
oldguo带大家学单词:
world ===>世界
city ===>城市
country ===>国家
countrylanguage ===>国家语言
city:城市表
DESC city;
ID : 城市ID
NAME : 城市名
CountryCode: 国家代码,比如中国CHN 美国USA
District : 区域
Population : 人口
SHOW CREATE TABLE city;
SELECT * FROM city WHERE id<10;
SELECT col1,col2 FROM TABLE WHERE colN 条件;
例子:
-- 查询中国(CHN)所有城市信息
SELECT * FROM city WHERE countrycode='CHN';
-- 查询北京市的信息
SELECT * FROM city WHERE NAME='peking';
-- 查询甘肃省所有城市信息
SELECT * FROM city WHERE district='gansu';
例子:
-- 查询世界上少于100人的城市
SELECT * FROM city WHERE population<100;
例子:
-- 中国人口数量大于500w
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
-- 中国或美国城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
例子:
-- 查询省的名字前面带guang开头的
SELECT * FROM city WHERE district LIKE 'guang%';
注意:%不能放在前面,因为不走索引.
-- 中国或美国城市信息
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;
根据 by后面的条件进行分组,方便统计,by后面跟一个列或多个列
**max()** :最大值
**min()** :最小值
**avg()** :平均值
**sum()** :总和
**count()** :个数
group_concat() : 列转行
d
例子1:统计世界上每个国家的总人口数.
USE world
SELECT countrycode ,SUM(population) FROM city GROUP BY countrycode;
例子2: 统计中国各个省的总人口数量(练习)
SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district;
例子3:统计世界上每个国家的城市数量(练习)
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
where|group|having
例子4:统计中国每个省的总人口数,只打印总人口数小于100
SELECT district,SUM(Population)
FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(Population) < 1000000 ;
实现先排序,by后添加条件列
7.6.2.1 查看中国所有的城市,并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
7.6.2.2 统计中国各个省的总人口数量,按照总人口从大到小排序
SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;
7.6.2.3 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
SELECT district, SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;
LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5
SELECT district, SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city ;
-- 中国或美国城市信息 (2)
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'