TSQL语句复习

  • 一、T-SQL创建XS数据库(database)
  • 二、T-SQL管理XS数据库（查看、修改、缩小、重命名、删除数据库）
    • 2.1、T-SQL查看(sp_helpdb)XS数据库信息
    • 2.2、T-SQL修改(alter)XS数据库信息
  • 三、T-SQL创建XSDA ==数据表== (table)
    • 3.1、T-SQL查看(sp_help)xsda数据表（==注意对比查看数据库==）
    • 3.2、T-SQL管理数据表(增删列、修改列属性、插入删除数据、重命名列or表名、删除表、修改记录值）
  • 四、T-SQL实现查询表中数据
    • 4.1语法格式 and 执行顺序
    • 4.2 简单查询习题汇总
  • 五、使用连接查询 or 子查询 实现查找
    • 习题汇总
  • 六、T-SQL创建视图(view)

一、T-SQL创建XS数据库(database)

create database XS
on
(	name = xs_dat,	--逻辑文件名
	filename = 'D:\数据库复习\xd_dat.mdf',	--物理文件名
	size = 20 mb,	--初始大小至少为4mb,因为系统自带数据库需要占用4Mb
	filegrowth = 1mb 	--默认都是mb，还可以使用%
)， --第一个只能为mdf文件，加逗号紧跟上是ndf文件
(
	name = xs_dat2,
	filename = 'D:\数据库复习\xd_dat.ndf',	--注意是ndf次要数据库文件
	size = 10 mb,
	filegrowth = 10%
)	--TSQL结尾都不要加 , 
log on 
(
	name = xs_log,
	filename = 'D:\数据库复习\xd_log.ldf', 	--ldf->log date file 
	size = 1,
	filegrowth = 10%
)

二、T-SQL管理XS数据库（查看、修改、缩小、重命名、删除数据库）

2.1、T-SQL查看(sp_helpdb)XS数据库信息

sp_helpdb xs
sp_helpdb 		--如果不接任何数据库名，则显示电脑所有数据库的信息

2.2、T-SQL修改(alter)XS数据库信息

--1.为XS数据库增加一个数据库文件
alter database xs
add file
(
	name = xs_dat3,
	filename = 'E:\data\xs_dat3.ndf',
	size = 6,
	filegrowth = 10
)
--2.扩容数据库文件，或者日志文件
alter database xs
modify file (name = xs_dat3,size = 7) --modify file 只能扩大
modify file (name = xs_log,size = 2)
--3.缩小数据库文件 	--缩小不可小于系统自带的数据库大小
DBCC shrinkfile (xs_dat3,4)
--4.为XS数据库增加一个日志文件
alter database xs
add file 
(
	name = xs_log2,
	filename = 'E:\data\xs_log2.ldf',
	size = 2,
	filegrowth = 1
)
--5.为XS数据库添加名为TextGroup文件组
alter database xs
add filegroup TextGroup
--6.删除指定的数据库文件
alter database xs
remove file xs_dat3
--7.设置数据库为只读(readonly true) --可读则为'false'
sp_dboption 'xs','readonly','true'
--8.将XS数据库重命名为Myxs
sp_renamedb "Myxs","xs"
--9.删除Myxs数据库
drop database Myxs

三、T-SQL创建XSDA 数据表 (table)

注意事项：
1.primary key -> 主键
2.默认值(default):假设字段为char若填入非本类型的数据，则为默认值
3.出生日期可以用 smalldatetime 此数据类型
4.更小的整型 tinyint

--在XS数据库中创建XSDA数据表，性别默认为女
use xs
go
create table xsda
(
	学号 char(10) not null primary key,
	姓名 char(8) not null,
	性别 char(2) not null defult('女'),
	系名 char(8) not null,
	出生日期 smalldatetime not null,
	民族 char(4) not null
	总学分 tinyint not null,
	备注 text null
)

3.1、T-SQL查看(sp_help)xsda数据表（注意对比查看数据库）

sp_help xsda

3.2、T-SQL管理数据表(增删列、修改列属性、插入删除数据、重命名列or表名、删除表、修改记录值）

1.alter table table_name
2.alter column
3.exec sp_rename 重命名表
4.re_rename 重命名列
5.insert values()
6.null 空值的判断 只能用 is 判断
7.update 更新
8.set 指定要更新的列或者变量名称的列表
9.insert into 插入到指定的别的表中

--1.为XSCJ表增加一个新列--学分
alter table xscj
add		--添加新列时，只能为null
	学分 tinyint null
--2.删除XSCJ表中的学分列
alter table xscj	--删除列之前要接触原有列上的约束条件与默认值
drop 
	column 学分 
--3.将XSDA表中的姓名列长度修改为10
alter table xsda
	alter column 姓名 char(10)
--4.将XSDA表中的出生日期数据类型改为 dadetime
alter table xsda
	alter column 出生日期 datetime
--5.将XSDA表中出生日期列名改为‘birthday’
sp_rename 'xsda.出生日期','birthday','column'
--6.重命名数据表
exec sp_rename 'xsda','学生档案'
--7.删除xsda表
drop table xsda	--删除多张表时，加上逗号接上
drop table xsda,xscj
--8.向xs数据库的xsda表插入如下数据
('200608','李中实'，'男'，'信息'，'1988-9-10','汉','59',null)
use xs 
insert xsda		
--1.全写型，不用管；
1. values('200608','李中实'，'男'，'信息'，'1988-9-10','汉','59',null)
 --2.写入部分时 into 先声明插入的有那些列 
 --可以省略 有默认值的列或允许为空值的列
2. insert into xsda(学号,姓名,系名,出生日期,总学分)
	values('200608','李中实'，'信息'，'1988-9-10','59')
--9.删除表记录
--删除总学分<50的列记录
use xs 
delete from xsda
where 总学分 < 50
--删除备注为空的列记录
delete from xsda
where 备注 is null
--删除XSDA所有行
delete from xsda
--删除学号为 200501 的列记录
delete from xsda
where 学号 = '200501'
--10.将XSDA表中学号为200502的学生的备注改为三好学生
update xsda
set 备注 = '三好学生'
where 学号 = '200502'
--11.为XSDA表中所有学生的总学分增加10分
update xsda
set 总学分 = 总学分 + 10
--12.将XSDA表中的信息系学生的学号、姓名、总学分、插入到XXXS表中
insert into XXXS
 select 学号,姓名,总学分
 from xsda
 where 系名 = '信息'

四、T-SQL实现查询表中数据

4.1语法格式 and 执行顺序

语法格式：
select xx from xx where xx
执行顺序：
from->where->group by->having->select->order by->…
无论如何 compute by 语句最后执行

4.2 简单查询习题汇总

--1.查询XSDA表中所有的学生的系名，消除结果集中的重复行
select distinct 系名
from xsda
--2.查询名字中含有”李“的学生的基本情况
select *
from xsda
where 姓名 like "%李%"
--3.查询名字中姓”李“的学生的基本情况
select * 
from xsda
where 姓名 like "李%"
--4.查询名字中含有”王李“且单姓的学生基本情况
select *
from xsda
where 姓名 like "[王李]_"
--5.查询学号开头是”2“结尾是”1“的学生的基本情况
select * from xsda
where 学号 like '2[0-9][0-9][0-9][0-9]1'
--6.查询开课学期在2，3，4学期的课程信息
select * from kcxx
where 开课学期 between 2 and 3
or 或者是
select * from kcxx
where 开课学期 in (2,3,4)
--7.查询在第5学期开设的所有课程信息，结果按学分降序排列
select * from kcxx
where 开课学期 = 5
order by 学分 desc
--8.分别汇总男女生人数
select 性别,count(*) as "人数"
from xsda
group by 性别
--9.求出XSDA表中各系的男、女生各自的总学分平均值
select 系名,性别,avg(总学分) as '平均分'
from xsda
group by 系名,性别
--10.求学号为200502的学生选修的课程中的最高分和最低分分别是多少
select 学号,max(成绩) as '最高分',min(成绩) as '最低分' from xscj
where 学号 = '200502'
group by 学号
--11.查找xscj表中选修三门课以上且平均分大于85，并且没有一门低于75的以平均分排名前三名的学生的学号
select top 3 学号
from xscj 
group by 学号 
having avg(成绩) > 85 and min(成绩) > 75 and count(*) >= 3

五、使用连接查询 or 子查询 实现查找

习题汇总

--1.查询选修了“计算机文化基础”但是未选修“数据结构”课程的学生的情况
select xsda.*
from xsda
where 学号 in
(select 学号 from xscj where 课程编号 in
(select 课程编号 from kcxx where 课程名称 = '计算机文化基础'))
and 学号 not in 
(select 学号 from xscj where 课程编号 in
(select 课程编号 from kcxx where 课程名称 = '数据结构'))
--2.查询选修了207号课程且分数大于该课程平均分以上的学生的学号、姓名、成绩
select xsda.学号,姓名,成绩
from xsda,xscj
where xscj.学号 = xsda.学号 and xscj.课程编号 = '207'
and 成绩 > (select avg(成绩) from xscj where 课程编号 = '207')
--3.找出大于所有女生年龄的学生信息
3.1 小于ALL女生出生日期
select *
from xsda
where 出生日期 < all(select 出生日期 from xsda where 性别 = '女')
3.2 建立子查询 小于 min(出生日期)
select *
from xsda
where 出生日期 < (select min(出生日期) from xsda where 性别 = '女')
--4.查找选修了104号课程成绩最高的学生的学号和成绩
select 学号,成绩
from xscj 
where  课程编号 = '104' 
and 成绩 = (select max(成绩) from xscj where 课程编号 = '104')
--5.查找选修了104号并且选修了108号课程的学生的学号、姓名和课程编号
select xsda.学号,姓名 
from xscj,xsda
where xsda.学号=xscj.学号 and 课程编号 in ('104','108')
group by xsda.学号,姓名
having count(*)=2
--6.查找选修了全部课程的学生的学号和姓名
select xsda.学号,姓名
from xsda,xscj
where xsda.学号 = xscj.学号 
group by xsda.学号,姓名
having count(*) = (select count(*) from kcxx)
--7.查询每门课程的最低分
select 课程名称, min(成绩) as 最低分
from xscj,kcxx
where xscj.课程编号 = kcxx.课程编号
group by 课程名称

六、T-SQL创建视图(view)

View跟表差不多，换汤不换药，把table改成view操作即可，时间有限，下次来补

更多推荐

T-SQL/TSQL语句复习,TSQL语句总结