007 学习笔记--约束 + 多表查询 + 联合查询 + 子查询

约束：是作用于表中字段上的规则，用于限制存储在标中的数据；其目的，是保证数据库中的数据的正确、有效和完整性；

约束分类：

-- 约束
create table if not exists users
(
    id int PRIMARY key auto_increment COMMENT '主键',
    name VARCHAR(100) not null unique COMMENT '姓名',
--   age int check (age >0 && a<=120) COMMENT'年龄',
    age INT CHECK (age >0 && a<=120),
    status char(1) DEFAULT '1' COMMENT '状态',
    gender char(1) COMMENT'性别'
)COMMENT'用户表';

多表关系，分为：一对多(多对一)、多对多、一对一

create table if not exists tb_users
(
    id int auto_increment PRIMARY key COMMENT '主键',
    name VARCHAR(100) not null unique COMMENT '姓名',
--   age int check (age >0 && a<=120) COMMENT'年龄',
    age INT CHECK (age >0 && a<=120),
    status char(1) DEFAULT '1' COMMENT '状态',
    gender char(1) COMMENT'性别',
    phone VARCHAR(11) COMMENT '手机号'
)COMMENT'用户表';
 
CREATE TABLE if not EXISTS tb_user_edu
(
    id int PRIMARY key auto_increment COMMENT '主键',
  userid int UNIQUE COMMENT '用户id',
  degree VARCHAR(20) comment '学历',
    major VARCHAR(50) COMMENT'专业',
    primaryschool VARCHAR(50) COMMENT'小学',
  middleschool VARCHAR(50) COMMENT'中学',
    university VARCHAR(50) COMMENT'大学',
    CONSTRAINT fk_userid FOREIGN key(userid) REFERENCES tb_users(id)
)comment '用户教育信息表';
 
 
INSERT into tb_users(id,NAME,age,gender,phone)
VALUES
(null,'张三',18,'1','110'),
(null,'历史',25,'1','119'),
(null,'王五',38,'2','120'),
(null,'麻子',48,'1','114')
 
insert into tb_user_edu(id,userid,degree,major,primaryschool,middleschool,university)
VALUES
(NULL,1,'本科','计算机','二小','二中','西安电子科技大学'),
(NULL,2,'本科','英语','三小','三中','西北大学'),
(NULL,3,'本科','数学','四小','四中','西安交通大学'),
(NULL,4,'本科','汉语言','五小','五中','西安邮电')

多表查询：　

连接查询
- 内连接：相当于查询A、B交集部分
- 外连接：
  - 左外连接：查询左表所有数据，以及两张表交集部分数据
  - 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：
  - 当前表与自身的连接查询，自连接必须使用表别名
  - -- select 字段列表 from 表A a join 表A b on 条件....
    -- 自连接查询，可以是内连接查询，也可以是外连接查询　　
子查询

内连接：

隐式内连接：select 字段列表 from 表1，表二，where 条件...
显示内连接：select 字段列表 from 表1 inner join 表二 on 连接条件...

-- 内连接 隐式
select * from tb_users,tb_user_edu where tb_users.id = tb_user_edu.id;
-- 内连接 显示
select * from tb_users INNER JOIN tb_user_edu on tb_users.id = tb_user_edu.id;
-- 等价于
select * from tb_users a,tb_user_edu b where a.id = b.id;
select * from tb_users a INNER JOIN tb_user_edu b on a.id = b.id;

连接查询--外连接

左外连接：
- select 字段列表 from 表1 left [outer] join 表2 on 条件
- 相当于查询表1--左表的所有数据，包含表1和表2交集部分的数据
- 以左表为主表数据，右表为辅表(即，左表查询的数据，对应的在右表中没有，则填充null)——例如，左表有5条数据，右表4条数据，则查询返回结果为5条，其中对应的右表数据--无对应数据时(数据为null)
右外连接
- select 字段列表 from 表1 right[outer] join 表2 on 条件
- 相当于查询表2的所有数据，包含表1和表2交集部分的数据
- 以右表为主表数据，左表为辅表(即，右表查询的数据，对应的在左表中没有，则填充null)——例如，右表有4条数据，左表有5条数据，则返回值为4条(或者，右表有6条数据，左表有5条，则返回6条，左表对应的数据--如果不存在，数据为null)

左连接：

INSERT into tb_users(id,NAME,age,gender,phone)
VALUES
(null,'李思',18,'1','112');
-- 左连接
-- 相当于查询表1--左表的所有数据，包含表1和表2交集部分的数据
-- 以左表为主表数据，右表为辅表(即，左表查询的数据，对应的在右表中没有，则填充null)
select a.*,b.degree,b.major,b.primaryschool,b.middleschool,b.university from tb_users a LEFT JOIN tb_user_edu b on a.id=b.userid;

右连接：

insert into tb_user_edu(id,userid,degree,major,primaryschool,middleschool,university)
VALUES
(NULL,5,'本科','计算机','二小','83中','厦门大学')
-- 右连接
-- 以右表为主表数据，左表为辅表(即，右表查询的数据，对应的在左表中没有，则填充null)
select a.*,b.degree,b.major,b.primaryschool,b.middleschool,b.university from tb_users a 
RIGHT JOIN tb_user_edu b on a.id=b.userid 
and b.middleschool='83中';