SqlServer 基本表操作和基本查询语句

--建表语句：
use db_Test;   --选择数据库
--创建部门表
go
if exists(select * from sysobjects where name='department')
drop table department;
create table department(
	depart_id int primary key,--部门id
	depart_name char(30) unique not null,--部门名称
	depart_add char(100) null                 --部门地址
);
--创建员工表
if exists(select * from sysobjects where name='employee')
drop table employee;
create table employee(
	emp_id char(6) primary key,--员工ID
	depart_id int,--员工所属部门ID
	emp_name varchar(16),--员工姓名
	emp_sex  bit,--员工性别
	emp_birth date,--员工出生日期
	emp_age  int,--员工年龄
	emp_phone	char(11)	 --员工联系方式
);
--添加主外键关联
alter table employee add foreign key(depart_id) references  department(depart_id);

--创建工资表
if exists (select * from  sysobjects where name='salary')
drop table salary;
create table salary (
	emp_id char(6) not null,income float,outcome float
);

--工资表和员工表外键关联
alter table salary add foreign key(emp_id) references employee(emp_id);

--向部门表中插入数据：
insert department select depart_id=1,depart_name='财务部',depart_add='一路' union all
select depart_id=2,depart_name='人力资源部',depart_add='二路' union all
select depart_id=3,depart_name='经理办公室',depart_add='三路' union all
select depart_id=4,depart_name='研发部',depart_add='四路' union all
select depart_id=5,depart_name='市场部',depart_add='五路'

--向员工表中插入数据：
insert into employee values('000001',2,'王林','1966-01-23',23,'13233334444');
insert into employee values('010008',1,'伍荣华','1976-03-28','13233334444');
insert into employee values('020010','王向容','1982-12-09','13233334444');
insert into employee values('020018','李丽','1960-07-30','13233334444');
insert into employee values('102201',5,'刘明','1972-10-18','13233334444');

insert into employee values('102208','朱俊','1965-09-28','13233334444');
insert into employee values('108991',3,'钟敏','1979-08-10','13233334444');
insert into employee values('111006','张士兵','1974-10-01','13233334444');
insert into employee values('210678','林涛','1977-04-20','13233334444');
insert into employee values('302556',4,'李玉珉','1968-09-20','13233334444');

insert into employee values('308759','叶凡','1978-11-18','13233334444');
insert into employee values('504209','陈林琳','1969-09-03','13233334444');

--向工资表插入数据：
insert into salary values('000001',2100.8,123.09);
insert into salary values('010008',1582.62,88.03);
insert into salary values('102201',2569.88,185.65);
insert into salary values('111006',1987.01,79.58);
insert into salary values('504209',2066.15,108.0);
insert into salary values('302556',2980.7,210.2);

insert into salary values('108991',3259.98,281.52);
insert into salary values('020010',2860.0,198.0);
insert into salary values('020018',2347.68,180.0);
insert into salary values('308759',2531.98,199.08);
insert into salary values('210678',2240.0,121.0);
insert into salary values('102208',1980.0,100.0);


--基本查询语句
select * from department;
select * from employee;
select * from salary;

--使用where添加查询条件

--查询月收入高于2000元 员工号码
select emp_id from salary where income>2000;
--查询1970 年之后出生的员工ID和姓名
select emp_id,emp_name from employee where emp_birth>'1970-01-01';
--使用as改变显示列名
select emp_name as '员工姓名',emp_birth as '出生日期' from employee where emp_sex=1;
--使用case语句
--查询员工表中的男员工的姓名和性别  要求sex为0显示女  为1显示男  
select emp_name as '员工姓名',case when emp_sex=1 then '男'
									 when emp_sex=0 then '女' end as '性别' from employee where emp_sex=1;


go
--使用count,avg,between and,max,min 等常用的函数做查询

--查询所有员工的最高收入  最低收入  平均收入
select MAX(income) as 最高收入,MIN(income)as 最低收入,AVG(income)as 平均收入 from salary;
--查询员工实际收入在 1000-2000的所有员工
select emp_name as '员工姓名',(income-outcome) as'实际收入' from employee join salary on employee.emp_id=salary.emp_id 
						where (income-outcome) between 1000 and 2000;
						
go
--使用Like比较查询 ：查询所有姓王的员工
select emp_name from employee where emp_name like '王%';

--使用 any,all,in,not in,exists 等逻辑谓词：

--查询所有实际收入高于平均收入的员工姓名
select emp_name from employee where emp_id in( 
			select emp_id from salary where(income-outcome)>(select AVG(income-outcome) from salary));
--查询员工的收入情况;
select emp_name as '员工姓名',emp_sex as'性别',income as'收入',outcome as'支出' from employee em,salary sa where em.emp_id=sa.emp_id;
--查询收入高于  所有(任一)女员工的收入的 男员工
select emp_name from employee where emp_sex=1 and emp_id in(
		select emp_id from salary where income> any(select income from salary where emp_id in(select emp_id from employee where emp_sex=0)));
--查询员工所属部门情况
select * from department de,employee em where em.depart_id=de.depart_id
--查询不属于任何女员工所在部门的  男员工
select e.emp_name from department d,employee e where e.depart_id=d.depart_id and e.emp_sex=1 and e.depart_id not in(
select de.depart_id from department de,employee em where de.depart_id=em.depart_id and em.emp_sex=0);
go
--使用groupBy 查询
--查询每个部门所有员工的 收入和  支出和 平均收入
select SUM(income),SUM(outcome),AVG(income) from employee join salary on employee.emp_id=salary.emp_id group by depart_id ;
--having子句：  查询每个部门所有 实际收入>5000  收入和  支出和 平均收入
select SUM(income),AVG(income) from employee join salary on employee.emp_id=salary.emp_id group by depart_id 
				having (SUM(income)-SUM(outcome))>0;

--使用order by子句
select * from employee order by emp_age;

--使用透视（pivot）,反透视（unpivot）实现行列互换：
--透视，如果属于该部门则为1否则为0

select * from employee;

select emp_name,[1] as '部门1',[2] as '部门2',[3] as '部门3',[4] as '部门4',[5] as '部门5'from
 (select * from  employee) as aa pivot
    ( count(depart_id)
		for depart_id in ([2],[1],[3],[4],[5])) as piv

	

--使用反透视
select * from  employee as aa pivot
    ( count(depart_id)
		for depart_id in ([2],[5])) as piv unpivot(
			departId for depart in([1],[2],[5]) 
		)as unpiv;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!