sql server 查询语句

sql server 2000 查询语句

   下面都是sql server 中的查询语句，但大多数是通用于数据库的，特别是sqlite
drop table spname
create table spname
(table1 int primary key , myname nvarchar(4) , age int , date datetime)
insert into spname values (1, '钟志钢1', 10, getdate())
insert into spname values (2, '钟志钢2', 20, getdate())
insert into spname values (3, '钟志钢3', 30, getdate())--主键必须给值
insert into spname(table1, myname, date) values (5, '钟志钢', getdate())
select * from spname

delete  from spname where (age >= 10 and age <= 20) or age is null
delete from spname where age is null or age = 30

drop table table1 (t1 bit)
create table table1 (t1 bit)
insert into table1 values (3)
select * from table1

update spname set age = 30 where table1 = 2
update spname set age = 90, myname = 'zxj' where table1 = 1
update spname set myname = 'zzg' where age is null

create database spdb1
--创建dept表
drop table dept
create table dept
(deptno int primary key,
dname nvarchar(30),
loc nvarchar (30)
)
insert into dept values(10, 'accounting', 'new york')
insert into dept values(20, 'reserch', 'dallas')
insert into dept values(30, 'sales', 'chicago')
insert into dept values(40, 'operations', 'boston')

drop table emp
--创建emp表
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),-- 奖金
deptno int foreign key references dept(deptno)--做成外键
)
insert into emp values (7876, 'adams', 'clerk', 7900, '1987-5-23', 11000.00, 200.00, 20)
insert into emp values (7900, 'james', 'operations', 7698, '1981-12-3', 9050.00, 400.00, 30)
insert into emp(empno,ename,job, mgr,hiredate,sal,deptno) values
 (7698, 'ford', 'amalyst', 1934, '1981-12-3', 3000.00, 20)
insert into emp(empno,ename,job, mgr,hiredate,sal,deptno)  values
 (1934, 'milleer', 'clerk', 1925, '1982-1-23', 1300.00, 10)
insert into emp(empno,ename,job, mgr,hiredate,sal,deptno)  values
 (1924, 'milleer2', 'amalyst', 1934, '1982-1-23', 1300.00, 40)
insert into emp(empno,ename,job,hiredate,sal,deptno)  values
 (1925, 'milleer3', 'clerk',  '1982-1-23', 1300.00, 40)
--关于外键
--只能指向主键
--外键与指向的主键数据类型要一致
select * from dept
select * from emp

--查询james的薪水，工作， 所在部门--spl server不区分大小写,oracle区分
select ename,sal, job, deptno from emp where ename = 'James'

--消除重复(完全相同)
select deptno from emp
select distinct deptno from emp--只能在一列

--显示每个雇员的年工资
select ename, sal*13 '年工资' from emp where ename = 'james'--‘年工资’列是别名
select ename as "姓名", sal*13 + isnull(comm, 0)*13 '年工资' from emp

--查找工资高于1000的员工
select * from emp where sal > 1000

--查找1982－1－1之后入职的员工
select * from emp where hiredate > '1982-1-1'

--查找工资在2000－5000之间的员工(包括等于)
select * from emp where sal between 1100 and 3000

--查找首字符为S的员工姓名和工资
select ename, sal from emp where ename like 'j%'--%表示0到n个字符
--查找第三个字符为a的员工
select ename, sal from emp where ename like '__a%'--一个划线表示任意一个字符

--查找empno为7901，1934, 1924.……的雇员情况
select * from emp  where empno = 1934 or empno =1924 or empno =7902
select * from emp where empno in (1924, 1934, 7902)

--查找没有上级的员工
select * from emp where mgr is null

--查找某个员工的上级???
select ename from emp where

--查找工资高于2000或者岗位为amalystr雇员，同时还要满足们姓名首字符为j
select *  from emp where (sal > 1000 or job = 'amalyst') and ename like 'j%'

--如何按照工资从低到高的顺序显示……。
select * from emp order by sal --默认是asc,升序，desc降序
select * from emp order by sal desc

--按入职的先后顺序
select * from emp order by hiredate

--按姓名字母顺序排列
select * from emp order by ename

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

--先算年薪再排序
select ename , sal * 13 + isnull(comm, 0)* 13 '年薪'from emp order by  '年薪' desc

--数据分组max, min, avg, sum, count
--查找工资最高的与最低的
--如果显示最低工资的雇员的信息
--sql 是从右向左执行,尽量把复杂的运算写在右边（优化）
select min(sal) from emp
select ename, sal from emp where sal = (select min(sal) from emp)
select avg(sal)平均工资,sum(sal)总工资 from emp
--查找高于平均工资的员工信息并显示平均工资
select * from emp where sal > (select avg(sal) from emp)

select empno, ename, job, mgr, hiredate, sal,(select avg(sal) from emp)平均工资, comm ,deptno from emp
--统计员工总数
select count(*)员工总数 from emp

--group by 与having
--显示每个部门(职位)的平均工资和最高工资－－分组
--并显示部门名称(多表查询)
--在选择列中有列、表达式和分组函数，则至少要有一个出现在group by中
select deptno, avg(sal)avg_sal, max(sal)max_sal, min(sal)min_sal from emp group by deptno
--select deptno,(select dname from dept ), avg(sal)avg_sal, max(sal)max_sal, min(sal)min_sal from emp group by deptno
select job, avg(sal)avg_sal, max(sal)max_sal, min(sal)min_sal, from emp group by job
select dname from dept where deptno = 20
select deptno from emp

--显示每个部门的每种岗位的平均工资和最低工资
select avg(sal)avg_sal, min(sal)min_sal, max(sal)max_sal,deptno, job from emp group by deptno, job order by deptno

--显示平均工资低于2000的部分号和它的平均工资
--having 往往是对group by 分组的结果进行筛选
select avg(sal)avg_sal, deptno from emp group by deptno having  avg(sal) < 2000
select avg(sal)avg_sal, deptno from emp group by deptno having  avg(sal) < 2000 order by deptno desc

--多表查询

--显示雇员名字，工资和所在部门
select * from emp,dept--宙卡尔集
select * from emp, dept where dept.dname = 'sales'
select * from emp, dept where dept.dname = 'sales' and dept.deptno = emp.deptno
select ename, sal, loc from emp,dept where dept.dname = 'sales' and dept.deptno = emp.deptno

--显示雇员名字，工资和所在部门和部门号
--如果两张表都有相同的字段，就要带别名
--表也可以取别名：表名+ 别名
select ename, sal, loc , e.deptno from emp e,dept d where d.dname = 'sales' and d.deptno = e.deptno

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

--自连接，同一张表的连接查询（单行子查询）
--显示某个员工上级领导的名字，比如:james
select mgr from emp where ename = 'james'
select ename from emp where empno = (select mgr from emp where ename = 'james')
--显示每个员工和他上级的名字
--把emp看着两张表，emp emper , emp boss
select emper.ename 员工列表, boss.ename 上司 from emp emper , emp boss where emper.mgr = boss.empno

--子查询
--单行子查询
--与james在同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename = 'milleer2')
select * from emp where deptno in (select deptno from emp where ename = 'james')