mysql 基础

最新推荐文章于 2024-07-08 21:31:16 发布

guoyiguang2

最新推荐文章于 2024-07-08 21:31:16 发布

阅读量311

点赞数 1

文章标签： mysql

参考视频：

https://www.bilibili.com/video/BV12b411K7Zu?p=76

（sql 脚本在最下面）

# 92 sql按功能划分：等值连接，非等值连接，自连接：

语法：

select 字段

from 表A，表B

where 连接条件
and
group by 字段
having [分组函数，等等]
order by 字段

# 99 sql按功能划分：内连接（等值连接，非等值连接，自连接），外连接（左外，右外，全外），交叉连接
# 等值连接： = ；非等值连接：！= ，> < ，between  and 在两个数之间 ，等等
语法：
select 字段
from 表A
连接类型（可以是 inner join  ，left join 等 ）
on 连接条件
where 过滤条件
and
group by 字段
having
order by 字段

# 笛卡尔乘积现象 boys 三条数据；girl 2条数据；不加条件生成3*2条数据
select boys.name ,girl.name from   boys,girl ;
# 有女朋友的男生情况 （建模，男生表中每行数据根据条件去找对应的 女表中数据）
select boys.name ,girl.name from   boys,girl where boys.id = girl.boy_id;

2. 非等值连接

#查询每个员工的工资级别


# 用员工表的每一条记录去匹配工资等级表，匹配条件是 非等值 条件（大于等于，或者between  and）
select employee.*, salary_level.level
from employee,
     salary_level
where employee.salary >= salary_level.low and employee.salary <= salary_level.high;

3.自连接

自己和自己连接，有些特殊字段，比如 id，parent_id 这种；省市县等

# 需求：找出员工及其领导的姓名和工资
# 分析： 员工姓名和员工工资 在 employee 表，领导姓名和工资在 employee 表，员工表的每条记录通过
# manage_id 去匹配 另一个employee 表的领导记录（将一张表看成两张表：员工表和领导表）
select e.name, e.salary, m.name, m.salary
from employee e,
     employee m
where e.manage_id = m.id;
# 查询结果说明：
# 数据只有三个员工的记录，而没有两个领导的记录，因为 员工表的 两个领导分别取匹配 领导表的 条件是根据 e.manage_id = m.id 这个条件匹配的
# 比如员工的id 为4和5，他们的manage_id 都为null，会去 领导表里去找 id为 null 的记录去匹配，因为匹配不到，自身也就不满足条件不显示了

表数据如下：

查询结果如下：（领导的两条数据不显示的，因为不满足 m.id = e.manage_id; 即 4= null ，5 = null 不存在）

此时效果等同于：（92sql 语法和 99语法的 inner join 底层原理是一样的，都是根据条件去匹配，去除笛卡尔积，所以调换表顺序结果一样）

select e.name, e.salary, m.name, m.salary
from employee e
    inner join  employee m
on e.manage_id = m.id;

分组函数：max ，min，sum，avg，count

分组语法：

select 分组函数，分组字段

from

表

where 分组前筛选条件，针对原始表

group by 分组字段

having 分组后筛选条件 [分组函数，等等]

order by 排序

一般都会出现：查询每个国家，每个就会考虑分组

sql 99 语法：

新增加了：

内连接：

外连接：左外，右外，全外（mysql不支持）

交叉连接：

语法：

select 字段

form 表A 连接类型

join 表B on 匹配条件1 and 匹配条件2 (说明：匹配条件2 可以是对表A的限制，也可以是对表B的限制，一般都是对表B)

where 结果集过滤条件

连接类型：

内联：inner join （说明：inner 可以省略）

外联：左外 left，全外 full

交叉连接：cross

内连接：

原理：三张表内联

前两张表关联生成一张大表，只要这张大表的字段可以和第三张表相关联就可以（三张表关联转为两张表关联这种思路）

外连接：

应用场景：查询一个表中有，另一个表中没有的

示例：

sql 如下：

# 左连接的应用场景： 一张表的所有数据都要显示（交集+非交集部分）
# 查询女生 有无 男朋友情况
select * from girl
left join boys on boys.id = girl.boy_id;

# 查询男生 有无 女朋友情况
select * from  boys
left join  girl on boys.id = girl.boy_id;

# 非单身的情况
# 写法1（92sql）
select * from boys  ,
 girl  where boys.id = girl.boy_id;
# 写法2（99sql）
select * from   boys inner join
  girl on boys.id = girl.boy_id;

例子2：

1. 子查询

列子查询:

上面说法有问题：

验证：

# 子查询
#子查询出现的位置:
#  select 后面（单行单列子查询：增加一列数据，子查询里的where 和表外的 字段做关联） ;
#  from 后面;
#  where 或 having后面；
#  exists(相关子查询)
#按结果集的行列数不同划分:一行一列 标量子查询；一列多行 列子查询；一行多列 列子查询；多行多列 表子查询

# select 后的  单行单列，适用场景：给某个表新增加一列数据，而且 子查询的where 和外表的 字段做关联
# 需求：统计每个部门的员工总数
#      方法①
   select count(*) , department_id from employee group by department_id;
#    需要显示部门名称时
select count(*), department_id,department.name
from employee
         inner join department on employee.department_id = department.id
group by department_id,department.name;
# 方法② 用left join ；部门作为主表
select count(*), department_id,department.name
from  department
         left join employee on employee.department_id = department.id
group by department_id,department.name;
# 方法③ select 子查询（单行单列）
select d.name,(
#     子查询是根据部门id 从 员工表获得 当前部门 的人数
    select count(*) from employee  e where e.department_id = d.id
    ) 部门人数
from  department d ;


# select 后的  单行单列，适用场景：给某个表新增加一列数据，而且 子查询的where 和外表的 字段做关联
# 统计每个部门的平均薪资的薪资等级
# 方法① select 后增加一列，内部用where 获得各个部门的薪资等级(有sql语法问题，改写成)
select avg(e.salary), e.department_id,(
    select l.level
    from salary_level  l where e.salary >= l.low and e.salary <= l.high
    ) level
from employee e
group by department_id;
;
# 上有sql语法问题，改写成如下
select department_id,(
    select l.level
    from salary_level  l where e.salary >= l.low and e.salary <= l.high
    ) level
from (
         select avg(e.salary) salary, e.department_id
         from employee e
         group by department_id
     ) e ;
# 显示部门名称
select department_id,e.name,(
    select l.level
    from salary_level  l where e.salary >= l.low and e.salary <= l.high
    ) level
from (
         select avg(e.salary) salary, e.department_id,department.name
         from employee e,department
         where e.department_id = department.id
         group by department_id,department.name
     ) e ;
# 法② 左连接

select e.* ,l.level
from (
         select avg(e.salary) salary, e.department_id,department.name
         from employee e,department
         where e.department_id = department.id
         group by department_id,department.name
     ) e left join salary_level l on e.salary >=l.low and e.salary<=l.high ;
# 法③ inner join 两张表关联
select e.* ,l.level
from (
         select avg(e.salary) salary, e.department_id,department.name
         from employee e,department
         where e.department_id = department.id
         group by department_id,department.name
     ) e inner join salary_level l on e.salary >=l.low and e.salary<=l.high
;

exists

语法：

exists（完整的查询语句）

结果：1或者0

# 查询有员工的部门名
# 法①inner join: 一个部门有多个员工，相乘所以一个部门出现多次，所以用 group by 去重
select department.id,department.name
from department inner join employee e on department.id = e.department_id group by department.id,department.name;
# 法② left join ,但是得加where 过滤掉非交集部分 ; 缺点：部门名称还是会重复，只能在Java代码去重
select department.id, department.name, e.name
from department
         left join employee e on department.id = e.department_id
where e.name is not null
group by department.id, department.name, e.name;
# 法③ in
select *
from department where id in (select distinct department_id from employee);
# 法④ exists 原理：拿出 department 表的所有数据，然后一行一行去和 employee表根据 where后条件去匹配，匹配成功就返回true，将当前记录存到一个集合里
select *
from department  d where exists(select * from employee e where e.department_id = d.id );



# 查询没有女朋友的男生信息
#
select boys.*
from boys
where boys.id not in (
#     有女朋友的男生id
    select girl.boy_id
    from girl
);
# 法② 
select boys.*
# 不包含  not exists
from boys where not exists(
#    有女朋友的男生
    select *
    from girl where girl.boy_id = boys.id
    );

# 查询各部门中工资比 本部门平均工资高的员工信息
# 各部门，每个部门 会涉及到分组
select avg(e.salary) avgSalary,e.department_id
from employee e  group by e.department_id;

# 法①inner join   分组后的虚拟表+ 员工表
select e1.*,avg_dep.*
from employee e1
         inner join (select avg(e.salary) avgSalary, e.department_id
                     from employee e
                     group by e.department_id) avg_dep on e1.department_id = avg_dep.department_id
 where e1.salary >= avg_dep.avgSalary
;
# 法② exists 拿出所有员工信息去以此从 分组后的虚拟表去匹配，匹配成功放到一个list里

select e1.*
from employee e1
where exists(
              select *
              from (
                       select avg(e.salary) avgSalary, e.department_id
                       from employee e
                       group by e.department_id
#                   同部门和大于平均薪资;衍生表必须有别名，否则报错
                   ) avg_dep
              where e1.department_id = avg_dep.department_id
                and e1.salary > avg_dep.avgSalary
          );

====

版本2：

inner join

===

查看执行计划：

explain

id：代表的是表（或者sql的优先级），值越大，优先级越大

id值相同：优先级相同，从上往下依次执行

id值不同：id值越大，优先级越大，越先制性

select_type: primary,subquery

type：

possible_key和key

===

索引优化：

单表优化，两表优化，三表优化

单标：

两表：

索引优化（索引避免失效）

优化原则：

小表驱动大表：小的数据集驱动大的数据集

1. 慢查询：

查看慢查询日志是否开启：

show variables like '%slow_query_log%'

sql脚本：

-- MySQL dump 10.13  Distrib 8.0.22, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: sql_test
-- ------------------------------------------------------
-- Server version	8.0.22

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `boys`
--

DROP TABLE IF EXISTS `boys`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `boys` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` varchar(20) DEFAULT '男' COMMENT '默认男',
  `age` varchar(20) DEFAULT NULL,
  UNIQUE KEY `boys_id_uindex` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='男生表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `boys`
--

LOCK TABLES `boys` WRITE;
/*!40000 ALTER TABLE `boys` DISABLE KEYS */;
INSERT INTO `boys` VALUES (1,'慕容皝','男','22'),(2,'慕容冲','男','33'),(3,'慕容垂','男','44'),(4,'慕容博','男','55');
/*!40000 ALTER TABLE `boys` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `department`
--

DROP TABLE IF EXISTS `department`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `department` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `department`
--

LOCK TABLES `department` WRITE;
/*!40000 ALTER TABLE `department` DISABLE KEYS */;
INSERT INTO `department` VALUES (1,'市场部'),(2,'研发部'),(3,'测试部');
/*!40000 ALTER TABLE `department` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `employee`
--

DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `employee` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `salary` decimal(10,0) DEFAULT NULL,
  `manage_id` int DEFAULT NULL COMMENT '领导id，对应该表的id字段',
  `department_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `employee`
--

LOCK TABLES `employee` WRITE;
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `employee` VALUES (1,'赵文卓',95,4,1),(2,'甄子丹',89,4,1),(3,'李连杰',76,5,2),(4,'张艺谋',96,NULL,2),(5,'冯小刚',96,NULL,NULL);
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `girl`
--

DROP TABLE IF EXISTS `girl`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `girl` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `boy_id` int DEFAULT NULL,
  `sex` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='女性表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `girl`
--

LOCK TABLES `girl` WRITE;
/*!40000 ALTER TABLE `girl` DISABLE KEYS */;
INSERT INTO `girl` VALUES (1,'黄蓉',1,'女'),(2,'李莫愁',2,'女'),(3,'陆无双',2,'女'),(4,'程英',NULL,'女'),(5,'周紫墨',NULL,'女'),(6,'宋小婉',NULL,'女'),(7,'侯小妹',NULL,'女'),(8,'小黄蓉',NULL,'女');
/*!40000 ALTER TABLE `girl` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pro_city_county`
--

DROP TABLE IF EXISTS `pro_city_county`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pro_city_county` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `parent_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pro_city_county`
--

LOCK TABLES `pro_city_county` WRITE;
/*!40000 ALTER TABLE `pro_city_county` DISABLE KEYS */;
INSERT INTO `pro_city_county` VALUES (1,'山西省',0),(2,'陕西省',0),(3,'北京市',0),(4,'晋中市',1),(5,'临汾市',1),(6,'平遥县',4),(7,'洪洞县',4),(8,'海淀区',3),(9,'渭南市',2),(10,'潼关县',9);
/*!40000 ALTER TABLE `pro_city_county` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `salary_level`
--

DROP TABLE IF EXISTS `salary_level`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `salary_level` (
  `level` varchar(20) DEFAULT NULL,
  `low` decimal(10,0) DEFAULT NULL,
  `high` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `salary_level`
--

LOCK TABLES `salary_level` WRITE;
/*!40000 ALTER TABLE `salary_level` DISABLE KEYS */;
INSERT INTO `salary_level` VALUES ('A',90,100),('B',80,89),('C',0,79);
/*!40000 ALTER TABLE `salary_level` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-01-02  3:16:35

guoyiguang2

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 基础

参考视频：https://www.bilibili.com/video/BV12b411K7Zu?p=761. # 92sql按功能划分：等值连接，非等值连接，自连接：语法：select 字段from 表A，表Bwhere 连接条件andgroup by 字段havingorder by 字段# 99 sql按功能划分：内连接（等值连接，非等值连接，自连接），外连接（左外，右外，全外），交叉连接# 等值连接： = ；非等值连接：！= ，> <...
复制链接

扫一扫