🔥 前端学MySQL？2024最卷新姿势：用「SQL渣男文学」驯服数据库！不是教你和数据库谈恋爱，但看完保证你删库跑路

Mysql

SQL语句常见规范：

通常关键字是大写的，比如CREATE ，TABLE ，SHOW等

一个语句结束后，需要以；结尾

如果遇到关键字作为表名或字段名 可以使用``包裹

对数据库的操作

查看所有的数据库

SHOW DATABASES

选择某一个数据库

USE XXX

查看当前正在使用的数据库

SELECT DATABASE( );

创建一个数据库

CREATE DATABASE xxx
#我们一般用这种方式创建数据库----防止重复
CREATE DATABASE IF NOT EXISTS xxx
#可以指定编码格式和排序规则  ---但我们一般默认就可以了 
CREATE DATABASE IF NOT EXISTS xxx DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci

修改数据库的编码

ALTER DATABASE xxx CHARACTER SET = utf8 COLLATE = utf8mb4_0900_ai_ci

删除数据库

DROP DATABASE IF EXISTS xxx

对数据表的操作

表约束

主键PRIMARY KEY

一张表中为了区分每一条记录的唯一性，必须有一个字段永远不会重复，并且不为空，则设置为主键

主键是表中唯一的索引
必须为NOT NULL
主键可以是多列索引，称为联合主键
开发中主键字段应该和业务无关的，尽量不要使用业务字段来作为主键

唯一 UNIQUE

某些字段我们希望是唯一不重复，比如手机号，身份证

使用了UNIQUE约束的字段在表中必须是不同的

不能为空 NOT NULL

必须插入值

默认值 DEFAULT

设置默认值

自动递增 AUTO_INCREMENT

不设置值的时候也可以递增比如用户id

#例子
CREATE TABLE  IF NOT EXISTS `users` (
  `id` INT  PRIMARY KEY AUTO_INCREMENT,
  `age` INT NOT NULL,
  `score` INT DEFAULT 60,
  `phone` VARCHAR(20) UNIQUE NOT null,
  `createtime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ，
    `updatetime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )
  #CURRENT_TIMESTAMP是设置默认值为当前时间
  #ON UPDATE CURRENT_TIMESTAMP ---数据更新时，设置数据为当前时间

对表的操作

查看所有的表

SHOW TABLES

创建一个表

CREATE TABLE IF NOT EXISTS `students`(
`name` VARCHAR(10),
`age` INT,
`score` INT
);

查看表的结构

DESC students

查看表的SQL语句

SHOW CREATE TABLE `students`
#结果如下 
CREATE TABLE `students` (
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `score` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

删除一个表

DROP TABLE IF EXISTS ` students`

修改表

#修改表名
ALTER TABLE `users` RENAME TO `user`

#添加一个新的列
ALTER TABLE `user` ADD `updateTime` TIMESTAMP

#修改一个字段
ALTER TABLE `user` CHANGE `phone` `telphone` VARCHAR(20)

#修改字段的类型
ALTER TABLE `user` MODIFY `name` VARCHAR(30)

#删除一个字段
ALTER TABLE `user` DROP `age`

根据表的结构创建另一个表

CREATE TABLE `user1` LIKE `user`
#------------------------------------
#user1的表结构与 user表结构一模一样

根据表的内容创建另一个表

CREATE TABLE `user2` (SELECT * FROM `user`)
#------------------------------------
#复制表的内容，不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用"alter"添加，而且容易搞错

对数据的操作

#插入数据
INSERT INTO `user` VALUES (110,'why','123123131','2020-10-20','2022-10-20')
#部分插入
INSERT INTO `user` (name,telPhone, createTime) VALUES ('why','123123131','2020-10-20')

#删除所有数据
DELETE FROM `user` 
#删除符合条件数据
DELETE FROM `user` where id=1

#更新数据
UPDATE `user` SET `name`='lily', `telPhone`='01231123' WHERE id=4

#查询
SELECT * FROM `user`
#查询指定字段
SELECT age,name FROM `user`
#对字段结果起一个别名
SELECT age as '年龄' FROM `user`

查询条件where

#查询年龄小于18的人
SELECT * FROM `user` WHERE age < 18
#查询年龄等于18的人
SELECT * FROM `user` WHERE age = 18
#查询年龄不等于18的人
SELECT * FROM `user` WHERE age <> 18
SELECT * FROM `user` WHERE age != 18
#查询年龄在18-70之间的人
SELECT * FROM `user` WHERE age > 18 AND age <70
SELECT * FROM `user` WHERE age > 18 && age <70
#BETWEEN--AND是包含等于的，在该例子中包含18和70
SELECT * FROM `user` WHERE age BETWEEN 18 AND 70
#查询年龄在18以上或者性别是男性 
SELECT * FROM `user` WHERE age >18 || sex='man'
#查询年龄为空的 判断为空 不能用 = 
SELECT * FROM `user` WHERE age IS NULL

模糊查询LIKE

% 表示匹配任意个的任意字符

_表示匹配一个的任意字符

#查询名字包含M字符的人
SELECT * FROM `user` WHERE name  LIKE '%M%'
#查询名字第二个字母M的人
SELECT * FROM `user` WHERE name  LIKE '_M%'

取多个值中的其中一个 IN

SELECT * FROM `user` WHERE  name ='爱吃香菜的猹' || name ='tongwu' || name ='小叶子'
#用 IN 语句
SELECT * FROM `user` WHERE  name IN ('爱吃香菜的猹' ,'tongwu' ,'小叶子')

结果排序ORDER BY

#按照年纪升序 ASC 升序 DESC降序
SELECT * FROM `user` WHERE  name IN ('爱吃香菜的猹' ,'tongwu' ,'小叶子') ORDER BY age ASC

分页查询LIMIT OFFSET

OFFSET 代表偏移量

LIMIT 代表查询多少条数据

#查询0~20条数据
SELECT * FROM `user`  LIMIT 20 OFFSET 0
#查询前3行记录
SELECT * FROM `user`  LIMIT 0，3
#第二种写法 
SELECT * FROM `user`  LIMIT 0，20
#查询20~40条数据
SELECT * FROM `user`  LIMIT 20 OFFSET 20
#每页显示m条数据，求查询第n页的数据
SELECT * FROM `user`  LIMIT (n-1)*m,m
#查询user表，每页显示5条记录，求总页数
SELECT COUNT(*) FROM `user`
#然后 总数 除以5
#如果有小数 +1 否则 就是总页数

聚合函数

默认情况下将所有数据当成一组数据

#SUM()
#求所有人的年龄总和
SELECT SUM(age) totalAge FROM `user`
#求所有男人的年龄总和
SELECT SUM(age) totalAge FROM `user` WHERE sex='man'

#AVG()
#求所有人的年龄平均值
SELECT AVG(age)  FROM `user`

#MAX()
#求最高年龄
SELECT MAX(age)  FROM `user`

#MIN()
#求最低年龄
SELECT MIN(age)  FROM `user`

#COUNT()  值为NULL是不会包含在内
#求男人的人数
SELECT COUNT(*)  FROM `user` WHERE sex='man'

#求个数的同时去除年龄相同的
SELECT COUNT(DISTINCT age) FROM `user`

#GROUP BY --分组
#通过性别来进行分组 
#第一组：男人的年龄平均值，成绩平均值和个数 
#第二组：女人的年龄平均值，成绩平均值和个数
SELECT sex ,AVG(age) , COUNT(*) ,AVG(score) FROM `user` GROUP BY sex

#先对数据进行筛选然后按照性别进行分组 求出平均分
#先将年龄>20的筛选出来然后通过性别分组，求出男人组的平均分和女人组的平均分
SELECT sex , ,AVG(score) FROM `user` WHERE age>20 GROUP BY sex

#HAVING 对分组后的结果进行筛选,
#注意：使用where是错误的
#对分组后的结果查询平均年龄大于200的
SELECT sex ,AVG(age) avgAge, COUNT(*)  FROM `user` GROUP BY sex HAVING  avgAge >200

注意:

在GROUP BY之前使用where 是正确语法

而在之后是错误的，必须使用HAVING

# 2.给brand_id设置引用brand中的id的外键约束
# 添加一个brand_id字段
ALTER TABLE `products` ADD `brand_id` INT;
# 修改brand_id为外键
ALTER TABLE `products` ADD FOREIGN KEY(brand_id) REFERENCES brand(id);
# 设置brand_id的值
UPDATE `products` SET `brand_id` = 1 WHERE `brand` = '华为';
UPDATE `products` SET `brand_id` = 2 WHERE `brand` = '苹果';
UPDATE `products` SET `brand_id` = 3 WHERE `brand` = '小米';
UPDATE `products` SET `brand_id` = 4 WHERE `brand` = 'oppo';

我们来思考一个问题：如果products中引用的外键被更新了或者删除了，这个时候会出现什么情况呢

比如将华为的id更新为100
UPDATE `brand` SET `id` = 100 WHERE `id` = 1;  
会报错！！！

如果我希望可以更新呢？我们需要修改on delete或者on update的值；

我们可以给更新或者删除时设置几个值：

RESTRICT（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除；

NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的；

CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：

更新：那么会更新对应的记录；

删除：那么关联的记录会被一起删除掉；

SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为 NULL；如果修改外键的更新时的动作呢

# 4.修改brand_id关联外键时的action
# 4.1.获取到目前的外键的名称
SHOW CREATE TABLE `products`;
#结果
CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand` varchar(20) DEFAULT NULL,
  `title` varchar(100) NOT NULL,
  `price` double NOT NULL,
  `score` decimal(2,1) DEFAULT NULL,
  `voteCnt` int DEFAULT NULL,
  `url` varchar(100) DEFAULT NULL,
  `pid` int DEFAULT NULL,
  `brand_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id` (`brand_id`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8mb3
# products_ibfk_1就是外键名称

# 4.2.根据名称将外键删除掉
ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;

# 4.2.重新添加外键约束
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id)						ON UPDATE CASCADE
ON DELETE RESTRICT;

#此时执行该语句就不会报错了
UPDATE `brand` SET `id` = 100 WHERE `id` = 1;

多表查询

现在我们开始多表查询！！！！！重点！！！

如果我们希望查询到产品的同时，显示对应的品牌相关的信息，因为数据是存放在两张表中，所以这个时候就需要进行多表查询。

如果我们直接通过查询语句希望在多张表中查询到数据，这个时候是什么效果呢

#笛卡尔乘积
`SELECT * FROM ` products`, `brand`

我们会发现一共有648条数据，这个数据量是如何得到的呢？

第一张表的108条 * 第二张表的6条数据；

也就是说第一张表中每一个条数据，都会和第二张表中的每一条数据结合一次；

这个结果我们称之为笛卡尔乘积，也称之为直积，表示为 X*Y；

但是事实上很多的数据是没有意义的，比如华为和苹果、小米的品牌结合起来的数据就是没有意义的，我们可不可以进行筛选呢？

使用where来进行筛选；这个表示查询到笛卡尔乘积后的结果中，符合products.brand_id = brand.id条件的数据过滤出来；

#对笛卡尔积进行筛选
SELECT * FROM `products`, `brand` WHERE `products`.brand_id = `brand`.id

多表连接来啦！！！！！！！

左连接

如果我们希望获取到的是左边所有的数据（以左表为主）：

这个时候就表示无论左边的表是否有对应的brand_id的值对应右边表的id，左边的数据都会被查询出来；

这个也是开发中使用最多的情况，它的完整写法是LEFT [OUTER] JOIN，但是OUTER可以省略的

# 2.左连接
# 2.1. 查询所有的手机（包括没有品牌信息的手机）以及对应的品牌 null
SELECT * FROM `products` LEFT  JOIN `brand` ON products.brand_id = brand.id;

# 2.2. 查询没有对应品牌数据的手机
SELECT * FROM `products` LEFT JOIN `brand` ON products.brand_id = brand.id WHERE brand.id IS NULL;
SELECT * FROM `products` LEFT JOIN `brand` ON products.brand_id = brand.id WHERE brand_id IS NULL;

右连接

如果我们希望获取到的是右边所有的数据（以由表为主）：

这个时候就表示无论左边的表中的brand_id是否有和右边表中的id对应，右边的数据都会被查询出来；

右连接在开发中没有左连接常用，它的完整写法是RIGHT [OUTER] JOIN，但是OUTER可以省略的；

# 3.右连接
# 3.1. 查询所有的品牌（没有对应的手机数据，品牌也显示）以及对应的手机数据；
SELECT * FROM `products` RIGHT OUTER JOIN `brand` ON products.brand_id = brand.id;

# 3.2. 查询没有对应手机的品牌信息
SELECT * FROM `products` RIGHT JOIN `brand` ON products.brand_id = brand.id WHERE products.brand_id IS NULL;

内连接

事实上内连接是表示左边的表和右边的表都有对应的数据关联：

内连接在开发中偶尔也会常见使用，看自己的场景。

内连接有其他的写法：CROSS JOIN或者 JOIN都可以；

# 4.内连接
SELECT * FROM `products` JOIN `brand` ON products.brand_id = brand.id;

我们会发现它和之前的下面写法是一样的效果

SELECT * FROM `products`, `brand` WHERE products.brand_id = brand.id;

但是他们代表的含义并不相同：

SQL语句一：内连接，代表的是在两张表连接时就会约束数据之间的关系，来决定之后查询的结果；

SQL语句二：where条件，代表的是先计算出笛卡尔乘积，在笛卡尔乘积的数据基础之上进行where条件的筛选

全连接

SQL规范中全连接是使用FULL JOIN，但是MySQL中并没有对它的支持，我们需要使用 UNION 来实现

#全连接
(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id);
#----------------------------------------------------------------------------------------
(SELECT * FROM `products` LEFT JOIN `brand` ON `products`.brand_id = `brand`.id WHERE `brand`.id IS NULL)
UNION
(SELECT * FROM `products` RIGHT JOIN `brand` ON `products`.brand_id = `brand`.id WHERE `products`.id IS NULL);

多对多的关系

先做初始数据创建

#基本数据的模拟
CREATE TABLE IF NOT EXISTS students(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	age INT
);

CREATE TABLE IF NOT EXISTS courses(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	price DOUBLE
);

INSERT INTO `students` (name, age) VALUES('why', 18);
INSERT INTO `students` (name, age) VALUES('tom', 22);
INSERT INTO `students` (name, age) VALUES('lilei', 25);
INSERT INTO `students` (name, age) VALUES('lucy', 16);
INSERT INTO `students` (name, age) VALUES('lily', 20);

INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
INSERT INTO `courses` (name, price) VALUES ('物理', 888);
INSERT INTO `courses` (name, price) VALUES ('地理', 333);

# 2.建立关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE,
    PRIMARY KEY (student_id,course_id)
);
#  PRIMARY KEY (student_id,course_id) 是设置联合主键
#可以防止重复，因为当我们在插入数据时候，一个学生只能插入同一个课程一次

# 3.学生选课
# why选择了英文、数学、历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 4);


INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);


INSERT INTO `students_select_courses` (student_id, course_id) VALUES (5, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (5, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (5, 4);

开始！

# 4.查询的需求
# 4.1. 查询所有有选课的学生，选择了哪些课程
SELECT stu.id id, stu.name stuName, stu.age stuAge, cs.id csId, cs.name csName, cs.price csPrice
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id;


# 4.2. 查询所有的学生的选课情况
SELECT stu.id id, stu.name stuName, stu.age stuAge, cs.id csId, cs.name csName, cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id;

# 4.3. 哪些学生是没有选课
SELECT stu.id id, stu.name stuName, stu.age stuAge, cs.id csId, cs.name csName, cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id
WHERE cs.id IS NULL;

# 4.4. 查询哪些课程是没有被选择的
SELECT stu.id id, stu.name stuName, stu.age stuAge, cs.id csId, cs.name csName, cs.price csPrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs ON ssc.course_id = cs.id
WHERE stu.id IS NULL;

# 4.5. 某一个学生选了哪些课程（why）
SELECT stu.id id, stu.name stuName, stu.age stuAge, cs.id csId, cs.name csName, cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
LEFT JOIN `courses` cs ON ssc.course_id = cs.id
WHERE stu.id = 2;

好了，那么我们平常做开发时候，后台返回都是JSON数据，我们该如何做到呢

mysql为我们提供了这一方法

# 将联合查询到的数据转成对象（一对多）
SELECT 
	products.id id, products.title title, products.price price,
	JSON_OBJECT('id', brand.id, 'name', brand.name, 'website', brand.website) brand
FROM `products`
LEFT JOIN `brand` ON products.brand_id = brand.id;

# 将查询到的多条数据，组织成对象，放入到一个数组中(多对多)
SELECT 
	stu.id, stu.name, stu.age,
	JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.name, 'price', cs.price)) selectCoure
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id
GROUP BY stu.id;

Mysql自带函数

`concat`

SELECT CONCAT('我是',2,3)  //拼接字符串

`length`

SELECT LENGTH('abc') //返回长度 3
SELECT LENGTH('童武') //返回长度6 一个汉字3

`left` `right`

SELECT LEFT('aurora',4) //从左边截取 auro
SELECT RIGHT('aurora',4) //从右边截取 rora

`substring`

SELECT SUBSTRING('aurora',1,2)  截取 au
SELECT SUBSTRING('aurora',1,3) aur

`ltrim rtrim trim 去除空格`

`round 四舍五入`

SELECT ROUND(132.34) 132

`rand随机数`

SELECT RAND()

`current_date`

SELECT CURRENT_DATE //返回当前日期 2023-07-23

`current_time`

SELECT CURRENT_TIME //返回当前时间 13：40：32

`now`

SELECT NOW() //返回当前日期加时间  2023-07-23 13：40：58

存储过程

一条或者多条SQL语句的集合

语法

create procedure 存储过程名称(参数列表)
begin 
sql语句
end

#创建存储过程
CREATE PROCEDURE stu()
BEGIN
SELECT * FROM `user`；
END

#调用存储过程
CALL stu()

#删除存储过程
DROP PROCEDURE stu

视图

对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要改变sql，则需要在多个地方进行修改，维护起来麻烦

视图就是对查询的封装

视图可以理解为一张可读的表

语法

create view 视图名称 as select sql语句

#创建视图
CREATE VIEW stu as 
SELECT * FROM users WHERE sex='male'

#使用视图
SELECT * FROM stu  //类似于将查询的结果变成表

#删除存储过程
DROP VIEW IF EXISTS stu

事务

比如A用户给B用户转账100操作，过程如下：

从A账户扣100

给B账户加100

如果在事务的支持下，上面最终只有2种结果：

操作成功：A账户减少100；B账户增加100

操作失败：A、B两个账户都没有发生变化

如果没有事务的支持，可能出现错：A账户减少了100，此时系统挂了，导致B账户没有加上100，而A账户凭空少了100。

1.用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务执行UPDATE DELETE,变更会写到缓存中，不会立刻生效
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

BEGIN;
DELETE FROM users where id='1';
DELETE FROM label where id ='te';
ROLLBACK；
COMMIT；

如果开启一个事务，没有rollback，commit，默认会执行rollback

索引

当表中数据量很大，查找数据会变得很慢

索引就类似于目录

语法

create index 索引名称 on 表名(字段名称(长度))

字段类型为字符型，需要指定长度
类型如果不是字符串，可以不指定

CREATE INDEX age_index on users(age) //为users表中age字段创索引
#where条件后面的字段，数据库会自动查找是否存在索引
#这里会自动调用age_index
SELECT * FROM users where age=13

#查看索引
SHOW INDEX FROM users

#删除索引
DROP INDEX age_index ON users

主键会自动添加索引

调高select查询速度
降低update,delete insert的执行速度
项目中大部分使用select,索引index是必须的
如果涉及到大量数据修改操作，修改之前可以把索引删除，修改完成后，再把索引建立起来

🔥 前端学MySQL？2024最卷新姿势：用「SQL渣男文学」驯服数据库！