MySQL

1. 了解SQL

1.1 数据库基础

数据库是一个以某种有组织的方式存储的数据集合。MySQL数据库中只有一个用户，名为root，但是它可以有多个数据库，如下是与Oracle数据库的不同之处：

1.1.1 表的概念

在数据库中，表是一种结构化的文件，用来存储某种特定类型的数据。数据库中的表都有一个唯一名字来标识自己，称为表名。同时表具有一些特性，这些特性定义了数据在表中如何存储，可以存储什么样的数据，数据如何分解、各部分信息如何让命名等等。

描述表的这组信息就是所谓的模式，模式即指关于数据库和表的布局以及特性的信息。

1.1.2 列和数据类型

表由列组成，列就是表中的一个字段。列中存储着表的某部分信息，数据库中的列都有相应的类型，数据类型定义了列中可以存储的数据种类。

1.1.3 行

虽然表由列组成，但存储过程中，表中的数据是按行存储的。每次保存的记录都存储在自己所在的行中，你也可以理解为行就是表中的一个记录。

1.1.4 主键

表中的每一行都应该有唯一标识自己的一列，这样搜索起来才不容易出错，保证数据的完整性，就如一个顾客列表可以使用顾客编号来作为主键,如果使用顾客姓名来搜索，万一有相同姓名的顾客搜索就容易混淆。因此对于每创建一个表来说都应该至少设置一列为主键，作为主键的列必须满足：

• 任意两行都不具有相同的主键值
• 每一行都必须具有一个主键值（主键列不允许为NULL值）

主键虽然通常定义在表的一列上，但是也可以使用多个列作为主键

1.1.5 外键

外键就是某个表中被定义为外键的一列，它包含另一个表的主键值，定义两个表之间的关系，一个表中可以有多个外键。其作用主要有：

• 不用重复存储另一个表中有的数据到本表，只需通过外键建立连续即可，节省了时间和空间
• 同时在删除中必须先删除外键才能删除主键，因此能保证数据的完整性

2. MySQL的安装和修改

在ubuntu中可以使用sudo apt install mysql-server进行安装MySQL数据库。以下是对数据库的的初始化必做事项

#进入本地数据库shell界面(root默认只支持locahost)
mysql -u root -p
#进入其他主机的数据库shell
mysql -h 192.168.18.251 -u root -p
#修改密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)

root默认只支持locahost连接(如下），因此需要支持其他主机连接，需要设置：

mysql> select user,host from mysql.user where user='root';
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)
#指定主机ip172.21.163.218可远程连接
UPDATE mysql.user SET host = '172.21.163.218' WHERE user = 'your_username';
#%任何主机都可以远程连接
UPDATE mysql.user SET host = '%' WHERE user = 'root';

2.1 启动和关闭MySQL服务器

MySQLS数据库分为客户端和服务器，mysql-server与mysql-client是DBMS的两个面向不同操作对象的工具。server是DBMS面向物理层次，包含存储数据的一系列机制、处理方法的集成；client是DBMS面向用户，提供一系列工具为用户所用，这些工具包括通常写的sql在内都要通过server的编译才能操作物理数据。即在连接服务器时要先开启mysql-server

//三种启动mysql服务器方法
service mysql start
systemctl statrt mysql
systemctl restart mysql

//查看状态
systemctl status mysql.service

//关闭服务器
systectl stop mysql
service mysql stop

注意数据库shell界面，shell界面是mysql-client提供了一种人与mysql直接交互的命令窗口，同Linux的bashShell是一样的。

2.2 创建新的数据库用户

一般来说我们对root用户不做任何更改，而是创建新的用户来做远程操作等等，因此创建一个用户为：

#创建了一个trluper用户，支持任何主机远程连接，密码是123456
mysql> CREATE USER 'trluper'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.14 sec)

创建完成后，trluper没有权限创建数据库也无法获取root已有的创建的数据库等，因此我们需要赋予它权限(在root用户下操作）：

mysql> GRANT CREATE ON *.* TO 'trluper'@'%';
Query OK, 0 rows affected (0.07 sec)

同样我们对root共享过来的数据库没有操作权限，因此我们要赋予权限(root用户下执行）：

mysql> GRANT ALL ON trluper.* to 'trluper'@'%';

3 数据库的基本命令

• 查看当前实例下包含多少个数据库：

  #展示目前数据库目录
  show databases；
  show CREATE DATABASE 数据库名；#查看创建库的信息

• 用户需要创建新的数据库：

  create database [IF NOT EXISTS]数据库名；
  create database 数据库名 character set GBK/UTF-8/...;#指定编码
  show variables like 'character%';#查看编码
  #创建数据库:创建一个使用utf-8字符集，并带校对规则的mydb3数据库。会对存入的数据进行检查。
  mysql> create database mydb3 character set utf8 collate utf8_general_ci;
  Query OK, 1 row affected, 2 warnings (0.03 sec)

• 删除指定数据库：

  drop database 数据库名；

• 进入该数据库

  use 数据库名；
  select database();#查看当前选择的数据库

• 修改数据库

  mysql> alter database test character set utf8;
  Query OK, 1 row affected, 1 warning (0.05 sec)

• 进入数据库后，查询该数据库下包含多少个数据表：

  show tables;

• 查看指定数据表的表结构（查看该表有多少列，每列的数据类型等信息）：

  desc 表名；

4 MySQL数据库存储机制

• MySAM：对事务支持不够好
• InnoDB：系统默认机制

  ENGING=MyISAM——强制使用第一种机制
  ENGING=InnoDB——强制使用第二种机制

5 SQL语句基础

SQL语句主要由DDL语句，DML数据库操作语句、DQL数据库查询语句、DCL数据库控制语句

5.1 DDL语句：数据定义语句

• DDL语句是用于操作数据库对象的，用来定义数据库对象,包括创建（create）、删除（drop）、修改（alter）数据库对象

• 其中数据库对象包含数据库本身database，表table、列column、索引index、约束constraint、视图view、触发器trigger、存储过程StoredProcedure和函数function）。

象名称	对应关键字	描述
表	table	表以行和列的形式存在：列就是字段，行就是记录
数据字典		就是系统表，存放数据库相关信息的表
约束	constraint	执行数据校验的规则，用于保证数据完整性的规则
视图	view	一个或者多个数据表数据的逻辑显示。视图并不存储数据
索引	index	用于提高查询性能，相当于书的目录
函数	function	用于完成一次特定的计算，具有一个返回值
存储过程	procedure	用于完成一次完整的业务处理，没有返回值，但可以通过传出参数将多个值传给调用环境
触发器	trigger	相当于一个事件监听器，当数据库发生特定事件后，触发器触发，完成相应的处理

因此上面的操作数据库的语句就是DDL语句

因此对于DDL语句来说，主要由数据库管理者使用，而开发人员用的最多的是DML数据库操作语句、DQL数据库查询语句、DCL数据库控制语句

5.1.1 DDL之操作表

本节主要简单介绍对表的操作关键字和相应的数据库数据类型，让读者对表创建有一个总体的认识。能够简单创建表，提升性能的表创建方法后续才会介绍到。在创建表之前，必须选择对哪一个数据库操作，同时你也必须了解Mysql内的的数据类型

mysql> use test;
Database changed

• MySQL的数据类型：数据类型是定义该列可以存储的数据以及该数据实际怎样存储的法则

  • 串数据类型

  • 数值数据类型

  • 日期和时间数据类型

  • 二进制数据类型：可以存储任何数据，如图像、多媒体、字处理文档等

• bit：1位，可以指定位数，如：bit(3)
• int：2字节可以指定最大位数，如int<4>,最大为4位的整数
• float：2个字节,可以指定最大的位数和最大的小数位数，如：float<5,2> 最大为一个5位的数，小数位最多2位
• double：4个字节,可以指定最大的位数和最大的小数位数，如：float<6,4>最大为一个6位的数，小数位最多4位
• char：必须指定字符数,如char(5),为不可变字符　即使存储的内容为'ab',也是用5个字符的空间存储这个数据
• varchar：必须指定字符数,如varchar(5) 为可变字符　如果存储的内容为'ab',占用2个字符的空间；如果为'abc',则占用3个字符的空间
• text: 大文本(大字符串)
• blob：二进制大数据,如图片，音频文件，视频文件
• date: 日期　如：'1921-01-02'
• datetime: 日期+时间　如：'1921-01-02 12:23:43'
• timeStamp: 时间戳，自动赋值为当前日期时间

创建表

创建表使用create table

#创建CUSTOMER表，以ID作为主键，同时指示除SALARY外其他都不能为NULL
mysql> CREATE TABLE IF NOT EXISTS CUSTOMER(
    -> NAME VARCHAR(20) NOT NULL, 
    -> ID INT NOT NULL AUTO_INCREMENT,
    -> SALARY DOUBLE NULL,
    -> HIREDATE DATE NOT NULL,
    -> PRIMARY KEY (ID)
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

//建立外键
create table epartment(
	ID INT NOT NULL,
	BELONG VARCHAR(20) NOT NULL,
	FOREIGN KEY (ID) REFERENCES CUSTOMER(ID)
)ENGINE=InnoDB;

````

**这里有几个关键字需要介绍**：

- `NULL`和`NOT NULL`:这两个关键字指示我们在插入记录对于该列来说，如果是`NOT NULL`,则插入记录时该列必须要有数据，否则会报错，而`NULL`修饰的列可以允许插入的记录在该列为空。

- `AUTO_INCREMENT`:在上述的表中`ID`是标识顾客的唯一编号，它们可以任意但是必须唯一，如果认为指定那么很难维护，`AUTO_INCREMENT`则是告诉MySQL本列每增加一行时自动增量，即每次执行一个`insert`,对该列增量。

- `PRIMARY KEY()`:指示使用哪一个或哪几个字段作为主键

- `ENGINE=InnoDB`:指示使用的引擎类型为`InnoDB` ，关于引擎类型后续会讲到

#####  更新表
有时候我们创建了表，但是对于表的字段列需要添加或者删除操作，这时候就用到了**表更新**这个概念，更新表使用`alter table`

**添加列定义：如果只新增一列，可以省略圆括号，在add后紧跟一个列定义即可。**
```mysql
alter table 表名
add
(
   #可以有多个列定义
   column_name1 datatype [default expr] [first|after col_name],
   ...
)

删除列定义：

alter table 表名
drop colum_name

修改列

alter table 表名
modify column_name datatype(修改后的类型) [default expr] [first|after col_name](指定需要将目标修改到指定位置）;

- 该修改语句每次只能修改一个列定义 - add新增的列名是原表中不存在的，modify修改的是原表中已存在的 - first 表示添加在最开始处，after xxx表示添加在xxx之后

完全改变列定义

alter table 表名
change old_column new_column type [default expr][first|after col_name]

一般只有需要修改列名时才会使用change选项

修改表的字符集:

alter table 表 character set [utf8/gbk];

truncate 表：删除表里全部数据和结构，在重新建立一个新表（更彻底，无法找回

truncate 表名

删除表

删除数据库中的表使用drop table

DROP TABLE CUSTOMER;

重命名表

如果相对表的名字重命名则可使用rename

mysql> rename table CUSTOMER TO customer;
Query OK, 0 rows affected (0.01 sec)

显示表结构

如果想对表的结构字段名、类型等了解，可通过describe来显示表结构： - describe(desc简写) 表名：显示出一个表

• show create table 表名：显示更详细，包括代码

  mysql> describe customer;
  +----------+-------------+------+-----+---------+-------+
  | Field    | Type        | Null | Key | Default | Extra |
  +----------+-------------+------+-----+---------+-------+
  | NAME     | varchar(20) | NO   |     | NULL    |       |
  | ID       | varchar(30) | NO   | PRI | NULL    |       |
  | SALARY   | double      | YES  |     | NULL    |       |
  | HIREDATE | date        | NO   |     | NULL    |       |
  +----------+-------------+------+-----+---------+-------+
  4 rows in set (0.02 sec)

5.2 数据库约束

数据库的约束类型有NOT NULL、UNIQUE、PRIMARY KEY、FOREING KEY、CHECH。

• 约束类型

  • NOT NULL：非空约束，指定某列不能为空。

  • UNIQUE：唯一约束，指定某列或者几列组合不能重复。

  • PRIMARY KEY：主键，指定该列的值可以唯一的标识该条记录，不能为NULL。

  • POREING KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。

  • CHECK：检查，指定一个布尔表达式，用于指定对应列的值必需满足该表达式

MySQL不支持CHECK约束，可使用但没卵用

5.2.1 NOT NULL约束

它只能作为列级约束使用，非空约束不能指定名字。null值：所有数据类型的值都可以是null；

空字符串不等于null，0也不等于null；

在数据库中null不等于null；

• 只要在列表定义后添加not null

  create table hehe
  (
    #建立了非空约束，这意味着hehe_id不可以为null
    hehe_id int not null,
    # MySQL的非空约束不能指定名字
    hehe_name varchar(255) default 'xyz' not null,
    #下面列可以为空，默认就是可以为空
    hehe_gender varchar(2) null
  );

• 在使用alter table 修改表时增加或者删除非空约束

  #增加非空约束
  alter table hehe
  modify hehe_gender varchar(2) not null
  #取消非空约束
  modify hehe_name varchar(2) null;
  #取消非空约束，并指定默认值
  alter table hehe
  modify hehe_name varchar(255) default 'abc' null;

5.2.2 UNIQUE约束

• 唯一约束用于保证指定列或指定列组合不允许出现重复值，但可以出现多个null
• 当某列创建唯一约束时，会为该列相应的创建唯一索引
• 如果不给唯一约束起名，该唯一约束默认与列名同名
• 如果需要为多列建组合约束，或者需要为唯一约束指定约束名，`只能用表级约束语法

使用: - 列级约束语法：在定义后增加unique关键字即可

#建表时创建唯一约束，使用列级约束法建立约束
create table unique_test
(
  #建立了非空约束
  test_id int not null,
  #unique是唯一约束，使用列级约束法建立唯一约束
  test_name varchar(255) unique
);

• 表级约束语法：[constraint 约束名] 约束定义

  • 即可放在create table中，也可放在alter table中使用关键字add来添加约束

    create table unique_test2
    (
      test_id int not null,
      test_name varchar(255),
      test_pass varchar(255),
      #使用表级约束法语句建立唯一约束
      unique (test_name),
      #使用表级约束法建立唯一约束，并指定约束名
      constraint test2_uk unique (test_pass)
      \
      #使用表级约束法建立唯一约束，指定两列组合不允许重复
      constraint test3_uk unique(test_name,test_pass)
      \
      #使用add关键字添加唯一约束
      add unique(test_name,test_pass),
    );

  • 在修改表结构时使用add关键字添加唯一约束

    alter table unique_test3
    add unique(test_name,test_pass);

  • 在修改表时使用modify关键字，为单列采用列级约束语法来增加唯一约束

    alter table unique_test3
    modify test_name verchar(255) unique;

  • 删除约束：drop index 约束名

    alter table unique_test3 drop index text3_uk;

5.2.3 PRIMARY KEY约束

• 主键约束相当于非空约束和唯一约束
• 主键列的值可用于唯一的标识表中的一条记录
• 每一个表中最多允许有一个主键，主键是表中能唯一确定一行记录的字段或字段组合
• 不管用户是否为主键约束指定约束名，MySQL总是将所有的主键约束命名为PRIMARY
• MySQL在主键约束所在列或列组合上建立对应的唯一索引

使用：primary key

• 列级约束法

  create table primary_test
  (
    #建立了主键约束
    test_id int primary key,
    test_name varchar(255)
  );

• 表级约束法:[constraint 约束名] 约束定义

  create table primary_test2
  (
    test_id int not null,
    test_name varchar(255),
    teat_pass varchar(255),
    #指定约束名对MySQL无效
    constraint test_pk primary key (test_id)
    \
    #建立多列组合的主键约束
    primary key (test_name,test_pass)
  );

  alter table primary_test3
  modify test_nama varchar(255) primary key;

• 删除指定表的主键约束：drop primary key

  alter table primary_test3
  drop primary key;
  \
  drop primary key (test_name,test_pass);

• 自增长特性：auto_increment
  • 如果某个数据列的类型是整形，而且该列作为主键列，可指定该列具有自增长功能。
  • 该功能通常用于设置逻辑主键列–该列的值没有任何物理意义，仅仅用于表示每行记录
  • 一旦指定了某列具有自增长特性，则向该表插入记录时可不为该列指定值，由数据库系统自动生成

    create table primary_test4
    (
      #建立主键约束，使用自增长
      test_id int auto_increment primary key,
      test_name varchar(255),
      test_pass varchar(255)
    );

5.2.4 FOREIGN KEY约束

• 外键约束主要用于保证一个或两个数据表之间的参考完整性，外键是构建于一个表的两个字段或者两个表的字段之间的参照关系：子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空。
• 当主表的记录被从表记录参照后，必须先把从表里参照记录的所有记录全部删除后，才可以删除主表的该记录或者删除主表记录时级联删除从表所有参照该记录的从表记录
• 从表外键参照的只能是主表主键列或者唯一键列。同一个表中可以拥有多个外键。
• 对于一对多的关联关系，通常在多的一端增加外键列
• 增加外键列的表被称为从表

使用：references

• 列级约束语法(MySQL建立的列级约束语法不会生效)

  #为保证从表参照的主表存在，通常应该先建主表
  create table teacher_table
  (
    #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
    teacher_id int auto_increment,
    teacher_name varchar(255)，
    primary key(teacher_id)
  )；
  create table student_table
  (
    #为本表建立主键约束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    #指定java_teacher参照到teacher_table的teacher_id列
    java_teacher int references teacher_table(teacher_id)
  );

• 表级约束语法：foreign key

  #为保证从表参照的主表存在，通常应该先建主表
  create table teacher_table1
  (
    #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
    teacher_id int auto_increment,
    teacher_name varchar(255)，
    primary key(teacher_id)
  )；
  create table student_table
  (
    #为本表建立主键约束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    #指定java_teacher参照到teacher_table的teacher_id列
    foreign key (java_teacher) references teacher_table1(teacher_id)
  );

  >使用表级约束语法可以为外键约束指定约束名，如果没有，MySQL会为该键命名为table_name_ibfk_n //table是从表的表名，而n从1开始的整数

• 如果需要建立多列组合的外键约束，必须使用表级约束法

  #为保证从表参照的主表存在，通常应该先建主表
  create table teacher_table3
  (
    #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
    teacher_pass varchar(255),
    teacher_name varchar(255)，
    primary key(teacher_name,teacher_pass)
  )；
  create table student_table3
  (
    #为本表建立主键约束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255),
    #指定两列的联合外键
    foreign key (java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass)
  );

• 显示指定外键约束的名字，可使用constraint来指定名字

  #为保证从表参照的主表存在，通常应该先建主表
  create table teacher_table2
  (
    #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
    teacher_id int auto_increment,
    teacher_name varchar(255)，
    primary key(teacher_id)
  )；
  create table student_table2
  (
    #为本表建立主键约束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    #指定java_teacher参照到teacher_table的teacher_id列，并指定外键约束名为student_teacher_fk
   constraint student_teacher_fk foreign key (java_teacher) references teacher_table2(teacher_id)
  );

• 删除外键约束：alter table后加 drop foreign key 约束名

  #删除student_table3表上名为student_table3_ibfk_1的外键约束
  alter table student_table3
  drop foreign key student_tables3_ibfk_1;

• 增加外键约束：add foreign key

  alter table student_table3
  add foreign key(java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass);

• 自关联：参照自身

  creater table foreign_test
  (
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    refer_id int,
    foreign key(refer_id) references foreign_test(foreign_id)
  )

• 删除主表记录：
  • on delete cascade:删除主表记录时，把参照该主表记录的从表记录全部联级删除
  • on delete set null：删除主表记录时，把参照该主表记录的从表记录的外键设为null
  • 在建立外键约束后添加

5.3 索引

• 索引是一个数据库对象，从属于数据表
• 作用：加快对表的查询
• 索引在数据字典中独立存在，但不能独立存在，必须属于某个表
• 创建索引：

  • 自动：当在表上定义主键约束、唯一约束和外键约束时，系统会为该数据自动创建对应的索引

  • 手动：用户可以通过create index语句来创建索引

• 删除索引：
  • 自动：数据表被删除时，该表上的索引自动被删除
  • 手动：通过drop index语句来删除指定数据表上的指定索引
• 一个表中可以有多个索引列，每个索引都可用于加速该列的查询速度

创建索引：

• 在创建表时创建索引：

  creat table 表名
  (
      字段1，字段2... 
      [unique]index [索引别名](列名)；//默认与列名相同
  );

  >加unique限定索引的值必须是唯一的

• 在已存在的表上创建索引

  create (unique)index index_name
  on table_name (column[,column]...);

  #提高employees基于last_name字段的查询速度
  create index exm_last_name_idx
  on emoployees (last_name);```
  
  
  - 对多列建立索引
  ```cpp
  #同时对多列建立索引
  create index emp_last_name_idx2
  on employees(first_name , last_name);

• 使用alter table创建索引

  alter table 表名
  add [unique] index [索引名] [列名]；

• 删除索引

  drop index 索引名 on 表名
  #示例
  #删除了employees表上的emp_last_name_idx2索引
  drop index emp_last_name_idx2
  on employees

6. SQL常用DQL语句

对于数据库来说，对数据库的操作离不开最重要的语句，而SQL语句就是对数据库操作的主流语言，可以说数据库和结构化查询语言SQL几乎是同义词，谈到SQL就当相于是在说数据库了。在数据库的操作中最常用的就是查询select,插入insert into，修改update和删除delete。也就是增删改差。

其中select属于DQL数据库查询语句，其他则为SML数据库操作语句，这两种语句可与SCL数据库控制语句结合

6.1 select语句

select是数据库中最常用的语句，它能从一个表或多个表中检索数据，使用select至少给出两个信息：一是要检索什么，从什么地方检索：select what from where;。你可以看作调用select会生成一个临时表作为结果集输出。

如下是最简单的两句查询语句：

//产看该表所有数据
select * from employee;
//查看特定数据
select name 姓名,birthday 出生日期 from employee where sal=15500;

上面的两条语句中：第一条既没有过滤也没有对查询结果排序，通常来说都不会使用如下低效的查询语句；第二句虽然增加了过滤条件sal=15500,也使用文字显示代替name和birthday作为输出界面，但是也没有对结果排序

6.1.1 distinct关键字

如果一个表中的一列不是主键有多个相同的值，但是我们又想查询结果只显示不同的值，这个使用就要使用到distinct关键字

//过滤表中重复数据。
select DISTINCT english from student;
select DISTINCT english,name from student;

6.1.2 limit关键字

如果我们对检索的结果不要求其返回太多话，或者是限定其应该返回几行，这个时候就应该使用limit关键字:

• limit后带一个数字的表示最多返回多少行
• limit后面带两个数字的表示从检索结果的的几行开始和返回的最多行数

mysql> select * from customer limit 5;
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| jack    | 1  |  12000 | 2022-08-03 |
| tom     | 2  |  12500 | 2022-08-03 |
| lili    | 3  |  15000 | 2022-08-03 |
| 小明    | 4  |  15000 | 2022-08-03 |
| trluper | 5  |  25000 | 2022-08-03 |
+---------+----+--------+------------+
mysql> select * from customer limit 5,5;
+------+----+--------+------------+
| NAME | ID | SALARY | HIREDATE   |
+------+----+--------+------------+
| trl  | 6  |  16000 | 2022-08-03 |
+------+----+--------+------------+

6.1.3 order by关键字

一般来说，如果select语句没有对数据进行排序，则默认使用的数据在底层表中出现的顺序而显示，它可能是数据最初添加到表的顺序，但是一旦你进行过更新和删除，这个顺序就会受到MySQL重用回收存储空间的影响。因此应该采用关键字order by对检索结构进行排序。

• 当order by后面只有一个字段名是，按该字段排序
• 当order by后面跟着多个字段名排序，首先是按照第一个字段排序，然后看排序后是否有相同的第一个字段值的记录，如果有，则对有相同第一个字段值的记录进行第二个字段排序。
• 默认情况下，使用的是升序排序，如果相要使用降序排序，则应该在后面添加desc,如...order by name DESC;

mysql> select name 姓名 from customer order by name;
+---------+
| 姓名    |
+---------+
| jack    |
| lili    |
| tom     |
| trl     |
| trluper |
| 小明    |
+---------+

6.1.4 where关键字

在上面首次介绍select时，我们已经使用了where关键字，where关键字时对检索记录的一个筛选，只有符合where后面的条件才会输出这些记录

#普通条件显示，默认是按升序排列，这里使用desc表明采用降序
mysql> select * from customer where salary>=15000 order by salary desc;
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
| lili    | 3  |  15000 | 2022-08-03 |
| 小明    | 4  |  15000 | 2022-08-03 |
+---------+----+--------+------------+
4 rows in set (0.00 sec)

#非空显示
mysql> select * from customer where salary is not null;
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| jack    | 1  |  12000 | 2022-08-03 |
| tom     | 2  |  12500 | 2022-08-03 |
| lili    | 3  |  15000 | 2022-08-03 |
| 小明    | 4  |  15000 | 2022-08-03 |
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------+

在这里我们还需要介绍能够与where组合的更高级的过滤语句关键字and、or。上面使用where都是单一条件,如果要求要符合多个条件的使用怎么办？那当然是使用and\or来解决

• and：同c++中的&&起一样的作用，优先级高于or
• or：同c++中的||起一样的作用

6.1.5 between . and .

为检查符号某个范围的，可以使用between关键，它的功能于in+[].

mysql> select Math from student where Math between 90 and 92;
+------+
| Math |
+------+
|   92 |
|   90 |
+------+
2 rows in set (0.04 sec)

6.1.5 IN关键字

IN操作符用来指定条件的范围，范围中的每个条件都可以进行匹配，它的功能与or很相像，使用IN是因为：

• 在使用长的合法选项清单是，IN操作符更加直观清楚，语句也不如or那样长冗余。
• 在使用IN时，计算的次序更容易管理
• IN操作一般比or操作符清单执行的更快
• IN操作符最多的优点是可以包含其他select语句，更加动态的建立where子句。

mysql> select * from customer where salary in(12000,15000,25000);
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| jack    | 1  |  12000 | 2022-08-03 |
| lili    | 3  |  15000 | 2022-08-03 |
| 小明    | 4  |  15000 | 2022-08-03 |
| trluper | 5  |  25000 | 2022-08-03 |
+---------+----+--------+------------+
4 rows in set (0.00 sec)

6.1.6 NOT关键字

NOT关键字有且只有一个功能，那就是否定它之后的紧跟的条件，NOT肯对and、or没什么用，但是对于IN来说却是绝配。

mysql> select * from customer where salary not in(12000,15000,25000);
+------+----+--------+------------+
| NAME | ID | SALARY | HIREDATE   |
+------+----+--------+------------+
| tom  | 2  |  12500 | 2022-08-03 |
| trl  | 6  |  16000 | 2022-08-03 |
+------+----+--------+------------+
2 rows in set (0.00 sec)

6.1.7 LIKE和通配符

通配符指的是用来匹配一部分的特殊字符；搜索模式则是指在搜索语句中使用字面值、通配符或两者组合成的搜索条件。使用通配符是必须在前面使用LIKE关键字，该关键字告诉MySQL后面跟着的搜索模式是使用通配符匹配而不是直接使用字面值匹配

• %通配符：该通配符是最常使用的，它可以表示任意字符出现任意次数，如trl%表示以trl开头的任意名称
• _通配符：同%是一样的作用，但是只匹配单个任意字符，即只匹配一次。

mysql> select * from customer where name like 'trl%';
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------+
2 rows in set (0.00 sec)

虽然通配符很有用，但是代价是很大的，它的搜索一般比前面的其他搜索所花时间更多，一般来说使用通配要遵循以下技巧：

• 尽量不使用，如果有其他操作符能够达到相同效果，则应该使用这些操作符
• 假如一定要使用，则不要把通配符放在搜索模式的开始处，因为这样的搜索极慢

6.1.8 select支持算术运算

对于MySQL中的数字类型，运行使用* + - /运算。

mysql> select salary,add_salary,salary+add_salary as finalSalary from custommer order by id;
+--------+------------+-------------+
| salary | add_salary | finalSalary |
+--------+------------+-------------+
|  12000 |        300 |       12300 |
|  12500 |        300 |       12800 |
|  15000 |        300 |       15300 |
|  15000 |        300 |       15300 |
|  25000 |        300 |       25300 |
|  16000 |        300 |       16300 |
+--------+------------+-------------+
6 rows in set (0.00 sec)

6.1.9 group by分组查询

• 表达式

  group by 属性名 [having表达式] [with rollup]

• 单独使用，只会列出第一个出现的值

  -- 只会筛选出第一个出现GradeName1的student的数据
  select * from t_student group by gradeName;

需要注意，最新的版本的 MySQL 标准不再允许 SELECT列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，默认为开启状态。

所以这样的语句是不可以的:

SELECT gender, 
       last_name 
FROM   employees 
GROUP  BY gender 

将last_name 从 SELECT 中移除或将其添加到 GROUP BY 中都可以修复：

SELECT gender,
FROM   employees 
GROUP  BY gender 

SELECT gender,
last_name
FROM employees
GROUP BY gender,
last_name

但这样的修改查询出来就可能就不是想要的结果了。针对以上情况，可以有三种方式来解决：

• 关闭 ONLY_FULL_GROUP_BY：可以选择关掉 MySQL 的 ONLY_FULL_GROUP_BY 模式，有两种方式.
  • 通过设置 sql_mode 来关闭。首先查看变更前的 sql_mode：

    mysql> SELECT @@sql_mode;
    +-----------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +-----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    通过以下脚本关闭 ：

    SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

    再次查询 @@sql_mode 返回中应该已经没有该模式了。

    mysql> SELECT @@sql_mode;
    +----------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                         |
    +----------------------------------------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +----------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

  • 第二种是找到 MySQL 配置文件修改并保存。MySQL 的配置文件名为 my.cnf，可通过以下命令查看你位置：

    $ mysql --help | grep cnf
                          order of preference, my.cnf, $MYSQL_TCP_PORT,
    /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

    找到后编辑并保存，重启 MySQL 后生效。

    [mysqld]
    -sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    +sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

    如果文件中没有 sql_mode 配置项可手动添加上。

注：因为 ONLY_FULL_GROUP_BY 更加符合 SQL 标准，所以不建议关掉。使用其他方法代替

• ANY_VALUE()：还可以通过 ANY_VALUE() 来改造查询语句以避免报错。使用 ANY_VALUE() 包裹的值不会被检查，跳过该错误。所以这样是可以的：

  SELECT gender, 
         ANY_VALUE(last_name) 
  FROM   employees 
  GROUP  BY gender 

• 添加列间的依赖：说到底上面的错误产生是因为在进行聚合的时候有不能确定的列参与了进来。假如让 gender 变成不重复的主键，last_name 便与gender 产生了一种关系，即 gender 可唯一确定 last_name。此时便可进行 GROUP BY 了

  • 与 group concat() 函数一起使用：会把所有值列出来

    -- 会把所有属于gradeName的studentName全部组合在一起列出来
    select gradeName,group concat(studentName) from t_student  group by gradeName;

  • 与聚合函数一起使用

  • 与 having 一起使用，限制输出结果

    -- 只会输出学生名字多于三个的年级及学生名字
    select gradeName,group concat(studentName) from t_student  group by gradeName having count(studentName) >3;

  • 与 with rollup 一起使用，在最后一行增加一个叠加总和

1. 当在一个SQL中同时使用where和group by和having子句时，其执行顺序为：where>group by>having。

2. where子句作用于表或者视图，having子句作用于组，having子句必须作用在group by之后 #### 6.2 正则表达式进行搜索

前面介绍的过滤使用匹配、比较和通配符进行操作寻找符合的数据，对于一些基本情况来说足够了，但是随着过滤条件的增加，where子句本身的复杂性也在增加，这时候就应当使用正则表达式匹配。使用正则表达式必须在正则表达式（文本）前面使用regexp关键字，同·like一样告诉MySQL后面使用正则表达式进行匹配。

注意在正则表达式中，对于有特殊意义的符号，如果你不希望他被翻译成则表示里的特殊符号，应该加\\进行转义，如. | []这些通过\\. \\| \\[\\]经过转义后才能被认为是字符串的一部分。

6.2.1 |

在正则表达式中|上面的or是起到一样的作用

mysql> select * from customer where name regexp 'trl|tom';
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| tom     | 2  |  12500 | 2022-08-03 |
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------+

6.2.2 []

[]的作用是匹配方括号内的字符之一，如[123]是指匹配1或2或3，同样为省略书写也可以写出[1-3]是同样的表示。

mysql> select * from customer where name regexp '[a-z]rl';
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------+

6.2.3 .

.也是正则表达式中一个特殊字符，它表示匹配任意一个字符。

mysql> select *from customer where name regexp '.rl';
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------|

6.2.4 匹配字符类

MySQL中存在一些已经定义的字符集，这样就不用你自己手动去阻止这些字符集

6.2.5 匹配多个字符

至今为止，上面介绍的都是匹配单个字符的情况，是否有匹配多个字符的正则表达式特殊字符呢，当然是有的。

mysql> select *from customer where name regexp 'trl*';
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------+
2 rows in set (0.00 sec)

注意这里的重复元字符是指能够匹配当前元字符的前一个字符的多个重复值，如trl*,匹配trl\trll\trll\trlll...\..等

6.2.6 定位符

到目前为止，上面的匹配规则都是匹配一个串中任意一个为止的文本，为了能够指定匹配的为止，给出了定位符：

mysql> select *from customer where name regexp '^trl';
+---------+----+--------+------------+
| NAME    | ID | SALARY | HIREDATE   |
+---------+----+--------+------------+
| trluper | 5  |  25000 | 2022-08-03 |
| trl     | 6  |  16000 | 2022-08-03 |
+---------+----+--------+------------+
2 rows in set (0.00 sec)

mysql> select *from customer where name regexp '^trl$';
+------+----+--------+------------+
| NAME | ID | SALARY | HIREDATE   |
+------+----+--------+------------+
| trl  | 6  |  16000 | 2022-08-03 |
+------+----+--------+------------+
1 row in set (0.00 sec)

一般来说，其他的正则表达式字符都应当与定位符结合使用

6.3 DML语句

6.3.1 insert语句

插入数据使用insert into，如下：

//插入完整数据
insert into employee values(1,'tom',15000,'1998-05-09',1,'2021-1-9'),(2,'xiaoming',15000,'1998-03-21',1,'2021-1-9');
//与select子查询结合使用
insert into  employee(id,name,salary,birthday,rank,hiredate) select id,name,salary,birthday,rank,hiredate from employee_1 where name='trluper';

6.3.2 update set语句

修改更新数据使用update:

mysql> update customer set salary=salary+500;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

6.3.3 delete语句

delete用来删除记录

//删除所有数据
delete from employee;
//删除指定数据
delete form employee where name='jack'; 

//使用truncate删除表中记录。无条件 效率高
truncate table employee;

4. 函数

有时候，我们对于搜索到的列的一些东西需要得到总和、平均和拼接，总之，我们不希望查找后的结果仍然是以底层表所存储的那样呈现上来,而是希望在select运行阶段经过处理后得到我们想要的结果，这时候就用到了想要的MySQL函数。大多数SQL支持以下函数：

• 用于处理文本串的文本函数，如删除或填充、大小写转换等。
• 用于数值数据进行算术的操作，如绝对值、求和等
• 用于处理日期和时间值并从时间值提取特定成分
• 返回DBMS正使用的特殊信息的系统函数，如返回登录信息、检查版本细节等

4.1 拼接字段

假如我们想在select中让输出的结果的一个字段即显示name又显示salary,而不是分开两个字段显示，这样就用到了concat()

mysql> select concat(name,':',salary,' ',hiredate) as 'name:salary hiredate'' from customer order by id;
+--------------------------+
| name:salary hiredate     |
+--------------------------+
| jack:12000 2022-08-03    |
| tom:12500 2022-08-03     |
| lili:15000 2022-08-03    |
| 小明:15000 2022-08-03    |
| trluper:25000 2022-08-03 |
| trl:16000 2022-08-03     |
+--------------------------+
6 rows in set (0.00 sec)

注意：在MySQL中拼接两个字段只能使用concat()函数，而在其他的数据库中则运行使用+直接拼接。

4.2 文本处理函数

4.3 日期和时间处理函数

mysql> select now(),year(now()) as 年,month(now() )as 月,date(now()) as 日; 
+---------------------+------+------+------------+
| now()               | 年   | 月   | 日         |
+---------------------+------+------+------------+
| 2022-08-03 22:26:25 | 2022 |    8 | 2022-08-03 |
+---------------------+------+------+------------+

4.4 数值处理函数

4.5 汇总函数

有时候我们需要对表中的数据进行汇总，而不是把它们实际检索出来，这时候就需要这样的函数，因此MySQL专门设立了汇总函数

#统计一个班级共有多少学生？
select count(*) from student;
#统计数学成绩大于90的学生有多少个？
select count(*) from student where math>90;
#统计总分大于250的人数有多少？
select count(*) from student where math+chinese+english>250;
#统计一个班级数学总成绩？
select sum(math) from student;
#统计一个班级语文、英语、数学各科的总成绩
select sum(math), sum(chinese), sum(english) from student;
#统计一个班级语文、英语、数学的成绩总和
select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;
#求一个班级数学平均分？
select avg(math) from student;
#求一个班级总分平均分
select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;
#求班级最高分和最低分
select max(math+chinese+english),min(math+chinese+english) from student;

4. 其他查询操作

在这里将会介绍分组查询、子查询和组合查询等查询手段，进一步掌握查询检索方法。

4.1 分组查询

分组查询得关键字为group by，使用该关键字能够使得检索后得结果按照你想要得情况进行分组,下述得group by句子指示是按salary排序并分组,这样一来我们就不用一一调用select salary count(*) where salary=各个数值;语句，只需要以group by就能执行全部，同时还有附有order by进行排序，如下所示：

#这句sql语句相当于select salary count(*) where salary=各个数值 order by salary;
mysql> select salary,count(*) from customer group by salary;
+--------+----------+
| salary | count(*) |
+--------+----------+
|  12500 |        1 |
|  13000 |        1 |
|  15500 |        2 |
|  25500 |        1 |
|  16500 |        1 |
+--------+----------+
5 rows in set (0.00 sec)

使用group by分组数据，还允许对分组进行过滤，但在这里使用得不是where，而应该使用having

mysql> select salary,count(*) from customer group by salary having salary>=13000;
+--------+----------+
| salary | count(*) |
+--------+----------+
|  13000 |        1 |
|  15500 |        2 |
|  25500 |        1 |
|  16500 |        1 |
+--------+----------+
4 rows in set (0.00 sec)

4.2 子查询

迄今为止，我们上面学的得select查询语句都是单语句得简单查询，即都是从单个数据表中检索数据得单条语句。还有一种嵌套在其他查询中查询，内部的查询语句称为子查询。有子查询的语句从内向外处理

使用子查询是因为在实际的应用中我们总不可能用一张表来存储全部的数据，而是让每一个表存储特定的内容，这样一来，我们就极有可能会用到一个表中的数据作为另一个表的字段名称或者条件来进行查询，如下：

//作为条件
select cust_id from order_num in(select order_num from orderitems where prod_id='TNT2');

//作为计算字段
select cust_name,cust_state,
(select count(*) from orders where order.cust_id=customer.cust_id) as orders from customer order by cust_name;

4.3 组合查询

组合查询是将多个select语句的组合成一个结果集返回，这些组合查询通常称为并union或复合查询。使用组合查询的情况有以下两点：

• 在多个查询中从不同表返回类似结构的数据要形成一个结果集
• 对单个表执行多个查询，要按单个查询结果集返回

这时就可使用union操作符来组合数条SQL查询语句，使用时只需要在各条select语句之间加上union关键字即可，返回的结果集中会自动的去重。

mysql> select name,salary from employee where name='trluper'
   -> union select name,salary froom employee where name='trl';
+---------+--------+
| name    | salary |
+---------+--------+
| trluper |  25500 |
| trl     |  16500 |
+---------+--------+
2 rows in set (0.00 sec)

使用union时的规则:

• union必须由两条或以上的select语句上才能使用
• union的每个查询必须包含相同的列、表达式或聚集函数（不要求相同次序列出），输出会以第一个select语句的列名称为准
• 列数据必须兼容：类型不必完全相同，但是DBMS可以隐式转换

mysql> select salary from employee where name='trluper'  union select job_name from employyee_job where id=5;
+-----------+
| salary    |
+-----------+
| 25500     |
| c++开发   |
+-----------+
2 rows in set (0.01 sec)

mysql> select name,salary from employee where name='trluper'  union select salary,name frmom employee where name='trl';
+---------+--------+
| name    | salary |
+---------+--------+
| trluper | 25500  |
| 16500   | trl    |
+---------+--------+
2 rows in set (0.00 sec)

5. 连结表（多表查询）

我们知道将数据分不同种类存储到不同的表能够更有效率，也更方便管理，单是我们不可能在检索的时候对单个表单个表的检索，我们希望能够将不同表但相同对象的数据放置在一起，这和时候就用到了连结表。SQL最强大的功能就是能在数据检索查询的执行中连结join表，连结表不存在于数据库的存储中，它是在select运行过程依据条件创建而成。一般来说使用连结表最好是定义主键和外键，通过主外键将两者两者连结在一起。

下面的例子中employee的ID为主键，employee_job的外键为ID。

mysql> select name,salary,job_name from employee,employee_job where employee.id=employee_jjob.id order by salary desc;
+---------+--------+--------------+
| name    | salary | job_name     |
+---------+--------+--------------+
| trluper |  25500 | c++开发      |
| trl     |  16500 | 老大         |
| lili    |  15500 | 技术服务     |
| 小明    |  15500 | 开发         |
| tom     |  13000 | 产品经理     |
| jack    |  12500 | 销售         |
+---------+--------+--------------+
6 rows in set (0.00 sec)

上面使用where子句来建立连结，但是对于连结表这一知识点来说，最好是使用它的特定规则来实现连结比较好即使用join关键字。如下介绍当中的：笛卡儿积、内连结、外连结。

5.1 笛卡儿积（交叉连接）

笛卡尔集是指两个集合A和B的乘积,如下

例如，有 A 和 B 两个集合，它们的值如下：
A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

我们发现笛卡儿积不遵从弄个交换律即A*B≠B*A。多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。笛卡儿积使用cross join

mysql> select name,salary,job_name from employee cross join employee_job order by name;
+---------+--------+--------------+
| name    | salary | job_name     |
+---------+--------+--------------+
| jack    |  12500 | 老大         |
| jack    |  12500 | 开发         |
| jack    |  12500 | 技术服务     |
| jack    |  12500 | 销售         |
| jack    |  12500 | 产品经理     |
| jack    |  12500 | c++开发      |
| lili    |  15500 | 开发         |
| lili    |  15500 | 技术服务     |
| lili    |  15500 | c++开发      |
| lili    |  15500 | 产品经理     |
| lili    |  15500 | 老大         |
| lili    |  15500 | 销售         |
| tom     |  13000 | 产品经理     |
| tom     |  13000 | c++开发      |
| tom     |  13000 | 销售         |
| tom     |  13000 | 技术服务     |
| tom     |  13000 | 老大         |
| tom     |  13000 | 开发         |
| trl     |  16500 | 老大         |
| trl     |  16500 | c++开发      |
| trl     |  16500 | 开发         |
| trl     |  16500 | 销售         |
| trl     |  16500 | 技术服务     |
| trl     |  16500 | 产品经理     |
| trluper |  25500 | 开发         |
| trluper |  25500 | c++开发      |
| trluper |  25500 | 技术服务     |
| trluper |  25500 | 产品经理     |
| trluper |  25500 | 老大         |
| trluper |  25500 | 销售         |
| 小明    |  15500 | 开发         |
| 小明    |  15500 | 技术服务     |
| 小明    |  15500 | c++开发      |
| 小明    |  15500 | 产品经理     |
| 小明    |  15500 | 老大         |
| 小明    |  15500 | 销售         |
+---------+--------+--------------+
36 rows in set (0.00 sec)

5.2 内连接

基于两个表直接的主键和外键相等的查询称为内部链接。只返回满足on后连接条件的数据（两边都有的才显示）。（对应oracle等值连接。）

mysql> select name,salary,job_name from employee a inner join employee_job b on a.id=b.id order by name;
+---------+--------+--------------+
| name    | salary | job_name     |
+---------+--------+--------------+
| jack    |  12500 | 销售         |
| lili    |  15500 | 技术服务     |
| tom     |  13000 | 产品经理     |
| trl     |  16500 | 老大         |
| trluper |  25500 | c++开发      |
| 小明    |  15500 | 开发         |
+---------+--------+--------------+

5.3 外连接

外连结有左外连结和右外连结，外连接与内连接的不同在于外连接对于另一个表会显示全部记录，不管符不符合后边on的条件，另一个表只显示符合条件的数据。

5.3.1 左外连结

left join是left outer join的简写，它的全称是左外连接，是外连接中的一种。外连接，左表a的记录将会全部表示出来，而右表b只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

mysql> select name,salary,job_name from employee a left join employee_job b on a.id=b.idorder by name;
+---------+--------+--------------+
| name    | salary | job_name     |
+---------+--------+--------------+
| Adson   |  19000 | NULL         |
| jack    |  12500 | 销售         |
| lili    |  15500 | 技术服务     |
| tom     |  13000 | 产品经理     |
| trl     |  16500 | 老大         |
| trluper |  25500 | c++开发      |
| 小明    |  15500 | 开发         |
+---------+--------+--------------+
7 rows in set (0.00 sec)

• 1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
• 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

5.3.2 右外连接

效果同左外是相反的

ysql> select name,salary,job_name from employee a right join employee_job b on a.id=b.id order by name;
+---------+--------+--------------+
| name    | salary | job_name     |
+---------+--------+--------------+
| jack    |  12500 | 销售         |
| lili    |  15500 | 技术服务     |
| tom     |  13000 | 产品经理     |
| trl     |  16500 | 老大         |
| trluper |  25500 | c++开发      |
| 小明    |  15500 | 开发         |
+---------+--------+--------------+
6 rows in set (0.01 sec)

6. 视图

视图是虚拟的比表，与包含数据的表不同，视图只包含使用时动态检索数据的查询。即视图是将一个查询语句包装成一个有名字的虚拟表，我们可以通过该虚拟表来使用这个查询语句。因此对于视图来说具有以下优点：

• 重用SQL语句
• 简化了复杂的SQL操作，在编写查询后，我们可以很方便重用它，而不用知道它的细节
• 使用表的组成部分而不是整个表
• 保护数据，可以给用户授予表的特定部分的权限而不是整个表的访问权限
• 更改数据格式和表示。视图可以返回与底层表不同的数据格式和表示。
• 节省空间，有了视图，不用在去创建一个同视图一样的表，在有需要的使用视图生成一个临时表即可。

mysql> create view employee_view as select name,salary,hiredate from employee,employee_job where employee.id=employee_job.id;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| employee       |
| employee_job   |
| employee_view  |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from employee_view;
+---------+--------+------------+
| name    | salary | hiredate   |
+---------+--------+------------+
| jack    |  12500 | 2022-08-03 |
| tom     |  13000 | 2022-08-03 |
| lili    |  15500 | 2022-08-03 |
| 小明    |  15500 | 2022-08-03 |
| trluper |  25500 | 2022-08-03 |
| trl     |  16500 | 2022-08-03 |
+---------+--------+------------+
6 rows in set (0.00 sec)

注意：通常来说，视图是支持更新的，即可以对视图使用和表一样的insert\update\delete操作，因为视图只是一个包装select语句的虚拟表，因此更新一个视图会更新其相关的底层表，对视图的更新就是对底层表的更新。但是并不是所有的视图都支持更新，如果视图不能正确的确定被更新的基数据所在就不能更新。

7. 存储过程

视图能够很好的重复利用SQL语句，但是只能存储select语句，存储过程能够解决这个问题，存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

优点：

• 简化操作，存储过程通过把处理语句封装在容易使用的单元中。
• 简化对变动的管理，如果有表名、列名的变动时，只需要改动存储过程内的代码即可。
• 提高性能，使用存储过程比使用单独的SQL语句要快。
• 安全性大，因此使用的是存储过程内的SQL语句，数据库管理员是可以向访问数据库中存储过程的应用程序授予适当的权限，而不是向基础数据库表提供任何权限。

缺点：

• 如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加
• 对于复杂的业务逻辑的存储过程的设计较为困难
• 开发和维护存储过程都不容易。
• 对数据库依赖程度较高，移值性差。

7.1 建立和调用存储过程

存储过程就类似于c++中的函数，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。调用存储过程使用call

• IN类型的参数表示接受调用者传入的数据；
• OUT类型的参数表示向调用者返回数据；
• INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。
• declare指示在存储过程中定义的变量，可使用set设置值

-- 创建存储过程
CREATE PROCEDURE demo2(IN s_sex CHAR(1),OUT s_count INT)
	-- 存储过程体
	BEGIN
		-- 把SQL中查询的结果通过INTO赋给变量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
		declare id int;
		set id=15;
	END;

-- 调用存储过程
call demo2('男',nums);

上面的SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;语句将统计到的数目存储到s_count以返回给调用着，因为调用后他这是将结果返回给了s_count并没有输出结果集，可通过select输出

select s_count;

7.2 删除和检查存储过程

可以通过show create procedure查看存储过程的详细信息，通过drop procedure删除存储过程

show create procedure demo2;
drop procedure demo2;

8. 触发器

触发器的作用是让某一条语句在某事件发生时会自动执行。创建触发器时必须给出四条信息：

• 唯一的触发器名称
• 触发器的关联表
• 触发器应该响应的活动，如select、insert、update、delete等
• 触发器何时执行befor\after

创建触发器使用create trigger,如下：

create trigger newproduct after insert on products for each row select NEW.product_id;

上面这条触发器名称为newproduct,触发时机为在products表对每个插入行后会执行select NEW.product.id。这里NEW时关键字指定该插入行

9. 事务处理

MySQL支持多种数据库引擎，最常见的是MyISAM和InnoDB。前者不支持事务处理，后者支持。事务处理可以用来维护数据的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行，事务处理是一种机制，利用事务处理可以保证成批的MySQL操作不会只执行一部分，如果全部执行那没问题，如果在执行过程中发生错误，它就会进行回退以恢复到数据库以前安全版本。

上面的这种事务处理机制保证了数据的完整性和安全性。事务处理需要知道的几个关键词如下：

• 事务transaction：指一组SQL语句
• 回退rollback：指撤销指定SQL语句的过程
• 提交commit：指将未存储的SQL语句写入数据库表
• 保留的savepoint：指事务处理设置的临时占位符，你可以对它发布回退

9.1 事务的特性

9.1.1 事务的四大特性acid

• 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的操作要么全部提交成功，要么全部失败回滚。
• 一致性：数据库总是从一个一致性状态转移达到另一个一致性状态，一致性状态就是由事务原子性保障，一个事务提交了，那是ok的，一个事务未提交，那也是OK的。
• 隔离性：通常来说，一个事务所做的修改在未提交以前，对其他事务是不可见的。事务间是互不干扰的。
• 持久性：一旦事务提交了，其所做的修改会永久保存到数据库总中，即使系统崩溃，修改的数据也不会丢失，（持久性不能做到100%保证策略，如果数据库本身就能做到吃性，那么要备份又有什么用呢）

9.1.2 事务的隔离级别

事务的并发问题：

• 脏读 : 能读取到另一个未提交的数据（最可怕）
• 不可重复读: 对同一记录的两次读取不一致, 因为另一事务对该记录做了修改
• 幻读(虚读): 对同一记录的两次查询不一致, 因为另一事务插入了一条记录

四大隔离级别:

• 未提交读(read uncommitted)：在该隔离级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。这个级别会导致喝多问题，从性能上来说，该级别没有比其他级别好很多，一般不推荐使用。

• 提交读(read committed)：在该级别，一个事务开始时只能看见已经提交的事务的修改。换句话说，一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的，因此也可称为不可重复的**。这个隔离级别时大多数数据库默认的隔离级别。
• 可重复读(repeatable read)：在该级别，保证了同一个事务多次读取同样的记录的结果是一致的。可重复读是MySQ的默认隔离级别，解决了脏读的问题，但是对于幻读无法解决。所谓的幻读就是指当某个事务在读取某个范围内的记录时，另一个事务又在该范围插入新的记录，当之前的事务再次读取该范围记录时，产生幻行。InooDB引擎使用多版本控制MVCC解决幻读问题。

• 可串行化(serializable1)：该隔离级别时最高的隔离级别，它通过强制事务串行执行，避免了前面说的幻读问题。简单来说，就是在读取每一行数据都加锁，所有会导致大量的超时和锁争问题。

9.2 rollback

MySQL的rollback命令用来指示回退到事务开始之前的版本，如下：

mysql> select * from employee_job;
+----+--------------+
| ID | JOB_NAME     |
+----+--------------+
| 1  | 销售         |
| 2  | 产品经理     |
| 3  | 技术服务     |
| 4  | 开发         |
| 5  | c++开发      |
| 6  | 老大         |
+----+--------------+
6 rows in set (0.00 sec)

#开启事务机制
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from employee_job;
Query OK, 6 rows affected (0.00 sec)
mysql> select * from employee_job;
Empty set (0.00 sec)

#回退
mysql> roolback;
mysql> select * from employee_job;
+----+--------------+
| ID | JOB_NAME     |
+----+--------------+
| 1  | 销售         |
| 2  | 产品经理     |
| 3  | 技术服务     |
| 4  | 开发         |
| 5  | c++开发      |
| 6  | 老大         |

9.3 commit

在Mysql中，如果不是在事务处理快，则提交的更改是默认自动立刻写入表中的。但是在事务处理块中，提交不会隐含执行，必须你显示的调用commit它才会提交到表中。但是如果在start transaction~commit当中的SQL语句出现错误时，则全部不会提交，回退事务开始前的版本

mysql> select * from employee_job;
+----+--------------+
| ID | JOB_NAME     |
+----+--------------+
| 1  | 销售         |
| 2  | 产品经理     |
| 3  | 技术服务     |
| 4  | 开发         |
| 5  | c++开发      |
| 6  | 老大         |
+----+--------------+
6 rows in set (0.00 sec)

#开始事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into employee_job values('2','老板');
Query OK, 1 row affected (0.01 sec)

#提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select* from employee_job;
+----+--------------+
| ID | JOB_NAME     |
+----+--------------+
| 1  | 销售         |
| 2  | 产品经理     |
| 3  | 技术服务     |
| 4  | 开发         |
| 5  | c++开发      |
| 6  | 老大         |
| 2  | 老板         |
+----+--------------+
7 rows in set (0.01 sec)

9.4 使用保留点

为了避免一次性对事务处理块进行全部的回退或者提交，使用savepoint来设置保留的，已达到能支持部分回退或者提交的功能。如下：

start transaction;
...
savepoint point1;
...
savepoint point2;
...
rollback to point1;
commit;

上面的程序会回退到point1，因此提交的MySQL批次只有point1前的，后面的无法提交。