MySQL数据库项目式教程
· 项目5 创建与使用视图
o 【任务5.1】使用语句创建视图
§ 1.基本概念
· （1）.概念：一个或多个表中导出来的表，它是一种虚拟存在的表
· （2）.特点：表的结构和数据都依赖于基本的表，可以像表一样对数据进行增、删、该、查。
· （3）.优点：简化性；安全性；逻辑数据独立性；
§ 2.创建视图
· create view 视图名 as select * from 表名;
§ 3.查看视图
· select * from 视图名;
§ 4.删除视图
· drop view 视图名;
· 补充:当视图中包含如下内容时，视图的更新操作将不被执行。
o （1）视图中不包含基本表中被定义为非空的列；
o （2）在定义视图的SELECT语句后的字段列表中使用了数学表达式
o （3）在定义视图的SELECT语句后的字段列表中使用了聚合函数
o （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 创建与使用存储过程
o 项目6 内容前言
§ 存储过程是一组为了完成特定功能的PL\SQL语句集，经编译后存储在数据库中，用户可以重复使用该存储过程，这样可以降低数据库开发人员的工作量。
§ 游标是处理数据的一种方法，为了查看或处理结果集中的数据，游标提供了在结果集中一次一行浏览数据的能力。
§ 事务是一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单元。
o 【任务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）优点
o 执行效率高
o 灵活
o 数据独立
o 安全
o 降低网络流量
· （3）修改SQL结束符：delimiter <自定义结束符>
· （4）创建存储过程
o create procedure 存储过程名()begin………….………..end;
o 示例
· （5）调用存储过程
o call 存储过程名();
o 示例
· （6）删除存储过程
o drop procedure 存储过程名;
o 【任务6.2】创建带输入参数的存储过程
o 【任务6.3】创建带输入和输出参数的存储过程
o 【任务6.4】创建应用游标的存储过程
§ 1.创建游标
· declare 游标名 cursor for 查询语句;
§ 2.打开游标
· open 游标名;
§ 3.读取游标
· fetch 游标名 into 变量;
§ 4.关闭游标
· close 游标名;
o 【任务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 创建与使用触发器
o 【任务7.1】创建触发器
§ 1.触发器概述
· 触发器是特殊的存储过程，是一个被指定关联到数据表上的数据库对象，可以看作数据表定义的一部分，用于实现数据库中数据的完整性。
§ 2.触发器作用
· （1）安全性
· （2）实现复杂的数据完整性
· （3）实现复杂的非标准数据相关完整性
§ 3.注意
· 在MySQL触发器中不能直接在客户端界面返回结果，所以在触发器动作中不要使用SELECT语句
§ 4.创建触发器
· create trigger 触发器名 触发时刻（before，after）触发事件 (insert) on 表名 for each row 触发器动作;
· 示例
o
o 【任务7.2】查看及删除触发器
§ 1.查看触发器
· show triggers;
§ 2.删除触发器
· drop trigger 触发器名;
o
· 项目8 数据库的安全性维护
o 【任务8.1】添加数据库用户
§ 1.MySQL的权限表
· MySQL通过权限表来控制用户对数据库的访问，MySQL数据库在安装时会自动安装多个数据库。
· （1)user权限表。user是MySQL中最重要的一个权限表，user列主要分为4个部分：用户列、权限列、安全列和资源控制列。
o ①用户列：用户登录时通过表中的Host、User和Password列判断连接的IP、用户名称和密码是否存在于表中来通过身份验证或拒绝连接。
o ②权限列：user表中包含多个以“＿priv”结尾的字段，这些字段决定了该用户的权限，既包括查询权限、插入权限、更新权限、删除权限等普通权限，也包括关闭服务器和加载用户等高级管理权限。
o ③安全列：ssl(加密）、x509(标识用户）开头的字段，以及 plugin和 authentication_string 字段（验证用户身份、授权的插件）。
o ④资源控制列：max(最大允许次数，0表示无限制）开头的字段。
§ max_questions:表示每小时允许执行查询数据库的次数。
§ max_updates:表示每小时允许执行更新数据库的次数。
§ max_connections:表示每小时允许执行连接数据库的次数。
§ max_user_conntions:表示单个用户同时连接数据库的次数。
§ 2.添加用户
· （1)CREATE USER 语句的语法格式为
o create user “用户名”@”主机号” identified by “密码”;
· （2)INSERT语句的语法格式为：
o insert into user (host,user,password,ssl_cipher,x509_issuer,x509_subject) values(“主机号”,”用户名”,password(“密码”),””,””,””);
o 【任务8.2】授予、回收数据库用户权限
§ 前导知识
· 新添加的数据库用户既不允许访问其他用户的数据库，也不能创建自己的数据库，只有在授予了相应的权限后才能访问或创建数据库，为满足MySQL服务器的安全需要考虑以下内容。
o ①多数用户只需要对数据表进行读、写操作，只有少数用户需要创建、删除数据表。
o ②某些用户需要读、写数据而不需要修改数据。
o ③某些用户允许添加数据而不允许删除数据。
o ④管理员用户需要有管理用户的权力，而其他用户则不需要。
o ⑤某些用户允许通过存储过程来访问数据，而不允许直接访问数据表。
§
§ 用户权限
o 【任务8.3】数据库用户管理
§ 1.修改用户的名称
· RENAME USER <' 旧的用户名＇＞＠＜＇主机＇＞TO<'新的用户名＇＞＠＜＇主机＞；
· 说明：RENAME USER 语句可以对用户进行重命名，该语句可以同时对多个已存在的用户进行重命名，各个用户之间使用逗号分隔，重命名时“旧的用户名”必须已经存在，并且“新的用户名”还不存在，使用者必须拥有“RENAME USER”权限。
§ 2.修改用户的密码
· （1)使用mysqladmin命令修改用户密码的语法格式为：mysqladmin-u<用户名＞ [-h<主机＞］－p password[<新密码＞］说明：“mysqladmin”是一条外部命令，必须在服务器端的“命令提示符”下执行。
· （2)使用SET PASSWORD语句修改用户密码的语法格式为：
o SET PASSWORD[FOR<'用户名＇＞＠＜＇主机＇＞］＝PASSWORD(<'新密码＞） ;
· （3)使用UPDATE语句修改用户密码的语法格式为：
o UPDATE mysql.user SET Password=PASSWORD(<'新密码＇＞）WHERE User=<'用户名＇＞AND Host=<'主机＇＞；
§ 3.删除用户
· （1)使用DROP USER 语句删除用户的语法格式为：
o DROP USER<'用户名＇＞＠＜＇主机＇＞；
· 2)使用DELETE语句删除用户的语法格式为：
o DELETE FROM mysql.user WHERE User=<'用户名＇＞ AND Host=<'主机＇＞；
· 项目1 MySQL映像
o 【任务1.1】尝试接触数据库
§ 1.MySQL概述
· MySQL是一个关系型数据库管理系统
· 特点：体积小、速度快、总体拥有成本低，开放源码
§ 2.数据库的技术构成
· 数据库
o 用于存储数据的地方
· 数据库管理系统（DBMS）
o 用于管理数据库的软件
· 数据库应用程序
o 为了提高数据库系统的处理能力所使用的管理数据库的软件补充
§ 3.客户端-服务器结构
· 主从式架构或客户端/服务器（简称C/S结构）是一种网状结构
§ 登入命令中的”mysql - h 127.0.0.1 - u root -p“。-h后面的是服务器的IP地址，本机为”localhost"或“127.0.0.1”。
o 【任务1.2】MySQL的命令行工具
§ 1.结构化查询语言（SQL)
· DDL 数据定义语言：主要用于定义数据库、数据表等
· DML 数据操作语言：主要用于对数据库中的数据进行添加、修改、和删除操作
· DQL 数据查询语言：主要用于查询数据
· DCL 数据控制语言：主要用于控制用户的访问权限
§ 2.my.ini
· my.ini是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的
o 【任务1.3】图形管理工具Navicat
§ Navicat是一套快速、可靠、便宜的数据库管理工具
· Oracle:良好的兼容性、可移植性、可连接性
· SQLServer：界面良好、易于操作
· MySQL：运营成本低
· 项目2 创建和维护MySQL数据库
o 【任务2.1】使用图形化工具创建数据库
§ 1.字符集及字符序
· MySQL默认使用的是latin1符集
· 字符是人类语言最小的表意符号
· 给一系列的字符并赋予对应的编码后，所有的这些“字符和编码对”组成集合就是字符集
· 字符序是指在同一个字符集内字符之间的比较规则
· 使用“SHOW CHARACTER SET"可以查看当前MySQL服务实例支持的
o 字符集、字符集默认的字符序，以及字符集占用的最大字节长度等信息
o latin1：支持西欧字符、希腊字符等
o gbk：支持中文简体字符
o utf8：几乎支持世界上所有国家的字符
§ 2.MySQL字符集的设置
· 通过修改my.ini配置文件，可修改MySQL默认的字符集
§ 3.MySQL储存引擎
· 数据库储存引擎是数据库底层软件组件
o 数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作
o 可以使用SHOW ENGINES语句查看系统所支持的引擎类型
§ InnoDB（是事务型数据库的首选引擎）：支持事务处理，支持外键
§ MyISAM：插入数据快，空间和内存使用率比较低
§ MEMORY：所有的数据都在内存中，数据处理速度虽快，安全性不高
o 【任务2.2】使用命令语句创建、修改和删除数据库
§ 新建数据库：create database 数据库名;
§ 删除数据库：drop database 数据库名;
§ 修改数据库默认字符集和排序规则：alter database 数据库名 default character set gbk（字符集名）;
§ 修改数据库默认字符集和排序规则：ALTER DATABASE 数据库名 CHARACTER SET GBK COLLATE 排序规则;
o 【任务2.3】使用语句方式选择与查看数据库
§ 选择当前的数据库：USE 数据库名;
§ 查看数据库的建库信息：show create database 数据库名;
· 项目3 创建和维护MySQL数据表
o 【任务3.1】数据表的结构及数据完整性设计
§ 1.关系型数据库
· 建立在关系模型（数学模型）上的数据库。
o 关系模型包含三个方面
o 数据结构：二维表，解决数据如何存储
o 操作指令集合：所有的SQL语句，解决如何处理数据
o 完整性约束：表内数据约束（字段与字段），表与表之间的约束(外键）
§ 2.数据类型
· MySQL中的数据类型分为三大类：数值型、字符串型、时间日期型
· 储存范围较大的日期最好使用datetime类型
· varchar类型是按实际长度存储，比较节省空间，检索速度比char慢
· text类型不能指定，不能有默认值
· enum类型和set类型的值都是以字符串形式出现的，但在库中存储的是数值
· blob类型存储的是二进制字符串、主要存图片、音频信息，text类型存储的是非二进制字符串、只能存储纯文本内容
§ 3.数据类型的属性
·
· 无符号：unsigned
· 指定一个字符集：character set <字符集名>
· 数值 类型前自动用0补足位数：zerofill
· 列描述：comment
§ 4.MySQL的约束
· 主键、外键、唯一、非空、默认值约束、自增
o 【任务3.2】使用语句创建数据表及其约束
§ 1.使用CREATE TABLE 语句创建数据表及其约束
· 创建表 和定义字段：create table 表名（name char(5),sex enum("1","0"), height int);
· 表的约束
o 单字段主键示例
o 多字段主键示例
o 外键示例
§ 一张表不能有同名外键
§ 2.使用语句查看数据表结构
· 查看基本结构语句：DESCRIBE/DESC
· 查看表详细结构语句：SHOW CREATE TABLE<表名>;
o 如果显示的结果非常混乱，就在语句命令后加上参数'\G'，可使结果直观、易于查看
o 【任务3.3】使用图形管理工具创建数据表
o 【任务3.4】修改、删除数据表结构
§ 1.使用ALTER TABLE语句修改、删除表结构
· （1）更改数据表名称：alter table 旧表名 rename to 新表名;
· （2）修改表的字段名和字段信息：alter table 数据表名 change 旧字段名 新字段名 新数据类型;
· （3）数据表添加字段：alter table 数据表名 add 字段名 数据类型;
§ 2.使用语句删除数据表
· （4）删除数据表中字段名：alter table 数据表名 drop 字段名;
o 【任务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 “条件”;
o 【任务3.6】数据转储
§ 备份单个数据库：mysqldump -u用户名 -p密码 数据库名>文件名.sqlmysqldump -u用户名 -p密码 数据库名>文件夹全路径\文件名.sql
§ 备份多个数据库：mysqldump -u用户名 -p密码 --databases 数据库名1 数据库名2>文件名.sql
§ 备份所有数据库mysqldump -u用户名 -p密码 --all-databases>文件名.sql
· 项目4 MySQL数据表的检索
o 【任务4.1】查询时选择列
§ 1.基本查询语句
· 查看表中所有列字段数据：select * from 表名;
· 查看表中部分字段数据select 字段名1,字段名2,…, from 表名;
· 取别名：字段名 [AS] 字段别名;
o 【任务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;
o 前可加NOT，表示不在这个范围
o 示例
§ 4.带LIKE的字符匹配查询（模糊查询）
· SELECT 字段1,字段2，... ,字段n FROM 表名 WHERE 字段名 LIKE 条件;
· 示例
o 以“a”开头的数据
o 长度为三的数据
§ 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 表名 ;
o 示例
§ 9.显示前N 行 （限制条数）
· SELECT DISTINCT 字段1,字段2，... ,字段n FROM 表名 LIMIT [位置偏移量,] 行数;
o 示例
o 【任务4.3】查询结果排序
§ 排序——正序排序
· select * from 表名 order by 字段名;
o
· select * from 表名 order by 字段名ASC;（默认正序）
o
§ 排序——倒序排序
· select * from 表名 order by 字段名DESC;（倒序）
o
o 【任务4.4】查询的分组与汇总
§ 1.聚集函数
·
§ 2.分组查询
· [GROUP BY 字段名] [HAVING <条件表达式> ]
· 示例
o
o
§ 3.SELECT 之句顺序
·
o 【任务4.5】创建多表连接查询
§ 1.交叉连接查询
· SELECT * FROM 表名1 CROSS JOIN 表名2;
o 示例
§ 2.内连接查询
· SELECT 查询字段 FROM 表名1 [INNER] JOIN 表名 2 ON 表名1.关系字段 = 表名2.关系字段;
o 示例
§ 3.外连接查询
· 左连接：
o select * from 表名1 left join 表名2 on 表名1.表1字段名 = 表名2.表2字段名;
o 示例
· 右连接：
o select * from 表名1 right join 表名2 on 表名1.表1字段名 = 表名2.表2字段名;
o 示例
§ 4.复合条件连接查询
· 在连接查询的过程中，通过添加过滤条件来限制查询结果
o 【任务4.6】创建子查询
§ 1.带IN关键字的子查询
· SELECT 查询字段 FROM 表名 WHERE 字段名[NOT] IN (SELECT 语句）；
· 示例
o 查询Daniel老师授课地点
o 教师姓名中不含A的老师老师授课地点
o 教师姓名中含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 语句）;
· 示例
o 【任务4.7】创建多表联合查询
§ 1.使用UNION
· SELECT 查询字段 FROM 表名UNION [ALL]SELECT 查询字段 FROM 表名;
§ 2.UNION规则
· （1）必须由两条或者两条以上SELECT语句组成语句之间使用UNION分割
· （2）UNION中的每个查询必须包含相同的列、表达式或聚合函数
· （3）列数据类型必须兼容
o 【任务4.8】使用Navicat生成查询
示例
外键示例
(1) 运算符的优先级
示例
示例
教师姓名中含A的老师其他老师授课地点
示例
示例
多字段主键示例
示例
示例
长度为三的数据
表的约束
以“a”开头的数据
示例
示例
示例
示例
查询Daniel老师授课地点
示例（EXISTS后为真，则有返回值，假则反之）
教师姓名中不含A的老师老师授课地点
示例
单字段主键示例
用户权限
