数据库的约束
程序员文章站
2022-05-06 22:53:40
...
1、非空约束 not null
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
age INT);
INSERT INTO student VALUES(1,"rose",20);
INSERT INTO student VALUES(3,"Jerry",10);
INSERT INTO student VALUES(2,NULL,20);//错误,name not null
-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(20);
INSERT INTO student VALUES(2,NULL,20);//现在可以插入name为null
-- 添加非空约束前先删除空字符段
DELETE FROM student WHERE sid=4
-- 给某个字段添加非空约束
ALTER TABLE student MODIFY NAME VARCHAR(20) NOT NULL
SELECT * FROM student;
2、唯一约束 unique
p_number为唯一约束 unique
p_number VARCHAR(10) UNIQUE
-- 出入数据监测
INSERT INTO worker1 VALUES(005,"盖聂","男","1024-09-04","aaa@qq.com","鬼谷传人","111");
INSERT INTO worker1 VALUES(005,"卫庄","男","1344-09-04","aaa@qq.com","鬼谷传人","111");
//报错,不能插入相同的p_number :111
//报错:Duplicate entry '111' for key 'p_number
-- 删除当前唯一约束'
ALTER TABLE worker1 DROP INDEX p_number
--再插入相同的p_number,此时可以成功插入
-- 修改字段类型为 唯一约束
ALTER TABLE worker1 MODIFY p_number VARCHAR(10)
--检测插入相同p_number
INSERT INTO worker1 VALUES(005,"盖聂","男","1024-09-04","aaa@qq.com","鬼谷传人","111");
//报错:Duplicate entry '3' for key 'PRIMARY'
3、主键约束 primary key
等同于:非同且唯一
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
age INT);
-------
INSERT INTO student(NAME,age) VALUES("tom",12)
//报错:逐渐sid必须要写
//插入一条数据
INSERT INTO student VALUES(1,"tom",12)
//报错:Jane的sid为1,不能与tom相同
INSERT INTO student VALUES(1,"Jane",12)
--删除主键
alter table student drop primary key(sid)
--添加字段类型为主键
ALTER TABLE student MODIFY sid int PRIMARY KEY
4、自增 auto_increment
CREATE TABLE class(
cid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL
);
INSERT INTO class VALUES(1,"zhang");
//及时cid没有给值,auto_increment也会默认增加
INSERT INTO class(NAME) VALUES("Lee");
//对于auto_increment修饰的字段,也可以手动赋值
INSERT INTO class VALUES(8,"bruce");
5、默认default
gender varchar(2) default "男"
//插入数据
insert into person values(1,"尼古拉斯凯奇","男");
insert into person(id,name)values(1,"郭达");
//添加了默认值“男”,所以不为null
6、外键约束
、、、多对多关系
-- cname 旅游线路分类名称非空,唯一,字符串 100
-- 旅游分类表
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT, -- 分类id(1,2...10)
cname VARCHAR(100) NOT NULL UNIQUE -- 旅游分类名称
);
-- 旅游线路表
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT, -- 旅游线路表的主键
rname VARCHAR(100) NOT NULL UNIQUE, -- 旅游线路名称
price DOUBLE, -- 价格
rdate DATE, -- 日期
cid INT, -- 旅游分类id
FOREIGN KEY (cid) REFERENCES tab_category(cid)
)
-- 用户表
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
)
-- 用户收藏旅游线路表 (关联用户id和旅游线路id)
CREATE TABLE tab_favorite (
rid INT,
DATE DATETIME,
uid INT, -- 创建复合主键
PRIMARY KEY(rid,uid), FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
)
7、数据库的备份与还原
-- 数据库的备份 dos
输入命令:
mysqldump -uroot -p数据库密码 数据库名 > 硬盘上的存储的路径 xxx.sql
数据库的还原
1)进入dos
2)登录mysql mysql-uroot -p密码
3)删除当前数据库
3)创建一个空的数据库
4)使用该use 数据库名
5)source 硬盘上的路径
source f://a.sql