MySQL数据库项目式教程思维导图模板_ProcessOn思维导图、流程图

项目5 创建与使用视图

【任务5.1】使用语句创建视图

1.基本概念

（1）.概念：一个或多个表中导出来的表，它是一种虚拟存在的表

（2）.特点：表的结构和数据都依赖于基本的表，可以像表一样对数据进行增、删、该、查。

（3）.优点：简化性；安全性；逻辑数据独立性；

2.创建视图

create view 视图名 as select * from 表名;

3.查看视图

select * from 视图名;

4.删除视图

drop view 视图名;

补充:当视图中包含如下内容时，视图的更新操作将不被执行。

（1）视图中不包含基本表中被定义为非空的列；

（2）在定义视图的SELECT语句后的字段列表中使用了数学表达式

（3）在定义视图的SELECT语句后的字段列表中使用了聚合函数

（4）在定义了视图的SELECT 语句中使用了DISTINCT、UNION、LIMIT、GROUP BY或HAVING子句。

5.查看视图字段信息

desc 视图名;

6.查看视图创建信息

show create view 视图名\G

7.修改视图

alter view 视图名 as 查询语句;

补充：视图是一个虚拟的表，当通过视图修改数据时，就是更新基本表中的数据

8.创建或修改视图

create or replace view 视图名 as 查询语句;

9.视图的规则和限制

（1）命名唯一。

（2）创建数目没有限制。

（3）为了创建视图，必须具有足够的访问权限。

（4）视图可以嵌套

（5）ORDER BY 可以使用在视图中，但在该视图检索数据SELECT中也含有ORDER BY ,那么该视图中的ORDER BY 将被覆盖。

（6）视图不能索引，也不能有关联的触发器或默认值。

（7）视图可以和表一起使用。

项目6 创建与使用存储过程

项目6 内容前言

存储过程是一组为了完成特定功能的PL\SQL 语句集，经编译后存储在数据库中，用户可以重复使用该存储过程，这样可以降低数据库开发人员的工作量。

游标是处理数据的一种方法，为了查看或处理结果集中的数据，游标提供了在结果集中一次一行浏览数据的能力。

事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单元。

【任务6.1】创建简单存储过程

1.PL\SQL的变量

(1) 用户变量：用户可以在PL\SQL中使用自己定义的变量，这样的变量称为用户变量。

(2) 系统变量：MySQL可以访问许多系统变量和连接变量，当服务器运行时许多变量可以动态改变。

(3) 局部变量：在语句块（BEGIN到End之间）中定义的变量为局部变量。

2.PL\SQL的运算符及表达式

(1) 运算符的优先级

3..PL\SQL的控制语句

4.MySQL的存储过程

（1）存储过程是一组为了完成特定功能的SQL 语句块，经编译后存储在数据库中，用户通过指定存储过程的名称并给定参数（如果该存储过程带有参数）来调用并执行，存储过程可以重复使用，这样可以降低数据库开发人员的工作量。

（2）优点

执行效率高

灵活

数据独立

安全

降低网络流量

（3）修改SQL结束符：delimiter <自定义结束符>

（4）创建存储过程

create procedure 存储过程名()
begin
………….
………..
end;

示例

（5）调用存储过程

call 存储过程名();

示例

（6）删除存储过程

drop procedure 存储过程名;

【任务6.2】创建带输入参数的存储过程

【任务6.3】创建带输入和输出参数的存储过程

【任务6.4】创建应用游标的存储过程

1.创建游标

declare 游标名 cursor for 查询语句;

2.打开游标

open 游标名;

3.读取游标

fetch 游标名 into 变量;

4.关闭游标

close 游标名;

【任务6.5】创建与使用事务

1.开启事务

start transaction;

2.结束事物

提交 commit;

取消 rollback;

3.设置事务隔离级别

set session transaction isolation level 隔离级别;

4.事务隔离级别

read uncommitted 读未提交（脏读）

read committed 读提交（不可重复读）

repeatable read 可重复读（幻读）

serializable 可串行化

5.事务的ACID特性

（1）原子性

（2）一致性

（3）隔离性

（4）持久性

项目7 创建与使用触发器

【任务7.1】创建触发器

1.触发器概述

触发器是特殊的存储过程，是一个被指定关联到数据表上的数据库对象，可以看作数据表定义的一部分，用于实现数据库中数据的完整性。

2.触发器作用

（1）安全性

（2）实现复杂的数据完整性

（3）实现复杂的非标准数据相关完整性

3.注意

在MySQL触发器中不能直接在客户端界面返回结果，所以在触发器动作中不要使用SELECT语句

4.创建触发器

create trigger 触发器名触发时刻（before，after）触发事件 (insert) on 表名 for each row 触发器动作;

示例

【任务7.2】查看及删除触发器

1.查看触发器

show triggers;

2.删除触发器

drop trigger 触发器名;

项目8 数据库的安全性维护

【任务8.1】添加数据库用户

1.MySQL的权限表

MySQL通过权限表来控制用户对数据库的访问，MySQL数据库在安装时会自动安装多个数据库。

（1)user权限表。user是MySQL中最重要的一个权限表，user列主要分为4个部分：用户列、权限列、安全列和资源控制列。

①用户列：用户登录时通过表中的Host、User和Password列判断连接的IP、用户名称和密码是否存在于表中来通过身份验证或拒绝连接。

②权限列：user表中包含多个以“＿priv”结尾的字段，这些字段决定了该用户的权限，既包括查询权限、插入权限、更新权限、删除权限等普通权限，也包括关闭服务器和加载用户等高级管理权限。

③安全列：ssl(加密）、x509(标识用户）开头的字段，以及 plugin和 authentication_string 字段（验证用户身份、授权的插件）。

④资源控制列：max(最大允许次数，0表示无限制）开头的字段。

max_questions:表示每小时允许执行查询数据库的次数。

max_updates:表示每小时允许执行更新数据库的次数。

max_connections:表示每小时允许执行连接数据库的次数。

max_user_conntions:表示单个用户同时连接数据库的次数。

2.添加用户

（1)CREATE USER 语句的语法格式为

create user “用户名”@”主机号” identified by “密码”;

（2)INSERT语句的语法格式为：

insert into user (host,user,password,ssl_cipher,x509_issuer,x509_subject) values
(“主机号”,”用户名”,password(“密码”),””,””,””);

【任务8.2】授予、回收数据库用户权限

前导知识

新添加的数据库用户既不允许访问其他用户的数据库，也不能创建自己的数据库，只有在授予了相应的权限后才能访问或创建数据库，为满足MySQL服务器的安全需要考虑以下内容。

①多数用户只需要对数据表进行读、写操作，只有少数用户需要创建、删除数据表。

②某些用户需要读、写数据而不需要修改数据。

③某些用户允许添加数据而不允许删除数据。

④管理员用户需要有管理用户的权力，而其他用户则不需要。

⑤某些用户允许通过存储过程来访问数据，而不允许直接访问数据表。

用户权限

【任务8.3】数据库用户管理

1.修改用户的名称

RENAME USER <' 旧的用户名＇＞＠＜＇主机＇＞TO<'新的用户名＇＞＠＜＇主机＞；

说明：RENAME USER 语句可以对用户进行重命名，该语句可以同时对多个已存在的用户进行重命名，各个用户之间使用逗号分隔，重命名时“旧的用户名”必须已经存在，并且“新的用户名”还不存在，使用者必须拥有“RENAME USER”权限。

2.修改用户的密码

（1)使用mysqladmin命令修改用户密码的语法格式为：mysqladmin-u<用户名＞ [-h<主机＞］－p password[<新密码＞］说明：“mysqladmin”是一条外部命令，必须在服务器端的“命令提示符”下执行。

（2)使用SET PASSWORD语句修改用户密码的语法格式为：

SET PASSWORD[FOR<'用户名＇＞＠＜＇主机＇＞］＝PASSWORD(<'新密码＞） ;

（3)使用UPDATE语句修改用户密码的语法格式为：

UPDATE mysql.user SET Password=PASSWORD(<'新密码＇＞）WHERE User=<'用户名＇＞AND Host=<'主机＇＞；

3.删除用户

（1)使用DROP USER 语句删除用户的语法格式为：

DROP USER<'用户名＇＞＠＜＇主机＇＞；

2)使用DELETE语句删除用户的语法格式为：

DELETE FROM mysql.user WHERE User=<'用户名＇＞ AND Host=<'主机＇＞；

项目1 MySQL映像

【任务1.1】尝试接触数据库

1.MySQL概述

MySQL是一个关系型数据库管理系统

特点：体积小、速度快、总体拥有成本低，开放源码

2.数据库的技术构成

数据库

用于存储数据的地方

数据库管理系统（DBMS）

用于管理数据库的软件

数据库应用程序

为了提高数据库系统的处理能力所使用的管理数据库的软件补充

3.客户端-服务器结构

主从式架构或客户端/服务器（简称C/S结构）是一种网状结构

登入命令中的”mysql - h 127.0.0.1 - u root -p“。-h后面的是服务器的IP地址，本机为”localhost"或“127.0.0.1”。

【任务1.2】MySQL的命令行工具

1.结构化查询语言（SQL)

DDL 数据定义语言：主要用于定义数据库、数据表等

DML 数据操作语言：主要用于对数据库中的数据进行添加、修改、和删除操作

DQL 数据查询语言：主要用于查询数据

DCL 数据控制语言：主要用于控制用户的访问权限

2.my.ini

my.ini是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的

【任务1.3】图形管理工具Navicat

Navicat是一套快速、可靠、便宜的数据库管理工具

Oracle:良好的兼容性、可移植性、可连接性

SQLServer：界面良好、易于操作

MySQL：运营成本低

项目2 创建和维护MySQL数据库

【任务2.1】使用图形化工具创建数据库

1.字符集及字符序

MySQL默认使用的是latin1符集

字符是人类语言最小的表意符号

给一系列的字符并赋予对应的编码后，所有的这些“字符和编码对”组成集合就是字符集

字符序是指在同一个字符集内字符之间的比较规则

使用“SHOW CHARACTER SET"可以查看当前MySQL服务实例支持的

字符集、字符集默认的字符序，以及字符集占用的最大字节长度等信息

latin1：支持西欧字符、希腊字符等

gbk：支持中文简体字符

utf8：几乎支持世界上所有国家的字符

2.MySQL字符集的设置

通过修改my.ini配置文件，可修改MySQL默认的字符集

3.MySQL储存引擎

数据库储存引擎是数据库底层软件组件

数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作

可以使用SHOW ENGINES语句查看系统所支持的引擎类型

InnoDB（是事务型数据库的首选引擎）：支持事务处理，支持外键

MyISAM：插入数据快，空间和内存使用率比较低

MEMORY：所有的数据都在内存中，数据处理速度虽快，安全性不高

【任务2.2】使用命令语句创建、修改和删除数据库

新建数据库：create database 数据库名;

删除数据库：drop database 数据库名;

修改数据库默认字符集和排序规则：alter database 数据库名 default character set gbk（字符集名）;

修改数据库默认字符集和排序规则：ALTER DATABASE 数据库名 CHARACTER SET GBK COLLATE 排序规则;

【任务2.3】使用语句方式选择与查看数据库

选择当前的数据库：USE 数据库名;

查看数据库的建库信息：show create database 数据库名;

项目3 创建和维护MySQL数据表

【任务3.1】数据表的结构及数据完整性设计

1.关系型数据库

建立在关系模型（数学模型）上的数据库。

关系模型包含三个方面

数据结构：二维表，解决数据如何存储

操作指令集合：所有的SQL语句，解决如何处理数据

完整性约束：表内数据约束（字段与字段），表与表之间的约束(外键）

2.数据类型

MySQL中的数据类型分为三大类：数值型、字符串型、时间日期型

储存范围较大的日期最好使用datetime类型

varchar类型是按实际长度存储，比较节省空间，检索速度比char慢

text类型不能指定，不能有默认值

enum类型和set类型的值都是以字符串形式出现的，但在库中存储的是数值

blob类型存储的是二进制字符串、主要存图片、音频信息，text类型存储的是非二进制字符串、只能存储纯文本内容

3.数据类型的属性

无符号：unsigned

指定一个字符集：character set <字符集名>

数值类型前自动用0补足位数：zerofill

列描述：comment

4.MySQL的约束

主键、外键、唯一、非空、默认值约束、自增

【任务3.2】使用语句创建数据表及其约束

1.使用CREATE TABLE 语句创建数据表及其约束

创建表和定义字段：create table 表名（
name char(5),
sex enum("1","0"),
height int);

表的约束

单字段主键示例

多字段主键示例

外键示例

一张表不能有同名外键

2.使用语句查看数据表结构

查看基本结构语句：DESCRIBE/DESC

查看表详细结构语句：SHOW CREATE TABLE<表名>;

如果显示的结果非常混乱，就在语句命令后加上参数'\G'，可使结果直观、易于查看

【任务3.3】使用图形管理工具创建数据表

【任务3.4】修改、删除数据表结构

1.使用ALTER TABLE语句修改、删除表结构

（1）更改数据表名称：alter table 旧表名 rename to 新表名;

（2）修改表的字段名和字段信息：alter table 数据表名 change 旧字段名新字段名新数据类型;

（3）数据表添加字段：alter table 数据表名 add 字段名数据类型;

2.使用语句删除数据表

（4）删除数据表中字段名：alter table 数据表名 drop 字段名;

【任务3.5】数据插入、更新与删除

1.使用命令语句插入数据

（1）.指定字段插入数据：insert into 表名 (字段名1，字段名2…)values (值1，值2…)；

（2）.所有字段插入数据：
insert into 表名 values (值1，值2…);

（3）所有字段批量插入数据：insert into 表名 values
(值1，值2…)，
(值1，值2…)；

2.使用命令语句更新数据

更新数据：update 表名 set 字段名 = 值 where “条件”;

更新多条数据：update 表名 set 字段名1 = 值1,字段名2=值2 where “条件”;

3.使用命令语句删除数据

删除数据：delete from 表名 where “条件”;

【任务3.6】数据转储

备份单个数据库：
mysqldump -u用户名 -p密码数据库名>文件名.sql
mysqldump -u用户名 -p密码数据库名>文件夹全路径\文件名.sql

备份多个数据库：
mysqldump -u用户名 -p密码 --databases 数据库名1 数据库名2>文件名.sql

备份所有数据库
mysqldump -u用户名 -p密码 --all-databases>文件名.sql

项目4 MySQL数据表的检索

【任务4.1】查询时选择列

1.基本查询语句

查看表中所有列字段数据：
select * from 表名;

查看表中部分字段数据
select 字段名1,字段名2,…, from 表名;

取别名：字段名 [AS] 字段别名;

【任务4.2】查询时选择行

1.查询指定记录

select 字段名1,字段名2,…, from 表名 where 条件;

2.带IN关键字的查询

SELECT 字段1,字段2，... ,字段n FROM 表名 WHERE 字段名 IN (值1,值2,...);

3.带BETWEEN AND 的范围查询

SELECT 字段名1，字段名2，...，字段名n FROM 表名 WHERE 字段名 BETWEEN 值1 AND 值2;

前可加NOT，表示不在这个范围

示例

4.带LIKE的字符匹配查询（模糊查询）

SELECT 字段1,字段2，... ,字段n FROM 表名 WHERE 字段名 LIKE 条件;

示例

以“a”开头的数据

长度为三的数据

5.查询空值

SELECT 字段1,字段2，... ,字段n FROM 表名 WHERE 字段名 IS NULL;

6.带AND的多条件查询

SELECT 字段1,字段2，... ,字段n FROM 表名 WHERE 字段名条件1 AND 条件2...;

7.带OR的多条件查询

SELECT 字段1,字段2，... ,字段n FROM 表名 WHERE 字段名条件1 OR 条件2...;

8.查询不同的值（去重）

SELECT DISTINCT 字段1,字段2，... ,字段n FROM 表名 ;

示例

9.显示前N 行（限制条数）

SELECT DISTINCT 字段1,字段2，... ,字段n FROM 表名 LIMIT [位置偏移量,] 行数;

示例

【任务4.3】查询结果排序

排序——正序排序

select * from 表名 order by 字段名;

select * from 表名 order by 字段名ASC;（默认正序）

排序——倒序排序

select * from 表名 order by 字段名 DESC;（倒序）

【任务4.4】查询的分组与汇总

1.聚集函数

2.分组查询

[GROUP BY 字段名] [HAVING <条件表达式> ]

示例

3.SELECT 之句顺序

【任务4.5】创建多表连接查询

1.交叉连接查询

SELECT * FROM 表名1 CROSS JOIN 表名2;

示例

2.内连接查询

SELECT 查询字段 FROM 表名1 [INNER] JOIN 表名 2 ON 表名1.关系字段 = 表名2.关系字段;

示例

3.外连接查询

左连接：

select * from 表名1 left join 表名2 on 表名1.表1字段名 = 表名2.表2字段名;

示例

右连接：

select * from 表名1 right join 表名2 on 表名1.表1字段名 = 表名2.表2字段名;

示例

4.复合条件连接查询

在连接查询的过程中，通过添加过滤条件来限制查询结果

【任务4.6】创建子查询

1.带IN关键字的子查询

SELECT 查询字段 FROM 表名 WHERE 字段名 [NOT] IN (SELECT 语句）；

示例

查询Daniel老师授课地点

教师姓名中不含A的老师老师授课地点

教师姓名中含A的老师其他老师授课地点

2.带EXISTS 关键字的子查询

SELECT 查询字段 FROM 表名 WHERE 字段名 [NOT] IN (SELECT 语句）；

示例（EXISTS后为真，则有返回值，假则反之）

3.带ANY、SOME 关键字的子查询（任意）

SELECT 查询字段 FROM 表名 WHERE 字段名比较运算符 ANY|SOME (SELECT 语句）;

示例

4.带ALL关键字的子查询（所有）

SELECT 查询字段 FROM 表名 WHERE 字段名比较运算符 ALL (SELECT 语句）;

示例

【任务4.7】创建多表联合查询

1.使用UNION

SELECT 查询字段 FROM 表名
UNION [ALL]
SELECT 查询字段 FROM 表名;

2.UNION规则

（1）必须由两条或者两条以上SELECT语句组成语句之间使用UNION分割

（2）UNION中的每个查询必须包含相同的列、表达式或聚合函数

（3）列数据类型必须兼容

【任务4.8】使用Navicat生成查询