一.简单条件查询
1.查询article文章表中，发表日期create_date在2019年1月1日上午10点30分至2019年11月10日下午4点2分的文章
select paper from article where create_date between '2019-01-01 10:00:00' and '2019-11-10 16:02:00';
注意引号的用法，只有整型、浮点型不用引号。
注意：MySQL以'YYYY-MM-DD'格式显示Date类型。
2.A表字段a 类型int中有100条记录，值分别为1至100。如下语句 ：
SELECT a FROM A WHERE a BETWEEN 1 AND 50 OR (a IN (25,70,95) AND a BETWEEN 25 AND 75) 则返回的结果集为？
条件：(between 1 and 50) or ( a in(25,70,95) and between 25 and 75)
[1,50] or 25,70,95 and [25,75]
[1,50] or 25,70
( [1,50] ,70)
3.已知表T1中有2行数据，T2中有3行数据，执行SQL语句,“select a.* from T1 a,T2 b”后，返回的行数为? 6
此处的查询没做任何条件查询，结果就是两张表做笛卡儿积，即：行数为两张表行数相乘，列数为两张表列数相加。
二.设计表
1.设计一个考勤系统，包含员工表，考勤记录表
这个系统中的两张表要能关联到一起。
员工表emp （id 做主键，name）
考勤记录表record （re_id 记录id 做主键，time 时间，emp_id 员工号，emp_id 做外键，关联emp表的id）
create table emp (id int primary key,name varchar(20));
create table record(re_id int primary key, time datetime, emp_id int, foreign key (emp_id) references emp(id));
2.设计一个学校宿舍管理系统，要求包含宿舍信息，学生信息，每日的宿舍查宿记录。
学生、宿舍、查房记录间的关系：一个学生只能住一个宿舍，一个宿舍能被多个学生住，学生和宿舍是1：m的关系；一个宿舍能被多次查宿，即有多个查宿记录，一次查宿记录只能记一次这个宿舍的情况，宿舍和查宿记录是1：n的关系。
宿舍表dormitory（id 宿舍号做主键）
学生表student（stu_id 学号做主键，name，dor_id 宿舍号，dor_id 外键，关联宿舍表的id）
查宿记录record（re_id 记录号做主键，时间，case 查宿情况，dor_id 宿舍号，dor_id做外键，关联宿舍表的id）
create table dormitory (id int primary key);
create table student (stu_id int primary key, name varchar(20),dor_id int, foreign key (dor_id) references dormitory(id));
create table record (re_id int primary key, time datetime, case varchar(50),dor_id int, foreign key (dor_id) references dormitory (id));
3.设计一个车辆违章系统，包含用户表，车辆表，违章信息表。违章信息表中包含用户和车辆的违章信息
一个用户可以有多辆车，一辆车只能被一个用户拥有，车辆和用户的关系是1：m；
违章信息表要包含用户和车辆的违章信息。一辆车可以有多个违章信息，一次的违章信息只能被一辆车拥有，违章信息和车辆的关系是1：n；一个用户可以有多次违章信息，一次违章信息只能属于一个用户，违章信息和用户的关系是1：k。
用户表user（id 身份证号做主键，name)
车辆表car（id 车牌号做主键，user_id 用户，user_id 做外键，关联user表的id）
违章信息表record（re_id 做主键，user_id做外键关联user表的id，car_id 做外键关联car表的id，case 违章信息，时间）
create table user (id int primary key, name varchar(20));
create table car (id int primary key, user_id int, foreign key (user_id) references user(id));
create table record (re_id int primary key, case varchar(50), time datetime, user_id int, car_id int, forengn key (user_id) references user(id), foreign key (car_id) references car(car_id));
4.设计一个学校食堂管理系统，包含食堂表，食堂仓口表，仓口收费记录表
一个食堂有多个仓口，一个仓口只能属于一个食堂，食堂和仓口的关系是1：m；一个仓口可以出多个收费记录表，一个收费记录表只能属于一个仓口，仓口和收费记录表的关系是1：n。
食堂表hall（id 做主键）
仓口表opening（id 做主键，hall_id 做外键关联hall表的id）
收费记录表record （re_id 做主键，time， price ，opening_id 做外键关联opening表的id）
create table hall (id int primary);
create table opening (id int primary key,hall_id int, foreign key (hall_id) references hall(id);
create table record (re_id int primary key, time datetime, price decimal(5,2), opening_id int, foreign key (opening_id) references opening(id));
三.高级查询（函数、分组、组合）
运营想查询复旦大学学生gpa最高值是多少，查询应返回gpa，结果保留到小数点后面1位
user_profile(id, name, university, gpa)
1.使用max() 聚合函数
select round( max(gpa), 1) as gpa from user_profile where university = '复旦大学';
2.使用order by 降序 然后limit 1，1
select round( gpa,1 ) as gpa from user_profile where university = '复旦大学' order by gpa desc limit 1,1;
3.窗口排序函数加子查询解题（没看懂，讨论里有，之后再学）
注意细节：保留一位小数
运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
user_profile(device_id,gender,age,university,gpa)
注意这里的条件：结果不去重，如果还用where 、or进行筛选， 那同时满足两个条件的记录只会出现一次。这个题的要求就相当于是查出条件1，再查条件2，结果放在一张表里，这里就使用union all 组合查询。
select device_id,gender,age,gpa from user_profile where university = '山东大学' union all select device_id,gender,age,gpa from user_profile where gender = 'male';
四.多表查询
1.有两个表分别如下：
表A (varchar(32) name， int grade)
zhangshan 80， lisi 60， wangwu 84
表B (varchar(32) name， int age)
zhangshan 26， lisi 24， wangwu 26， wutian 26
写SQL语句得到如下查询结果：
| NAME | GRADE | AGE |
| --------- | ----- | ---- |
| zhangshan | 80 | 26 |
| lisi | 60 | 24 |
| wangwu | 84 | 26 |
| wutian | null | 26 |
首先查询出来的列是name、grade、age
列的内容包含了两张表的内容，所以是两张表进行了连接
接着判断是自连接还是外连接，内连接是显示共有的部分，所以是外连接，这里记为b join a
并且b表的内容（wutian）全部显示出来，则为b left join a
连接需要有连接条件，即为b.name = a.name
select b.name, a.grade, b.age from b left join a on b.name = a.name;
运营想要查看所有来自浙江大学的用户题目回答明细情况，查询应返回device_id, question_id,result，查询结果根据question_id升序排序
question(id, device_id, question_id, result) q
user_profile(id, device_id, university) u
1.子查询
查询来自浙江大学的device_id select device_id from u where university = '浙江大学' eg:1
查询device_id为以上的设备的答题情况 select device_id, question_id,result from q where device_id = 1;
select q.device_id, q.question_id, q.result from question as q where q.device_id in (select u.device_id from user_profile as u where university = '浙江大学' ) order by question_id asc;
2.链接查询
select q.device_id, q.question_id, q.result from question as q join user_profile as u on q.device_id = u.device_id and u.university = '浙江大学' order by question_id asc;
3.统计每个学校的答过题的用户的平均答题数_牛客题霸_牛客网
运营想了解每个学校答过题的用户平均答题数量情况，查询应返回university、avg_answer_cnt（结果保留4位小数），结果按照university升序排序
user_profile u (device_id,university)
question q (device_id, question_id)
select university, round( count(q.question_id) / count( distinct q.device_id), 4) from question q join user_profile u on q.device_id = u.device_id group by university order by university;
错题版本+总结：
select university, round( count(q.question_id) / count( q.device_id), 4) from question q join user_profile u on q.device_id = u.device_id group by university having count(q.question_id) ，count( q.device_id) order by university;
首先count( distinct q.device_id)的dinstinct是必要的，一个设备可能做多个题，需要去重才能保证设备是不重复的；其次having是不能写的，having后边跟着的是条件，count不用group by也能查出来；最后题目中的答过题的用户这一条件，使用inner join自连接就筛选出来了
五.常用函数查询
运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量，age为null 也记为 25岁以下。
(1)if 函数
select if(age<25 or age is null,'25岁以下','25岁及以上') as age_cut,count(device_id) as number from user_profile group by age_cut;
(2)case函数
select case when age<25 or age is null then '25岁以下' else '25岁及以上' end as age_cut,count(device_id) as number from user_profile group by age_cut;
(3)union
select '25岁以下' as age_cut, count(device_id) from user_profile where age<25 or age is null union all select '25岁及以上' as age_cut, count(device_id) from user_profile where age>=25;
2.查看用户在某天刷题后第二天还会再来刷题的留存率。
question_practice_detail(device_id, question_id, date)
1.使用自连接。
想到例题，查询语文成绩比英语成绩高的同学【score(id,chinese,english)】就是使用自连接。
这里自连接q1和q2，通过q1.device_id=q2.device_id可以排除掉无效数据；
现在把左边的表视为第一天刷题的人，右边的表视为第二天刷题的人，这里能表示出这个效果的有两种方法：一个是datediff(q2.date,q1.date)=1，利用datadiff函数筛选出q2.date-q1.date=1，另一个是q1.date is not null and q2.date is not null and q2.date = date_add(q1.date,interval 1 day)；
接着要考虑自连接是做外连接还是内连接，因为要计算第一天刷过题的人的数量，所以左表的数据要保留全部，即left join
平均留存率：第二天还在刷题的人的数量 / 所有第一天刷过题的人的数量，那这里就要考虑是否有重复的情况。每天都存在这样的情况：一是同一台设备，刷了好几道题，所以device_id需要去重；去重date，可以这样设想：如果一台设备一天答了两道题，那第二天的一道题对应的数据，就会有两条，第一天的第一题和第二天的题、第一天的第二题和第二天的题，但实际上计算留存率只需要一次，所以date去重。
2.使用datediff()函数得到两天均刷题的条件
select count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
from question_practice_detail q1 left join question_practice_detail q2 on q1.device_id=q2.device_id
and datediff(q2.date,q1.date)=1;
3.统计每种性别参赛者的数量
1.原题目要求的是文本函数
性别的信息在profile里，所以需要从这里获取。
使用到 substring_index(字符串str, 分隔符delim, 计数count)
count是正数，那就是从左到右分隔符前边的；负数，就从右到左数，分隔符右边的。
count,是第count个分隔符
select substring_index(profile, ',' ,-1) as gender ,count(device_id) from user_submit group by gender;
2.使用模糊查询like
select case when profile like '%,male' then 'male' when profile like '%,female' then 'female' end gender, count(device_id) from user_sumbit group by gender;
六.增删改操作
1.把examination_info表中tag为PYTHON的tag字段全部修改为Python
更新操作的语法是 update 表名 set 列名 …… 条件；
1.where
update examination_info set tag = 'Python' where tag = 'PYTHON';
2.使用replace默认表列名1的字段所有满足要求的查找内容都修改为替换内容
update 表名 set 列名1 = replace(列名1, '查找内容', '替换内容') [,列名2 = replace(列名2, '查找内容', '替换内容')] [where 条件];
update examination_info set tag = replace（tag, 'PYTHON', 'Python');
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
