MySQL第二周完结

农专猿

已于 2023-01-28 18:31:48 修改

阅读量65

点赞数

分类专栏：后端文章标签： mysql 数据库 Powered by 金山文档

于 2023-01-28 16:14:39 首次发布

本文链接：https://blog.csdn.net/m0_73886108/article/details/128766070

版权

后端专栏收录该内容

33 篇文章 0 订阅

订阅专栏

DCL-数据控制语言

管理用户

查询用户

use mysql;

select * from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

修改用户密码

alter user '用户名'@'主机名' identified by '密码';

删除用户

drop user '用户名'@'主机名'；

注意：

主机名可以使用%通配。

这类sql开发人员操作的较少，主要是Database Administrator使用。

权限控制

查询权限

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

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

多个权限之间，使用逗号分隔

授权时，数据库名和表名可以使用*进行通配，代表所有。

多表查询

多表关系

一对多（多对一）

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一（UNIQUE）

多表查询分类

连接查询

内连接：相当于查询A、B交集部分数据（查询不出有null的数据）

隐式内连接

SELECT 字段列表 FROM 表1，表2 WHERE条件…；

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…；

外连接:（可以查出有null的数据）

左外连接：查询左表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件…；

右外连接：查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件…；

自连接：当前表与自身的连接查询，自连接必须使用表别名

可以用内连接，也可以用外连接

SELECT 字段列表 FROM 表A 别名1 JOIN 表A 别名2 ON 连接条件…；

联合查询：

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A...

union [all]

select 字段列表 from 表B...；

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重。

子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

select * from t1 where column1=(select column1 from t2);

子查询的外部语句可以是insert/update/delete/select的任何一个。

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

常用的操作符：= <> > >= < <=

列子查询（子查询结果为一列）

常用操作符：in( 在指定的集合范围之内，多选一)，not in(不在指定的集合范围之内)，

any(子查询返回列表中，有任意一个满足即可)，all(子查询返回列表的所有值都必须满足)

行子查询（子查询结果为一行）

常用操作符：in，not in，==，<>

表子查询（子查询结果为多行多列）

常用操作符：in

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

多表查询案例

-- 1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id;
-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name,e.age,e.job,d.name from emp e join dept d on e.dept_id=d.id where e.age<30;
-- 3. 查询拥有员工的部门ID、部门名称
select distinct d.id,d.name from dept d,emp e where e.dept_id=d.id;
-- 4. 查询所有年龄大于40岁的员工信息, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age>40;
-- 5. 查询所有员工的工资等级
select e.name,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;
-- 6. 查询 "研发部" 所有员工的信息及 工资等级
select e.*,s.grade from emp e, salgrade s,dept d where e.dept_id =d.id and (e.salary between s.losal and s.hisal) and d.name='研发部';
-- 7. 查询 "研发部" 员工的平均工资
select avg(e.salary) from emp e,dept d where e.dept_id=d.id and d.name='研发部';
-- 8. 查询工资比 "灭绝" 高的员工信息。
-- a.查询灭绝的工资
select e.salary from emp e where e.name='灭绝';
-- b.查询员工信息
select * from emp where salary>(select salary from emp  where name='灭绝');
-- 9. 查询比平均薪资高的员工信息
-- a.查询平均薪资
select avg(salary) from emp;
-- b.查询员工信息
select * from emp where salary>(select avg(salary) from emp);
-- 10. 查询低于本部门平均工资的员工信息（子查询）
-- a.查询本部门平局工资
select avg(e1.salary) from emp e1 where e1.dept_id=1;
-- b.查询员工信息
select * from emp e2 where e2.salary<(select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);
-- 11. 查询所有的部门信息, 并统计部门的员工人数
-- a.统计部门的员工人数
select count(*) from emp e where e.dept_id=1;
-- b.查询所有部门信息
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id) '员工人数' from dept d;
-- 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
select s.name,s.no,c.name from student s,course c,student_course sc where s.id=sc.studentid and sc.courseid=c.id;