数据库之数据表控制语句
一、约束条件相关语句
1、主键约束(主键约束要求主键列的数据唯一,不允许为空)
#创建库 mysql> create database test1; #进入到库中 mysql> use test1; #创建一个带有主键约束的表 mysql> create table tab1( -> id int(10), -> name varchar(10) primary key, -> sex varchar(5), -> info varchar(200) -> ); Query OK, 0 rows affected (0.01 sec)
确定创建的是否为主键(该列是否有PRI字样):
上述是在定义列的同时定义主键,下面来写一下定义完所有列之后指定主键:
mysql> create table tab2( -> id int(10), -> name varchar(10), -> sex varchar(10), -> primary key(id) # 括号里定义主键的列 -> );
2、非空约束(不允许列的值为空)
mysql> create table tab3( -> id int(6) not null, # not null :不允许为空 -> name varchar(10) -> );
3、设置值的唯一性(不允许重复数据,可以为空,但只能有一个空,否则就会被视为重复)
mysql> create table tab4( -> id int not null unique, -> name varchar(20) -> );
可以看到其被标识为了主键,但是在创建时并没有指定它是主键,而是这一列的属性基本满足了主键的要求,如唯一、不可以为空。
4、设置列的默认值(如果该列为空,则写入默认值)
mysql> create table tab5( -> id int(2) not null, -> name varchar(20), -> project varchar(20) default ‘mysql‘ -> );
5、设置自增值(一般用于id列,自增列必须设置为主键)
注:mysql只允许设置初始值,而不允许设置自增值,也就是说,可以设置为第一个值为5,然后依次递增,如:5、6、7.....但不可以设置其一次递增2个数,比如:5、7、9......
mysql> create table tab6( -> id int not null primary key auto_increment, -> name varchar(20) -> );
测试其自增效果:
mysql> insert into tab6(name) values(‘zyz‘),(‘lisi‘); mysql> select * from tab6; +----+------+ | id | name | +----+------+ | 1 | zyz | | 2 | lisi | +----+------+
从上面的测试可以看出,只插入了两个name的值,并没有插入id的值,但是查看表数据时,id已经有值了,说明自增生效
设置自增的起始值
mysql> create table tab7( -> id int primary key auto_increment, -> name varchar(20) -> )auto_increment=6; #插入数据验证 mysql> insert into tab7(name) values(‘zyz‘),(‘lisi‘),(‘zhangsan‘); mysql> select * from tab7; +----+----------+ | id | name | +----+----------+ | 6 | zyz | | 7 | lisi | | 8 | zhangsan | +----+----------+
二、ALTER指令的使用
1、修改列值的数据长度
mysql> desc tab1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(10) | NO | PRI | NULL | | | sex | varchar(5) | YES | | NULL | | | info | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ #修改name 字符长度为20 mysql> alter table tab1 modify name varchar(20);
2、修改字段名(在修改字段名的同时,还可以修改其新字段名的数据类型及数据长度)
mysql> desc tab1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | name | varchar(20) | NO | PRI | NULL | | | sex | varchar(5) | YES | | NULL | | | info | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ mysql> alter table tab1 change info info2 char(20);
3、向表中插入一个新的字段
1)在最后一列插入新列:
mysql> desc tab3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(6) | NO | | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) #插入一个 zuihou 列 mysql> alter table tab3 add zuihou int(10);
2)在表格开头插入新列:
mysql> alter table tab3 add sex char(10) first;
3)在指定的列后面插入新列:
mysql> alter table tab3 add local varchar(255) after name;
4、添加字段时添加约束
mysql> alter table tab3 add hello varchar(20) default ‘work‘;
5、添加一个外键
进行此操作前,需要自行找一个有主键的表(我这里tab1表的name列是主键)
tab1表结构:
tab3表结构:
现在将t3表的name列添加为t1表的name列的外键(其中t3_t1_name为自定义的约束名称):
mysql> alter table tab3 add constraint tab3_tab1_name foreign key(name) references tab1(name);
查看tab3表的变化:
6、删除外键
将上面添加的外键删除,tab3_tab1_name是外键的名称。
mysql> alter table tab3 drop foreign key tab3_tab1_name; mysql> alter table tab3 drop key tab3_tab1_name;
——————————
说到了外键,这里就要谈谈什么是外键了
查询相关资料都是讲一些术语。说外键的主要作用是:保持数据的一致性、完整性。听得我是一头雾水,表示没有听懂。
这里我也是借鉴一位大佬的话来解释的,可以看到下方小表
+-------+ ref +-------+
| sub | ------> | main |
+-------+ +-------+
从表(sub)的某列引用(ref)主表(main)的某列的值。
比方学生表有个学生编号(sid),分数表中的学生列(stu)引用学生表的学 生编号,此时对于分数表的 stu 来说。学生表的 sid 就是外键。
从表也叫外键表,主表也叫主键表、外表,列也叫字段。
所以在设计的时候。就给表1加入一个外键,这个外键就是表2中的学号字段。那么这样表1就是主表,表2就是子表。
你的主从关系理解颠倒了。你的图中,表1的确是主表。表2是子表,但不是叫做给表1加入一个外键,而是给表2加入一个外键,表2中的学号 字段就叫外键,它是表1学号字段的主键。
你能够这样说:表1的学号字段是表2的外键
————————外键解释结束——————
7、删除列
mysql> alter table tab3 drop zuihou;
注意:如果要删除的列和其他表中的列有关联关系,则需要先删除关系,再删除列。否则当以后再创建了相同名称的列时,会自动将其建立关系。
8、修改列的顺序
mysql> alter table tab3 modify name varchar(10) first;
9、删除表
#直接删除 mysql> drop table tab6; Query OK, 0 rows affected (0.01 sec) #再次删除这个表,可以看到已经提示说没有这个表了,说明已经删除 mysql> drop table tab6; ERROR 1051 (42S02): Unknown table ‘test1.tab6‘ #进行判断后删除,if exists表示如果存在就删除 mysql> drop table if exists tab6; Query OK, 0 rows affected, 1 warning (0.00 sec) #可以看到上述返回的信息有1个warning事项,可以执行以下命令进行查看 mysql> show warnings; # 返回的信息显示不知道test1库中的tab6表 +-------+------+----------------------------+ | Level | Code | Message | +-------+------+----------------------------+ | Note | 1051 | Unknown table ‘test1.tab6‘ | +-------+------+----------------------------+ 1 row in set (0.00 sec)
同样,当执行删除表操作时,如果存在关联关系,则需要先删除关联关系,再删除表
同样,当执行删除表操作时,如果存在关联关系,则需要先删除关联关系,再删除表
同样,当执行删除表操作时,如果存在关联关系,则需要先删除关联关系,再删除表