本文共 6370 字，大约阅读时间需要 21 分钟。

MySQL必知必会笔记

前言

本文是《MySQL必知必会》的总结笔记，可用作快速查询MySQL基本操作。部分不常用命令未包含，可单独学习。

基本概念梳理

• 表 (table)
• 列 (column)
• 行 (row)
• 主键 (primary key)
• SQL (Structured Query Language)

MySQL简介

• MySQL是一种数据库管理系统 (DBMS)。
• 采用客户机-服务器型架构。

连接

连接需要提供以下信息：

• 主机名
• 端口
• 用户名
• 密码

常用命令

• 选择数据库

  USE crashcourse;

• 查看数据库

  SHOW DATABASES;

• 查看当前数据库的表

  SHOW TABLES;

• 查看表的列

  SHOW COLUMNS FROM customers;

• 查看表结构

  DESCRIBE customers;

• 其他命令

  SHOW STATUS;  // 显示服务器状态信息SHOW CREATE DATABASE;  // 查看数据库创建语句SHOW CREATE TABLE;  // 查看表创建语句SHOW GRANTS;  // 查看用户权限SHOW ERRORS;  // 查看服务器错误信息SHOW WARNINGS;  // 查看服务器警告信息

检索数据

• 检索单个列

  SELECT prod_name FROM products;

• 检索多个列

  SELECT prod_id, prod_name FROM products;

• 检索所有列

  SELECT * FROM products;

• 检索不同的行

  SELECT DISTINCT vend_id FROM products;

• 限制结果

  SELECT prod_name FROM products LIMIT 5;  // 只返回前5个

  LIMIT 5, 5;  // 第一个数：开始位置，第二个数：检索的行数

  LIMIT 4 OFFSET 3;  // MySQL 5+新语法，等价于 LIMIT 3, 4

• 完全限定表名和数据库

  SELECT products.prod_name FROM crashcourse.products;

排序检索数据

• 按单列排序

  SELECT prod_name FROM products ORDER BY prod_name;

• 按多个列排序

  ORDER BY prod_price, prod_name;

• 指定排序方向

  ORDER BY prod_price DESC;  // 降序排序

  ORDER BY prod_price DESC, prod_name;  // 按多个列降序排序

• 默认排序方向为 ASC。

过滤数据

• where子句

  SELECT prod_name, prod_proce FROM products WHERE prod_price BETWEEN 10 AND 20.5;

• 支持的操作符：=, <>, !=, >, >=, <, <=, BETWEEN

  SELECT prod_name FROM products WHERE prod_price IS NULL;

• AND 和 OR 运算符

  WHERE vend_id = 1003 AND prod_price <= 10;

  WHERE vend_id = 1002 OR vend_id = 1003;

• IN 和 NOT 运算符

  WHERE vend_id IN (1002, 1003);

  WHERE vend_id NOT IN (1002, 1003);

• LIKE 运算符（用于过滤数据）

  WHERE prod_name LIKE 'jet%';

• LIKE 运算符使用技巧：
  • 不使用LIKE时可以用其他操作符。
  • 避免在LIKE的开始使用通配符，因为这会导致查询变慢。

使用正则表达式

• 基本字符匹配

  WHERE prod_name REGEXP '1000';

• 定位符

  WHERE prod_name REGEXP '^[0-9]{4}';

• 正则表达式字符匹配

  WHERE prod_name REGEXP '[1-9] ton';

• 特殊字符转义

  WHERE prod_name REGEXP '\\([0-9] stricks?\\)';

• 元字符

  WHERE prod_name REGEXP BINARY 'JetPack .000';

• 重复元字符

  WHERE prod_name REGEXP '[1-9] ton';

创建计算字段

• 拼接列值

  SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;

• 去除空格

  SELECT Concat(vend_name, '(', Trim(vend_country), ')') FROM vendors ORDER BY vend_name;

• 使用别名

  SELECT Concat(vend_name, '(', Trim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

• 算术运算

  SELECT prod_id, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

使用数据处理函数

• 文本处理函数

  SELECT Left('xdy', 2) AS left_str FROM dual;

• 日期和时间处理函数

  SELECT Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30' FROM orders;

• 数值处理函数

  SELECT Sqrt(123) AS sqrt_value FROM dual;

汇总数据

• AVG() 函数

  SELECT AVG(prod_price) AS avg_price FROM products;

• COUNT() 函数

  SELECT COUNT(*) AS num_cust FROM customers;

• MAX(), MIN(), SUM() 等函数

  SELECT MAX(prod_price) AS max_price FROM products;

分组数据

• 创建分组

  SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

• 分组规则
  • GROUP BY 的列必须在 SELECT 中出现（除聚集函数外）。
  • 分组列中的 NULL 会被视为一组。
  • GROUP BY 必须放在 WHERE 子句之后。

子查询

• 子查询用于过滤

  SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

• 子查询用于计算字段

  SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders_count FROM customers ORDER BY cust_name;

联结表

• 等值联结

  SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

• 使用 INNER JOIN

  SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

• 联结多个表

  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;

高级联结

• LEFT OUTER JOIN 和 RIGHT OUTER JOIN

  LEFT OUTER JOIN  // 从左边选择全部行

  RIGHT OUTER JOIN  // 从右边选择全部行

• 带聚集函数的联结

  SELECT customers.cust_name, 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

  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 规则：
  • 两条及以上 SELECT 语句组成，语句之间用 UNION 分割。
  • 每个查询必须包含相同的列、表达式或聚集函数。
  • 列数据类型必须兼容，可以隐式转换。
• 去重和不去重

  UNION 默认去重

  UNION ALL 不去重

• 排序

  SELECT ... FROM ... ORDER BY ... LIMIT ...

全文搜索

• 使用 LIKE 运算符

  WHERE prod_name LIKE 'jet%';

• 使用正则表达式

  WHERE prod_name REGEXP 'JetPack .000';

插入数据

• 插入完整的行

  INSERT INTO customers VALUES (NULL, 'Zhou Jielun', 'Taipei', NULL, NULL);

• 安全插入（指定列）

  INSERT INTO customers(cust_name, cust_city) VALUES ('Zhou Jielun', 'Taipei');

• 插入多个行

  INSERT INTO customers(cust_name, cust_city) VALUES ('Zhou Jielun', 'Taipei'), ('Wang Feng', 'Beijing'), ('Yang Miao', 'Shanghai');

• 插入检索数据

  INSERT INTO customer(cust_id, cust_name, cust_city) SELECT cust_id, cust_name, cust_city FROM customer_new;

更新和删除数据

• 更新行

  UPDATE customer SET cust_city = 'Chongqing', cust_email = '432431@gmail.com' WHERE cust_id = 12345;

• 删除行

  DELETE FROM customer WHERE cust_id = 12345;

• 更新和删除原则：
  • 带上 WHERE 子句，除非确实希望对所有行进行修改。
  • 确保表有主键。
  • 操作前先测试。

创建和操作表

• 创建表（示例）

  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;

• 表的主键和 AUTO_INCREMENT

  SELECT last_insert_id();

• 添加和删除列

  ALTER TABLE customers ADD vend_phone VARCHAR(20);

  ALTER TABLE customers DROP COLUMN vend_phone;

• 删除表

  DROP TABLE customers;

• 重命名表

  RENAME TABLE customer TO customer2;

使用视图

• 视图简介
  • 视图是虚拟表，基于一系列 SELECT 语句。
  • 视图可以简化数据访问。

使用存储过程

• 存储过程是可重用的 SQL 语句组。

  CREATE PROCEDURE sp_get_product_list ()BEGIN  SELECT * FROM products;END;

使用游标

• 游标用于处理结果集。

  CREATE PROCEDURE sp_get_product_list ()BEGIN  DECLARE done INT.Default 0;  DECLARE current_record VARCHAR(100);  DECLARE product_id INT.Default 0;  GET THE FIRST (100) ROWS FROM products INTO :current_record;  WHILE done

  （示例简化，实际实现需要更多步骤。）

使用触发器

• 触发器在特定事件（如插入、更新、删除）触发存储过程或函数。

  CREATE TRIGGER newproduct AFTER INSERT ON customersFOR EACH ROWSELECT 'product added';

• BEFORE 和 AFTER 触发器：
  • BEFORE：可以修改数据，但不能插入或更新。
  • AFTER：无法修改原始数据。

管理事务处理

• 事务处理简介
  • 事务可以确保一系列 SQL 语句的原子性、持久性和一致性。
  • 使用 InnoDB 引擎支持事务。

全球化和本地化

• 字符集、编码和校对
  • 字符集：字母和符号的集合。
  • 编码：字符在存储和传输中的内部表示。
  • 校对：指定字符如何比较的指令。

安全管理

• 用户管理

  CREATE USER xdy IDENTIFIED BY 'password';

• 查看用户权限

  SHOW GRANTS FOR xdy;

• 授予和撤销权限

  GRANT SELECT ON project.* TO xdy;

  REVOKE SELECT ON project.* FROM xdy;

• 修改密码

  SET PASSWORD FOR xdy = Password('newPassword');

数据库维护

• 数据库维护包括备份、恢复和优化。

改善性能

• 优化数据库设计
  • 分析查询性能。
  • 优化索引。
  • 选择合适的存储引擎。
• 使用查询缓存器。
• 分析和优化慢查询。

通过以上内容，可以快速掌握MySQL的基本操作和常用命令。

转载地址：http://qydfk.baihongyu.com/