注:本案例题目来源LintCode,题解为个人刷题答案。
目录
1.查询,并设定某int字段为筛选条件(where)
题解:
-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
select id,name,student_count,created_at,teacher_id from courses where student_count>1000
2.删除 2020 年前的所有课
题解:
-- Write your SQL Query here --
delete from courses where created_at<'2020-01-01';
3.删除表中所有行
题解:
注意是删除行数据且不删表
-- Write your SQL Query here --
truncate table courses;
4. 向课程表中插入 SQL 课程信息
插入语句insert into
题解:
-- Write your SQL Query here --
insert into courses (id,name,student_count,created_at,teacher_id)
values('14','SQL','200','2021-02-25','1')
5.根据邮箱查询教师信息
题解:
关键词“like”
-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
select name,email from teachers where email like '%@qq.com';
6.查询有电子邮箱的中日籍教师
题解:
注意整个条件打括号啊~
-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
select * from teachers where (country ='CN' or country = 'JP') and email is NOT NULL
7. 查询教师 id 不为 1 和 3 的课程
题解:
不包括筛选可使用NOT IN,或者!=
-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
select name from courses where (teacher_id != 1) and (teacher_id != 3);
8.查询教师的年龄并按升序排序
题解:
唯一值关键词‘distintct’;
排序关键词‘order by’,默认升序。如需降序可在句末标注‘desc’
-- Write your SQL Query here --
-- example: SELECT * FROM XX_TABLE WHERE XXX --
select distinct age from teachers order by age;