USE school;
SELECT stu_id,
stu_name,
stu_sex,
stu_birth,
stu_addr,
col_id
FROM tb_student;
SELECT stu_id AS 学号,
stu_name AS 姓名,
stu_addr AS 籍贯
FROM tb_student;
SELECT cou_name AS 课程名称,
cou_credit AS 学分
FROM tb_course;
SELECT stu_name,
stu_birth
FROM tb_student
WHERE stu_sex = 0;
SELECT stu_name,
stu_birth
FROM tb_student
WHERE stu_sex = 0
AND stu_addr = '四川成都';
SELECT stu_name,
stu_birth
FROM tb_student
WHERE stu_sex = 0
OR stu_addr = '四川成都';
SELECT stu_name,
stu_sex,
stu_birth
FROM tb_student
WHERE '1980-1-1' <= stu_birth
AND stu_birth <= '1989-12-31';
SELECT stu_name,
stu_sex,
stu_birth
FROM tb_student
WHERE stu_birth BETWEEN '1980-1-1' AND '1989-12-31';
SELECT cou_name,
cou_credit
FROM tb_course
WHERE cou_credit > 2;
SELECT cou_name,
cou_credit
FROM tb_course
WHERE cou_credit MOD 2 <> 0;
SELECT stu_id
FROM tb_record
WHERE cou_id = 1111
AND score > 90;
SELECT stu_name AS 姓名,
CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别
FROM tb_student
WHERE stu_name = '杨过';
SELECT stu_name AS 姓名,
IF(stu_sex, '男', '女') AS 性别
FROM tb_student
WHERE stu_name = '杨过';
SELECT stu_name AS 姓名,
CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别
FROM tb_student
WHERE stu_name LIKE '杨%';
SELECT stu_name AS 姓名,
CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别
FROM tb_student
WHERE stu_name LIKE '杨_';
SELECT stu_name AS 姓名,
CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别
FROM tb_student
WHERE stu_name LIKE '杨__';
SELECT stu_id,
stu_name
FROM tb_student
WHERE stu_id LIKE '%3';
SELECT stu_id,
stu_name
FROM tb_student
WHERE stu_name LIKE '%不%'
OR stu_name LIKE '%嫣%';
SELECT stu_id,
stu_name
FROM tb_student
WHERE stu_name LIKE '%不%'
UNION
SELECT stu_id,
stu_name
FROM tb_student
WHERE stu_name LIKE '%嫣%';
SELECT stu_id,
stu_name
FROM tb_student
WHERE stu_name REGEXP '[林杨][\\u4e00-\\u9fa5]{2}';
SELECT stu_name
FROM tb_student
WHERE TRIM(stu_addr) = ''
OR stu_addr is null;
SELECT stu_name
FROM tb_student
WHERE TRIM(stu_addr) <> ''
AND stu_addr is not null;
SELECT DISTINCT sel_date
FROM tb_record;
SELECT DISTINCT stu_addr
FROM tb_student
WHERE TRIM(stu_addr) <> ''
AND stu_addr is not null;
SELECT stu_name,
stu_birth
FROM tb_student
WHERE stu_sex = 1
ORDER BY stu_birth ASC;
SELECT stu_name AS 姓名,
FLOOR(DATEDIFF(CURDATE(), stu_birth) / 365) AS 年龄
FROM tb_student
WHERE stu_sex = 1
ORDER BY 年龄 DESC;
SELECT MIN(stu_birth)
FROM tb_student;
SELECT MAX(stu_birth)
FROM tb_student;
SELECT MAX(score)
FROM tb_record
WHERE cou_id = 1111;
SELECT MIN(score) AS 最低分,
MAX(score) AS 最高分,
ROUND(AVG(score), 1) AS 平均分,
STDDEV(score) AS 标准差,
VARIANCE(score) AS 方差
FROM tb_record
WHERE stu_id = 1001;
SELECT ROUND(SUM(score) / COUNT(*), 1) AS 平均分
FROM tb_record
WHERE stu_id = 1001;
SELECT CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别,
COUNT(*) AS 人数
FROM tb_student
GROUP BY stu_sex;
SELECT col_id AS 学院编号,
COUNT(*) AS 人数
FROM tb_student
GROUP BY col_id
WITH ROLLUP;
SELECT col_id AS 学院编号,
CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别,
COUNT(*) AS 人数
FROM tb_student
GROUP BY col_id, stu_sex;
SELECT stu_id AS 学号,
ROUND(AVG(score), 1) AS 平均分
FROM tb_record
GROUP BY stu_id;
SELECT stu_id AS 学号,
ROUND(AVG(score), 1) AS 平均分
FROM tb_record
GROUP BY stu_id
HAVING 平均分 >= 90;
SELECT stu_id AS 学号,
ROUND(AVG(score), 1) AS 平均分
FROM tb_record
WHERE cou_id in (1111, 2222, 3333)
GROUP BY stu_id
HAVING 平均分 >= 90
ORDER BY 平均分 ASC;
SELECT stu_name
FROM tb_student
WHERE stu_birth = (SELECT MIN(stu_birth)
FROM tb_student);
SELECT stu_name
FROM tb_student
WHERE stu_id in (SELECT stu_id
FROM tb_record
GROUP BY stu_id
HAVING COUNT(*) > 2);
SELECT stu_name,
stu_birth,
col_name
FROM tb_student AS t1, tb_college AS t2
WHERE t1.col_id = t2.col_id;
SELECT stu_name,
stu_birth,
col_name
FROM tb_student INNER JOIN tb_college
ON tb_student.col_id = tb_college.col_id;
SELECT stu_name,
stu_birth,
col_name
FROM tb_student NATURAL JOIN tb_college;
SELECT stu_name,
stu_birth,
col_name
FROM tb_student CROSS JOIN tb_college;
SELECT stu_name,
cou_name,
score
FROM tb_student, tb_course, tb_record
WHERE tb_student.stu_id = tb_record.stu_id
AND tb_course.cou_id = tb_record.cou_id
AND score is not null;
SELECT stu_name,
cou_name,
score
FROM tb_student
INNER JOIN tb_record
ON tb_student.stu_id = tb_record.stu_id
INNER JOIN tb_course
ON tb_course.cou_id = tb_record.cou_id
WHERE score is not null;
SELECT stu_name,
cou_name,
score
FROM tb_student
NATURAL JOIN tb_record
NATURAL JOIN tb_course
WHERE score is not null;
SELECT stu_name,
cou_name,
score
FROM tb_student
NATURAL JOIN tb_record
NATURAL JOIN tb_course
WHERE score is not null
ORDER BY cou_id ASC, score DESC
LIMIT 5;
SELECT stu_name,
cou_name,
score
FROM tb_student
NATURAL JOIN tb_record
NATURAL JOIN tb_course
WHERE score is not null
ORDER BY cou_id ASC, score DESC
LIMIT 5
OFFSET 5;
SELECT stu_name,
cou_name,
score
FROM tb_student
NATURAL JOIN tb_record
NATURAL JOIN tb_course
WHERE score is not null
ORDER BY cou_id ASC, score DESC
LIMIT 10, 5;
SELECT stu_name,
avg_score
FROM tb_student
NATURAL JOIN (SELECT stu_id,
ROUND(AVG(score), 1) AS avg_score
FROM tb_record
GROUP BY stu_id) as tmp;
SELECT stu_name,
total
FROM tb_student
NATURAL JOIN (SELECT stu_id,
COUNT(*) AS total
FROM tb_record
GROUP BY stu_id) as tmp;
SELECT stu_name AS 姓名,
COALESCE(total, 0) AS 选课数量
FROM tb_student AS t1
LEFT JOIN (SELECT stu_id,
COUNT(*) AS total
FROM tb_record
GROUP BY stu_id) AS t2
ON t1.stu_id = t2.stu_id;