SELECT语句的基本使用（单表查询）
1) 查询每个雇员的姓名、地址和电话。
001
USE yggl
SELECT name,address,phonenumber
FROM employees
2) 查询员工编号为“00001”的姓名与地址。
002
SELECT name,address
FROM employees
where employeeid=00001
3) 查询女雇员的姓名与电话，并加标题。（三种方法）
select name as 姓名,phonenumber as 年龄
from employees
where sex='False'
select name 姓名,phonenumber 电话
from employees
where sex='False'
select 姓名=name,电话=phonenumber
from employees
where sex='False'
SELECT name,phonenumber
FROM employees
where sex=0
SELECT name 姓名 , phonenumber 电话
FROM employees
where  sex=0
4) 用表employees查询员工来自几个不同的部门.(distinct)
SELECT distinct departmentid
FROM employees
5) 显示每个员工的编号与实际收入。
SELECT employeeid ,income-outcome 实际收入
FROM salary 
6) 出所有姓王的员工的部门编号。(别忘了消除查询结果重复的行)（三种方法）
select distinct departmentid
from employees
where LEFT(name,1)='王'
select distinct departmentid
from employees
where name like'王%'
select distinct departmentid
from employees
where SUBSTRING(name,1,1)='王'
7) 出收入在2000~3456之间的员工的编号。(两种方法表示)
select employeeid
from salary
where outcome between 2000 and 3456
select employeeid
from salary
where outcome>=2000 and outcome<=3456
8) 出所有在部门’1’或’2’或’5’工作的员工编号.(两种方法)
SELECT employeeid 编号
FROM employees 
where departmentid LIKE '[1,2,5]'
SELECT employeeid 编号
FROM employees 
where departmentid in ('1','2','5')
9) 出所有地址中含有’中山’的员工的编号及部门号.
select employeeid,departmentid
from employees
where address like '%中山%'
10) 出最后一位不是2或6或8的员工的姓名与。（三种方法）
select name,zip
from employees
where zip like '%[^2,6,8]'
select name,zip
from employees
where RIGHT(zip,1)!=2 and RIGHT(zip,1)!=6 and RIGHT(zip,1)!=8
select name,zip
from employees
where SUBSTRING(zip,6,1)!=2 and SUBSTRING(zip,6,1)!=6 and SUBSTRING(zip,6,1)!=8
11) 查询各员工的姓名与出生时间，按年龄从大到小排列。
select name,birthday
from employees
order by YEAR(GETDATE())-YEAR(birthday) desc
12) 查询各雇员的情况先按部门，同一部门再按姓名先后排列。
select *
from employees
order by departmentid,name
13) 查询employees表中前20%行数据，输出姓名、性别与电话号码。
select top 20 percent name,sex,phonenumber
from employees
select AVG (income)平均
into temp
from salary
select MAX (b.income),MIN (b.outcome)
from employees a,salary b,departments c
ployeeid =b.employeeid  and a.departmentid =c.departmentID
      and c.DepartmentName ='经理办公室'
select AVG (b.income-b.outcome)
from employees a,salary b,departments c
ployeeid =b.employeeid and a.departmentid =c.departmentID
      and c.DepartmentName ='财务部'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。