SQL 查询语法以及函数
SELECT * FROM student;
SELECT name,score FROM student;
SELECT name as 姓名,score as 分数 FROM student;
SELECT name as 姓名,age as 年龄 FROM student WHERE age >= 18;
SELECT name as 姓名, class as 班级, score as 分数 FROM student WHERE gender=’男’ and score >= 90;
– 查询名字以“王”开头的学生
SELECT * from student WHERE name like ‘王%’;
SELECT * from student WHERE class IN (‘一班’,’二班’);
SELECT * from student WHERE class NOT IN (‘一班’,’二班’);
SELECT * from student WHERE age BETWEEN 18 AND 20;
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5;
– 跳过5条，限制展示 5 条
SELECT * FROM student LIMIT 5,5;
– order by 指定根据 score 升序排列，如果 score 相同再根据 age 降序排列
SELECT name,score,age FROM student ORDER BY score ASC, age DESC;
– 统计每个班级的平均成绩
SELECT class as 班级, AVG(score) AS 平均成绩 FROM student GROUP BY class ORDER BY 平均成绩 DESC;
– 查询每个班级的学生人数
SELECT class, COUNT(*) as count from student GROUP BY class;
SELECT class, AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score > 90;
– DISTINC 去重
SELECT DISTINCT class from student;
– 聚合函数：AVG,COUNT,SUM,MIN, MAX
SELECT class AS 班级, AVG(score) AS 平均成绩,COUNT(*) AS 人数,SUM(score) AS 总成绩,MIN(score) AS 最低分,MAX(score) AS 最高分 FROM student GROUP BY class;
– 字符串处理函数： CONCAT,SUBSTR,LENGTH,UPPER,LOWER
– substr 第二个参数表示开始的下标（mysql 下标从 1 开始）
SELECT CONCAT(‘xx’,name,’yy’), SUBSTR(name,2,3),LENGTH(name),UPPER(‘aa’),LOWER(‘TT’) FROM student;
– 数值函数：ROUND 四舍五入、CEIL 向上取整、FLOOR 向下取整、ABS 绝对值、MOD 取模
SELECT ROUND(1.234567,2),CEIL(1.234567),FLOOR(1.234567),ABS(-1.233),MOD(5,2);
– 日期函数：DATE,TIME,YEAR,MONTH,DAY
SELECT YEAR(‘2006-01-02 15:04:05’),MONTH(‘2006-01-02 15:04:05’),DAY(‘2006-01-02 15:04:05’),DATE(‘2006-01-02 15:04:05’),TIME(‘2006-01-02 15:04:05’);
– 条件函数：根据条件是否成立返回不同的值。比如 if case
SELECT name, IF(score >= 60,’及格’,’不及格’) from student;
SELECT name,
score,
CASE
WHEN score >= 90 THEN
‘优秀’
WHEN score >= 60 THEN
‘及格’
ELSE
‘差’
END AS ‘档次’
FROM
student;
– 系统函数：用于获取系统信息，比如 VERIOSN,DATABASE,USER
SELECT VERSION(),DATABASE(),USER();
– 其他函数：NULLIF,COALESCE,GREATEST,LEAST
– NULLIF,如果相等返回第一个值，不相等返回null
SELECT NULLIF(1,1),NULLIF(1,2);
– COALESCE,返回第一个非 null 的值
SELECT COALESCE(null,1), COALESCE(null,null,2);
– GREATEST、LEAST:返回几个值中最大 最小的。
SELECT GREATEST(1,2,3),LEAST(1,2,3,4);
– 类型转换函数：转换类型为另一种，比如 CAST、CONVERT、DATE_FORMAT、STR_TO_DAT
– 将 ‘123’ 转化为 整型3
SELECT GREATEST(1,CONVERT(‘123’,signed),3);
SELECT GREATEST(1,CAST(‘123’ AS signed),3);
SELECT DATE_FORMAT(‘2022-01-01’,’%y年%m月%d日’);
SELECT STR_TO_DATE(‘2025-06-06’,’%Y-%m-%d’);