数据库键值与约束条件

最新推荐文章于 2024-08-21 14:16:39 发布

原创最新推荐文章于 2024-08-21 14:16:39 发布 · 595 阅读

2 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

11 篇文章

订阅专栏

约束条件
Null 允许为空（“”）–不为空（0个字符）（null）
Not null 不允许为空
key 索引类型
default 设置默认值
create table db2.t2(name char(3) not null, leven int not null default 300, sex enum(“m”,“w”) not null default “m” );
±------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±--------------±-----±----±--------±------+
| name | char(3) | NO | | NULL | |
| leven | int(11) | NO | | 300 | |
| sex | enum(‘m’,‘w’) | NO | | m | |
±------±--------------±-----±----±--------±------+
insert into t2(name,sex) values(“tom”,“w”);
±-----±------±----+
| name | leven | sex |
±-----±------±----+
| aa | NULL | w |
| jim | 300 | m |
| tom | 300 | w |
±-----±------±----+

修改表结构
基本用法：alter table 表名执行动作
add 添加字段
modify 修改字段类型
change 修改字段名
dorp 删除字段
rename 修改表名

add 字段名类型（宽度）约束条件；可加 after 字段名；或者first
alter table t2 add emall varchar(30); (在后添加一列)
±------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±--------------±-----±----±--------±------+
| name | char(3) | NO | | NULL | |
| leven | int(11) | YES | | 300 | |
| sex | enum(‘m’,‘w’) | NO | | m | |
| emall | varchar(30) | YES | | NULL | |
±------±--------------±-----±----±--------±------+
alter table t2 add likes set(“a”,“b”,“c”) default “a,b” after name; （添加在name后）
±------±-----------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------------±-----±----±--------±------+
| name | char(3) | NO | | NULL | |
| likes | set(‘a’,‘b’,‘c’) | YES | | a,b | |
| leven | int(11) | YES | | 300 | |
| sex | enum(‘m’,‘w’) | NO | | m | |
| emall | varchar(30) | YES | | NULL | |
±------±-----------------±-----±----±--------±------+
alter table t2 add stu-num char first; (加到最前面)
删除字段 alter table 表名 drop 字段名
alter table t2 drop stu_num, drop email （删除stu_num email这两列）–删除多行（用逗号分）
修改表名 alter table 表名 rename 新表名
alter table t2 rename ta; （修改表名，不能修改库名)
修改字段类型： alter table 表名 modify 字段名类型（宽度）约束条件可加after 字段名或则 first；
alter table t2 modify sex enum(‘m’,‘w’) not null default “m” after name; (将sex。。。这一列调到name后)
alter table t2 modify stu_num varchar(12); （修改stu_num 类型）
修改字段名 alter table 表名 change 源字段名新字段类型（宽度）约束条件
alter table t2 change stu_num stu_id varchar(10) (修改字段名stu_num 为stu_id)

mysql键值
普通索引 index *
唯一索引 unique
主键 primary key *
外键 foreign key *
全文索引 fulltext
索引介绍
索引：索引是记录集的多个字段进行排序的方法。
类似于书的目录
索引类型包括：BTree （默认）二叉树算法 B+Tree hash

索引优缺点：优点：通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
可以加快数据的检索速度
缺点：当对表中数据进行增加，删除和修改时，索引也要动态的维护，降低了数据维护速度。索引需要占物理空间。
索引（排队）-----》表文件
select * from t2 where name=“tom”;

index普通索引
在已有的表中设置index字段
create index 索引名 on 表名（字段名）
删除指定表的索引字段
drop index 索引名 on 表名；
show index from 表名；
index_type：BTREE （使用B树算法）

使用说明：
一个表中可以有多个index字段
字段的值允许有重复，可以赋null值
经常把做查询条件的字段设置为index字段
index字段的key标志时mul
index 普通索引
create table t9( 姓名 char(3), 年龄 int, 班级 char(7) ,index(姓名) ,index(班级)) default charset=utf8;(创建t9表，并创建索引)
±-------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------±-----±----±--------±------+
| 姓名 | char(3) | YES | MUL | NULL | |
| 年龄 | int(11) | YES | | NULL | |
| 班级 | char(7) | YES | MUL | NULL | |
±-------±--------±-----±----±--------±------+
show index from t9; （行查看t9表索引）
show index from t9\G; (列查看表中索引)
drop index 班级 on t9; (删除班级索引)
把已有表的字段设置为索引 :create index xxx on t9(班级); ------>drop index xxx on t9;(对应删除)

primary key 主键
注意事项
一个表中只能有一个primarry key 字段
对应的字段值不允许有重复，且不允许赋Null。
如果有多个字段都作为primary key ，称为复合键，必须一起创建
主键字段的key 标志是PRI
通常于auto_increment l连用
经常把表中能够唯一标示记录的字段设置为主键字段『记录编号字段』

primary key 主键
建表的时候指定主键字段 primary key （字段名）

create table t5( stu_id char(7), name char(5), primary key(stu_id)) (创建（stu_id）为 primary主键)
或 create table t5 (stu_id, name char(5) primary key）;
±-------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------±-----±----±--------±------+
| stu_id | char(7) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
±-------±--------±-----±----±--------±------+
insert t5 values(“nsd1808”,null);
±--------±-----+
| stu_id | name |
±--------±-----+
| nsd1803 | null |
| nsd1804 | NULL |
| nsd1808 | jim |
| nsd1809 | jim |
±--------±-----+

alter table ta add primary key(name);(在已有的表设置主键)—注意已有的表中有空值添加不上。
alter table t2 drop primary key; (删除主键)
create table t9( name char(5), class char(7), pay enum(“yes”,“no”), primary key(name,class));（创建主复键，创的新表）
-------±-----------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-----------------±-----±----±--------±------+
| name | char(5) | NO | PRI | NULL | |
| class | char(7) | NO | PRI | NULL | |
| pay | enum(‘yes’,‘no’) | YES | | NULL | |
±------±-----------------±-----±----±--------±------+
insert t9 values(“bob”,“nsd1809”,“no”);
insert t9 values(“bob”,“nsd1808”,“no”);
insert t9 values(“jim”,“nsd1808”,“no”);
±-----±--------±-----+
| name | class | pay |
±-----±--------±-----+
| bob | nsd1808 | no |
| bob | nsd1809 | no |
| jim | nsd1808 | no |
±-----±--------±-----+
alter table t9 drop primary key; （删除复合主键）
主复键（name class 两个值不能同时一样设置。）
在已有的表添加复合主键
alter table t9 add primary key(name,class) （注，已有的表有重复要先删除）

create table t10(
-> id int primary key auto_increment,
-> name char(10),
-> age int default 19,
-> sex enum(“m”,“w”) default “m”);
insert t10(name,age,sex) values(“jim”,“22”,“m”);
±—±-----±-----±-----+
| id | name | age | sex |
±—±-----±-----±-----+
| 1 | jim | 22 | m |
| 2 | jim | 22 | m |
| 3 | tom | 22 | m |
±—±-----±-----±-----+

select * from t10 where name=“tom”;
±—±-----±-----±-----+
| id | name | age | sex |
±—±-----±-----±-----+
| 3 | tom | 22 | m |
±—±-----±-----±-----+
alter table 表名 add primary key（字段名）
删除 alter table 表名 drop primary key
create table t1(
-> id int primary key auto_increment,
-> name char(10),
-> age tinyint unsigned,
-> class char(7) default “nsd1809”,
-> index(name));

show create table t1 （查看）
注（当 insert t1（id，name，age） values（null，“jim”，12） ------id=null（不会报错，auto会子赠长）当表删除后在加（id会继承之前后加一）
当有值曾长时删除主键要先删除auto_increment（子增长） alter table t1 modify id int not null
alter table t1 drop primary key
已有得表id 在增加 key auto 可以先删除 alter table t1 drop id；
alter table t1 add id int primary key auto_increment first;

poreign key 外键
什么是外键？
让当前表字段的值在另一个表中字段的氛围内选择。
使用外键条件
表的存储引擎必须是innodb
字段类型要一致
被参考字段必须要是索引类型的一种（primary key）

基本用法
foreign key（表A的字段名）
references 表B（字段名）
on update cascade （同步更新）
on delete cascade （同步删除）
删除外键字段
alter table 表名 drop foreign key 约束名

create table 员工表(员工编号 int primary key auto_increment,员工姓名 char(10))engine=innodb default charset=utf8;
insert 员工表(员工姓名) values(“李四”);
insert 员工表(员工姓名) values(“李远辉”);
±-------------±-------------+
| 员工编号 | 员工姓名 |
±-------------±-------------+
| 1 | 张三 |
| 2 | 王五 |
| 3 | 李四 |
±-------------±-------------+
create table 工资表(工资编号 int,工资 float(7,2),foreign key(工资编号) references 员工表(员工编号) on update cascade on delete cascade)engine=innodb default charset=utf8;
±-------------±-----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------------±-----------±-----±----±--------±------+
| 工资编号 | int(11) | YES | MUL | NULL | |
| 工资 | float(7,2) | YES | | NULL | |
±-------------±-----------±-----±----±--------±------+
show create table 工资表;
| 工资表 | CREATE TABLE 工资表 (
工资编号 int(11) DEFAULT NULL,
工资 float(7,2) DEFAULT NULL,
KEY 工资编号 (工资编号),
CONSTRAINT 工资表_ibfk_1 FOREIGN KEY (工资编号) REFERENCES 员工表 (员工编号) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±----------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

select * from 员工表;
±-------------±-------------+
| 员工编号 | 员工姓名 |
±-------------±-------------+
| 1 | 张三 |
| 2 | 王五 |
| 3 | 李四 |
| 4 | 李远辉 |
| 5 | 大腿 |
±-------------±-------------
update 员工表 set 员工编号=8 where 员工姓名=“李远辉”; on update cascade （同步更新）
±-------------±-------------+
| 员工编号 | 员工姓名 |
±-------------±-------------+
| 1 | 张三 |
| 2 | 王五 |
| 3 | 李四 |
| 5 | 大腿 |
| 8 | 李远辉 |
±-------------±-------------+
select * from 工资表
±-------------±---------+
| 工资编号 | 工资 |
±-------------±---------+
| 1 | 20000.00 |
| 2 | 15000.00 |
| 3 | 10000.00 |
| 8 | 5000.00 |
±-------------±---------+
delete from 员工表 where 员工编号=5 (删除员工表中员工编号为5 的人对应工资表5 的也删除了)
以上有个弊端工资编号能重复
±-------------±---------+
| 工资编号 | 工资 |
±-------------±---------+
| 1 | 20000.00 |
| 2 | 15000.00 |
| 3 | 10000.00 |
| 8 | 5000.00 |
| 3 | 11000.00 |
| 3 | 11000.00 |
| 3 | 22222.00 |
±-------------±---------+
解决方案（给工资编号添加主键）避免重复发
先删除（工资表 delete from 工资表 where 工资编号=3;）： alter table 工资表 add primary key(工资编号)
删除外键（赋值不再受外键限制）： alter table 工资表 drop foreign key 工资表_ibfk_1`（在show create table 工资表中 CONSTRAINT）
在已有表设置外键（要先删除）
alter table 工资表 add forenign key(工资编号) references 员工表（员工编号） on update cascade on delete cascade；