三、MySQL操作数据表的记录

• 插入记录（INSERT）

INSERT [INTO] tbl_name [(col_name),...] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...

• 插入记录（INSERT SET-SELECT）

• 说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery)

INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...

• 更新记录-单表更新（UPDATE）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name={expr2|DEFAULT}]...[WHERE where_condition]

• 删除记录-单表删除（DELETE）

DELETE FROM tbl_name [WHERE where_condition]

• 查找记录（SELECT)

SELECT select_expr [,select_expr ...]
[
    FORM table_references
    [WHERE where_condition]
    [GROUP BY {col_name|position} [ASC|DESC],...]
    [HAVING where_condition]
    [ORDER BY {col_name|expr|position} [ASC|DESC],...]
    [LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

• 查询表达式（select_expr）

  • 每一个表达式表示想要的一列，必须有至少一个
  • 多个列之间以英文逗号分隔
  • 星号（*）表示所有列
  • tbl_name.*可以表示命名表的所有列
  • 查询表达式可以使用[AS] alias_name为其赋予别名
  • 别名可用于GROUP BY,ORDER BY或HAVING子句

• 条件表达式（WHERE）

  • 对记录进行过滤，如果没有指定WHERE子句，则显示所有记录
  • 在WHERE表达式中，可以使用MySQL支持的函数或运算符

• 查询结果分组（GROUP BY）

[GROUP BY {col_name | position} [ASC | DESC], ...]

• 分组条件（HAVING）

[HAVING where_condition]

• 对查询结果进行排序（ORDER BY）

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

• 限制查询结果返回的数量（LIMIT）

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

• 子查询（SubQuery）

  • 子查询是指出现在现在其他SQL语句内的SELECT子句

# 例如：
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
# 其中SELECT * FROM t1,称为Outer Query/Outer Statement
# SELECT col2 FROM t2,称为SubQuery

• 子查询指嵌套在查询内部，且必须始终出现在圆括号内

  • 子查询可以包含多个关键字或条件
  • 如：DISTINCT、GROUP BY、ORDER BY、LIMIT，函数等
    • 子查询的外层查询可以是：SELECT，INSERT，UPDATE，SET或DO
    • 子查询可以返回标量、一行、一列或者子查询

• 使用比较运算符的子查询

  • =、>、<、>=、<=、<>、!=、<=>

• 语法结构

  • operand comparision_operator subquery

• 比较运算符的修饰

  • operand comparison_operator ANY（subquery）
  • operand comparison_operator SOME（subquery）
  • operand comparison_operator ALL（subquery）

• ANY、SOME、ALL关键字

运算符\关键字	ANY	SOME	ALL
>、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

• 使用[NOT] IN的子查询

• 语法结构

  • operand comparision_operator [NOT] IN (subquery) = ANY 运算符与IN等效
  • !=ALL或<>ALL运算符与NOT IN等效

• 使用[NOT] EXISTS的子查询

  • 如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE

• 将查询结果写入数据表（INSERT...SELECT）

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

• 多表更新

UPDATE table_references
SET col_name={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

• 创建数据表同时将查询结果写入到数据表（CREATE...SELECT）

  CREATE TABLE [IF NOT EXISTS] tbl_name
  [(create_definition,...)]
  select_statement
  

• 连接

  • MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作
  • 语法结构

table_reference
{[INNER | CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

• 数据表参照

  • table_reference

  • table_name [[AS] alias] | table_subquery [AS] alias

  • 数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名

  • table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

• 连接类型

• • INNER JOIN，内连接

  • 在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的

  • LEFT [OUTER] JOIN，左外连接

  • RIGHT [OUTER] JOIN，右外连接

• 连接类型

  • 使用ON关键字来设定连接条件，也可以使用WHERE来代替

  • 通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤

• 内连接（INNER JOIN）

  • 显示左表及右表符合连接条件的记录!

内连接.png

• 左外连接（LEFT JOIN）

  • 显示左表的全部记录及右表符合连接条件的记录

左外连接.png

• 右外连接（RIGHT JOIN）

  • 显示右表的全部记录及左表符合连接条件的记录

右外连接.png

• 外连接

  • A LEFT JOIN B join_condition
  • 数据表B的结果集依赖数据表A
  • 数据表A的结果集根据左连接条件依赖所有数据表（B表除外）
  • 左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）
  • 如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行
  • 如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行

• 多表删除

DELETE tbl_name[.*] [,tb_name[.*]]...
FROM table_references
[WHERE where_condition]