MySQL必知必会总结笔记

布鸽不鸽 Lv4
  2023-06-11 15:33:18 2023-06-11 15:33:18 创建   2023-08-28 18:56 2023-08-28 18:56 更新    2.9k 字  12 分钟  

前言

本文是《MySQL必知必会》的总结笔记，可以用于快速查询MySQL基本操作。其中一些不常用的命令被略过，可以另行学习。
原文地址：https://xuedongyun.cn/post/16749/

基本概念梳理

表（table）

列（column）

行（row）

主键（primary key）

SQL（Structured Query Language）

MySQL简介

  • 一种DBMS（数据库管理系统）
  • 基于客户机-服务器类型

连接

为了连接需要以下信息：

  • 主机名

  • 端口

  • 用户名

  • 口令

常用命令

  • 选择数据库
1
USE crashcourse;
  • 查看数据库
1
SHOW DATABASES;
  • 查看当前数据库的表
1
SHOW TABLES;
  • 查看表的列
1
SHOW COLUMNS FROM customers;
1
DESCRIBE customers;
  • 其他命令
STATUS;``` 显示广泛的服务器状态信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

```SHOW CREATE DATABASE;``` ```SHOW CREATE TABLE;``` 显示创建特定数据库或表的MySQL语句

```SHOW GRANTS;``` 显示用户的安全权限

```SHOW ERRORS;``` ```SHOW WARNINGS;``` 显示服务器错误或警告信息



## 检索数据

- 检索单个列

```mysql
SELECT prod_name FROM products;
  • 检索多个列
1
SELECT prod_id, prod_name from products;
  • 检索所有的列
1
SELECT * FROM products;
  • 检索不同的行
1
SELECT DISTINCT vend_id FROM products;
  • 限制结果
1
2
3
SELECT prod_name
FROM products
LIMIT 5;

​ 只返回前5个

1
LIMIT 5, 5;

​ 第一个数：开始位置

​ 第二个数：检索的行数

1
LIMIT 4 OFFSET 3;

​ MySQL 5开始的新语法，等价于LIMIT 3, 4

  • 完全限定表名，数据库
1
2
SELECT products.prod_name
FROM crashcourse.products

排序检索数据

  • ORDER BY子句
1
2
3
SELECT prod_name
FROM products
ORDER BY prod_name;
  • 按多个列排序
1
ORDER BY prod_price, prod_name;
  • 指定排序方向
1
ORDER BY prod_price DESC;
1
ORDER BY prod_price DESC, prod_name;

​ 默认ASC

过滤数据

  • where子句
1
2
3
SELECT prod_name, prod_proce
FROM products
WHERE prod_price BETWEEN 10 AND 20.5;

  • 支持的操作符

    • =, <>, !=, >, >=, <, <=, BETWEEN

    • BETWEEN包括两端

  • 空值检查

1
2
3
SELECT prod_name
FROM products
WHERE prod_price IS NULL

数据过滤

  • AND操作符
1
WHERE vend_id = 1003 AND prod_price <= 10;
  • OR操作符
1
WHERE vend_id = 1002 OR vend_id = 1003;
  • 计算次序：AND>OR, 可以加()解决
  • IN操作符
1
WHERE vend_id IN (1002, 1003)
  • NOT操作符
1
WHERE vend_id NOT IN (1002, 1003)

通配符进行过滤

  • LIKE操作符，在where中使用

  • %表示任何字符，出现任意次数

1
WHERE prod_name LIKE 'jet%'
  • _表示任何字符，次数限定为1
1
WHERE prod_name LIKE 'jet_'
  • 使用技巧
    • 其他操作符能达到相同目的，就不要使用通配符
    • 除非绝对必要，尽量不要用在搜索模式的开始处，搜索起来是最慢的

使用正则表达式

  • 基本字符匹配
1
WHERE prod_name REGEXP '1000'
1
WHERE prod_name REGEXP '.000'

  • 正则表达式只要出现了就会匹配，当然也可以使用^和$定位符来匹配整个列值

  • 正则表达式不区分大小写，可以使用BINARY关键词开启区分大小写

1
WHERE prod_name REGEXP BINARY 'JetPack .000'
  • 进行OR搜索
1
WHERE prod_name REGEXP '1000|2000|3000'
  • 匹配几个字符之一
1
WHERE prod_name REGEXP '[123] ton'
1
WHERE prod_name REGEXP '[1|2|3] ton'
  • 匹配排除几个字符
1
WHERE prod_name REGEXP '[^123] ton'
  • 匹配范围
1
WHERE prod_name REGEXP '[1-9] ton'
1
WHERE prod_name REGEXP '1 [a-z]on'
  • 转义特殊符号
    • \\- \\. \\\等等
    • 元字符
      • \\f 换页
      • \\n 换行
      • \\r 回车
      • \\t 制表
      • \\v 纵向制表
    • 使用两个\：MySQL自己转义一个，正则表达式转义另一个
  • 匹配字符类
    • [:alnum:]：任意字符和字母
    • [:alpha:]：任意字符
    • 更多此处略去
  • 重复元字符
    • *：0或多
    • +：1或多
    • ?：0或1
    • {n}：指定数目
    • {n,}：不少于指定数目
    • {n,m}：指定范围
1
WHERE prod_name REGEXP '\\([0-9] stricks?\\)'
1
WHERE prod_name REGEXP '\\([[:digit:]]{4}\\)'
  • 定位符
    • ^：文本的开始
    • $：文本的结尾
    • [[:<:]]：词的开始
    • [[:>:]]：词的结束
1
WHERE prod_name REGEXP '^[0-9]{4}'
  • 使用简单正则表达式测试，返回0或1
1
SELECT 'hello' REGEXP '[0-9]'

创建计算字段

  • Concat()函数拼接
1
2
3
SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;
  • Trim(), RTrim(), LTrim()去除空格
1
2
3
SELECT Concat(vend_name, '(', Trim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
  • 使用别名
1
2
3
SELECT Concat(vend_name, '(', Trim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
  • 执行算术计算
1
2
3
SELECT prod_id, quantity*item_price as expanded_price
FROM orderitems
WHERE order_num = 20005;

支持的基本操作符：+, -, *, /

使用数据处理函数

函数没有SQL可移植性强

文本处理函数

  • Left(), Right()
    • Left('xdy', 2): 返回’xd’
  • Length()
  • Locate()
    • Locate('dy', 'xdy', 0): 返回子串第一次出现的位置
  • Lower(), Upper()
  • LTrim(), RTrim(), Trim()
  • SubString()
  • Soundex()
    • Soundex(lee) = Soundex(lie): 函数返回语音表示

日期和时间处理函数

  • 具体函数略

  • MySQL日期必须是yyyy-mm-dd的格式

  • 基本的日期选择

1
2
3
SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01'

order_date数据类型为datatime, 此时匹配的其实是’2005-09-01 00:00:00’

正确的做法应该是：

1
WHERE Date(order_date) = '2005-09-01'
  • 匹配2005年9月的订单
1
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'
1
WHERE Year(order_date) = 2005 AND Month(order_date) = 9

数值处理函数

  • Abs()
  • Cos(), Sin(), Tan()
  • Exp()
  • Mod()
  • Pi()
  • Rand()
  • Sqrt()

汇总数据

  • AVG()函数
1
SELECT AVG(prod_price) AS avg_price

AVG()函数只能用于单列，忽略NULL的行

  • COUNT()函数
1
2
SELECT COUNT(*) as num_cust
FROM customers

COUNT(*)统计行的数量，不忽略NULL

1
2
SELECT COUNT(cust_email) AS num_cust
FROM customers

COUNT(column)统计具有值的行的数量，忽略NULL

  • MAX()函数，忽略NULL
1
SELECT MAX(prod_price) AS max_price
  • MIN()函数，忽略NULL
1
SELECT MIN(prod_price) AS max_price
  • SUM()函数，忽略NULL
1
SELECT SUM(quantity*item_price) AS total_price
  • 汇聚不同的值，DISTINCT的使用
1
SELECT AVG(DISTINCT prod_price) AS avg_price

DISTINCT必须指定列名，COUNT(DISTINCT *)肯定不可以

  • 组合聚集函数
1
2
3
4
5
SELECT COUNT(*) AS num_items,
		MIN(prod_price) AS price_min,
		MAX(prod_price) AS price_max,
		AVG(prod_price) AS price_avg
FROM products;

分组数据

  • 创建分组
1
2
3
SELECT vend_id, COUNT(*) as num_prods
FROM products
GROUP BY vend_id;
  • GROUP BY的一些规则
    • 可以包含任意列，能对分组进行细致的嵌套
    • 嵌套时，将在最后的分组上汇总
    • GROUP BY的列，必须在SELECT中（不能是聚集函数）。如果使用了表达式，GROUP BY中也必须使用表达式，不能使用别名
    • 除聚集函数外，别的列都必须在GROUP BY中出现
    • 分组列中NULL将分为一组
    • 必须在WHERE子句之后
  • 过滤分组
1
2
3
4
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

WHERE过滤行而HAVING过滤分组

  • select子句顺序
1
2
3
4
5
6
7
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

使用子查询

  • 使用子查询进行过滤
1
2
3
4
5
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                   FROM orderitems
                   WHERE prod_id = 'TNT2');

子查询一般与IN等操作符一起使用，但也可以用于等于=，不等于<>等等

  • 作为计算字段使用子查询
1
2
3
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

联结表

外键：表的某一列，为其他表的主键

  • 创建联结
1
2
3
4
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

没有where语句的话，就返回笛卡尔积了

这种联结方式也叫等值联结，内部联结

  • 可以使用另一种方式
1
2
3
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
  • 联结多个表
1
2
3
4
5
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
	AND orderitems.prod_id = products.prod_id
	AND order_num = 20005;

创建高级联结

  • 自联结
  • 自然联结
  • 外部联结
1
LEFT OUTER JOIN // 从左边选择全部行
1
RIGHT OUTER JOIN // 从右边选择全部行
  • 使用带聚集函数的联结
1
2
3
4
5
6
SELECT customers.cust_name, 
	customers.cust_id, 
	COUNT(orders.order_num) AS num_order
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

组合查询

  • 使用UNION
1
2
3
4
5
6
7
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

在这个简单例子下，UNION比WHERE子句更复杂

但对于更复杂的过滤条件，或从多个表中检索数据，使用UNION可能更简单

  • UNION规则
    • 两条及以上SELECT语句组成，语句之间用UNION分割
    • 每个查询必须包含相同的列，表达式，或聚集函数（次序未必相同）
    • 列数据类型必须兼容，必须是可以隐式转换的类型
  • 包含或取消重复的行
    • UNION 默认去重
    • UNION ALL 不去重
  • 对组合查询结果排序
1
2
3
4
5
6
7
8
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
ORDER BY vend_id, prod_id

只能在最后排一次

全文搜索

插入数据

  • 插入完整的行
1
2
INSERT INTO customers
	VALUES(NULL, 'Zhou Jielun', 'Taipei', NULL, NULL);

如果某列没有值，必须指定NULL

  • 更安全的做法，指定列
1
2
INSERT INTO customers(cust_name, cust_city)
	VALUES('Zhou Jielun', 'Taipei');
  • 插入多个行
1
2
3
4
5
6
7
8
9
10
11
// 多条语句
INSERT INTO customers(cust_name, cust_city)
	VALUES('Zhou Jielun', 'Taipei');
INSERT INTO customers(cust_name, cust_city)
	VALUES('Wang Feng', 'Beijing');
INSERT INTO customers(cust_name, cust_city)
	VALUES('Yang Miao', 'Shanghai');
	
// 可以组合各条语句
INSERT INTO customers(cust_name, cust_city)
	VALUES('Zhou Jielun', 'Taipei'),('Wang Feng', 'Beijing'),('Yang Miao', 'Shanghai');
  • 插入检索出的数据
1
2
INSERT INTO customer(cust_id, cust_name, cust_city)
SELECT cust_id, cust_name, cust_city FROM customer_new

更新和删除数据

  • 更新行
1
2
3
4
UPDATE customer
SET cust_city = 'Chongqing'
	cust_email = '432431@gamil.com'
WHERE cust_id = 12345;
  • 删除行
1
2
DELETE FROM customer
WHERE cust_id = 12345;
  • 更新和删除的原则
    • 记得带上WHERE，除非真的希望对所有行进行更改
    • 保证每个表都有主键
    • 操作前先用SELECT测试一下

创建和操作表

  • 创建表
1
2
3
4
5
6
CREATE TABLE customers IF NOT EXISTS (
	cust_id		int			NOT NULL	AUTO_INCREMENT,
    cust_name	varchar(50)	NOT NULL,
    cust_city	varchar(50)	NULL DEFAULT 'Beijing',
    PRIMARY KEY (cust_id)
)ENGINE=InnoDB;

  • 使用NULL值

    • 每个表列或是NULL列，或是NOT NULL列，在表的创建时规定
    • NULL为默认设置

  • 主键

    • 主键必须唯一
    • 主键可以是多个值的组合

  • AUTO_INCREMENT

    • 每个表只能有一个AUTO_INCREMENT
    • 它必须被索引（如，通过使他成为主键）
    • 可以使用last_insert_id()函数获得最后一个AUTO_INCRMENT的值，用于后续
    1
    SELECT last_insert_id()

  • 指定默认值

    • 尽量只用默认值而不是NULL值

  • 引擎

    • InnoDB：可靠的事务处理引擎
    • MyISAM：性能极高的引擎，支持全文搜索，不支持事务
    • MEMORY：功能等同于MyISAM，但存储在内存，速度极快，适合临时表

  • 更新表

    • 理想状态下，表中有数据就不该被更新了
    • 添加列
    1
    2
    ALTER TABLE customers
    ADD vend_phone VARCHAR(20);
    • 删除列
    1
    2
    ALTER TABLE customers
    DROP COLUMN vend_phone;

  • 删除表

1
DROP TABLE customers
  • 重命名表
1
RENAME TABLE customer TO customer2

使用视图

  • 略

使用存储过程

  • 略

使用游标

  • 略

使用触发器

  • 创建触发器
1
2
CREATE TRIGGER newproduct AFTER INSERT ON customers
FOR EACH ROW SELECT 'product added';

  • BEFORE触发器失败，后续SQL语句也不会执行

  • BEFORE触发器或SQL语句失败，AFTER触发器也不会执行

  • 删除触发器
1
DROP TRIGGER newproduct
  • INSERT触发器
1
2
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT NEW.order_num;

可引用一个虚拟的表NEW访问被插入的行

  • BERFORE INSERT时，允许修改插入的值

  • 对于AUTO_INCREAMENT的列，BERFORE INSERT为0，AFTER INSERT为实际值

  • DELETE触发器
1
2
3
4
5
6
CREATE TRIGGER newproduct BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archieve_order(prod_id, prod_name)
	VALUES(OLD.prod_id, OLD.prod_name);
END;

  • 可以引用虚拟的OLD表，访问被删除的值

  • OLD中的值都是只读的

  • 使用BEGIN END的好处是能容纳多条SQL语句

  • UPDATE触发器
1
2
3
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW 
SET NEW.vend_state = Upper(New.vend_state);
  • OLD虚拟表访问修改前的值，NEW虚拟表访问修改后的值
  • BEFORE UPDATE时，NEW的值可以被更改
  • OLD中的值都是只读的

管理事务处理

  • 略

全球化和本地化

字符集：字母和符号的集合

编码：字符集成员的内部表示

校对：指定字符如何比较的指令

  • 略

安全管理

不应当在日常MySQL中使用root

  • 获取用户账号列表
1
2
USE mysql;
SELECT user FROM user;
1
2
3
4
5
6
7
8
9
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
4 rows in set (0.00 sec)
  • 创建用户账号
1
CREATE USER xdy IDENTIFIED BY 'password';
  • 重命名用户
1
RENAME USER xdy TO xdy123
  • 删除用户
1
DROP USER xdy;
  • 查看赋予用户的权限
1
SHOW GRANTS FOR xdy;
1
2
3
4
5
6
+---------------------------------+
| Grants for xdy@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `xdy`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

USAGE表示没有任何权限。

MySQL的权限：用户名和主机名结合定义，若不指定主机名，用默认主机名%

  • 授予权限（用root用户授予）
1
2
GRANT SELECT ON project.* TO xdy;
GRANT SELECT, INSERT ON project.* TO xdy;

授予该用户在project数据库上所有表的SELECT权限

  • 撤销权限
1
REVOKE SELECT ON project.* FROM xdy
  • 查看所有的权限
1
show privileges;
  • 更改密码
1
SET PASSWORD FOR xdy = Password('newPassword');

数据库维护

  • 略

改善性能

  • 略
  • 标题: MySQL必知必会总结笔记
  • 作者: 布鸽不鸽
  • 创建于 : 2023-06-11 15:33:18
  • 更新于 : 2023-08-28 18:56:00
  • 链接: https://xuedongyun.cn//post/16749/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
推荐阅读
SpringBoot源码系列（1）：版本仲裁 SpringBoot源码系列（1）：版本仲裁 SpringBoot-核心功能 SpringBoot-核心功能 Spring6学习拾遗 Spring6学习拾遗
推荐阅读
SpringBoot源码系列（1）：版本仲裁 SpringBoot源码系列（1）：版本仲裁 SpringBoot-核心功能 SpringBoot-核心功能
评论
目录
MySQL必知必会总结笔记
  1. 前言
  2. 基本概念梳理
    1. MySQL简介
    2. 连接
    3. 常用命令
  3. 排序检索数据
  4. 过滤数据
  5. 数据过滤
  6. 通配符进行过滤
  7. 使用正则表达式
  8. 创建计算字段
  9. 使用数据处理函数
    1. 文本处理函数
    2. 日期和时间处理函数
    3. 数值处理函数
  10. 汇总数据
  11. 分组数据
  12. 使用子查询
  13. 联结表
  14. 创建高级联结
  15. 组合查询
  16. 全文搜索
  17. 插入数据
  18. 更新和删除数据
  19. 创建和操作表
  20. 使用视图
  21. 使用存储过程
  22. 使用游标
  23. 使用触发器
  24. 管理事务处理
  25. 全球化和本地化
  26. 安全管理
  27. 数据库维护
  28. 改善性能