- 约束
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];
# 删除自身一些重复记录需用到表别名
网友评论