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

数据库

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

数据库设计

MySQL概述

数据库设计-DDL

数据库操作

数据库操作-DML

数据库操作-DQL

多表设计

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

多表查询

内连接
隐式内连接: 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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

数据库优化

事务

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

四大特性

索引

索引(index) 是帮助数据库高效获取数据的数据结构。

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