欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql学习-查询语句

程序员文章站 2024-01-15 21:40:04
...

环境

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

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

mysql学习-查询语句
主要使用的是world database数据库下的city表
mysql学习-查询语句

查询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
相关标签: mysql