2.mysql---约束和数据表操作

2.约束和数据表操作

1.约束：保证数据的完整性和一致性。
   根据操作数目划分为表级约束和列级约束：是根据数目，如果针对一个字段叫做列级约束，针对两个或者两个字段以上的话叫做表级约束。
   功能划分： 非空         NOT NULL
                     主键约束  PRIMARY KEY
                     唯一约束  UNIQUE KEY
                     默认约束   DEFAULT
                     外键约束   FOREIGN KEY：

外键约束：实现数据表的一对一或者一对多的关系。

 条件：(1):父表【参照的表】和子表【外键列的表】适用相同的存储引擎InnoDB，而且禁止适用表。
           (2):外键列和参照列具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
           (3):参照列没有索引的话，MYSQL才自动创建索引；
           (4):外键列没有索引的话，MYSQL不会创建索引。
1.1->.编辑数据表的默认存储引擎：default-storage-engin=INNODB[如果没有在my.ini下面mysqld下面自己加]

案例：

CREATE TABLE province(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-------->>格式和下面一样
pname VARCHAR(20) NOT NULL
);

CRETE TABLE users(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,                     --------------------------------->>格式和上面一样
FOREIGN KEY (pid) REFERENCES province(id)
);

SHOW INDEXES FROM province\G;    查看该表的索引以网格的形式
SHOW CRETE TABLE users;

 先在父表中插入记录

外键约束的参照操作：
1. CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
2. SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。子表不能是NOT NULL
3. RESTRICT:拒绝对父表删除或更新操作

开发当中一般使用逻辑的外键约束【我们在定义两张表的结构的时候是按照表的逻辑结构来定义，但是不使用FOREIGN KEY关键词定义】，很少使用物理的外键约束，因为只有InnoDB的存贮引擎才支持物理外键约束。其他引擎不支持，myisam等都不支持。实际项目中不定义物理的外键。但是不使用foreign key 这个关键词，而是按照某种逻辑进行

案例：
CREATE TABLE province(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pname VARCHAR(20) NOT NULL
);

CRETE TABLE users(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES province(id) ON DELETE CASCADE
);

INSERT prinvince(pename) VALUE('BeiJin');
INSERT prinvince(pename) VALUE('TianJin');
SELECT *FROM prinvince;

INSERT users(username,pid)VALUE('Jack',3);

INSERT users(username,pid)VALUE('rose',2);
INSERT users(username,pid)VALUE('jim',2);
SELECT *FROM users;

DELETE FROM prinvince WHERE id=2;
SELECT *FROM prinvince;
SELECT *FROM users; //区别一下删除后跟删除前的区别

 

表级约束和列级约束：
对一个数据列建立的约束，称为列级约束.----开发中用的较多
对多个数据列建立的约束，称为表级约束.----少用
列级约束既可以在列定义时声明，也可以在列定义后声明。
表级约束只能在列定义后声明。
NOT NULL和DEFAULT约束不存在表级约束

1.修改数据表
 添加单列
 ALTER TABLE db_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];默认是在最后面添加不写后面的关键词的话
 eg:

 ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
 ALTER TABLE users1 ADD passwd VARCHAR(30) NOT NULL AFTER username;
 ALTER TABLE users1 ADD truename VARCHAR(20) NOT NULL FIRST;

 2.添加多列（不能指定位置关系）
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...用逗号隔开);
 3.删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name;//单次删除
ALTER TABLE users1 DROP passwd, DROP age;//多次删除

 

1.添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...);

案例：

 CREATE TABLE users2(
 username VARCHAR(20) NOT NULL,
 pid SMALLINT UNSIGNED
 );

 ALTER TABLE users2 ADD id SMALLINT UNSIGNED;
 ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);

//备注：此处constrint是指定这个操作的名称； 可以不写CONSTRAINT 和这个名称
 SHOW COLUMNS FROM users2;

 

 2.添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);

eg:
 ALTER TABLE users2 ADD UNIQUE (username);
 SHOW COLUMNS FROM users2;

 3.添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name, ...） reference_definition

eg：
 ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES province (id);

 4.添加、删除默认约束

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

eg:
 //添加元素：ALTER TABLE users2 ADD age TINYINT UNSIGNED NOT NULL;
 //修改默认值：ALTER TABLE users2 ALTER age SET DEFAULT 15;
//删除默认值：ALTER TABLE users2 ALTER age DROP DEFAULT;

1.删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;

eg:
 ALTER TABLE users2 DROP PRIMARY KEY;
 2.删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

eg:
 ALTER TABLE users2 DROP INDEX username;
 
 3.删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
eg: 

先执行：SHOW CREATE TABLE user2；  查看系统自动的设置的名称，然后依据名称进行删除就行；

 ALTER TABLE users2 DROP FOREIGN KEY ...;

二 ------>>>

1.修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name];

eg：    

 ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; //将id字段的位置提到第一列
 ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL; //修改数据类型,需注意数据丢失的问题

2.修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] col_name new_col_name column_definition [FIRST|AFTER col_name];

eg:

 ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED; //id字段修改为p_id字段；
3.数据表更名
 方法1
 ALTER TABLE tbl_name RENAME [TO/AS] new_tbl_name
eg:

 ALTER TABLE users2 RENAME TO users3;
 
 ALTER TABLE users2 RENAME AS users4;
 
 ALTER TABLE users2 RENAME users5;

方法2
 RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...
eg:

 RENAME TABLE users5 TO users2; //少使用数据表的列名及表名的更名。

 

 

 删除表：DROP TABLE IF EXISTS user;