LintCode_1">炼码LintCode
数据库
入门级别的笔记
笔记如下，把所有涉及到的入门级别的知识点简单总结了一下。
以及一点点举一反三的写法。
增
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 descselect age, count(age) as 'age_count' from teachers group by age order by age desc2075 · 查询不同国家教师的人数select country , count(country) as teacher_count from teachers group by country order by count(country) ,country2061 · 查询课程名称及授课教师的邮箱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.id2046 · 查询课程创建日期按照 ’yyyy-MM-dd HH:mm:ss’ 的格式返回结果--DATE_FORMAT()select DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s') as 'DATE_FORMAT' from courses2043 · 查询在第一季度创建的课程（这个适合时间是多个年份的）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 > 8002039 · 查询课程表中课程的创建日期 -- 2020-04-22 13:01:12 改成 2020-04-22select 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 courses2037 · 查询 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 courses2034 · 查询指定邮箱结尾的教师平均年龄 -- 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 -- betweenSELECT 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 > 201991 · 统计 3 号教师的学生总数 - sumselect sum(student_count) as 'select_student_sum' from courses where teacher_id = '3'1989 · 查询最小的教师年龄 - minselect min(age) as 'min_age' from teachers1987 · 查询最年长的中国教师的年龄-- maxselect max(age) as 'max_age' from teachers where country = 'CN';1984 · 统计不同教师 id 的数量 （去重再统计数量） -- count 、 distinctselect count(distinct teacher_id) as 'teacher_count' from courses 1982 · 查询教师的年龄并按升序排序select age from teachers group by age order by age asc;1979 · 查询学生人数最少的三门课 -- order by 、 limitselect * from courses order by student_count asc limit 31972 · 查询有电子邮箱的中日籍教师 -- in 、 is not nullselect * 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 和 monthselect * 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、BETWEENselect 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 inselect * from teachers where country not in ('CN','UK') and age between 20 and 25;1961 · 查询非日、美籍教师信息 -- not inselect * 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-011955 · 查询不满足条件的教师-- 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^2SELECT *,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 的比较结果是 UNKNOWNselect 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 = 1SELECT 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_count1921 · 从不充值的玩家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 from students group by name having count(1) > 1;1919 · 分数超过组长的组员 -- 自连接select g1.name as 'name' from group_members g1 left join group_members g2 on g1.group_leader_id = g2.id where g1.score > g2.score----------------事务--------------------开启事务：begin 或 START TRANSACTION
提交事务：COMMIT
回滚/撤销事务：ROLLBACK ------------------锁------------------对表加上写锁：LOCK TABLES courses WRITE;
对表加上读锁：LOCK TABLES teachers READ;如果使用了 LOCK TABLES 来锁定表，解锁表的语法是：UNLOCK TABLES; 这条语句会解锁所有被当前会话锁定的表。
读锁和写锁都可以用 UNLOCK TABLES; 命令来解锁，然后就可以继续执行 CRUD 操作。注意点，如果有两张表 t1 和 t2，t2表的 t1_id 字段是 t1 表的字段id的外键，那么当 t1 被加上写锁之时，t2 表可能也会被限制到，比如查询都查询不了之类的。-------------视图-----------
-- 基于 v_teachers 表创建视图 v_teachers_1
create view v_teachers_1 as select * from v_teachers where age < 20
-- 创建的视图的数据来源是这么来的
select * from v_teachers where age < 20-- 给teachers表创建视图表v_teachers
CREATE VIEW v_teachers AS SELECT * FROM `teachers`;-----------------------------------------------------------------
