SQL语言

概述与作用

**概述：**结构化查询语言(Structured Query Language)简称SQL，SQL语句就是对数据库进行操作的一种语言。

**作用：**通过Sql语言可以对数据库管理系统中的数据库，表，表中的数据进行增删改查。

sql分类和语法

分类：

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

用来操作数据库和表

关键字：create,drop,alter等
DML（Data Manipulation Language）数据操作语言

用来对数据库中表的数据进行增删改

关键字：insert,delete, update等
DQL（Data Query Language）数据查询语言

用来对数据库中表的数据进行查询

关键字:select，from，where等
DCL（Data Control Language）数据控制语言（了解）

用来定义数据库的访问权限和安全级别，及创建用户。

关键字：grant， revoke等
TCL（Transaction Control Language）事务控制语言

用于控制数据库的事务操作

关键字: commit，rollback等

sql语法：

SQL语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
QL关键字本身不区分大小写。表，列等的名称具有与数据库相关的区分大小写
可以使用 – 或 # 或 /**/ 的方式完成注释

DDL（数据定义语言）

操作数据库

1-创建数据库

-- 创建数据库。默认：utf-8	
create database 数据库名;
-- 判断是否存在并创建数据库。存在：不创建；不存在：创建。默认：utf-8
create database if not exists 数据库名;
-- 创建数据库指定字符集。一般不用
create database 数据库名 character set 字符集(gbk);

2-查询数据库

-- 查询所有数据库
show databases;

-- 查看某个数据库的定义信息
show create database 数据库名称;

3-删除数据库

-- 删除数据库
drop database 数据库名;

4-数据库的其他操作

-- 查看当前使用的数据库
select database();

-- 切换数据库。注意：如果想要操作哪个库（创建表），就得先进入到该数据库中
use 数据库名;

操作表

注意：如果想要操作哪个库（创建表），就得先进入到该数据库中：use 数据库名；

1-创建表（重要）

-- 如果表存在就删除该表
drop table IF EXISTS 表名;
-- 创建数据库表，同时定义表列属性。	-- ps：表名会有关键字  解决：换名。	
create table 表名(
	字段名1 字段类型1(长度),		-- ps：只有字符串类型需要手动加长度，其它类型都不需要加长度(默认有)
    字段名2 字段类型2(长度)   
    .....
)

-- 创建空表
create table 表名;

-- 示例: 创建一个学生表(id name sex)
create table stu(
    id int,
    name varchar(10),
    sex varchar(2),
    price double,
    time date		    		
) 

-- 数据类型
java的数据类型      mysql数据库的数据类型
    int		    		int
    float		    	float
    double		    	double
    char/string	    	varchar

    date		    	datetime  YYYY-MM-DD HH:MM:SS  
                        date      yyyy-MM-dd
                        time      HH:MM:SS

2-查看表

-- 查看某个数据库中的所有表
show tables;

-- 查看表结构
desc 表名称;

-- 查询建表语句
show create table 表名;

-- 快速创建表结构。特点：2张表的结构一样
create table 新表名 like 旧表名;

3-修改表结构

-- 添加表列
alter table 表名 add 列名 类型;
-- 删除表列
alter table 表名 drop 列名;

-- 修改列名
alter table 表名 change 旧名称 新名称 类型;
-- 修改列类型
alter table 表名 modify 列名 新类型

-- 修改表名称
rename table 表名 to 新表名;

4-删除表

-- 直接删除表
drop table 表名;
-- 当指定表名存在时删除该表。常用在初始化数据库建表前
drop table if exists 表名;

DML（数据操作语言）

1-插入记录

-- 添加部分字段
insert into 表名 (字段名1, 字段名2, ...) values(值1, 值2, ...);

-- 按列定义顺序添加全部字段
insert into 表名 values (值1, 值2, 值3);

注意事项：

值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其它的字段类型的值必须使用引号引起。
如果要插入空值，可以不写字段，或者手动插入null

2-蠕虫复制

-- 将表2中数据复制插入到表1中。前提：表结构得一致
insert into 表名1 select * from 表名2;

3-修改记录

-- 根据条件修改数据。特点：按条件改
update 表名 set 字段名1 = 值1, 字段名2 = 值2 where 字段名=值 ;
-- 不带条件修改数据。特点：表中相应字段全改
update 表名 set 字段名1 = 值1, 字段名2 = 值2 ;

4-删除记录

-- 根据条件删除数据。特点：按条件删
delete from 表名 where 字段名=值; 
-- 不带条件删除数据。 特点：全删
delete from 表名;

-- truncate删除表记录。
truncate table 表名;

truncate和delete的区别：

delete是将表中的数据一条一条删除。truncate是将整个表摧毁，重新创建一个新的表，新的表结构和原来表结构一模一样
delete删除的数据能够找回。truncate删除的数据找不回来了

DQL（数据查询语言）

查询顺序、条件查询、运算查询

select....from...where..group by...having...order by...limit a,b

简单查询

-- 查询所有列
select * from 表名;

-- 查询指定列
select 字段名1,字段名2... from 表名; 

-- 别名查询
select 字段名1 as 别名, 字段名2 as 别名... from 表名 as 表别名; 

-- 清除重复值
select distinct 字段名 from 表名;

-- 查询结果参与运算。参数运算的字段必须为数值型
select 列名1 + 固定值 from 表名;

条件查询：

select * from 表名 where 条件（各种运算符）;

运算查询：

>   大于 
<   小于 
<=  小于等于 
>=  大于等于 
=   等于 
<> 、 !=  不等于

and（&&） 	多个条件同时满足 
or（||） 		多个条件其中一个满足 
not（!） 		不满足

范围查询、模糊查询、排序查询

范围查询：

between 值1 and 值2
in(值1, 值2, 值3, ...)

模糊查询 like

like
	%  : 模糊多位 通配符
	_  : 模糊一位 通配符

is null
is not null

排序查询 order by 字段

order by 字段名 [desc | asc] , 字段名 [desc | asc] ;		-- asc(默认): 升序，desc: 降序

聚合函数查询

count() : 计数

sum(字段) : 求和
max(字段) : 求最大值
min(字段) : 求最小值
avg(字段) : 求平均值

ifnull(字段, 备用值) : 第一个参数若不为NULL，则返回该值；若为NULL，则返回备用值，

count(1)、count(*)与count(列名)的区别

执行效果：

count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1) 包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率：

如果有主键，则 count(主键) 的执行效率是最优的
列名为主键，count(列名) 会比 count(1) 快
列名不为主键，count(1) 会比 count(列名) 快
如果表多个列并且有主键或索引，则 count(*) 系统会自动优化走主键或者索引
如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(* )

分组查询、分页查询

分组查询

group by 分组字段 [having 条件]

-- 示例：查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示总人数大于2的数据
select sex, COUNT(*) from student where age>25 group by sex having COUNT(*)>2;

having与where的区别

having是在分组后对数据进行过滤；where是在分组前对数据进行过滤
having后面可以使用聚合函数；where后面不可以使用聚合函数

分页查询

limit 跳过条数,查询条数;

DCL（数据控制语言）

-- 创建用户
-- 命令格式：
CREATE USER '用户名'@'主机地址' IDENTIFIED BY '密码';	-- 默认没有任何权限 什么都做不了
-- 示例：
CREATE USER 'tom'@'localhost' IDENTIFIED BY '123';

-- 给用户分配权限
-- 命令格式：
GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';
-- 示例：
GRANT ALL ON db3.* TO 'tom'@'localhost';

-- 查看权限
-- 命令格式：
SHOW GRANTS FOR '用户名'@'主机名';
-- 示例：
SHOW GRANTS FOR 'tom'@'localhost'

-- 撤销权限
-- 命令格式：
REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';
-- 示例：
REVOKE ALL ON db3.* FROM 'tom'@'localhost';

-- 删除用户
-- 命令格式：
DROP USER '用户名'@'主机名';
-- 示例：
DROP USER 'tom'@'localhost';

-- 给root用户修改密码
-- 特点：不需要登录 直接操作命令即可
-- 格式：
mysqladmin -uroot -p password 新密码

-- 给普通用户修改密码
-- 特点：需要root用户先登录 再使用命令去修改普通用户的密码
-- 格式：
set password for '用户名'@'主机名' = password('新密码');
-- 示例：
set password for 'aaa'@'localhost' = password('root');

数据库约束

约束：对表中的数据可以进行进一步的限制，来保证数据的唯一性，正确性和完整性

约束种类：

PRIMARY KEY ：主键约束。代表该字段的数据不能为空且不可重复
NOT NULL ：非空。代表该字段的数据不能为空
UNIQUE ：唯一。代表该字段的数据不能重复

主键约束：primary key

一个表中都得需要主键约束，用来标注一条记录的唯一性

特征：

主键字段值唯一不可重复
主键字段值不能包含NULL值
一个表中只能有一个主键，但主键可以是多个字段 (联合主键)

MySQL实现方式

添加主键

方式一：在创建表时，添加约束

格式：

create table 表名 ( 
	字段名称1 字段类型 primary key, 	
	字段名称2 字段类型 约束 
);

方式二：在创建表时，结尾内添加约束

格式1：单一主键

create table 表名 (
	字段名称1 字段类型, 	
	字段名称2 字段类型, 	
	primary key(字段名称1)  
);

格式2：联合主键

create table 表名 (
	字段名称1 字段类型, 	
	字段名称2 字段类型, 	
	primary key(字段名称1,字段名称2)  
);

删除主键

alter table 表名 drop PRIMARY KEY;

主键自增

auto_increment		-- 默认地AUTO_INCREMENT 的开始值是1

-- 修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

唯一约束：unique

**特征：**被修饰的字段唯一，不可重复

注意：一个表中可以有多个被unique修饰的字段，但对null不起作用

实现方式

-- 创建表时在字段后添加 
create table 表名 ( 
	字段名称1 字段类型 unique, 
	字段名称2 字段类型 约束 
);

非空约束：not null

**特征：**被修饰的字段不可为空

**实现方式：**直接在字段后面添加即可

create table 表名 ( 
	字段名称1 字段类型 not null default '默认值', 			-- default：默认值
	字段名称2 字段类型 unique not null default '默认值', 	-- 一个字段上可以同时出现唯一约束和非空约束的
    字段名称3 字段类型 约束 
);

多表

多表的设计与实现

有三种：

一对一

实现：

1、让双方的主键作为外键一一对应

2 、在任意一方创建一个字段当成是外键指向另一方的主键，但是这个外键必须唯一
一对多

称一的一方为主表，称多的一方为从表

建立原则：只要是一对多，就在从表中（多的一方）创建一个字段为外键，然后让这个外键指向主表的（一的一方）主键
多对多

建立原则：需要在外部创建一张中间表。这个中间表至少需要2个字段，然后让这2个字段分别作为外键只向各自表的主键

多表约束（外键约束）

外键约束的作用：能够保证数据的完整性和有效性

特征：从表中如果关联了主表的数据则强制主表的数据不能删除，保证数据的完整性和有效性

外键的性能问题：

数据库需要维护外键的内部管理；
外键等于把数据的一致性事务实现，全部交给数据库服务器完成；
有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；

外键的使用策略：

在大型系统中（性能要求不高，安全要求高），使用外键；

在大型系统中（性能要求高，安全自己控制），不用外键；

小系统随便，一般不用外键。
用外键要适当，不能过分追求
不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后每个应用通过这个层来访问数据库。

添加外键约束

方式1：在已有表上添加外键约束

给从表的外键字段添加外键约束

alter table 从表 add constraint [外键别名] foreign key(外键字段) references 主表名称(主键字段)

方式2：可视化图形方式

在架构设计器中，直接指定外键拖向主键即可，简单方便快捷

删除外键

方式一：

ALTER TABLE 从表 drop foreign key 外键名称;

方式二：在架构设计器中，选中指向关系右键删除即可

多表查询

多表查询：从多张表中获取到有关系的数据。比如：查询分类信息以及分类下的所有商品

多表查询的语法分类：

内连接查询：只查询多张表之间有关系的数据
外连接查询
子查询
交叉查询：查询的是2张表的乘积（笛卡尔积），有关系的数据和没关系的数据都查出来了

内连接查询

特点：可以使用内连接去查询2张表之间有关系的数据

隐式内连接：

-- 语法格式：
select * from 表1 表1别名,表2 where 关联条件 and 筛选条件; 	-- ps：可以使用表别名简化书写

显示内连接

-- 语法格式：
select * from 表1 [inner] join 表2  on 关联条件 where 筛选条件 and 筛选条件; 
    -- on: 后跟的是关联条件
    -- where: 后跟的是对结果的筛选条件

外连接查询

左外连接：以 join 左边的表为主，查询出来的是2张表之间有关系的数据以及左边表所有的数据。

如果右表中没有满足条件的对应数据，则填充 null
```
-- 语法格式：
select * from 表1 left [outer] join 表2 on 关联条件 where 筛选条件;
```
右外连接：以 join 右边的表为主，查询出来的是2张表之间有关系的数据以及右边表所有的数据

如果左表中没有满足条件的对应数据，则填充null
```
-- 语法格式：
select * from 表1 rigth [outer] join 表2 on 关联条件 where 筛选条件;
```

子查询

语法格式：

-- 子查询作为where条件。适用于单列单值或者单列多值
select * from 表名 where (子查询结果);
-- 子查询作为临时表。适用于多列多值
select * from 子查询结果 where 条件;
-- 子查询作为结果集字段
select *,(子查询结果) from 表名 where 条件;

示例：

子查询作为where条件示例：查询工资最高的员工是谁（结果是单列单值）

-- 先查询最高的工资
select MAX(salary) from emp;
-- 再查询最高工资的员工
select name from emp where salary=(select MAX(salary) from emp);

子查询作为where条件示例：查询工资大于5000的员工部门ID，来自于哪些部门（结果是单列多值）

-- 先查询工资大于5000的员工部门ID
select dept_id from emp where salary>5000;
-- 根据部门id获取部门名称
select name from dept where id in (select dept_id from emp where salary>5000);

子查询作为临时表示例：查询出2011-01-01以后入职的员工信息，包括部门名称（结果是多列多值）
```
select l.*,d.name from dept d,(select * from emp where join_date>'2011-01-01') l
where d.id=l.dept_id;
```

交叉连接

select * from 表1,表2;

会产生2张表的乘积数据，简称笛卡尔积数据，有关系的数据和没关系的数据都查出来了

数据备份与还原

mysqldump：MySQL数据库备份

mysqldump命令是MySQL数据库中备份工具，用于将MySQL服务器中的数据库以标准的sql语言的方式导出，并保存到文件中。

语法格式:

mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项]  > 脚本名

# 常用参数：
    -h, --host		服务器IP地址
    -P. --port		服务器端口号
    -u, --user		MySQL 用户名
    -p, --pasword	MySQL 密码
    --databases			指定要备份的数据库
    --all-databases		备份mysql服务器上的所有数据库
    --compact		压缩模式，产生更少的输出
    --comments		添加注释信息
    --complete-insert	输出完成的插入语句
    --lock-tables		备份前，锁定所有数据库表
    --no-create-db/--no-create-info		禁止生成创建数据库语句
    --force			当出现错误时仍然继续备份操作
    --default-character-set		指定默认字符集
    --add-locks		备份数据库表时锁定数据库表
    --ignore-table	备份数据时库排除某表

参考实例：

# 导出整个数据库：
mysqldump -u linuxcool -p smgp_apps_linuxcool > linuxcool.sql
# 导出指定的数据库：
mysqldump -u linuxcool -p smgp_apps_linuxcool users > linuxcool_users.sql
# 备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p  mysql db event > /backup/mysqldump/2table.sql
# 导出一个数据库结构：
mysqldump -u linuxcool -p -d --add-drop-table smgp_apps_linuxcool > linuxcool_db.sql
# 备份指定数据库排除某些表
mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.sql

备份还原命令：

mysqladmin -uroot -p create db_name 
mysql -u root -p password db_name < /backup/mysqldump/db_name.db

# 注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。

k8s部署定时备份mysql数据库及还原命令：

# 创建定时任务
crontab -e
# 定时任务（每天0点执行）：
0 0 * * * /usr/local/bin/kubectl exec `/usr/local/bin/kubectl get po | grep mysql | awk '{print $1}'` -- mysqldump -u user -p password db_name > /root/mysql-bak/`date + \%d`.sql 2>&1

# 注：2>&1 的意思就是将标准错误重定向到标准输出。这里标准输出已经重定向到了~.sql。那么标准错误也会输出到~.sql文件里

# 还原命令：
/usr/local/bin/kubectl exec `/usr/local/bin/kubectl get po | grep mysql | awk '{print $1}'` -- mysql -u user -p password db_name < /root/mysql-bak/`date + \%d`.sql