<pre>
--注意語句的讀寫順序是從右往左的
create database D1
drop table dept
drop table emp
create table dept (
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30))
insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON')
create table emp (
empno int primary key,
ename nvarchar(30),
job nvarchar(30),
mgr int,
hiredate datetime,
sal numeric(9,2),
comm numeric(9,2),
deptno int foreign key references dept(deptno))
--因?yàn)閐eptno根據(jù)需要得做成外鍵
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500.00,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);
--*全部查詢
select * from emp
select * from dept
select sal,job,deptno from emp where ename ='smith'
-----distinct消除相同的
select distinct deptno,ename from emp order by deptno
--顯示年工資
select ename, sal13+isnull(comm,0)13 年工資 from emp
--顯示1982-1-1之后入職
select ename from emp where hiredate>'1982-1-1'
--顯示首字母為s的工資，姓名
select ename,sal from emp where ename like 's%'--%表示多個(gè)字符 --_a表示單個(gè)字符
-----in
select ename,sal from emp where empno in(123,800,325)
--顯示沒有上級(jí)的
select ename,sal from emp where mgr is null
--查詢工資高于500或者崗位是manage 同時(shí)滿足首個(gè)姓名字母是j
select ename,sal from emp where (sal>500 or job='MANAGER')and ename like 'j%'
--工資從低到高，order by（低到高/先到后asc）,高到低/后到先desc
select * from emp order by sal desc
--按照部門號(hào)升序而雇員工資降序
select from emp order by deptno,sal desc
--年薪從低到高
select ename,(sal13+isnull(comm,0)13) 年薪 from emp order by (sal13+isnull(comm,0)*13)
--min(),max,avg 平均,sum,count函數(shù)
--顯示最低工資
--顯示最低工資和他的名字
--select ename, min(sal) from emp 錯(cuò)誤的
--select ename,(select min(sal) from emp) from emp 錯(cuò)誤的，我的，顯示出來不對(duì)應(yīng)
select ename,sal from emp where sal=(select min(sal) from emp)--子列表
--平均工資 總工資
select avg(sal) 平均工資 ,sum(sal) 總工資 from emp
--高于平均工資的雇員工資和名字顯示--select ename,sal from emp where sal=(select sal>avg(sal) from emp) 錯(cuò)誤的,因?yàn)閟al>avg(sal)自己和自己的平均比較錯(cuò)誤。
select ename,sal from emp where sal>(select avg(sal) from emp)--這里是括號(hào)外和里面比較所以不一樣
--分組group by分組 having限制結(jié)果
--顯示部門的平均和最高工資(并顯示部門名稱涉及到另一張表，多表查詢)
select avg(sal) 平均工資 ,max(sal) 最高工資,deptno from emp group by deptno
--沒個(gè)部門每種崗位的平均和最高工資
select avg(sal) 平均工資 ,max(sal) 最高工資,deptno,job from emp group by deptno,job --order by deptno
--平均工資低于2000的部門和他的平均工資
--having和group by 結(jié)合,可以對(duì)分組之后的結(jié)果進(jìn)行篩選拋棄
select avg(sal) 平均工資 ,deptno from emp group by deptno having avg(sal)<2000
-----------------多表查詢
select from emp,dept--顯示笛卡爾集144,所以匹配
--顯示雇員名雇員工資及所在部門及部門號(hào)(emp.deptno)
--emp.deptno=dept.deptno這個(gè)是關(guān)鍵的KEY把他們關(guān)聯(lián)起來
select ename,sal,loc,emp.deptno from emp,dept where dept.dname='sales'and emp.deptno=dept.deptno
--顯示部門10，部門名，員工名和工資
select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10
-------------------自連接（內(nèi)連接）
--顯示某個(gè)員工的上級(jí)1.知道福德上級(jí)編號(hào)
select mgr from emp where ename='ford'
select ename from emp where empno=(select mgr from emp where ename='ford')
--顯示每個(gè)員工的姓名和他上級(jí)的名字,把emp表看做兩張表利用別名worker,boss，讓自己與自己關(guān)聯(lián)
----------select ename, mgr from emp where (select mgr from emp )
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno
------------------左外連接，右外連接
-----------把沒有老板的也顯示出來？--外連接（左，右）
--左外連接：把左邊表的記錄全部顯示，如果沒有匹配的記錄就用null
select worker.ename,boss.ename 老板 from emp worker left join emp boss on worker.mgr=boss.empno
--右外連接：把右邊表的記錄全部顯示，如果沒有匹配的記錄就用null
select worker.ename,boss.ename 老板 from emp worker right join emp boss on worker.mgr=boss.empno
--總結(jié)：1.自連接，除上述也可以 from 表1 inner join 表2 on(表1自己加入到表2，上述where 該為on)
--------2.左連接，from 表1 left join 表2 on(表1自己左加入到表2)，把左邊表的記錄全部顯示，如果沒有匹配的記錄就用null
--------3.右連接，from 表1 right join 表2 on(表1自己右加入到表2)，把右邊表的記錄全部顯示，如果沒有匹配的記錄就用null
--------子查詢，嵌入在其他sql語句的select語句
--單行子查詢，顯示與SMITH同一部門的所有員工
select * from emp where deptno=(select deptno from emp where ename='smith')
---------多行子查詢,返回多行子數(shù)據(jù)的查詢
--查詢部門10的工作相同的雇員名字，崗位，工資，部門號(hào)多行返回需要用到in，單行可以用=
select * from emp where job in(
select distinct job from emp where deptno=10 )
--排除10部門本身
select * from emp where job in(
select distinct job from emp where deptno=10 ) and(deptno !=10)
--------from 中使用子查詢
--顯示高于部門平均工資的員工的信息
--1.各個(gè)部門平均工資
select avg(sal),deptno from emp group by deptno
--2.把上面的當(dāng)做臨時(shí)表 select * from emp兩張表關(guān)聯(lián)
select emp.ename ,sal,tem.myavg from emp,(select avg(sal) myavg,deptno from emp group by deptno) tem
where emp.deptno=tem.deptno and emp.sal>tem.myavg
--------分葉查詢
--第5個(gè)到第10入職的（按照時(shí)間順序）
--1.顯示第一個(gè)到第四個(gè)入職的雇員
select top 4*from emp order by hiredate
--top :取出幾條
--排除前4個(gè)在取6個(gè)即就是5-10
select 6 *from from emp where emptno not in
(select top 4 emptno from emp order by hiredate)
order by hiredate
-----------------------identity(1,1)從1，每次+1自增長(zhǎng)
create table test(
testID int primary key identity(1,1),
testName varchar(30),testPass varchar(30))
insert into test (testName,testPass )values('shunping','shunping')
insert into test (testName,testPass )select testName,testPass from test
-------------------刪除一張重復(fù)的記錄
create table cat(
catId int,catName varchar(40))
insert into cat values(1,'aa')
insert into cat values(2,'bb')
select *from cat
--1.把cat的記錄distinct后的結(jié)果放在臨時(shí)表里面去，也就是沒有重復(fù)的記錄
select distinct * into #temp from cat
--清空cat表
delete *from cat
--把temp的數(shù)據(jù)（沒有重復(fù)的記錄），在插入到cat表去，最后刪除臨時(shí)表
insert into cat select *from #temp
drop table #temp
-----------------------#temp臨時(shí)表
--約束unique,primary key 都是唯一但是unique開業(yè)為null，但只能唯一一個(gè)，primary key有復(fù)合主鍵primary key（test1Id，testname）
--一張表只能一個(gè)主鍵但是能多個(gè)外鍵
create table test1(
test1Id int primary key identity(1,1),
testname varchar(30) not null,test varchar(30) not null,
testage int)
--check（testage>100） 約束范圍只能選大于100，default默認(rèn)約束
--備份數(shù)據(jù)庫(kù)
backup database D1 to disk='f:/sp.bak'
--刪除數(shù)據(jù)庫(kù)
drop database D1
--恢復(fù)數(shù)據(jù)庫(kù)
restore database D1 from disk='f:/sp.bak'
--備份表
----修改---把工資低于100提高10% update 表名 set 字段名1=，字段名2= where 條件
----刪除---delete from 表名 where 條件
---nchar unicode 一字符 一漢字 一英文
---char 非unicode 二字符 一漢字 二英文
----定義DDL 操作DML 查詢DQL 控制DCL
---DATEDIFF(YEAR,STARTDATE,ENDDATE),LEN(),UPPER(),LOWER(),SUBSTRING(ENAME,1,1)
--------練習(xí)
--1.列出至少一個(gè)員工的所有部門
--得到每個(gè)部門多少員工
select count(),deptno from emp group by deptno
select count(),deptno from emp group by deptno having count(*)>1
--2.列出薪金比smith多的所有員工
--先找出他薪水
select *from emp where sal>(select sal from emp where ename='smith')and ename<>'smith'
--3.列出所有員工的姓名及其上級(jí)的姓名
select w.ename b.ename from emp w,emp b where w.mgr=b.empno
--4.受雇日期晚于其直接上級(jí)的所有員工
--找出上級(jí)的時(shí)間，和下級(jí)的時(shí)間
select w.ename from emp w,emp b where w.mgr=b.empno and b.hiredate<w.hiredate
--5.部門名稱和這些部門的員工信息，同時(shí)那些沒有員工的部門
select d.dname, e.ename, e.job from emp e right join dept d on e.deptno=d.deptno
--6."CLERK"的姓名及部門名稱
--7.最低薪金大于1500的各種工作
--每個(gè)工作是最低工資
select min(sal),job from emp group by job having min(sal)>1500
--8.列出在sale部的工作的員工的姓名
select ename,'sales' from emp where deptno=(select deptno from dept where dname='sales')
--9.薪金高于公司的平均工資的員工
select from emp where sal>(select avg(sal)from emp)
--10.列出于“scott”從事相同工作的員工
select ename from emp where job=(select job from emp where ename='scott')
--11.列出薪金等于部門30中的員工的薪金的所有員工的姓名和薪金
select ename,sal from emp where sal in(select sal from emp where deptno=30)
--12薪金高于在部門30工作的所有員工的薪金的員工姓名和薪金
select ename,sal from emp where sal >(select max(sal) from emp where deptno=30)
--13.每個(gè)部門工作的員工數(shù)量，平均工資，平均服務(wù)期限
select count(),avg(sal),avg(datediff(year,hiredate,getdate())),deptno from emp group by deptno
--14 給出不同部門相同職業(yè)的一種組合
select distinct job,deptno from emp where job='clerk'
--所有部門的詳細(xì)信息和人數(shù)
select d2.dname,d2.loc,isnull(d.c,0) from dept d2 left join
(select count(*) c,deptno de from emp group by deptno) d
on d2.deptno=d.de
