MySQL

一、数据库

• 是一种 持久化 设备：可以永久的（持久的）存储数据，即使断电数据也不丢失
• 本质上来讲，数据库中的数据 是保存在硬盘上的（存的是 数据文件）
• 不同的数据库，提供了 一系列的 数据库命令（自己的语法）进行数据的 基础操作（增删改查CURD）

• 分类：

  • 关系型数据库（Relactional Database -》 RDB）

    • 数据是以一种 结构化 的方式 存储的：（Java中的类，C中 结构体）
    • 有很多数据具有完全一致的 信息
    • 数据与数据之间 存在 某种 关系（10个商品数据，5个商品类型数据）

    • 核心 操作语言是一致的，叫做 SQL

      • Structured Query Language 结构化查询语言
      • 是一门独立的语言
      • 对于所有的关系型数据库 基本 通用
    • 产品： MySQL 、 Oracle 、 SqlServer 等等......

  • 非关系型数据库

    • 产品： Redis 、MangoDB、Neo4J 等等......
    • MemCache 内存型数据库

二、MySQL安装

是一个 开源的（Free） 关系型数据库 。

基本满足 大中小微型 项目的 研发

端口：

• 是计算机与网络上的其他计算机进行信息交换的窗口
• 每一台电脑，有0 - 65535 共 65536 个 端口，有一些端口是 系统使用的端口

• 常见端口：

  • MySQL：3306
  • Oracle：1521
  • Tomcat：8080
  • HTTP：80
  • FTP：20 、 21 、 22

三、常用命令

1、登录Mysql

打开 终端，输入以下命令

# mysql -u 用户名 -p 回车
mysql -uroot -p
# 输入密码，如果没有密码，再次回车
# Access denied for user 'root'@'localhost' (using password: YES)
# 命令提示符变成mysql> 说明 已经进入了 mysql 的 客户端
mysql>

2、退出Mysql

退出mysql

mysql > exit
# ctrl + c

四、MySQL 的几个概念

1、表

table , 用于存储具有相同结构的 数据，类似于 Excel 中的 表格

2、库

database , 用于存放 表 ， 类似于 Excel 中的 sheet

3、数据库实例

MySQL 软件的一个对象， 类似于 一个 Excel 文件

MySQL 默认是 单例的

五、库的操作

1、查询已有的所有的库

show databases;
-- 结果集 ResultSet
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              | -- 核心库，用于存储mysql数据库中的核心信息，包括用户信息
| performance_schema |
| test               |
+--------------------+

2、创建一个库

create database [库名];

3、删除一个库

drop database [if exists] [库名];

4、使用库

use 库名

六、表的操作

下面内容，如无特殊说明，全部认为是SQL语法

1、创建表

指定engine 和 charset 是MySQL语法

-- 建表其实是在 规定 表中的数据的结构
create table 表名(
    列名 列类型 [约束] , 
    列名 列类型 [约束] ,
    列名 列类型 [约束] ,
    .....
    列名 列类型 [约束] 
)engine=Innodb default charset=utf8; -- 设置这张表使用的引擎和字符集 my.ini

• 表名： 建议以t_开头，表示这是一张表
• 引擎使用：innodb
• 字符集设置为：utf8

2、MySQL 中的数据类型

• 关于varchar(n) : 可变长字符，n指的是 值得 最大长度，数据长度可以小于n的值
• 关于n的值： 在Mysql4.X 版本下，单位是 字节 ， 在 MySQL 5.x 版本下，单位是 字符

-- 创建 Employee对应的表  
-- default 用于指定默认值
-- comment 相当于注释
create table t_employee(
    id int default null comment '编号',
      username varchar(200) default null comment '姓名',
      loginname varchar(200) default null comment '登录账户',
      password varchar(200) default null comment '登录密码',
      birthday date default null comment '出生日期',
       salary double  default null  comment '薪资'
)engine=Innodb default charset=utf8;

3、查看库中有哪些表

-- MySQL语法
show tables;

4、查看表的结构

-- MySQL语法
desc 表名;
-- 结果集 ResultSet
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | YES  |     | NULL    |       |
| username  | varchar(200) | YES  |     | NULL    |       |
| loginname | varchar(200) | YES  |     | NULL    |       |
| password  | varchar(200) | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| salary    | double       | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

5、查看建表语句

-- MySQL语法
show create table t_employee;

6、修改表

6-1 修改表名

alter table 表名 rename to 新名字

6-2 添加字段

alter table 表名 add column (列名 列类型 , ......)

6-3 删除字段

-- 会删除该列的数据
alter table 表名 drop column 列名

6-4 修改字段名

-- 是 标准SQL 的语法
alter table 表名 rename column 旧字段名 to 新字段名
-- 此处比较特殊，MySQL的语法为
alter table 表名 change 旧字段名 新字段名 字段类型

6-5 修改字段类型

alter table 表名 modify  字段名 新类型
# 如果要修改的字段中已经有值，可能会报错，如果数据能够自动转为新类型，那么不会报错

7、删除表

drop table t_emp;

七、约束

1、概念及分类

• 所谓的约束 指的是，对表中数据的 一种 限制
• 约束是添加在 列/字段 上的
• 可以在 定义列的时候，直接添加约束 （行级约束）
• 可以在所有的列定义完后 ， 添加约束（表级约束）

• 分类

  • 非空约束：这张表中所有的数据的某一列的值不能为空
  • 唯一约束：这张表中的某条数据的某个列的值 不能与 其他数据 相同列的值 一致（这个列所有的值都不重复）
  • 主键约束：非空 + 唯一
  • 外键约束：如果给某个列添加外键约束，那么这个列的值 来自于 （其他表的/自己表的）其他列
  • 检查约束：MySQL支持检查约束的声明，但是约束不生效

2、非空约束

Not Null , 字段值不能为 null

-- 方式1 
create table t_user (
    name varchar(200) not null , -- 行级约束
      age int
);
-- 方式2 对已有的表添加非空约束
alter table 表名 modify column 字段名 类型 not null;

3、唯一约束

为某个字段添加唯一约束后，该字段不可以有重复的值

-- 方式1 行级约束
create table t_user (
    name varchar(200) unique , -- 行级约束
      age int
);
-- 表级约束
-- 方式2 MySQL写法
create table t_user (
    name varchar(200) ,
      age int，
    unique(name) -- 表级约束，一个unique只能为一个列添加唯一约束
);
-- 方式3  标准SQL写法
create table t_user (
    name varchar(200) ,
      age int，
    constrant unique_user_name unique(name) -- 约束名可以随便写，建议写法  约束类型_表名_列名
);

-- 方式2 对已有的表添加唯一约束
alter table 表名 add unique(列名)
-- 标准SQL写法
alter table 表名 add constrant unique_user_name unique(列名)

1、唯一约束 不会 对null 做检查

2、MySQL中 null 是 不等于 null 的

4、主键约束

1. 主键约束 指的是： 非空 + 唯一 约束
2. SQL规范 要求： 每一张表 都应该 存在一个 与业务无关的 列 作为 主键使用，一般使用id作为这个列的名字

与业务无关：只是作为主键使用，并不含有其他信息

例如：

中国公民表： 身份证号就不满足要求

1. 添加方式

   -- 方式1  行级约束
   create table t_user(
       id int primary key
   )
   -- 方式2 表级约束
   create table t_user(
       id int ,
         name varchar(200),
       primary key(id)
   )
   -- 方式3 标准SQL
   create table t_user(
       id int ,
         name varchar(200),
       constrant pk_user_id primary key(id)
   )

2. 主键类型

   1. int类型 ， 可以使用auto_increment 实现主键的 自增长：插入数据时，不需要指定id的值，默认会进行自增操作（不可逆的操作）

      create table t_user(
          id int primary key auto_increment,
            name varchar(200)
      )

   2. varchar(n)类型， MySQL没有生成策略，可以通过代码生成-->UUID

   String id = UUID.randomUUID().toString();
   System.out.println(id);

5、联合主键

多个列联合在一起，作为主键使用

6、外键约束

一张表中 受到外键约束的 列 的值 （ 来自于 (其他表/当前表) 其他列 ） 或者 （为 null）

-- 员工表
create table t_emp(
    id int primary key auto_increment,
      name varchar(200),
      salary int,
      hire_date date,
    -- 每个员工应该唯一的对应一个部门
      -- 员工所在部门的数据应该来自于部门表
      -- dept_id int references t_dept(id)
      dept_id int,
      manager_id int comment '领导的id',
      foreign key (manager_id) references t_emp(id),
      foreign key (dept_id) references t_dept(id)
)engine=Innodb default charset=utf8;
-- 部门表
create table t_dept(
    id int primary key auto_increment,
      name varchar(200),
      address varchar(200)
)engine=Innodb default charset=utf8;

MySQL 中添加外键约束：

• 必须使用 表级约束
• 引擎（engine） 必须是 Innodb

否则，约束可以添加，但是不生效！！！1

被引用的表 叫做 主表 , 有外键的表 叫做 从表

从表 必须要引用主表的 具有 唯一约束的 字段（一般情况下为主表的主键）

八、引擎

engine ，驱动MySQL 数据库， 不同的引擎，提供了 不同的 驱动方式，具有不同的特性

• ISAM

  • 读取效率高，内存占用低
  • 不支持事务，不支持外键约束（可以添加，但是约束不生效），不能容错

• MyISAM

  • 对ISAM的优化
  • 存在和ISAM一样的问题

• InnoDB

  • 支持事务、外键约束
  • 可以进行 崩溃恢复
  • 设置方式

create table t_xx() engine=Innodb default charset=utf8; -- 对单表进行引擎的设置

# 在 mysql安装目录/my.ini
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

九、表关系

1、一对多关系

一张表中的一条数据，对应 另一张表中的多条数据

使用外键创建一对多关系

例如：员工表和部门表： 部门表中的一条数据，就对应员工表中的 多条数据（一个部门有多个员工）

t_a (id) , t_b(id, a_id) : 外键在哪张表，哪张表是多方

2、多对一关系

一张表中的多条数据，对应 另一张表中的同一条数据

使用外键创建多对一关系

例如：员工表和部门表： 员工表中的多条数据，就对应部门表中的 同一条数据（多个员工在一个部门）

3、一对一关系

一张表中的一条数据，对应 另一张表中的一条数据，反之亦然

例如：一对一辅导，一个老师只有一个学生，反之，一个学生在特定时间只有一个老师

• 给一个外键 添加 唯一约束
• 同时将主键作为 外键使用

4、多对多关系

一张表中的一条数据，对应另一张表中的多条数据，反之亦然

例如：商品 和 购物车

注意

• 在关系型数据库中 无法直接存储 多对多关系
• 解决方案是 建立一张 中间表 ， 将一个多对多 拆分成两个 一对多
• 中间表的 命名方式： 如果没有业务需求，命名方式为t_表A_表B
• 中间表的特征：至少含有两个外键引用其他两张表的主键

5、树形关系

另类的一对多关系，一方 和 多方 是 同一张表

使用外键，指向自己表的主键

例如： 员工和领导 ， 菜单表

-- 商品表（名称，价格,...）
-- 商品类型表（名称，父类型,...）
-- 订单表(订单编号，创建时间，总价，所属用户,...)
-- 订单详情表（单价，商品数量，商品,...）
-- 用户表（用户名，密码,...）
create table t_user(
    id int primary key auto_increment,
    name varchar(200),
    password varchar(200)
)engine=innodb default charset=utf8;

create table t_product_type(
    id int primary key auto_increment,
    name varchar(200) ,
    parentId int,
    foreign key (parentId) references t_product_type(id)
)engine=innodb default charset=utf8;

create table t_product(
    id int primary key auto_increment,
    name varchar(200),
    price double,
    type_id int,
    foreign key (type_id) references t_product_type(id)
)engine=innodb default charset=utf8;

create table t_order(
    id int primary key auto_increment,
    no varchar(200),
    create_time date,
    total_price double,
    user_id int,
    foreign key (user_id) references t_user(id)
)engine=innodb default charset=utf8;

create table t_order_details(
    id int primary key auto_increment,
    num int,
    price double,
    product_id int,
    order_id int ,
    foreign key(order_id) references t_order(id),
    foreign key (product_id) references t_product(id)
)engine=innodb default charset=utf8;

十、增删改

在SQL语句中，除了 1. 关键字 2. 列名 3. 数值类型 4. 表名 以外，其他所有值都必须加引号，数值类型也可以加引号

1、添加（Create）

1-1 方式1

-- insert into 表名 values (列1的值,列2的值....,最后一列的值);
insert into 
    t_employee 
values 
    (null,'zhangsan','zs','123','2012-12-22',4000);

要求 列1的值,列2的值....,最后一列的值部分 顺序和 个数 必须和 创建表时的列的顺序和个数一致（desc 表名 查看顺序）可读性差，不建议使用

1-2 方式2

-- insert into 表名 (列1,列2....,列N) values (列1的值,列2的值....,列N的值);
insert into 
    t_employee (loginname,password,salary) 
values 
    ('lname',3845,6000);

建议使用

2、删除操作（Delete）

-- delete from 表名 ： 删除表中的所有的数据
-- delete from 表名 where 条件
delete from
    t_employee 
where
    id = 3;

如果要删除的数据是 主表数据， 那么 从表中 不能有 数据 引用 主表要删除的数据

Cannot delete or update a parent row:

3、修改操作（Update）

-- 修改表中的所有的数据
-- update 表名 set 列名=新值 , 列名=新值 , .... 列名=新值 
-- 根据条件修改
-- update 表名 set 列名=新值 , 列名=新值 , .... 列名=新值 where 条件
update t_employee set salary = 6000 where username = 'zhangsan';
update t_employee set salary = salary * 1.5;

十一、执行sql脚本

1. 创建.sql文件

2. 在文件中编写SQL语句（一般情况下，只包含建库建表，增（删改）操作），更多情况下，用于数据库的初始化操作

   drop database if exists abc;
   create database abc;
   use abc;
   create table t_a(
       id int primary key auto_increment,
         name varchar(200)
   )engine=Innodb default charset=utf8;
   
   create table t_b(
       id int primary key auto_increment,
         name varchar(200)
   )engine=Innodb default charset=utf8;
   
   insert into t_a(name) values ('a1');
   insert into t_a(name) values ('a2');
   insert into t_a(name) values ('a3');
   insert into t_b(name) values ('b1');
   insert into t_b(name) values ('b2');
   insert into t_b(name) values ('b3');
   insert into t_b(name) values ('b4');

3. 在mysql命令行中执行命令

mysql > source .sql文件的相对路径（相对于登录mysql时的路径）或者绝对路径

十二、查询（Read）

1、基础查询

-- 查询某张表中的所有的数据，* 代表的是所有的列
-- select * from 表名;
-- 查询某张表中的所有的数据
-- select 列1, 列2, 列3 ... from 表名
select * from t_dept;
select id,name from t_dept;
select 
    name 
from 
    t_dept;

建议指定列名，即使是查询所有列 -> 为了提高代码的可读性

2、条件查询

select * from 表名 where 查询的条件 and | or 查询的条件 ....

查询条件中能够使用的运算符：

• 常规运算符

=, >,<,>=,<=, !=, <> 不等于 , +, -,*,/,%

• 特有运算符

Mysql中 null 和 任何东西（包括null）进行 = 比较，结果都是null （既不是true 也不是false）

null 和 任何值 进行 运算 ， 结果还是 null

3、为ResultSet中的列取别名

select name,salary * 12 from t_emp
-- 为结果集中的列取别名
select name, salary * 12 as year_salary from t_emp
-- as 可以省略
select name, salary * 12  year_salary from t_emp
-- 如果别名中有 空格 ， 那么 别名需要使用 引号 引起来
select name, salary * 12 as 'year salary' from t_emp

4、ifnull 函数

-- 如果 列的值 为null ，  则使用 后面的值，否则 使用原有值
ifnull(列名,'值');

5、distinct 去重

去重

-- 查询员工分布在哪些部门

-- 结果的对的，但是不是预期的效果
-- select dept_id from t_emp where dept_id is not null;
-- 应该去除重复数据
select distinct dept_id from t_emp where dept_id is not null;
-- 该语句的distinct 去重是 dept_id 和 manager_id 联合不重复
select 
    distinct dept_id , manager_id
from 
    t_emp 
where 
    dept_id is not null;

-- 报错！ 原因是 对dept_id去重后， manager_id 无法合并
select 
    ( distinct dept_id ), manager_id
from 
    t_emp 
where 
    dept_id is not null;

6、limit

MySQL 自有语法

-- select * from t_emp where id > 1 limit 数字M,数字N
-- 数字M：从第几条数据开始查询 下标从0开始
-- 数字N：一共查询几条数据
select * from t_emp limit 4,5;

用于实现 分页 功能

• 已有信息

  • 第几页 pageNo = 3
  • 每页数据条数 pageSize = 5

• sql 语句

  • select * from t_emp limit 10,5

• 通用规则

  • select * from t_emp limit (pageNo - 1)*pageSize , pageSize

7、order by

对查询结果进行排序

-- 对查询结果进行排序  不写和asc 一致： 升序排序 ， desc 降序排序
-- select * from xxx where xxx order by 字段名  [asc|desc|不写]

-- 首先按照字段A 升序排序， 如果字段A的值相同，那么再按照字段B升序排序
-- select * from xxx where xxx  order by 字段A,字段B

-- 首先按照字段A 升序排序， 如果字段A的值相同，那么再按照字段B降序排序
-- select * from xxx where xxx  order by 字段A asc,字段B desc

在order by 中， null 是 最小值

8、like

用户 模糊查询，和=相比：=做的是完全匹配，like 做的是 部分匹配

select * from xxx where xxx like '表达式';

表达式：

• 常规内容： 正常匹配
• % ： 匹配任意长度的任意内容
• _: 匹配一个长度的任意内容
• \：转义

insert into t_emp (name) values ('aaa\\b'); -- -> aaa\b
select name from t_emp where name like '%\\\%';

实际项目开发中， 如果有 分页功能，必有 查询功能（组合查询+模糊查询）

十三、查询-常用函数

1、concat

mysql> select '2a' + 'b1';
+-------------+
| '2a' + 'b1' |
+-------------+
|           2 |
+-------------+
1 row in set, 2 warnings (0.00 sec)
-- 用于字符串拼接
-- concat(str1,str2,str3,.....)
select concat('aaa','bbb','ccc','ddd') as result;

2、数学函数

abs (x), pi() , 三角函数 , rand()

2-1 ceiling(x)

向上取整， 取得是 不小于 x 的 最小整数

 select ceiling(2.34); -- 3

2-2 floor(x)

向下取整，取得是不大于 x 的最大整数

select floor(4.458); -- 4

3、日期函数

select now() -- 获取的是 当前时间

4、组函数（聚合函数）

• null 不参与 组函数运算
• 只能用于 select 后 或者 having 后

十四、查询-分组查询

将数据按照一定条件进行分组。

-- 查询每个部门的平均薪资和部门编号

-- 这种写法在标准SQL中是错误的  因为两个列的 长度不一致，不能出现结果
mysql> select dept_id , avg(salary) from t_emp;
+---------+-------------------+
| dept_id | avg(salary)       |
+---------+-------------------+
|      20 | 2073.214285714286 |
+---------+-------------------+
1 row in set (0.00 sec)

-- 根据部门id进行分组后，查询
mysql> select dept_id , avg(salary) from t_emp group by dept_id;
+---------+--------------------+
| dept_id | avg(salary)        |
+---------+--------------------+
|    NULL |               5000 |
|      10 |               1875 |
|      20 |               2175 |
|      30 | 1566.6666666666667 |
+---------+--------------------+
4 rows in set (0.00 sec)


-- 先根据部门编号进行分组，在同一组中，再根据manager_id 进行分组
select dept_id , manager_id from t_emp group by dept_id , manager_id;

-- 求各个部门的最高薪资和最高薪资这个人的姓名
-- 错误的！ 在标准SQL中要报错的！
select name, max(salary) from t_emp group by dept_id

如果SQL中出现了group by，select 后面的列 ：

• 出现在 group by 中
• 使用 组函数（聚合函数）

-- 查询 平均薪资  大于 2000 的部门id 和 平均薪资
-- select dept_id , avg(salary) from t_emp where avg(salary) > 2000 group by dept_id;
ERROR 1111 (HY000): Invalid use of group function
-- select dept_id , avg(salary) from t_emp  group by dept_id where avg(salary) > 2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'where avg(salary) > 2000' at 
line 1

如果对于分组有条件限制(对分组后的限制)，那么应该在 group by 后 使用 having 关键字 指定条件

select dept_id , avg(salary) from t_emp  group by dept_id having avg(salary) > 2000;

十五、多表查询

1、概念及分类

多表查询指的是： 将多张表的数据，按照某种 逻辑关系，联系在一起 ，进行 查询操作

分类：

• 子查询

  • 非关联子查询
  • 关联子查询

• 连接查询

  • 左连接
  • 右连接
  • 内连接
  • 全连接
  • 笛卡尔积

十六、子查询

1、非关联子查询

一个查询A 嵌套在 另一个查询B的 where/having 关键字后，此时， A 称为 B 的子查询

-- 查询工资最低的员工的姓名和工资
select
    name, salary
 from
    t_emp
 where
    salary = (
      select
        min(salary)
      from
        t_emp
    )

2、关联子查询

一个查询A 嵌套在 另一个查询B的 select/from 关键字后，此时， A 称为 B 的子查询

不论是否会报错，请养成一个习惯：

只要SQL 中出现2张（包括同一张表出现两次）及以上 表， 请为每张表起一个别名

-- 查询公司所有部门的部门编号，部门名称以及 这个部门的 员工数量
select
    d.id,d.name,(
        select 
            count(1)
        from
            t_emp as e
        where
            e.dept_id = d.id
    ) as total
from
    t_dept as d

-- 查询公司所有部门的部门编号，该部门的最高薪资 和 该部门的平均薪资
select
    d.id,
    (
        select 
            max(e1.salary)
        from
            t_emp e1
        where
            e1.dept_id = d.id
    ) max_salary,
    (
        select 
            avg(e2.salary)
        from
            t_emp e2
        where
            e2.dept_id = d.id
    ) avg_salary
from
    t_dept d

十七、连接查询

1、概念和分类

将两张及以上的表中的数据，按照 某种 规则 连接在一起，变成查询结果

分类：

• 内连接: inner join
• 左连接: left join
• 右连接: right join
• 全连接: full outer join
• 笛卡尔积

2、笛卡尔积

将A表的每一条数据，无条件和 B 表的 每一条数据 连接在一起，形成一条新数据

select * from A , B

本身数据无实际意义，使用场景极少

3、全连接

-- 将A 表中的每一条数据 和 B 表中的每一条数据 进行连接，
-- 保留 
--   1.满足连接条件的数据，
--   2.如果A表中的某一条数据在B表中没有找到满足条件的数据，也会保留该数据，但是B表的字段值全部为null
--   3.如果B表中的某一条数据在A表中没有找到满足条件的数据，也会保留该数据，但是A表的字段值全部为null
-- select * from  A a full outer join B b on 连接条件

MySQL 不支持

4、left / right / inner join

1. 语法

   select 
       *
   from
       表A a -- 主表
   left/right/inner join 表B b on 连接条件  -- 子表

2. 作用

将A表中的每一条数据 和 B 表中的每一条数据 进行连接，保留

• 满足连接条件的数据
• 对于内连接（inner join ）而言： 只保留所有满足条件的
• 对于左连接（left join） 而言： 加上 A 表中 所有在 B 表找不到满足连接条件的数据，B 表部分的字段值为null
• 对于右连接（right join） 而言： 加上 B 表中 所有在 A表找不到满足连接条件的数据，A表部分的字段值为null

5、总结

-- 查询所有的员工姓名 和 所在部门 名称
select 
    e.name , d.name
from
    t_emp e
left  join t_dept d  on e.dept_id = d.id
-- 所有的部门名称，以及部门人数
select
    d.name, count(e.id)
from 
    t_emp e
right join t_dept d on e.dept_id = d.id
group by d.name

十八、案例

-- ---------------------基础查询---------------------------------
-- 1.查询 部门表中的所有数据
select id,name from t_dept
-- 2.查询 所有员工的 姓名 和 薪资
select name,salary from t_emp

-- ---------------------条件查询---------------------------------
-- 3.查询 所有员工的 姓名 和 年薪
select name,salary * 12 from t_emp -- select中做运算，只对查询结果产生影响，对原始数据不产生影响
-- 4.查询 boss 的姓名（manager_id 为null 的）
select name from t_emp where menager_id is null
-- 5.查询 所有薪资 低于2000 的员工
select name,salary from  t_emp where salary < 2000
-- 6.查询 所有薪资 在 2000- 3000 之间的员工信息
select name,salary from t_emp where salary >= 2000 and salary <= 3000
select name,salary from t_emp where salary between 2000 and  3000
-- 7.查询10号部门的所有的员工信息和 老板的信息
select name from t_emp where dept_id = 10 or manager_id is null

-- ---------------------ifnull---------------------------------
-- 8 查询所有员工的姓名和 部门编号，没有部门 ， 显示 BOSS
select name, ifnull(dept_id,'BOSS') from t_emp

-- ---------------------distinct---------------------------------
-- 9 查询员工分布在哪些部门
select distinct dept_id from t_emp where dept_id is not null;

-- ---------------------作业---------------------------------
-- 10. 查询20号部门所有的员工姓名和薪资
select name,salary from t_emp where dept_id = 20
-- 11.查询所有年薪>40000的员工姓名和部门编号，如果为null ，显示BOSS
select name,ifnull(dept_id,'BOSS') from t_emp where salary * 12 > 40000
-- 12.查询所有月薪 > 4000 或者 月薪 < 3000 的员工姓名
select name from t_emp where salary > 4000 or salary < 3000
-- 13.查询10号部门和20号部门的所有员工的姓名（2种方式）
select name from  t_emp where dept_id in(10,20)
select name from t_emp where dept_id = 10 or dept_id = 20
-- 14.查询除了10号部门和20号部门的所有员工的姓名（2种方式：此题有坑）
select name from t_emp where dept_id not in (10,20) or dept_id is null
select name from t_emp where dept_id!=10 and dept_id!=20 or dept_id is  null

-- ---------------------order by-----------------------
-- 15.查询员工的年薪，ResultSet中显示的名字是 salary of year，按照降序排序
select name,salary*12 as 'salary of year' from t_emp order by salary desc;
-- 16.查询员工的姓名和部门编号，按照部门编号降序排序
select name,dept_id from t_emp order by dept_id desc;


-- --------------------------常用函数---------------------------
-- 17.修改所有员工的姓名：前面追加e_ , 后面追加 id值
update t_emp set name = concat('e_',name,'_',id);

-- --------------------------组函数---------------------------
-- 18. 求20号部门的最高薪资
select max(salary) from t_emp where dept_id = 20
-- 19. 求10号部门的最低薪资
select min(salary) from t_emp where dept_id = 10
-- 20. 求员工的平均薪资
select avg(salary) from t_emp
-- 21. 求员工的薪资总和
select sum(salary) from t_emp
-- 22. 求30号部门的员工数量
select count(salary|id|name|*|1|3 ) from t_emp where dept_id = 30
-- 23. 求有员工的部门的数量
select count(distinct dept_id) from t_emp
-- 24. 将7934号员工的薪资 修改为 null
update t_emp set salary = null where id = 7934
-- 25. 求10号部门的平均薪资，null 认为薪资为0
select avg(ifnull(salary,0)) from t_emp where dept_id = 10;


-- --------------------------子查询---------------------------
-- 26. 找出所有allen 的同部门同事
select
    * 
from 
    t_emp 
where 
    dept_id=(
          select 
              dept_id 
          from 
              t_emp 
          where 
              name='allen'
     ) 
    and name !='allen';
-- 27. 找出所有的领导的姓名
select name from t_emp where id in (select manager_id from t_emp);
-- 28. 找出所有 不是领导的 员工姓名
select 
    name 
from 
    t_emp 
where 
    id not in (
          select 
              manager_id 
          from 
              t_emp 
          where
              manager_id is not null 
    )
-- 29. 找出所有没有员工的部门
select d.name from t_dept d where d.id not in 
(select dept_id   from t_emp where dept_id is not null );
-- 30. 找出所有工资比martin高的员工
select *
from t_emp e
where e.salary>(select salary from t_emp where name='martin');
-- 31. 找出各个部门工资最低的员工姓名和薪资
select 
    e.name , e.salary,e.dept_id
from
    t_emp e 
where
    salary in
    (
      select
         min(ex.salary) 
      from
          t_emp ex
      where
            ifnull(ex.dept_id,0) = ifnull(e.dept_id,0)
    )
-- 32. 找出人数最多的部门的部门名称
select d.name from t_dept d where d.id in (
  select dept_id from t_emp group by dept_id having count(1) = 
  (
    select max(t.c) from (
        select dept_id , count(1) c from t_emp group by dept_id
    ) t
  )
)
-- 33. 找出那些部门的平均薪资比research部门的平均薪资高，需要平均薪资和部门编号，按照平均薪资降序
select avg(salary),dept_id from t_emp group by dept_id having avg(salary)>(
 select avg(salary) from t_emp where dept_id = (
select id from t_dept where name = 'research'))order by avg(salary) desc;
--     排序
-- --------------------------连接查询---------------------------
-- 34. 查询所有员工的姓名和部门名称，没有部门的员工不需要展示
select  e.name ,d.name 
from t_emp e inner join t_dept d  on e.dept_id=d.id ;

-- 35. 查询所有员工的姓名和部门名称，没有部门的员工展示BOSS
select e.name,ifnull(d.name,'boss')
from t_emp e left join t_dept d on e.dept_id = d.id;

-- 36. 查询jones所在部门的部门名称
select d.name  
from t_dept d 
left join t_emp e on e.dept_id = d.id
where e.name = 'jones';

-- 37. 找出谁是领导
select distinct m.name from t_emp e inner join t_emp m on e.manager_id = m.id;
-- 38. 找出谁不是领导(难！)

select m.name 
from
t_emp e  right join t_emp m 
on e.manager_id = m.id
where e.id is null 
-- 39. 找出那些部门没有员工
select  d.name  
from t_dept d 
left join t_emp e on e.dept_id = d.id
where e.id is null;
-- 40. 找出那些部门有员工
select distinct d.name  
from t_dept d 
inner join t_emp e on e.dept_id = d.id
-- 41. 找出所有工资比martin高的员工
select e.name from t_emp e left join t_emp m on m.name='martin' where e.salary>m.salary ;

十九、事务(transaction,tx)

1、概念

一次不可再分的操作，即 一个 操作单元

一次操作 VS 一次不可再分的操作

一次操作指的是： 对数据库 进行 一次（select / update / delete / insert / create /drop ...）操作

一次不可再分的操作： 可能是对数据库进行 多次 操作，这多次操作，如果有一次失败了，那么要将数据还原为 所有操作进行前 的状态，就像这些操作从来没有做过一样， 当且仅当所有的操作都成功执行，才认为执行成功

例如 实现银行的转账功能：

t_account : id , acc_no , num .....

if: select num from t_account where acc_no = 'a' >= ?

​ update t_account set num = num - ? where acc_no = 'a'

​ update t_account set num = num + ? where acc_no = 'b'

2、事务的特性（ACID）

• 原子性（Atomicity）: 不可再分
• 一致性（Consistency）：数据的一致性，事务结束后，所有人读取到的数据是一致的
• 隔离性（Isolation）: 不同事务之间 互不影响
• 持久性 （Durability）：事务一旦结束，在进行其他操作之前，多次查询数据，数据不变

3、事务要解决问题

多线程并发下，不用事务会产生很多问题：

多个人 同时 对 同一张表中的数据进行操作: 购物

• 脏读（Dirty Read）：一个事务（人） 读取到了 另一个事务（人） 没有提交的数据

A 1000 B 1000 C 1000

A 账户 向 B 账户 转入 800 元 同时 B 账户 向 C 账户 转入 1600 元

1.判断A 账户中是否>=800元

2.A 账户扣除 800元

3.B 账户 存入 800元

3.5 结束操作

---

4.判断B账户中是否>=1600元

5.B 账户扣除 1600元

6.C 账户 存入 1600元

6.5 结束操作

• 幻读（虚读: Phantom Read）: 一次事务中（人）， 进行多次读取操作，读到的 数据量 不一致

一个人 两次执行 select count(1) from t_xxx

在 第二次 执行 之前， 另一个人 执行了 delete from t_xxx

• 不可重复读（Non-Repeatable Read）:一次事务中（人）， 进行多次读取操作，读到的 数据 不一致

剩余量，库存......

4、事务的隔离级别

数据库提供了几种隔离级别 来解决 上面的问题

NONE=不支持事务
READ_COMMITTED=可以阻止脏读的发生，不可重复读和虚读 可能发生
READ_UNCOMMITTED=脏读 ，不可重复读和虚读 都可能发生
REPEATABLE_READ=可以阻止脏读，不可重复读的发生，虚读 可能发生(mysql的默认隔离级别)
# 一定不用这个，串行化，响应速度慢，会导致 操作的 积压
SERIALIZABLE=三个都可以阻止发生

查看：在mysql 命令行下，输入以下命令

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
-- 修改隔离级别
set global/session transaction isolation level [repeatable-read]

5、事务的控制

1. 开启事务

   # 将 自动提交 设置 为 false --》 关闭数据库的自动提交功能---> 开启事务
   set autocommit = false;

2. 提交事务

   # 将数据进行持久化保存
   commit;
   # 执行 DDL ， 事务 自动 提交

3. 回滚事务

   # 将数据还原到上一次commit的状态，另外： 回滚不会回滚id的自增长
   rollback;

4. 设置回滚点

   savepoint 回滚点名称;

5. 回滚到特定回滚点

   rollback to 回滚点名称;

6. 案例：

# 开启事务
set autocommit = false;
# 删除id < 7567
# 保存回滚点a
savepoint a;
# 删除id < 7789
# 保存回滚点b
savepoint b;
# 回滚到回滚点a
rollback to a;
# 回滚到回滚点b
rollback to b; --> savepoint b does not exist

6、事务锁

事务通过事务锁 实现 ACID 特性，以及 解决 脏读 不可重复读 和 幻读问题

在数据库中，有 这样两把锁：

• 表级共享锁：在一个事务 访问 某张表的时候，允许 另一个事务 对同一张表 进行 查询操作
• 行级排它锁：在一个事务 修改（insert/update/delete） 某张表的 某条数据时,不允许另一个事务修改同一条数据

二十、SQL

SQL : Structured Query Language 结构化 查询 语言

是一种 通用于 绝大多数 关系型数据库 的 查询语言

分类：