mysql学习笔记（二）

• 约束
  1、约束保证数据的完整性和一致性；
  2、约束分为表级约束（针对2个或2个以上字段的约束）和列级约束；
  3、约束类型包括（按功能划分）：

NOT NULL（非空约束）
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

• FOREIGN KEY：保证数据的一致性和完整性；实现一对一或一对多的关系。

• 外键约束的要求：
  1、父表（子表参照的表）和子表（具有外键列的表）必须使用相同的存储引擎，而且禁止使用临时表；
  2、数据表的存储引擎只能为InnoDB。
  3、外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度可以不同；
  4、外键列和参照列必须创建索引。如果参照列不存在索引，mysql将自动创建索引，而外键列则不会自动创建！

• 编辑数据表的默认存储引擎：在mysql配置文件my.ini中设置default-storage-engine=INNODB

• 外键约束的参照操作
  1、CASCADE：从父表删除或更新记录将会自动删除或更新子表中匹配的行；例如创建外键约束时添加ON DELETE CASCADE
  2、SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。若使用该选项，必须保证子表列没有指定NOT NULL，否则操作失败！
  3、RESTRICT：拒绝对父表的删除或更新操作；
  4、NO ACTION：标准SQL的关键字，在mysql中与RESTRICT相同。

• 表级约束与列级约束
  1、列级约束：对一个数据列建立的约束；
  2、表级约束：对多个数据列建立的约束；注意：NOT NULL、DEFAULT不存在表级约束，只有列级约束！
  3、列级约束既可以在列定义时声明，也可在列定义后声明；而表级约束只能在列定义后声明。

• 修改数据表

1、添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
# 参数FIRST表示创建的字段插入表头；
# 参数AFTER 表示创建的字段插入某个列的后面；
# 若不添加上面这2个参数，则默认添加到所有列的后面。

2、添加多列

ALTER TABLE tbl_name ADD  [COLUMN] (col_name column_definition, ...),
                     ADD  [COLUMN] (col_name column_definition, ...)
                     ...;

3、删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name;

4、添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...);
# symbol 为约束名称

5、添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

6、添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition;

7、添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

8、删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY;

9、删除唯一约束

ALTER TABLE tal_name DROP [INDEX | KEY] index_name;

10、删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

11、修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

12、修改列名称，可同时重新定义类型和字段的位置

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];

13、数据库表更名

# 法一：
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
# 法二：
RENAME  TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

• 更新记录（单表更新）

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1 = {expr1|DEFAULT} [, col_name2={expr|DEFAULT}] ... [WHERE where_condition];

• 删除记录（单表删除）

DELETE FROM tbl_name [WHERE where_condition];

• 查找记录

SELECT select_expr [, select_expr ...]
[
  FROM table_references  # 从哪个表查询
  [WHERE where_condition]
  [GROUP BY {col_name| position} [ASC | DESC], ...]  # 将查询结果分组
  [HAVING where_condition]  # HAVING子句一般和分组语句一起使用
  [ORDER BY {col_name | expr | position} [ASC | DESC], ...]  # 排序
  [LIMIT {[offset,] row_count | row_count OFFSET offset}]   # 分页查询 
]

• 星号（*）表示所有列，tbl_name.*可以表示命名表的所有列。
• 查询表达式可以使用[AS] alias_name为其赋予别名。
• 别名可用于GROUP BY，ORDER BY或HAVING子句。
• GROUP BY：将查询结果分组

[GROUP BY {col_name| position} [ASC | DESC], ...]
# col_name：字段名；position：查询字段的位置；
# ASC：升序；DESC：降序。

• 分组条件，要么为一个聚合函数，要么出现在select语句中，否则会出错！

[HAVING where_condition]

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

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

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

[LIMIT {[offset,] row_count | row_count OFFSET offset}]
# limit 2 表示返回前2条记录
# limit 4, 3 从第5条记录开始返回3条记录

• 子查询指嵌套在查询内部，且必须始终出现在圆括号内。
• 子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT，函数等。
• 子查询的外层查询可以是SELECT，INSERT，UPDATE、SET或DO。
• 子查询返回值：标量、一行、一列或子查询。
• 使用比较运算符的子查询：=、>、<、>=、<=、<>、!=、<=>
• 用ANY、SOME、或ALL修饰的比较运算符

operand comparison_operator ANY(subquery);
operand comparison_operator SOME(subquery);
operand comparison_operator ALL(subquery);

	ANY	SOME	ALL
>、>=	最大值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值	-
<>、!=	-	-	任意值

• 使用[NOT] IN的子查询，语法结构：operand comparison_operator [NOT] IN (subquery)
• = ANY运算（任意值）与IN等效。
• != ALL或<> ALL运算与NOT IN等效。
• 使用[NOT] EXISTS的子查询：若子查询返回了结果，EXISTS则返回TRUE，否则返回FALSE。
• 查看表结构的详细信息

DESC tbl_name; 

• 多表更新

UPDATE table_references SET col_name1 = {expr1 | DEFAULT} [, col_name2={expr2|DEFAULT}]... [WHERE where_condition];
# table_references 多个表之间的连接

• table_reference（多表连接）：{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN } ON condition_expr
• 连接类型：OUTER JOIN（外连接），CROSS JOIN（交叉连接）和INNER JOIN（内连接）
• 左外连接：LEFT [OUTER] JOIN；
• 右外连接：RIGHT [OUTER] JOIN。
• 使用ON关键字来设定连接条件，也可以使用WHERE 来代替，但WHERE一般用来过滤结果集记录。
• 现有两张表，Table A 是左边的表。Table B 是右边的表。其各有四条记录，其中有两条记录name是相同的：
  1、INNER JOIN 查询结果是AB的交集；
  2、LEFT [OUTER] JOIN 产生表A的完全集，而B表中匹配查询条件才有值，没有匹配的则以null值取代。
  3、RIGHT [OUTER] JOIN产生表B的完全集，而A表中匹配查询条件才有值，没有匹配的则以null值取代。
  4、FULL [OUTER] JOIN 产生A和B的并集。对于没有匹配的记录，则会以null做为值。
  5、CROSS JOIN把表A和表B的数据进行一个N*M的组合，即笛卡尔积。如本例会产生条记录，这种连接方式很少用！
• 自身连接：同一个数据表对其自身进行连接。
• 创建数据表并将查询结果写入数据表中

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

• mysql在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
• 数据库表参照，数据库表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名；table_subquery可作为子查询使用在FROM子句中，注意：这样的子查询必须为其赋予别名！

tbl_name [[AS] alias] | table_subquery [AS] alias

• 多表删除

DELETE tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition]; 
# 删除自身一些重复记录需用到表别名