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