Cleofwine | 开发者

后端技术_SQL语句_索引_视图_存储过程_触发器

最近更新：2024-09-23 | 字数总计：4.2k | 阅读估时：18分钟 | 阅读量：次

基础术语
MySQL体系结构
1. 怎么执行一条select语句（*）
三范式及反范式
CRUD
高级查询
视图
1. 作用
触发器
存储过程( X )
权限管理
预处理语句
复杂案例

基础术语

OLTP：（On-Line transaction processing）联机事务处理，对应业务层对数据的增删改查；
OLAP：（On-Line Analytical Processing）联机分析处理，对应后台管理系统对数据进行分析，以帮助公司决策；
SQL：Structured Query Language，结构化查询语句，是关系型数据库系统的标准语言；
DML：Data Manipulate Language，数据操作语言，会引起磁盘读写的语句，insert、update、delete；
DDL：Data Define Languge，数据定义语言，会引起表结构改变的语句，create、alter、drop；
TCL：Transaction Control Language，事务控制语言，commit、rollback；

MySQL体系结构

Connectors：不同语言的代码程序实现与MySQL交互的驱动；
连接池：MySQL实现的连接池，使用了IO多路复用select，另外每个连接分配了一个线程，且线程内部使用了阻塞io（因为MySQL设计上不允许很多的连接）
查询解析器：将 SQL 对象交由解析器验证和解析，并生成语法树；
查询优化器：SQL 语句执行前使用查询优化器进行优化；
缓冲组件：1）5.7版本后实现了一个查询缓存，已经进行过的查询会通过查询缓存（kv存储）直接返回，不会真正执行select操作，但是由于命中率不高8.0版本后取消了该实现；2）还有其他缓冲池数据类型：索引、数据、及存储引擎页等；

怎么执行一条select语句（*）

连接器：接收连接、管理连接、校验用户信息
查询缓存：5.7以上8.0以下，kv存储，命中直接返回，否则继续执行
分析器：词法句法分析生成语法树
优化器：制定执行计划，选择查询成本最小的计划
执行器：根据执行计划，从存储引擎获取数据，并返回客户端

三范式及反范式

范式一

确保每列保持原子性，数据库表中所有字段都是不可分解的原子值；
- 例如：某表中有一个地址字段，如果经常需要访问地址字段中的城市属性，则需要将该字段拆分为多个字段，省份、城市、详细地址等

范式二

确保表中的每列都和主键相关，而不能只与主键的某一部分相关（组合索引）
- 例如

订单编号	商品编号	商品名称	数量	单位	价格	客户	所属单位	联系方式
1	1	电脑	1	台	10000	mark	0voice1	137
1	2	手机	2	部	6000	mark	0voice1	137
2	3	ipad	3	部	3000	milo	0voice2	88

拆分成三个表

订单编号	客户	所属单位	联系方式
1	mark	0voice1	137
2	milo	0voice2	88

订单编号	商品编号	数量
1	1	1
1	2	2
2	3	3

商品编号	商品名称	单位	价格
1	电脑	台	10000
2	手机	部	6000
3	ipad	部	3000

范式三

确保每列都和主键直接相关，而不是间接相关；在范式2的基础上直接依赖主键；

三范式的目的

减少数据存储空间上的占用

反范式

范式可以避免数据冗余，减少数据库的空间，减小维护数据完整性的麻烦；但是采用数据库范式化设计，可能导致数据库业务涉及的表变多，并且造成更多的联表查询，将导致整个系统的性能降低；
反范式的依据：频繁进行联表查询的表，一般联表查询最多联合2个表；

CRUD

创建数据库

1	CREATE DATABASE `数据库名` DEFAULT CHARACTER SET utf8;

删除数据库

1	DROP DATABASE `数据库名`;

选择数据库

1	USE `数据库名`;

创建表

CREATE TABLE `table_name` (column_name column_type);
CREATE TABLE IF NOT EXISTS `0voice_tbl` (
`id` INT UNSIGNED AUTO_INCREMENT COMMENT '编
号',
`course` VARCHAR(100) NOT NULL COMMENT '课程',
`teacher` VARCHAR(40) NOT NULL COMMENT '讲师',
`price` DECIMAL(8,2) NOT NULL COMMENT '价格',
PRIMARY KEY ( `id` )
)ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课
程表';

删除表

1	DROP TABLE `table_name`;

清空数据表

1
2

TRUNCATE TABLE `table_name`; -- 截断表 以页为单位（至少有两行数据），有自增索引的话，从初始值开始累加
DELETE TABLE `table_name`; -- 逐行删除，有自增索引的话，从之前值继续累加

drop、truncate、delete的区别（*）

数据库中的对象：
1. 表结构：记录了表中数据的格式及约束
2. 表数据：内存中的B+树，节点为一行数据的主键（主键必须存在，若不给定，将由MySQL自动创建），由主键为依据进行了排序
3. 磁盘数据：B+树的一个节点经过mmap映射到磁盘中连续的两个8K的页中（16K）
drop：
1. 快，通过标记的方式实现的异步删除，
2. 删除整张表结构和表数据，包括索引、约束、触发器等（表结构也删除了）
3. 不能回滚
truncate：
1. 较快，以页为单位（16K）进行逐步的删除
2. 仅删除表数据，但是自增编号会重新计算
3. 不能回滚
delete：
1. 慢，需要查询并删除某个B+树的节点（对应是逐行删除的）
2. 删除部分或全部数据，且自增编号并不会重新计算
3. 可以进行回滚

增加

1
2

INSERT INTO `table_name`(`field1`, `field2`, ..., `fieldn`) VALUES (value1, value2, ..., valuen);
INSERT INTO `0voice_tbl` (`course`, `teacher`, `price`) VALUES ('C/C++Linux服务器开发/高级架构师', 'Mark', 7580.0);

删除

1 2	DELETE FROM `table_name` [WHERE Clause]; DELETE FROM `0voice_tbl` WHERE id = 3;

修改

UPDATE table_name SET field1=new_value1, field2=new_value2 [, fieldn=new_valuen]
UPDATE `0voice_tbl` SET `teacher` = 'Mark' WHERE id = 2;
-- 累加
UPDATE `0Voice_tbl` set `age` = `age` + 1 WHERE id = 2;

查询

1	SELECT field1, field2,...fieldN FROM table_name [WHERE Clause]

五大约束

not null非空约束
auto_increment 自增约束
unique 唯一约束
primary 主键约束（非空 + 唯一）
foreign 外键约束

高级查询

基础查询

-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT `sname`, `class_id` FROM student;
-- 别名 列明 不要用关键字
SELECT `sname` AS '姓名' , `class_id` AS '班级ID'
FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct `class_id` FROM student;

条件查询

-- 查询姓名为 邓洋洋 的学生信息
SELECT * FROM `student` WHERE `name` = '邓洋洋';
-- 查询性别为 男，并且班级为 2 的学生信息
SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=2;

范围查询

1 2	-- 查询班级id 1 到 3 的学生的信息 SELECT * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;

判空查询

-- is null 判断造成索引失效
-- 索引 B+ 树
SELECT * FROM `student` WHERE `class_id` IS NOT NULL; -- 判断不为空
SELECT * FROM `student` WHERE `class_id` IS NULL;
-- 判断为空
SELECT * FROM `student` WHERE `gender` <> '';
-- 判断不为空字符串
SELECT * FROM `student` WHERE `gender` = '';
-- 判断为空字符串

模糊查询

-- 使用 like关键字，"%"代表任意数量的字符，”_”代表占位符
-- 查询名字为 m 开头的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '谢%';
-- 查询姓名里第二个字为 小 的学生的信息
SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';

分页查询

-- 分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用
-- 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0。
-- 查询第二条到第三条内容
SELECT * FROM `student` LIMIT 1,2;

查询后排序

-- 关键字：order by field, asc:升序, desc:降序
SELECT * FROM `score` ORDER BY `num` ASC;
-- 按照多个字段排序
SELECT * FROM `score` ORDER BY `course_id` DESC, `num` DESC;

聚合查询

SELECT sum(`num`) FROM `score`;
SELECT avg(`num`) FROM `score`;
SELECT max(`num`) FROM `score`;
SELECT min(`num`) FROM `score`;
SELECT count(`num`) FROM `score`;

分组查询

-- 分组加group_concat
SELECT `gender`, group_concat(`age`) as ages FROM `student` GROUP BY `gender`;
-- 可以把查询出来的结果根据某个条件来分组显示
SELECT `gender` FROM `student` GROUP BY `gender`;
-- 分组加聚合
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`;
-- 分组加条件
SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;

联表查询

-- inner join, 只取两张表有对应关系的记录
SELECT cid FROM `course` INNER JOIN `teacher` ON course.teacher_id = teacher.tid;
-- left join, 在内连接的基础上保留左表没有对应关系的记录
SELECT course.cid FROM `course` LEFT JOIN `teacher` ON course.teacher_id = teacher.tid;
-- right join, 在内连接的基础上保留右表没有对应关系的记录
SELECT course.cid FROM `course` RIGHT JOIN `teacher` ON course.teacher_id = teacher.tid;

子查询/合并查询

-- 单行子查询
select * from course where teacher_id = (select tid from teacher where tname = '谢小二老师')
-- 多行子查询
-- IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
select * from student where class_id in (select
cid from course where teacher_id = 2);

-- EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为 true 时，外层查询语句将进行查询；当返回的为false 时，外层查询语句不进行查询或者查询不出任何记录。
select * from student where exists(select cid
from course where cid = 5);

-- 在 FROM 子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用。
SELECT student_id, sname FROM
(SELECT * FROM score WHERE course_id = 1 OR
course_id = 2) AS A
LEFT JOIN student ON A.student_id =
student.sid;

-- ALL 关键字：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
-- ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。

视图

虚拟逻辑表技术
Mysql并没有物化视图（未写入磁盘），所以没有对查询效率的优化，Oracle进行了物化
通常只用select，update和delete、insert的限制多，不要用
1
CREATE VIEW <视图名> AS <SELECT语句>

作用

重构利器（假设表过大时，进行拆表，而用户层进行联合查询不符合业务逻辑，可以将查询操作包装成视图）

1
2
3

-- user 表 被拆成 usera userb
create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;
-- 此时拆表操作并不会影响应用层

权限控制，某些表对用户屏蔽，但是可以给该用户通过视图来对该表操作；

触发器

定义触发器，可用于数据库保证完整性的方法，当表中一行数据进行操作时可以触发触发器

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body -- 此处写执行语句
-- trigger_body: 可以一个语句，也可以是多个语句；多个语句写在 BEGIN ... END 间
-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
other_trigger_name

触发器具备事务性？

存储过程( X )

08年后几乎没有人用

权限管理

创建用户

1	CREATE USER username@host IDENTIFIED BY password;

授权

1	GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

对视图授权

1	GRANT select, SHOW VIEW ON `databasename`.`tablename` to 'username'@'host';

刷新权限

1 2	-- 修改权限后需要刷新权限 FLUSH PRIVILEGES;

预处理语句

将sql语句分离成两个部分，准备和执行（准备的时候先执行分析器、优化器得到执行计划，执行的时候再执行）
流程：
1. 将sql语句发送给服务器进行解析、编译、优化（准备）
2. 生成一个执行计划并缓存（准备）
3. 将传递过来的参数作用在缓存好的查询计划再执行（执行）
优点：
1. 减少重复解析和编译；
2. 2：防止sql注入，避免拼接字符串而导致sql注入（会先将prepare的带？占位符的语句传给mysql，之后传具体的类型值，类型值会由mysql进行判断，避免了字符串的拼接）；

复杂案例

具体操作

表结构

DROP DATABASE IF EXISTS `edu_svc`;
CREATE DATABASE `edu_svc`;

-- 百度   navicat
USE `edu_svc`;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '1908班');
INSERT INTO `class` VALUES ('2', '2004班');
INSERT INTO `class` VALUES ('3', '2101班');
INSERT INTO `class` VALUES ('4', '2109班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'c++高级架构', '1');
INSERT INTO `course` VALUES ('2', '音视频', '2');
INSERT INTO `course` VALUES ('3', 'linux内核源码剖析', '4');
INSERT INTO `course` VALUES ('4', 'go云原生', '2');
INSERT INTO `course` VALUES ('5', 'dpdk网络协议栈', '1');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '10');
INSERT INTO `score` VALUES ('2', '1', '2', '59');
INSERT INTO `score` VALUES ('3', '4', '5', '100');
INSERT INTO `score` VALUES ('4', '5', '4', '67');
INSERT INTO `score` VALUES ('5', '2', '2', '66');
INSERT INTO `score` VALUES ('6', '2', '1', '58');
INSERT INTO `score` VALUES ('8', '2', '3', '68');
INSERT INTO `score` VALUES ('9', '2', '4', '99');
INSERT INTO `score` VALUES ('10', '3', '1', '77');
INSERT INTO `score` VALUES ('11', '3', '2', '66');
INSERT INTO `score` VALUES ('12', '3', '3', '87');
INSERT INTO `score` VALUES ('13', '3', '4', '99');
INSERT INTO `score` VALUES ('14', '4', '1', '79');
INSERT INTO `score` VALUES ('15', '4', '2', '11');
INSERT INTO `score` VALUES ('16', '4', '3', '67');
INSERT INTO `score` VALUES ('17', '4', '4', '100');
INSERT INTO `score` VALUES ('18', '5', '1', '79');
INSERT INTO `score` VALUES ('19', '5', '2', '11');
INSERT INTO `score` VALUES ('20', '5', '3', '67');
INSERT INTO `score` VALUES ('21', '5', '5', '100');
INSERT INTO `score` VALUES ('22', '6', '1', '9');
INSERT INTO `score` VALUES ('23', '6', '2', '100');
INSERT INTO `score` VALUES ('24', '6', '3', '67');
INSERT INTO `score` VALUES ('25', '6', '4', '100');
INSERT INTO `score` VALUES ('26', '7', '1', '9');
INSERT INTO `score` VALUES ('27', '7', '2', '100');
INSERT INTO `score` VALUES ('28', '7', '3', '67');
INSERT INTO `score` VALUES ('29', '7', '4', '88');
INSERT INTO `score` VALUES ('30', '8', '1', '49');
INSERT INTO `score` VALUES ('31', '8', '2', '100');
INSERT INTO `score` VALUES ('32', '8', '3', '67');
INSERT INTO `score` VALUES ('33', '8', '4', '88');
INSERT INTO `score` VALUES ('34', '9', '1', '91');
INSERT INTO `score` VALUES ('35', '9', '2', '88');
INSERT INTO `score` VALUES ('36', '9', '5', '67');
INSERT INTO `score` VALUES ('37', '9', '4', '22');
INSERT INTO `score` VALUES ('38', '10', '1', '90');
INSERT INTO `score` VALUES ('39', '10', '2', '77');
INSERT INTO `score` VALUES ('40', '10', '3', '43');
INSERT INTO `score` VALUES ('41', '10', '4', '87');
INSERT INTO `score` VALUES ('42', '11', '1', '90');
INSERT INTO `score` VALUES ('43', '11', '2', '77');
INSERT INTO `score` VALUES ('44', '11', '5', '43');
INSERT INTO `score` VALUES ('45', '11', '4', '87');
INSERT INTO `score` VALUES ('46', '12', '1', '90');
INSERT INTO `score` VALUES ('47', '12', '2', '77');
INSERT INTO `score` VALUES ('48', '12', '3', '43');
INSERT INTO `score` VALUES ('49', '12', '5', '87');
INSERT INTO `score` VALUES ('50', '13', '3', '87');
INSERT INTO `score` VALUES ('51', '14', '2', '33');
INSERT INTO `score` VALUES ('52', '15', '3', '22');
INSERT INTO `score` VALUES ('53', '15', '5', '11');
INSERT INTO `score` VALUES ('54', '13', '1', '99');
INSERT INTO `score` VALUES ('55', '13', '2', '99');
INSERT INTO `score` VALUES ('56', '13', '4', '67');
INSERT INTO `score` VALUES ('57', '13', '5', '87');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '男', '1', '罗谊');
INSERT INTO `student` VALUES ('2', '女', '1', '戴巧');
INSERT INTO `student` VALUES ('3', '男', '1', '叶黎');
INSERT INTO `student` VALUES ('4', '男', '3', '邵柴');
INSERT INTO `student` VALUES ('5', '女', '1', '韩琪');
INSERT INTO `student` VALUES ('6', '男', '3', '尹伸');
INSERT INTO `student` VALUES ('7', '女', '2', '孙燕');
INSERT INTO `student` VALUES ('8', '男', '2', '廖宽');
INSERT INTO `student` VALUES ('9', '男', '2', '孙行');
INSERT INTO `student` VALUES ('10', '女', '2', '宋贤');
INSERT INTO `student` VALUES ('11', '男', '2', '谭国兴');
INSERT INTO `student` VALUES ('12', '女', '3', '于怡瑶');
INSERT INTO `student` VALUES ('13', '男', '4', '文乐逸');
INSERT INTO `student` VALUES ('14', '男', '4', '邹乐和');
INSERT INTO `student` VALUES ('15', '女', '5', '邓洋洋');
INSERT INTO `student` VALUES ('16', '男', '5', '秦永福');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '王天一老师');
INSERT INTO `teacher` VALUES ('2', '谢小二老师');
INSERT INTO `teacher` VALUES ('3', '廖阿三老师');
INSERT INTO `teacher` VALUES ('4', '吴启四老师');
INSERT INTO `teacher` VALUES ('5', '谢飞五老师');

查询平均成绩大于60分的同学的学号和平均成绩；
1
查询 ‘c++高级架构’ 课程比 ‘音视频’ 课程成绩高的所有学生的学号；
1
查询所有同学的学号、姓名、选课数、总成绩；
1
查询没学过 ‘谢小二’ 老师课的同学的学号、姓名；
1
查询学过课程编号为 ‘1’ 并且也学过课程编号为 ‘2’ 的同学的学号、姓名；
1
查询学过 ‘谢小二’ 老师所教的所有课的同学的学号、姓名；
1
查询有课程成绩小于 60 分的同学的学号、姓名；
1
查询没有学全所有课的同学的学号、姓名；
1
查询至少有一门课与学号为 ‘1’ 的同学所学相同的同学的学号和姓名；
1
查询至少学过学号为 ‘1’ 同学所有课的其他同学学号和姓名；
1

同步连接数据库

异步连接数据库

2024-03-11 该篇文章被 Cleofwine 归为分类: 服务端