一：建表并初始化
============================
create database mf2011 --創(chuàng)建數(shù)據(jù)庫 use mf2011 --使用數(shù)據(jù)庫 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ù)類型必須一致 二：練習(xí)
============================
1，查詢emp表所有列
select * from emp --劉君正 2，查詢指定列（例如：姓名，工資兩列）
select ename,sal from emp --鄭朝陽 3，取消重復(fù)行（從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結(jié)合使用，可以對分組查詢結(jié)果進行篩選）
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)字段有歧義的時候要用別名)
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,顯示某個員工的上級領(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,顯示公司每個員工和他上級的名字
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，在上面查詢結(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 ,顯示各個部門高于本部門平均工資的員工的信息
(1)先查出各個部門的平均工資 www.jb51.net
select avg(sal),deptno from emp group by deptno
(2)把上面的表當(dāng)成一個臨時表來對待
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字句中使用子查詢時，該子查詢會被當(dāng)做一個臨時表來對待，
（2）當(dāng)在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，同一張表中的數(shù)據(jù)復(fù)制
（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）復(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.jb51.net
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é)果，插入到臨時表#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表中所有雇員的及其上級的名字(看看區(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; 常見約束：
(1)not null, 非空
(2)unique, 唯一，允許出現(xiàn)一個null
(3)primary key, 主鍵，唯一，非空
(4)foreign key, 外鍵,定義主表和從表的關(guān)聯(lián)關(guān)系
(5)check，檢查，強制數(shù)據(jù)必須滿足定義的條件,例如：sal int check(sal>=2000 and sal<=3000)
(6)default, 默認(rèn)值，用于數(shù)據(jù)的完整性，例如：birthday datetime default getdate(), 50，復(fù)合主鍵只能用表級定義
例如：create table cat(
catId int,
catName varchar(40)，
catAge int,
primary key(catId, catName)
)
www.jb51.net
51，商品售貨系統(tǒng)表設(shè)計案例：現(xiàn)有一個商店數(shù)據(jù)庫，記錄客戶及其購物情況，由下面三個表組成：商品（goods），客戶（customer），購買（purchase）
商品goods(商品號goodsId, 商品名稱goodsName, 單價unitPrice, 商品類別category, 供應(yīng)商provider);
客戶customer(客戶號customerId, 姓名name, 住址address, 電郵email, 性別sex, 身份證cardId);
購買purchase(客戶號customerId, 商品號 goodsId, 購買數(shù)量nums);
（1）建表，在定義中要求申明：
ü 每個表的主鍵和外鍵；
ü 客戶的姓名不能為空；
ü 單價必須大于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.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

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。