文章目录
1、mysql的约束属性
1.1 主键约束 primary key
作用：主键约束 primary key 字符的值不能重复，不能为NULL，一个表中只能有一个主键
主键只能有一个，但是可以有多个字段，可以是两个字段组合成一个主键
mysql> create table gfy (id int,name varchar(10),age tinyint,primary key (id,namee));
Query OK, 0 rows affected (0.04 sec)
mysql> desc gfy;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(10) | NO | PRI | NULL | |
| age | tinyint | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
一个表中只能有一个组件
mysql> desc gfy;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(10) | NO | PRI | NULL | |
| age | tinyint | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table gfy add primary key (age);
ERROR 1068 (42000): Multiple primary key defined
mysql>
1.2 唯一键约束 unique
字段的值不能重复，但可以为NULL，一个表中可以有多个唯一键
mysql> create table gfy1 (id int,name varchar(10),age tinyint,unique key(id));
Query OK, 0 rows affected (0.03 sec)
mysql> desc gfy1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | UNI | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
1.3 非空约束 NOT NULL
字段的值不能为空
1.4 默认值约束 default
字段的值如果没有设置，则使用默认值自动填充
1.5 自增填充 auto_increment
字段的值如果没有设置，默认从1开始填充，并每行自动递增1（在上一行的基础之上），要求设置自增约束的字段必须为主键字段
mysql> create table gfy4 (id int(4) zerofill primary key auto_increment,name varcchar(40) default '匿名',carid int(18) unique key, hobby varchar(10) not null);
Query OK, 0 rows affected, 3 warnings (0.02 sec)
mysql> show tables
-> ;
+---------------+
| Tables_in_abc |
+---------------+
| gfy |
| gfy1 |
| gfy4 |
+---------------+
3 rows in set (0.01 sec)
mysql> desc gfy4;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | 匿名 | |
| carid | int | YES | UNI | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql>
#插入操作
mysql> select * from gfy4;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | abc | 2483724 | 看书 |
+------+------+---------+--------+
1 row in set (0.00 sec)
mysql> insert into gfy4 (name,carid,hobby) values('dsv',2483724,'看书');
ERROR 1062 (23000): Duplicate entry '2483724' for key 'gfy4.carid'
mysql> insert into gfy4 (name,carid,hobby) values('dsv',2483725,'看书');
Query OK, 1 row affected (0.01 sec)
mysql> select * from gfy4;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | abc | 2483724 | 看书 |
| 0003 | dsv | 2483725 | 看书 |
+------+------+---------+--------+
2 rows in set (0.00 sec)
mysql> insert into gfy4 (name,carid,hobby) values('dsv',2483726,'看书');
Query OK, 1 row affected (0.00 sec)
mysql> select * from gfy4;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | abc | 2483724 | 看书 |
| 0003 | dsv | 2483725 | 看书 |
| 0004 | dsv | 2483726 | 看书 |
+------+------+---------+--------+
3 rows in set (0.00 sec)
mysql>
1.6 外键约束 foreign key
保证主键表和外键表相关连的数据的完整性和一致性
- 外键约束
主键表：alter table 主表名 add primary key (公共字段)；
外键表：alter table 外表名 add foreign key (公共字段) reference 主表名 （公共字段）；
外键约束是如何保证数据的完整性和一致性
插入数据时，需要先保证主键表有相关公共字段的数据，才能再外键表插入相关的数据
删除数据时，需要先保证外键表的数据删除，才能删除主表的数据
删除外键约束
alter table account drop foreign key
alter table account drop key
2、数据表的高级操作
2.1 克隆表
#先复制表再插入数据
mysql> create table test01 like gfy4;
Query OK, 0 rows affected, 1 warning (0.51 sec)
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| gfy |
| gfy1 |
| gfy4 |
| test01 |
+---------------+
4 rows in set (0.00 sec)
mysql> desc test01;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | 匿名 | |
| carid | int | YES | UNI | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert into test01 select *from gfy4;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test01;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | abc | 2483724 | 看书 |
| 0003 | dsv | 2483725 | 看书 |
| 0004 | dsv | 2483726 | 看书 |
+------+------+---------+--------+
3 rows in set (0.00 sec)
#一步到位创建克隆表
mysql> create test02 (select * from gfy4);
ERROR 1064 (42000): 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 'test02 (select * from gfy4)' at line 1
mysql> create table test02 (select * from gfy4);
Query OK, 3 rows affected, 1 warning (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| gfy |
| gfy1 |
| gfy4 |
| test01 |
| test02 |
+---------------+
5 rows in set (0.00 sec)
#两种方式之间的对比
mysql> desc test02;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id | int(4) unsigned zerofill | NO | | 0000 | |
| name | varchar(40) | YES | | 匿名 | |
| carid | int | YES | | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from test02;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | abc | 2483724 | 看书 |
| 0003 | dsv | 2483725 | 看书 |
| 0004 | dsv | 2483726 | 看书 |
+------+------+---------+--------+
3 rows in set (0.00 sec)
mysql> desc test01;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | 匿名 | |
| carid | int | YES | UNI | NULL | |
| hobby | varchar(10) | NO | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from test01;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | abc | 2483724 | 看书 |
| 0003 | dsv | 2483725 | 看书 |
| 0004 | dsv | 2483726 | 看书 |
+------+------+---------+--------+
3 rows in set (0.00 sec)
mysql>
虽然数据相同，但是两者的表结构有差异，完全复制还是推荐使用第一种方法
2.2 清空表
delete方式清空表
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| gfy |
| gfy1 |
| gfy4 |
| test01 |
| test02 |
+---------------+
5 rows in set (0.00 sec)
mysql> delete from test01;
Query OK, 3 rows affected (0.01 sec)
mysql> insert into test01 (name,carid,hobby) values('dsv',2483726,'看书');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test01;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0005 | dsv | 2483726 | 看书 |
+------+------+---------+--------+
1 row in set (0.00 sec)
mysql>
turncate方式清空表
mysql> truncate table test01;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test01 (name,carid,hobby) values('dsv',2483726,'看书');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test01;
+------+------+---------+--------+
| id | name | carid | hobby |
+------+------+---------+--------+
| 0001 | dsv | 2483726 | 看书 |
+------+------+---------+--------+
1 row in set (0.00 sec)
mysql>
2.3 临时表
临时表相当于存在于缓存之中
mysql> create temporary table test03 (id int,name varchar(20),age int,sex char(1)));
Query OK, 0 rows affected (0.00 sec)
#show tables;命令无法查看到新建的表
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| gfy |
| gfy1 |
| gfy4 |
| test01 |
| test02 |
+---------------+
5 rows in set (0.00 sec)
mysql> desc test03
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | NULL |
| name | varchar(20) | YES | | NULL | NULL |
| age | int | YES | | NULL | NULL |
| sex | char(1) | YES | | NULL | NULL |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
#插入数据查看表中的内容
mysql> insert into test03 values(1,'gfy',18,'男');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test03
-> ;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 1 | gfy | 18 | 男 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql>
#此时在另外一台终端上也无法查看到新建的test03的表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| abc |
| bbs |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> use abc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| gfy |
| gfy1 |
| gfy4 |
| test01 |
| test02 |
+---------------+
5 rows in set (0.00 sec)
mysql>
2.4 总结
克隆表
create table 新表名 like 旧表名； 克隆表结构
insert into 新表明 select * from 旧表名 克隆表数据
create table 新表名 （select * from 旧表名）仅仅复制了数据，表的结构可能会不一样清空表
delete from 表名； 一条一条的删除记录，效率比较慢，自增长字段仍保留原有的记录
truncate table 表名 ； 直接重建表，清空表的效率较快，自增字段也会被重置临时表
create temporary table 表名 （）；
与一般表的相同点：都可以对表中数据进行增删改查表数据记录，也可以查看、删除表对象
不同点：show tables 看不到临时表，临时表只能在当前会话连接中有效，退出当前会话临时表会删除，临时表就会失效