表详情:
学生表:
学院表:
学生选课记录表:
课程表:
教师表:
查询:
1. 查全表
-- 01. 查询所有学生的所有信息
-- 方法一:会更复杂,进行了两次查询,第一次查询这个表的列,第二次根据查到的列再查每列的数据
select * from teachers;
-- 方法二:直接查询该实体所有属性
select stu_id
, stu_name
, stu_sex
, stu_birth
, stu_addr
, col_id
from students;
2. 投影(select)和别名(alias / as)
-- 02. 查询学生的学号、姓名和籍贯(投影和别名) - 别名:alias
select stu_id as 学号
, stu_name as 姓名
, stu_addr as 籍贯
from students;
-- 03. 查询所有课程的名称及学分(投影和别名)
select cou_name as 名称
, cou_credit as 学分
from courses;
3. 数据筛选(where 加 and / or 加 分支 加 比较符)
-- 04. 查询所有女学生的姓名和出生日期(数据筛选)
select stu_name
, stu_birth
from students
where stu_sex = 0;
-- 05. 查询籍贯为“四川成都”的女学生的姓名和出生日期(数据筛选)
select stu_name
, stu_birth
from students
where stu_sex = 0 and stu_addr = '四川成都';
-- 06. 查询籍贯为“四川成都”或者性别是女的学生(数据筛选)
select stu_name
, stu_birth
from students
where stu_sex = 0 or stu_addr = '四川成都';
-- 07. 查询所有80后学生的姓名、性别和出生日期(数据筛选)
select stu_name
, stu_sex
, stu_birth
from students
where stu_birth >= '1980-1-1' and stu_birth <= '1989-12-31';
select stu_name
, stu_sex
, stu_birth
from students
where stu_birth between '1980-1-1' and '1989-12-31';
-- 将01改为男女
-- MySQL方言
select stu_name as 姓名
, if(stu_sex,'男','女') as 性别
, stu_birth as 出生日期
from students
where stu_birth between '1980-1-1' and '1989-12-31';
-- 标准SQL
select stu_name as 姓名
, case stu_sex when 1 then '男' when 0 then '女' else '未知' end as 性别
from students
where stu_birth between '1980-1-1' and '1989-12-31';
-- 08. 查询学分大于2分的课程名称和学分(数据筛选)
select cou_name
, cou_credit
from courses
where cou_credit > 2;
-- 09. 查询学分是奇数的课程的名称和学分(数据筛选)
select cou_name
, cou_credit
from courses
where cou_credit%2 <> 0;
-- 10. 查询选择选了1111的课程考试成绩在90分以上的学生学号(数据筛选)
select stu_id
from records
where cou_id = 1111 and score > 90;
-- 11. 查询名字叫“杨过”的学生的姓名和性别
select stu_name
, stu_sex
from students
where stu_name = '杨过';
4.模糊查询(通配符和正则)
-- 12. 查询姓“杨”的学生姓名和性别(模糊查询)
-- wild card - 通配符 - % - 零个或任意多个字符
select stu_name
, stu_sex
from students
where stu_name like '杨%';
-- 13. 查询姓“杨”名字两个字的学生姓名和性别(模糊查询)
-- wild card - 通配符 - _ - 精确匹配一个字符
select stu_name
, stu_sex
from students
where stu_name like '杨_';
-- 14. 查询姓“杨”名字三个字的学生姓名和性别(模糊查询)
select stu_name
, stu_sex
from students
where stu_name like '杨__';
-- 15. 查询名字中有“不”字或“嫣”字的学生的姓名(模糊查询)
select stu_name
from students
where stu_name like '%不%' or stu_name like '%嫣%';
select stu_name
from students
where stu_name regexp '[\\u4e00-\\u9fa5]*?[不嫣][\\u4e00-\\u9fa5]*?';
select stu_name
from students
where stu_name like '%不%'
union
select stu_name
from students
where stu_name like '%嫣%';
-- 16. 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
select stu_name
from students
where stu_name regexp '[杨林][\\u4e00-\\u9fa5]{2}';
5. 空值和去重(三值逻辑和trim)
-- 17. 查询没有录入籍贯的学生姓名(空值处理)
-- 三值逻辑 - true / false / unknown
select stu_name
from students
where stu_addr is null;
-- 如果遇到空字符串
-- trim - 修剪字符串左右两端指定的字符(默认修建空格)
update students set stu_addr = ' ' where stu_id = 3011;
select stu_name
from students
where stu_addr is null or trim(stu_addr) = '';
-- 18. 查询录入了籍贯的学生姓名(空值处理)
select stu_name
from students
where stu_addr is not null and trim(both ' ' from stu_addr)<