首页  思维导图  详情

MySQL 数据库项目式教程

2022-05-26 16:08:01   0  举报





AI智能生成

数据库全程概念思维导图

项目管理流程图

作者其他创作

大纲/内容

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

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

字符集及字符序

使用MySQL命令”show character set“；查看当前MySQL服务实列支持的字符集、字符集默认的字符序，以及字符集占用的最长字节长度等信息

MySQL字符集的设置

通过修改my. ini 配置文件，可修改 MySQL默认的字符集。
(2) MySQL提供一些 MySQL命令，可以临时修改 MysQL 当前会话的字符集万
字符序。
例如，“SET CHARACTER SET_ CLIENT= GBK;”可以将当前会话的
MySQL客户机字符集临时设置为GBK。
(3)使用MySQL命令“SET NAMES GBK;"。可以临时一次性地设置CHARAC
TER SET_CLIENT、 CHARACTER SET_CONNECTION 及 CHARACTER SET
RESULTS 的字符集为 GBK。
与0用中
(4)连接MySQL服务器时指定字符集。在命令行输人连接 MySQL命令
“MySQL -DEFAULT_CHARACTER SET=GBK H服务器IP地址-U用户名一P".
连接成功后可以临时一次性地设置 CHARACTER SET_CLIENT、 CHARACTER
SET CONNECTION及CHARACTER SETRESULTS的字符集为 GBK.

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

创建数据库命令语句

create database 数据库名

修改数据库默认字集命令语句

alter database 数据库名 default character set gbk;

删除数据库命令语句

drop database 数据库名；

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

使用数据库，查看数据库

use 数据库名，show databases;

任务2.4使用图形工具备份、还原MySQL的数据库

使用Navicat备份数据库数据库名

mysqldump -u用户名 -p密码数据库名>文件名.sql

备份多个数据库

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

还原数据库

mysql -u用户名 -p密码数据库名<文件名.sql

项目4 MySQL数据表的检索

任务4.1查询时选择列

1.基本查询语句

MySQL从数据表中查询数据的基本语句为select语句

SELECT
{* x |<字段列表>}
[ FROM
<表1>,<表2>..
「WHERE
<表达式>]
「GROUP BY。
<group by definition>
CHAVING <expression> [(operator) <expression>)..]
[ORDER BY <order by definition>]
[LIMIT[<offset>,]<row
count>]
SELECT [字段1,字段2,,字段n]
FROM [表或视图]
WHERE[查询条件];

2.检索所有列

select * from 表名;

select 字段1，字段2，字段3.....,字段n from 表名;

3检索指定列

select 字段名 from 表名;

select 字段1，字段2，字段3.....,字段n from 表名;

简写字段名：（字段名 as 字段别名）

任务4.2查询时选择行

1.查询指定记录

select 字段名1，字段名2，字段3，......,字段名n,from 表名 where 条件;

2.带in关键字的查询

select 字段名1，字段名2，字段3，......,字段名n,from 表名 where 字段名 in (值1，值2，.....,);

3.带betweed and 的范围查询

select 字段名1，字段名2，.....,字段名n from 表名 where 字段名 between 值1 and 值2;

4.带like的字符匹配查询

select 字段名1，字段名2，.....,字段名n from 表名 where 字段名 like “...%...”

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字段名1,字段名2..字段名n FROM表名ORDER BY字段名1[ASC|DESC],字段名2,[ASC|DESC].....;

任务4.4查询的分组和汇总

1.聚集函数

函数         作用
AVG()       返回某列的平均值
COUNT() 返回某列的行数
MAX()      返回某列的最大值
MIN()      返回某列的最小值
SUM()     返回某列值之和

2.分组查询

group by 字段名 having 条件表达式

3.select 子句顺序

子句说明是否必须使用
SELECT 要返回的列或表达式是
FROM      从中检索数据的表        仅在从表选择数据时使用
WHERE   行级过滤否
GROUP BY 分组说明   仅在按组计算聚集时使用
HAVING   组级过滤否
ORDER BY 输出排序顺序   否

任务4.5创建多表连接查询

交叉连接查询

select * from 表名1 cross join 表名2;

内连接查询

select 查询字段 from 表名1 inner join 表名2 on 表名1.关系字段 = 表名2.关系字段;

外连接查询

select 查询语句 from 表名1 left outer jion 表名2 on 表名1.关系字段 = 表名2.关系字段;

select 查询语句 from 表名1 right outer jion 表名2 on 表名1.关系字段 = 表名2.关系字段;

复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件来限制查询的结果，使查询结果更加准确。

为表去别名

select * from 表名 as 表别名;

任务4.6创建子查询

带in关键字的子查询

select 查询字段 from 表名 where 字段名 not in （select 语句);

带exists关键字的子查询

select 查询字段 from 表名 where not exists (select 语句)；

带any、some 关键字的子查询

select 查询字段 from 表名 where 字段名比较运算符 any|some (select 查询语句);

带 all 关键字的子查询

select 查询字段 from 表名 where 字段名比较运算符 all (select 语句);

任务4.7创建多表联合查询

使用union

select 查询字段 from 表名
union all
select 查询字段 from 表名;

union 规则

UNION 非常容易使用，但在应用过程中需要注意以下几条规则
①UNION 必须由两条或两条以上的SELF ECT 语句组成，语句之间用关键字 UNION分隔(因此，如果组合 4条 SELECT 语句们，将要使用3个UNION关键字)。
②UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过，各个列不
需要以相同的次序列出)。
③列数据类型必须兼容:类型不必完全相同，但必须是 DBMS可以隐式转换的类型(如不同的数值类型或不同的日期类型)。
如果遵守了这些基本规则或限制，那么可以将UNION 用于任何数据检索操作。

包含或取消重复的行

UNION从查询结果集中自动去除重复的的行，换句话说，它的行为与一条 SELECT语句中使用多个WHERE子句条件一样。女如果想返回所有的匹配行，要使用 UNION ALL 而不是 UNION。

对联合查询结果排序

SELECT 语句可以使用 ORDER BY子句排序。在使用 UNION 联合查询时，只能使用后条 ORDER BY子句，它必须位于最后一条 SELECT 语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条 ORDER BY 子句

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

任务6.1 创简单存储过程

创建存储过程：
Create procedure 存储过程名
Begin
…………
…………
end

调用存储过程
Call 存储过程名

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

delimiter //
create procedure pro_test()
begin
set @_sql = 'select ? + ?';
set @a = 5;
set @b = 6;
PREPARE stmt from @_sql; // 预定义sql
EXECUTE stmt USING @a,@b;// 传入两个会话变量来填充sql中的 ?
DEALLOCATE PREPARE stmt; // 释放连接
end //
delimiter //
CREATE PROCEDURE pro_stu(in order_param VARCHAR(50),in startindex int ,in size int)
BEGIN
     set @v_sql = "select * from student s ORDER BY ?  LIMIT ?,?";
     PREPARE stmt from @v_sql;
      set @a = order_param;
      set @b = startindex;
      set @c = size;

      EXECUTE stmt using @a,@b,@c;
      DEALLOCATE PREPARE stmt;
end//
delimiter;

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

带参数的存储过程
create procedure proc_name

@id int,

@name nvarchar(50)

as

select * from 表 where name=@name and id=@id

go

调用时：

exec proc_name 22,'李四'

带输出参数的存储过程

create proc proc_name

@count int output,

@name nvarchar(50)

as

select @count=count(*) from 表 where name=@name

go

调用时：

declare @count int

exec proc_name @count output,'李四'

select @count

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

declare youbiao cursor
for
select SubID,SubType,SubMoney,SubYear,ChargeDep
from table2
where pid=@feeStandard
--打开游标
open youbiao
--从游标里取出数据给变量赋值
fetch next from youbiao
into @subID,@subType,@subMoney,@subYear,@chargeDep
begin
--判断游标的状态
-- 0 fetch语句成功
-- -1 fetch语句失败或此行不在结果集中
-- -2 被提取的行不存在
while @@fetch_status = 0

begin
set @stuPayData = (select isnull(count(*),0) from table1 where SubYear=@subYear and SubID=@subID and StuID=@studentID )
if(@stuPayData = 0)
begin
insert into table1(StuID, DepName, ClassName, FeeID, SubID, SubType, ShouldPayment, SubYear, ChargeDep, CUser, CTime)
select @studentID, DepName, ClassName, @feeStandard, @subID, @subType, @subMoney, @subYear, @chargeDep, @cuser, getdate() from StudentInfo where id=@studentID
end

--用游标去取下一条记录
fetch next from youbiao into @subID,@subType,@subMoney,@subYear,@chargeDep

end

end
--关闭游标
close youbiao
--撤销游标
deallocate youbiao

END

任务6.5 创建与使用事务

1.事务的ACID特性

(1)原子性。一个事务(transaction) 中的所有操作，要么全部执行，要么全部不
执行。
(2)一致性。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
(3)隔离性。MySQL 数据库允许多个并发事务，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
(4)持久性。事务处理结束后，对数据的修改就是永久的。

2.事务控制语句

开始事务。其语法格式为:

start transaction；

提交事务。其语法格式为:

commit;

设置保存点。其语法格式为

savepoint 保存点名称

撤销事务。其语法格式为:

rollback;|rollback to savepoint 保存点名称

项目1 MySQL印象

任务 1.1尝试接触数据库

MySQL概述

MySQL是一个关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据库放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL特点：
速度。MySQL运行速度很快。
容易使用。MySQL是一个高性能且相对简单的数据库系统
价格：MySQL对于大多数个人用户来说是免费的，对于企业需要购买商业许可
支持查询语言。
性能：许多客服机可同时来连接到服务器，并同时使用多个数据库。
连接性安全。

数据库的技术构成

数据库系统是由硬件部分和软件部分共同构成。

(1 )数据库。用于储存数据的地方
（2）数据库管理系统。用于管理数据库软件。
（3）数据库应用程序。为了提高数据库系统的处理能力所使用的管理数据库的软件补充。

数据库用户调用数据过程

数据库管理系统(Data Base Management System,
DBMS)是用户创建、管理和
维护数据库时所使用的软件，位于用户与操作系统之间，对数据库进行统一管趸，
DBMS 能定义数据存储结构，提供数据的操作机制，维护数据库的安全性、完整性和
可靠性，

客户端-服务器管理结构

主从式架构通过不同的途径应用于许多不同类型的应用程序，如在因特网上使用
的网页。例如，当顾客想要在某网上书店买书时，计算机和网页浏览器就被当作一个
客户端，同时，组成某网上书店的计算机、
数据库和应用程序就被当作服务器。当顾
客的网页浏览器向某网上书店请求搜寻与数
据库相关的图书时，某网上书店服务器从
它的数据库中找出所有该类型的图书信息，并形成一
一个网页，再发送到顾客的浏览器
服务器端一般使用高性能的计算村
，并配合使用不同类型的数据库，
如 Oracle、
Se或 MysQL等。客户端需要安装专门6
的软件，如专门开发的客户端工具浏览

任务1.2MySQL的命令行工具

结构化查询语言

sQL包含以下几个部分
①数据定义语言(Data Definition Language,
DL):用于创建、删除、更改数据
库对象，代表语句有CREATE、 DROP、 ALTER等。
②数据操作语言(Data Manipulation Language,
, DML):用于对数据进行操作，
代表语句有SELECT、 INSERT、UPDATE、 DELETE等。
③数据控制语言(Data Contol Language,
DCL):主要负责用户权限管理，代表
指令有GRANT、 REVOKE、 COMMIT、 ROLLBACK等。

my，ini

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

任务1.3图形管理工具navicat

使用Navicat建立并打开链接

在Navicat中查看MySQL已有数据库。

查看数据库”mysql“中已有的表

使用Navicat命令列

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

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

关系型数据库

(1)关系型数据库是一种建立在关系模型(数学模型)上的数据库。关系模型是
-种建立在关系上的模型，包括以下3个方面。
①数据结构:二维表，解决数据如何存储。
②操作指令集合:所有的SQL语句，解决如何处理数据。
③完整性约束:表内数据约束(字段与字段)，表与表之间的约束(外键)。

①储存在单元中的数据必须是原子的。每个单元只能存储一条数据，这也称信息
原则。违反这一条将不能运用良好的设计原则。当一个单元包含多于一条的信息时，
称为信息编码。在这样的情况下，是否采用违背理论的方案是一个设计的选择问题，
尽管在多数情况下，结果证明这对数据的完整性是不利的。
②储存在列下的数据必须具有相同的数据类型③每行都是唯一的(没有完全相同的行)④列没有顺序。⑤行没有顺序李出治市的分的长⑥列有一个唯一性的名称。

数据类型

数据类型的属性

MySQL关键字含义
null   数据列可包含null值
not   数据列不允许包含null值
default 默认值
Dprimary key   主键
autoincrement   自动递增，适用于整数类型
unsigned    无符号
character set <字符集名>    指定一个字符集
zerofill 数值类型前自动用0补足位数
comment 列描述

MySQL的约束

MySQL的约束主要包括主键约束、外键约束、唯一约束、非空约束和默认值约束

数据库设计的三大范式

第一范式(1NF):每一列属性都是不可再分的属性值，确保每一列的原子性。两
列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。满足第
一范式是关系模式规范化的最低要求，否则将有很多基本操作在这样的关系模式中实
现不了。
第二范式(2NF):满足1NF 后，要求表中的所有列都必须依赖于主键，而不能有
任何一列与主键没有关系，即一个表只描述一件事情。
第三范式(3NF):必须先满足第二范式(2NF)，要求表中的每一列只与主键直接
相关，而不是间接相关(表中的每一列只能依赖于主键)。
总结:第一范式和第二范式的区别在于有没有分出两张表，第二范式是在一张表
中包含了几种不同的实体属性，那么必须分成多张表，第三范式要求已经分成了多张
表，那么一张表中只能有另一张表中的d (主键)，而不能有其他的任何信息(其他的
信息一律用主键在另一张表中查询)。

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

使用create table 语句创建数据表及约束

create table 数据表名(
       字段名类型名 comment’备注’
       ~~~~~~~~~~
       );                   （在结尾时不能加逗号）

<字段名><数据类型>primary key 默认值

constraint <主键约束名>primary key<字段名>

constraint <主键约束名>primary key<字段名><字段名>

<字段名><数据类型>unique唯一

constraint<唯一约束名> 唯一 unique

not null 空值

外键语法：字段别名一般为fk_本表字段名】【constraint fk_表名_字段名 foreign key (表名_字段名) references 表名(字段名)】

default 默认值

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

创建数据表

单击”新建表“按钮

单击“名”输入栏

定义完一个字段后，单击“添加字段”

在表中设计器字段

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

使用alter table 语句修改、删除表结构

alter table 旧表名 rename to 新表名;

修改字段类型的数据类型

alter table 表名 modify 字段名数据类型;

修改字段名，其语法类型

alter table 表名 change 旧字段名新字段名新数据类型;

添加字段

alter table 表名 add 新字段名数据类型约束条件 first after 已存在字段名;

删除字段

alter table 表名 dorp 字段名;

修改字段排列位置

alter table 表名 modify 待修改字段名数据类型 after 字段名;

alter table 表名 engine = 更改后的存储引擎名;

修改主键的各类约束。

alter table 表名 add primary key (字段1，字段2.....);

删除主键约束语法

alter table 表名 drop primary key;

添加外键约束

alter table 表名 add constraint 外键约束名 foreign key（外键字段）references 关联表名关联字段;

删除外键的约束的语法

alter table 表名 drop foreign key 外键约束名；

添加唯一的语法

alter table 表名 add constraint 唯一约束名 unique 字段名；

删除唯一约束名

alter table 表名 drop index 唯一约束名;

使用语句删除数据表

drop table 表名;

drop table [if exists] 表1，表2，表3

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

使用命令语句插入数据

指定字段插入数据；

insert into 表名（字段名1，字段名2）values（”值1”，”值2”）；

14. 所有字段插入数据

insect into 表名 values（”值1”，”值2”…）；

更新数据

update 表名 set 字段名 = 值 where 条件；

删除数据

delete from 表名 where 条件;

任务3.6数据转存

使命命令语句转存数据

MySQLdump -h IP地址 -u 用户名 -p数据密码 database 数据库1，数据库2，数据库3>存储路径文件名.sql

项目5 创建与使用视图

任务5.1使用语句创建视图

1.创建视图

create or replace algorithm = {undefined|merge|tempt able
view view_name
as select_表名
with cascaded local check option

2.视图的规定和限制

创建视图前，应该知道它的一些限制。视图创建和使用最常见的规则和限制如下。①与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名称)。
②对于可以创建的视图数目没有限制。果 1321含号中图
③为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
④视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
⑤ORDER BY可以用在视图中，但如果在该视图检索数据SELECT中也含有 ORDER BY，那么该视图中的 ORDER BY将被覆盖。
⑥视图不能索引，也不能有关联的触发器或默认值。
⑦视图可以和表一起使用。例如，编写一条连接表和视图的 SELECT 语句。

create view view_视图名 as select 字段1，字段2....., from 表名;

select * from view 视图名;

任务5.2使用图形管理工具创建视图

添加数据表

用户在视图创建工具中新建视图，首先必须添加表到视图，从左侧数据库对象窗口拖动数据表到图表设计窗口或双击相应的数据表。

选择字段名

用户在视图创建工具界面的图表设计窗口中，选中相应数据表对象的字段名复选
框;也可以选中对象标题的复选框，即可包含所有字段。

删除对象

从视图创建工具界面的图表设计窗口中移除对象，单击对象标题上的关闭按钮。

添加表别名

在视图创建工具中添加表别名，用户只需在图表设计窗口双击表名并输入别名。

设置表间关系

通过两个字段连接数据库对象，从一个对象列表拖动一个字段到另一个对象列表，一条线将出现在连接的字段之间。
删除某些对象的全部连接，单击对象别名旁的“-”按钮。
转到语法窗口改变连接的关系，单击运算符并在弹出的菜单中选择属性项目，还可以通过列表(=、<>、<、<=、>、>=)改变关系条件，最后单击“确定”按钮进行保存。除此之外，还可以改变连接的类型。

设置输出字段

在图表设计窗口选择的字段将显示在语法窗口，设置它们的显示顺序，以及使用和修改视图输出字段。
<Distinct>:相同的记录不会包含在查询结果中。
<func>:为每个栏位设置聚集函数(SUM、MANMIX、AVG、COUNT)。<别名>:改变输出查询字段名。

设置条件

添加一个查询条件，在语法窗口的WHERE子句单击“<按这里添加条件>”单击“<-->，<-->”中的“<-->”从当前所建查询中添加的所有数据表的字段列表中选择一个字段。
定义条件，在“编辑”选项卡中输人值，单击“=”设置条件运算符。

设置组条件

可以从语法窗口的GROUP BY 子句为组查询记录设置条件，它们以设置条件相同的方法设置，条件将会包含在当前的HAVING语句中

设置排序条件

可视化创建工具可以从语法窗口 ORDER BY 子句设置排序的方法，单击ASC或 DESC可改变排序方向。

设置限制准则

LIMIT 子句用于在指定的范围限制查询记录，可以用它来显示前X条记录，格式为LimitX，X参数指的是返回从0条开始的X条记录行;也可以显示由X~Y之间的记录，格式为Limitx，Y，X参数指定第一个返回记录行的偏移量(

任务5.3 修改视图

ALTER VIEW <视图名> AS <SELECT语句>

alter view view_视图名 as select 字段1，字段2，字段n，from 表名 where 条件;

任务5.4 利用视图更新数据表

update view_视图名 set 条件 where 条件;

任务5.5 删除视图

drop view 视图名;

创建与使用触发器

任务7.1创建触发器

create trigger 触发器名触发时刻触发事件 on 表名 for each row动作;
create trigger tr log stu in after insert on stu for each row insert into log values (......);
create trigger tr log stu up before update on stu for each row insert into log values (......);
create trigger tr log stu de before delete on stu for each row insert into log values (......);

任务7.2查看及删除触发器

查看触发器
show triggers;
删除触发器
drop trigger 触发器名;

数据库的安全性维护

任务8.1添加数据库用户

create语句创建用户
create user "用户名"@"主机号"identified by"密码";
set语句修改密码不需要刷新权限
set password =password("密码"); set password for "用户名"@"主机号"= password("密码");直接生效

drop语句删除用户
drop user "用户名"@"主机号";

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

刷新权限 flush privileges；

任务8.3数据库用户管理

insert语句创建用
insert into user (hostuser,passwordssl cipherx509 issuer,x509 subject)values("主机号"，"用户名"，password("密码")，""，""，"");
update语句修改用户密码需要刷新权限
update user set password=password("密码")
where host="主机号"and user="用户名"; 刷新权限后生效
delete语句删除用户
delete from user where host="主机号"and user="用户名";