《Mysql必知必会》知识点整理

发表于 2023/05/15

作者 Feelord

22 分钟阅读

《Mysql必知必会》知识点整理

假定已有数据名 – brycenote
假定已有数据库表 — notebook

基础

语句结束符号 – ;
SQL大小写 – 不区分
对表名列名应 – 小写
书写规范的好处 – 易于调试和阅读
选择数据库 – USE brycenote;
返回当前选择的数据库内可用表的列表 – SHOW TABLES;
显示指定表的列信息 – SHOW COLUMS FROM notebook;
列信息包括 – Field,Type,Null,Key,Default,Extra
在命令实用程序中,显示允许的SHOW语句 – HELP SHOW;
在命令实用程序中,显示(指定的模式信息) – INFORMATION_SCHEMA;
显示服务器的状态概要信息 – SHOW STATUS;
显示指定数据库的创建语句 – SHOW CREATE DATABASE brycenote;
显示指定表的创建语句 – SHOW CREATE TABLE notebook;
显示授予用户的安全权限 – SHOW GRANTS;
显示服务器错误信息 – SHOW ERRORS;
显示服务器警告信息 – SHOW WARNINGS;

检索

检索id列 – SELECT id FROM notebook;
检索的多个列使用__隔开 – ,
检索所有列 – SELECT * FROM notebook;
检索不索要的列通常会 – 降低检索速度,损耗应用程序的性能
检索不同值应使用关键字 – DISTINCT
DISTINCT应用于 – 所有列
限制检索结果的数量就使用关键字 – LIMIT
从第3行开始检索出5行数据的简要语法 – LIMIT 2,5
从第3行开始检索出5行数据的完全语法 – LIMIT 2,5

排序

不明确规定排序顺序就不应假定 – 检索出的数据是有意义的
SQL语句由__构成 – 子句
以指定列进行排序应使用 – ORDERY BY子句
ORDER BY子句既可用要选择的列指定也可以用 – 非选择的列
指定排序为降序使用关键字 – DESC
指定排序为升序使用关键字 – ASC
ORDER BY子句不能单独指定某列的排序方向 – 错
排序方向默认 – ASC
ORDER BY 子句的语法位置在__子句之后 – FROM
LIMIT子句的语法位置在__子句之后 – ORDER BY
找最值可组合使用 – ORDER BY和LIMIT

WHERE

过滤数据使用__子句 – WHERE
等于操作符 – =
不等于操作符 – !=
不等于操作符 – <>
小于操作符 – <
小于等于操作符 – <=
大于操作符 – >
大于等于操作符 – >=
介于两值之间的操作符 – BETWEEN
判空 – IS NULL
判非空 – IS NOT NULL
逻辑与操作 - AND
逻辑或操作 - OR
AND操作优先级__OR操作 – 高于
同时使用AND和OR操作应使用__进行分组 – 括号
指定范围条件的语法 – IN(A,B,C)
IN可以包含其它SELECT语句 – 对
指定范围条件的语法 – NOT IN(A,B,C)
可以与NOT组合取反的关键字 – IN BETWEEN EXIST

通配符

不限长度的通配符 – %
仅限单个字符的通配符 – _
_通配符可以配空 – 错
使用通配符过滤数据应使用关键字 – LIKE
通配符可以出现在匹配模式的中间 – 对
使用通配符过滤数据如何匹配上尾部有空格的数据 – 用%匹配尾空格
通配符比较耗性能,应避免过度使用通配符 – 对
将通配符放在匹配模式开头时的性能要比放在后面时性能要好 – 错

正则

MySQL仅提供了正则表达式的初步支持 – 对
使用正则表达式过滤数据应使用关键字 – REGEXP
LIKE匹配整个列的值 – 对
REGEXP匹配列内值,所以不能匹配整个列的值 – 错
REGEXP如何做到匹配整个列的值 – 使用^和$定位符
使用正则时转义需要使用_个\ – 两
为什么使用两个\ – MySQL和正则表达库要分别解释\

字段

拼接字段使用__函数 – concat
给字段或表取别名时使用关键字 – AS
要执行算术计算应使用 – 算术操作符

函数

函数的可移植性和SQL一样 – 错
进行日期判断应使用Date函数 – 对

字符操作

将文本转为大写的函数 – Upper
将文本转为小写的函数 – Lower
去除空格的函数有 – Trim,RTrim,LTrim
定位子串的位置 – Locate
指定位置(和长度)截取子字符串 – Substring
指定截取关键字与其序号来截取字符串 - SubString_Index
指定长度截取左边的字符串 – Left
指定长度截取右边的字符串 – Right
Soundex函数有助于比较拼写不同但英语__的单词 – 发音相似

时期操作

增加指定天数或周 – AddDate
增加指定时或分 – AddTime
获取当前日期 – CurDate
获取当前时间 – CurTime
获取日期和时间 – Date
计算两个日期之差 – DateDiff
给指定时期增加指定时期种类的数值 – Date_Add
获取格式化日期或时间字符串 – Date_Format
获取日期的对应的号数 – Day
获取日期对应的星期几 – DayOfWeek
获取时间的小时部分数值 – Hour
获取时间的分钟部分数值 – Minute
获取时间对应的月份 – Month
获取当前的日期和时间 – Now
获取时间的秒数 – Second
获取日期的时间部分 – Time
获取日期所在的年份 – Year

数值操作

获取绝对值 – Abs
获取角度余弦 – Cos
获取数的指数 – Exp
取余 – Mod
获取圆周率 – Pi
获取随机数 – Rand
获取角度正统 – Sin
获取数的平方根 – Sqrt
获取角度的正切 – Tan

聚合

获取某列的平均值 – AVG
获取某列的行数 – COUNT
获取某列的最大值 – MAX
获取某列的最小值 – MIN
获取某列值的和 – SUM
COUNT(*)不会统计包含空值的列 – 错
COUNT(notebook)只统计具有值的行 – 对
AVG只能用于单个列且会忽略空值 – 对
MAX只能用于数值类型的列 – 错
DISTINCT可以用于聚合函数中 – 对
使用聚合函数时应尽量取别名 – 对

分组

数据分组应使用__子句 – ORDER BY
GROUP BY子句中不能出现聚合函数 – 对
GROUP BY子句的语法位置在ORDER BY子句之前 – 对
将GOUP BY子句中分组级别进行任意组合的分组应使用 – ROLLUP
过滤分组应使用关键字 – HAVING
HAVING 支持所有WHERE操作符 – 对
HAVING 可以替代WHERE进行使用 – 对
HAVING 是分组后进行过滤,WHERE是分组前进行过滤 – 对
使用聚合函数可以不配合GROUP BY使用 – 错
GOUP BY 的排序是不可靠的,应使用ORDER BY来保证排序 – 对

语法顺序

==SELECT->FROM->WHER->GROUP BY->HAVING->ORDER BY->LIMIT==

子查询

使用子查询应尽量格式化SQL – 对
可以出现子查询的关键字有 – FROM,IN,=,<>
使用子查询时应将父查询和子查询分别测试后再进行嵌套 – 对

联结

联结必须使用JOIN关键字 – 错
多个表不使用联结进行查询得到的结果为 – 笛卡尔积
使用联结尽量使用__语法 – JOIN
JOIN 语法格式 – FROM …JOIN …ON …
联结的表越多,性能___ – 下降越厉害
联结的种类 – 自联结,自然联结,外部联结
UNION会去除重复的行 – 对
UNION ALL会保留重复的行 – 对
要对组合查询结果排序应该将ORDER BY放在第一个SELECT语句之后 – 错

全文本搜索

最常使用的引擎为 – MyISAM InnoDB
MyISAM引擎不支持全文本搜索 – 错
全文本搜索应在建表时指定,使用的关键字是 – FULLTEXT
启用全文本搜索的目标列会被索引 – 对
进行全文本搜索时需同时使用Match函数和 – Against函数
传递给Match函数的值可和FULLTEXT指定的列不同 – 错
除非指定BINARY方式,否则全文本搜索 – 不区分大小写
Match函数和Against函数可以用在 – WHERE子句和SELECT子句
查询扩展可以找出 – 可能相关的结果
查询扩展的语法格式 – Match(…) Against(… WITH QUERY EXPANSION)
布尔文本搜索需要定义FULLTEXT索引 – 错
布尔文本搜索的性能会随着数据量的增加而 – 降低
布尔文本搜索的语法格式 – Match(…) Against(… IN BOOLEAN MODE)

数据插入

使用INSERT语句应尽量给出列名 – 对
有很多索引要更新时，INSERT语句会很 – 耗时
可能通过在INSERT和INTO之间添加关键字__以降低INSERT语句的优先级 – LOW_PRIORITY
使用单条INSERT语句处理多个插入比使用多条INSERT语句__ – 快
插入检出的数据的语法格式 – INSERT INTO … SELECT …

更新删除

使用UPDATE或DELETE时应注意不要__WHERE子句 – 忽略
UPDATE语句中不能使用子查询 – 错
使用UPDATE时可以关联其它表 – 对
批量更新时如果想要忽略期间操作的错误应在UPDATE之后使用关键字 – IGNORE
关联更新的语法有 – 显式关联和自然关联
DELETE可以删除表 – 错
TRUNCATE会逐行删除表的内容 – 错
TRUNCATE会先删除表然后重新创建表 – 对

引擎

需要MySQL支持事务处理时应选用 – InnoDB
MEMORY的数据存储在__, 适合于__ – 内存临时表
MyISAM支持__,但不支持事务处理 – 全文本搜索
外键可以跨引擎 – 错
数据库表的更新不能撤销 – 对
ALTER TABLE时为了保护数据应做一个__ – 完整的备份
删除表使用关键字 – DROP
删除表没有__,也不能__ – 确认撤销

视图

使用视图可以__SQL语句
使用视图可以__复杂的SQL操作
使用视图可以__数据
使用视图可以做到添加和更新数据,只是存在某些限制 – 对
视图不能嵌套 – 错
视图可以和表一起用 – 对
视图不能索引,也不能有关联的触发器或默认值 – 对
创建视图的语句 – CREATE VIEW … AS …
查看创建视图的语句 – SHOW CREATE VIEW viewname
更新视图可以先__再__,或者直接用__语句 – DROP,CREATE,CREATE OR REPLACE VIEW
利用视图可以__复杂的关联 – 简化
尽量创建可__的视图 – 重用
利用视图可以重新___化检索出的数据 – 格式
视图主要用于__ – 数据检索
视图定义中如果存在__操作,则不能进行视图的更新 – 分组,联结,子查询,并,聚集函数,DISTINCT,导出(计算)列

存储过程

存储过程是为以后使用面保存的一条或多条MySQL语句的集合 – 对
存储过程可以保证数据的完整性 - 对
存储过程可以提高性能 – 对
调用存储过程使用关键字 – CALL
创建存储过程的语法是 – CREATE PROCEDURE … BEGIN … END

删除存储过程的语法是 – DROP PROCEDURE …

CREATE PROCEDURE productpricing(
  out p1 DECIMAL(8,2)
  out pH DECIMAL(8,2)
  out pa DECIMAL(8,2)
)
BEGIN
  SELECT Min(prod_price)
  INTO p1
  FROM products;
  SELECT Max(prod_price)
  INTO ph
  FROM products;
  SELECT Avg(prod_price)
  INTO pa
  FROM products;
END;

参数关键字 – IN OUT INOUT

所有MySQL返参变量必须以_开始 – @

CALL productpricing(@pricelow,
                  @pricehigh,
                  @priceaverage);
SELECT @priceaverage
-- 下面同时使用IN(入参)和OUT(回参)
-- Name: ordertotal
CREATE PROCEDURE ordertotal(
  IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
BEGIN
  SELECT Sum(item_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO ototal;
END;
//调用
CALL ordertotal(2005, @total)

列出某存储过程的语法 – SHOW PROCEDURE STATUS procedurename;
列出所有存储过程的语法 – SHOW PROCEDURE STATUS;
附加限制地列出存储过程的语法 – SHOW PROCEDURE STATUS LIKE ‘ordertotal’

游标

当需要在检索出来的行中前进或后退一行或多行应该使用 – 游标
MySQL游标只能用于存储过程 – 对

声明游标的语法格式 – DECLARE cursorname CURSON FOR …

CREATE PROCEDURE processorders()
BEGIN
  DECLARE o INT;
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM Orders;
  OPEN ordernumbers;
    
  FETCH ordernumbers INTO O
    
  CLOSE ordernumbers;
END;

使用DECLARE定义局部变量必须在定义任意游标或句柄之前定义 – 对
需要循环或重复执行语句时的语法 – REPEAT …UNTIL variala END REPEAT

触发器

触发器支持响应的语句分别是 – DELECT,INSERT,UPDATE

 CREATE TRIGGER newproduct  AFTER INSERT ON products
 FOR EACH ROW SELECT 'Product added'

删除触发器的语法格式 – DROP TRIGGER newproduct
访问被插入的行可以使用关键字 – NEW
在 BEFORE INSERT 触发器中NEW的值可以被更新 – 对
对于AUTO_INCREMENT列,插入前是0,插入后是新生成的值 – 对
对数据进行验证和净化可以使用 – BEFORE

在定义DELETE触发器时要访问被删除的行可以通过使用凑字 – OLD

CREATE TRGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO archive_orders(order_num, Order_date, cust_id)
  VALUES(OLD.order_num, OLD.order_date, OLD.cust_id)
END

定义触发器时使用BEGIN END块的好处是能使用多条__ – SQL语句
应该用触发器来保证数据的__ – 一致性
触发器非常适合于创建审计__ –跟踪
MySQL触发器支持CALL语句 – 错

事务

有关事务处理的术语分别有 – 事务回滚提交保留点
开始事务语法 – START TRANSACTION;
回滚的语法 – ROLLBACK;
事务中不能回滚的操作有 – SELECT CREATE DROP
ROLLBACK只能在一个事务处理内使用 - 对
当COMMIT或ROLLBACK语句执行之后,事务会自动关闭 – 对
在事务处理块中提交不会隐藏地进行 – 对
设置保留点的语法 – SAVEPOINT pointname;
回滚到保留点的语法 – ROLLBACK TO pointname;
事务处理中为为理加灵活地进行回退，需要设置更多的 – 保留点
保留点在事务处理完成后自动释放 – 对
设置MySQL不自动提交更改 – SET autocommit=0

性能优化

要遵循MySQL的硬件建议 – 对
要调整内存分配、缓冲区大小时，查看当前设置的命令有 – SHOW VARIABLES; SHOW STATUS;
MySQL是多线程DBMS，查看所有活动线程的命令是 – SHOW PROCESSLIST;
终止某个特定的进程应该使用命令 – KILL
尝试使用关联，并和子查询重新编写出最佳的 – SELECT语句
使用EXPLAIN语句查看MySQL是如何执行一条SELECT语句
存储过程一般都比逐行执行SQL要快，应尽量使用 – 存储过程
应该总是使用正确的 – 数据类型
决不要检索比需求还多的数据，即避免使用 – SELECT *
如果操作支持DELAY可以尝试使用__来异步执行SQL – DELAY
导入数据时应该关闭自动提交 – 对
必须__数据库表以改善数据检索的性能 – 索引
查找重复使用的WHERE和ORDER BY子句,耗时过长的WHERE子句使用的列就是需要__的对象 – 索引
尝试使用UNION语句重构__语句 – OR
对极少被搜索的表,如无必要就不要__它们 – 索引
索引改善数检索的性能,但会损害数据__,__,和__的性能 – 插入,删除,更新
LIKE很慢,能使用__就不要使用__ – FULLTEXT,LIKE
随着表的使用和内容的更改,理想的优化和配置也会 – 改变

数据库

Mysql

本文由作者按照 CC BY 4.0 进行授权