SQL

Web	描述
SQL语言种类
sql语句文档

初始SQL

SQL语言呢是一种描述性的语言，和Java、php、python等程序语言一样，也是一种编程语言。只不过呢SQL是为了操作关系型数据库而开发的一种语言。

一种描述性语言
- 之所以说其是一种描述性语言，是因为其是面向语义的。比如说我们要从某一个表中取出符合某种条件的数据，那么根据这句话，我们就可以写出相应的SQL语句。所以说，每个SQL语言都很容易理解。学习它的难度要比其他语言低很多。
SQL语言的作用：对存储在RDBMS**（Relational Database Management System，关系数据库管理系统）**中的数据进行增删改查等操作
- 所谓的关系型数据库呢，就是我们之前所提到的像MySQL、SQL Server、Oracle呀，这样的数据库。当然前面也提到了现在有一些这非关系型数据库，也已经开始支持SQL语言来对存储其中的数据进行操作了。比如Hive，就可以呢支持使用SQL语言来查询存储在Hadoop中的数据，可见呢SQL语言不但学习难度低，而且引用范围也是越来越广，实在是每个程序员所必备的一种语言。
常用的SQL语言种类：DCL、DDL、DML、TCL
- DCL：数据库管理语句，用于管理数据库访问的。比如授权语句、用户建立语句等
- DDL：数据定义类语句的简称，主要用于建立数据库对象的，比如建立表的语句，修改表结构的语句等等
- DML：数据操作语句的一种简称，也是我们平时使用最多的一种语言，从其名字中大家就可以知道DML主要用于操作存储在数据库中的数据，也就是之前所说的执行这个增删改查，这些任务的语句全都属于DML类语句
- TCL：这是一种事务控制类语句的简称，主要的作用是实现对事物的控制，比如开启事务、提交事务、回滚事务等等，这些都是属于TCL类语句。

DCL（Data Control Language)

建立程序所使用的数据库访问账号，并对这个账号赋予相应的权限。这类操作呢是通过DCL语言来实现的，常用的DCL主要有三个。

建立数据库账号：create user
- 我们需要使用这个语句来建立我们需要使用的数据库账号，并且指定这个账号的密码。
- 接下来建立好账号后，我们还要对这个账号进行授权
对用户授权：grant
- 至于可以为数据库账号授予那些权限，我们下面再来讨论
- 最后，如果我们发现有的账号具有并不是很需要的权限，超过了它使用范围的权限，就可以使用
收回用户权限：revoke
- 把那些不需要的权限收回

任务一：建立程序使用的数据库账号下面是create user 命令一个简单的语法

sql

CREATE USER [ IF NOT EXISTS]
		user [auth_option] [,user [auth_option]] ...
		DEFAULT ROLE role [, role ] ...
		[REQUIRE {NONE | tls_option [[AND] tls_option]
		...}]
		[WITH resource_option [resource_option] ...]
		[password_option | lock_option] ...

sql

help create user; # show help infomation

show plugins; #显示Mysql支持的插件，如authentication认证类插件

tip:::


认证插件名	描述
mysql_native_password	是MySQL5.7(包括）版本之前所使用的默认认证插件
caching_sha2_password	是MySQL8.0默认所使用的
sha256_password

:::

有中括号的这些参数呢，就是可选的参数；而没有中括号就是呢必须使用的

sql

user [auth_option]

用户名@访问控制列表 上面的user就是我们所要建立的账号名，这里要注意MySQL的用户名是由两部分组成的，第一部分是用户名，第二部分是访问控制列表，第一部分和第二部分之间是用@符号来进行分割的。访问控制列表决定了我们可以从那些客户端进行访问。

sql

CREATE USER mc_class@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' by '1234567';

上面的mc_class就是用户名，**192.168.1.%就是访问控制列表，%**是一个通配符，表示在这个网段下的所有主机都可以访问我们的数据库服务器。IDENTIFIED WITH用于指定MySQL密码所使用的加密方式的，以及mc_class账号的密码。

建立一个测试账号

sql

CREATE USER mc_class@'192.168.1.%' 
	IDENTIFIED  BY '123456' WITH MAX_USER_CONNECTIONS 1;

可以使用help命令来查看我们使用语句的帮助信息

sql

mysql>help create user

给账号授权

MySQL常用权限

权限名称	说明
Insert	向表中插入数据的权限
Delete	删除表中数据的权限
update	修改表中数据的权限
Select	查询表中数据的权限
Execute	智行存储过程的权限

授权语句

sql

GRANT
	priv_type [(column_list)]
		[,priv_type[(column_list)]] ...
	ON [object_type] priv_level
	TO user_or_role [,user_or_ole] ...	
	[WITH GRANT OPTION]

GRANT后：权限列表
ON后：对那些对象进行授权，对象可以是一个库、表、存储过程、函数，如果是表中列，就要在上面权限列表中的（column_list）来进行制定
TO后：把权限授予指定的用户或角色

查看MySQL支持的全部权限列表：

sql

show privileges

sql

-- 给用户mc_class授予mysql.user表上的user和host列的查询权限
GRANT select(user,host) on mysql.user to mc_class@'localhost';
-- 给用户mc_class授予mysql.user表的所有列的查询权限；没有给出列列表是查询所有列
GRANT select  on mysql.user to mc_class@'localhost';
-- 给用户  授予一个库所有表的查询权限
GRANT select ON mysql.* TO mc_class@'localhost';

查看一个用户被授予的权限

sql

SHOW GRANTS FOR 'username'@'host';

+-------------------------------------------------------------------------------+
| Grants for mc_class@192.168.0.%                                               |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mc_class`@`192.168.0.%`                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO `mc_class`@`192.168.0.%` |
+-------------------------------------------------------------------------------+

授权注意事项

使用GRANT授权的数据库账号必需存在
用户使用grant命令授权必须具有grant option权限
只能授予别的用户自己有的权限
获取命令帮助 help grant

回收用户权限

sql

REVOKE
	priv_type [(column_list)]
		[,priv_type[(column_list)]] ...
	ON [object_type[ priv_level	
	FROM user_or_role	[, user_or_role] ...

sql

REVOKE DELETE,INSERT,UPDATE ON mysql.* FROM mc_class@'localhost';

DDL(Data Definition language)

DDL就是数据库定义语言的简称，用于定义数据的各种对象，比如数据库的表、库、视图等等。

DDL 分类

建立/修改/删除数据库：create/alter/drop database 在建立数据库对象前，要先建立书库，其他的对象像表、视图等都在某一个数据库中的。可以使用create database语句来创建一个数据库。如果要对一个数据库进行修改的话，可以使用alter database。删除数据库使用drop database语句。

建立/修改/删除表：create/alter/drop table 我们可以使用create table 语句来创建表，表进行修改可以使用alter table语句，是对表的结构来进行修改，删除一个表的话就可以使用drop table 命令。

建立/删除索引：create/drop index 索引呢并不是越多越好，过多的索引不但不能提升数据的查询性能，反而影响数据的查询效率。

清空表：truncate table 为什么清空表不叫数据管理命令，而叫数据定义命令呢？实际上truncate table做的操作呢，实际上就相当于drop table create table，也就是先删除表再创建表，所以truncate table命令实际上就是DDL命令，其所实现的功能呢就是删除表中的所有数据并保留这个表的结构，但是并不会记录数据的删除日志，所以也无法通过日志对已删除的数据进行恢复。

重命名表：rename table 可以对表进行重命名，还可以利用其在同一个数据库的不同实例之间移动表，rename table命令并不会影响表中的数据

视图是我们很常用的一个数据库对象，我们可以利用视图来简化查询，在MySQL中要创建视图。

建立/修改/删除视图：create/alter/drop view

建立数据库imc_db

创建数据库

数据库的名称要见名知意，不能使用关键字
需要指定字符集和字符编码，如果不指定就会使用系统默认的字符集和排序规则

sql

create database imc_db;

建立数据库imc_db中的表

sql

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    
create_definition:
    col_name column_definition
 
...其它使用help命令查询

TEMPORARY：临时表，是当前线程创建的表，线程结束也会删除，我们并不需要指定这个关键字

示例：

查看建表代码

sql

use imc_db;

create table if not exists imc_course (
    course_id int unsigned auto_increment not null comment 'course id',
    title varchar(20) not null default '' comment 'course main title',
    title_desc varchar(50) not null default '' comment 'course vice id',
    type_id smallint unsigned not null default 0 comment 'course category id',
    class_id smallint unsigned not null default 0 comment 'course class id',
    level_id smallint unsigned not null default 0 comment 'course level id',
    online_time datetime not null default current_timestamp comment 'course online time',
    study_cnt int unsigned not null  default 0 comment 'study people',
    course_time time not null default '0.00' comment 'course time',
    intro varchar(200) not null default '' comment 'course desc',
    info varchar(200) not null default '' comment 'learn know',
    harvest varchar(200) not null default '' comment 'harvest',
    user_id int unsigned not null default 0 comment 'teacher id',
    main_pic varchar(200) not null default '' comment 'course main pic',
    content_score decimal(3,1) not null default 0.0 comment 'comment score',
    level_score decimal(3,1) not null default 0.0 comment 'simple score',
    logic_score decimal(3,1) not null default 0.0 comment 'logic score',
    score decimal(3,1) not null default 0.0 comment 'total score',
    primary key (course_id),
    unique key udx_title(title)
) comment 'main course table';

create table if not exists imc_chapter (
    chapter_id int unsigned auto_increment not null comment 'chapter id',
    course_id int unsigned not null default 0 comment 'course id',
    chapter_name varchar(20) not null default '' comment 'chapter name',
    chapter_info varchar(200) not null default '' comment 'chapter desc',
    chapter_no tinyint(2) unsigned zerofill not null default 0 comment 'chapter num',
    primary key (chapter_id),
    unique key udx_courseid_chaptername(course_id,chapter_name)
) comment 'course chapter';

create table if not exists imc_subsection(
    sub_id int unsigned auto_increment not null comment 'section id',
    chapter_id int unsigned not null default 0 comment 'chapter id',
    course_id int unsigned not null default 0 comment 'course id',
    sub_name varchar(50) not null default '' comment 'section name',
    sub_url varchar(200) not null default '' comment 'section url',
    video_type enum('avi','mp4','mpeg') not null default 'mp4' comment 'video format',
    sub_time time not null default '0.00' comment 'section time',
    chapter_no tinyint(2) unsigned zerofill not null default 0 comment 'chapter num',
    primary key (sub_id),
    unique key udx_chapterid_courseid_subname(chapter_id,course_id,sub_name)
) comment 'course section table';

create table if not exists imc_class(
    class_id smallint unsigned auto_increment not null comment 'course class id',
    class_name varchar(10) not null default '' comment 'category name',
    add_time timestamp not null default current_timestamp comment 'add time',
    primary key (class_id)
) comment 'class category';

create table if not exists imc_type(
    type_id smallint unsigned auto_increment not null comment 'course type id',
    type_name varchar(10) not null default '' comment 'type name',
    add_time timestamp not null default current_timestamp comment 'add time',
    primary key (type_id)
) comment 'course type';

create table if not exists imc_level(
    level_id smallint unsigned auto_increment not null comment 'course level id',
    level_name varchar(10) not null default '' comment 'level name',
    add_time timestamp not null default current_timestamp comment 'add time',
    primary key (level_id)
) comment 'course level';

create table if not exists imc_user(
    user_id int unsigned auto_increment not null comment 'user id',
    user_nick varchar(20) not null default 'imooc' comment 'nickname',
    user_pwd char(32) not null default '' comment 'password',
    sex char(2) not null default 'un' comment 'sex',
    province varchar(20) not null default '' comment 'province',
    city varchar(20) not null default '' comment 'city',
    position varchar(20) not null default 'unknown' comment 'position',
    mem varchar(100) not null default '' comment 'desc',
    exp_cnt mediumint unsigned not null default 0 comment 'empirical value',
    score int unsigned not null default 0 comment 'score',
    follow_cnt int unsigned not null default 0 comment 'follow num',
    fans_cnt int unsigned not null default 0 comment 'fans num',
    is_teacher tinyint unsigned not null default 0 comment 'teacher tag:0: normal,1:teacher',
    reg_time datetime not null  default current_timestamp comment 'register time',
    user_status tinyint unsigned not null default 1 comment 'user status 1:normal,0:freeze',
    primary key (user_id),
    unique key udx_usernick(user_nick)
) comment 'user table';

create table if not exists imc_question(
    quest_id int unsigned auto_increment not null comment 'comment',
    user_id int unsigned not null default 0 comment 'user id',
    course_id int unsigned not null default 0 comment 'course id',
    chapter_id int unsigned not null default 0 comment 'chapter id',
    sub_id int unsigned not null default 0 comment 'sub id',
    reply_id int unsigned not null default 0 comment 'reply id',
    quest_title varchar(50) not null default '' comment  'comment title',
    quest_content text comment 'comment content',
    quest_type enum('question','comment') not null default 'comment' comment 'comment type',
    view_cnt int unsigned not null default 0 comment 'browser num',
    add_time datetime not null default current_timestamp comment 'publish time',
    primary key (quest_id)
) comment 'question table';

create table if not exists imc_note(
    note_id int unsigned auto_increment not null comment 'note',
    user_id int unsigned not null default 0 comment 'user id',
    course_id int unsigned not null default 0 comment 'course id',
    chapter_id int unsigned not null default 0 comment 'chapter id',
    sub_id int unsigned not null default 0 comment 'sub id',
    note_title varchar(50) not null default '' comment 'note title',
    note_content text comment 'comment content',
    add_time datetime not null default current_timestamp comment 'publish time',
    primary key (note_id)
) comment 'note';

create table if not exists imc_classvalue(
    value_id int unsigned auto_increment not null comment 'value',
    user_id int unsigned not null default 0 comment 'user id',
    course_id int unsigned not null default 0 comment 'course id',
    content_score decimal(3,1) not null default 0.0 comment 'comment score',
    level_score decimal(3,1) not null default 0.0 comment 'level_score',
    logic_score decimal(3,1) not null default 0.0 comment 'logic_score',
    score decimal(3,1) not null default 0.0 comment 'common score',
    add_time datetime not null default current_timestamp comment 'publish time',
    primary key (value_id)
) comment 'note';

create table if not exists imc_selectcourse(
    select_id int unsigned auto_increment not null comment 'select',
    user_id int unsigned not null default 0 comment 'user id',
    course_id int unsigned not null default 0 comment 'course id',
    select_time datetime not null default current_timestamp comment 'select time',
    study_time time not null default '0.0' comment 'accumulate time',
    primary key (select_id)
) comment 'user select table';

修改表语句

sql

ALTER  TABLE  tbl_name
	[alter_specification [, alter_specification] ....]
alter_specification:
	| ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
	| ALTER  [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT }
	| CHANGE [COLUMN] old_col_name new_col_name column_definition
		[FIRST | AFTER col_name]
	| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
	| DROP [COLUMN] col_name
	| RENAME COLUMN old_col_name TO new_col_name

ADD:: 增加列，列的名称、数据类型、在表中的位置
ALTER 修改列的默认值
CHANGE 修改列的默认值时再修改列的定义
MODIFY 不修改列的默认值情况下修改列的定义、位置
DROP删除表中不需要的列，删除列的时候数据也会被删除，需要做下备份
RENAME 只修改列的名称

删除表语句

sql

CREATE 	[UNIQUE] INDEX index_name
	ON 	tb1_name (index_col_name, ...)

index_col_name:
	col_name [(length)] [ASC | DESC]

sql

DROP INDEX index_name ON tb1_name

清空表

sql

TRUNCATE TABLE imc_note;

重命名表

sql

RENAME TABLE imc_note TO bak_imc_note;

DML(Data Manipulation language)

DML就是数据操作语句的英文简称。所谓的数据操作语句的作用，从名称中我们就可以知道一二了， DML语句作用就是用于操作存储在数据库中的数据。比如我们希望向表中添加、修改、删除数据，这样的SQL语句就都是DML语句:

新增表中的数据：insert into
删除表中的数据：delete
修改表中的数据：update
查询表中的数据：select

编写INSERT语句的思路

确认要把数据插入到那个表
确认表的数据库结构，那些列不能为null，那些列可以为NULL，对于不能为NULL的列是否有默认值
sql
```
show create table imc_class;
```
1
确认对应插入列的插入值的清单
sql
```
value('mysql'),('redis')
```
1

往具有主键和唯一索引的表中插入数据时，如果出现主键或唯一索引冲突，插入的操作可能就会被回滚，为了避免这种情况发生，可以在insert语句中指定当出现唯一索引冲突时候的操作行为，比如我们可以设置当出现唯一索引冲突时则更新表中的某几列的值为新的插入数据的值

sql

create unique index uqx_classname on imc_class(class_name);

插入数据

sql

# 插入
insert into imc_class(class_name) values('mysql'),('redis') ;

# 设置了唯一索引后，插入重复数据仅更新时间, 主键会受到影响。
insert into imc_class(class_name) values('mysql'),('redis') 
on duplicate key update add_time=current_timestamp;

编写查询语句的思路

首先确定我们要获取的数据存在哪些表中 确定FROM子句
其次是确定我们要取表中的哪些列 确定SELECT子句
确认是否需要对表中的数据进行过滤 确定WHERE子句

MySQL的运算符

运算符	说明
= > < >= <= <> !=	<>和!=都表示不等于
BETWEEN min AND max	列的值大于等于最小值，小于等于最大值，包含前闭后闭
IS NULL 、 IS NOT NULL	判断列的值是否为空，上面的比较运算符不能判断为null的值
LIKE 、 NOT LIKE	%代表任何数量的字符 _代表任何一个字符
IN 、NOT IN	判断列的值是否在指定范围内
AND ， &&	AND运算符两边的结果都为真时，返回结果才为真
OR	OR 表达式两表表达式有一个为真，返回结果为真
XOR	XOR (异或）运算符两边的表达式一真一假时返回真，两真两假时返回假

两条SQL语句用union all关联

sql

# 需求：查询出课程标题中含有MySQL关键字并且学习人数小于5000，课程标题中不含MySQL关键字并且学习人数大于5000的课程，课程标题和学习人数
SELECT title,study_cnt FROM imc_course
WHERE title LIKE '%mysql%' AND study_cnt <5000
UNION ALL
SELECT title,study_cnt FROM imc_course
WHERE title  NOT  LIKE '%mysql%' AND study_cnt >5000


# 等价
SELECT title,study_cnt FROM imc_course
WHERE title LIKE '%mysql%' XOR study_cnt <5000

关联查询JOIN

内关联 INNER JOIN

取每个查询table中都有的数据。

在这里插入图片描述

外关联左连接

在这里插入图片描述

左边显示的是存在于表A的数据，无论表B是否有关联数据，若表B有关联数据同时查出来。

右边则显示的是只属于表A，而不属于表B的数据。

sql

select a.course_id, a.title from imc_course a where course_id not in (select b.course_id from imc_chapter b);
# 等价于
select a.course_id,a.title from imc_course a left join imc_chapter b on b.course_id=a.course_id where b.course_id is null; #执行效率高

外关联右连接

在这里插入图片描述

GROUP BY 分组查询

查看模式

sql

show variables like 'sql_mode'; #  MySQL在宽松的SQL_model下，是可以支持select子句中非聚合函数列同group by 子句中的列不同的情况，但是这种情况下查询的结果可能并不正确。STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION


set session sql_mode = 'ONLY_FULL_GROUP_BY'; # 设置分组模式为严格模式，即所有出现在select子句中的非聚合函数的列呀，都应该出现group by 子句之后

HAVING子句过滤分组结果

可以对聚合后的数据进行过滤

常用的聚合函数

聚合函数	说明
COUNT(*) / COUNT(col)	计算符合条件的行数
SUM(col_name)	计算表中符合条件的数值列的合计值，只能用在数值列
AVG (col_name)	计算表中符合条件的数值列的平均值，只能用在数值列
MAX(col_name)	计算表中符合条件的任意列的最大值
MIN(col_name)	计算表中符合条件的任意列的最小值

sql

SELECT level_name ,count(*)
FROM imc_course a 
JOIN imc_level b ON a.level_id=b.level
group by level_name having count(*) > 3;	

select count(course_id), count(distinct user_id) from imc_course; # 查询不同的user有多少

order by 子句对查询结构进行排序

limit 子句限制返回结构集的行数

一定要和order by 子句配合使用，由于limit子句每次获取不同的分页数据的时候，都会重复的来执行获取数据的查询语句，所以只是返回数据的起始行数不一样而已，所以为了保证每次获取的数据都是按照相同的顺序来进行排列的一定要配合order by子句使用

创建视图

创建视图就是在AS关键字后面写一个SELECT语句

sql

CREATE VIEW view_name
AS
	SELECT
		[ALL | DISTINCT | DISTINCTROW ]
		select_expr [, select_expr ...]
		[FROM table_references
		[WHERE where_condition]

可以查询视图就可以达到查询select语句的效果，个人理解就是创建了一种查询的快捷方式

sql

create view class_view as
select class_id, class_name from imc_class;

select * from class_view;

按条件删除表中的数据

sql

DELETE 
	FROM table_name
		[WHERE where_confition]
		[ORDER BY ...]
		[LIMIT row_count]

编写DELETE语句的思路

确定要删除的数据存储在那张表中 FROM子句
确认删除数据的过滤条件 WHERE子句
确认是否只删除有限条数据 ORDER BY … LIMIT子句

删除课程表中没有章节信息的课程

sql

DELECT a 
FROM imc_course a
LEFT JOIN imc_chapter b ON a.course_id =b.course_id
WHERE 	b.course_id IS NULL

删除课程方向表中重复的课程方向，保留方向ID最小的一条，并在方向名称上增加唯一索引

sql

# 删除课程方向重复并且保留方向Id最小的一条
DELETE a
FROM imc_type a
JOIN (
		SELECT type_name,MIN(type_id) AS min_type_id, count(*)
		FROM imc_type
		GROUP BY type_name HAVING count(*)>1
	  ) b 
ON a.type_name=b.type_name AND a.type_id>min_type_id

# 建立唯一索引
CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name)

UPDATE语句

sql

UPDATE table_name
SET col_name1{expr1 | DEFAULT}
	[,col_name2{expr2 | DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT ROW_count]

编写UPDATE语句的思路

确定要更新的数据存储在那张表中 UPDATE子句
确定要更新的新的列及值 SET子句
确认更新数据的条件 WHERE 子句

sql

show warnings ;# 显示warning信息

系统函数

常用的时间函数

函数名	说明
CURDATE ()/CURTIME()	返回当前日期/时间
NOW()	返回当前的日期和时间
DATE_FORMAT(date_fmt,fmt)	按照fmt的格式对日期进行格式化
SEC_TO_TIME(seconds)	把秒数转换为（小时:分:秒)
TIME_TO_SEC(time)	把时间(小时:分:秒)转换为秒数
DATEDIFF(date1,date2)	返回date1和date2两个日期相差的天数
DATE_ADD(date,INTERVAL expr unit)	对给定的日期增加或减少指定的时间单元（unit:DAY天/HOUR小时/MINUTE分钟/SECOND秒）
EXTRACT(unit FROM date)	返回日期date的指定部分
UNIX_TIMESTAMP()	返回unix 时间戳(1970年1月1号到当前时间经过的秒数）
FROM_UNIXTIME()	把unix时间戳转化为日期时间

常用的字符串函数

函数名	说明
CONCAT(str1,str2,…)	把字符串str1、str2连接为一个字符串返回当前日期/时间
CONCAT_WS(sep,str1,str2,…)NOW()	用指定的分隔符连接字符串返回当前的日期和时间
CHAR_LENGTH(str)DATE_FORMAT(date_fmt,fmt)	返回字符串的字符个数按照fmt的格式对日期进行格式化
LENGTH(str)	返回字符串str的字节个数把秒数转换为（小时:分:秒)
FORMAT(N,D[,locale])TIME_TO_SEC(time)	将数字N格式化为格式locale，如”#,###,###.##“，并舍入到D位小数把时间(小时:分:秒)转换为秒数
LEFT(str,len)/RIGHT(str,len)DATEDIFF(date1,date2)	从字符串的左/右边起返回指定len长度的子字符串返回date1和date2两个日期相差的天数
SUBSTRING(str,pos,[len])	从字符串str的pos位置起返回长度为len的子串对给定的日期增加或减少指定的时间单元（unit:DAY天/HOUR小时/MINUTE分钟/SECOND秒）
SUBSTRING_INDEX(str,delim,count)EXTRACT(unit FROM date)	返回字符串str按delim分割的前count个子字符串返回日期date的指定部分
LOCATE(substr,str)	在字符串str中返回子串substr第一次出现的位置返回unix 时间戳(1970年1月1号到当前时间经过的秒数）
TRIM( [remstr FROM ] str)FROM_UNIXTIME()	从字符串两端删除不需要的字符remstr把unix时间戳转化为日期时间

其它常用函数

函数名	说明
ROUND(x,d)	对数值x进行四舍五入保留d位小数
RAND()	返回一个在0和1间的随机数
CASE WHEN [condition] THEN result [WHEN[condition] THEN result…] [ELSE result] END	用于实现其它语言中的case when功能，提供数据流控制
MD5(str)	返回str的MD5值

CASE WHEN

显示每个用户的昵称和性别，再只显示男性

sql

SELECT user_nick
	   ,CASE WHEN sex=1 THEN '男'
	   		 WHEN sex=0 THEN '女'
	   		 ELSE '未知'
	    END  AS '性别'
  FROM user
  WHERE CASE WHEN sex=1 THEN '男'
	   		 WHEN sex=0 THEN '女'
	   		 ELSE '未知'
	    END  ='男'

公共表表达式CTE(Common Table Expressions)

MySQL8.0版本之后才可以使用
功能类似于子查询，都可以在复杂的查询中定义一个临时表，这个表不用于存储数据，仅在查询执行期间有效，但同子查询不同的是由CTE定义的是一个命名临时表
CTE生成的临时表可以在查询中多次引用，并且还可以实现自身的自引用
性能和可读性都要优于子查询

CTE基础语法

-- WITH开头
WITH [RECURSIVE]  -RECURSIVE,定义的时候指定了代表是可自引用的临时表，通常用于递归查询
-- 定义临时表的表名了，后面是可选列的列表，注意一定要和下面子查询的列是一一对应的
cte_name [(column_list)] AS (
 	query
)
-- 如果要在一个查询中用多个公共表表达式的话，还可以在下面继续定义其它的公共表表达式
[,cte_name [(column_list)] AS (
 	query
)]
--使用公共表表达式 
SELECT * FROM cte_name;

CTE递归生成序列

sql

WITH RECURSIVE cte_test AS  (
SELECT 1 AS n
UNION ALL 
SELECT 1+n FROM cte_test WHERE n<10 --n<10避免无限循环下去
)
SELECT * FROM cte_test

SQL ​

初始SQL ​

DCL（Data Control Language) ​

给账号授权 ​

回收用户权限 ​

DDL(Data Definition language) ​

DDL 分类 ​

建立数据库imc_db ​

创建数据库 ​

建立数据库imc_db中的表 ​

修改表语句 ​

删除表语句 ​

清空表 ​

重命名表 ​

DML(Data Manipulation language) ​

编写INSERT语句的思路 ​

编写查询语句的思路 ​

MySQL的运算符 ​

关联查询JOIN ​

内关联 INNER JOIN ​

外关联左连接 ​

外关联右连接 ​

GROUP BY 分组查询 ​

HAVING子句过滤分组结果 ​

常用的聚合函数 ​

order by 子句对查询结构进行排序 ​

limit 子句限制返回结构集的行数 ​

创建视图 ​

按条件删除表中的数据 ​

编写DELETE语句的思路 ​

删除课程表中没有章节信息的课程 ​

UPDATE语句 ​

编写UPDATE语句的思路 ​

系统函数 ​

常用的时间函数 ​

常用的字符串函数 ​

其它常用函数 ​

CASE WHEN ​

公共表表达式CTE(Common Table Expressions) ​

CTE递归生成序列 ​

SQL

初始SQL

DCL（Data Control Language)

给账号授权

回收用户权限

DDL(Data Definition language)

DDL 分类

建立数据库imc_db

创建数据库

建立数据库imc_db中的表

修改表语句

删除表语句

清空表

重命名表

DML(Data Manipulation language)

编写INSERT语句的思路

编写查询语句的思路

MySQL的运算符

关联查询JOIN

内关联 INNER JOIN

外关联左连接

外关联右连接

GROUP BY 分组查询

HAVING子句过滤分组结果

常用的聚合函数

order by 子句对查询结构进行排序

limit 子句限制返回结构集的行数

创建视图

按条件删除表中的数据

编写DELETE语句的思路

删除课程表中没有章节信息的课程

UPDATE语句

编写UPDATE语句的思路

系统函数

常用的时间函数

常用的字符串函数

其它常用函数

CASE WHEN

公共表表达式CTE(Common Table Expressions)

CTE递归生成序列