MSSQL 基本語法及實例操作語句教程

這是一篇有關MSSQL 基本語法及實例操作語句的文章，有需要的朋友可以參考下。

MS SQL基本語法及實例操作

一：建表並初始化

============================

create database mf2011 --創建數據庫

use mf2011 --使用數據庫

create table dept --創建“部門”表

( www.jb51.net

deptno int primary key, --部門編號(主鍵)

dname nvarchar(30), --部門名稱

loc nvarchar(30) --部門所在的地點

)

-----

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 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

--主鍵：唯一，非空

--外鍵：只能指向主鍵，與指向的主鍵數據類型必須一致

二：練習

============================

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，統計總共有都少個部門(兩種方式：從emp或從dept)

select count(deptno) from dept --諾布才仁

select count(distinct(deptno)) as 部門總數 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，統計每個人的年薪，並按從低到高的順序排序(使用列的別名排序)

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，使用企業管理器來操作數據庫的分離與附加

見操作步驟

30，使用企業管理器來操作數據庫的備份與恢復操作

見操作步驟

31，使用查詢分析器器來操作數據庫的備份與恢復操作

(1)備份數據庫

backup database mf2011 to disk='f:/lxliog.bak';

(2)刪除數據庫

drop database mf2011;

(3)恢復數據庫

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