基础篇

数据库相关

增

CREATE DATABASE test

删

DROP DATABASE test

查

SHOW DATABASES

使用某个数据库

USE test

表相关操作

单表增删改查

增

CREATE TABLE USER (
 id INT 
)

删

DROP TABLE USER

改

将user表名改成user01

ALTER TABLE USER RENAME TO user01

查

SELECT * FROM USER

数据相关

增

# 添加指定字段
INSERT INTO USER (userName,userPassword,sex,userEmail) VALUES ('张三','123456','男','111xxx@qq.com')

# 不指定字段则默认添加全部字段。
# 注意：这里需要显性的指定ID，尽管它是自增的。
INSERT INTO USER VALUES (48, '张三','123456','男','111xxx@qq.com')

# 批量增加
# 指定字段方式
INSERT INTO 
    USER 
        (userName, userPassword, sex, userEmail) 
    VALUES 
        ('孙悟空','123456','男','111xxx@qq.com'), 
        ('猪八戒','123456','男','111xxx@qq.com'), 
        ('唐僧','123456','男','111xxx@qq.com')
# 全部字段方式
INSERT INTO 
    USER 
    VALUES 
        (57, '孙悟空','123456','男','111xxx@qq.com'), 
        (58, '猪八戒','123456','男','111xxx@qq.com'), 
        (59,'唐僧','123456','男','111xxx@qq.com')

删

这里是根据userId删除对应记录。

DELETE FROM USER WHERE userId = 47

改

# 修改一个字段
UPDATE USER SET userName = '李四' WHERE userId = 48
# 修改多个字段
UPDATE USER SET userName = '李四', userPassword = '66666' WHERE userId = 48

查

SELECT * FROM USER

DQL (数据查询语言)

语法

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

基础查询

# 查询user表中全部字段数据
SELECT * FROM USER

# 查询user表中指定字段数据
SELECT userName, sex FROM USER

# 设置别名
# 语法：as (也可省略)
SELECT userName AS uName FROM USER
SELECT userName uName FROM USER

# 去重（针对某个字段）
SELECT DISTINCT userName FROM USER

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围内（含最小、最大值）
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且(多个条件同时成立)
OR 或 \|\|	或者(多个条件任意一个成立)
NOT 或 !	非，不是

聚合查询(聚合函数)

SELECT 聚合函数(字段列表) FROM 表名

常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

# 查看有多少条记录
SELECT COUNT(*) FROM building;
# 查看小区二下面有多少楼幢
SELECT COUNT(id) FROM building WHERE community_id = 2;
# 查询多大的id
SELECT MAX(id) FROM building;
# 查询多小的id
SELECT MIN(id) FROM building;
# 计算id平均数
SELECT AVG(id) FROM building;
# 计算id总合
SELECT SUM(id) FROM building;

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];

# 根据性别分组，统计男女数量(只显示分组数量)
SELECT COUNT(*) FROM person GROUP BY sex;
# 根据性别分组，统计男女数量(显示性别字段)，个人感觉这种看数据更直观
SELECT sex, COUNT(*) FROM person GROUP BY sex;
# 根据性别分组，统计男性和女性的平均年龄
SELECT sex, COUNT(*), AVG(age) FROM person GROUP BY sex;
# 年龄小于30，并根据工作地址分组
SELECT address, COUNT(*) FROM person WHERE age < 30 GROUP BY address;
# 年龄小于30，并根据工作地址分组，获取记录数大于1的工作地址
SELECT address, COUNT(*) FROM person WHERE age < 30 GROUP BY address HAVING COUNT(*) > 1;

where 和 having 的区别：

执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项：

执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

升序：ASC
降序：DESC

# 年龄升序，不指定默认升序
SELECT * FROM person ORDER BY age;
SELECT * FROM person ORDER BY age ASC;  # 和第一条语句同等
# 年龄升序，入职时间降序
SELECT * FROM person ORDER BY age ASC, entryTime DESC;

注意事项：

当多字段排序时，当第一个字段相同时，才会根据第二个字段排序。

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

# 查询第一页数据，显示十条
SELECT * FROM building LIMIT 0,10;
SELECT * FROM building LIMIT 10; # 查询第一页 起始索引可以忽略

# 查询第二页
SELECT * FROM building LIMIT 10,10;

注意事项：

起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数

分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT

如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

DCL执行顺序

FROM ---> WHERE --> GROUP BY ---> SELECT ---> ORDER BY ---> LIMIT

函数

字符串函数

常用函数

函数	功能
CONCAT(s1, s2, …, sn)	字符串拼接
LOWER(str)	将字符串全部转为小写
UPPER(str)	将字符串全部转为大写
LPAD(str, n, str2)	左填充，用字符串str2对str的左边进行填充，达到n个字符串长度
RPAD(str, n, str2)	右填充，用字符串str2对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)	返回从字符串str从start位置起的len个长度的字符串
REPLACE(column, source, replace)	替换字符串

# 字符串拼接
SELECT CONCAT('我是', '帅哥');
# 字符串转小写
SELECT LOWER('MYSQL  YYDS');
# 字符串转大写
SELECT UPPER('mysql  yyds');
# 左填充  第一个参数：字符串； 第二个参数：字符串长度； 第三个参数：填充的字符
SELECT LPAD('a', 5, 'b'); # 'bbbba'
# 右填充  则是从右边填充。
SELECT RPAD('a', 5, 'b'); # 'abbbb'
# 去除字符串头部尾部空格
SELECT TRIM('    	aaa bc d   		'); # 'aaa bc d'
# 返回mysql good字符串 1-5位置的  即为：mysql
SELECT SUBSTRING('mysql good', 1, 5);
# 替换字符串  第一个参数：字符串； 第二个参数：被替换的字符串； 第三个参数：新的字符串
SELECT REPLACE('mysql', 'm','b');

数值函数

常用函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x, y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x, y)	求参数x的四舍五入值，保留y位小数

SELECT CEIL(1.2);     # 2
SELECT FLOOR(1.2);    # 1
SELECT MOD(10, 3);    # 1
SELECT RAND();        # 0-1的随机数
SELECT ROUND(1.2, 2); # 1.20

日期函数

常用函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)	返回起始时间date1和结束时间date2之间的天数

SELECT CURDATE();          # 2023-07-24
SELECT CURTIME();          # 20:50:18
SELECT NOW();              # 2023-07-24 20:50:33
SELECT YEAR(NOW());        # 2023
SELECT MONTH(NOW());       # 7
SELECT DAY(NOW());         # 24
SELECT DATE_ADD(NOW(), INTERVAL 50 YEAR); # 2073-07-24 20:53:27
SELECT DATEDIFF('2023-07-24', '2023-07-20'); # 4

流程函数

常用函数

函数	功能
IF(value, t1, t2)	如果value为true，则返回t1，否则返回t2
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END	如果expr的值等于val1，返回res1，… 否则返回default默认值

# 年龄大于25岁是社会人，反之青少年
SELECT NAME,IF(age > 25, '社会人', '青少年') AS '年龄' FROM person;
# 判断age是否为null，为null则赋值一个0
SELECT NAME, IFNULL(age, 0) FROM person;
# 年龄大于60为老年，大于40是中年，其他为壮年
SELECT 
    (CASE WHEN age > 60 THEN '老年' WHEN age > 40 THEN '中年' ELSE '壮年' END) 
AS 
    '年龄阶段' 
FROM 
    person;
    
#北京、上海为一线城市，其余是二线城市
SELECT 
    (CASE address WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) 
AS 
    '工作地址' 
FROM 
    person;

约束

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.1版本后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张图的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY
自动增长	一般用在id上，id自增	AUTO_INCREMENT

CREATE TABLE USER(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10) NOT NULL UNIQUE,
  age INT CHECK(age > 0 AND age < 120),
  STATUS CHAR(1) DEFAULT '1',
  gender CHAR(1)
)

外键约束

外键用于与另一张表的关联。是能确定另一张表记录的字段，数据库强制你保持数据的一致性。

# 语法
# 创建表时添加外键
CREATE TABLE 表名(
    字段名 字段类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
# 修改表时添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

接下来理解一下外键的用法吧。

# 部门表
CREATE TABLE department(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(22) NOT NULL
)
# 员工表
CREATE TABLE staff(
	id INT(11) PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(22) NOT NULL,
	department_id INT(11),
	CONSTRAINT fk_staff_dep FOREIGN KEY(department_id)  REFERENCES department(id)
)

# 现在是表中是没有任何数据的
# 此时，我在员工表中插入一条数据
# 下面插入sql会报错，外键约束失败。愿意：因为部门表中没有id为1的记录
INSERT INTO staff (NAME, department_id) VALUES ('张三', 1) 

# 此时我们在部门表插入一条id为1的数据
INSERT INTO department (NAME) VALUES ('开发部');
INSERT INTO staff (NAME, department_id) VALUES ('张三', 1);  # 成功

通过以上例子，外键其实就是约束关系，当员工所在的部门在部门表不存在时，操作则失败！

当主表不存在该条数据，子表使用时会报错。

当子表有数据依赖父表时，则父表该条数据不能删除。

当子表有数据依赖父表时，可以删除外键。

外键所在的表是子表，与之对应关联的则是父表。

外键关联的主表字段一般是主键。（所以父表必须要定义主键）

外键中列的数据类型必须和父表主键中对应列的数据类型相同

主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。（外键中可以出现NULL，我的理解是，假设员工今天刚入职，但是还未给他分配部门，此时就是NULL）

多表查询

多表关系

一对一

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

一对多(多对一)

案例：部门和员工。
关系：一个部门可以有多个员工，一个员工只能有一个部门
实现：在多的一方建立外键，指向一的一方的主键

多对多

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

查询

合并查询

会展示所有组合结果

也叫笛卡尔积
笛卡尔积：两个集合A集合和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

# 合并查询（笛卡尔积，会展示所有组合结果）
SELECT * FROM department, staff;
# 消除无效笛卡尔积
SELECT * FROM department, staff WHERE department.id = staff.`department_id`;

内连接查询

内连接查询的是两张表交集的部分

显式性能比隐式高
查询员工名称以及所属部门名称

# 隐式内连接
SELECT 
    s.`name`, dept.`name` 
FROM 
    department AS dept, staff AS s 
WHERE 
    dept.`id` = s.`department_id`;
    
# 显式内连接
SELECT 
    dept.`name`, s.`name` 
FROM 
    department AS dept 
INNER JOIN 
    staff AS s 
ON 
    dept.`id` = s.`department_id`;

外连接

左外连接：返回左表所有记录，右表只返回符合条件的记录。右表记录不足的地方为NULL
右外连接：返回右表所有记录，左表只返回符合条件的记录。左表记录不足的地方为NULL

# 左外连接，
# 查出部门所有信息，以及部门下的员工
SELECT 
    department.*,staff.name 
FROM 
    department 
LEFT OUTER JOIN 
    staff 
ON 
    department.`id` = staff.`department_id`;



# 右外连接，
# 查出部门所有信息，以及部门下的员工
SELECT 
    department.*,staff.name 
FROM 
    staff 
RIGHT OUTER JOIN 
    department 
ON 
    department.`id` = staff.`department_id`;

如上我写的两个sql查出来的结果是一样的。

自连接查询

其实就是一张表，左表右表都是自己，然后通过表别名的方式模仿多表。

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

自连接查询，可以是内连接查询，也可以是外连接查询

比如：一个网站想要做产品的层级分类，数码产品分类下面有笔记本，笔记本下面又有很多品牌。有点像树级结构，那这怎么在数据库展示呢？创建一个表，给一个id字段，然后给一个分类名称字段，再来一个存储上级分类id的字段。最后通过自连接查询即可。
表数据

# 查询所有分类以及分类的父类(没有父类的不会显示)
SELECT 
	p.`id`,p.`cate_name`,s.`cate_name` 
FROM 
	tdb_cates AS s 
JOIN 
	tdb_cates AS p 
ON 
	s.`id` = p.`parent_id`;

# 查询所有分类以及分类的父类(没有父类也会显示，为NULL)
# 通过左外连接实现
SELECT 
	p.`id`,p.`cate_name`,s.`cate_name` 
FROM 
	tdb_cates AS s 
JOIN 
	tdb_cates AS p 
ON 
	s.`id` = p.`parent_id`;
        
        
# 通过右外连接实现
SELECT 
	s.`id`,s.`cate_name`,p.`cate_name`
FROM 
	tdb_cates AS p 
RIGHT JOIN 
	tdb_cates AS s 
ON
	p.`id` = s.`parent_id`;

# 查询所有分类以及分类的子类（没用子类则显示NULL）
# 思路：通过表别名使得这一张表分为两个表，利用左外连接的特性（左表查出所有数据，右表查出符合数据）。
# 可以把左表看为父表，右边看为子表。左表就是所有分类，也就是s需要展示id和cate_name,右边展示子类名称(cate_name)即可。
# 判断条件就是父表的id=子表的parent_id。
SELECT 
	s.`id`,s.`cate_name`,p.`cate_name`
FROM
	tdb_cates s
LEFT JOIN
	tdb_cates p
ON
	s.`id` = p.`parent_id`;

联合查询

把多次查询的结果合并，形成一个新的查询集

两个表列的数量必须要一样多。不然会报错。

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

UNION：去重
UNION ALL：不去重
联合查询比使用or效率高，不会使索引失效

不同表联合查询

# 学生年龄大于30岁的和老师姓名合并
SELECT student.`name` FROM student WHERE age > 30
UNION
SELECT teachers.`name` FROM teachers

单张表联合查询

查询student表，id 2-5 和 id 1-3的联合起来

SELECT * FROM student WHERE id BETWEEN 2 AND 5
UNION 
SELECT * FROM student WHERE id BETWEEN 1 AND 3

可以看到只有id为2、3、4、5、1的数据，因为UNION去重。

我们看看加上ALL 会是什么样呢

SELECT * FROM student WHERE id BETWEEN 2 AND 5
UNION ALL
SELECT * FROM student WHERE id BETWEEN 1 AND 3

可以看到当我加上了ALL，没有去重。

语句执行顺序

SELECT * FROM student WHERE id BETWEEN 2 AND 5
UNION ALL
SELECT * FROM student WHERE id BETWEEN 1 AND 3
ORDER BY id

ORDER BY id类似子句会等UNION将结果合并之后再执行。

MySQL数据类型和存储引擎

1.数据类型

数值类型

tinyint、smallint、mediumint、int、bigint，浮点型类型包括float 和 double，定点数类型为 decimal

日期/时间类型

year、time、date、datetime 和 timestamp

字符串类型

char、varchar、binary、varbinary、blob、text、enum 和 set等

二进制类型

bit、binary、varbinary、tinyblob、blob、mediumblob 和 longblob

TODO：后期补充存储大小！

2.存储引擎

后期再看这个吧，目前略过

自学MySQL

基础篇

数据库相关

增

删

查

使用某个数据库

表相关操作

单表增删改查

增

删

改

查

数据相关

增

删

改

查

DQL (数据查询语言)

语法

基础查询

条件查询

聚合查询(聚合函数)

分组查询

排序查询

分页查询

DCL执行顺序

函数

字符串函数

数值函数

日期函数

流程函数

约束

外键约束

多表查询

多表关系

一对一

一对多(多对一)

多对多

查询

合并查询

内连接查询

外连接

自连接查询

联合查询

MySQL数据类型和存储引擎

1.数据类型

数值类型

日期/时间类型

字符串类型

二进制类型

2.存储引擎