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)
);

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;
-- 聯(lián)查 消除無(wú)效的笛卡爾積
select * from emp,dept where emp_dept_id = dept.dept_id;

# 學(xué)生表
CREATE TABLE student(
	id INT auto_increment PRIMARY KEY COMMENT '主鍵ID',
	name VARCHAR(10) COMMENT '姓名',
	no VARCHAR(10) COMMENT '學(xué)號(hào)'
)COMMENT '學(xué)生表';
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');

# 學(xué)生課程關(guān)系表（中間表）
CREATE TABLE student_course(
	id INT auto_increment COMMENT '主鍵' PRIMARY KEY,
	student_id INT NOT NULL COMMENT '學(xué)生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 '學(xué)生——課程關(guān)系中間表';
INSERT INTO student_course VALUES(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

-- 方式一
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. 查詢員工全部信息及其對(duì)應(yīng)的部門信息 左外連接
select  e.*,dept_name from emp e left join dept d on d.dept_id = e.emp_dept_id;
-- 3. 查詢?nèi)坎块T和其對(duì)應(yīng)的所有員工信息 右外連接
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;

-- 查詢年齡大于50的員工  和薪資小于10000的員工
select * from emp where emp.emp_age > 50
union all
select * from emp where emp.emp_salary < 10000;

select * from t1 where column1 =(select column1 from t2)；

-- 查詢“開發(fā)部”的所有員工
-- 1. 兩條語(yǔ)句查詢
select dept_id from dept where dept_name = '開發(fā)部';
select emp_name from emp where emp_dept_id = 1;

-- 內(nèi)連接
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 = '開發(fā)部';

-- 使用子查詢
select * from emp where emp_dept_id = (select dept_id from dept where dept_name = '開發(fā)部');