1. 约束
约束保证数据的完整性和一致性,约束分为表级约束和列级约束。约束类型包括:NOT NULL(非空约束)、PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、DEFAULT(默认约束)、FOREIGN KEY(外检约束)。
外键约束保证了数据的一致性、完整性,实现了一对一或一对多的关系
外键约束的要求:
(1). 父表和字表必须使用相同的存储引擎,而且禁止使用临时表。
(2). 数据表的存储引擎只能为InnoDB。
(3). 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
(4). 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。
MySQL配置文件:
default-storage-engine=INNODB
CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL); SHOW CREATE TABLE provinces; CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id)); SHOW INDEX from provinces\G; SHOW INDEX from users\G;
2. 外键约束的参照操作
(1). CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
(2). SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL
(3). RESTRICT:拒绝对父表的删除或更新操作。
(4). NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
例如:
CREATE TABLE users1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE); INSERT provinces(pname) VALUES('A'); INSERT provinces(pname) VALUES('B'); INSERT provinces(pname) VALUES('C'); INSERT users1(username, pid) VALUES('Tom', 3); INSERT users1(username, pid) VALUES('Jerry', 1); #查看我们添加的数据 SELECT * FROM users1; #删除一个省份 DELETE FROM provinces WHERE id=3; #查看省份 SELECT * FROM provinces; #再查看人员表 SELECT * FROM users1;
3. 表级约束与列级约束
对一个数据列简历的约束,称为列级约束;对多个数据列建立的约束,称为表级约束。列级约束既可以在列定义时声明,也可以在列定义后声明。表级约束只能在列定以后声明。
4. 修改数据表
(1). 添加/删除列
添加单列:
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
添加多列:
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...);
删除列:
ALTER TABLE tbl_name DROP [COLUMN] col_name;
例如:
SHOW COLUMNS FROM users1; ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; ALTER TABLE users1 ADD password VARCHAR(32) NOT NULL AFTER username; ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST; #然后查看一下users1结构 SHOW COLUMNS FROM users1; ALTER TABLE users1 DROP truename; ALTER TABLE users1 DROP password, DROP age; #然后查看一下users1结构 SHOW COLUMNS FROM users1;(2). 添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...);
例如:
CREATE TABLE users2(username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED); SHOW CREATE TABLE users2; ALTER TABLE users2 ADD id SMALLINT UNSIGNED; SHOW COLUMNS FROM users2; #设置id为主键 ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);(3). 添加唯一约束:
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...);
例如:
ALTER TABLE users2 ADD UNIQUE (username); SHOW CREATE TABLE users2;(4). 添加外键约束:
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference definition;
例如:
ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id); #查看 SHOW CREATE TABLE users2;(5). 添加/删除默认约束:
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};
例如:
ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL; SHOW COLUMNS FROM users2; #设置默认值 ALTER TABLE users2 ALTER age SET DEFAULT 15; SHOW COLUMNS FROM users2; #删除默认值 ALTER TABLE users2 ALTER age DROP DEFAULT; SHOW COLUMNS FROM users2;(6). 删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;
例如:
ALTER TABLE users2 DROP PRIMARY KEY; # 查看 SHOW COLUMNS FROM users2;(7). 删除唯一约束
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;
例如:
# 查看索引 SHOW INDEXES FROM users2\G; ALTER TABLE users2 DROP INDEX username; #查看 SHOW COLUMNS FROM users2; # 再查看索引 SHOW INDEXES FROM users2\G;
(8). 删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
例如:
SHOW CREATE TABLE users2; ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1; # 查看外键不见了,但是pid索引还在 SHOW CREATE TABLE users2; ALTER TABLE users2 DROP INDEX pid; # pid索引也删除了 SHOW CREATE TABLE users2;(9). 修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];
例如:
SHOW CREATE TABLE users2; # 将id放在最前面 ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED FIRST; SHOW COLUMNS FROM users2; # 修改字段类型。 注意:由大类型修改为小类型有可能会造成数据的丢失。 ALTER TABLE users2 MODIFY id TINYINT UNSIGNED FIRST;(10). 修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];
例如:
#修改列名称和类型 ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL; SHOW COLUMNS FROM users2;(11). 数据表更名
方法一:
ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name;
方法二:
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]...;
例如:
ALTER TABLE users2 RENAME users3; SHOW TABLES; RENAME TABLE users3 TO users2; SHOW TABLES;
5. 总结
(1). 约束:
按功能划分:NOT NULL,PRIMARY KEY,UNIQUE KEY,DEFAULT, FOREIGN KEY
按数据列的数目划分:表级约束、列级约束
(2). 修改数据表:
针对字段的操作:添加/删除字段、修改列定义、修改列名称等
针对约束的操作:添加/删除各种约束
针对数据表的操作:数据表更名(两种方式)

MySQL函数可用于数据处理和计算。1.基本用法包括字符串处理、日期计算和数学运算。2.高级用法涉及结合多个函数实现复杂操作。3.性能优化需避免在WHERE子句中使用函数,并使用GROUPBY和临时表。

MySQL批量插入数据的高效方法包括:1.使用INSERTINTO...VALUES语法,2.利用LOADDATAINFILE命令,3.使用事务处理,4.调整批量大小,5.禁用索引,6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE,这些方法能显着提升数据库操作效率。

在MySQL中,添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column,删除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段时,需指定位置以优化查询性能和数据结构;删除字段前需确认操作不可逆;使用在线DDL、备份数据、测试环境和低负载时间段修改表结构是性能优化和最佳实践。

使用EXPLAIN命令可以分析MySQL查询的执行计划。1.EXPLAIN命令显示查询的执行计划,帮助找出性能瓶颈。2.执行计划包括id、select_type、table、type、possible_keys、key、key_len、ref、rows和Extra等字段。3.根据执行计划,可以通过添加索引、避免全表扫描、优化JOIN操作和使用覆盖索引来优化查询。

子查询可以提升MySQL查询效率。1)子查询简化复杂查询逻辑,如筛选数据和计算聚合值。2)MySQL优化器可能将子查询转换为JOIN操作以提高性能。3)使用EXISTS代替IN可避免多行返回错误。4)优化策略包括避免相关子查询、使用EXISTS、索引优化和避免子查询嵌套。

在MySQL中配置字符集和排序规则的方法包括:1.设置服务器级别的字符集和排序规则:SETNAMES'utf8';SETCHARACTERSETutf8;SETCOLLATION_CONNECTION='utf8_general_ci';2.创建使用特定字符集和排序规则的数据库:CREATEDATABASEexample_dbCHARACTERSETutf8COLLATEutf8_general_ci;3.创建表时指定字符集和排序规则:CREATETABLEexample_table(idINT

要安全、彻底地卸载MySQL并清理所有残留文件,需遵循以下步骤:1.停止MySQL服务;2.卸载MySQL软件包;3.清理配置文件和数据目录;4.验证卸载是否彻底。

MySQL中重命名数据库需要通过间接方法实现。步骤如下:1.创建新数据库;2.使用mysqldump导出旧数据库;3.将数据导入新数据库;4.删除旧数据库。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

SublimeText3 Linux新版
SublimeText3 Linux最新版

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

SublimeText3汉化版
中文版,非常好用

VSCode Windows 64位 下载
微软推出的免费、功能强大的一款IDE编辑器

PhpStorm Mac 版本
最新(2018.2.1 )专业的PHP集成开发工具