1. DDL
1.1. 概念:
Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段)
1.2. 格式:
数据库:
| 查询所有数据库 | show databases; |
|---|---|
| 使用数据库 | use 数据库名 ; |
| 创建数据库 | create database [ if not exists ] 数据库名 ; |
| 删除数据库 | drop database [ if exists ] 数据库名 ; |
show databases;
use db1;
create database db1;
create schema db1;
create database if not exists db2 ;
drop database db1;
drop database if exits db1;
表:
| 增加表格 | create table 表名(字段1 字段类型 [ 约束 ] [ comment 字段1注释 ]......字段n 字段类型 [ 约束 ] [ comment 字段n注释 ] ) |
|---|---|
| 删除表格 | drop table [ if exists ] 表名; |
| 添加字段 | alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]; |
| 修改字段类型 | alter table 表名 modify 字段名 新数据类型(长度); |
| 修改字段名和字段类型 | alter table 表名 change 旧字段名 新字段名 类型 (长度)[comment 注释] [约束]; |
| 删除字段 | alter table 表名 drop column 字段名; |
| 修改表名 | rename table 表名 to 新表名; |
| 查询当前数据库所有表 | show tables; |
| 查询表结构 | desc 表名; |
| 查询建表语句 | show create table 表名; |
create table user2
(
id int primary key auto_increment comment '主键',
username varchar(20) not null unique comment '登录账号',
name varchar(10) not null ,
age int,
gender char(1) default '男'
);
drop table user;
alter table emp add address varchar(50); #加地址列
alter table emp drop name;
alter table emp modify username varchar(21); #改数据类型(长度,约束)
alter table emp change image img varchar(100); #改字段名并指定新的字段长度
rename table user to user3;
show tables;
desc emp;
show create table emp;
1.3. 约束：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key (auto_increment自增) |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
| 无符号约束 | 没有负值（用于数据类型后） | unsign |
外键：
| 创建表时指定 | create table 表名(字段名 数据类型, ... [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)); |
|---|---|
| 创建表后增加 | alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名); |
create table emp2
(
id int auto_increment
primary key
unique,
emp_id int not null,
constraint emp2_emp_id_fk
foreign key (emp_id) references emp (id)
);
alter table dish
add constraint dish_category_id_fk
foreign key (category_id) references category (id);
作用：
用于建立表与表之间的关系，以保证数据的完整性和一致性。同时，外键也可以限制数据的删除和修改，以避免对关联表中的数据造成影响。
2. DML
2.1. 概念：
Data Manipulation Language, 数据操作语言，用来对数据库表中的数据进行增删改
2.2. 格式:
| 指定字段添加数据 | insert into 表名 (字段名1, 字段名2) values (值1, 值2); |
|---|---|
| 全部字段添加数据 | insert into 表名 values (值1, 值2, ...); |
| 批量添加数据（指定字段） | insert into 表名 (字段名1, 字段名2)values (值1, 值2), (值1, 值2); |
| 批量添加数据（全部字段） | insert into 表名 values (值1, 值2, ...), (值1, 值2, ...); |
| 删除数据 | delete from 表名 [ where 条件 ]; |
| 修改数据 | update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ where 条件 ] ; |
-- 插入单条数据片段
insert into user (name, username, gender)
values ('李四', 'lisi', 1);
-- 插入单条数据
insert into user (id, username, name, ages, gender)
values (3, 'wangwu', '王五', 20, '男');
insert into user
values (4, 'zhaoliu', '赵六', 33, '1');
-- 插入多条数据
insert into user
values (5, 'hu', 'hu', 33, '1'),
(6, 'hu', 'hu', 33, '1');
-- 修改数据
update user
set username = 'zhangsan'
where id = '5';
-- 删除数据
delete
from user
where id = 6;
delete
from user
where id in (4, 5);
2.3. 注意事项:
- 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
- DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为NULL)。
3. DQL
3.1. 概念：
Data Query Language(数据查询语言)，用来查询数据库表中的记录。
关键字：SELECT
3.2. 格式：
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
select *
from emp
where name like '%张%'
and gender = 1
and entrydate between '2000-01-01' and '2010-01-01'
order by entrydate desc
limit 10;
select if(gender = 1, '男性员工', '女性员工'), count(gender)
from emp
group by gender;
select case job
when 1 then '班主任'
when 2 then '讲师'
when 3 then '教研主管'
when 4 then '学工主管'
else '无'
end,
count(job)
from emp
group by job;
3.3. 分类：
基本查询
条件查询（where）
分组查询（group by）
排序查询（order by）
分页查询（limit）
3.4. 注意事项：
- null值不参与所有聚合函数运算。
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
where与having区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
4. 多表设计
4.1. 概念：
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多，多对多，一对一。
4.2. 分类:
一对一
关系:
多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
实现:
在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
案例:
用户 与 身份证信息 的关系
用户基本信息表(tb_user)
用户身份信息表(tb_user_card)
一对多
关系：
一张父表对应多个子表，每个部门有多名员工
实现：
在数据库表中多的一方，添加字段，来关联一的一方的主键。
案例：
部门与员工的关系
员工表（tb_emp）
部门表（tb_dept）
多对多
关系：
一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
案例：
学生 与 课程的关系
学生表(tb_student)
课程表(tb_course)
学生课程关系表(tb_student_course)
5. 多表查询
5.1. 分类:
内连接查询
外连接查询
子查询
-- ============================= 内连接 ==========================
-- A.查询员工的姓名, 及所属的部门名称 (隐式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp,
tb_dept
where tb_emp.dept_id = tb_dept.id;
-- B.查询员工的姓名, 及所属的部门名称 (显式内连接实现)
select tb_emp.name, tb_dept.name
from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;
select tb_emp.name, tb_dept.name
from tb_emp
inner join tb_dept on tb_emp.dept_id = tb_dept.id;
-- =============================== 外连接 ============================
-- A.查询员工表所有员工的姓名, 和对应的部门名称 (左外连接)
select tb_emp.name , tb_dept.name
from tb_emp
left outer join tb_dept on tb_emp.dept_id = tb_dept.id;
select tb_emp.name , tb_dept.name
from tb_emp
left join tb_dept on tb_emp.dept_id = tb_dept.id;
-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select tb_dept.name as 部门, tb_emp.name as 员工
from tb_dept
right join tb_emp on tb_dept.id = tb_emp.dept_id;
-- ========================= 子查询 ================================
-- 标量子查询
-- A.查询 "教研部" 的所有员工信息
select *
from tb_emp
where dept_id = (select id
from tb_dept
where 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, job) = (select entrydate, job
from tb_emp
where name = '韦一笑');
-- 表子查询
-- A.查询入职日期是 "2006-01-01" 之后的员工姓名 , 及其部门信息
select tb_emp.name, tb_dept.name
from tb_emp,
tb_dept
where entrydate > '2006-01-01'
and tb_emp.dept_id = tb_dept.id;
select tb_emp2.name,tb_dept.name
from (select * from tb_emp where entrydate > '2006-01-01') as tb_emp2
left join tb_dept on tb_emp2.dept_id = tb_dept.id;
-- 1.查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称
select d.name, d.price, c.name
from dish d,
category c
where price < 10 && d.category_id = c.id;
-- 2.查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来)
select dish.name, dish.price, c.name
from dish,
category c
where price >= 10 && price <= 50
and dish.category_id = c.id
and dish.status = 1;
-- 3.查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格
select c.name, max(d.price)
from dish d,
category c
where d.category_id = c.id
group by c.name;
select c.name, d2.max
from (select category_id, max(price) max
from dish
group by category_id) d2,
category c
where d2.category_id = c.id;
-- 4.查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3的 分类名称
select c.name, count(d.status)
from category c,
dish d
where d.category_id = c.id && d.status = 1
group by c.name
having count(d.status) >= 3;
select category_id, count(*)
from dish
where status = 1
group by category_id
having count(*) >= 3;
select c.name ,d.count
from (select category_id, count(*) count
from dish
where status = 1
group by category_id
having count >= 3) d,
category c
where d.category_id = c.id;
-- 5.查询出 "商务套餐A" 中包含了哪些菜品 (展示出套餐名称、价格, 包含的菜品名称、价格、份数)
select s.name, s.price, d.name
from setmeal s,
dish d,
setmeal_dish sd
where s.name = '商务套餐A'
and sd.setmeal_id = s.id
and sd.dish_id = d.id;
-- 6.查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
select dish.name, price
from dish
where price < (select avg(price) from dish);
5.2. 注意事项:
- 外连接可以查出null值
- 外键可以为null
