数据库

最新推荐文章于 2022-09-15 11:23:27 发布

小宇学编程

最新推荐文章于 2022-09-15 11:23:27 发布

阅读量164

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/Pasiyu/article/details/100564461

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

DDL:数据定义语言--定义数据库对象(数据库,表,列,),可以创建,删除,修改
DML:数据操作语言  增删改
DCL:数据控制语言,用它定义访问权限和安全级别
DQL:数据查询语言

存储引擎:

InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。
NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

排序规则:

排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感

排序方式:

在MySQL中，支持两种排序方式：FileSort和Index排序。Index排序的效率更高，
Index排序：索引可以保证数据的有序性，因此不需要再进行排序。
FileSort排序：一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序，效率较低。

所以使用ORDER BY子句时，应该尽量使用Index排序，避免使用FileSort排序。
当然具体优化器是否采用索引进行排序，你可以使用explain来进行执行计划的查看。
优化建议：
1、SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，ORDER BY子句避免使用FileSort排序。
当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
一般情况下，优化器会帮我们进行更好的选择，当然我们也需要建立合理的索引。
2、尽量Using Index完成ORDER BY排序。
如果WHERE和ORDER BY相同列就使用单索引列；如果不同使用联合索引。
3、无法Using Index时，对FileSort方式进行调优。

执行顺序:

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

SQL函数:

算术函数,字符串函数,日期函数


小知识点:
1.用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数
2.因为很多时候你无法确认 birthdate 的数据类型是字符串，还是 datetime 类型，如果你想对日期部分进行比较，那么使用DATE(birthdate)来进行比较是更安全的
3.MySQL 在 Linux 的环境下，数据库名、表名、变量名是严格区分大小写的，而字段名是忽略大小写的。
  而 MySQL 在 Windows 的环境下全部不区分大小写。
4.建议：关键字和函数名称全部大写；
  数据库名、表名、字段名称全部小写；
  SQL 语句必须以分号结尾

日期:

DATE 日期格式必须是 yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较
*无法确认是字符串还是日期*

聚集函数:

AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行，MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。

分组:

WHERE 是用于数据行，而 HAVING 则作用于分组。

执行顺序:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

子查询:

我们可以将子查询分成关联子查询和非关联子查询，其中非关联子查询与主查询的执行无关，只需要执行一次即可，而关联子查询，则需要将主查询的字段值传入子查询中进行关联查询

5 种连接方式:

SQL92 中的 5 种连接方式，它们分别是笛卡尔积、等值连接、非等值连接、外连接（左连接、右连接）和自连接

笛卡尔积:SELECT * FROM player, team
等值连接:SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id
非等值连接:SELECT p.player_name, p.height, h.height_level FROM player AS p, height_grades AS h WHERE p.height BETWEEN h.height_lowest AND h.height_highest
左连接:SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id
右连接:SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id
自连接:SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height

小知识点:

ANY、ALL 关键字必须与一个比较操作符一起使用。

视图:

创建视图：CREATE VIEW

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition(过滤条件)

嵌套视图

创建好一张试图后,还可以在它的基础上继续创建视图

在player_above_avg_height的基础上创建player_above_above_avg_height
CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height的基础上创建)

修改视图：ALTER VIEW

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

删除视图：DROP VIEW

DROP VIEW view_name

利用视图对数据进行格式化:

对应格式为 player_name(team_name)
CREATE VIEW player_team AS 
SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team FROM player JOIN team WHERE player.team_id = team.team_id

在这里插入图片描述

存储过程

什么是存储过程?

存储过程,就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。存储过程实际上由 SQL 语句和流控制语句共同组成。

如何定义一个存储过程:

CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
    需要执行的语句
END

更新,删除:

和视图一样，删除已经创建的存储过程，使用的是 DROP PROCEDURE。如果要更新存储过程，我们需要使用 ALTER PROCEDURE。

想做一个累加运算，计算 1+2+…+n 等于多少，我们可以通过参数 n 来表示想要累加的个数，那么如何用存储过程实现这一目的呢？

CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END

当我们需要再次使用这个存储过程的时候，直接使用CALL add_num(50);

如果直接用Mysql

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

因为默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此我们就需要临时定义新的 DELIMITER，新的结束符可以用（//）或者（$$）。


首先用（//）作为结束符，又在整个存储过程结束后采用了（//）作为结束符号，告诉 SQL 可以执行了，然后再将结束符还原成默认的（;）。

需要注意的是，如果你用的是 Navicat 工具，那么在编写存储过程的时候，Navicat 会自动设置 DELIMITER 为其他符号，我们不需要再进行 DELIMITER 的操作。

在这里插入图片描述

举例:

创建一个存储类型 get_hero_scores，用来查询某一类型英雄中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值:

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

调用:

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

流控制语句

BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
SET：赋值语句，用于对变量进行赋值。
SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。

CASE：CASE 语句用于多条件的分支判断，使用的语法是下面这样的。

CASE 
	WHEN expression1 THEN ...
	WHEN expression2 THEN ...
	...
    ELSE 
    --ELSE 语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。
REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。