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中';

联合查询：union\union all

联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果

select 字段列表 from tableA

union [all]  -- all 可选

select 字段列表 from tableB 

注：

• 联合查询，多张表查询的列个数、字段类型必须保持一致
• union all 将全部的数据直接合并在一起
• union会对合并之后的数据去重

子查询：又称嵌套查询

select * from tablea where colname=(select colname from tableb)

子查询外部的语句可以是：insert/update/delete/select的任何一个。

根据子查询结果不同，可分为：

1. 标量子查询：子查询结果为单个值
2. 列子查询：子查询结果为一列
3. 行子查询：子查询结果为一行
4. 表子查询：子查询结果为多行多列

根据子查询位置分为：where 之后、from之后、select之后