MySql数据库一

最新推荐文章于 2022-03-10 20:18:09 发布

小羊and阿童木

最新推荐文章于 2022-03-10 20:18:09 发布

阅读量179

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/helloworld999999/article/details/112866249

版权

一、MySql基础、入门

1、安装、卸载、配置环境变量。。。此处省略！

命令行：
停止MySql服务：net stop mysql57；启动Mysql：net start mysql57; 登录 mysql -uroot -p123456；退出exit或quit； mysql -h127.0.0.1 -uroot -p123456;

SQLYog

MySql安装目录
bin->可执行文件mysql.exe
docs->文档
include->头文件
lib->依赖库
share->字符集和语言

MySql数据目录

my.ini:MySql配置文件
Data:保存的是数据库（文件夹）和数据表（文件）的信息

①Mysql服务器就是一台安装了MySql软件的电脑。
②MySql中管理者多个数据库。
③数据库中包含多张表。
④表中存放着多条记录。

2、SQL语言

2.1、Java中的类对应一张表；成员变量对应表中字段。

2.2、SQL结构化查询语言，操作关系型数据库的统一查询规范。
①不区分大小写
②注释 -- ，/**/,#

DDL：数据定义语言，操作数据库、表；
DML：数据操作语言。对数据库中的表进行增删改；
DQL：数据查询语言；
DCL:数据控制语言，定义数据访问权限；
对数据库操作的分类：CRUD create、retrieve、update、delete和使用数据库

MySql自带数据库的介绍：
infomation_schema：信息数据库，保存的是其它数据库的信息。
mysql：mysql核心数据库，保存的是用户和权限相关的信息。
performance_shema：保存一些性能相关的信息，监控MySql性能。
sys：记录了DBA所需要的一些信息，快速了解数据库的运行情况。

------------------------------------------------------------------------------------------------

2.3 2.3.1 DDL 对标进行操作

--------------------CREATE创建-------------------------

CREATE DATABASE db_1;

-- 修改数据库的字符集
-- 语法格式 alter database 数据库名 character set utf8;
ALTER DATABASE db_1 CHARACTER SET utf8;

-- 查询当前数据库的基本信息
SHOW CREATE DATABASE db_1;

-- 删除数据库
-- 语法格式 drop database 数据库名将数据库从mysql中永久删除
DROP DATABASE db_1; -- 慎用

-------------------------------------------------------------------------------------------------------

MySql常见的数据类型：
int 整型
   double 浮点类型
   varchar 字符类型
   date 日期类型只显示年月日没有时分秒 yyyy-MM-dd
   datetime 年月日时分秒 yyyy-MM-dd HH:mm:ss
   char 也代表字符串
varchar和char类型的区别：
varchar类型的特点：是可变长度的，存储字符串时只使用所需的空间；
char类型的特点：是固定长度的，指定了多少的长度，创建时就使用多少；
保存指定长度的数据时，可以使用char，比如保存密码；保存在一定范围内长度变化的数据用varchar；

-- 创建商品分类表
/*
   创建表的语法格式
       create table 表名(
       字段名称字段类型(长度)，
       字段名称2 字段类型,
       字段名称3 字段类型最后一个字段不要加逗号
       );
*/

CREATE TABLE category(
cid INT,
cname VARCHAR(20)
);

-- 测试表
CREATE TABLE test1(
tid INT,
tdate DATE
);

-- 快速创建一个表结构相同的表（复制表结构）
-- 语法 create table 新表名称 like 旧表名称

CREATE TABLE test2 LIKE test1;

------------------------查看表------------------------------

-- 查看表结构
DESC test2;

-- 查看表
-- 查看当前数据库中所有的表
SHOW TABLES;

-- 查看创建表的sql
SHOW CREATE TABLE category;

-- 查看表结构
DESC category;

--------------------删除表-------------------------

/*
   表的删除
   语法格式
       drop table 表名; 从数据库中永久删除一张表
       drop table if exists 表名; 判断表是否存在，存在则删除，不存在不操作
*/

-- 删除test1表
DROP TABLE test1;

-- 使用先判断再删除
DROP TABLE IF EXISTS test1;

/*
修改表的名称
修改表的字符集
修改表中的某一列（数据类型、名称、长度）
向表中添加一列
删除表中的某一列
*/

------------------修改表------------------------

-- 修改表的名称 rname table 旧表名称 to 新表名称
RENAME TABLE category TO category1;

-- 修改表的字符集为gbk
-- 语法：alter table 表名 character set 字符集
ALTER TABLE category1 CHARACTER SET GBK;

-- 向表中添加一个字段 add
-- 语法：alter table 表名 add 字段名称字段类型(长度)
-- 添加分类描述字段
ALTER TABLE category1 ADD cdesc VARCHAR(20);

-- 修改表中列的类型或者长度关键字 modify
-- 语法： alter table 表名 modify 字段名称字符类型
-- 修改cdesc长度为50
ALTER TABLE category1 MODIFY cdesc VARCHAR(50);
-- 修改字段偿付
ALTER TABLE category1 MODIFY cdesc CHAR(20);

-- 修改列的名称关键字 change
-- 语法： alter table 表名 change 旧列名新列名类型（长度）
ALTER TABLE category1 CHANGE cdesc description VARCHAR(20);

-- 删除列关键字 drop
-- 语法： alter table 表名 drop 列名
ALTER TABLE category1 DROP description;

2.3.2 DML 对表中的数据进行增删改操作

----------------insert 插入数据-----------------

/*
增加
语法：insert into 表名 (字段名1，字段名2...) values(字段值1，字段值2...)
*/

-- 创建学生表
CREATE TABLE student(
   sid INT,
   sname VARCHAR(20),
   age INT,
   sex CHAR(1),
   address VARCHAR(40)
);

-- 向学生表中插入数据

-- 方式1 插入全部字段，将所有字段名都写出来
INSERT INTO student (sid,sname,age,sex,address) VALUES (1,'孙悟空',18,'男','花果山');

注意事项：①值与字段必须对应，个数&数据类型&长度都必须对应；
②在插入varchar char date类型时，必须使用单引号或者双引号进行包裹；
③如果插入空值，可以忽略不写或者写null；

---------------update修改数据-----------------

/*
   修改操作
   语法格式1：update 表名 set 列名 = 值;
   语法格式2：update 表名 set 列名 = 值 [where 条件表达式：字段名=值]
*/
-- 修改表中所有学生性别为女
update student set sex = '女';   -- 慎用！！

-- 带条件的修改，将sid为1的数据，性别改为男
update student set sex = '男' where sid = 1;

-- 一次性修改多个列
-- 修改sid为3的数据年龄为30，地址为盘丝洞
update student set age = 30, address = '盘丝洞' where sid = 3;

-----------------delete 删除数据-----------------------

/*
删除
语法格式1：delete from 表名;
语法格式2：delete from 表名 [where 条件1];
*/

-- 删除sid为3的数据
DELETE FROM student WHERE sid = 3;
-- 删除所有数据
DELETE FROM student;

-- 删除所有数据的方式两种
-- 1. delete from 表; 对表中的数据逐条删除，效率低
-- 2. truncate table 表名; 推荐，删除整张表，然后再创建一个一模一样的表

TRUNCATE TABLE student;

2.3.3 DQL查询数据

-----------------------------简单查询-----------------------------------

/*
简单查询
select 列名 from 表名;
*/
-- 查询emp表中所有数据
SELECT * FROM emp; -- *表示所有列

-- 查询所有数据，只显示id和name
SELECT eid, ename FROM emp;

-- 查询所有数据，然后给列名改为中文，使用关键字as
SELECT
   eid AS '编号',
   ename AS '姓名',
   sex AS '性别',
   salary AS '薪资',
   hire_date AS '入职时间',
   dept_name '部门名称' -- AS 可以省略
FROM emp;

-- 查询一共有几个部门
SELECT dept_name FROM emp;
-- 去重操作关键字 distinct
SELECT DISTINCT(dept_name) FROM emp;

-- 将我们的员工薪资数据 + 1000
SELECT ename, salary+1000 FROM emp;

-- 注意：查询操作不会对数据库中表中的数据进行修改，只是一种现实方法

---------------------------------条件查询--------------------------------------------

/*
条件查询
语法格式：select 列名 from 表名 [where 条件表达式]

比较运算符、逻辑运算符
*/

#
-- 1.查哪张表 2.查哪些字段 3.查询条件
SELECT * FROM emp WHERE ename = '黄蓉';
#
SELECT * FROM emp WHERE salary = 5000;
#
SELECT * FROM emp WHERE salary != 5000;
SELECT * FROM emp WHERE salary <> 5000;
#
SELECT * FROM emp WHERE salary > 6000;
#
SELECT * FROM emp WHERE salary >=5000 AND salary <=10000;
SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;
#
SELECT * FROM emp WHERE salary IN (3600, 7200,20000);
SELECT * FROM emp WHERE salary=3600 OR salary=7200 OR salary=20000;

/*
like '%精%'
% 通配符，表示匹配任意多个字符串
_ 通配符，表示匹配一个字符
*/
#查询含有’精‘字的多有员工信息
SELECT * FROM emp WHERE ename LIKE '%精%';
# 查询以’孙‘字开头的所有员工信息
SELECT * FROM emp WHERE ename LIKE '孙%';
# 查询第二个字为’兔‘的所有员工信息
SELECT * FROM emp WHERE ename LIKE '_兔%';
#查询没有部门信息的员工信息
SELECT * FROM emp WHERE dept_name IS NULL;
# 查询有部门的员工信息

-- 条件查询先取出表中的每条数据，满足条件的就返回，不满足的就过滤掉

二、MySql单表、约束和事务

2.1排序

/*
排序
使用order by子句
语法结构：select 字段 from 表名 [where 字段名 = 值] order by 字段名称 [ASC/DESC]
ASC 升序排序（默认升序）
DESC 降序排序
*/

-- 单列排序，按照某一个字段进行排序
-- 使用salary字段对emp表进行排序
SELECT * FROM emp ORDER BY salary; -- 默认的升序
SELECT * FROM emp ORDER BY salary DESC; -- 降序

-- 组合排序，同时对多个字段进行排序
-- 在薪资基础上再去使用id字段进行排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

-- 组合排序特点：如果第一个字段值相同，就按照第二个字段进行排序。

2.2 DQL_聚合函数

# 1.查询员工的总数
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
SELECT COUNT(eid) FROM emp;

-- count函数在统计的时候会忽略空值
-- 注意不要使用带空值的列进行count
SELECT COUNT(dept_name) FROM emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
SELECT
   SUM(salary) AS '总薪水',
   MAX(salary) '最高薪水',
   MIN(salary) '最小薪水',
   AVG(salary) '薪水的平均值'
FROM emp;

#3 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary > 4000;

#4 查询部门为'教学部'的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水
SELECT AVG(salary) FROM emp WHERE dept_name = '市场部';

2.3 DQL_分组查询 GROUP BY

group by 分组的过程：第一步将性别相同的数据分为一组；第二步：返回的是每组的子一条数据；
分组的目的就是为了统计操作，一般分组会和聚合函数一起使用；另外查询的时候要查询分组字段。

/*
分组查询使用 group by子句
语法格式： select 分组字段/聚合函数 from 表名 group by 分组字段;
*/

# 通过性别字段进行分组,求各组的平均薪资
SELECT sex, AVG(salary) FROM emp GROUP BY sex;

#1.查询所有部门信息
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;

#2.查询每个部门的平均薪资
SELECT dept_name, AVG(salary) FROM emp GROUP BY dept_name;

#3.查询每个部门的平均薪资, 部门名称不能为null
SELECT
dept_name AS '部门名称',
AVG(salary) AS '平均薪资'
FROM emp
WHERE dept_name IS NOT NULL
GROUP BY dept_name;

# 4.查询平均薪资大于6000的部门
-- 1.首先要分组求平均薪资
-- 2.求出平均薪资大于6000的部门

-- 在分组之后进行条件过滤使用 having 判断条件
SELECT
dept_name,
AVG(salary)
FROM emp
WHERE dept_name IS NOT NULL
GROUP BY dept_name HAVING AVG(salary) > 6000;

/*
where与having的区别
   where：
   1.字分组前进行过滤；
   2.where后面不能跟聚合函数
   having：
   1.是在分组后进行条件过滤
   2.having后面可以写聚合函数
*/

2.4 limit关键字

/*
limit 通过limit去指定要查询的数据的条数行数
语法格式
select 字段 from 表名 limit offset, length;
参数说明
offset:起始行数，默认总0开始计数
length:返回的函数（要查询几条数据）
*/

# 查询emp表中前5条数据
SELECT * FROM emp LIMIT 0, 5;
SELECT * FROM emp LIMIT 5;

# 查询emp表中，从第4条开始，查询6条
SELECT * FROM emp LIMIT 3, 6;

-- limit 分页操作，每页显示3条
SELECT * FROM emp LIMIT 0, 3; -- 第一页
SELECT * FROM emp LIMIT 3, 3; -- 第二页

-- 分页公式起始行数= （当前页-1）*每页显示条数

2.5 约束

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

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

2.5.1 主键约束

/*
主键约束
特点：不可重复、唯一、非空
作用：用来表示数据库中的每一条记录

语法格式
字段名字段类型 primary key
*/

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

-- 方式2 创建
DROP TABLE emp2; -- 删除

CREATE TABLE emp2(
   eid INT,
   ename VARCHAR(20),
   sex CHAR(1),
   PRIMARY KEY(eid) --    指定eid为主键
);

-- 方式3 创建表之后再添加
CREATE TABLE emp2(
   eid INT,
   ename VARCHAR(20),
   sex CHAR(1)
);

-- 通过DDL语句，添加主键约束
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

-- 删除主键 DDL语句
ALTER TABLE emp2 DROP PRIMARY KEY;

2.5.2 主键自增

/*
主键的自增
关键字 auto_increment 主键的自动增长（字段类型必须是整数类型）
*/

-- 创建主键自增的表
CREATE TABLE emp2(
   eid INT PRIMARY KEY AUTO_INCREMENT,
   ename VARCHAR(20),
   sex CHAR(1)
);

-- 添加数据，观察主键的变化

-- 修改自增的起始值
-- 创建主键自增的表，自定义自增的起始位置
CREATE TABLE emp2(
   eid INT PRIMARY KEY AUTO_INCREMENT,
   ename VARCHAR(20),
   sex CHAR(1)
) AUTO_INCREMENT=100;

2.5.3 DELETE和TRUNCAT对自增长的影响

delete删除表中所有数据，将表中的数据逐条删除；删除对自增没有影响；
truncat删除表中的所有数据，是将整个表删除，再创建一个结构删除的表；删除后自增从1开始；

2.5.4 非空约束

非空约束
特点：莫一列不允许为空
语法格式：字段名字段类型 not null

2.5.5 唯一约束

唯一约束特点：表中某一列不能重复（对null值，不做唯一判断）
语法格式：字段名字段类型 unique

主键约束和唯一约束的区别：
①主键约束唯一且不能为空；唯一约束要求唯一但可以为空；
②一个表中可以有多个主键，但可以有多个唯一约束；

2.5.6 默认值

语法格式：字段名字段类型 default 默认值

2.5.7 数据库的事务

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

MySql事务操作：
手动提交事务：
1.开始事务 start transaction；或者begin；
2.提交事务 commit；
3.回滚事务 rollback；
自动提交事务：
MySql默认的提交方式就是自动提交事务；我们每执行一条DML语句，就是一个单独的事务；
查看是否自动提交 show variables like 'autocommit';
改变：set @@autocommit=off;

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

MySql的隔离级别
在理想状态下各个事务是隔离的，相互独立，但是如果多个事务对数据库中的同一批数据进行并发访问的时候就会引发一些问题，可以通过设置不同的隔离级别来解决对应的问题。
并发访问的问题：
脏读：一个事务读取到了另一个事务没有提交的数据；
不可重复读：一个事务中，两次读取的事务不一致；
幻读：一个事务中一次查询的结果无法支撑后续的业务操作；

设置隔离级别：
read uncommitted：读未提交；可以防止哪些问题：无；
read committed：读已提交；可以防止：脏读；（Oracle默认的隔离级别）
repeatable read：可重复读；可以防止：脏读、不可重复读；（MySql默认的隔离级别）
serializable：串行化；可以防止：脏读、不可重复度、幻读；
注意：隔离级别从小到大安全性越来越高，效率越来越低的，根据不同的情况选择不同的隔离级别。

查看隔离级别：select @@tx_isolation;
设置隔离级别：set global transaction isolation level read uncommitted;

三、MySql多表、外键和数据库设计

外键所在的表叫做从表，外键对应的主键表叫做主表。

3.2 外键约束

外键约束：
作用：外键约束可以让两张表之间产生一个对应关系，从而保证了主从表引用的完整性。
外键：从表中与主表的主键对应的字段。
主表和从表：
主表：主键id所在的表，一的一方；
从表：外键字段所在的表，多的一方；

3.3 删除外键约束

/*
   删除外键约束
   语法格式：
   alter table 从表 drop foreign key 外键约束的名称
*/

-- 删除 employee表中外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 创建表之后添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id);

/*
外键约束注意事项：
   1.从表的外键类型必须与主表的主键类型一致；
   2.在添加数据时，应该先添加主表的数据，再去添加从表的数据；
   3.删除数据时，要先删除从表中的数据
*/

3.4 级联删除

在删除主表数据的同时，可以删除与之相关的从表中数据；

-- 创建员工表，添加外键
CREATE TABLE employee(
   eid INT PRIMARY KEY AUTO_INCREMENT,
   ename VARCHAR(30),
   age INT,
   dept_id INT,
   -- 外键
   CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id),
   -- 添加级联删除
   on delete cascade
);

3.5 多表关系

表与表之间的三种关系
   一对多关系（1：n 常见）：班级和学生部门和员工
   多对多关系（n：n 常见）：学生和课程演员和角色
   一对一关系（1：1 了解）：身份证和人

一对多建表原则：多的一方建立外键，指向一的一方的主键；
多对多建表原则：需要创建第三张表（中间表），中间表中至少要有两个字段（两张表中的主键字段），作为中间表的外键；

CREATE TABLE actor(
	aid INT PRIMARY KEY AUTO_INCREMENT,
	aname VARCHAR(10)
);

CREATE TABLE role(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(10)
);

-- 中间表
CREATE TABLE actor_role(
	acid INT PRIMARY KEY AUTO_INCREMENT,
	-- 外键
	aid INT,
	-- 外键
	rid INT
);

-- 添加外键约束
ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(aid);

ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(rid);

一对一建表原则：可以在任意一方建立外键指向另一方主键；

3.6 多表查询

多表查询语法
select 字段列表 from 表名列表;

笛卡尔积
多表查询，交叉链接查询的查询结果会产生笛卡尔积，是不能够使用的。

1.内连接查询：通过制定的条件去匹配两张表中的内容，匹配不上的就不显示；
隐式内连接：
语法格式：select 字段名...from 左表，右表 where 连接条件；

SELECT * FROM category, products WHERE category.cid = products.`category_id`;

显示内连接：
语法格式：select 字段名... from 左表 [inner] join 右表 on 连接条件; inner可以省略掉

SELECT * FROM products p INNER JOIN category c ON p.`category_id` = c.`cid`;

查询之前要确认几件事情：
       1.查询几张表；
       2.表的链接条件：从表.外键=主表.主键；
       3.查询的字段；
       4.查询的条件

2.外连接查询
左外连接
语法格式：关键字 left [outer] join
select 字段名 from 左表 left join 右表 on 链接条件;

左外连接特点：以左表中的数据为基准，匹配右表中的数据，如果匹配上就显示；如果匹配不上，左表中的数据正常显示，右表中的数据显示为null；

SELECT * FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`;

-- 查询每个分类下的商品个数
SELECT 
	c.`cid`, 
	c.`cname`,
	COUNT(p.`pid`) 
FROM category c LEFT JOIN products p 
ON c.`cid` = p.`category_id` 
GROUP BY c.`cid`;

右外连接
语法格式：关键字 left [outer] join
select 字段名 from 左表 left join 右表 on 链接条件;
右外连接特点：以右表中的数据为基准，匹配左表中的数据，如果匹配上就显示；如果匹配不上，右表中的数据正常显示，左表中的数据显示为null；

SELECT * FROM products p RIGHT JOIN category c ON p.`category_id` = c.`cid`;

3.7 子查询

/*
	子查询subQuery
	    一条select语句的结果，作为另一条select语句的一部分。
	 子查询特点：
	     子查询必须放在小括号里面；
	     子查询作为父查询的条件使用（更多的时候）
*/

-- 查询价格最高的商品信息
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

子查询的分类
where型子查询：将子查询的结果作为父查询的比较条件使用；

-- 查询化妆品分类下 商品名称 商品价格
SELECT pname, price FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');

-- 查询小于平均价格的商品信息
SELECT AVG(p.`price`) FROM products p;
SELECT * FROM products WHERE price < (SELECT AVG(p.`price`) FROM products p);

from型子查询：将子查询的查询结果作为一张表使用；

SELECT 
p.pname, p.price, c.`cname`
-- 注意：子查询结果作为一张表时，需起一个别名，否则无法访问表中的字段
FROM (SELECT * FROM products WHERE price > 500) p INNER JOIN category c ON p.category_id = c.`cid`;

exists型子查询：查询结果是单列多行的情况，可以将子查询的结果作为父查询的in函数中的条件使用；

-- 语法格式：select 字段名 from 表名 where 字段名 in(子查询)
-- 查询价格小于两千的商品，来自于哪些分类（名称）
SELECT c.`cname` FROM category c WHERE c.`cid` IN (SELECT DISTINCT p.`category_id` FROM products p WHERE p.`price` < 2000);

子查询总结：
1、子查询如果是一个字段（单列），那么就在where后面做条件；
2、如果是多个字段（多列），就当做一张表使用（另起别名）；

3.8 数据库设计三范式

三范式指数据库设计的一个规则；
作用：为了创建冗余较小、结果合理的数据库；

第一范式（1NF）：满足最低要求的范式；列具有原子性，设计要做到列不可拆分；
第二范式（2NF）：在满足第一范式的基础上，进一步满足更多的规范；一张表只能描述一件事情；
第三范式（3NF）：以此类推；消除传递依赖，表中的信息如果能够被推导出来，就不要设计一个字段单独记录；空间最省原则；

反三范式：通过增加冗余或者重复数据，来提高数据库的性能。浪费存储空间，节省查询时间（以空间换时间）；

冗余字段：某一个字段属于一张表，但是又在多张表中都有出现。

总结：1、进来根据三范式的规则去设计数据库；
2、可以合理的加冗余字段，减少join操作，让数据库执行的更快；

四、MySql索引&视图&存储过程

4.1 索引

索引概念：我们可以通过对数据表中的字段创建索引来提高查询速度。
常见的索引分类：
主键索引（primary key）：主键是一个唯一性的索引，每个表中只能有一个主键。
创建方式：1.创建表的时候直接添加上主键。
2.创建表之后添加索引，使用DDL。
唯一索引（unique）：索引列的所有数据只能出现一次，必须是唯一。保证了数据的唯一性，同时也提高了数据可的效率。
创建方式：1.创建表的时候直接添加。
2.create unique index 索引名 on 表名 (列名（长度）);

CREATE UNIQUE INDEX idx_hobby ON demo1(hobby);

3. alter table 表名 add unique (列名);
普通索引（index）：最常见的索引，作用就是提高对数据的访问速度。
创建方式：1.create index 索引名 on 表名 (列名（长度）);
2. alter table 表名 add index 索引名（列名）;

表对应的索引被保存到一个索引文件中，如果对数据进行增删改操作，那么mysql就对索引进行更新。

删除索引：alter table 表名 drop index 索引名称；

索引的总结：
创建索引的原则：优先选择为经常出现在查询条件或者排序、分组后面的字段创建索引。
索引的优点：
1.大大的提高查询速度。
2.减少查询中分组和排序的时间。
3.通过创建唯一索引，保证数据的唯一性。
索引的缺点：
1.创建和维护索引需要时间，数据量越大，时间越长。
2.表中的数据进行增删改操作时，索引也需要进行维护，降低了维护的速度。
3.索引文件需要间须磁盘空间。

4.2 视图

视图是由查询结果形成的一张虚拟的表。
视图的作用：如果某个查询的结果出现的十分的频繁，那么这个时候就可以根据这条查询语句构建一张视图，方便查询。

视图的语法：
create view 视图名 [字段列表] as select 查询语句；

操作视图就相当于操作一张只读的表；

-- 使用视图进行查询操作
SELECT cid, cname, AVG(price) FROM products_category_view GROUP BY cid;

视图与表的区别：
1.视图是建立在表的基础之上的。
2.通过视图不要进行增删改，视图主要是用来简化操作的。
3.删除视图，表不受影响；删除表，视图就不再起作用了。

4.3 存储过程

存储过程其实是多条SQL语句合并，中间加入一些逻辑判断。

优点：
1.调试完成可以稳定运行（在业务数据相对稳定情况）。
2.存储过程可以减少业务系统与数据库的交互。
缺点：
1.互联网项目中较少使用存储过程，因为业务需求变化太快。
2.存储过程移植十分困难。

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

创建存储过程方式一、

-- 编写存储过程，查询所有商品数据
DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
	SELECT * FROM goods;
END $$

-- 调用存储过程
CALL goods_proc;

创建存储过程方式二、

/*
   语法格式： create procedure 存储过程名(IN 参数名 参数类型)
*/
-- 创建存储过程，接受一个商品id，根据id删除数据
delimiter $$
create procedure goods_proc02(in goods_id int)
begin
	delete from goods where gid = goods_id;
end $$

call goods_proc02(1);

创建存储过程方式三、
获取存储过程的返回值

/*
   1.变量的赋值
	SET @变量名=值
   2.OUT 输出
	OUT 变量名 数据类型
*/
-- 向订单表插入一条数据，返回1，表示插入成功
DELIMITER $$
CREATE PROCEDURE orders_proc(IN o_oid INT, IN o_gid INT, IN o_price INT, OUT out_num INT)
BEGIN
	INSERT INTO orders VALUES(o_oid, o_gid, o_price);
	-- 设置out_num值为1
	SET @out_num = 1;
	-- 返回out_num
	SELECT @out_num;
END $$

CALL orders_proc(1,2,50,@out_num);

4.4 触发器

当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句。
四个要素：1.监视地点（table）；2.监视事件（insert、delete、update）；3.触发时间（before/after）；4.触发事件（insert、delete、update）；

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

-- 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。
/*
  监视的表：orders
  监视事件：insert
  触发时间：after
  触发事件：update
*/

DELIMITER $
CREATE TRIGGER t1
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
	UPDATE goods SET num = num-1 WHERE gid=1;
END $

-- 向订单表中插入一条数据
INSERT INTO orders VALUES(1,1,25);

4.5 DCL

4.5.1 DCL创建用户

   语法结构：
       create user '用户名'@'主机名' identified by '密码';
   创建完成后保存在mysql数据库 user表中

-- 创建 admin1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';

-- 创建 admin2 用户可以在任何电脑上登录 mysql 服务器，密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';

4.5.2 DCL用户授权

语法格式：grant 权限1，权限2...... on 数据库名.表名 to ‘用户名’@‘主机名’；

-- 给 admin1 用户分配对 db4 数据库中 products 表的 操作权限：查询
GRANT SELECT ON db3_2.`products` TO 'admin1'@'localhost';

-- 给 admin2 用户分配所有权限，对所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%';

4.5.3 DCL 查看用户权限

语法格式：show grants for '用户名'@‘主机名’；

-- 查询root用户权限
SHOW GRANTS FOR 'root'@'localhost';
-- 查看admin1用户权限
SHOW GRANTS FOR 'admin1'@'localhost';

-- 删除用户
DROP USER 'admin1'@'localhost';

-- 查询用户
SELECT * FROM mysql.user;

4.5.4数据库备份还原
SQLYog备份还原
命令行备份还原
语法格式：备份：mysqldump -u用户名 -p密码数据库名 > 文件路径;
还原：source mysql文件地址

小羊and阿童木

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql数据库一

一、MySql基础、入门安装、卸载、配置环境变量。。。此处省略！命令行：停止MySql服务：net stop mysql57；启动Mysql：net start mysql57; 登录 mysql -uroot -p123456；退出exit或quit； mysql -h127.0.0.1 -uroot -p123456;SQLYogMySql安装目录bin->可执行文件mysql.exedocs->文档include->头文件...
复制链接

扫一扫