sql server查询(SELECT ,where,distinct,like 查询,in,is null,group by 和having,order by,as)

基本查询:

实例表

1 示例表 2 --部门表 3  4 create table dept( 5  6    deptno int primary key,--部门编号 7  8    dname nvarchar(30),--部门名 9 10    loc nvarchar(30)--地址11 12 );13 14  15 16 --雇员表17 18 create table emp(19 20    empno int primary key,--雇员号21 22    ename nvarchar(30),--员工姓名23 24    job   nvarchar(30),--雇员工作25 26    mrg int,--雇员上级27 28    hiredate datetime,--入职时间29 30    sal numeric(10,2),--薪水31 32    comm numeric(10,2),--奖金33 34    deptno int foreign key references dept(deptno)--设置外键35 36 );37 38  39 40 insert into dept values (10,'ACCOUNTING','NEW YORK');41 42 insert into dept values (20,'RESEARCH','DALLAS');43 44 insert into dept values (30 ,'SALES','CHICAGO');45 46 insert into dept values (40, 'OPERATIONS','BOSTON');47 48  49 50 insert into emp values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,null,20);51 52 insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30);53 54 insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30);55 56 insert into emp values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,null,20);57 58 insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30);59 60 insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,null,30);61 62 insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,null,10);63 64 insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000.00,null,20);65 66 insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000.00,null,10);67 68 insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30);69 70 insert into emp values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,null,20);71 72 insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-3',950.00,null,30);73 74 insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3000.00,null,20);75 76 insert into emp values(7934,'MILLER','CLERK',7782,'1982-1-23',1300.00,null,10);

View Code

1 select 2 基本select语句 3  4 select [distinct] *|{列名1, 列名2,列名3...}  5  6 from  表名 [where (条件)]; 7  8   9 10 说明:11 12 select 指定查询哪些列的数据。13 14 *号代表查询所有列。15 16 from指定查询哪张表。17 18 where 表示条件。19 20 distinct可选,指显示结果时,是否剔除重复数据21 22  23 24 简单的查询语句25 26  27 28 ■ 查询所有列29 30 select * from 表名;31 32 ■ 查询指定列33 34 select 列1,列2… from 表名;35 36 select ename,sal from emp;37 38 ■ 如何取消重复行39 40 select distinct deptno ,job from emp;41 42 ?查询SMITH 的薪水,工作,所在部门43 44 select sal,job,deptno from emp where ename='SMITH';45 46 特别说明:SQLServer 的sql 不区分大小写,内容也不区分大小写47 48  49 50 ■ 使用算数表达式51 52 ?显示每个雇员的年工资(奖金为null的用0代替)53 54 select ename,sal*12+isnull(comm,0)*12 as '年薪' from emp;55 56 说明:isnull函数是 SQLserver提供的,用于处理数据 null 的问题57 58  59 60 ■ 使用列的别名61 62 select ename '姓名',sal*12 as '年收入' from emp;63 64  65 66 使用别名时,as 可省略,别名可用单引号,双引号或者不加67 68  69 70 查询工资在 2000 到 5000 的员工情况71 72 ①,     select * from emp where sal>=2000 and sal<=3000;73 74 ②,     select * from emp where sal between 2000 and 3000;

where

使用where子句如何显示工资高于的员工select ename from emp where sal>3000; 如何显示工资在到的员工情况select * from emp where sal>=2000 and sal<=250; select * from emp where sal between 2000 and 2500;(闭区间)

distinct

distinct可选,指显示结果时,是否剔除重复数据 select [distinct] * from emp;

like 查询

%: 表示任意0到多个字符 _: 表示任意单个字符不区分大小写如何显示首字符为S的员工姓名和工资select ename,sal from emp where ename like 'S%'; 如何显示第三个字符为O的所有员工的姓名和工资select ename,sal from emp where ename like '__O%'; 查询首字母不是 S 的雇员信息select ename,job from emp where ename not like 'S%';

in

如何显示empno为123,345,800...的雇员情况 ①,select * from emp where empno=123 or empno=345 or empno=800; ②,select * from emp where empno in (123,456,800);一般我们使用 in 这个关键字,这样效率更高

is null

如何显示没有上级的雇员的情况select * from emp where mgr is null;

group by 和having(重点)

1 group by用于对查询的结果分组统计,默认升序 2  3 having子句用于限制分组显示结果. 4  5   6  7 使用order by字句 8  9 如何按照工资的从低到高的顺序显示雇员的信息10 11 select ename,sal from emp order by sal asc;12 13 按照部门号升序而雇员的工资降序排列14 15 select * from emp order by deptno asc,sal desc;16 17 按照入职先后顺序排序18 19 select ename,hiredate from emp order by hiredate asc;20 21 ■使用列的别名排序22 23 select ename,sal*12 '年薪' from emp order by '年薪' asc;24 25 别名需要使用''号圈中,也可以不在''号圈中。26 27  28 29 如何显示每个部门的平均工资和最高工资30 31 select avg(sal),max(sal),deptno from emp group by deptno;32 33  34 35 显示每个部门的每种岗位的平均工资和最低工资36 37 select avg(sal),max(sal),deptno,job from emp group by deptno,job order by deptno;38 39  40 41 显示部门平均工资低于2000的部门号和它的平均工资42 43 思路:   44 45 1,查询出各个部门的平均工资46 47 select avg(sal),deptno from emp group by deptno;48 49  50 51 2,挑选出低于的52 53 select avg(sal),deptno from emp group by deptno having avg(sal)<2000;54 55  56 57  58 59 对数据分组的总结60 61 1  分组函数只能出现在选择列表、having、order by子句种62 63 2 如果在select 语句种同时包含有group by ,having ,64 65 order by 那么他们的顺序是group by , having , order by66 67 3 在选择列中,如果有列、表达式、和分组函数,那么这些列和68 69 表达式必须有一个出现在group by 子句中,否则就会出错70 71 select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;72 73 这里deptno就一定要出现在 group by 中

order by

1 order by 子句用于排序结果集。 2  3 asc表示升序 4  5 desc表示降序 6  7   8  9 如何按照工资的从低到高的顺序显示雇员的信息10 11 select * from emp order by sal [asc];12 13  14 15 查询学生信息,按照学号升序排列16 17 select * from student order by sno;18 19 查询学生信息,按照年龄降序排列20 21 select * from student order by sage desc;22 23 查询学生信息,按照年龄降序排列,如果年龄相等再按照学号升序排列24 25 select * from student order by sage desc,sno asc;

使用别名

1 1)   select ename,sal '薪水' from emp; 2  3 2)   select ename,sal "薪水" from emp; 4  5 3)   select ename,sal 薪水from emp; 6  7 4)   select ename,sal as '薪水' from emp; 8  9 5)   select ename,sal as "薪水" from emp;10 11 6)   select ename,sal as 薪水from emp;12 13  14 15 ■使用列的别名排序16 17 select ename,sal*12 '年薪' from emp order by '年薪' asc;
(0)

相关推荐