DBAMysql的约束条件键值外键

最新推荐文章于 2023-01-17 15:28:30 发布

运维螺丝钉

最新推荐文章于 2023-01-17 15:28:30 发布

阅读量359

点赞数

分类专栏： MySQL 文章标签： MySQL 约束条件健直

本文链接：https://blog.csdn.net/weixin_42816196/article/details/82598890

版权

MySQL 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

字段的约束条件

作用控制如何给字段赋值

是否允许赋空值 null
mysql> create table t7(name char(15) not null, class char(7) default "nsd1804",age tinyint(2) not null default 19, sex enum("boy","gril") not null default "boy");————创建表，赋值，是否为空值

Query OK, 0 rows affected (0.13 sec)

mysql> desc t7; ————查看表结构

+-------+--------------------+------+-----+---------+-------+

+-------+--------------------+------+-----+---------+-------+

| class | char(7) | YES | | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

| sex | enum('boy','gril') | NO | | boy | |

+-------+--------------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> insert into t7 values("bob2","nsd1804",21,"gril");————插入表信息

Query OK, 1 row affected (0.02 sec)

mysql> select * from t7; ————查看表信息

+------+---------+-----+------+

| name | class | age | sex |

+------+---------+-----+------+

| bob | nsd1804 | 19 | boy |

| bob2 | nsd1804 | 21 | gril |

+------+---------+-----+------+

2 rows in set (0.01 sec)

mysql> insert into t7 values(null,null,22,"boy"); ——插入表信息，name为空，出现报错信息，不能为空

ERROR 1048 (23000): Column 'name' cannot be null

mysql> insert into t7 values("tang",null,22,"boy"); ——插入表信息

Query OK, 1 row affected (0.03 sec)

mysql> select * from t7; ————查看表信息

+------+---------+-----+------+

| name | class | age | sex |

+------+---------+-----+------+

| bob | nsd1804 | 19 | boy |

| bob2 | nsd1804 | 21 | gril |

| tang | NULL | 22 | boy |

+------+---------+-----+------+

3 rows in set (0.00 sec)

mysql> insert into t7 (class,age,sex) values("nsd1804",27,"gril");

ERROR 1364 (HY000): Field 'name' doesn't have a default value

修改表结构

对已经创建的表进行修改

添加新的字段，add 字段名类型（宽度）约束条件

mysql> alter table t7 add email varchar(50) default "student@tedu.cn",add qq char(11);

Query OK, 0 rows affected (0.52 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t7;

+------+---------+-----+------+-----------------+------+

| name | class | age | sex | email | qq |

+------+---------+-----+------+-----------------+------+

| bob | nsd1804 | 19 | boy | student@tedu.cn | NULL |

| bob2 | nsd1804 | 21 | gril | student@tedu.cn | NULL |

| jim | NULL | 22 | boy | student@tedu.cn | NULL |

+------+---------+-----+------+-----------------+------+

mysql> alter table qr.t7 drop email;————删除字段

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t7;

+------+---------+-----+------+------+

| name | class | age | sex | qq |

+------+---------+-----+------+------+

| bob | nsd1804 | 19 | boy | NULL |

| bob2 | nsd1804 | 21 | gril | NULL |

| jim | NULL | 22 | boy | NULL |

+------+---------+-----+------+------+

4 rows in set (0.00 sec)

mysql> alter table t7 add stu_num char(9) first, add likes set("eat","sleeps","game","flim") after sex;

Query OK, 0 rows affected (0.31 sec) ——添加字段确认添加的位置

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t7;

+---------+------+---------+-----+------+-------+------+

| stu_num | name | class | age | sex | likes | qq |

+---------+------+---------+-----+------+-------+------+

| NULL | bob | nsd1804 | 19 | boy | NULL | NULL |

| NULL | bob2 | nsd1804 | 21 | gril | NULL | NULL |

| NULL | jim | NULL | 22 | boy | NULL | NULL |

+---------+------+---------+-----+------+-------+------+

4 rows in set (0.00 sec)

mysql> alter table t7 drop stu_mum, drop qq;

ERROR 1091 (42000): Can't DROP 'stu_mum'; check that column/key exists

mysql> alter table t7 drop stu_num, drop qq;

Query OK, 0 rows affected (0.31 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from t7;

+------+---------+-----+------+-------+

+------+---------+-----+------+-------+

| bob | nsd1804 | 19 | boy | NULL |

| bob2 | nsd1804 | 21 | gril | NULL |

| jim | NULL | 22 | boy | NULL |

+------+---------+-----+------+-------+

4 rows in set (0.00 sec)

修改已有的字段类型

修改字段类型时，如果新的类型与字段已经存储数据冲突，不允许修改. 不能修改的还要抄一遍

mysql> desc t7;

+-------+-----------------------------------+------+-----+---------+-------+

+-------+-----------------------------------+------+-----+---------+-------+

| class | char(7) | YES | | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

| sex | enum('boy','gril') | NO | | boy | |

+-------+-----------------------------------+------+-----+---------+-------+

mysql> alter table t7 modify name varchar(15);

Query OK, 4 rows affected (0.37 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> desc t7;

+-------+-----------------------------------+------+-----+---------+-------+

+-------+-----------------------------------+------+-----+---------+-------+

| class | char(7) | YES | | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

| sex | enum('boy','gril') | NO | | boy | |

+-------+-----------------------------------+------+-----+---------+-------+

mysql> alter table t7 modify sex enum('boy','gril',"no")not null default "boy" after name;

Query OK, 0 rows affected (0.30 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t7;

+-------+-----------------------------------+------+-----+---------+-------+

+-------+-----------------------------------+------+-----+---------+-------+

| sex | enum('boy','gril','no') | NO | | boy | |

| class | char(7) | YES | | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

+-------+-----------------------------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改字段名 change

mysql> alter table t7 change name mm varchar(10);——把name 修改为mm 字段类型为varchar(10)

Query OK, 4 rows affected (0.38 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> desc t7;

+-------+-----------------------------------+------+-----+---------+-------+

+-------+-----------------------------------+------+-----+---------+-------+

| mm | varchar(10) | YES | | NULL | |

| sex | enum('boy','gril','no') | NO | | boy | |

| class | char(7) | YES | | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

+-------+-----------------------------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改表名 rename

mysql> alter table t7 rename tang; ——把t7修改为tang ,表里的信息不改变

Query OK, 0 rows affected (0.06 sec)

mysql> show tables;

+--------------+

| Tables_in_qr |

+--------------+

| 前任 |

| t1 |

| t2 |

| t3 |

| t4 |

| tang |

+--------------+

6 rows in set (0.00 sec)

mysql> select * from tang;

+------+------+---------+-----+-------+

| mm | sex | class | age | likes |

+------+------+---------+-----+-------+

| bob | boy | nsd1804 | 19 | NULL |

| bob2 | gril | nsd1804 | 21 | NULL |

| jim | boy | NULL | 22 | NULL |

+------+------+---------+-----+-------+

4 rows in set (0.01 sec)

删除已有的字段

MYSQL 的键值

普通的索引：

索引介绍：给字段值排序的树型结构，类似“书的目录” 方便查找，记录表的信息种类是通过一种算法设计

索引的类型有 Btree B+tree hash ——是通过算法给字段做成索引进行排队，方便查找

Btree 又叫二叉树算法，存储在表里。每次只是查看一半的记录，可以增加查找速度

索引的优点

通过创建唯一性索引,可以保证数据库表中每一行数

据的唯一性没有索引就会全盘搜索，这样速度就会慢，创建索引就是加快搜索的速度，提高客户体验

– 可以加快数据的检索速度

索引的缺点

当对表中的数据进行增加、删除和修改的时候,索引

也要动态的维护,降低了数据的维护速度,数据的写入速度，因为记录需要重新排队，索引需要重新计算，表里的信息越多，排队信息越多，占据的空间就越大。

– 索引需要占物理空间

使用规则——需要记下来

一个表中可以有多个 INDEX 字段

– 字段的值允许有重复,切可以赋 NULL 值

– 经常把做查询条件的字段设置为 INDEX 字段

– INDEX 字段的 KEY 标志是 MUL

select * from t7 where 条件;

查看

desc 表；

show index for t7;

show index for t7\G; ——竖着显示

创建

把已经有的表的字段设置为 index 字段

mysql> create index aaa on tang(mm);

Query OK, 0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from tang\G;

*************************** 1. row ***************************

Table: tang

Non_unique: 1

Key_name: aaa

Seq_in_index: 1

Column_name: mm

Collation: A

Cardinality: 4

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

mysql> desc tang;

+-------+-----------------------------------+------+-----+---------+-------+

+-------+-----------------------------------+------+-----+---------+-------+

| mm | varchar(10) | YES | MUL | NULL | |

| sex | enum('boy','gril','no') | NO | | boy | |

| class | char(7) | YES | | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

+-------+-----------------------------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

创建表时指定index字段

mysql> create table t9(name char(15) not null default "", class char(7) default "nsd1804",age tinyint(2) not null default 19,sex enum("boy","girl")not null default "boy",index(name),index(class));

Query OK, 0 rows affected (0.24 sec)

mysql> desc t9;

+-------+--------------------+------+-----+---------+-------+

+-------+--------------------+------+-----+---------+-------+

| name | char(15) | NO | MUL | | |

| class | char(7) | YES | MUL | nsd1804 | |

| age | tinyint(2) | NO | | 19 | |

| sex | enum('boy','girl') | NO | | boy | |

+-------+--------------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> show index from t9\G;

*************************** 1. row ***************************

Table: t9

Non_unique: 1

Key_name: name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: t9

Non_unique: 1

Key_name: class

Seq_in_index: 1

Column_name: class

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.00 sec)

ERROR:

No query specified

删除

primary key 主键

一个表中只能有一个 primary key 字段

– 对应的字段值不允许有重复,且不允许赋 NULL 值

– 如果有多个字段都作为 PRIMARY KEY ,称为复合主

键,必须一起创建。

– 主键字段的 KEY 标志是 PRI

– 通常与 AUTO_INCREMENT 连用

– 经常把表中能够唯一标识记录的字段设置为主键字段

[ 记录编号字段 ]

创建主键

创建表时候创建主键字段

mysql> create table t10(stu_id char(9) primary key,name char(10),age int(2));

mysql> desc t10;

+--------+----------+------+-----+---------+-------+

+--------+----------+------+-----+---------+-------+

| age | int(2) | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> create table t11(stu_id char(9), name char(10), age int(2), primary key(stu_id));

mysql> desc t11;

+--------+----------+------+-----+---------+-------+

+--------+----------+------+-----+---------+-------+

| age | int(2) | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table t10 drop primary key;

Query OK, 1 row affected (0.31 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> desc t10;

+--------+----------+------+-----+---------+-------+

+--------+----------+------+-----+---------+-------+

| age | int(2) | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

已经有表，创建主键

mysql> alter table t10 add primary key(stu_id); ————提示表里的内容与主键有冲突

ERROR 1062 (23000): Duplicate entry 'nsd1804' for key 'PRIMARY'

mysql> select * from t10;

+---------+-------+------+

| stu_id | name | age |

+---------+-------+------+

| nsd1804 | luy11 | 21 |

| nsd1804 | lucy | 22 |

+---------+-------+------+

2 rows in set (0.00 sec)

mysql> delete from t10; ————删除表里的内容

Query OK, 2 rows affected (0.04 sec)

mysql> select * from t10; ————表为空表

Empty set (0.00 sec)

mysql> alter table t10 add primary key(stu_id);

Query OK, 0 rows affected (0.32 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t10; ——成功创建主键

+--------+----------+------+-----+---------+-------+

+--------+----------+------+-----+---------+-------+

| age | int(2) | YES | | NULL | |

+--------+----------+------+-----+---------+-------+

3 rows in set (0.00 sec)

删除主键

mysql> alter table t10 drop primary key; ——删除主键

Query OK, 1 row affected (0.31 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t10 values("nsd1804","lucy",22);

Query OK, 1 row affected (0.03 sec)

mysql> select * from t10;

+---------+-------+------+

| stu_id | name | age |

+---------+-------+------+

| nsd1804 | luy11 | 21 |

| nsd1804 | lucy | 22 |

+---------+-------+------+

有主键，又有自动增长，如何删主键

create table t1 (id int primary key auto_increment,name char(2));

alter table t1 modfiy id int not null

alter table t1 drop primary key;

创建符合主键：表中的列一起做主键多列值不可以一起相同，赋值时，主键字段的值不同时重复。复合主键要一起创建，一起删除

mysql> alter table t10 add primary key(name); ————已经有主键不能在创建主键，只能创建复合主键

ERROR 1068 (42000): Multiple primary key defined

mysql> create table t12(name char(12),class char(14),pay enum("yes","no"),primary key(name,class));

Query OK, 0 rows affected (0.19 sec)

mysql> desc t12;

+-------+------------------+------+-----+---------+-------+

+-------+------------------+------+-----+---------+-------+

| pay | enum('yes','no') | YES | | NULL | |

+-------+------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into t12 values("bob","nsd1804","yes");

Query OK, 1 row affected (0.04 sec)

mysql> insert into t12 values("bob","nsd1804","no");

ERROR 1062 (23000): Duplicate entry 'bob-nsd1804' for key 'PRIMARY'

mysql> insert into t12 values("bob","nsd1805","no");

Query OK, 1 row affected (0.03 sec)

mysql> insert into t12 values("lucy","nsd1805","yes");

Query OK, 1 row affected (0.03 sec)

mysql> select * from t12;

+------+---------+------+

| name | class | pay |

+------+---------+------+

| bob | nsd1804 | yes |

| bob | nsd1805 | no |

| lucy | nsd1805 | yes |

+------+---------+------+

3 rows in set (0.00 sec)

mysql> alter table t12 drop primary key; ——删除主键，主键只删除一次

Query OK, 3 rows affected (0.65 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into t12 values("bob","nsd1805","no");

Query OK, 1 row affected (0.03 sec)

mysql> select * from t12;

+------+---------+------+

| name | class | pay |

+------+---------+------+

| bob | nsd1804 | yes |

| bob | nsd1805 | no |

| lucy | nsd1805 | yes |

| bob | nsd1805 | no |

+------+---------+------+

9 rows in set (0.00 sec)

已经有表的创建复合主键

mysql> delete from t12;

Query OK, 9 rows affected (0.04 sec)

mysql> alter table t12 add primary key(name,class);

mysql> desc t12;

+-------+------------------+------+-----+---------+-------+

+-------+------------------+------+-----+---------+-------+

| pay | enum('yes','no') | YES | | NULL | |

+-------+------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> alter table t12 add primary key(name,class);

Query OK, 0 rows affected (0.25 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t12;

+-------+------------------+------+-----+---------+-------+

+-------+------------------+------+-----+---------+-------+

| pay | enum('yes','no') | YES | | NULL | |

+-------+------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

通常和auto_increment 连用，让字段的值自动增长

做+1计算 permin

行号字段就是叫id

向表中插入记录时，不给自动增长的字段赋值，不给自动增长字段值的时候，要使用没有用过的值。

字段的值是如何获得的呢，用当前字段最大的值 +1 后把结果做当前记录字段的值

mysql> create database db3;

Query OK, 1 row affected (0.00 sec)

mysql> use db3;

Database changed

mysql> create table t1(

mysql> create table t1( id int(2) primary key auto_increment, name char(15) not null, age tinyint(2) unsigned default 19, pay float(7,2) default 26800);————插入id号

Query OK, 0 rows affected (0.17 sec)

mysql> show tables;

+---------------+

| Tables_in_db3 |

+---------------+

| t1 |

+---------------+

1 row in set (0.00 sec)

mysql> insert into t1(name,age,pay) values("tom",21,1800); ——插入表的时候可以自动列出id号

Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;

+----+------+------+---------+

| id | name | age | pay |

+----+------+------+---------+

| 1 | tom | 21 | 1800.00 |

+----+------+------+---------+

1 row in set (0.01 sec)

mysql> insert into t1(name,age,pay) values("lucy",25,18800); ——默认自动添加id号

Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;

+----+------+------+----------+

| id | name | age | pay |

+----+------+------+----------+

| 1 | tom | 21 | 1800.00 |

| 2 | lucy | 25 | 18800.00 |

+----+------+------+----------+

2 rows in set (0.00 sec)

mysql> insert into t1 values(7,"jerry",33,18000); ——可以自定义id号不给自动增长字段值的时候，要使用没有用过的值。

Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;

+----+-------+------+----------+

| id | name | age | pay |

+----+-------+------+----------+

| 1 | tom | 21 | 1800.00 |

| 2 | lucy | 25 | 18800.00 |

| 7 | jerry | 33 | 18000.00 |

+----+-------+------+----------+

3 rows in set (0.00 sec)

mysql> insert into t1 (name,age,pay) values("alice",33,18000); ——不给与指定id时，就会在原有基础上自动添加id

Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;

+----+-------+------+----------+

| id | name | age | pay |

+----+-------+------+----------+

| 1 | tom | 21 | 1800.00 |

| 2 | lucy | 25 | 18800.00 |

| 7 | jerry | 33 | 18000.00 |

| 8 | alice | 33 | 18000.00 |

+----+-------+------+----------+

4 rows in set (0.00 sec)

mysql> insert into t1 values(null,"jerry",23,18000);——相当与默认值为空，不指定id号，自动添加id号

Query OK, 1 row affected (0.05 sec)

mysql> select * from t1;

+----+-------+------+----------+

| id | name | age | pay |

+----+-------+------+----------+

| 1 | tom | 21 | 1800.00 |

| 2 | lucy | 25 | 18800.00 |

| 7 | jerry | 33 | 18000.00 |

| 8 | alice | 33 | 18000.00 |

| 9 | jerry | 23 | 18000.00 |

+----+-------+------+----------+

5 rows in set (0.00 sec)

mysql> select * from t1 where id=2; ——赋值id可以快速查找表里信息

+----+------+------+----------+

| id | name | age | pay |

+----+------+------+----------+

| 2 | lucy | 25 | 18800.00 |

+----+------+------+----------+

1 row in set (0.00 sec)

MYSQL 的外键——foreign key

定义：让当前表字段的值在另一个表中字段值的范围内选择

A表的name字段参考 B表中的user的字段。赋值的时候时一样的，不一样就会报错。

使用外键的条件

表必须是存储引擎

字段的类型是一致

被参照字段必须是 primary key 的一种不能重复，不能为空

首先要创建被参考的表员工信息表

ysql> create table yginfo(yg_id int(2) primary key auto_increment, name char(15))engine=innodb;

Query OK, 0 rows affected (0.26 sec)

mysql> insert into yginfo(name)values("bob");

Query OK, 1 row affected (0.02 sec)

mysql> insert into yginfo(name)values("bob");

Query OK, 1 row affected (0.02 sec)

mysql> insert into yginfo(name)values("lucy");

Query OK, 1 row affected (0.02 sec)

mysql> select * from yginfo;

+-------+------+

| yg_id | name |

+-------+------+

| 1 | bob |

| 2 | bob |

| 3 | lucy |

+-------+------+

3 rows in set (0.00 sec)

创建外键 ——员工的工资表

mysql> create table gztab(

-> gz_id int(2),

-> pay float(7,2),

-> foreign key(gz_id) references yginfo(yg_id) on delete cascade on update cascade)engine=innodb;

Query OK, 0 rows affected (0.23 sec)

on delete cascade on update cascade——可以同步更新数据，保证数据的一致性

engine=innodb;——数据存储引擎

mysql> desc gztab;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| gz_id | int(2) | YES | MUL | NULL | |

| pay | float(7,2) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> show create table gztab;

mysql> insert into gztab values (1,15000);

Query OK, 1 row affected (0.03 sec)

mysql> insert into gztab values (2,50000);

Query OK, 1 row affected (0.03 sec)

mysql> insert into gztab values (3,100000);

ERROR 1264 (22003): Out of range value for column 'pay' at row 1

mysql> insert into gztab values (3,10000);

Query OK, 1 row affected (0.06 sec)

mysql> insert into gztab values (4,10000); ——报错信息，员工信息表没有4

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db3`.`gztab`, CONSTRAINT `gztab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yginfo` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql>

同步修改数据

mysql> update yginfo set yg_id=7 where yg_id=2;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from yginfo;

+-------+------+

| yg_id | name |

+-------+------+

| 1 | bob |

| 3 | lucy |

| 7 | bob |