常用的 sql 语句

2.3.1 聚合函数

1. COUNT()函数

使用 COUNT 函数统计 tb_student 表中的记录数

SELECT COUNT(*) FROM tb_student;

2. SUM()函数

统计 tb_score 表中分数字段（score）的总和

SELECT SUM(score) FROM tb_score;

3. AVG()函数

某个字段取值的平均值

SELECT AVG(score) FROM tb_score;

4. MAX(),MIN()函数

查询 tb_score 表中分数字段（score）的最大值

SELECT MAX(score) FROM tb_score;

2.3.2 数学函数

1. ABS()函数

返回一个数的绝对值

SELECT ABS(5),ABS(-5);

2. FLOOR()函数

向下取整

SELECT FLOOR(1.5),FLOOR(-2);

3. RAND()函数

随机数(0~1)之间

SELECT RAND(),RAND();

4. TRUNCATE()函数

截取指定小数位数

保留两位小数

SELECT TRUNCATE(123.456789,2);

5. SQRT()函数

参数平方根

SELECT SQRT(16),SQRT(25);

2.3.3 字符串函数

1. UPPER(s)函数 UCASE(s)函数

均可实现将参数转化为大写

SELECT UPPER('hello'),UCASE('hello');

2. LEFT(s,n)函数

返回字符串 s 的前 n 个字符

SELECT LEFT("hello",2);

3. SUBSTRING(s,n,m)函数

返回字符串 s 的从第 n 个字符开始的 m 个字符

SELECT SUBSTRING("hello world",2,6);

2.3.4 日期和时间函数

1. CURDATE()函数，CURRENT_DATE()函数

均可返回当前日期

SELECT CURDATE(),CURRENT_DATE();

2. CURTIME()函数，CURRENT_TIME()函数

均可返回当前时间

SELECT CURTIME(),CURRENT_TIME();

3. NOW()函数

均可返回当前日期和时间

SELECT NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP();

2.3.5 其他函数

1. IF(expr,v1,v2)函数

如果表达式 expr 成立，则执行 v1 ，否则执行 v2。

如果分数字段 （score） 大于 85 则返回 “优秀”，否则返回 “一般”。 结果在 level 字段中显示

SELECT studentNo,courseNo,score,IF(score>85,'优秀','一般') level FROM tb_score;

2. IFNULL(v1,v2)函数 函数返回 v1 ，除非 v1 为 NULL ，那么返回 v2。

SELECT IFNULL(1/0,'空');

4.2.1

1. 查询指定字段

SELECT classNo,department,classNum FROM tb_class;
SELECT department FROM tb_class;

去掉重复记录 distinct

SELECT DISTINCT department FROM tb_class;

2. 查询所有字段

SELECT * FROM tb_student;

等价于

SELECT studentNo，studentname, sex, birthday, native, nation, classNo, FROM tb_student;

3. 查询经过计算的值

SELECT studentName, sex, 'Age:',YEAR(NOW()) - YEAR(birthday) FROM tb_student;

4. 定义字段别名

SELECT studentName AS '姓名', sex '性别', YEAR(NOW()) - YEAR(birthday) '年龄' FROM tb_student;

4.2.2 选择指定记录

1. 比较大小

SELECT courseName, credit, courseHour FROM tb_course WHERE courseHour>=48;

SELECT studentName, sex, native, nation FROM tb_student WHERE nation != "汉族" AND nation != "汉";

SELECT studentName, sex, birthday FROM tb_student WHERE birthday BETWEEN '1997-01-01' AND '1997-12-31';

SELECT studentName, sex, birthday FROM tb_student WHERE birthday NOT BETWEEN '1997-01-01' AND '1997-12-31';

SELECT * FROM tb_student WHERE native IN ('北京','上海','天津');

SELECT * FROM tb_student WHERE studentNo='2013110201';
SELECT * FROM tb_student WHERE studentNo LIKE '2013110201';

"%"（百分号） 代表任意长度的字符串，甚至包括长度为零的字符串

SELECT * FROM tb_student WHERE studentName LIKE '王%';
SELECT * FROM tb_student WHERE studentName LIKE '%志%';

"_"（下横线） 代表任意单个字符

匹配 “王小二”

SELECT * FROM tb_student WHERE studentName LIKE '王__';

匹配“信息系统_分析 y 与设计”

如果要匹配的字符串本身就含有通配符 "%" ,"",这时候就需要使用 ESCAPE '<换码字符>' 短语对通配符进行转义， 把通配符 "%" ,"" 转换成普通字符。

SELECT * FROM tb_course WHERE courseName LIKE '%#_%' ESCAPE '#';

正则

SELECT * FROM tb_course WHERE courseName REGEXP '管理|信息|系统';

IS NULL

-- SELECT * FROM tb_course WHERE priorCourse IS NULL;

SELECT * FROM tb_course WHERE priorCourse IS NOT NULL;

带 AND 或 OR 的多条件查询

SELECT courseName, credit, courseHour FROM tb_course WHERE credit >= 3 AND courseHour > 32;

SELECT studentName, native, nation FROM tb_student WHERE native='北京' OR native='上海' ;

对查询结果排序

SELECT studentName, native,nation FROM tb_student ORDER BY studentName;

先按照学号升序，再按照成绩降序（关键词 ASC，DESC，默认 ASC 升序，DESC 降序）

SELECT * FROM tb_score WHERE score > 85 ORDER BY studentNo,score DESC;

LIMIT 2 是指第三条记录对应的位置偏移量，3 是指要返回的记录数，即第 3 至 第 5 条记录

SELECT studentNo,courseNo, score FROM tb_score ORDER BY score DESC LIMIT 2,3;

4.3.1 使用聚合函数查询

查询学生总人数

SELECT COUNT(*) FROM tb_student;

计算选修课程编号为 “21001” 的学生平均成绩

SELECT AVG(score) FROM tb_score WHERE courseNo = '21001';

计算选修课程编号为 “21001” 的学生最高分

SELECT MAX(score) FROM tb_score WHERE courseNo = '21001';

查询各个课程号及相应的选课人数

SELECT courseNo, COUNT(studentNo) FROM tb_score GROUP BY courseNo;

HAVING 过滤分组

SELECT AVG(score) 平均分 FROM tb_score HAVING AVG(score) >= 80;

4.4.1 连接查询

如果一个查询同时涉及两个或多个表，则称之为连接查询。

查询每个学生选修课程的情况

SELECT tb_student.*, tb_score.* FROM tb_student,tb_score WHERE tb_student.studentNo = tb_score.studentNo;

查询会计学院全体同学的学号，姓名，籍贯，班级编号和所在的班级名称

在连接操作中，如果 SELECT 语句涉及多个表相同字段名（如 classNo）,必须在相同的字段名前加上表名（如 tb_student）加以区分

SELECT
	studentNo,
	studentName,
	native,
	tb_student.classNo,
	className
FROM
	tb_student,
	tb_class
WHERE
	tb_student.classNo = tb_class.classNo
	AND department = '会计学院';

查询了选修课程名称为 "程序设计"的学生学号，姓名和成绩

SELECT
	a.studentNo,
	studentName,
	score
FROM
	tb_student AS a,
	tb_course b,
	tb_score c
WHERE
	a.studentNo = c.studentNo
	AND b.courseNo = c.courseNo
	AND courseName = "程序设计";