MySQL数据库配置及高级操作【增删改查、约束、事务、多表、范式、索引、视图、存储、触发器、DCL用户、备份还原】
1 环境配置
1.1 MySQL安装与卸载
-
安装
按照提示一步步操作即可,有几个需要注意的地方:
-
如果需要配置环境变量,①新建
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文件完整路径