Mysql数据库基础知识(超详细)
- 数据库
- 数据库设计
- MySQL概述
- 数据库设计-DDL
- 数据库操作
- 数据库操作-DML
- 数据库操作-DQL
- 多表设计
- 多表查询
- 数据库优化
- 事务
- 索引
数据库
数据库: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)
数据库优化
事务
介绍&操作
事务是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
开启事务: start transaction; / begin ;
提交事务:commit;
回滚事务:rollback;
四大特性
索引
索引(index) 是帮助数据库高效获取数据的数据结构。
语法
创建索引
create [ unique ] index索引名on表名(字段名,… );
查看索引
show index from表名;
删除索引
drop index索引名 on表名;
主键字段,在建表时,会自动创建主键索引。
添加唯一约束时,数据库实际上会添加唯一索引