Mysql数据库基础知识（超详细）

小咪咪 2023-09-25 19:46 33阅读 0赞

### Mysql数据库基础知识（超详细） ###

*   *   *  数据库
         *  数据库设计
         *   *   *  MySQL概述
                 *  数据库设计-DDL
         *  数据库操作
         *   *   *  数据库操作-DML
                 *  数据库操作-DQL
                 *  多表设计
                 *  多表查询
         *  数据库优化
         *   *   *  事务
                 *  索引

### 数据库 ###

> 数据库:DataBase ( **DB**)，是存储和管理数据的仓库。  
> 数据库管理系统:DataBase Management System (**DBMS**)，操纵和管理数据库的大型软件。  
> SQL: Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

![在这里插入图片描述][87d642c979ea4f019fa159abd020b2f9.png]

### 数据库设计 ###

##### MySQL概述 #####

![在这里插入图片描述][8070e3accdd44971957a8111f7e1a6b6.png]  
![在这里插入图片描述][a1f8bc5399e540af9e52d2b9c3369976.png]

##### 数据库设计-DDL #####

![在这里插入图片描述][9c9daafc93bc46ee8e4b9b2bd26499b1.png]  
![在这里插入图片描述][c8542f07e67043c6a933a6088b6264ac.png]  
![在这里插入图片描述][5d8ca5ad6cdb48c792b63a842cf06c6d.png]

### 数据库操作 ###

##### 数据库操作-DML #####

![在这里插入图片描述][8bf82a43b8b843d6ae9b5dd82384c0e4.png]  
![在这里插入图片描述][feb4e2e468c54d44ad5fdace91f25fa2.png]  
![在这里插入图片描述][4ea667a141f6485994a3b12c28fffc73.png]  
![在这里插入图片描述][e1eda913b01744b9b45feba14ac84c00.png]

##### 数据库操作-DQL #####

![在这里插入图片描述][43618975b81c4d51bd1712dc7229bf0e.png]  
![在这里插入图片描述][596e0ab8272c4318857e3c43d9f4f08e.png]  
![在这里插入图片描述][c74c0bfe8b1241e7a4cdacaa63e04eed.png]  
![在这里插入图片描述][96db7be4dfca41839781ecd29b58ca6e.png]  
![在这里插入图片描述][74e8c49d2ca54b70ba8d1d1b6a04b074.png]  
![在这里插入图片描述][9b851adfad7f4657b3fdef183fedc705.png]  
![在这里插入图片描述][d53b229d453a41cba6c239f81a0fd896.png]

##### 多表设计 #####

> 1.一对多  
> 在多的一方添加外键，关联另外一方的主键。  
> 2.一对一  
> 任意一方，添加外键，关联另外一方的主键。  
> 3．多对多  
> 通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键。

![在这里插入图片描述][44f287ace5404e0095a576819ff46f95.png]

![在这里插入图片描述][b5a888a4a0eb4ce38bad5c196cac4c4f.png]

##### 多表查询 #####

![在这里插入图片描述][e602dc58792f4ebeb61533b4083861d8.png]

> 内连接  
> 隐式内连接: select 字段列表 from 表1,表2 where 条件…;  
> 显式内连接: select 字段列表 from 表1 \[ inner \] join 表2 on 连接条件…;  
> 外连接  
> 左外连接: select 字段列表 from 表1 left \[ outer \] join 表2 on 连接条件…;  
> 右外连接: selec t字段列表 from 表1 right \[ outer \] join 表2 on 连接条件…;  
> 子查询  
> 介绍:SQL语句中嵌套select语句，称为嵌套查询，又称子查询。  
> 形式: select \* from t1 where column1 = ( select column1 from t2 …);  
> 子查询外部的语句可以是insert / update / delete / select的任何一个，最常见的是select

**相关查询的练习**

-- ============================= 内连接 =============================
    -- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
    SELECT tb_emp.`name`as 姓名,tb_dept.`name` AS 部门 FROM tb_emp,tb_dept WHERE tb_emp.dept_id=tb_dept.id
    
    -- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
    SELECT tb_emp.`name`as 姓名,tb_dept.`name` AS 部门 FROM
    tb_emp INNER JOIN tb_dept on tb_emp.dept_id=tb_dept.id
    
    -- =============================== 外连接 ============================
    -- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
    SELECT tb_emp.`name` AS 姓名,tb_dept.`name` AS 部门 FROM 
    tb_emp LEFT JOIN tb_dept ON tb_emp.dept_id=tb_dept.id
    
    -- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
    SELECT tb_emp.`name` AS 姓名,tb_dept.`name` AS 部门 FROM 
    tb_emp RIGHT JOIN tb_dept ON tb_emp.dept_id=tb_dept.id
    
    -- ========================= 子查询 ================================
    -- 标量子查询
    -- A. 查询 "教研部" 的所有员工信息
    SELECT * FROM tb_emp WHERE tb_emp.dept_id=(
    SELECT tb_dept.id FROM tb_dept WHERE tb_dept.`name`='教研部' )
    
    -- B. 查询在 "方东白" 入职之后的员工信息
    SELECT * FROM tb_emp WHERE entrydate>(SELECT entrydate FROM
    tb_emp WHERE `name`='方东白'
    )
    
    -- 列子查询
    -- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
    SELECT * FROM tb_emp WHERE dept_id in (SELECT id FROM tb_dept WHERE `name`in ('教研部','咨询部'))
    
    -- 行子查询
    -- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
    SELECT * FROM tb_emp WHERE (entrydate=(SELECT entrydate FROM tb_emp WHERE `name`='韦一笑') and job =(SELECT job FROM tb_emp WHERE  `name`='韦一笑'))
    
    -- 表子查询
    -- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
    SELECT tb_emp.*,tb_dept.* FROM tb_emp LEFT JOIN tb_dept ON tb_emp.dept_id=tb_dept.id
     WHERE tb_emp.entrydate>'2006-01-01' 
    
    -- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
    SELECT dish.`name`,dish.price ,category.`name` as 分类 FROM dish,category WHERE dish.price<10 AND category.id=dish.category_id
    
    -- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
    SELECT dish.`name`,dish.price,category.`name` FROM dish LEFT JOIN category ON category.id=dish.category_id
    WHERE price BETWEEN 10 and 50  and dish.`status`=1 
    
    -- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
    SELECT category.`name`,MAX(dish.price) AS 最贵的 FROM dish,category WHERE dish.category_id=category.id GROUP BY category.`name`
    
    -- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
    SELECT category.`name`,COUNT(*)FROM dish,category WHERE category.`status`=1 AND dish.category_id=category.id  
    GROUP BY category.`name` HAVING count(*)>=3
    
    -- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示 出套餐名称、价格, 包含的菜品名称、价格、份数）.
    SELECT dish.`name`,dish.price ,setmeal_dish.copies,setmeal.`name` ,setmeal.price FROM dish ,setmeal,setmeal_dish WHERE 
     setmeal_dish.dish_id=dish.id and setmeal_dish.setmeal_id=setmeal.id AND setmeal.`name`='商务套餐A' 
    
    -- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
    SELECT dish.`name`,dish.price FROM dish WHERE dish.price < (SELECT AVG(price) FROM dish)

### 数据库优化 ###

##### 事务 #####

> **介绍&操作**  
> **事务**是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。  
> 开启事务: start transaction; / begin ;  
> 提交事务:commit;  
> 回滚事务:rollback;

**四大特性**  
![在这里插入图片描述][f92e7ded90c84fd18537def64420f187.png]

##### 索引 #####

**索引(index)** 是帮助数据库高效获取数据的数据结构。  
![在这里插入图片描述][4f7444db17324b6a9e0a03e7d289e3aa.png]  
![在这里插入图片描述][70099584f97c4930bd8a4f9a58bbfdc0.png]  
![在这里插入图片描述][0832283d11604fefa42da3c02e7a5e2f.png]  
![在这里插入图片描述][964c5de6e8914d798501f98e6b872a46.png]

> **语法**  
> 创建索引  
> create \[ unique \] index索引名on表名(字段名,… );  
> 查看索引  
> show index from表名;  
> 删除索引  
> drop index索引名 on表名;  
> **主键字段，在建表时，会自动创建主键索引。  
> 添加唯一约束时，数据库实际上会添加唯一索引**

[87d642c979ea4f019fa159abd020b2f9.png]: https://img-blog.csdnimg.cn/87d642c979ea4f019fa159abd020b2f9.png
[8070e3accdd44971957a8111f7e1a6b6.png]: https://img-blog.csdnimg.cn/8070e3accdd44971957a8111f7e1a6b6.png
[a1f8bc5399e540af9e52d2b9c3369976.png]: https://img-blog.csdnimg.cn/a1f8bc5399e540af9e52d2b9c3369976.png
[9c9daafc93bc46ee8e4b9b2bd26499b1.png]: https://img-blog.csdnimg.cn/9c9daafc93bc46ee8e4b9b2bd26499b1.png
[c8542f07e67043c6a933a6088b6264ac.png]: https://img-blog.csdnimg.cn/c8542f07e67043c6a933a6088b6264ac.png
[5d8ca5ad6cdb48c792b63a842cf06c6d.png]: https://img-blog.csdnimg.cn/5d8ca5ad6cdb48c792b63a842cf06c6d.png
[8bf82a43b8b843d6ae9b5dd82384c0e4.png]: https://img-blog.csdnimg.cn/8bf82a43b8b843d6ae9b5dd82384c0e4.png
[feb4e2e468c54d44ad5fdace91f25fa2.png]: https://img-blog.csdnimg.cn/feb4e2e468c54d44ad5fdace91f25fa2.png
[4ea667a141f6485994a3b12c28fffc73.png]: https://img-blog.csdnimg.cn/4ea667a141f6485994a3b12c28fffc73.png
[e1eda913b01744b9b45feba14ac84c00.png]: https://img-blog.csdnimg.cn/e1eda913b01744b9b45feba14ac84c00.png
[43618975b81c4d51bd1712dc7229bf0e.png]: https://img-blog.csdnimg.cn/43618975b81c4d51bd1712dc7229bf0e.png
[596e0ab8272c4318857e3c43d9f4f08e.png]: https://img-blog.csdnimg.cn/596e0ab8272c4318857e3c43d9f4f08e.png
[c74c0bfe8b1241e7a4cdacaa63e04eed.png]: https://img-blog.csdnimg.cn/c74c0bfe8b1241e7a4cdacaa63e04eed.png
[96db7be4dfca41839781ecd29b58ca6e.png]: https://img-blog.csdnimg.cn/96db7be4dfca41839781ecd29b58ca6e.png
[74e8c49d2ca54b70ba8d1d1b6a04b074.png]: https://img-blog.csdnimg.cn/74e8c49d2ca54b70ba8d1d1b6a04b074.png
[9b851adfad7f4657b3fdef183fedc705.png]: https://img-blog.csdnimg.cn/9b851adfad7f4657b3fdef183fedc705.png
[d53b229d453a41cba6c239f81a0fd896.png]: https://img-blog.csdnimg.cn/d53b229d453a41cba6c239f81a0fd896.png
[44f287ace5404e0095a576819ff46f95.png]: https://img-blog.csdnimg.cn/44f287ace5404e0095a576819ff46f95.png
[b5a888a4a0eb4ce38bad5c196cac4c4f.png]: https://img-blog.csdnimg.cn/b5a888a4a0eb4ce38bad5c196cac4c4f.png
[e602dc58792f4ebeb61533b4083861d8.png]: https://img-blog.csdnimg.cn/e602dc58792f4ebeb61533b4083861d8.png
[f92e7ded90c84fd18537def64420f187.png]: https://img-blog.csdnimg.cn/f92e7ded90c84fd18537def64420f187.png
[4f7444db17324b6a9e0a03e7d289e3aa.png]: https://img-blog.csdnimg.cn/4f7444db17324b6a9e0a03e7d289e3aa.png
[70099584f97c4930bd8a4f9a58bbfdc0.png]: https://img-blog.csdnimg.cn/70099584f97c4930bd8a4f9a58bbfdc0.png
[0832283d11604fefa42da3c02e7a5e2f.png]: https://img-blog.csdnimg.cn/0832283d11604fefa42da3c02e7a5e2f.png
[964c5de6e8914d798501f98e6b872a46.png]: https://img-blog.csdnimg.cn/964c5de6e8914d798501f98e6b872a46.png