mysql数据库，语法语句总结。以及事务理解-基础篇

末蓝、 2023-10-10 16:03 33阅读 0赞

#### 文章目录 ####

*  数据库
 *   *  \*\*Sql语句\*\*
     *   *  \*\*DDL语句\*\*
         *   *  \*\*操作数据库\*\*
             *  \*\*操作数据库的表结构\*\*
             *  \*\*表结构的修改：\*\*
         *  \*\*sql数据类型：\*\*
         *  \*\*DML语句(增删改)\*\*
         *   *  \*\*插入数据\*\*
             *  \*\*删除数据\*\*
             *  \*\*修改数据\*\*
         *  \*\*DQL语句(查询)\*\*
         *   *  \*\*基本查询\*\*
             *  \*\*条件查询\*\*
         *  \*\*综合操作查询\*\*
         *  \*\*DCL控制语句\*\*
     *  \*\*函数\*\*
     *   *  \*\*数值函数\*\*
         *  \*\*日期函数：\*\*
     *  \*\*约束\*\*
     *  \*\*多表查询\*\*
     *   *  \*\*内连接\*\*
         *  \*\*外连接\*\*
         *  \*\*自连接\*\*
         *  \*\*联合查询\*\*
         *  \*\*子查询\*\*
         *   *  \*\*标量子查询\*\*
             *  \*\*列子查询\*\*
             *  \*\*行子查询\*\*
             *  \*\*表子查询\*\*
     *  事务
     *   *  事务的隔离级别（由低到高，性能由高到低）
         *   *  读未提交 等级 1
             *  读已提交 等级 2
             *  可重复读 等级4
             *  串行化隔离 等级8
 *  数据库进阶

## 数据库 ##

mysql概述

### **Sql语句** ###

在命令行窗口中，都是以分号结尾的。

#### **DDL语句** ####

##### **操作数据库** #####

show databases;
    --  都有默认的设置 "[]"不是sql里的
    create database if not exists [数据库名];
    create database [ if not exists] [数据库名] [default charset utf-8] [COLLATE 排序规则];
    drop database [if exists] [数据库名];
    use [数据库名] ;
    -- 查看当前使用的数据库
    select database();

##### **操作数据库的表结构** #####

要选中数据库再执行sql语句

-- 查询当前数据库的所有表
    show tables;
    -- 创建表结构
    create table [表名] (
        字段1 字段1的数据类型 [comment '注释']，
        字段2 字段2的数据类型 [comment '注释'],
        字段3 字段3的数据类型 [comment '注释'],
        ...
        字段n 字段n的数据类型 [comment '注释']
    )[comment '注释'];
    -- 查看表结构，但是查看不到注释的详细信息
    desc [表名]
    -- 查看表结构的详细信息
    show create table [表名];

##### **表结构的修改：** #####

-- 往表结构中添加一个新的字段
    alter table [表名] add [字段名 类型(长度)] [comment '注释'];
    -- 修改表结构中的字段名
    alter table [表名] change [旧字段名 新字段名 数据类型(长度)] ;
    -- 修改表的某个字段类型
    alter table [表名] modify [字段名 新数据类型(长度)] [comment '注释'] [约束];
    -- 删除表字段
    alter table [表名] drop [字段名];

-- 修改表名
    alter table [表名] rename to [新表名];

-- 删除表
    drop table [表名];
    -- 删除指定表 ，并重新创建表
    truncate table [表名];

#### **sql数据类型：** ####

数值类型

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>字节大小</th> 
   <th>有符号的范围</th> 
   <th>无符号的范围</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>1byte</td> 
   <td>(-128,127)</td> 
   <td>(0,255)</td> 
   <td>小整数值</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2字节</td> 
   <td>(-3276,3227)</td> 
   <td>（0，65535）</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>MEdiumint</td> 
   <td>3字节</td> 
   <td>（8388608，8388609）</td> 
   <td>（0，16777215</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>int/integer</td> 
   <td>4bytes</td> 
   <td>很大</td> 
   <td>很大</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>8bytes</td> 
   <td>很大</td> 
   <td>很大</td> 
   <td>极大整数值</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>8bytes</td> 
   <td>~</td> 
   <td>~</td> 
   <td>双精度浮点值</td> 
  </tr> 
  <tr> 
   <td>float</td> 
   <td>4bytes</td> 
   <td>~</td> 
   <td>~</td> 
   <td>单精度浮点数值</td> 
  </tr> 
  <tr> 
   <td>decima</td> 
   <td></td> 
   <td></td> 
   <td>依赖于M精度和D标度的值</td> 
   <td>小数值(精确定点数)</td> 
  </tr> 
 </tbody> 
</table>

字符类型：

变长，定长-性能-内存\\

二进制文件、文件的存储形式，长文本数据

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char</td> 
   <td>0-255bytes</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>varchar</td> 
   <td>0-65535bytes</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>tinyBlob</td> 
   <td>0-255bytes</td> 
   <td>不超过255个字符的<strong>二进制</strong></td> 
  </tr> 
  <tr> 
   <td>Blob</td> 
   <td>0-255bytes</td> 
   <td><strong>二进制</strong>的<strong>长文本数据</strong></td> 
  </tr> 
  <tr> 
   <td>Text</td> 
   <td>0-65535bytes</td> 
   <td><strong>长文本数据</strong></td> 
  </tr> 
  <tr> 
   <td>long blob</td> 
   <td>0-4294 9672 95bytes</td> 
   <td><strong>二进制形</strong>式的极<strong>大文本数据</strong></td> 
  </tr> 
  <tr> 
   <td>long text</td> 
   <td>0-4294 9672 95bytes</td> 
   <td>极大<strong>文本数据</strong></td> 
  </tr> 
 </tbody> 
</table>

**日期时间类型：**

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小</th> 
   <th>范围</th> 
   <th>格式</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date</td> 
   <td>3</td> 
   <td>1000-01-01至9999-12-31</td> 
   <td>YYYY-MM-DD</td> 
   <td>日起值</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>3</td> 
   <td>-838:59:59 至 838：59：59</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1</td> 
   <td>1901至2155</td> 
   <td>YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td>DateTime</td> 
   <td>8</td> 
   <td>1000-01-01 00:00:00 至 9999-12-3123：59：59</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>TIMESTARP</td> 
   <td>4</td> 
   <td>1000-01-01 00:00:00 至 9999-12-3123：59：59</td> 
   <td>YYYY-MM-DD HH:MM:SS</td> 
   <td>混合日期和时间值</td> 
  </tr> 
 </tbody> 
</table>

图形化界面安装：

#### **DML语句(增删改)** ####

DML-介绍：DML英文全称是Data Manipulate Language(数据操作语言) ，用来对数据库中表的数据记录进行增删改操作

*  添加数据: insert
 *  删除数据 delete
 *  修改数据 update

##### **插入数据** #####

-- 给指定数据添加数据
    insert into 表名 (字段1，字段2 ,...) values (值1,值2,...);
    -- 给全部字段添加数据,不能少，且要一一对应
    insert into [表名] values(值1,值2,...);
    -- 批量添加数据
    insert into [表名](字段1，字段2 ,...)  values (值1,值2,...),(值1,值2,...),(值1,值2,...),(值1,值2,...),...;
    insert into [表名]values(值1,值2,...),(值1,值2,...),(值1,值2,...),...;-- 不能少字段

##### **删除数据** #####

-- 根据where条件删除一条记，如果没有where语句则会删除整张表的记录
    -- delete语句不能删除莫一个字段的值
    delete from [表名] where id = 1;

##### **修改数据** #####

-- 按照where条件修改记录，如果没有带where条件则修改整张表的记录
    update [表名] set [ 字段1 = 值1 ,字段2 = 值2,... ] [where 条件];

#### **DQL语句(查询)** ####

DQL-介绍：数据查询语言

*  查询关键字 select

DQL-语法

select [字段列表] from [表名] where [条件列表] Group By [分组字段列表] having [分组后列表查询] order by[排序字段列表] Limt [分页参数]

##### **基本查询** #####

-- 查询多个字段
    select * from [表名];
    select [字段1 as 别名 , 字段2 as 别名, ...] from [表名];
    -- 在查询结果中，去除重复记录 ,distinct 只能去除单个字段，不能在整个记录中起作用
    select DIstinct [字段列表] from [表名];

##### **条件查询** #####

-- 语法形式
    select [字段列表] from [表名] where 条件列表;

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
  </tr> 
  <tr> 
   <td>&lt;&gt; 或 !=</td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td>between…and…</td> 
   <td>在某个范围之间（含最小值，含最大值）</td> 
  </tr> 
  <tr> 
   <td>in(…)</td> 
   <td>在in 之后的列表中的值，多选一</td> 
  </tr> 
  <tr> 
   <td>LIKE 占位符</td> 
   <td>模糊匹配（_匹配单个字符，%匹配任意个字符）</td> 
  </tr> 
  <tr> 
   <td>is null</td> 
   <td>是 空</td> 
  </tr> 
  <tr> 
   <td>AND 或 &amp;&amp;</td> 
   <td>并且</td> 
  </tr> 
  <tr> 
   <td>OR 或 ||</td> 
   <td>或</td> 
  </tr> 
  <tr> 
   <td>Not 或 ！</td> 
   <td>非</td> 
  </tr> 
 </tbody> 
</table>

**条件查询的实操**

select * from smbms_user where id = 1;
    select *  from smbms_user where userRole <= 2;
    select * from smbms_user where modifyBy is null;
    select * from smbms_user where modifyBy  is not null;
    select * from smbms_user where userRole != 3;
    select * from smbms_user where userRole >=1 and userRole<=3;
    select * from smbms_user where userRole between 1 and 3;
    select * from smbms_user where gender = 2 and userRole<3;
    select * from smbms_user where gender = 2 || userRole<3;
    select * from smbms_user where userRole in(1,2);
    -- 匹配查询，‘_’标识一个字符，%表示任意一个字符
    select * from smbms_user where like '__';
    -- phone末尾为4的记录
    select * from smbms_user where phone like '%4';
    -- phone 第二个字符为4的记录
    select * from smbms_user where phone like '_4%';
    -- phone从第二个开始到最后有8的记录
    select * from smbms_user where phone like '_%8%';
    -- phone不含有4到7的记录，这里的中括号是正则表达式，有用的
    select * from smbms_user where phone not like '%[4-5]%';
    select * from smbms_user where phone like '%8%';

**聚合函数：**

概念：将一列数据作为一个整体的纵向计算

<table> 
 <thead> 
  <tr> 
   <th align="center">函数</th> 
   <th align="center">功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">count</td> 
   <td align="center">统计数量</td> 
  </tr> 
  <tr> 
   <td align="center">max</td> 
   <td align="center">该列的最大值</td> 
  </tr> 
  <tr> 
   <td align="center">min</td> 
   <td align="center">该列的最小值</td> 
  </tr> 
  <tr> 
   <td align="center">avg</td> 
   <td align="center">该列的平均值</td> 
  </tr> 
  <tr> 
   <td align="center">sum</td> 
   <td align="center">该列的总值</td> 
  </tr> 
 </tbody> 
</table>

-- 语法规则
    select [聚合函数(字段)] from [表名];

**实例操作**

-- 所有都不会统计null 的计算
    select count(*) from emp; 
    select count(usernName) from emp;
    -- 其他
    select avg(age) from emp;
    select max(age) from emp;
    select min(age) from emp;
    select sum(age) frme emp where address = '江西';

**分组查询**

-- where 和 having 的区别
    -- where 是分组之前进行过滤，判断条件不同，不能操纵聚合函数
    -- having是分组之后进行过滤，判断条件不同，可以操纵聚合函数
    select [字段列表] from [表名] where [条件] group by [分组字段名] having [分组后过滤条件];

**实例操作**

select gender,count(*) from emp group by gender ;
    select gebder ,avg(age) from emp group by gender;
    -- 查询年龄小于45，并根据地址分组，获取员工数量大于等于3的地区。
    select address, count(*) from emp where age< 45 group by address having count(*)>=3;

> **执行优先级: where > 聚合函数 > having**
> 
> **分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段是没有任何意义的**

**排序查询**

*  关键字 order by
 *  ASC 升序 ，默认值
 *  DESC 降序

> 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段排序

select [字段列表] from [表名] order by [字段1 排序方式1],[字段2 排序方式2],...;

实例操作

-- 查询emp表，按照工作id 升序排序
    select * from emp order by job_id asc;
    -- 查询员工表，根据工作id升序排序后，再根据管理员工号排序
    select * from emp order by job_id asc,mgr desc;

**分页查询**

> 注意：
> 
>  *  起始索引是从0开始的，起始索引 = （查询页码-1）\* 页码显示的记录数；
>  *  分页查询是各个数据库方言，各有不同。MySQL可能与orcale 不同
>  *  如果查询时第一页数据，起始索引可以省略，直接写成limit 10；

-- 语法
    select [字段列表] from [表名] limit [起始索引 , 查询记录数];

**实例操作**

-- 分页查询
    select * from emp order by id asc limit 0 ,3
    select * from emp order by id asc limit 3,3;

#### **综合操作查询** ####

> 注意：执行顺序
> 
> 表名（from)—>条件列表（where）—>分组字段列表（group by)—>字段列表（select）—>排序字段列表（order）–>分页参数（limit)

select * from emp where job_id = 4 and salary between 7000.0 and 20000.0 and ename like '___';
    -- 
    select ename,salary ,joindate from emp  where salary < 14000.00 order by salary asc ,joindate;
    -- 
    select ename,salary ,joindate,job_id from emp  where salary < 14000.00 order by job_id desc, salary asc ;
    --
    select * from emp where job_id in (2,3,4)  and salary between 6000.00 and 15000.00 order by job_id asc ,salary desc limit 4,4;

#### **DCL控制语句** ####

DCL：用来管理数据库用户，控制数据库的访问和权限

> 注意：主机可以使用% 进行通配
> 
> 这类sql 人员操作较少，主要是DBA(数据库管理人员）使用。

sql语句如下：

-- 查寻数据库用户
    Use mysql;
    select * from user;
    -- 创建用户
    create user '用户名' @'主机名' identified by '密码';
    create user 'itpsz' @'localhost' identified by '123456';
    -- 创建的用户 pszit ，可以在任意的主机上访问该数据库，密码为123456；
    create user 'pszit'@'%' identified by '123456';
    -- 修改密码命令
    alter user '用户名'@'%' identified with mysql_native_password by '1234567';
    -- 删除用户
    drop user '用户名'@'localhost';

DQL-权限控制

<table> 
 <thead> 
  <tr> 
   <th>权限</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ALL,ALL PRIVILEGES</td> 
   <td>所有权限</td> 
  </tr> 
  <tr> 
   <td>SELECT</td> 
   <td>查询数据</td> 
  </tr> 
  <tr> 
   <td>INSERT</td> 
   <td>添加数据</td> 
  </tr> 
  <tr> 
   <td>UPDATE</td> 
   <td>修改数据</td> 
  </tr> 
  <tr> 
   <td>DELETE</td> 
   <td>删除数据</td> 
  </tr> 
  <tr> 
   <td>ALTER</td> 
   <td>修改表</td> 
  </tr> 
  <tr> 
   <td>DROP</td> 
   <td>删除数据库/表/视图</td> 
  </tr> 
  <tr> 
   <td>CREATE</td> 
   <td>创建数据库/表</td> 
  </tr> 
 </tbody> 
</table>

-- 查询权限，% 指的是任意主机
    show grants for '用户名'@'%';
    -- 授予权限， * 通配符
    grant [权限名] on 数据库.表 to '用户名'@'主机名';
    -- 撤销权限，
    revoke 权限列表 on 数据库名.表 from '用户名'@'主机名'
    ------------------------------------------------------
    -- 所有数据库，所有表，所有主机的所有权限
    grant all on *.* to  'pszit'@'%';

--------------------

### **函数** ###

什么是函数：函数是指一段可以被另一段程序调用的程序代码

字符串函数：主要是统一修改数据库字段的规范

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>contact (s1,s2,…,sn)</td> 
   <td>字符串拼接，s1,s2,…sn，拼接成一个字符串</td> 
  </tr> 
  <tr> 
   <td>lower(str)</td> 
   <td>将字符串str全部转为小写</td> 
  </tr> 
  <tr> 
   <td>upper(str)</td> 
   <td>将字符串str全部转为大写</td> 
  </tr> 
  <tr> 
   <td>lpad(str, n ,pad)</td> 
   <td>左填充，用字符串pad对str的左边进行填充，达到n个字符传长度</td> 
  </tr> 
  <tr> 
   <td>Rpad(str ,n, pad)</td> 
   <td>右填充</td> 
  </tr> 
  <tr> 
   <td>trim (str)</td> 
   <td>去掉字符串头部和尾部的空格</td> 
  </tr> 
  <tr> 
   <td>substring(str,start,len)</td> 
   <td>返回字符串str从start位置起的len个长度的字符串</td> 
  </tr> 
 </tbody> 
</table>

实例操作

-- 业务规范需求，要求工号id必须是五位数，不足五位数的左侧补0
    update emp set id = lpad(id,5,0);

#### **数值函数** ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ceil(x)</td> 
   <td>向上取整</td> 
  </tr> 
  <tr> 
   <td>floor(x)</td> 
   <td>向下取整</td> 
  </tr> 
  <tr> 
   <td>mod()</td> 
   <td>返回x/y的模</td> 
  </tr> 
  <tr> 
   <td>rand()</td> 
   <td>返回0~1内的随机数</td> 
  </tr> 
  <tr> 
   <td>round(x,y)</td> 
   <td>求参数x的四舍五入的值，保留y位小数</td> 
  </tr> 
 </tbody> 
</table>

实操实例：

-- 通过数据库的函数，生成一个随机的验证码
    select lpad(round(rand()*100000,0),6,0);

#### **日期函数：** ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>curdate()</td> 
   <td>返回当前的日期</td> 
  </tr> 
  <tr> 
   <td>curtime()</td> 
   <td>返回当前的时间</td> 
  </tr> 
  <tr> 
   <td>now()</td> 
   <td>返回当前的日期和时间</td> 
  </tr> 
  <tr> 
   <td>year(date)</td> 
   <td>获取指定的date年份</td> 
  </tr> 
  <tr> 
   <td>month(date)</td> 
   <td>获取指定的date月份</td> 
  </tr> 
  <tr> 
   <td>day(date)</td> 
   <td>获取指定date的日期</td> 
  </tr> 
  <tr> 
   <td>date_add(date,interval expr type)</td> 
   <td>返回一个日期/时间值加上一个时间间隔expr 后的时间</td> 
  </tr> 
  <tr> 
   <td>datediff(date1,date2)</td> 
   <td>返回起始时间date1和截止时间date2之间的天数</td> 
  </tr> 
 </tbody> 
</table>

-- 当前时间
    select curtime();
    select month(now());
    -- date_add .当前时间往后退七十个单位
    select date_add(now(),interval 70 day);
    -- datediff
    select datediff(now(),join_date) as Exitsdate from emp order by desc ;

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>if(value ,t,f)</td> 
   <td>如果value 为true,则返回t,否则f</td> 
  </tr> 
  <tr> 
   <td>if null (value1,value2)</td> 
   <td>如果value值不为null,返回value1,否则value2</td> 
  </tr> 
  <tr> 
   <td>case [字段名] when [value1] than [res1] …else[default] end</td> 
   <td>如果value 为true,返res1否则返回默认值</td> 
  </tr> 
  <tr> 
   <td>case [expr] when [value] than [res1] … eles[default] end</td> 
   <td>如果expr的值等于value1,返回expr，否则返回默认值</td> 
  </tr> 
 </tbody> 
</table>

流程函数：流程函数也是很常用的一类函数，可以在sql中实现条件的筛选，从而提高语句的效率

select name ,(case address when '北京' then '一线城市' when '上海' then '一线城市' end) as '工作城市' from emp;
    -- 根据成绩划分等级
    select id ,name ,(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
    				 (case when english >= 85 then '优秀' when englise >= 60 then '及格' else '不及格' end) '英语',
    				 (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文'
    from emp;

--------------------

### **约束** ###

概念：约束是作用于表中字段的规则，用于限制在表中的字段数据

目的：保证数据库中数据的正确性，有效性和完整性

<table> 
 <thead> 
  <tr> 
   <th>约束</th> 
   <th>关键字</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>非空约束</td> 
   <td>not null ,auto_increment</td> 
   <td>限制该字段不能为空</td> 
  </tr> 
  <tr> 
   <td>唯一约束</td> 
   <td>unique</td> 
   <td>保证该数据在该字段中都是唯一的</td> 
  </tr> 
  <tr> 
   <td>主键约束</td> 
   <td>primary key</td> 
   <td>主键是唯一的标识，要求非空且唯一</td> 
  </tr> 
  <tr> 
   <td>默认约束</td> 
   <td>default</td> 
   <td>保存数据时，如果没指定数值，则采用默认值来保存</td> 
  </tr> 
  <tr> 
   <td>检查约束（8.0.16)版本之后</td> 
   <td>check()</td> 
   <td>保证某一字段的数据满足某个条件</td> 
  </tr> 
  <tr> 
   <td>外键约束</td> 
   <td>forigen</td> 
   <td>用于建立两张表之间的联系**，保证数据的一致性和完整性**</td> 
  </tr> 
 </tbody> 
</table>

create table emp2022(id int auto_increment comment 'Id' primary key ,
                     name varchar(20) not null unique comment '姓名',
                     age int check(age >=0 and age <=120) comment 'nianl',
                     job varchar(20) default '暂时员工' comment '职位',
                     salary int comment '薪资',
                     entrydate date comment '入职时间',
                     manager_id int comment '直属领导',
                     dept_id int comment '部门Id'
                    )comment '员工表';

**外键约束**

概念：如果用两张表的数据建立连接，从而保证数据的一致性和完整性

create table [表名](
    [字段名] 数据类型,
        ...
        -- 一定要先创建外键字段，才可以指定这个字段为外键
        [constraint] [外键名称] foreign key [外键字段名] references [主表](主表的列名);
    )
    
    -- 第二种
    alter table [表名] add constraint [外键名称] foreign key [外键字段名] references [主表](主表的列名);
    
    -- 删处外键
    alter table emp drop foreign key [外键名称]

create table emp (
    id int auto_increment PRIMARY KEY comment '员工id',
    name varchar(20) not NULL comment '员工姓名',
    age int  comment'年龄',
    job VARCHAR(20) comment '职责',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    manager_id int not null comment '直属领导id',
    dept_id int COMMENT '部门id',
    constraint id FOREIGN key (dept_id) references dept(id)
    )comment '员工表';

### **多表查询** ###

**多表关系**

> 项目开发过程中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间的互相关联，表之间有以下关系：一对多，多对多，一对一

多的一方添加外键，多对多，则可以建立新的表，含量有两方的主键

**一对一**

案例：用户与用户之间的详情关系

关系：一对一关系，多用于单表的拆分，将一张表的基础字段放在一张表中，其他详情放在有另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的

**多表查询概述**

笛卡尔积：select \* from emp,dept;

select * from emp ,dept where dept.id = emp.id;

#### **内连接** ####

概念：相当于查询 两个表之间的交集部分

隐式内连接

select [字段列表] from 表1,表2 where 条件;
    -- 实例,取别名
    select * from emp ,dept d where d.id = emp.id;

显示内连接

select [字段列表] from 表1 inner join 表2 on 连接条件...;
    -- 实例
    select e.name ,d.name from emp e inner join dept d on e.id = d.id;

#### **外连接** ####

概念：如下

左外连接：查询左表的全部数据以及两表之间的交集数据

select [字段列表] from 表1 left outer join 表2 on 条件...;
    -- 示例
    select e.name ,d.name from emp e left outer join dept d on e.id = d.id;

右外连接：查询右表所有的数据以及两表之间的交集数据

select [字段列表] from 表1 right outer join 表2 on 条件...;
    -- 示例，不管你字段中
    select e.name ,d.* from emp e right outer join dept d on e.id = d.id;

#### **自连接** ####

概念：当前表与自身的连接查询，自连接必须使用表别名。

#### **联合查询** ####

概念：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结构，并集结果集

**注意：这两个表的结构必须相同（列数，字段类型保持一致）**

select [字段列表] from 表1...
    union [all] -- all 就是会把默认去掉的重复记录加上，
    select [字段列表] from 表2...; 
    
    -- 示例 
    select * from emp where age >=50 
    union all 
    select * from emp where salary >= 8000;

#### **子查询** ####

概念：sql语句中嵌套select语句，称为嵌套语句，又称子查询

select * from 表1 where column1 = (select column1 from 表2);

根据查询结果的不同，可分为以下几种

*  标量子查询
 *  列子查询（子查询结果为一列）
 *  行子查询（子查询结果为一行）
 *  表子查询（子查询结果为多行多列）

根据子查询的位置：分为，where 之后 ，from 之后， select 之后；

##### **标量子查询** #####

-- 示例
    select * from emp where dept_id = (select id from dept where name = '销售部');
    -- 查询在东方白入职之后的员工信息
    select * from emp where entryDate > (select entryDate from emp where name = '东方白');

##### **列子查询** #####

子查询的返回结果时一列可以是多列，做为结果衔接主句，这种称为列子查询

常用的操作符：in，not in ,any ,some,all

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>in</td> 
   <td>在指定的集合范围之内多选一</td> 
  </tr> 
  <tr> 
   <td>not in</td> 
   <td>不在指定的集合范围内</td> 
  </tr> 
  <tr> 
   <td>any</td> 
   <td>子查询列表中，有任意一个满足即可</td> 
  </tr> 
  <tr> 
   <td>some</td> 
   <td>等同于 any</td> 
  </tr> 
  <tr> 
   <td>all</td> 
   <td>子查询的返回列表的所有值都必须满足</td> 
  </tr> 
 </tbody> 
</table>

select * from emp where dept_id in (select id from emp where name = '销售部' or name = '研发部');
    -- 比财务部所有人工资都高的员工信息
    select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部') );
    -- 比研发部任意一个人工资高的员工信息
    select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name= '研发部') );
    -- 连接查询-用到了子表查询
    select * from (select * from emp e right outer join dept d on d.id = e.dept_id) e where salary > any(select salary from e where dept_name = '研发部');

##### **行子查询** #####

子查询返回的结果是一行多行。

常用操作符：= ， <> ,in，not in

-- 示例 查询'张无忌'的薪资和直属领导一致的员工信息,子查询所返回的结果是多个字段
    select * from emp where (salary ,manager_id) = (select salary ,manager_id from emp where name = '张无忌');

##### **表子查询** #####

衔接在from 后的子查询，将子查询的结果当成一个新的表，在进行筛选

-- 示例 ,在from 之后的子查询，子查询的结果当成一个表然后进行连接查询
    select e.* ,d.* from (select * from emp where entryDate > '2006-10-2') e left join dept d on dept_id = d.id;

**多表查询案例**

创建表的语句：

create table salgrade(
    grade int ,
    losal int ,
    hisal int
    )comment '薪资等级表';
    
    insert into salgrade values (1,0,3000);
    insert into salgrade values(2,3001,5000);
    insert into salgrade values(3,5001,8000);
    insert into salgrade values(4,8001,10000);
    insert into salgrade values(5,10001,15000);
    insert into salgrade values(6,15001,20000);
    insert into salgrade values(7,20001,25000);
    insert into salgrade values(8,25001,35000);
    insert into salgrade values(9,35001,45000);
    
    
    insert into salgrade VALUES(1,0,5000);
    insert into salgrade VALUES(2,0,8000);
    insert into salgrade VALUES(3,0,15000);
    insert into salgrade VALUES(4,0,10000);
    insert into salgrade VALUES(5,0,15000);
    insert into salgrade VALUES(6,0,20000);
    insert into salgrade VALUES(7,0,25000);
    insert into salgrade VALUES(8,0,3000);
    insert into salgrade VALUES(9,0,3000);
    insert into salgrade VALUES(10,0,30000);
    
    select * from salgrade;
    
    create table dept(
    d int auto_increment PRIMARY KEY comment '部门id',
    name VARCHAR(20) COMMENT '部门名称'
    )comment '部门表';
    
    -- alter table [表名] change [旧字段名 数据类型(长度)] ;
    alter table dept CHANGE d id int ;
    
    select * from dept;
    
    
    insert into dept values (1,'财务部门');
    insert into dept values (2,'研发部门');
    insert into dept values (4,'销售部门');
    insert into dept values (3,'法律部门');
     
    create table emp (
    id int auto_increment PRIMARY KEY comment '员工id',
    name varchar(20) not NULL comment '员工姓名',
    age int  comment'年龄',
    job VARCHAR(20) comment '职责',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    manager_id int not null comment '直属领导id',
    dept_id int COMMENT '部门id',
    constraint id FOREIGN key (dept_id) references dept(id)
    )comment '员工表';
    
    insert into emp VALUES(1,'潘胜志',19,'软件设计师',18000,'2001-10-12',1,2);
    insert into emp VALUES(2,'擦痕',19,'法律顾问师',18000,'2002-10-12',1,4);
    insert into emp VALUES(12,'李章',19,'法律顾问师',10000,'2002-10-12',1,4);
    insert into emp VALUES(3,'陈悦',20,'数据库维护',18000,'2004-10-12',1,2);
    insert into emp VALUES(4,'徐宏都',129,'java开发工程师',18000,'2001-10-12',1,2);
    insert into emp VALUES(5,'周志锐',77,'销售部组长',18000,'2021-10-12',1,3);
    insert into emp VALUES(6,'臣下',55,'会计',18000,'2011-10-12',1,1);
    insert into emp VALUES(7,'豫章',44,'研发部分析师',18000,'2014-10-12',1,2);
    insert into emp VALUES(8,'李志涛和',19,'网络工程师',18000,'2001-10-12',1,2);
    insert into emp VALUES(9,'里博维',29,'销售部经理',18000,'2021-10-12',1,3);
    insert into emp VALUES(10,'神采房',39,'嵌入式开发工程师',18000,'2001-10-12',1,2);
    insert into emp VALUES(11,'与张诗也',69,'会计会长',18000,'2016-10-12',1,1);
    insert into emp VALUES(13,'与张诗也',59,'会计会长',17000,'2012-10-12',1,null);
    insert into emp VALUES(14,'与张诗也',39,'会计会长',18000,'2016-10-12',1,null );

案例实操：

-- 查询员工的姓名年龄，职位，部门信息
    select ename,work,dept from emp inner join dept on dept_id = dept.id;
    -- 隐式链接
    select emp.name,age,job,dept.name from emp,dept where emp.dept_id = dept.id;
    -- 显示连接，查询员工年龄小于30的姓名，工作，部门名称，
    select emp.name ,emp.age,job,dept.name from emp inner join dept on dept_id = dept.id where age < 30;
    -- 查询拥有员工的部门id ,和部门名称,并去重
    select diatinct dept_id,dept.name from emp inner join dept on dept_id = dept.id;
    -- 查询所有年龄大于40岁的员工，及其归属的部门名称，如果员没有份配部门也展示出来，左外连接如下
    select e.*,d.name from emp e LEFT JOIN dept d on dept_id = d.id where age > 40; 
    -- 查询所有员工的工资等级，并按等级排序，这个等级是没有主外键 between and 也可以
    SELECT e.*,s.* from emp e ,salgrade s where salary >= s.losal and salary <= s.hisal order by s.grade DESC;
    -- 查询员工研发部的信息以及工资等级，
    -- 第一种实现方法，连接了两个表，但不能出现dept表的信息
    SELECT e.*,s.grade from emp e ,salgrade s where salary >= s.losal and salary <= s.hisal and dept_id = (select id from dept where dept.name = '研发部门') order by s.grade DESC;
    -- 第二种实现方法
    SELECT e.*,s.grade,d.* from emp e ,salgrade s,dept d where salary >= s.losal and salary <= s.hisal and d.id = dept_id and d.name = '研发部门' order by s.grade DESC  ;
    -- 查询研发部员工的平均薪资
    select avg(e.salary)as middleMoney from emp e LEFT JOIN dept d on e.dept_id = d.id where d.name = '研发部门';
    -- 查询员工比潘胜志薪资高的员工信息
    select * from emp where salary > (select salary from emp where name = '潘胜志');
    -- 查询比平均工资高的员工信息
    select * from emp where salary > (select avg(salary) from emp);
    -- 查询低于研发部门平均工资的员工
    select * from emp where salary < (select avg(salary) from emp,dept where dept_id = dept.id and dept.id = 1 );
    -- 查询研发部门的员工低于本部平均薪资的所有部门对应的员工信息
    select * from emp e2 where e2.salary < (select avg(salary) from emp e1 where e2.dept_id = e1.dept_id);
    -- 验证
    select *,(select avg(salary) from emp,dept where dept_id = dept.id and dept.name = '研发部门' ) '平均薪资' from emp e2 where e2.salary < (select avg(salary) from emp,dept where dept_id = dept.id and dept.name = '研发部门' );
    -- 查看平均工资对比验证
    select * ,(select avg(salary) from emp e1 where e1.dept_id = e2.dept_id) '平均薪资' from emp e2 where e2.salary < (select avg(salary) from emp e1 where e1.dept_id = e2.dept_id);
    -- 查询部门的所有信息，并统计员工的人数
    select d.id ,d.name,(select count(*) from emp e where dept_id = d.id) '人数' from dept d;
    -- 第二种，先连成一张整体表，在分组排序
    select dept.name, count(dept_id) as '人数' from emp,dept where dept_id = dept.id group by dept_id;
    -- 与第二种一致
    select  dept.name,count(dept.name), dept_id as '人数' from emp,dept where dept_id = dept.id group by dept.name;
    -- 查询所有学生选课情况，显示学生名称，学号，课程名称
    
    -- 用大量数据测试一下查询效率

### 事务 ###

事务理解：事务是一组操作的集合，他是一个不可分割的单位，十五会把**所有的操作作伪一个整体，一起向系统提交或者撤销操作，请求，这些操作要么成功，要么失败**，保证数据的完整性和一致性

开启事务 ----> 出现异常 -----> 回滚事务（抛异常） -------> 问题解决-----> 提交事务

> 例子： A账号 B账户
> 
> A账户 ----> 500元----->B账户
> 
> \\1. A账户划去500元 –> update table set account = xxx where Id = A账户 id 运行后1，出现异常，可以中断，数据不会发生变化，可以回管道之前的状态
> 
> \\2. B账户增加500元 ->
> 
> \\3. 要么成功，要么失败

-- 开启事务
    start transaction 或者 begin;
    -- 设置自动提交关闭
    set autocommit = 0;
    -- 出错,回滚事务
    rollback;
    -- 数据正确，未发生异常，提交数据
    commit;

事物的四大特性：（ACID）

原子性（ atomicity ): 事务是**不可分割的最小单元**，要么全部成功，要么全部失败

一致性（consistency）：事务完成时，必须保证**所有的数据一致**

隔离性（isolation）：数据库系统提供的隔离机制，保证事务在**不受外部并发操作的独立环境下运行**，多个事务相互隔离，在各自独立的环境下运行

持久性（Durability）：事务一旦提交或者回滚，他对数据库中的**数据的改变是永久的**

**并发事务所引发的问题**

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>脏读</td> 
   <td>读脏读错了，乱作一团</td> 
  </tr> 
  <tr> 
   <td>幻读</td> 
   <td>插不进去，或者插错了</td> 
  </tr> 
  <tr> 
   <td>不可重复读</td> 
   <td>不能插入重复的数据</td> 
  </tr> 
 </tbody> 
</table>

详细见下解答

**隔离级别**

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>read uncommitted</td> 
   <td>有</td> 
   <td>有</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>read committed（orcale ,默认级别）</td> 
   <td>无</td> 
   <td>有</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>repeatable read (数据库默认级别)</td> 
   <td>无</td> 
   <td>无</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>serializable</td> 
   <td>无</td> 
   <td>无</td> 
   <td>无</td> 
  </tr> 
 </tbody> 
</table>

#### 事务的隔离级别（由低到高，性能由高到低） ####

**数据库常隔离用命令**

-在隔离级别中常用的命令
    -设置关闭自动提交
    set autocommit = 0;
    -开启事务命令
    start transaction ;
    -查询事务级别-5.7版本以前
    select @@tx_isolation ;
    show variables like 'tx_isolation';
    -查询事务级别5.7以后；
    select @@tx_isolation ;
    show variables like 'tx_isolation';
    -- 修改给力级别命令,只是对当前会话窗口有效
    set session transaction isolation level read uncommitted;
    set session transaction isolation level read committed;
    set session transaction isolation level REPEATABLE READ;
    set session transaction isolation level serializable;
    -- 修改给力级别命令,针对所有客户端窗口有效
    set global transaction isolation level REPEATABLE READ;
    -其次便是增删改查命令,查看表结构等
    ......

*  脏读：执行SQL，就直接全局修改，回滚也不会改变数据
 *  不可重复读：在一个sql会话中，没有commit ,所查询的数据永远是刚开始的那个表的，尽管其他会话修改了数据
 *  幻读：**在一个会话中**，数据库的表被其添加了数据（id = 5 )并提交后，**在另一个会话**中**查询该插入的数据显示为空**，但在这个会话中**插入(id = 5)的语句**时就会出现，**重复提交id= 5 的数据**，进而出现了串行化限制他们进行排队。

##### 读未提交 等级 1 #####

**-read uncommitted**

> 解释：在关闭自动提交事务的情况下，在执行操纵数据库时，运行完数据库语句后，它会直接修改表中的数据，也就是只读了数据，但还没commit，就可以查出修改后的数据。这肯定会出现很多数据异常问题。

Ps 其中123步都是开启事务必备操作

--------------------

\\1. 先将默认的隔离等级设置为 读未提交。

\-隔离命令：- set session transaction isolation level read uncommitted ;

\\2. 将默认的自动提交关闭 -set autocommit = 0 ;

\\3. 其次开启事务 – start transaction ;

\\4. 此时做修改时或者插入（CRUD）时，操作者未提交 -commit 数据也会被加载入数据库，但此时操作者又可以回滚，回滚后数据与之前的数据不一致，在这一段时间里，如果利用该数据去操作相关事件，会导致回滚后的数据与之前的数据不一致。数据失常。

\\5. 所以后续出现了-读已提交机制

##### 读已提交 等级 2 #####

\-read committed:

> 解释：当操作者修改表数据后，如果没有提交 -commit ; 则不会将数据加载到数据库中，防止读脏数据
> 
> \-read committed，操作只会在当前的会话中有效，只有等提交后才全局有效

--------------------

\\1. 在关闭自动提交的基础上设置隔离级别 – set session transaction isolation level read committed ;

\\2. 当修给数据库信息时 – update user set account = ‘ 潘胜志 ’ where id = 1; 未提交数据 – commit ; ,则不会将数据修改入数据库文件。

\\3. 提交 – commit ; 后才可以查询得到修改的信息。

##### 可重复读 等级4 #####

\-repeattable Read :

> 非幻读：在一个会话中，即一个数据操纵过程中没有提交，你查询的数据库数据与你每次查询的数据都是一致的，反之亦然。
> 
> 解释：可重复读它是在读已提交的隔离级别上，增加了防止幻读的功能。即在你修改 -– update user set account = ‘ 潘胜志 ’ where id = 1; 修改数据方和查询数据方都提交数据 – commit ;时，查询放数据才会查出修改的情况。当有任何一方未提交，查询的数据任然是你上一次查询的结果。

\\1. 在关闭自动提交的基础上，修改隔离级别

\-set session transaction isolation level REPEATTABLE READ;

##### 串行化隔离 等级8 #####

\\1. 在开启事务的基础上，修改隔离级别

\-set session transaction isolation level serizabble

\\2. 当你在里另一个窗口修改数据库表时，会禁止其他窗口修改数据表，会阻塞其他的命令过程。

\\3. 当其他事务提交后，才会允许其他窗口运行操纵语句

## 数据库进阶 ##

存储引擎

索引

sql优化

视图/存储过程/触发器