【数据库原理】实验一：SQL Server 基本操作

最新推荐文章于 2023-01-01 00:34:39 发布

庚_

最新推荐文章于 2023-01-01 00:34:39 发布

阅读量1.2k

点赞数

分类专栏：数据库原理实验文章标签： sql sqlserver

本文链接：https://blog.csdn.net/Dershy_/article/details/123693245

版权

数据库原理实验专栏收录该内容

2 篇文章 0 订阅

订阅专栏

实验一：SQL Server 基本操作

实验内容
- 实验要求
- - 代码

实验内容

进行数据库和表的修改、删除操作

实验要求

（1）熟悉DBMS的安装、了解“对象资源管理器”、“查询分析器”的功能；
（2）使用“对象资源管理器”和T-SOL两种方法，创建员工管理数据库，名为YGGL，包含下列3个表：（进行实验所要求的各种操作，所有的SQL操作均在此数据库中进行）
（3）进行数据库和表的修改、删除操作；
（4）向表中插入记录，进行数据的插入、修改、删除。（其中，部门表10条记录，如包括研发部、市场部、销售部、咨询部、项目部、生产部、财务部、人力资源部、经理办公室、秘书办等,另外两个表15条记录以上，注意表之间数据的对应关联，数据应满足各种条件/情况）
（5）简单查询
1）查询每个雇员的所有数据；
2）查询每个雇员的电话和地址；
3）查询月收入高于6000的员工号；
4）查询每个员工的实际收入（实际收入= InCome - OutCome ）；
5）查询财物部的员工总数；
6）查询男员工的姓名和出生日期，要求各列标题用中文表示；

Employees（员工自然信息）表结构
{列名数据类型长度是否可空说明
EmployeeID 定长字符 6 Х 员工编号，主键
Name 定长字符 10 Х 姓名
Education 定长字符 4 Х 学历
Birthday 日期时间型系统默认 Х 出生日期
Sex 位型系统默认 Х 性别，默认值为1
Workyer 整数型系统默认 √ 工作时间
Address 不定长字符型 40 √ 地址
PhoneNumber 定长字符型 12 √ 电话号码
DepartmentID 定长字符型 3 Х 员工部门号，外键}

Departments（部门信息）表结构
{列名数据类型长度是否可空说明
DepartmentID 定长字符型 3 Х 部门编号，主键
DepartmentName 定长字符型 20 Х 部门名
Note 不定长字符型 100 √ 备注}

Salary（员工薪水情况）表结构
{列名数据类型长度是否可空说明
EmployeeID 定长字符 6 Х 员工编号，主键
Income 浮点型系统默认 Х 收入
Outcome 浮点型系统默认 Х 支出}

代码

SQL Sever

create table Departments(
    DepartmentID char(3) not null primary key,
    DepartmentName char(20) not null,
    Note varchar(400)
);

create table Employees(
    EmployeeID char(6) not null primary key ,
    Name char(10) not null ,
    Education char(4) not null ,
    Birthday date not null ,
    Sex bit not null default (1),
    Workyer int ,
    Address varchar(40),
    PhoneNumber char(12),
    DepartmentID char(3) not null ,
    foreign key (DepartmentID)references Departments(DepartmentID)
);

create table Salary(
    EmployeeID char(6) not null primary key,
    Income float not null,
    Outcome float not null
	foreign key (EmployeeID)references Employees(EmployeeID)
);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('01','研发部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('02','市场部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('03','销售部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('04','咨询部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('05','生产部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('06','财务部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('07','人力资源部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('08','经理办公室',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('09','秘书办公室',NULL);

insert  into Departments(DepartmentID,DepartmentName,Note)
Values('10','技术部',Null)

insert  into Departments(DepartmentID,DepartmentName,Note)
Values('11','安全部',Null)

delete 
from Departments
where DepartmentName='安全部'

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365480','芋圆','本科','20010101','1','20200515','北京市海淀区','18025693164','02')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365480',6500,3800)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365481','麻薯','硕士','19870205','0','20020612','上海市普陀区','18647895462','03')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365481',7500,7000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365482','红豆','博士','20020203','1','20200401',' 北京市朝阳区建国门南大街6号','18025693369','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365482',8962,6500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365483','芋头','大专','20010215','1','20200603','北京市朝阳区平乐园100号','19825693164','06')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365483',3500,2000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365484','茶冻','硕士','20010622','1','20180912','定福庄东街1号','14565693164','01')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365484',7500,3600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365485','奶盖','本科','20010711','1','20190302','北京市朝阳区惠新东街10号','17525693164','10')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365485',4500,3600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365486','布丁','本科','19830506','1','19920304','西藏中路268号上海来福士广场01-01D','18321453164','08')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365486',3246,3800)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365487','冰激凌','大专','19920331','1','20120328','西藏中路268号上海来福士广场01-01D','18167693164','09')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365487',4500,4000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365488','爆珠','本科','20010911','1','20200515','淞沪路111号万达广场C座1312号','13025693164','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365488',5000,7000)

insert 
into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165489','珍珠','本科','20010131','1','20200303','徐家汇路618号日月光中心广场泰康区1层','15921693164','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165489',6500,2500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165490','波霸','硕士','20011202','1','20200404','上海漕宝路3366号七宝万科广场1层','13695543164','06')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165490',8700,5600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365491','双皮奶','本科','20010314','1','20200718','上海漕宝路3366号七宝万科广场1层','13445693164','07')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365491',5400,2500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165492','绵绵冰','博士','20020214','1','2021912','南京西路1601号芮欧百货5楼','17895664164','01')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165492',9500,4500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('123493','巧克力','本科','20030302','1','20220214','南京西路1601号芮欧百货5楼','13524593164','02')
insert into Salary(EmployeeID,Income,Outcome)
Values ('123493',5000,4000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365494','苹果','本科','19980302','1','20200614',' 肇嘉浜路1111号美罗城B区1层','14525653164','03')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365494',6500,5600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165495','椰子','本科','20030201','1','20201009','长宁路1018号龙之梦购物中心','13425693564','10')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165495',15000,5000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165496','麦芬','本科','20010112','1','20201203','欣宁街15号北京荟聚F1','18854193135','09')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165496',8650,5460)

update Employees
set Address = '上海市普陀区121号'
where Name='麻薯';

delete from Salary
where EmployeeID ='165496';
delete from Employees 
where EmployeeID='165496';

select *
from Employees

select PhoneNumber,Address
from Employees

select EmployeeID
from Salary
where Income>6000;

select EmployeeID,Income-OutCome 'RealCome'
from Salary

select COUNT(*)
from Employees
where DepartmentID='06';

select Name '姓名',Birthday '出生日期'
from Employees
where Sex=0;

alter table Salary add Name char(6);

update Salary
set Name = '椰子'
where EmployeeID='165495';
update Salary
set Name = '巧克力'
where EmployeeID='123493';

alter table Salary drop column Name;

drop table Salary;
drop table Employees;
drop table Departments;

drop database YGGL;

T-SQL

create database YGGL1
Go
use YGGL1
go
create table dbo.Departments(
    DepartmentID char(3) not null primary key,
    DepartmentName char(20) not null,
    Note varchar(400)
)
go

create table dbo.Employees(
    EmployeeID char(6) not null primary key ,
    Name char(10) not null ,
    Education char(4) not null ,
    Birthday date not null ,
    Sex bit not null default (1),
    Workyer int ,
    Address varchar(40),
    PhoneNumber char(12),
    DepartmentID char(3) not null ,
    foreign key (DepartmentID)references Departments(DepartmentID)
)
go

create table dbo.Salary(
    EmployeeID char(6) not null primary key,
    Income float not null,
    Outcome float not null
	foreign key (EmployeeID)references Employees(EmployeeID)
)
go

insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('01','研发部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('02','市场部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('03','销售部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('04','咨询部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('05','生产部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('06','财务部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('07','人力资源部',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('08','经理办公室',NULL)
go
insert dbo.Departments(DepartmentID,DepartmentName,Note)
Values('09','秘书办公室',NULL)
go
insert  dbo.Departments(DepartmentID,DepartmentName,Note)
Values('10','技术部',Null)
go
insert  dbo.Departments(DepartmentID,DepartmentName,Note)
Values('11','安全部',Null)
go

insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365480','芋圆','本科','20010101','1','20200515','北京市海淀区','18025693164','02')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365480',6500,3800)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365481','麻薯','硕士','19870205','0','20020612','上海市普陀区','18647895462','03')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365481',7500,7000)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365482','红豆','博士','20020203','1','20200401',' 北京市朝阳区建国门南大街6号','18025693369','05')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365482',8962,6500)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365483','芋头','大专','20010215','1','20200603','北京市朝阳区平乐园100号','19825693164','06')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365483',3500,2000)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365484','茶冻','硕士','20010622','1','20180912','定福庄东街1号','14565693164','01')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365484',7500,3600)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365485','奶盖','本科','20010711','1','20190302','北京市朝阳区惠新东街10号','17525693164','10')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365485',4500,3600)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365486','布丁','本科','19830506','1','19920304','西藏中路268号上海来福士广场01-01D','18321453164','08')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365486',3246,3800)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365487','冰激凌','大专','19920331','1','20120328','西藏中路268号上海来福士广场01-01D','18167693164','09')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365487',4500,4000)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365488','爆珠','本科','20010911','1','20200515','淞沪路111号万达广场C座1312号','13025693164','05')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365488',5000,7000)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165489','珍珠','本科','20010131','1','20200303','徐家汇路618号日月光中心广场泰康区1层','15921693164','05')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('165489',6500,2500)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165490','波霸','硕士','20011202','1','20200404','上海漕宝路3366号七宝万科广场1层','13695543164','06')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('165490',8700,5600)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365491','双皮奶','本科','20010314','1','20200718','上海漕宝路3366号七宝万科广场1层','13445693164','07')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365491',5400,2500)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165492','绵绵冰','博士','20020214','1','2021912','南京西路1601号芮欧百货5楼','17895664164','01')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('165492',9500,4500)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('123493','巧克力','本科','20030302','1','20220214','南京西路1601号芮欧百货5楼','13524593164','02')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('123493',5000,4000)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365494','苹果','本科','19980302','1','20200614',' 肇嘉浜路1111号美罗城B区1层','14525653164','03')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('365494',6500,5600)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165495','椰子','本科','20030201','1','20201009','长宁路1018号龙之梦购物中心','13425693564','10')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('165495',15000,5000)
go
insert dbo.Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165496','麦芬','本科','20010112','1','20201203','欣宁街15号北京荟聚F1','18854193135','09')
go
insert dbo.Salary(EmployeeID,Income,Outcome)
Values ('165496',8650,5460)
go

update dbo.Employees  
    set Address = '上海市普陀区121号'  
    where Name = '麻薯'  
GO

select *
from dbo.Employees
go

select EmployeeID as '工号',Income as '收入'
from dbo.Salary
where Income>6000
go


drop table Employees  ;
go

drop table Departments  ;
go

drop table Salary  ;
go

USE MASTER;  
GO  
DROP DATABASE YGGL1;  
GO

奶茶配料的家就是喜茶，感觉两个版本差别不大。

庚_

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
【数据库原理】实验一：SQL Server 基本操作

实验一：SQL Server 基本操作实验内容实验要求代码实验内容进行数据库和表的修改、删除操作实验要求（1）熟悉DBMS的安装、了解“对象资源管理器”、“查询分析器”的功能；（2）使用“对象资源管理器”和T-SOL两种方法，创建员工管理数据库，名为YGGL，包含下列3个表：（进行实验所要求的各种操作，所有的SQL操作均在此数据库中进行）（3）进行数据库和表的修改、删除操作；（4）向表中插入记录，进行数据的插入、修改、删除。（其中，部门表10条记录，如包括研发部、市场部、销售部、咨询部
复制链接

扫一扫

专栏目录