mysql入门（约束二十一)

来源：互联网发布：java抓取网页数据编辑：程序博客网时间：2024/06/01 19:27

约束:constraint关键字

约束包括: 非空约束:not null

唯一约束 unique

主键约束 primary key 简称 PK

外键约束: foreign key 简称 FK

1：非空约束

select * from t_user;
+------+------+-------------+
| id | name | email |
+------+------+-------------+
| 1 | | 123@163.com |
+------+------+-------------+
1 row in set (0.00 sec)

2：唯一约束:(保证字段所在记录的唯一性)

insert into t_user(id,name,email) values(1,'jack','123@163.com');
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_user(id,name,email) values(2,'Simth','123@163.com');
ERROR 1062 (23000): Duplicate entry '123@163.com' for key 'email'

not null,unique可以同时使用

insert into t_user(id,name) values(1,'jack');
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_user(id,name) values(2,'jack');
ERROR 1062 (23000): Duplicate entry 'jack' for key 'name'

3:主键约束（不能为空，字段不能重复，而且可以默认添加索引) 一张数据表只能有一张主键

主键值:是当前行的标识

create table t_user(
    -> id int(10) primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |
| name | varchar(32) | YES |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

create table t_user(
    -> id int(10) primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.56 sec)

mysql> insert into t_user(name) values('jack');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
| 1 | jack |
| 2 | jack |
+----+------+
2 rows in set (0.00 sec)

2 rows in set (0.07 sec)

4：外键约束 foreign key(被引用的数据表的字段必须具有唯一约束

先创建父表，插入父表记录

后建立字表，插入子表

先删除字表的数据，再删除父表的数据

create table t_class(
    -> cno int(3) primary key,
    -> cname varchar(30) not null unique
    -> );
Query OK, 0 rows affected

mysql> create table t_student(
    -> sno int(3) primary key,
    -> sname varchar(32) not null,
    -> classno int(3),
    -> constraint t_student_classno_fk foreign key(classno) references t_class(cno)
    -> );
Query OK, 0 rows affected

insert into t_class(cno,cname) values(100,'计科1501班');
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_class(cno,cname) values(200,'计科1502班');
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_class(cno,cname) values(300,'计科1503班');
Query OK, 1 row affected (0.12 sec)

mysql> insert into t_student(sno,sname,classno) values(1,'jack',100);
Query OK, 1 row affected (0.10 sec)

mysql> insert into t_student(sno,sname,classno) values(2,'Alice',100);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_student(sno,sname,classno) values(3,'Bob',200);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_student(sno,sname,classno) values(4,'King',200);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_student(sno,sname,classno) values(5,'Smith',300);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t_student(sno,sname,classno) values(6,'Lucy',300);
Query OK, 1 row affected (0.11 sec)

mysql> select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | jack |     100 |
|   2 | Alice |     100 |
|   3 | Bob   |     200 |
|   4 | King |     200 |
|   5 | Smith |     300 |
|   6 | Lucy |     300 |
+-----+-------+---------+
6 rows in set (0.00 sec)

mysql> select * from t_class;
+-----+------------+
| cno | cname |
+-----+------------+
| 100 | 计科1501班 |
| 200 | 计科1502班 |
| 300 | 计科1503班 |
+-----+------------+
3 rows in set (0.30 sec)

阅读全文

0 0