mysql查询语句使用

#=常用函数===

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%’