MySQL数据库配置及高级操作【增删改查、约束、事务、多表、范式、索引、视图、存储、触发器、DCL用户、备份还原】

FisrtBqy

已于 2023-04-10 10:57:40 修改

阅读量948

点赞数

分类专栏： Java 文章标签：数据库 mysql database

于 2022-03-14 16:38:43 首次发布

本文博主FisrtBqy。文章内容仅供学习交流，未经允许请勿用于论文写作、竞赛答辩、书刊出版、商业活动等。如有问题请联系作者：[email protected]

本文链接：https://blog.csdn.net/FisrtBqy/article/details/123481945

版权

Java 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

MySQL数据库配置及高级操作【增删改查、约束、事务、多表、范式、索引、视图、存储、触发器、DCL用户、备份还原】

1 环境配置
2 基本操作
3 约束
4 事务
5 多表
6 数据库设计
- 6.1 三范式
- 6.2 反三范式
7 MySQL高级应用

1 环境配置

1.1 MySQL安装与卸载

下载地址: mysql-installer-community-5.7.28.0.msi
安装

按照提示一步步操作即可，有几个需要注意的地方：
如果需要配置环境变量，①新建MYSQL_HOME变量,并配置: C:\Program Files\MySQL\MySQL Server 5.7；②编辑path系统变量，将 %MYSQL_HOME%\bin添加到path变量后。

这样就可以通过命令行登录了：

mysql -u用户名 -p密码

例如：mysql -uroot -p123456
卸载

找到 Mysql安装目录下的 my.ini 文件，找到定义datadir的语句，datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data，这是用来保存数据文件的目录，将其复制；在控制面版卸载MySQL后，找到之前复制的路径，删除即可。

1.2 SQLyog安装

下载地址: SQLyog 12免费版

1.3 SQL简介

分类	说明
数据定义语言	DDL(Data Definition Language)，用来定义数据库对象：数据库、表、列等。
数据操作语言	DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。
数据查询语言	DQL(Data Query Language)，用来查询数据库中表的记录。
数据控制语言	DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。

数据库（初始化生成）	作用
information_schema	信息数据库，存放其他数据库的信息。
mysql	MySQL的核心数据库，保存用户和权限
performance_schema	保存性能相关数据，监控MySQL的性能。
sys	记录了DBA所需要的一切信息，让DBA快速了解数据库的运行情况。

2 基本操作

2.1 DDL(Data Definition Language)

2.1.1 查询操作

-- 查询当前数据库
SELECT DATABASE();
-- 查询MySQL中有哪些数据库
SHOW DATABASE;
-- 切换数据库
USE 表名;  -- 例如USE db_1;
-- 查看表结构
DESC 表名

2.1.2 创建操作

-- 创建数据库并指定字符集
CREATE DATABASE db_1 CHARACTER SET utf8;
-- 创建表的语法格式
CREATE TABLE 表名 {
	字段1名称 字段类型(长度),
	字段2名称 字段类型(长度),
	字段3名称 字段类型  -- 最后一个字段不加逗号
};
-- 创建一个与指定表结构相同的表
CREATE TABLE test2 LIKE test1;
-- MySQL常见的数据类型：int double char(字符串,指定后占用固定长度) varchar(字符串，指定后占用实际长度) date datetime

2.1.3 删除操作

-- 永久删除数据库，删除内容和定义，释放空间
DROP DATABASE 数据库名;
-- 永久删除表
DROP TABLE 表名;
-- 先判断后删除
DROP TABLE IF EXISTS test1;
-- 删除字段（列）
ALTER TABLE TableName DROP column_name;

2.1.4 修改操作

-- 修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET utf8;
-- 修改表的名称
RENAME TABLE 旧表名 TO 新表名;
-- 向表中添加字段
ALTER TABLE TableName ADD column_new VARCHAR(20);
-- 修改表中列的类型或长度
ALTER TABLE TableName MODIFY column_name CHAR(10);
-- 修改字段名
ALTER TABLE TableName CHANGE column_name column_New_name VARCHAR(10);

2.2 DML(Data Manipulation Language)

2.2.1 增加数据

-- 向表中插入数据
-- 1.插入指定字段的值
INSERT INTO TableName (column_name1,column_name2,column_name3) VALUES(123,'张三','abc');
-- 如果是一组数据包含了全部字段，也可以这么写：
INSERT INTO TableName VALUES(123,'张三','abc');
-- 在插入varchar char date 类型的数据时，必须用单引号或双引号包裹

2.2.2 修改数据

-- 把指定表中的某列的值全部修改
UPDATE TableName SET column_name = 值
-- 把指定表的某列中值为指定值的数据全部修改
UPDATE TableName SET column_name = 值 ,WHERE 条件表达式:column_name = 值
-- 例如
UPDATE student SET age = 20, name = 'zhangsan' WHERE sid = 6;

2.2.3 删除操作

-- 删除一条数据
DELETE FROM TableName WHERE column_name = 值;
-- 删除指定表中所有数据
DELETE FROM TableName;  -- 逐条删除
TRUNCATE TABLE TableName; -- 删除整张表，然后再创建一个一模一样的表

2.3 DQL(Data Query Language)

2.3.1 基本查询操作

查询操作并不会对原有数据修改，只是取出数据并可以按照语句操作后显示。

-- 查询表中的所有数据
SELECT * FROM TableName;  -- * 表示所有的列（字段）
-- 查询所有数据，只显示指定列（字段）
SELECT column1_name,column2_name FROM TableName;
-- 查询所有数据，然后给列名改为中文
SELECT 
	column1_name AS '中文别名1',
	column2_name AS '中文别名2',
	column3_name '中文别名3'  -- AS可以省略
FROM TableName;
-- 去重查询
SELECT DISTINCT column_name FROM TableName;
-- 数据运算并显示
SELECT column_name,column2_name + 100 AS column2_name FROM TableName;

2.3.2 条件查询

一般条件插询

-- 查询表中指定列值为指定值的数据
SELECT * FROM TableName WHERE column_name = 指定值;
-- 查询表中指定列值不为指定值的数据
SELECT * FROM TableName WHERE column_name != 指定值;     -- 或者用<>符号
-- 查询表中指定列值为指定范围的数据
SELECT * FROM TableName WHERE column_name BETWEEN 特定范围下限 AND 特定范围上限;
-- 查询表中指定列值匹配几个指定值的数据
SELECT * FROM TableName WHERE column_name IN(value1,value2,value3);

通配符的使用

-- 查询字段中含有指定字符的数据
SELECT * FROM TableName WHERE column_name LIKE '%天%';
-- 查询字段中以特定字符开头的数据
SELECT * FROM TableName WHERE column_name LIKE '天%';
-- 查询字段中以特定字符结尾的数据
SELECT * FROM TableName WHERE column_name LIKE '%天';
-- 查询字段中第二个字符为特定字符的数据
SELECT * FROM TableName WHERE column_name LIKE '_天%';
-- 查询指定字段值为NULL的数据
SELECT * FROM TableName WHERE column_name IS NULL;
-- 查询指定字段值不为空的数据
SELECT * FROM TableName WHERE column_name IS NOT NULL;

limit关键字

-- 查询指定表中某一列的数据，从第offset行开始共length条
SELECT column_name FROM TableName LIMIT offset,length;

2.3.3 排序查询

使用order by语句

SELECT column_name FROM TableName [WHERE column_name = value] ORDER BY column_name [ASC/DESC]

其中ASC为升序排序（默认），DESC为降序排序

-- 单列排序(按照某一个字段排序)
SELECT * FROM TableName ORDER BY column_name;
-- 组合排序（单列排序后若有相同数据，按照另一个指定的字段排序）
SELECT * FROM TableName ORDER BY column1_name,column2_name;
-- 如果需要降序，则在需要降序的字段名后加上DESC
SELECT * FROM TableName ORDER BY column1_name DESC,column2_name DESC;

2.3.4 聚合函数

-- 查询表中数据的条数，或者指定列中数据（除去NULL）的条数
SELECT COUNT(*) FROM TableName;
SELECT COUNT(1) FROM TableName;
SELECT COUNT(column_name) FROM TableName;
-- 查询指定字段中的总和、最大值、最小值、平均值
SELECT
	SUM(column_name) AS '总和',
	MAX(column_name) '最大值',
	MIN(column_name) '最小值',
	AVG(column_name) '平均值'
FROM TableName
-- 查询指定字段中值大于给定值的数据的个数
SELECT COUNT(*) FROM TableName 	WHERE column_name > 100;
-- 查询指定字段中值大于给定值的数据的平均值
SELECT AVG(column2_name) FROM TableName WHERE column_name > 100;

2.3.5 分组查询

WHERE用于分组前过滤，后不能加聚合函数

HAVING用于分组后过滤，后可以加聚合函数

-- 通常配合聚合函数使用，比如将表按指定字段中的不同数据类型来分组，然后求各组的平均值
SELECT column1_name,AVG(column2_name) FROM TableName GROUP BY column1_name;

-- 分组之后进行条件过滤，则需要用HAVING关键字
-- 例如上述语句完成后再查看平均值大于100的情况
SELECT column1_name,AVG(column2_name) FROM TableName GROUP BY column1_name HAVING AVG(column2_name) > 100;

3 约束

约束是指对数据进行一定的限制，来保证数据的完整性、有效性、正确性

常见的约束：主键约束primary key，唯一约束unique，非空约束not null，外键约束foreign key

3.1 主键约束

特点：不可重复唯一非空；

用来表示数据库中的每一条记录；

语法格式：字段名字段类型 primary key；

3.1.1 主键的创建

-- 创建一个带有主键的表(方式一)
CREATE TABLE emp(
	eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);
-- 创建一个带有主键的表(方式二)
CREATE TABLE emp(
	eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    PRIMARY KEY(eid)
);
-- 创建一个带有主键的表(方式三)
CREATE TABLE emp(
	eid INT,
    ename VARCHAR(20),
    sex CHAR(1)
);
ALERT TABLE emp ADD PRIMARY KEY(eid);

3.1.2 主键的自增

关键字：auto_increment

主键的字段类型必须是整数型

-- 创建一个带有自增主键的表（主键每次自增加1）
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex CHAR(1)
);

-- 自定义自增的起始值为100
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT;
	name VARCHAR(20);
	sex CHAR(1)
) AUTO_INCREMENT = 100;

删除表时，可用DELETE或TRUNCATE关键字，但DELETE不会影响自增，而TRUNCATE会初始化自增。

3.2 非空约束

创建表时指定某一列不允许为空

字段名字段类型 NOT NULL;

3.3 唯一约束

创建表时指定某一列不能重复

但与主键不同的是对null值不做约束，一个表中只能有一个主键，但可以有多个唯一约束

字段名字段类型 UNIQUE;

3.4 默认值

创建表时用来指定某一列的默认值

字段名字段类型 DEFAULT 默认值

4 事务

由一个或多条SQL语句组成的一个整体，事务中的操作，要么全部成功，要么全部失败。

4.1 手动提交事务

-- 开启事务
START TRANSACTION;   -- 或者BEGIN;
具体SQL语句
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

4.2 自动提交事务（默认方式）

每执行一条DML语句，都是一个单独的事务

-- 关闭自动提交，改为手动提交
SET @@AUTOCOMMIT=OFF;

**原子性：**每个事务都是一个整体，不可再分，事务中所有SQL要么都i执行成功要么都执行失败。
**一致性：**事务在执行之前数据库的状态与事务执行后的状态一致。
**隔离性：**事务与事务之间不能相互影响，执行时要保证隔离状态。
**持久性：**一旦事务执行成功，对数据的修改是持久的。

4.3 事务的隔离级别

并发访问数据库时，通过设置隔离级别来解决冲突。

脏读：一个事务读取到了另一个事务没有提交的数据。

不可重复读：一个事务中两次读取的数据不一致。

幻读：一个事务中一次查询的结果无法支撑后续的操作。

4.3.1 隔离级别分类（安全级别递进）

**read uncommitted:**读未提交
**read committed:**读已提交（防止脏读）
**repeatable read:**可重复读（可防止脏读，不可重复读）
**serializable:**串行化（可防止脏读，不可重复读，幻读），当前窗口的事务需等上一个窗口的事务执行完毕后才可执行。

4.3.2 查看隔离级别

SELECT @@tx_isolation;

4.3.3 设置隔离级别

-- 语法：set global transaction isolation level 级别名称;
-- 设置后需要重新连接

5 多表

5.1 外键约束

外键约束可以让两张表之间产生一个对应的关系，从而保证主从表引用的完整性

外键是指从表中与主表的主键对应的字段

5.1.1 添加外键约束

-- 创建表时添加外键
CREATE TABLE 表名 (
	字段名    
    [constraint 外键约束名] foreign key(外键字段名) references 主表（主键字段）
);
-- 其中外键约束名为自定义，[]中的内容可不填写

-- 创建表之后添加外键
alert table 从表 add [constraint 外键约束名] foreign key(外键字段名) references 主表（主键字段）;

5.1.2 删除外键约束

-- 删除外键约束
alert table 从表 drop foreign key 外键约束的名称;

从表的外键类型必须与主表的主键类型一致
添加数据时，应该先添加主表的数据
删除数据时，要先删除从表的数据

5.1.3 级联删除

由于外键约束，删除主表数据前需要先删除从表的数据。为使删除主表的数据同时，删除所有相关联的从表数据，可在主表生成时添加级联操作。

-- 创建表时添加级联操作
on delete cascade;

5.2 多表关系

多表关系建立时，需要创建第三张表——中间表，中间表至少要有两个字段，分别对应另外两张表的主键字段。

5.2.1 内连接查询

通过指定的条件去匹配两张表中的内容，匹配不上的就不显示

-- 隐式连接
select 字段名 from 左表,右表 where 连接条件;

select * from products,category where c_id=p_id;
-- 通过定义别名
select
	p.`pname`,
	p.`price`,
	c.`cname`
from products p,category c where c.`category_id`=p.`p_id`;

-- 显式连接
select 字段名 from 左表 [inner] join 右表 on 连接条件

select * from products p inner join category c on p.`p_id`=c.`c_id` where p.`pname`='zhangsan';

5.2.2 外连接查询

-- 左外连接
-- 以左表为基准，匹配右表的数据，若能匹配到即显示，否则左表的数据正常显示而右表的数据为null
select 字段名 from 左表 left join 右表 on 连接条件

select * from category c left join products p on c.`c_id`=p.`p_id`;

-- 右外连接
-- 以右表为基准，匹配左表的数据，若能匹配到即显示，否则右表的数据正常显示而左表的数据为null
select 字段名 from 左表 right join 右表 on 条件

5.3 子查询

一条select语句的结果，作为另外一条select语句的一部分来执行

子查询必须要放在小括号中，通常作为条件使用

5.3.1 where型

-- 将子查询的结果作为父查询的比较条件使用
select
	p.`name`,
	p.`price
from products p where p.`price`<(select avg(products.price) from products)

5.3.2 from型

-- 将子查询的查询结果作为一张表使用
-- 必须给子查询的结果定一个别名
select * from category;  --子查询
select
	p.`pname`,
	p.`price`,
	c.cname
from products p
inner join(select * from category) c on p.`p_id`=c.cid
where p.`price`>100;

5.3.3 exists型

-- 子查询的结果是单列多行，作为父查询的in函数的条件使用
select * from category where c_id in(select p_id from products where price<100);

6 数据库设计

6.1 三范式

第一范式：列具有原子性
第二范式：每张表描述唯一关系
第三范式：消除传递依赖，表中的信息如果能被推导出就不再设计一个字段来记录，空间最省

6.2 反三范式

通过增加冗余或者重复数据，牺牲存储空间来节省查询时间

冗余字段：某一个字段再多张表中都出现，且表达含义相同

7 MySQL高级应用

7.1 索引

主键索引：唯一性，每个表中只能有一个
唯一索引：索引列中的所有数据只能出现一次
普通索引：最常见的索引，提高对数据的访问速度

表对应的索引保存在一个文件中，如果对数据进行增删改操作，MySQL会对索引进行更新。

7.1.1 创建索引

-- 1.创建表之后通过修改表的结构添加主键索引
alter table 表名 add primary key(主键列名);
-- 2.创建唯一索引
create unique index 索引名(自定义的) on 表名(列名[长度]); 
-- 3.创建普通索引
create index 索引名 on 表明(列名[长度]);
alter table 表名 add index 索引名(列名);

7.1.2 删除索引

alter table 表名 drop index 索引名;

7.2 视图

视图是由查询结果形成的一张虚拟表，可以用于权限控制，如果某个查询的结果出现的十分频繁，并且查询语法比较复杂，即可构建视图。

7.2.1 创建视图

create view 视图名[字段列表] as select查询语句;
-- 例如
create view products_category_view as
select * from products p left join category c on p.`p_id`=c.`c_id`;

7.2.2 查询视图

-- 视图的操作语句与表的操作语句相同
select
	p.pname,
	avg(pc.`price`)
from products_category_view pc group by pc.`cname`;

-- 包含子查询
select
	*
from products_category_view pc
where pc.`cname`='张三' and pc.`price`=
(select max(pc.`price`) from products_category_view pc where pc.`cname`='张三');

7.2.3 视图与表的区别

视图建立在表的基础上
不要通过视图进行增删改操作
删除视图不会影响表，删除表则视图不存在

7.3 存储

MySQL5.0版本开始支持存储过程。

存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定的存储过程名并给定参数来调用执行。

7.3.1 存储过程的创建

方式一(无参)

-- 语法格式
delimiter $$  -- 声明语句的结束符号（自定义的）
create procedure 存储过程名称()  -- 声明存储过程名称
begin  -- 开始编写存储过程
……  -- 要执行的SQL
end $$  -- 存储过程结束

方式二(接收参数)

-- 语法格式
delimiter $$  -- 声明语句的结束符号（自定义的）
create procedure 存储过程名(in 参数名 参数类型) -- 声明存储过程名称
begin  -- 开始编写存储过程
……  -- 要执行的SQL
end $$  -- 存储过程结束

方式三(获取返回值)

-- 1.变量的赋值
set @变量名 = 值
-- 2.输出参数
out 变量名 数据类型
-- 3.返回参数
select @参数名
-- 语法格式
delimiter $$  -- 声明语句的结束符号（自定义的）
create procedure 存储过程名(in 参数名 参数类型,in 参数名 参数类型,out 参数名 参数类型) -- 声明存储过程名称
begin  -- 开始编写存储过程
	……  -- 要执行的SQL
end $$  -- 存储过程结束

-- 举例说明----------------------------------------------------------------------------
delimiter $$
create procedure getnum(in cid int,in pid int,out out_num int)
begin
	insert into tablename values(cid,pid);
	set @out_num=1;
	select @out_num;
end $$

7.3.2 存储过程的调用

call 存储过程名;

7.4 触发器

当执行一条SQL语句时，该语句会自动触发执行其他的SQL语句

-- 创建触发器语法格式
delimiter $  -- 自定义结束符号
create trigger 触发器名
after/before(insert/update/delete)  -- 出发的时机，和监视的事件
on tableName  -- 触发器所在表
for each row  -- 固定写法，表示行触发器
begin
	……  -- 被触发的事件
end $

-- 举例说明
delimiter $
create trigger t1
after insert on table1
for each row
begin
	update table2 set num=num-1 where gid=4;
end $
-- 向表table1中每插入一条数据后，表table2中的num字段中对应gid=4的值减一

7.5 DCL用户操作

7.5.1 创建用户

-- 创建可在指定主机上登录MySQL的用户
create user '用户名'@'主机名' identified by '密码';
-- 创建可在任何电脑上登录MySQL的用户
create user '用户名'@'%' identified by '密码';

7.5.2 用户授权

grant 权限1,权限2... on 数据库名,表, to '用户名';
-- 1.给用户分配指定数据库表的权限
grant select on db1.'tablename' to 'user1'@'localhost';
-- 2.给用户分配所有数据库的权限
grant all on *.* to 'user2'@'%';

7.5.3 查看用户权限

show grants for '用户名'@'主机名';

7.5.4 用户删除和查询

-- 删除
drop user '用户名'@'主机名';
-- 查询
select * from user;

7.6 数据库的备份与还原

7.6.1 SQLyog方式

备份

选择数据库右击→备份/导出→备份数据库，转储到SQL……→更改输出路径→导出
还原

选择主机名右击→执行SQL脚本→找到上一步备份操作后生成的.sql文件→执行

7.6.2 命令行方式

备份
```
mysqldump -u用户名 -p密码 数据库名 > 文件输出路径
```
或者打开资源管理器→在数据库的bin路径的地址栏中输入cmd→回车Enter→mysqldump -u用户名 -p密码数据库名 > 文件输出路径
还原

命令行连接MySQL→use 要还原到的数据库名→source 上步操作生成的.sql文件完整路径

FisrtBqy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库配置及高级操作【增删改查、约束、事务、多表、范式、索引、视图、存储、触发器、DCL用户、备份还原】

MySQL数据库配置及高级操作1 环境配置1.1 MySQL安装与卸载1.2 SQLyog安装1.3 SQL简介2 基本操作2.1 DDL*(Data Definition Language)*2.1.1 查询操作2.1.2 创建操作2.1.3 删除操作2.1.4 修改操作2.2 DML*(Data Manipulation Language)*2.2.1 增加数据2.2.2 修改数据2.2.3 删除操作2.3 DQL*(Data Query Language)*2.3.1 基
复制链接

扫一扫