mysql

Source

学习过程参考视频教程：java1234网站《一头扎进mysql》
源码及视频网盘：链接

0前言

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

  • 1.数据以表格的形式出现
  • 2.每行为各种记录名称
  • 3.每列为记录名称所对应的数据域
  • 4.许多的行和列组成一张表单
  • 5.若干的表单组成database

1 mysql的数据类型简介

1.1 整数类型

整数类型 字节数 无符号(unsigned)范围 有符号(signed)范围
TINYINT 1 0~255 -128~127
SMALLINT 2 0~65535 -32768~32767
MEDIUMINT 3 0~16777215 -8388608~8388607
INT 4 0~4294967295 -2147483648~2147483647
INTEGER 4 0~4294967295 -2147483648~2147483647
BIGINT 8 0~18446744073709551615 -9223372036854775808~9223372036854775807

INTEGER和INT是相同的，一般定义主键使用INT即可。默认是有符号情况，无符号要特别说明。

1.2 浮点数和定点数类型

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

数据类型精度有无符号的范围

  • float(m,d)单精度浮点型，(4字节)，m总位数，d小数位10^38 级别
  • double(m,d)双精度浮点型，(8字节)，m总位数，d小数位10^308 级别
  • decimal(m,d)m<65 是总个数，d<30 且 d<m 是小数位$1

float32位中，有1位符号位，8位指数位，23位尾数位，实际的范围是-2128—2127，约为-3.4E38—3.4E38

double64位中，1位符号位，11位指数位，52位尾数位，范围约是-1.7E308—1.7E308

float和double精度问题：参考博客

1.3 日期与时间类型

在这里插入图片描述

常用 DATA，TIME，DATATIME

1.4 字符串类型

数据类型大小用途

  • CHAR 0-255 字节定长字符串(实际分配的空间是固定的)
  • VARCHAR 0-65535 字节可变长字符串(实际分配空间根据字符存决定)
  • TEXT 0-65535 字节文本数据(TINYTEXT\LONGTEXT\MEDIUMTEXT)
  • ENUM枚举类型（只能取一个）
  • SET集合类型（可以取多个）

1.5 二进制类型

在这里插入图片描述

存一些图片视频，一般存在web目录下读取快，存数据库慢。

2 数据库（database）的基本操作

2.1 数据库组件

  • Mysql Shell：自带的一个高级的mysql命令行工具。
  • MySQL Workbench：为MySQL设计的ER/数据库建模工具，实现可视化管理数据库。
  • Mysql Command Line Client：MySQL的DOS界面，客户端工具。
  • Mysql Command Line Client-Unicode同上。
  • Mysqladmin：运维和管理工具

退出Mysql：quit

2.2 显示数据库

显示所有数据库：show databases;

2.3 创建数据库

创建数据库：create database Name; Name最好有一定规范，如db_book1

2.4 删除数据库

删除数据库:drop database Name;

3 数据表（Tables）的基本操作

表是数据库存储数据的基本单位，一个表包含若干字段或记录。

3.1 创建表

1
2
3
4
5
6
CREATE TABLE table_name (
    属性名 数据类型 [完整性约束条件],
    属性名 数据类型 [完整性约束条件],
    .....
    属性名 数据类型 [完整性约束条件],
);

表的常见约束条件：

在这里插入图片描述

创建图书类别表：

1
2
3
4
5
6
use db_book;
create table t_bookType(
    id int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);

创建图书类型表：

并将图书的类别ID，做外键关联使用。

1
2
3
4
5
6
7
8
create table t_book(
    id int primary key auto_increment,
    bookname varchar(20),
    author varchar(10),
    price decimal(6,2),
    bookTypeId int,
    constraint `fk` foreign key(`bookTypeId`) references `t_booktype`(`id`)
);

若之前表已经存在了，则：

1
2
3
4
5
6
create table Connect(
    con DOUBLE,
    nectid INT
);

alter table Connect add constraint `fkid` foreign key(`nectid`) references `t_booktype`(`id`)

注意这里的符号是数字1左侧英文输入状态下的 ` 不是单引号 ’

3.2 查看表结构

查看基本表结构：DESCRIBE/DESC 表名;

详细结构：SHOW create table 表名;

显示本数据库内的所有表：SHOW tables

1
2
3
desc t_booktype;
show create table t_booktype;
show tables；

3.3 修改表结构

在这里插入图片描述

修改表名： alter table t_book rename t_book2;

修改t_book表的bookName字段名为bookName2，数据类型为 varchar(20): alter table t_book change bookName bookName2 varchar(20);

增加字段名: alter table t_book add authorage varchar(10);

带约束增加

1
2
3
4
#加到最前面一列
alter table t_book add testField varchar(10) first;
#加到指定属性后面
alter table t_book add testField varchar(10) after author;

删除字段名: alter table t_book drop testField;

3.4 删除表

drop table 表名;

有依赖关系的时候先删掉子表再删主表。

4 表内容的相关操作

先创建一个数据表：

1
2
3
4
5
6
7
create table t_student(
    id int primary key auto_increment,
    stuName varchar(60),
    age int,
    sex varchar(30),
    gradeName varchar(60)
);

插入数据： 注意里面是使用的单引号

1
2
3
4
5
6
7
8
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (1,'张一',18,'男','大一');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (2,'张二',19,'男','大二');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (3,'张三',20,'男','大三');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (4,'张四',21,'男','大四');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (5,'张五',22,'男','大一');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (6,'张六',23,'男','大二');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (7,'张七',18,'男','大三');
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (8,'张八',20,'男','大四');

或者后面多组：

1
insert into db_book.t_student (id,stuName,age,gender,gradeName) values (9,'张九',20,'女','大二'),(10,'张十',20,'女','大四');

给表取别名：表名 表别名

select * from t_book where id=1; 等价 select * from t_book tb where tb.id=1;

字段取别名：属性 [AS] 属性别名

select tb.bookName [AS] tbNa from t_book tb where tb.id=1;

4.1 查询表内字段

4.1.1 查询所有字段：

1
2
3
4
#select 字段1,字段2,字段3... from 表名;
select id,stuName,age,gender,gradeName from t_student; #顺序可调
#select * from 表名;
select * from t_student;

4.1.2 查询指定字段：

1
2
select 指定字段1,指定字段3... from 表名;
select stuName,gender from t_student;

4.1.3 where条件查询

1
2
3
#select 字段1,字段2... from 表名 where 条件表达式;
select * from t_student where id=1;
select * from t_student where age>19;

指定条件删除字段：

1
delete from t_student where id=4;

4.1.4 带in关键字查询

1
2
3
#select 字段1,字段2,字段3... from 表名 where 字段 [NOT] IN (元素1.元素2…)；
select * from t_student where age in (18,19); #只能取这两个，不是范围
select * from t_student where age not in (18,19);

4.1.5 带between and的范围查询

1
2
3
#select 字段1,字段2,字段3... from 表名 where 字段 [NOT] BETWEEN 取值a AND 取值b；
select * from t_student where age between 18 and 20； #18 19 20 都可以取，是范围
select * from t_student where age not between 18 and 20；

4.1.6 带like模糊查询

1
2
3
4
5
6
7
#select 字段1,字段2,字段3... from 表名 where 字段 [NOT] LIKE ‘字符串’;
#"%"代表任意字符 '_'代表单个字符，前后都可

select * from t_student where stuName like '张三';
select * from t_student where stuName like '张三%'; #带'张三'开头的
select * from t_student where stuName like '张三_'; #_只能占一个位。多个就写多个_ _
select * from t_student where stuName like '%张三%';

4.1.7 空值查询

1
2
3
4
#select 字段1,字段2,字段3... from 表名 where 字段 IS [NOT] NULL;

select * from t_student where age is NULL;
select * from t_student where age is NOT NULL;

4.1.8 带and的多条件查询

1
2
3
4
#select 字段1,字段2,字段3... from 表名 where 条件表达式1 AND 条件表达式2 AND 条件表达式3 AND ...
#注意末尾是没有分号的

select * from t_student where age=20 and gradeName='大四'

4.1.9 带or的多条件查询

1
2
3
4
#select 字段1,字段2,字段3... from 表名 where 条件表达式1 OR 条件表达式2 OR 条件表达式3 OR ...
#或，语句末尾也是无分号；

select * from t_student where age=20 or gradeName='大四'

4.1.10 distinct去重复查询

1
2
3
#select distinct 字段名 from 表;

select distinct age from t_student;

4.1.11 对查询结果排序

1
2
3
4
#select 字段1,字段2,... from 表名 order by 属性名 [ASC|DESC]
#默认升序

select age from t_student order by age asc;

4.1.12 group by 分组查询

在这里插入图片描述

一般不单独使用，常和聚合函数一起使用，一定注意使用的时候各个符号。

1
2
3
4
5
6
7
8
9
10
11
#按age分组，看每个(岁数)组有哪些stuName,GROUP_CONCAT把所有stuName拼接
select age,GROUP_CONCAT(stuName) from t_student group by age;

#按age分组，看每个(岁数)组有多少个stuName
select age,COUNT(age) from t_student group by age;

#按age分组，看每个(岁数)组有多少个stuName，只输出统计总数大于1的
select age,COUNT(age) from t_student group by age having count(age)>1;

#按age分组，看每个(岁数)组有多少个stuName，多加一行计算总和，若是文本为所有元素拼接
select age,COUNT(age) from t_student group by age with rollup;

4.1.13 limit 分页查询

1
2
3
4
#select 字段1,字段2... from 表名 limit 初始位置,记录数,

select * from t_student LIMIT 0,5; #从第0条开始查5条
select * from t_student LIMIT 5,5; #从第5条开始查5条

4.2 使用聚合函数查询（统计）

先创建一个相关数据表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table t_garde(
    int primary key auto_increment,
    stuName varchar(60),
    course varchar(50),
    score int
);

insert into t_garde (id,stuName,course,score) values
    (1,'张一一','语文',88),
    (2,'张一一','数学',89),
    (3,'张一一','英语',90),
    (4,'张三三','语文',92),
    (5,'张三三','数学',93),
    (6,'张三三','英语',94),
    (7,'张五五','语文',76),
    (8,'张五五','数学',78),
    (9,'张五五','英语',80);

4.2.1 COUNT() 函数

在这里插入图片描述

1
2
3
select COUNT(*) from t_grade; #统计t_grade表内总共有多少条目
select COUNT(*) as total from t_grade; #统计t_grade表内总共有多少条目,并把列名 count(*) 改为 total
select stuName,COUNT(*) from t_grade group by stuName; #按stuName分组，看每个(stuName)组有多少条

4.2.2 SUM() 函数

在这里插入图片描述

1
2
select stuName,SUM(score) from t_grade where stuName='张一一'; #求某个学生的总分
select stuName,SUM(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的各score总和

4.2.3 AVG() 函数

在这里插入图片描述

1
2
select stuName,AVG(score) from t_grade where stuName='张一一'; #求某个学生的总分
select stuName,AVG(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的平均score

4.2.4 MAX() 函数

在这里插入图片描述

1
2
select stuName,course,MAX(score) from t_grade where stuName='张一一'; #求某个学生的最高score,并列出course
select stuName,MAX(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的最高score #分组前面属性只能写一个属性

4.2.5 MIN() 函数

在这里插入图片描述

1
2
select stuName,course,MIN(score) from t_grade where stuName='张一一'; #求某个学生的最低score,并列出course
select stuName,MIN(score) from t_grade group by stuName; #按stuName分组，看每个(stuName)组的最低score #分组前面属性只能写一个属性

4.3 多表连接查询

将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

4.3.1 内连接查询

可以查询两个或两个以上的表，最常用。

1
2
3
4
5
6
7
select * from t_book,t_bookType; #数量取两表笛卡尔乘积(两两组合)

select * from t_book,t_bookType where t_book.bookTypeId=t_booktype.id; #数量取两表笛卡尔乘积后，看bookTypeId与id相等的条目

select bookName,author,bookTypeName from t_book,t_bookType where t_book.bookTypeId=t_booktype.id;

select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id; #设置别名，防止多表字段名有重合

4.3.2 外连接查询

外链接可以查询某一张表的所有信息，就是把一张表不全的部分补进来，left right决定谁合并到谁之中。

1
2
select 属性列表 from 表名1 left|right join 表名2 on 表1.属性1=表2.属性2
#left连接把表1所有属性列出来加上表2匹配的条目，right连接吧表2所有属性列出来加上表1匹配的条目。空值NULL，on限制条件。

1、左连接查询

1
select * from t_book left join t_booktype on t_book.bookTypeId=t_booktype.id; #显示表t_book所有属性

2、右连接查询

1
2
select tb.bookName,tb.author,tby.bookTypeName from t_book tb right join t_bookType tby on tb.bookTypeId=tby.id;
#显示表t_bookType全部属性，推荐使用别名。

4.3.3 多条件查询

多个条件之间使用AND连接。

1
select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id AND price>70;

4.4 子查询

在表2中进行查询，查询的某属性是限定在表一的条件中的。

4.4.1 带IN关键字的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中。

1
2
select * from t_book where bookTypeID [NOT] in (select id in t_booktype);
#选择表t_book的所有属性显示，筛选bookTypeID 在表t_booktype里的

4.4.2 带比较运算条件的子查询

1
2
select * from t_book where price >= (select price from t_pricelevel where pricelevel=1);
#后面括号筛选完成的条件要是数字，不是集合

4.4.3 带Exits关键字的子查询

判断性质：假如子查询语句查询到记录则进行外层查询，子查询为空不执行外层查询。

1
2
select * from t_book where [NOT] EXITS (select * from t_booktype);
#后面括号筛选得到空，则不执行前面的查询

4.4.4 带ANY关键字的子查询

满足任一条件即可。

1
2
select * from t_book where price >= any (select price from t_pricelevel);
#后面括号筛选完成的条件是集合

4.4.5 带ALL关键字的子查询

满足所有条件。

1
2
select * from t_book where price >= all (select price from t_pricelevel);
#后面括号筛选完成的条件是集合

4.5 合并查询

使用UNION关键字将所有查询结果合并到一起，去除相同记录。UNION ALL不去除重复记录。

1
2
3
4
select id from t_book; #(1,2,3,4)
select id from t_booktype; #(1,2,3,6)
select id from t_book UNION select id from t_booktype; #(1,2,3,4,6)
select id from t_book UNION ALL select id from t_booktype; #(1,2,3,4,1,2,3,6)

4.6 插入、更新、删除数据

4.6.1 插入

所有字段插入数据：

1
2
3
#insert into 表名 values(值1,值2,值3...);
insert into t_book values(NULL,'围城',,'钱钟书',1)；
#自增的属性可以设为NULL

指定字段插入数据：

1
2
3
#insert into 表名(属性1,属性2,属性n) values(值1,值2,值3...);
insert into t_book(id,bookName,price,author,bookTypeId) values(NULL,'围城',28,'钱钟书',1)；
insert into t_book(bookName,author) values('围城','钱钟书')； #也可以取指定的属性插入，其他默认为NULL

同时插入多条记录：

1
2
#insert into 表名[(属性列表)] values (取值列表1),(取值列表2),...(取值列表n);
insert into t_book(id,bookName,price,author,bookTypeId) values (NULL,'围城2',28,'钱钟书',1),(NULL,'围城3',28,'钱钟书',1);

4.6.2 更新

表里的一些属性值发生了变化，及时更新

1
2
3
4
5
#update 表名 set 属性1 =取值1,属性2=取值2...属性n=取值n where 条件表达式;
update t_book set bookName='围城',price=40 where id=5; #逗号分隔

update t_book set bookName='钱钟书的书' where like '%围%'；
#使用模糊查询，将表中含有 围 字书名的书全部改为 钱钟书的书

4.6.3 删除

1
2
3
4
5
6
7
#delete from 表名 where [条件表达式]
#条件不是主键可能会报错，需要百度修改数据库模式。

delete from t_book where id=6; #删除一条

delete from t_student where age>=20; #符合条件多条
delete from t_student where age='2%'; #错误，非字符串不能这么匹配

5.索引

索引类似图书的目录，方便快速定位，查找指定内容，提高查询速度。

索引是数据库的一个对象，数据库中的一列或者多列组成，它不能独立存在，必须对某个表对象进行依赖。

索引保存在information_schema数据库里的STATISTICS表中。

  • 优点：提高查询数据的速度
  • 缺点：创建和维护索引的时间增加了

5.1 索引分类

查看数据库 db_book 内所有索引：

1
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'db_book';

查看当前table的索引：

1
show index from tableName;

在这里插入图片描述

5.2 创建索引

创建的关键字 INDEX

一般主键在创建时默认就是唯一性索引。

方法1：最开始创建表的时候添加索引：

在这里插入图片描述

1
2
3
4
5
6
7
8
9
10
11
12
#普通 单列 索引
create table t_u1(id int, uName varchar(20), password varchar(20), INDEX (uName));

#唯一性单列索引  注意对应位置含义
create table t_u2(id int, uName varchar(20), password varchar(20), UNIQUE INDEX (uName));
create table t_u2(id int, uName varchar(20), password varchar(20), UNIQUE INDEX index_uName(uName)); #带别名

#全文单列索引
create table t_u2(id int, uName varchar(20), password varchar(20), FULLTEXT INDEX (uName));

#多列索引，uName,password两列属性指向一个索引
create table t_u3(id int, uName varchar(20), password varchar(20), INDEX index_uName_password(uName,password));

方法2：为已经存在的表添加索引：

在这里插入图片描述

1
2
3
create INDEX index_uName ON t_u4(uName); #新增普通单列索引
create UNIQUE INDEX index_uName ON t_u4(uName); #新增唯一性单列索引
create INDEX index_uName_password ON t_u4(uName,password); #新增多列索引

方法3：使用alter方法创建索引：

在这里插入图片描述

1
2
3
alter TABLE t_u5 ADD INDEX index_uName(uName); #新增普通单列索引
alter TABLE t_u5 ADD UNIQUE INDEX index_uName(uName); #新增唯一性单列索引
alter TABLE t_u5 ADD INDEX index_uName_password(uName,password); #新增多列索引

5.3 删除索引

1
2
3
4
#DROP INDEX 索引名 ON 表名;

DROP INDEX index_userName ON t_u5;
DROP INDEX index_userName_password ON t_u5; #删除多列索引

6 数据库视图（View）

  • 视图是从一个或几个基本表（或视图）中导出的虚拟的表。
  • 视图是从一个或多个实际表中获得的，那些用于产生视图的表叫做该视图的基表。在系统的数据字典中仅存放了视图的定义，不存放对应的数据，通过视图看到的数据存放在基表中。
  • 通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。
  • 由于逻辑上的原因，有些视图可以修改对应的基表，而有些则不能（仅仅能查询）。

6.1 视图的好处

  • 提高重用性，针对重复使用的属性字段；
  • 增强安全性，不同权限用户使用部分数据创建的对应视图；
  • 表的逻辑独立性。

可参考：博客

6.2 创建视图

在这里插入图片描述

ALGORITHM表示视图选择的算法（可选参数）

  • UNDEFINED：MySQL将自动选择所要使用的算法
  • MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
  • TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句

WITH CHECK OPTION表示更新视图时要保证在该试图的权限范围之内（可选参数）

6.2.1 单表视图

1
2
3
4
5
6
7
8
CREATE VIEW v1 AS SELECT * FROM t_book; #用t_book所有字段生成视图v1
SELECT * FROM v1; #查看视图v1的所有的字段属性

CREATE VIEW v2 AS SELECT bookName,price FROM t_book; #用t_book的bookName,price字段生成视图v2
SELECT * FROM v2;

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book; #创建的视图内将bookName和price字段重命名为 b 和 p
SELECT * FROM v3;

6.2.2 多表视图

同一个数据库下不同表：

1
create view v4 as (select * from table1) union all (select * from table2); #合并查询

或：

1
2
3
create view v5 as select bookName,bookTypeName from t_book,t_booktype where t_book.bookTypeID=t_booktype.id;
CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id; #从两张取了别名的表里按指定条件创建视图
SELECT * FROM v5;

不同数据库下的不同表：

1
2
3
4
5
#在数据库1 目录下创建
create view 数据库1.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);

#在数据库2 目录下创建
create view 数据库2.v as (select * from 数据库1.table1) union all (select * from 数据库2.table2);

6.3 查看视图

通过指令或数据库图形管理工具查看。

1
2
3
4
5
6
7
DESC view1; #基本的视图内容信息：字段名、类型等

# 查看状态信息：包含创建时间，一些虚拟状态值
SHOW TABLE STATUS LIKE 'view1'; #视图是虚表，所以没有collation之类的信息
SHOW TABLE STATUS LIKE 't_book'; #t_book是表，有collation之类的信息

SHOW CREATE VIEW view1; #详细信息，包括编码，建表语句等

6.4 修改视图

方法1：

在这里插入图片描述

1
2
#视图v1有4个字段，改为只有bookName和price
CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book;

方法2：

在这里插入图片描述

1
2
#视图v1只有bookName和price，改为t_book所有的字段
ALTER VIEW v1 AS SELECT * FROM t_book;

6.5 更新视图

  • 通过视图来插入、更新、删除表中的数据。
  • 视图只是一个虚表，没有数据，操作的是识图来源的基表
  • 更新视图要在权限范围内。

6.5.1 插入视图（数据）

1
2
#视图v1有id,bookName,price,author,bookTypeID
INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1);

6.5.2 更新视图（数据）

1
UPDATE v1 SET bookName='java very good',price=200 WHERE id=5;

6.5.3 删除视图（数据）

1
DELETE FROM v1 WHERE id=5;

6.6 删除视图

删除数据库中已经存在的视图，删除视图并不会删除数据，只是删除视图定义。

1
DROP VIEW IF EXISTS view1;

7 触发器（TRIGGER）

执行某项操作（INSERT、UPDATE、DELETE）时候自动触发执行预设好的相对应操作。

如在表a添加数据时表b里的对应属性也进行一个变化。

相同的表相同的操作只能创建一个对应的触发器。

7.1 创建使用触发器

单个执行语句的触发器：

在这里插入图片描述

注意执行语句where 后面的部分old和new

  • old表示插入之前的值，old用在删除和修改
  • new表示新插入的值，new用在添加、更新和修改
1
2
3
4
5
6
#创建trigger：在insert插入新数据操作之后 更新书的数量 new表示插入的条目
CREATE TRIGGER trig_book AFTER INSERT
    ON t_book FOR EACH ROW
        UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;

INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1); #插入操作触发trigger

多条执行语句：

在这里插入图片描述

默认mysql是遇到一个分号 ； 执行一次的。

DELIMITER | 是告诉mysql解释器不要将多条程序体里面单条语句的分号 ；直接执行，是作为一个程序体的。也可以使用 $$ // 等表示

可参考：厚积_薄发博客

1
2
3
4
5
6
7
8
9
10
11
12
13
#创建触发器
DELIMITER |
CREATE TRIGGER trig_book2 AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN
        UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
        INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据'); #mysql里NOW（）表示当前时间
        DELETE FROM t_test WHERE old.bookTypeId=t_test.id; #顺便把t_test的一条也删掉
    END
|
DELIMITER;

DELETE FROM t_book WHERE id=5; #删除操作触发trigger

上面函数的作用是：当删除 t_book 里面 id=5的数据时，触发在

  • 给t_bookType 表中 bookNum数量-1
  • 在日志表 t_log 中加入一条记录
  • 在测试表 t_test 里面删除了和之前记录id相等的数据 old.bookTypeId=t_test.id

7.2 查看触发器状态

查看触发器的状态：

1
SHOW TRIGGERS; #列出来所有的

7.3 删除触发器

注意结束符号 ；之前是否有空格或者全半角区别，可能引起错误。

1
DROP TRIGGER trig_book ;

触发器补充介绍：硕果累累的博客

8 函数（functions）

8.1 常用函数

8.1.1 日期和时间函数

获取当前时间的语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
select now();
select sysdate();

获取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒

set @test='2019-03-26 23:08:12.123456';

select date(@test); 		#示例
+-------------+
| date(@test) |
+-------------+
| 2019-03-26  |
+-------------+

其他类似：time、year 、quarter、month、week、day、hour、minute、second

与时间相关函数：

名称作用CURDATE()返回当前日期CURTIME()返回当前时间MONTH(d)返回当前日期d中的月份值

1
2
SELECT CURDATE(),CURTIME(),MONTH(birthday) AS m FROM table; #birthday是列名，MONTH(birthday)重命名为m
select curdate(),curtime() from gongkuang limit 5;

8.1.2 字符串函数

  • CHAR_LENGTH(s)计算字符串字符数
  • UPPER(s)转为大写
  • LOWER(s)转为小写
  • CONCAT(s1,s2...)拼接多个字符串
  • LEFT(str,len) RIGHT(str,len)返回字符串str 从左、右起len长的子串。
  • REVERSE(str)把str倒序
1
SELECT uName,CHAR_LENGTH(uName),UPPER(uName) up,LOWER(uName) low,concat(uName,upper(uName)) FROM t_u1;

输出：

在这里插入图片描述

1
select uName,left(uName,2),right(uName,2) from t_u1;

其他还有很多字符串函数可以需要的时候再查阅使用：

1
2
3
4
5
6
7
8
9
10
11
12
ASCII(str),
BIN(N),
CONV(str,from_base,to_base),
ELT(N,str1,str2,str3,…),
FIELD(str,str1,str2,str3,…),
FIND_IN_SET(str,strlist),
FORMAT(X,D),
INSERT(str,pos,len,newstr),
LOCATE(substr,str,pos),
REPLACE(str,from_str,to_str),
POSITION(substr IN str),
INSTR(str,substr),

8.1.3 数学函数

  • ABS(x)绝对值
  • SQRT(x)平方根
  • MOD(x，y)求余
  • ROUND(X,Y)X的Y位四舍五入小数
  • CEIL(x)、CEILING(x)向上取整
  • FLOOR(x)向下取整
  • POW(x,y)、POWER(x,y)幂运算，求x的y次方幂
1
select num,ABS(num),SQRT(num),MOD(num,3) from t_t; #计算num列的绝对值和对3求余

8.1.4 加密函数

  • PASSWORD(str)对密码加密，不可逆
  • MD5(str)、SHA5()MD5校验、SHA5校验
  • ENCODE(str，pswd_str)加密字符串，结果必须用BLOB类型保存
  • DECODE(crypt_str,pswd_str)解密字符串

在插入数据的时候设置加密的格式、加密数据和加密解密的钥匙：

1
2
3
4
5
#t_t有id,birthday,uName,num,password,pp    其中pp是blob类型
INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,PASSWORD('123456')); #password字段不是明文123456
INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,MD5('123456')); #password字段不是明文123456
INSERT INTO t_t VALUES(NULL,'2018-11-11','a',1,MD5('123456'),ENCODE('abcd','aa')); #pp是明文abcd用aa加密得到的二进制
SELECT DECODE(pp,'aa') FROM t_t WHERE id=1; #用aa解密pp 得到abcd

8.2 其他函数可以查阅mysql官方手册

9 存储过程

9.1 存储过程和函数定义

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输；

区别：

  • 存储函数必须有返回值，而存储过程没有，
  • 存储过程的参数可以使用 in，out，inout 类型，而存储函数的参数只能是 in 类型的。
  • 如果有函数从其他类型的数据库迁移到 MySQL，那么就可能因此需要将函数改造成存储过程

9.1.1 创建存储过程：

创建存储过程或函数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

sp_name 参数是存储过程/或存储函数的名称

proc_parameter 表示存储过程的参数列表，每个参数格式 [IN|OUT|INOUT] param_name type
    IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
    存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型

func_parameter:param_name type

type:Any valid MySQL data type

characteristic 参数指定存储过程的特性
可取:LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }| COMMENT 'string'

    LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成

    [NOT] DETERMINISTIC  存储过程输出结果是否是确定的，即每次输入一样输出也一样的程序，NOT 每次输入一样但输出可能不一样

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER } 指明子程序使用 SQL 语句的限制
        - CONTAINS SQL子程序包含 SQL 语句，但不包含读或写数据的语句。默认格式
        - NO SQL 表示子程序中不包含 SQL语句
        - READS SQL DATA 子程序中包含读数据的语句
        - MODIFIES SQL DATA 表示子程序中包含写数据的语句

    SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。
        - DEFINER 表示只有定义者自己才能够执行；
        - INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。

    COMMENT ‘string’ ：注释信息；

routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束

创建存储过程/函数示例sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
    READS SQL DATA
    BEGIN
         SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
    END
&&
DELIMITER ;

CALL pro_book(1,@total); #调用存储过程

DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
    RETURNS VARCHAR(20)
    BEGIN
        RETURN ( SELECT bookName FROM t_book WHERE id=bookId );
    END
&&
DELIMITER ;

SELECT func_book(2); #调用存储函数

9.1.2 修改存储过程或函数：

1
2
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
#characteristic:参数含义类似

9.1.3 调用存储函数/过程

调用存储过程 CALL sp_name([param_name[,...]);

调用存储函数 sp_name([param_name[,...]);

9.1.4 查看现有存储过程/函数

1
2
3
4
5
#SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'] #查看状态

SHOW PROCEDURE STATUS LIKE 'pro_book';

SHOW CREATE {PROCEDURE | FUNCTION} sp_name #查看定义

9.1.5 删除存储函数/过程

1
2
3
#DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

DROP PROCEDURE pro_user3;

9.2 存储函数变量

9.2.1 存储函数/过程中定义变量

1
DECLARE var_name[,...] type [DEFAULT value]

定义的变量不赋值时默认插入为NULL

1
2
3
4
5
6
7
8
9
10
11
#t_user有id,uName,password
DELIMITER &&
CREATE PROCEDURE pro_user()
    BEGIN
        DECLARE a,b VARCHAR(20) ;
        INSERT INTO t_user VALUES(NULL,a,b);
    END
&&
DELIMITER ;

CALL pro_user(); #调用存储过程

9.2.2 存储函数过程变量赋值

1
2
3
4
5
#方式1 直接赋值
SET var_name = expr [, var_name = expr] ...

#方式2 从其他表查询结果赋给当前表
SELECT col_name[,...] INTO var_name[,...] table_expr
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#分别表示直接赋值和查询结果赋值

DELIMITER &&
CREATE PROCEDURE pro_user2()
    BEGIN
        DECLARE a,b VARCHAR(20) ;
        SET a='java1234',b='123456';
        INSERT INTO t_user VALUES(NULL,a,b);
    END
&&
DELIMITER ;
CALL pro_user2(); #调用存储过程


#t_user2有id,uName,password  从t_user2查询数据存入变量再插入到t_user
DELIMITER &&
CREATE PROCEDURE pro_user3()
    BEGIN
        DECLARE a,b VARCHAR(20) ;
        SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
        INSERT INTO t_user VALUES(NULL,a,b);
    END
&&
DELIMITER ;
CALL pro_user3(); #调用存储过程

9.3 游标

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标必须声明在处理程序之前，并且声明在变量和条件之后。

游标的使用包括

  • 声明游标 DECLARE cursor_name CURSOR FOR select_statement ;
  • 打开游标 OPEN cursor_name;
  • 使用游标 FETCH cursor_name INTO var_name [,var_name ... ];
  • 关闭游标 CLOSE cursor_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER &&
CREATE PROCEDURE pro_user4()
    BEGIN
        DECLARE a,b VARCHAR(20) ;
        DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;
        OPEN cur_t_user2;
        FETCH cur_t_user2 INTO a,b; #未使用循环，所以只取出、插入了集合的一条数据
        INSERT INTO t_user VALUES(NULL,a,b);
        CLOSE cur_t_user2;
    END
&&
DELIMITER ;
CALL pro_user4(); #调用存储过程

9.4 流程控制

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。

9.4. 1 IF语句

1
2
3
4
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list ]...
[ELSE statement_list ]
END IF

举例：根据传入的id查询，结果数量大于0时更新这个id对应的name，否则新插入一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
    BEGIN
        SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId; #@num 表示全局变量
        IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
        ELSE
            INSERT INTO t_user VALUES(NULL,'2312312','2321312');
        END IF ;
    END
&&
DELIMITER ;
CALL pro_user5(4); #调用存储过程
CALL pro_user5(5); #调用存储过程

9.4. 2 CASE 语句

1
2
3
4
5
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list ]
END CASE

举例：传入id，查询的结果数量为1时更新，2时插入，其他情况插入另一条

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
    BEGIN
        SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
        CASE @num
        WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
        WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
        ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
        END CASE ;
    END
&&
DELIMITER ;
CALL pro_user6(5); #调用存储过程
CALL pro_user6(6); #调用存储过程

9.4. 3 LOOP，LEAVE 语句

LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是 LOOP 语句本身没有停止循环的语句，必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下：

1
2
3
[begin_label：]LOOP
    Statement_list
END LOOP [ end_label ]

LEAVE 语句主要用于跳出循环控制。语法形式如下：

1
LEAVE label

举例：循环插入指定参数条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
    BEGIN
        aaa:LOOP
            SET totalNum=totalNum-1;
            IF totalNum=0 THEN LEAVE aaa ;
            ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
            END IF ;
        END LOOP aaa ;
    END
&&
DELIMITER ;
DELETE FROM t_user;
CALL pro_user7(11); #调用存储过程

9.4. 4 ITERATE 语句

ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次循环。基本语法：

1
ITERATE label ;

示例：指定插入次数参数为3的时候不插入，其余在0之前的都插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
    BEGIN
        aaa:LOOP
            SET totalNum=totalNum-1;
            IF totalNum=0 THEN LEAVE aaa ;
            ELSEIF totalNum=3 THEN ITERATE aaa ; #当是3时，直接continue
            END IF ;
            INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
        END LOOP aaa ;
    END
&&
DELIMITER ;
DELETE FROM t_user;
CALL pro_user8(11); #调用存储过程

9.4. 5 REPEAT 语句

REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本语法形式如下：

1
2
3
4
[ begin_label : ] REPEAT
    Statement_list
    UNTIL search_condition
END REPEAT [ end_label ]

举例：

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
    BEGIN
        REPEAT
            SET totalNum=totalNum-1;
            INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
            UNTIL totalNum=1
        END REPEAT;
    END
&&
DELIMITER ;
DELETE FROM t_user;
CALL pro_user9(11); #调用存储过程

9.4. 6 WHILE 语句

1
2
3
[ begin_label : ] WHILE search_condition DO
    Statement_list
END WHILE [ end_label ]

举例：

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
    BEGIN
        WHILE totalNum>0 DO
            INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
            SET totalNum=totalNum-1;
        END WHILE ;
    END
&&
DELIMITER ;
DELETE FROM t_user;
CALL pro_user10(11); #调用存储过程

10 数据备份与还原

备份数据可以保证数据库中数据的安全，需要定期的进行数据库备份，可以备份数据表和整个数据库。

10.1 使用 mysqldump 备份（导出）

可以使用命令备份，也可以使用图形管理工具直接导出备份。

1
mysqldump -username -p dbname table1 table2 ... > BackupName.sql
  • dbname ：表示数据库的名称；
  • table1 和 table2 ：表示数据表的名称，没有指明时将备份整个数据库；
  • BackupName.sql ：表示备份文件的名称，文件名前面可以加绝对路径，一般以sql做后缀

10.2 还原（导入sql数据）

使用命令或者图形界面导入还原数据。

1
mysql -u root -p [dbname] < backup.sql
  • dbname ：表示导入后的数据库名称，参数可指定或不指定
  • 指定数据库名时，表示还原文件到这个表下
  • 不指定数据库名时，表示还原备份sql文件中默认的数据库和表属性。