用户与用户信息表：当用户的信息数据过多时，我们可以将其分成两个表分别对应用户基本信息和用户的详情信息。
create table user(
`id` int auto_increment primary key comment'用户id',
`name` varchar(10),
`age` varchar(10),
`gender` char,
`tel` varchar(30),
`school` varchar(20),
`addr` varchar(20),
`degree` varchar(10),
`university` varchar(10)
);
当用户信息过多时，就可以使用外键进行关联。在任意一方加入外键，关联另一方主键，并且设置外键为唯一的UNIQUE如下实现：
create table user(
`id` int auto_increment primary key,
`name` varchar(10),
`age` varchar(10),
`gender` char
);
create table user_info(
`id` int auto_increment primary key ,
`tel` varchar(30),
`school` varchar(20),
`addr` varchar(20),
`degree` varchar(10),
`university` varchar(10),
`user_id` int unique,
constraint fk_user_info foreign key (user_id) references user(id)
);
部门与员工：一个员工对应一个部门，一个部门对应多个员工
员工指向多的一方，部门指向一的一方。此时应该在员工表中创建外键，指向部门表中的主键
# 员工表 create table emp ( `emp_id` int auto_increment primary key, `emp_name` varchar(20) not null, `emp_gender` char, `emp_tel` varchar(30), `emp_dept_id` int, constraint fk_emp_dept foreign key (emp_dept_id) references dept (dept_id) ); # 部门表 create table dept ( `dept_id` int auto_increment primary key, `dept_name` varchar(20) not null );
查询方法：
-- 正常单表查 select * from emp; -- 全查 笛卡尔积 select * from emp,dept; -- 联查 消除无效的笛卡尔积 select * from emp,dept where emp_dept_id = dept.dept_id;
学生与课程：一个学生可以选修多门课程，一门课程可以被多个学生选择
此时我们应该在学生表与课程表之间建立中间表。中间表包含两个外键，分别对应学生表和课程表的主键
首先我们准备好数据，学生表+课程表+中间信息表
# 学生表 CREATE TABLE student( id INT auto_increment PRIMARY KEY COMMENT '主键ID', name VARCHAR(10) COMMENT '姓名', no VARCHAR(10) COMMENT '学号' )COMMENT '学生表'; INSERT INTO student VALUES(NULL,'小瘪三','2001'),(NULL,'小瘪四','2002'),(NULL,'小瘪五','2003'),(NULL,'小瘪六','2004'); # 课程表 CREATE TABLE course( id INT auto_increment PRIMARY KEY COMMENT'主键ID', name VARCHAR(10) COMMENT '课程名称' )COMMENT '课程表'; INSERT INTO course VALUES(NULL,'java'),(NULL,'PHP'),(NULL,'MySQL'),(NULL,'Hadoop'); # 学生课程关系表（中间表） CREATE TABLE student_course( id INT auto_increment COMMENT '主键' PRIMARY KEY, student_id INT NOT NULL COMMENT '学生ID', course_id INT NOT NULL COMMENT '课程ID', CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES course (id), CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES student (id) )COMMENT '学生——课程关系中间表'; INSERT INTO student_course VALUES(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);
我们切换IDEA打开可以清晰的看到三个表之间的关系，中间信息表中的两个外键：student_id与course_id将学生表和课程表关联了起来
查询A集合与B集合的交集
-- 方式一 select [字段列表] from 表1,表2 where 条件...; -- 方式二 select [字段列表] from 表1 inner join 表2 on 条件...;
示例：查询所有员工与其所属部门
-- 方式一 select emp_name,dept_name from emp inner join dept d on emp.emp_dept_id = d.dept_id; -- 方式二 select emp_name,dept_name from emp,dept where emp.emp_dept_id = dept.dept_id;
右外连接：查询右表所有数据以及两表交集部分数据
select 字段列表 from 表1 right outer join 表2 on 条件...;
左外连接：查询左表所有数据以及两表交集部分数据
select 字段列表 from 表1 left outer join 表2 on 条件...;
示例：
-- 2. 查询员工全部信息及其对应的部门信息 左外连接 select e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id; -- 3. 查询全部部门和其对应的所有员工信息 右外连接 select d.*,e.emp_name from emp e right join dept d on e.emp_dept_id = d.dept_id;
当前表与自身的连接查询，自连接必须使用别名
格式：
select 字段列表 from 表A 别名 join 表A 别名 on 条件...;
在员工表中，所有的普通员工、管理者都是员工。查询每个员工归属哪个管理者管理就需要用到自连接
select a.name,b.name from emp a join emp b on a.emp_id = b.manager_id;
关键字 union [all] 将两条sql语句查询的结果拼接起来
-- 查询年龄大于50的员工 和薪资小于10000的员工 select * from emp where emp.emp_age > 50 union all select * from emp where emp.emp_salary < 10000;
加上all表示不会去重，不加all表示去重复（即同时满足两条sql语句的只出现一次即可）
多张表的列数必须保持一致，字段类型也需要一致
子查询是指在SQL语句中嵌套select语句进行嵌套查询
select * from t1 where column1 =(select column1 from t2)；
子查询的外部语句可以是insert、uodate、delete、select的任何一个
标量子查询示例：查询‘ 开发部 ’的全部员工信息
首先你可以利用正常的两条sql去查询：
-- 查询“开发部”的所有员工 -- 1. 两条语句查询 select dept_id from dept where dept_name = '开发部'; select emp_name from emp where emp_dept_id = 1;
或者你可以使用内连接方式进行联查：
-- 内连接 select e.emp_name,d.dept_name from emp e inner join dept d on e.emp_dept_id = d.dept_id where dept_name = '开发部';
或者使用子查询：
-- 使用子查询 select * from emp where emp_dept_id = (select dept_id from dept where dept_name = '开发部');
