mysql学习-查询语句

环境

使用sqlyog
使用的mysql官方的2个开源 实例 数据库
下载地址

https://dev.mysql.com/doc/index-other.html

主要使用的是world database数据库下的city表

查询mysql语句

#查询
SELECT NAME FROM city;#查询name列
SELECT * FROM city;#查询所有列 *效率低，可读性差
SELECT NAME ,Population*12 FROM city;#数据可以进行运算
SELECT NAME ,Population*12 AS ‘人口12倍‘ FROM city;#用as为运算后的列起名字
SELECT DISTINCT Population FROM city;#DISTINCT去除重复的数据，保留唯一性
SELECT DISTINCT Population FROM city ORDER BY Population ASC;#desc 降序排序 asc升序排序
SELECT DISTINCT Population,B FROM city ORDER BY Population ASC,B ASC;#Population数据相同时按B的升序来排列
SELECT DISTINCT Population FROM city WHERE Population=300;#where 条件 = > <  != <>最后2个都是不等于
SELECT DISTINCT Population FROM city WHERE Population!=300;
SELECT DISTINCT Population FROM city WHERE Population<>300;
SELECT  NAME,Population FROM city WHERE NAME='Fakaofo' AND Population=300;#and or not 
SELECT Population FROM city WHERE Population BETWEEN 300 AND 20000;
SELECT Population FROM city WHERE Population>=300 AND Population<=20000;#和上面等价
SELECT Population FROM city WHERE Population IS NOT NULL;#查询null
SELECT Population FROM city WHERE Population IN(300,5200,2345);#枚举查询 效率低 可用多个and代替
#模糊查询
SELECT Population FROM city WHERE Population LIKE _%;# _表示单个任意字符 %表示任意长度的任意字符
#分支查询
SELECT Population,NAME,
CASE 
	WHEN Population<10000 THEN 'A'
	WHEN Population>=10000 AND Population<=40000 THEN 'B'
	ELSE 'C'
END AS 'level'
FROM city ORDER BY LEVEL ASC;
#时间查询
SELECT SYSDATE() AS TIME;
SELECT CURDATE() AS DATE;
SELECT CURTIME() AS '时分秒';
SELECT WEEK(SYSDATE()) AS 一年中的第几周;
SELECT YEAR(SYSDATE()) AS 日期中的年份;
SELECT HOUR(SYSDATE()) AS  小时值;
SELECT MINUTE(SYSDATE()) AS  分钟值;
SELECT DATEDIFF('2020-4-5','2020-4-1') AS 相隔天数;#指定日期间的相隔天数
SELECT ADDDATE('2020-4-1',4) AS 隔n天后的日期;#n天后的日期
#字符串查询
SELECT CONCAT('My','s','q','l') AS 拼接结果;#字符串拼接
SELECT CONCAT(NAME,population) AS 拼接结果 FROM city;
#insert(str,pos,len,newstr) 将set中pos位置后的len长度字符替换成newstr 数据库中下标从1开始
SELECT INSERT('这是一个数据库',3,2,'mysql');#2代表’一个‘，替换成了mysql
SELECT LOWER('POWER');
SELECT UPPER('power');
#SUBSTRING(str,num,len) 将str指定num位置开始截取len个内容
SELECT SUBSTRING('i love mysql',3,6);
#聚合函数
SELECT SUM(Population) AS 所有人口 FROM city;
SELECT AVG(Population) AS 平均值 FROM city;
SELECT MAX(Population) AS MAX FROM city;
SELECT MIN(Population) AS MIN FROM city;
SELECT COUNT(Population) AS 行数 FROM city;#聚合函数会自动忽略null值
#分组查询
SELECT IsOfficial,SUM(Percentage) FROM countrylanguage GROUP BY IsOfficial;#分组求和 按IsOfficial的值分组
SELECT CountryCode,IsOfficial,SUM(Percentage) FROM countrylanguage GROUP BY CountryCode,IsOfficial;#多列分组
#分组查询中 select显示的列只能是分组依据列，聚合函数列 不可是其他的列
#分组过滤查询
SELECT CountryCode,SUM(Percentage) FROM countrylanguage GROUP BY CountryCode HAVING CountryCode='ZWE';#多列分组
#限定查询 limit 起始行 查询行数 起始行从0开始
SELECT * FROM city LIMIT 0,5;
SELECT * FROM city LIMIT 5,5;#一般用来分页 起始行变，行数不变
#语句编写顺序 
#select 列名 from 表名 where 条件 group by 分组 having 过滤条件 order by 排序(desc/asc) limit 起始行,总行数
#执行顺序 
#from 	where 	group by  having    select    order by    limit