千锋MYSQL基础笔记
一、MYSQL课程内容
1.1数据库介绍
- 数据库概念
- 属于介绍
1.2mysql数据库
- 下载、安装、配置、卸载
- mysql客户端工具的安装及使用
1.3SQL结构化查询语言
- 什么是SQL
- SQL操作数据
- SQL数据操作（CRUD操作：添加、查询、修改、删除）
1.4SQL高级
- 存储过程
- 索引
- 触发器、视图
1.5数据库设计
- 数据库设计步骤
- 数据库设计范式
- E-R图
- powerDseigenr建模工具、PDman
1.6数据库事务
- 什么是事务
- 事务特性ACID
- 事务隔离级别
- 事务管理
二、数据库介绍
2.1数据库概念
数据库：就是数据存放的仓库
数据库（Database，简称DB）是长期存储在计算机内部有结构的、大量的共享数据集合。
-
长期存储：持久存储
-
有结构：
- 类型：数据库不仅可以存放数据，而且存放的数据还是有类型的
- 关系：存储数据与数据之间的关系
-
大量：大多数数据库都是文件系统的，也就是说存储数据在数据库中的数据实际上就是存储在磁盘文件中
-
共享：多个应用程序可以通过数据库共享
2.2关系型数据库与非关系型数据库
-
关系型数据库
关系型数据库，采用了关系模型来组织数据存储，以行和列的形式存储数据并记录数据与数据之间的关系。
将数据存储在表格中，可以通过建立表格与表格之间的关联来维护数据与数据之间的关系。
学生信息——学生表
班级信息——班级表
-
非关系型数据库
非关系型数据库，采用键值对的模型来存放存储数据，只完成的数据记录，不会记录数据与数据之间的关系。
在非关系型数据库中基于其特定存储结构解决一些大数据应用难题
NoSQL（Not only sql）数据库来指代非关系型数据库
2.3常见数据库产品
关系型数据库产品
-
mysql
- maria DB
- percona server
-
postgreSQL
-
Oracle
-
SQL Server
-
Access
-
Sybase
-
达梦数据库
非关系型数据库产品
-
面向检索的列式存储 Column-Oriented
- Habase（Hadoop子系统）
- BigTable(Google)
-
面向高并发的缓存存储 Key-value
- redis
- MecaheDB
-
面向海量数据访问的文档存储 Document-Oriented
- MongoDB
- CouchDB
2.4数据库术语
三、MYSQL数据库环境准备
略
四、MYSQL的管理工具
当完成数据库安装之后，mysql是以服务的形式运行在windows/liunx系统，用户通过DBMS工具对mysql进行操作的，当我们安装完成Mysql之后默认安装了 mysql Command line clinet，此工具是一个命令行形式工具，通常我们会单独安装可视化DBMS工具
- navicat
4.1mysql Command line clinet使用
-
打开mysql Command line clinet：开始菜单–mysql –mysql Command line clinet
-
链接mysql：输入密码即可（如果密码输入错误会闪退 ——–密码password）
-
-
关闭输入exit即可
4.2navicat可视化工具下载
略
五、MYSQL逻辑结构
Mysql可以存储数据，但是存储在mysql中的数据需要按照特定的接管进行存储
学生——学校
数据——数据库
5.1逻辑结构
数据库软件——》数据库——》数据表——》字段（列）
5.2记录/元组
六、SQL
6.1SQL概述
SQL结构化查询语言，用于存储、查询、更新数据以及管理关系型数据库系统
6.1.1SQL发展
- sql是在1981年由IBM公司推出，一经推出基于其简洁语法在数据库中得到了广泛应用，成为主流数据库的通用规模
- SQL由ANSI组织确定规范
- 在不同的数据产品中遵守SQL通用规范，但是也对SQL有一些不同的改进，形成了一些数据库的专有指令
- mysql：limit
- sqlserver：top
- oracle：rownum
6.1.2SQL分类
根据SQL指令完成数据库操作的不同，可以将SQL指令分为四类：
- DDL Data Definition Language 数据定义语言
- 用于完成对数据库对象（数据库、数据表、视图、索引等）的修改、创建、删除
- DML Data Manipulation Language 数据操作/操纵语言
- 用于对数据表中的数据添加、修改、删除等操作
- 添加：将数据存储到数据表
- 删除：将数据从数据表中移除
- 修改：对数据表中数据修改
- DQL Data Query Language 数据查询语言
- 用于将数据表中数据查询出来
- DCL Data Control Language 数据控制语言
- 用于完成事务管理等控制性操作
6.2SQL基本语法
在MYSQL command line client 或者navicat等工具中都可以编写SQL指令
-
SQL指令不区分大小写
-
每条SQL表达式结束后都已；结束
-
SQL指令以空格进行分隔
-
SQL之间可以不限制换行（可以有空格的地方就可以有换行）
6.3DDL数据定义语言
6.3.1DDL数据库操作
使用DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库
查询数据库
##显示当前MYSQL中数据库列表
show databases；
##显示指定名称的数据库的创建的SQL指令
show create database db_test;
创建数据库
##创建数据库 db_name 表示创建数据库名称，可以自定义
create database <db_name>;
##创建数据库，当指定名称的数据库不存在时执行创建
create database if not exists <db_name>;
##在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库采用的编码格式utf8 gbk）
create database db_fmsc character set utf8；
修改数据库 修改数据库的字符集
##修改数据库的字符集
alter database <db_test> character set utf8; #utf8 gbk
删除数据库 删除数据库时会删除当前数据库中所有的数据表以及数据表中数据
## 删除数据库
drop database db_test;
## 如果数据库存在则删除数据库
drop database if exists db_test;
使用/切换数据库
use <db_name>
6.3.2DDL-数据表操作
创建数据表
数据表实际就是一个二维表格，一个表格是由多列组成，表格每一类称之为表格的一个字段
create table students(
stu_num char(8) not null unique,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null,
stu_tel char(11) not null unique,
stu_qq varchar(11) unique
);
查询数据表
show tables;
查询表结构
desc <stundents>;
删除数据表
##删除数据表
drop table <studnts>;
##当数据表存在时删除数据表
drop table if exists students;
修改数据表
##修改表名
alter table <students> rename to stus;
##数据表也是有字符集的，默认字符集和数据库一致
alter table <stus> character set utf8;
##添加字段(添加列)
alter table stus add stu_remark varchar(200);
##修改列类型和名称
alter table stus change stu_remark stu_desc text;
##只修改字段类型
alter table stus modify stu_desc varchar(200);
##删除字段
alter table stus drop stu_desc;
6.4mysql 中的数据类型
数据类型，指的是数据表中的列支持存放的数据类型
6.4.1数值类型
在mysql中有多种数据类型可以存放数值，不同类型存放的数值的范围或者形式是不同的
| 类型 | 内存空间大小 | 范围 | 说明 |
|---|---|---|---|
| tinyint | 1byte | 有符号-128127<br>无符号0255 | 小型整数（年龄） |
| smallint | 2byte（16bit） | 有符号-3276832767<br>无符号065535 | 小型整数 |
| mediumint | 3byte | 有符号-231~231-1 无符号0~2^32-1 |
中型整数 |
| int/integer | 4byte | 有符号-3276832767<br/>无符号065535 | 整数 |
| bigint | 8byte | 有符号-3276832767<br/>无符号065535 | 大型整数 |
| float | 4byte | 单精度 | |
| double | 8byte | 双精度 | |
| decimal | 第一个参数+2 | decimal（10，2）表示数值一共有10位小数位有两位 |
6.4.2字符串类型
存储字符序列的类型
| 类型 | 字符序列的长度范围 | 说明 |
|---|---|---|
| char | 0~255字节 | 定长字符串，最多可以存储255个字符，当我们指定数据表字段为char（n） 此列中的数据最长为n个字符，如果添加的数据少于n，则补充0000至n长度 |
| varchar | 0~65535字节 | 可变长度字符串，此类型最大长度为65535 |
| tinyblob | 0~255字节 | 存储二进制字符串 |
| blob | 0~65535字节 | 存储二进制字符串 |
| mediumblob | 0~1677215字节 | 存储二进制字符串 |
| longblob | 0~4294967295字节 | 存储二进制字符串 |
| tinytext | 0~255字节 | 文本数据（字符串） |
| text | 0~65535字节 | 文本数据（字符串） |
| mediumtext | 0~1677215字节 | 文本数据（字符串） |
| longtext | 0~4294967295字节 | 文本数据（字符串） |
6.4.3日期类型
在mysql数据库中，我们可以使用字符串来存储时间，但是我们需要基于时间字段进行查询（查询在某个时间段内数据） 就不便于查询现实
| 类型 | 格式 | 说明 |
|---|---|---|
| date | 2021-9-13 | 日期、只存储年月日 |
| time | 11：12：54 | 时间、只存储时分秒 |
| year | 2021 | 年份 |
| datetime | 2021-9-13 1：12：54 | 日期+时间 存储年月日时分秒 |
| timestamp | 20210913 111213 | 日期+时间（时间戳） |
6.5字段约束
6.5.1约束介绍
创建数据表时，对数据表的列的数据限制性要求（对表的列的数据进行限制）
为什么要给表中添加约束？
- 保证数据有效型
- 保证数据完整性
- 保证数据正确性
字段常见约束有哪些？
- 非空约束（not null）：限制此列的值必须提供，不能为null
- 唯一约束（unique）：在表中的多条数据，此列的值不能重复
- 主键约束（primary key）：非空+唯一，能够唯一表示数据表中的一条数据
- 外键约束（foreign key）：建立不同表之间的关联关系
6.5.2非空约束
限制数据表中此列的值必须提供*
- 创建表：设置图书表的book_name not null
create table books(
book_isbn char(4),
book_name varchar(10) not null,
book_author varchar(6)
);
添加数据：
6.5.3唯一约束
在表中的多条数据，此列的值不能重复
- 创建表：设置book_isbn 为unique
create table books(
book_isbn char(4) unique,
book_name varchar(10) not null,
book_author varchar(6)
);
- 添加数据
6.5.4主键约束
主键--就是数据表中唯一标识，在一张表中只能有一个主键（主键可以为一列，也可以是多个列的组合）
当一个字段声明为主键之后，添加数据时：
- 此字段数据不能为null
- 此字段数据不能重复
创建表时定义主键
create table books(
book_isbn char(4) primary key,
book_name varchar(10) not null,
book_author varchar(6)
);
或者
create table books(
book_isbn char(4) ,
book_name varchar(10) not null,
book_author varchar(6),
primary key(book_isbn)
);
删除数据表主键约束
alter table books drop primary key;
创建表后添加主键约束
##创建表时没有添加主键约束
create table books(
book_isbn char(4),
book_name varchar(10) not null,
book_author varchar(6)
);
##创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;
6.5.5主键的自动增长
我们在创建数据表时，如果数据表中有列可以作为主键（例如学生表学号、图书表isbn）我们可以直接设置这个列为主键；当有些数据表中没有合适的列作为主键时，我们可以额外定义一个与记录本书无关的列（ID）作为主键，此列数据无具体含义，主要用于标识一条记录，在mysql中我们可以将此列定义为int，同时设置为自动增长，当我们像数据表中新增一条记录时，无需提供ID列的值，他会自动生成
定义自动增张
- 定义int类型字段自动增长：auto_increment
create table types(
type_id int primary key auto_increment,
type_name varchar(20) not null,
type_remark varchar(100)
);
注意：自动增长从1开始，每田间一条记录，自动的增长的列会自定+1，当我们把某条记录删除之后，自动增长的数据也不会重复生成（自动增长只保证唯一性，不保证连续性）
6.5.6联合主键
联合主键——将数据表中多列组合在一起设置为表的主键
定义联合主键
create table grades(
stu_num char(8),
course_id int,
score int,
primary key(stu_num,course_id)
);
注意：在实际企业项目数据库设计中，联合主键使用比较少，使用频率并不高，当一张数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键。
6.5.7外键约束
在多表管理部分讲解
6.6DML数据操纵语言
用于完成对数据表中数据的插入、删除、修改操作
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,
stu_tel char(11) not null unique,
stu_qq varchar(11) unique
);
6.6.1插入数据
语法
insert into <table_name> (columName...)values(values1,values2....);
示例
##向数据表中指定的列添加数据（不允许为空的列必须提供数据）
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel)
values('20210101','张三','男','21','130313131');
##数据表名后的字段名列表顺序可以不与表中一致，但是values中值的顺序必须与表名后字段名顺序对应
insert into stus(stu_num,stu_name,stu_age,stu_tel,stu_gender)
values('20210102','王五','20','123666558','女');
##当要向表中所有列添加数据时，数据表名后面的字段列表可以省略，但是value中值的顺序需要与数据表定义的字段保持一致，
insert into stus values('20210105','孙琪','男','21','13031221332','666666');
##不过在项目开发中即使要向所有列添加数据，也建议将字段列表显示写出来（增强sql稳定性）
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq)
values('20210105','孙琪','男','21','13031221332','666666');
6.6.2删除数据
从数据表中删除满足特定条件（所有）的数据的记录
语法
delete from <tablename> where conditions;
实例
##删除学号为20210102的学生信息
delete from stus where stu_num='20210102';
##删除年龄大于20岁的学生信息（如果满足where子句的记录有多条，则删除多条记录）
delete from stus where stu_age>20;
##删除语句没有where子句，则表示删除当前数据表中的所有数据（敏感操作）
delete from stus;
6.6.3修改数据
对数据表中已经添加的记录进行修改
语法
update <tableName> set columnName=vlaue
示例
##将学号20210105学生姓名修改为孙七
update stus set stu_name='孙七'where stu_num='20210105';
##将学号为20210103学生性别修改为男，qq修改为7777
update stus set stu_gender='男',stu_qq='7777' where stu_num='20210103';
##根据主键修改其他所有列
update stus set stu_name='韩美美',stu_gender='女',stu_age='18',stu_tel='1311155588',stu_qq='999999' where stu_num='20210102';
##如果update语句没有where语句，则表示当前表中所有行（记录）
update stus stu_name='Tom';
6.7DQL数据查询语言
从数据表中提取满足特定条件的记录
- 单表查询
- 多表联合查询
6.7.1查询基础
语法
##select关键字后指定要显示查询到的记录有哪些列
select column1,[column2,column3.....]from <tableName>;
##如果要显示查询到记录的所有列，则可以使用 *代替字段名表 （在项目开发中不建议使用）
select * from stus;
6.7.2where 子句
在删除、修改以及查询语句后都可以添加where子句（条件），用于筛选满足特定条件的数据进行删除、修改以及查询
delete from tableName where conditions;
update tableName set ... where conditons;
select ....from tableName where conditions;
条件关系运算符
## = 等于
select * from stus where stu_num='20210101';
## != <>不等于
select * from stus where stu_num!='20210101';
select * from stus where stu_num<>'20210101';
## > 大于
select * from stus where stu_age>'18';
## < 小于
select * from stus where stu_age<'20';
## >= 大于等于
select * from stus where stu_age>='20';
##<= 小于等于
select * from stus where stu_age<='20';
##between and 区间查询 between V1 and V2
select * from stus where stu_age between 18 and 20;
条件逻辑运算符
在where子句中，可以将多个条件通过逻辑预算符（and or not）进行连接，通过多个条件来筛选要操作的数据。
##and 并且 筛选多个条件同时满足的数据
select * from stus where stu_gender='女' and stu_age<21;
##or 或者 筛选多个条件中满足一个即可
select * from stus where stu_gender='女' or stu_age<21;
##not 取反
select * from stus where stu_age not between 18 and 20;
6.7.3LIKE子句
在where子句条件中，我们可以使用LIKE关键字来实现模糊查询
语法
xxxxxxxxxx xxxxxxxxxx select * from tableName where columnName like 'reg'; mysql mysql
- 在lIKE关键字后的表达式中
- % 表示任意多个字符【%o%包含字母O】
- '' 表示任意一个字符【 ‘ __’o%表示第二个字母为o】
示例
#查询包含学生姓名字母o的学生信息
select * from stus where stu_name like '%o%';
#查询姓名第一个字为张的学生信息
select * from stus where stu_name like '张%';
#查询学生姓名最后一个字母为o的学生信息
select * from stus where stu_name like '%o';
#查询学生姓名中第二个字母为o的学生信息
select * from stus where stu_name like '_o%';
6.7.4对查询结果的处理
设置查询的列
声明显示查询结构指定列
select columnName1,columnName2,....from stus where stu_age>20;
计算列
对从数据表中查询的记录进行一定的运算之后显示出来
#出生年份=当前年份-年龄
select stu_name,2021-stu_age from stus;
+----------+--------------+
| stu_name | 2021-stu_age |
+----------+--------------+
| omg | 2000 |
| 韩美美 | 2003 |
| Tom | 2001 |
| lucy | 2000 |
| polly | 2000 |
| theo | 2004 |
+----------+--------------+
as字段取别名
可以给查询结果的列名，取一个语义性更强的别名(如下案例中as关键字可以省略)
mysql> select stu_name,2021-stu_age as stu_birth_year from stus;
+----------+----------------+
| stu_name | stu_birth_year |
+----------+----------------+
| omg | 2000 |
| 韩美美 | 2003 |
| Tom | 2001 |
| lucy | 2000 |
| polly | 2000 |
| theo | 2004 |
+----------+----------------+
mysql> select stu_name as 姓名,2021-stu_age as 出生年份 from stus;
+--------+----------+
| 姓名 | 出生年份 |
+--------+----------+
| omg | 2000 |
| 韩美美 | 2003 |
| Tom | 2001 |
| lucy | 2000 |
| polly | 2000 |
| theo | 2004 |
distinct消除重复行
从查询结果中将重复的记录消除
mysql> select distinct stu_age from stus;
+---------+
| stu_age |
+---------+
| 21 |
| 18 |
| 20 |
| 17 |
+---------+
6.7.5排序- order by
将查询到的满足条件的记录按照指定的列的值升序或降序排列
语法
select *from tableName where conditions order by columnName asc|desc;
- order by columnName 表示将查询结构按照指定的列排序
- asc按照指定的列升序（默认）
- desc按照指定的列降序
实例
#单字段排序
select * from stus where stu_age>15 order by stu_gender desc;
+----------+----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+----------+------------+---------+-------------+--------+
| 20210101 | omg | 男 | 21 | 130313131 | NULL |
| 20210103 | Tom | 男 | 20 | 123666558 | 7777 |
| 20210105 | polly | 男 | 21 | 13031221332 | 666666 |
| 20210106 | theo | 男 | 17 | 213212312 | NULL |
| 20210102 | 韩美美 | 女 | 18 | 1311155588 | 999999 |
| 20210104 | lucy | 女 | 21 | 132132123 | NULL |
+----------+----------+------------+---------+-------------+--------+
#多字段排序 先满足第一个排序规则，当第一个排序规则的列的值相同在按照第二个列的规则排序
mysql> select * from stus where stu_age>15 order by stu_gender asc,stu_age desc;
+----------+----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+----------+------------+---------+-------------+--------+
| 20210104 | lucy | 女 | 21 | 132132123 | NULL |
| 20210102 | 韩美美 | 女 | 18 | 1311155588 | 999999 |
| 20210101 | omg | 男 | 21 | 130313131 | NULL |
| 20210105 | polly | 男 | 21 | 13031221332 | 666666 |
| 20210103 | Tom | 男 | 20 | 123666558 | 7777 |
| 20210106 | theo | 男 | 17 | 213212312 | NULL |
+----------+----------+------------+---------+-------------+--------+
6.7.6聚合函数
SQL中提供了一些对查询记录的列进行计算的函数——聚合函数
- count
- max
- min
- sum
- avg
- count （）统计函数 ，统计满足条件的指定字段值的函数（记录数）
#统计学生表中学生的总数
mysql> select count(stu_num) from stus;
+----------------+
| count(stu_num) |
+----------------+
| 7 |
+----------------+
#统计学生表中性别为男的总数
mysql> select count(stu_num) from stus where stu_gender='男';
+----------------+
| count(stu_num) |
+----------------+
| 5 |
+----------------+
- MAX（）计算最大值查询满足条件的记录中指定列的最大值
select max(stu_age) from stus;
+--------------+
| max(stu_age) |
+--------------+
| 21 |
+--------------+
select max(stu_age) from stus where stu_gender='女';
+--------------+
| max(stu_age) |
+--------------+
| 21 |
+--------------+
- min（）计算最小值查询满足条件的记录中指定列的最小值
mysql> select min(stu_age) from stus;
+--------------+
| min(stu_age) |
+--------------+
| 14 |
+--------------+
1 row in set (0.00 sec)
mysql> select min(stu_age) from stus where stu_gender='女';
+--------------+
| min(stu_age) |
+--------------+
| 18 |
+--------------+
1 row in set (0.00 sec)
- sum()计算和，查询满足条件的记录中，指定的列的组合
#计算所有年龄学生总和
select sum(stu_age) from stus;
+--------------+
| sum(stu_age) |
+--------------+
| 133 |
+--------------+
#计算所有男生年龄的总和
select sum(stu_age) from stus where stu_gender='男';
+--------------+
| sum(stu_age) |
+--------------+
| 94 |
+--------------+
- avg（）求平均值，查询满足条件记录中，计算指定列的平均值
select avg(stu_age) from stus;
+--------------+
| avg(stu_age) |
+--------------+
| 19.0000 |
+--------------+
select avg(stu_age) from stus where stu_gender='男';
+--------------+
| avg(stu_age) |
+--------------+
| 18.8000 |
+--------------+
6.7.7日期函数和字符串函数
日期函数
当我们向日期类型的列添加数据时，可以通过字符串类型（字符串格式必须为yyyy-MM-dd hh:mm:ss）
如果想要获取当前系统时间添加到当前日期类型的列，可以使用‘now（）或者sysdate（）
示例
desc stus;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| stu_num | char(8) | NO | PRI | NULL | |
| stu_name | varchar(20) | NO | | NULL | |
| stu_gender | char(2) | YES | | NULL | |
| stu_age | int | NO | | NULL | |
| stu_tel | char(11) | NO | | NULL | |
| stu_qq | varchar(11) | YES | UNI | NULL | |
| stu_enterence | datetime | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
#通过字符串类型，给日期类型的列赋值
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210108','张小三','女','20','134343434','12311','2021-09-01 09:00:00');
#通过now（）获取当前时间
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210109','张小四','女','20','1355553434','133311',now());
#通过sysdate（）获取当前时间
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
-> values('20210110','李磊','男','16','134556434','12314441',sysdate());
#通过now和sysdate获取当前系统时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-12-25 07:04:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-12-25 07:04:57 |
+---------------------+
1 row in set (0.00 sec)
字符串函数
通过SQL指令对字符串进行处理
示例
#concat(column1,column2.....)拼接多列
select concat(stu_name,'-',stu_gender) from stus;
+---------------------------------+
| concat(stu_name,'-',stu_gender) |
+---------------------------------+
| omg-男 |
| 韩美美-女 |
| Tom-男 |
| lucy-女 |
| polly-男 |
| theo-男 |
| 林涛-男 |
| 张小三-女 |
| 张小四-女 |
| 李磊-男 |
+---------------------------------+
#upper(column)将字段值转为大写
mysql> select upper(stu_name) from stus;
+-----------------+
| upper(stu_name) |
+-----------------+
| OMG |
| 韩美美 |
| TOM |
| LUCY |
| POLLY |
| THEO |
| 林涛 |
| 张小三 |
| 张小四 |
| 李磊 |
+-----------------+
#lower（column）将指定列的值转换成小写
mysql> select lower(stu_name) from stus;
+-----------------+
| lower(stu_name) |
+-----------------+
| omg |
| 韩美美 |
| tom |
| lucy |
| polly |
| theo |
| 林涛 |
| 张小三 |
| 张小四 |
| 李磊 |
+-----------------+
#substring(column,start,len)从指定列中截取部分显示，start从1开始
select stu_name,substring(stu_tel,7,4) from stus;
+----------+------------------------+
| stu_name | substring(stu_tel,7,4) |
+----------+------------------------+
| omg | 131 |
| 韩美美 | 5588 |
| Tom | 558 |
| lucy | 123 |
| polly | 2133 |
| theo | 312 |
| 林涛 | |
| 张小三 | 434 |
| 张小四 | 3434 |
| 李磊 | 434 |
+----------+------------------------+
6.7.8分组查询-group by
分组-就是将数据表中的记录按照指定的列分组
语法
select (分组字段/聚合函数)
from 表名
[where 条件]
group by 分组列名[having条件]
[order by排序字段]
;
- select 后使用*显示对查询结果进行分组之后，显示每组第一条记录（这种显示通常是无意义的）
- select后通常显示分组字段和聚合函数（对分组后的数据进行统计，求和，平均值等）
- 语句执行顺序：1——先根据where条件从数据库查询记录 2——group by对查询记录进行分组 3——执行haning对分组后的数据进行筛选
示例
#先对查询学生按性别进行分组（纷争了男女两组），然后再分别统计每组学生的个数
mysql> select stu_gender,count(stu_num) from stus group by stu_gender;
+------------+----------------+
| stu_gender | count(stu_num) |
+------------+----------------+
| 女 | 4 |
| 男 | 5 |
+------------+----------------+
#先对查询学生按性别进行分组（纷争了男女两组），然后再分别统计每组学生的平均年龄
mysql> select stu_gender,avg(stu_age) from stus group by stu_gender;
+------------+--------------+
| stu_gender | avg(stu_age) |
+------------+--------------+
| 女 | 19.7500 |
| 男 | 18.2000 |
+------------+--------------+
#先对学生按年龄进行分组（分为16、17、18、20、21、22六祖），然后统计个组的学生数，还可以对最终结果进行排序
mysql> select stu_age,count(stu_num) from stus group by stu_age order by stu_age;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 17 | 1 |
| 18 | 1 |
| 20 | 3 |
| 21 | 1 |
| 22 | 1 |
+---------+----------------+
#查询所有学生，按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数>1的组显示出来，再按年龄升序排序
mysql> select stu_age,count(stu_num)
-> from stus
-> group by stu_age
-> having count(stu_num)>1
-> order by stu_age
-> ;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 20 | 3 |
+---------+----------------+
#查询性别为‘男’的学生，按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数>1的组显示出来，再按年龄升序排序
mysql> select stu_age,count(stu_num)
-> from stus
-> where stu_gender='男'
-> group by stu_age
-> having count(stu_num)>1
-> order by stu_age;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 20 | 2 |
+---------+----------------+
6.7.9分页查询-limit
当数据表中的数据比较多时，如果一次性全部查询出来显示给用户，用户体验性/可读性不还好，因此我们可以将这些数据分页显示
语法
select ....
from...
where...
limit param1,param2
- param1 int 表示获取查询语句的结果中第一条数据的索引（索引从0开始）
- param2 int 表示获取的查询记录的条数(如果剩下的数据调试
,则返回剩下的所有记录)
案例
对数据表中的学生进行分页显示，总共有10条数据，我们每页显示3条
总记录数 count10
每页显示 pageSize 3
总页数 pageCount =count%pageSize==0？count\pageSize;count\pageSiz:+1;
#查询第一页
select * from [where ....] limit 0,3; (1-1)*3
#查询第二页
select * from stus [where...] limit 3,3; (2-1)*3
#查询第三页
select * from stus [where...] limit 6,3; (3-1)*3
#查询第四页
select *from stus [where...] limit 9,3; (4-1)*3
#如果在一张数据表中，
#pageNum表示查询的页码，
#pageSize表示每页显示的条数
#通用分页语句如下
select * from <tabName>[where...] limit (pageNum-1)*pageSize，pageSize
七、数据表的关联关系
7.1关联关系介绍
mysql是一个关联型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系，通过在数据表中添加字段建立外键约束
数据与书记之间的关联关系分为四种
- 一对一关联
- 一对多关联
- 多对一关联
- 多对多关联
7.2一对一关联
人——身份证 一个人只有一个身份证，一个身份证只对应一个人
学生——学籍 一个学生只对应一个学籍，一个学籍也对应唯一一个学生
用户——用户详情 一个用户只有一个详情，一个详情也只对应一个用户
方案一：主键关联——两张数据表中主键相同的数据为相互对应的数据
方案二：唯一外键——在任意一张表中添加一个字段添加外键约束与另外一张表主键关联，并且将外键列设置添加唯一约束
7.3一对多与多对一关联
班级——学生（一对多）一个班级包含多个学生
学生——班级（多对一）多个学生可以属于一个班级
图书——分类 商品——商品类别
方案：在多的一端添加外键，与一的一段进行关联
7.4多对多关联
学生——课堂 一个学生可以选择多门课，一门课程也可以由多个学生选择
会员——社团 一个会员可以参加多个社团，一个社团也可以招纳多个会员
方法：额外创建一张关系表来维护多对多关联——在关系表中定义两个外键，分别在两个关系表的主键进行关联
7.5外键约束
外键约束——将一个列添加外键约束与另一张表的组件进行关联之后，这个外键约束的列添加数据
必须要在关联的主键阻断中存在
案例：学生表与班级表（在学生表中添加外键与班级表的主键进行关联）
1. 先创建班级表
create table classes(
class_id int primary key auto_increment,
class_name varchar(40) not null unique,
class_remark varchar(200)
);
2. 创建学生表（在学生表中添加外键与班级表的主键进行关联）
#【方式一】在创建表中，定义cid字段，并添加外键约束
#由于cid列要与classes表的class_id进行关联，因此cid字段类型和长度要与class_id一致
create table student(
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_CLASSES foreign key(cid) references classes(class_id)
);
#【方式二】先创建表，再添加外键约束
create table student(
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 student add constraint Fk_STUDENT_CLASSES foreign key(cid) references
classes(class_id);
#删除外键约束
alter table student dorp foreign key Fk_STUDENT_CLASSES;
3.向班级表添加班级信息
insert into classes(class_name,class_remark) values('Java2104','...');
insert into classes(class_name,class_remark) values('Java2105','...');
insert into classes(class_name,class_remark) values('Java2106','...');
insert into classes(class_name,class_remark) values('Python2106','...');
select * from classes;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 1 | Java2104 | ... |
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
+----------+------------+--------------+
4.向学生表中添加学生信息
insert into student(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210102','李四','女','20','4')
#添加学生时，设置给cid外键列的值必须在其关联的主表存在（clasess的class——id）
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210103','王五','男','20','6');
#6不存在与clases表中
7.6外键约束_级联
当学生表中存在学生信息关联班级表的某条记录时，就不能对班级表的这条记录进行修改ID、删除操作
mysql> select * from classes;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 1 | Java2104 | ... |
| 2 | Java2105 | ... | #班级表中class_id=1的班级信息，被学生表中关联了
| 3 | Java2106 | ... | #我们就不能修改JAVA2104的class_id 并且不能删除
| 4 | Python2106 | ... |
+----------+------------+--------------+
mysql> select * from student;
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 1 |
| 20210102 | 李四 | 男 | 18 | 1 |
| 20210103 | 王五 | 男 | 18 | 1 |
| 20210104 | 赵六 | 女 | 18 | 2 |
+----------+----------+------------+---------+------+
mysql> update classes set class_id=5 where class_name='Java2104';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_test2`.`student`, CONSTRAINT `Fk_STUDENT_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))
mysql> delete from classes where class_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_test2`.`student`, CONSTRAINT `Fk_STUDENT_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))
如果一定要修改Java2014id
- 将引用Java2104班级ID的学生记录修改为NULL
- 再修改班级信息表中Java2104记录的class_id
- 将学生表中cid设置为NULL记录的cid重新修改Java2104这个班级的新id
update student set cid=Null where cid=1;#结果如下
select * from student;
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | NULL |
| 20210102 | 李四 | 男 | 18 | NULL |
| 20210103 | 王五 | 男 | 18 | NULL |
| 20210104 | 赵六 | 女 | 18 | 2 |
+----------+----------+------------+---------+------+
update classes set class_id=5 where class_name='Java2104';#结果如下
select * from classes;
+----------+------------+--------------+
| class_id | class_name | class_remark |
+----------+------------+--------------+
| 2 | Java2105 | ... |
| 3 | Java2106 | ... |
| 4 | Python2106 | ... |
| 5 | Java2104 | ... |
+----------+------------+--------------+
update student set cid=5 where cid IS NULL;
select * from student;
+----------+----------+------------+---------+------+
| stu_num | stu_name | stu_gender | stu_age | cid |
+----------+----------+------------+---------+------+
| 20210101 | 张三 | 男 | 18 | 5 |
| 20210102 | 李四 | 男 | 18 | 5 |
| 20210103 | 王五 | 男 | 18 | 5 |
| 20210104 | 赵六 | 女 | 18 | 2 |
+----------+----------+------------+---------+------+
4 rows in set (0.00 sec)
我们可以使用级联操作来实现
-
在添加外键时，设置级联修改和级联删除
#删除原有外键约束 alter table student drop foreign key FK_STUDENT_CLASSES; #重新添加外键约束，并设置 级联修改 和 级联删除 alter table student add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE; -
测试级联修改
#班级信息 +----------+------------+--------------+ | class_id | class_name | class_remark | +----------+------------+--------------+ | 2 | Java2105 | ... | | 3 | Java2106 | ... | | 4 | Python2106 | ... | | 5 | Java2104 | ... | +----------+------------+--------------+ #学生信息 +----------+----------+------------+---------+------+ | stu_num | stu_name | stu_gender | stu_age | cid | +----------+----------+------------+---------+------+ | 20210101 | 张三 | 男 | 18 | 5 | | 20210102 | 李四 | 男 | 18 | 5 | | 20210103 | 王五 | 男 | 18 | 5 | | 20210104 | 赵六 | 女 | 18 | 2 | +----------+----------+------------+---------+------+ #直接修改Java2104的class_id，关联Java2104这个班级的学生记录的cid也会同步修改 mysql> update classes set class_id=1 where class_name='Java2104'; #学生信息 +----------+----------+------------+---------+------+ | stu_num | stu_name | stu_gender | stu_age | cid | +----------+----------+------------+---------+------+ | 20210101 | 张三 | 男 | 18 | 1 | | 20210102 | 李四 | 男 | 18 | 1 | | 20210103 | 王五 | 男 | 18 | 1 | | 20210104 | 赵六 | 女 | 18 | 2 | +----------+----------+------------+---------+------+ #班级信息 +----------+------------+--------------+ | class_id | class_name | class_remark | +----------+------------+--------------+ | 1 | Java2104 | ... | | 2 | Java2105 | ... | | 3 | Java2106 | ... | | 4 | Python2106 | ... | +----------+------------+--------------+-
测试级联删除
#删除class_id=1的班级信息，学生表中引用此班级信息的记录也会同步删除 mysql> delete from classes where class_id=1; +----------+------------+--------------+ | class_id | class_name | class_remark | +----------+------------+--------------+ | 2 | Java2105 | ... | | 3 | Java2106 | ... | | 4 | Python2106 | ... | +----------+------------+--------------+ +----------+----------+------------+---------+------+ | stu_num | stu_name | stu_gender | stu_age | cid | +----------+----------+------------+---------+------+ | 20210104 | 赵六 | 女 | 18 | 2 | +----------+----------+------------+---------+------+
-
八、连接查询
通过对DQL的学习，我们可以很轻松的从一张数据表中查询出所需要的数据，在企业的应用开发中，我们经常需要从多张表中查询数据（例如：我们查询学生信息时，需要同时查询学生班级信息）
可以通过连接查询从多张数据表格中查询数据：
在mysql中可以使用join关键字实现多表的联合查询——连接查询，join按照其功能不同分为三个操作：
- inner join 内连接
- left join 左连接
- right join 右连接
8.1数据准备
8.1.1创建数据表
班级信息表和学生信息表
CREATE TABLE classes(
class_id int PRIMARY KEY auto_increment,
class_name VARCHAR(40) not NULL UNIQUE,
class_remark VARCHAR(200)
);
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_CLASSES FOREIGN KEY(cid) REFERENCES classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE
);
8.1.2添加数据
添加班级信息
INSERT into classes(class_name, class_remark) VALUES('Java2104','...');
INSERT into classes(class_name, class_remark) VALUES('Java2105','...');
INSERT into classes(class_name, class_remark) VALUES('Java2106','...');
INSERT into classes(class_name, class_remark) VALUES('Python2104','...');
添加学生信息
INSERT into students(stu_num,stu_name,stu_gender,stu_age,cid)
VALUES('20210101','张三','男',20,1);
INSERT into students(stu_num,stu_name,stu_gender,stu_age,cid)
VALUES('20210102','李四','女',20,1);
INSERT into students(stu_num,stu_name,stu_gender,stu_age,cid)
VALUES('20210103','王五','男',20,1);
INSERT into students(stu_num,stu_name,stu_gender,stu_age,cid)
VALUES('20210104','赵柳','女',20,2);
INSERT into students(stu_num,stu_name,stu_gender,stu_age,cid)
VALUES('20210105','孙7','男',20,2);
#小红和小明没有设置班级信息
INSERT into students(stu_num,stu_name,stu_gender,stu_age)
VALUES('20210106','小红','女',20);
INSERT into students(stu_num,stu_name,stu_gender,stu_age)
VALUES('20210107','小明','男',20);
8.2内连接 inner join
语法
select ... from tableName1 inner join tableName2;
8.2.1笛卡尔积
-
笛卡尔积（A集合&B集合）：使用A中的每个记录依次关联B中的每个记录，笛卡尔积的总数=A总数*B总数
-
如果直接执行 select ... from tableName1 inner join tableName2; 会获得两种数据表中的数据集合的迪卡尔积（依次使用tableName1表中的每一条记录取匹配tableName2的每条数据）
8.2.2内连接条件
两张表使用inner join连接查询后产生的笛卡尔积数据中很多数据都是无意义的，我们如何消除无意义的数据呢——添加两张表进行连接查询词的条件
-
使用ON关键字设置两张表连接查询的匹配条件
-- 使用where 连接过滤条件，先生成笛卡尔积再从笛卡尔积中过滤数据（效率低） select * FROM students INNER JOIN classes WHERE students.cid = classes.class_id; -- 使用ON 设置连接条件；先判断连接条件是否成立，如果成立两张表的数据进行组合生成一条结果 select * FROM students INNER JOIN classes ON students.cid = classes.class_id; -
结果：只获取两张表中匹配条件成立的数据，任何一张表在另一张表中如果没有找到对应匹配则不会出现查询结果中（例如：小红和小明没有对应的班级信息，Java2016和python2106没有对应学生）
8.3左连接 LEFT JOIN
需求：查询出所有的学生信息，如果学生有对应的班级信息，则将对应的班级信息也查询出来
左连接：显示左表中的所有数据，如果在右表中存在与左表记录满足匹配条件的数据则进行匹配；如果右表中不存在匹配数据，则显示为NULL
-- 语法：
select * FROM leftTable LEFT JOIN rightTable ON 匹配条件 {where 条件}；
-- 左连接：显示左表中的所有数据
select * FROM students LEFT JOIN classes ON students.cid = classes.class_id;
8.4右连接 RIGHT JOIN
-- 右连接：显示右表中的所有数据
select * FROM students Right JOIN classes ON students.cid = classes.class_id;
8.5数据表别名
如果在连接查询的多张表中存在相同名字的字段，我们可以使用 表名、字段名来进行区分，如果表名太长则不使用SQL语句的编写，我们可以使用数据表别名
使用示例：
select s.*,c.class_name
FROM students s
iNNER JOIN classes c
ON s.cid = c.class_id;
8.6子查询/嵌套查询
子查询-先进行一次查询，第一次查询的结果作为第二次查询的源/条件（第二次查询时基于第一次查询的结果来进行的）
8.6.1子查询返回单个值
案例1:查询班级名称为'Java2104'班级中的学生信息（只知道班级名称，而不知道班级ID）
-
传统方式：
-- a.查询Java2104班的班级编号 SELECT class_id from classes where class_name='java2104'; -- b.查询此编辑编号下的学生信息 select * from students where cid=1; -
子查询：
-- 如果子查询返回的结果是一个值（单行单列），条件可以直接使用关系运算符（=，！=，.....） select * FROM students where cid= (SELECT class_id from classes where class_name='java2105');
8.6.2子查询返回多个值多行单列
案例2：查询所有Java班级中的学生信息
-
传统方式：
-- 查询所有Java班级中的学生信息 -- a.查询所有JAVA班级中的班级编号 select class_id from classes where class_name LIKE 'Java%'; +----------------+ | classs_id | +----------------+ | 1 | | 2 | | 3 | +----------------+ -- b.查询这些班级编号中的学生信息(UNION 将多个查询语句的结果拼接在一起) select * from students where cid=1 UNION select * from students where cid=2 UNION select * from students where cid=3; -
子查询
-- 如果子查询返回的结果是多个值（单列多行），条件使用IN / not in select * from students where cid IN (select class_id from classes where class_name LIKE 'Java%');
8.6.3子查询返回多个值，多行多列
案例3：查询cid=1的班级中性别为男的学生信息
-- 多条件查询：
select * from students where cid=1 and stu_gender='男';
-- 子查询:先查询cid=1班级中的所有学生信息，将这些作为一个整体虚拟表（多行多列）
-- 再基于这个虚拟表查询性别为男的学生信息（’虚拟表需要别名‘）
SELECT * from (SELECT *from students where cid=1) t where stu_gender='男';
九、存储过程
9.1存储过程介绍
9.1.1SQL指令执行过程
从SQL执行的流程中我们分析存在的问题：
1.如果我们需要多次执行相同的SQL，SQL执行都需要通过连接传递到MYSQl，并且需要经过编译和执行的步骤；
2.如果我们需要连续执行多个SQL指令，并且第二个SQL指令需要使用第一个SQL指令执行的结果为参数；
9.1.2存储过程的介绍
存储过程：
将能够完成特定功能的SQL指令进行封装（SQL指令集），编译后存储再数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用SQL指令集，获取执行结果.
9.2创建存储过程
9.2.1存储过程创建语法
-- 语法
creat PROCEDURE <proc_name>([In/Out args])
BEGIN
-- SQL
END;
9.2.2示例
-- 存储过程：就是能够完成特定功能的SQl指令进行封装
-- 创建一个存储过程实现加法运算 Java 语法中，方法是由参数和返回值的
-- 存储过程中，是有输入参数和输出参数的
create PROCEDURE proc_test1(IN a INT,IN b INT,OUT c INT)
BEGIN
SET c =a+b;
END;
9.3调用存储过程
-- 调用存储过程
-- 定义变量@m
set @m=0;
-- 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
SELECT @m from ;
9.4存储过程中变量的使用
存储过程中的变量分为两种，**局部变量 ** 和 用户变量
9.4.1局部变量
局部变量：定义在存储过程中的变量，只能在存储过程中使用
- 局部变量定义语法
-- 局部变量定义在存储过程中，而且必须定义在存储过程中开始的位置
declare <attr_name> <type> [default values]
- 局部变量定义示例
-- 创建一个存储过程：计算输入参数的平方与 输入参数/2之和
CREATE PROCEDURE proc_test2(IN a int,Out r int)
BEGIN
-- 定义x int类型 ，默认值为0
-- 定义 y int类型 ，默认值为0
DECLARE x INT DEFAULT 0;
DECLARE y INT DEFAULT 1;
set x = a*a;
SET y = a/2;
SET r = x+y;
end;
9.4.2用户变量
用户变量：相当于全局变量，定义的用户变量可以通过'select @attrName from '进行查询
-- 用户变量会存储在mysql数据库的数据字典中（）
-- 用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1
9.4.3给变量设置值
- 无论是局部变量还是用户变量，都使用set关键字修改值
SET @n=1;
call proc_test2(6,@n);
SELECT @n FROM ;
9.4.4将查询结果赋值给变量
在存储过程中使用select ...into ..给变量赋值
-- 查询学生数量
-- 创建存储过程：查询学生数量
CREATE PROCEDURE proc_test3(Out c INT)
BEGIN
SELECT COUNT(stu_num) INTO c from students; -- 将查询到的学生数量赋值给c
end;
-- 调用存储过程
call proc_test3(@n);
SELECT @n from ;
9.4.5用户变量使用注意事项
因为用户变量相当于全局变量，可以在SQl指令以及多个存储过程中共享，在开发过程中建议尽量少使用用户变量
因为用户变量过多会导致不易理解，难以维护
9.5存储过程的参数
MYSQL存储过程的参数一共有3种：IN/OUT/INOUT
9.5.1IN输入参数
输入参数——在调用过程中传递数据给存储过程的参数（在调用过程必须为具有实际值的变量或者 字面值）
-- 创建存储过程；添加学生信息
CREATE PROCEDURE Proc_test4(IN snum CHAR(8), in sname VARCHAR(20),in gender CHAR(2),IN age INT,in cid int,in remark VARCHAR(255))
BEGIN
INSERT into students(stu_num,stu_name,stu_age,stu_gender,cid,remark)
VALUES(snum,sname,age,gender,cid,remark);
end;
call proc_test4('20210108','小丽','女','20',1,'aaa');
9.5.2Out输出参数
输出参数——将存储过程中产生的数据返回给过程的调用者，相当于java方法的返回值，但不同的是一个存储过程可以有多个输出参数
-- 创建存储过程，根据学生学号，查询学生姓名
CREATE PROCEDURE proc_test5(in snum char(8),OUT sname VARCHAR(20))
BEGIN
SELECT stu_name INTO sname from students where stu_num=snum;
end;
SET @name='';
call proc_test5('20210108',@name);
SELECT @name from ;
9.5.3InOut输入输出参数
CREATE PROCEDURE proc_test6(INOUT str varchar(20))
BEGIN
SELECT stu_name INTO str from students where stu_num=str;
end;
SET @name='20210108';
call proc_test6(@name);
SELECT @name from ;
9.6存储过程中的流程控制
在存储过程中支持流程控制语句于实现逻辑的控制
9.6.1分支语句
-
if=then-else
-- 单分支，如果条件成立则执行SQL if conditions THEN -- SQL end if; -- 如果参数a的值为1，则添加一条班级信息 CREATE PROCEDURE proc_test7(IN a int) BEGIN if a=1 THEN INSERT into classes(class_name,remark) VALUES('Java2109','test'); end if; end;-- 双分支,如果条件成立则执行SQL1，否则执行SQL2 if a=1 THEN -- SQL1 ELSE -- SQL2 end if; -- 如果参数a的值为1，则添加一条班级信息；否则添加一条学生信息 CREATE PROCEDURE proc_test7(IN a int) BEGIN if a=1 THEN INSERT into classes(class_name,remark) VALUES('Java2109','test'); ELSE insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) values('20210110','小花','女','19',1,'...'); end if; end; -
case
-- CASE
CREATE PROCEDURE proc_test8(In a int)
BEGIN
case a
when 1 THEN
-- SQL1 如果a的值为1，则执行SQL1
INSERT into classes(class_name,remark) VALUES('Java2110','wwwaahhh');
when 2 THEN
-- SQL2 如果a的值为2，则执行SQl2
insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)
values('20210111','小刚','男','21',2,'...');
ELSE
-- SQL（如果变量值和所有when值都不匹配，则执行else中的SQL）
UPDATE students set stu_age=18 where stu_num='20210110';
end case;
end;
9.6.2循环语句
-
while
-- 创建一个存储过程:添加参数指定格式的班级信息 CREATE procedure proc_test9(IN num int) begin DECLARE i int; set i = 0; WHILE i<num DO -- SQL insert into classes(class_name,remark) values(CONCAT('Java',i),'....'); set i =i+1; end while; end; call proc_test9(4);
-
repeat
-- REPEAT CREATE PROCEDURE proc_test10(In num int) BEGIN DECLARE i int; set i=1; repeat -- SQL insert into classes(class_name,remark) values(CONCAT('Python',i),'....'); set i=i+1; UNTIL i>num end repeat; end; call proc_test10(4);
-
loop
-- LOOP create procedure proc_test11(In num int) BEGIN declare i int; set i =0; myloop:LOOP -- SQL insert into classes(class_name,remark)values(CONCAT('html',i),'....'); set i= i+1; if i = num then leave myloop; end if; end loop; end; call proc_test11(5);
9.7存储过程管理
9.7.1查询存储过程
存储过程是属于数据库的，也就是说当我们将存储过程创建在某个数据库之后，只能在当前数据库中调用此存储过程。
查询存储过程：查询某个数据库中有哪些存储过程
-- 根据数据库名，查询当前数据库中的存储过程
show procedure STATUS where db='db_test2';
-- 查询存储过程的创建细节
show create procedure db_test2.proc_test1;
9.7.2修改存储过程
修改存储过程主要指的是修改存储过程的特性/特征
alter procedure <proc_Name> 特征1 [特征2 特征3 ...]
存储过程的特征参数
-
CONTAINS SQL 表示子程序包含SQL语句，但不包含读或写数据的语句
-
NO SQL 表示子程序中不包含SQL语句
-
READS SQL DATA 表示子程序中包含数据的语句
-
MODIFIES SQL DATA’表示子程序中包含写数据的语句
-
SQL SECURITY {DEFINER |INVOKER}指明谁有权限来执行
- INVOKER表示只有定义者才能够执行
- DEFINER表示只有定义者自己才能够执行
-
COMMENT‘string’表示注释信息
ALTER PROCEDURE proc_test1 READS SQL DATA;
9.7.3删除存储过程
-- 删除存储过程
-- drop 删除数据库中的对象、数据库、数据表、列、存储过程、视图、触发器、索引...
-- delete 删除数据表中的数据
drop PROCEDURE proc_test1;
9.8存储过程的练习案例
使用存储过程解决企业项目开发过程中的问题
案例：使用存储过程完成借书操作
9.8.1数据准备
-- 创建数据库
create database db_test3;
-- 使用数据库
use db_test3;
-- 创建图书信息表；
create table books(
book_id int primary key auto_increment,
book_name varchar(50) not null,
book_author varchar(20) not null,
book_price decimal(10,2) not null,
book_stock int not null,
book_desc varchar(200)
);
-- 添加图书信息
insert into books(book_name,book_author,book_price,book_stock,book_desc)
VALUES ('Java程序设计','亮亮',38.80,12,'亮亮老师带你学Java');
insert into books(book_name,book_author,book_price,book_stock,book_desc)
VALUES ('Java王者之路','威哥',40.40,9,'千峰威哥，Java王者引路人');
-- 创建学生信息
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
);
-- 添加学生信息
INSERT into students(stu_num,stu_name,stu_gender,stu_age)
VALUES('1001','张三','男',20);
INSERT into students(stu_num,stu_name,stu_gender,stu_age)
VALUES('1002','李四','女',20);
INSERT into students(stu_num,stu_name,stu_gender,stu_age)
VALUES('1003','王五','男',20);
- 业务分析
创建存储过程实现借书操作：那个学生借那本书，借了多少本？
操作：
- 保存借书记录
- 修改图书库存
条件：
- 判断学生是否存在？
- 判断图书是否存在、库存是否充足？
- 创建借书记录表
-- 借书记录表：
create table records(
rid int PRIMARY key auto_increment,
snum char(4) not null,
bid int not null,
borrow_num int not null,
is_return int not null, -- 0表示未归还，1表示已经归还
borrow_date date not null,
constraint FK_RECORDS_STUDENTS FOREIGN key(snum) references students(stu_num),
constraint FK_RECORDS_BOOKS FOREIGN key(bid) references books(book_id)
);
9.8.2 创建存储过程
-- 实现借书业务：
-- 参数1：a 输入参数 学号
-- 参数2：b 输入参数 图书编号
-- 参数3：m 输入参数 借书的数量
-- 参数4：state 输出参数 借书的状态（1：借书成功、2：学号不存在、3：图书不存在、4：库存不足）
create procedure Proc_borrow_book(IN a char(4),IN b int,In m int, out state int)
BEGIN
declare stu_count int default 0;
declare book_count int default 0;
declare stock int default 0;
-- 判断学号是否存在：根据参数a到学生信息表查询是否有stu_num=a记录
select count(stu_num) Into stu_count from students where stu_num=a;
if stu_count>0 THEN
-- 学号存在
-- 判断图书ID是否存在：根据参数b 查询图书记录总数
SELECT count(book_id) into book_count from books where book_id=b;
if book_count >0 THEN
-- 图书存在
-- 判断图书库存是否充足；查询当前图书库存，然后和参数m进行比较
select book_stock into stock from books where book_id=b;
IF stock >=m THEN
-- 执行借书
-- 操作1：在借书记录表中添加记录
insert into records(snum,bid,borrow_num,is_return,borrow_date)
values(a,b,m,0,sysdate());
-- 操作2:修改图书库存
update books set book_stock=stock-m where book_id=b;
-- 借书成功
set state =1;
ELSE
-- 库存不足
set state =4;
end if;
ELSE
-- 图书不存在
set state =3;
end if;
ELSE
-- 学号不存在
set state = 2;
end if;
end;
-- 调用存储过程借书
set @state=0;
call proc_borrow_book('1001',1,2,@state);
select @state from ;
不存在学号1005返回state值2
学号存在1001返回state值1
图书编号为3的不存在state返回3
借20本书，图书数量不足state返回4
9.8.3创建存储过程实现还书业务
....还没做
9.9游标
问题：如果我们要创建一个存储过程，需要返回调查语句查询的多条数据，该如何实现呢？
9.9.1游标的概念
游标可以用来依次取出查询结果中的每一条数据——逐条读取查询结果集中的记录
9.1.2游标的使用步骤
1.声明游标
- 声明游标语法
declare cursor_name CURSOR for select_statement;
- 示例
declare mycursor cursor for select book_name,book_author,book_price from books ;
2.打开游标
- 语法
open mycursor;
3.使用游标
- -- 使用游标；提取游标当前指向记录（提取之后，游标自动下移）
fetch mycursor into bname,bauthor,bprice;
4.关闭游标
close mysursor;
示例1 ——未使用游标的调用
create procedure Proc_test1(IN id int,Out result varchar(100))
BEGIN
declare bname varchar(20);
declare bauthor varchar(20);
declare bprice decimal(10,2);
select book_name,book_author,book_price into bname,bauthor,bprice from books where book_id=id;
set result = concat_ws('~',bname,bauthor,bprice);
end;
set @r= '';
call proc_test1(1,@r);
select @r from ;
示例2——使用游标的调用
create procedure Proc_test2(Out result varchar(200))
BEGIN
declare bname varchar(20);
declare bauthor varchar(20);
declare bprice decimal(10,2);
declare num int;
declare i int;
declare str varchar(50);
-- 此查询语句返回的是一个结果集（多条记录），使用游标可以遍历查询结果集
declare mycursor cursor for select book_name,book_author,book_price from books;
select count(1) into num from books;
-- 打开游标
-- 打开游标
open mycursor;
-- 使用游标要结合循环语句
set i=0;
while i<num DO
-- 使用游标；提取游标当前指向记录（提取之后，游标自动下移）
fetch mycursor into bname,bauthor,bprice;
set i= i+1;
-- set str=concat_ws('~',bname,bsuthor,bprice);
select concat_ws('~',bname,bauthor,bprice) into str;
set result = concat_ws(',',result,str);
end while;
-- 关闭游标
close mycursor;
end;
set @r='';
call proc_test2(@r);
select @r from ;
十、触发器
10.1触发器的介绍
触发器：就是一种特殊的存储过程。触发器和存储过程一样是一段能够完成特定功能，存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据表中当数据执行DML操作时自动触发SQL片段执行，无需手动调用
在mysql中只有执行insert\delete\update操作才能触发触发器
10.2触发器的使用
10.2.1案例说明
-- 学生信息表
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
);
-- 学生信息操作日志
CREATE table stulogs(
id int PRIMARY key auto_increment,
time TIMESTAMP,
log_text varchar(200)
);
-- 当向students表中添加学生信息时，同时要在 stulogs表中添加一条操作日志
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1004','小丽','女',20);
-- 手动记录日志
insert into stulogs(time,log_text) values(now(),'添加1004学生信息');
案例：当向学生信息表添加、删除、修改学生信息时，使用触发器自动进行记录
10.2.2创建触发器
语法
create trigger tri_name
<before | after> -- 定义触发时机
<insert|delete|update> -- 定义DML类型
ON <table_name>
for each row -- 声明为行级触发器（只要操作一条记录就触发触发器执行一次）
sql_statement -- 触发器操作
-- 创建触发器，当学生信息表发生添加操作时，则向日志信息表中记录一条日志
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(),concat('添加','NEW_stu_num','学生信息'));
10.2.3查看触发器
-- 查看触发器
show triggers;
10.2.4测试触发器
- 我们创建的触发器是在students表发生insert 操作时触发，我们只需要执行学生信息的添加操作
-- 测试1：添加一条学生信息，触发器触发一次
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1005','小明','男',20);
-- 测试2：一条SQL指令添加了2条学生信息，触发器执行两次
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1006','小刚','男',20),('1007','李磊','男',20);
10.2.5删除触发器
-- 删除触发器
drop trigger tri_test1;
10.3NEW与OLD
触发器用于监听对数据表中的insert、delete、update操作，在触发器中通常处理一些DML的关联操作；
我们可以使用’NEW‘和’OLD‘关键字在触发器中获取触发这个触发器的DML操作数据
- NEW：在触发器中用于获取insert操作添加的数据，update操作修改后的记录
- OLD：在触发器中用于获取delete操作删除前的数据，update操作修改前的数据
10.3.1NEW
- insert操作中：NEW表示修改后的数据
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(),concat('添加','NEW.stu_num','学生信息'));
- update操作中：NEW表示修改后的数据
-- 创建触发器:在监听update操作的触发器中，可以使用NEW获取修改后的数据
create trigger tri_test2
after update on students for each ROW
insert into stulogs(time,log_text) values(now(),concat('修改学生信息为',NEW.stu_num,NEW.stu_name));
10.3.2OLD
- 删除操作中：OLD表示删除的记录
create TRIGGER tri_test3
after delete on students for each row
insert into stulogs(time,log_text) values(now(),concat('删除',OLD.stu_num,'学生信息'));
- update操作中：OLD表示修改前的记录
drop TRIGGER tri_test2;
create trigger tri_test2
after update on students for each ROW
insert into stulogs(time,log_text) values(now(),concat('将学生姓名从',OLD.stu_name,'修改为',NEW.stu_name));
10.4触发器使用总结
10.4.1优点
- 触发器自动执行的，当对触发器相关的表执行相应的DML操作时立即执行；
- 触发器可以实现表中数据的级联操作（关联操作），有利于保证数据的完整性；
- 触发器可以对DML操作的数据进行更为复杂的合法性校验
10.4.2缺点
- 使用触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难；
- 大量使用触发器容易导致代码结构杂乱，增加了程序复杂性；
- 当触发器操作的数据量比较大时，执行效率会大大降低。
10.4.3使用建议
- 在互联网项目中，应避免使用触发器
- 对于并发量不大的项目可以选择存储过程，单互联网引用中不提倡使用存储过程（原因：存储过程时将实现业务的逻辑交给数据库处理，一则增减了数据库负载，二则不利于数据库的迁移）
十一、视图
11.1视图的概念
视图：就是由数据库中一张表或多张表根据特定的条件查询出的数据构造成的虚拟表
11.2视图的作用
- 安全性：如果我们直接将数据表授权给用户操作，那么用户可以CRUD数据表中所有数据，加入我们想要对数据表中的部分数据进行保护，可以将公开数据生成视图，授权用户访问视图；用户通过查询视图可以获取数据表中公开数据，从而达到将数据表中的部分数据对用户隐藏
- 简单性：如果我们需要查询的数据来源于多张数据表，可以使用多表连接查询来实现；我们通过视图将这些连表查询的结果对用户开放，用户可以直接查询视图获取多表数据，操作更加便捷
11.3创建视图
11.3.1语法
create view <view_name>
AS
select_statement
11.3.2实例
-- 示例1：创建视图 ：将学生表中性别为男的学生生成一个视图
CREATE view view_test1
AS
select * from students where stu_gender='男';
-- 查询视图
select * from view_test1;
- 示例2：
-- 创建视图的示例2：查询学生借书信息（学生名、图书名、借书数量）
create view view_test2
AS
select s.stu_name,b.book_name,borrow_num
from books b inner join records r INNER JOIN students s
on b.book_id=r.bid and r.snum=s.stu_num;
-- 查询视图
SELECT * from view_test2;
11.4视图数据的特性
视图是虚拟表，查询视图的数据是来源于数据表的，当对视图进行操作时，对原数据表中的视图是否有影响呢？
查询操作：在数据表中添加了数据，而且满足这个数据满足创建视图时查询语句条件，通过查询视图也可以查询出新增数据；当删除原表中满足查询条件的数据时，也会从视图中删除
新增数据：如果在视图中添加数据，数据会被添加到原数据表
删除数据：如果从视图删除数据，数据也将从原表中删除
修改数据：如果通过视图修改数据，则也将修改原表中的数据
-- 在视图中新增数据
insert into view_test1 VALUES('1010','王大帅','男',18);
-- 从视图中删除数据
delete from view_test1 where stu_num='1010';
-- 从视图中修改数据
update view_test1 set stu_name='小刚'where stu_num='1006';
视图使用建议：对复杂查询简化操作，并且不会对数据进行修改的情况下可以使用视图
11.5查询视图结构
-- 查询视图结构
desc view_test2;
11.6修改视图
-- 方式一修改创建替换原视图
CREATE OR REPLACE view view_test1
AS
select * from students where stu_gender='女';
-- 方式二使用alter修改
alter view view_test1
AS
select * from students where stu_gender='男';
11.7删除视图
- 删除数据表时同时会删除数据表中的数据，删除视图时不会影响原数据表中数据
-- 删除视图
drop view view_test2l;
十二、索引
数据库是用来存储数据，在互联网应用中数据库中存储的数据可能很多（大数据），数据表中数据的查询速度会随着数据量的增长逐渐变慢，从而导致相应用户请求的数据变慢——用户体验差，我们如何提高数据的查询效率呢
12.1索引的介绍
索引，就是用来提高数据表中数据的查询效率的。
索引，就是将数据表中某一列./某几列构造成便于查找的结构进行存储，生成数据表的目录
当我们进行数据查询时，先在目录进行查找的到对应的数据地址，然后再到数据表中根据地址快速获取记录，避免全表扫描
12.2索引的分类
mysql中的索引，根据创建索引的列的不同分为
- 主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key 修饰，每张表只能有一个主键
- 唯一索引：在数据表中的唯一索引（unique），此列的所有值只能出现一次，可以为null
- 普通索引：在普通字段上创建的索引，没有唯一性的限制
- 组合索引：两个以上字段联合创建的索引
说明
- 在创建数据表时，将字段声明为主键（添加主键约束），会主动在主键字段创建主键索引，
- 在创建数据表时，将字段声明为唯一键（添加唯一约束），会自动在唯一字段创建唯一索引，
12.3创建索引
12.3.1唯一索引
-- 创建唯一索引:创建唯一索引的值不能重复
--create unique index <index_name> on 表明；
create UNIQUE index index_test2 on tb_testindex(tid);
12.3.2普通索引
-- 创建普通索引:不要求创建索引的列的唯一性
--create index <index_name> on 表明（列名）；
create index index_test2 on tb_testindex(tid);
12.3.3组合索引
-- 创建组合索引
--create index <index_name> on 表名（列名1，列名2.。。。）；
create index index_test4 on tb_testindex(tid,name);
12.3.4全文索引
mysql5.6版本新增的索引，可以通过此索引进行全文检索操作，因为mysql全文检索不支持说中文，因此这个全文索引不被开发者关注，在应用开发中通常是通过索索引擎（数据库中间件）实现全文检索
create fulltext index <index_name> on 表名{字段名}；
12.4索引使用
索引创建完成后无需调用，当根据创建索引的列进行数据查询时，会自动使用索引
组合索引需要根据创建索引的所有字段进行查询时触发
- 在命令行窗口中可以查看查询语句的查询规划：
explain select * from tb_testindex where tid=250000\G;
12.5查看索引
-- 命令行
show create table tb_testindex\G;
-- 查询数据表的索引
show indexes from tb_testindex;
-- 查询索引
show keys from tb_testindex;
12.6删除索引
-- 删除索引:索引是建立在表的字段上的，不同的表中可能会出现相同名称的索引，因此删除索引时需要指定表名
drop index index_test3 on testindex;
12.7索引的使用总结
12.7.1优点
- 索引大大降低了数据库服务器在执行操作时扫描的数据，提高查询效率
- 索引可以避免服务器排序，将随机IO变成顺序IO
12.7.2缺点
- 索引时根据数据表列创建的，当数据表中的数据发生DML操作时，索引页需要更新
- 索引文件也会占用磁盘空间
12.7.3注意事项
- 数据表中数据不多时，全表扫描可能更快，不要使用索引
- 数据量大但是dML操作频繁，不建议使用索引
- 不要在数据重复高的列上创建索引（性别）
- 创建索引之后，要主机查询SQL语句的编写，避免索引失效
十三、数据库事务
13.1数据事务介绍
- 我们把完成特定的业务的多个数据库DML步骤操作称之为一个事务
- 事务，就是完成同一个业务的多个DML操作
-- 借书业务
-- 操作1：在借书记录中添加记录
insert into records(sum,bid,borrow_num,is_return,borrow_date) values('1007',4,2,0,SYSDATE());
-- 操作2：修改借书库存
update books set book_stock=book_stock-2 where book_id=4;
-- 事务
-- 转账业务； 张三给李四转账1000
-- 操作1 李四账号+1000
-- 操作2 张三账号-1000
13.2数据库事务特性
ACID：高频面试题
原子性（atomicity）:一个事务中多个DML操作，要么同时执行成功，要么同时执行失效
一致性（consistency）：事务执行之前和事务执行之后，数据库中数据是一致的，完全性和一致性不能被破坏
隔离性（isolation）:数据库允许多个事务同时执行（张三借Java书同时允许李四借Java书），多个并行的事务之间不能相互影响。
持久性（durability）：事务完成后，对数据的操作是永久的
13.3mysql事务管理
13.3.1自动提交与手动提交
- 在mysql，默认DML指令的执行是子弟哦那个提交的，当我们执行一个DML指令之后会自动同步到数据库中
13.3.2事务管理
开启事务，就是关闭自动提交
- 在开始事务第一个操作之前，执行 start trancsaction 开启事务
- 依次执行事务中的DML操作
- 如果在执行过程中任何位置出现异常，则执行rollback回滚事务
- 如果事务中所有DML操作执行都成功，则在最后执行 commit提交事务
-- 开启事务（关闭自动提交--手动提交）
start TRANSACTION;
-- 操作1：在借书记录中添加记录
insert into records(snum,bid,borrow_num,is_return,borrow_date) values('1007',4,2,0,SYSDATE());
-- select aaa;
-- 事务回滚（清除连接缓存中的操作）撤销当前事务已经执行的操作
-- ROLLBACK;
-- 操作2：修改借书库存
update books set book_stock=book_stock-2 where book_id=4;
-- 提交事务（将连接缓存中的操作写入数据文件）
COMMIT;
13.4事务隔离级别
数据库允许多个事务运行，多个事务之间是隔离的，相互独立；如果多个事务之间不相互隔离，并且操作同一数据时，可能会导致数据一致性被破坏。
MYSQL数据库事务隔离级别分为四个级别
- 读未提交（read uncommitted）：T2可以读取T1执行但未提交事务；可能会导致脏读
脏读 一个事务读取到了另一个事务中未提交的数据
- 读已提交（read committed）:T2只能读取T1已经提交的数据；可以避免脏读；可能会导致不可重复读（虚读）
不可重复读（虚读）：在同一事务中，两次查询操作读取到的数据不一致
例如：T2进行第一次查询之后在第二次查询之前，T1修改并提交了数据，T2进行第二次查询时读取到的数据和第一次查询读取到的数据不一致
- 可重复读（repeatable read）:T2执行第一次查询之后，在事务结束之前其他事务不得修改对应数据；避免了不可重复读（虚读），但可能会导致幻读
幻读：T2对数据表中的数据进行修改，然后查询，在查询之前T1向数据表中新增了一条数据，就导致T2以为修改了所有数据，但却查询出了与修改不一致的数据（T1事务·新增的数据）
- 串行化（serializable）；同时只允许一个事务对数据表进行操作；避免了脏读，虚读及幻读
| 隔离级别 | 脏读 | 不可重复读（虚读） | 幻读 |
|---|---|---|---|
| read uncommitted | √ | √ | √ |
| read committed | × | √ | √ |
| repeatable read | × | × | √ |
| serializable | × | × | × |
13.4.5设置数据库事务隔离级别
我们可以通过设置数据库默认的事务隔离级别来控制事务之间的隔离性；
通过客户端与数据库连接设置来设置事务间的隔离性（在应用程序中设置-spring）；
mysql数据库默认的隔离级别未——可重复读
- 查看mysql数据库默认的隔离级别
-- 在mysql8.0.3之前
select @@tx_isolation;
-- 在mysql8.0.3之后
select @@transaction_isolation;
- 设置mysql默认隔离级别
set session transaction isolation level ,<read committed>;
十四、数据库设计
MYSQL数据库作为数据存储的介质为应用系统提供数据存储服务，我们如何设计出合理的数据库、数据表以满足应用系统的数据存储需求
- 车库：是用来存储车辆的，车库都需要划分车位，如果不划分车位，车子杂乱无章的存放可能会导致车辆堵塞，同时也可能造成场地浪费——有限场地能够停放最多的车辆，同时方便每一辆车的出入
- 数据库：用来存放数据的，我们需要设计合理的数据表——能够完成数据的存储，同时能够方便提取应用系统所需的数据
14.1数据库设计流程
数据库是为应用系统服务的，数据库存储什么样的数据也是由应用系统决定的
当我们进行应用系统开发时，我们首先要明确应用系统的功能需求——软件系统的需求分析
-
1.根据应用系统的功能，分析数据实体（实体：就是要存储的数据对象）
- 电商系统：商品、用户、订单...
- 教务管理系统：学生、课程、成绩...
-
2.提取实体数据项（数据项：实体的属性）
- 商品（商品名称、商品图片、商品描述...）
- 用户（姓名、登录名、登录密码....）
-
3.根据数据库设计三范式视图的数据项 检查实体的数据向是否满足数据库设计三范式
- 如果实体的数据项不满足三范式，可能会导致数据冗余，从而引起数据维护困难，破坏数据一致性等问题
-
4.绘制E-R图（实体关系图，直观展示实体与实体之间的关系）
-
5.数据库建模
- 使用三线图进行数据表设计
- PowerDesigner
- PDMan
-
6.建库建表（编写SQL指令创建数据库、数据表）
-
7.添加测试数据，通过SQL测试
14.2数据设计案例
学校图书馆图书管理系统（借书）
14.2.1数据实体
- 学生
- 类别
- 图书
- 借书记录
- 管理员
14.2.2提取数据项
- 学生（学号、姓名、性别、年龄）
- 院系（院系编号、院系名称、院系说明）
- 类别（类别ID、类别名称、类别描述）
- 图书（图书ID、图书名称、图书作者、图书封面、图书价格、图书库存）
- 借书记录（记录ID、学号、图书编号、数量、是否归还、借书日期、还书日期）
- 管理员（管理员ID、管理员登录名、登录密码、）
- 员工（员工编号、员工姓名、员工联系方式手机、qq、邮箱）
14.2.3数据库设计三范式
第一范式：数据表中字段（列）不可再分（数据库中创建不出不满足第一范式的表）
改造如下将细分的列该为单独一列
第二范式：不存在非关键字段对关键字段的部分依赖
以下表不满足第二范式
将每个关键字段列出来\关键字段组合列出来、依次检查每个非关键字段
第三范式：不存在非关键字段之间的传递依赖
以下数据表不满足第三范式
将关键字段和被依赖的非关键字段分别作为主键，依次检查所有的非关键字段的依赖关系
14.2.4绘制E-R图
E—R（Entity-Relationship）实际关系图，用于直观的体现实体与实体之间的关系（一对一、一对多、多对一、多对多）
E-R图基本图例
14.2.5数据库建模（PD）
E-R图实际上就是数据库建模的一部分
- E-R 数据表设计 建库建表
- PowerDesigner建模工具 导出数据表
- PDMan建模工具
-
下载并安装PowerDesigner工具
-
powerdesigner使用
- 概念数据模型（选择workspace-右键new-conceptual data model）,就相当于E-R
- 逻辑数据模型（打开概念数据模型--tools-Generate logical Data model ）
- 物理数据模型（打开逻辑数据模型-tools-Generatephysical Data model-选择数据库类型及版本）
- 可以对物理数据模型进行微调
- 可以通过物理数据模型生成建库键表的SQL语句（在物理数据模型的窗口中-database工具条-generate database）
- 通过数据的管理工具执行SQL文件就可以完成数据表的创建
-
面向对象模型（打开概念数据模型/逻辑数据模型/物理数据模型——选择tools——Generate Object Orentited Model）
-
可以根据语言设置，生成实体类（Java）
-
如果想要借助于PD建模工具生成JAVA代码，创建概念模型的实体名、属性名，都要符合JAVA程序的命名规范
-
在企业项目开发中，我们通常不会使用建模工具来生成数据表
-
浙公网安备 33010602011771号