数据库第三天－多表查询

多表查询

是指基于两个和两个以上的表或是视图的查询。select * from emp,dept

如显示雇员名，雇员工资及所在部门的名字【笛卡尔集】

笛卡尔集

--把两张表都显示出来
select * from emp
select * from dept

--笛卡尔集
select * from emp,dept

--显示SALES部门的员工的信息
select * from emp,dept where dept.DNAME='SALES' and emp.DEPTNO=dept.DEPTNo

--显示雇员名，雇员工资及所在部门的名字和部门号
select ename,sal,loc,dept.DEPTNo from emp,dept where emp.DEPTNO=dept.DEPTNo

--使用别名时,要统一使用别名
select ename,sal,loc,d.DEPTNo from emp e,dept d where e.DEPTNO=d.DEPTNo

 

如果两张表都有相同的字段，则需要带表名（别名）

--显示雇员名,雇员工资及所在的部门的名字,并按部门排序
select e.ENAME,e.SAL,d.DNAME from emp e,dept d 
where e.DEPTNO=d.DEPTNo order by d.DNAME

自连接

自连接是指在同一张表的连接查询。

如显示某个员工上级的领导姓名

--显示某个员工上级的领导姓名,如'FORD'
--分步：
--1.先显示FORD上级的编号
select emp.MGR from emp where emp.ENAME='FORD'
--2.
select ename from emp where EMPNO=(select MGR from emp where ENAME='FORD')

单表扩张成两个表，利用笛卡尔集显示所有雇员的上级：

--思考:显示公司每个员工和他的上级的名字(笛卡尔集容解释)
select worker.ENAME,boss.ENAME from emp worker,emp boss where worker.MGR=boss.EMPNO

注：此时，boss为null的雇员没有查询出来，要用到外连接

子查询（嵌套查询）

单行子查询：只返回一行数据的子查询语句

多行子查询

--思考:如何查询和部门里的工作相同的所有雇员的信息(排除部门本身)
select * from emp where emp.DEPTNO!=10 and 
JOB in (select distinct job from emp where DEPTNO=10)

注：单行返回结果用 ＝ ，多行返回结果用 in

from子句中使用子查询

把查询语句的结构当做一个临时表，置于ｆｒｏｍ子句里

如显示每个部门中，高于其部门平均工资的员工信息

--如何显示高于部门平均工资的员工的信息
--1.求每个部门的平均工资
select AVG(sal) av,DEPTNO from emp group by DEPTNO
--把上述查询结果当做一个临时表
select * from emp,(select AVG(sal) myavg,DEPTNO from emp group by DEPTNO) tmp 
where emp.DEPTNO=tmp.DEPTNO and emp.SAL>tmp.myavg

分页查询

--显示第到第位入职人的信息
select top 3 * from emp where EMPNO not in 
(select top 10 EMPNO from emp order by HIREdate) order by HIREdate

--显示工资从高到低的第到第位员工的信息
select top 5 * from emp where EMPNO not in 
(select top 4 EMPNO from emp order by SAL desc) order by SAL desc

分页查询，数据量大时的测试：

--identity(1,1):表示该字段自增,从开始每次+1
create table test
(testID int primary key identity(1,1),
testName varchar(30),
testPass varchar(30)
)

insert into test (testName,testPass) values ('baiweiguo','baiweiguo')
--自身复制
insert into test (testName,testPass) select testName,testPass from test

select top 6 * from test where testID not in 
(select top 999 testID from test)

如何删除一张表的重复记录

--如何删除一张表的重复记录
create table cat(
catID int,
catName varchar(40)
)
insert into cat values (1,'aa')

--选出test无重复记录的结果,并存于临时表#temp中
select distinct * into #temp from cat
--把cat表清空
delete from cat
--把表#temp中的数据存于表cat中
insert into cat select * from #temp
--将临时表#temp删掉
drop table #temp
select * from cat

左外连接与右外连接

左外连接:指左边的表的纪录全部显示,如果没有匹配的纪录就用那个空表示

--左外连接和右外连接
--思考：显示公司每个员工和他上级的名字
--内连接(没有显示无boss的员工姓名)
select w.ENAME,b.ENAME from emp w,emp b where w.MGR=b.EMPNO

--思考:显示公司每个员工和他上级的名字,同时要显示没有上级的员工的名字
--左外连接
select w.ENAME,b.ENAME from emp w left join emp b on w.MGR=b.EMPNO

维护数据的完整性－约束

约束，用于维护数据库数据满足特定的商业规则。

在sql server中，约束包括：not null，unique，primary key，foreign key，check 五种

注：空 NULL 与 空 '' 有区别：null指没有分配任何东西，'' 分配了空间。相当于没有房子（null），有房子，但房子是空的（''）

1.not null（非空）：如果在列上定义了not null，则插入数据时，必须提供数据

2.unique 与 primary key都表示列值不能重复，但unique可以存null（最多只能由一个null），而primary key不能为存null

   一张表只能由一个主键，但可以由多个unique约束

    表可以有复合主键：多列 作为一个主键。

create table test1(
testID int,　　　　　　　　　　　－－ｔｅｓｔＩＩＤ　ｉｎｔ　ｐｒｉｍａｒｙ　ｋｅｙ　行级定义
testName varchar(30),
testPass varchar(30),
age int,
primary key (testID,testName)　　－－表级定义
)

3.foreign key（外键）

   用于定义主表和从表之间的关系

4.check约束

create table test1(
testID int,
testName varchar(30),
testPass varchar(30),
age int check (age>=0 and age<=100),
)

5.default的使用

--default 的使用
create table mes(
mesID int primary key identity(1,1),
mesCon varchar(2000) not null,
mesDate datetime default getdate()
)
insert mes(mesCon) values('你好吗')