天天看點
傳回

Java學習路線-42：SQL進階：限制、關系、連接配接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 < 1000;

-- 查詢名字由3個字組成的員工(3個下劃線)
select * from emp where ename like '___';

-- 查詢2000年入職的員工
select * from emp where hiredate like '2000-%';

-- 查詢所有員工，用編号升序排序
select * from emp order by empno asc;

-- 查詢所有員工詳細資訊，用工資降序排序，如果工資相同使用入職日期升序排序
select * from emp order by sal desc, hiredate asc;

-- 查詢每個部門的平均工資
select deptno, avg(sal) from emp group by deptno;

-- 查詢每個部門雇員數量
select deptno, count(*) from emp group by deptno;

-- 查詢每種工作的最高工資，最低工資，人數
select job, max(sal), min(sal), count(*) from emp group by job;

課時2 2.mysql編碼問題 

-- 檢視MySQL的資料庫編碼
show variables like 'char%';

-- 臨時設定變量
set character_set_client=utf8
set character_set_results=utf8

永久設定:

my.ini中配置

課時3 3.mysql備份與恢複資料庫1、備份：資料庫->SQL語句 

$ mysqldump -u使用者名 -p密碼 資料庫名 > 要生成的SQL腳本路徑

2、恢複：SQL語句->資料庫

$ mysql -u使用者名 -p密碼 資料庫名 < 要生成的SQL腳本路徑
# 或者
> 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> employee;
}

// 多對多關系 老師-學生
class Student{
    private List<Teacher> teachers
}

class Teacher{
    private List<Student> 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 列 from 表2 别名2 where 條件)

-- 多行單列
select * from 表1 别名1 where 列1[in, all, any]
(select 列 from 表2 别名2 where 條件)

-- 單行多列（一個對象）
select * from 表1 别名1 where (列1, 列2) in
(select 列1, 列2 from 表2 别名2 where 條件)

-- 多行多列
select * from 表1 别名1, 
(select ...) 别名2 where 條件

eg:

-- 工資高于平均工資的員工
select * from emp where sal > (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<m.hiredate

-- 2、查詢部門名稱
select e.empno, e.ename, d.dname
from emp e, emp m, dept d
where e.mgr=m.empno 
and e.hiredate<m.hiredate 
and e.deptno=d.deptno

課時19 19.多表查詢練習第5題

列出部門名稱和這些部門的員工資訊，同時列出哪些沒有員工的部門

select *
from emp e right outer join dept d
on e.deptno=d.deptno;

課時20 20.多表查詢練習第7題

列出最低薪金大于15000的各種工作及從事此工作的員勞工數

select job, count(*)
from emp e
group by job
having min(sal) > 15000

課時21 21.多表查詢練習第8題

列出在銷售部工作的員工姓名，假定不知道銷售部的部門編号

select ename
from emp e
where e.deptno = (select deptno from dept where dname='銷售部')

課時22 22.多表查詢練習第9題

列出薪金高于公司平均薪金的所有員工資訊，所在部門名稱，上級上司，工資等級

-- 薪金高于公司平均薪金的所有員工資訊
select * from emp where e.sal>(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
sql 資料可視化 java 資料庫 mysql Java連接配接 java資料互動 java資料前端 java連接配接sql java資料對象
上一篇: Java學習路線-42：SQL進階：限制、關系、連接配接SQL進階：限制、關系、連接配接
下一篇: Java學習路線-43：JDBC資料庫開發入門5-JDBC資料庫開發入門

繼續閱讀