【SQL server】实验操作语句汇总

实验二：数据库文件操作

drop database S_T;
create database S_T;
-- 修改mdf
create database company
on
(
	name=company_data,
	filename='V:\学习文件\数据库\生成文件\company_data.mdf',
	size=5MB,
	maxsize=15MB,
	filegrowth=1MB
)

-- 修改ndf
alter database company
add file
(
	NAME=company2_data,
 	FILENAME='V:\学习文件\数据库\生成文件\company2_data.ndf',
	SIZE=1MB,
	MAXSIZE=5MB,
	FILEGROWTH=1MB
)
-- 修改ldf
alter database company
log on
(
	NAME=company_data_log,
 	FILENAME='V:\学习文件\数据库\生成文件\company_data_log.ldf',
	SIZE=3MB,
	MAXSIZE=15MB,
	FILEGROWTH=2MB 
)

alter database company
add FILEGROUP TempGroup
alter database company
add file
(
	NAME=company3_data,
 	FILENAME='V:\学习文件\数据库\生成文件\company3_data.ndf',
	SIZE=3MB,
	MAXSIZE=10MB,
	FILEGROWTH=1MB 
)to filegroup TempGroup


alter database company

remove file company2_data

-- 删除数据库
USE MASTER    
GO    
     
DECLARE @dbname SYSNAME    
SET @dbname = 'company' 
     
DECLARE @s NVARCHAR(1000)    
DECLARE tb CURSOR LOCAL   
FOR  
    SELECT s = 'kill   ' + CAST(spid AS VARCHAR)  
    FROM   MASTER..sysprocesses  
    WHERE  dbid = DB_ID(@dbname)    
     
OPEN   tb      
FETCH   NEXT   FROM   tb   INTO   @s    
WHILE @@fetch_status = 0  
BEGIN  
    EXEC (@s)   
    FETCH NEXT FROM tb INTO @s  
END    
CLOSE   tb    
DEALLOCATE   tb    
  
EXEC ('drop   database   [' + @dbname + ']')    

create database company

实验三：数据表创建和修改

-- 删除数据库S_T
USE MASTER    
GO    
DECLARE @dbname SYSNAME    
SET @dbname = 'S_T' 
DECLARE @s NVARCHAR(1000)    
DECLARE tb CURSOR LOCAL   
FOR  
    SELECT s = 'kill   ' + CAST(spid AS VARCHAR)  
    FROM   MASTER..sysprocesses  
    WHERE  dbid = DB_ID(@dbname)    
     
OPEN   tb      
FETCH   NEXT   FROM   tb   INTO   @s    
WHILE @@fetch_status = 0  
BEGIN  
    EXEC (@s)   
    FETCH NEXT FROM tb INTO @s  
END    
CLOSE   tb    
DEALLOCATE   tb    
  
EXEC ('drop   database   [' + @dbname + ']')    
-- 创建数据库S_T
create database S_T
use S_T
-- 1．	创建student表，设置Sno为主键；
-- 在Sno列上创建核查约束，要求学号各个位上只能取数字字符；
-- 在Ssex列上创建核查约束，要求性别只能取“男”或“女”；
-- 在Sage 列上创建核查约束，要求学生的年龄只能在14至38之间。
create table student(
	Sno char(5) not null primary key,
	Sname char(20) not null unique,
	Ssex char(2) not null check (Ssex='男' or Ssex='女'),
	Sage smallint not null check(Sage between 14 and 38),
	Sdept char(20) not null
)
-- 2．	创建course表，设置列Cno为主键；
-- 在Cno列上创建核查约束，要求课程号只能取数字字符。
use S_T
create table course(
	Cno char(4) not null primary key,
	Cname char(40) not null,
	Cpno char(4),
	Ccredit smallint not null
)
-- 3．	创建sc表，设置主键为Sno与Cno的组合；
-- 在Grade列上创建核查约束，限定成绩只能在0-100。
use S_T
create table sc(
	Sno char(5) not null,
	Cno char(4) not null,
	primary key(Sno,Cno),
	Grade int check(Grade between 0 and 100)
)
-- 4．	修改course表，
-- 在表中为Cpno列添加一外键约束Fk_Cpno，
-- 它参照了course表的主键Cno。
alter table course
add constraint Fk_Cpno foreign key (Cpno) references course(Cno)
-- 5．	修改sc表，在表中为Sno添加一外键约束Fk_Sno，
-- 它参照了student表中的主键Sno；
-- 为Cno添加外键约束Fk_Cno， 它参照了course表中的主键Cno。
alter table sc
add constraint Fk_Sno foreign key(Sno) references student(Sno)
alter table sc
add constraint Fk_Cno foreign key(Cno) references course(Cno)
-- ------------------------------------
-- 	针对company数据库：
-- 删除数据库company
USE MASTER    
GO    
DECLARE @dbname SYSNAME    
SET @dbname = 'company' 
DECLARE @s NVARCHAR(1000)    
DECLARE tb CURSOR LOCAL   
FOR  
    SELECT s = 'kill   ' + CAST(spid AS VARCHAR)  
    FROM   MASTER..sysprocesses  
    WHERE  dbid = DB_ID(@dbname)    
     
OPEN   tb      
FETCH   NEXT   FROM   tb   INTO   @s    
WHILE @@fetch_status = 0  
BEGIN  
    EXEC (@s)   
    FETCH NEXT FROM tb INTO @s  
END    
CLOSE   tb    
DEALLOCATE   tb    
  
EXEC ('drop   database   [' + @dbname + ']')    
-- 创建数据库company
create database company
use company
-- 1．	在数据库company中创建以上五张表，并设置各表的主键。
create table employee(
	emp_no char(5) not null primary key,
	emp_name VARCHAR(10) not null,
	sex char(2) not null,
	dept VARCHAR(10) not null,
	title VARCHAR(10) not null,
	date_hired datetime not null,
	birthday datetime ,
	salary int not null,
	telephone VARCHAR(20),
	addr VARCHAR(50)
)
create table customer(
	cust_id char(5) not null primary key,
	cust_name VARCHAR(20) not null,
	addr VARCHAR(40) not null,
	tel_no VARCHAR(20) not null,
	zip char(6)
)
create table sales(
	order_no int not null primary key,
	cust_id char(5) not null,
	sale_id char(5) not null,
	tot_amt numeric(9,2) not null,
	order_date datetime not null
)
create table sale_item(
	order_no int not null ,
	prod_id char(5) not null ,
	qty int not null,
	unit_price numeric(7,2) not null,
	order_date datetime
	primary key(order_no,prod_id),
)
create table product(
	prod_id char(5) not null primary key,
	prod_name VARCHAR(20) not null
)
-- 2．	在销售主表sales中添加字段“发票号码” invoice_no，char(10)，NOT NULL。

alter table sales
add invoice_no char(10) not null
-- 3．	添加外键约束：
-- 在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为FK_sale_id。
alter table sales
add constraint Fk_sale_id foreign key(sale_id) references employee(emp_no)

-- 在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为FK_cust_id。
alter table sales
add constraint Fk_cust_id foreign key(cust_id) references customer(cust_id)
-- 在销售明细表sale_item的订单编号字段order_no上添加外键约束，
-- 参照字段为销售主表sales中的字段订单编号order_no，
-- 约束名为FK_order_no。
alter table sale_item
add constraint Fk_order_no foreign key(order_no) references sales(order_no)

-- 在销售明细表sale_item的产品编号字段prod_id上添加外键约束，
-- 参照字段为产品名称表product中的产品编号字段prod_id，
-- 约束名为FK_prod_id。
alter table sale_item
add constraint Fk_prod_id foreign key(prod_id) references product(prod_id)
-- 4．	添加核查约束：
-- a) 将员工表employee中的薪水字段salary的值限定在1000至10000间，
-- 约束名为CK_salary。
alter table employee
add constraint Ck_salary check(salary between 1000 and 10000)
-- b) 将员工表employee中的员工编号字段emp_no设定为以“E”字母开头,
-- 后面跟5位数的编号，约束名为CK_emp_no。
alter table employee
add constraint CK_emp_no check(LEFT(emp_no,1)='E' and len(emp_no)=5) 
-- c)	将员工表employee中的性别字段设定这取值只能是“男”和“女”。
--约束名为CK_sex。
alter table employee
add constraint Ck_sex check(sex='男' or sex='女')
-- d)	将销售主表sales中的发票号码字段invoice_no设定为以“I”字母开头，
--后面跟9位数的编号，约束名为CK_inno。
alter table sales
add constraint Ck_inno check(LEFT(invoice_no,1)='I' and len(invoice_no)=10)

-- 5．为销售主表sales中的字段发票号码invoice_no设置为唯一约束，
-- 约束名为UN_inno。
alter table sales
add constraint UN_no unique(invoice_no)

实验四：数据表的查询

use company 
-- 1、查找所有经理的姓名、职称、薪水。
select emp_name,dept,salary from employee where title='经理'
-- 2、在销售主表sales中查找销售金额大于等于10000元的订单。
select * from sales where tot_amt>=10000
-- 3、在员工表employee中查找薪水在4000至8000元之间的员工。
select emp_name from employee where salary between 4000 and 8000
-- 4、在员工表employee中查找住址为上海、北京、天津这三个城市的员工。
select emp_name from employee where addr in ('上海市' , '天津市', '北京市')
-- 5、在客户表customer中查找住址不在上海、北京、天津这三个城市的客户。
select emp_name from employee where addr not in ('上海市' , '天津市', '北京市')
-- 6、在员工表employee中查找姓“王”用姓名最后一个字为“功”的员工。
select emp_name from employee where emp_name like '王%功'
-- 7、在客户表customer中查找姓“刘”的客户名称、电话。
select cust_name,tel_no from customer where cust_name like '刘%' 
-- 8、查找出职称为“经理”或“职员”的女工的信息。
select * from employee where title in ('经理','职员') and sex='女'
-- 9、查找薪水最高的前三条员工记录。
select top(3) * from employee order by salary desc
-- 10、查找订单金额最高的前10%的订单记录。
select top(10) percent * from sales order by tot_amt desc
-- 11、查找员工表中所属部门。（去掉重复记录）
select distinct dept from employee
-- 12、查找员工表中的所有记录，并按薪水由低到高进行排序。
select * from employee order by salary desc

实验五：数据表查询进阶

use company
go
-- ％	在员工表employee中统计员工人数。
select count(*) as '员工人数'
from employee
go
-- ％	统计各部门员工的员工人数及平均薪水。
-- dept 部门号  salary 薪水
select AVG(salary) as '平均收入', count(*) as '人数'
from employee
group by dept;
go

-- ％	查询销售业绩超过10000元的员工编号。
-- sale_id 业务员编号   tot_amt 订单金额
select sale_id as 业务员编号
from sales
GROUP BY sale_id
HAVING SUM(tot_amt)>10000
go
-- ％	计算每一产品销售数量总和与平均销售单价。
-- unit_price 单价 qty销售数量 prod_id产品编号
SELECT prod_id as 产品编号, SUM(qty) as 数量总和, AVG(unit_price) as 平均销售单价
FROM sale_item
GROUP BY prod_id;
go
-- ％	统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUP  BY 子句中使用CUBE关键字）
select dept as 部门, sex as 性别, AVG(salary)
from employee
GROUP BY dept,sex
WITH CUBE

select AVG(salary) as 平均薪水 from employee
select sex as 性别,AVG(salary) AS 平均薪水 FROM employee GROUP BY sex;

go
-- ％	统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUP  BY 子句中使用ROLLUP关键字）
select sex as 性别 , dept as 部门, AVG(salary) as 平均薪水
FROM employee
GROUP BY sex,dept WITH ROLLUP
-- 两种效果不一样
select sex as 性别 , dept as 部门, AVG(salary) as 平均薪水
FROM employee
GROUP BY dept,sex WITH ROLLUP
go
-- ％	计算出一共销售了几种产品。
select count(distinct prod_id) as 销售产品种类
from sale_item
go
-- ％	显示sale_item表中每种产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。
select product.prod_name as 产品名称, SUM(unit_price) AS 总价格
from product, sale_item
where product.prod_id=sale_item.prod_id
GROUP BY product.prod_name
ORDER BY SUM(unit_price) desc;

-- 将两个表连接
SELECT *
FROM product, sale_item
WHERE product.prod_id=sale_item.prod_id
order by product.prod_id
go
-- ％	计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。
SELECT product.prod_id as 产品编号,
    product.prod_name as 产品名称 ,
    MONTH(order_date) as 月份,
    SUM(unit_price) as 销售金额总和
from product, sale_item
where product.prod_id=sale_item.prod_id
GROUP BY product.prod_id ,MONTH(order_date),product.prod_name
ORDER BY MONTH(order_date),product.prod_id;
go
-- ％	查询每位业务员各个月的业绩，并按业务员编号、月份降序排序。
SELECT employee.emp_no as 业务员编号,
    employee.emp_name as 业务员名字,
    MONTH(sales.order_date) as 月份,
    SUM(sales.tot_amt) as 业绩
From employee, sales
where employee.emp_no=sales.sale_id
GROUP BY employee.emp_no ,employee.emp_name,MONTH(order_date)
ORDER BY employee.emp_no,MONTH(order_date) desc;
go

SELECT * FROM sales
GO