MYSQL学习笔记

MySQL

4.1 数据库介绍

4.1.1 数据库概念

数据库（database），简称DB，是长期存储在计算机内部有结构的、大量的、共享的数据集合

• 长期存储：持久存储

• 有结构：

  • 类型
  • 关系

• 大量：大多数据库都是文件系统的，也就是说您处在数据库中的数据实际上就是存储在磁盘的文件中

• 共享：多个应用可以通过数据库实现数据的共享

4.1.2 关系型数据库和非关系型数据库

• 关系型数据库：

  关系型数据库，采用关系模型来组织数据的存储，以行和列的形式存储数据并记录数据与数据之间的关系-------将数据存储在表格中，可以通过建立表格与表格之间的关联来位数数据与数据之间的关系

• 非关系型数据库：

  采用键值对的模型来存储数据，只完成数据的记录，不会记录数据与数据之间的关系，在非关系型数据库中基于其特定的存储结构来解决一些大数据应用的难题。
  也用nosql数据库来指代非关系型数据库

4.1.3 常见的数据库产品

关系型数据库产品：

• MySQL(免费)
  • MariaDB
  • percona server
• PostgreSql
• Oracle（收费，但是功能最强大）
• SQL Server
• Access
• Sybase
• 达梦数据库

非关系型数据库

• 面向检索的列式存储
  • HaBase(属于hadoop子系统)
  • BigTable (Google)
• 面向高并发的缓存存储（基于键值对）
  • Redis
  • memcacheDB
• 面向海量数据访问的文档存储
  • MongoDB
  • couchDB

4.1.4 数据库术语

数据库(DataBase)：存储的数据的集合
数据(Data)：实际上指的是描述事物的符号记录
数据库管理系统（DBMS):是介于用户和操作系统之间的数据管理软件
数据库系统管理员（DBA):负责数据库创建，使用及维护的专门人员
数据库系统（DBS):以上整个系统的整合叫做数据库系统

4.2 MySQL数据库

4.2.1MySQL数据库环境准备

• MySQL下载，安装，配置，卸载
• DBMS安装及使用

4.2.2MySQL 服务的启动与停止

4.2.3MySQL的管理工具

1. ​ 可视化的DBMS工具

   • SQLyog

   • Navicat for mysql

     下载及安装   创建连接   

2. Mysql command line client

• 输入密码闪退：密码错误或者MySQL服务启动没有启动

4.2.4 MySQL逻辑结构

数据库   数据表   字段（列)   元组（一条记录）

4.3 SQL结构化查询语言

4.3.1 SQL概述

SQL（Structured Query Language）结构化查询语言，用于存取、查询、更新数据及管理关系型数据库系统

4.3.2 SQL发展

• 1981年有IBM公司推出，基于其简洁的语法在数据库中得到了广泛的应用
• SQL由ANSI组织确定规范
• 在不同的数据库产品中遵守SQL的通用规范，但是也对SQL有一些不同的改进，形成了一些数据库的专有指令
  • MySQL：limit
  • SQLServer:top
  • Oracle:rownum

4.3.3 SQL分类

• DDL（data definition language) ：数据定义语言
  • 用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、更改
• DML(Data manipulation language) ：数据操作语言
  • 用于完成对数据表中的数据的添加、删除、修改等操作
  • 添加：将数据存储到数据表中
  • 删除：将数据从数据表移除
  • 修改：对数据表中的数据进行修改
• DQL(Data Query Language)：数据查询语言
  • 用于将数据表中的数据查询出来
• DCL(Data Control Language)：数据控制语言
  • 用于完成事务管理等控制性操作

4.3.4 SQL基本语法

在MySQL终端或可视化工具中进行操作

• SQL 不区分大小写
• 每条SQL指令结束之后以分号结尾
• SQL关键字之间以空格进行分隔
• SQL之间可以不限制换行

4.3.5 DDL

DDL-数据库操作

1.创建数据库

##创建数据库
create database <db_name>;
##如果指定名称的数据库不存在则创建
create database if not exists <db_name> ;
##创建数据库的同时指定数据库的编码格式
create database <db_name> character set utf8;

2.查询数据库

##显示当前MySQL中的数据库列表
show databases;
##显示指定名称的数据库的创建语句
show create database <db_name>;

3.修改数据库

##修改数据库字符集
alter database <db_name> character set gbk;

4.删除数据库 删除数据库是会删除当前数据库中所有的数据表及数据表中的数据

## 删除数据库（如果数据库存在）
drop database （if exists） <db_name>;

5.使用/切换数据库

##使用数据库
use <db_name>

6.创建数据表

##创建一个学生表，分别插入not null,unique属性，指定类型
create table <tableName>(stu_name varchar(20),stu_num char(8) not null unique,stu_age int,stu_gender char(2),
stu_qq varchar(11)
)

7.查询数据表

##显示表
show tables
##查询表结构
desc <tablename>

8.删除数据表

##当数据表存在时删除
drop table if exists <tablename>

9.修改数据表

##修改数据表名 rename tos
alter table students rename to stus;
##数据表也有字符集，默认字符集与数据库一致
alter table stus character set utf8
##添加字段  add
alter table stus add stu_phone varchar(200) not null unique
##修改字段名和类型  change
alter table stus change stu_phone studesc text(500)
##只修改字段类型  modify
alter table stus modify stu_phone varchar(11)
##删除字段  drop
alter table stus drop stu_phone

10.数据类型

1. 数值类型

   MySQL中有多种数据类型可以存放数值，不同的类型存放的数值范围或形式不同

   类型	内存大小	范围	说明
   tinyint	1 byte	-128—127	特小型整数(年龄)
   smallint	2 byte	-32768—32767	小型整数
   mediumint	3 byte	-2^31—2^31	中型整数
   int	4 byte	—	整数
   bigint	8 byte	—	大型整数
   float	4 byte	—	单精度
   double	8 byte	—	双精度
   decimal	自定义	—	可指定小数位（位数，小数位）

2. 字符类型

   存储字符序列的类型

   类型	字符序列的长度范围	说明
   char	0—255字节	定长字符串，最多可以存储255个字符，当指定，数据表字段为cahr(n)时此列中的数据最长为N个字符，如果<n,自动补充’\u0000’
   varcahr	0—65535字节	可变长度字符串，varchar(n)限制最大存储字节为n
   tinyblob	0—255字节	存放二进制字符串
   blob	0—65535	存储二进制字符串
   mediumblob	0—1677215	存储二进制字符串
   longblob	0—4294967295字节	存储二进制字符串
   tinytext	0—255	存储文本数据
   text	0—65535	存储文本数据
   mediumtext	0—1677215	存储文本数据
   longtext	0—4294967295	存储文本数据

3. 日期类型

   MySQL数据库中，可以使用字符串来存储时间，但是如果需要对日期进行查询，字符串则不便于该操作

   类型	格式	说明
   date	2022-10-08	日期，只存储年月日
   time	10:34:13	时间，只存储时分秒
   year	2022	年份
   datetime	2021-10-08 10:34:15	日期-时间
   timestamp	2021-10-08 10:34:15	日期-时间

11.字段约束

1.在创建数据表的时候，指定的对数据表的列的数据限制性的要求

2.目的：

• 保证数据的有效性
• 保证数据的完整性
• 保证数据的正确性

3.常见的约束：

1. 非空约束(not null)

2. 唯一约束(unique)

3. 主键约束(primary key),非空+唯一，能够唯一标识数据库中的一条数据

4. 外键约束(foreign key),建立不同表之间的关联关系

   ##非空 唯一
   create table books(book_isbn int not null unique,book_name varchar(20) not null,book_author varchar(20) not null);
   ##主键约束  在一张表中最多只能有一个主键 主键非空唯一
   create table list1(list_id primary key) 或者
   create table list2(list_id, primary key(list_id))
   ##去除主键约束
   alter table list1 drop primary key
   ##创建表之后添加主键
   alter table list1 modify list_id primary key
   ##主键自动增长   没有合适的列作为主键时可以单独定义ID作为主键
   create table types(
       ##只有int类型可以自动增长
       ##id 生成后删除，ID不会重复已经有过的ID
       	#即只保证唯一性不保证连续性
       type_id int primary key auto_increment,
       type_name varchar(20) not null,
       type_remark varchar(100)
   );
   ##联合主键 将数据表中的多列组合在一起设置为表的主键
   #在实际企业项目中联合主键使用频率不高，也是额外生成一个主键id
   #当一张表中没有明确的字段作为主键时额外添加一个ID字段
   create table grades(
   	stu_num char(8) not null,
       course_id int,
       scores int,
       primary key(stu_num,course_id)
   )

4.3.6 DML 数据操纵语言

1.插入数据

##向数据表中指定的列添加相应数据，列的顺序不需要跟数据库保持一致
insert into <tablename>(columnname1,columnname2...) values(value1,valu12...);
##向表中所有字段添加数据
	#value值得顺序与数据表定义的字段顺序保持一致
	#在项目开发中，即使向所有字段添加数据，也建议写出字段列表
insert into <tablename> values (value1,value2,value3)

2.删除数据

##语法
delete from tablename where conditions;

##实例
delete from stus where stu_age>18;

##清空数据表(敏感操作)
delete from tablename

3.修改数据

##语法
update tablename set columnname1=newvalue1,columnname2=newvalue2 where conditions

4.3.7 DQL数据查询语言

从数据表中提取满足特定条件的记录

• 单表查询
• 多表联合查询

1.查询基础语法

##语法
select columnname1,columnname2... from tablename 
##显示所有列
select * from tablename

2.where 子句

delete from tablename where conditions;
update tablename set ... where conditions;
select...from tablename where conditions;

1.条件

操作符	说明
=	等于，筛选字段值的精确匹配
!=	不等于
< >	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between …and …	在某一范围之内，闭区间

2.多条件查询

操作符	说明
and	连接多个单条件语句，同时满足
or	连接多个单条件语句，满足其一
not	取反操作

3.like子句

##查询名字中含有字母'o'的学生
# %表示任意多个字符 _表示任意一个字符
select * from stus where stu_name like '%o%';

#查询姓名第一个字为张的同学
select * from stus where stus_name like'张';

#查询姓名最后一个字母为o的学生
select * from stus where stu_name like '%o';

4.对查询结果的处理

1.设置查询的列

#声明查询结果的指定列
select columnName from stus where stu_age=20;
#对从数据表中查询到的列进行一定的运算之后显示出来
select stus_name,stu_age-21 from stus;
#字段别名 as  可以给查询结果的列取一个语义性更强的别名(as可以省略)
select select stus_name,2021-stu_age as stu_birth_year from stus; 
#查询结果去重 distinct
select distinct stu_age from stus;

2.查询结果排序

#对查询的结果按照性别升序排序   order by... asc
select * from stus where stu_age>15 order by stu_gender;
#对查询的结果按照性别降序排序  order by... desc
select * from stus where stu_age>15 order by stu_gender desc;
#多字段排序
select * from stus where stu_age>15 order by stu_gender desc,stu_age desc;

3.聚合函数

​ 1.count（） 统计函数

#统计性别男的学生个数
select count(stu_name) from stus where stu_gender = '男';

​ 2. max() 计算最大值函数

#查询满足条件的记录中指定列的最大值
select max(stu_age) from stus where stu_gender = '女';

​ 3.min()计算最小值函数

#查询满足条件的记录中指定列的最小值
select min(stu_age) from stus where stu_gender = '男';

​ 4.sum() 求和函数

#计算年龄总和
select sum(stu_age) from stus; 

​ 5.avg() 求平均值函数

#计算指定列的平均值
select avg(stu_age) from stus;

4.日期函数

​ 1.now() 获取当前时间

​ 2.sysdate() 获取系统当前时间

5.字符串函数

​ 1.concat(str1,str2…) 字符串拼接

​ 2.upper(column) 将字段的值转换为大写

​ 3.lower() 小写转换

​ 4.substring(str,start,length) 从指定列中截取部分字符

5.分组查询/筛选

语句执行顺序：where—>group by—–>having

##语法
select 分组字段、聚合函数 from 表名 group by 分组字段;
##示例
select stus_gender,count(stu_name) from stus;

##分组查询并筛选 having 关键字
select stu_age,count(stu_num) from stus group by stu_age having count(stu_num)>1 order by stu_age;

6.分页查询

当数据表中的记录比较多的时候，如果一次性呈现出来，可读性比较差，可将这些数据分页进行显示

#语法
select * from ... limi start,datanums;
#分页语句的通用表达式
select * from ... where... limit (pageNum-1)*pageSize,pageSize;

4.4 数据表的关联关系

MySQL是一个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系（通过在数据表中添加字段建立外键约束）

数据与数据之间的关联关系分为4种：

4.4.1 一对一关联

1.主键关联

两张数据表中主键相同的数据为相互对应的数据

2.唯一外键关联

建立一个表的外键与另外一个表的主键一一对应，建立的外键的值必须在其关联的主键中存在，并且将外键天剑唯一约束

4.4.2 一对多与多对一关联

班级–学生 一对多 一个班级包含多个学生

学生–班级 多对一 多个学生可以属于一个班级

方案：

​ 在多的一端添加外键与少的一端的主键关联

4.4.3 多对多关联

学生–课程 一个学生可以选多门课程，一门课程可以由多个学生选择

会员–社团 一个会员可以参加多个社团，一个社团可以招纳多个会员

方案：

​ 额外创建一张关系表来维护多对多关联—在关系表中定义两个外键分别与两个数据表的主键进行关联

4.4.4 外键约束

外键约束—将一个列添加外键约束与另一张表的主键进行关联之后，这个外键约束的列添加的数据必须在关联的主键字段中存在

案例：学生表 与 班级表 在学生表中添加外键与班级表的主键进行关联

​ 1.先创建班级表

create table classes(
     class_id int primary key auto_increment,
    class_name varchar(10) not null unique,
     class_remark varchar(200)
  );

​ 2.创建学生表

##方式一：在创建表的同时创建cid字段并添加外键约束
##由于cid列要与class主键关联，cid的类型和长度要和class_id一致
create table students(
	stu_num char(8) primary key,
    stu_name varchar(20) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    cid int,
   	constraint FK_STUDENTS_CLASS foreign key(cid) references classes(class_id)
);
##方式二：创建表的时候不设置外键，后面再添加外键约束
create table students(
	stu_num char(8) primary key,
    stu_name varchar(20) not null,
    stu_gender char(2) not null,
    stu_age int not null,
    cid int,
);
#在创建表后为cid添加外键约束
alter table students add constraint FK_STUDENTS_CALSSES foreign key(cid) references classes(class_id);
#删除外键约束
alter table students drop foreign key FK_STUDENTS_CLASSES;

​ 3.向班级表添加数据

+----------+------------+--------------+  #班级表中ID=1 的班级信息被学生表中的记录关联了，我们就不能修改这条ID的class信
| class_id | class_name | class_remark |  #息，并且不能删除 （设置级联修改和级联删除可以做到修改和删除）
+----------+------------+--------------+
|        1 | java2104   | ...          |
|        2 | java2105   | ...          |
|        3 | java2106   | ...          |
|        4 | python2106 | ...          |
+----------+------------+--------------+

​ 4.向学生表添加数据

insert into students(stu_num,stu_name,stu_gender,stu_age,cid) values('202210102','李四','女',20,4);

​ 5.注意

当学生表中存在学生信息关联班级表的某条记录是，则不能对班级表的这条信息进行修改id操作
##如果非要删除或修改：
#先解除关联
update students set cid=null where cid=1;
#后修改或删除班级表信息
update calsses set class_id=5 where class_name='java2104';
#再进行关联
update students set cid=5 where cid is null;

外键约束之级联

#添加外键的时候指定级联修改和级联删除
alter table students add constraint FK_STUDNETS_CLASSES foreign key(cid) references classes(class_id) on update cascade on delete cascade;

4.4.5连接查询

使用jion实现多表的联合查询

1.inner join 内连接

内连接结果只获取两张表中匹配条件成立的数据，任何一张表在另一张表中如果没有找到对应的匹配，则不会出现在查询结果中

##语法
select ... from tablename1 inner join tablename2;
##笛卡尔积：
#如果使用上述语法，会获取两种数据表中数据集合的笛卡尔积（依次使用tablename1中的每一条记录去匹配tablename2的每条数据），此时很
#多数据都是没意义的  

##对笛卡尔积增加条件  顺序：先生成笛卡尔积再进行筛选（效率比较低）
select ...from tablename1 inner join tablename2 where conditions;

##改变上述语句的执行顺序   即先匹配再生成笛卡尔积
select ... from tablename1 inner join tablename2 on conditions;

##双重筛选
select ... from tablename1 inner join tablename2 on conditions1 where conditions2;

2.left join 左连接

显示左表中的所有数据，如果跟右表有匹配的关联信息，则也显示满足匹配条件的右表数据

##语法：
select * from tablename1 left join righttable2 on conditions where conditions2;

3.right jon 右连接

显示右表中的所有记录，如果跟左表有关联关系，则显示匹配到的数据；

##语法
select * from lefttable right join righttable on conditions where conditions2;

4.数据表别名

如果在连接查询的多张表中存在相同名字的字段，可以使用表名.字段名进行区分，如果表名太长，则不便于SQL语句的编写，此时可以使用数据表别名

##语法：
#s 和 c 分别是students 和 classes 的别名
select * from students s inner join classes c on conditions;

4.4.6 子查询/嵌套查询

第二个查询是基于第一个查询的结果进行的，即第一次查询作为第二次查询的源

如果返回的结果是一个值，可以直接使用关系运算符

返回结果是多个，用in 关键字

##查询java2104班级中的学生信息（此时我们假设只知道班级名称）
select * from students where cid=(select class_id from classes where class_name='java2104');

##查询所有java班级中的学生信息
# 传统方式union表示将多个查询结果拼接在一起
#in 关键字
SELECT * from students where cid in (select class_id from classes where class_name like 'java%');

##查询所有class_id=1的班级中性别为男的学生信息
select * from students where cid=1 and stu_gender='男';
select * from(select * from students where cid=1) where students.stu_gender='男';

4.5 SQL高级

4.5.1 存储过程

1.存储过程介绍

关键词：SQL指令，SQL引擎，编译

2.存储过程

将能够完成特定行为的SQL指令进行封装，编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

优点：

• SQL指令无需在客户端编写再通过网络发送，可以节省网络开销
• 避免SQL指令在网络传输过程中被恶意篡改，保证安全性
• 存储过程经过编译创建并保存在数据库中，执行过程无需重复进行编译操作
• 存储过程中多条SQL指令之间存在逻辑关系，支持流程控制语句，可以实现复杂的功能

缺点：

• 因为存储过程是根据不同的数据库进行编译创建并且存储在数据库中，当我们需要切换到其他数据库产品时，需要重新编写存储过程
• 受限于数据库产品，如果需要高性能的优化，会成为一个问题
• 在互联网项目中，如果需要数据库的高并发访问，使用存储过程会增加数据库的连接执行时间

3.存储过程的创建

##语法：
create procedure name ([入参，出参 args])
begin
	指令集
end;
##传参实现加法
create procedure proc_test1(in a int,in b int,out c int)
begin
	set c = a+b;
end;

4.调用存储过程

##语法：
set @m=0;
call name (入参，@m)
##显示变量值
select @m from dual;

5.变量的使用

存储过程中的变量分为两种，局部变量和用户变量

• 局部变量：定义在存储过程中的变量，只能在存储过程的内部使用

  ##必须定义在存储过程开始处
  begin
  declare x int default 2;
  declare ... .
  ...
  end;

• 用户变量：相当于全局变量，定义的用户变量可以通过select @name from dual进行查询

  ##用户变量会存储在数据库的dual字段中,必须以@开头
  set @变量 ;

• 给变量赋值

  ##使用set关键字赋值
  set @n=1;

• 存储过程中使用select … into 赋值

  set @n=0;
  CREATE PROCEDURE test_3(out c int)
  BEGIN
  SELECT COUNT(stu_num) into c from students;
  end;
  call test_3(@n);
  SELECT @n from dual;

• 用户变量注意事项：用户变量可以在SQL指令和存储过程中共享，因此建议尽量少使用用户变量

6.存储过程的参数

MySQL存储过程的参数共有三种，输入（in） ，输出（out），输入输出（inout）

1.in 输入参数

##创建存储过程添加学生信息
create procedure addstu(in snum varchar,in sname varchar,in gender varchar,in age int,in cid int)
begin
	insert into students(stu_num,stu_name,stu_age,stu_gender,cid)
	values(snum,snaem,gender,age,cid)
end;

2. out 输出参数

##创建存储过程根据学生学号查询学生姓名
create procedure lookup (in snum char,out sname varchar)
begin
	select stu_name into sname from students where stu_name=snum;
end;
set @name='';
call lookup('20210103',@name);
select @name from dual;

3.inout 输入输出参数

建议少用

##创建存储过程根据学号查询学生姓名
create procedure lookup2(inout str varchar(20))
begin
	select stu_name into str from students where stu_name=str;
end;
set @name='20210103';
call lookup2(@name);
select @name from dual;

7.存储过程中的流程控制

1.分支语句

##if-then-else语法
##创建一个存储过程，如果参数输入1则添加一条班级信息，2则添加一条学生信息
create procedure ifthenelse(in a int)
begin
	#单分支，如果条件成立则执行
	if conditions then
		--SQL1
	end if;
end;

##双分支，非此即彼
	if conditions then
		--sql1
	else
		--sql2
	end if;

##case语法
create procedure casetest(in a int)
begin
	case a 
	when 1 then 
		sql1
	when 2 then
		sql2
	when ... then
	##如果前面都不匹配则执行sqlelse
	else
	    sqlelse
	end case;
end;

2.循环语句

##while语法
create procedure whiletest(in num int)
begin
	declare i int default 0;
	set i = 0;
	while i < num do
		set i = i+1;
		insert into classes(class_name,remark) values(concat('java',i),'...');
	end while;
end;

##repeat语法  until语句后不加分号
create repeattest(in n int)
begin
	declare i int;
	set i = 0;
	repeat
		insert into classes(class_name,class_remark) values (cancat('python',i),'...');
		set i = i+1;
	until i>n;
	end repeat;
end;

##loop语法
create procedure looptest(in num int)
begin
	declare i int;
	set i = 1;
	myloop:loop
		dosomething...
		if i=num then
			leave myloop;
	end loop;
end;

8.存储过程的管理

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库中调用此存储过程

1.查询存储过程

查询某个数据库中有哪些存储过程

##根据数据库名查询当前数据库中的存储过程
show procedure status where db='db_test2';
##查询存储过程的创建细节
show create procedure dbtest1.proc_test1;

2.修改存储过程

主要指的是修改存储过程的特性

##语法
alter procedure procname 特征1 特征2 特征...

存储过程的特征参数：

• CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句

• NO SQL 表示子程序中不包含SQL

• READS SQL DATA 表示子程序中包含读数据的语句

• MODIFIES SQL DATA 表示子程序中包含写数据的语句

• SQL SECQURITY DATA 指明谁有权限来执行

  • DEFINER 表示只有定义者自己才能够执行
  • INVOKER 表示调用者可移植性

• COMMENT’string’ 表示注释信息

  ##案例
  #指示该存储过程可以有SQL语句但不包含读或写的语句
  alter procedure proc_test1 NO SQL;

3.删除存储过程

##语法
drop procedure procname
###drop 删除数据库中的对象、数据库、列、存储过程、视图、触发器、索引

9.存储过程的练习案例

##使用存储过程完成借书操作

#创建借书记录表
create table records(
	rid int primary key auto_increment,
    stu_number char(4) not null,
    bid int not null,
    borrow_num int null,
    is_return int not null,--0表示未归还，1表示已归还
    borrow_data data not null
    constraint FK_RECORDS_STUDENTS foreign key(stu_num) references students(stu_num),
    constraint FK_RECORDS_BOOKS foreign key(bid) feferences books(book_id),
)

##创建存储过程实现借书业务
	--参数1：输入参数，学号
	--参数2：输入参数，图书编号
	--参数3：输入参数：借书数量
	--惨数3：输出参数，借书的状态  (1借书成功  2学号并存在  3图书不存在 4库存不足)
create procedure proc_borrow_book(in stunum char(4),in bookid int,in booknum int,out state int)
begin
	declare stu_count int;
	declare book_count int;
	declare stock int;
	--判断学号是否存在：根据参数stunum到学生信息表中查询是否有stunum的学生
	select count(stu_num) into stu_count from students where stu_num=stunum;
	if stu_count>0 then
		--说明该学号学生存在
		--校验图书是否存在，根据参数bookid查询图书记录总数
		select count(book_id) into book_count from books where book_id=bookid;
		if book_count>0 then
			--说明图书存在
			--检查图书库存是否足够
			select book_stock into stock from books where book_id=bookid;
			if stock>=booknum then
				--库存足够
				--操作1，在结束记录表中添加记录
				insert into records(snum,bid,borrow_nums,is_return,borrow_data) values (stunum,bookid,booknum,0,sysdate());
				--操作2，修改图书库存
				update books set book_stock=stock-booknum where book_id=bookid;
				--结束成功
				set state=1;
			else
				--库存不足
				set state=4;
			end if;
			
		else
			--说明图书不存在
			set state=3;
		end if;
	else
		--说明学号不存在
		set state = 2;
	end if;
	
end; 

10.游标

如果我们要创建一个存储过程，需要返回查询语句查询的多条数据

##concat_ws('~',a,b,c)  用~连接abc

1.游标的概念

游标可以用来一次取出查询结果集中的每一条数据

2.游标的使用

##声明游标
declare cursor_name cursor for 查询语句；
##打开游标
open cursor_naem;
set i =0;
while i<num do
##游标提取获取到的集合里的一条数据
	fetch cursorname into 变量1，变量2，变量3...;
	set i = i+1;
	
end while;
##使用完成后需要关闭游标
close cursorname;

4.5.2 触发器

1.触发器的介绍

是一种特殊的存储过程，出发其和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当数据表中的数据执行DML操作时自动触发这个SQL片段的执行

在MySQL中只有执行insert\delete\update操作时才能触发触发器的执行

2.触发器的使用

##创建触发器的语法
create trigger tri_name 
before | after
insert|delete|update
on tablename
for each row   ##行级触发器

##使用
#当向学生表中添加一条记录时，向日志表中记录一条数据
create trigger trigger_test1
after insert on students for each row
begin
	insert into stulogs(times,log_text) values(now(),concat('添加',NEW.stu_num,'学生信息'));  
end;

3.查看触发器

show triggers;

4.测试触发器

• 添加一个学生信息，触发器执行一次
• 一条语句添加两条学生信息，触发器执行两次

5.删除触发器

drop trigger triggername;

6.NEW 与 OLD 关键字

在触发器中，通常处理一些DML的关联操作

可以使用new关键字和old关键字在触发器中获取触发这个触发器的DML的操作的数据
	new.用于获取Insert操作添加的数据或者update操作后的数据
	old.用于获取delete操作删除前的数据或者update操作前的数据

7.触发器使用总结

目前企业项目中已不提倡使用触发器，在实际开发中应尽量避免使用触发器

实际项目中也不提倡使用存储过程（增加了数据库的负担，不利于数据的迁移）

优点：

• 触发器是自动执行的，当对触发器相关的表执行相应的DML操作时，可以自动运行
• 楼栋-房间模型保持数据的关联性，有利于数据的完整性和有效性
• 触发器可以对DML操作的数据进行更为复杂的合法性校验

缺点：

• 使用触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难
• 大量使用触发器容易导致代码结构混乱，增加了程序的复杂性
• 当触发器操作的数据比较大时，执行效率会大大降低

4.5.3 视图

1.概念

就是有数据库中一张表或者多张表根据特定条件查询出的数据构造成的虚拟表

2.视图的作用

• 安全性，如果直接将数据表授权给用户操作，那么用户可以对数据表中的所有数据进行操作，因此可以用视图只对用户开放一部分数据，保证安全性
• 简单性，如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现，我们可以通过视图将这些连表查询的结果对用户开放，用户则可以直接通过查询视图来获取多表数据，操作更便捷

3.创建视图

##语法
create view viewname
as
查询语句

##将学生表中性别为男的学生查询出并生成一个视图
create view view_test1
as
select * from students where stu_gender='男';

4.查询视图

##从视图中获取数据
select * from view_test1;

5.视图数据的特性

视图是虚拟表，查询视图的数据是来源于数据表的，对视图数据进行操作是，对原数据表中的数据是否有影响呢？

• 对原表进行增删改，查询视图也会相应变化
• 在视图中添加数据，数据也会被添加到原表
• 在视图中删除数据，数据也会从原表中删除
• 在视图中更新数据，数据也会在原表中更新

6.视图的使用建议

• 对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图

7.查询视图结构

desc viewname;

8.修改视图

##创建或替换
create or replace viewname;
##修改
alter viewname;

9.删除视图

##删除视图不会影响原表信息
drop view viewnaem;

4.5.4 索引

数据库是用来存储数据的，在互联网应用中数据库中存储的数据可能会很多，数据表中数据的查询速度会随着数据量的增加而变慢，从而导致响应用户请求的速度变慢—用户体验差，数据库服务器压力也大，那么如何提高数据的查询效率呢？

1.索引的介绍

索引用来提高数据表中数据的查询效率

构造便于查找的结构进行存储，生成数据表的目录

2.索引的分类

根据创建索引的列的不同

• 主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key修饰
• 唯一索引：在数据表中的唯一列创建的索引，这个字段被unique修饰或数据不重复即可
• 普通索引：在普通字段上创建的索引，没有唯一性的限制
• 组合索引：两个及以上的字段联合起来创建的索引

说明：

1. 在创建数据表时，将字段声明为主键，会自动在主键字段创建主键索引
2. 在创建数据表时，将字段声明为唯一键，会自动在唯一字段创建唯一索引

3.查询数据表的索引

show indexes from tablename;

4.创建索引

1.创建唯一索引

要求该字段数据不能有重复

##语法
#表示在 字段名 字段创建唯一索引
create unique index index_name on tablename(字段名);

2.创建普通索引

不要求该字段数据的重复性和唯一性

##语法
create index index_name on tablename(字段名)；

3.创建组合索引

##语法
create index index_naeme on tablename(字段1，字段2...);

4.全文索引

是MySQL5.6版本新增的索引，可以通过此索引进行全文检索操作，但是该索引不支持中文检索

在应用开发中通常通过搜索引擎（数据库中间件）实现全文检索

create fulltext index index_name on tablename(字段名);

5.索引的使用

索引创建完成后无需调用，当根据创建索引的列进行数据查询的时候会自动使用索引查询

组合索引需要根据创建索引的所有字段查询时才能触发索引

• 在命令行窗口可以查看查询语句的解释：

  ##conditions 中包含索引时可以显式索引的使用
  explain select * from tbname where conditions;6.删除索引

6.查看索引

##在命令行运行
show create table tbname\G;
##查询数据表的索引
show indexes from tbname;
show keys from  tbname;

7.删除索引

##语法
drop index index_name on tbname;

8.索引的使用总结

优点：

• 降低了数据库服务器在查询时扫描的数据量，提高查询效率
• 可以避免服务器对数据进行排序，将随机IO变成有序IO

缺点：

• 当数据表中数据发生DML操作时，索引页需要更新
• 会占用额外的磁盘空间

使用规范：

• 数据表中的数据不多时，全表扫描可能更快
• 数据量大但是DML操作很频繁是，不建议使用索引
• 不要在数据重复度高的字段创建索引
• 创建索引之后，要注意查询SQL语句的编写，避免索引失效

4.6 数据库事务

4.6.1数据库事务介绍

我们把完成特定业务的多个DML操作称为一个事务

4.6.2数据库事务的特性

ACID特性：高频面试题

1.原子性（Atomicity):

一个事务的多个dml操作，要么同时执行成功，要么不执行

2.一致性（Consistency)

事务执行之前和事务执行之后数据库中的数据是一致的，完整性和一致性不能被破坏

3.隔离性（Isolation)

数据库允许多个事务同时执行，多个并行的事务之间不能相互影响

4.持久性（Durablility)

事务完成之后，对数据的操作是永久性的

4.6.3事务管理

1.自动提交与手动提交

• 在MySQL中，默认DML指令的执行是自动提交的，当我们执行一个DML指令之后会自动同步到数据库
• 关闭自动提交后，如过事务执行过程中出现Bug,则进行事务回滚，清空缓存，不修改数据表
• 关闭自动提交后，在事务过程执行完成后需要手动提交

##手动提交事务执行
start transaction;
...
rollback;
...
commit;

4.6.4 事务隔离级别

数据库允许多个事务并行，多个事务之间是隔离的，相互独立的。如果多个事务之间不相互隔离，并且操作同一数据时，可能会导致数据的一致性被破坏

1.四个级别：

• 读未提交(read uncommitted) ：不同事务可以读取彼此未提交的（缓存）数据
  • 可能导致出现脏读(读取未提交的数据)
• 读已提交(read committed)：不同事务间只能读取已提交的数据
  • 可以避免脏读
  • 但可能会导致不可重复读（虚读），即在同一个事务中在事务执行完毕之前两次读取到的数据不一致
• 可重复读(repeatable read)：T2执行第一次查询之后，在事务提交之前其他事务不能修改此次查询的数据
  • 避免了虚读的问题
  • 可能会导致幻读，即T2对数据表中的数据进行修改然后查询，在查询之前其他事务向数据表中新增了数据，此时会查询出与修改不一致的数据
• 串行化(serializable)：同时只允许一个事务对数据进行操作
  • 可以避免脏读，虚读，幻读

2.设置事务的隔离界别

• 可以通过设置数据库默认的事务隔离界别来控制事务之间的隔离性
• 也可以通过设置客户端与数据库连接设置来设置事务间的隔离级别
• MySQL数据库默认的隔离级别是可重复读

##查看MySQL数据库默认的隔离级别
#8.0.3之前：
select @@tx_isolation;
#8.0.3之后：
select @@transaction_isolation;

##设置MySQL的默认隔离级别
set session transaction isolation level [read committed];

4.7 数据库设计

• 车库用来存放车辆，需要划分车位
• 数据库用来存放数据，我们需要设计合理的数据表–节约磁盘，方便提取和保存

4.7.1数据库设计流程

数据库是为应用系统服务的，数据库存储什么数据也是有应用系统决定的，当我们进行应用系统开发时首先要明确应用系统的需求

1.分析数据实体，实体就是我们要存储的数据对象

• 电商系统：商品，用户，订单···
• 教务系统：学生，课程，成绩···

2.提取实体的数据项（实体的属性）

3.根据数据库设计范式来检查数据的提取是否合理

• 如果实体的数据项不满足范式，可能导致数据的冗余，从而导致维护困难等问题

4.绘制E-R图（实体关系图），直观展示实体与实体之间的关系

5.数据库建模

• 三线图进行数据表设计
• PowerDesigner
• PDMan

6.建库建表，编写SQL指令，创建数据库，数据表

7.SQL测试

4.7.2数据库设计案例

学校图书馆图书管理系统

1. 数据实体： 学生 类别 图书 借书记录 管理员

2. 提取数据项：

   1. 学生：学号 姓名 性别 年龄 院系编号 …
   2. 类别：类别ID 类别名称 类别描述
   3. 图书：ID 封面 名称 价格 进货日期 库存…
   4. 借书记录：ID 学号 图书编号 数量 是否归还 结束日期 还书日期
   5. 管理员信息：ID 登录名 登录密码 员工编号 姓名 联系方式
   6. 院系信息
   7. 员工信息

3. 数据库设计三范式

   第一范式：要求数据表中的字段不可再分（数据库中创建不出不满足第一范式的表）

   第二范式：要求数据表中不存在非关键字段对关键字段的部分依赖

   第三范式：不存在非关键字段之间的传递依赖

   4.绘制E-R图

E-R （Entity-Relationship),用于直观地体现实体与实体之间的关联关系（一对一，一对多，多对一，多对多）

​

5.数据库建模

• E-R图，数据表设计，建库建表
• PowerDesigner建模工具，导出数据表
• PDMan建模工具

PowerDesigner的使用

• 概念数据模型(CDM),相当于E-R图（甲方看）
• 逻辑数据模型（LDM)—-从概念数据模型生成（程序员看）
• 物理数据模型（PDM)
  • 可以对物理数据模型进行微调
  • 可以通过物理数据模型生成建设表的SQL语句（物理数据模型的databases栏目中–generate database)生成SQL文件
• 面向对象模型（在工具栏目中选择genegrte object-orentited model)可以根据语言设置生成实体类
  • 如果要借助建模工具生成Java代码，实体名和属性名都要符合Java的命名规范
  • 在企业项目开发中一般不会使用建模工具生成数据表和实体

PDMan工具的使用

• 创建项目–生成关系图

三线图

写出表名、字段、类型、长度、约束、说明等规范表的创建

• 每个实体创建一张数据表
• 如果存在多对多关系，则需要额外建一张表维护关系，多的每张表建立外键与关系表建立约束
• 一对一，多对多关联，在多的一张表添加一个外键与一的主键建立外键约束
• 一对一关联，在任意一端建立外键与另一端关联，要将外键设置为unique

​