MySQL約束(語法指令)
#使用default 預設默認值
#創建 msdql_3數據表 default默認
create table mysql_3(
id int default 1,
name varchar(20) default '預設對象');
#顯示表結構
desc mysql_3;
>>>
+-------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | 1 | |
| name | varchar(20) | YES | | 預設對象 | |
+-------+-------------+------+-----+--------------+-------+
#對id插入值
insert into mysql_3 (id) values ('2')
#顯示表數據
select * from mysql_3;
>>>
+------+--------------+
| id | name |
+------+--------------+
| 2 | 預設對象 |
+------+--------------+
#對默認值進行修改
#顯示表結構
desc mysql_3;
>>>
+-------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | 1 | |
| name | varchar(20) | YES | | 預設對象 | |
+-------+-------------+------+-----+--------------+-------+
#使用alter table .. modify id ..default進行修改
alter table mysql_3 modify id int default 0 ;
#顯示表結構
desc mysql_3;
>>>
+-------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | 0 | |
| name | varchar(20) | YES | | 預設對象 | |
+-------+-------------+------+-----+--------------+-------+
#刪除默認值
>>>
+-------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | 0 | |
| name | varchar(20) | YES | | 預設對象 | |
+-------+-------------+------+-----+--------------+-------+
#刪除default
alter table mysql_3 modify id int;
>>>
+-------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | 預設對象 | |
+-------+-------------+------+-----+--------------+-------+
#創建 不為空數據表 not null
#創建 不為空數據表 not null
create table pa1(
id int not null,
name varchar(20) not null);
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
#添加
insert into pa1(id) values(2);
#報錯(字段name 沒有默認值)
>>>
ERROR 1364 (HY000): Field 'name' doesn't have a default value
#添加
insert into pa1(id,name) values(2,'不為空');
#查看表數據
>>>
+----+-----------+
| id | name |
+----+-----------+
| 2 | 不為空 |
+----+-----------+
#設置id 為unique(唯一性) name(不為空)
create table pa2(
id int unique,
name varchar(32) not null);
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------
#添加
insert pa2 values(1,'bot1')
>>Query OK, 1 row affected (0.01 sec)
#添加
insert pa2 values(1,'bot2');
#報錯 因為id 具"唯一"的約束性值
>>>ERROR 1062 (23000): Duplicate entry '1' for key 'id'
#id_unique 對同id同name組進行約束
#constraint id_unique unique 對同組進行約束 if id and name 而非 if id or name
create table pa3 (
id int,
name varchar(32) ,
constraint id_unique unique(id,name)
);
insert pa3 values (1,'bot1'); <<<<同組
>>>Query OK, 1 row affected (0.01 sec)
insert pa3 values (1,'bot2');
>>>Query OK, 1 row affected (0.01 sec)
insert pa3 values (1,'bot1'); <<<<同組
>>>ERROR 1062 (23000): Duplicate entry '1-bot1' for key 'id_unique'
#添加唯一約束
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
alter table pa2 modify name varchar(32) unique;
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(32) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
#刪除id_unique約束
#id_unique 約束
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------
#刪除id_unique約束
alter table pa3 drop index id_unique;
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#primary key添加主鍵約束
#相當於 唯一約束+非空的約束
create table pa4 (
id int primary key,
name varchar(20)
);
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#聯合主鍵約束
#constraint.... primary key
alter table pa5 add constraint id_name primary key(id,name);
>>>
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
#auto_increment自增長
#隨著數據增加而增多
# auto_increment自增長 但必須是唯一,所以需要添加primary key,之類具唯一性的約束
#一張表只能有一個自增長
create table pa6(
id int primary key auto_increment,
name varchar(20)
);
>>>
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
#添加
insert into ap6(name) values ('注意id變化');
>>>
+----+----------------+
| id | name |
+----+----------------+
| 1 | 注意id變化 |
+----+----------------+
#添加
insert into ap6(name) values ('注意id變化');
>>>
+----+----------------+
| id | name |
+----+----------------+
| 1 | 注意id變化 |
| 2 | 注意id變化 |
+----+----------------+
#unsigned 無符號約束
#tinyint最大字節-128~127 OR 0~255
#unsigned 無符號約束(意味不能添加負號:-
create table pa7(
id int,
age tinyint unsigned
);
>>>
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
insert into pa7 (age)values(-10);
>>>ERROR 1264 (22003): Out of range value for column 'age' at row 1
insert into pa7 (age)values(10);
>>>Query OK, 1 row affected (0.01 sec)
#zerofill零填充
#zerofill零填充針對不足條件"位"數者,在其前面補0直到滿自定位數
create table ap7(
id int,
age int(6) zerofill)
;
>>>
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | int(6) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
insert into ap7 (age) values (6);
>>>
+------+--------+
| id | age |
+------+--------+
| NULL | 000006 |
+------+--------+
insert into ap7 (age) values(6000);
>>>
+------+--------+
| id | age |
+------+--------+
| NULL | 000006 |
| NULL | 006000 |
+------+--------+
#constraint(外鏈式約束)
#創建一張表 id具有 唯一性與自增長且不為空,name 不為空
create table HR (
id int primary key auto_increment,
name varchar(32) not null,
deptno int
);
>>>
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| deptno | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
create table dept(
#創建一張表 deptid具有 唯一性與自增長且不為空
deptid int primary key auto_increment ,
dename varchar(32),
loc varchar(32)
);
>>>
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| deptid | int(11) | NO | PRI | NULL | auto_increment |
| dename | varchar(32) | YES | | NULL | |
| loc | varchar(32) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------
# constraint(外鏈式) ,添加在HR表中 由HR表的deptno連結dept表中的deptid
#fk_name 是自定義名稱(隨便取)刪除外鏈時會用到
alter table HR add constraint fk_name foreign key (deptno) references dept(deptid);
insert into dept (deptid,dename) values (1,'人力');
insert into dept (deptid,dename) values (2,'市場');
insert into dept (deptid,dename) values (3,'研發');
+--------+--------+------+
| deptid | dename | loc |
+--------+--------+------+
| 1 | 人力 | NULL |
| 2 | 市場 | NULL |
| 3 | 研發 | NULL |
+--------+--------+------+
#假如當HR表的deptno在進行添加時會判斷該添加值是否存在deptid表中的值
insert into HR (name,deptno) values ('老軌',3);
>>>Query OK, 1 row affected (0.01 sec)
#在這邊添加時就報錯了,因為4並不存在dept表中deptid的值
insert into HR (name,deptno) values ('老保',4);
>>>ERROR 1452 (23000): Cannot add or update a child row:
#刪除外鏈
#刪除外鏈 添加對象表HR 添加時的命名fk_name
alter table HR drop foreign key fk_name;
insert into HR (name,deptno) values ('老保',4);
>>>Query OK, 1 row affected (0.01 sec)
#級聯刪除
#例如dept表為主表,HR為從表
#如果有添加('老保',4)記得刪除,才可以添加從主關係
#on delete cascade主表被刪從表的數據也會被刪除的約束
#on update cascade 主表更新,從表刪
#on delete set null 主表刪,從表設置為空
alter table HR add constraint fk_name foreign key (deptno) references dept(deptid) on delete cascade;