MySQL数据库（高级）

文章目录

- 1.MySQL约束
- - - 1.主键
    - - 细节说明
      - 演示复合主键
    - 2.not null（非空）
    - 3.unique（唯一）
    - - 细节说明
      - 演示
    - 4.外键
    - - 外键示意图
      - 使用细节
      - 演示
    - 5.check
    - - 演示
    - 6.创建表练习
    - - 答案
    - 7.自增长
    - - 演示
      - 细节
- 2.索引
- - - 1.索引机制
    - 2.创建索引
    - - 演示
      - 细节
    - 3.删除索引
    - 4.课后练习
    - - 练习一
      - 练习二
    - 5.创建索引规则
- 3.事务
- - - 1.示意图
    - 2.基本操作
    - - 演示
    - 3.回退事务和提交事务
    - 4.事务注意事项
    - 5.事务的隔离级别
    - - 1.基本介绍
      - 2.理解
      - 3.事务隔离级别演示
      - 可串行化的隔离级别
    - 6.设置隔离的指令
    - 7.mysql事务的ACID
- 4.存储引擎
- - - 1.主要的存储引擎特点
    - 2.三种重点的存储引擎
    - 3.表的存储引擎选择
    - 4.修改存储引擎
- 5.视图
- - - - 1.基本介绍
      - 2.基本使用
      - 案例
    - 3.视图细节
    - 4.视图最佳实践
    - - 练习
- 6.MySQL用户管理
- - - 1.创建用户和删除用户
    - - 代码实例
    - 2.用户修改密码
- 7.MySQL权限管理
- - - 1.基本语法
    - 2.案例
    - - 答案
      - 细节
- 8.MySQL作业
- - - 1.作业一
    - - 题目
      - 答案
    - 2.作业二
    - - 题目一
      - 题目二
      - 题目三
      - 题目四
    - 3.作业三
    - 4.作业四
    - 5.作业五
    - 6.作业六
    - 7.作业七

1.MySQL约束

1.主键

细节说明

primary key 不能重复并且不能为null
一张表最多只能有一个主键，但是可以是复合主键
复合主键的任意一个都不能为null，不允许两个同时重复
主键的指定方式有两种
1. 字段名 + primary key
2. 在表定义最后写primary key(列名)
使用desc 表名可以看到primary key的情况

演示复合主键

CREATE TABLE prikey (
	id INT,
	name VARCHAR(32),
	PRIMARY KEY(id, name)
)

INSERT INTO prikey VALUES(1, 'df')
INSERT INTO prikey VALUES(1, 'df')

2.not null（非空）

3.unique（唯一）

细节说明

如果没有指定not null，则unique字段可以有多个null
如果一个列是unique not null则相当于主键
一张表可以有多个unique字段

演示

-- 如果没有指定not null， 则unique字段可以有多个null
-- 一张表可以有多个unique字段
CREATE TABLE prikey (
	id INT UNIQUE,
	name VARCHAR(32) UNIQUE
)

INSERT INTO prikey VALUES(NULL, 'df');
INSERT INTO prikey VALUES(NULL, 'df')

4.外键

外键示意图

使用细节

外键指向的表字段要求是主键或者unique
表的类型必须是innodb，这样的表才支持外键
外键字段的类型和主键的类型必须相同
外键可以为null，前提是外键字段允许为null
如果建立了主外键关系，如果主键被外键使用了，主键就不能删除，除非先将外键的数据删除

演示

-- 演示外键使用
CREATE TABLE stu (
	id INT,
	name VARCHAR(32),
	class_id int,
-- 	指定外键关系
	FOREIGN KEY (class_id) REFERENCES my_class(id)
);
CREATE TABLE my_class (
	id INT PRIMARY KEY,
	name VARCHAR(32)
)
INSERT INTO my_class VALUES(1, 'zhanglaoshi')
-- 外键可以为null
INSERT INTO stu VALUES(1, 'lihua', NULL)
-- 外键可以在主键中
INSERT INTO stu VALUES(2, 'xiaoming', 1)
-- 一旦主键被外键使用了则不能删除,这句话会报错
DELETE FROM my_class WHERE id = 1

5.check

演示

CREATE TABLE t23 (
	id INT PRIMARY KEY,
	name VARCHAR(32),
	sex VARCHAR(6) CHECK(sex IN ('man','woman'))
)
-- 这样是插入不进去的
INSERT INTO t23 VALUES(1, 'xiaoming', 'sex')

6.创建表练习

答案

-- 创建 数据库
CREATE DATABASE shop_db
-- 创建表goods
CREATE TABLE goods(
	goods_id int PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	unitprice DOUBLE NOT NULL DEFAULT 0 CHECK (unitprice BETWEEN 1.0 AND 9999.99),
	category VARCHAR(32) NOT NULL DEFAULT '',
	provider VARCHAR(32) NOT NULL DEFAULT ''
)

-- customer
CREATE TABLE customer(
	customer_id INT UNSIGNED PRIMARY KEY,
	name VARCHAR(32) NOT NULL DEFAULT '',
	address VARCHAR(32) NOT NULL DEFAULT '',
	email VARCHAR(32) UNIQUE,
	sex ENUM('男','女'),
	card_Id VARCHAR(32)
)
-- purchase
CREATE TABLE purchase(
	order_id INT UNSIGNED PRIMARY KEY,
	customer_id INT UNSIGNED NOT NULL DEFAULT 0,
	goods_id INT NOT NULL DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
)

7.自增长

演示

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

-- 两种方式添加数据
INSERT INTO users VALUES(NULL, '小明');
INSERT INTO users(name) VALUES('小明')
SELECT * FROM users

细节

一般来说自增长是和primary key配合使用的
自增长也可以单独使用但是需要配合一个unique
自增长修饰的字段为整数型的（虽然小数也可以）
自增长默认从1开始，但是也可以赋值，id INT AUTO_INCREMENT = 100，如果赋值了则默认从你赋的值开始
如果添加数据时没有将自增长的列设置为null，而是自己添加了一个值则下次自增长会是添加的值加一。。。

2.索引

1.索引机制

2.创建索引

演示

-- 1.添加普通索引方式一 
CREATE INDEX name_intdex ON t25 (name)
-- 2.添加普通索引方式二
ALTER TABLE t25 ADD INDEX index_id (id)
-- 3.添加唯一索引 
CREATE UNIQUE INDEX id_index ON t25 (id)
-- 4.添加主键索引
ALTER TABLE t25 ADD PRIMARY KEY (id)
-- 查看索引
SHOW INDEX FROM t25

细节

primary key，unique关键字修饰的本身就是索引
在不允许数据重复的时候优先使用唯一索引，因为他比普通索引速度快

3.删除索引

-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t25 DROP PRIMARY KEY

4.课后练习

练习一

-- 创建主键索引一
CREATE TABLE ORDER1 (
	ID INT PRIMARY KEY,
	DNAME VARCHAR(32)
)
-- 创建主键索引二
CREATE TABLE ORDER2 (
	ID INT,
	DNAME VARCHAR(32)
)
ALTER TABLE ORDER2 ADD PRIMARY KEY(ID)

练习二

-- 创建唯一索引一
CREATE TABLE MENU1 (
	ID INT,
	DNAME VARCHAR(32)
)
CREATE UNIQUE INDEX index_1 ON MENU1(ID)
-- 创建唯一索引二
CREATE TABLE MENU2 (
	ID INT,
	DNAME VARCHAR(32)
)
ALTER TABLE MENU2 ADD UNIQUE INDEX index_2(ID)

5.创建索引规则

3.事务

1.示意图

2.基本操作

演示

-- 事务基本操作演示
CREATE TABLE SW(
	ID INT,
	NAME VARCHAR(32)
)
-- 开启事务
START TRANSACTION
-- 设置保存点
SAVEPOINT A
-- 执行dml操作
INSERT INTO sw VALUES(1,'200')
-- 设置保存点
SAVEPOINT B
-- 执行dml操作
INSERT INTO sw VALUES(2, '300')
-- 回滚到b
ROLLBACK TO b
-- 直接回滚到A
ROLLBACK

3.回退事务和提交事务

4.事务注意事项

如果不开启事务，dml操作是自动提交的，不能回滚
如果开启一个事务，你没有创建保存点，你可以执行rollback，默认就是回到事务开始时的状态
事务假如保存点a比保存点b先创建，如果回退到保存点a，则不能再回到保存点b
mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使
开启一个事务，start transaction 或者 set autocommit = off

5.事务的隔离级别

1.基本介绍

2.理解

前提条件，开启两个事务
脏读： 一个事务对数据库进行了修改，还没提交，另一个事务就可以读到
不可重复读：一个事务对数据库进行了修改或删除，提交了之后，另一个事务就可以读到
幻读：一个事务对数据库进行了插入操作，提交了之后，另一个事务就可以读到
最佳的情况是：开启一个事务，则此刻这个事务操作的数据库就是，此刻的数据库状态，不会被其他事务所影响

3.事务隔离级别演示

可串行化的隔离级别

他是加锁的，使用多个锁使事务就像是顺序执行一样
当一个事务正在对表进行操作的时候，另一个事务是无法查询这个表的内容，直到其操作完毕

6.设置隔离的指令

7.mysql事务的ACID

4.存储引擎

1.主要的存储引擎特点

2.三种重点的存储引擎

3.表的存储引擎选择

4.修改存储引擎

5.视图

1.基本介绍

视图是根据基表（可以是）来创建的，视图是虚拟的表
视图也有列，数据来自基表
通过视图可以修改基表的数据
基表的改变，也会影响到视图的数据

2.基本使用

案例

-- 创建视图
CREATE VIEW EMP_VIEW01 
AS SELECT EMPNO, ENAME, JOB, DEPTNO 
FROM EMP 
-- 查看视图
SELECT * FROM emp_view01
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01
-- 删除视图
DROP VIEW  emp_view01

3.视图细节

4.视图最佳实践

练习

-- 创建多表连接的视图
CREATE VIEW view02
AS SELECT EMP.empno, EMP.ename, dept.dname,salgrade.grade
FROM EMP, DEPT, SALGRADE
WHERE EMP.deptno = DEPT.deptno AND EMP.sal BETWEEN SALGRADE.losal AND SALGRADE.hisal 
-- 查询视图
SELECT * FROM view02

6.MySQL用户管理

1.创建用户和删除用户

代码实例

CREATE USER 'SUN'@'LOCALHOST' IDENTIFIED BY '123456'
-- 查看所有用户
SELECT *
FROM mysql.`user`
-- 删除用户
DROP USER 'SUN'@'LOCALHOST'

2.用户修改密码

-- 修改自己的密码
ALTER USER USER() IDENTIFIED BY '新密码';
-- 修改他人的密码
ALTER USER 'SUN'@'localhost' IDENTIFIED BY '新密码';

7.MySQL权限管理

1.基本语法

2.案例

答案

-- 创建用户
CREATE USER 'sun'@'localhost' IDENTIFIED BY '123'
-- 使用root用户创建testdb，表news
CREATE DATABASE testdb
use testdb
CREATE TABLE news(
	ID INT,
	content VARCHAR(32)
);
-- 添加一条测试数据
INSERT INTO news VALUES(1,'1244234')
-- 给用户分配查看news表和添加news的权限
GRANT SELECT, INSERT 
	ON testdb.news
	TO 'sun'@'localhost'
-- 修改密码为abc
ALTER USER 'sun'@'localhost' IDENTIFIED BY 'abc';
-- 回收权限
REVOKE SELECT, INSERT ON testdb.news FROM 'sun'@'localhost'
-- 删除用户
DROP USER 'sun'@'localhost'

细节

8.MySQL作业

1.作业一

题目

答案

D：别名应该只有一个空格
B
C：排序应该指定列

2.作业二

题目一

DESC dept
DESC emp

题目二

-- 显示所有部门名称
SELECT dname
FROM dept
-- 显示所有雇员名及其全年收入,并指定列名为‘年收入’
SELECT ename, (sal + IFNULL(comm,0)) * 12 年收入  
FROM emp

题目三

-- 显示工资超过2850的雇员姓名和工资
SELECT ename, sal
FROM emp
WHERE sal > 2850
-- 显示工资不在1500到2850之间的所有雇员名以及工资
SELECT ename,sal
FROM emp
WHERE sal < 1500 OR sal > 2850
-- 显示编号为7566的雇员
SELECT *
FROM emp
WHERE empno = 7566
-- 显示部门10和30中工资超过1500的雇员名以及工资
SELECT ename, sal
FROM emp
WHERE deptno IN (10,30) AND sal > 1500
-- 显示无管理者的雇员名以及岗位
SELECT ename, job
FROM emp
WHERE mgr IS NULL

题目四

-- 显示在1991年2月1日到1991年5月1日之间雇佣的雇员名，岗位以及雇佣日期，并按照雇佣日期排序
SELECT ename, job, hiredate
FROM emp
WHERE hiredate BETWEEN '1991-02-01' AND '1991-05-01' 
ORDER BY hiredate
-- 显示获得补助的所有雇员名，工资以及补助并以工资降序排序
SELECT ename, sal,comm
FROM emp
WHERE COMM IS NOT NULL 
ORDER BY sal DESC

3.作业三

-- 1.找出部门30中所有的员工
SELECT *
FROM emp
WHERE DEPTNO = 30
-- 2.列出所有办事员（clerk）的姓名，部门以及部门编号
SELECT ename, dname, emp.deptno
FROM emp, dept
WHERE EMP.deptno = dept.deptno AND JOB = 'CLERK'
-- 3.找出佣金高于薪金的员工
SELECT *
FROM emp
WHERE comm > sal
-- 4.找出佣金高于薪金百分之六十的员工
SELECT *
FROM emp
WHERE comm > 1.6 * sal
-- 5.找出部门10中所有经理和部门20中所有办事员的详细资料
SELECT *
FROM emp
WHERE deptno = 10 AND job = 'MANAGER' OR deptno = 20 AND job = 'CLERK'
-- 6.找出部门10中所有经理，部门20中所有办事员，还有既不是经理又不是办事员但薪金大于或等于2000的所有员工的详细资料
SELECT *
FROM emp
WHERE deptno = 10 AND job = 'MANAGER' OR deptno = 20 AND job = 'CLERK' OR job NOT IN ('MANAGER', 'CLERK') AND sal >= 2000
-- 7.找出收取佣金的员工的不同工作
SELECT DISTINCT job
FROM emp
WHERE comm IS NOT NULL
-- 8.找出不收取佣金或收取佣金低于100的员工 
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 100
-- 9.找出各月倒数第三天受雇的所有员工
SELECT *
FROM emp
WHERE DAY(DATE_ADD(hiredate,INTERVAL 3 DAY)) = 1 
-- 10.找出早于12年前受雇的员工
SELECT *
FROM emp
WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW()
-- 11.以首字母小写的方式显示所有员工的姓名
SELECT  CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2, LENGTH(ename) - 1))
FROM emp
-- 12.显示正好为5个字符的员工的姓名
SELECT ename
FROM emp
WHERE LENGTH(ename) = 5

4.作业四

-- 13显示所有不带有'R'的员工的名字
SELECT *
FROM emp
WHERE ename NOT LIKE '%R%'
-- 14显示所有员工姓名的前三个字符
SELECT SUBSTRING(ename, 1, 3)
FROM emp
-- 15显示所有员工的姓名，使用a替换'A'
SELECT REPLACE(ename,'A','a')
FROM emp
-- 16显示满10年服务年限的员工的姓名和受雇日期
SELECT ename, hiredate
FROM emp
WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) <= NOW()
-- 17显示员工的详细资料，按照姓名排序
SELECT *
FROM emp
ORDER BY ename
-- 18显示员工的姓名和受雇日期，根据其服务年限将最老的员工排在最前面
SELECT ename, hiredate
FROM emp
ORDER BY hiredate
-- 19显示所有员工的姓名、工作和薪金，按工作排序，如果工作相同则按薪金排序
SELECT ename, job, sal
FROM emp
ORDER BY job, sal
-- 20显示所有员工的姓名，加入公司的年份和月份，按照雇佣日期所在月排序，若月份相同则将最早年份的员工排在最前面
SELECT ename, YEAR(hiredate), MONTH(hiredate)
FROM emp
ORDER BY MONTH(hiredate), YEAR(hiredate)
-- 21显示在一个月为30天的情况所有员工的日薪金，忽略余数
SELECT IFNULL(sal,0) / 30
FROM emp
-- 22找出在任何年份2月受聘的所有员工
SELECT *
FROM emp
WHERE MONTH(hiredate) = 2
-- 23对于每个员工，显示其加入公司的天数
SELECT ename, DATEDIFF(CURRENT_DATE,hiredate)
FROM emp
-- 24显示姓名字段的任何位置包含‘A’的所有员工性名
SELECT ename
FROM emp
WHERE ename LIKE '%A%'
-- 25以年月日的方式显示所有员工的服务年限（假设一个月30天）
SELECT FLOOR(DATEDIFF(CURRENT_DATE,hiredate) / 365) as year, FLOOR(MOD(DATEDIFF(CURRENT_DATE,hiredate) , 365) / 30) AS month, MOD(MOD(DATEDIFF(CURRENT_DATE,hiredate) , 365) , 30) as day
FROM emp

5.作业五

-- 1.列出至少有一个员工的所有部门
SELECT DISTINCT DNAME
FROM emp, dept
WHERE EMP.deptno = DEPT.deptno
-- 2.列出薪金比“smith”多的所有员工
SELECT *
FROM emp
WHERE sal > (
	SELECT sal
	FROM emp
	WHERE ename = 'SMITH'
)
-- 3.列出受雇日期晚于其直接上级的所有员工
SELECT *
FROM emp LOW, emp HIGH
WHERE LOW.mgr = HIGH.empno AND LOW.hiredate > HIGH.hiredate
-- 4.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT dname, emp.*
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
-- 5.列出所有‘check’的姓名及其部门名称
SELECT ename,dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND job = 'CLERK'
-- 6.列出最低薪金大于1500的各种工作
SELECT job, sal
FROM emp
WHERE sal > 1500
-- 7.列出在部门‘sales’工作的员工的姓名
SELECT ename, dname 
FROM emp, dept
WHERE emp.deptno = dept.deptno AND dname = 'SALES'
-- 8.列出薪金高于公司平均薪金的所有员工
SELECT *
FROM emp
WHERE sal > (
	SELECT AVG(sal)
	FROM emp
)

6.作业六

-- 1.列出与‘scott’从事相同工作的员工 
SELECT *
FROM emp
WHERE job = (
	SELECT JOB
	FROM emp
	WHERE ename = 'SCOTT'
)
-- 2.列出佣金高于在部门30工作的所有员工的薪金的员工姓名和薪金
SELECT ename, sal
FROM emp
WHERE sal > all(
	SELECT sal
	FROM emp 
	WHERE deptno = 30 
)
-- 3.列出在每个部门工作的员工数量，平均工资，以及平均服务期限
SELECT COUNT(*), AVG(sal), AVG(CURRENT_DATE - hiredate)
FROM emp
GROUP BY deptno
-- 4.列出所有员工的姓名、部门名称和工资
SELECT ename, dname, sal
FROM emp, dept
WHERE emp.deptno = dept.deptno
-- 5.列出所有部门的详细信息和部门人数
SELECT dept.*, count
FROM (
	SELECT count(*) as count, deptno
	FROM emp
	GROUP BY deptno
) temp RIGHT JOIN dept
ON temp.deptno = dept.deptno
-- 6.列出各种工作的最低工资
SELECT job, MIN(sal)
FROM emp
GROUP BY job
-- 7.列出经理的最低薪金
SELECT DISTINCT job, MIN(sal)
FROM emp
WHERE job = 'MANAGER'
-- 8.列出所有员工的年工资，按照年薪从低到高排序
SELECT ename, (sal + IFNULL(comm,0)) * 12 AS salary
FROM emp
ORDER BY salary

7.作业七

CREATE TABLE class(
	classid VARCHAR(16) PRIMARY KEY,
	`subject` VARCHAR(16),
	deptname VARCHAR(16),
	enrolltime YEAR,
	num INT,
	FOREIGN KEY (deptname) REFERENCES department(deptname)
)
CREATE TABLE student (
	studentid VARCHAR(16) PRIMARY KEY,
	`name` VARCHAR(8) NOT NULL DEFAULT '',
	age INT,
	classid VARCHAR(16),
	FOREIGN KEY (classid) REFERENCES class(classid)
)
CREATE TABLE department(
	departmentid VARCHAR(16) PRIMARY KEY,
	deptname VARCHAR(16) UNIQUE NOT NULL
)

insert into department values('001','数学');
insert into department values('002','计算机');
insert into department values('003','化学');
insert into department values('004','中文');
insert into department values('005','经济');

insert into class values(101,'软件','计算机',1995,20);
insert into class values(102,'微电子','计算机',1996,30);
insert into class values(111,'无机化学','化学',1995,29);
insert into class values(112,'高分子化学','化学',1996,25);
insert into class values(121,'统计数学','数学',1995,20);
insert into class values(131,'现代语言','中文',1996,20);
insert into class values(141,'国际贸易','经济',1997,30);
insert into class values(142,'国际金融','经济',1996,14);

insert into student values(8101,'张三',18,101);
insert into student values(8102,'钱四',16,121);
insert into student values(8103,'王玲',17,131);
insert into student values(8105,'李飞',19,102);
insert into student values(8109,'赵四',18,141);
insert into student values(8110,'李可',20,142);
insert into student values(8201,'张飞',18,111);
insert into student values(8302,'周瑜',16,112);
insert into student values(8203,'王亮',17,111);
insert into student values(8305,'董庆',19,102);
insert into student values(8409,'赵龙',18,101);
insert into student values(8510,'李丽',20,142);

SELECT * FROM class;
SELECT * FROM student;
SELECT * FROM department;

-- 找出所有姓李的学生
SELECT *
FROM student
WHERE `name` LIKE '李%'

-- 找出所有开设超过1个专业的系的名字
SELECT deptname, COUNT(*)
FROM class
GROUP BY deptname
HAVING COUNT(*) > 1

-- 列出人数大于等于30的系的编号和名字
SELECT departmentid, class.deptname
FROM class, department
WHERE class.deptname = department.deptname AND num >= 30

-- 学校又新增了一个物理系，编号为006
INSERT INTO department VALUES('006', '物理系')

-- 张三退学
SELECT *
FROM student
WHERE `name` = '张三'
DELETE FROM student 
WHERE name = '张三'