MySQL

数据库相关概念

关系型数据库

SQL

通用语法

分类

SQL

查询
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
创建
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULTCHARSET 字符集] [COLLATE 排列顺序]
删除
DROP DATABASE[IF EXISTS]数据库名
使用
USE 数据库名

DDL-表操作-查询

查询当前数据库所有表
SHOW TABLES
查询表结构
DESC表名
查询指定表的键表语
SHOW CREATE TABLE 表名

DML

用来对数据库中表的数据记录进行增删改操作
添加数据(INSERT)

1.给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,…)VALUES(值1,值2,…);
2.给全部字段添加数据
INSERT INTO 表名VALUES(值1,值2,…)
3.批量添加数据
INSERT 表名(字段名1,字段名2,…)VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);
INSERT 表名VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);

注意:

• 插入数据时,指定的字段顺序需要与值的顺序一一对应
• 字符串和日期数据应包含在引号中
• 插入的数据大小,应该在字段的规定范围内

修改数据(UPDATE)

UPDATE 表名 SET 字段名1=值1, 字段名2=值2,…[WHERE 条件]

删除数据(DELETE)
DELETE FROM 表名 [WHERE 条件]

DQL

用来查询数据库中表的记录
SELECT:字段列表
1.查询多个字段
SELECT 字段1,字段2,字段3…FROM 表名;
SELECT*FROM 表名;
2.设置别名
SELECT 字段1[AS 别名1],字段2[AS别名2]…FROM 表名
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

FROM:表名列表
WHERE:条件列表
GROUP BY:分组后字段列表
HAVING:分组后条件列表
ORDER BY:排序字段列表
LIMIT:分页参数

条件查询

聚合函数

分组查询

执行顺序:where > 聚合函数 >having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义

排序查询

分页查询

DCL

用来管理数据库 用户,控制数据库的访问 权限

管理用户

权限控制

函数

是指一段可以直接可以被另一端程序调用的程序或者代码

数值函数

日期函数

流程函数

小练习
需求：
统计班级各个学员的成绩，展示的规则如下：
‘>=85’，展示优秀
‘>=60’，展示及格
否则，展示不及格

select 
	id ,
	name ,
	(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end ) '数学' ,
	(case when enlish >= 85 then '优秀' when enlish >= 60 then '及格' else '不及格' end ) '英语' ,
	(case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end ) '语文' 
from loveemployee;

约束

1、概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2、目的：保证数据库中数据的正确、有效性和完整性。
3、分类
约束：描述：关键字
非空约束：限制该字段的数据不能为null：NOT NULL
唯一约束：保证该字段的所有数据都是唯一的、不重复的：UNIQUE
主键约束：主键是一行数据的唯一标识，要求非空且唯一：PEIMARY KEY
默认约束：保存数据时，如果未指定该字段的值，则采用默认值：DAFAULT
检查约束（8.0.16版本之后）：保证字段值满足某一个条件：CHECK
外键约束：用来让两张表的数据之间建立连接，保证数据的一致性和完整性：FOREGIN KEY

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
约束的演示:

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 and age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

insert into user(name, age, status, gender) values ('Tom',19,'1','雄'),('Eric',25,'0','雌');

外键约束
什么是外键约束：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

语法：
添加外键

CREATE TABLE 表名(
	字段名 数据类型
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (关键字段名) REFERENCES 主表(主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

删除外键

ALTER ATBLE 表名 DROP FOREIGN KEY 外键名称;

外键删除更新行为

//当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致）
NO ACTION
//当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致）
RESTRICT
//当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
CASCADE
//当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
SET NULL
//父表有变更时，子表将外键列设置为一个默认的值（Innodb不支持）
SET DEFAULT

多表查询

多表关系
在项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种
一对多（多对一）
多对多
一对一
一对多（多对一）
案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方主键

多对多
案例：学生与课程的关系
关系：一个学生可以选秀多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

create table student (
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
)comment '学生表';

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
)comment '课程表';
create table student_course(
    id int auto_increment comment '主键' primary key ,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student (id)
)comment '学生课程中间表';

一对一
案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

多表查询概述
概述：指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

select * from emp , dept where emp.dept_id = dept.id ;

多表查询的分类
连接查询
内连接：相当于查询A、B交集部分数据
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
子连接：当前表与自身的连接查询，自连接必须使用表别名
子查询
内连接
内连接查询的是两张表交集的部分
外连接
外连接查询语法：
左外连接：

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据
右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

自连接
自连接查询语法

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

联合查询
联合查询-union，union all
对于union查询，就是把多次查询的结构合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

子查询
概述
概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

根据子查询结果不同，分为
标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

根据子查询位置，分为：WHERE之后，FROM之后，SELECT之后。
标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为表量子查询。
常用的操作符：= <> > >= < <=
列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询
常用的操作符：
IN：在指定的集合范围之内，多选一
NOT IN：不在指定的集合范围之内
ANY：子查询返回列表中，有任意一个满足即可
SOME：与ANY等同，使用SOME的地方都可以使用ANY
ALL：子查询返回列表的所有值都必须满足
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
常用的操作符：=、<>、IN、NOT IN
表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询.
常用操作符:IN