神光《Nest 通关秘籍》学习总结-前端快速入门MySql语法（真的很友好）-六虎

最近在学习神光大神的《Nest通关秘籍》，该小册首要包含下面这些内容：

想购买的能够点击《传送门》。

接下来的日子里，我将更新一系列的学习笔记。感兴趣的能够重视我的专栏《Nest 通关秘籍》学习总结。

特别申明：本系列文章已经经过作者本人的答应。大家也不要想着白嫖，我的笔记仅仅个人边学习边记载的，不是很完好，大家想要深化学习仍是要自己去购买原版小册。

本章咱们来学习一下MySql的一些语法常识和常见的查询操作。

1. 数据定义句子(DDL)

1.1. 登录数据库

mysql -u root -p

1.2. 创立数据库

create database test;

1.3. 检查一切数据库

show databasese;

1.4. 选择数据库并使用

use test;

1.5. 检查一切数据表

show tables;

1.6. 删去数据库

drop database test;

2. 表操作

2.1. 创立表

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
    name VARCHAR(50) NOT NULL COMMENT '学生名',
    gender VARCHAR(10) NOT NULL COMMENT '性别',
    age INT NOT NULL COMMENT '年龄',
    class VARCHAR(50) NOT NULL COMMENT '班级名',
    score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4

2.2. 刺进数据

INSERT INTO student (name, gender, age, class, score)
    VALUES 
        ('张三', '男',18, '一班',90),
        ('李四', '女',19, '二班',85),
        ('王五', '男',20, '三班',70),
        ('赵六', '女',18, '一班',95),
        ('钱七', '男',19, '二班',80),
        ('孙八', '女',20, '三班',75),
        ('周九', '男',18, '一班',85),
        ('吴十', '女',19, '二班',90),
        ('郑十一', '男',20, '三班',60),
        ('王十二', '女',18, '一班',95),
        ('赵十三', '男',19, '二班',75),
        ('钱十四', '女',20, '三班',80),
        ('孙十五', '男',18, '一班',90),
        ('周十六', '女',19, '二班',85),
        ('吴十七', '男',20, '三班',70),
        ('郑十八', '女',18, '一班',95),
        ('王十九', '男',19, '二班',80),
        ('赵二十', '女',20, '三班',75);

2.3. 删去表

drop table emp

2.4. 查询列

SELECT name, score FROM student;
# as命名列
SELECT name as "姓名", score as "分数" FROM student;
# 带where条件
select name as "姓名",class as "班级" from student where age >= 19;
# 条件能够是 and 连接的多个
select name as '姓名',class as '班级' from student where gender='男' and score >= 90;
# 用 LIKE 做含糊查询
select * from student where name like '王%';
# 经过 in 来指定一个调集：
select * from student where class in ('一班', '二班');
# not in：
select * from student where class not in ('一班', '二班');
# 经过 between and 来指定一个区间
select * from student where age between 18 and 20;
# 经过limit完成分页回来
select * from student limit 0,5
# 简写为
select * from student limit 5;
#第二页的数据
select * from student limit 5,5;
# 经过 order by 来指定排序的列，asc表明升序，desc表明降序
select name,score,age from student order by score asc,age desc;
# 分组计算每个班级的均匀成果：
SELECT class as '班级', AVG(score) AS '均匀成果' FROM student GROUP BY class ORDER BY '均匀成果' DESC;
# 经过count计算班级人数
select class, count(*) as count from student group by class;
# 依据having计算
SELECT class,AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score > 90;
# distinct去重
SELECT distinct class FROM student;

3. 内置函数

3.1. 聚合函数

聚合函数：用于对数据的计算，比方 AVG、COUNT、SUM、MIN、MAX。

select avg(score) as '均匀成果',count(*) as '人数',sum(score) as '总成果',min(score) as '最低分', max(score) as '最高分' from student

3.2. 字符串函数

字符串函数：用于对字符串的处理，比方 CONCAT、SUBSTR、LENGTH、UPPER、LOWER。

SELECT CONCAT('xx', name, 'yy'), SUBSTR(name,2,3), LENGTH(name), UPPER('aa'), LOWER('TT') FROM student;

其中，substr 第二个参数表明开始的下标（mysql 下标从 1 开始），所以 substr(‘一二三’,2,3) 的成果是 ‘二三’。

当然，也能够不写结束下标 substr(‘一二三’,2)

3.3. 数值函数

数值函数：用于对数值的处理，比方 ROUND、CEIL、FLOOR、ABS、MOD。

SELECT ROUND(1.234567, 2), CEIL(1.234567), FLOOR(1.234567), ABS(-1.234567), MOD(5, 2);

分别是 ROUND 四舍五入、CEIL 向上取整、FLOOR 向下取整、ABS 绝对值、MOD 取模。

3.4. 日期函数

日期函数：对日期、时刻进行处理，比方 DATE、TIME、YEAR、MONTH、DAY

SELECT YEAR('2023-06-01 22:06:03'), MONTH('2023-06-01 22:06:03'),DAY('2023-06-01 22:06:03'),DATE('2023-06-01 22:06:03'), TIME('2023-06-01 22:06:03');

3.5. 条件函数

条件函数：依据条件是否建立回来不同的值，比方 IF、CASE

select name, if(score >=60, '及格', '不及格') from student;
SELECT name, score, CASE WHEN score >=90 THEN '优秀' WHEN score >=60 THEN '杰出'ELSE '差' END AS '档次' FROM student;

if 和 case 函数和 js 里的 if、swtch 句子很像，很容易理解。

if 函数合适单个条件，case 合适多个条件。

3.6. 体系函数

体系函数：用于获取体系信息，比方 VERSION、DATABASE、USER。

select VERSION(), DATABASE(), USER()

3.7. 其他函数

其他函数：NULLIF、COALESCE、GREATEST、LEAST。

NULLIF：假如持平回来 null，不持平回来第一个值。

select NULLIF(1,1), NULLIF(1,2);

COALESCE：回来第一个非 null 的值：

select COALESCE(null, 1), COALESCE(null, null, 2)

GREATEST、LEAST：回来几个值中最大最小的。

select GREATEST(1,2,3),LEAST(1,2,3,4);

3.8. 类型转化函数

类型转化函数：转化类型为另一种，比方 CAST、CONVERT、DATE_FORMAT、STR_TO_DATE。

select greatest(1, '123',3);

3 最大，由于它并没有把 ‘123’ 当成数字.

用 convert 或许 cast 做类型转化：

select greatest(1, convert('123', signed),3);
select greatest(1, cast('123' as signed),3);

这儿能够转化的类型有这些：

signed：整型；
unsigned：无符号整型
decimal：浮点型；
char：字符类型；
date：日期类型；
time：时刻类型；
datetime：日期时刻类型；
binary：二进制类型

SELECT DATE_FORMAT('2022-01-01', '%Y年%m月%d日');

SELECT STR_TO_DATE('2023-06-01', '%Y-%m-%d');

灵活把握这些语法，就能写出各种复杂的查询句子。

4. JOIN ON 相关查询

4.1. 1对1查询

在MySQL中，联表查询是指在查询操作中使用多个表进行相关操作。通常情况下，咱们将不同的数据拆分到多个表中，经过联表查询能够依据这些表之间的联系，一次性获取到需求的数据。

外键（Foreign Key）是一种用于建立表与表之间相关的束缚。它定义了一个表中的一列，该列的值必须在另一个表的主键列中存在。外键用于确保数据的完好性和一致性，以及完成表之间的相关。

下面我用user和id_card两个表作为示例，来看看怎么创立外健和联表查询。

新建user表：

CREATE TABLE `mysql2-test`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(45) NOT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
);

新建id_card表：

CREATE TABLE `id_card` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `card_name` varchar(45) NOT NULL COMMENT '身份证号',
  `user_id` int DEFAULT NULL COMMENT '用户 id',
  PRIMARY KEY (`id`),
  INDEX `card_id_idx` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
)  CHARSET=utf8mb4;

user 表的主键是 id、能够经过 id 来唯一标识一个 user。那假如id_card 想查找 user，也便是我想经过身份知道这个user叫什么，那是不是在id_card表中需求存在一个id跟user表中的一模相同啊，这个id便是外健，也便是表中的user_id。

当咱们在id_card表查询某个用户身份证的时分，是不是也能知道对应的user_id,然后经过这个user_id再去user表中去查询id，假如持平是不是就找到了这个人的姓名。

这个便是经过外健来做的联表查询了。

下面分别在两个表刺进一些数据，以便咱们操作。

user表：

INSERT INTO `user` (`name`)
	VALUES
		('张三'),
		('李四'),
		('王五'),
		('赵六'),
		('孙七'),
		('周八'),
		('吴九'),
		('郑十'),
		('钱十一'),
		('陈十二');

id_card表：

INSERT INTO id_card (card_name, user_id)
    VALUES
        ('110101199001011234',1),
	('310101199002022345',2),
	('440101199003033456',3),
	('440301199004044567',4),
	('510101199005055678',5),
	('330101199006066789',6),
	('320101199007077890',7),
	('500101199008088901',8),
	('420101199009099012',9),
	('610101199010101023',10);

这样，1对1联系的数据就刺进成功了。

那怎么相关查出来呢？

SELECT * FROM user JOIN id_card ON user.id = id_card.user_id;

咱们优化一下上面列表的展示：

SELECT user.id, name, id_card.id as card_id, card_name
    FROM user
    JOIN id_card ON user.id = id_card.user_id;

这便是多表相关查询，语法是 JOIN ON。

JOIN ON的语法有三种：

INNER JOIN 是只回来两个表中能相关上的数据
LEFT JOIN 是额外回来左表中没有相关上的数据。
RIGHT JOIN 是额外回来右表中没有相关上的数据。

在 FROM 后的是左表，JOIN 后的表是右表。

当 user 删去的时分，相关的 id_card 要不要删去？

当 user 的 id 修正的时分，相关的 id_card 要不要改 user_id？

咱们之前在设置user_id的时分，是这样的：

其实它还还有其他的类型：

CASCADE：主表主键更新，从表相关记载的外键跟着更新，主表记载删去，从表相关记载删去
SET NULL：主表主键更新或许主表记载删去，从表相关记载的外键设置为 null
RESTRICT：只要没有从表的相关记载时，才答应删去主表记载或许更新主表记载的主键 id
NO ACTION：同 RESTRICT，仅仅 sql 标准里分了 4 种，但 mysql 里 NO ACTION 等同于 RESTRICT。

级联方法为 CASCADE 的处理逻辑：主表删去，从表相关记载也级联删去，主表 id 更新，从表相关记载也跟着更新。

4.2. 一对多查询

一对多的联系，比方一个部分有多个职工。

咱们会有一个部分表和一个职工表：

在职工表增加外键 department_id 来表明这种多对一联系：

咱们增加这两个表。

department表：

CREATE TABLE `mysql2-test`.`department` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(45) NOT NULL COMMENT '职工称号',
  PRIMARY KEY (`id`)
);

employee表：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(45) NOT NULL COMMENT '部分称号',
  `department_id` int DEFAULT NULL COMMENT '部分id',
  PRIMARY KEY (`id`),
  INDEX `department_id_idx` (`department_id` ASC) VISIBLE,
  CONSTRAINT `department_id` 
  	FOREIGN KEY (`department_id`) 
  	REFERENCES `department` (`id`)
  	ON DELETE SET NULL 
  	ON UPDATE SET NULL
)  CHARSET=utf8mb4;

往部分表刺进几条数据：

INSERT INTO `department` (`id`, `name`)
    VALUES 
        (1, '人事部'),
        (2, '财务部'),
        (3, '市场部'),
        (4, '技能部'),
        (5, '出售部'),
        (6, '客服部'),
        (7, '采购部'),
        (8, '行政部'),
        (9, '品控部'),
        (10, '研发部');

往职工表里刺进几条数据:

INSERT INTO `employee` (`id`, `name`, `department_id`)
    VALUES 
        (1, '张三', 1),
        (2, '李四', 2), 
        (3, '王五', 3),
        (4, '赵六', 4),
        (5, '钱七', 5),
        (6, '孙八', 5),
        (7, '周九', 5),
        (8, '吴十', 8),
        (9, '郑十一', 9),
        (10, '王十二', 10);

咱们经过 JOIN ON 相关查询下 id 为 5 的部分的一切职工：

select * from department
    join employee on department.id = employee.department_id
    where department.id = 5

把 id 为 5 的部分删掉：

DELETE FROM department WHERE id = 5;

再检查下职工表，能够看到出售部下的 3 个职工的部分被设置为 null 了：

一对多是不是还挺简单的？

的确，它和1对1没啥实质的区别。

4.3. 多对多查询

多对多，比方文章和标签：

之前一对多联系是经过在多的一方增加外键来引用一的一方的 id。

可是现在是多对多了，每一方都是多的一方。这时分是不是双方都要增加外键呢？

一般咱们是这样设计：

文章一个表、标签一个表，这两个表都不保存外键，然后增加一个中心表来保存双方的外键。

这样文章和标签的相相联系就都被保存到了这个中心表里。

下面咱们先创立这两个表。

创立article表：

CREATE TABLE `article` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `title` VARCHAR(50) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`id`)
) CHARSET=utf8mb4;

这儿的 TEXT 是长文本类型，能够存储 65535 长度的字符串。

刺进数据：

INSERT INTO `article` (`title`, `content`)
    VALUES
            ('文章1', '这是文章1的内容。'),
            ('文章2', '这是文章2的内容。'),
            ('文章3', '这是文章3的内容。'),
            ('文章4', '这是文章4的内容。'),
            ('文章5', '这是文章5的内容。');

创立Tag表：

CREATE TABLE `tag` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
);

刺进数据：

INSERT INTO `tag` (`name`)
    VALUES
            ('标签1'),
            ('标签2'),
            ('标签3'),
            ('标签4'),
            ('标签5');

创立中心表：

CREATE TABLE `article_tag` (
  `article_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`article_id`, `tag_id`),
  INDEX `tag_id_idx` (`tag_id` ASC) VISIBLE,
  CONSTRAINT `article_id` 
  	FOREIGN KEY (`article_id`) 
  	REFERENCES `article` (`id`)
  	ON DELETE CASCADE 
  	ON UPDATE CASCADE,
  CONSTRAINT `tag_id` 
  	FOREIGN KEY (`tag_id`) 
  	REFERENCES `tag` (`id`)
  	ON DELETE CASCADE 
  	ON UPDATE CASCADE
)  CHARSET=utf8mb4;

刺进数据：

INSERT INTO `article_tag` (`article_id`, `tag_id`)
    VALUES
    (1,1), (1,2), (1,3),
    (2,2), (2,3), (2,4),
    (3,3), (3,4), (3,5),
    (4,4), (4,5), (4,1),
    (5,5), (5,1), (5,2);

刺进的数据如下：

咱们查询出 id 为 1 的 article 的一切标签：

SELECT * FROM article a
    JOIN article_tag at ON a.id = at.article_id
    JOIN tag t ON t.id = at.tag_id
    WHERE a.id = 1

这样查询出的便是 id 为 1 的 article 的一切标签：

删去文章1:

delete from article where id = 1;

能够看到联系也被级联删去了，这便是 CASCADE 的作用。

当然，删去的仅仅联系，并不影响 id=1 的标签。

这便是多对多数据的表设计、相关查询和级联方法。

5. 子查询

sql 还支撑更复杂的组合，sql 能够嵌套 sql，也便是子查询。

还记得之前咱们创立过一个student表：

select * from student;

咱们想查询学生表中成果最高的学生的姓名和班级称号。

这是不是就要分红两个 sql 句子：

先查询最高分：

SELECT MAX(score) FROM student;

再查询这个分数为这个最高分的学生：

SELECT name, class FROM student WHERE score = 95;

这两个 sql 合并便是子查询：

SELECT name, class FROM student WHERE score = (SELECT MAX(score) FROM student);

会得到和上面相同的成果。

比方查询成果高于全校均匀成果的学生记载：

SELECT * FROM student WHERE score > (SELECT AVG(score) FROM student);

此外，子查询还有个特有的语法 EXISTS、NOT EXISTS。

咱们用部分表和职工表来试一下。

修正一下职工表的数据：

这样就有的部分 2 个职工，有的部分 3 个职工，有的部分没有职工了。

查询有职工的部分：

SELECT name FROM department
    WHERE EXISTS (
        SELECT * FROM employee WHERE department.id = employee.department_id
    );

EXISTS 的作用：子查询回来成果，条件建立，反之不建立。

用 NOT EXISTS 来查询一切没有职工的部分：

SELECT name FROM department
    WHERE NOT EXISTS (
            SELECT * FROM employee WHERE department.id = employee.department_id
    );

子查询不止 select 里能够用，insert、update、delete 句子相同能够。

咱们建个产品表：

CREATE TABLE product (
     id INT PRIMARY KEY,
     name VARCHAR(50),
     price DECIMAL(10,2),
     category VARCHAR(50),
     stock INT
);

刺进几条数据：

INSERT INTO product (id, name, price, category, stock)
	VALUES 
		(1, 'iPhone12',6999.00, '手机',100),
		(2, 'iPad Pro',7999.00, '平板电脑',50),
		(3, 'MacBook Pro',12999.00, '笔记本电脑',30),
		(4, 'AirPods Pro',1999.00, '耳机',200),
		(5, 'Apple Watch',3299.00, '智能手表',80);

查询价格最高的产品的信息：

SELECT name, price FROM product WHERE price = (SELECT MAX(price) FROM product);

除了 select 之外，增删改也是能够用子查询的。

比方咱们把每个产品分类的分类名、均匀价格查出来放入另一个 avg_price_by_category 表。

先创立这个表：

CREATE TABLE avg_price_by_category (
 id INT AUTO_INCREMENT,
 category VARCHAR(50) NOT NULL,
 avg_price DECIMAL(10,2) NOT NULL,
 PRIMARY KEY (id)
);

然后把 product 产品表里的分类和均匀价格查出来刺进这个表：

INSERT INTO avg_price_by_category (category, avg_price)
    SELECT category, AVG(price) FROM product GROUP BY category;

这便是 insert + select 结合使用的场景。

update 相同也能够使用 select 子查询。

比方之前的 department 和 employee 表，咱们想把技能部一切人的 name 前加上 “技能-”，就能够这么写：

UPDATE employee SET name = CONCAT('技能-', name)
    WHERE department_id = (
      SELECT id FROM department WHERE name = '技能部'
    );

还未更新完。。。欢迎持续重视

神光《Nest 通关秘籍》学习总结-前端快速入门MySql语法（真的很友好）

1. 数据定义句子(DDL)

1.1. 登录数据库

1.2. 创立数据库

1.3. 检查一切数据库

1.4. 选择数据库并使用

1.5. 检查一切数据表

1.6. 删去数据库

2. 表操作

2.1. 创立表

2.2. 刺进数据

2.3. 删去表

2.4. 查询列

3. 内置函数

3.1. 聚合函数

3.2. 字符串函数

3.3. 数值函数

3.4. 日期函数

3.5. 条件函数

3.6. 体系函数

3.7. 其他函数

3.8. 类型转化函数

4. JOIN ON 相关查询

4.1. 1对1查询

4.2. 一对多查询

4.3. 多对多查询

5. 子查询

作者信息

推广

神光《Nest 通关秘籍》学习总结-前端快速入门MySql语法（真的很友好）

1. 数据定义句子(DDL)

1.1. 登录数据库

1.2. 创立数据库

1.3. 检查一切数据库

1.4. 选择数据库并使用

1.5. 检查一切数据表

1.6. 删去数据库

2. 表操作

2.1. 创立表

2.2. 刺进数据

2.3. 删去表

2.4. 查询列

3. 内置函数

3.1. 聚合函数

3.2. 字符串函数

3.3. 数值函数

3.4. 日期函数

3.5. 条件函数

3.6. 体系函数

3.7. 其他函数

3.8. 类型转化函数

4. JOIN ON 相关查询

4.1. 1对1查询

4.2. 一对多查询

4.3. 多对多查询

5. 子查询

相关文章

几款常用database的性能对比

树的子结构

小白也能一看就懂的KNN算法——原理+实例+代码

gulp自动化构建项目

作者信息

推广