SQL学习笔记（基础篇）

@TOC

一、文章目的

写此文旨在让自己通过整理所学知识，加深对SQL的理解，巩固所学知识；同时也方便后期复习，顺便方便同道中人，希望对有缘点进来的你有所帮助，觉得可以别忘记点赞藏。不奢求有打赏，但愿对您有所帮助！哪里写错了还望批评指正。

二、SQL简介

SQL，全称Structured Query Language（结构化查询语言） 是用于管理关系数据库管理系统（RDBMS），其通用语法包括：
1、可以单行或多行书写，以分号结尾；
2、可以使用空格或缩进来增强语句可读性；
3、注释：单行注释可以使用–或者#（其中#为MYSQL独有）加上注释内容；多行注释可以用/注释内容/，即用/**/包裹
4、关键字建议用大写

三、SQL语句分类

SQL语句主要分为四类，分别是DDL、DML、DQL、DCL

（一）DDL（Data Definition Language，数据库定义语言）

数据库数据库，首先得有库，才能进行后学习后面的语言，那么就先来学习第一种数据库语句DDL。
DDL主要用于定义数据库对象，如创建、删除数据库，创建表等等；常用语句的语法如下：

1. 查询所有数据库：show databases;
2. 查询当前数据库:show database();
3. 创建数据库：create database [if not exists] 数据库名 [dufault charset 字符集] [callate 排序规则].注意[]表示里面的代码可以有可无，即可选代码。
4. 删除数据库：drop database [if exists] 数据库名;注意，此操作存在风险，请勿轻易操作
5. 使用/切换数据库：use 数据库名;
6. 查询所有表:show tables;
7. 查询表结构：desc 表名;
8. 查询指定表的建表语句：show create table 表名;
9. 建表语句：create table 表名 ( 字段1 字段类型(长度) [comment 字段1注释] [约束], 字段2 字段类型(长度) [comment 字段2注释] [约束],...,字段n 字段类型(长度) [comment 字段n注释] [约束]) [comment 表注释];此处字段类型暂不记录，不懂就查阅各个数据库的数据类型，约束也自行查阅，可以参考菜鸟教程-SQL约束
10. 修改表名：alter table 表名 rename to 新表名;
11. 删除表：drop table [if exists] 表名;
12. 删除并创建表：truncate table 表名;
13. 添加字段：alter table 表名 add 字段名 字段类型(长度) [comment 字段注释] [约束];
14. 修改字段类型: alter table 表名 modify [column] 字段名 新数据类型(长度);
15. 修改字段类型（SQLServer中用法）:alter table 表名 alter column 字段名 字段类型(长度)[约束];或者alter table 表名 modify column 字段 字段类型 [约束];
16. 修改字段名以及类型：alter table 表名 change [column] 旧字段 新字段名 字段类型(长度) [comment 注释];
17. 修改字段名（SQLServer用法）：alter table 表名 rename column 旧字段名 to 新字段名
18. 删除字段：alter table drop [column] 字段名;

（二）DML（Data Manipulation Language，数据库操作语言）

有了库、表（容器），那么，接下来当然是考虑一下如何往容器中添加东西或者改变，移除其中的东西，那么接下来就是用于实现增删改的语言DML。
DML，用于对数据库表中的数据进行增删改（不包括查）

添加/插入数据记录（关键字：INSERT）

1. 指定字段单条记录添加：inser into 表名(字段名1,字段名2, ...) values(值1, 值2, ...);
2. 指定字段批量添加：inser into 表名(字段名1,字段名2, ...) values(值1, 值2, ...),(值1, 值2, ...), ...;
3. 全字段单条添加：inser into 表名 values(值1, 值2, ...);,当然，也可以自己手动按照第一点将所有字段列出来.
4. 全字段批量添加：inser into 表名 values(值1, 值2, ...),(值1, 值2, ...), ...;

修改记录值（关键字：UPDATE）

语法：update 表名 set 字段名1 = 新值1, 字段2 = 新值2, ...[where 条件];，注：若无条件语句，则整表更新

删除表中记录（关键字：DELETE）

语法：delete from 表名 [where 条件] ，同上，若无条件，则删除整表记录，此操作有一点风险，请勿轻易整表删除

（三）DQL（Data Query Language,数据库查询语言）

整体语法：select 字段列表 from 表名列表 where 条件别表 group by 分组字段列表 having 分组条件列表 order by 排序列表 [limit 分页参数].
注意:

1. 字段列表中字段之间用’,'号隔开，其他列表亦然。
2. 此语法中的[] 只是作为一个强调作用，并不意味这其是可选语句，也不是以为着其他语句是必选语句；因为limit 分页语法并不通用，有些数据库不支持此关键字（此为mysql的分页用法）
   从以上语法，将查询语句拆分为基本查询语句、条件查询、分组查询、排序查询、分页查询。基础篇只记录普通查询，高级查询如连接查询、子查询、联合查询等不记录

基本查询

关键字where 之前的语句为最基本的查询语句，不带where条件即是全表查询，将表中所有记录查询出来。

1. 多字段查询：select 字段1,字段2, ... from 表名; 若想给字段设置别名，可以使用关键字AS，如：select 字段1 as 别名1, 字段2 as 别名2, 字段 3, ... from 表名 as 表名的别名 .想给谁取别名，就在其后加上‘as 别名’ ,此处 ‘as’ 可以省列，即直接跟上别名也可，语法如下:select 字段1 别名1, ... from 表名 表名的别名 .
2. 全字段查询：select * from 表名;``或者select 字段列表(像1一样将所有字段列出来) from 表名;`.
3. 去重操作（关键字：distinct）：select distinct 字段列表 from 表名;可以去掉表中重复的记录。

条件查询（跟在关键字where后面）

基本查询语句是条件查询语句存在的必要条件，where语句不能单独存在。

1. 常用关键字：and（和）、or（或者）、in（包含）、not in（不包含）、like（像）、between and（在某某之间），and和or用于多条件之间的连接，如select * from emp where id = 1 or id = 2;、select * from emp where age = 19 and dept_id = 2:
2. where条件语句主要用到的条件比较符有：=(等于)、!=（不等于）、<>(不等于)、>(大于)、>=(大于等于)、<(小于)、<=（小于等于）、between a and b （在a和b之间）、like （模糊匹配，其常用通配符有’‘和’%'），其中‘’为单字符匹配，多少个‘_’就匹配多少个字符、‘%’为多字符匹配，如select * from a where b like '_影';表示从a表中搜索出字段b含有两个字且以‘影’字结尾的所有记录，select * from a where b like '%影';则表示从a表中搜索出b字段中以‘影’字结尾的所有记录，这里就不管他是多少个字了。

分组查询（group by）

分组查询语句也是离不开基本查询语句，group by语句往往 都会含有其条件语句的，即配合having 语句使用（非必须）；
当然了，要分组，一般离不开聚合函数，常用的聚合函数有：count、max、min、avg、sum，分别用于查询记录数、求最大值、求最小值、求平均值、求和，单独查询语法：select 聚合函数(字段) from 表名;
语法：select 字段列表 from 表名 [where 条件] group by 分组字段 [having 分组过滤条件]

1. 在分组查询中，若字段列表中的字段并不在聚合函数字段里面，那么它必须出现在分组字段中，如：select dept_id, sex, avg(age) from emp where age > 18 group by dept_id, age having avg(age) > 30; 此语句中dept_id和sex并不在聚合函数中，所以其必须出现在group by后面的分组字段中
2. 同为条件语句，where与having的区别在于：a、执行时机不同，where是分组前过滤，不满足where的不参与分组，having为分组后过滤，是对分组结果进行过滤，不满足不予显示；b、判断条件不同，where 不能对聚合函数进行判断，而having可以。

排序（order by 排序方式）

排序也是基于基本查询语句，排序语法比较简单

1. 排序方式：asc（升序）、desc（降序）
2. 语法：select 字段列表 from 表名 [where 查询条件] [group by 分组字段列表 [having 分组条件]] order by 字段1 排序方式1, 字段2 排序方式2, ...

分页

1. mysq 分页：select 字段列表 from 表名 limit 索引,记录数;，此处索引从零开始，起始索引=（查询页码-1）*每页显示记录数；若是查询第一页，起始索引可省略直接简写成select 字段列表 from 表名 limit 记录数;
2. oracle分页：select * from (select a.*,rownum r from (select *from 表名) a where rownum <= 截止索引) where r >= 起始索引，起始索引=(页码-1)每页记录数，截止索引=页码每页记录数-1
3. 其他数据库分页待添加

高级查询（多表查询）

多表查询常见有连接查询、联合查询、子查询，当然，也可以这几种合在一起

连接查询

内连接(inner join)

内连接相当于数学的交集，用于查询多张表之间相互交集的部分，其分为隐式内连接和显示内连接。

1. 隐式内连接：select 字段列表 from 表1,表2 where 条件...;，例如 select emp.name, emp.age, dept.name as deptName from emp , dept where emp.dept_id = dept.id –查询员工的姓名、年龄以及所在部门名称
2. 显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件;例如select emp.name, emp.age, dept.name as deptName from emp inner join dept on emp.dept_id = dept.id 所查询结果与1相同

外连接（left/right outer join）

外连接分两种，左外连接和右外连接

1. 左外连接：用于查询左表所有数据以及两表交集部分数据，语法：select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
2. 右外连接：用于查询右表所有数据以及两表之间交集部分，语法：select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;
   此外，还有一种自连接，相互连接的两张表是同一张表，其可以是内连接也可以是外连接

子查询（嵌套查询）

子查询：SQL语句中嵌套select 查询语句，它可以出现嵌套在insert/update/delete/select 等任何一个语句中。子查询分为：

1. 标量子查询，子查询结果为单个值的查询，其常用的操作符有： =、<> 、>= 、 < 、 <= ，如select *from emp a where a.dept = (select dept_id from dept where name = '研发部'); 此句成为标量子查询的前提是select dept_id from dept where name = '研发部'所得结果dept_id 有且仅有一个值
2. 列子查询，子查询结果为一列的，常用操作符： in 、not in、any、 some、all
3. 行子查询：子查询结果为一行的，常用操作符： = 、<> 、 in 、not in
4. 表子查询：子查询结果为多行多列的，常用操作符：in；如： insert into tb_user(user_id, user_name) select regist_id,user_name from user_regist where status = 1

（四）DCL（Data Control Language，数据库控制语言）

光有数据库，数据还不行，总不能随便来个人就删表删库，操作数据，故而就需要DCL语言了，DCL是用来管理数据库用户，访问权限的语言。废话不多说，直接上常用语句语法

数据库用户的管理(Mysql数据库用户管理)

1. 查询数据库用户：a、切换数据库，use mysql; 先将数据库切换到mysql；b、查询用户select * from user;
2. 创建用户：create user '用户名'@'主机名' identified by '密码';
3. 修改用户密码：alter user '用户名'@'主机名' identified with mysql_native_password by '密码';
4. 删除用户：drop user '用户名'@'主机名';

权限控制（Mysql数据库用户权限控制）

mysql的权限级别分全局（可以管理整个MySQL）、数据库（管理指定的数据库）、数据表（管理指定的表）、字段（管理指定数据库的指定表的指定字段），常用的权限如下图：

1. 查询已有权限：show grants for '@'主机名';
2. 授予权限：grant 权限列表 on 数据库.表名 to '用户名'@'主机名'; 此处若是想富裕全库全表的权限，则数据库.表名可用*.*代替，如grant
3. 撤销权限：revoke 权限列表 on 数据库.表名 from '用户名'@'主机名'
4. 刷新权限，使权限生效：flush privileges;
5. 以下使完整的用户创建到赋权过程：
   5.1、使用CREATE创建用户，创建 nmj用户,设置密码为123456，此时并没有权限
   CREATE USER 'nmj'@'localhost' IDENTIFIED BY '123456';
   5.2、授予nmj查询和添加test库的权限
   grant select,insert,update,delete,create,alter on test.* to 'nmj';
   5.3、使用GRANT创建用户并授权test库的所有操作
   grant all privileges on test.* to 'nmj'@'%' identified by "123456" with grant option;
   说明：此处WITH GRANT OPTION表示该用户可以将自己拥有的权限授权给别人，若不指定此选项，后期将无权给其他用户授权

题外话

此文是转载上班之余，经过三个晚上历时十多个小时整理所学知识而得（可能您觉得太久，没办法，初学，很多知识还未贯通，而且不常用CSDN，有些生疏了），创作不易，如需转载，还望注明出处。
权限图来源与文章