MySQL 基础语法

标签：name 基础 id 语法 stu MySQL WHERE teacher SELECT

SELECT

基础用法
- SELECT name, age FROM user
DISTINCT —— 对检索结果去重
- 应用于所有列而不仅是它后面的一列，所有列的值都不同才会被认为不同。
- SELECT DISTINCT name FROM user
LIMIT —— 返回检索结果的前几条
- 可以指定两个参数，第一个表示 OFFSET，第二个表示要检索出的记录条数，OFFSET 从 0 开始。
- SELECT name FROM user LIMIT 5,5
ORDER BY —— 指定排序
- 如果没有明确规定排序，那么应该认为检索结果的顺序无意义。
- 按名字的字典序升序排序：SELECT name FROM user ORDER BY name
- 先按名字排序，对名字相同的再按年龄升序排序：SELECT name, age FROM user ORDER BY name, age
- DESC
  - 默认排序都是升序，如果要进行降序排列，需要为对应列指定 DESC 关键字。
  - 只应用到直接位于它前面的列名，如果要在多个列上进行降序排序，需要指定多个 DESC。
  - SELECT name, age FROM user ORDER BY name, age DESC
  - 如果同时使用 LIMIT 子句，需要位于 ORDER BY 子句之后。
WHERE —— 指定搜索条件（过滤数据）
- 操作符
  - 大小关系：>, <, >=, <=, =, !=, <>
  - 区间：BETWEEN val1 AND val2
  - 空值检查：IS NULL
  - 执行匹配时默认不区分大小写
- 组合 WHERE 子句
  - AND：WHERE name = 'Mason' AND name = 'Rachael'
  - OR：WHERE name = 'Mason' OR name = 'Rachael'
  - 计算优先级
    - AND 优先级高于 OR
    - AND、OR 组合使用时，应该使用圆括号明确指定计算顺序。
- IN
  - WHERE name IN ('Mason', 'Rachael')
  - IN 操作符和 OR 逻辑运算符的功能相同，但是 IN 执行更快。
  - IN 可以接上子查询
- NOT
  - WHERE name NOT IN ('Mason', 'Rachael')
  - 对过滤条件取反，可以修饰 IN、BETWEEN、EXISTS 条件。
LIKE —— 模糊查询
- %
  - 表示 任何字符出现任意次数，但是不能匹配 NULL。
  - WHERE name LIKE 'mason%' 可以匹配 Mason Li
- _
  - 表示 任何字符出现一次
  - WHERE name LIKE 'maso_' 可以匹配 Mason
- 注意
  - 通配符搜索比其他搜索的效率低，所以如果其他操作符能达到相同目的，应该使用其他操作符。
  - 尽量 不要把通配符放在搜索模式的开始处，放在开始处效率最低。
REGEXP —— 使用正则表达式查询
- 基本字符匹配：WHERE name REGEXP 'mason'
- 指定区分大小写：WHERE name REGEXP BINARY 'Mason'
- OR 匹配：WHERE name REGEXP 'mason|rachael'
- 匹配几个字符之一：[mM]ason
- 匹配范围：[a-z] 匹配任意字母，[0-9] 匹配任意数字
- 转义：\\. 匹配 . 符号
- 匹配字符类：[:alnum:] 匹配字母和数字（[a-zA-Z0-9]），[:alpha:] 匹配字母，[:digit:] 匹配数字，[:space:] 匹配任意空白字符。
- 匹配多个实例：* 使得它前面的字符可选；+ 使得它前面的字符 至少出现 1 次；? 使得它前面的字符 最多出现一次；{n} 指定它前面的字符出现的次数；{n,} 指定它前面的字符最少出现的次数；{n,m} 指定它前面的字符出现次数的范围。
- 定位符：^a 表示以字符 a 开始；a$ 表示以字符 a 结束。

汇总数据

计算字段
- 如果存储在表中的某些字段并非我们想要的直接结果，那么需要从数据库 检索出数据时对字段进行转换、计算。
- concat() —— 拼接若干个字段的值
  - SELECT concat(first, ' ', last) AS fullName FROM user
  - SELECT concat(trim(first), ' ', trim(last)) AS fullName FROM user
- 算术运算
  - SELECT price * count AS cost FROM product
函数
- 文本处理函数
  - lower(str)、upper(str)、ltrim(str)、rtrim(str)、trim(str)
- 日期和时间处理函数
  - 日期和时间采用特殊的格式存储，以便快速地排序或过滤，且节省存储空间。为了「解析」这种格式，变成用户友好的日期和时间字符串，需要用到日期和时间处理函数。
  - CurDate() / CurTime() 返回当前日期（年月日） / 时间（时分秒）
  - Date(date_time) / Time(date_time) 返回日期时间的日期部分 / 时间部分
  - Year(date) / Month(date) / Day(date) 返回日期的年 / 月 / 日
  - Hour(time) / Minute(time) / Second(time) 返回时间的时 / 分 / 秒
- 聚集函数（5 个） —— 在检索时进行汇总
  - AVG()
    - SELECT AVG(age) AS avg_age FROM user WHERE age >= 18
    - 忽略 NULL 值
  - COUNT()
    - count(*) 计数表中所有的行，包括 NULL。
    - count(column) 计数特定列中值不为 NULL 的行。
  - MAX() 、MIN() 、SUM()
  - DISTINCT —— 去重后汇总
    - SELECT AVG(DISTINCT age) AS avg_age FROM user
    - SELECT COUNT(DISTINCT age) AS age_num FROM user
    - SELECT SUM(DISTINCT age) as sum_age FROM user

分组数据

GROUP BY —— 创建分组
- 将数组按指定字段值的不同分成若干组，对每个组进行汇总。
- 例子：汇总每个班的人数：SELECT class_id, count(*) AS num_stu FROM students GROUP BY class_id
- GROUP BY column1, column2 先根据 column1 分组，然后在各个分组内，按照 column2 进一步分组。
- GROUP BY 要将SELECT 语句中的字段都用上。
- NULL 值作为一个分组。
- GROUP BY 子句用在 ORDER BY 子句之前。
HAVING _ 对分组进行过滤
- WHERE 基于行进行过滤，HAVING 子句基于分组进行过滤（基于分组的聚集值进行过滤）。或者说，WHERE 在分组之前进行过滤，HAVING 在分组之后进行过滤。
- 例子：过滤出学生人数达到 30 的班级：GROUP BY class_id HAVING COUNT(*) >= 30
- 对分组排序： ORDER BY 依据的字段为汇总字段。例子——按班级分组，按每个班的人数排序：
```
SELECT class_id, count(*) AS sum_stu 
FROM stu 
HAVING count(*) >= 30 
ORDER BY sum_stu
```

子查询

用于 IN 操作符：下例查找 Rachael 老师负责的学生：

SELECT name FROM stu 
WHERE teacher_id IN (SELECT id FROM teacher 
                     WHERE name = 'Rachael')

用作计算字段：下例查找每个学生对应的老师名字：

SELECT name, (SELECT name FROM teacher 
              WHERE teacher.id = stu.teacher_id) AS teacher_name 
FROM stu

联结

作用：联结用来在一条 SELECT 语句中关联多个表，联合多个表返回一组输出。创建联结需要给出要联结的表以及它们如何关联。

WHERE：通过 WHERE 子句给出联结条件（联结点）

SELECT stu.name AS stu_name, teacher.name AS teacher_name
FROM stu, teacher
WHERE stu.teacher_id = teacher.id

INNER JOIN：内联结，和 WHERE 子句实现的联结一样，都是通过分属于两个表的两个字段之间的相等测试来建立联结，这也称为 等值联结。用特有的 ON 子句指定联结点。
```
SELECT stu.name AS stu, teacher.name AS teacher
FROM stu INNER JOIN teacher
	ON stu.teacher_id = teacher.id
```

自联结：同一个表之间的联结，例如要检索出和 Mason 属于同一个老师的所有学生，可以使用子查询，也可以使用自联结来实现。使用自联结时，需要给表指定两个不同的别名，以便在联结条件中引用。

# 使用子查询
SELECT name FROM student
WHERE teacher_id = (SELECT teacher_id
                    FROM student WHERE name = 'Mason');

# 使用自联结
SELECT s1.name
FROM student AS s1, student AS s2
WHERE s1.teacher_id = s2.teacher_id
	AND s1.name = 'Mason';

外部联结：前面各种类型的联结，只会返回满足联结条件的行，外部联结会将一个表中所有的行保留下来，即使这一行没有和另一个表在联结点对应，这个返回行中对应另一个表的字段值为 NULL。LEFT OUTER JOIN 会保留左表所有的行，RIGHT OUTER JOIN 保留右表所有的行。
```
SELECT stu.name AS stu_name, teacher.name AS teacher_name
FROM stu LEFT OUTER JOIN teacher
	ON stu.teacher_id = teacher.id;
```

UNION

组合查询允许执行多个查询，并将各个查询的结果组合为单个结果集。

组合查询的效果相当于 WHERE 子句中多个条件用 OR 组合起来。例子：查出班级为 3 或者老师 ID 为 1 的所有学生：

# 使用单个查询
SELECT name FROM student
WHERE class_id = 3 OR teacher_id = 1;

# 使用组合查询
SELECT name FROM student
WHERE class_id = 3
UNION
SELECT name FROM student
WHERE teacher_id = 1

UNION 连接的多个查询结果必须具有相同的列，这样才能将各个结果复合起来。
如果要对结果排序，只能用一次 ORDER BY 子句，且要放到最后一个查询后面；也就是说，只能对最终的复合结果进行排序，不能对单个查询结果排序。

修改数据表

INSERT

插入一行：INSERT INTO stu(name, age) VALUES ('Rachael', 27);
插入多行：INSERT INTO stu(name, age) VALUES ('Rachael', 27),('Chandler', 30);
插入查询结果 INSERT SELECT：INSERT INTO stu(name, age) SELECT name, age FROM new_stu; 查询出的列和 INSERT 中指定的列的顺序需要一致（不关心列名是否一样）。

UPDATE

更新特定行的一个字段：UPDATE stu SET age = 26 WHERE name = 'Rachael';
更新特定行的多个字段：UPDATE stu SET age = 26, name = 'Monica' WHERE name = 'Mason';
删除特定行的一个字段：将该字段的值设置为 NULL，即可删除其值。（前提是该字段允许为 NULL）

DELETE

删除特定行：DELETE FROM stu WHERE name = 'Monica';
使用 UPDATE 和 DELETE 操作时，应该先使用 SELECT 语句测试其 WHERE 条件，保证过滤的是正确的记录。

视图

利用视图简化复杂的联结：视图最常见的应用是隐藏复杂的 SQL，这通常会涉及联结。下例创建一个名为 product_customers 的视图，它联结了三个表，返回已订购了任意产品的所有客户的列表。然后查询订购任意产品的客户时，只需要直接从这个视图中查询即可，不需要再管复杂的联结条件了。
```
CREATE VIEW product_customers AS
SELECT cust_name, cust_contact, prod_id
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num;

# 直接从该视图中查询
SELECT cust_name, cust_contact
FROM product_customers
WHERE prod_id = 'product_1';
```
利用视图格式化检索出的数据：视图的另一常见用途是重新格式化检索出的数据。下例中将供应商的名称和国家拼接在一起，作为单个列返回，并将其包装为一个视图：
```
CREATE VIEW vendor_country AS
SELECT concat(vend_name, ' (', vend_country, ')') AS vend_title
FROM vendors ORDER BY vend_name;
```

利用视图过滤不想要的数据：下例中过滤掉没有电子邮件的客户：

CREATE VIEW cust_email AS
SELECT cust_id, cust_name, cust_email
FROM customers WHERE cust_email IS NOT NULL

应该只用视图进行检索（SELECT），不用它来更新表（INSERT / UPDATE / DELETE）。

最后附上整篇文档的脑图：

标签：name,基础,id,语法,stu,MySQL,WHERE,teacher,SELECT
From： https://www.cnblogs.com/lzh1995/p/16757607.html