SQL操作语句

朱雀 2021-03-27 11:46 1614阅读 0赞

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)