一、已有数据库,其名称为userdb
1.在userdb数据库中按照要求创建以下数据表:
创建数据库userdb CREATE DATABASE userdb ON PRIMARY (NAME=userdb_data, FILENAME='D:\software\userdb.mdf', SIZE=10MB, MAXSIZE=UNLIMITED, FILEGROWTH=10% ) LOG ON (NAME=userdb_log, FILENAME='D:\software\userdb.ldf', SIZE=1MB, MAXSIZE=5MB, FILEGROWTH=1MB )
创建部门表Departments create table Departments ( DepartmentsID int identity(1,1) not null PRIMARY KEY, DepartmentsName varchar(50) not null CONSTRAINT UN_sname UNIQUE, Note varchar(100) )
创建雇员表Employees USE userdb CREATE TABLE Employees ( EmployeeID int identity(1,1) PRIMARY KEY, DepartmentID int FOREIGN KEY(DepartmentID) REFERENCES Departments, Name varchar(20) NOT NULL, Age int NOT NULL, Sex char(2) NOT NULL, Edate datetime )
创建员工薪水表Salary USE userdb CREATE TABLE Salary ( EmployeeID int NOT NULL PRIMARY KEY, Income Money, Outcome Money )
2.为上面的数据表添加以下约束:
(1)Departments,Employees,Salary表添加三个主键约束，约束名分别为PK_DepartmentsID,PK_EmployeesID,PK_Salary_ID
alter table Departments ADD constraint PK_DepartmentsID PRIMARY KEY(DepartmentsID) alter table Employees ADD constraint PK_EmployeeID PRIMARY KEY(EmployeeID) alter table Salary add constraint PK_Salary_ID primary key(EmployeeID)
(2)为Employees表的DepartmentID字段添加外键约束，依赖于Departments表的DepartmentsID
alter table Employees ADD constraint FK_DepartmentID foreign key(DepartmentID) REFERENCES Departments(DepartmentsID)
(3)为Employees表的Age字段添加CHECK约束，检查Age大于0
alter table Employees add check(Age>0)
(4)为Employees表的Sex字段添加默认约束，默认Sex为男
alter table Employees add constraint constraintSex DEFAULT '男' for Sex;
(5)为Employees表的Edate字段添加默认约束，默认为当天日期
alter table Employees add constraint constraintWdate DEFAULT getdate()for Edate;
3.修改表Employees中列Name的数据类型为varchar(30)
alter table Employees alter column Name varchar(30)
4.修改表Employees,在表中增加新列zip,类型为char(6)
alter table Employees add zip char(6)
5.从表Employees中删除列zip
alter table Employees drop column zip
6.向表中插入如下记录
INSERT INTO Departments( DepartmentsName) VALUES('销售部') INSERT INTO Departments( DepartmentsName) VALUES('采购部') INSERT INTO Departments( DepartmentsName) VALUES('生产部')
INSERT INTO Employees(DepartmentID,Name,Age,Sex,Edate)VALUES('1','王林','35','男','2000-11-10 0:00:00' ) INSERT INTO Employees(DepartmentID,Name,Age,Sex,Edate)VALUES('1','李红','46','女','1992-10-11 0:00:00') INSERT INTO Employees(DepartmentID,Name,Age,Sex,Edate)VALUES('1','李强','48','男','2005-3-10 0:00:00') INSERT INTO Employees(DepartmentID,Name,Age,Sex,Edate)VALUES('2','张扬','23','女','2010-5-10 0:00:00') INSERT INTO Employees(DepartmentID,Name,Age,Sex,Edate)VALUES('2','赵明','50','男','2003-6-10 0:00:00' )
INSERT INTO Salary VALUES('1','3500.0000','800.0000') INSERT INTO Salary VALUES('2','4000.0000','1200.0000') INSERT INTO Salary VALUES('3','7000.0000','2500.0000') INSERT INTO Salary VALUES('4','1300.0000','1200.0000') INSERT INTO Salary VALUES('5','5500.0000','2500.0000')
7.修改Salary表中的列Income(工资)的数据，为EmployeeID等于的雇员增加元工资
update Salary set Income=Income+500 where EmployeeID=1 select * from Salary
8.删除雇员表中工号为的人员记录
delete from Employees where EmployeeID=5 select * from Employees
9.修改表Employees中姓名为王林的雇员信息，部门编号修改为，年龄修改为，雇佣日期修改为“2002-11-10”
update Employees set DepartmentID=2,Age=37,Edate='2002-11-10'where Name='王林'
二、表的管理测试题
使用T-SQL语句完成下列对应功能:
1.创建一个数据库。数据库名称为worker,主数据文件的逻辑名称为worker_ data,操作系统文件的名称为D:\software\worker_mdf, 大小为MB,最大为MB,以%的速度增长。数据库的日志文件逻辑名称为student_log,操作系统文件的名称为D:\software\worker_ ldf,大小为MB,最大为MB,以MB的速度增长。
create database worker on primary (NAME=worker_data, FILENAME='D:\software\worker.mdf', SIZE=20MB, MAXSIZE=50MB, FILEGROWTH=25% ) LOG ON ( NAME=student_log, FILENAME='D:\software\worker.ldf', SIZE=3MB, MAXSIZE=10MB, FILEGROWTH=1MB )
2.在worker数据库中创建一个名为employees的数据表
use worker create table employees ( number int not null primary key, name varchar(10) not null, sex char(3), birthday datetime not null, hometown varchar(30), telephone varchar(15), address varchar(30) )
3.向employees的数据表添加两个记录，各个字段的值顺序是:
INSERT INTO employees VALUES('201','李小明','男',1980-07-12,'广东','22218371','五一路') INSERT INTO employees VALUES('202','张娟','女',1983- 12-19,'北京','47372812','芙蓉路')
4.向employees表中插人number为，name为张军，birthday为-08-08的一条记录。
INSERT INTO employees(number,name,birthday)VALUES(203,'张军',1980-08-08)
5.在employees的数据表中，将name为“李小明”的birthday修改成“-02-09”
update employees set birthday='1982-02-09' where name='李小明'
6.将hometown为“北京”的telephone值在前面加上“-”,如“-47372812”
update employees set telephone='010-'+telephone where hometown='北京'
7.将number为和的记录，birthday都改为-09-09，address改为太白路
update employees set birthday='1990-09-09',address='太白路' where number=201 or number=202
8.将name为“李小明”的数据从employees的数据表中删除
delete from employees where name='李小明'
9.清空employees 表中的所有数据
delete from employees
总结
今天总结的课后习题是否对友友们有帮助呢?😉可以点赞收藏博客,关注博主的后续分享关于数据库原理内容哦~👻👻
