10.6 约束条件10.61 not null 、default[url=]![]() [/url]
create table t15( id int, name char(16) not null, sex enum('male','female','other') not null default "male");#alter table t15 modify name char(16) not null;insert into t15(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3');mysql> desc t15;+-------+-------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------------------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | char(16) | NO | | NULL | || sex | enum('male','female','other') | NO | | male | |+-------+-------------------------------+------+-----+---------+-------+mysql> select * from t15;+------+-------+------+| id | name | sex |+------+-------+------+| 1 | egon1 | male || 2 | egon2 | male || 3 | egon3 | male |+------+-------+------+[url=]![]() [/url]
10.62 uniquemysql中存在一种专门的数据结构，叫 key，又称为索引，通过该数据结构可以减少 i/o 次数，从而加速查询效率 index key ： 只有加速查询的效果，没有约束的功能 unique key：不仅有加速查询的效果，还附加了一种约束的功能 primary key：不仅有加速查询的效果，还附加了一种约束的功能，并且innodb存储引擎会按照主键字段的值 来组织表中所有的数据，所以一种inndob表中必须有、而且只能有一个主键，通常为该表的id字段 unique:限制字段的值的唯一性，单从约束角度去看：唯一性约束 [url=]![]() [/url]
#单列唯一create table t16( id int unique, name char(16));# 联合唯一(不能完全相同)create table server( id int unique, ip char(15), port int, unique(ip,port));mysql> desc server;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id | int(11) | YES | UNI | NULL | || ip | char(15) | YES | MUL | NULL | || port | int(11) | YES | | NULL | |+-------+----------+------+-----+---------+-------+insert into server values(1,'1.1.1.1',3306),(2,'1.1.1.1',3307),(3,'1.1.1.2',3306);mysql> select * from server;+------+---------+------+| id | ip | port |+------+---------+------+| 1 | 1.1.1.1 | 3306 || 2 | 1.1.1.1 | 3307 || 3 | 1.1.1.2 | 3306 |+------+---------+------+[url=]![]() [/url]
10.63 primary keyprimary key:单单从约束角度去看，primary key就等同于 not null + unique 强调： 1、一张表中必须有，并且只能有一个主键2、一张表中都应该有一个id字段，而且应该把id字段做成主键 [url=]![]() [/url]
create table t17( id int primary key, name char(16), age int, sex char(6))engine=innodb;#联合主键(不完全相同，但不能为空)create table t19( ip char(15), port int, primary key(ip,port));mysql> desc t19;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ip | char(15) | NO | PRI | | || port | int(11) | NO | PRI | 0 | |+-------+----------+------+-----+---------+-------+[url=]![]() [/url]
10.64 auto_increment只能给被约束成key的字段加自增属性，默认起始位置是1，步长也为1 [url=]![]() [/url]
# primary key auto_incrementcreate table t20( id int primary key auto_increment, name char(16))engine=innodb;mysql> insert t20(name) values('egon1');mysql> insert t20(name) values('egon2');mysql> select * from t20;+----+-------+| id | name |+----+-------+| 1 | egon1 || 2 | egon2 |+----+-------+[url=]![]() [/url]
10.7 表关系10.71 多对一(foreign key)1、把所有数据都存放于一张表的弊端: 约束1：在创建表时，先建被关联的表dep，才能建关联表emp [url=]![]() [/url]
create table dep( id int primary key auto_increment, dep_name char(10), dep_comment char(60));mysql> desc dep;+-------------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || dep_name | char(10) | YES | | NULL | || dep_comment | char(60) | YES | | NULL | |+-------------+----------+------+-----+---------+----------------+create table emp( id int primary key auto_increment, name char(16), gender enum('male','female') not null default 'male', dep_id int, foreign key(dep_id) references dep(id));mysql> desc emp;+--------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(16) | YES | | NULL | || gender | enum('male','female') | NO | | male | || dep_id | int(11) | YES | MUL | NULL | |+--------+-----------------------+------+-----+---------+----------------+[url=]![]() [/url]
约束2：在插入记录时，必须先插被关联的表dep，才能插关联表emp [url=]![]() [/url]
insert into dep(dep_name,dep_comment) values('sb教学部','sb辅导学生学习，教授python课程'),('外交部','形象大使'),('nb技术部','nb技术能力有限部门');mysql> select * from dep;+----+-------------+-------------------------------------------+| id | dep_name | dep_comment |+----+-------------+-------------------------------------------+| 1 | sb教学部 | sb辅导学生学习，教授python课程 || 2 | 外交部 | 形象大使 || 3 | nb技术部 | nb技术能力有限部门 |+----+-------------+-------------------------------------------+insert into emp(name,gender,dep_id) values('alex','male',1),('egon','male',2),('lxx','male',1),('wxx','male',1),('wenzhou','female',3);mysql> select * from emp;+----+---------+--------+--------+| id | name | gender | dep_id |+----+---------+--------+--------+| 1 | alex | male | 1 || 2 | egon | male | 2 || 3 | lxx | male | 1 || 4 | wxx | male | 1 || 5 | wenzhou | female | 3 |+----+---------+--------+--------+[url=]![]() [/url]
约束3：更新与删除都需要考虑到关联与被关联的关系(不能直接改变dep表的id) 解决方案： ![]() View Code
10.72 多对多(foreign key)1、什么是多对多 两张表之间是一个双向的多对一关系，称之为多对多 2、如何实现？ 建立第三张表，该表中有一个字段是fk左表的id，还有一个字段是fk右表的id [url=]![]() [/url]
create table author( id int primary key auto_increment, name char(16));create table book( id int primary key auto_increment, bname char(16), price int);insert into author(name) values('egon'),('alex'),('wxx');mysql> select * from author;+----+------+| id | name |+----+------+| 1 | egon || 2 | alex || 3 | wxx |+----+------+insert into book(bname,price) values('python从入门到入土',200),('葵花宝典切割到精通',800),('九阴真经',500),('九阳神功',100);mysql> select * from book;+----+-----------------------------+-------+| id | bname | price |+----+-----------------------------+-------+| 1 | python从入门到入土 | 200 || 2 | 葵花宝典切割到精通 | 800 || 3 | 九阴真经 | 500 || 4 | 九阳神功 | 100 |+----+-----------------------------+-------+create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade);insert into author2book(author_id,book_id) values(1,3),(1,4),(2,2),(2,4),(3,1),(3,2),(3,3),(3,4);mysql> select * from author2book;+----+-----------+---------+| id | author_id | book_id |+----+-----------+---------+| 1 | 1 | 3 || 2 | 1 | 4 || 3 | 2 | 2 || 4 | 2 | 4 || 5 | 3 | 1 || 6 | 3 | 2 || 7 | 3 | 3 || 8 | 3 | 4 |+----+-----------+---------+[url=]![]() [/url]
10.73 一对一(unique+foreign key)[url=]![]() [/url]
一对一：左表的一条记录唯一对应右表的一条记录，反之也一样create table customer( #先建被参照的表 id int primary key auto_increment, name char(20) not null, qq char(10) not null, phone char(16) not null);create table student( id int primary key auto_increment, class_name char(20) not null, customer_id int unique, #该字段一定要是唯一的,一对一 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique on delete cascade on update cascade);insert into customer(name,qq,phone) values('李飞机','31811231',13811341220),('王大炮','123123123',15213146809),('守榴弹','283818181',1867141331),('吴坦克','283818181',1851143312),('赢火箭','888818181',1861243314),('战地雷','112312312',18811431230);mysql> select * from customer;+----+-----------+-----------+-------------+| id | name | qq | phone |+----+-----------+-----------+-------------+| 1 | 李飞机 | 31811231 | 13811341220 || 2 | 王大炮 | 123123123 | 15213146809 || 3 | 守榴弹 | 283818181 | 1867141331 || 4 | 吴坦克 | 283818181 | 1851143312 || 5 | 赢火箭 | 888818181 | 1861243314 || 6 | 战地雷 | 112312312 | 18811431230 |+----+-----------+-----------+-------------+insert into student(class_name,customer_id) values('python',3),('java',4),('c++',5);mysql> select * from student;+----+-------------+-------------+| id | class_name | customer_id |+----+-------------+-------------+| 1 | python | 3 || 2 | java | 4 || 3 | c++ | 5 |+----+-------------+-------------+[url=]![]() [/url]
|
|
收藏
淘帖
踩
