数据库语句学习

学习地址：LintCode 炼码 - 更高效的学习体验！

使用 INSERT INTO 在指定的列中插入数据

例：向教师表 teachers 插入一条新的教师记录，该记录指定列的字段内容如下：

name	email	age	country
XiaoFu	XiaoFu@lintcode.com	20	CN

INSERT INTO teachers (`name`, `email`, `age`, `country`) VALUES ('XiaoFu','XiaoFu@lintcode.com', 20, 'CN')

使用 UPDATE 更新数据

例：将课程表 courses 中人工智能课 (Artificial Intelligence) 的学生人数修改为 500 人。 

UPDATE courses
SET student_count = 500
WHERE name = 'Artificial Intelligence'

简单的DELETE语句：

例：删除2020年前的课程

-- 删除开课时间在2020-1-1前的课程信息
DELETE FROM courses WHERE created_at<'2020-1-1'
-- 删除学生总数大于1000的课程信息
DELETE FROM courses WHERE student_count>1000

例：在不删除表的前提下，删除课程表 courses 中的所有行数据。

DELETE FROM courses

WHERE AND:

请编写 SQL 语句，从 courses 表中，选取课程名为 'Web' 或者 'Big Data' 的课程信息，如果这两门课程存在，请将这两门课程的信息全部返回。

SELECT * FROM courses
where name in ('Web','Big Data')
-- 等同于
-- WHERE name = 'Web' or name = 'Big Data'

请编写 SQL 语句，查询课程表 courses 中课程创建时间 created_at 在 '2020-01-01' (包括) 到 '2020-05-01' (不包括) 之间的所有课程名称和课程创建时间

select name , created_at from courses
where created_at>='2020-01-01' and created_at<'2020-05-01'

WHERE NOT:

题目要求查询课程表 courses 中，教师 id teacher_id 不为 3，且学生人数 student_count 超过 800 的所有课程，最后返回满足条件的课程的所有信息。

SELECT * FROM courses
WHERE NOT (teacher_id = 3 or student_count < 800)

WHERE NOT IN

请编写 SQL 语句，查询课程表 courses 中所有教师 id teacher_id 不为 1 或 3 的所有课程，并返回满足查询条件的课程名称。

SELECT name FROM courses
WHERE teacher_id not in (1,3)

BETWEEN AND 

BETWEEN AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

查询国籍不为中国和英国的 20~25 岁老师:

SELECT *
FROM teachers
WHERE (age BETWEEN 20 AND 25) AND (country NOT IN ('CN','UK'));

 IS NULL 、IS NOT NULL

查询教师表 teachers 中，国籍为 'CN' 或 'JP' 且 email 信息不为空的所有教师信息。

SELECT * FROM teachers 
WHERE email is not NULL and country in ('CN', 'JP')

  LIKE 模糊查询

例：查询教师表 teachers 中，所有使用 qq 邮箱的教师名字和邮箱。

SELECT name , email FROM teachers
where email like '%qq.com'

order by：升序排列

例：查询教师表 teachers 中教师年龄 age 的唯一值，并将结果按照年龄 age 进行升序排序。
表定义: teachers (教师表)

SELECT distinct age from teachers
-- 升序--
order by age
--或者--
--order by age ASC--
-- 降序 --
--ORDER BY age DESC;--

 limit限制行数：

select * from table_name limit 10;//检索前10行记录
select * from table_name limit 5 ，10;//从第6行开始，检索10行记录，即：检索记录行 6-15

例：从教师表 teachers 中查询一条年龄最大的并且国籍为中国（对应的 country 值为 CN）的教师的信息。

SELECT * FROM teachers
WHERE country = 'CN'
-- 将age升序排列，按理说第一行数据就是年龄最大的
-- 但是不能这样写，检索的结果是第2行
-- order by age
-- LIMIT 1;
-- 要将age降序，那么第2行是最大的
ORDER BY age DESC
limit 1;

使用 AVG() 函数求数值列的平均值

查询教师表 teachers 中教师邮箱为 '@qq.com' 结尾的年龄的平均值，最后返回结果列名显示为 'average_teacher_age'

SELECT AVG(age) AS average_teacher_age from teachers
WHERE email like '%@qq.com'

 MAX()：(MIN()相同用法)

例：使用聚合函数 MAX()，从教师表 teachers 中，查询最年长的且国籍为中国的教师信息，并返回该教师的年龄，结果列名显示 max_age。

SELECT MAX(age) AS max_age FROM teachers
where country = 'CN'

SUM()

 例：统计课程表 courses 中 teacher_id 为 3 的教师所教授的学生总数，并用select_student_sum 作为结果集列名。

SELECT SUM(student_count) as select_student_sum FROM courses
WHERE teacher_id = 3

round()

例：查询教师表 teachers 中，20 岁（不包含 20 岁）以上教师的平均年龄，返回的字段为 avg_teacher_age ，结果保留四舍五入后的整数。

SELECT round(AVG(age),0) as avg_teacher_age from teachers
where age > 20

ISNULL、IFNULL、COALESCE:

SELECT `name`, `email`, ISNULL(`email`) AS isnull_email, IFNULL(`email`, '0') AS ifnull_email, COALESCE(`email`, 0) AS coalesce_email
FROM `teachers`;

COUNT() 函数计数

统计教师表中年龄在 20 到 28 岁之间，且国籍为中国（对应的 country 值为 CN）或英国（对应的 country 值为 UK）的教师人数，最后返回统计值，结果列名显示为 teacher_count 。

SELECT count(*) as teacher_count from teachers
-- SELECT * from teachers
-- 以下两行代码效果相同
-- WHERE age between 20 and 28 and country in ('CN', 'UK')
WHERE age >= 20 and age <= 28 and country in ('CN', 'UK')

时间函数：

使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间

例：向记录表 records 中插入当前的日期。表定义: records (记录表)

INSERT INTO records values(now(2))

 使用 DATE()、TIME() 函数提取日期和时间

例：使用 DATE() 、 TIME() 函数从课程表 courses 中查询课程的名字 name 和课程创建时间 created_at，从课程创建时间 created_at 中提取出创建课程的日期与时间，用 created_date 和 created_time 作为结果集列名。

SELECT `name`, `created_at`, DATE_FORMAT(DATE(`created_at`),"%Y-%m-%d") AS `created_date`, DATE_FORMAT(TIME(`created_at`),"%H:%i:%s") AS `created_time`
FROM `courses`;

结果： 

使用 EXTRACT() 函数提取指定的时间信息：

EXTRACT() 函数用于返回日期/时间的单独部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小时)、MINUTE (分钟)、 SECOND (秒)。

例：从课程表 courses 中查询所有课程的课程名称（ name ）和课程创建时间（ created_at ）的小时数，将提取小时数的列名起别名为 created_hour。 

SELECT name, extract(hour FROM created_at) as created_hour FROM courses

结果：

使用 DATE_FORMAT() 格式化输出日期：

DATE_FORMAT() 函数返回的是字符串格式。

%m 表示月份，%d 表示日期，%Y 表示年份，%w 表示星期

例：查询 courses 表，查询课程创建时间，按照 ’yyyy-MM-dd HH:mm:ss’ 的格式返回结果，返回列名显示为 DATE_FORMAT。

-- 注意'%Y-%m-%d %H:%i:%s'大小写，并且要加单引号，不要忘记
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') as DATE_FORMAT FROM courses

使用DATE_ADD()增加时间

例：查询出课程表 courses 中的课程名 name 和课程创建日期 created_at 并将课程创建日期 created_at 向后推迟一天

SELECT name,DATE_ADD(created_at, interval 1 day) as new_created FROM courses

使用DATE_SUB()减少时间

例：查询 courses 表中课程的课程创建日期，将课程创建日期均提前一天，最后返回课程 id 、课程名称 name 及修改后的开课日期，修改后的课程创建日期命名为 new_created 。

SELECT id, name, DATE_SUB(created_at, interval 1 day) AS new_created FROM courses

DATEDIFF()函数：计算两个日期之间的天数差异：

SELECT DATEDIFF('2020-04-22',created_at) as MonthDiff FROM courses

TIMESTAMPDIFF()函数：计算两个日期时间之间的差值

语法：TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

参数说明：unit：表示时间单位，可以是YEAR（年）、QUARTER（季度）、MONTH（月）、WEEK（周）、DAY（天）、HOUR（小时）、MINUTE（分钟）、SECOND（秒）等。

计算 '2020-04-22' 与课程创建时间的月数差，返回列名显示为 MonthDiff。

SELECT TIMESTAMPDIFF(MONTH,created_at,'2020-04-22') as MonthDiff FROM courses

例：教师表中的教师来自不同的国家，现需要统计不同国家教师的人数，并将结果按照不同国籍教师人数从小到大排列