2024-01-重学MySQL

0 SQL

0.1 SQL分类

SQL语言在功能上主要分为如下三大类：

• DDL（Data Definition Languages，数据定义语言）：这些语言定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构
  • 主要的语句关键字包括 CREATE、DROP、ALTER、RENAME、TRUNCATE等
• DML（Data Nabipulation Language，数据操作语言）：用于增删改查数据库记录，并检查数据完整性
  • 主要的语句关键字包括 INSERT、DELETE、UPDATE、SELECT等
  • SELECT是SQL语言的基础，最为重要
• DCL（Data Control Language，数据控制语言）：用于定义数据库、表、字段、用户的访问权限和安全级别
  • 主要的语句关键字包括 GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等

说明：

​ 因为查询语句使用比较频繁，很多人帮他单拎出来一类：DQL（数据查询语言）

​ 还有单独将 COMMIT、ROLLBACK 取出来称为 TCL（Transaction Control Language，事务控制语言）

0.2 SQL语言的规则和规范

0.2.1 基本规则

• SQL可以写在一行或多行。为提高可读性，各个子句分行写，必要时使用缩减

• SQL语句结束标准 ; \g \G \G可以使输出更美观

• 关键字不能被缩写，也不能分行

• 关于标点符号：

  • 必须保证所有的 ()、单引号、双引号 都是成对结束的
  • 必须使用英文状态下的半角输入方式
  • 字符串型和日期时间类型的数据可以使用 单引号（‘’）表示
  • 列的别名，尽量使用双引号（“”），而且不建议省略 as

0.2.2 SQL的规范

• MySQL 在Windows环境下是大小写不敏感的

• MySQL 在Linux环境下是大小写敏感的

  • 数据库名、表名、表的别名、变量名 都是严格区分大小写的
  • 关键字、函数名、列名（或字段名）、列的别名 是忽略大小写的

• 推荐采用同一的书写规范：

  • 数据库名、表名、表的别名、列名、列的别名 等都小写
  • SQL关键字、函数名、绑定变量等都大写

0.2.3 MySQL的三种注释

#单行注释（mysql特有的方式）

-- 单行注释（-- 后面必须包含一个空格）

/*
	多行注释
*/

0.2.4 命令规则

• 数据库、表名不得超过30个字符，变量名限制为 29 个
• 必须只能包含 A-Z、a-z、0-9，_ 共63个字符
• 数据库名、表名、字段名等对象名中间不能出现空格
• 同一个MySQL中，数据库不能同名；同一个库中，数据表不能重名；同一张表中，字段不能重名
• 必须保证你的字段名和和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（反引号）引起来
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假设数据类型在一个表里是整数，拿在另一个表中就变成字符型了，这显然不合适

1、数据库基本操作

1.1、创建数据库

create database [if not exists] company;
create database [if not exists] company charset=utf8;  -- 推荐

数据库命令规则：

字母、数字、下划线、@、$、#，不允许有空格和特殊字符，最大长度是128位

1.2、修改数据库

alter database company charset=utf8;

1.3、切换/选择数据库

use company;

1.4、查看当前所在数据库

select database();

1.5、查询建库语句

mysql> show create database company;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| company  | CREATE DATABASE `company` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.01 sec)

1.6、查看所有数据库

show databases;

1.7、删除数据库

drop database databasetest1;

2、MySQL存储引擎和数据类型

-- 查看当前数据库支持的存储引擎
SHOW ENGINES ;

其中

engine 表示存储引擎名称

support 表示该DBMS是否支持该引擎 ，yes 支持，no 不支持，default DBMS支持的默认存储引擎

comment 该存储引擎的评论

transactions 是否支持事务

XA 表示该存储引擎支持的分布式是否符合XA规范

savepoints 表示是否支持事务处理中的保存点

安装版MySQL5.5默认支持的存储引擎为InnoDB

2.1、查看所有支持的存储引擎

show variables like 'have%';

2.2、修改默认存储引擎

打开MySQL安装目录下的my.ini配置文件

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8  # mysql服务端的字符集
default-storage-engine=INNODB  # 存储引擎
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

log-output=FILE
general-log=1
general_log_file = "D:\mysql.log"
slow-query-log = 1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2

2.3、数据类型

整数类型	字节	范围
TINYINT	1	-128~127 0~255
SAMALLINT	2
MEDIUMINT	3
INT和INTEGER	4
BIGINT	8

浮点类型	字节
FLOAT	4
DOUBLE	8

定点数型
DEC(M,D) 和 DECIMAL(M,D)	M+2

存储小数是可以采用浮点型float或double，当对小数精度要求非常高时，可以采用dec和decimal

位类型	字节
BIT(M)	1~8字节

日期和时间类型	字节	范围
DATA	4	1000-01-01~9999-12-31
DATATIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP	4	19700101080001~2038年的某个时刻
TIME	3	-835:59:59~838:59:59
YEAR	1	1901~2155

CHAR型
CHAR(M)	M字节	M为0~255之间的整数	适用于定长字符串
VARCHAR(M)	M字节	M为0~65535之间的整数	适用于变长字符串

TEXT型	字节	描述
TINYTEXT	0~255	值的长度为+2个字节
TEXT	0~65535	值的长度为+2个字节
MEDIUMTEXT	0~167772150	值的长度为+3个字节
LONGTEXT	0~4294967295	值的长度为+4个字节

BINARY	字节	描述
BINARY(M)	M	允许长度为0~M
VARBINARY(M)	M	允许长度为0~M

BLOB型	字节
TINYBLOB	0~255
BLOB	0~2的16次方
MEDIUMBLOB	0~2的24次方
LONGBLOB	0~2的32次方

​

3、表的基本操作

3.1、创建表

在数据库company，创建表t_dept

create database company;  -- 创建数据库
use company;  -- 切换数据库

create table if not exists t_dept(
     deptno INT,
     dname VARCHAR(20),
     loc VARCHAR(40)
) charset=utf8;  -- 表默认的字符编码就是当前数据库的字符编码

经验之谈：

​ 创建一张表，会在相应文件夹中产生两个文件，一个 frm 结尾（存储表结构），另一个 ibd 结尾（存储数据）

3.2 查询表结构/查看表定义 DESC

describe t_dept;
-- 或简写
desc t_dept;

3.3、show查看建表语句 /查看表详细定义

show create table t_dept;

查看当前数据库的所有表

show tables;

3.4、删除表

drop table t_dept;

3.5、修改表

3.5.1、修改表名

alter table 旧表名 rename [to] 新表名

mysql> alter table t_dept rename to tep_dept;
Query OK, 0 rows affected (0.04 sec)

3.5.2、增加字段

3.5.2.1、在表末尾增加字段

alter table tb_name ADD 属性名 属性类型

mysql> alter table t_dept add test char;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| test   | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

3.5.2.2、在表开头增加字段

alter table tb_name ADD 属性名 属性类型 FIRST

mysql> alter table t_dept add test2 char first;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| test2  | char(1)     | YES  |     | NULL    |       |
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| test   | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3.5.2.3、在表的任意位置添加字段

alter table tb_name ADD 属性名 属性类型 after 属性名

-- 演示在 loc字段 后添加 test3字段
mysql> alter table t_dept add test3 char after loc;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| test2  | char(1)     | YES  |     | NULL    |       |
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| test3  | char(1)     | YES  |     | NULL    |       |
| test   | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

3.5.3、删除字段

alter table tb_name DROP 属性名

mysql> alter table t_dept drop test3;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| test2  | char(1)     | YES  |     | NULL    |       |
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| test   | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3.5.4、修改字段

3.5.4.1、修改字段类型

alter table tb_name MODIFY 属性名 数据类型

mysql> alter table t_dept modify test int;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| test2  | char(1)     | YES  |     | NULL    |       |
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| test   | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3.5.4.2、修改字段名称

alter table tb_name CHANGE 旧属性名 新属性名 旧数据类型

mysql> alter table t_dept change test demo int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| test2  | char(1)     | YES  |     | NULL    |       |
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| demo   | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

其实 修改字段名称与同时修改字段名称和属性类型的SQL语句是一样的

3.5.4.3、同时修改字段名称和属性

alter table tb_name CHANGE 旧属性名 新属性名 新数据类型

mysql> alter table t_dept change demo demo2 date;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| test2  | char(1)     | YES  |     | NULL    |       |
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| demo2  | date        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3.5.4.4、修改字段顺序

alter table tb_name MODIFY 属性名1 数据类型 <FIRST|AFTER 属性名2>

FIRST	表示将属性名1调整到表最前面
ALTER 属性名2	表示将属性名1调整到属性名2后面

-- 把 test2字段 移动到 demo2字段后面
mysql> alter table t_dept modify test2 char after demo2;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
| demo2  | date        | YES  |     | NULL    |       |
| test2  | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3.6、操作表的约束

3.6.1、完整性约束

所谓完整性，指的是数据的准确性和一致性。

完整性约束关键字	含义
not null	约束字段不能为空（非空）
default	设置字段的默认值
unique key （uk）	约束字段值唯一
primary key （pk）	约束字段为主键，可以作为该表记录的唯一标识
auto_increment	约束字段自增长
foreign key （fk）	约束字段为表的外键

3.6.2、设置非空约束（not null，nk）

create table t_dept(
	deptno int not null,
	dname varchar(20),
    loc varchar(40)
);

3.6.3、设置默认约束（default）

create table t_dept(
	deptno int not null,
	dname varchar(20) default ‘小黑子’,
    loc varchar(40)
);

3.6.4、设置唯一约束（unique，uk）

create table t_dept(
	deptno int not null,
	dname varchar(20) unique,
    loc varchar(40)
);

3.6.5、设置主键约束（primary key，pk）

主键约束相当于 唯一约束+非空约束

3.6.5.1、单字段主键

create table t_dept(
	deptno int primary key,
	dname varchar(20),
    loc varchar(40)
);

若想要给主键约束设置约束名，则为以下方式

create table t_dept(
	deptno int ,
	dname varchar(20),
    loc varchar(40),
    constraint pk_dname primary key(dname)
);

3.6.5.2、多字段主键（联合主键）

create table t_dept(
	deptno int ,
	dname varchar(20),
    loc varchar(40),
    constraint pk_dname_deptno primary key(deptno,dname)
);

其中，constraint pk_dname_deptno 可以省略，此时表的主键采用默认名称

3.6.6、设置字段值自增长（autu_increment）

create table t_dept(
	deptno int primary key auto_increment,
	dname varchar(20),
    loc varchar(40)
);

3.6.7、设置外键约束（foreign key，fk）

create table t_dept(
	deptno int primary key,
	dname varchar(20),
    loc varchar(40)
);

create table t_employee(
	empno int primary key,
    ename varchar(20),
    job  varchar(40),
    mgr int,
    hiredate date,
    sal double(10,2),
    comm double(10,2),
    deptno int,
    constraint fk_deptno foreign key(deptno) references t_dept(deptno)
);

4、索引

4.1、索引概述

索引类似于书的目录，主要是为了提高从表中检索数据的速度。

索引的存储类型可分为B树型索引（Btree）和哈希索引（Hash）

InnoDB和MyISAM存储引擎支持btree类型索引，MEMORY存储引擎支持Hash类型索引，默认为前者索引。

MySQL支持的6种索引类型

• 普通索引
• 唯一索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

索引的利弊：

​ 创建索引可以提高查询速度，但过多的索引则会占据许多的磁盘空间。因此创建索引前需要权衡利弊。

常见情况下适合创建索引的情况：

​ 经常被查询的字段，即在where子句中出现的字段

​ 在分组的字段，即在group by子句中出现的字段

​ 存在依赖的子表和副表之间的查询，即主键或外键字段

​ 设置唯一完整性约束的字段

不太适合创建索引的情况：

​ 在查询中很少使用的字段

​ 拥有很多重复值的字段

4.2、创建、查看索引

4.2.1、普通索引 index|key

所谓普通索引，就是在创建索引时，不添加任何限制条件（唯一、非空等约束）。该类型索引可以创建在任何字段

4.2.1.1、创建表时创建普通索引

create table tb_name(
	属性名 数据类型,
	.....
	index|key [索引名] (属性名1 [(长度)] [ASC|DESC] )
);

参数

index|key 用于指定索引字段

属性名1 指定索引关联的字段

长度 指定索引的长度

ASC|DESC 升序|降序排序

create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	index index_deptno (deptno)
);

使用查询建表语句 查看索引是否成功

show create table t_dept;

执行下面语句查看索引是否被使用

explain select * from t_dept where deptno=1;

4.2.1.2、在已存在的表上创建普通索引

create index 索引名 on 表名 (属性名1 [(长度)] [ASC|DESC] )

create index index_deptno on t_dept (deptno);

使用查询建表语句 查看索引是否成功

show create table t_dept;

4.2.1.3、alter table 创建普通索引

alter table tb_name add index|key  索引名 (属性名1 [(长度)] [ASC|DESC] )

alter table t_dept add index index_deptno(deptno)

4.2.2、唯一索引 unique index|key

所谓唯一索引，就是创建唯一索引值时，限定索引的值必须是唯一的。该类型的索引可以更快速的查询某条记录。

创建索引的方式可分为自动索引和手动索引。

自动索引：是指在设置完整性约束时，该表会被系统自动创建索引。

手动索引：是指手动在表上创建索引

在设置表中的某个字段设置主键或为唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

（说人话就是字段为主键或被唯一约束系统就会自动创建唯一索引）

4.2.2.1、创建表时创建唯一索引

create table tb_name(
	属性名 数据类型,
	.....
	unique index|key [索引名] (属性名1 [(长度)] [ASC|DESC] )
);

上述语句相比创建普通普通索引，多了关键字unique，其中unique index 或 unique key 表示创建唯一索引

create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	unique index index_deptno (deptno)
);

使用查询建表语句 查看索引是否成功

show create table t_dept;

执行下面语句查看索引是否被使用

explain select * from t_dept where deptno=1;

4.2.2.2、在已存在的表上创建唯一索引

create unique index 索引名 on 表名 (属性名1 [(长度)] [ASC|DESC] )

create index index_deptno on t_dept (deptno);

4.2.2.3、alter table 创建唯一索引

alter table tb_name add unique index|key  索引名 (属性名1 [(长度)] [ASC|DESC] )

alter table t_dept add unique index index_deptno(deptno)

4.2.3、全文索引 fulltext index|key

全文索引主要关联的数据类型为 char、varchar、text的字段，以便快速查询数据量较大的字符串类型的字段。

默认情况下，全文索引的搜索执行方式不区分大小写，若关联的是二进制类型数据，则区分大小写。

MySQL从3.23.23版本开始支持全文索引，只能在MyISAM存储引擎的表上使用全文索引

4.2.3.1、创建表时创建全文索引

create table tb_name(
	属性名 数据类型,
	.....
	fulltext index|key [索引名] (属性名1 [(长度)] [ASC|DESC] )
);

上述语句相比创建普通索引，多了关键字fulltext，其中fulltext index 或fulltext key 表示创建全文索引

create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	fulltext index index_loc (loc)
)ENGINES=MyISAM;

使用查询建表语句 查看索引是否成功

show create table t_dept;

执行下面语句查看索引是否被使用

explain select * from t_dept where dname='cjgong';

4.2.3.2、在已存在的表上创建全文索引

create fulltext index 索引名 on 表名 (属性名1 [(长度)] [ASC|DESC] )

create fulltext index_loc on t_dept (loc);

4.2.3.3、alter table 创建全文索引

alter table tb_name add fulltext index|key  索引名 (属性名1 [(长度)] [ASC|DESC] )

alter table t_dept add fulltext index index_loc(loc)

4.2.4、多列索引

所谓多列索引，就是创建索引时关联多个字段。

只有查询条件使用所关联的字段的第一个字段时，多列索引才会生效。

4.2.4.1、创建表时创建多列索引

create table tb_name(
	属性名 数据类型,
	.....
	index|key [索引名] (属性名1 [(长度)] [ASC|DESC],
                    	属性名1 [(长度)] [ASC|DESC]，
                    	......)
);

上述语句创建索引时，所关联的字段至少大于一个字段

create table t_dept(
	deptno int,
	dname varchar(20),
	loc varchar(40),
	key index_dname_loc (dname,loc)
);

使用查询建表语句 查看索引是否成功

show create table t_dept;

执行下面语句查看索引是否被使用

explain select * from t_dept where dname='cjgong';

4.2.4.2、在已存在的表上创建多列索引

create index 索引名 on 表名 (属性名1 [(长度)] [ASC|DESC],
                       		属性名1 [(长度)] [ASC|DESC],
                       		......)

create fulltext index_dname_loc on t_dept (dname,loc);

4.2.4.3、alter table 创建全文索引

alter table tb_name add index|key  索引名 (属性名1 [(长度)] [ASC|DESC],
                                       	属性名1 [(长度)] [ASC|DESC],
                                       	......)

alter table t_dept add index index_dname_loc(dname,loc)

4.3、删除索引

drop index 索引名 on tb_name;

drop index index_deptno on t_dept;

5.视图

5.1、视图概述

视图就是为了提高复杂SQL的复用性和表操作的安全性。

视图本质上就是一张虚拟表，其内容与真实表相似，视图的行和列数据来自定义视图的查询所引用基本表，并且在引用视图时动态生成。

视图特点：

• ​ 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• ​ 视图是由基本表（实表）产生的表（虚表）
• ​ 视图的建立和删除不影响基本表
• ​ 对视图内容的更新（增删改）直接影响基本表
• ​ 当视图来自多个基本表时，不允许添加和删除数据

5.2、创建视图

5.2.1创建视图

创建视图语法如下：

crete view view_name as 查询语句;

create view view_selectproduct
    as select id,name from t_product;

使用视图（查询视图与查询表格很类似）

select * from view_selectproduct;

5.2.2、创建各种视图

视图的功能其实就是将各种查询语句封装起来。

常量视图

封装实现查询常量语句的视图，即所谓常量视图

create view view_test1
	as select 3.1415926;

聚合函数视图

封装使用聚合函数（sum、min、max、count等）查询语句的视图

create view view_test2
    as
    select count(name) from t_student;

使用视图

select * from view_test2;

排序视图

封装实现排序功能（order by）查询语句的视图

create view view_test3
    as
    select name from t_student order by id desc;

使用视图

select * from view_test2;

内连接视图

封装了实现表内连接查询语句的视图

create view view_test4
    as
    select s.name from t_student as s,t_group as g where s.group_id = g.id and g.id=2;

外连接视图

封装了实现表外连接（left join 和right join）查询语句的视图

create view view_test5
    as
    select s.name from t_student as s left join
        t_group as g on s.group_id=g.id
    where g.id=2;

子查询视图

封装了实现子查询语句的视图

create view view_test6
    as
    select s.name from t_student as s
    where s.group_id in
          (select id from t_group);

联合查询视图

封装了记录联合查询（union 和 union all）语句的视图

create view view_test7
    as
    select id,name from t_student
    union all
    select id,name from t_group;

5.3、查看视图

5.3.1、show tables 查询视图名

show tables;

该语句的作用是查询数据库下的所有表名和视图名

5.3.2、show table status 查看视图详情

该语句的作用是显示表、视图的详细信息。

语法如下：

show table status [from db_name] [like 'pattern']

其中

​ db_name 用来设置数据库

show table status from view\G

截图输出一段如下：

*************************** 4. row ***************************
Name: view_selectproduct
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW

各个字段含义如下：

字段名	含义
Name	表或视图名
Engine	表的存储引擎
Version	表的.frm文件版本号
Row_format	表的行存储格式
Rows	表中行的数
Avg_row_length	表中行平均长度
Data_length	表数据文件的长度
Max_data_length	表数据文件的最大长度
Index_length	表索引的长度
Data_free	表被整序后，但是未使用的字节的数目
Auto_increment	表中的下一个Auto_increment值
Create_time	表的创建时间
Update_time	表的最后一次更新时间
Check_time	表的最后一次检查时间
Collation	表的字符集
Checksum	表的活性校验
Create_options	表的额外选项
Comment	表的注解

5.3.3、show create view 查看视图定义信息

语法格式如下：

show create view viewname;

show create view view_selectproduct;

5.3.4、describe|desc 查看视图设计信息

describe|desc viewname;

describe view_selectproduct;

5.3.5、通过系统查看视图信息

当MySQL安装成功后，系统会自动创建系统数据库information_schema。该数据库中包含一个存放视图信息的表格views，可通过查看表格wiews查看所有视图的信息。

use information_schema;

select * from views where table_name='view_selectproduct' \G

5.4、修改视图

原来创建视图的语句如下：

create view view_selectproduct
    as select id,name from t_product;

5.4.1、通过create or replace view修改视图

create or replace view view_selectproduct
    as select name from t_product;

5.4.2、通过alter 语句修改视图

语法如下：

alter view viewname
	as 查询语句

alter view view_selectproduct
    as select name from t_product;

5.5、删除视图

使用drop view 语句可以一次删除一个或多个视图

格式如下：

drop view view_name[,view_name,......]

drop view view_selectproduct;

5.6、通过视图操作基本表

5.6.1、检索（查询）数据

通过视图查询数据，与通过表查询数据是完全相同的。

通过视图查询比表更安全、更简单、实用。

如下：

select * from view.view_selectproduct;

5.6.2、利用视图操作基本表

前面描述过，通过视图可以查询数据，通过视图也可以对表进行更新（增删改）。这是因为视图是虚表，所以对视图的更新，其实就是对视图背后的基本表更新。

注意：

• ​ 对视图的增删改操作会直接影响基本表
• ​ 当视图来自多个表时，不允许对其添加和删除

下面演示以下增删改操作

5.6.2.1、添加数据

先创建一个视图

create view view_produce
    as select * from t_product;

通过视图在基本表中添加数据

insert into view_produce values(null,'pear2',5.5);

5.6.2.2、修改数据

通过视图在基本表中修改数据

update view_produce
    set price=3.5
    where name='banana';

5.6.2.3、删除数据

通过视图在基本表中删除数据

delete from view_produce where name='pear2';

6、触发器

按照触发器触发时执行得语句数码，触发器可以分为”一个执行语句得触发器“和“多个执行语句得触发器”

6.1、创建触发器

6.1.1、创建一条执行语句的触发器

语法如下：

create trigger trigger_name
	before|after trigger_event
		on table_name from each row trigger_stmt;

其中

trigger_name 表示触发器名称

before|after 表示触发器执行的时间

trigger_event 表示触发事件（即触发器执行的条件，包含delete、update、insert语句）

table_name 表示触发器执行时操作的表名

from each row 表示行级触发器

trigger_stmt 表示触发器执行时执行的语句

-- 创建触发器
create trigger tri_diarytime
    before insert
    on t_dept for each row
    insert into t_diary values (null,'t_dept',now());

测试，在表t_dept中插入数据

insert into t_dept values (1,'cjgong','Beijing');

查询t_diary表中是有数据，验证触发器是否创建成功

select * from t_diary;

6.1.2、创建多条执行语句的触发器

语法如下：

create trigger trigger_name
	before|after trigger_event
	on table_name for each row
	begin
		trigger_stmt
	end
	

关键字delimiter 语句，例如 delimiter $$，可以将实现将结束符号换成$$

例子如下：

delimiter $$
create trigger tri_diarytime2
    before insert
    on t_dept for each row
    begin
        insert into t_diary values (null,'t_dpet',now());
        insert into t_diary values (null,'t_dpet',now());
    end $$
$$ delimiter ;

6.2、查看触发器

6.2.1、通过show triggers查看触发器

show trigger \G

6.2.2、通过查看系统表triggers查看触发器

在系统数据库information_schema中存在一个存储所有触发器的系统表triggers

use information_schema;
select *
from triggers;

6.3、删除触发器

drop trigger trigger_name;

7、数据的增、删、改

7.1、插入数据记录

插入数据的语句如下所示：

insert into tb_name[(field1,field2,....)]
	values(value1,value2,...)[,(value1,value2,...)...] -- 可以同时插入一条数据 或 多条数据

mysql> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into t_dept values(100,'zhangsan','beijing');
Query OK, 1 row affected (0.02 sec)

7.2、插入子查询语句结果

语法格式如下：

insert into tb_name[(field1,field2,...)]
	select <*|[field1,field2,...]> from tb_name2 where ...;

mysql> insert into t_dept select * from t_dept;  -- 插入子查询语句的结果
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_dept;
+--------+----------+---------+
| deptno | dname    | loc     |
+--------+----------+---------+
|      1 | cjgong   | Beijing |
|    100 | zhangsan | beijing |
|      1 | cjgong   | Beijing |
|    100 | zhangsan | beijing |
+--------+----------+---------+
4 rows in set (0.00 sec)

7.3、更新数据记录

语法如下：

update tb_name 
	set field1=value1,
		field2=value2,
		...
		[where 条件]

注意：

​ 如果不加条件，所有记录的相应字段都会被改变

mysql> update t_dept set dname='lisi' where deptno=100;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t_dept;
+--------+--------+---------+
| deptno | dname  | loc     |
+--------+--------+---------+
|      1 | cjgong | Beijing |
|    100 | lisi   | beijing |
|      1 | cjgong | Beijing |
|    100 | lisi   | beijing |
+--------+--------+---------+
4 rows in set (0.00 sec)

7.4、删除数据记录

语法如下：

delete from tb_name
	where 条件

Query OK, 2 rows affected (0.01 sec)

mysql> select * from t_dept;
+--------+--------+---------+
| deptno | dname  | loc     |
+--------+--------+---------+
|      1 | cjgong | Beijing |
|      1 | cjgong | Beijing |
+--------+--------+---------+
2 rows in set (0.00 sec)

8 单表查询

8.1 简单数据查询 SELETE

8.1.1 查询所有字段

语法如下：

SELECT field1,field2,....  -- field1,field2,.... 表示需要查询的字段
	FROM tb_name;

例如：

SELECT  empno,ename,job,mgr,job,mgr,hiredate,sal,comm,deptno from t_employee;

说明：

​ empno,ename,job,mgr,job,mgr,hiredate,sal,comm,deptno这些字段可以交换位置

​ empno,ename,job,mgr,job,mgr,hiredate,sal,comm,deptno这些字段用 * 代替

​ 但是用 * 代替了字段以后，查询到的记录只能表的字段排序来显示，不够灵活

8.1.2 查询指定字段

语法与查询所有字段相似

例如

select empno, ename, sal from t_employee;

8.1.3 避免查询重复数据 DISTINCT

例如

select distinct job from t_employee;

8.1.4 使用四则运算查询数据

MySQL支持的关系运算符有：

运算符	描述
+	加法
-	减法
*	乘法
/ (DIV)	除法
% (MOD)	求余

例如：

select ename, sal * 12 from t_employee;

如需改变查询结果的字段，可以采用如下方式：

select ename, sal * 12 as yearsalary from t_employee;
-- 或
select ename, sal * 12  yearsalary from t_employee;

其中 ，

​ as 可以改变字段的显示结果，as 可以省略

8.1.5 设置显示格式数据查询

可以采用MySQL提供的函数concat()来连接字符串实现

例如：

select concat(ename,'雇员的年薪为：',sal*12) from t_employee;
-- 或
select concat(ename, '雇员的年薪为：', sal * 12) as yearsalary
from t_employee;

8.2 条件数据查询 WHERE

语法如下：

select field1,field2,...
	from tb_name
	where condition（条件）

condition条件包括以下几种：

• ​ 带有关系运算符和逻辑运算符的条件数据查询
• ​ 带有 between and 关键字的条件数据查询
• ​ 带 is null 关键字的条件数据查询
• ​ 带 in 关键字的条件数据查询
• ​ 带 like 关键字的条件数据查询

比较运算符： > < = !=（<>） >= <=

逻辑运算符： and(&&) 与 or(||) 或 xor 异或 not(!) 非

查询sql例如：

-- 单条件查询
select ename from t_employee where job='clerk';

-- 多条件查询
select ename from t_employee where job='clerk' && sal > 800;

-- 带关键字 between and 的范围查询
select ename from t_employee where sal between 1000 and 2000;
-- 不符合范围的数据查询
select ename from t_employee where sal not between 1000 and 2000;

-- 带is null 关键字的空值查询
select ename from t_employee where comm is null;
-- 不是空值的数据查询
select ename from t_employee where comm is not null;

-- 带in 关键字的集合查询
select ename from t_employee where empno = 7902
                                or empno = 7788
                                or empno = 7698
                                or empno = 7566;
select ename from t_employee where empno in(7902,7788,7698,7566);
-- 不在集合中的数据查询
select ename from t_employee where empno not in(7902,7788,7698,7566);

-- 带like 关键字的模糊查询   mysql中默认不区分大小写
select ename from t_employee where ename like 'A%';   # %代表通配符，可以匹配多个字符
select ename from t_employee where ename like '_A%';   # _代表通配符，只能匹配一个字符

select ename from t_employee where ename not like 'A%';
select ename from t_employee where !(ename like 'A%');

注意：

使用in关键字时需要注意，如果是 in ，集合中包含null不会影响查询结果，如果是not in，集合中包含null则查询不到结果，如下sql语句：

select ename from t_employee where empno not in (7902,null);
-- 该语句查询不到任何数据

8.3 排序数据查询 ORDER BY

排序查询的关键字是 order by

语法如下：

select field1,field2,...
	from tb_name
	where condition（条件）
		order by fieldm1 [ASC|DESC] [,fieldm2 [ASC|DESC]...]

上述语句中，参数 fieldm1 表示按照该字段进行排序，默认升序AS

参数：

• ASC 升序
• DESC 降序

注意：

​ 在roder by 后面可以放多个字段，表示按照多个字段进行排序

​ 若在排序字段中，如果有为null的，则该组为最小

8.3.1 按照单字段排序

语句如下：

select * from t_employee order by sal ASC;

8.3.1 按照多个字段排序

select * from t_employee order by sal ASC,hiredate;

8.4 限制/分页查询 LIMIT

语法如下：

select field1,field2,...
	from tb_name
	where condition（条件）
		limit [offset_start],row_cont

参数:

• offset_start 表示起始偏移量 该参数为可选参数

• row_count 表示显示的行数（记录数）

8.4.1 不指定初始位置

语法形式

LIMIT row_cont  -- 若 row_cont的值 > 查询的总记录数，则显示所有查询结果

SELECT * FROM t_employee WHERE comm IS null LIMIT 2;

8.4.3 指定起始位置

select * from t_employee where comm is null 
    order by hiredate 
        limit 0,5;

8.5 聚合函数与分组数据查询GROUP BY

8.5.1 聚合函数

聚合函数	说明
count()	用于统计记录条数 count(*)：统计包含null值，count(field)：统计时忽略null值
avg()	用于统计平均值 avg(field)：对指定字段计算平均值，忽略null
sum()	用户统计求和 sum(field)：对指定字段求和，忽略null
max()	用户求最大值 max(field)：求指定字段中最大值，忽略null
min()	用户求最小值 max(field)：求指定字段中最小值，忽略null

注意：

​ 空值： null

​ null 不等同于 0 ，‘’，‘null’

​ 聚合函数中，若所操纵的表中无数据，count()函数会返回0，其他函数会返回null（空值参与运算，结果也一定为空）

​

8.5.2 分组查询

语法如下：

select field1,field2,...
	from tb_name
	group by field

select * from t_employee
    group by deptno;

8.5.2 多字段分组查询

select * from t_employee
    group by deptno,hiredate;

8.5.3 HAVING 子句限制 分组查询

语法如下：

select field1,field2,...
	from tb_name
	group by field
		having 条件;

select deptno, AVG(sal) averagr, group_concat(ename) ename, COUNT(ename) number
    from t_employee
    group by deptno
    having AVG(sal) > 2000;

其中

​ 函数 group_concat(ename) 表示将分组后的同一组的ename字段拼接起来

9 多表查询

9.1 关系操作符

• ​ 并（union）
• ​ 笛卡尔积（cartesian product）
• ​ 内连接（inner join）
• ​ 外连接（outer join）
• ​ 交叉连接（cross join）

9.2 内连接 INNER JOIN

9.2.1 自连接

自连接可以通过sql语句“ inner join … on”来实现，也可以不用，如下

select e.ename employeename, e.job, l.ename loadername
    from t_employee e inner join t_employee l
        on e.mgr = l.empno;
-- 或
select e.ename employeename, e.job, l.ename loadername
    from t_employee e,t_employee l
        where e.mgr = l.empno;

还要等值连接、不等连接都大同小异，不再赘述

9.3 外连接

外连接查询分为以下三种：

• ​ 左外连接
• ​ 右外连接
• ​ 全外连接（MySQL不支持）

语法如下：

select field1,field2,...
from tb_name1 <left|right|full> [outer] join tb_name2
	on 连接条件

9.3.1、左外连接

左外连接指的是新关系中执行匹配条件时，以left [outer] join 关键字左边的表为参考表

select t1.ename employeename, t1.job, t2.ename loadername from t_employee t1 
	left join t_employee t2
    	on t1.mgr = t2.empno;

9.3.2、右外连接

右外连接指的是新关系中执行匹配条件时，以right [outer] join 关键字右边的表为参考表

select t2.ename employeename, t2.job, t1.ename loadername from t_employee t1 right join t_employee t2
    on t2.mgr = t1.empno;

从上述语句可以看出，左外连接可以改写为右外连接，反之毅然

9.4、合并查询

通过关键字 union 可以实现将多个sql语句的查询结果合并在一起组成的新的关系。

语法形如：

select field1,field2,...
from tb_name1
	union|union all
select field1,field2,...
from tb_name2
	.....

其中

​ 关键字 union 表示查询到的结果会去重复值展示

​ 关键字 union all 表示查询到的结果不会去重复值展示

9.4.1、使用union 合并

select * from t_cstudent
    union 
select * from t_mstudent;

9.4.2、使用union all 合并

select * from t_cstudent
    union all
select * from t_mstudent;

9.5、子查询

9.5.1、返回结果单行单列子查询

# 返回结果单行单列
select sal from t_employee where ename='SMITH';

# 子查询语句
select * from t_employee
    where sal > (select sal from t_employee where ename='SMITH');

9.5.2、返回结果单行多列子查询

# 返回结果单行多列
select sal,job from t_employee where ename='SMITH';

# 子查询语句
select * from t_employee 
    where (sal,job) = (select sal,job from t_employee where ename='SMITH');

9.5.3、返回结果多行单列子查询

当子查询的结果返回结果为多行单列时，一般在主查询语句会包含in、any、all、exists等关键字

# 返回结果多行单列
select deptno from t_dept;

select * from t_employee
    where deptno not in (select deptno from t_dept);

9.5.4、返回结果为多行多列子查询

# 返回结果多行多列
select deptno dno, count(empno) number, avg(sal) average from t_employee
    group by deptno desc ;

select  d.deptno, d.dname,d.loc,number, average  from t_dept d
    inner join (select deptno dno, count(empno) number, avg(sal) average from t_employee
                group by deptno desc) employee
    on d.deptno = employee.dno;

select field1,field2,...
	from tb_name
	group by field