【数据库】表的增删改查（进阶）

表的增删改查（基础操作）： https://blog.youkuaiyun.com/qq_43361209/article/details/105749745

1.数据库约束

约束类型	说明	示例
NULL约束	使用NOT NULL指定列不为空	id int not null
UNIQUE唯一约束	指定列为唯一的、不重复的值	id int unique
DEFAULT默认值约束	指定列为空时的默认值	age int default 20
主键约束	NOT NULL 和 UNIQUE 的结合，表中唯一标识	id int primary key
外键约束	关联其他表的主键或唯一键	foreign key(字段名) references 主键表(列)
CHECK约束	保证列中的值符合指定的条件	check (sex =‘男’ or sex=‘女’)

1.1 NULL约束

-- 创建表时，可以指定某列不为空

create table student(
    id int not null,
    name varchar(20),
    main varchar(30)
);

插入id为空时报错：
       

1.2 UNIQUE：唯一约束

-- 创建表时，可以指定某列值唯一

create table student(
    id int unique,
    name varchar(20)
);

插入重复id报错：
       

1.3 DEFAULT：默认值约束

-- 创建表时，可以指定某列的默认值

create table student(
    id int,
    name varchar(20) default 'unknow'
);

指定插入数据时，只插入id，name默认值 unknow ：
       

1.4 PRIMARY KEY：主键约束

-- 创建表时，可以指定主键
create table student(
    id int primary key,
    name varchar(20)
);

-- 对于整数类型的主键，常配搭自增长auto_increment来使用。
id INT PRIMARY KEY auto_increment

主键不能为空，不能重复，否则报错：
       

1.5 FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键，语法：

foreign key (字段名) references 主表(列)

-- 创建班级表class，id为主键
create table class(
    id int primary key auto_increment,
    name varchar(20)
);

-- 创建学生表student，一个学生对应一个班级，一个班级对应多个学生
-- 使用id为主键，classes_id为外键，关联班级表id
create table student(
    id int primary key,
    class_id int,
    foreign key (class_id) references class (id)
);
-- 此时操作 student 中的 class_id，会受到 class 表内容的制约
-- 插入 student 表的 class_id 的值必须在 class 表中存在
-- 若插入的 class_id 字段的值在 class 表中不存在，插入失败，报错

测试：
-- 班级表插入数据
insert into class (name) values ("A班");
insert into class (name) values ("B班");
-- 学生表插入数据
insert into student values(001,3); -- 报错 插入失败
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test1`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

insert into student values(001,1); -- 插入成功
insert into student values(002,1); -- 插入成功
insert into student values(003,2); -- 插入成功

注意：
         对带有外键的表进行插入/修改时，mysql会自动去关联表中查找插入/修改的值是否存在，查找完毕才会真正执行插入/修改操作。
         带来的影响是加强了数据的效验（提高了完整性），但降低了效率。

1.6 CHECK约束

-- -- 创建表时，可以保证列中的值符合指定条件

create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

插入非指定条件，报错：
       

2.表的设计

三大范式：一对一、一对多、多对多

2.1 一对一

       

2.2 一对多

       

2.3 多对多

       
案例：

-- 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(20)
);

-- 创建课程学生中间表：考试成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
    id INT PRIMARY KEY auto_increment,
    score DECIMAL(3, 1),
    student_id int,
    course_id int,
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(id)
);

3.新增：插入查询结果

语法：

-- 插入一个 select 返回的结果
INSERT INTO table_name [(column [, column ...])] SELECT ...

案例：

create table s1 (id int,name varchar(20),sex varchar(1));
insert into s1 values
    (1,'黎明','男'),
    (2,'孙悟空','男'),
    (3,'胡歌','男');
   
create table s2 (id int,name_s2 varchar(20),sex varchar(1));
-- 将 s1 中数据全部插入 s2
insert into s2 select * from s1;

create table s3 (name varchar(20),sex varchar(1));
-- 将 s1 中姓名和性别数据全部插入 s3
insert into s3 (name,sex) select name,sex from s1;

       
       

4.查询

聚合查询和联合查询：https://blog.youkuaiyun.com/qq_43361209/article/details/105846871

MySQL中内连接，外连接等的区别
       
图片来源于网络，仅供学习使用，如有侵权立马删除！！！