sql查询语句

数据库语句使用


2012——2——6
use LiangShanHeros
go
---create dept table
create table dept
(deptno int primary key,
dname   nvarchar(30),
loc     nvarchar(30))
----------------
--create emp table
create table emp(
empno int primary key,
ename nvarchar(30),
job   nvarchar(30),
mgr   int,
hiredate datetime,
sal   numeric(10,2),
comm numeric(10,2),
deprno int foreign key references dept(deptno)
)


---针对外键，请注意：
---①外键只能指向主键
---②外键和主键的数据类型要一致


insert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')
select * from dept
select * from emp




insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800.00,200.00,20)
insert into emp values(7499,'allen','salesman',7698,'1981-2-20',1600.00,300,30)
insert into emp values(7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30)
insert into emp values(7566,'jones','manages',7839,'1981-1-2',2975,500.00,30)


insert into emp values(7654,'martin','salesman',7698,1981-9-28,1250.00,1400.00,30)
insert into emp values(7698,'blake','manager',7839,'1981-5-1',2850.00,null,30)
insert into emp values(7782,'clark','manager',7839,'1981-6-9',2450.00,null,10)
insert into emp values(7788,'scott','analyst'7566,'1987-4-19',3000.00,null,20)
insert into emp values(7839,'king','prestdent',null,'1981-11-17',5000.00,null,10)
insert into emp values(7844,'turner','salesman',7986,'1981-9-8',1500.00,0.00,30)
insert into emp values(7876,'adams','clerk',7788,'1987-5-23',1100.00,null,20)
insert into emp values(7900,'james','clerk',7698,'1987-12-3',950.00,null,30)
insert into emp values(7902,'ford','analyst',7566,'1981-12-3',3000.00,null,20)
insert into emp values(7934,'milier','clerk',7782,'1982-1-23',1300.00,null,10)
insert into emp values(999,'shunping','clerk',7782,'1988-5-5',2456.34,55.66,10)








2012——2——7
use LiangShanHeros
go


select  distinct deptno ,ename from emp
----how to dispose
select ename ,sal*13 +isnull(comm,0)*13 'sal' from emp
--如何查找1982.1.1后入职的员工
select * from emp where hiredate>'1982-1-1'


--显示工资在2000到3000的员工
select * from emp where sal>2000 and sal<2500
select * from emp where sal between 2000 and 2500
--如何显示首字符为s的员工的姓名和工资
select ename,sal from emp where ename like 's%';
--如何显示第三个字符为大写O的所有员工的姓名和工资
select ename,sal from emp where ename like '__O%';


---如何显示empno为123,345,800...的雇员情况
---一般使用in这个关键字，效率更高
select * from emp where empno=123 or empno=345 or empno=800
select * from emp where empno in(123,345,800);


---如何显示没有上级的雇员的情况
select * from emp where mgr is null;




---查询工资高于500或是岗位为MANAGER的雇员，同时满足他们的姓名首写字母为j
select * from emp where (sal>500 or job='manager') and ename like 'j%';


---如何按照工资的从低到高的顺序显示雇员的信息
---order by 默认是升序排列
---order by desc
---按照入职的先后顺序排列
select * from emp order by sal asc
select * from emp order by hiredate asc


---按照部门号升序而雇员的工资降序排列
---order by 可以根据不同的字段排序
select * from emp order by deptno,sal desc




---统计每个人的年薪，并按照从低到高的顺序排列
select ename,(sal+isnull(comm,0)*13) nianxin from emp order by nianxin 


---如何显示所有员工中最高工资和最低工资
---如何显示最低工资和该雇员的名字
select * from emp where sal=(select min(sal) from emp)




---显示所有员工的平均工资和工资总和
--把高于平均工资的雇员的名字和他的工资显示
select avg(sal) 平均工资 ,sum(sal) 总工资 from emp
select ename,sal from emp where sal>(select avg(sal) from emp)
select ename,sal,(select avg(sal) from emp) avgxinshui from emp where sal>(select avg(sal) from emp)




----统计员工个数
select count(*) from emp




----如何显示每个部门的平均工资和最高工资
----显示每个部门的每种岗位的平均工资和最低工资
----显示平均工资低于2000的部门号和他的平均工资
----having 往往和group by 结合使用，可以对分组查询结果进行筛选
select avg(sal),deptno ,max(sal) from emp group by deptno
select avg(sal) 平均工资,min(sal) 最少工资,deptno,job from emp group by deptno,job order by deptno
select avg(sal) vv,deptno from emp group by deptno having avg(sal)>=1500 order by avg(sal)










2012——2——7
use LiangShanHeros
go


select  distinct deptno ,ename from emp
----how to dispose
select ename ,sal*13 +isnull(comm,0)*13 'sal' from emp
--如何查找1982.1.1后入职的员工
select * from emp where hiredate>'1982-1-1'


--显示工资在2000到3000的员工
select * from emp where sal>2000 and sal<2500
select * from emp where sal between 2000 and 2500
--如何显示首字符为s的员工的姓名和工资
select ename,sal from emp where ename like 's%';
--如何显示第三个字符为大写O的所有员工的姓名和工资
select ename,sal from emp where ename like '__O%';


---如何显示empno为123,345,800...的雇员情况
---一般使用in这个关键字，效率更高
select * from emp where empno=123 or empno=345 or empno=800
select * from emp where empno in(123,345,800);


---如何显示没有上级的雇员的情况
select * from emp where mgr is null;




---查询工资高于500或是岗位为MANAGER的雇员，同时满足他们的姓名首写字母为j
select * from emp where (sal>500 or job='manager') and ename like 'j%';


---如何按照工资的从低到高的顺序显示雇员的信息
---order by 默认是升序排列
---order by desc
---按照入职的先后顺序排列
select * from emp order by sal asc
select * from emp order by hiredate asc


---按照部门号升序而雇员的工资降序排列
---order by 可以根据不同的字段排序
select * from emp order by deptno,sal desc




---统计每个人的年薪，并按照从低到高的顺序排列
select ename,(sal+isnull(comm,0)*13) nianxin from emp order by nianxin 


---如何显示所有员工中最高工资和最低工资
---如何显示最低工资和该雇员的名字
select * from emp where sal=(select min(sal) from emp)










2012——2——21
use master
 go
---------
select * from emp
select * from emp where ename like 's'
select * from emp where ename like '__O%'
select * from emp where (sal>500 or job='manger') and ename like 'j%'
select * from emp order by sal asc


----------------------------------
select ename ,(sal+isnull(comm,0)*13) nianxian from emp order by nianxian




select ename from emp where sal=(select min(sal) from emp)+10
select * from emp
select * from dept
---多表查询
---显示雇员名，雇员工资及所在部门的名字，部门好
---如果两张都有相同名字的字段，则需要带表名,也可以对表名进行别名修改，用表名进行查询
select ename,sal,e.deprno 'emp.deprno' from emp e,dept where dept.dname='sales' and e.deprno=dept.deptno


---
select d.dname,e.ename,e.sal from emp e,dept d where  d.deptno=10 and e.deprno=d.deptno
---显示雇员名，雇员工资及所在部门的名字，并按部门排序
select e.ename,e.sal,e.deprno from emp e,dept d where e.deprno=d.deptno order by d.dname 


---显示某个员工的上级领导的姓名 比如显示‘FORD’ 的上级
---1.知道FORD上级编号
select ename,(select ename from emp where ename='ford') 'ename' from emp where emptno=(select mgr from emp where ename='ford')


---在同一个表中查询
---思考题：显示公司每个员工名字和他的上级的名字
---分析:表emp表看作两张表，分别为w，b
---外连接（左外链接 右外连接）
select w.ename '雇员',b.ename '老板' from emp w,emp b where w.mgr=b.emptno


---如何显示与SMITH同一部门的所有员工
select ename from emp where deprno=(select deprno from emp where ename='smith') 


---如何查询和部门10的工作相同的雇员的 名字 岗位 工资  部门好
select * from emp where job in(select job from emp where deprno=10)


---如何显示高于部门平均工资的员工的信息


select * from emp where (select deprno from emp )






2012——2——22
use master 
go
---------
---如何显示高于部门平均工资的员工的名字，薪水和他的部门的平均工资
---分析
---1.知道各个部门的平均工资（分组）


select avg(sal),deprno from emp group by deprno 
---2.把上面的查询结果当作一个临时表对待
select emp.ename,emp.sal,tem.myvag,emp.deprno from emp,(select avg(sal) myvag,deprno from emp group by deprno )  tem 
where emp.deprno=tem.deprno and emp.sal>tem.myvag;




---请显示第五个到第十个入职的雇员（按照时间的先后顺序）
---1.显示第一个到第四个入职的雇员
select top 4 * from emp order by hiredate
---top 后面的数表示要取出几条记录


select top 6 * from emp where emptno not in (select top 4 emptno from emp order by hiredate) order by hiredate


---请显示第11个到13个入职的人的信息
select top 3 * from emp e where emptno not in (select top 11 emptno from emp order by hiredate) order by e.hiredate


---
---如何删除掉一张表重复的记录
create table cat(
catID int,
catName varchar(10))


insert into cat values(2,'b')
select * from cat




---把cat表的记录distinct后的结果放入到临时表#tenp中去
select distinct * into #tem from cat


---清空cat表的记录
delete from cat 


---把#temp表的数据（该表示没有重复的记录），插入到cat表
insert into cat select * from #temp


---删除#temp
drop table #tem




---讲左外连接和右外连接
---显示公司每个员工和他的上级名字
---内连接
select e.ename,p.ename from emp e,emp p where e.mgr=p.mgr


---显示公司每个员工和他的上级名字，要求没有上级人，名字也要显示
---左外连接：指左边的表的记录全部显示，右边如果没有匹配的记录就用null来填
---右外连接：指右边的表的记录全部显示，左边如果没有匹配的记录就用null来填
select w.ename,b.ename from emp w left join emp b on w.mgr=b.emptno
select w.ename,b.ename from emp w right join emp b on w.mgr=b.emptno
select * from emp
select * from emp




---








2012-2-23


create table test1
(
testId int primary key identity(1,1),
testName varchar(30) not null,
testPass varchar(30) not null,
testAge int --
)
---int --表示可空可不空
insert into test1 (testAge) values(3)


insert into test1 (testName,testPass,testAge) values('','',5)
select * from test1




create table test2
(
testId int primary key identity(1,1),
testName varchar(30) unique,---唯一的
testPass varchar(30) ,
testAge int --
)
insert into test2 (testName,testPass,testAge) values('aa','123',45)
insert into test2 (testPass,testAge) values('123',45)
insert into test2 (testName,testPass,testAge) values('','123',45)
select * from test2


---复合组件
create table test3
(
test1Id varchar(30),
testName varchar(30),
testAge int,
primary key (test1Id,testName)
)


create table test4
( 
testId int,
testName varchar(30),
testPass varchar(30),
sal int check(sal>=1000 and sal<=2000)
)
insert into test4 values(4,'aa','aa',2200)




---default
 create table mes(
mesid int primary key identity(1,1),
mescon varchar(2200) not null,
mesdate datetime default getdate(),
)
insert into mes (mescon) values('hello')
select * from mes


---商品售货系统表设计案例
---现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
---商品goods（商品号goodsid，商品名goodsName，单价unitprice，商品类别
---category，供应商provider）；


---客户customer（客户号customerId，姓名name，住址address，电邮email，性别sex，身份证cardId）


---购买purchase（客户号customerId，商品名goodsId,购买数量nums）


---要求：
--（1）每个表的主外键
---（2）客户的姓名不能为空值
--（3）单价必须大于0，购买数量必须在1到30之间；
---（4）电邮不能够重复
--（5）客户的性别必须是男或者女，默认是男
---
create table goods(
goodsid nvarchar(10) primary key,
goodsName nvarchar(10) not null,
unitprice numeric(10,2) check (unitprice>0),
category nvarchar(3) check (category in('食品','日用品')),
provider nvarchar(10)
)
-----
create table customer(
customerId nvarchar(10) primary key ,
customname nvarchar(10) not null,
address nvarchar(100),
email nvarchar(200) unique,
sex nchar(2) check (sex in('男','女')) default '男',
cardId nvarchar(10)
)
-------
create table purchase
(
customerId nvarchar(10) foreign key references customer(customerId),
goodsId nvarchar(10) foreign key references goods(goodsId),
nums int check (nums>0)
)








2012-2-25
use ubuntu 
go


select * from dept
insert into dept values('10','ce','jinang')


---sql注入漏洞
create table users
(
username nvarchar(10),
pass varchar(10)
)
drop table users
insert into users values('12123','sad')
select * from users
select * from users where username='luanxue' and pass='luanxue' or 1='1'








2012-3-3
create database spdbl
use spdbl
go
----
--创建学生表
create table stu
(
stuId varchar(30) primary key,
stuName nvarchar(50) not null,
stuSex nvarchar(1)  check (stuSex in('男','女')) default '男',
stuAge int check (stuAge>1),
stuJg nvarchar(30),
stuDept nvarchar(40)
)


insert into stu values('sp001','孙悟空','男',20,'花果山','少林派');
insert into stu values('sp002','猪八戒','男',15,'高老庄','天上的');
insert into stu values('sp003','沙悟净','男',26,'流沙河','水里的');






2012-4-6
-------实验五单表查询
create database shiyan5
use shiyan5 
go
create table student(
sno nvarchar(5) primary key,
sname nvarchar(6),
ssex nvarchar(2) check(ssex in('男','女')) default '男',
sage int ,
sdept nvarchar(5)
)


create table course(
cno nvarchar(2) primary key,
cname nvarchar(10),
cpno nvarchar(2),
ccredit nvarchar(2)
)


create table sc(
sno nvarchar(5) foreign key references student(sno),
cno nvarchar(2) foreign key references course(cno),
grade nvarchar(3)
)  
drop table sc,student,course
select * from student,course,sc


insert into student values('95001','李勇','男','20','cs')
insert into student values('95002','刘晨','女','19','is')
insert into student values('95003','王敏','女','18','ma')
insert into student values('95004','张立','男','19','is')






insert into course values('1','数据库','5','4')
insert into course values('2','数学',null,'2')
insert into course values('3','信息系统','1','4')
insert into course values('4','操作系统','6','3')
insert into course values('5','数据结构','7','4')
insert into course values('6','数据处理','2','2')
insert into course values('7','C语言','6','4')






select * from course
insert into sc values('95001','1','92')
insert into sc values('95001','2','85')
insert into sc values('95001','3','88')
insert into sc values('95002','2','90')
insert into sc values('95002','3','80')


select * from course
select * from sc
select * from student


----查询
--例1-9
select * from course


select cno,cname,ccredit from course


--2.使用top，distinct关键字查询
select top 2 * from sc
select top 50 percent * from sc


select ccredit from course
select distinct ccredit from course 


----使用计算机列进行查询
---例1-12
select 教职工.姓名,工资*9 as 预发工资 from teacher


---注意在Transat-sql的计算机上，可以使用+，-，*，/，%等运算符和and，or，not等逻辑运算符以及字符串连接符（+）


--例如1-13
select cno+'.'+cname,cpno,ccredit from course
---4.操作查询结果的列名
select cno '课程号',cname'课程名称',ccredit+1 '修正学分'
from course
select '课程编号'=cno,'课程名'=cname,'修正学分'=ccredit+1 from course
select cno as '课程号',cname as '课程名称',ccredit+1 as '修正学分' from course


---5.使用where子句进行查询
---1.算术表达式
---例如1-14
select cno '课程编号',cname '课程名称',ccredit '学分'
from course
----2.逻辑表达式
select cno'课程编号' 
from course where ccredit>4 and cno<5
----3.在where子句中使用detween表达式
select cno from course where ccredit between 4 and 5
----4.在where子句中使用in关键字
----5.在where子句中使用like关键字
select * from
student
where sname like '李%' or sname like '_[晨,立]%'
-----6.使用order by子句进行查询
select * from course where ccredit>1
order by cno,cpno desc
-----7.使用企业管理器进行查询




use temp
go
create database ShiYan
use ShiYan 
go
use shiyan5
go
use shiyan5
go
create table S(
SNO nvarchar(4) primary key,
SNAME nvarchar(20) not null,
STATUS int,
CITY nvarchar(30)
)
--------
create table P
(
PNO nvarchar(4) primary key,
PNAME nvarchar(20) not null,
COLOR nvarchar(6),
WEIGHT int default 0
)
-----
create table J(
JNO nvarchar(4) primary key,
JNAME nvarchar(20) not null,
CITY nvarchar(30)
)
drop table J
-----
create table SPJ
(
SNO nvarchar(4) foreign key references S(SNO),
PNO nvarchar(4) foreign key references P(PNO),
JNO nvarchar(4) foreign key references J(JNO),
QTY int check(QTY>1 and QTY<1000)
)
drop table SPJ


insert into S values('S1','精益','20','天津')
insert into S values('S2','盛锡','10','北京')
insert into S values('S3','东方红','30','北京')
insert into S values('S4','丰盛泰','20','天津')
insert into S values('S5','为民','30','上海')


insert into P values('P1','螺丝','红','12')
insert into P values('P2','螺栓','绿','17')
insert into P values('P3','螺丝刀','蓝','14')
insert into P values('P4','螺丝刀','红','14')
insert into P values('P5','凸轮','蓝','40')
insert into P values('P6','齿轮','红','30')


insert into J values('J1','三键','北京')
insert into J values('J2','一汽','长春')
insert into J values('J3','弹簧厂','天津')
insert into J values('J4','造船厂','天津')
insert into J values('J5','机床厂','唐山')
insert into J values('J6','无线电厂','常州')
insert into J values('J7','半导体厂','南京')
select * from SPJ
select * from J
insert into SPJ values('S1','P1','J1','200')
insert into SPJ values('S1','P1','J3','100')
insert into SPJ values('S1','P1','J4','700')
insert into SPJ values('S1','P2','J2','100')
insert into SPJ values('S2','P3','J1','400')
insert into SPJ values('S2','P3','J2','200')
insert into SPJ values('S2','P3','J5','400')
insert into SPJ values('S2','P3','J5','400')
insert into SPJ values('S2','P3','J4','500')
insert into SPJ values('S2','P3','J5','400')
insert into SPJ values('S2','P5','J1','400')
insert into SPJ values('S2','P5','J2','100')
insert into SPJ values('S3','P1','J1','200')
insert into SPJ values('S3','P3','J1','200')
insert into SPJ values('S4','P5','J1','100')
insert into SPJ values('S4','P6','J3','300')
insert into SPJ values('S4','P6','J4','200')
insert into SPJ values('S5','P2','J4','100')
insert into SPJ values('S5','P3','J1','200')
insert into SPJ values('S5','P6','J2','200')
insert into SPJ values('S5','P6','J4','500')




---学生表




---多表查询
---（1）连接查询
---1）(只保留满足条件的记录集)等值查询，非等值查询和自然查询,把目标列中重复的属性列去掉则称为自然连接
---例1-21
select J.*,SPJ.* from J,SPJ where J.JNO=SPJ.JNO
select J.*,PNO,SNO,QTY from J,SPJ where J.JNO=SPJ.JNO
select J.* from J,SPJ where J.JNO=SPJ.JNO
select SPJ.* from J,SPJ where J.JNO=SPJ.JNO
select *,* from J,SPJ where J.JNO=SPJ.JNO
select student.*,sc.cno,grade from student,sc where student.sno=sc.sno
select student.*,sc.* from student,sc where student.sno=sc.sno
select * from J
select * from SPJ
---2）（可保留一方不满足条件的记录，对应的一方为空值）外连接此时不用where
select J.*,SPJ.* from SPJ,J where SPJ.JNO=J.JNO
select student.* ,cno,grade 
from student left join sc 
on student.sno=sc.sno
---3）复合连接
---(4)自身连接
select * from course
select first.cno as "课程号",second.cno as "先行课编号"
from course first,course second where first.cpno=second.cno
---（2）嵌套查询：
---一个select ... from ...where语句称为一个查询块。将一个查询块嵌套在另一个查询块的where
---子句或
----1-27
select sno,cno,grade
from sc
where sno in(
select sno
from student where sdept='is')
--1-28 查询没有选修1号课程的学生的姓名。
select sname from student 
where not exists
(select * from sc 
where sno=student.sno and cno='1'
) 
select * from student
select * from sc


--例1-29
---（3）集合查询：如果有多个不同的查询结果数据集，但又希望他们连接在一起，组成一组数据，
---这组数据就这多个结果集的逻辑联合。此时可以union子句，但union要遵循两条基本原则：
---1.每一个查询结果集的数据类型必须相同
---2.每一个查询结果集合中的列数必须相同，列的排列顺序必须相互对应


---实验五（作业）
--1.查询为工程J1供应红色零件的供应商号码SNO
select distinct SNO from SPJ
where JNO='J1' and 
PNO in 
(select distinct PNO from P where color='红')
--2.查询没有使用天津供应商生产的零件并且当前工程所使用零件的颜色全部为红色的工程号JNO
select j.JNO from J,SPJ where j.JNO not in
(
select distinct JNO from SPJ
where SNO in
(
select SNO from S where CITY ='天津'
)and  j.JNO=SPJ.JNO
---
)
and j.JNO in(
select distinct JNO from SPJ
where SNO not in
(
select SNO from S where CITY ='天津'
)and  j.JNO=SPJ.JNO
)
and j.JNO=spj.JNO
and spj.PNO not in(
select  p.PNO from P where p.COLOR!='红' and p.PNO=spj.PNO

)

--3.查询至少选用了供应商s1所供应的全部零件的工程号jno
--分析：不存在这样的零件，即是s1生产的，但又不是jno使用的
--
select distinct jno 
from J x 
where  not exists
(
--不存在是s1生产并且该零件是jno使用的
select * 
from SPJ y
where SNO='s1'
and not exists(
--找出s1和jno共同的
select * 
from SPJ z,J x
where z.PNO=y.PNO and z.JNO=x.JNO and x.JNO=y.JNO
)
)
--4.找出工程j2使用的各种零件的名称及其重量
select p.PNAME,p.WEIGHT 
from p
where exists
(
select * 
from SPJ
where spj.PNO=p.PNO and spj.JNO='j2'
) 
--5.找出上海厂商供应的所有零件号码
select distinct  pno
from SPJ 
where SNO=(
select s.SNO 
from s
where s.CITY='上海'
)
--6.找出使用上海厂产的零件的工程名称
select P.PNAME
from p 
where p.PNO in
(
select spj.PNO
from SPJ
where SPJ.SNO=
(
select s.SNO 
from s 
where s.CITY='上海'
)
)
--7.找出没有使用天津产的零件的工程号
select J.JNO
from J
where J.JNO not in
(
select distinct spj.JNO 
from SPJ
where spj.SNO in
(
select s.SNO 
from s
where s.CITY='天津'
)
)
--8.找出质量最轻的红色零件的零件号pno
select distinct jno 
from SPJ 
where spj.PNO=
(
select PNO 
from P temp
where temp.WEIGHT<=all(
select P.WEIGHT 
from P
where p.COLOR='红'
)  
)
--9.找出供应商与工程所在城市相同的供应商提供的零件号码
select distinct spj.PNO 
from SPJ,s,J
where SPJ.JNO=j.JNO and s.CITY=j.CITY and spj.SNO=s.SNO
--10.找出所有这样的一些<CITY,CITY,PNAME>三元组，使得第一个城市的供应商为第二个城市的工程供应零件的名称ＰＮＡＭＥ
select s.CITY,j.CITY,P.PNAME 
from s,J,P,SPJ
where s.SNO=spj.SNO and j.JNO=spj.JNO and p.PNO=spj.PNO


--11.重复第10题，但不检索两个CITY值相同的三元组
select s.CITY,j.CITY,P.PNAME 
from s,J,P,SPJ
where s.SNO=spj.SNO and j.JNO=spj.JNO and p.PNO=spj.PNO and s.CITY!=j.CITY
--12.找出供应商s1为工程名中含有"厂"
select QTY 
from SPJ
where spj.SNO='s1'
and exists 
(
select * 
from J
where J.JNAME like '%厂' and j.JNO=spj.JNO 
)