SQL语句超全总结

1. 数据相关操作

1.1 登录

-- u和p后面没有空格
mysql -u用户名 -p密码
mysql -hIP地址 -u用户名 -p密码
mysql --host=IP地址 --user=用户名 --password=密码

1.2 退出

quit 或 exit

1.3 数据库备份和还原

• 备份格式：DOS下，未登录时。(这是一个可执行文件exe，在bin文件夹)

mysqldump -u用户名 -p密码 数据库 > 文件的路径

• 还原格式：mysql中的命令，需要登录后才可以操作

use 数据库;
source 导入文件的路径;

2. SQL语句

2.1 DDL数据定义语言

2.1.1 操作数据库

• 创建数据库

-- 创建数据库
create database 数据库名;
-- 判断数据库是否已经存在，不存在则创建数据库
create database if not exists 数据库名;
-- 创建数据库并指定字符集
create database 数据库名 character set 字符集;

• 查看数据库

-- 查看所有数据库
show databases;
-- 查看某个数据库的创建语句
show create database 数据库名;

• 修改数据库

-- 修改数据库默认的字符集
alter database 数据库名 default character set 字符集;

• 删除数据库

drop database 数据库名;
-- 判断数据库是否存在，存在再删除
drop database if exists 数据库名;

• 使用数据库

-- 查看正在使用的数据库
select database(); #使用mysql中的全局函数
-- 使用/切换数据库
use 数据库名;

2.1.2 操作表结构

• 创建表

create table 表名 (
	列名1 数据类型1,
	列名2 数据类型2,
	....
	列名n 数据类型n
);
-- 最后一列，不需要加逗号

• 查看表

-- 查看某个数据库中的所有表
show tables;
-- 表格格式返回
desc 表名;
-- 以sql格式返回
show create table 表名;

• 快速创建一个表结构相同的表

create table 新表名 like 旧表名;

• 删除表

-- 直接删除表
drop table 表名;
-- 判断表是否存在，如果存在则删除表
drop table if exists 表名;

• 修改表结构

-- 添加新字段
alter table 表名 add 字段 字段类型;
-- 修改字段类型MODIFY
alter table 表名 modify 字段 新的类型;
-- 修改字段名CHANGE
alter table 表名 change 旧字段名 新字段名 类型;
-- 删除字段DROP
alter table 表名 drop 字段名;

-- 修改表名
rename table 表名 to 新表名;
alter table 表名 rename to 新的表名;
-- 修改字符集character set
alter table 表名 character set 字符集;

2.2 DML数据操纵语言

• 插入

-- 插入全部字段
insert into 表名 (列名1,列名2,列名3...) values (值1,值2,值3...);
-- 插入全部字段另一写法
insert into 表名 values (值1,值2,值3...)

-- 字符和日期型数据应包含在单引号中
-- 不指定列或使用null，表示插入空值

• 蠕虫复制：将一张已经存在的表中的数据复制到另一张表中

-- 将表2中的所有列复制到表1中
insert into 表名1 select * from 表名2;
-- 只复制部分列
insert into 表名1(列1，列2) select 列1,列2 from 表2;

• 更新表记录

update 表名 set 列名=值 [where 条件表达式]

-- 不带条件修改数据(修改所有行)
update 表名 set 字段名=值;
-- 带条件修改数据
update 表名 set 字段名=值 where 字段名=值;

• 删除表记录

delete from 表名 [where 条件表达式]

-- 不带条件删除数据[会删除表中所有记录]
delete from 表名;
-- 带条件删除数据
delete from 表名 where 字段名=值;
-- 使用truncate删除表中所有记录
truncate table 表名;	

-- truncate和delete区别：
truncate相当于删除表的结构再创建一张表

2.3 DQL数据查询语言

select 列名 from 表名 [where 条件表达式]

2.3.1 简单查询

• 查询所有数据

select * from 表名;

• 查询指定列

select 字段名1,字段名2... from 表名; -- 多个列之间以逗号分隔

2.3.2 使用别名查询

• 显示的时候使用别名，但并不修改表结构

-- as可以省略
select 字段名1 as 别名,字段名2 as 别名... from 表名; -- 对列指定别名
select 字段名1 as 别名,字段名2 as 别名... from 表名 as 表别名; -- 列和表同时指定别名

2.3.3 清楚重复值

• 查询指定列时，结果不出现重复数据

select distinct 字段名 from 表名;

2.3.4 查询结果参与运算

-- 某列数据和固定值运算
select 列名1+固定值 from 表名;
-- 某列数据和其他列数据参与运算
select 列名1+列名2 from 表名;

-- 注：参与运算的必须是数值类型

2.3.5 条件查询

• 流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

select 字段名 from 表名 where 条件;

• 运算符

比较运算符	说明
<、>、<=、>=、=、<>	<> 在SQL中表示不等于，在mysql中也可以使用!=
between…and	表示在一个范围之内，包括边界值
in(集合)	集合表示多个值，用逗号分隔
like ‘刘%’	模糊查询
is null	查询某一列为null值，不能写=null

逻辑运算符	说明
and 或 &&	与
or 或 ||	或
not 或 !	非

• 关键字in

-- in里面的每个数据都会作为一次条件，满足条件就会显示
select 字段名 from 表名 where 字段 in(数据1,数据2...);

• 范围查询

between 值1 and 值2 -- 表示从值1到值2范围，包括值1和值2

• 关键字like：表示模糊查询

select * from 表名 where 字段名 like '通配符字符串';

通配符	说明
%	匹配任意多个字符串
-	匹配一个字符

2.3.6 排序order by

• 通过order by子句，可将查询出的结果进行排序(排序只一种显示方式，不会影响数据库中数据的顺序)
  • 升序[默认值]：asc；降序：desc

select 字段名 from 表名 where 字段=值 order by 字段名 [asc|desc];

• 单列排序：只按某一个字段进行排序
• 组合排序：同时对多个字段进行排序

select 字段名 from 表名 where 字段=值 order by 字段名1[asc|desc],字段名2[asc|desc];

2.3.7 聚合函数

• 之前的查询都是横向查询，即根据条件一行一行的进行判断；而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值

• 聚合函数会忽略空值null

• SQL中的聚合函数

  聚合函数	作用
  max(列名)	求这一列的最大值
  min(列名)	求这一列的最小值
  avg(列名)	求这一列的平均值
  count(列名)	统计这一列有多少条记录
  sum(列名)	对这一列求总和

• 语法：

select 聚合函数(列名) from 表名;

• 对于null的记录不会统计，建议统计个数不要使用可能为null的列。若需要将null也统计进去：

-- 如果列名不为空，返回这列的值；如果为null，返回默认值
ifnull(列名，默认值)

-- 如果记录为null，给个默认值(这样统计数据不会遗漏)
select count(ifnull(id,0)) from 表名;

2.3.8 分组查询group by

• 使用group by语句对查询信息进行分组，将结果中相同内容作为一组，并返回每组的第一条数据

select 字段1,字段2... from 表名 group by 分组列 [having 过滤条件];

• 分组的目的是为了统计，一般分组会和聚合函数一起使用

• 分组查询若要添加条件使用having，而不使用where。

  • having和where的区别

  	作用
  where子句	在分组之前过滤数据；where后面不可以使用聚合函数
  having子句	在分组之后过滤数据；having后面可以使用聚合函数

2.3.9 分页查询limit

• 限制查询记录的条数

select *|字段列表 [as 别名] from 表名 [where子句][group by子句][order by子句][limit子句];

• 语法格式：

  -- offset：起始行数；从0开始计数，若省略则默认是0
  -- length：返回的行数
  limit offset,length;
  

2.4 DCL数据控制语言

2.4.1 创建用户

• 建立一个用户，默认自动授予其usage权限[usage：连接(登录)权限]

create user '用户名'@'主机名' identified by '密码';

• 关键字说明

  关键字	说明
  ‘用户名’	将创建的用户名
  ‘主机名’	指定该用户在哪个主机上可以登录。本地用户：localhost；可从任意远程主机登录：使用通配符 %
  ‘密码’	该用户的登录密码；密码可以为空，若为空则该用户可以不需要密码登录服务器

2.4.2 给用户授权

• 用户名、主机名要与创建时所写的相同，且加单引号

grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';

• 关键字说明

  关键字	说明
  权限	用户的权限，例：create、update、select等。所有的权限则使用all
  数据库名.表名	对于哪些数据库的哪些表。所有数据库和表的相应操作权限则可用*表示

2.4.3 取消授权

revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';
-- 撤销所有权限
revoke all on 数据库名.表名 from '用户名'@'主机名';

2.4.5 查看权限

show grants for '用户名'@'主机名';

2.4.6 删除用户

drop user '用户名'@'主机名';

2.4.7 修改管理员密码

• 需要在未登录MySQL的情况下操作，新密码不需要加引号

mysqladmin -uroot -p password 新密码

2.4.8 修改普通用户密码

• 需要在登陆 MySQL 的情况下操作，新密码要加单引号

set password for '用户名'@'主机名' = password('新密码');

3. 数据库表的约束

3.1 约束的作用

• 对表中数据进行限制，保证数据的正确性、有效性和完整性。(约束在创建表的时候添加比较合适)

3.2 约束种类

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check [注：mysql不支持]

3.3 主键约束

1. 作用：用来唯一标识数据库中的每一条记录

2. 哪个字段应作为表的主键？

   • 通常不用业务字段作为主键，单独给每张表设计一个id字段作为主键；
   • 主键是给数据库和程序使用的，不是给最终的客户使用的。故：主键有无含义没关系，只有不重复、非空就行。

3. 主键的特点

   • 非空(not null)、唯一

4. 创建主键

-- 在建表的时候给字段添加主键
字段名 字段类型 primary key
-- 在已有表中添加主键
alter table 表名 add primary key(字段名)；

5. 删除主键

alter table 表名 drop primary key;

6. 主键自增
   • 若我们自己添加主键，很有可能重复。故一般在插入新记录时，让数据库自动生成主键字段的值

auto_increment  -- 自动增长(字段类型必须是整数类型)

7. 修改自增长的默认起始值
   • 默认auto_increment的开始值是1
   • 修改起始值：

-- 创建表时指定起始值
create table 表名(
	列名 int primary key auto_increment
)auto_increment=起始值;
-- 创建好以后修改起始值
alter table 表名 auto_increment=起始值;

8. delete和truncate对自增长的影响
   • delete：删除所有记录之后，对自增长没有影响；
   • truncate：删除以后，自增长又重新开始；

3.4 唯一约束

1. 唯一约束：表中某一列不能出现重复的值
2. 基本格式

字段名 字段类型 unique

3.5 非空约束

1. 非空约束：某一列不能为null
2. 基本格式

字段名 字段类型 not null

3. 默认值

字段名 字段类型 default 默认值

4. 若一个字段设置了非空与唯一约束，该字段与主键的区别是什么？
   • 一张表只能有一个主键
     • 不能出现多个主键；
     • 主键可以是单列也可以是多列
   • 自增长只能用在主键上

3.6 外键约束

1. 什么是外键约束

   • 外键：从表中，与主表主键对应的那一列
   • 主表：一方，用来约束别人的表
   • 从表：多方，被别人约束的表

2. 创建约束

-- 建表时增加外键
[constraint][外键约束名称] foreign key(外键字段名) references 主表名(主键字段名)
-- 已有表增加外键
alter table 从表 add [constraint][外键约束名称] foreign key(外键字段名) references 主表(主键字段名)

3. 删除外键

alter table 从表 drop foreign key 外键名称;

4. 外键的级联

   • 级联操作：在修改和删除主表的主键时，同时更新或删除副表的外键值
   • 语法：更新或删除主表中的主键，从表中的外键列也自动同步更新

   级联操作语法	描述
   on update cascade	级联更新，只能是创建表的时候创建级联关系
   on delete cascade	级联删除

alter table 从表 add [constraint][外键约束名称] foreign key(外键字段名) references 主表(主键字段名) on update cascade on delete cascade

3.7 小结

约束名	关键字	说明
主键	primary key	唯一、非空
默认	default	若一列没有值，则使用默认值
非空	not null	该列必须有值
唯一	unique	该列不能有重复值
外键	foreign key	主表中主键列，在从表中外键列

4. 表与表之间的关系

4.1 一对多

• 建表原则：在从表创建一个字段，字段作为外键指向主表的主键

4.2 多对多

• 建表原则：创建一张中间表，中间表至少包含两个字段。这两个字段分别作为外键指向各自一方的主键

4.3 一对一

• 建表原则

一对一建表原则	说明
外键唯一	主表的主键和从表的外键(唯一)，形成主外键关系；外键唯一unique
外键是主键	主表的主键和从表的主键，形成主外键关系

4.4 小结

表与表的关系	关系的维护
一对多	通过主外键约束
多对多	通过中间表，中间表与两个表是多对一
一对一	特殊的一对多，多方加唯一约束；从表的主键又是外键

5. 表连接查询

5.1 内连接

5.1.1 隐式内连接

• 条件使用where指定

select 字段名 from 表1,表2 where 条件

5.1.2 显示内连接

• 使用 inner join … on 语句，可省略 inner

select 字段名 from 表1 [inner] join 表2 on 条件 [where 过滤条件]	-- where添加过滤条件

5.2 外连接

5.2.1 左外连接

• 可以理解为：在内连接的基础上保证左表的数据全部显示

• 使用 left outer join … on ，可省略 outer

select 字段名 from 左表 left [outer] join 右表 on 条件

5.2.2 右外连接

• 可以理解为：在内连接的基础上保证右表的数据全部显示

6.子查询

6.1 概念

• 一个查询的结果做为另一个查询的条件
• 有查询的嵌套，内部的查询称为子查询
• 子查询要使用括号

6.2 结果的三种情况

• 单行单列
• 多行单列
• 多行多列

6.3 结果是一个值

1. 结果是单行单列

   • 子查询结果在where后面作为条件

   • 父查询使用比较运算符

   select 字段名 from 表 where 字段=(子查询);
   

2. 结果是多行单列

   • 子查询结果类似一个数组

   • 父查询使用运算符 in

   select 字段名 from 表 where 字段 in (子查询);
   

6.4 结果是多行多列

• 子查询结果在from后面作为一张表再次查询

select 字段名 from (子查询) 表别名 where 条件;

6.5 小结

• 子查询结果是单列，则在 where 后面作为条件
• 子查询结果是多列，则在 from 后面作为表进行二次查询

7. 事务

7.1 手动提交

功能	SQL语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

7.2 自动提交

• MySQL 默认开始自动提交事务

• MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务

• 取消自动提交

  -- 查看MySQL是否开启自动提交事务
  select @@autocommit;	-- @@表示全局变量；1表示开启，0表示关闭
  -- 取消自动提交事务
  set @@autocommit = 0;
  

7.3 回滚点

• 设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候

• 操作语句

  回滚点操作语句	语句
  设置回滚点	savepoint 名字
  回到回滚点	rollback to 名字

7.4 事务的隔离级别

• 事务的四大特性

• 原子性、一致性、隔离性、持久性

• 可能引发的并发访问问题

  并发访问的问题	含义
  脏读	一个事务读取到了另一个事务中尚未提交的数据
  不可重复读	一个事务中两次读取的数据内容不一致；要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
  幻读	一个事务中两次读取的数据的数量不一致；要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

• 四种隔离级别

  • 上面的级别最低，下面的级别最高
  • 隔离级别越高，性能越差，安全性越高

  级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
  1	读未提交	read uncommitted	是	是	是
  2	读已提交	read committed	否	是	是	Oracle和SQL Server
  3	可重复读	repeatable read	否	否	是	MySQL
  4	串行化	serializable	否	否	否

• 相关命令

-- 查询全局事务隔离级别
select @@tx_isolation;
-- 设置隔离级别[需要退出 MySQL 再重新登录才能看到隔离级别的变化]
set global transaction isolation level 级别字符串;

8. DOS命令窗口操作数据乱码问题

• 因为系统cmd窗口的编码是GBK，而mysql的是utf-8，故有时会出现乱码问题；
• 查看MySQL内部设置的编码

show variables like 'character%'; -- 查看包含character开头的全局变量

• 解决方案：修改client、connection、results的编码为GBK，保证和DOS命令行编码保持一致

  • 单独设置

  单独设置	说明
  set character_set_client=gbk;	修改客户端的字符集为GBK
  set character_set_connection=gbk;	修改连接的字符集为GBK
  set character_set_results=gbk;	修改查询的结果字符集为GBK

  • 同时设置三项

    set names gbk;
    

    | 否 | |

• 相关命令

-- 查询全局事务隔离级别
select @@tx_isolation;
-- 设置隔离级别[需要退出 MySQL 再重新登录才能看到隔离级别的变化]
set global transaction isolation level 级别字符串;

8. DOS命令窗口操作数据乱码问题

• 因为系统cmd窗口的编码是GBK，而mysql的是utf-8，故有时会出现乱码问题；
• 查看MySQL内部设置的编码

show variables like 'character%'; -- 查看包含character开头的全局变量

• 解决方案：修改client、connection、results的编码为GBK，保证和DOS命令行编码保持一致

  • 单独设置

  单独设置	说明
  set character_set_client=gbk;	修改客户端的字符集为GBK
  set character_set_connection=gbk;	修改连接的字符集为GBK
  set character_set_results=gbk;	修改查询的结果字符集为GBK

  • 同时设置三项

    set names gbk;
    

  • 退出DOS命令行就失效了，需要每次都配置