MS SQL基本語法及實(shí)例操作

一：建表并初始化
============================
create database mf2011 --創(chuàng)建數(shù)據(jù)庫

use mf2011 --使用數(shù)據(jù)庫

create table dept --創(chuàng)建“部門”表
( www.dbjr.com.cn
deptno int primary key, --部門編號(hào)（主鍵）
dname nvarchar(30), --部門名稱
loc nvarchar(30) --部門所在的地點(diǎn)
)
-----

create table emp --創(chuàng)建“員工”表
(
empno int primary key, --員工編號(hào)（主鍵）
ename nvarchar(30), --員工的姓名
job nvarchar(30), --員工的工作類型
mgr int, --員工的上級(jí)
hiredate datetime, --員工的入職時(shí)間（受聘時(shí)間）
sal numeric(10,2), --員工的每月工資
comm numeric(10,2), --員工的年終獎(jiǎng)金
deptno int foreign key references dept(deptno) --創(chuàng)建一個(gè)外鍵指向部門表（描述該員工屬于哪個(gè)部門）
)

--------
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.dbjr.com.cn
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ù)類型必須一致

二：練習(xí)
============================
1，查詢emp表所有列
select * from emp --劉君正

2，查詢指定列（例如：姓名，工資兩列）
select ename,sal from emp --鄭朝陽

3，取消重復(fù)行（從emp表中查詢部門號(hào)）
select distinct(deptno) from emp --徐紹峰,郝艷芳,劉輝,馬東勤

4，從emp表中查詢名字為lxliog員工的薪水，工作，所作部門
select sal,job,deptno from emp where 'lxliog'=ename --侯耀文,譚雪玲,黎小龍

5，統(tǒng)計(jì)總共有都少個(gè)部門(兩種方式：從emp或從dept)
select count(deptno) from dept --諾布才仁
select count(distinct(deptno)) as 部門總數(shù) from emp --諾布才仁

use mf2011
select * from dept;
select * from emp;
--6，顯示每個(gè)雇員的年工資
select isnull(sal,0)*12+isnull(comm,0) as 年薪 from emp --扎西多杰

7，如何顯示工資高于3000的員工信息
select * from emp where sal>3000 --鄧文文
www.dbjr.com.cn
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，如何顯示第三個(gè)字母為‘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，如何顯示沒有上級(jí)的雇員的情況（使用is null 的操作符）
select * from emp where mgr is null;

14,查詢工資高于1000或是崗位為manager的雇員，
同時(shí)還要滿足他們的姓名首字母為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，按部門號(hào)升序而雇員的工資降序排列顯示雇員信息
（使用order by字句asc，desc）
select * from emp order by deptno asc,sal desc;

19，統(tǒng)計(jì)每個(gè)人的年薪，并按從低到高的順序排序(使用列的別名排序)
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.dbjr.com.cn
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，計(jì)算共有多少名員工
select count(ename) from emp;

25，如何顯示每個(gè)部門的平均工資和最高工資
select avg(sal) as "avg",max(sal) as "max",deptno
from emp group by deptno

26，如何顯示每個(gè)部門的高于總平均工資的平均工資和最高工資
select avg(sal) as "avg",max(sal) as "max",deptno
from emp where sal>(select avg(sal) from emp) group by deptno

27顯示每個(gè)部門的每種崗位的平均工資和最低工資
select avg(sal),min(sal),deptno,job from emp group by deptno,job
order by deptno

28，顯示平均工資低于2000的部門號(hào)和它的平均工資
（having往往和group by結(jié)合使用，可以對(duì)分組查詢結(jié)果進(jìn)行篩選）
select avg(sal),deptno from emp group by deptno having avg(sal)<2000

29，使用企業(yè)管理器來操作數(shù)據(jù)庫的分離與附加
見操作步驟

30，使用企業(yè)管理器來操作數(shù)據(jù)庫的備份與恢復(fù)操作
見操作步驟

31，使用查詢分析器器來操作數(shù)據(jù)庫的備份與恢復(fù)操作
（1）備份數(shù)據(jù)庫
backup database mf2011 to disk='f:/lxliog.bak';
（2）刪除數(shù)據(jù)庫
drop database mf2011;
（3）恢復(fù)數(shù)據(jù)庫
restore database mf2011 from disk='f:/lxliog.bak';

復(fù)雜查詢（多表查詢）
32,顯示sales部門的位置和其員工的姓名
select d.loc,e.ename from emp e,dept d where d.dname='sales' and d.deptno=e.deptno

33,顯示雇員名字，雇員工資及所在部門的名字(當(dāng)字段有歧義的時(shí)候要用別名)
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno
www.dbjr.com.cn
34，顯示部門號(hào)為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,顯示某個(gè)員工的上級(jí)領(lǐng)導(dǎo)的姓名（比如：smith）
（1）select mgr from emp where ename='adamc'
（2）select ename from emp where empno=(select mgr from emp where ename='adamc')

37,顯示公司每個(gè)員工和他上級(jí)的名字
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,查詢和部門號(hào)為10的工作相同的雇員的名字、崗位、工資、部門號(hào)。(注意要使用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，在上面查詢結(jié)果的基礎(chǔ)上排除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 ,顯示各個(gè)部門高于本部門平均工資的員工的信息
(1)先查出各個(gè)部門的平均工資 www.dbjr.com.cn
select avg(sal),deptno from emp group by deptno
(2)把上面的表當(dāng)成一個(gè)臨時(shí)表來對(duì)待
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）當(dāng)在from字句中使用子查詢時(shí)，該子查詢會(huì)被當(dāng)做一個(gè)臨時(shí)表來對(duì)待，
（2）當(dāng)在from字句中使用子查詢時(shí)，必須給子查詢指定一個(gè)別名

分頁查詢：按雇員的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個(gè)到第13個(gè)入職的人的信息（寫法同上）

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ù)復(fù)制
（1）創(chuàng)建一張臨時(shí)表：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）復(fù)制數(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

用查詢結(jié)果創(chuàng)建一張新表（一種快捷的建表方法）
47，語法：select *(這里可以選擇字段) into 另一張表面 from 表
select testName,testPass into mytest from test where testId<8
表mytest在上述語句中已經(jīng)創(chuàng)建好了，并且初始化好了數(shù)據(jù)
并且把testId設(shè)置為主鍵：ALTER TABLE test01 ADD primary key(testId)
www.dbjr.com.cn
48，刪除一張表中的重復(fù)數(shù)據(jù)
（1）create table cat(
catId int,
catName varchar(40)
)
（2）insert into cat values(1,'aa'); //重復(fù)執(zhí)行幾次
insert into cat values(2,'bb'); //重復(fù)執(zhí)行幾次
（3）select distinct * into #temp from cat;//把cat的記錄distinct后的結(jié)果，插入到臨時(shí)表#temp中
delete from cat;//把cat表的記錄清空
insert into cat select * from #temp;//把#temp表的數(shù)據(jù)（沒有重復(fù)的數(shù)據(jù)）插入到cat表中
drop table #temp;//刪除表#temp3

左外連接和右外連接
左外連接：左邊表的查詢數(shù)據(jù)全部顯示，右邊的表中如果沒有匹配的數(shù)據(jù)則用null填充
右外連接：右邊表的查詢數(shù)據(jù)全部顯示，左邊的表中如果沒有匹配的數(shù)據(jù)則用null填充
49，顯示emp表中所有雇員的及其上級(jí)的名字(看看區(qū)別)
（1）左外連接：select e.ename 雇員名字,b.ename 上級(jí)名字
from emp e left join emp b on e.mgr=b.empno;
（2）右外連接：select e.ename 雇員名字,b.ename 上級(jí)名字
from emp e right join emp b on e.mgr=b.empno;

常見約束：
(1)not null, 非空
(2)unique, 唯一，允許出現(xiàn)一個(gè)null
(3)primary key, 主鍵，唯一，非空
(4)foreign key, 外鍵,定義主表和從表的關(guān)聯(lián)關(guān)系
(5)check，檢查，強(qiáng)制數(shù)據(jù)必須滿足定義的條件,例如：sal int check(sal>=2000 and sal<=3000)
(6)default, 默認(rèn)值，用于數(shù)據(jù)的完整性，例如：birthday datetime default getdate(),

50，復(fù)合主鍵只能用表級(jí)定義
例如：create table cat(
catId int,
catName varchar(40)，
catAge int,
primary key(catId, catName)
)
www.dbjr.com.cn
51，商品售貨系統(tǒng)表設(shè)計(jì)案例：現(xiàn)有一個(gè)商店數(shù)據(jù)庫，記錄客戶及其購物情況，由下面三個(gè)表組成：商品（goods），客戶（customer），購買（purchase）
商品goods(商品號(hào)goodsId, 商品名稱goodsName, 單價(jià)unitPrice, 商品類別category, 供應(yīng)商provider);
客戶customer(客戶號(hào)customerId, 姓名name, 住址address, 電郵email, 性別sex, 身份證cardId);
購買purchase(客戶號(hào)customerId, 商品號(hào) goodsId, 購買數(shù)量nums);
（1）建表，在定義中要求申明：
ü 每個(gè)表的主鍵和外鍵；
ü 客戶的姓名不能為空；
ü 單價(jià)必須大于0，購買數(shù)量必須在1到10之間
ü 電郵不能夠重復(fù)
ü 客戶的性別必須是：男或女，默認(rèn)是男
ü 商品的類別是：食物，日用品

----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.dbjr.com.cn
----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

最新評(píng)論