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 where ename='adamc')
37,顯示公司每個員工和他上級的名字
select worker.ename, boss.ename from emp worker, emp boss where worker.mgr=boss.empno
子查詢：嵌入在其它sql語句中的select語句，也叫嵌套查詢。
單行子查詢：只返回一行數據的子查詢語句。
38,顯示與jones同一部門的所有員工
(1)select deptno from emp where ename='jones'
(2)select * from emp where deptno=(select deptno from emp where ename='jones')
多行子查詢：返回多行數據的子查詢。
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
注意：（1）當在from字句中使用子查詢時，該子查詢會被當做一個臨時表來對待，
（2）當在from字句中使用子查詢時，必須給子查詢指定一個別名
分頁查詢：按雇員的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，同一張表中的數據復制
（1）創建一張臨時表：identity(1,1)表示該testId字段自增，從1開始每次+1
create table test(
testId int primary key identity(1,1),
testName varchar(30),
testPass varchar(30)
)
（2）插入一條數據
insert into test(testName, testPass) values('zhangsan','123456');
（3）復制數據
insert into test(testName, testPass) (select testName,testPass from test);
46，查詢testId為第10000-10009的數據，看看性能。
select top 10 * from test where testId not in
(select top 9999 testId from test order by testId)
order by testId
用查詢結果創建一張新表（一種快捷的建表方法）
47，語法：select *(這裡可以選擇字段) into 另一張表面 from 表
select testName,testPass into mytest from test where testId<8
表mytest在上述語句中已經創建好了，並且初始化好了數據
並且把testId設置為主鍵：ALTER TABLE test01 ADD primary key(testId)
www.jb51.net
48，刪除一張表中的重復數據
（1）create table cat(
catId int,
catName varchar(40)
)
（2）insert into cat values(1,'aa'); //重復執行幾次
insert into cat values(2,'bb'); //重復執行幾次
（3）select distinct * into #temp from cat;//把cat的記錄distinct後的結果，插入到臨時表#temp中
delete from cat;//把cat表的記錄清空
insert into cat select * from #temp;//把#temp表的數據（沒有重復的數據）插入到cat表中
drop table #temp;//刪除表#temp3
左外連接和右外連接
左外連接：左邊表的查詢數據全部顯示，右邊的表中如果沒有匹配的數據則用null填充
右外連接：右邊表的查詢數據全部顯示，左邊的表中如果沒有匹配的數據則用null填充
49，顯示emp表中所有雇員的及其上級的名字(看看區別)
（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;
常見約束：
(1)not null, 非空
(2)unique, 唯一，允許出現一個null
(3)primary key, 主鍵，唯一，非空
(4)foreign key, 外鍵,定義主表和從表的關聯關系
(5)check，檢查，強制數據必須滿足定義的條件,例如：sal int check(sal>=2000 and sal<=3000)
(6)default, 默認值，用於數據的完整性，例如：birthday datetime default getdate(),
50，復合主鍵只能用表級定義
例如：create table cat(
catId int,
catName varchar(40)，
catAge int,
primary key(catId, catName)
)
www.jb51.net
51，商品售貨系統表設計案例：現有一個商店數據庫，記錄客戶及其購物情況，由下面三個表組成：商品（goods），客戶（customer），購買（purchase）
商品goods(商品號goodsId, 商品名稱goodsName, 單價unitPrice, 商品類別category, 供應商provider);
客戶customer(客戶號customerId, 姓名name, 住址address, 電郵email, 性別sex, 身份證cardId);
購買purchase(客戶號customerId, 商品號 goodsId, 購買數量nums);
（1）建表，在定義中要求申明：
ü 每個表的主鍵和外鍵；
ü 客戶的姓名不能為空；
ü 單價必須大於0，購買數量必須在1到10之間
ü 電郵不能夠重復
ü 客戶的性別必須是：男或女，默認是男
ü 商品的類別是：食物，日用品
----goods表
Create table goods(
goodsid nvarchar(50) primary key,
goodsnamd nvarchar(80) not null,
unitPrice numeric(10,2) check (unitPrice>0)
category nvarchar(3) check (category in (‘食物','日用品')),
provider nvarchar(50)
)
----customer
Create table customer(
Customerid nvarchar(50) primary key,
Cusname nvarchar(50) not null,
Address nvarchar(100),
Email nvarchar(50) unique,
Sex nchar(1) check(sex in(‘男','女')) default ‘男',
Cardid nvarchar(18)
) www.jb51.net
----purchase
Create table purchase(
Customerid nvarchar(50) foreign key references customer(Customerid),
goodsid nvarchar(50) foreign key references goods(goodsid),
nums int check(nums>0 and nums<10),
primary key(customerid, goodsid)
)
作者 qq395740774
