该篇博文关于数据库中对表的简单查询、排序和连接均是以下面三张表为基础进行的操作。在每一条操作语句下对应有相关的操作说明。
查询分析器下代码附上:
create database YGGL;
use YGGL;
create table Employees
(
EmployeeID char(6) primary key,
Name char(10) not null,
Birthday datetime not null,
Sex bit default 1,
Address varchar(40),
Zip char(6),
PhonNumber char(12),
Email varchar(30),
DepartmentID char(3) not null foreign key references Departments(DepartmentID)
);
create table Departments
(
DepartmentID char(3) not null primary key,
DepartmentName char(20) not null unique,
Note text
);
create table Salary
(
EmployeeID char(6) foreign key references Employees(EmployeeID) primary key,
Income float not null,
Outcome float not null
);
select * from Employees;
update Employees set Sex=0 where EmployeeID in('101','104','106','109')
insert into Employees ( EmployeeID ,Name , Birthday , Address ,Zip,PhonNumber ,Email ,DepartmentID )
values('101','张三','1991-1-1','安徽','1001','1515594','101@qq.com','110')
,('102','李四','1992-1-2','六安','1002','1515595','102@qq.com','111')
,('103','王麻子','1993-1-4','马鞍山','1003','1515596','103@qq.com','112')
, ('104','阿斗','1994-1-5','巢湖','1004','1515597','104@qq.com','113')
,('105','阿豆','1995-1-7','霍邱','1005','1515598','105@qq.com','114')
,('106','王五','1996-2-1','金安','1006','1515599','106@qq.com','115')
,('107','豆豆','1997-3-1','裕安','1007','1515591','107@qq.com','116')
, ('108','嘟嘟','1997-4-1','大别山','1008','1515592','108@qq.com','117')
select *from Departments;
insert into Departments
values('110','人事部','人事相关')
,('111','消防部','消防相关')
,('112','纪检部1','纪检相关')
,('113','纪检部2','纪检相关')
,('114','信工部','信工相关')
,('115','文艺部','文艺相关')
,('116','党团部','党团相关')
,('117','思政部','思政相关')
select *from Salary;
insert into Salary
values('101',2000,500)
,('102',1000,600)
,('103',1500,1000)
,('104',1600,1000)
,('105',6000,1500)
,('106',6000,1000)
,('107',7000,1000)
,('108',8000,1500)
update Salary set Income = 2890 where EmployeeID='101' --修改职工号是101的工资为2890
update Salary set Income=Income+200 where Income<2000 --修改工资低于2000元的并加上200
---delete from Salary where Income<2000 --删除工资低于2000的员工信息
delete from Employees where EmployeeID ='13' --删除多余的第十行,莫名其妙多出来的
select *from Employees --查询Employees中的所有员工信息
select *from Departments --查询Departments中所有记录
select *from Salary --查询Salary 中的所有记录
select EmployeeID , Address,PhonNumber from Employees --查询每个员工的地址和联系方式
select DepartmentID , DepartmentName from Departments --查询每个部门的部门号和部门名
select Name as 姓名,Address as 地址,DepartmentID as 部门号 from Employees where Sex='1' -- 查询所有女员工的姓名和地址及部门号,并用as子句将结果中各列的标题分别指定为姓名和地址及部门号。
select EmployeeID ,实际收入 =(Income-Outcome) from Salary --计算每个员工的实际收入
select EmployeeID,Income from Salary where Income between 2000 and 3000 -- 找出所有收入在2000~3000之间的员工编号。
select *from Employees order by Birthday ---查询员工的基本信息并出生时间先后排序。
select DepartmentID ,Name from Employees where DepartmentID in('110','111') --找出所有在部门‘1’或部门‘2’工作的员工的部门号及姓名。
select Name ,DepartmentID from Employees where Name like '张%' ---找出所有姓王的员工的姓名及部门号。
select Name,Address from Employees where Address like '%六安%' --找出所在其地址中含有“中山”的员工的姓名及地址
select s.*,Income,Outcome, DepartmentName,Note from Employees s join Salary on s.EmployeeID=Salary.EmployeeID join Departments on s.DepartmentID=Departments.DepartmentID --- 查询每个员工的基本信息及其薪水情况。
select s.*,DepartmentName, Note from Employees as s join Departments on s.DepartmentID=Departments.DepartmentID --、查询每个员工的情况及其工作部门的情况。
select Name,Income,Outcome from Employees S join Salary on S.EmployeeID=Salary.EmployeeID join Departments on S.DepartmentID=Departments.DepartmentID
where Income>1000 and DepartmentName='纪检部' -- 查找纪检部收入在2200以上的员工的姓名及其薪水详情
select Name,Income,Outcome, birthday from Employees S join Salary on S.EmployeeID=Salary.EmployeeID join Departments on S.DepartmentID=Departments.DepartmentID
where birthday<'1996' and DepartmentName='纪检部' ----查找纪检部在1966年以前出生的员工的姓名及其薪水情况。
select s.* ,departmentname, note ,income ,outcome from Employees S join Salary on S.EmployeeID=Salary.EmployeeID join Departments on S.DepartmentID=Departments.DepartmentID
order by income ---将各员工的情况按收入由低到高排序。
select*from Employees where DepartmentID in
(
select DepartmentID from Departments where DepartmentName='信工部' --- 查找所有在信工部工作的员工的基本信息
)
select Name as 员工姓名,DepartmentID as 部门号 from Employees where EmployeeID in
(
select EmployeeID from Salary where Income<2500 ---查找所有收入在2500以下的员工的姓名及部门号
)
select name from Employees where DepartmentID in
(
select DepartmentID from Departments where DepartmentName='文艺部'
) and GETDATE()-Birthday < -----查找文艺部年龄低于信工部员工年龄的员工的姓名。
(
select GETDATE()-birthday from Employees where DepartmentID in
(
select DepartmentID from Departments where DepartmentName='信工部'
)
)
---问题
/*
一、数据的分类与汇总
1、求所有女员工的平均收入。
2、求信工部员工的平均收入。
3、查询信工部员工的最高和最低收入。
4、求所有男员工的平均实际收入。
5、求信工部员工的平均实际收入。
6、查询信工部员工的最高和最低实际收入。
7、求年龄在45以上的总人数。
8、求信工部员工的总人数。
9、求信工员收入在3000以上的总人数。
10、求各部门的员工人数。
11、统计各部门收入在2000以上的员工的人数。*/
select AVG(income) as 平均收入 from Salary
join Employees on Employees.EmployeeID=Salary.EmployeeID where Sex=0 ---求所有女员工的平均收入
select AVG(income) as 平均收入 from Salary
join Employees on Employees.EmployeeID=Salary.EmployeeID
join Departments on Departments.DepartmentID=Employees.DepartmentID
where DepartmentName='信工部' ---求财务部员工的平均收入
select MAX(income) as 最高收入, MIN(income) as 最低收入 from Salary
join Employees on Employees.EmployeeID=Salary.EmployeeID
join Departments on Departments.DepartmentID=Employees.DepartmentID where DepartmentName='文艺部'
---查询财务部员工的最高和最低收入
select AVG(income-outcome) as 实际收入平均值 from Salary
join Employees on Employees.EmployeeID=Salary.EmployeeID where Sex=1 ---求所有男员工的平均实际收入
select AVG(income-outcome) as 实际收入平均值 from salary
join Employees on Employees.EmployeeID=Salary.EmployeeID
join Departments on Departments.DepartmentID=Employees.DepartmentID
where DepartmentName='信工部' -----求财务部员工的平均实际收入
select MAX(income-outcome) as 最高实际收入 ,MIN(income-outcome) as 最低实际收入
from Salary join Employees on Employees.EmployeeID=Salary.EmployeeID
join Departments on
Departments.DepartmentID=Employees.DepartmentID
where DepartmentName='信工部' -----查询财务部员工的最高和最低实际收入
select COUNT(*) AS 总人数 from Employees where (GETDATE()-birthday) >45 ---求年龄在45以上的总人数
select COUNT(*) as 总人数 from Employees
join Departments on Employees.DepartmentID=Departments.DepartmentID
where DepartmentName='信工部' ---求财务部员工的总人数
select COUNT(*) as 总人数 from Salary
join Employees on Employees.EmployeeID=Salary.EmployeeID
join departments on Departments.departmentid=Employees.DepartmentID
where departmentname='信工部' and Income>3000 ---求财务员收入在3000以上的总人数
select departmentname as 部门 ,COUNT(*) as 人数 from Departments
join Employees on Employees.DepartmentID=Departments.DepartmentID
join Salary on Salary.EmployeeID=Employees.EmployeeID
group by DepartmentName ---求各部门的员工人数
select departmentname as 部门 ,COUNT(*) as 人数 from Departments
join Employees on Employees.DepartmentID=Departments.DepartmentID
join Salary on Salary.EmployeeID=Employees.EmployeeID
group by DepartmentName
---统计各部门收入在2000以上的员工的人数
SQL server数据库的功能:
SQL server中常见的几种命令动词的语法:
(1)drop语法:
1、DROP TABLE 语句用于删除表(表的结构、属性以及索引也会被删除)
语法格式:
DROP TABLE 表名称
说明:只能删除自己创建的表,不能删除其他用户创建的表。
2、DROP DATABASE 语句用于删除数据库
语法格式:
DROP DATABASE 数据库名称
说明:一个drop语句可以一次删除 多个数据库。
(2)update用于修改表中的数据
语法格式:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
(3)delete用于删除表中的行
语法格式:
DELETE FROM 表名称 WHERE 列名称 = 值
(4)insert into用于向表格中插入新的行
INSERT INTO 表名称 VALUES (值1, 值2,....)
(5)alter table可用于在已有的表中添加、修改或者删除列
语法格式:
如需在表中添加列,请使用下列语法:
ALTER TABLE table_name
ADD column_name datatype
要删除表中的列,请使用下列语法:
ALTER TABLE table_name
DROP COLUMN column_name
注释:某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。
要改变表中列的数据类型,请使用下列语法:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
(6)select 语句用于从表中选取数据,结果被存储在一个结果表中(称为结果集)
语法格式:
SELECT 列名称 FROM 表名称
以及:
SELECT * FROM 表名称
注释:SQL 语句对大小写不敏感。SELECT 等效于 select。
(7)
1、create用于创建数据库
语法格式:
CREATE DATABASE database_name
2、create用于创建数据库中的表
语法格式:
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
需注意:以上所有操作语句均是在 SQL server数据库的基础上进行的。
课余时间总结的数据库相关操作,希望对每一个认真学习数据库的同学有所帮助。如果大家觉得写的不错还可以把链接分享给更多的人,如有表达欠妥之处还请留言指出,会酌情进行修改。最后祝愿大家都能共同进步!
更多精彩内容请关注公众号:干货分享录