前端狗的后端路——MySQL 简笔

数据库

数据库（database 或者 schema）下存储着很多表，视图，存储过程，函数

    graph BT
    表Table -->  D[数据库]
    视图View -->  D
    存储过程StoredProducer -->  D
    函数Function -->  D

数据类型

MySQL 里的数据类型有很多：

整形：TINYINT(1 字节) SMALLINT(2 字节) MEDIUMINT(3 字节) INT(4 字节) BIGINT(8 字节)

浮点数类型：FLOAT(4 字节) DOUBLE(8 字节)

定点型数字：DECIMAL（用于存储金额）

// 表示存储的值为 -999.99 ~ 999.99 之间
DECIMAL(5, 2) // 5表示总位数。 2表示小数点后的位数

定长字符串：CHAR （不够的自动在末尾填充空格）

CHAR(60) // 60个字符，不论存储多少，都会占用60个字符的空间

变长字符串：VARCHAR

等等。。。。其他不过多赘述。

增删改查

-- 插入
INSERT INTO `库名`.`表名` (`字段1`, `字段2`) VALUES (`值1`, `值2`);

-- 修改
UPDATE `库名`.`表名` SET `字段2` = '新值2' WHERE (`id`='2')

-- 批量修改
UPDATE `库名`.`表名` SET `字段2` = '新值2' WHERE `id` IN ('1','2','3')

-- 批量修改2
UPDATE `库名`.`表名` SET `字段2` = '新值2' WHERE `id` BETWEEN '1' AND '3'

-- 删除
DELETE FROM `库名`.`表名` WHERE (`id` = '1')

-- 最简单的查询（ * 表示所有字段）
SELECT * FROM `库名`.`表名`;

查询进阶

-- 查询 pnm-database 库下的 student 表的所有人的 name,age 字段
SELECT name,age FROM `pnm-database`.student;

-- 查询 pnm-database 库下的 student 表的 id 为 2 的人的 `id,name,age` 字段
SELECT id,name,age FROM `pnm-database`.student WHERE (`id` = 2);


SELECT id,name,age FROM `pnm-database`.student WHERE age>22;

在 GUI 里有些可以省略，不一定非要写 引号 括号 之类的

但是注意，字符串值需要加引号， 字段名可以不加引号, 且字符串值只能用 '' , 不能用 ``

分组，排序，分组再过滤，去重

-- 查找出各个班的平均分，并且升序排列（注意 ORDER BY 后面的 如果你要加 引号， 别加单引号，要加 ` 引号）
SELECT class as '班级', AVG(score) as '平均分' FROM student GROUP BY class ORDER BY `平均分` ASC;

这里 GROUP BY class 表示 以 class 划分组别（你也可以用 GROUP BY `班级`，注意班级要用反引号，或者不写引号）

而 ORDER BY 平均分 ASC 表示 用 平均分 升序排列

-- 先用班级分组，再找出 平均分 > 90 的。
SELECT class as '班级', AVG(score) as '平均分' FROM student GROUP BY `班级` HAVING `平均分`>90;

-- 这条报错
SELECT class as '班级', AVG(score) as '平均分' FROM student GROUP BY `班级` ORDER BY `平均分` ASC HAVING `平均分`>90;

-- 这条则没错。
SELECT class as '班级', AVG(score) as '平均分' FROM student GROUP BY `班级` HAVING `平均分`>80 ORDER BY `平均分` ASC ;

当用了 GROUP BY 之后，想再过滤，则需要使用 HAVING 子句，不能使用 WHERE。

【注】要注意规则关系，HAVING 是跟在 GROUP BY 后面的，咱们逻辑捋清楚来：先找出符合平均分条件的内容，再进行排序。

我第二条的做法相当于我想先排序，再找出符合条件的内容。逻辑有些尴尬，语法也错了，记录下以此为训。

-- 给 class 去重
SELECT DISTINCT clss FROM student;

内置函数

内置函数分为好几大类，其中有：

1. 聚合函数

对数据统计，比如 AVG、COUNT、SUM、MIN、MAX

SELECT MAX(score) as '最大值',MIN(score) as '最小值',COUNT(*) as '人数',AVG(score) as '平局分',SUM(score) as '总计' FROM student;

2. 字符串函数

对字符串进行处理，比如 CONCAT、SUBSTR、LENGTH、CHAR_LENGTH、UPPER、LOWER

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

SUBSTR(name,2,3) 表示截取 name 字段中从下标 2 开始到下标 3 结束的字符串。（mysql 中下标是从 1 开始的）

LENGTH 和 CHAR_LENGTH 其中 LENGTH 是去计算这段字符串的字节数，而 CHAR_LENGTH 是计算字符数

LENGTH('你好'); -- mysql 默认 utf-8 ，所以这里返回 6 字节
CHAR_LENGTH('你好'); -- 两个字符

3. 数值函数

用于对数值的处理，比如 ROUND、CEIL、FLOOR、ABS、MOD

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

4. 日期函数

对日期、时间进行处理，比如 DATE、TIME、YEAR、MONTH、DAY

5. 条件函数

根据条件是否成立返回不同的值，比如 IF、CASE

SELECT name, IF(score >=60, '及格', '不及格') as '是否及格' FROM student;

SELECT name, score, CASE WHEN score >=90 THEN '优秀' WHEN score >=60 THEN '良好'ELSE '差' END AS '档次' FROM student;

主从表查询

表与表之间有一对一，一对多，多对多的关系。

一对一

-- 查找 user 和 id_card 两个表的信息，并且通过 user.id 和 id_card.user_id 关联起来
SELECT * FROM user JOIN id_card ON user.id=id_card.user_id;

主表 JOIN 从表 ON 主表主键=从表外键

JOIN ON 默认是 INNER JOIN 是只返回两个表中能关联上的数据。

还可以指定其余 2 种 join 类型：

1. LEFT JOIN 是额外返回左表（主）中没有关联上的数据。

2. RIGHT JOIN 是额外返回右表（从）中没有关联上的数据。

一对多 和 一对一 没有本质的区别，从表外键也是只有一个。

一对一的例子可以用 用户 和 身份证：一个用户只能对应一个身份证。而一对多的例子可以用 部门 和 员工 来举例，一个部门可以有多个员工。

多对多

一般来说，一对一或者一对多，我们只需要给从表设置一个外键即可。但多对多是否需要设置多个外键呢？？

很明显多个外键不现实，我们用 文章 和 标签 举个例子。

标签表里的标签可以分配给多个文章，文章表里的文章又可以添加多个标签

那如果设置多个外键，那么 一篇文章 里需要设置很多个外键（标签数量是不统一的）很明显这样做法就很蠢笨无效率。

所以就有了这种设计：

采用一个中间表的办法，这个中间表保存 文章 id 和 标签 id

比如：

文章 id	标签 id
1	1
1	2
2	1
3	1
3	2

然后将这个表的 文章 id 和 标签 id 作为外键分别关联 文章表 和 标签表

如上，文章1 ，有标签1 和标签2，文章2 有标签1，从标签中也可以看出，标签1 分别分配给了文章 1，2，3。

那么查要如何查？

-- 通过多个join连接三个表
select * from 文章 join 中间表 on 文章.id=中间表.文章id join 标签表 on 标签.id=中间表.标签id;


select * from article_tag
    join article on article.id=article_tag.article_id
    join tag on tag.id=article_tag.tag_id;

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

【注】中间表的外键的连接方式一定为 CASCADE 即：数据清空了，中间表里的也自动删除。另外，在数据表设计里是强烈建议不要设置 物理外键，向上述这种都是属于物理外键。

子查询

除了上述用到的这些查询，SQL 还支持更复杂的查询，SQL 里面嵌套 SQL。比如，我要查找最高成绩的人的班级和名字，那么就可以这样做：

-- 先通过 select max(score) from student 查询成绩最高是多少分
-- 在通过 where score=() 查找到该分数的人的班级及名字
select class,name,score from student where score=(select max(score) from student);

EXISTS

EXISTS 原意为“存在”，在其后跟随的子查询语句中，不会返回具体数据，只会返回 true 或 false，然后会根据子查询语句的结果选择是否展示

-- 这里会对 department 表里的数据逐条取出与子查询语句进行对比
-- 如果子查询语句能查到数据，则返回 true，否则返回 false
-- 所以这里显示的是，有员工的部门
SELECT name FROM department
    WHERE EXISTS (
        SELECT * FROM employee WHERE department.id = employee.department_id
    );

举例描述下，假设 department 表 的数据为如下：

id	部门名称
1	人事部
2	开发部
3	产品部
4	测试部

employee 表 的数据为如下：

id	员工名称	部门 ID
1	彭尼玛	1
2	王尼玛	3
3	月尼玛	3
4	牛尼玛	4

用我们上述的 SQL 语句，他会依次将部门逐条取出，然后用子查询语句 SELECT * FROM employee WHERE department.id = employee.department_id，然后发现 department 第一条，id 为 1 的人事部，且能在 employee 中找到 彭尼玛 与之对应。则可以找出数据，返回 true。

紧接着第二条，id 为 2 的开发部，同样使用子查询语句，会发现找不出数据，则返回 false。

以此类推，最终只会查出 人事部，产品部，测试部。

【注】这里的子查询语句 SELECT * FROM employee WHERE department.id = employee.department_id 是不能直接查出东西来的，因为 department 是不存在的，但结合之前学过的，可以使用 JOIN ON 查出一样的数据。

-- 查出有员工的部门
SELECT department.name FROM employee JOIN department ON department.id = employee.department_id

-- 但不能使用上述语句作为 EXISTS 的子查询语句，因为这已经干扰 `部门逐条取出` 这一功能，使得这条子查询语句几乎都返回true，所以达不到效果。

NOT EXISTS

NOT EXISTS 不存在

-- 查出不存在员工的部门
SELECT name FROM department
    WHERE NOT EXISTS (
        SELECT * FROM employee WHERE department.id = employee.department_id
    );

除了 select 外，insert、update、delete 这些语句也可以有子查询。

-- 从表2中根据 category 分组，并求出平均值，在存储到 category 和 avg_price 字段中
-- 可以结合上述 INSERT INTO VALUES 会发现少了 VALUES ,但却多了 SELECT FROM 的子查询语句
INSERT INTO 表1 (category, avg_price) SELECT category, AVG(price) FROM 表2 GROUP BY category;

事务

如果有多个 update 语句，其中一个修改了订单详情表的数量，另外一个修改了订单表的总金额，但是修改总金额的 sql 执行失败了。。。

为了避免上述的问题，就需要事务（transaction）来进行处理。

-- 开启事务
START TRANSACTION

-- 执行sql语句
UPDATE order_items SET quantity=1 WHERE order_id=3;

UPDATE orders SET total_amount=200 WHERE id=3;

-- 发现执行错误，即可用 ROLLBACK 将数据恢复~
ROLLBACK;

START TRANSACTION;

UPDATE order_items SET quantity=1 WHERE order_id=3;

UPDATE orders SET total_amount=200 WHERE id=3;

-- 提交，提交之后就不能在回滚了
COMMIT;

START TRANSACTION;

UPDATE order_items SET quantity=1 WHERE order_id=3;

-- 记录 aaa 位置
SAVEPOINT aaa;

UPDATE orders SET total_amount=200 WHERE id=3;

-- 回滚到 aaa
ROLLBACK TO SAVEPOINT aaa;

视图，存储过程，函数

上面说了一堆关于表的，接下来还有 视图，存储过程 和 函数。

1. 视图

视图可以将一些复杂的查询组合起来，有点类似前端中，将多个标签组合成一个组件，然后用户直接调用组件。

视图也是由多个复杂的 sql 语句组合起来，然后用户直接调用 视图 即可展示出需要的信息。

-- 创建了一个 customer_orders 视图
CREATE VIEW customer_orders AS
    SELECT
        c.name AS customer_name,
        o.id AS order_id,
        o.order_date,
        o.total_amount
    FROM customers c
    JOIN orders o ON c.id = o.customer_id;

-- 查询视图 customer_orders ，则会查出 as 后面的sql语句能查出的内容
SELECT * FROM practice.customer_orders;

2. 存储过程

存储过程也是将一段 sql 封装起来，然后允许传参数调用。

3. 函数

函数同存储过程，不过函数还有返回值。

这三种相对用的比较少，所以只做了解即可。

这篇好文章是转载于：学新通技术网