SQL学习记录彩色

1、视图操作方式 概念理论 索引从1开始第二章 数据库管理USE master-当前指向的操作的数据库GOIF EXISTS(SELECT * FROM sysdatabases WHERE name='E_Market')DROP DATABASE E_Market-删除数据库-创建数据库CREATE DATABASE E_MarketON PRIMARY -主文件组(NAME='E_Market_data',-逻辑文件名-物理文件名FILENAME='H:projectE_Market_data.mdf',SIZE=5MB,-初始大小MAXSIZ

2、E=100MB,-最大容量FILEGROWTH=15%-增长率),FILEGROUP FG -创建了一个次文件组(NAME='E_Market2_data',FILENAME='H:projectE_Market2_data.ndf',SIZE=4MB,FILEGROWTH=10%)LOG ON -日志文件(NAME='E_Market_log',FILENAME='H:projectE_Market_log.ldf',SIZE=5MB,FILEGROWTH=0-未启用自动增长),(NAME='E_Market2_log&

3、#39;,FILENAME='H:projectE_Market2_log.ldf',SIZE=3MB,FILEGROWTH=10%,MAXSIZE=100MB)GO第三章 数据库用户管理-1创建一个登录名pbMaster 密码 -创建登录名CREATE LOGIN pbMaster WITH PASSWORD='123456'-2创建数据库用户-MasterDBUser是数据库用户名, pbMaster是登录名CREATE USER MasterDBUser FOR LOGIN pbMaster-3赋予用户操作权限-为表UserInfo赋予查看，新增，修改的操

4、作权限MasterDBUser数据库用户GRANT SELECT,INSERT,UPDATE ON UserInfo TO MasterDBUser-4收回新增的操作权限REVOKE INSERT ON UserInfo TO MasterDBUser角色：事先定义好的一组权限数据库服务器方面的控制权限服务器角色创建、修改、删除数据库，管理磁盘文件，添加或删除数据库连接等等服务器级别的对象，只能包含登录名数据库所有者dbo出现在每个数据库中登录名 sa 自动映射为所有数据可用户dbo角色名描述服务器角色bulkadmin运行bulk语句，该语句可将文本文件内数据导入到SQL Server200

5、8 中dbcreator创建、更改、删除和还原任何数据库diskadmin管理磁盘文件processadmin管理SQL Server中运行的进程securityadmin管理和审核登录账户serveradmin设置服务器范围的配置setupadmin可以添加和删除连接服务器，并可执行某些系统存储过程sysadmin可以在服务器引擎中执行任何活动public初始状态是没有权限所有的数据库用户都是它的成员，因此不能将用户、组或角色指派为public角色的成员，也不能删除public角色的成员。数据库角色db_owner执行所有的配置活动和维护活动以及授予或取消数据库对象的select、inser

6、t、updatedb_accessadminWindows 登陆账户db_datareader读取数据库中所有用户表中的数据db_datawriter想来自数据库中所有用户表添加、更新和删除数据db_ddladmin运行任何数据库语言命令db_securityadmin管理市局哭的安全，可以修改角色成员身份和管理权限db_backupoperator可以备份数据库db_denydatareader不能参看数据库中用户表的任何数据db_denydataweiter不能像用户表中添加跟新或删除任何数据public初始状态是没有权限所有的数据库用户都是它的成员，因此不能将用户、组或角色指派为publ

7、ic角色的成员，也不能删除public角色的成员。第四章 数据库维护-1使用sql语句来查看数据的状态SELECT state_desc FROM sys.databases WHERE name='E_Market'-2使用函数来查看数据库的状态SELECT DATABASEPROPERTYEX('Library','status') 数据库名称，状态-3将数据库E_Market设为脱机状态 对象资源管理器单击数据库任务脱机 脱机后可对数据库文件复制-4将数据库E_Market设为联机状态对象资源管理器单击数据库任务联机 联机可操作查询，不能操

8、作数据库-分离数据库，数据库 对应数据库节点任务分离EXEC sp_detach_db dbname=E_Market -附加数据库 中数据库节点附加选择主数据库路径及对应文件EXEC sp_attach_db dbname=E_Market, filename1='H:projectE_Market_data.mdf',filename2='H:projectE_Market2_data.ndf',filename3='H:projectE_Market_log.ldf',filename4='H:projectE_Market2_lo

9、g.ldf'脱机：与数据库服务器断开连接，但在数据库节点上还存在脱机的数据库名称分离：将数据库从服务器上分离出去，数据库不存在，只存在对应的数据文件和日志文件相同点：都可以对数据库文件进行复制等操作收缩数据库：删除数据库的每个文件中已经分配但还没有使用的页，收缩后数据库空间自动减少收缩方式：自动收缩数据库/手动收缩数据库自动收缩数据库 对应数据库属性选项自动收缩false改为true SQL每半小时自动检查空间并收缩手动收缩数据库 对应数据库任务收缩数据库收缩操作有复选框打钩填入百分比确定手动收缩数文件 对应数据库任务收缩文件选择文件类型、文件组、文件名选择收缩操作为重新组织页填入数值

10、确定SQL Server 四种备份方式完整备份：备份整个数据的所有内容，包括事务日志 需要比较大的磁盘空间，还原时只需要还原一个文件差异备份：只备份上次完整备份后更改的数据部分事务日志备份：之备份事务日志里的内容文件或文件组备份：之备份文件或文件组中的某些文件在数据库备份期间 SQL Server 做如下工作备份数据及数据库中所有表的结构和相关文件结构备份在备份期间发生的所有活动备份在事务日志中未确认的事务数据库完整备份 对应数据库任务备份选择要备份的数据库备份方式选择备份时间路径以及名称完整还原数据库 数据库节点还原数据库选择要还原的数据库以及备份文件确定数据库差异备份 完整备份后 对应数据

11、库任务备份选择要备份的数据库备份方式选择备份时间路径以及名称差异还原数据库 数据库节点还原数据库选择要还原的数据库以及备份文件（先还原完整再还原差异备份）选项覆盖现有数据库不对市局哭提供任何操作（A）确定 要还原的数据库未删除的情况第五章 数据表管理实体完整性：要求表中的行唯一：唯一约束，主键约束，标示列域完整性：摆正列子符合规定要求：限制数据类型，检查约束，外检约束，默认值，非空约束引用完整性：量表字段必须一致：外键约束主键：表中一列或几列组合的，能够唯一标示表中的每一行，一个表只能有一个主键，多列组合称为复合主键外键：相对应于主键，一个表可以有多个外键标识列：自动增长列或自动标号，本身没有

12、具体含义，只是用来标识不同的实体，定义标识列必须为整形，数据整栋增加，不能手动修改主键约束：要求主键列不能为空，主键列唯一非空约束：要求该列不能出现空值唯一约束：要求该列值唯一，允许为空，但只能有一个空值创建唯一约束 对应列索引/键添加类型改为唯一键，选择对应列检查约束：限制某列曲子范围是否合适创建检查约束 对应列Check 约束添加表达式限定内容（例：AGE>=18 AND AGE<=55）确定默认约束：设置某列的默认值外键约束：用于在量表之间建立关系，需要制定引用主键是那一列创建外键约束 对应列关系添加表和列的关系添加主外键的关系和对应键查看设置主外键 对应表设置列名右边的黄色

13、钥匙图标为主键、灰色钥匙图标为外键标识列 对应表设计对应列列属性（标识规范）（是标识）设为是则为标识列设置默认值 对应表设计-对应列列属性（常规）默认值或绑定分类分类说明数据类型数据说明文本数据字母、符号、数字char固定长度的非Unicode字符数据varchar可变长度非Unicode字符数据nchar固定长的的Unicode数据nvarchar可变常德的Unicode数据text存储长文本信息ntext存储可变长文本信息日期和时间日期和时间datetime时间数字数据数字intsmallintfloatrealmumeric(*,*)货币数据十进制货币数据monrybit数据表示是、否的

14、数据bit布尔 true 1 false0主键 Prinmary Key外键 Foreign Key唯一 Unique Key默认 Defaukt Key检查 check KeyOrderId INT IDENTITY(1,1) NOT NULL,1开始加1的标识键CREATE TABLE CommoditySort(SortId INT IDENTITY(1,1) NOT NULL, -主键SortName VARCHAR(50) NOT NULL)-商品类别名称添加约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 具体约束(约束形式),约束名推荐 约束类型_约束列A

15、LTER TABLE 表名 WITH NICHECK ADD CONSTRAINT 约束名 具体约束(约束形式) 添加约束时不检查已存在数据删除约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名例ALTER TABLE UserInfoADD 主键CONSTRAINT PK_UserId PRIMARY KEY (UserId),检查CONSTRAINT CK_UserPwd CHECK(LEN(UserPwd)>=6),长度的函数默认CONSTRAINT CK_Gender CHECK(Gender=0 OR Gender=1),CONSTRAINT DF_Ge

16、nder DEFAULT(0) FOR Gender,CONSTRAINT CK_Email CHECK(Email LIKE '%')必须包含符号%任意多字符？单一通配符ALTER TABLE CommodityInfoADD CONSTRAINT PK_CommodityId PRIMARY KEY (CommodityId)ALTER TABLE OrderInfoADD CONSTRAINT PK_OrderId PRIMARY KEY (OrderId),外键CONSTRAINT FK_UserId FOREIGN KEY(UserId) REFERENCES Use

17、rInfo(UserId),表名（列名）CONSTRAINT FK_CommodityId FOREIGN KEY(CommodityId) REFERENCES CommodityInfo(CommodityId),CONSTRAINT DF_PayWay DEFAULT('网上银行') FOR PayWay,CONSTRAINT CK_Confirm CHECK(Confirm=0 OR Confirm=1),CONSTRAINT DF_Confirm DEFAULT(0) FOR Confirm,CONSTRAINT CK_SendGoods CHECK(SendGood

18、s =0 OR SendGoods=1),CONSTRAINT DF_SendGoods DEFAULT(0) FOR SendGoods-1外键约束注意类型，长度必须与引用的主键列的类型，长度必须完全一致-2外键约束注意引用的表中必须有主键列创建数据库关系图 选择对应数据库数据库关系图新建关系图将表选中添加各表均可添加自定义列名可在数据库关系图创建主外键查询是否存在表有则执行下一句 IF EXISTS(SELECT * FROM sysobjects WHERE name='CommodityInfo')删除表 先删除没有被引用的表 DROP TABLE 表名(Commodi

19、tyInfo）第六章 T-SQL 数据语言操作算数运算符：+、-、*、/、% /左右两边都为整数 结果为整数 select 5/2 as 除的结果 结果为2赋值运算符 = -+左右，都是数值型+就是算术运算符，逻辑运算符 AND、OR、NOT -优先级NOT >AND >OR比较运算符>、<、>=、<=、<>、=、!=（非92标准）连接运算符+ -+左右，都是字符型+就是连接运算符-+左右，类型不一致，就需要类型转换运算符优先级 算数运算符>比较运算符>逻辑运算符>连接运算符>赋值运算符声明变量 DECLARE x int

20、 ,y int赋值 SET x=8 SET y=10逻辑运算IF x>=y 换行 SELECT 'x的值大于等于y的值'换行ELSE换行SELECT 'y的值大'向表中添加删除数据-注意事项：-1、先向主表中添加数据，然后再向子表中添加数据-2、标识列不需要手动添加数据-1、先删除子表中的数据，然后再删除主表中的数据添加数据 INSERT INTO 表名(列名1,列名2,列名3)VALUES('数值1','数值2',数值3) 可省略标识列不写 INSERT INTO CommoditySort VALUES('手机数

21、码')所有列插入数据 INSERT UserInfo VALUES('yoyo','iloveyou') 可省略 INTO和列名关键字部分添加数据 INSERT INTO 表名(列名1,列名2,列名3)VALUES(1,'数值2',数值3)向默认值添加数据 INSERT INTO 表名(列名1,列名2,列名3)VALUES(1,'数值2',DEFAULT)使用了默认值利用原有表向新表（已存在）插入数据 INSERT INTO 新表表名(新表列名)SELECT 源表对应列名 FROM 源表表名例INSERT INTO Use

22、rAddress(UId,Uname, UPhone) SELECT UserId,UserNamePhoneFROM UserInfo利用原有表向新表（新存在）插入数据 SELECT 新表对应列名 AS 别名 INTO 新表表名 FROM 源表表名 例 SELECT UserId,UserName,UserAddress,Phone,IDENTITY(INT,1,1) AS IDINTO AddressListFROM UserInfo 增加了标识列更新数据 UPDATE 表名 SET 列名 =更新值 WHRER 更新条件修改语法1)如果要更新的值为多个，那个多个要列新的值使用英文的逗号来分

23、隔2)WHERE条件不写的话，将更新表中所有的数据3)使用UPDTAE更新数据的时候可以更新一行数据，也可能更新多行数据，也有可能不会更新任何数据修改要素1)要修改的表是哪个？2)修改哪个列的值？如果要修改的值为多个，中间使用英文的逗号分隔3)条件是什么？如果条件为多个的话，使用关系运算符（AND,NOT,OR）连接【1】省略WHERE条件的更新, 更新数据库中表的所有数据【2】更新的值为一个例 UPDATE UserInfo SET UserName='张三' WHERE UserId='yoyo'【3】一次性更新多个值，中间使用英文的逗号来分隔例 UPDAT

24、E UserInfo SET UserName='李四',UserAddress='北京市昌平区'WHERE UserId='daxia'【4】修改的条件为多个，使用关系运算符连接例 UPDATE CommodityInfo SET Amount=10 WHERE SortId=1 AND CommodityId=3【5】如查要修改的数据不存在，0行受影响例 UPDATE CommodityInfo SET InPrice=900 WHERE CommodityId=4删除数据 DELECT FROM 表名WHERE<删除条件> 或

25、TRUNCATE TABLE 表名使用DELETE进行删除数据，删除后，标识列继续增长，不会从标识种子开始【1】基本删除，省略WHERE 它将删除表中所有的数据例 DELETE FROM OrderInfo【2】带条件的删除，是否存在主外键关系，如果存在，先删除子表中的数据，再删除主表中的数据先删除子表中的数据例 DELETE OrderInfo WHERE UserId='yoyo' DELETE UserInfo WHERE UserId='yoyo'删除要素1）哪个表？条件是什么？使用TRUNCATE TABLE 删除数据 TRUNCATE TABLE 表

26、名 比DELETE快 不能恢复还原数据例 TRUNCATE TABLE OrderInfo1)类似于没有WHERE条件的DELETE语句，删除表中所有的数据 2)删除数据之后，再插入数据，标识列从标识种子开始 3)不能用于有外键约束的表 TRUNCATE TABLE 删除会报错 但是可以使用DELETE删除表中所有数据导入数据注意事项，与插入数据的顺序相同，有主外键关系的，先导主表中的数据，再导子表中的数据 关闭数据源导入数据对应数据库 任务导入数据选择对应文件（EXCEL 选择Microsoft Excel文本文档选择平面文件）选择文件路径下一步*2选择Excel的Sheet映射数据源下一步

27、*3完成导出数据对应数据库 任务导出数据数据源不动、身份验证均可选择对应文件（EXCEL 选择Microsoft Excel文本文档选择平面文件）选择文件路径（无则新建或直接输入想要的文件名）设置出错解决方式下一步*3完成批处理的特点-1自成一行-2每个批处理单独发送到服务器去执行，一个批处理出错，不会影响另外的批处理-3GO是批处理结束的标志，但不是T-SQL命令GO之前不能有T-SQL语句，之后有T-SQL语句严重影响可读性，所以GO要独占一行第七章 T-SQL数据查询SELECT COUNT(*) FROM Test/查询表中记录数不显示受影响的行数 SET NOCOUNT ON查询语句

28、 SELECT 列名/* FROM表名 限制条件ORDER BY 排序-1查询全部的行和列,*代表所有的行和列例SELECT * FROM UserInfo-2查询部分行和列，部分行使用WEHRE来限制，部分列使用列名来限制例SELECT UserId ,PayWay,PayMoney FROM OrderInfo WHERE PayWay like '网%'3在查询的结查集中使得别名 -（）使用AS关键字来改变结果集中的别名例SELECT UserId AS 用户名 ,PayWay AS 付款方式FROM OrderInfo WHERE PayWay='网上银行

29、9; -（）使用“=”赋值运算符来改变结果集中的别名，赋值的顺序是从右向左赋值例SELECT 用户名=UserId ,付款方式=PayWay,付款金额=PayMoney FROM OrderInfo WHERE PayWay='网上' -（）使用“空格”来改变结果集中列的别名例SELECT UserId 用户名 ,PayWay 付款方式,PayMoney 付款金额FROM OrderInfo WHERE PayWay='网上银' -4查询NULL值例SELECT * FROM UserInfo WHERE Email IS NULL例SELECT * FROM

30、UserInfo WHERE Email IS NULL OR Email=''-IS NULL:从未录入过数据，没有地址-''：录入过数据，而后被删除，是有地址-1在查询中使用常量列 数据库中没有 但是结果会显示购物网站列 天猫具体内容例SELECT UserId 用户号,PayWay 付款方式,PayMoney 付款金额,'天猫' AS 购物网站 FROM OrderInfo-2查询返回限制的行数，使用TOP关键字 -(1)返回限定的个数 例SELECT TOP 5 UserName AS 用户名,UserAddress AS 地址FROM

31、UserInfo WHERE Gender=1 -(2)返回限定百份比20% PERCENT -使用百份比的形式能够得到大概的数据而非精确的数据 例 SELECT TOP 20 PERCENT UserName AS 用户名,UserAddress AS 地址FROM UserInfo WHERE Gender=1 -3在查询的结查集中进行排序，关键字是ORDER BY 升序为ASC,降序为DESC 多条件首条件优先 例SELECT UserId,PayWay,PayMoney FROM OrderInfo ORDER BY PayMoney DESC 例 SELECT UserId,PayW

32、ay,Amount,PayMoney FROM OrderInfo ORDER BY Amount DESC,PayMoney ASC字符串函数-【1】CHARINDEX:在一个字符串中查找另一个字符串，如果找到返回找到开始的位置，如果没有找到返回0，无源字符返回null-两个参数，不指定开始位置，默认从开始例SELECT CHARINDEX('zxw','')-三个参数，第一个是查找的，第二个是源字符串，第三个是开始查找的位置例SELECT CHARINDEX('zxw','',10)-将函数放在查询语句中进行使用-查询“何双呈

33、”的邮箱中""符号的位置例SELECT CHARINDEX('',Email) FROM UserInfo WHERE UserName='何双呈'-使用CHARINDEX的目的是想把邮箱中的用户名提取出来-【2】LEN:求子符串的长度例SELECT LEN(Email) FROM UserInfo WHERE UserName='何双呈'-【3】LEFT/RIGHT从字符串的左边/左边截取指定个数的字符例SELECT LEFT(Email,CHARINDEX('',Email)-1) FROM UserInf

34、o WHERE UserName='何双呈'-【4】REPLACE替换一个字符串的字符 （目标字符串、目标字符、被替换的新字符）-最后一个参数可为空的话例SELECT REPLACE('我最喜欢的颜色是白色','白色','绿色')例SELECT REPLACE('我最喜欢的颜色是白色','白色','') -【5】STUFF-参数，(源字符串，开始位置参数，删除的长度参数，参数是插入的新字符串)例SELECT STUFF('ABCDEF',2,3,'我要自学网&

35、#39;)-【6】UPPER LOWER 将字符串转换为大写、小写例DECLARE x xml = N'abcDEF!4'SELECT x.value('fn:upper-case(/text()1)', 'nvarchar(10)');-【7】LTRIM RTRIM 返回字符串为左边、右边的空格去除后的字符串例 LTRIM(" Hello")日期函数-【1】获得当前系统时间 例SELECT GETDATE()-【2】DATEADD()参数-指定的日期部分，参数-整数值，参数-有效的日期格式例 SELECT DATEADD(M

36、M,1,GETDATE()/当前月份加一-参数的整数是否允许为负数,例 SELECT DATEADD(MM,1.5,GETDATE() /-参数的整数值是小数,直接舍点小数部分向下取整-【3】DATEDIFF,求两个指定日期之间的差 结果可为负数例 SELECT DATEDIFF(YY,'2008-8-8',GETDATE()-【4】DATENAME与DATEPART-DATENAME返回的是日期指定部分的一个字符形式，“+”起的是连接作用，因为“+”号左右都是字符形式例 SELECT DATENAME(YY,GETDATE()+DATENAME(DD,GETDATE()-DA

37、TEPART返回的是日期指定部分的一个数值形式，“+”起的作用是相加运算，是算术运算符例 SELECT DATEPART(YY,GETDATE()+DATEPART(DD,GETDATE()数学函数和系统函数-【1】随机数RAND,如果指定了随机种子，那么返回的随机数都相同，如果没有指定随机种子，那么系统自动分配，所以每次的返回值都不相同 例 SELECT RAND(100)例 SELECT RAND()-【2】CEILING、FLOOR、ROUND、ABS 向上、向下、四舍五入取整、取绝对值例 SELECT CEILING(9.000001) SELECT CEILING (RAND(100

38、)*100)-【2.5】SIGN 正数返回+1、负数返回-1、0返回0-【3】系统函数CONVERT用于数据类型转换 CONVERT(要转换的类型,要转换的数据源)-将字符型转为数值型例 SELECT CONVERT(int,'12')+CONVERT(int,'10') -+号起的是算术运算，相加运算-将数值型转为字符型例 SELECT CONVERT(varchar(2),12)+CONVERT(varchar(2),10)-+号是连接运算符，起连接作用-将日期型转为字符型 CONVERT(varchar(10),GETDATE(),日期格式)例 SELEC

39、T CONVERT(varchar(10),GETDATE(),102) SELECT CONVERT(varchar(10),GETDATE(),112)模糊查询 通配符 <_> 一个字符 <%>零或任意多字符 <>指定范围内的任意字符 <>不在指定范围内的任意字符-【1】使用LIKE进行模糊查 -查询用户表中姓李的用户姓名，地址，电话例 SELECT UserName,UserAddress,PhoneFROM UserInfo WHERE UserName LIKE '李%'-LIKE特点: 1、只能与字符型一起使用 2、对

40、于查询结果中的个数不固定-【2】BETWEEN .AND 查询在两个已知值的之间的未知的值-查询订单表中购买数量到之间的数据 例 SELECT * FROM OrderInfo WHERE Amount BETWEEN 2 AND 10特点1、查询包含起止值 2、起始值小于终止值 3、等同于>=a and <=b 4、可以与数值型与日期型一起使用-与日期型一起使用的举例 -查询下单日期不在-1-1到-1-1之间的订单信息例 SELECT * FROM OrderInfo WHERE OrderTime NOT BETWEEN '2012-1-1' AND '

41、2013-1-1'-【3】使用IN在列举值范围内进行查询 -使用NOT对结果进行取反 -查询使用网上银行邮局汇款方式下单的信息例 SELECT * FROM OrderInfo WHERE PayWay IN'网上银行','邮局汇款')IN特点 1、可以与字符型，数值型，以及日期型一起使用2、通常用于子查询3、相当于使用OR进行连接的查询聚合函数 将结果聚合-聚合函数的特点 1、返回结果为单行单列 2、要查询中如果有一个使用了聚合函数，那么就不允许存在非聚合函数项，-如果有那么必须使用分组GROUP BY语句-【1】SUM用于对表达式中的列进行求和（只能

42、用于数值型，空值将忽略）-查询商品编号为的销售总量例 SELECT SUM(Amount) ,PayWay FROM OrderInfo WHERE CommodityId=6 GROUP BY PayWay 分组-【2】AVG求平均值，（只能与数值型一起使用，空值将忽略）-图书音像的平均进货价例 SELECT AVG(InPrice) FROM CommodityInfo WHERE SortId=2-【3】MAX与MIN,求最值，（空值将忽略，可以与数值型，日期型，字符型一起使用）-手机数码的最高进货价与最低最货价例 SELECT MAX(InPrice)AS 最高价, MIN(InPri

43、ce) AS 最低价 FROM CommodityInfo WHERE SortId=1-【4】COUNT用于统计记录集中的记录数（）SELECT COUNT(列名 也可为*或常量列) FROM UserInfo-例 SELECT COUNT(*) FROM UserInfo等同SELECT COUNT(1) FROM UserInfo -空值将被统计例 SELECT COUNT(Email) FROM UserInfo -如果写列名，空值将忽略，分组查询 SELECT 聚合函数 分组的列 FROM 表名 WHERE 条件 GROUP BY 分组的列-【1】查询网购用户中男女用户的人数各是多少

44、？例 SELECT COUNT(*) AS 总人数,Gender AS 性别 FROM UserInfo GROUP BY Gender-注意事项：对查询的列有限制，除了聚合函数外，就必须是分组的项-2在分组之后如何对分组的结果进行排序ORDER BY ,ASC升序，DESC降序-举例：查询每种商品的销售总量并按降序排列 -小技巧：“每”，“各”后面的名词就是分组的项例 SELECT CommodityId AS 商品编号,SUM(Amount) AS 销售总量 FROM OrderInfo GROUP BY CommodityId ORDER BY SUM(Amount) DESC-3如何对

45、分组后的结果进行筛选HAVING -WHERE 用于分组之前的条件筛选，HAVING用于分组后的条件筛选语法：SELECT .FROM .WHERE .GROUP BY.HAVING.ORDER BY -查询销售总量超过的商品信息例 SELECT CommodityId AS 商品编号,SUM(Amount) AS 销售总量 FROM OrderInfo GROUP BY CommodityIdHAVING SUM(Amount)>10 ORDER BY SUM(Amount) DESC内连接查询 先将量表中数据相乘在通过where选出重叠的部分-查看订单编号，用户姓名，购买数量，购买商

46、品编号-1WHERE SELECT 表1.列1，表1.列2，表2.列3FROM 表1，表2 不分顺序WHERE表1.列1=表2.列2 （主外键）例 SELECT OrderInfo.OrderId,UserInfo.UserName,OrderInfo.Amount,OrderInfo.CommodityId FROM OrderInfo,UserInfoWHERE OrderInfo.UserId=UserInfo.UserId-如果不写WHERE会出现迪卡尔集现象，将两表中的数据条数相乘？-改进版，三表连接查询 SELECT 表1别名.OrderId,表2别名.UserName,表3别名.

47、Amount,C FROM 表1 AS 表1别名,表2 AS 表2别名,表3 AS 表3别名 WHERE 表1.相同列1=表2. 相同列1 AND 表2. 相同列2=表3. 相同列2例 SELECT O.OrderId,U.UserName,O.Amount,C.CommodityName FROM OrderInfo AS O,UserInfo AS U,CommodityInfo AS C WHERE O.UserId=U.UserId AND C.CommodityId=O.CommodityId-2使用INNER JOIN.ON格式 SELECT表1别名.列A,表2别名.列B,表3别名

48、.列3 FROM 表1 AS 表1别名 INNER JOIN 表2 AS 表2别名 ON 表1.相同列1=表2.相同列1 INNER JOIN 表3 AS 表3别名 ON 表2.相同列2=表2.相同列2 WHERE 表别名.列=XX例 SELECT OrderId,UserName,O.Amount,CommodityName FROM UserInfo AS U INNER JOIN OrderInfo AS O ON U.UserId=O.UserId INNER JOIN CommodityInfo AS C ON O.CommodityId=C.CommodityIdWHERE U.U

49、serName='赵可以'外连接查询 至少返回一个表所有数据 表有主次之分 主表不符合条件的列 将填入NULL 返回-1左外连接，SELECT 列名 商品名称 FROM 主表 LEFT JOIN 副表 ON 主表.关联列=附表.关联列-左外连接LEFT JOIN 以左表为主，返回左表的所有记录，-如果左表中的某些行在右表中没有匹配的将用NULL值代替-查询各类商品的库存情况，所有的类别都要显示出来，以类别表为主表例 SELECT SortName AS 商品类别,Amount AS 库存量,CommodityName AS 商品名称 FROM CommoditySort LEF

50、T JOIN CommodityInfo ON CommoditySort.SortId=CommodityInfo.SortId-2右外连接，SELECT 列名 商品名称 FROM 副表 LEFT JOIN 主表 ON 主表.关联列=附表.关联列例 SELECT CommodityName AS 商品名称,OrderId AS 订单号 FROM OrderInfo RIGHT JOIN CommodityInfoON OrderInfo.CommodityId =CommodityInfo.CommodityId-3左外连与右外连接相互转换-左外连接例 SELECT S.Name AS 类别

51、名称,Amount AS 库存量 FROM Sort AS S LEFT JOIN Info AS C ON S.Id=C.Id=SELECT S.Name AS 类别名称,Amount AS 库存量 FROM Info AS C RIGHT JOIN Sort AS S ON C.Id=S.Id使用UNION进行合并查询-SELECT . FROM 表名 UNION SELECT . FROM 表名例 SELECT UserId AS 用户编号 ,UserName AS 用户姓名,UserAddress AS 地址 FROM UserInfo UNIONSELECT PayWay AS 付款

52、方式,UserId AS 下单用户名,CONVERT(varchar(10),OrderTime) AS 下单时间FROM OrderInfo UNION ALL SELECT UserId AS 用户编号 ,UserName AS 用户姓名,UserAddress AS 地址 FROM UserInfo合并查询特点 1、合并查询要求合并的表列数相同 2、要求列的类型相同或相兼容,列的名称由第一个查询中的列决定 3、行的个数由表中的行的总和决定，如查有重复行将舍掉 如果想显示所有合并的数据需加上UNION ALL 4、从上向下代次执行，可以使用空格或者是列的顺序来改变结果集中的数据位置5、如果

53、想把合并的结果插入到新表中显示那么SELECT INTO必须放在第一个查询中例 SELECT UserId,UserName,UserAddress INTO newTables FROM UserInfo UNION SELECT UserId,PayWay,CONVERT(varchar(10),PayMoney)6、如果想对合并的结查进行排序，那么ORDER BY 必须放到最后一个SELECT语句中 SELECT UserId,UserName,UserAddress FROM UserInfo -ORDER BY UserId UNION SELECT UserId,PayWay,CO

54、NVERT(varchar(10),PayMoney) FROM OrderInfo ORDER BY UserId DESC7、合并查询，两表中行的个数的总和，行合并到结果集连接查询 行的个数最大值为两表中行的个数乘积，与类型无关例 SELECT UserId,UserName,UserAddress FROM UserInfo UNION SELECT UserId,PayWay, CONVERT(varchar(10),OrderTime) FROM OrderInfo子查询 先执行子查询 在执行父查询 父查询外层可加条件 子查询语句不能排序 多表连接均可转换为子查询 反之不可任何允许使

55、用表达式的地方都可以使用子查询 子查询可以嵌套 SELECT FROM WHERE后均可-【1】根据已知项去查未知项，已知项是商品的类别名称例 SELECT * FROM CommodityInfo WHERE SortId=( SELECT SortId FROM CommoditySort WHERE SortName='手机数码' -只能有一个列 ) ORDER BY SortId DESC子查询的特点 1、要求子查询必须放在小括号中 2、子查询与比较运算符使用时要求子查询的列只有一个 3、ORDER BY排序不允许放在子查询中，如果要排序只能放在父查询中-【2.1】多表

56、连接查询 等同于【2.2】 适合于作为查询的WHERE条件，只能查询主表中字段例 SELECT U.UserName AS 用户姓名,U.UserAddress AS 地址 FROM CommodityInfo AS CINNER JOIN OrderInfo AS O ON C.CommodityId=O.CommodityId INNER JOIN UserInfo AS U ON O.UserId=U.UserId WHERE C.CommodityName='苹果Iphone6'-【2.2】使用子查询 -根据已知项去查询未知项 适用于从多表中查看数据 可以查看连接表中的

57、任意字段-STEP1、根据商品名称查询商品编号例 SELECT CommodityId FROM CommodityInfo WHERE CommodityName='苹果Iphone6'-STEP2、根据中得到的商品编号去查询购买用户的编号例 SELECT UserId FROM OrderInfo WHERE CommodityId=(SELECT CommodityId FROM CommodityInfo WHERE CommodityName='苹果Iphone6')-STEP3、根据中得到的用户的编号查询用户的姓名和住址例 SELECT UserName AS 用户名,UserAddress AS 地址 FROM UserInfo WHERE User