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