DBMS-MySQL

它是一种软件系统，用于创建、管理和维护数据库。DBMS 提供了一系列功能，包括数据定义、数据存储、数据操作（如查询、插入、更新、删除等）、数据控制（如访问权限管理）、数据备份与恢复等。常见的 DBMS 有 MySQL、Oracle、SQL Server、DB2 等。它在企业信息化、数据处理和管理等方面发挥着重要作用。

DBMS相当于用户和数据库之间的桥梁

所以，MySQL本身不是数据库，而是一种数据库管理系统。

它用于管理和操作数据库。在 MySQL 中，可以创建多个具体的数据库，这些数据库用于实际存储数据，如用户信息、业务数据等。

可以说 MySQL 是实现对数据库进行管理和操作的工具软件。

二.数据库分类

数据库主要有以下几种分类方式：

按数据模型分类：

关系型数据库：如 MySQL、Oracle、SQL Server 等，以二维表的形式组织数据，遵循特定的关系模型。（个人见解：很像Exce，用行和列来组织数据l)
非关系型数据库（NoSQL 数据库）：包括键值对存储数据库（如 Redis）、文档型数据库（如 MongoDB）、列族数据库（如 HBase）、图数据库等。

按应用场景分类：

事务型数据库：强调事务的一致性和可靠性，常用于在线交易等场景。
分析型数据库：主要用于数据的分析处理和决策支持。

按部署方式分类：

本地数据库：安装在本地服务器或计算机上。
云数据库：部署在云计算平台上。

关系型数据库

基于二维表（类似于excel中的表格）

三.SQL介绍

操作关系型数据库的DBMS大部分使用SQL管理数据

SQL（Structured Query Language，结构化查询语言）是用于管理关系型数据库的标准语言。

它具有以下主要功能和特点：

DDL数据定义：用于创建、修改和删除数据库对象，如表、视图、索引等。

DML数据操作：插入数据（INSERT 语句）、更新数据（UPDATE 语句）、删除数据（DELETE 语句）等操作。

DCL数据控制：如设置用户权限等。

DQL数据查询：可以进行各种复杂的查询，通过不同的子句和条件来精确获取所需的数据。

SQL 是关系型数据库操作的基础和核心，几乎所有的关系型数据库都支持 SQL，尽管不同的数据库在具体语法上可能会有一些差异。(学会SQL，就相当于学会了关系型DBMS的核心）

四.SQl基本语法

SQL数据类型

数值类型

分类	类型	大小(byte)	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述	备注
数值类型	tinyint	1	(-128，127)	(0，255)	小整数值
	smallint	2	(-32768，32767)	(0，65535)	大整数值
	mediumint	3	(-8388608，8388607)	(0，16777215)	大整数值
	int	4	(-2147483648，2147483647)	(0，4294967295)	大整数值
	bigint	8	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
	float	4	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值	float(5,2)：5表示整个数字长度，2 表示小数位个数
	double	8	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值	double(5,2)：5表示整个数字长度，2 表示小数位个数
	decimal				小数值(精度更高)	decimal(5,2)：5表示整个数字长度，2 表示小数位个数

字符串类型

分类	类型	大小	描述
字符串类型	char	0-255 bytes	定长字符串	char(10): 最多只能存10个字符, 不足10个字符,占用10个字符空间
	varchar	0-65535 bytes	变长字符串	varchar(10): 最多只能存10个字符, 不足10个字符, 按照实际长度存储
	tinyblob	0-255 bytes	不超过255个字符的二进制数据
	tinytext	0-255 bytes	短文本字符串
	blob	0-65 535 bytes	二进制形式的长文本数据
	text	0-65 535 bytes	长文本数据	phone char(11)
	mediumblob	0-16 777 215 bytes	二进制形式的中等长度文本数据	username varchar(20)
	mediumtext	0-16 777 215 bytes	中等长度文本数据
	longblob	0-4 294 967 295 bytes	二进制形式的极大文本数据
	longtext	0-4 294 967 295 bytes	极大文本数据

日期类型

分类	类型	大小(byte)	范围	格式	描述
日期类型	date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
	time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
	year	1	1901 至 2155	YYYY	年份值
	datetime	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
	timestamp	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

语法

数据库database可以换成scheme，语法实现效果一致

模式（schema）是数据库中全体数据的逻辑结构和特征的描述

1.创建数据库

CREATE DATABASE database_name;

2.指定数据库

USE database_name;

这里的 database_name 就是要指定的数据库的名称。执行该语句后，后续的操作就会在指定的这个数据库上进行。

3.创建表格

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    column3 data_type,
 ...
    columnN data_type
);

其中

约束：

table_name 是要创建的表的名称。
column1, column2,... 是表中的列名。
data_type 是列的数据类型，如 INT（整数）,VARCHAR（可变长度字符串）,DATE（日期) 等。
还可以添加各种约束条件:如非空(NOT NULL)，可空(NULL)，自增数字（AUTO_INCREMENT），主键（PRIMARY KEY）（设置主键后此数据必须唯一不可重复，且一定不能为空），外键（Foreign Key)。

TIP：主键 PRIMARY KEY 用空格隔开自增数字 AUTO_INCREMENT 用下划线隔开

案例：

CREATE TABLE your_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    other_column VARCHAR(50)
);

4.插入数据

向指定表中插入所有列的数据：

   INSERT INTO table_name VALUES (value1, value2, value3,...);

向指定表中插入指定列的数据：

   INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...);

TIP：关键字DEFAULT：

定义字段默认值：在创建表时，可以为字段指定一个默认值为“default”，这意味着如果在插入数据时没有明确为该字段提供值，就会使用这个默认值。

在插入语句中：在 INSERT 语句中可以使用“default”来表示使用字段定义的默认值来填充该字段。

以下是两个包含 default 使用的示例：

创建一个表 students ，其中 age 字段设置默认值为 20 ：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT DEFAULT 20
);
INSERT INTO students (name) VALUES ('张三');

创建一个表 test_table ，其中有一个自增主键 id ：

CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO test_table (id, name) VALUES (default, '示例数据');

练习案例：

-- 插入数据
insert into emp(username,name,gender,create_time,update_time)values ('T','Tang',1,now(),now());
-- 为所有字段插入数据
insert into emp values (null,'AD','q123','Antony',1,'1.jpg',2,'2024-6-17',now(),now());
-- 批量插入数据
insert into emp(username,name,gender,create_time,update_time)values ('J','Jun',2,now(),now()),('Z','Zhang',2,now(),now());

已经把id设为主键且自增，所以中间那行，设id为null 会自动填充该有的序号

插入效果:

TIP:获取当前系统时间

用自带的 NOW（）函数

5.更新表格（修改表格类型）

ALTER TABLE 关键字

添加新列：

ALTER TABLE table_name ADD column_name data_type;
//例： ALTER TABLE students ADD age INT;

修改列数据类型：

   ALTER TABLE students MODIFY age BIGINT;

删除列：

   ALTER TABLE students DROP age;

添加主键：

假设原来没有主键，添加一个 id 为主键。

   ALTER TABLE students ADD id INT PRIMARY KEY AUTO_INCREMENT;

添加外键：

假设有另一个表 courses ，要在 students 表中添加一个指向 courses 表 id 的外键。

   ALTER TABLE students ADD course_id INT;
   ALTER TABLE students ADD FOREIGN KEY (course_id) REFERENCES courses(id);

案例练习：

use db03_practice;
show tables ;
desc tb_emp;
show create table tb_emp;
-- DDL修改表结构
-- 添加字段
alter table tb_emp add qq varchar(11) comment 'QQ号';
-- 修改字段类型
alter table tb_emp modify qq varchar(13) comment 'QQ号';
-- 修改字段名称
alter table tb_emp change qq qq_name varchar(13) comment 'QQ号';
-- 删除字段
alter table tb_emp drop qq_name;
-- 重命名表
rename table db03_practice.tb_emp to emp;

6.更新表格数据

UPDATE table_name
SET column1 = value1, column2 = value2,...
WHERE condition;

其中：

table_name 是要更新的表名。
通过指定多个列和对应的值来设置要更新的内容。
WHERE 子句用于指定更新满足特定条件的行，如果不指定 WHERE 子句，则会更新表中的所有行。

例：

假设有一个名为 students 的表，包含 id、name 和 age 。

这个语句会将名字为 '张三' 的学生的年龄更新为 25 。

UPDATE students
SET age = 25
WHERE name = '张三';

案例练习：

-- 更改一个数据
update emp set name='张三',update_time=now() where id=1;
-- 批量更改数据
update emp set entryDate='2024-09-01',update_time=now();

不加where就会更改整张表

会爆一个不安全操作的警告，选择继续执行就好

7.删除表格数据

关键字 DELETE FROM

DELETE FROM table_name WHERE condition;

例：假设有一个 students 表，要删除年龄大于 20 岁的学生记录：

DELETE FROM students WHERE age > 20;

只能一行一行的删除一项数据，不能删除一个字段（要想删除字段，要么修改表格类型，把这个字段的一列全删了，要么更新表格数据，把当前字段设为NULL）

8.删除表格

关键字 DROP TABLE

DROP TABLE 语句用于删除一个或多个表

DROP TABLE table_name;

9.删除数据库

使用该语句时要特别注意，执行后表的结构和数据都会被永久删除，无法恢复，所以要谨慎操作。

关键字 DROP DATABASE

DROP DATABASE database_name;

执行该语句后，指定的数据库及其包含的所有表、数据、索引等都会被永久删除，操作需谨慎。

（删库跑路 ——————嘀嘀嘀）

10.查询数据

基本查询

关键字 SELECT FROM

查看数据库里的全部数据（用*号）

SELECT * FROM students;

查看某一列（加列名）

SELECT column_name FROM table_name;
//例：SELECT name FROM students;

条件查询

创建一个表 students ，包含 id、name、age 等列，要查询年龄大于 20 岁的学生

SELECT * FROM students WHERE age > 20;

WHERE与比较运算符联用

比较运算符

在 SQL 中，常见的比较运算符有：

= （等于）
< （小于）
> （大于）
<= （小于等于）
>= （大于等于）
<> 或 != （不等于）

常见的逻辑运算符有：

AND （并且）
OR （或者）
NOT （非）

TIP：WHERE 要放在 ORDER BY 的前面

范围查询

使用 BETWEEN　　AND 来表示在两个值之间

SELECT * FROM table_name WHERE column_name BETWEEN 10 AND 20;

使用IN 运算符用于指定一个值列表

假设有一个表 students ，有一个列 grade（年级），要查询年级是 1 年级、2 年级、3 年级的学生

SELECT * FROM students WHERE grade IN (1, 2, 3);

模糊查询

使用LIKE 运算符用于进行模糊查询。

它通常与通配符结合使用，常见的通配符有：

% ：表示任意字符序列（包括空字符序列）。
_ ：表示恰好一个任意字符。

例如：

查询以特定字符串开头的记录：WHERE column LIKE 'prefix%' 。
查询包含特定字符串的记录：WHERE column LIKE '%substring%' 。
查询以特定字符串结尾的记录：WHERE column LIKE '%suffix' 。
查询两个字的名字的记录：WHERE name LIKE '__' 。
查询“张“姓名字的记录：WHERE column LIKE '张%' 。

例：

假设存在一个表 employees ，包含 name 列。

查询名字以 Jo 开头的员工：

SELECT * FROM employees WHERE name LIKE 'Jo%';

查询名字中包含 on 的员工：

SELECT * FROM employees WHERE name LIKE '%on%';

聚合函数

聚合函数 是数据库中用于对一组数据进行计算并返回单个结果的函数。

所有聚合函数，不参与计算NULL的值

常见的聚合函数包括：

SUM() ：用于计算某一列的总和。例如，在一个包含销售金额的表中，使用 SUM(sales_amount) 可以计算出总销售额。
```
-- sum
select sum(id) from tb_emp;
```
AVG() ：计算某一列的平均值。比如，在学生成绩表中，通过 AVG(grade) 可以得到平均成绩。
```
-- avg
select avg(id) from tb_emp;
```
COUNT() ：计算行数。可以使用 COUNT(*) 计算表中的所有行数，或者 COUNT(column_name) 计算某一列非空值的行数。 COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数
```
-- count（不对NULL进行统计)  
select count(id) from tb_emp;
select count('a') from tb_emp;  -- 任意一个不为NuLL的常数
-- 推荐用*
select count(*) from tb_emp;
```
MAX() ：返回某一列的最大值。例如，在产品价格表中，MAX(price) 能找出最高价格。
MIN() ：返回某一列的最小值。比如在温度记录表中，MIN(temperature) 可以获取最低温度。
```
-- min
select min(tb_emp.entrydate)from tb_emp;
-- max
select max(tb_emp.entrydate)from tb_emp;
```

分组查询

-- 根据性别分组，统计男性和女性的数量
select gender,count(*) from tb_emp group by gender;

-- 分组后的select 后面跟着两个参数，一个是分组字段，一个是聚合函数

分组后过滤条件：

-- 查询入职时间在 2015-01-01（包含）之前的员工，并对结果根据职位分组，获取员工数量大于等于二的职位
select job,count(*) from tb_emp where entrydate<='2015-01-01' group by job having count(*)>=2;

排序查询

关键字ORDER BY （排序）

SELECT column1, column2,... 
FROM table_name 
ORDER BY column_name [ASC|DESC];

其中：

ASC 表示升序（默认，如果不写就是升序）。
DESC 表示降序。

例：创建一个 students 表，包含 id、name、age 等列

这将按照 age 列升序排列学生信息。

SELECT * FROM students ORDER BY age ASC;

这将按照 name 列降序排列学生信息。

SELECT * FROM students ORDER BY name DESC;

练习案例：

-- 排序查询
-- 升序排序-默认
select * from tb_emp order by entrydate;
-- 降序排序
select * from tb_emp order by entrydate desc ;
-- 多个排序字段，按先后顺序写在order by后面，用逗号分隔
select * from tb_emp order by entrydate,update_time desc ;

分页查询

-- 分页查询
-- 起始索引为0，开始查询员工数据，每页展示五条数据
select * from tb_emp limit 0,5;
-- 查询第1页 员工数据，每页展示五条数据
select * from tb_emp limit 0,5;
-- 查询第2页 员工数据，每页展示五条数据
select * from tb_emp limit 5,5;
-- 查询第3页 员工数据，每页展示五条数据
select * from tb_emp limit 10,5;
-- 总结：
-- 查询第n页 员工数据，每页展示五条数据
select * from tb_emp limit (n-1)*5,5;

控制流程语句

if(表达式，true取值，false 取值)

-- if(表达式，true取值，false 取值)
select if(gender=1,'男性员工','女性员工') 性别,count(*)from tb_emp group by gender;

case 表达式 when 值1 then 结果一 when 值2 then 结果2...else...end

-- case 表达式 when 值1 then 结果一 when 值2 then 结果2...else...end
select
    (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then'教研主任' else '未分配职位' end)职位,
    count(*)
from tb_emp
group by job;

案例总结

案例一：基础语法案例

-- 创建一个示例表 students
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10)
);

-- 插入一些示例数据
INSERT INTO students (name, age, grade) VALUES
    ('Alice', 20, 'A'),
    ('Bob', 22, 'B'),
    ('Charlie', 19, 'A'),
    ('David', 21, 'C');

-- 查询所有学生信息
SELECT * FROM students;

-- 使用 WHERE 子句进行条件查询
SELECT * FROM students WHERE age > 20;

-- 使用 ORDER BY 子句进行排序
SELECT * FROM students ORDER BY age ASC;

-- 使用 DISTINCT 去除重复
SELECT DISTINCT grade FROM students;

-- 使用 IN 操作符
SELECT * FROM students WHERE grade IN ('A', 'B');

-- 使用 BETWEEN 操作符
SELECT * FROM students WHERE age BETWEEN 19 AND 21;

-- 使用 LIKE 进行模糊查询
SELECT * FROM students WHERE name LIKE '%a%';

-- 内连接示例
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_name VARCHAR(50)
);

INSERT INTO courses (student_id, course_name) VALUES
    (1, 'Math'),
    (2, 'Science'),
    (3, 'History');

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.id = c.student_id;

-- 更新数据
UPDATE students SET age = 25 WHERE name = 'Alice';

-- 删除数据
DELETE FROM students WHERE grade = 'C';

案例二：表结构的设计流程案例

根据页面原型和要求文档设计表

分析表的设计每个字段和每个字段的相关约束

看要求，加上创建表的时间和最后一次修改表的时间

SQL代码

create table tb_emp
(
    id          int auto_increment comment 'ID 唯一 主键'
    username    varchar(20)                  not null comment '用户名',
    password    varchar(32) default '123456' not null comment '用户密码，默认”123456"',
    name        varchar(10)                  not null comment '姓名',
    gender      tinyint unsigned             not null comment '性别(1表示男，2表示女）',
    image       varchar(300)                 null comment '用户图像的URL',
    job         tinyint unsigned             null comment '职位(1-班主任，2-讲师，3-学工主管，4-教研主管）',
    entryDate   date                         null comment '入职日期',
    create_time datetime                     not null comment '创建数据时间',
    update_time datetime                     not null comment '最后一次修改数据时间',

    constraint tb_emp_pk_1
        primary key (id)
    constraint tb_emp_pk_2
        unique (username)
)
    comment '员工信息';

案例三：查询的综合操作

select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2000-01-01' and '2015-12-31'
order by tb_emp.update_time desc
limit 0,10;

五.SQL多表设计

多表关系

1.一对多的多表关系

在这种关系中，一张表中的一条记录可以与另一张表中的多条记录相关联。

如下代码

创立员工信息表和部门表

其中员工信息表有部门表的部门ID字段

部门表的ID记录与员工信息表的多条记录相关联

create table emp
(
    id          int auto_increment comment 'ID 唯一 主键'
        primary key,
    username    varchar(20)                  not null comment '用户名',
    password    varchar(32) default '123456' not null comment '用户密码，默认”123456"',
    name        varchar(10)                  not null comment '姓名',
    gender      tinyint unsigned             not null comment '性别(1表示男，2表示女）',
    image       varchar(300)                 null comment '用户图像的URL',
    job         tinyint unsigned             null comment '职位(1-班主任，2-讲师，3-学工主管，4-教研主管）',
    entryDate   date                         null comment '入职日期',
    create_time datetime                     not null comment '创建数据时间',
    update_time datetime                     not null comment '最后一次修改数据时间',
    constraint tb_emp_pk_2
        unique (username)
)
    comment '员工信息';

create table dept(
    id int unsigned primary key auto_increment comment 'ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime                     not null comment '创建数据时间',
    update_time datetime                     not null comment '最后一次修改数据时间'
)comment '部门表';

alter table emp add dept_id int unsigned comment '归属的部门ID';

外键

外键（Foreign Key）是数据库中用于建立表之间关联关系的一种约束。

在 MySQL 中，外键具有以下特点和作用：

特点：
参照关联：它指向另一个表中的主键或唯一键。
数据一致性：确保了相关表之间数据的一致性和完整性。保持数据的逻辑一致性，例如确保在关联表中存在对应的主键值。方便进行关联查询和数据操作，能够更清晰地表达表与表之间的关系。

例如，有一个学生表（students）和一个班级表（classes），可以在学生表中添加一个班级外键，指向班级表的主键，以表示每个学生所属的班级。这样可以避免出现学生所属班级在班级表中不存在的不合理情况。

-- 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50)
);

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE CASCADE
);

在上述示例中，在学生表的 class_id 字段上创建了指向班级表 class_id 的外键约束，并且指定了在删除班级记录时进行级联删除相关学生记录。

ON DELETE CASCADE 是在定义外键关系时使用的一种操作规则。

当在父表中删除一条记录时，如果外键关系设置了 ON DELETE CASCADE，那么与该父表记录相关联的子表中的记录也会自动被删除。

外键的弊端

在现代开发中，有时不建议或避免使用外键，原因：

性能考虑：在高并发和大规模数据操作的情况下，外键的约束检查可能会带来一定的性能开销，特别是在频繁的插入、更新和删除操作时。
例如，当大量数据同时插入，如果外键关联的表数据量很大，外键的验证可能会导致操作延迟。
灵活性和可扩展性：某些业务场景可能会频繁更改数据库结构或数据关系。使用外键可能会限制这种灵活性，因为修改外键关系可能涉及到复杂的操作和潜在的数据一致性问题。
比如，业务需求突然变更，原本关联的表关系不再适用，去除外键比修改外键约束更容易。
分布式系统和数据分片：在分布式数据库环境或数据分片的架构中，外键的跨分片或跨库处理可能会变得复杂和难以管理。
数据库迁移和不同数据库的兼容性：不同的数据库系统对于外键的支持和行为可能有所不同。这可能导致在数据库迁移或使用多种数据库时出现兼容性问题。

2.一对一的多表关系

constraint user_id
        unique (user_id),
    constraint fk_user_id
        foreign key (user_id) references tb_user (id)

3.多对多的多表关系

建表代码：


create table tb_student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');


create table tb_course(
   id int auto_increment primary key comment '主键ID',
   name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');


create table tb_student_course(
   id int auto_increment comment '主键' primary key,
   student_id int not null comment '学生ID',
   course_id  int not null comment '课程ID',
   constraint fk_courseid foreign key (course_id) references tb_course (id),
   constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';

案例总结

案例一：参考页面原型及需求，设计多表结构

菜品/套餐分类表需求

菜品表需求

套餐表需求

创建表代码

菜品/套餐分类表
create table category
(
    id          int unsigned auto_increment comment 'ID'
        primary key,
    name        varchar(20)                  not null comment '分类名称',
    type        tinyint unsigned             null comment '类型：1.菜品分类 2.套餐分类',
    sort        tinyint unsigned             not null comment '排序',
    status      tinyint unsigned default '0' not null comment '状态信息：0.停用 1.启用',
    creat_time  datetime                     not null comment '创建时间',
    update_time datetime                     not null comment '最后一次修改时间'
);

菜品表
create table dish
(
    id          int unsigned auto_increment comment 'ID'
        primary key,
    name        varchar(20)                  not null comment '菜品名称',
    category_id int unsigned                 not null,
    price       decimal(8, 2)                not null comment '价格',
    iamge       varchar(300)                 not null comment '图片',
    description varchar(200)                 null comment '描述信息',
    status      tinyint unsigned default '0' not null comment '状态 0.停售 1.起售',
    create_time datetime                     not null,
    update_time datetime                     not null,
    constraint dish_pk_2
        unique (name)
)
    comment '菜品表';

套餐表
create table setmeal
(
    id          int unsigned auto_increment comment '套餐主键ID'
        primary key,
    name        varchar(20)                  not null comment '套餐名称',
    category_id tinyint unsigned             not null comment '套餐分类id',
    price       decimal(8, 2)                not null comment '套餐价格',
    image       varchar(300)                 not null comment '套餐图片',
    description varchar(200)                 null comment '套餐描述',
    status      tinyint unsigned default '0' not null comment '套餐状态： 0.停售 1.起售',
    create_timr datetime                     not null,
    update_time datetime                     not null,
    constraint setmeal_pk_2
        unique (name)
)
    comment '套餐表';

套餐菜品关系表（中间表）
create table setmeal_dish
(
    id         int unsigned auto_increment
        primary key,
    setmeal_id int unsigned     not null comment '关联套餐的id',
    dish_id    int unsigned     not null comment '关联菜品的id',
    copies     tinyint unsigned not null comment '份数'
)
    comment '套餐菜品关系表'

未设置物理外键，后期用逻辑外键联系各表关系

多表查询

select * from table1,table2;

查询结果是笛卡尔积的形式

关键字 JOIN（合并表格）交集

内连接（INNER JOIN）：返回两个表中匹配的行。
左连接（LEFT JOIN）：返回左表中的所有行以及与右表匹配的行。
右连接（RIGHT JOIN）：返回右表中的所有行以及与左表匹配的行。
全外连接（FULL OUTER JOIN）：返回左表和右表中的所有行。

假设有表 table1 有列 id 和 name，表 table2 有列 id 和 address：

SELECT t1.name, t2.address
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

关键字UNION（合并表格）并集

假设有两个查询 query1 和 query2 ，可以这样使用 UNION

会去除重复的行（除非使用 UNION ALL 则保留所有行，包括重复的）

SELECT * FROM query1
UNION
SELECT * FROM query2;

给表起别名

在 from 表名后直接加上表的别名，起别名后获取字段只能通过别名获取

内连接

-- 隐式内连接
select * from table1,table2 where table1.t_id=table2.id;

-- 显式内连接
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept  on tb_emp.dept_id = tb_dept.id;
-- 给表起别名
select a.name,b.name from tb_emp a inner join tb_dept b on a.dept_id = b.id;

inner可省略

内连接只会查询出两个表都符合的数据

若某一个表逻辑外键数据为NULL 则不会查询出来

外连接

右外连接和左外连接可以互通

-- 左外连接
select a.name,b.name from tb_emp a left join tb_dept b on a.dept_id =b.id;
-- 右外连接
select a.name,b.name from tb_emp a right join tb_dept b on a.dept_id =b.id;

子查询

标量子查询

-- 查询教研部所有员工信息
-- 1.查询教研部的 id
select id from tb_dept where name='教研部';
-- 查到结果为id=2
-- 2.根据教研部的id 查询 dept_id为2的所有员工
select * from tb_emp where dept_id=2;
-- 以上两步可总结为以下子查询代码
select * from tb_emp where dept_id=(select id from tb_dept where name='教研部');

子查询只返回了一个查询到的id值（单个值）

-- 查询***入职之后的员工信息
-- 1.查询***的入职时间
select entrydate from tb_emp where name='方东白';
-- 查到结果为 2012-11-01
-- 2.查询2012-11-01之后入职的员工信息
select * from tb_emp where entrydate>'2012-11-01';
-- 以上两步可总结为以下子查询代码
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白');

子查询只返回了一个查询到的入职时间值（单个值）

列子查询

-- 查询教研部和咨询部的所有员工信息
-- 1.查询两个部门的id
select id from tb_dept where name='教研部'or name='咨询部';
-- 查到结果为 部门id为3和2
-- 2.查询 部门id为3和2的员工信息
select * from tb_emp where dept_id=2 or dept_id=3;
-- 或者 select * from tb_emp where dept_id in (2,3);
-- 以上两步可总结为以下子查询代码
select * from tb_emp where dept_id in (select id from tb_dept where name='教研部'or name='咨询部');

子查询只返回符合条件的id值（只有一列，这一列数据都属于id行）

行子查询

-- 查询与***入职日期和职位都相同的员工信息
-- 1.查询***的入职日期和职位
select entrydate, job from tb_emp where name='韦一笑';
-- 查到结果为 入职日期2007-01-01，职位 2
-- 2.查询 入职日期2007-01-01，职位 2 的所有员工
select * from tb_emp where entrydate='2007-01-01' and job=2;
-- 或者 select * from tb_emp where dept_id in (2,3);
-- 以上两步可总结为以下子查询代码
select * from tb_emp where entrydate=(select entrydate from tb_emp where name='韦一笑') and job=(select job from tb_emp where name='韦一笑');
-- 上面代码要进行两次子查询，效率不高
-- 可以优化如下
select * from tb_emp where (entrydate,job)=(select entrydate, job from tb_emp where name='韦一笑');

子查询返回符合条件的入职日期和职位多个信息，但都属于同一个员工（只有一行，这一行数据有多列）

表子查询

-- 查询入职日期是 2006-01-01之后的员工信息，及其部门名称
-- 1.查询入职日期是 2006-01-01之后的员工信息
select * from tb_emp where entrydate>'2006-01-01';
-- 2.查询这部分 员工信息和其部门名称
-- 把刚才查询到的所有信息当作一张临时表
select a.*,b.name from (select * from tb_emp where entrydate>'2006-01-01') a,tb_dept b where a.dept_id=b.id;

子查询只返回符合条件的所有信息（即符合条件的新表）

案例***

假设菜品表名为 dishe，包含字段 id（菜品编号）、name（菜名）、price（价格）和 category_id（菜品类别编号）；菜品类别表名为 category，包含字段 id（菜品类别编号）和 name（菜品类别名称）。

分组查询出每一类菜品的最贵的菜的菜名和价格

select c.id, c.name, d.name, d.price
from dish d
         join category c on d.category_id = c.id
         join (select dish.category_id, max(dish.price) max_price from dish group by dish.category_id) t
              on t.category_id = c.id and max_price = d.price;

tip：

join可以连续传递，可以存在一行几个join的情况，多表相关联

子查询的时候，子查询语句不可以用括号外的表的别名

想法：一般多表相关联的时候，用到分组查询，基本上用相关联的外键作为分组标准（仅个人看法，因为我一开始想用category.name作为分组依据，但发现我无法得到菜名）

以上想法有瑕疵，on的时候匹配不同的表的字段也可以

六.SQL事务

SQL 事务是数据库操作中的一个重要概念。它是一组要么全部成功执行，要么全部失败的集合。

在 SQL 中，通过使用 start transaction/begin语句开始一个事务，使用 commit 语句提交事务，使更改永久生效；使用 rollback 语句回滚事务，撤销所有未提交的更改。

代码案例：

-- 开启事务
start transaction;
-- 或者 begin;
-- 删除部门
delete from tb_dept where id=1;
-- 删除部门下的员工
delete from tb_emp where dept_id = 1;
-- 提交事务
commit;
-- 回滚事务
rollback ;

事务四大特性

（ACID）

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
一致性（Consistency）：事务执行的结果必须使数据库从一个一致性状态转变到另一个一致性状态。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不能被其他事务干扰。每个事务都感觉不到有其他事务在并发执行。
持久性（Durability）：一旦事务成功提交，其对数据库的更改就会永久保存。

七.数据库优化

索引

索引介绍

引入：当数据库中存了大量数据的时候，用查询，会很消耗时间，这时候可以考虑用索引。

在数据库中，索引是一种用于加速数据检索的数据结构。

在没有建立索引时候，查询数据是全表扫描

建立索引后，会根据索引的目标，构建数据结构，存放需要索引的数据

如下，以二叉搜索树举例展示

索引的主要作用是提高数据库查询操作的效率。它类似于书籍的目录，通过索引可以快速定位到数据所在的位置，而不必扫描整个数据表。

索引的数据结构

索引的类型有多种，常见的包括：

B+ 树索引：这是最常见的索引类型，适用于大多数情况。
哈希索引：适用于精确匹配的查询。

tip：

B+树是一种平衡的多路搜索树，在数据库和文件系统中被广泛应用。

B+树的特点包括：

所有的数据都存储在叶子节点，非叶子节点只存储索引信息。
叶子节点之间通过链表相连，便于范围查询。

与其他数据结构相比，B+树具有以下优势：

支持高效的随机查找：通过从根节点到叶子节点的路径搜索，可以快速定位到目标数据。
适合范围查询：由于叶子节点的链表结构，范围查询可以沿着链表顺序读取，而不需要多次重新查找索引。

例如，在一个存储学生成绩的数据库中，如果按照学号建立 B+树索引。当需要查找学号在 100 到 200 之间的学生成绩时，可以快速定位到学号为 100 的叶子节点，然后沿着链表顺序读取直到学号为 200 的节点。

B+树的高度相对较低，这意味着在查找数据时，磁盘 I/O 操作的次数较少，从而提高了数据访问的效率。

假设一个 B+树的阶数为 4（即每个节点最多有 4 个孩子），根节点有 2 到 4 个孩子，非叶子节点至少有 2 个孩子。如果要存储 1000 条数据，可能只需要 3 层就能完成索引，大大减少了查找的时间。

总之，B+树的结构特点使其在处理大量数据的存储和查询时表现出色，是数据库实现高效索引的重要数据结构之一。

索引的语法

-- 创立 索引
create index idx_sku_sn on dish(name);
-- 查询表的索引信息
show index from dish;
-- 删除索引
drop index idx_sku_sn on dish;

主键索引

唯一约束索引

DAWN_T17

关注

15
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
DBMS-MySQL

Author：Dawn_T17🥥DBMS 是数据库管理系统（DatabaseManagementSystem）的缩写。它是一种软件系统，用于创建、管理和维护数据库。DBMS 提供了一系列功能，包括数据定义、数据存储、数据操作（如查询、插入、更新、删除等）、数据控制（如访问权限管理）、数据备份与恢复等。常见的 DBMS 有 MySQL、Oracle、SQL Server、DB2 等。它在企业信息化、数据处理和管理等方面发挥着重要作用。
复制链接

扫一扫