数据库MySQL_sequencen mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/NGC2237999/article/details/146081386

一、SQL简介及安装

数据库（Data Base，简称DB)：长期保存在计算机的存储设备上，数据按照一定的规则组织起来，可以被各种用户、应用共享的数据集合。

数据库管理系统（Database Management System, 简称DBMS）：指的是一种用来管理和操作数据的大型软件，用于建立、使用、维护数据，对数据库进行统一的管理和控制，以保证数据的完整性和安全性。用户可以通过数据库管理系统访问数据库中的数据。

数据库：存储、维护和管理数据的集合。

数据库管理系统：数据库软件，数据库是通过这些软件进行创建和操作的。

常见的数据库管理系统

Oracle：被认为是目前业界比较成功的关系型数据库的管理系统。Oracle可以运行在Windows、UNIX等主流的操作系统平台，完全支持所有的工业标准，并获得了最高级别的ISO标准安全性认证。

MySQL：是一种关系型数据库管理系统。由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。MySQL是目前最流行的关系型数据库管理系统（RDBMS：Relational DBMS）之一。

SQLServer：是由微软推出的关系型数据库管理系统。

DB2：是IBM的

关系型数据库、非关系型数据库

非关系型数据库：

采用了没有特定关系模型来组织数据。
NOSQL基于键值对： not only sql
代表： HBase, MongoDB(文档)，Redis, Oracle NOSQL

关系型数据库：

依据所有存储数据的模型之间的关系建立的数据库。
所谓关系模型，指的是“一对一、一对多、多对多”等关系模型

安装与卸载

具体的安装步骤可以参考这篇大佬写的文章，非常全面：MySQL安装教程（详细版）_mysql安装教程8.0.36-优快云博客

卸载：

控制面板 -> 程序 -> 卸载程序 -> 找到MySQLServer -> 右键 -> 卸载

打开C盘 -> 打开ProgramData文件夹 -> 删除MySQL文件夹

清除注册表信息：

打开注册表：

win+r 输入：regedit

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\EventLog\Application\MySQL

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\EventLog\Application\MySQL

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\MySQL

MySQL服务的管理

临时的启动和停止服务

打开任务管理器-->服务-->mysql服务项--> 启动或者停止

在服务设置界面里管理

此电脑 -> 右键 -> 管理 -> 服务 -> 找到MySQL服务项 -> 右键菜单或者双击进入设置界面，可以设置手动，自动，禁用等

命令行管理服务项

开始菜单 -> windows系统 -> 命令提示符 -> 右键 -> 以管理员身份运行

输入指令：

net start mysql80 // 开启服务

net stop mysql80 // 停止服务

客户端连接数据库

第一种方式：使用mysql自带的命令行客户端

在开始菜单中–>mysql 8.0 command line client–> input password

第二种方式：使用window的命令行界面

需要配置mysql的环境变量

将mysql的服务端的bin文件夹，比如C:\Program Files\MySQL\MySQL Server 8.0\bin，配置

到电脑的环境变量里的path变量里

输入：

mysql -u root -p'your password'

第三种：使用第三方客户端软件

很多公司都提供了可以连接数据库服务器的用户可视化操作界面(客户端)，比如，在国内用的最多的就是navicat。

参考下图：

文件->新建连接->MySQL

输入连接参数即可

扩展：

远程授权,即客户端可以不和服务端在一台机器上，也就是在其他的任何机器上使用root账号登录

> use mysql <--切换库
> update user set host='%' where user='root';
> flush privileges;

第四种方式：使用开发工具idea

点击Datebase图标->Date Source->MySQL->输入相关参数

MySql密码操作

当我们进入数据库后，修改mysql密码的方式有以下四种：

第一种：mysql> alter user root@localhost identified by‘新密码';
第二种：mysql> set password for root@localhost=password('mmforu');
第三种：
mysql> use mysql
mysql> update user set authentication_string=password('123456') where user='root' and host='localhost';
mysql> flush privileges;
第四种：C:\Users\Michael> mysqladmin -uroot –p旧密码 password 新密码

如果忘记了密码：

1. 停止MySQL80服务
2. 找到C:\ProgramData\MySQL\MySQL Server 8.0\目录下的my.ini文件，在77行下面添加skip-grant-tables
3. 启动MySQL80服务
4. 打开命令提示符界面，直接输入mysql指令，进入mysql内
5. 利用上一页的第三种方式，修改密码
6. 然后再修改my.ini文件，去掉skip-grant-tables,重启服务即可

SQL简介

SQL: Structure Query Language（结构化查询语言），SQL最早是被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准。后来被国际化标准组织（ISO）采纳为关系型数据库语言的国际标准。

各种数据库厂商都支持ISO标准的SQL，类似于普通话。

各个数据库厂商在标准的基础上，定义了若干自己的扩展，类似于方言。

SQL是一种标准化的语言，允许你在数据库上进行操作，如：创建项目、查询内容、更新内容和删除内容等操作。

这些操作：Create、Read、Update、Delete，通常被称为 CRUD操作

SQL分类

- DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（数据库、表、列）
- DML（Data Manipulation Language）：数据操作语言，用于定义数据库记录（数据）
- DCL（Data Control Language）：数据控制语言，用于定义访问权限和安全级别
- DQL（Data Query Language）：数据查询语言，用于查询记录（数据）
- TCL (Transaction Control Language): 事务控制语言，用于保证数据的完整性约束。(commit,rollback)

SQL语句大小写是不区分的。
CREATE / create / Create / CrEaTe
但是，一般情况下，我们会大写。

二、数据库的基本操作

DDL

DDL(Data definition Language)：数据定义语言，用来定义数据库中相关的对象的结构

对象: 指的就是库，表
结构：库的创建，删除，修改，更名以及，表创建，表删除，表修改(列名修改，添加新列，删除新列)
对应的关键字：create、alter、drop 其后都跟着对象的关键字，比如 database table

DDL的操作（对库）

创建数据库

# 创建一个库
create database 库名;
# 创建一个库，同时指定编码集 
create database 库名 character set 编码集

查看数据库

# 查看当前数据库服务器中的所有的数据库
show databases;
# 查看创建数据库mydb1定义的信息
show create database 库名;

切库操作（使用哪个库）

# 切换当前使用的数据库
use 库名;
# 查询当前正在使用的数据库
select database();

修改数据库

# 将数据库mydb1的字符集修改为GBK
alter database 库名 character set 新编码集;

删除库

drop database 库名;

DDL的操作（对表）

表的概念

表：数据在数据库中的存储是以表的形式存在的。一个表中有若干个字段，将数据按照这些字段进行存储。

1、数据表(table),是关系型数据库的基本存储结构。一个关系型数据库是由多个表组成的。
2、数据表是二维的，它由纵向的列和横向的行组成。
3、数据表的行(Row)是横排数据，也被称之为记录(Recond)。
4、数据表的列(Column)是竖排数据，也被称之为字段(Field)。
5、表与表之间也可能存在着关系。

表与表之间的关系

1. 一对一
在实际开发中应用不多。因为一对一的关系，完全可以放到一个表中
2. 一对多
一对多建表原则：在多的一方，创建一个字段，作为外键指向另外那一方的主键。
3. 多对多
多对多的建表原则：需要创建第三张表，在中间表中至少需要有两个字段。这两个字段分别作为外键指向各自一方的主键。

数据类型

每一个字段在设计表的时候都要去指定类型：

int：整型

double：浮点型，例如double(5,2)：表示最多有5位，其中必须有两位是小数，即最大值是 999.99

char：固定长度的字符串，例如char(5) : 'aa' 占5位

varchar：可变长度的字符串，例如varchar(5)：'aa' 占2位

text：字符串类型

blob：字节类型

date：日期类型，格式是：yyyy-MM-dd

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型，yyyy-MM-dd hh:mm:ss，会自动赋值

datetime：时间类型，yyyy-MM-dd hh:mm:ss

DDL操作表

# 创建表格
CREATE TABLE t_users(name varchar(50), age INT, gender VARCHAR(10), height INT, weight INT);
# 删除表格
DROP TABLE t_users;
# 查看当前数据库中所有的表
SHOW TABLES;
# 查看创建一个表的信息
SHOW CREATE TABLE t_users;
# 查看一张表的字段信息
DESC t_users;
# 给一张表添加一个字段
ALTER TABLE t_users ADD score DOUBLE(5,2);
# 修改一张表的字段类型
ALTER TABLE t_users MODIFY score INT;
# 修改一张表的字段名
ALTER TABLE t_users CHANGE name uname VARCHAR(50);
# 修改一张表的字符集
ALTER TABLE t_users CHARACTER SET GBK;
# 删除一张表中的字段
ALTER TABLE t_users DROP score;

DML操作

DML(Data Manipulation Language)：数据操纵语言，指的是对表中的数据(记录)进行增、删、改的操作。不要和DDL搞混了。

关键字：insert into 、update、delete

注意事项：

在SQL中，字符串类型和日期类型需要用单引号括起来
空值操作：在条件中使用 is null 或者is not null

增加数据

就是使用insert into 向表中添加记录，可以添加一条，也可以添加多条

添加一条的写法：

写法1：给表中的所有字段赋值，从左到右，按照类型进行赋值，不能多，不能少
insert into tableName values (val1,val2,val3,...);
写法2：指定某些字段赋值, 从左到右，按照类型进行赋值
insert into tableName (colName1,colName2,colName3,...) values (val1,val2,val3,...);

添加多条的写法：

写法1：给表中的所有字段赋值，从左到右，按照类型进行赋值，不能多，不能少
insert into tableName values (val1,val2,val3,...),(val1,val2,val3,...),..,(val1,val2,val3,...);

写法2：指定某些字段赋值, 从左到右，按照类型进行赋值
insert into tableName (colName1,colName2,colName3,...) values
(val1,val2,val3,...),(val1,val2,val3,...),..,(val1,val2,val3,...);
create table teacher(
   id int,
   name varchar(10),
   age int,
   gender char(1),
   salary double(6,2),
   subject varchar(10),
   hiredate date
)default character set utf8;

insert into teacher values 
  (1001,    '张三',    23,    '男',   2000,    '语文',    '2010-03-03'),
  (1002,    '李四',    24,    '女',   2000,    '数学',    '2010-03-04'),
  (1003,   '王五',    23,    '女',   2000,    '英语',    '2010-03-05'),
  (1004,    '赵六',    25,    '男',   2000,    '生物',    '2010-03-06'); 

修改数据

关键字使用update，表示对表中的记录的部分字段进行更新操作。可以使用where 子句用来进行条件筛选(过滤)

where的用法：

在进行数据的删除、修改、查询的时候，可以使用where对数据进行一个过滤。

= ：相等比较（类似于java中的==）

!= <> : 表示不相等

> < >= <= : 大小比较

BETWEEN...AND... : 在xxx和xxx之间

IN(set) ：在括号中所有值之间取

IS [not] NULL

AND、OR

注意：如果没有使用where子句进行过滤筛选，则是对表中的所有记录进行更新

语法如下：

update tableName set colName1 = value1, colName2 = value2,...... [where conditions];

1. 修改所有人的工资为2500
update teacher set salary = 2500;
2. 将所有人的工资增加500
update teacher set salary=salary+500;
3. 修改年龄为23岁的教师的工资为5000,入职日期为'2010-10-10'
update teacher set salary = 5000,hiredate = '2010-10-10' where age = 23;
4. 修改名字为张三的教师的入职日期为空，工资为空
update teacher set hiredate = null, salary = null where name = '张三';
5. 将在'2010-10-10'之前入职的员工工资上调1000元
update teacher set salary = salary+1000 where hiredate < '2010-10-10';
6. 将教语文、英语、生物的老师的年龄改为null
update teacher set age = null where subject = '语文' or subject = '英语' or subject = '生物';
update teacher set age = null where subject in ('语文','英语','生物');
7. 将没有课的老师工资下调1000元。
update teacher set salary = salary -1000 where subject is null;

删除数据

关键字是delete. 指的是删除表中的符合条件的所有记录(行数据)

注意：如果不指定条件，表中的所有行都被删除了。

语法：

delete from tableName [where conditions];

1. 删除工资是4000，年龄不是null的教师信息
delete from teacher where salary = 4000 and age is not null;

2. 删除剩下的所有记录
delete from teacher;

delete 与 truncate的区别

delete删除表中的数据，表结构还在；删除的数据可以恢复。

truncate是直接将表DROP掉，然后再按照原来的结构重新创建一张表。数据不可恢复。

truncate删除效率比delete高。

truncate table tableName

DQL的操作

DQL： SQL分类的一种，是Data Query language的简称。用于对表中的记录进行查询操作。可以查询表中的所有记录，也可以通过条件筛选过滤，查询表中的部门记录，还可以对表中的数据进行排序操作，分页操作等。当然，还可以进行多表关联查询(因为需求的数据，可能不只是在一张表中，而是在多张表中都有)

基本查询语言的结果

DQL的整体语法，是可以分成多个子句的。最简单的子句是select…子句和from…子句。这两个子句必须存在。

from…子句：用于指定要查询的表

select…子句：选择表中的哪些字段进行查询/显示

语法：

select...from....

含有其他子句的语法如下：

select [distinct].....from....[where....][group by .....][having.....][order by.....][limit.....]

查询语句的执行顺序

了解mysql的查询语句的执行顺序，会对编写sql语句有一定的帮助。

1. 先执行from子句:基于表进行查询操作
2. 再执行where子句:进行条件筛选或者条件过滤
3. 再执行group by子句:对剩下的数据进行分组查询。
4. 再执行having子句：分组后，再次条件筛选或过滤
5. 然后执行select子句：目的是选择业务需求的字段进行显示
（distinct）
6. 再执行order by子句：对选择后的字段进行排序
7. 最后执行limit子句:进行分页查询，或者是查询前n条记录

别名的用法

有的时候，表名或者列名过长，或者在查询过程中涉及到的虚拟表或者是虚拟字段，我们就需要给他们起一个别称，也就是别名。

别名的命名：

虽然可以使用汉字，但是尽量还是要使用英文字母。
表别名，不能使用单双引号
列别名，可以加单双引号，也可不加。

reg:
select name 姓名,
(year(now())-year(birth)) 年龄
from test1 t;

reg:
select e.empno 员工编号,e.ename,e.job,mgr '领导编号',hiredate,sal,comm,deptno from emp e;

where字句的使用

如果想要对表中的数据进行条件筛选或过滤，需要使用where子句。where关键字后，用于书写筛选的条件。（分组前筛选第一次筛选）

条件如下：

1）关系表达式: >,>=,<,<=,=,!=,<>
2）多条件连接符: and，or, [not] between ..and..
3）集合操作: [not] in (set),

>all(set), <all(set), >any(set), <any(set)
注意：mysql不支持简单的集合查询操作，但是子查询里支持,是针对于all和any集合操作来说的。
4）模糊查询: like
_:占位符，表示匹配任意一个字符
%:表示匹配任意N个字符，大于等于0.

reg:查询工资大于1600的所有员工的编号，姓名，职位，工资
select empno,ename,job,sal from emp where sal>1600
reg:查询工资大于1600并且小于2500的所有员工的编号，姓名，职位，工资
select empno,ename,job,sal from emp where sal>1600 and sal<2500;
select empno,ename,job,sal from emp where sal between 1600 and 2500;
reg:查询10，20号部门的所有员工的信息。
select * from emp where deptno not in (10,20);
reg:查询不是10号部门中姓名第二个字符是m的员工信息。
select * from emp where deptno<>10 and ename like '_m%';

group by子句

1.需求：
   有的时候，需要分组统计一些，最大值，最小值，平均值，和，总数之类的这样的信息，此时需要分组查询。

2.聚合函数：也叫分组函数
- count(): 统计每组满足的记录总数
- max():统计每组满足条件的最大值
- min():统计每组满足条件的最小值
- avg():统计每组满足条件的平均值
- sum():统计每组满足条件的总和。

    注意：
    - 所有的聚合函数，都会忽略字段为null的那条记录。
- count(*),不会忽略null值所在的行记录，即通常用于统计总行数。

3. 在分组查询时，只有分组字段可以写在select子句中,其他不是分组的字段，不应该写在select子句中，无意义

   多字段进行分组：
   A B 字段组合情况下：组的数组最多为 m*n

   1 a 1001
1 b 1002
   2 b
2 c
   3 a
   3 b
   1 a 1003
   1 b 1004

4. 聚合函数处理null值，可以使用ifnull（colName, value）.
- ifnull(colName,value): 如果colName对应的值不为空，就使用本身的值，如果为null,使用value.


# 案例：查询每个部门中的每种职位的最高工资，最低工资，工资之和
select deptno,job ,max(sal),min(sal),sum(sal) from emp group by job,deptno;

# 案例: 查询所有员工的平均工资，平均奖金使用ifnull函数
select avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp;

having子句

当使用了group by子句后，如果想再次对数据进行筛选和过滤，就需要使用having子句了。

注意： having子句只能跟在groub by子句后面

分组后再次过滤第二次过滤

# 查询部门平均工资大于1000部门号，平均工资。
select deptno,avg(ifnull(sal,0)),max(sal) avg_sal from emp group by deptno having avg(ifnull(sal,0)) >1000;

# 查询每种职位的最高工资大于1500的职位、最高工资，平均工资，平均奖金。
select job,max(sal),avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp group by job having max(sal)>1500;

order by子句

用于查询排序的，通常放置在一个查询语句的最后部分。
语法： order by colName [asc|desc] [,colName [asc|desc]]
asc：升序，默认情况就是升序
desc:降序
reg:查询员工表中的所有员工信息，按照工资降序排序
select * from emp order by sal desc;

reg:查询员工表中的所有员工信息，按照工资降序排序,如果相同，再按照奖金升序排序
select * from emp order by sal desc,comm asc;

去重查询

有的时候，我们需要查询表中有那些不同的数据。不需要重复出现，此时可以使用distinct关键字进行去重处理

注意：distinct关键字只能放在select关键字之后。
比如：查询有那些部门号

分页查询

- 需求：当一页的数据量过大时，我们可以进行分页显示操作。注意：分页查询时，一般都要进行先排序，再分页。
- 关键字limit.
- 语法:limit m[,n];
m 表示从第几条记录开始查询，
n表示要查询的记录数目。

注意：mysql的记录index从0开始。

一个参数的含义：limit n
表示从0开始查询n条记录

案例1：每页5条记录，查询第二页的数据。
select empno,ename from emp limit 5,5;

案例2：查询第page页的数据, 每页大小为pageSize。limit的写法如下：
limit (page-1)*pageSize , pageSize

三、约束constraint(重点掌握)

概念

完整性约束条件，用于对表中的字段值进行限制，必须随时遵守指定的约束规则。这样可以保证数据的一致性和正确性。

完整性约束条件，简称约束。
是一种强制性的校验手段
只针对于DML操作。不符合约束条件，直接报错不执行。
这些约束，尽量在建表期间指定好，避免在表中已经有数据的情况下修改(可能修改失败)

定义期间，约束定义在指定列的后面，称之为列级约束，定义在表的最后，称之为表级约束

默认值约束

在建表期间，可以给字段设置有默认值。这样在插入数据时，该字段如果没有指定新的值，数据库就会自动为这个字段插入默认值。（设置bull会赋值null）

default value
建表时写法

create table 表名(
...
字段名字段类型 default 值,
...
);

建表后修改

alter table 表名 modify 字段名字段类型 default value;

建表后取消默认值约束

alter table 表名 modify 字段名字段类型;

非空约束

当想要限定字段的值不能为null时，可以使用非空约束

not null ，简称NN
建表写法

create table 表名(
...
字段名字段类型 not null,
...
);

表后修改

alter table 表名 modify 字段名字段类型 not null;

取消非空约束

alter table 表名 modify 字段名字段类型 ;

唯一性约束

当想要限定字段的值不能重复时，可以使用唯一性约束，不过可以为null(无穷大不等于无穷大) null与null不相同。

unique，简称UK
建表时列级约束写法

create table 表名(
...
字段名字段类型 unique,
...
);

建表时表级约束写法

create table table_UK_1(
...
...
constraint 约束名字 unique(字段,...)
);

多个字段组合成唯一约束

create table user(
...
...
constraint 约束名字 unique(字段1,字段2)
);

建表后修改

alter table 表名 modify 字段名字段类型 unique;

取消唯一索引

alter table 表名 drop index 唯一索引名称

查看唯一索引的名字

show create table 表名

主键约束

主键约束，用于标识表中的每一条记录都是唯一的。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。

主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。

primary key，简称PK
唯一且非空注意：表里之有一个主键约束
主键可以由一个字段组成，也可以由多个字段组成
如果主键可以由一个字段组成既可以添加到列级也可以添加到表级，但是如果由多个字段组成只能添加到表级

建表时列级约束语法：

create table 表名(
...
字段名 primary key,
...
);

建表时表级约束语法

create table 表名(
...
...
constraint 主键约束名字 primary key(字段1,....)
);

建表后语法：

alter table 表面 add primary key(字段名1,....);
alter table 表名 modify 字段名字段类型 primary key;

刪除主键约束，注意非空约束还在

alter table tableName drop primary key;

选择主键约束的字段要求：

1.对业务需求没有意义的字段，比如序号
2.不建议对动态赋值的字段进行设置，比如时间戳。
3.如果设置了主键约束，那么此字段最好不好人为的修改。而是自动生成。使用auto_increment（自增序列）

自增键约束

在MySQL中，可通过关键字auto_increment为列设置自增约束。能设置自增约束的条件如下:

列的类型必须是整型
该列必须设置为主键约束或者唯一性约束
向数据库表中插入新记录时，字段上的值默认的初始值1，每增加一条记录，该字段的值会增加1;
一个表中只能有一个自增约束

create table 表名(
...
字段名 int primary key auto_increment,
...
);

建表后语法：

alter table 表名 modify 字段名字段类型 auto_increment;

取消自增约束：

alter table 表名 modify 字段名字段类型;

设置初始值：

alter table tableName auto_increment = 8;

外键约束

外键约束是为了保证多个表（通常为两个表）之间的参照完整性，即构建两个表的字段之间的参照关系。

foreign key,简称FK
建表时写法

create table tableName(
...
constraint 外键约束名称 foreign key(字段A) references 表名2(字段B)
);

建表后写法

alter table 表名1 add constraint 约束名称 foreign key(字段A) references 表名2(字段B)

注意：

目前外键，存储函数，存储过程，触发器…这些在后台开发过程中基本是不用的，因为这些限制逻辑或者代码逻辑是由mysql本身控制的，一个后端服务器的性能首先到瓶颈的是存储层模块，所以要把核心逻辑给mysql作，各个表的关系要放到业务层，不给mysql增加负担，外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。

有外键约束的情况下被依赖表里的数据所在行不能被删除除非该值没用被依赖甚至被依赖的表都不能之间删除掉

被依赖的表中的那个字段必须是唯一性约束或者是主键约束（只有非空不够用）。

检查约束

检查性约束，用于对表中的字段进行条件限制。比如余额不能小于0，性别必须是f和m等。mysql8.0.15以前是不支持这个约束的，从8.0.16这个版本才开始支持。

check，简称ck。
建表时语法

create table tableName(
tid int,
tname varchar(20),
age int check(age>=18),
tgender char(1) check(tgender in('f','m'))
);

之前的版本可以使用枚举代替

create table temp_6(
tid int,
tname varchar(20),
tgender enum('f','m') #枚举写法
);
insert into temp_6 values (1001,'zs','m');

删除检查性约束：

alter table temp_5 drop check checkName ;

四、关联查询（多表查询）

有的时候，我们的业务需求的数据不只是在一张表中，而是在两张或两张以上的表中，而这些表中通常都会存在着“有关系"的字段。那么此时的查询操作涉及到多表查询，我们称之为关联查询。

案例：最简单的关联查询

select * from emp,dept;
select * from emp join dept;

笛卡尔积:

- 当做关联查询时，如果两张表中不存在关联字段，或者忘记写关联条件。那么会出现如下图所示的结果。
- 即表A中的每一条记录都回与表B中的所有记录进行匹配组合。例如表A中有m条记录。表B中有N条件。那么匹配组合的记录数目为M*N，此值被称之为笛卡儿积。
- 通常没有意义。

写法分类

在进行多表关联查询时，通常有两种写法：

第一种：在from子句中，直接写多个表名，表名之间使用逗号分隔开，如：

select A.*,B.*,C.* from A,B,C where 关联条件

第二种：在from子句中,多个表名之间使用join关键字连接，并在on关键字后面添加关联条件。

select A.*,B.*,C.* from A join B on 关联条件
select A.*,B.*,C.* from A join B on AB关联条件 join C on AC或BC关联条件
select A.*,B.*,C.* from A join B join C on AB的关联条件 and BC或AC关联条件

join连接分类

join关联查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。

1. 内连接: [inner] join
2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
- 左外连接:left [outer] join, 左表是驱动表
- 右外连接:right [outer] join, 右表是驱动表
- 全外连接:full [outer] join, mysql不支持.两张表里的数据全部显示出来
3. 注意： join连接只支持等值连接

集合查询

union(去重)/union all(不去重):

两个查询语句使用上述的关键字连接即可。
注意：两个查询语句的字段名，字段个数，必须对应上。

五、高级关联查询（子查询）

有的时候，当一个查询语句A所需要的数据，不是直观在表中体现，而是由另外一个查询语句B查询出来的结果，那么查询语句A就是主查询语句，查询语句B就是子查询语句。这种查询我们称之为高级关联查询，也叫做子查询。

子查询语句的返回数据形式：

- 返回单行单列
- 返回多行单列
- 返回单行多列
- 返回多行多列
子查询语句的位置可以在以下几个子句中：

- 在where子句中:   子查询的结果可用作条件筛选时使用的值。
- 在from子句中:       子查询的结果可充当一张表或视图，需要使用表别名。
- 在having子句中:   子查询的结果可用作分组查询再次条件过滤时使用的值
- 在select子句中:   子查询的结果可充当一个字段。仅限子查询返回单行单列的情况。

在where子句中

# 需求：查询工资大于员工编号为7369这个员工的所有员工信息。
解析：
第一步：目的是查询工资大于某一个数num的所有员工信息
select * from emp where sal>num
第二步：num的值7369员工的工资
select sal from emp where empno = 7369;
第三步：将主查询中的代词使用子查询语句替换
select * from emp where sal>(select sal from emp where empno = 7369);
# 需求：查询工资大于10号部门的平均工资的所有员工信息
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10);
# 需求：查询工资大于10号部门的平均工资的非10号部门的员工信息。
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10) and deptno<>10;
# 需求：查询与7369同部门的同事信息。
select * from emp where deptno=(select deptno from emp where empno=7369) and empno<>7369;

NOT IN 在子查询返回的结果集较小的情况下表现良好。但如果子查询返回的结果集非常大，可能会影响性能。此外，如果子查询返回的结果包含NULL值，NOT IN可能会产生意外的结果。

NOT EXISTS 通常在性能上更优，尤其是在处理大数据集时。它也更不容易受到NULL值的影响。

在from子句中

可以把from后的子句看成新的表从新表里查询

# 需求：查询员工的姓名，工资，及其部门的平均工资。
解析：
第一步：先查询每个部门的平均工资
select deptno,avg(ifnull(sal,0)) from emp group by deptno;
第二步：将上一个查询语句的返回结果当成一张表，与员工表进行关联查询

select A.ename,A.sal,B.avg_sal
from emp A join (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno) B on A.deptno = B.deptno

在having子句中

# 需求：查询平均工资大于30号部门的平均工资的部门号，和平均工资
select deptno,avg(ifnull(sal,0)) from emp group by deptno having avg(ifnull(sal,0))>
(select avg(ifnull(sal,0)) from emp where deptno=30);

在select子句中

相当于外连接的另外一种写法
# 查询每个员工的信息及其部门的平均工资，工资之和，部门人数
select A.empno,A.ename,A.sal,
(select avg(ifnull(sal,0)) from emp B where B.deptno=A.deptno) avg_sal,
(select sum(sal) from emp C where C.deptno=A.deptno) sum_sal,
(select count(*) from emp D where D.deptno=A.deptno) count_
from emp A;

sql完整执行顺序

select distinct..from t1 [inner|left|right] join t2 on 条件
where...group by...having...order by...limit

1. from t1
2. on 条件
3. [inner|left|right] join t2
4. where...
5. group by...
6. having...
7. select...
8. distinct...
9. order by...
10. limit....

六、常用函数

日期函数

函数	函数说明
curdate()\curtime()\now()\sysdate()\current_timestamp()	获取系统时间
dayofweek(date) \weekday(date) \dayname(date)	获取星期几
dayofmonth(date) \dayofyear(date) \monthname(date)	获取第几天
year(date)\month(date)\day(date) \ hour(date) \minute(date) \second(date)	获取时间分量
date_format(date,format) （%Y年 %m月 %d日 %h时 %i分 %s秒 %p上下午 %W星期）	日期格式化，根据format字符串格式化date的值
date_add(date,interval expr type) \date_sub(date,interval expr type)	日期运算
adddate(date,interval expr type) \subdate(date,interval expr type)	日期运算

字符串函数

函数	函数说明
conv(n,from_base,to_base)	进制转换，对from_base进制的数n,转成to_base进制的表示方式
concat(str1,str2,...)	将多个参数拼接成一个字符串，只要有一个为null,就返回null
lpad(str,len,padstr) rpad(str,len,padstr)	用字符串padstr填补str左端/右端直到字串长度为len并返回
left(str,len) right(str,len)	返回字符串str的左端/右端的len个字符
substring(str,pos[,len])	返回字符串str的位置pos起len个字符,此函数的下标是从1开始
length(str) octet_length(str)	返回参数对应的默认字符集的所有字节数
char_length(str) character_length(str)	返回字符串str的字符长度
ltrim(str) rtrim(str) trim(str)	返回删除了左空格/右空格的字符串str
lcase(str)lower(str)ucase(str)upper(str)	返回str的大小写
replace(str,from_str,to_str)	用字符串to_str替换字符串str中的子串from_str并返回
insert(str,pos,len,newstr)	把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回

数值函数

函数	函数说明
pow(x,y)/power(x,y)	返回值x的y次幂
sqrt(n)	返回非负数n的平方根
pi()	返回圆周率
rand() rand(n)	返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
truncate(n,d)	保留数字n的d位小数并返回
least(x,y,...) greatest(x,y,...)	求最小值或最大值
mod(n,m)	取模运算,返回n被m除的余数
ceiling(n) floor(n)	向上/向下取整函数
round(n,d)	返回n的四舍五入值,保留d位小数(d的默认值为0)

常用高级函数

排名函数：over开窗函数5.7版本不支持 8.0以上版本支持
1. row_number()over(...):给排序过的表记录分配行号，从1开始的连续自然数(不重复)
2. rank()over(...):给排序过的表分配名次，相同的值名次一样，后续的排名出现跳跃情况
3. dense_rank()over(...):给排序过的表分配名次，相同的值名次一样，后续的排名不出现跳跃情况
over函数是一个开窗函数用于给字段进行排序和分组

七、DCL语句

数据控制语言是SQL语言中的五大分类之一，用于创建用户，授予权限，撤销权限，删除用户等操作。

注意：该语言一般由DBA来使用

作用：

用于创建用户，授予权限，撤销权限，删除用户等操作。
create user
grant 授权
revoke 撤销
alter user
drop user

管理用户

创建用户，修改密码，删除用户等操作，都需要使用超级管理员root进行操作

- 创建用户语法:
create user username@ip identified by 'password';
scott@'%'

- 修改用户密码:
alter user username@ip identified by 'newPassword';
# set password for username@ip = password('newPassword');低版本支持高版本不支持

- 删除用户:
drop user username@ip
'scott'
'scott'@'%'

权限管理

可进行授予权限，撤销权限，查询权限等操作

- 查看用户权限：使用超级管理员root
show grants for username

- 授权：使用超级管理员root
grant 权限名[,权限名.....] on dbname.* to username@ip

DDL权限：create、alter、create view.....,drop
DML权限：insert、update、delete
DQL权限: select

案例：授于某用户全部权限
grant all privileges on *.* to 'scott'@'localhost' identified by '123456' with grant option;
案例：启用root用户的远程连接操作
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

- 撤销权限
revoke 权限名 [,权限名.....] on dbname.* from username@ip

- 刷新权限
flush privilages;

TCL语言

事务控制语言

什么是事务：

就是做一件事时，这件事要么必须从头到尾来完成，要么不做，做一半也必须回到起点。

事务所具有的特性：

原子性（atomicity）：不能再被分割了，表示已经是最小的。

一致性（consistency）:即事务开始前的数据状态和结束后的数据状态必须一致。

隔离性（isolation）：多用户操作同一张表（同一条记录时），数据库为每一个用户单独开启一个事务，某一时刻只能有一个事务正在进行，其他事务处于等待状态，保证事务之间没用影响。降低了效率换取了数据的安全稳定性

持久性（Durability）:一旦事务被提交了，必须保证数据库改正对应的数据，即使数据库崩溃，也要保证事务的完整性。

简称：事务的ACID特征

事务只针对DML语言来说的 DML才会设计到事务概念

mysql的DML语言，每一个DML默认就是事务。

所以DML的一个sql,要么成功，要么回到最初。

关键字：

commit：提交事务表示想要完成

rollback: 回滚事务表示回到最初

savepoint:临时保存

不手动开启默认就是一个事务

手动开启：

start transaction；开启事务

commit:提交事务

八、数据库备份和恢复

手动复制方法

a.可以先登陆mysql 查看数据文件的位置：
show global variables like '%datadir%'
b.在某一个位置创建备份目录backup. 比如：D:\backup

c.将%datadir%下的所有文件，copy到backup下。

d.先模拟某一个数据库mydb毁坏。drop database mydb;

e.先终止服务项。再将所有的数据，copy到%datadir%下。然后启动mysql再去查看能否访问这个库。

mysqkdump命令进行备份

a.在命令提示符下输入(别登陆到mysql内)：

mysqldump -uUsername -pPwd --all-databases > filename.sql #备份所有数据库

b.备份一部分数据库

mysqldump -uUsername -pPwd --databases dbname1 dbname2 > filename.sql

c.备份某个数据库中的某些表

mysqldump -uUsername -pPwd dbname tablename1 tablename2 > filename.sql

d.恢复数据

登入到mysql内，使用source命令

语法： source filename.sql
注意：路径是绝对路径