create table dept --創(chuàng)建“部門”表 ( www.jb51.net deptno int primary key, --部門編號（主鍵） dname nvarchar(30), --部門名稱 loc nvarchar(30) --部門所在的地點 ) -----
create table emp --創(chuàng)建“員工”表 ( 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) --創(chuàng)建一個外鍵指向部門表（描述該員工屬于哪個部門） )
-------- insert into dept values (10,'accounting','new york') insert into dept values (20,'reasarch','dallas') insert into dept values (30,'sales','chicago') insert into dept values (40,'operations','boston') -------
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'michael','clerk',7902,'2010-1-12',675.23,300,20)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'allen','salesman',7698,'2009-1-23',1675.23,322.50,30)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7521,'ward','salesman',7698,'2008-1-3',12675.99,399.50,30)
insert into emp (empno,ename,job,mgr,hiredate,comm,deptno) values (7566,'jones','manager',7839,'2000-1-1',8675.99,20)
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7654,'martin','salesman',7698,'2007-12-31',1275.99,999.00,30)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7782,'blake','manager',7839,'2007-12-20',1275.99,30)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7788,'sccot','analyst',7566,'2003-1-22',1275.99,10)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7781,'miller','opreator',7566,'2005-10-12',1275.99,40) www.jb51.net insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7744,'adamc','opreator',7566,'2006-10-30',1244.0,40)
insert into emp (empno,ename,job,hiredate,sal,deptno) values (7839,'king','president','2000-1-1',100244.0,10)
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (1999,'lxliog','opreator',7566,'2006-10-30',1244.0,40) ----- select * from dept select * from emp
--主鍵：唯一，非空 --外鍵：只能指向主鍵，與指向的主鍵數(shù)據(jù)類型必須一致
二：練習 ============================ 1，查詢emp表所有列 select * from emp --劉君正
2，查詢指定列（例如：姓名，工資兩列） select ename,sal from emp --鄭朝陽
3，取消重復行（從emp表中查詢部門號） select distinct(deptno) from emp --徐紹峰,郝艷芳,劉輝,馬東勤
4，從emp表中查詢名字為lxliog員工的薪水，工作，所作部門 select sal,job,deptno from emp where 'lxliog'=ename --侯耀文,譚雪玲,黎小龍
5，統(tǒng)計總共有都少個部門(兩種方式：從emp或從dept) select count(deptno) from dept --諾布才仁 select count(distinct(deptno)) as 部門總數(shù) from emp --諾布才仁
use mf2011 select * from dept; select * from emp; --6，顯示每個雇員的年工資 select isnull(sal,0)*12+isnull(comm,0) as 年薪 from emp --扎西多杰
7，如何顯示工資高于3000的員工信息 select * from emp where sal>3000 --鄧文文 www.jb51.net 8，如何查找1982.1.1后入職的員工 select * from emp where hiredate>'1982-1-1'
9，如何顯示工資在2000到2500之間的員工情況 select * from emp where sal between 1244 and 100244 select * from emp where sal>=2000 and sal=250000
10，如何顯示首字母為S的員工姓名和工資(模糊查詢) select ename ,sal from emp where ename like 's%'
11，如何顯示第三個字母為‘O'的所有員工的姓名和工資（模糊查詢） select ename 姓名,sal as 月薪 from emp where ename like '__R%';
12，如何顯示empno為123,345,800...的員工情況(在where條件中使用in) select * from emp where empno in(123,345,800,1999,7400,7744);
13，如何顯示沒有上級的雇員的情況（使用is null 的操作符） select * from emp where mgr is null;
14,查詢工資高于1000或是崗位為manager的雇員， 同時還要滿足他們的姓名首字母為j（使用邏輯操作符） select * from emp where (sal>1000 or job='manager') and ename like 'j%';
16,如何按工資的從低到高的順序顯示雇員的信息 （使用order by字句asc， desc） select * from emp order by sal desc;
17,如何按入職的先后順序顯示雇員的信息（使用order by字句asc， desc） select * from emp order by hiredate desc;
18，按部門號升序而雇員的工資降序排列顯示雇員信息 （使用order by字句asc，desc） select * from emp order by deptno asc,sal desc;
19，統(tǒng)計每個人的年薪，并按從低到高的順序排序(使用列的別名排序) select isnull(sal,0)*12+isnull(comm,0) 年薪 from emp order by 年薪;
20,如何顯示員工中最高工資和最低工資 select max(sal) as "max(sal)",min(sal) from emp;
21，如何顯示最低工資的員工信息 select * from emp where sal = (select min(sal) from emp); www.jb51.net 22，顯示所有員工的平均工資和工資總和 select avg(sal) as "avg(sal)" , sum(sal) as "sum(sal)" from emp;
23，把高于平均工資的雇員的名字和他的工資顯示出來 select ename,sal from emp where sal>(select avg(sal) from emp);
24，計算共有多少名員工 select count(ename) from emp;
25，如何顯示每個部門的平均工資和最高工資 select avg(sal) as "avg",max(sal) as "max",deptno from emp group by deptno
26，如何顯示每個部門的高于總平均工資的平均工資和最高工資 select avg(sal) as "avg",max(sal) as "max",deptno from emp where sal>(select avg(sal) from emp) group by deptno
27顯示每個部門的每種崗位的平均工資和最低工資 select avg(sal),min(sal),deptno,job from emp group by deptno,job order by deptno
28，顯示平均工資低于2000的部門號和它的平均工資 （having往往和group by結合使用，可以對分組查詢結果進行篩選） select avg(sal),deptno from emp group by deptno having avg(sal)2000
29，使用企業(yè)管理器來操作數(shù)據(jù)庫的分離與附加 見操作步驟
30，使用企業(yè)管理器來操作數(shù)據(jù)庫的備份與恢復操作 見操作步驟
31，使用查詢分析器器來操作數(shù)據(jù)庫的備份與恢復操作 （1）備份數(shù)據(jù)庫 backup database mf2011 to disk='f:/lxliog.bak'; （2）刪除數(shù)據(jù)庫 drop database mf2011; （3）恢復數(shù)據(jù)庫 restore database mf2011 from disk='f:/lxliog.bak';
復雜查詢（多表查詢） 32,顯示sales部門的位置和其員工的姓名 select d.loc,e.ename from emp e,dept d where d.dname='sales' and d.deptno=e.deptno
33,顯示雇員名字，雇員工資及所在部門的名字(當字段有歧義的時候要用別名) select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno www.jb51.net 34，顯示部門號為10的部門名稱，及該部門的員工名字和工資 select d.dname, e.ename, e.sal from emp e,dept d where d.deptno=10 and d.deptno=e.deptno
35，顯示雇員名字，雇員工資及所在部門的名字，并按部門名稱排序 select e.ename, e.sal, d.dname from emp e,dept d where e.deptno=d.deptno order by d.dname
自連接：在同一張表的連接查詢 36,顯示某個員工的上級領導的姓名（比如：smith） （1）select mgr from emp where ename='adamc' （2）select ename from emp where empno=(select mgr from emp where ename='adamc')
37,顯示公司每個員工和他上級的名字 select worker.ename, boss.ename from emp worker, emp boss where worker.mgr=boss.empno
子查詢：嵌入在其它sql語句中的select語句，也叫嵌套查詢。 單行子查詢：只返回一行數(shù)據(jù)的子查詢語句。 38,顯示與jones同一部門的所有員工 (1)select deptno from emp where ename='jones' (2)select * from emp where deptno=(select deptno from emp where ename='jones')
多行子查詢：返回多行數(shù)據(jù)的子查詢。 39,查詢和部門號為10的工作相同的雇員的名字、崗位、工資、部門號。(注意要使用in，不能用=) (1)select distinct job from emp where deptno=20 (2)select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20);
40，在上面查詢結果的基礎上排除20部門的員工 select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20) and deptno not in (20); 或select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=20) and deptno >20;
在from字句中使用子查詢 41 ,顯示各個部門高于本部門平均工資的員工的信息 (1)先查出各個部門的平均工資 www.jb51.net select avg(sal),deptno from emp group by deptno (2)把上面的表當成一個臨時表來對待 select e.ename,e.sal,tem.myavg,e.deptno from emp e,(select avg(sal) myavg,deptno from emp group by deptno) tem where e.deptno=tem.deptno and e.sal>tem.myavg
分頁查詢：按雇員的hiredate屬性升序取出第5條到第10條記錄 42，顯示第5條到第10條記錄 （1）顯示第1到第4條記錄 select top 4 empno from emp order by hiredate （2）顯示后6條記錄（第5條到第10條記錄） select top 6 * from emp where empno not in(select top 4 empno from emp order by hiredate) order by hiredate;
43,顯示第11個到第13個入職的人的信息（寫法同上）
44，顯示第5到9的人的信息，按薪水的降序排列（寫法類似） select top 5 * from emp where empno not in (select top 4 empno from emp order by sal desc) order by sal desc; 45，同一張表中的數(shù)據(jù)復制 （1）創(chuàng)建一張臨時表：identity(1,1)表示該testId字段自增，從1開始每次+1 create table test( testId int primary key identity(1,1), testName varchar(30), testPass varchar(30) ) （2）插入一條數(shù)據(jù) insert into test(testName, testPass) values('zhangsan','123456'); （3）復制數(shù)據(jù) insert into test(testName, testPass) (select testName,testPass from test);
46，查詢testId為第10000-10009的數(shù)據(jù)，看看性能。 select top 10 * from test where testId not in (select top 9999 testId from test order by testId) order by testId
用查詢結果創(chuàng)建一張新表（一種快捷的建表方法） 47，語法：select *(這里可以選擇字段) into 另一張表面 from 表 select testName,testPass into mytest from test where testId8 表mytest在上述語句中已經(jīng)創(chuàng)建好了，并且初始化好了數(shù)據(jù) 并且把testId設置為主鍵：ALTER TABLE test01 ADD primary key(testId) www.jb51.net 48，刪除一張表中的重復數(shù)據(jù) （1）create table cat( catId int, catName varchar(40) ) （2）insert into cat values(1,'aa'); //重復執(zhí)行幾次 insert into cat values(2,'bb'); //重復執(zhí)行幾次 （3）select distinct * into #temp from cat;//把cat的記錄distinct后的結果，插入到臨時表#temp中 delete from cat;//把cat表的記錄清空 insert into cat select * from #temp;//把#temp表的數(shù)據(jù)（沒有重復的數(shù)據(jù)）插入到cat表中 drop table #temp;//刪除表#temp3
左外連接和右外連接 左外連接：左邊表的查詢數(shù)據(jù)全部顯示，右邊的表中如果沒有匹配的數(shù)據(jù)則用null填充 右外連接：右邊表的查詢數(shù)據(jù)全部顯示，左邊的表中如果沒有匹配的數(shù)據(jù)則用null填充 49，顯示emp表中所有雇員的及其上級的名字(看看區(qū)別) （1）左外連接：select e.ename 雇員名字,b.ename 上級名字 from emp e left join emp b on e.mgr=b.empno; （2）右外連接：select e.ename 雇員名字,b.ename 上級名字 from emp e right join emp b on e.mgr=b.empno;
