SQL语句与Mysql的语句大体上比较相似。以下是sql server的一套练习题,是很好的数据库操作语句学习资料,学校的学习资料,在此整理了以下。
数据库exam:这是一个模拟电子商务,网上直销的数据,主要销售计算机配件。该数据库包含了订单数据、经销商基本数据、产品基本数据以及供应商基本数据等。该数据库共包含6个表,表名分别为:orders、distributors、suppliers、city、items和products。各个表的内容与格式如下:
表1 网上直销订单表格,表名为:orders
列名 | 描述 | 数据类型 |
OrderNum | 订单号码 | Int |
OrderDate | 订购日期 | Datatime |
DistriNum | 经销商号码 | Int |
PaidDate | 付款日期 | Datatime |
表2 经销商表格,表名为:distributors
列名 | 描述 | 数据类型 |
DistriNum | 经销商号码 | Int |
BossName | 经理名称 | Char |
Company | 公司名称 | Char |
CityCode | 所属市区代码 | Char |
Address | 公司地址 | Char |
Phone | 公司电话号码 | Char |
表3 供应商表格,表名为:suppliers
列名 | 描述 | 数据类型 |
SuppCode | 供应商代码 | Char |
SuppName | 供应商名称 | Char |
表4 市区表格,表名为:city
列名 | 描述 | 数据类型 |
CityCode | 市区代码 | Char |
CityName | 市区名称 | Char |
表5 订单项目表格,表名:items
列名 | 描述 | 数据类型 |
Item | 订单项目号码 | Int |
OrderNum | 订单号码 | Int |
ProdNum | 产品号码 | Int |
SuppCode | 供应商代码 | Char |
Quantity | 单项定购数量 | Int |
TotalPrice | 单项总价 | Money |
表6 产品表格,表名:products
列名 | 描述 | 数据类型 |
ProdNum | 产品号码 | Int |
SuppCode | 供应商 | Char |
Descriptions | 产品名称 | Char |
UnitPrice | 产品单价 | Money |
一、数据表的简单操作
用SQL语句选取表中所有的列:
1、 查询exam数据库中city表中所有的列的内容
select * from city;
2、 查询exam数据库中orders表OrderDate列的内容
select OrderDate from orders;
选取惟一的数值:
消除重复关键字:distinct (放在select后面)
1、 查询exam数据库中orders表中的订购日期OrderDate,并且相同的订购日期只显示一次(distinct)
select distinct OrderDate from orders;
2、对记录进行操作——选取特定的记录:
查询exam数据库中供应商代码为HHT的供应商的实际名称
select SuppName from city where SuppCode= "HHT";
3、对记录进行操作——排除特定的记录:
查询exam数据库中供应厂代码不为HHT的所有供应商的实际名称
select SuppName from city where SuppCode!= "HHT";
3、对数据进行操作——选取介于某一范围的数据:
查询exam数据库的products表中所有单价不超过20元的产品的名称及其对应的价格
select Descriptions,UnitPrice from products where UnitPrice <= 20;
4、对数据进行操作——选取属于某一子集合的数据:
in:在...里
查询exam数据库中经销商号码为1006、1009、1013、1014、1017的经理名称以及其所在的公司名称
select DistriNum,BossName,Company from distributors where DistriNum in (1006,1009,1013,1014,1017);
5、对字符串进行操作——选取子字符串:
查询exam数据库中所有经销商的电话区号
select substring(Phone,1,3) from distritubors; //第一位和从这一位开始几位
查询exam数据库中所有经销商的姓
select substring(BossName,1,1) from distributors;
6、对字符串进行操作——搜索子字符串:
‘%’:表示N个任意字符
查询exam数据库中所有经理姓“王”的公司的名称、电话以及公司地址
select BossName,Company,Phone,Address from distributors where BossName like '王%';
二、一般数据查询与处理
1、同时执行多个SQL语句:
实现在一次查询中同时查询exam数据库中orders表和distributors表所有列的内容
select * from orders; go select * from distributors; go
2、用order by子句对数据进行排序:
查询exam数据库products表格中所有的记录,查询结果根据单价由小到大的顺序(降序)排序
select * from products order by UnitPrice desc;
3、使用算术运算符:
查询exam数据库products表中的所有记录,并且把每个产品单价显示成调高10%后的价格(只显示,数据库不变)
select ProNum,SuppCode,description,UnitPrice * 1.1from products;
3、 查询exam数据库products表中的所有记录,并且把每个产品单价显示成8折后的价格
select ProNum,SuppCode,description,UnitPrice * 0.8 from products;
5、搜索单一长度的任意字符串:
‘_’:表示一个任意字符
查询exam数据库suppliers表中供应商代码的第1、3位分别为H和T的所有供应商的记录
select * from suppliers where SuppCode like "H_T";
6、使用常用的字符串函数:
查询exam数据库distribuotrs表中经销商号码为1001的地址字符串的长度
select len(Address) from distributors where DistriNum = "1001";
7、查询字符串的长度
select len(‘中华人民共和国’);
8、将字符串‘I LOVE SQL SERVER‘的所有字母转换成小写字母
select LOWER('I LOVE SQL SERVER');
9、将字符串‘abcdefg’中的小写字母转换成大写字母
select UPPER('abcdefg');
10、将字符串‘ abcdefg’前的空格去掉
select ltrim(' abcdefg);
11、将字符串‘uvwxyz ’后的空格去掉
select rtrim('uvwxyz ');
12、将字符串‘我喜欢数据库’中的字符由左至右反转排列
select REVERSE('我喜欢数据库');
13、求出字符A的ASCII码
select ASCII('A');
14、判断数字5的正负号,如果正返回1,负返回-1
select SIGN(5);
15、使用常用的数学函数:
计算出以下三个数的绝对值:-2.5,0.0,7.4
select ABS(-2.5),ABS(0.0),ABS(7.4);
16、返回分别大于或等于以下数字的最小整数:5.5,-7.2,0.0
select floor(5.5),floor(-7.2),floor(0.0);
17、返回π(PI)的值
select PI();
18、返回630.67的近似值,并将该数分别四舍五入到1、-2、3、0长度的小数
select ROUND(630.67,1),ROUND(630.67,-2),ROUND(630.67,3),ROUND(630.67,0);
19、返回以下3个数的平方值:4、-6,6.6
select SQUARE(4),SQUARE(-6),SQUARE(6.6);
20、返回以下3个数的算数平方根:4,0,50
select SQRT(4),SQRT(0),SQRT(50);
21、计算Cos(3.1415926)的值 sin(3.14159260) ctg(3.14) tg(3.1415926)
select COS(3.1415926),SIN(3.14159260),COT(3.14),TAN(3.1415926);
22、计算2的自然对数、指数值
select LOG(2),EXP(2);
23、计算1000的以10为底的对数
select LOG10(1000);
24、计算2的10次方
select POWER(2,10);
三、常用函数
常用的集总函数的应用:
1、 查询exam数据库products表中共销售多少种产品
select COUNT(*) from products;
2、 查询exam数据库items表中代码为HSC的供应商已订货的总金额
select SuppCode,SUM(TotalPrice) from items where SuppCode = 'HSC';
3、 查询exam数据库products表中所有供应商所供应的货品的平均单价
select SuppCode,AVG(UnitPrice) from products group by SuppCode;
4、 查询exam数据库products表中每一个供应商产品的最高单价/最低单价
select SuppCode,MAX(UnitPrice),MIN(UnitPrice) from products group by SuppCode;
6、时间函数:
(1)获取当前时间
select getdate();
(2)、在1991-07-10 2:30:00.000时间上增加21天
select dateadd(d,21,’ 1991-07-10 2:30:00.000’);
(3)、返回1991-07-10 2:30:00.000五天前的时间
select DATEADD(D,-5,'1991-07-10 2:30:00.000');
(4)、返回两个日期间隔的时间
select DATEDIFF(D,'02/20/1999','02/28/1999');
(5)、以字符串形式返回当天几年
select DATENAME(Y,GETDATE());
(6)、以整数形式返回当天几号
select DATEPART(D,GETDATE());
(7)、查询当前UTC时间(协调时间)
select GETUTCDATE();
四、高级数据查询与处理
1、在一个select语句中使用多个表格:
查询exam数据库中每一个经销商所有的订单数据,列出经销商号码、公司名称、电话号码以及订货日期
select distributors.DistriNum,Company,Phone,OrderDate from distributors,orders where orders.DistriNum = distributors.DistriNum
2、在select语句中使用子查询:
查询exam数据库中与经理名称为‘王为’的经销商同在一个市区的所有经销商的数据内容
select * from distributors where CityCode=(select CityCode from distributors where BossName='王为');
3、用union运算符实现从不同的表格抽取相同格式的数据:
查询exam数据库中单价不大于25元或者是已订货项目中数量小于2的产品数据
select ProdNum,SuppCode,Description,UnitPrice from products where UnitPrice <= 25 union select ProdNum,SuppCode,Quantity from items where Quantity <2
4、添加记录:
在orders表中添加一笔记录,OrderNum为119,OrderDate为1905-06-8,DistriNum为1022,PaidDate为1905-06-9
insert into orders(OrderNum,OrderDate,DistriNum,PaidDate) values(119,1905-06-8,1022,1905-06-9);
5、删除记录:
删除orders表中OrderNum为119的记录
delete from orders where OrderNum = 119
6、更新记录:
将orders表中订单号为118的订单的订购日期改为2003-07-11
update orders set OrderDate = "2003-07-11" where OrderNum = 118
7、实现查询exam数据库中每一个经销商的订单号码(orders表中)与经销商公司所属市区的代码(ditributoirs表中)
select orders.DistriNum,OrderNum,distributors.CityCode from orders,distributors where orders.DistriNum = distributors.DistriNum
8、实现查询exam数据库中公司在宣武区的所有经销商的数据内容
select * from distributors where CityCode= (select CityCode from city where CityName='宣武区')
9、实现查询exam数据库中订单号码为100对应的经销商的情况
select * from distributors where DistriNum= (select DistriNum from orders where OderNum = 100)
五、设计数据库
用T-SQL语句实现默认值与列的结合:
默认值创建:create default 关键字 as XX
绑定默认值:sp_bindefault ‘默认值名’,’表名.[列名]’
重命名默认值:exec sp_rename ‘旧名称’,’新名称’
1.在exam数据库中新建名为moren的默认值,值为100,并把该默认值与orders表的OrderNum列结合(sp_bindefault)
create default moren as 100
go
sp_bindefault 'moren','orders.[OrderNum]'
go
sp_bindefault 将新默认值绑定到列(尽管最好使用 DEFAULT 约束)或者绑定到别名数据类型。原有默认值将被覆盖。
2、新建SQL查询,输入Transact-SQL语句,解除(sp_unbindefault)上步中建立的默认值与列的结合,并删除(drop)该默认值。
sp_unbindefault 'orders.[OrderNum]' go drop default moren go
3、建立新的用户自定义的数据类型:bigint,允许NULL值,类型名称为newtype,
sp_addtype newtype,'bigint','null'
4、建立新的名为newrule的规则(create rule newrule as @values),并把这个规则与orders表的OrderNum列结合(sp_bindrule)
create rule newrule as @value between 100 and 150
go
sp_bindrule 'newrule','orders.[OrderNum]'
go
5、解除上步中建立的规则与列的结合,并删除该规则。操作成功后,保存该查询
sp_unbindrule 'orders.[OrderNum]' go drop rule newrule go
6、建立新的名为newview的视图,建立orders表格中的OrderNum、OrderDate列的视图
create view newview
as
select OrderNum,OrderDate from orders go select * from newview
7、删除上步中建立的视图
drop view newview
六、存储过程和触发器
创建存储过程:
创建存储过程:create procedure 名称
执行存储过程:exec 名称
1、在exam数据库中新建名为storedproc的存储过程,用来查询orders表中OrderNum和OrderDate的列,并执行该存储过程
create procedure storedproc as select OrderNum,OrderDate from orders go exec storedproc go
2、重命名存储过程:
重命名为newproc
sp_rename storedproc,newproc
3、删除存储过程:
删除存储过程:drop proc 名称
drop procedure newproc
4、创建触发器:
create trigger 触发器名
on 表名
for XX ——创建XX操作触发器
建立名为newtrigger的触发器,规定当orders表的OrderNum列的数值发生变化的时候,显示出‘订单号码已被更改,触发器起到作用’,再更改orders表的OrderNum列的数值以触发该触发器
create trigger newtrigger
on orders
for update as if update(OrderNum)
print '订单号码已被更改,触发器起到作用。'
go
//例如
update orders set OrderNum='100' where OrderNum='101'
go
5、查看系统内所有的触发器列表:
显示出系统内全部的触发器
select * from sysobjects where XTYPE='tr'
七、数据库的管理和数据索引
增加数据库空间:
用企业管理器新建一个名为module7的数据库,将主要数据库文件和日志文件分别命名为module7_Data.MDF和module7_Log.LDF,大小都设置成1M(默认值)并将这两个文件存放在C盘的根目录下;打开查询分析器,加入名为modu2.ldf的文件,使得数据库module7的日志文件容量增大5M,此时该数据库显示的大小为7M
alter database module7 add file ( name=modu2, filename=’c:\modu2.ldf’, size=5, filegrowth=1 )
1、缩小数据库空间:
将上步建立的module7数据库的日志文件的大小缩小为原来的一半
dbcc shrinkdatabase(module7,50) //50为50%
2、删除数据库:
删除module7数据库
drop database module7
3、创建索引:
创建索引:create unique/clustered/nonclustered index 索引名 on 表名(列名)
唯一值/聚集索引/非聚集索引
建立名为theindex的索引,orders表OrderNum,唯一值
create unique index theindex on orders(OrderNum)
4、重命名索引:
将上步建立的索引重命名为newindex
exec sp_rename ‘orders.theindex’,’newindex’,’index’
5、删除索引:
删除建立的索引
drop index orders.newindex