1.主键
PRIMARY KEY 主键的使用
字段值不允许重复，且不允许赋NULL值
创建主键
root@mysqldb 10:11: [d1]> CREATE TABLE t3(-> name varchar(10) PRIMARY KEY,-> age int,-> class varchar(8)-> );
Query OK, 0 rows affected (0.01 sec)root@mysqldb 10:12: [d1]> create table t4 (-> name char(10), pay float(7,2) default 20000,-> primary key(name)-> );
Query OK, 0 rows affected (0.01 sec)-- 创建复合主键
root@mysqldb 10:15: [d1]> create table t5( -> cip char(15) , port int , stuat enum("yes","no") , -> primary key(cip , port ) );
Query OK, 0 rows affected (0.01 sec)删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
root@mysqldb 10:16: [d1]> ALTER TABLE t4 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0-- 在已有的表中添加主键
root@mysqldb 10:20: [d1]> DESC t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| cip | char(15) | NO | PRI | NULL | |
| port | int(11) | NO | PRI | NULL | |
| stuat | enum('yes','no') | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)root@mysqldb 10:21: [d1]> ALTER TABLE t5 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@mysqldb 10:21: [d1]> DESC t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| cip | char(15) | NO | | NULL | |
| port | int(11) | NO | | NULL | |
| stuat | enum('yes','no') | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)-- 添加复合主键
root@mysqldb 10:23: [d1]> ALTER TABLE t5 ADD PRIMARY KEY(cip, port);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0-- 添加主键
root@mysqldb 10:24: [d1]> ALTER TABLE t4 ADD PRIMARY KEY(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0主键与 AUTO_INCREMENT 连用,字段值自增长
root@mysqldb 10:25: [d1]> create table t8 ( id int primary key auto_increment ,-> name char(10) , age tinyint , sex enum("m","g") , homeaddr char(10) );
Query OK, 0 rows affected (0.01 sec)root@mysqldb 10:27: [d1]> insert into t8 (name,age,sex,homeaddr)values("tom",19,"m","usa");
Query OK, 1 row affected (0.01 sec)root@mysqldb 10:27: [d1]> insert into t8 (name,age,sex,homeaddr)values("jerry",19,"m","usa");
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:27: [d1]> insert into t8 (name,age,sex,homeaddr)values("lucy",19,"m","usa");
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:27: [d1]> select * from t8;
+----+-------+------+------+----------+
| id | name | age | sex | homeaddr |
+----+-------+------+------+----------+
| 1 | tom | 19 | m | usa |
| 2 | jerry | 19 | m | usa |
| 3 | lucy | 19 | m | usa |
+----+-------+------+------+----------+
3 rows in set (0.00 sec)root@mysqldb 10:27: [d1]> insert into t8 values( 8 ,"lucy",29,"m","usa");
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:27: [d1]> insert into t8 (name,age,sex,homeaddr)values("jerry",29,"m","usa");
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:27: [d1]> insert into t8 values (null , "summer" , 28 , "g" , "china" );
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:28: [d1]> select * from t8;
+----+--------+------+------+----------+
| id | name | age | sex | homeaddr |
+----+--------+------+------+----------+
| 1 | tom | 19 | m | usa |
| 2 | jerry | 19 | m | usa |
| 3 | lucy | 19 | m | usa |
| 8 | lucy | 29 | m | usa |
| 9 | jerry | 29 | m | usa |
| 10 | summer | 28 | g | china |
+----+--------+------+------+----------+
6 rows in set (0.00 sec)2.外键
限制字段赋值，插入记录时，字段值在另一个表字段值范围内选择。
-- 建表
root@mysqldb 10:31: [d1]> create table yg_tab(-> yg_id int primary key auto_increment,-> user char (10), -> sex enum("man","woman")-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)root@mysqldb 10:31: [d1]> create table gz_tab(-> gz_id int , pay float(7,2),-> foreign key(gz_id) references yg_tab(yg_id) -> on update cascade on delete cascade )engine=innodb ;
Query OK, 0 rows affected (0.01 sec)-- 查看建表语句
root@mysqldb 10:32: [d1]> show create table yg_tab;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| yg_tab | CREATE TABLE `yg_tab` (`yg_id` int(11) NOT NULL AUTO_INCREMENT,`user` char(10) DEFAULT NULL,`sex` enum('man','woman') DEFAULT NULL,PRIMARY KEY (`yg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)root@mysqldb 10:33: [d1]> show create table yg_tab \G
*************************** 1. row ***************************Table: yg_tab
Create Table: CREATE TABLE `yg_tab` (`yg_id` int(11) NOT NULL AUTO_INCREMENT,`user` char(10) DEFAULT NULL,`sex` enum('man','woman') DEFAULT NULL,PRIMARY KEY (`yg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)-- 删除外键
root@mysqldb 10:36: [d1]> alter table gz_tab drop foreign key gz_tab_ibfk_1 ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0-- 在已有表添加外键
root@mysqldb 10:37: [d1]> alter table gz_tab add-> foreign key(gz_id) references yg_tab(yg_id) on update cascade on delete cascade;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@mysqldb 10:37: [d1]> select * from yg_tab;
Empty set (0.00 sec)-- 插入数据会报错
root@mysqldb 10:37: [d1]> insert into gz_tab values (1,20000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`d1`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)root@mysqldb 10:39: [d1]> insert into yg_tab(user,sex)values("bob","man");
Query OK, 1 row affected (0.01 sec)root@mysqldb 10:39: [d1]> insert into yg_tab(user,sex)values("lucy","woman");
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:39: [d1]> select * from yg_tab;
+-------+------+-------+
| yg_id | user | sex |
+-------+------+-------+
| 1 | bob | man |
| 2 | lucy | woman |
+-------+------+-------+
2 rows in set (0.00 sec)root@mysqldb 10:40: [d1]> insert into gz_tab values (1,20000);
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:40: [d1]> insert into gz_tab values (2,20000);
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:40: [d1]> insert into gz_tab values (3,20000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`d1`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
root@mysqldb 10:40: [d1]> insert into yg_tab(user,sex)values("lili","woman");
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:40: [d1]> insert into gz_tab values(3,40000);
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:40: [d1]> select * from gz_tab;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 20000.00 |
| 2 | 20000.00 |
| 3 | 40000.00 |
+-------+----------+
3 rows in set (0.00 sec)root@mysqldb 10:40: [d1]> update yg_tab set yg_id=8 where yg_id=2 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0root@mysqldb 10:41: [d1]> delete from yg_tab where yg_id=8;
Query OK, 1 row affected (0.00 sec)root@mysqldb 10:41: [d1]> alter table gz_tab add primary key(gz_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
