MySQL数据库课程笔记

Course:MySQL数据库

⼀、MySQL课程内容

1.1数据库介绍

数据库概念

术语介绍

1.2MySQL数据库

下载、安装、配置、卸载

MySQL客户端⼯具的安装及使⽤

1.3SQL结构化查询语⾔

什么是SQL

SQL操作数据(CRUD操作:添加、查询、修改、删除)

1.4SQL⾼级

存储过程

索引

触发器、视图

1.5数据库设计

数据库设计步骤

数据库设计范式

E-R图

PowerDesigner建模⼯具、PDMan

1.6数据库事务

什么是事务

事务特性ACID

事务隔离级别

事务管理

2.1数据库概念

数据库,就是存放数据的仓库

数据库(DataBase,简称DB)是⻓期存储在计算机内部有结构的、⼤量的、共享的数据集合。

⻓期存储:持久存储

有结构:

类型:数据库不仅可以存放数据,⽽且存放的数据还是有类型的

关系:存储数据与数据之间的关系

⼤量:⼤多数数据库都是⽂件系统的,也就是说存储在数据库中的数据实际上就是存储

在磁盘的⽂件中

共享:多个应⽤程序可以通过数据库实现数据的共享

2.2关系型数据库与⾮关系型数据库

关系型数据库

关系型数据库,采⽤了关系模型来组织数据的存储,以⾏和列的形式存储数据并记录数据与数据之间的关系——将数据存储在表格中,可以通过建⽴表格与表格之间的关联来维护数据与数据之间的关系。

学⽣信息----学⽣表

班级信息----班级表

⾮关系型数据库

⾮关系型数据库,采⽤键值对的模型来存储数据,只完成数据的记录,不会记录数据与数据之间的关系。

在⾮关系型数据库中基于其特定的存储结构来解决⼀些⼤数据应⽤的难题。NoSQL(NotonlySQL)数据库来指代⾮关系型数据库。

2.3常⻅的数据库产品

关系型数据库产品

MySQL免费

MariaDB

PerconaServer

PostgreSQL

Oracle收费

SQLServer

Access

Sybase

达梦数据库

⾮关系型数据库产品

⾯向检索的列式存储Column-Oriented

HaBase(Hadoop⼦系统)

BigTable(Google)

⾯向⾼并发的缓存存储Key-Value

MemcacheDB

⾯向海量数据访问的⽂档存储Document--Oriented

MongoDB

CouchDB

2.4数据库术语

数据库(Database):存储的数据的集合,提供数据存储的服务

数据(Data):实际上指的是描述事物的符号记录数据库管理系统(DatabaseManagementSystem,DBMS):数据库管理系统,是位于⽤

户与操作系统之间的⼀层数据管理软件

数据库系统管理员(DatabaseAnministrator,简称为DBA):负责数据库创建、使⽤及维

护的专⻔⼈员

数据库系统(DatabaseSystem,DBS):数据库系统管理员、数据库管理系统及数据库组

成整个单元

三、MySQL数据库环境准备

MySQL下载、安装、配置、卸载,安装DBMS、使⽤DBMS

3.1MySQL版本及下载

3.1.1版本

MySQL是Oracle的免费的关系型数据库,官⽹https://www.mysql.com/

MySQL⽬前的最新版本为8.0.26,在企业项⽬中主流版本:5.0---5.5---5.6---5.7

---8.0.26

5.x---2020年5.7.32

8.x---2018年8.0.11---2019年8.0.16---2021年8.0.26

MySQL8.x新特性

性能:官⽅8.x⽐5.7速度要快2倍

⽀持NoSQL存储:5.7开始提供了对NoSQL的⽀持,8.0.x做了更进⼀步的改进窗⼝函数

索引:隐藏索引、降序索引

可⽤性、可靠性

3.1.2下载

官⽹下载:https://dev.mysql.com/downloads/installer/

需要注册oracle

服务器在国外,下载速度....

镜像下载:https://www.filehorse.com/download-mysql-64/download/

3.2MySQL安装

傻⽠式(直接点击下⼀步)

选择DeveloperDefault模式安装

此模式会安装开发⼈员需要的常⽤组件;在安装这些组件时需要对应的环境依赖,我们要暂停,先去安装依赖的环境:

例如:MicrosoftVisualC++2019RedistributablePackage(x64)isnot

installed.Latestbinarycompatibleversionwillbeinstalledifagreedto

resolvethisrequirement.

安装:

选择自定义Custom安装

3.3MySQL配置

3.3.1端口配置

3.3.2账号密码设置

3.3.3服务名称

3.4MySQL服务的启动与停⽌

MySQL是以服务的形式运⾏在系统中

3.4.1计算机管理窗⼝

此电脑---右键---管理

3.4.2windows命令行

打开命令⾏:win+R---输⼊cmd回⻋

以管理员身份打开命令⾏:win+s----输⼊cmd----选择以管理员身份运⾏

3.5MySQL卸载

卸载软件

打开控制⾯板

点击“程序和功能”

卸载MySQL

删除⽬录

MySQL的安装⽬录:C:\ProgramFiles(x86)\MySQL

MySQL的数据⽂件⽬录(默认隐藏):C:\ProgramData\MySQL(如果不允许删除,强

制删除)

删除注册表

打开注册表:win+r---输⼊regedit---回⻋

删除HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL80删除搜索mysql的相关项(⾮必须)

四、MySQL的管理工具

当完成数据库的安装之后,mysql是以服务的形式运⾏在windows/linux系统,⽤户是通过DBMS⼯具来对MySQL进⾏操作的,当我们安装完成MySQL之后默认安装了mysqlCommcandlineClient,此⼯具是⼀个命令⾏形式的⼯具,通常我们会单独安装可视化的DBMS⼯具:

SQLyog

NavicatforMySQL

4.1MySQLCommandlineClient使⽤

打开MySQLCommandlineClient:开始菜单---MySQL---MySQL8.0Commandline

Client

连接MySQL:输⼊密码即可(如果密码错误或者mysql服务没有启动,窗⼝会闪退)

关闭MySQLCommandlineClient:输⼊exit指令回⻋即可退出

4.2可视化⼯具Navicat使⽤

4.2.1Navicat⼯具下载及安装

傻⽠式安装

4.2.2创建连接

打开navicat⼯具

创建连接:

五、MySQL逻辑结构

MySQL可以存储数据,但是存储在MySQL中的数据需要按照特定的结果进⾏存储学⽣------学校

数据------数据库

5.1逻辑结构

5.2记录/元组

六、SQL结构化查询语⾔

6.1SQL概述

SQL(StructuredQueryLanguage)结构化查询语⾔,⽤于存取、查询、更新数据以

6.1.1SQL发展

SQL是在1981年由IBM公司推出,⼀经推出基于其简洁的语法在数据库中得到了⼴泛的

应⽤,成为主流数据库的通⽤规范

在不同的数据库产品中遵守SQL的通⽤规范,但是也对SQL有⼀些不同的改进,形成了⼀

些数据库的专有指令

MySQL:limit

SQLServer:top

Oracle:rownum

6.1.2SQL分类

根据SQL指令完成的数据库操作的不同,可以将SQL指令分为四类:

DDLDataDefinitionLanguage数据定义语⾔

⽤于完成对数据库对象(数据库、数据表、视图、索引等)的创建、删除、修改

DMLDataManipulationLanguage数据操作/操纵语⾔

⽤于完成对数据表中的数据的添加、删除、修改操作

添加:将数据存储到数据表

删除:将数据从数据表移除

修改:对数据表中的数据进⾏修改

DQLDataQueryLanguage数据查询语⾔

⽤于将数据表中的数据查询出来

DCLDataControlLanguage数据控制语⾔

⽤于完成事务管理等控制性操作

6.2SQL基本语法

在MySQLCommandLineClient或者navicat等⼯具中都可以编写SQL指令

SQL指令不区分⼤⼩写

每条SQL表达式结束之后都以;结束

SQL关键字之间以空格进⾏分隔

SQL之间可以不限制换⾏(可以有空格的地⽅就可以有换⾏)

6.3DDL数据定义语⾔

6.3.1DDL-数据库操作

使⽤DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库

查询数据库

创建数据库

##创建数据库dbName表示创建的数据库名称,可以⾃定义

createdatabase<dbName>;

##创建数据库,当指定名称的数据库不存在时执⾏创建

createdatabaseifnotexists<dbName>;

##在创建数据库的同时指定数据库的字符集(字符集:数据存储在数据库中采⽤的编码格式utf8gbk)

createdatabase<dbName>charactersetutf8;

修改数据库修改数据库字符集

删除数据库删除数据库时会删除当前数据库中所有的数据表以及数据表中的数据

1

2

3

4

5##删除数据库

dropdatabase<dbName>;

##如果数据库存在则删除数据库

dropdatabaseisexists<dbName>;

使⽤/切换数据库

6.3.2DDL-数据表操作

创建数据表

数据表实际就是⼀个⼆维的表格,⼀个表格是由多列组成,表格中的每⼀类称之为表格的⼀个字段

1createtablestudents(

2stu_numchar(8)notnullunique,

3stu_namevarchar(20)notnull,

4stu_genderchar(2)notnull,

5stu_ageintnotnull,

6stu_telchar(11)notnullunique,

7stu_qqvarchar(11)unique

8);

查询数据表

查询表结构

删除数据表

1

2

3

4

5##删除数据表

droptable<tableName>;

##当数据表存在时删除数据表

droptableifexists<tableName>;

修改数据表

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17##修改表名

altertable<tableName>renameto<newTableName>;

##数据表也是有字符集的,默认字符集和数据库⼀致

altertable<tableName>charactersetutf8;

##添加列(字段)

altertable<tableName>add<columnName>varchar(200);

##修改列(字段)的列表和类型

altertable<tableName>change<oldColumnName><newCloumnName><type>;

##只修改列(字段)类型

altertable<tableName>modify<columnName><newType>;

##删除列(字段)

altertablestusdrop<columnName>;

6.4MySQL数据类型

数据类型,指的是数据表中的列中⽀持存放的数据的类型

6.4.1数值类型

在mysql中有多种数据类型可以存放数值,不同的类型存放的数值的范围或者形式是不同的

类型内存空间⼤⼩范围说明

tinyint

1byte有符号-128~127

⽆符号0~255

特⼩型整数(年龄)

smallint2byte(16bit)有符号-32768~32767⽆符号0~65535

⼩型整数

mediumint

3byte有符号-2^31~2^31-1⽆符号0~2^32-1

中型整数

int/integer4byte整数

bigint8byte⼤型整数

float4byte单精度

double8byte双精度

decimal

第⼀参数+2decimal(10,2)

表示数值⼀共有10位⼩数位有2位

6.4.2字符串类型

存储字符序列的类型

类型字符⻓度说明

char

0~255字节定⻓字符串,最多可以存储255个字符;当我们指定数据表字段为char(n)

此列中的数据最⻓为n个字符,如果添加的数据少于n,则补'\u0000'⾄n⻓度

varchar0~65536字节可变⻓度字符串,此类型的类最⼤⻓度为65535

tinyblob0~255字节存储⼆进制字符串

blob0~65535存储⼆进制字符串

mediumblob0~1677215存储⼆进制字符串

longblob0~4294967295存储⼆进制字符串

tinytext0~255⽂本数据(字符串)

text0~65535⽂本数据(字符串)

mediumtext0~1677215⽂本数据(字符串)

longtext0~4294967295⽂本数据(字符串)

6.4.3⽇期类型

在MySQL数据库中,我们可以使⽤字符串来存储时间,但是如果我们需要基于时间字段

类型格式说明

date2021-09-13⽇期,只存储年⽉⽇

time11:12:13时间,只存储时分秒

year2021年份

datetime2021-09-1311:12:13⽇期+时间,存储年⽉⽇时分秒

timestamp20210913111213⽇期+时间(时间戳)

6.5字段约束

6.5.1约束介绍

在创建数据表的时候,指定的对数据表的列的数据限制性的要求(对表的列中的数据进⾏限制)

为什么要给表中的列添加约束呢?

保证数据的有效性

保证数据的完整性

保证数据的正确性

字段常⻅的约束有哪些呢?

⾮空约束(notnull):限制此列的值必须提供,不能为null

唯⼀约束(unique):在表中的多条数据,此列的值不能重复

主键约束(primarykey):⾮空+唯⼀,能够唯⼀标识数据表中的⼀条数据

外键约束(foreignkey):建⽴不同表之间的关联关系

6.5.2非空约束

限制数据表中此列的值必须提供

创建表:设置图书表的book_namenotnull

添加数据:

6.5.3唯⼀约束

在表中的多条数据,此列的值不能重复

创建表:设置图书表的book_isbn为unique

1

2

3

4

5createtablebooks(

book_isbnchar(4)unique,

book_namevarchar(10)notnull,

book_authorvarchar(6)

);

添加数据:

6.5.4主键约束

主键——就是数据表中记录的唯⼀标识,在⼀张表中只能有⼀个主键(主键可以是⼀个列,也可以是多个列的组合)

当⼀个字段声明为主键之后,添加数据时:

此字段数据不能为null

此字段数据不能重复

创建表时添加主键约束

1

2

3

4

5createtablebooks(

book_isbnchar(4)primarykey,

book_namevarchar(10)notnull,

book_authorvarchar(6)

);

或者

1createtablebooks(

2book_isbnchar(4),

3book_namevarchar(10)notnull,

4book_authorvarchar(6),

5primarykey(book_isbn)

6);

删除数据表主键约束

创建表之后添加主键约束

1

2

3

4

5

6

7

8

9##创建表时没有添加主键约束

createtablebooks(

book_isbnchar(4),

book_namevarchar(10)notnull,

book_authorvarchar(6)

);

##创建表之后添加主键约束

altertablebooksmodifybook_isbnchar(4)primarykey;

6.5.5主键⾃动增⻓

在我们创建⼀张数据表时,如果数据表中有列可以作为主键(例如:学⽣表的学号、图书表的isbn)我们可以直接这是这个列为主键;

当有些数据表中没有合适的列作为主键时,我们可以额外定义⼀个与记录本身⽆关的列(ID)作为主键,此列数据⽆具体的含义主要⽤于标识⼀条记录,在mysql中我们可以将此列定义为int,同时设置为⾃动增⻓,当我们向数据表中新增⼀条记录时,⽆需提供ID列的值,它会⾃动⽣成。

定义主键⾃动增⻓

定义int类型字段⾃动增⻓:auto_increment

1

2

3

4

5createtabletypes(

type_idintprimarykeyauto_increment,

type_namevarchar(20)notnull,

type_remarkvarchar(100)

);

注意:⾃动增⻓从1开始,每添加⼀条记录,⾃动的增⻓的列会⾃定+1,当我们把某条记录删除之后再添加数据,⾃动增⻓的数据也不会重复⽣成(⾃动增⻓只保证唯⼀性、不保证连续性)

6.5.6联合主键

联合组件——将数据表中的多列组合在⼀起设置为表的主键

定义联合主键

注意:在实际企业项⽬的数据库设计中,联合主键使⽤频率并不⾼;当⼀个张数据表中没有明确的字段可以作为主键时,我们可以额外添加⼀个ID字段作为主键。

6.5.7外键约束

在多表关联部分讲解

6.6DML数据操纵语⾔

⽤于完成对数据表中数据的插⼊、删除、修改操作

1createtablestudents(

2stu_numchar(8)primarykey,

3stu_namevarchar(20)notnull,

4stu_genderchar(2)notnull,

5stu_ageintnotnull,

6stu_telchar(11)notnullunique,

7stu_qqvarchar(11)unique

8);

6.6.1插⼊数据

语法

示例

6.6.2删除数据

从数据表中删除满⾜特定条件(所有)的记录

语法

实例

1##删除学号为20210102的学⽣信息

2deletefromstuswherestu_num='20210102';3

4##删除年龄⼤于20岁的学⽣信息(如果满⾜where⼦句的记录有多条,则删除多条记录)

5deletefromstuswherestu_age>20;6

7##如果删除语句没有where⼦句,则表示删除当前数据表中的所有记录(敏感操作)

8deletefromstus;

6.6.3修改数据

对数据表中已经添加的记录进⾏修改

语法

示例

6.7DQL数据查询语⾔

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

单表查询

多表联合查询

6.7.1查询基础语法

6.7.2where⼦句

在删除、修改及查询的语句后都可以添加where⼦句(条件),⽤于筛选满⾜特定的添加的数据进⾏删除、修改和查询操作。

1

2

3deletefromtableNamewhereconditions;

updatetabeNameset...whereconditions;

select....fromtableNamewhereconditions;

条件关系运算符

1##=等于

2select*fromstuswherestu_num='20210101';3

4##!=<>不等于

5select*fromstuswherestu_num!='20210101';

6select*fromstuswherestu_num<>'20210101';7

8##>⼤于

9select*fromstuswherestu_age>18;

10

11##<⼩于

12select*fromstuswherestu_age<20;13

14##>=⼤于等于

15select*fromstuswherestu_age>=20;16

17##<=⼩于等于

18select*fromstuswherestu_age<=20;19

20##betweenand区间查询betweenv1andv2[v1,v2]

21select*fromstuswherestu_agebetween18and20;

在where⼦句中,可以将多个条件通过逻辑预算(andornot)进⾏连接,通过多个条件来筛选要操作的数据。

1##and并且筛选多个条件同时满⾜的记录

2select*fromstuswherestu_gender='⼥'andstu_age<21;3

4##or或者筛选多个条件中⾄少满⾜⼀个条件的记录

5select*fromstuswherestu_gender='⼥'orstu_age<21;6

7##not取反

8select*fromstuswherestu_agenotbetween18and20;

6.7.3LIKE子句

在where⼦句的条件中,我们可以使⽤like关键字来实现模糊查询

语法

在like关键字后的reg表达式中

%表示任意多个字符【%o%包含字⺟o】

_表示任意⼀个字符【_o%第⼆个字⺟为o】

示例

1

2

3

4

5

6

7

8

9

10

11#查询学⽣姓名包含字⺟o的学⽣信息

select*fromstuswherestu_name

#查询学⽣姓名第⼀个字为`张`的学⽣信息

select*fromstuswherestu_name

#查询学⽣姓名最后⼀个字⺟为o的学⽣信息

select*fromstuswherestu_name

#查询学⽣姓名中第⼆个字⺟为o的学⽣信息

select*fromstuswherestu_name

like'%o%';

like'张%';

like'%o';

like'_o%';

6.7.4对查询结果的处理

设置查询的列

声明显示查询结果的指定列

计算列

对从数据表中查询的记录的列进⾏⼀定的运算之后显示出来

1##出⽣年份=当前年份-年龄

2selectstu_name,2021-stu_agefromstus;3

4+-----------+--------------+

5|stu_name|2021-stu_age|

6+-----------+--------------+

7|omg|2000|

8|韩梅梅|2003|

9|Tom|2001|

10|Lucy|2000|

11|Polly|2000|

12|Theo|2004|

13+-----------+--------------+

as字段取别名

我们可以为查询结果的列名去⼀个语义性更强的别名(如下案例中as关键字也可以省略)

distinct消除重复行

distinct

1selectstu_agefromstus;2+---------+

3|stu_age|

4+---------+

5|21|

6|18|

7|20|

8|21|

9|21|

10|17|

11+---------+

12

13selectdistinctstu_agefromstus;14+---------+

15|stu_age|

16+---------+

17|21|

18|18|

19|20|

20|17|

21+---------+

6.7.5排序-orderby

将查询到的满⾜条件的记录按照指定的列的值升序/降序排列

语法

orderbycolumnName表示将查询结果按照指定的列排序

asc按照指定的列升序(默认)

desc按照指定的列降序

实例

21

20

21

17|

|

|

|13030303300

13030303302

13030303304

13232323322|

|

|

|NULL

777777

666666

NULL|

|

|

|

18|13131313311|999999|

21|13131323334|NULL|

+----------+-----------+------------+---------+-------------+--------+

#多字段排序:先满⾜第⼀个排序规则,当第⼀个排序的列的值相同时再按照第⼆个列的规则

排序

select*fromstuswherestu_age>15orderbystu_genderasc,stu_age

desc;

+----------+-----------+------------+---------+-------------+--------+

|stu_num|stu_name|stu_gender|stu_age|stu_tel|stu_qq|

+----------+-----------+------------+---------+-------------+--------+

|

|

21

21

20

17|

|

|

|13030303300

13030303304

13030303302

13232323322|

|

|

|NULL

666666

777777

NULL|

|

|

|

+----------+-----------+------------+---------+-------------+--------+

6.7.6聚合函数

SQL中提供了⼀些可以对查询的记录的列进⾏计算的函数——聚合函数

count

max

min

sum

avg

count()统计函数,统计满⾜条件的指定字段值的个数(记录数)

1#统计学⽣表中学⽣总数

2selectcount(stu_num)fromstus;3+----------------+

4|count(stu_num)|

5+----------------+

6|7|

7+----------------+

8

9#统计学⽣表中性别为男的学⽣总数

10selectcount(stu_num)fromstuswherestu_gender='男';11+----------------+

12|count(stu_num)|

13+----------------+

14|5|

15+----------------+

max()

计算最⼤值,查询满⾜条件的记录中指定列的最⼤值

1

2

3

4

5

6

7

8

9

10

11

12

13

selectmax(stu_age)fromstus;

+--------------+

|max(stu_age)|

+--------------+

|21|

+--------------+

selectmax(stu_age)fromstuswherestu_gender='⼥';

+--------------+

|max(stu_age)|

+--------------+

|21|

+--------------+

min()计算最⼩值,查询满⾜条件的记录中指定列的最⼩值

1

2

3

4

5

6

7

8

9

10

11

12

13

selectmin(stu_age)fromstus;

+--------------+

|min(stu_age)|

+--------------+

|14|

+--------------+

selectmin(stu_age)fromstuswherestu_gender='⼥';

+--------------+

|min(stu_age)|

+--------------+

|18|

+--------------+

sum()计算和,查询满⾜条件的记录中指定的列的值的总和

1

2

3

4

5

6

7

8

9

10

#计算所有学⽣年龄的综合

selectsum(stu_age)fromstus;

+--------------+

|sum(stu_age)|

+--------------+

|133|

+--------------+

#计算所有性别为男的学⽣的年龄的综合

selectsum(stu_age)fromstuswherestu_gender='男';

avg()求平均值,查询满⾜条件的记录中计算指定列的平均值

1selectavg(stu_age)fromstus;2+--------------+

3|avg(stu_age)|

4+--------------+

5|19.0000|

6+--------------+

7

8selectavg(stu_age)fromstuswherestu_gender='男';9+--------------+

10|avg(stu_age)|

11+--------------+

12|18.8000|

13+--------------+

6.7.7日期函数和字符串函数

日期函数

当我们向⽇期类型的列添加数据时,可以通过字符串类型赋值(字符串的格式必须为yyyy-MM-ddhh:mm:ss)

如果我们想要获取当前系统时间添加到⽇期类型的列,可以使⽤now()或者sysdate()示例:

char(11)|NO

varchar(11)|YES

datetime|YES

+---------------+-------------+------+-----+---------+-------+

13

#通过字符串类型给⽇期类型的列赋值

insertinto

stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)values('20200108','张⼩三','⼥',20,'13434343344','123111','2021-09-0109:00:00');

17

#通过now()获取当前时间

insertinto

stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)values('20210109','张⼩四','⼥',20,'13434343355','1233333',now());

21

#通过sysdate()获取当前时间

insertinto

stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)values('20210110','李雷','男',16,'13434343366','123333344',sysdate());

25

#通过now和sysdate获取当前系统时间

mysql>selectnow();

+---------------------+

|now()|

+---------------------+

|2021-09-1016:22:19|

+---------------------+

33

mysql>selectsysdate();

+---------------------+

|sysdate()|

+---------------------+

|2021-09-1016:22:26|

+---------------------+

字符串函数

就是通过SQL指令对字符串进⾏处理

示例:

2

3

4selectconcat(stu_name,'-',stu_gender)fromstus;

+---------------------------------+

|concat(stu_name,'-',stu_gender)|

5

6+---------------------------------+

|韩梅梅-⼥|

7|Tom-男|

8|Lucy-⼥|

9

10|林涛-男|

+---------------------------------+

11

12#upper(column)将字段的值转换成⼤写

13

14

15

16

17mysql>selectupper(stu_name)fromstus;

+-----------------+

|upper(stu_name)|

+-----------------+

|韩梅梅|

18|TOM|

19|LUCY|

20|POLLY|

21|THEO|

22

23|林涛|

+-----------------+

24

25#lower(column)将指定列的值转换成⼩写

26mysql>selectlower(stu_name)fromstus;

27

28

29

30+-----------------+

|lower(stu_name)|

+-----------------+

|韩梅梅|

31|tom|

32|lucy|

33|polly|

34

35

36|theo|

+-----------------+

37#substring(column,start,len)从指定列中截取部分显示start从1开始

38mysql>selectstu_name,substring(stu_tel,8,4)fromstus;

39

40

41

42+-----------+------------------------+

|stu_name|substring(stu_tel,8,4)|

+-----------+------------------------+

|韩梅梅|3311|

43|Tom|3302|

6.7.8分组查询-groupby

分组——就是将数据表中的记录按照指定的类进⾏分组

语法

select后使⽤*显示对查询的结果进⾏分组之后,显示每组的第⼀条记录(这种显示通常是⽆意义的)

select后通常显示分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)语句执⾏属性:先根据where条件从数据库查询记录groupby对查询记录进⾏分组执⾏having对分组后的数据进⾏筛选

示例

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

#先对学⽣按年龄进⾏分组(分了16、17、18、20、21、22六组),然后统计各组的学⽣数量,还可以对最终的结果排序

selectstu_age,count(stu_num)fromstusgroupbystu_ageorderby

stu_age;

+---------+----------------+

|stu_age|count(stu_num)|

+---------+----------------+

|

|

|

|

|

|16

17

18

20

21

22|

|

|

|

|

|2

1

1

3

1

1|

|

|

|

|

|

+---------+----------------+

#查询所有学⽣,按年龄进⾏分组,然后分别统计每组的⼈数,再筛选当前组⼈数>1的组,再按

年龄升序显示出来

selectstu_age,count(stu_num)

fromstus

groupbystu_age

havingcount(stu_num)>1

orderbystu_age;

+---------+----------------+

|stu_age|count(stu_num)|

+---------+----------------+

|

|16

20|

|2

3|

|

+---------+----------------+

#查询性别为'男'的学⽣,按年龄进⾏分组,然后分别统计每组的⼈数,再筛选当前组⼈数>1的组,再按年龄升序显示出来

mysql>selectstu_age,count(stu_num)

->fromstus

->wherestu_gender='男'

->groupbystu_age

->havingcount(stu_num)>1

->orderbystu_age;

+---------+----------------+

|stu_age|count(stu_num)|

+---------+----------------+

6.7.9分页查询-limit

当数据表中的记录⽐较多的时候,如果⼀次性全部查询出来显示给⽤户,⽤户的可读性/

语法

1

2

3

4select...

from...

where...

limitparam1,param2

param1int,表示获取查询语句的结果中的第⼀条数据的索引(索引从0开始)

param2int,表示获取的查询记录的条数(如果剩下的数据条数<param2,则返回剩下

的所有记录)

案例

对数据表中的学⽣信息进⾏分⻚显示,总共有10条数据,我们每⻚显示3条

总记录数count10

每⻚显示pageSize3

总⻚数:pageCount=count%pageSize==0?count/pageSize:count/pageSize+1;

七、数据表的关联关系

7.1关联关系介绍

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

数据与数据之间的关联关系分为四种:

⼀对⼀关联

⼀对多关联

多对⼀关联

多对多关联

7.2⼀对⼀关联

⼈---身份证⼀个⼈只有⼀个身份证、⼀个身份证只对应⼀个⼈

学⽣---学籍⼀个学⽣只有⼀个学籍、⼀个学籍也对应唯⼀的⼀个学⽣

⽤户---⽤户详情⼀个⽤户只有⼀个详情、⼀个详情也只对应⼀个⽤户

⽅案1:主键关联——两张数据表中主键相同的数据为相互对应的数据

⽅案2:唯⼀外键——在任意⼀张表中添加⼀个字段添加外键约束与另⼀张表主键关联,并且将外键列添加唯⼀约束

7.3⼀对多与多对⼀

班级---学⽣

学⽣---班级

图书---分类商品----商品类别

⽅案:在多的⼀端添加外键,与⼀的⼀端主键进⾏关联

7.4多对多关联

学⽣---课程⼀个学⽣可以选择多⻔课、⼀⻔课程也可以由多个学⽣选择

会员---社团⼀个会员可以参加多个社团、⼀个社团也可以招纳多个会员

⽅法:额外创建⼀张关系表来维护多对多关联——在关系表中定义两个外键,分别与两个数据表的主键进⾏关联

7.5外键约束

外键约束——将⼀个列添加外键约束与另⼀张表的主键(唯⼀列)进⾏关联之后,这个外键

1.先创建班级表

1createtableclasses(

2class_idintprimarykeyauto_increment,

3class_namevarchar(40)notnullunique,

4class_remarkvarchar(200)

5);

2.创建学⽣表(在学⽣表中添加外键与班级表的主键进⾏关联)

3.向班级表添加班级信息

insertintoclasses(class_name,class_remark)

values('Java2104','...');

insertintoclasses(class_name,class_remark)

values('Java2105','...');

insertintoclasses(class_name,class_remark)

values('Java2106','...');

insertintoclasses(class_name,class_remark)

values('Python2106','...');

select*fromclasses;

+----------+------------+--------------+

|class_id|class_name|class_remark|

+----------+------------+--------------+

|

|

|

|

+----------+------------+--------------+

7.6外键约束-级联

当学⽣表中存在学⽣信息关联班级表的某条记录时,就不能对班级表的这条记录进⾏修改ID和删除操作,如下:

mysql>select*fromclasses;

+----------+------------+--------------+

|class_id|class_name|class_remark|

+----------+------------+--------------+

|

被学⽣表中的记录关联了

|

class_id,并且不能删除

|3|Java2106

|4|Python2106

+----------+------------+--------------+

10

mysql>select*fromstudents;

+----------+----------+------------+---------+------+

|stu_num|stu_name|stu_gender|stu_age|cid|

+----------+----------+------------+---------+------+

|

|

|

|

+----------+----------+------------+---------+------+

20

mysql>updateclassessetclass_id=5whereclass_name='Java2104';

22

ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkey

constraintfails(`db_test2`.`students`,CONSTRAINT

`FK_STUDENTS_CLASSES`FOREIGNKEY(`cid`)REFERENCES`classes`

(`class_id`))

24

25

26

27

`classes`

如果⼀定要修改Java2104的班级ID,该如何实现呢?

将引⽤Java2104班级id的学⽣记录中的cid修改为NULL

在修改班级信息表中Java2104记录的class_id

将学⽣表中cid设置为NULL的记录的cid重新修改为Java2104这个班级的新的id

111updatestudentssetcid=NULLwherecid=1;#结果如下:

+----------+----------+------------+---------+------+

|stu_num|stu_name|stu_gender|stu_age|cid|

+----------+----------+------------+---------+------+

|

|

|

|

+----------+----------+------------+---------+------+

10

211updateclassessetclass_id=5whereclass_name='Java2104';#结果如下

+----------+------------+--------------+

|class_id|class_name|class_remark|

+----------+------------+--------------+

|

|

|

|

+----------+------------+--------------+

20

311updatestudentssetcid=5wherecidISNULL;#结果如下

+----------+----------+------------+---------+------+

|stu_num|stu_name|stu_gender|stu_age|cid|

+----------+----------+------------+---------+------+

|

|

|

|

我们可以使⽤级联操作来实现:

1.在添加外键时,设置级联修改和级联删除

#删除原有的外键

altertablestudentsdropforeignkeyFK_STUDENTS_CLASSES;

3

#重新添加外键,并设置级联修改和级联删除

altertablestudentsaddconstraintFK_STUDENTS_CLASSESforeign

key(cid)referencesclasses(class_id)ONUPDATECASCADEONDELETE

CASCADE;

2.测试级联修改:

#班级信息

+----------+------------+--------------+

|class_id|class_name|class_remark|

+----------+------------+--------------+

|

|

|

|

+----------+------------+--------------+

#学⽣信息

+----------+----------+------------+---------+------+

|stu_num|stu_name|stu_gender|stu_age|cid|

+----------+----------+------------+---------+------+

|

|

|

|

+----------+----------+------------+---------+------+

19

#直接修改Java2104的class_id,关联Java2104这个班级的学⽣记录的cid也会同步修

updateclassessetclass_id=1whereclass_name='Java2104';

22

#班级信息

+----------+------------+--------------+

|class_id|class_name|class_remark|

3.测试级联删除

1#删除class_id=1的班级信息,学⽣表引⽤此班级信息的记录也会被同步删除2deletefromclasseswhereclass_id=1;

3+----------+------------+--------------+

4|class_id|class_name|class_remark|

5+----------+------------+--------------+

6|2|Java2105|...|

7|3|Java2106|...|

8|4|Python2106|...|

9+----------+------------+--------------+

10

11+----------+----------+------------+---------+------+

12|stu_num|stu_name|stu_gender|stu_age|cid|

13+----------+----------+------------+---------+------+

14|20210104|赵柳|⼥|18|2|

15+----------+----------+------------+---------+------+

八、连接查询

通过对DQL的学习,我们可以很轻松的从⼀张数据表中查询出需要的数据;在企业的应⽤开发中,我们经常需要从多张表中查询数据(例如:我们查询学⽣信息的时候需要同时查询学⽣的班级信息),可以通过连接查询从多张数据表提取数据:

在MySQL中可以使⽤join实现多表的联合查询——连接查询,join按照其功能不同分为三个操作:

innerjoin内连接

leftjoin左连接

rightjoin右连接

8.1数据准备

8.1.1创建数据表

创建班级信息表和学⽣信息表

1createtableclasses(

2class_idintprimarykeyauto_increment,

3class_namevarchar(40)notnullunique,

4class_remarkvarchar(200)

5);

6createtablestudents(

7stu_numchar(8)primarykey,

8stu_namevarchar(20)notnull,

9stu_genderchar(2)notnull,

10stu_ageintnotnull,

11cidint,

12constraintFK_STUDENTS_CLASSESforeignkey(cid)references

classes(class_id)ONUPDATECASCADEONDELETECASCADE

13);

8.1.2添加数据

添加班级信息

1

2

3

4

5

6

7

8

9#Java2104包含三个学⽣信息

insertintoclasses(class_name,class_remark)values('Java2104','...');

#Java2105包含两个学⽣信息

insertintoclasses(class_name,class_remark)values('Java2105','...');

#以下两个班级在学⽣表中没有对应的学⽣信息

insertintoclasses(class_name,class_remark)values('Java2106','...');

insertintoclasses(class_name,class_remark)values('Python2105','...');

添加学⽣信息

1#以下三个学⽣信息属于class_id=1的班级(Java2104)

2insertintostudents(stu_num,stu_name,stu_gender,stu_age,cid)

3values('20210101','张三','男',20,1);

4insertintostudents(stu_num,stu_name,stu_gender,stu_age,cid)

5values('20210102','李四','⼥',20,1);

6insertintostudents(stu_num,stu_name,stu_gender,stu_age,cid)

7values('20210103','王五','男',20,1);

8

9#以下三个学⽣信息属于class_id=2的班级(Java2105)

10insertintostudents(stu_num,stu_name,stu_gender,stu_age,cid)

11values('20210104','赵柳','⼥',20,2);

12insertintostudents(stu_num,stu_name,stu_gender,stu_age,cid)

13values('20210105','孙七','男',20,2);

14

15#⼩红和⼩明没有设置班级信息

16insertintostudents(stu_num,stu_name,stu_gender,stu_age)values('20210106','⼩红','⼥',20);

17insertintostudents(stu_num,stu_name,stu_gender,stu_age)values('20210107','⼩明','男',20);

8.2内连接INNERJOIN

语法

8.2.1笛卡尔积

笛卡尔积(A集合&B集合):使⽤A中的每个记录⼀次关联B中每个记录,笛卡尔积的总

数=A总数*B总数

如果直接执⾏select...fromtableName1innerjointableName2;会获取两种数

据表中的数据集合的笛卡尔积(依次使⽤tableName1表中的每⼀条记录去匹配tableName2的每条数据)

8.2.2内连接条件

两张表时⽤innerjoin连接查询之后⽣产的笛卡尔积数据中很多数据都是⽆意义的,我们

使⽤on设置两张表连接查询的匹配条件

--使⽤where设置过滤条件:先⽣成笛卡尔积再从笛卡尔积中过滤数据(效率很低)

select*fromstudentsINNERJOINclasseswherestudents.cid=

classes.class_id;

--使⽤ON设置连接查询条件:先判断连接条件是否成⽴,如果成⽴两张表的数据进⾏组合⽣成⼀条结果记录

select*fromstudentsINNERJOINclassesONstudents.cid=

classes.class_id;

结果:只获取两种表中匹配条件成⽴的数据,任何⼀张表在另⼀种表如果没有找到对应

匹配则不会出现在查询结果中(例如:⼩红和⼩明没有对应的班级信息,Java2106和Python2106没有对应的学⽣)。

8.2左连接LEFTJOIN

需求:请查询出所有的学⽣信息,如果学⽣有对应的班级信息,则将对应的班级信息也

左连接:显示左表中的所有数据,如果在有右表中存在与左表记录满⾜匹配条件的数据,则进⾏匹配;如果右表中不存在匹配数据,则显示为Null

#语法

select*fromleftTabelLEFTJOINrightTableON匹配条件[where条件];

--左连接:显示左表中的所有记录

select*fromstudentsLEFTJOINclassesONstudents.cid=

classes.class_id;

8.3右连接RIGHTJOIN

8.4数据表别名

如果在连接查询的多张表中存在相同名字的字段,我们可以使⽤表名.字段名来进⾏区

分,如果表名太⻓则不便于SQL语句的编写,我们可以使⽤数据表别名

使⽤示例:

1

2

3

4selects.*,c.class_name

fromstudentss

INNERJOINclassesc

ONs.cid=c.class_id;

8.5⼦查询/嵌套查询

⼦查询—先进⾏⼀次查询,第⼀次查询的结果作为第⼆次查询的源/条件(第⼆次查询

8.5.1⼦查询返回单个值-单⾏单列

案例1:查询班级名称为'Java2104'班级中的学⽣信息(只知道班级名称,⽽不知道班级ID)

传统的⽅式:

1--a.查询Java2104班的班级编号

2selectclass_idfromclasseswhereclass_name='Java2104';3

4--b.查询此班级编号下的学⽣信息

5select*fromstudentswherecid=1;

⼦查询:

8.5.2⼦查询返回多个值-多⾏单列

案例2:查询所有Java班级中的学⽣信息

传统的⽅式:

⼦查询

1

2--如果⼦查询返回的结果是多个值(单列多⾏),条件使⽤IN/NOTIN

select*fromstudentswherecidIN(selectclass_idfromclasseswhere

class_nameLIKE'Java%');

8.5.3⼦查询返回多个值-多⾏多列

案例3:查询cid=1的班级中性别为男的学⽣信息

九、存储过程

9.1存储过程介绍

9.1.1SQL指令执⾏过程

从SQL执⾏执⾏的流程中我们分析存在的问题:

1.如果我们需要重复多次执⾏相同的SQL,SQL执⾏都需要通过连接传递到MySQL,并且需要经过编译和执⾏的步骤;

2.如果我们需要连续执⾏多个SQL指令,并且第⼆个SQL指令需要使⽤第⼀个SQL指令执⾏的结果作为参数;

9.1.2存储过程的介绍

存储过程:

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

9.1.3存储过程优缺点分析

存储过程优点:

1.SQL指令⽆需客户端编写,通过⽹络传送,可以节省⽹络开销,同时避免SQL指令在⽹络传输过程中被恶意篡改保证安全性;

2.存储过程经过编译创建并保存在数据库中的,执⾏过程⽆需重复的进⾏编译操作,对SQL指令的执⾏过程进⾏了性能提升;

3.存储过程中多个SQL指令之间存在逻辑关系,⽀持流程控制语句(分⽀、循环),可以实现更为复杂的业务;

存储过程的缺点:

1.存储过程是根据不同的数据库进⾏编译、创建并存储在数据库中;当我们需要切换到其他的数据库产品时,需要重写编写针对于新数据库的存储过程;

2.存储过程受限于数据库产品,如果需要⾼性能的优化会成为⼀个问题;

3.在互联⽹项⽬中,如果需要数据库的⾼(连接)并发访问,使⽤存储过程会增加数据库的连接执⾏时间(因为我们将复杂的业务交给了数据库进⾏处理)

9.2创建存储过程

9.2.1存储过程创建语法

1--语法:

2createprocedure<proc_name>([IN/OUTargs])

3begin

4--SQL

5end;

9.2.2示例

1--创建⼀个存储过程实现加法运算:Java语法中,⽅法是有参数和返回值的

2--存储过程中,是有输⼊参数和输出参数的

3createprocedureproc_test1(INaint,INbint,OUTcint)

4begin

5SETc=a+b;

6end;

9.3调⽤存储过程

1--调⽤存储过程

2--定义变量@m

3set@m=0;

4--调⽤存储过程,将3传递给a,将2传递给b,将@m传递给c

5callproc_test1(3,2,@m);6--显示变量值

7select@mfromdual;

9.4存储过程中变量的使⽤

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

9.4.1定义局部变量

局部变量:定义在存储过程中的变量,只能在存储过程内部使⽤

局部变量定义语法

局部变量定义示例:

1createprocedureproc_test2(INaint,OUTrint)

2begin

3declarexintdefault0;--定义xint类型,默认值为0

4declareyintdefault1;--定义y

5setx=a*a;

6sety=a/2;

7setr=x+y;

8end;

9.4.2定义⽤户变量

⽤户变量:相当于全局变量,定义的⽤户变量可以通过select@attrNamefromdual进⾏查询

1

2

3--⽤户变量会存储在mysql数据库的数据字典中(dual)

--⽤户变量定义使⽤set关键字直接定义,变量名要以@开头

set@n=1;

9.4.3给变量设置值

⽆论是局部变量还是⽤户变量,都是使⽤set关键字修改值

1

2

3set@n=1;

callproc_test2(6,@n);

select@nfromdual;

9.4.4将查询结果赋值给变量

在存储过程中使⽤select..into..给变量赋值

1

2

3

4

5

6

7

8

9--查询学⽣数量

createprocedureproc_test3(OUTcint)

begin

selectcount(stu_num)INTOcfromstudents;--将查询到学⽣数量赋值给cend;

--调⽤存储过程

callproc_test3(@n);

select@nfromdual;

9.4.5⽤户变量使⽤注意事项

因为⽤户变量相当于全局变量,可以在SQL指令以及多个存储过程中共享,在开发中建议尽量少使⽤⽤户变量,⽤户变量过多会导致程序不易理解、难以维护。

9.5存储过程的参数

MySQL存储过程的参数⼀共有三种:IN\OUT\INOUT

9.5.1IN输⼊参数

输⼊参数——在调⽤存储过程中传递数据给存储过程的参数(在调⽤的过程必须为具有实际值的变量或者字⾯值)

1

2

3

4

5

6

7

8--创建存储过程:添加学⽣信息

createprocedureproc_test4(INsnumchar(8),INsnamevarchar(20),IN

genderchar(2),INageint,INcidint,INremarkvarchar(255))

begin

insertintostudents(stu_num,stu_name,stu_gender,stu_age,cid,remark)

values(snum,sname,gender,age,cid,remark);

end;

callproc_test4('20210108','⼩丽','⼥',20,1,'aaa');

9.5.2OUT输出参数

输出参数——将存储过程中产⽣的数据返回给过程调⽤者,相当于Java⽅法的返回值,但不同的是⼀个存储过程可以有多个输出参数

1

2

3

4

5

6

7

8

9--创建存储过程,根据学⽣学号,查询学⽣姓名

createprocedureproc_test5(INsnumchar(8),OUTsnamevarchar(20))

begin

selectstu_nameINTOsnamefromstudentswherestu_num=snum;

end;

set@name='';

callproc_test5('20210108',@name);

select@namefromdual;

9.5.3INOUT输⼊输出参数

1createprocedureproc_test6(INOUTstrvarchar(20))

2begin

3selectstu_nameINTOstrfromstudentswherestu_num=str;

4end;5

6set@name='20210108';

7callproc_test6(@name);

8select@namefromdual;

9.6存储过程中流程控制

在存储过程中⽀持流程控制语句⽤于实现逻辑的控制

9.6.1分⽀语句

if-then-else

--单分⽀:如果条件成⽴,则执⾏SQL

ifconditionsthen

--SQL

endif;

--如果参数a的值为1,则添加⼀条班级信息

createprocedureproc_test7(INaint)

begin

ifa=1then

insertintoclasses(class_name,remark)values('Java2109','test');

endif;

end;

case

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16--case

createprocedureproc_test8(INaint)

begin

casea

when1then

--SQL1如果a的值为1则执⾏SQL1

insertintoclasses(class_name,remark)values('Java2110','wahaha');

when2then

--SQL2如果a的值为2则执⾏SQL2

insertinto

students(stu_num,stu_name,stu_gender,stu_age,cid,remark)

values('20210111','⼩刚','男',21,2,'...');

else

--SQL(如果变量的值和所有when的值都不匹配,则执⾏else中的这个SQL)

updatestudentssetstu_age=18wherestu_num='20210110';

endcase;

end;

9.6.2循环语句

while

1

2

3

4

5

6

7

8

9

10

11

12

13--while

createprocedureproc_test9(INnumint)

begin

declareiint;

seti=0;

whilei<numdo

--SQL

insertintoclasses(class_name,remark)values(CONCAT('Java',i)

,'....');

seti=i+1;

endwhile;

end;

callproc_test9(4);

repeat

1

2

3

4

5

6

7

8

9

10

11

12

13--repeat

createprocedureproc_test10(INnumint)

begin

declareiint;

seti=1;

repeat

--SQL

insertintoclasses(class_name,remark)values(CONCAT('Python',i)

,'....');

seti=i+1;

untili>numendrepeat;

end;

callproc_test10(4);

loop

1

2

3

4

5

6

7--loop

createprocedureproc_test11(INnumint)

begin

declareiint;

seti=0;

myloop:loop

--SQL

9.7存储过程管理

9.7.1查询存储过程

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

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

9.7.2修改存储过程

修改存储过程指的是修改存储过程的特征/特性

存储过程的特征参数:

READSSQLDATA

DEFINER表示只有定义者⾃⼰才能够执⾏

INVOKER表示调⽤者可以执⾏

COMMENT'string'表示注释信息

9.7.3删除存储过程

9.8存储过程练习案例

使⽤存储过程解决企业项⽬开发过程中的问题

案例:使⽤存储过程完成借书操作

9.8.1数据准备

业务分析

哪个学⽣借哪本书,借了多少本?

操作:

保存借书记录

修改图书库存

条件:

判断学⽣是否存在?

判断图书是否存在、库存是否充⾜?

创建借书记录表

--借书记录表:

createtablerecords(

ridintprimarykeyauto_increment,

snumchar(4)notnull,

bidintnotnull,

borrow_numintnotnull,

is_returnintnotnull,--0表示为归还1表示已经归还

borrow_datedatenotnull,

constraintFK_RECORDS_STUDENTSforeignkey(snum)references

students(stu_num),

constraintFK_RECORDS_BOOKSforeignkey(bid)REFERENCES

books(book_id)

);

创建存储过程实现借书业务

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47insertinto

records(snum,bid,borrow_num,is_return,borrow_date)

values(a,b,m,0,sysdate());

--操作2:修改图书库存

updatebookssetbook_stock=stock-mwherebook_id=b;

--借书成功

setstate=1;

else

--库存不⾜

setstate=4;

endif;

else

--图书不存在

setstate=3;

endif;

else

--不存在

setstate=2;

endif;

end;

--调⽤存储过程借书

set@state=0;

callproc_borrow_book('1001',1,2,@state);

select@statefromdual;

9.8.3创建存储过程实现还书业务

期待...

9.9游标

问题:如果我们要创建⼀个存储过程,需要返回查询语句查询到的多条数据,该如何实现呢?

9.1.1游标的概念

游标可以⽤来依次取出查询结果集中的每⼀条数据——逐条读取查询结果集中的记录

9.1.2游标的使⽤步骤

1、声明游标

声明游标语法:

实例

2、打开游标

语法

3、使⽤游标

使⽤游标:提取游标当前指向的记录(提取之后,游标⾃动下移)

4、关闭游标

9.1.3游标使⽤案例

⼗、触发器

10.1触发器的介绍

触发器,就是⼀种特殊的存储过程。触发器和存储过程⼀样是⼀个能够完成特定功能、存储在数据库服务器上的SQL⽚段,但是触发器⽆需调⽤,当对数据表中的数据执⾏DML操作时⾃动触发这个SQL⽚段的执⾏,⽆需⼿动调⽤。

在MySQL,只有执⾏insert\delete\update操作才能触发触发器的执⾏。

10.2.1案例说明

1--学⽣信息表

2createtablestudents(

3stu_numchar(4)primarykey,

4stu_namevarchar(20)notnull,

5stu_genderchar(2)notnull,

6stu_ageintnotnull

7);

8

9--学⽣信息操作⽇志表

10createtablestulogs(

11idintprimarykeyauto_increment,

12timeTIMESTAMP,

13log_textvarchar(200)

14);

案例:当向学⽣信息表添加、删除、修改学⽣信息时,使⽤触发器⾃定进⾏⽇志记录

10.2.2创建触发器

语法

1--创建触发器:当学⽣信息表发⽣添加操作时,则向⽇志信息表中记录⼀条⽇志

2createtriggertri_test1

3afterinsertonstudents

4foreachrow

5insertintostulogs(time,log_text)values(now(),concat('添加',NEW.stu_num,'学⽣信息'));

10.2.3查看触发器

10.2.3测试触发器

我们创建的触发器是在students表发⽣insert操作时触发,我们只需执⾏学⽣信息的添加

操作

1--测试1:添加⼀个学⽣信息,触发器执⾏了⼀次

2insertintostudents(stu_num,stu_name,stu_gender,stu_age)

values('1005','⼩明','男',20);

3

4

5--测试2:⼀条SQL指令添加了2条学⽣信息,触发器就执⾏了2次

6insertintostudents(stu_num,stu_name,stu_gender,stu_age)

values('1006','⼩刚','男',20),('1007','李磊','男',20);

10.2.4删除触发器

10.3NEW与OLD

触发器⽤于监听对数据表中数据的insert、delete、update操作,在触发器中通常处理⼀些DML的关联操作;我们可以使⽤NEW和OLD关键字在触发器中获取触发这个触发器的DML操作的数据

NEW:在触发器中⽤于获取insert操作添加的数据、update操作修改后的记录

OLD:在触发器中⽤于获取delete操作删除前的数据、update操作修改前的数据

10.3.1NEW

insert操作中:NEW表示添加的新记录

update操作中:NEW表示修改后的数据

10.3.2OLD

delete操作中:OLD表示删除的记录

1

2

3createtriggertri_test3

afterdeleteonstudentsforeachrow

insertintostulogs(time,log_text)values(now(),concat('删除',OLD.stu_num,'学⽣信息'));

update操作中:OLD表示修改前的记录

1

2

3createtriggertri_test2

afterupdateonstudentsforeachrow

insertintostulogs(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语法

11.3.2实例

实例1:

1--创建视图实例1:将学⽣表中性别为男的学⽣⽣成⼀个视图

2createviewview_test1

3AS

4select*fromstudentswherestu_gender='男';5

6--查询视图

7select*fromview_test1;

示例2:

1

2

3

4

5

6

7

8

9--创建视图示例2:查询学⽣借书的信息(学⽣名、图书名、借书数量)

createviewview_test2

AS

selects.stu_name,b.book_name,borrow_num

frombooksbinnerjoinrecordsrinnerjoinstudentss

onb.book_id=r.bidandr.snum=s.stu_num;

--查询视图

select*fromview_test2;

11.4视图数据的特性

视图是虚拟表,查询视图的数据是来源于数据表的。当对视图进⾏操作时,对原数据表中的数据是否由影响呢?

查询操作:如果在数据表中添加了新的数据,⽽且这个数据满⾜创建视图时查询语句的条件,通过查询视图也可以查询出新增的数据;当删除原表中满⾜查询条件的数据时,也会从视图中删除。

新增数据:如果在视图中添加数据,数据会被添加到原数据表

删除数据:如果从视图删除数据,数据也将从原表中删除

修改操作:如果通过修改数据,则也将修改原数据表中的数据

视图的使⽤建议:对复杂查询简化操作,并且不会对数据进⾏修改的情况下可以使⽤视图。

11.5查询视图结构

11.6修改视图

1

2

3

4

5

6

7

8

9--⽅式1

createORREPLACEview

AS

select*fromstudents

--⽅式2

alterviewview_test1

AS

select*fromstudents

view_test1

wherestu_gender='⼥';

wherestu_gender='男';

11.7删除视图

删除数据表时会同时删除数据表中的数据,删除视图时不会影响原数据表中的数据

⼗⼆、索引

数据库是⽤来存储数据,在互联⽹应⽤中数据库中存储的数据可能会很多(⼤数据),数据表中数据的查询速度会随着数据量的增⻓逐渐变慢,从⽽导致响应⽤户请求的速度变慢——⽤户体验差,我们如何提⾼数据库的查询效率呢?

12.1索引的介绍

索引,就是⽤来提⾼数据表中数据的查询效率的。

索引,就是将数据表中某⼀列/某⼏列的值取出来构造成便于查找的结构进⾏存储,⽣成数据表的⽬录

当我们进⾏数据查询的时候,则先在⽬录中进⾏查找得到对应的数据的地址,然后再到数据表中根据地址快速的获取数据记录,避免全表扫描。

12.2索引的分类

MySQL中的索引,根据创建索引的列的不同,可以分为:

主键索引:在数据表的主键字段创建的索引,这个字段必须被primarykey修饰,每张表只能有⼀个主键唯⼀索引:在数据表中的唯⼀列创建的索引(unique),此列的所有值只能出现⼀次,可以为NULL

普通索引:在普通字段上创建的索引,没有唯⼀性的限制

组合索引:两个及以上字段联合起来创建的索引

说明:

1.在创建数据表时,将字段声明为主键(添加主键约束),会⾃动在主键字段创建主键索引;

2.在创建数据表时,将字段声明为唯⼀键(添加唯⼀约束),会⾃动在唯⼀字段创建唯⼀索引;

12.3创建索引

12.3.1唯⼀索引

12.3.2普通索引

1

2

3--创建普通索引:不要求创建索引的列的值的唯⼀性

--createindex<index_name>on表名(列名);

createindexindex_test2ontb_testindex(name);

12.3.3组合索引

12.3.4全⽂索引

MySQL5.6版本新增的索引,可以通过此索引进⾏全⽂检索操作,因为MySQL全⽂检索不⽀持中⽂,因此这个全⽂索引不被开发者关注,在应⽤开发中通常是通过搜索引擎(数据库中间件)实现全⽂检索

12.4索引使⽤

索引创建完成之后⽆需调⽤,当根据创建索引的列进⾏数据查询的时候,会⾃动使⽤索引;

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

在命令⾏窗⼝中可以查看查询语句的查询规划:

12.5查看索引

12.6删除索引

1

2

3--删除索引:索引是建⽴在表的字段上的,不同的表中可能会出现相同名称的索引

--因此删除索引时需要指定表名

dropindexindex_test3ontb_testindex;

12.7索引的使⽤总结

12.7.1优点

索引⼤⼤降低了数据库服务器在执⾏查询操作时扫描的数据,提⾼查询效率索引可以避免服务器排序、将随机IO编程顺序IO

12.7.2缺点

索引是根据数据表列的创建的,当数据表中数据发⽣DML操作时,索引⻚需要更新;索引⽂件也会占⽤磁盘空间;

12.7.3注意事项

数据表中数据不多时,全表扫⾯可能更快吗,不要使⽤索引;

数据量⼤但是DML操作很频繁时,不建议使⽤索引;

不要在数据重复读⾼的列上创建索引(性别);

创建索引之后,要注意查询SQL语句的编写,避免索引失效。

⼗三、数据库事务

13.1数据库事务介绍

我们把完成特定的业务的多个数据库DML操作步骤称之为⼀个事务

事务,就是完成同⼀个业务的多个DML操作

1

2

3

4

5

6

7

8

9--借书业务

--操作1:在借书记录表中添加记录

insertintorecords(snum,bid,borrow_num,is_return,borrow_date)

values('1001',1,1,0,sysdate());

--操作2:修改图书库存

updatebookssetbook_stock=book_stock-1wherebook_id=1;

--转账业务:张三给李四转账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事务管理

开启事务,就是关闭⾃动提交

在开始事务第⼀个操作之前,执⾏starttransaction开启事务

依次执⾏事务中的每个DML操作

如果在执⾏的过程中的任何位置出现异常,则执⾏rollback回滚事务如果事务中所有的DML操作都执⾏成功,则在最后执⾏commit提交事务

13.4事务隔离级别

数据库允许多个事务并⾏,多个事务之间是隔离的、相互独⽴的;如果事务之间不相互隔离并且操作同⼀数据时,可能会导致数据的⼀致性被破坏。

MySQL数据库事务隔离级别:

13.4.1读未提交(readuncommitted)

T2可以读取T1执⾏但未提交的数据;可能会导致出现脏读

脏读,⼀个事务读取到了另⼀个事务中未提交的数据

13.4.2读已提交(readcommitted)

T2只能读取T1已经提交的数据;避免了脏读,但可能会导致不可重复度(虚读)

不可重复度(虚读):在同⼀个事务中,两次查询操作读取到数据不⼀致

例如:T2进⾏第⼀次查询之后在第⼆次查询之前,T1修改并提交了数据,T2进⾏第⼆次查询时读取到的数据和第⼀次查询读取到数据不⼀致。

13.4.3可重复读(repeatableread)

T2执⾏第⼀次查询之后,在事务结束之前其他事务不能修改对应的数据;避免了不可重复读(虚读),但可能会导致幻读

幻读,T2对数据表中的数据进⾏修改然后查询,在查询之前T1向数据表中新增了⼀条数据,就导致T2以为修改了所有数据,但却查询出了与修改不⼀致的数据(T1事务新增的数据)

13.4.4串⾏化(serializable)

同时只允许⼀个事务对数据表进⾏操作;避免了脏读、虚读、幻读问题

隔离级别脏读不可重复读(虚读)幻读

readuncommitted√√√

readcommitted×√√

repeatableread××√

serializable×××

13.4.5设置数据库事务隔离级别

我们可以通过设置数据库默认的事务隔离级别来控制事务之间的隔离性;

也可以通过客户端与数据库连接设置来设置事务间的隔离性(在应⽤程序中设置--Spring);

MySQL数据库默认的隔离级别为可重复读

查看MySQL数据库默认的隔离级别

1--在MySQL8.0.3之前

2select@@tx_isolation;3

4--在MySQL8.0.3之后

5select@@transaction_isolation;6

设置MySQL默认隔离级别

⼗四、数据库设计

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图示例

三线图统⼀数据实体的表结构

每个实体创建⼀张数据表

多对多关联:需额外常⻅⼀个数据表维护关系,关系表分别创建外键与两张表关联

⼀对多、多对⼀关联:在多的⼀端添加外键与⼀的⼀端的主键建⽴主外键约束

⼀对⼀关联:在任意⼀端创建外键与另⼀端建⽴主外键关联,并且将外键设置为unique

14.2.5数据库建模(PD)

E-R图实际上就是数据模建模的⼀部分:

E-R图数据表设计建库建表

PowerDesigner建模⼯具导出数据表

PDMan建模⼯具

1.下载并安装PowerDesigner建模⼯具

2.

概念数据模型(选择workspace--右键new--ConceptualDataModel),相当于E-R

逻辑数据模型(打开概念数据模型--tools--GenerateLogicalDataModel),体现了实体的主外键关联

物理数据模型(打开逻辑数据模型--tools--GeneratePhysicalDataMode---选择数据库类型及版本)

可以对物理数据模型进⾏微调

可以通过物理数据模型⽣成建库建表的SQL语句(在物理数据模型的窗⼝中----Database⼯具条---GenerateDatabase--⽣成SQL⽂件)

通过数据库的管理⼯具执⾏SQL⽂件就可以完成数据表的创建

⾯向对象模型(打开概念数据模型/逻辑数据模型/物理数据模型---tools--Generate

Object-OrentitedModel)

可以根据语⾔设置,⽣成实体类(Java)

如果想要借助于PD建模⼯具⽣成Java代码,创建概念的模型时实体名、属性名

都要符合Java程序的命名规范。

在企业项⽬开发,我们通常是不会使⽤建模⼯具来⽣成数据表、实体类的,因为⽣成的代码规范不合乎我们的代码需求

14.2.6数据库建模(PDMan)

下载安装PDMan

创建项⽬——在项⽬中创建数据表

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

热门产品

历史上的今天:03月29日

热门专题

大理科技管理学校|大理科技管理中等职业技术学校,大理市科技管理中等职业技术学校
大理科技管理学校
中源管业|中源管业,中源管业公司,中源管业有限公司,中源管业电话,中源管业地址,中源管业电力管,中源管业mpp电力管,中源管业cpvc电力管,中源管业pe穿线管
中源管业
易捷尔单招|易捷尔单招,易捷尔单招培训,易捷尔单招报名,易捷尔单招考试,易捷尔单招培训学校,易捷尔单招分数
易捷尔单招
卓越综合高中|卓越综合高中
卓越综合高中
云南网站建设|云南网站制作,网站建设,云南网站开发,云南网站设计,云南网页设计,云南网站建设公司,云南网站建设
云南网站建设
APP开发|app开发_app开发公司_app软件开发_专业app开发_云南app开发公司_app定制_原生app开发定制
APP开发
自考本科|自考本科有用吗,自考文凭,自考本科文凭,自考文凭有用吗,自考本科文凭有用吗,自考文凭承认吗
自考本科
一年制中专|中专学历,中专是什么学历,中专是什么,中专有什么专业,中专升大专,一年制中专
一年制中专

微信小程序

微信扫一扫体验

立即
投稿

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部