约束以及修改数据表
约束分为:
主键约束primary key 默认约束default 唯一约束 unique key 非空约束null\not null
外键约束 foreign key 表一(pid) references 表二(id)
约束分为(表级约束,和列级约束(非空和默认)):
对于一个列所创建的约束,称之为列级约束,而对于两个或两个以上的列所创建的约束,我们称之为表级约束,列级约束在使用的时候,既可以在列定义的时候声明,也可以在列定义以后声明,而表级的约束只能在列定义以后来声明,在实际开发中,用列级约束比较多,表级约束很少用,除此之外,在所有的约束中,并不是说每种约束都存在着表级或列级约束,其中,NOT NULL 非空约束,DEFAULT约束这两种约束就不存在表级约束,它们只有列级约束,而对于其他的三种,像主键,唯一,外键,它们都可以存在表级和列级约束。
==========================================================================================
创建;两个表 province : id pname 和user 表 id username pid为外键参照province表中得id字段
1) 数据表的存储引擎必须是INNODB 如果不是需要在配置文件my.ini修改
2)整型要类型相同 都是smallint 符号位也要相同 unsigned
如果是字符的情况下要相似
3)参照列和外键列必须要创建索引 (主键约束自动创建索引的 也就是users表中有两个索引(id pid))
FOREIGN KEY (pid)REFERENCES provinces(id)
外键列:pid
参照列:id
mysql> create table privence(
-> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
-> pname VARCHAR(20) not null
-> );
Query OK, 0 rows affected
mysql> create table users(
-> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
-> username varchar(20) not null,
-> pid smallint unsigned
-> ,
-> foreign key (pid) references privence(id)
-> );
查看表的记录
mysql> select * from privence;
Empty set
查看表的所有信息
mysql> show create table privence;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| privence | CREATE TABLE `privence` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
错误的操作
mysql> show table privence;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'privence' at line 1
-----------------------------==================================================
外键约束的参照操作
1.CASCADE:从父表删除或更新且自动删除或更新指标的匹配的行
2.SET NULL:从父表删除或更新行,并设置指标的外键列为NULL。
如果使用该选项,必须保证值列表没有指定NOT NULL
3.RESTRICT:拒绝对父表的删除或更新操作
4.NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
创建表users1
删除操作时使用cascade操作 对父表privence删除 一条记录 子表users1中与之关联的记录全部删除
删除操作时使用set null操作 对父表privence删除一条记录 子表users1中与之关联的记录全部变为null
当然 这要建立在该删除字段在子表中可以允许为null 值 不然操作失败
mysql> create table users1(
->
-> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
-> username varchar(20) not null,
-> pid smallint unsigned,
-> foreign key users1(pid) references privence(id) ON DELETE CASCADE
-> );
插入数据:
mysql> INSERT users1(username) values('tomcate');
Query OK, 1 row affected
mysql> INSERT users1(username) values('魔法少女');
Query OK, 1 row affected
mysql> INSERT users1(username) values('厄加特');
Query OK, 1 row affected
mysql> INSERT privence (pname) values("四川")
-> ;
Query OK, 1 row affected
mysql> INSERT privence (pname) values("北京")
;
Query OK, 1 row affected
mysql> INSERT privence (pname) values("自贡")
;
Query OK, 1 row affected
删除操作:
mysql> delete from privence where id=3;
username为战神的记录被删除
==========================================================
增加,删除列
ALTER TABLE table_name ADD COLUMN [first| after column]:
alter table users1 add wife varchar(20) not null first;
ALTER TABLE users1 ADD age smallint unsigned not null AFTER id;
mysql> alter table users1 drop wife;
==============================================================
增加约束 删除约束:
添加唯一约束:ALTER TABLE users2 ADD UNIQUE(username);
添加外键约束:ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES privence (id);
添加默认约束:ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除默认约束:ALTER TABLE users2 ALTER age DROP DEFAULT;
数据表的修改操作:无非就是添加列,删除列,添加约束,删除约束。用的是ALTER,而INSERT是对数据表添加插入记录用的。
mysql> create table users2(
-> username varchar(20) not null,
-> age smallint unsigned not null
-> );
添加主键约束:
mysql> alter table users2 ADD id smallint unsigned AUTO_INCREMENT PRIMARY KEY;
mysql> SHOW COLUMNS FROM USERS2;
mysql> alter table users2 alter age set default 15;
mysql> alter table users2 alter age drop default;
mysql> alter table users2 add pid smallint unsigned;
mysql> alter table users2 add foreign key (pid) references privence(id);
mysql> alter table users2 add unique(username);
============================================================
删除约束:
主键约束:alter table users2 drop primary key;
找到唯一约束的索引:
mysql> alter table users2 drop index username;
索引为username
唯一约束:alter table users2 drop index username
找到外键约束的索引:
mysql> show create table users2;
外键约束:mysql> alter table users2 drop index users2_ibfk_1;
===================================================================================
修改列的位置(modify):alter table user2 modify id smallint unsigned not null first;
修改列的名称(change):alter table user2 changed pid p_id tinyint unsigned not null;
数据表更名:
方法一:alter table user2 rename user3;(user2变user3)
方法二:rename table user3 to user4(可为多张数据表更名)
尽量不要随意更改数据列和数据表的名字;
约束分为:
主键约束primary key 默认约束default 唯一约束 unique key 非空约束null\not null
外键约束 foreign key 表一(pid) references 表二(id)
约束分为(表级约束,和列级约束(非空和默认)):
对于一个列所创建的约束,称之为列级约束,而对于两个或两个以上的列所创建的约束,我们称之为表级约束,列级约束在使用的时候,既可以在列定义的时候声明,也可以在列定义以后声明,而表级的约束只能在列定义以后来声明,在实际开发中,用列级约束比较多,表级约束很少用,除此之外,在所有的约束中,并不是说每种约束都存在着表级或列级约束,其中,NOT NULL 非空约束,DEFAULT约束这两种约束就不存在表级约束,它们只有列级约束,而对于其他的三种,像主键,唯一,外键,它们都可以存在表级和列级约束。
==========================================================================================
创建;两个表 province : id pname 和user 表 id username pid为外键参照province表中得id字段
1) 数据表的存储引擎必须是INNODB 如果不是需要在配置文件my.ini修改
2)整型要类型相同 都是smallint 符号位也要相同 unsigned
如果是字符的情况下要相似
3)参照列和外键列必须要创建索引 (主键约束自动创建索引的 也就是users表中有两个索引(id pid))
FOREIGN KEY (pid)REFERENCES provinces(id)
外键列:pid
参照列:id
mysql> create table privence(
-> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
-> pname VARCHAR(20) not null
-> );
Query OK, 0 rows affected
mysql> create table users(
-> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
-> username varchar(20) not null,
-> pid smallint unsigned
-> ,
-> foreign key (pid) references privence(id)
-> );
查看表的记录
mysql> select * from privence;
Empty set
查看表的所有信息
mysql> show create table privence;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| privence | CREATE TABLE `privence` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
错误的操作
mysql> show table privence;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'privence' at line 1
-----------------------------==================================================
外键约束的参照操作
1.CASCADE:从父表删除或更新且自动删除或更新指标的匹配的行
2.SET NULL:从父表删除或更新行,并设置指标的外键列为NULL。
如果使用该选项,必须保证值列表没有指定NOT NULL
3.RESTRICT:拒绝对父表的删除或更新操作
4.NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
创建表users1
删除操作时使用cascade操作 对父表privence删除 一条记录 子表users1中与之关联的记录全部删除
删除操作时使用set null操作 对父表privence删除一条记录 子表users1中与之关联的记录全部变为null
当然 这要建立在该删除字段在子表中可以允许为null 值 不然操作失败
mysql> create table users1(
->
-> id smallint unsigned AUTO_INCREMENT PRIMARY KEY,
-> username varchar(20) not null,
-> pid smallint unsigned,
-> foreign key users1(pid) references privence(id) ON DELETE CASCADE
-> );
插入数据:
mysql> INSERT users1(username) values('tomcate');
Query OK, 1 row affected
mysql> INSERT users1(username) values('魔法少女');
Query OK, 1 row affected
mysql> INSERT users1(username) values('厄加特');
Query OK, 1 row affected
mysql> INSERT privence (pname) values("四川")
-> ;
Query OK, 1 row affected
mysql> INSERT privence (pname) values("北京")
;
Query OK, 1 row affected
mysql> INSERT privence (pname) values("自贡")
;
Query OK, 1 row affected
删除操作:
mysql> delete from privence where id=3;
username为战神的记录被删除
==========================================================
增加,删除列
ALTER TABLE table_name ADD COLUMN [first| after column]:
alter table users1 add wife varchar(20) not null first;
ALTER TABLE users1 ADD age smallint unsigned not null AFTER id;
mysql> alter table users1 drop wife;
==============================================================
增加约束 删除约束:
添加唯一约束:ALTER TABLE users2 ADD UNIQUE(username);
添加外键约束:ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES privence (id);
添加默认约束:ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除默认约束:ALTER TABLE users2 ALTER age DROP DEFAULT;
数据表的修改操作:无非就是添加列,删除列,添加约束,删除约束。用的是ALTER,而INSERT是对数据表添加插入记录用的。
mysql> create table users2(
-> username varchar(20) not null,
-> age smallint unsigned not null
-> );
添加主键约束:
mysql> alter table users2 ADD id smallint unsigned AUTO_INCREMENT PRIMARY KEY;
mysql> SHOW COLUMNS FROM USERS2;
mysql> alter table users2 alter age set default 15;
mysql> alter table users2 alter age drop default;
mysql> alter table users2 add pid smallint unsigned;
mysql> alter table users2 add foreign key (pid) references privence(id);
mysql> alter table users2 add unique(username);
============================================================
删除约束:
主键约束:alter table users2 drop primary key;
找到唯一约束的索引:
mysql> alter table users2 drop index username;
索引为username
唯一约束:alter table users2 drop index username
找到外键约束的索引:
mysql> show create table users2;
外键约束:mysql> alter table users2 drop index users2_ibfk_1;
===================================================================================
修改列的位置(modify):alter table user2 modify id smallint unsigned not null first;
修改列的名称(change):alter table user2 changed pid p_id tinyint unsigned not null;
数据表更名:
方法一:alter table user2 rename user3;(user2变user3)
方法二:rename table user3 to user4(可为多张数据表更名)
尽量不要随意更改数据列和数据表的名字;