前端狗的后端路——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 类型:
-
LEFT JOIN
是额外返回左表(主)中没有关联上的数据。 -
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. 函数
函数同存储过程,不过函数还有返回值。
这三种相对用的比较少,所以只做了解即可。
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgbbahk
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01