SQLserver2012教程

admin3个月前网络资源117


在对应的表或库上单击右键,可以选择:编写表脚本为(s):可以省一些功夫,但学习的时候可以先不用,以后编写时可以使用提高编写的效率。


默认属性建库:CREATE DATABASE Manage

如果还需要进行设置存放位置、初值、次数据库文件等需要增加代码,例如:

CREATE DATABASE Manage

ON PRIMARY

(NAME=Manage_data,

 FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Manage_data.mdf',

 SIZE=5MB,

 MAXSIZE=UNLIMITED,

 FILEGROWTH=10%)

LOG ON

(NAME=Manage_log,

 FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Manage_log.ldf',

 MAXSIZE=100,

 FILEGROWTH=1MB)

GO 



ALTER DATABASE Manage

ADD FILEGROUP newfilegroup

GO


ALTER DATABASE Manage

ADD FILE

(NAME=Manage_add,

 FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Manage_add.ndf',

 SIZE=3MB,

 MAXSIZE=UNLIMITED,

 FILEGROWTH=10)

TO FILEGROUP newfilegroup

GO




建表(可以同时进行约束,如果建表时没有约束,可以通过修改追加) 例如:

USE Manage

GO

CREATE TABLE Buyers1

( BuyerID int PRIMARY KEY,

  BuyerName varchar(20) NOT NULL,

  BuyerSex char(2) NOT NULL,

  Address varchar(50),

  PhoneCode varchar(20),

  Birthday date

)


USE Manage

GO

CREATE TABLE wares

( warename  varchar(20) PRIMARY KEY,

  stock int ,

supplier varchar(50),

 status bit,

  unitprice money

)


USE Manage

GO

CREATE TABLE Sales1

( SaleID int identity(1,1) PRIMARY KEY,

  WareName varchar(20) FOREIGN KEY(WareName) REFERENCES Wares(WareName),

  BuyerID int REFERENCES Buyers1( BuyerID),

  Quantity int CONSTRAINT ck_Sales1 CHECK(Quantity>0),

  Amount int NULL,

  SaleTime datetime NULL DEFAULT getdate()

)



修改:4.8其中主键已设置不能再设置了。【任务4.8】对于这个代码要注意的是BuyerID在前面已经是主键,如果还要指定就会报警,可以去掉ADD CONSTRAINT pk_Buyers1_BuyerID PRIMARY KEY(BuyerID),这一句。主键设定后不要在后继进行改动和删除,会造成许多关联的问题。例如要想删除表wares,必须要先删除sales1表,因为后表引用关联了表wares.因此它不能先删除。修改时有时要注意有些设定了主键或相关联约束的,许多时候要按创建时的顺序进行梳理好,进行改变。这就告诉我们,进行库建立时要思考好每个字段(列),是否设定为主键和唯一的标识。


USE Manage

GO

ALTER TABLE Buyers1

ADD EMail varchar(30) NOT NULL DEFAULT ' ' CHECK(EMail LIKE '%@%') 

GO


USE Manage

go

ALTER TABLE Buyers1

add CONSTRAINT df_Buyers1_BuyerSex DEFAULT '男' FOR BuyerSex,

CONSTRAINT ck_Buyers1_BuyerSex CHECK(BuyerSex in('男','女'))

go


USE Manage

GO

ALTER TABLE Buyers1

ALTER COLUMN PhoneCode varchar(30)

Go


USE Manage

GO

ALTER TABLE Buyers1

ADD CONSTRAINT un_Buyers1_PhoneCode UNIQUE(PhoneCode)

GO



【任务4.9】如果创建时没有设置约束,此命令执行时是错误的。

USE Manage

GO

ALTER TABLE Buyers1

DROP CONSTRAINT df_Buyers1_BuyerSex

GO

ALTER TABLE Buyers1

NOCHECK CONSTRAINT ck_Buyers1_BuyerSex

GO



【任务4.10】查看对应的表

USE Manage

GO

SP_HELP Sales1

GO



建立一个表sales2的方式改以上代码如下,其实就是把一行分成了两行,建立字段与约束分开写:

USE Manage

GO

CREATE TABLE Sales2

( SaleID int identity(1,1),

  WareName varchar(20),

  BuyerID int,

  Quantity int,

  Amount int NULL,

  SaleTime datetime NULL DEFAULT getdate(),

  CONSTRAINT pk_Sales2 PRIMARY KEY(SaleID),

  CONSTRAINT fk_Sales2_G FOREIGN KEY(WareName) REFERENCES Wares(WareName),

  CONSTRAINT fk_Sales2_C FOREIGN KEY(BuyerID) REFERENCES Buyers1(BuyerID),

  CONSTRAINT ck_Sales2 CHECK(Quantity>0)

)


【任务4.11】删除对应的表。

USE Manage

GO

DROP TABLE Sales2

GO



录入数据的顺序和建立表的时候一样,并且数据与字段的顺序要相互对应 :insert into 使用的频率也是很高的,插入数据最简单的方式不过就是这样了。

表Buyers1:

USE Manage

GO

INSERT INTO Buyers1

(BuyerID, BuyerName, BuyerSex, Address, PhoneCode, Birthday, EMail)

VALUES(17, '姜萌萌', '女', '济南天桥区31号', '18936456456', '1988-02-11', 'jmm@163.com')

INSERT INTO Buyers1

VALUES(1, '李红', '男', '重庆电子学院', '98653621', '1968-04-06','gzht@163.com' )

INSERT INTO Buyers1

VALUES(18, '刘志山', '男', '北京23451信箱', NULL, '1980-07-23', 'lzs@163.com')

INSERT INTO Buyers1

VALUES(2, '孙玉强', '男', '北京市南京路1号','010-215436321', '1973-05-06', 'syq@163.com')

INSERT INTO Buyers1

VALUES(3, '王硕', '男', '郑州市花园路1号','0371-6325632', '1975-07-06', 'wansuo@163.com')

INSERT INTO Buyers1

VALUES(4, '陈晓东', '男', '上海市北方公司','96525421', '1963-02-03', 'cxd@163.com')

INSERT INTO Buyers1

VALUES(5, '何海红', '男', '广州市白云机场','020-9586585', '1965-09-09', 'hhh@163.com')

INSERT INTO Buyers1

VALUES(6, '赵虹', '男', '深圳市罗湖区','25854255', '1975-05-21', 'zh@163.com')

INSERT INTO Buyers1

VALUES(7, '刘鹏', '男', '天津电器五厂','13509872518', '1988-02-19', 'lp@163.com')

INSERT INTO Buyers1

VALUES(8, '张思涵', '女', '青岛四方区95号','053247869577', '1990-09-30', 'zsh@163.com')

INSERT INTO Buyers1

VALUES(9, '周晓娅', '女', '济南历城区旅游路3号','13784756132', '1973-05-23', 'zxy@163.com')

INSERT INTO Buyers1

VALUES(10, '李红', '女', '烟台芝罘区2431号','05357583729', '1987-08-14', 'lh@163.com')

INSERT INTO Buyers1

VALUES(11, '陈玉卿', '女', '北京2361信箱','13395847284', '1968-12-09', 'cyq@163.com')

INSERT INTO Buyers1

VALUES(12, '王兰', '女', '济南市电机二厂','053193846352', '1985-11-23', 'wl@163.com')

INSERT INTO Buyers1

VALUES(13, '王硕', '男', '山东商业职业技术学院','053186332281', '1988-05-19', 'ws@163.com')

INSERT INTO Buyers1

VALUES(14, '陈晓东', '男', '临沂北方电子信息公司','13593726173', '1979-07-29', 'cxd@163.com')

INSERT INTO Buyers1

VALUES(15, '刘晓', '男', '广州新世界批发公司','02073621837', '1971-04-05', 'lx@163.com')

INSERT INTO Buyers1

VALUES(16, '李梅', '女', '北京东方计算机公司','01064758372', '1988-03-11', 'lm@163.com')

GO



表WARES:

USE Manage

GO

INSERT INTO wares

(warename,stock,supplier,status,unitprice)

VALUES('pen',10000, '义乌小商品批发城',0,1)

INSERT INTO wares

VALUES('pencil',15000, '义乌小商品批发城',0,0.1)

INSERT INTO wares

VALUES('desk',2000, '济南光明家具经销公司',0,150)

INSERT INTO wares

VALUES('chair',5000, '济南光明家具经销公司',0,80)

INSERT INTO wares

VALUES('book',3500, '济南光明家具经销公司',0,29)

INSERT INTO wares

VALUES('projector',200, '济南电子商品科技市场',0,2580)

INSERT INTO wares

VALUES('ballpen',20000, '义乌小商品批发城',1,0.5)

INSERT INTO wares

VALUES('camera',1000, '济南电子商品科技市场',0,260)

INSERT INTO wares

VALUES('earphone',2000, '济南电子商品科技市场',1,35)

INSERT INTO wares

VALUES('speakerbox',800, '济南电子商品科技市场',0,130)

GO



表SALES:

USE Manage

GO

INSERT INTO Sales1

(saleid,warename,buyerid,quantity,amount,SaleTime)

VALUES(1,'pen',1,100,100,'2011-03-15')

INSERT INTO Sales1

VALUES(2,'pencil',2,2000,200,'2011-03-23')

INSERT INTO Sales1

VALUES(3,'pen',4,1500,1500,'2011-04-09')

INSERT INTO Sales1

VALUES(4,'projector',1,1,2580,'2011-04-21')

INSERT INTO Sales1

VALUES(5,'chair',5,100,8000,'2011-05-12')

INSERT INTO Sales1

VALUES(6,'book',2,100,2900,'2011-06-27')

INSERT INTO Sales1

VALUES(7,'pencil',1,1000,100,'2011-07-02')

INSERT INTO Sales1

VALUES(8,'speakerbox',3,2,260,'2011-07-09')

INSERT INTO Sales1

VALUES(9,'pen',8,500,500,'2011-07-11')

INSERT INTO Sales1

VALUES(10,'chair',7,10,800,'2011-07-28')

go


如果:在利用代码录入数据时,由于标识限制的问题无法录入,可以先取消。

例如可以重新创建表时不设置:SaleID int identity(1,1) PRIMARY KEY

USE Manage

GO

CREATE TABLE Sales1

( SaleID int  PRIMARY KEY,

  WareName varchar(20) FOREIGN KEY(WareName) REFERENCES Wares(WareName),

  BuyerID int REFERENCES Buyers1( BuyerID),

  Quantity int CONSTRAINT ck_Sales1 CHECK(Quantity>0),

  Amount int NULL,

  SaleTime datetime NULL DEFAULT getdate()

)


或者要先用:IDENTITY_INSERT开关功能选项开启(设置为ON状态),即需在执行插入语句之前先执行如下语句。

SET IDENTITY_INSERT表名ON


在执行完INSERT语句后,再执行下面的一条语句。但许多时候是无法实现的。

SET IDENTITY_INSERT表名OFF


或者不进行改变,将输入代码省略:saleid,这是系统自动生成的,不能由用户指定!

USE Manage

GO

INSERT INTO Sales1

(warename,buyerid,quantity,amount,SaleTime)

VALUES('pen',1,100,100,'2011-03-15')

INSERT INTO Sales1

VALUES('pencil',2,2000,200,'2011-03-23')

INSERT INTO Sales1

VALUES('pen',4,1500,1500,'2011-04-09')

INSERT INTO Sales1

VALUES('projector',1,1,2580,'2011-04-21')

INSERT INTO Sales1

VALUES('chair',5,100,8000,'2011-05-12')

INSERT INTO Sales1

VALUES('book',2,100,2900,'2011-06-27')

INSERT INTO Sales1

VALUES('pencil',1,1000,100,'2011-07-02')

INSERT INTO Sales1

VALUES('speakerbox',3,2,260,'2011-07-09')

INSERT INTO Sales1

VALUES('pen',8,500,500,'2011-07-11')

INSERT INTO Sales1

VALUES('chair',7,10,800,'2011-07-28')

go




update更新语句

update 用于修改表中的数据:

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值


实例: 

--更新某一行中的一个列

select *from teacher;

update teacher set  name = 'bb3' where ID = 3;  --teachaer中ID 等于3的name 改为bb3

select * from teacher;

--更新某一行中的若干列

select *from teacher;

update teacher set  name = 'cc7 ' , salary = 90000 where  ID = 7;  --teachaer中ID 等于7的name 改为cc7,salary改为9000

select * from teacher;

delete删除语句

delete用于删除表中的行:

DELETE FROM 表名称 WHERE 列名称 = 值

实例: 

delete from teacher where ID =1;  --删除表中ID 为1 的行

delete from teacher;  --删除表中所有行



【任务5.5】P89

USE Manage

GO

UPDATE Buyers1 

SET BuyerSex=DEFAULT, PhoneCode='053186635512' 

WHERE BuyerID=17



【任务5.6】但主表中被从表引用的记录信息不能被删除,当然如果一定要删除可以将外键断开,然后再重新添加外键约束。这个操作其实是不好的,因为与表sales1表有关联,单向的把主表中的删除了从表并不会改变,正确的是先要找到从表中的对应记录先删除,再删除主表中的对应记录。如果删除了外键,还可以通过在键上单击右键选择新建外键去添加,利用集成环境可以操作回来。

USE Manage

GO

DELETE Buyers1 WHERE Address LIKE'%北京%'

GO


USE Manage

GO


DELETE sales1 WHERE buyerid in (2,11,16,18)

GO

删除sales表中地址为北京的客户订货记录:??????????要先删除SALES1表才能执行

use manage

go

delete Buyers1 where buyers1.Address like '%北京%'

go


!!!正确的做法是:

这要分两步做:第一步,先找到buyers1表中关于北京的返回编号与sales1表中的编号相同的记录删除,然后再删除buyers1表中关于北京的记录,代码如下:P92第7问

USE Manage

GO

DELETE Sales1 FROM Sales1,buyers1 WHERE  ((buyers1.address like '%北京%')and  buyers1.buyerid=sales1.buyerid)

DELETE buyers1 FROM buyers1 WHERE  address like '%北京%'

 go




【任务5.7】

USE Manage

GO

TRUNCATE TABLE Buyers1

GO



P92页第3问:

将WARES表中的货品库存量大于100的货品降价10%。代码如下:

use manage 

go

update wares

set unitprice=unitprice*0.9  where stock>100


P92页第4问:

把sales表中的订货金额用该货品在wares表中的货品单价与在sales表中的订货数量的乘积代替,并显示修改后的记录。参考代码如下:必须找一个共同的字段才能确保是同一个货品的运算。这时主键与外键的作用就体现出作用了。

use manage

go

SELECT Sales1.Quantity,wares.unitprice,Sales1.Quantity*wares.unitprice AS amount FROM Sales1 JOIN wares ON Sales1.warename=wares.warename

go


删除wares表中的未知定价的记录:先输入两行数据,unitprice列输入0或负数

use manage

go

delete wares where unitprice=' '

go


use manage

go

delete wares where unitprice<=0

go


删除wares表中的记录:要先断开相对应的外键,或者先删除SALES1表才能执行。

use manage

go

truncate table wares

go


删除wares表中库存状态status为true的记录:(true 1,false 0)

use manage

go

delete wares where status=1

go




删除sales表中的所有订单:

use manage

go

delete Sales1 where  quantity>0

go



use manage

go

truncate table sales1

go





DELETE删除多表数据,怎样才能同时删除多个关联表的数据呢?这里做了深入的解释:

1、 delete from t1 where 条件

2、delete t1 from t1 where 条件

3、 delete t1 from t1,t2 where 条件

4、delete t1,t2 from t1,t2 where 条件

 前 3者是可行的,第4者不可行。

也就是简单用delete语句无法进行多表删除数据操作,不过可以建立级联删除,在两个表之间建立级联删除关系,则可以实现删除一个表的数据时,同时删除另一个表中相关的数据。

1、从数据表t1中把那些id值在数据表t2里有匹配的记录全删除 掉

 DELETE t1 FROM t1,t2 WHERE t1.id=t2.id 或 DELETE FROM t1 USING t1,t2 WHERE t1.id=t2.id

2、从数据表t1里在数据表t2里没有匹配的记录查找出来并删除掉

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL 或 DELETE FROM t1,USING t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL

3、 从两个表中找出相同记录的数据并把两个表中的数据都删除掉

DELETE t1,t2 from t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t1.id=25

注意此处的delete t1,t2 from 中的t1,t2不能是别名

如:delete t1,t2 from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25 在数据里面执行是错误的(MYSQL 版本不小于5.0在5.0中是可以的)

上述语句改 写成

delete table_name,table2_name from table_name as t1 left join table2_name as t2 on t1.id=t2.id where table_name.id=25




6.1  SELECT语句的基本语法格式

SQL 对大小写不敏感!

select语句基本使用(包括where、and/or 、order by 、top、like、between、通配符)

select语句在数据库使用的频率应该是很高的了吧,反正我在学习的时候一天不敲几百遍就感觉自己没学啥一样,可见这个语句的重要性。

select基本语法:

select 列名称 from 表名称

--或者

select * from  表名称




查询通过SELECT语句来实现,SELECT语句的基本语法格式如下。

SELECT列名1,列名2, … , 列名n

[INTO新表名]

[FROM表名1,表名2, … , 表名n]

[WHERE条件表达式]

[GROUP BY列名1,列名2, … , 列名n]

[HAVING条件表达式]

[ORDER BY列名1 [ASC | DESC], …, 列名n [ASC | DESC]]

[COMPUTE统计函数[,…n] [BY分类表达式]]


where----有条件的从表中选取数据

and 和 or --- 基于一个以上的条件对记录进行过滤

order by ---对结果进行排序(默认是升序),若在语句后面添加desc,则是降序

top ---规定要返回记录的数目(可以是返回的具体数目 , 也可以是百分比)

like ---在where子句中搜索列的指定模式

between---在where子句中使用 ,选取介于两者之间的数据

select distinct---句用于返回唯一不同的值

通配符(%、_ 、[charlist] 、[^charlist]/[!charlist] )---可替代一个或多个字符,必须与like一起使用


下面我给出code实例:

select * from teacher;   --查找所有表元素


--where 

select *  from  teacher where ID> 3  --查找ID>3的列


--and 、or

select * from teacher where ID>3 and salary <>6000   --查找ID>3  并且 salary != 6000的列

select * from teacher where ID> 3 or salary < 6000   --查找ID>3 或者 salary<6000的列


--order by 

select * from teacher order by salary; --按salary进行排序,升序

select * from teacher order by salary desc ;  --按salary进行排序,降序


--top

select top 2 * from teacher;  --查找前2行

select top 20 percent * from teacher;  --查找前20%


--like 通配符一块使用

select * from teacher  where  name like 'a%';  --查找name是以a开头的所有列

select * from teacher where name like '%b';  --查找name是以b结尾的所有列

select * from teacher where  name  like 'a_';  --查找name是以a开头后面只有一个字符 的所有列

select * from teacher where name like '[ac]%';  --  查找name是以a/c开头的所有列

select * from teacher  where name like '[^ac]%'; --查找name 不是以a/c开头的所有列


--between   and

select * from teacher where salary between 5000 and  8000;  --查找salary介于[5000,8000]的所有列


--select distinct

select distinct salary  from teacher ;  -- 找出teacher表中salary不一样的列



6.2  单表的简单查询

6.2.1  使用SELECT选取字段


【任务6.1】

USE Manage

GO

SELECT * FROM Buyers1

GO


【任务6.2】

USE Manage

GO

SELECT BuyerID,BuyerName,BuyerSex FROM Buyers1

GO


为结果集的列指定名称,可以使用以下两种格式。

格式一:

SELECT列别名=列原名FROM数据源

格式二:

SELECT列原名[AS]列别名FROM数据源


【任务6.3】

USE Manage

GO

SELECT客户编号=BuyerID,BuyerName AS客户姓名,BuyerSex性别

FROM Buyers1

GO

应当改为:更容易理解。

USE Manage

GO

SELECT BuyerID as 客户编号,BuyerName AS 客户姓名,BuyerSex as 性别

FROM Buyers1

GO 



【任务6.4】

USE Manage

GO

SELECT DISTINCT WareName FROM Sales1

GO


【任务6.5】显示前5条记录:

USE Manage

GO

SELECT TOP 5 * FROM Sales1

GO


从数据库表中随机获取N(如3条)条记录的SQL语句:

use Manage

go

select top 3 * from sales1 order by newid()

go


从数据库表中查询从最后N(如3条)条记录的SQL语句:首先对整个表的记录进行计数,其中的n充当一个随机变量的作用,然后倒着进行显示。

use Manage

go

select *from Sales1

where 3 > (select count(*) from Sales1 n where Sales1.saleid <n.SaleID) 

go

 或者用以下代码:查询表最后   n(假设5条)   条记录的SQL语句:默认的编号是升序排列,先将编号降序排列然后输出前几个

use Manage

go

select   top  5* from   Sales1   order   by   SaleID   desc

go


6.2.2  使用INTO子句


【任务6.6】生成一个结构一样的新表,但是约束和数据都是没有的。条件为假时是空,如果指定条件会把符合条件的数据一起生成。

USE Manage

GO

SELECT * INTO 新表名称 FROM Buyers1 WHERE 1=2

GO

SELECT * FROM 新表名称

GO


USE Manage

GO

SELECT * INTO nnn FROM Buyers1 WHERE 1=2

GO

SELECT * FROM nnn

GO


将所有性别为女的数据一起生成:

USE Manage

GO

SELECT * INTO a FROM Buyers1 WHERE buyersex='女'

GO

SELECT * FROM a

GO

如果全部数据一起生成,则条件表达式要为真,例如1=1;1-1=0;之类的表达式均可。

USE Manage

GO

SELECT * INTO b FROM Buyers1 WHERE 1=1

GO

SELECT * FROM b

GO




6.2.3  使用WHERE子句


【任务6.7】

USE Manage

GO

SELECT * FROM Sales1 WHERE Quantity>20

GO


范围运算符用来判断列值是否在指定范围内。该运算符的基本语法格式如下。

测试表达式[NOT] BETWEEN起始值AND终止值


【任务6.8】范围之间,类似于n在10<=n<=100,如果是这个范围之外则在前面加上NOT

USE Manage

GO

SELECT * FROM Sales1  WHERE Quantity BETWEEN 10 AND 100

GO

如果是这个范围之外则在前面加上NOT

USE Manage

GO

SELECT * FROM Sales1  WHERE Quantity  not BETWEEN 10 AND 1000

GO

列表运算符用来判断列值是否在所给定的集合中。该运算符的基本语法格式如下。

测试表达式[NOT] IN(列值1, …, 列值n)


【任务6.9】 IN在什么集合中

USE Manage

GO

SELECT * FROM Wares WHERE WareName IN('pen', 'desk', 'book')

GO


或NOT IN不在所给的范围中

USE Manage

GO

SELECT * FROM Wares WHERE WareName  not IN('pen', 'desk', 'book')

GO


模式匹配语句的基本语法格式如下。

测试表达式[NOT] LIKE'通配符'


【任务6.10】通配符的不同结果:

USE Manage

GO

SELECT * FROM Wares WHERE WareName LIKE '_e%'

GO


USE Manage

GO

SELECT * FROM Wares WHERE WareName LIKE '_e_'

GO


USE Manage

GO

SELECT * FROM Wares WHERE WareName LIKE '%e%'

GO



空值运算基本语法格式如下。

测试表达式IS [NOT] NULL


【任务6.11】

USE Manage

GO

SELECT * FROM Wares WHERE Supplier IS NOT NULL

GO


逻辑运算符语法格式如下。

①逻辑表达式1 AND | OR 逻辑表达式2     ②NOT逻辑表达式


【任务6.12】:如果是男,则重庆有一个,女的重庆没有。

USE Manage

GO

SELECT * FROM Buyers1 WHERE BuyerSex='女' AND Address LIKE '%重庆%'

GO

济南的有:

USE Manage

GO

SELECT * FROM Buyers1 WHERE BuyerSex='女' AND Address LIKE '%济南%'

GO


6.2.4  使用ORDER BY子句


ORDER BY子句的语法格式如下。

ORDER BY {列名[ASC | DESC]}[,…n]


【任务6.13】先按名称的英文字母升序排序,然后同名后再按时间进行降序。ASC是升序

USE Manage

GO

SELECT * FROM Sales1 ORDER BY WareName, SaleTime DESC

GO


ASC是升序:

USE Manage

GO

SELECT * FROM Sales1 ORDER BY WareName, Quantity asc

GO



6.3  统计

6.3.1  使用聚合函数


【任务6.14】

USE Manage

GO

SELECT COUNT(*) 订购记录数, SUM(Quantity) 订货总数量, AVG(Amount) 平均订货金额, Min(SaleTime) 最早订货时间, Max(SaleTime) 最晚订货时间 FROM Sales1

GO



USE Manage

GO

SELECT COUNT(*) 订购记录数, SUM(Quantity) 订货总数量, AVG(Amount) 平均订货金额, Min(SaleTime) 最早订货时间, Max(SaleTime) 最晚订货时间 FROM Sales1

GO



6.3.2  使用GROUP BY子句


GROUP BY子句用于对结果集进行分组并对每一组数据进行汇总计算。基本语法格式如下。

GROUP BY列名[HAVING条件表达式]


【任务6.15】按什么分类:如按名称分类

USE Manage

GO

SELECT WareName, SUM(Quantity) 订货总数量, SUM(Amount) 订货总金额

FROM Sales1

GROUP BY WareName

GO


【任务6.16】

USE Manage

GO

SELECT WareName, SUM(Quantity) 订货总数量, SUM(Amount) 订货总金额

FROM Sales1

GROUP BY WareName HAVING SUM(Quantity)>50

GO





先要完成书上107-108页内容再学习第7章内容:












7.1  指定数据源

7.1.1  使用交叉连接


交叉连接有两种语法格式。

格式一:

SELECT列名列表FROM表名1 CROSS JOIN表名2

格式二:

SELECT列名列表FROM表名1, 表名2



7.1.2  使用内连接


内连接有两种语法格式。

格式一:

SELECT列名列表FROM表名1 [INNER] JOIN表名2 ON连接条件表达式

格式二:

SELECT列名列表FROM表名1, 表名2 WHERE连接条件表达式


【任务7.1】

USE Manage

GO

SELECT BuyerName, PhoneCode, WareName, Quantity, Amount

FROM Buyers1 INNER JOIN Sales1

ON Buyers1.BuyerID=Sales1.BuyerID

GO

或:

USE Manage

GO

SELECT BuyerName, PhoneCode, WareName, Quantity, Amount

FROM Buyers1 AS B, Sales1 AS S

WHERE B.BuyerID=S. BuyerID

GO



7.1.3  使用外连接


左外连接的语法格式如下。

SELECT列名列表FROM表名1 LEFT [OUTER] JOIN表名2

ON连接条件表达式


【任务7.2】

USE Manage

GO

SELECT BuyerName, PhoneCode, WareName, Quantity, Amount

FROM Buyers1 LEFT OUTER JOIN Sales1

ON Buyers1.BuyerID=Sales1.BuyerID

GO


右外连接的语法格式如下。

SELECT列名列表FROM表名1 RIGHT [OUTER] JOIN表名2

ON连接条件表达式


【任务7.3】

USE Manage

GO

SELECT BuyerName, PhoneCode, WareName, Quantity, Amount

FROM Sales1 RIGHT OUTER JOIN Buyers1

ON Buyers1.BuyerID=Sales1.BuyerID

GO


全外连接的语法格式如下。

SELECT列名列表FROM表名1 FULL [OUTER] JOIN表名2

ON连接条件表达式



7.1.4  使用自连接


【任务7.4】

USE Manage

GO

SELECT DISTINCT S1.BuyerID, S1.WareName

FROM Sales1 S1, Sales1 S2

WHERE S1.BuyerID=S2.BuyerID AND S1.WareName<>S2.WareName

GO


7.1.5  合并结果集


实现联合查询的语法格式如下。

SELECT语句1 {[UNION [ALL] | INTERSECT | EXCEPT] SELECT语句2} [,…n]


【任务7.5】

USE Manage

GO

SELECT * FROM Buyers1 WHERE Address LIKE '%北京%'

UNION

SELECT * FROM Buyers1 WHERE Address LIKE '%重庆%'

GO


【任务7.6】

USE Manage

GO 

SELECT * FROM Buyers1 WHERE BuyerSex='男'

INTERSECT

SELECT * FROM Buyers1 WHERE Address LIKE '%北京%'

GO


【任务7.7】

USE Manage

GO 

SELECT * FROM Buyers1 WHERE BuyerSex='男'

EXCEPT

SELECT * FROM Buyers1 WHERE Address LIKE '%北京%'

GO



7.2  子查询

7.2.1  使用子查询进行比较测试


【任务7.8】

USE Manage

GO

SELECT * FROM Wares WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Wares)

GO



7.2.2  使用子查询进行集成员测试


【任务7.9】

USE Manage

GO

SELECT * FROM Buyers1 WHERE BuyerID NOT IN(SELECT BuyerID FROM Sales1)

GO



7.2.3  使用子查询进行存在性测试


【任务7.10】

USE Manage

GO

SELECT * FROM Buyers1 WHERE NOT EXISTS(

SELECT * FROM Sales1 WHERE Buyers1. BuyerID=Sales1.BuyerID)

GO



7.2.4  使用子查询进行批量比较测试


使用ANY运算符进行批量比较测试的语法格式如下。

测试表达式 比较运算符ANY (子查询)


【任务7.11】

USE Manage

GO

SELECT * FROM Buyers1 WHERE 500>ANY

(SELECT Amount FROM Sales1 WHERE Buyers1. BuyerID=Sales1.BuyerID)

GO


使用ALL运算符进行批量比较测试的语法格式如下。

测试表达式 比较运算符ALL (子查询)


【任务7.12】

USE Manage

GO

SELECT * FROM Wares WHERE UnitPrice >= ALL(SELECT UnitPrice FROM Wares)

GO

或:

USE Manage

GO

SELECT * FROM Wares WHERE UnitPrice >= (SELECT MAX(UnitPrice) FROM Wares)

GO



标签: SQL数据库
返回列表

上一篇:用户须知

下一篇:JAVA复习题

相关文章

《网页设计与制作》理论题

《网页设计与制作》理论题

一、判断题第一单元    √1.目前,在Internet上应用最为广泛的服务是WWW服务。(      ) &...

本站出售大量低价域名

本站出售大量低价域名

552300.cnccTLD 正常 未分组 2021-04-03 10:41:51 2022-04-03 10:41:51 续费|解析|安全锁|管理webserver.vipNew gTLD 正常 未...

JAVA复习题

JAVA复习题

一、单项选择题 1. 若定义有变量float a,b=6.5F,则下列说法正确的是(  )。 A.变量a,b均被初始化为6.5B.变量a没有被初始化,b被初始化为6.5C...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。