当前位置: 代码迷 >> SQL >> MS SQL根本语法及实例操作
  详细解决方案

MS SQL根本语法及实例操作

热度:230   发布时间:2016-05-05 15:24:43.0
MS SQL基本语法及实例操作

一:建表并初始化

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

create database mf2011        --创建数据库

 

use mf2011  --使用数据库

 

create table dept   --创建“部门”表

(

  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)

 

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

 

8,如何查找1982.1.1后入职的员工

select * from emp where hiredate>'1982-1-1'

 

9,如何显示工资在20002500之间的员工情况

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,如何显示empno123,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字句ascdesc

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

 

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

 

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

    1select mgr from emp where ename='adamc'

    2select 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)先查出各个部门的平均工资

     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,显示第59的人的信息,按薪水的降序排列(写法类似)

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)

 

48,删除一张表中的重复数据

    1create table cat(

              catId     int,

              catName varchar(40)

   )

    2insert into cat values(1,'aa'); //重复执行几次

        insert into cat values(2,'bb'); //重复执行几次

    3select 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)

   )

 

51,商品售货系统表设计案例:现有一个商店数据库,记录客户及其购物情况,由下面三个表组成:商品(goods),客户(customer),购买(purchase

商品goods(商品号goodsId, 商品名称goodsName,  单价unitPrice,商品类别category, 供应商provider);

客户customer(客户号customerId, 姓名name,  住址address, 电邮email,  性别sex,身份证cardId);

购买purchase(客户号customerId, 商品号 goodsId,  购买数量nums);

1)建表,在定义中要求申明:

ü  每个表的主键和外键;

ü  客户的姓名不能为空;

ü  单价必须大于0,购买数量必须在110之间

ü  电邮不能够重复

ü  客户的性别必须是:男或女,默认是男

ü  商品的类别是:食物,日用品

 

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

)

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

)

  相关解决方案