炼码LintCode--数据库题库（级别：入门；数量：144道）--刷题笔记_01
增
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
批量增
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...)，(值1, 值2, ...)，(值1, 值2, ...);
删除
DELETE FROM 表名 WHERE 条件;
改
UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2 WHERE 条件;
查
SELECT 列1, 列2 FROM 表名 WHERE 条件;
查数据为空 is null
select * from teachers where email is null;
分组
SELECT age, COUNT(*) FROM students GROUP BY age;
排序
SELECT name, age FROM students ORDER BY age DESC;
范围 between
select * from courses where student_count between 50 and 55
数量 -- 最大 max
select max(student_count) as max_student_count from courses
数量 -- 大于 >
select * from courses where student_count > 1000;
查询字段不为指定值 -- not in
select name from courses where teacher_id not in (1,3);
查询字段为指定值 -- in
select * from courses where created_at in ('2021-01-01 ','2021-01-03')
如果side=p，则改成n，反之亦然
-- 写法1：if 函数
-- update coins set side = if(side = 'p','n', if(side = 'n','p',side))
-- 写法2：case when 函数
update coins set side = case when side = 'p' then 'n' when side = 'n' then 'p' else side end;
update coins set side = case side when 'p' then 'n' when 'n' then 'p' else side end;
简单 join 连接
select c.created_at as 'course_date' , t.country as 'teacher_country' from courses c join teachers t on c.teacher_id = t.id
交叉连接 CROSS JOIN
-- CROSS JOIN teachers t：表示将 courses 表和 teachers 表进行交叉连接，交叉连接会返回两张表中所有记录的组合。
-- 交叉连接并不考虑任何条件，它会把左表（课程表）和右表（教师表）中的每一条记录都进行匹配，因此在大表中使用时会产生非常多的记录。
select c.name AS 'course_name' , t.name AS 'teacher_name' from courses c CROSS JOIN teachers t
-----------------------------------------------------------
2085 · 删除课程表的主键约束
-- desc `courses`; 是 SQL 中的命令，用于显示 courses 表的结构。它会返回表的列名、数据类型、是否允许 NULL 值、键类型、默认值等信息。这有助于查看表的设计和字段的属性。
-- 用这个命令可以看出 name 字段是主键，所以现在来删除它
-- desc `courses`;
-- 查看 courses 表中主键索引（PRIMARY）的详细信息。
-- SHOW KEYS FROM courses WHERE Key_name = 'PRIMARY';
-- 删除这个表的主键
alter table courses drop primary key;
2084 · 对课程表的id添加主键约束
-- ALTER TABLE courses ADD PRIMARY KEY (id);
2083 · 创建学生表在 Id 列上添加 PRIMARY KEY 约束
-- UNSIGNED 是一种用于数字数据类型的属性，它表示该列只能存储非负数，即不能存储负值。通常用于整数类型（如 INT、BIGINT 等）列的定义中
-- DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
id INT UNSIGNED NOT NULL PRIMARY KEY comment '主键',
`name` VARCHAR(64) NOT NULL comment '姓名',
`email` VARCHAR(64) NOT NULL comment '邮箱',
`age` int UNSIGNED NOT NULL comment '年纪',
`country` VARCHAR(32) NOT NULL comment '国家'
) ;
2081 · 向表中插入当前的日期
-- YYYY-MM-DD HH:MM:SS
--insert into records (now_time) values (now())
-- YYYY-MM-DD
INSERT INTO records (now_time) VALUES (CURDATE());
2080 · 查询教师全部课程学生总数不足 3000 人的教师姓名及学生总数
SELECT t.name, IFNULL(SUM(c.student_count), 0) AS student_count
FROM teachers t
LEFT JOIN courses c ON c.teacher_id = t.id
GROUP BY t.id
HAVING student_count < 3000
ORDER BY student_count ASC, t.name ASC;
---也可以这样写
SELECT t.name, IFNULL(SUM(c.student_count), 0) AS student_count
FROM teachers t
LEFT JOIN courses c ON c.teacher_id = t.id
GROUP BY t.id
HAVING IFNULL(SUM(c.student_count), 0) < 3000
ORDER BY IFNULL(SUM(c.student_count), 0) ASC, t.name ASC;
2078 · 查询不同年龄的教师的人数 --如果年龄均为 NULL，则年龄返回为 NULL，数量返回为 0
-- IFNULL(COUNT(age), 0) 格式是 IFNULL(expression, replacement_value)，即当 expression 为 NULL 时返回 replacement_value
-- 注意：count(age) 只会统计age不为null的列，age 值为null不会进行统计，只有count(1)或count(*) 才会
-- COUNT(*) 会计算分组内的所有行数量（包括 NULL 值），而不是只计算 age 列的非 NULL 值。
-- 所以如果所有行的 age 列均为 NULL，GROUP BY age 会产生一组 age 为 NULL 的分组，COUNT(*) 会返回此分组的行数，而不是 0。
-- select age, ifnull(count(*),0) as 'age_count' from teachers group by age order by age desc
select age, count(age) as 'age_count' from teachers group by age order by age desc
2075 · 查询不同国家教师的人数
select country , count(country) as teacher_count from teachers group by country order by count(country) ,country
2061 · 查询课程名称及授课教师的邮箱
select c.id,c.name as 'course_name' , t.email as 'teacher_email' from courses c join teachers t on c.teacher_id = t.id
2057 · 修改教师 Eastern Heretic 创建的课程信息
update courses set name = 'PHP',student_count = 300 where teacher_id = (select id from teachers where name = 'Eastern Heretic')
2055 · 查询所有课程名称以及与其相互对应的教师名称和国籍
-- 全外连接（Full Outer Join）用于合并两个表，并保留两个表中的所有记录，无论连接条件是否匹配。如果某一行在其中一个表中没有匹配的行，另一侧的结果会填充 NULL。
-- select c.name as 'course_name' , t.name as 'teacher_name' , t.country as 'teacher_country' from courses c full Outer join teachers t on c.teacher_id = t.id
-- 用 union 代替全外连接 ，UNION 自动去除重复的行，仅返回唯一的结果。UNION ALL 不会去除重复行，而是将所有行直接合并到结果集中
select c.name as 'course_name' , t.name as 'teacher_name' , t.country as 'teacher_country' from courses c left join teachers t on c.teacher_id = t.id
union
select c.name as 'course_name' , t.name as 'teacher_name' , t.country as 'teacher_country' from courses c right join teachers t on c.teacher_id = t.id
2046 · 查询课程创建日期按照 ’yyyy-MM-dd HH:mm:ss’ 的格式返回结果--DATE_FORMAT()
select DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s') as 'DATE_FORMAT' from courses
2043 · 查询在第一季度创建的课程（这个适合时间是多个年份的）
select name , created_at from courses where month(created_at) in (1,2,3)
2042 · 查询课程表中所有的课程名和创建日期中的年份和月份 -- year()、month()
select name , Year(created_at) as 'year' , month(created_at) as 'month' from courses
2041 · 查询指定教师授课的课程信息 -- in()
select c.name , c.teacher_id, c.created_at from courses c where c.teacher_id in (1,2,3) order by c.teacher_id asc , c.created_at desc ;
2040 · 查询教师 id 不为 3 且人数大于 800 的课程 -- not in ()
select * from courses where teacher_id not in (3) and student_count > 800
2039 · 查询课程表中课程的创建日期 -- 2020-04-22 13:01:12 改成 2020-04-22
select name , date_format(created_at,'%Y-%m-%d') as 'created_date' from courses
2038 · 分别查询出课程表的课程创建时间中的日期与时间 -- 年月日、时分秒格式
select name , created_at , date_format(created_at,'%Y-%m-%d') as 'created_date', date_format(created_at,'%H:%i:%s') as 'created_time' from courses
2037 · 查询 2020 年 8 月前的课程名和课程日期
-- 写法1：
-- select name , date_format(created_at,'%Y-%m-%d') as 'created_date' from courses where created_at < '2020-08-01'
-- 写法2：
select name , date_format(created_at,'%Y-%m-%d') as 'created_date' from courses
where year(created_at) < 2020 or (year(created_at) = 2020 and month(created_at) < 8);
2036 · 计算课程表中所有课程指定日期与开课日期的月数差 -- TIMESTAMPDIFF
-- MySQL 提供了 TIMESTAMPDIFF() 函数，可以计算两个日期之间的差异，并指定单位为月（month），如果是年则改为（year）即可。
select TIMESTAMPDIFF(month,created_at , '2020-04-22') as 'MonthDiff' from courses
2034 · 查询指定邮箱结尾的教师平均年龄 -- avg()
select avg(age) as 'average_teacher_age' from teachers where email like '%@qq.com'
2033 · 查询所有课程表的课程名和创建时间的秒数 如：2020-06-01 09:10:12 得出具体秒数为 12
-- 是当前时间的具体秒数，不是时间戳
select name ,second(created_at) as 'created_second' from courses
-- 分钟
--select name ,MINUTE(created_at) as 'created_hour' from courses
-- 小时
--select name ,HOUR(created_at) as 'created_year' from courses
2032 · 将课程创建日期均提前一天
-- interval 间隔、区间的意思
-- DATE_SUB(created_at, INTERVAL 1 DAY)：这是一个 MySQL 函数，用于将 created_at 字段的日期提前一天（即减去一天）。
select id,name,date_sub(created_at ,interval 1 day) as 'new_created' from courses
-- 这个是减去 30 天，比如：2020-05-31 --> 2020-05-01，因为4月只有30天，MySQL 会尝试找到一个合适的日期来表示“前一个月的对应日期”。
-- 如果没有找到合适的日期（例如，前一个月没有31号），MySQL 会根据某些规则做调整
-- select id,name,date_sub(created_at ,interval 1 month) as 'new_created' from courses
-- 这个是减去一年，原理同理可得
-- select id,name,date_sub(created_at ,interval 1 year) as 'new_created' from courses
2028 · 将课程创建日期均推迟一天
-- 推迟1天
select name,date_add(created_at ,interval 1 day) as 'new_created' from courses
-- 推迟1个月
--select name,date_add(created_at ,interval 1 month) as 'new_created' from courses
-- 推迟1年
--select name,date_add(created_at ,interval 1 year) as 'new_created' from courses
2029 · 计算 2019 年 03 月 26 日到课程创建时间的天数（计算从 2019 年 03 月 26 日到创建时间（created_at）相差的天数）
-- 与指定日期相差的天数--datediff
select datediff(created_at,'2019-03-26') as 'date_diff' from courses
-- 与指定日期相差的月数--TIMESTAMPDIFF
--select TIMESTAMPDIFF(month,created_at,'2019-03-26') as 'date_diff' from courses
-- 与指定日期相差的月数
--select name,TIMESTAMPDIFF(year,created_at,'2019-03-26') as 'date_diff' from courses
2027 · 查询课程创建日期按‘年 月’显示 （2020-06-01 --> 2020 06）
select date_format(created_at,'%Y %m') as 'DATE_FORMAT' from courses
2026 · 向表中插入当前时间（精确到毫秒）
-- 写法1：
--insert into records (now_time) values (now(3));
-- 写法2：
insert into records (now_time) values (current_timestamp(3));
1998 · 统计在 2020 年 1 月到 5 月的课程，统计课程表 courses 中 2020 年 1 月到 5 月之间的课程数量
--写法1：between
-- select count(1) as 'course_count' from courses where created_at between '2020-01-01' and '2020-05-30';
-- 写法2：year - month -- between
SELECT COUNT(*) AS course_count FROM courses where year(created_at) = 2020 and MONTH(created_at) between 1 and 5;
1996 · 判断教师是否拥有邮箱
请编写 SQL 语句，对教师表 teachers 中教师是否拥有邮箱进行判断，最后返回教师姓名和邮箱，以及使用函数 ISNULL 、IFNULL 、COALESCE 判断结果
-- coalesce 联合、合并；返回传入参数列表中第一个非 NULL 的值。如果所有参数都为 NULL，则返回 NULL。
SELECT
name,
email,
-- 使用 ISNULL 判断邮箱字段是否为空，0 代表有邮箱，1 代表没有邮箱
ISNULL(email) AS isnull_email,
-- 使用 IFNULL 判断邮箱字段是否为空，0 代表有邮箱，1 代表没有邮箱
IFNULL(email, 0) AS ifnull_email,
-- 使用 COALESCE 判断邮箱字段是否为空，0 代表有邮箱，1 代表没有邮箱
COALESCE(email, 0) AS coalesce_email
FROM
teachers;
1995 · 查询 20 岁以上教师的平均年龄 -- ROUND 、 avg
-- ROUND 函数用于对数值进行四舍五入，通常是针对小数进行保留，也可以指定保留的小数位数。
-- 语法：ROUND(number, decimals)
-- number：要进行四舍五入的数值。
-- decimals：保留的小数位数。如果为 0，则表示四舍五入到整数，如果是1，就是保留小数点后1位
select round(avg(age),0) as 'avg_teacher_age' from teachers where age > 20
1991 · 统计 3 号教师的学生总数 - sum
select sum(student_count) as 'select_student_sum' from courses where teacher_id = '3'
1989 · 查询最小的教师年龄 - min
select min(age) as 'min_age' from teachers
1987 · 查询最年长的中国教师的年龄-- max
select max(age) as 'max_age' from teachers where country = 'CN';
1984 · 统计不同教师 id 的数量 （去重再统计数量） -- count 、 distinct
select count(distinct teacher_id) as 'teacher_count' from courses
1982 · 查询教师的年龄并按升序排序
select age from teachers group by age order by age asc;
1979 · 查询学生人数最少的三门课 -- order by 、 limit
select * from courses order by student_count asc limit 3
1972 · 查询有电子邮箱的中日籍教师 -- in 、 is not null
select * from teachers where country in ('CN','JP') and email is not null;
1970 · 查询创建日期在 2020 年 6 月到 8 月的课程信息
-- 写法1：
-- select * from courses where created_at between '2020-06-01' AND '2020-08-31';
-- 写法2：不用具体时间，用year 和 month
select * from courses where year(created_at) = 2020 and month(created_at) between 6 and 8;
1969 · 查询 2020 年内开课的课程
-- 写法1：like
-- select * from courses where created_at like '2020%';
-- 写法2：year 函数
select * from courses where year(created_at) = 2020;
1968 · 查询首两个字母在 'Db' 和 'Dy' 之间的课程名称 -- LEFT、BETWEEN
select name from courses WHERE LEFT(name, 2) BETWEEN 'Db' AND 'Dy';
1967 · 查询以字母 'D' 到 'O' 开头的课程
select name from courses where left(name,1) between 'D' and 'O';
1966 · 使用 NOT BETWEEN 查询中国教师--查询教师（ID）不在 5 到 10 之间
select * from teachers where country = 'CN' and id not between 5 and 10;
1963 · 查询国籍不为中国和英国的 20~25 岁老师 -- not in
select * from teachers where country not in ('CN','UK') and age between 20 and 25;
1961 · 查询非日、美籍教师信息 -- not in
select * from teachers where country not in ('JP','USA');
1957 · 查询2020年5月之前开课的课程
-- between 两边是包含边界的，所以不能用，除非修改下日期
select * from courses where created_at >= '2020-01-01' and created_at <2020-05-01
1955 · 查询不满足条件的教师-- not ()
查询教师表 teachers 中除了年龄 age 在 20 岁以上 (不包括 20 岁) 的中国 (CN) 教师以外所有教师信息
SELECT * from teachers where not (age > 20 and country = 'CN')
1944 · 被同一个人至少使用过三次的共享单车
-- 重点：这个 GROUP BY bike_id, user_id 是把这两个字段看成一个整体来进行分组的。可以理解为联合分组
-- 联合分组：查询会根据 bike_id 和 user_id 的每个唯一组合来进行分组。每个组合代表一组数据，也就是说，只有当这两个字段的值完全相同时，才会被视为同一组。
-- 只有 bike_id, user_id 这两个组合的数量有3个以上，才能说明改辆车被同一个人使用过3次
select bike_id , user_id FROM shared_bicycles GROUP BY bike_id, user_id HAVING COUNT(*) >= 3;
1941 · 寻找直角三角形 -- case when
-- 直角三角形的定理：勾股定理：a^2 + b^2 = c^2
SELECT
*,
CASE
WHEN a*a + b*b = c*c OR a*a + c*c = b*b OR b*b + c*c = a*a THEN 'Yes'
ELSE 'No'
END AS right_triangle
FROM line_segments;
1932 · 挂科最多的同学 ,成绩状态 (0 表示挂科，其他表示通过) -- count
-- 先查出挂科的数据，然后根据 group by 分组，然后用count统计分组后的每个student_id 对应着多少行数据。
select student_id from exams where is_pass = 0 group by student_id order by count(1) desc limit 1 ;
1931 · 寻找特定的患者
-- NULL 是一个特殊的值，表示“未知”或“缺失”，而 NULL 与任何其他值（包括 NULL）的比较结果都为 FALSE，即 NULL 和任何值的比较都不会返回 TRUE
-- 如果某个记录的 infected_by_id 为 NULL，它 不会 满足 infected_by_id != 2 条件，因为 NULL 与 2 的比较结果是 UNKNOWN
select name from patients where infected_by_id != 2 or infected_by_id is null;
1928 · 网课上课情况分析 ，查询每位同学第一次登录平台听课的日期。
select student_id , min(date) as 'earliest_course_date' from online_class_situations
where course_number > 0 group by student_id;
1926 · 热门的英雄
-- concat 用来拼接字符串
-- 奇数：id % 2 = 1
SELECT id, name, popularity, CONCAT(ban, '%') AS probability
FROM heroes
WHERE popularity != 'T3'
AND id % 2 = 1
ORDER BY ban DESC;
1925 · 超过3名球员所得到的分数（请找到所有被三名或以上球员得过的分数）
select score from player_scores group by score having count(1) >= 3;
1923 · 增长的疫情感染人数
编写一个 SQL 语句，来查找与前一天的日期相比美国的新增病例数更高的所有日期的 id。
-- 使用自连接
-- ON n1.date = DATE_ADD(n2.date, INTERVAL 1 DAY) 解释：n2的date需要+1天才等于n1的天数，说明 n2.date 就是 n1.date的前一天
select n1.id from new_cases n1 join new_cases n2 on n1.date = date_add(n2.date, interval 1 day)
where n1.increased_count > n2.increased_count
1921 · 从不充值的玩家
select u.name as 'player' from users u left join recharges r on u.id = r.user_id where r.user_id is null;
1920 · 查找重名的同学
select name