【MySQL】part3

最新推荐文章于 2023-07-24 20:43:59 发布

五岁能抬头1995

最新推荐文章于 2023-07-24 20:43:59 发布

阅读量131

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46932548/article/details/106415929

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

【`MySQL`】`part3`

多表查询

查询语句：

select
	列名列表
from
	表名列表
where
	...

使用语句SELECT * FROM 表1,表2;获得表1、表2所有数据的所有组成情况，这就是笛卡尔积。

笛卡尔积：有两个集合A、B，取这两个集合的所有组成情况。

这其中会有很多无用的数据，要完成多表查询，需要消除这些无用的数据。

多表查询的分类：

内连接查询
外连接查询
子查询

内连接查询

隐式内连接：使用where条件消除无用数据
显式内连接，语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件
内连接查询：
1. 从哪些表中查询数据；
2. 查询条件是什么（判断怎么样才是有效数据）；
3. 查询哪些字段。

-- 隐式内连接：使用where条件消除无用数据
SELECT
	* 
FROM
	route_table,
	categories_table 
WHERE
	categories_table.cid = route_table.cid;
	
--  隐式内连接：使用where条件消除无用数据，查询序号、路线名、价格、所属路线：
SELECT
	t1.rid 序号,
	t1.NAME 路线名,
	t1.price 价格,
	t2.tour_route_name 所属路线 
FROM
	route_table t1,
	categories_table t2 
WHERE
	t2.cid = t1.cid;
	
-- 显示内连接：`select 字段列表 from 表名1 inner join 表名2 on 条件`
SELECT
	t1.rid 序号,
	t1.NAME 路线名,
	t1.price 价格,
	t2.tour_route_name 所属路线 
FROM
	route_table t1
	INNER JOIN categories_table t2 ON t1.cid = t2.cid;

外连接查询

左外连接，语法：select 字段列表 from 表名1 left [outer] join 表名2 on 条件

注意：左外连接查询的是左表所有数据以及其交集部分。
右外连接，语法：select 字段列表 from 表名1 right [outer] join 表名2 on 条件

注意：右外连接查询的是右表所有数据以及其交集部分。

-- 在route-table表中添加一个数据，暂不指定数据哪条路线
insert into route_table values(null,'北极游',38000,null);

-- 因为暂未指定属于哪条路线，所以在使用内连接时，会被剔除出条件，这时候需要用到外连接
-- 左外连接查询的是左表所有数据以及其交集部分：
SELECT 
	t1.rid 序号,
	t1.NAME 路线名,
	t1.price 价格,
	t2.tour_route_name 所属路线 
FROM
	route_table t1
	LEFT JOIN categories_table t2 ON t1.cid = t2.cid;
	
-- 若用右外连接可以写作：
SELECT 
	t1.rid 序号,
	t1.NAME 路线名,
	t1.price 价格,
	t2.tour_route_name 所属路线 
FROM
	route_table t1
	RIGHT JOIN categories_table t2 ON t1.cid = t2.cid;

子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

-- 查询最贵的旅游线路的信息，分两步走：
-- 1. 查询最贵的价格是多少
SELECT
	max( price ) 
FROM
	route_table;
-- 结果得到38000
	
-- 2. 查询路线信息，并且价格等于38000的：
SELECT
	* 
FROM
	route_table 
WHERE
	route_table.price = 38000;
	
-- 一条sql语句完成这个操作,子查询：
SELECT
	* 
FROM
	route_table 
WHERE
	route_table.price = ( SELECT max( price ) FROM route_table );

子查询的不同情况

子查询的结果是单行单列的；
- 子查询可以作为条件，使用运算符去判断，如>、<、=等。
- 例如：查询价格高于平均价格的旅游线路：
```
SELECT
	* 
FROM
	route_table 
WHERE
	price > ( SELECT AVG( price ) FROM route_table );
```

子查询的结果是多行单列的；

子查询可以作为条件，使用运算符in去判断

例如：查找欧洲游、东南亚游的所有线路信息：

SELECT
	* 
FROM
	route_table 
WHERE
	route_table.cid IN (
	SELECT
		t2.cid 
	FROM
		categories_table t2 
	WHERE
	t2.tour_route_name IN ( '欧洲游', '东南亚游' ));

子查询的结果是多行多列的；

子查询可以作为一张虚拟表，参与查询。

例如：查找价格在10000元以上的旅游线路信息及所属路线：

SELECT
	* 
FROM
	( SELECT * FROM route_table WHERE route_table.price > 10000 ) t2 
	-- 价格在100090元以上的路由线路组成一个新的表
	LEFT JOIN categories_table t1 ON t1.cid = t2.cid;
	-- 左外连接，显示所有线路信息，如该线路有所属的线路分类信息，打印该分类信息

练习

-- 部门表
CREATE TABLE dept ( id INT PRIMARY KEY AUTO_INCREMENT, -- 部门id
	dname VARCHAR ( 50 ), -- 部门名称
	loc VARCHAR ( 50 ) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept ( id, dname, loc )
VALUES
	( 10, '教研部', '北京' ),
	( 20, '学工部', '上海' ),
	( 30, '销售部', '广州' ),
	( 40, '财务部', '深圳' );
	
-- 职务表：职务名称，职务描述
CREATE TABLE job ( id INT PRIMARY KEY AUTO_INCREMENT, jname VARCHAR ( 20 ), description VARCHAR ( 50 ) );

-- 添加四个职务
INSERT INTO job
VALUES
	( NULL, '董事长', '管理整个公司，接单' ),
	( NULL, '总经理', '管理部门员工' ),
	( NULL, '销售员', '向客户推销产品' ),
	( NULL, '文员', '使用办公软件' );
	
-- 员工表
CREATE TABLE emp (
	id INT PRIMARY KEY auto_increment,-- 员工id
	ename VARCHAR ( 50 ),-- 员工姓名
	job_id INT,-- 职务id
	mgr INT,-- 上级领导
	joindate DATE,-- 入职日期
	salary DECIMAL ( 7, 2 ),-- 工资
	bonus DECIMAL ( 7, 2 ),-- 奖金
	dept_id INT,-- 所属部门编号
	CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY ( job_id ) REFERENCES job ( id ),
	CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY ( dept_id ) REFERENCES dept ( id ) 
);

-- 添加员工
INSERT INTO emp
VALUES
	( 1001, '孙悟空', 4, 1004, '2000-12-17', '8000.00', NULL, 20 ),
	( NULL, '卢俊义', 3, 1006, '2001-02-20', '16000.00', '3000.00', 30 ),
	( NULL, '林冲', 3, 1006, '2001-02-22', '12500.00', '5000.00', 30 ),
	( NULL, '唐僧', 2, 1009, '2001-04-02', '29750.00', NULL, 20 ),
	( NULL, '李逵', 4, 1006, '2001-09-28', '12500.00', '14000.00', 30 ),
	( NULL, '宋江', 2, 1009, '2001-05-01', '28500.00', NULL, 30 ),
	( NULL, '刘备', 2, 1009, '2001-09-01', '24500.00', NULL, 10 ),
	( NULL, '猪八戒', 4, 1004, '2007-04-19', '30000.00', NULL, 20 ),
	( NULL, '罗贯中', 1, NULL, '2001-11-17', '50000.00', NULL, 10 ),
	( NULL, '吴用', 3, 1006, '2001-09-08', '15000.00', '0.00', 30 ),
	( NULL, '沙僧', 4, 1004, '2007-05-23', '11000.00', NULL, 20 ),
	( NULL, '李逵', 4, 1006, '2001-12-03', '9500.00', NULL, 30 ),
	( NULL, '小白龙', 4, 1004, '2001-12-03', '30000.00', NULL, 20 ),
	( NULL, '关羽', 4, 1007, '2002-01-23', '13000.00', NULL, 10 );
	
-- 工资等级表
CREATE TABLE salarygrade ( 
    grade INT PRIMARY KEY auto_increment, -- 级别
	losalary INT, -- 最低工资
	hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade
VALUES
	( NULL, 7000, 12000 ),
	( NULL, 12010, 14000 ),
	( NULL, 14010, 20000 ),
	( NULL, 20010, 30000 ),
	( NULL, 30010, 99000 );

需求：

查询所有员工信息：查询员工编号，员工姓名，工资，职务名称，职务描述
查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
查询员工姓名，工资，工资等级
查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
查询出部门编号、部门名称、部门位置、部门人数
查询出所有员工的姓名及其直接上级的姓名，没有领导的员工也需要查询

查询所有员工信息：查询员工编号，员工姓名，工资，职务名称，职务描述：

-- 方法1：隐式内连接查询
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp,
	job 
WHERE
	emp.job_id = job.id 
ORDER BY
	emp.id ASC;
	
-- 方法2：显式内连接查询
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description 
FROM
	emp
	JOIN job ON emp.job_id = job.id 
ORDER BY
	emp.id ASC;

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置：

-- 方法1：隐式内连接查询
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp,
	job,
	dept 
WHERE
	emp.job_id = job.id 
	AND emp.dept_id = dept.id 
ORDER BY
	emp.id ASC;

-- 方法2：显式内连接查询
SELECT
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc 
FROM
	emp
JOIN
	job
ON
	emp.job_id = job.id 
JOIN
	dept
ON
	emp.dept_id = dept.id 
ORDER BY
	emp.id ASC;

查询员工姓名，工资，工资等级：

-- 隐式内连接查询
SELECT
	emp.ename,
	emp.salary,
	salarygrade.grade 
FROM
	emp,
	salarygrade 
WHERE
	emp.salary BETWEEN salarygrade.losalary 
	AND salarygrade.hisalary 
ORDER BY
	salarygrade.grade DESC;

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级:

-- 使用隐式内连接查询
SELECT
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	salarygrade.grade 
FROM
	emp,
	salarygrade,
	job,
	dept 
WHERE
	emp.salary BETWEEN salarygrade.losalary 
	AND salarygrade.hisalary 
	AND emp.dept_id = dept.id 
	AND emp.job_id = job.id 
ORDER BY
	job.id;

查询出部门编号、部门名称、部门位置、部门人数:

-- 使用子查询,分组查询
SELECT
	dept.id,
	dept.dname,
	dept.loc,
	t2.t1 
FROM
	dept,
	( SELECT dept_id, count( id ) t1 FROM emp GROUP BY emp.dept_id ) t2 -- 起别名;
WHERE
	dept.id = t2.dept_id;

查询出所有员工的姓名及其直接上级的姓名，没有领导的员工也需要查询：

-- 起别名
SELECT
	emp.ename 员工姓名,
	t1.ename 上级姓名 
FROM
	emp
	LEFT JOIN emp t1 ON emp.mgr = t1.id;

事务

事务的演示

概念：如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败。
操作：
1. 开启事务：start transaction;
2. 回滚：rollback;
3. 提交：commit;
实例：

-- 创建账户表
CREATE TABLE account(
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
balance DOUBLE
);

-- 添加数据
INSERT INTO account(name,balance) values
("张三",1000),("李四",1000);

张三向李四转账500元，正常情况下，代码如下：

-- 张三账户 -500
UPDATE account set balance = balance - 500 WHERE account.`name` = "张三";

-- 李四账户 +500
UPDATE account set balance = balance + 500 WHERE account.`name` = "李四";

当张三转出500元和李四接收500元中间发生错误时，可能发生钱财丢失的情况：

-- 张三账户 -500
UPDATE account set balance = balance - 500 WHERE account.`name` = "张三";
未知错误
-- 下面这段代码不会执行，因为上述发生错误，程序终止
UPDATE account set balance = balance + 500 WHERE account.`name` = "李四";

为避免这种情况，需要用到事务：

-- 开启事务
START TRANSACTION;
-- 张三账户 -500
UPDATE account set balance = balance - 500 WHERE account.`name` = "张三";
出错了...
-- 李四账户 -500
UPDATE account set balance = balance + 500 WHERE account.`name` = "李四";

-- 发现出问题了 回滚事务
ROLLBACK;

-- 若没有问题，提交事务
COMMIT;

事务的自动提交与手动提交

事务提交的两种方式：

自动提交：
- 注意：MySQL数据库中事务默认自动提交：
- 如：一条DML(增删改)语句会自动提交一次事务
手动提交：
- 需要先开启事务，再提交
- 注意：Oracle数据库默认时手动提交事务

修改事务的默认提交方式：

查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
修改默认提交方式：SET @@autocommit = 0;

事务的四大特征【面试常问】

**原子性：**是不可分隔的最小操作单位，要么同时成功，要么同时失败；
**持久性：**当事务提交（或回滚）之后，数据库会持久化的保存数据；
**隔离性：**多个事务之间，相互独立；
**一致性：**事务操作前后，数据总量不变。

事务的隔离级别【了解】

**概念：**多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

脏读：一个事务，读取到另一个事务中没有提交的数据；
不可重复读（虚读）：再同一个事务中，两次读取到的数据不一样；
幻读：一个事务操作（DML）数据表中的所有数据，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

read uncommitted：读未提交
- 产生的问题：脏读、不可重复读、幻读
read committed：读已提交（Oracle默认）
- 产生的问题：不可重复读、幻读
repeatable read：可重复读（MySQL默认）
- 产生的问题：幻读
serializable：串行化
- 解决所有问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查看隔离级别：

select @@transaction_isolation;

数据库设置隔离级别：

如设为读已提交：SET GLOBAL TRANSACTION ISOLATION LEVEL read committed;

`DCL`：管理用户，授权

管理用户

主要有以下四种操作：

添加用户
删除用户
修改用户
查询用户

查询用户

-- 1.切换到MySQL数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;

创建用户

-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED '密码';

删除用户

-- 删除用户
DROP USER '用户名' @'主机名';

修改密码

-- 修改密码
UPDATE USER SET PASSWORD = PASSWORD("新密码") WHERE USER = '用户名';

-- DCL特有的修改密码的语句
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

在MySQL中忘记了root用户的密码？(5.5版本)

cmd --> net stop mysql停止mysql服务（需要管理员权限）；
使用无验证方式启动mysql服务：mysqld --skip-grant-tables；
打开新的cmd窗口，直接输入mysql命令，敲回车,就可以登陆成功；
use mysql;
UPDATE USER SET PASSWORD = PASSWORD("新密码") WHERE USER = '你的用户名';
关闭两个窗口；
打开任务管理器，手动结束mysqld.exe的进程；
启动mysql服务；
使用新密码登录。

权限管理

主要有以下3个操作：

查询权限
授予权限
撤销权限

查询权限

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';

授予权限

-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

-- 授予所有表的所有权限
GRANT ALL ON *.* TO '用户名'@'主机名';

撤销权限

-- 撤销权限
GRANT 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

五岁能抬头1995

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

【MySQL】part3

【MySQL】part3

多表查询

内连接查询

外连接查询

子查询

子查询的不同情况

练习

事务

事务的演示

事务的自动提交与手动提交

事务的四大特征【面试常问】

事务的隔离级别【了解】

DCL：管理用户，授权

管理用户

权限管理

【`MySQL`】`part3`

`DCL`：管理用户，授权