MYSQL 查询自动补零

转载

mob6454cc780924 2024-07-24 16:22:55

文章标签 MYSQL 查询自动补零数据库 mysql mysql必知必会存储过程 文章分类 MySQL 数据库

《MySQL必知必会》学习笔记-查漏补缺

查询相关

1.DISTINCT关键字引用于所有的列而不是仅仅前置它的列
2.LIMIT限制输出的结果

例如LIMIT 3表示只显示查询结果的3行， LIMIT的行数从0开始有行0
若为LIMIT 3 4 则表示从行3开始输出4个结果，其同 LIMIT 4 OFFSET 3

3.DESC,ASC关键字只应用于直接位于其前的列
4.ORDER BY可以对多列进行排序
5.使用ORDER BY 和 LIMIT组合可以找到一列中最大或最小值
6.ORDER BY 必须位于 WHERE 之后
7.<>和！=都表示不等于
8.MySQL在执行匹配时默认不区分大小写
9.BETWEEN 匹配包括开始值和结束值
10.“不匹配”不等于“NULL”因此，其不会返回NULL所在的行，即若要找出某个表中价格小于3的行，且表中某些行为空，此时若用price<3则不会返回那些价格为空的行。
11.AND的优先级大于OR
12.为什么要使用IN操作符？

1.IN操作符更简单直观；
2.次序更容易管理；
3.IN 比 OR更快；
4.可以包含其它SELECT字句，动态建立WHERE字句。

13.通配符不能匹配NULL，%代表0个或者多个任意字符
14.使用通配符技巧：

1.除非必要，否则不要使用；
2.尽量不要将通配符放在开头，这样最慢；
3.通配符放置位置很关键，放置是要仔细检查

15.LIKE 与 REGEXP的区别：

LIKE匹配整个列,需要整个列与模式匹配才会得到匹配结果，而REGEXP在列值内进行匹配，只要列内出现匹配的子串，都会返回结果，LIKE若要实现局部匹配需要利用通配符。
REGEXP不区分大小写，若要区分需要用到BINARY关键字，如WHERE name REGEXP BINARY ‘JetPack.1000’

16.用SELECT语句检查正则表达式，如

SELECT ‘hello’ REGEXP ‘[0-9]’,其输出结果为0；
作用：验证‘hello’是否满足正则表达式定义的模式

17.拼接函数 Concat() 拼接多个字段和字符如

SELECT Concat(name,’(’,age,’)’) From school;从school表中拼接姓名和年龄并将年龄用括号括起来

18.去除空格函数LTrim() RTrim() Trim()分别为去掉值左边的空格，右边的空格，和左右两边的空格
19.SQL支持别名，通过在列名，计算列，表名后跟 “AS +别名”实现别名功能
20.SELECT的其它作用进行测试，如之前的测试正则表达一样。还可有如下用法：

SELECT 3*2
SELECT Trim(‘ 123 ’);
SELECT NOW();
等等

函数

21.常用文本处理函数

Soundex()函数进行字符串发音比较，一般用于WHERE字句中，判断列中的字符是否与比较的字符发音类似

22.常用日期和时间处理函数

23.在进行日期比较是一个好的习惯是将日期函数作用在表中表示日期的那一列上再进行比较，其比较的日期格式最好为yyyy-mm-dd，这种格式可以避免歧义。
例子：
1.直接字符比较

分析：可行但不可靠，若order_date中还包含时间则得不到所要结果

2.利用日期函数作用后再比较

分析：不用考虑order_date的格式问题

3.检索一个时间段

方式一:

方式二：

.

24.常用数值处理函数

25.常用聚集函数

补充说明：

1.AVG()只能作用于单列，如果要求多列的均值，则要用多个AVG()函数；

2.AVG会忽略NULL行，即求均值时不计算在内；

3.COUNT(*)不会忽略NULL行，而COUNT(col)则会忽略空行；

4.MAX()，MIN()用于返回任意列的最大值，不限于数值，还包括日期数据，文本数据，若作用于文本数据，则返回最后一行或最前面一行,且都忽略空值；

5.SUM()同样忽略空值，且可作用于多列的算术计算列，即多列的加减乘除；

6.上述所有聚集函数都可以与DISTINCT 组合使用以得到去重后再聚集的效果。

7.DISTINCT关键字放置在函数的括号中，且必须放置在列名前。

8.DISTINCT不能用于COUNT(*),DISTINCT必须与列名配合使用，不能和计算或表达式连用；

9.多个聚集函数可以在一个SELECT中使用；

10.在使用聚集函数时一般都会为计算结果给一个别名。

MySQL分组

26.分组子句 GROUP BY， HAVING
27.

28.GROUP BY可以与 WITH ROLLUP一起使用，可以得到每个分组以及每个分组的级别。
29.几乎所有的WHERE子句都可用HAVING子句进行替代，且WHERE过滤行，HAVING过滤组，或者说，WHERE分组前过滤，HAVING分组后过滤。
30.SELECT子句顺序
SELECT —> FROM —>WHERE —> GROUP BY —> HAVING —> ORDER BY —> LIMIT
31.子查询不仅可以放到WHERE子句中通过IN实现，也可放到SELECT子句中作为计算列实现，通过小括号将查询子句括起来如下：

32.建立子查询的一种技巧，先从最内层子查询开始，得到查询结果后，将查询结果作为外层循环的条件，进行外层循环，直到正确，再重复，直到整个查询完成，这样做虽然开始比较耗时，但可很大几率减少错误，从而大大减少错误发生时去查找调试的时间。

联结

33.使用联结是为了通过以条查询语句返回多个表的内容。
34.联结可以通过WHERE子句实现。但联结也有其专门的句法如：

A INER JOIN B ON condition A表内联结B表

35.联结类型：

内联结（等值联结）：相当于多张表进行笛卡尔集，找到满足条件的行，然后去除了那些查询列值为空的行
自联结：单个表自己联结自己
自然联结：相当于内联结去除重复的列
外部联结：联结表中关联了那些没有关联行的行，即进行笛卡尔集，找到满足条件的行，同时那些查询列值为空的也计算在内。

36.联结和子查询完全都可实现多表查询，具体测试，哪种性能好选哪种。
37.组合查询利用UNION联合多个具有相同查询列的多个SELECT语句，并且输出结果合并在一起，且去除了多个SELECT查询的重复行，如果不要去除重复行，则使用UNION ALL,其实UNION完成的工作和WHERE子句使用OR完成的功能类似。
38.对组合查询结果进行排序 ORDER BY只能放在最后一条查询语句后，不能有多个ORDER BY子句，且不能对其中一个查询使用升序，另一个查询使用降序。

全文本索引

39.MySQL的两种常用的数据库引擎MyISAM和InnoDB只有前者支持全文本索引。
40.使用LIKE关键字和正则表达式都可以进行文本的匹配，但这些机制都存在一些问题如：

1.随着行数增加性能下降很快；
2.很难做到明确控制哪些匹配哪些不匹配；
3.不能提供智能化选择结果。

而全文本索引则可以解决上述问题，MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行，这样MySQL可以快速有效地决定哪些词匹配，哪些不匹配，它们的频率等。

41.启用全文本索引：
一般在创建表的时候就启用全文本索引，通过将FULLTEXT(col_name)加到表的所有列字段最后即可。FULLTEXT可以索引单列也可以索引多列，且在表的最后指定引擎为InnoDB。如下所示：

42.不要在导入数据时使用FULLTEXT,应该先导入数据后再通过修改表的形式定义FULLTEXT,这样可以加快数据导入的速度。
43.使用全文本索引
在WHERE子句中使用Match()指定被搜索的列，Against()指定要使用的搜索表达式

例子

44.全文本索引搜索时不区分大小写，除非使用BINARY形式。
45.使用查询扩展

查询扩展会放宽全文本的搜索范围，利用查询扩展会返回可能相关的结果，即使它们并不精确包含所查询的词。使用查询扩展的方法：在Against(‘词’ WITH QUERY EXPANSION)。

46.布尔文本搜索

布尔搜索即使没有定义索引可以进行搜索，但这是一种非常缓慢的操作
布尔文本搜索可以做到以下细节
1.匹配词；2.要排斥的词；3.排列提示，优先级等
布尔文本搜索的使用，在Against(‘词’ IN BOOLEAN MODE);
全文本布尔操作符

例子

插入数据

47.插入语法：

INSERT INTO table_name(col1,col2,…)VALUES(val1,val2,…);

48.不指出列名的方式插入数据时，所插入的数据必须完全和表中列的顺序一致，且这种方式不安全，因为若表的结构改变，那么该插入语句会报错。因此在插入数据时最好指明哪些列插入。若某列数据是自动增长型的，可以使用NULL填充位置，不需要直接列出数据。
49.当数据库的主要操作为查询时，可以降低插入语句的优先级，通过在INSERT 和INTO关键字中间加入LOW_PRIORITY，即 INSERT LOW_PRIORITY INTO
50.利用单个插入语句插入多条数据，将多个值组用小括号括起来用逗号分隔即可，且这种方式比用多条INSERT语句要快。
51.插入检索出来的数据，即INSERT语句与SELECT语句一起使用，只要INSERT语句的列表与SELECT查询出来的数据个数和类型匹配即可，不需要列名相同。实现时将SELECT子句替代VALUES子句即可。
更新和删除数据
52.更新语法

UPDATE table_name
SET col_name = value;
WHERE conditions;

53.在更新表示一定注意不能省略WHERE子句，否则会更新所有行。
54.更新多个列时，只需多个类似赋值的语句用逗号分隔即可，不需多个SET关键字。
55.在UPDATE语句中可以使用子查询，即在WHERE子句中使用子查询
56.在更新多行出现错误时，整个更新操作会取消，若要即使错误也更新可使用IGNORE关键字。如UPDATE IGNORE talbe_name;
57.删除某个列的值，可将其设置为NULL;
58.删除行语法

DELETE FORM table_name
WHERE condition；

59.同时删除操作中的WHERE子句不能省略，否则删除整个表的所有行，DELETE是删除行，若要删除某个列的值，使用UPDATE
60.更快的删除整个表使用TRUNCATE而不是DELETE,其实际为删除原表再重建一个同样的表。
61.在删除数据前通过SELECT先查看要删除的数据，进行一次验证，以免造成数据的丢失。

创建和操纵表

62.创建表的语法

CREATE TABLE table_name(
col1 type attribute,
col2 type attribute,
col3 type attribute
PRIMARY KEY(col1,col2,..);
)ENGINE=engine_name;
其中 attribute 可为 NULL,NOT NULL, AUTO INCREMENT, DEFAULT value.

63.每个表只允许一个AUTO_INCREMENT列，而且它必须被索引。如通过使其为主键。
64.可以通过last_insert_id() 函数获得最后一个AUTO_INCREMENT的值，让后用于后续的SQL语句。
65.在很多情况下使用默认值而不是NULL.
66.常见的引擎 1.InnoDB（支持事务）；2.MEMORY(等同于MyISAM,数据存储在内存)；3.MyISAM(不支持事务，但支持全文搜索)
67.引擎可以混用，但是外键不能跨引擎。
68.修改表语法

ALTER TABLE table_name
ADD col_name type; 添加列
DROP COLUMN col_name; 删除列
ADD CONSTRAINT fk_name 添加外键
FOREIGH KEY (fk_col) REFERENCES des_table_name(des_col);
作为外键的列外键引用的表引用的列

69.复杂表的更改一般通过重建表实现，它一般需要如下过程

1.用新列布局创建新表
2.使用INSERT SELECT从旧表复制数据
3.检验新表数据
4.重命名旧表
5.用旧表名重命名新表
6.根据需要重建触发器，存储过程，外键，索引等

70.在使用ALTER TABLE修改表时最好先备份
71.删除表 DROP TABLE table_name;
72.重命名表 RENAME TABLE old_name TO new_name;可一次重命名多个表。

视图

73.为什么是用视图？

74.视图的规则和限制？

75.使用视图

视图其实是对查询语句的一种封装，创建视图的操作只需在要封装的查询语句前加入如下语句即可
创建视图：CREATE VIEW view_name AS 查询语句
删除视图：DROOP VIEW view_name；

存储过程

76.为什么要使用存储过程？

简单安全高效

77.执行存储过程：CALL procedure_name(参数列表)；即使没有参数列表也许加上小括号，执行存储过程相当于函数调用。
78.创建存储过程：

CREATE PROCEDURE procedure_name(参数列表)
BEGIN
存储过程体
END;

79.临时更改命令行实用程序分隔符：

mysql默认语句分隔符为“；”，而命令行实用程默认的分隔符也为“；”，故当命令行对存储过程进行解释的时，“；”将不再作为存储过程的一部分，此时就会使得存储过程中没有分隔符而出现SQL语法错误，故在创建存储过程时，需要临时变更命令行使用程序的分隔符，如下使用DELIMITER进行变更
DELIMITER // 在创建存储过程之前对其进行变更,将//定义为命令行的分隔符
CREATE PROCEDURE procedure_name(参数列表)
BEGIN
存储过程体,此时内部的SQL语句仍然使用“；”作为分隔符
END // 存储过程外，使用“//”为分隔符
DELIMITER ; 将命令行分隔符重设为“；”

80.删除存储过程：DROP PROCEDURE procedure_name;
81.变量：内存中一个特定的位置，用于存储数据，所有mysql变量都必须以@开头
82.在创建带参数存储过程时必须说明三个部分: 存储过程参数类型（IN, OUT, INOUT）, 参数名，参数类型（INT CHAR…）
83.在调用存储过程时，若存储过程的参数有输出类型的，那么该参数必须为一个变量,并且使存储过程中有输出型参数也不会显示任何数据，因为该存储过程的结果存储在变量中，若要显示结果，需利用SELECT 语句即SELECT @变量名
84.在存储过程体中若要将查询结果保存到变量中需要使用INTO关键字。如

SELECT col
INTO @变量名
FORM table_name;

85.存储过程中使用 “–”进行注释
86.存储过程中使用 “DECLARE 变量名变量类型” 声明局部变量

游标

87.mysql中的游标只能用于存储过程。
88.游标相当于查询结果集上的一个指针。
89.使用游标的步骤

1.为查询语句定义游标；2.打开游标；3.使用游标；4关闭游标
创建游标：
DECLARE cursor_name CURSOR for + 查询语句。必须在存储过程体中
打开游标
OPEN cursor_name;
使用游标
FETCH cursor_name INTO o; 将游标当前指向的一行数据保存到局部变量o中，o必须提前声明。若要取出多行数据，将该语句放到循环体中即可。
关闭游标
CLOSE cursor_name;

90.DECLARE 语句次序

DECLARE定义的局部变量若在游标或者句柄中使用到了，则其应该在定义的游标或者句柄前定义，且句柄需在游标后定义。

91.循环

REPEAT
循环体
UNTIL 变量 END REPEAT; 变量为真时跳出循环
如何让循环判别变量在结束时为真呢？
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET 变量=1;
上述语句定义了一个句柄，当遇到SQLSTATE 为‘02000’时设置变量为1
而‘02000’表示结果集中已经游标已经移动到了结果集的结尾。

触发器

92.触发器是mysql在响应INSERT DELETE UPDATE语句时执行的一条或一组SQL语句
93.创建触发器应给出的4个信息

1.触发器名；2.触发器关联的表；3.触发器响应的活动；4.触发器何时执行

94.创建触发器：

CREATE TRIGGER trigger_name [AFTER|BEFORE] [DELETE|INSERT|UPDATE] ON table_name；
触发器名指明何时执行指明响应的活动关联的表

95.每个表的每个事件只允许一个触发器，故每个表最多6个触发器，触发器仅仅只支持表，不支持视图或临时表。
96.删除触发器：DROP TRIGGER trigger_name;触发器不支持修改，要修改一个触发器，必须先删除再重新创建。
97.INSER TRIGGER 中可引用一个NEW的临时表，用以访问插入的新行。
98.DELETE 中可以引用一个OLD的临时表，用以访问删除的行。

管理事物处理

99.事务处理保证数据的完整性，它使得成批的MySQL要么全部成功执行，要么全部不执行。
100.标识事物的开始：START TRANSACTION;
101.撤销:ROLLBACK;

ROLLBACK只能在一个事务内才能使用，且只能用来回退INSERT,DELETE,UPDATE,不能回退SELECT,CREATE,DROP

102.一般的SQL操作都是自动提交的，也即隐含提交，但在事物处理中只能手动使用COMMIT提交。
103.隐含事物关闭：当COMMIT和ROLLBACK执行后，事物自动关闭。
104.使用保留点

使得在事物处理时可以部分回退。
保留点的使用：
创建保留点：SAVEPOINT savepoint_name；
回退到保留点：ROLLBACK TO savepoint_name;

105.更改默认提交行为：

SET autocommit=0; 使得MySQL不默认提交。

字符集和校对顺序

106.查看支持字符集

SHOW CHARACTER SET;

107.查看支持校对

SHOW COLLATIONL

108.在创建表时可以为该表指定字符集合校对，也可为表中某列指定字符集和校对
例子：

109.校对对ORDER BY子句排序其非常重要的作用,可以在ORDER BY子句中使用与创建表时不一致的校对进行排序如：

110.COLLATE除了可以用于ORDER BY 还可以用于 GROUP BY,HAVING,聚集函数，别名等。
111.可利用Cast()或Convert()函数使得串可以在字符集中进行转换。

安全管理

112.mysql数据库的用户信息存储在MYSQL数据库的USER表中,user表中有个user列
113.创建用户账号 CREATE USER user_name IDENTIFIED BY ‘password’; 创建用户时不一定需要给出密码
114.重命名表 RENAME USER olduser_name TO newuser_name,也可更新user表。
115.删除用户 DROP USER user_name;
116.新建的用户必须紧接着设置访问权限，否则能登录，但不能操作数据库，也不能查看数据。
117.使用SHOW GRANTS FOR user_name；查看用户的权限。
118.为用户设置权限 GRANT