《MySQL》必知必会

初步认识

什么是SQL？

1. SQL（结构化查询语言）
2. 数据完整性：数据的准确性和可靠性。如，出现主键相同的两个记录、输入格式错误；

数据一致性：事务执行前后状态一致，符合逻辑。转钱。

2. 事务的四大特征ACID：

隔离性：并发执行的事务，中间状态是透明的。

分布式系统的三个指标CAP：一致性、可用性、分区容错性

3. 服务器：与数据文件打交道，提供的软件：MySQL

   客户端：与用户打交道，提供的软件：IDEA

4. 自动增量：在创建新记录时，自动生成唯一值。

查找

DISTINCT和LIMIT如何使用？

1. 现在的MySQL不区分大小写，但最好服从代码规范，所有关键字用大写 。

2. 去重（DISTINCT）：不能部分使用，对所有行都操作

   SELECT DISTINCT vend_id
   FROM products;
   

3. 检索5-9行

   SELECT vend_id
   FROM products
   LIMIT 5,5;
   

   第一行是行0

ORDER BY？

1. 默认升序，降序DESC只用于一行，升序ASC

SELECT prod_id
FROM products
ORDER BY prod_price DESC, prod_name;

WHERE？

1. ORDER BY应位于WHERE之后

SELECT prod_id
FROM products
WHERE prod_price = 2.0
ORDER BY prod_price DESC;

2. <> 与 ！= 都表示不等于
3. BETWEEN操作符：包括开始值和结束值

SELECT prod_name
FROM products
WHERE prod_price BETWEEN 5 AND 10;

4. 空值检查

SELECT prod_name
FROM products
WHERE prod_price IS NULL;

IN与NOT IN？

1. AND比OR优先级高
2. IN操作符：匹配清单

SELECT prod_name
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;

3. NOT关键字：用于其他关键字前，否定

如何用LIKE和通配符过滤数据？

1. 通配符：用于匹配值的一部分的特殊字符，区分大小写！
2. %通配符：匹配任意长度的任意字符，包括0个字符，但不能匹配NULL

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%jet%'

3. _：匹配单个字符

4. 能不用就不用，性能低；必须用也要放在搜索模式后面。

正则表达式的妙用？

1. REGEXP ：不匹配整个列，而是在列值内进行匹配

SELECT prod_id, prod_name
FROM products
WHERE prod_name REGEXP 'jet'

2. |搜索两个串之一

3. []匹配几个字符之一：[123]Ton 与 [1|2|3]Ton 同义

4. 转义符：\\

5. 匹配多个实例

   

   样例：

匹配4个连续数字，也可以’[0-9][0-9][0-9][0-9]’

6. 定位符

样例：

如何拼接字段？

1. CONCAT拼接字段

SELECT CONCAT(name,'(',country,')')
...

2. TRIM删除空格

TRIM(name) #删除name串两边的空格

汇总数据的函数有哪些？

五个聚集函数：都不计算NULL值

AVG COUNT MAX MIN SUM

如何用GROUP BY分组数据？

1. GROUP BY将不同供应商分组

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 3;

2. WHERE行级过滤，HAVING组级过滤
   详解group by和order by

高级联结有哪些？（重点）

1. 内联结（等值连接）：最常见的一种情况，一个表的外键连接另一个表的主键

INNER JOIN 
ON

2. 自联结：同一个表，查询某供应商生产的其他产品

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTN';

3. 自然联结：使相同的列只返回一次。其实我们大多数情况都使用自然连接的内连接，或许根本不会用到不是自然连接的内连接。

4. 外部联结：包含在相关表中没有关联行的行，如：

   对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders  #选择左边表的所有行
ON customers.cust_id = orders.cust_id;

用UNION组合查询的条件？

1. UNION：组合查询，每个查询必须包括相同的列、表达式或聚集函数

增删改

1. 插入

INSERT INTO 表名（列名）
VALUES （值）

2. 删除

DELETE FROM 表名
WHERE

3. 更新

UPDATE 表名
SET 
WHERE 

4. 使用WHERE子句前，先SELETE确保查询的记录正确。
5. 使用默认值（DEFAULT），而不是NULL

优化

为什么要使用视图？

1. 为什么要使用视图？

   • 简化复杂的SQL操作，重用它，不必知道基础细节
   • 保护数据，给用户授予表的特定部分的访问权限
   • 更改数据格式和表示

2. 视图的规则和限制

   • 不能使用索引

事务处理的重要语句有哪些？

1. 为什么引入事务？保证数据库的完整性

START TRANSACTION
...
COMMIT TRANSACTION

2. 回滚

ROLLBACK;

3. 保留点

SAVEPOINT delete1;
ROLLBACK TO delete1;

高级SQL特性有哪些？

1. 外键
   • 用于保护数据库的完整性。Order表中的cust_id必须是Customers表中的cust_id
   • 防止意外删除，表之间产生了关联。

CREATE TABLE Orders
(
	order_num INTEGER NOT NULL PRIMARY KEY,
    cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
)

2. 索引：按字母顺序排序的位置列表
   什么是索引？

   • 对检索有效，但增删改反而降低性能。

   CREATE INDEX prod_name_ind
   ON Products(prod_name);
   

3. 电话和邮编不是数字，是字符串。串必须加''，数值不用。

如何进行安全管理？

1. 尽量别用root登录，创建新用户

CREATE USER ben IDENTIFIED BY '1234'

2. 设置访问权限

SHOW GRANTS FOR ben;  //查看访问权限
GRANT SELECT ON products.* TO ben;  //授予读取权限
REVOKE SELECT ON products.* FROM ben;  //撤销读取权限

3. 修改密码

SET PASSWORD = password('1234')

常见的SQL优化方法有哪些？（重点）

SQL优化方法