SQL语句的格式和使用（ DDL\DML\DQL\DCL）

结构化查询语言。一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

SQL的特点

1. SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体
2. 面向集合的语言
3. 非过程语言
4. 类似自然语言，简洁易用
5. 自含式语言，又是嵌入式语言。可独立使用，也可嵌入到宿主语言中。

SQL的分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库、表、字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL数据定义语言

数据定义语言，用来定义数据库对象（数据库、表、字段）

一、创建数据库

        <!--数据库操作-->
        <!--创建表结构-->
           create table 表名(
           字段 字段类型 [约束] [comment 注释]
           ...........

           [constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名))
           )[comment 注释];  

        <!--外键约束-->
        <!--建完表后，添加外键-->
        	alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);

二、操作数据库

       <!--创建数据库-->
            create database [if not exist] 数据库名 [default charset 字符集];

        <!--删除数据库-->
            drop database [if not exist] 数据库名;

        <!--切换数据库-->
            use 数据库名; 
     
        <!--查询所有数据库-->
            show databases;

        <!--查询当前数据库-->
            select database();

        <!--查询当前数据库所有表-->
            show tables;

        <!--查询指定表结构-->
            desc 表名;

        <!--查询指定表的建表语句-->
            show create table 表名;

三、修改数据库

        <!--表操作-修改-->
        <!--添加字段-->
            alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
        <!--修改数据类型-->
            alter table 表名 modify 字段名 新数据类型(长度);
        <!--修改字段名和字段类型-->
            alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
        <!--修改表名-->
            alter table 表名 rename to 新表名;

四、删除

        <!--删除表-->
        	drop table 表名;
        <!--删除表时判断表是否存在,若存在则删除-->
			drop table if exists 表名;
        <!--删除字段-->
            alter table 表名 drop 字段名;

DML数据操作语言

数据操作语言，用来对数据库表中的数据进行增删改

一、增加

    <!--给指定字段添加数据-->
        insert into 表名 (字段列表) values (值列表);
    <!--给全部字段添加数据-->
        insert into 表名 values(值列表);

    <!--批量添加数据（指定字段）-->
        insert into 表名 [(字段列表)] values (值列表),(值列表);

    <!--批量添加数据(全部字段)-->
        insert into 表名 values (值列表1), (值列表2)....;

二、修改

    <!--修改数据-->
        update 表名 set 字段名=值 [where 条件];

三、删除

    <!--删除数据-->
        delete from 表名 [where 条件];
    <!--删除数据库-->
        delete from 表名;

    <!--删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）-->        
        truncate table 表名；

DQL数据查询语言

数据查询语言，用来查询数据库中表的记录

查询分为：

• 基本查询（不带任何条件）

• 条件查询（where）

• 分组查询（group by）

• 排序查询（order by）

• 分页查询（limit）

一、基本查询

不带任何条件

 <!--基础查询-->
        <!--查询多个字段-->
            select */字段列表 from 表名;
        <!--字段设置别名-->
            select 字段 [as] 别名 from 表名;
        <!--去除重复记录-->
            select distinct 字段列表
  

二、条件查询

条件：可以有多个条件

    <!--语法-->
        select 字段列表 from 表名 where 条件;

常用的比较运算符如下:

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between ... and ...	在某个范围之内(含最小、最大值)
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

常用的逻辑运算符如下:

逻辑运算符	功能
and 或 &&	并且 (多个条件同时成立)
or 或 ||	或者 (多个条件任意一个成立)
not 或 !	非 , 不是

三、聚合函数

注意 : 聚合函数会忽略空值，对NULL值不作为统计。

函数	count	max	min	avg	sum
说明	统计数量	最大值	最小值	平均值	最大值

        <!--语法-->
            select 聚合函数(字段) from 表名;

四、分组查询

分组： 按照某一列或者某几列，把相同的数据进行合并输出。

执行顺序: where -> 聚合函数 -> having 

注意事项：

1.where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2.判断条件不同：where不能对聚合函数进行判断，而having可以。

        <!--语法-->
            select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

五、排序查询

排序方式：默认ASC升序排序，升序排序ASC可省略不写。
                  DESC：降序

        <!--语法-->
            select 字段列表 from 表名[group by  分组字段 ]
            			   order by 字段1 排序方式1, 字段2 排序方式2;

六、分页查询

起始索引：计算公式 ： 起始索引 = （查询页码 - 1）* 每页显示记录数

1. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

2. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

    <!--语法-->
        select 字段列表 from 表名 limit 起始索引，查询记录数;

七、多表查询

多表查询：查询时从多张表中获取所需数据

1.连表查询

内连接：相当于查询 表1、表2 交集部分数据

隐式内连接语法

    <!--隐式内连接语法-->
    	select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接语法

    <!--显式内连接语法-->
    	select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

2.外连接

左外连接查询 表1(左表) 的所有数据，也包含表1和表2交集部分的数据。

    <!--左外连接语法结构-->
    	select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

右外连接查询 表2(右表) 的所有数据，也包含表1和表2交集部分的数据。

    <!--右外连接语法结构-->
    	select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

3.子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

    <!--子查询语句-->
    	select  *   from   t1  whereE  column1 =  ( select column1  from t2 ... );

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值[一行一列]）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

1. where之后

2. from之后

3. select之后

标量子查询

常用的操作符： = <> > >= < <=

案例：查询"教研部"的所有员工信息

可以将需求分解为两步：

1. 查询 "教研部" 部门ID

2. 根据 "教研部" 部门ID，查询员工信息

<!--1.查询"教研部"部门ID-->
select id from tb_dept where name = '教研部';    #查询结果：2

<!--2.根据"教研部"部门ID, 查询员工信息-->
select * from tb_emp where dept_id = 2;

<!--合并出上两条SQL语句为完整的语句-->
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

案例：查询"教研部"和"咨询部"的所有员工信息

分解为以下两步：

1. 查询 "销售部" 和 "市场部" 的部门ID

2. 根据部门ID, 查询员工信息

<!--1.查询"销售部"和"市场部"的部门ID-->
select id from tb_dept where name = '教研部' or name = '咨询部';    #查询结果：3,2
<!--2.根据部门ID, 查询员工信息-->
select * from tb_emp where dept_id in (3,2);

<!--合并以上两条SQL语句为完整语句-->
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

案例：查询与"韦一笑"的入职日期及职位都相同的员工信息

可以拆解为两步进行：

1. 查询 "韦一笑" 的入职日期 及 职位

2. 查询与"韦一笑"的入职日期及职位相同的员工信息

<!--1.查询"韦一笑"的入职日期 及 职位-->
select entrydate , job from tb_emp where name = '韦一笑';  #查询结果： 2007-01-01 , 2
<!--2.查询与"韦一笑"的入职日期及职位相同的员工信息-->
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);

<!--合并以上两条SQL语句-->
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');

表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

案例：查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

分解为两步执行：

1. 查询入职日期是 "2006-01-01" 之后的员工信息

2. 基于查询到的员工信息，在查询对应的部门信息

<!--1.查询入职日期是 "2006-01-01" 之后的员工信息-->
select * from emp where entrydate > '2006-01-01';

<!--合并SQL语句-->
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

 DCL数据控制语言

数据控制语言，用来定义访问权限和安全级别

一、创建用户

注：①主机名可以使用%通配。

        ②这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

--查询用户
	use mysql;
	select * from user;
 
 
	--创建用户语句
	CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
 
	--例：创建用户cast，只能够在当前主机localhost访问，密码111111；
	create user 'cast'@'localhost' identified by '111111';
 
	--例：创建用户super，可以在任意主机访问数据库，密码111111；
	create user 'super'@'%' identified by '111111';
 
 
	--修改用户密码语句
	ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
 
	--例：修改用户super的访问密码为1234；
	alter user 'super'@'%' identified with mysql_native_password by '1234';
 
 
	--删除用户语句
	DROP USER '用户名'@'主机名';
 
	--例：删除cast@localhost用户
	drop user 'cast'@'localhost';

二、用户授权

常用SQL权限

权限	说明
ALL，ALL PRIVLEGES	所有权限
SELECT	查询权限
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

注：

        多个权限之间，使用逗号分隔。

        授权时，数据库名和表名可以使用*进行通配，代表所有。

--查看权限
SHOW GRANT FOR '用户名'@'主机名'; 
 
--例：查看用户super拥有的所有权限
show grants for 'super'@'%';
 
 
--授予权限
GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
 
--授予用户super给用户cast的所有表的所有权限
grant all on cast.* to 'super'@'%';
 
 
--撤销权限
REVOKE 权限列表 ON 数据库名. 表名 FROM '用户名'@'主机名';
--例：从用户super撤销cast的所有权限
revoke all on cast.* from 'super'@'%';

三、用户权限

-- 查看root用户权限：
show grants for 'root'@'%';

四、撤销用户权限

-- 撤销test456的 select 权限：
REVOKE SELECT ON *.* FROM 'test456'@'127.0.0.1' ;

五、删除用户

-- 删除 test456 用户
drop user test456@'127.0.0.1';