数据库语句学习

客户端启动数据库：win+R-->cmd-->mysql -u root -p-->输入密码-->进入操作界面（mysql>）

///展示所有数据库
show databases;
//进入数据库
use dkd;
//查询目前所在数据库
select database();

//创建表
create table testt(
    -> id int comment '编号',
    -> name varchar(50) comment '姓名',
    -> age int comment '年龄',
    -> gender varchar(1) comment '性别'
    -> ) comment '用户表';

//查询表（粗略）
desc testt;
//查询表（详细）
show create table testt;

创建表：

查询表：

学习地址：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')

if函数：

SELECT id, name, score, IF(score >= 90, 'A', IF(score >= 80, 'B', 'C')) AS grade FROM students;

时间函数：

使用 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

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

约束：

非空约束：NOT NULL

唯一约束：UNIQUE

主键约束：PRIMARY KEY

主键约束，也叫 PRIMARY KEY 约束

PRIMARY KEY 可以与外键配合，从而形成主从表的关系

例如：

表一：用户 id (主键)，用户名

表二: 银行卡号 id (主键)，用户 id (外键)

则表一为主表，表二为从表。

例：对课程表添加主键约束

注意：如果使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

 ALTER TABLE courses
 ADD PRIMARY KEY(id)

删除课程表 courses 中的主键约束:

ALTER TABLE courses
DROP PRIMARY key;

例：为课程表 courses 的 teacher_id 添加外键约束，能与教师表 teachers 中的 id 相关联

alter table courses 
add constraint fk_teacher_id 
foreign key(teacher_id) references teachers (id);

检查约束 CHECK

为多个列添加 CHECK 约束

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

为 CHECK 约束命名

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CONSTRAINT chk_courses CHECK (`student_count` > 0) ;

课程表 courses 已存在的情况下为学生总数 student_count 字段添加一个大于 0 的 CHECK 约束。

ALTER TABLE `courses`  
ADD CONSTRAINT chk_courses CHECK ( `student_count` > 0 AND `teacher_id` > 0);

如果想要撤销 CHECK 约束，可以使用 DROP 关键字

ALTER TABLE `courses` 
DROP CHECK chk_courses

DEFAULT 约束用法：

DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。例如女同学较多，性别就可以默认为“女”，如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

例：将城市字段的默认值设为“巴黎”

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'paris'
)

例：表被创建，字段修改为默认值

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'beijing'

例：如需撤销 Persons表的 DEFAULT 约束 :

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT

多表连结：

语句汇总：

查询语句：

--查询单行语句
--查询教师表 teachers 中所有教师的姓名
select name from teachers;

--查询多行
--从课程表 courses 中获取课程名称 name 和上课学生人数 student_count 的列
SELECT name, student_count from courses;

--查询所有行
SELECT * FROM `courses`;

--查询教师表 teachers 中的不重复的教师国籍（country）
select distinct country from teachers;

--使用 SELECT WHERE 对行进行筛选过滤
select * from courses
where student_count>1000

插入语句：

-- 不指定列插入一条数据
-- insert into values (value1, values2,...)
insert into courses
values (14, "SQL", 200, "2021-02-25", 1);

-- 指定列插入一条数据
insert into teachers
(name, email, age, country)
values ('XiaoFu', 'XiaoFu@lintcode.com', 20,'CN');

更新语句：

-- 将课程表 courses 中人工智能课 (Artificial Intelligence) 的学生人数修改为 500 人
update courses
set student_count=500
where name = 'Artificial Intelligence'

删除语句：

--将从数据库中永久删除 courses 表及其所有的数据和相关对象（如索引、约束等）
DROP TABLE courses;
--删除表中的所有记录
DELETE FROM employees;
--清空表中的所有数据而不删除表结构，使用TRUNCATE TABLE语句
TRUNCATE TABLE employees;
--名为employees的表，包含员工的信息如id, name, position, 和 salary
--删除名叫"John Doe"的所有记录
DELETE FROM employees
WHERE name = 'John Doe';

多表连结：

内连接：

外连接（左连接）：

外连接（全连接）：

-- 内连接
-- 将课程表 courses 和教师表 teachers 进行内连接
-- 查询 “Eastern Heretic” 老师所教的所有课程的课程名和课程编号
-- 且结果列名分别以课程编号 id 、课程名称 course_name 和教师姓名 teacher_name 显示
select c.id, c.name as course_name, t.name as teacher_name
from courses c
inner join teachers t on t.id = c.teacher_id and t.name='Eastern Heretic';

-- 或者
select courses.id, courses.name as course_name, teachers.name as teacher_name
from courses
inner join teachers on teachers.id = courses.teacher_id and teachers.name='Eastern Heretic';

-- 外连接

-- 左连接
-- 将教师表 teachers 和课程表 courses 进行左连接，
-- 查询来自中国（讲师国籍 country ='CN' ）的教师名称以及所教课程名称，
-- 结果列名请分别以课程名称 course_name ，教师名称 teacher_name 显示。
select c.name as course_name, t.name as teacher_name
from teachers t
left join courses c on c.teacher_id = t.id where t.country = 'CN';

-- 全外连接
-- 将课程表 courses 和教师表 teachers 进行全外连接，
-- 查询所有课程名称以及与其相互对应的教师名称和教师国籍，
-- 结果列名请分别以课程名称 course_name 、教师名称 teacher_name 、教师国籍 teacher_country 显示。
SELECT c.name AS `course_name`,t.name AS `teacher_name`,t.country AS `teacher_country`
FROM `courses` AS c 
    LEFT JOIN teachers AS 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` AS c
    Right JOIN teachers AS t ON c.teacher_id = t.id;