数据库SQL2005几个简单的语句、

好多啊。。。。。 首先两个表不知道怎么关联，暂且默认 employee 里面有个pid的字段吧。 然后salary 是日薪？月薪？年薪？暂且当月薪吧 e_part 是个啥？ --1、列出月薪比 "BLAKE" 少的所有雇员 select from employee where salary < (select isnull(salary,0) from employee where ename = 'BLAKE') --2、列出至少有一个雇员的部门详细信息 select from employee left join department on departmentpid = employeepid --3、列出所有雇员的姓名及其直接上级的姓名。 select eename , mename from employee e left join employee m on emanager = meid --4、列出入职日期早于其直接上级的所有雇员 select eename from employee e left join employee m on emanager = meid where ehiredate < mhiredate --5、列出没有雇员的部门信息 select from department where pid not in (select distinct pid from employee) --6、列出所有“CLERK”（办事员）的姓名及其部门名称 select eename,ppname from employee e left join parentment p on epid = ppid where job = 'CLERK' --7、列出最低薪金大于1500的工作类别信息 SELECT DISTINCT JOB FROM EMPLOYEE WHERE salary > 1500 --8、列出月薪高于公司平均水平的所有雇员 select from employee where salary > (select avg(salary) from employee) --9、列出与“SCOTT”从事相同工作的所有雇员 select from employee where job in (select job from employee where ename = 'SCOTT') ----10、列出某些雇员的姓名和薪金，条件是他们的月薪高于部门30中所有雇员的薪金 SELECT eENAME,eSALARY FROM EMPLOYEE e inner join (select max(salary) as msalary,pid from employee group by pid) ms on esalary = msmsalary and epid = mspid ----11、列出每个部门的信息以及该部门中雇员的数量-- select dpid,dpname,cscEMP from department d left join (select pid,count() as cEMP from employee group by pid) cs on dpid = cspid --12、列出所有雇员的雇员名称、部门名称和月薪 SELECT eename,ddepart,esalary from employee e left join department d on dpid = epid --13、列出各个部门的MANAGER（经理）的最低薪金 这句话不好理解。最好解析一下。 select dpname,esalary from employee e left join department d on dpid = epid where ejob = 'MANAGER' --14、列出所有雇员的年薪，并且按年薪排序 SELECT ENAME,SALARY 12 AS YSALARY FROM EMPLOYEE ORDER BY SALARY --15、列出薪金水平处于第四位到第七位的雇员 SELECT FROM EMPLOYEE WHERE EID IN (SELECT TOP 7 PID FROM EMPLOYEE ORDER BY SALARY DESC) AND EID NOT IN (SELECT TOP 3 PID FROM EMPLOYEE ORDER BY SALARY DESC) ---------------一额汗

用union all进行多表联结:

select i字段1, i字段2, i字段3,

from(

select 字段1, 字段2, 字段3, from info1

union all

select 字段1, 字段2, 字段3, from info2

union all

select 字段1, 字段2, 字段3, from info3

) info i;

这样做性能应该还不错，最好写明字段，最好建立一个视图进行查询

创建

create proc p_test

@rwmc varchar(30),

@gh varchar(40)

as

begin

select c数量,c信息,cID,c版本,cIP,c次数,c间隔,c票换,c黑名单,c人数,c备用1,c备用2,c状态,cIP段,a票数1,b票数2

from

(select sum(票数) 票数1 from  NMXSJJL  where 任务名称=@rwmc) a,

(select sum(票数) 票数2 from  NMXSJJL  where 任务名称=@rwmc and 工号=@gh) b,

(SELECT 数量,信息,ID,版本,IP,次数,间隔,票换,黑名单,人数,备用1,备用2,状态,IP段 from NMZKB where 任务名称=@rwmc) c

end

执行

exec p_test '任务名称','工号'

-----(1)------------------------------

use school

go

create table student (

sid int not null,

sname varchar(20) not null,

sex varchar(5) not null,

department_name varchar(20) not null,

age int null default 0,

constraint pk_student primary key (sid)

)

go

create table score (

sid int not null,

cid int not null,

mark int not null,

constraint pk_score primary key (sid, cid)

)

go

-----(2)-----------------------------

insert into student values(1, '蛋头', '男', '炮灰系', 21)

insert into student values(2, '阿炮', '男', '冒险系', 22)

insert into student values(3, '龟毛', '男', '极地系', 19)

insert into score values(3, 5, 89)

insert into score values(3, 6, 58)

insert into score values(2, 5, 60)

go

-----(3)------------------------------

update score set mark = mark 06 where cid=3

go

-----(4)------------------------------

create view score_view

as

begin

select sid, sex, sname, department_name from student

end

go

-----(5)------------------------------

select sname, department_name score_view where sex='女'

go

-----(6)------------------------------

drop table score

go

drop table student

go

将三个选中的数据从hibernate里面查询出来。

然后再执行删除 *** 作，面向对象觉度来思考撒

如果你非要用sql来写，那可以这样。比如你传了n个id过来删除。这n个id你保存在数组当中,比如为userId[];

那sql这样写

StringBuffer sql = new StringBuffer("delete from XXX where 1=2");

for(int i=0;i<userIdlength;i++){

sqlappend(" or userId="+userId[i]);

}

Query query = getSession()createSQLQuery(sqltoString);

以上就是关于数据库SQL2005几个简单的语句、全部的内容，包括:数据库SQL2005几个简单的语句、、sql数据库多表查询语句、SQL数据库多条语句查询结果合并输出的问题等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！