Java学习路线-42：SQL进阶：约束、关系、连接

SQL进阶：约束、关系、连接 课时1 1.单表的查询练习

可视化客户端 SQLyog

-- 查询部门编号为30的所有员工
select * from emp where deptno=30;

-- 查询所有销售员的姓名、编号和部门编号
select ename, empno, deptno from emp where job='销售员'

-- 查询奖金高于工资的员工
select * from emp where comm > sal;

-- 查询奖金高于工资60%的员工
select * from emp where comm > sal * 0.6;

-- 查询部门编号为10中所有经理，和部门编号编号为20中所有销售员的详细资料
select * from emp 
where (deptno=10 and job='经理')
or (deptno=20 and job='销售员');

-- 查询部门编号为10中所有经理，和部门编号编号为20中所有销售员，
-- 还有既不是经理又不是销售员但工资大于等于20000的所有员工详细资料
select * from emp 
where (deptno=10 and job='经理')
or (deptno=20 and job='销售员')
or (job not in ('经理', '销售员') and sal >= 2000);

-- 无奖金或奖金低于1000的员工
select * from emp where comm is null or comm SQL语句
 
$ mysqldump -u用户名 -p密码 数据库名 > 要生成的SQL脚本路径
 
2、恢复：SQL语句->数据库
 
$ mysql -u用户名 -p密码 数据库名  source 要生成的SQL脚本路径
 
课时4 4.约束之主键约束 
特点：唯一，非空，被引用
 
指定id列为主键列
 
-- 1、创建表时指定主键
create table stu(
    id int primary key,
    name varchar(20)
)

-- 或者
create table stu(
    id int,
    name varchar(20),
    primary key(id)
)

-- 2、已存在表添加主键
alter table stu add primary key(id);

-- 3、删除主键
alter table stu drop primary key;
 
课时5 5.主键自增长 
保证插入数据时主键唯一非空
 
-- 1、创建表时指定主键自增长
create table stu(
    id int primary key auto_increment,
    name varchar(20)
)

-- 设置字段自增长
alter table stu change id id int auto_increment;

-- 删除自增长
alter table stu change id id int;
 
uuid作为主键
 
课时6 6.非空和唯一约束 
非空约束：不能为null not null
 
唯一约束:不能重复 unique
 
create table stu(
    id int primary key auto_increment,
    name varchar(20) not null unique 
)
 
课时7 7.概述模型、对象模型、关系模型 
1、对象模型 is a 继承 has a 关联 1对1 1对多 多对多 use a
 
2、关系模型 数据库中的表
 
代码实现
 
// 一对一关系 丈夫-妻子
class  Husband{
    private Wife wife;
}

class Wife{
    private Husband husband;
}

// 一对多关系 部门-员工
class Employee{
    private Department department;
}

class Department{
    private List employee;
}

// 多对多关系 老师-学生
class Student{
    private List teachers
}

class Teacher{
    private List students
}
 
外键约束 外键引用主键，必须引用另一张表主键 外键可以重复 外键可以为空 一张表中可以有多个外键
 
课时8 8.外键约束 
create table dept(
    deptno int primary key auto_increment,
    dname varchar(50)
)

insert into dept values(10, '人力部');
insert into dept values(20, '研发部');
insert into dept values(30, '财务部');

-- 创建表时添加外键约束
create table emp(
    empno int primary key auto_increment,
    ename varchar(50),
    dno int,
    constraint fk_emp_dept foreign key(dno) references dept(deptno)
)

-- 添加外键约束
alter table emp add constraint fk_emp_dept foreign key(dno) references dept(deptno)

insert into emp(ename) values('张三');
insert into emp(ename, dno) values('李四', 10);
insert into emp(ename, dno) values('王五', 20);
 
课时9 9.一对一关系 
从表的主键就是外键
 
create table husband(
    hid int primary key auto_increment,
    hname varchar(50)
)

insert into husband(hname) values ('刘备'), ('关羽'), ('张飞')

create table wife(
    wid int primary key auto_increment,
    wname varchar(50),
    constraint fk_wife_husband foreign key(wid) references husband(hid)
)
-- wid 非空，唯一，引用hid

insert into wife(wid, wname) values(1, '杨贵妃');
insert into wife(wid, wname) values(2, '西施');

 
课时10 10.多对多关系 
create table student(
    sid int primary key auto_increment,
    sname varchar(50)
)

create table teacher(
    tid int primary key auto_increment,
    tname varchar(50)
)

create table stu_tea(
    sid int,
    tid int,
    constraint fk_student foreign key(sid) references student(sid),
    constraint fk_teacher foreign key(tid) references teacher(tid)
)

insert into student(sname) values('段誉');
insert into student(sname) values('乔峰');
insert into student(sname) values('虚竹');

insert into teacher(tname) values('黄老师');
insert into teacher(tname) values('刘老师');
insert into teacher(tname) values('李老师');

insert into stu_tea(sid, tid) values(1, 1);
insert into stu_tea(sid, tid) values(2, 1);
insert into stu_tea(sid, tid) values(3, 1);

insert into stu_tea(sid, tid) values(1, 2);
insert into stu_tea(sid, tid) values(3, 2);

insert into stu_tea(sid, tid) values(1, 3);
insert into stu_tea(sid, tid) values(2, 3);
 
课时11 11.合并结果集 
要合并的结果集表结构一样(列数, 列类型)
 
create table tb_a(id int, a_name varchar(50));
insert into tb_a(id, a_name) values(1, '1');
insert into tb_a(id, a_name) values(2, '2');
insert into tb_a(id, a_name) values(3, '3');

create table tb_b(id int, b_name varchar(50));
insert into tb_b(id, b_name) values(3, '3');
insert into tb_b(id, b_name) values(4, '4');
insert into tb_b(id, b_name) values(5, '5');

-- 不合并重复行
select * from tb_a union all select * from tb_b;

-- 合并重复行
select * from tb_a union select * from tb_b;
 
课时12 12.连接查询之内连接（方言） 
-- 方言
select * from 表1 别名1, 表2 别名2 where 别名1.xx = 别名2.xx;

-- 标准 (推荐)
select * from 表1 别名1 inner join 表2 别名2 on 别名1.xx = 别名2.xx;

-- 自然
select * from 表1 别名1 natural join 表2 别名2
 
笛卡尔积:
 
(a, b, c) X (1, 2)

-> 
a1, a2, b1, b2, c1, c2
 
-- 笛卡尔积
select * from emp, dept

-- 员工对应部门信息
select * from emp, dept where emp.dno=dept.deptno;

-- 打印所有员工的姓名，部门名称, 取别名
select e.ename, d.dname
from emp e, dept d
where e.dno=d.deptno;
 
课时13 13.连接查询之内连接（标签和自然） 
-- 标准推荐
select * from emp inner join dept on emp.dno=dept.deptno;

-- 自动加where条件
select * from emp natural join dept
 
课时14 14.连接查询之外连接 
主表中所有记录都会打印, 副表没有null补位
 
-- 左外连接, 左表为主
select e.ename, ifnull(d.dname, '无部门') as dname
from emp e left outer join dept d
on e.dno=d.deptno;

-- 右外连接, 右表为主
select e.ename, d.dname
from emp e right outer join dept d
on e.dno=d.deptno;

-- 全外连接
select e.ename, d.dname
from emp e left outer join dept d
on e.dno=d.deptno;
union
select e.ename, d.dname
from emp e right outer join dept d
on e.dno=d.deptno;
 
课时15 15.子查询 
查询中有查询
 
-- 查询本公司工资最高的员工详细信息
select * from emp where sal=(select max(sal) from emp);
 
子查询出现的位置
 
from 后作为表存在(多行多列)
where 后作为条件存在
 
条件
 
-- 单行单列 
select * from 表1 别名1 where 列1[=, >, =,  (select avg(sal) from emp);

-- 大于30部门所有人的工资的员工
select * from emp where sal > all(select sal from emp where deptno=30);

-- 和李白岗位部门都相同的员工
select * from emp where (job, deptno) in (select jobm, deptno from emp where ename ='李白');
 
课时16 16.多表查询练习第1题 
查询至少有一个员工的部门，显示部门编号，部门名称，部门位置，部门人数
 
-- 部门编号，部门名称，部门位置
select * from dept;

-- 部门人数
select deptno, count(*) from emp group by deptno

-- 整合
select d.* e1.cnt from dept d inner join 
(select deptno, count(*) cnt from emp group by deptno) as e1
on d.deptno=e1.deptno
 
课时17 17.多表查询练习第2题 
列出所有员工的姓名及其直接上级的姓名
 
select e.ename, m.ename
from emp e left outer join emp m
on e.mgr=m.empno;
 
课时18 18.多表查询练习第4题 
列出受雇日期早于直接上级的所有员工编号，姓名，部门名称
 
-- 1、先查询员工
select e.empno, e.ename, e.deptno
from emp e, emp m
where e.mgr=m.empno and e.hiredate(select avg(sal) from emp)

select e.*, d.dname, m.ename, s.grade 
from 
    emp e left outer join dept d on e.deptno=d.deptno
          left outer join emp m on e.mgr=m.empno
          left outer join salgrade s on e.sal between s.losal and s.hisal
where e.sal>(select avg(sal) from emp)
 
课时23 23.多表查询练习第10题 
列出与庞统从事相同工作的所有员工及部门名称
 
select e.*, d.dname
from emp e left outer join dept d
on e.deptno=d.deptno
where e.job=(select job from emp where ename='庞统')

 
课时24 24.多表查询练习第11题 
列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
 
select e.ename, e.sal, d.dname
from ename e left outer join deptno d
on e.deptno=d.deptno
where e.sal>(select max(sal) from emp where deptno=30)

 
课时25 24.多表查询练习第13题 
查出年份，利润，年度增长比
 
select * from tb_year
year  zz
2000  100
2001  150
2002  250
2003  300

select y1.* ifnull(concat((y1.zz-y2.zz)/y2.zz * 100, '%'), '0%') 增长比
from tb_year y1 left outer join tb_year y2
on y1.year=y2.year+1