#=常用函数===
SELECT MAX(score) FROM student_info
SELECT MIN(score) FROM student_info;
SELECT AVG(score) FROM student_info;
#求和
SELECT SUM(score) FROM student_info;
#统计条数
SELECT COUNT(*) FROM student_info;
#统计某个字段数量
SELECT COUNT(username) FROM student_info;
SELECT NOW() AS cur_time;
#只获取 时分秒
SELECT CURTIME();
#获取系统年月日
SELECT CURDATE();
#向上去舍
SELECT CEIL(2.3);
#向下取舍
SELECT FLOOR(2.3);
SELECT RAND();
#查询某几条字段
SELECT * FROM student_info WHERE id IN(1,11,14);
#查询处记录并排序 asc 升序
SELECT * FROM student_info WHERE id NOT IN(2) ORDER BY score ASC;
3.20
#===分组查询
#查询类别
SELECT student_id FROM id_subjects GROUP BY student_id;
SELECT * FROM id_subjects GROUP BY student_id;
SELECT * FROM id_subjects GROUP BY student_id HAVING score=100
#分页查询
第一页
SELECT * FROM student_info LIMIT 0,5;
第2页
SELECT * FROM student_info LIMIT 5,5;
第3页
SELECT * FROM student_info LIMIT 10,5;
#===多表查询
#子查询
SELECT * FROM student_info WHERE id IN(1,11,13)
SELECT MAX(score) AS top,username FROM student_info;
SELECT MAX(score) username FROM student_info;
SELECT MIN(score) FROM student_info;
SELECT username FROM student_info WHERE
#同时查询多张 表
SELECT u.username ,temp.maxScore FROM student_info u,
(SELECT MAX(score) AS maxScore FROM student_info) temp
WHERE u.score=temp.maxScore
SELECT * FROM id_subjects s,subjects temp WHERE s.course_num=temp.course_num
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp WHERE s.course_num=temp.course_num
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id=011118239
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id IN(011118239,011118234)
#联合查询
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id=011118239
UNION
SELECT s.student_id,temp.subject_name FROM id_subjects s,subjects temp
WHERE s.course_num=temp.course_num AND s.student_id=011118234
#=模糊查询========
SELECT * FROM subjects WHERE teacher LIKE ‘Mrs%’
SELECT * FROM subjects WHERE teacher LIKE ‘%in’
SELECT * FROM subjects WHERE teacher LIKE ‘%Li%’