mysql学习笔记2-

约束

  • 约束保证数据的完整性与一致性
  • 约束分为表级约束和列级约束
  • 约束类型包括
    – NOT NULL (非空约束)
    – PRIMARY KEY (主键约束)
    – UNIQUE KEY (唯一约束)
    – DEFAULT (默认约束)
    – FOREIGN KEY (外键约束)

外键约束的要求解析

FOREIGN KEY
保证数据的一致性,完整性.
实现一对一或一对多关系

  • 父表与子表必须使用相同的存储引擎,而且禁止使用临时表.
  • 数据表的存储引擎只能为InnoDB
  • 外键列和参照列必须具有相似的数据类型.其中数字的长度或者是否有符号位必须相同;而字符的长度则可以不同.
  • 外键列和参照列必须创建索引.如果外键列不存在索引的话,MySQL将自动创建索引

显示数据表的创建数据

1
SHOW CREATE TABLE tbl_name;

显示数据表索引

1
SHOW INDEXES FROM tbl_name;

创建两个表并且进行外键约束

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces(id)
);

外键与主键数据类型必须的一直 比如 pname SMALLINT 对应的 pid SMALLINT 而且符号位也必须相同
有外键的表称为子表 子表参照的表称为附表
参照列自动创建了索引

外键约束的参照操作

  • CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
  • SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL.如果使用该选项,必须保证子表列没有指定NOT NULL.
  • RESTRICT:拒绝对父表的删除或更新操作.
  • NO ACTION:标准的SQL关键字,在MYSQL中与RESTRICT相同.

外键约束仅仅支持innodb引擎

1
2
3
4
5
6
CREATE TABLE user1(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
);

参照操作需要开启ON DELETE后再加上参数

添加数据

1
2
3
4
5
6
7
INSERT provinces(pname) VALUES("A");
INSERT provinces(pname) VALUES("B");
INSERT provinces(pname) VALUES("C");
//插入用户数据
INSERT user1(username,pid) VALUES("Tom",3);

插入用户数据只需要对应设置外键的id即可.

如果记录没有写入成功,但是编号会自动递增

删除记录

删除provinces id为3的记录

1
DELETE FROM provinces WHERE id = 3;

那么相应的子表中外键id为3的也会删除.

表级约束与列级约束

  • 对一个数据列建立的约束,称为列级约束.
  • 对多个数据列建立的约束,称为表级约束.
  • 列级约束既可以在列定义时声明,也可以在列定义后声明.
  • 表级约束只能在列定义后声明.

修改数据表

添加单列

1
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

FIRST 指的是插入的这一列会在最前面.AFTER col_name则是在某一列后面

添加单列数据

1
2
3
4
SHOW COLUMNS FROM user1;
ALTER TABLE user1 ADD age INT UNSIGNED NOT NULL DEFAULT 10;
ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;//插入某列后面
ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST;

DEFAULT:未明确表明数据的时候.默认指定的数据
UNSIGNED:针对数值型类型 是否有符号

添加多列

添加多列无法指定位置

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

给user1添加多列数据

1
2
3
4
ALTER TABLE user1 ADD(
num INT UNSIGNED NOT NULL DEFAULT 1,
sex ENUM("男","女") NOT NULL DEFAULT "男"
);

删除列

1
ALTER TABLE user1 DROP truename;

删除多列

1
ALTER TABLE user1 DROP truename,DROP password;

修改列的时候可以删除的时候再添加.中间通过逗号分隔.

##删除的同时再添加列

1
ALTER TABLE user1 DROP num, ADD pm FLOAT UNSIGNED DEFAULT 15;

添加主键约束

1
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)

范例一

1
2
3
4
5
6
7
8
CREATE TABLE user2(
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED
);
//增加主键
ALTER TABLE user2 ADD id SMALLINT UNSIGNED;
//增加主键约束
ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY (id);

CONSTRAINT:用来起别名

添加唯一约束

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

范例一

1
ALTER TABLE user2 ADD UNIQUE (username);

添加外键约束

范例一

user2中pid参照provinces;

1
ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);

添加/删除默认约束

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

范例一

1
2
ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL;
ALTER TABLE user2 ALTER age SET DEFAULT 15;

给age添加默认约束

删除主键约束

任何一个表有且只有一个主键

1
ALTER TABLE user2 DROP PRIMARY KEY;

删除主键索引

1
ALTER TABLE user2 DROP INDEX username;

删除的仅仅是索引

显示索引列表名称

1
SHOW INDEXes FROM user2;

删除外键约束

删除约束是删除约束的名称

1
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbok

范例

1
2
3
4
5
6
//查询外键名称
SHOW CREATE TABLE user2;
//找到pid的外键名称为 user2_ibfk_1;然后进行删除
ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;

虽然外键不在了,但是索引还是存在的 如果不想要索引则删除即可

1
ALTER TABLE user2 DROP INDEX pid;

修改列定义和更名数据表

###修改列定义

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

修改列顺序

1
ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

column_definition指的是列定义 不变

修改列定义-范例

1
ALTER TABLE user2 MODIFY id TINYINT UNSIGNED NOT NULL;

修改列定义的时候有可能会造成数据的丢失.

修改列名称

1
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new col_name column_definition [FIRST |AFTER col_name]

修改id的类型与名称

1
ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

数据表更名

一.

1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

二.

1
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2];

范例

1
2
3
ALTER TABLE user2 RENAME users2;
RENAME TABLE users2 TO user2;

总结

约束

  • 按功能化为:NOT NULL,PRIMARY KEY, UNIQUE KEY, DEFAULT,FOREIGN KEY
  • 按数据列的数目化为:表级约束,列级约束

修改数据表

  • 针对字段的操作:添加/删除字段,修改列定义,修改列名称等
  • 针对约束的操作:添加/删除各种约束.
  • 针对数据表的操作:数据表更名(两种方式).

列级约束:只能应用于一列上。
表级约束:可以应用于一列上,也可以应用在一个表中的多个列上。

默认约束(DEFAULT)与非空约束(NOT NULL)不存在表级约束