SQL Server基础
第一章、SQL Server基础
1.1、预备知识
-
SQL Server 是Microsoft公司推出的关系型数据库管理系统。
-
Microsoft SQL Server 是一个全面的数据库平台，使用集成的商业智能（BI）工具提供了企业级的数据管理。
1.2、专业单词
| 单词 | 释义 |
|---|---|
| Database | 数据库 |
| Management | 管理 |
| System | 系统 |
| DBS | 数据库系统 |
| DBMS | 数据库管理系统 |
| RDBMS | 关系数据库 |
| DBA | 数据库管理员 |
| SQL | 结构化查询语言 |
| Server | 服务 |
1.3、什么是数据
数据（Data）是描述事物的符号记录，如数字、文字、图形图像、声音、视频等。
1.4、数据管理发展的三个阶段
-
人工管理
-
文件系统管理
-
数据库管理系统
1.5、数据库系统技术发展三代
-
第一代：网状和层次数据库系统
-
第二代：关系数据库系统
-
第三代：以面向对象数据模型为主要特征的数据库系统
1.6、数据库相关概念
-
数据库（DB）：是长期存储在计算机内有组织的、可共享的数据集合。
-
数据库管理系统（DBMS）：是操纵和管理数据库大型软件，用于建立、使用和维护数据库，是数据库系统的核心。
-
数据库系统（DBS）：是在计算机系统中应用数据库后的系统。
1.7、SQL Server 2012简介
-
SQL Server是Microsoft公司推出的关系型数据库管理系统，SQL Server 是微软发布的新一代数据平台产品。包含企业版、标准版、商业智能版、web版、开发者版本以及精简版。
-
SQL Server 2012的系统架构分为数据库模块和商务智能模块。数据库模块包括数据库引擎、Server Broker复制、全文搜索等功能组件。商务智能模块包括集成服务、分析服务、报表服务三大组件。
第二章、数据库管理
2.1、专业单词
| 单词 | 释义 |
|---|---|
| master | 主要的、数据库中指主数据库 |
| model | 模型，数据库中指模板数据库 |
| tempdb | 临时数据库 |
| primary | 主要的 |
| log | 日志 |
2.2、数据库的组成
-
数据库的物理结构
-
主数据文件（）——有且仅有1个
-
辅助数据文件（）——可以有N个
-
事务日志文件（）——至少有1个
-
-
数据库的逻辑结构
数据库的逻辑结构主要包含了表、视图、索引、存储过程及触发器等数据库对象，用于保存SQL Server中相关数据信息及用户对数据的相关操作。
2.3、数据库的分类
-
系统数据库
在安装时，会由系统自动创建一些数据库，它们用于协助系统共同完成对数据库的相关操作，同时也是SQL Server2012运行的基础，如master、model、tempdb、msdb。
-
用户数据库
根据用户实际需求，自行创建的数据库。
2.4、图形界面创建、维护数据库
-
创建数据库
-
启动图形界面管理器——SQL Server Management Studio,登录服务器。
-
登陆成功后在【对象资源管理器】中右击【数据库】——单击【新建数据库】选项。
-
填写数据库各项信息，确定即可。
-
-
修改数据库
-
修改数据库名称：右击需重命名的数据库，单击【重命名】选项，输入新名称即可。
-
扩大数据库：右击需要扩大的数据库，单击【属性】选项，在【数据库属性】对话框中的【文件】选项中，修改文件大小属性或添加文件即可。
-
收缩数据库：右击需要收缩的数据库，选择【任务】——【收缩】——【数据库】命令，打开【收缩数据对话框】，设置相关收缩项，确定即可。
-
-
删除数据库
-
右击需要删除的数据库——单击【删除】选项即可。
注意：正在使用的数据库不能删除。
-
选中需要删除的数据库，按 Delete键。
-
2.5、图形界面分离、附加数据库
-
分离数据库：分离数据库是指将数据库从SQL Server的服务器上删除，但不删除物理文件。
方法：右击需要分离的数据库名称，在弹出的选项列表中单击【任务】——【分离】命令。
-
附加数据库：将分离后的数据库文件添加到SQL Server的服务器上。
方法：右击【数据库】——单击【附加】命令，弹出【附加数据库】对话框，在对话框中单击【添加】按钮，添加主数据文件，确定即可。
第三章、数据表管理
3.1、专业单词
| 单词 | 释义 |
|---|---|
| table | 表 |
| tinyint | 微整型 |
| numeric | 数字的 |
| real | 实数 |
| varchar | 可变长字符串 |
| text | 文本 |
| bit | 位 |
| detetime | 日期时间 |
3.2、数据表概述
-
什么是数据表？
数据表是数据库最基本的组成对象，用来组织和存储数据，是由行和列交叉组成一张二维表。每个列包含特定类型的数据信息，一个列就是一个字段；每个行包含一条数据信息，一行就是一条记录。
-
数据表的分类
-
系统表
-
临时表
-
用户表
-
3.3、列的数据类型
-
数字类型
单词 释义 tinyint 微整型（0~255） smallint 短整型 int 整型 bigint 长整型 decimal 固定精度小数 numeric 固定精度小数 float 浮点小数 real 浮点小数 smallmoney 小范围货币类型 money 货币类型 -
字符串类型
单词 释义 char 定长字符串 varchaar 变长字符串 nchar 定长Unicode字符串 nvarchar 定长Unicode字符串 text 定长文本 ntext 变长Unicode文本 -
日期时间类型
单词 释义 datetime 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 datetime2 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 smalldatetime 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 date 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 timestamp 存储唯一的数字，每当创建或修改某行时，该数字会更新。 datetimeoffset 与 datetime2 相同，外加时区偏移。 time 仅存储时间。精度为 100 纳秒。 -
其它类型
单词 释义 bit 位类型，只存储0和1 image 长度可变的二进制数据 binary（n） 长度为n的固定长度二进制数据 varbinary（n|max） 长度可变的二进制数据
3.4、图形界面创建、维护数据表
-
创建数据表
-
设计数据表结构
-
登录成功后在展开【数据库】节点中右击【表】节点，执行【新建表】命令，进入表设计器中，对列名、数据类型、是否允许NULL值进行设置。
-
输入表名保存
-
-
修改数据表结构
-
修改数据表名称：右击需要重命名的数据表，单击【重命名】选项，输入新名称即可。
-
修改表的列：右击需要修改表的名称，执行【设计】命令，进入表的设计器，可添加列、删除列、修改列属性。
-
-
删除数据表：右击需要删除的数据表，执行【删除】命令。
3.5、修改数据库不能保存
单击【工具】菜单下的【选项】命令——弹出【选项】对话框——单击【设计器】，将【阻止保存要求重新重新创建表的更改】前的复选框选中状态去掉——单击【确定】即可。
第四章、维护数据完整性
4.1、专业单词
| 单词 | 释义 |
|---|---|
| CONSTRAINT | 约束、限制 |
| PRIMARY KEY | 主关键字（主键） |
| IDENTITY | 身份、个性（标识） |
| UNIQUE | 唯一的 |
| NOT NULL | 非空 |
| DEFAULT | 缺省、默认 |
| CHECK | 检查 |
| FOREIGN KEY | 外关键字（外键） |
4.2、数据完整性概述
-
什么是数据完整性？
维护数据完整性就是要确保数据的准确性和一致性，使表内的数据是准确的、不相矛盾，表之间的数据不相矛盾，关联性不被破坏。
-
数据完整性的分类
-
实体完整性
-
域完整性
-
参照完整性
-
4.3、数据完整性的约束
-
约束的种类
约束（constraint）是实现数据完整性的主要方法。
完整性 约束类型 描述 实体完整性 IDENTITY 自增约束，指定列中的值自动输入 实体完整性 PRIMARY KEY 主键约束，每行的唯一标识 实体完整性 UNIQUE 唯一约束，指列中的值不能重复 域完整性 CHECK 检查约束，指定列中可以接受的值 域完整性 NOT NULL 非空约束，指定列中不能接受的值 域完整性 DEFAULT 默认值约束，在插入数据时指定列中的默认值 参照完整性 FOREIGN KEY 外键约束，匹配另一张表中的主键
4.4、数据完整性约束——实体完整性
-
主键约束——PRIMARY KEY
主键就是主关键字，用来限制列的数据具有唯一性且不为空。一个表只能有一个主键，一般表中最重要的、不重复的且不为空的一列做主键。
设置主键：
在【表设计器】中右击某列，单击【设置主键】或【删除主键】可添加或删除主键，还可单击工具栏上【设置主键】或【删除主键】按钮进行主键的添加或删除。
-
自增约束——IDENTITY
自增约束就是可以自动增长编号的约束，又叫标识符列。
设置标识符:
在表设计器中选定列，在【列属性】区域展开【标识规范】，对相关属性进行设置即可添加或删除标识列。
-
唯一约束——UNIQUE
同主键约束，列中数据唯一不重复，但可以有一个空值。
设置唯一约束：
在表设计器中选定列并右击，执行【索引/键】命令，弹出【索引/键】对话框，单击添加或【删除】可进行唯一约束的添加或删除。
-
非空约束——NOT NULL
一个列是否允许有空值，就是非空约束，几即
NULL和NOT NULL约束。设置非空约束：
在进行表设计时，默认每个列都是允许为空的。当某列不允许为空值时，在表设计器中选定这列，将【允许NULL值】的勾去掉。
-
默认值约束——DEFAULT
通过为某列设置一个默认值约束，当这列不插入数据时，就会自动插入默认值约束指定的值。
设置默认值约束：
在表设计器中，单击列在【列属性】区域。在【常规】节点下的【默认值或绑定】选项中设置默认值即可。
-
检查约束——CHECK
检查约束通过给定的条件（逻辑表达式）来检查输入的数据是否符合要求。
设置检查约束：
在表设计器中选定列并右击，执行【Check约束】命令，弹出【Check约束】对话框，然后单击【添加】或【删除】按钮设置
Check约束。 -
外键约束——FOREIGN KEY
通过设置外键约束，可以使表A某列的值，必须来源于表B主键的值，其中表B为主键表，表A为外键表。
设置外键约束：
首先确保主键表中已设置主键。
在外键表设计器中选定列并右击，执行【关系】命令，弹出【外键关系】对话框，再点击【添加】按钮，添加外键。
第五章、使用DDL管理数据库和表
5.1、专业单词
| 单词 | 释义 |
|---|---|
| CREATE | 创造、新建 |
| ALTER | 更改、修改 |
| DROP | 放弃、删除 |
| INSERT | 插入、添加 |
| UPDATE | 更新 |
| DELETE | 删除 |
| SELECT | 选择、查询 |
| GRANT | 授予、承认 |
| REVOKE | 撤销、取消 |
| DENY | 拒绝 |
5.2、T—SQL概述
-
什么是T—SQL？
T—SQL全称为Transact SQL，它是基于SQL的一种交互式查询语言
-
T—SQL的分类：
-
数据定义语言（DDL）：CREATE、ALTER、DROP
-
数据操纵语言（DML）: INSERT、UPDATE、DELETE
-
数据查询语言（DQL）：SELECT
-
数据控制语言（DCL）：REVOKE、DENY、GRANT
-
-
T—SQL的注释
-
单行注释：--
-
多行注释：/* */
-
-
T—SQL的语法约定
T—SQL语法格式中有很多约定的符号和写法，约有一些和C#差不多的。
5.3、DDL管理数据库
-
使用DDL定义语言创建数据库
-
创建默认数据库语法：CREATE DATABASE 数据库名称
-
操作方法：
-
新建查询
-
在【查询编辑器窗口】编写T—SQL语句
use[master] --使用master数据库 go create database [Student] --修改数据库名称 containment=none on primary --主要数据库 ( name=N'Student, --数据库名称 filename=N'C:\Student.mdf, --数据文件名称和物理路径 size=4096kb, --初始大小 maxsize=unlimited, --最大大小限制：无上限 filegrowth=1024kb --增长量 ) log on --日志文件 ( name=N'Student_log, --日志文件名称 filename=N'C:\Student.ldf, --日志文件名称和物理路径 size=1024kb, --初始大小 maxsize=2048GB, --最大大小限制 filegrowth=10% --增长量 ) go
-
-
-
使用DDL定义语言删除数据库
-
删除数据库语法：DROP DATABASE 数据库名称
-
操作方法。。。。
-
-
使用DDL定义语言创建表
-
创建表的语法：CREATE TABLE <表名>（<列名> <数据类型> [约束]）,....）
use [Student] go --创建班级表 create table Class ( ClsaaID int primary key identity(1,1), ClsaaName nvarchar(20) not null ) --创建学生信息表 create table StudentInfo ( StudentID int primary key identity(1,1), --主键约束，标识列自动增长 StudentName nvarchar(20) not null, --非空约束 Sex nchar(1) default'男', --默认值约束 Age tinyint check(Age>=15 and Age<=50), --检查约束 Email nvarchar(100) unique, --唯一值约束 [Address] nvarchar(100), ClassID int referencer class(ClassID) --外键约束 ) go
-
-
使用DDL定义语言删除数据表
-
删除数据表语法：DROP TABLE <表名>
-
删除数据表的时候，先删除外键表，再删除主键表。数据表一旦被删除，表中的数据、约束及结构都将自动被删除。
-
5.4、CREATE TABLE 创建表的问题
-
表名后面，一定要用小括号() 将所有创建的列括起来，并且小括号要成对出现。
-
所有标点符号、运算符等都必须是英文。
-
关键字不能写错。
-
整数等数据类型不能加长度，如int(5)。
-
创建外键时，参照的主键不匹配。
第六章、使用DML操作数据记录
6.1、专业单词
| 单词 | 释义 |
|---|---|
| INSERT | 插入、添加 |
| INTO | 进入...中 |
| VALUES | 值（value的名词复数） |
| UPDATE | 更新 |
| SET | 设置、放置 |
| WHERE | 哪里，SQL中指定条件的关键字 |
| DELETE | 删除 |
| TRUNCATE | 缩短 |
6.2、DML概述
-
什么是DML
DML（Data Manipulation Language 数据操纵语言）用于操作数据库对象中包含的数据，也就是说操作的单位是记录。
-
DML语句有：
-
INSERT语句：用于向数据表中插入数据内容。
-
UPDATE语句：用于修改已存在表中的记录的数据。
-
DELETE语句：删除数据表中的记录。
-
6.3、使用INSERT语句插入数据记录
-
插入语法：
-
语法一：INSERT INTO 表名（字段1，字段2，...，字段n）
VALUES（值1，值2，...值n）
-
语法二：INSERT INTO 表名 VALUES（值1，值2，...，值n）
-
语法三：INSERT INTO 表名 VALUES（值1，值2，值3），（值4，值5，值6）...
insert into StudentInfo(StudentName,Sex,Email,[Address],ClassID) values('喜洋洋','羊类','青青草原')--语法一 insert into StudentInfo values('美羊羊','羊类','青青草原')--语法二 insert into StudentInfo values('灰太狼','狼类','青青草原'), ('慢羊羊洋洋','羊类','青青草原'), ('红太狼','狼类','青青草原')--语法三（一次插多条数据）
-
6.4、使用UPDATE语句更新数据库
-
更新语法：
UPDATE 表名 SET 字段1=新值，...，字段n=新值 【where 修改条件表达式】
--不加条件的更新会将数据表中所有的记录全部更新 pdate StudentInfo set [Address] ='新路村1号' update StudentInfo set [Address] = '新路村38号' where StudentName='喜洋洋' --这就是过滤条件，当过滤条件为真时执行，否则更新失败 update StudentInfo set [Address] = '新路村138号' where StudentName='喜洋洋' --这就是过滤条件，当过滤条件为真时执行，否则更新失败 update StudentInfo set [Address]='新路村172号',Sex='男' -- 一次更新多个字段 where StudentName='沸羊羊'
6.5、删除数据记录
-
删除指定条件记录的语法：
DELETE 【RORM】 表名 【WHERE 删除条件表达式】
--没有加删除条件表达式，就会删除全部的数据记录(保留日志的删除) delete StudentInfo --添加删除条件表达式 delete StudentInfo where StudentName='灰太狼'
-
删除所有记录的语法：
-
保留日志删除：DELETE 【FORM】 表名
-
不留日志删除：TRUNCATE TABLE 表名
-
6.6、INSERT插入数据记录时出错
-
插入值得数量与指定列的数量不匹配
-
插入值得数据类型与表中该列定义的类型不兼容
-
插入的值违反了表中的约束规定
第七章、使用DQL之基础查询
7.1、专业单词
| 单词 | 释义 |
|---|---|
| DQL | Data Query Language数据查询语言 |
| SELECT | 选择、T—SQL中查询语句的关键字 |
| FROM | 来自，T—SQL中查询语句的关键字 |
| AND | 和、与 |
| OR | 或者 |
| IN | 在...之内 |
| NOT IN | 不在...之内 |
| BETWEEN AND | 两者之间、在...之间 |
| DISTINCT | 不相同的，有区别的 |
| TOP | 顶部、最高的，最上的 |
| PERCENT | 百分比，百分数 |
| ORDER | 秩序，次序 |
| TIES | 结、关系、将...系在...上 |
7.2、基本的SELECT语句结构
SELECT语句结构：
SELECT [ALL|DISTINCT] select_list FROM table_name [WHERE<search_condition>] [GROUP BY<group_by_expression>] [HAVING <search_condition>] [ORDER BY <order_expression>] [ASC|DESC]
7.3、不带条件的查询
-
查询表中部分列的值
语法：SELECT 列1，列2...列n FROM 源表名
SELECT StudentName,Age,Sex FROM Student -
查询表中所有列
语法：SELECT * FROM 源表名
方法一： SELECT StudentID,StudentName,Sex,Age,Email,Address,ClassID FROM Student 方法二： SELECT * FROM Student
-
为查询指定列添加别名
语法：SELECT 列名1 【AS】别名1, 列名2 【AS】别名2，...FROM 源表名
SELECT StudentID AS 编号,StudentName AS 姓名,Sex AS 性别 FROM Student
7.4、简单的条件查询
语法：SELECT 【*|列名列表】 FROM 源表名
WHERE 查询条件
-
比较条件
常用比较运算符：
比较运算符 含义 = 等于 > 大于 < 小于 >= 大于等于 <= 小于等于 <>、!= 不等于 !> 不大于 !< 不小于 语法：SELECT 查询列表 FROM 源表名
WHERE 表达式1 比较运算符 表达式2
--简单条件的查询 select StudentInfo.StudentName,StudentInfo.Address,StudentInfo.ClassID from StudentInfo where ClassID=1-->这个就是过滤条件 --比较条件 select StudentInfo.StudentName,StudentInfo.Address from StudentInfo where Sex !='男'--->比较条件 select * from StudentInfo where [Address]<>'新路村特1号'
-
逻辑条件
逻辑运算符 含义 AND 表示当该运算符两边所有条件都为真时才返回该行数据结果 OR 表示该运算符两边的条件中有一个为真就返回该行数据结果 语法：SELECT 查询列表 FROM 源表名 WHERE 条件表达式1 AND|OR 条件表达式2
select * from StudentInfo where Sex='女' and ClassID=2 select * from StudentInfo where Sex='男' or address='新路村特1号' and ClassID=2
-
列表条件
列表条件运算符 含义 IN 表示属性值是否属于指定集合 NOT IN 表示属性值是否不属于指定集合 语法：SELECT 查询列表 FROM 源表名 WHERE 表达式【NOT】IN 值得列表
1.查询年龄为16,18,20岁的相关信息 SELECT * FROM Student WHERE Age IN(16,18,20) 2.查询年龄不是16,18,20,岁的学习信息 SELECT * FROMStudent WHERE Age NOT IN(16,18,20) --逻辑条件结合列表条件 select * from StudentInfo where StudentName in('喜洋洋','美羊羊','红太狼','懒洋洋') and sex='女' select * from StudentInfo where StudentName in('喜洋洋','美羊羊','红太狼','懒洋洋') or sex='女'
7.4、范围条件和空值条件查询
-
范围条件查询
列表条件运算符 含义 BETWEEN 指查询某一个范围内的数据 NOT BETWEEN 指查询不在某一个范围内的数据 语法：SELECT 查询列表 FROM 源表名
WHERE 表达式【NOT】BETWEEN 值1 AND 值2
--范围条件查询 select StudentName as 姓名,Age as 年龄,Sex as 性别 from Student where Age Between 22 and 26 select * from StudentInfo where StudentID between 2 and 6 --值1小于值2 select * from StudentInfo where StudentID not between 2 and 6 --值1小于值2
-
空值条件查询
语法：SELECT 查询列表 FROM 源表名
WHERE 表达式 IS 【NOT】 NULL
注意：IS NULL不能用=NULL替换，IS NOT NULL 不能用!=NULL替换
SELECT * FROM StudentInfo WHERE Remark IS NULL--查询null值的数据 SELECT * FROM StudentInfo WHERE Remark IS NOT NULL--查询不是null值的数据
7.5、消除重复项和返回前N行的查询
-
消除重复项查询
语法：SELECT DISTINCT 列名 FROM 表名
--消除重复项查询 SELECT DISTINCT ClassID FROM StudentInfo--只能对某一列消除重复项
-
返回前N行的查询
语法：SELECT TOP N [PERCENT] 查询列表 FROM 源表名
注意：如果未指定PERCENT，N就是返回行数。如果指定了PERCENT ,N就是返回结果集的百分比。
--返回前N行数据 SELECT TOP 3 * FROM StudentInfo -- 指定了返回的行数为3行 --百分之最大为%100，最小为0 SELECT TOP 30 PERCENT * FROM StudentInfo --指定返回前百分之30的行数
7.6、查询排序
语法：ORDER BY 排序表达式 【ASC|DESC】
参数说明：
排序表达式：要排序的列名或列的别名的表达式
排序规则：ASC表示升序，DESC 表示降序。默认情况为ASC
-
排序语句的常规使用
-
对一个字段排序
SELECT * FROM Student WHERE Age>20 ORDER BY Age Asc
-
对多个字段排序
SELECT* FROM Student WHERE Age>20 ORDER BY Age DESC,StudentID ASC
-
-
排序语句与TOP关键字联合使用
-
TOP N...ORDER BY:排序后取前N条数据
--查询编号为1的课程的前三名 SELECT TOP 3* FROM Grade WHERE SubID= ORDER BY Score DESC select top 5 * from StudentInfo where StudentID between 1 and 8 order by sex desc
-
-
TOP N WITH TIES...ORDER BY:排序后取前N行数据，并能取出与最后一条数据并列的所有语句
--查询编号为2的班级前两个，。。。 select top 2 with ties * from StudentInfo where classId=2 order by StudentID desc --查询编号为1的课程的前三名，允许并列 SELECT WITH TIES *FROM Grade WHERE SubjectID=1 ORDER BY sCORE DESC
第八章、DQL之模糊和聚合查询
8.1、专业单词
| 单词 | 释义 |
|---|---|
| LIKE | 像、如同、相似的、相同的 |
| SUM | 总和，总计 |
| AVG | Average的缩写，平均数、平均值 |
| MAX | 最大值 |
| MIN | 最小值 |
| COUNT | 计数、计算 |
8.2、模糊查询
语法：SELECT 查询列表 FROM 源表名
WHERE 表达式 LIKE '匹配表达式字符串'
| 通配符 | 含义 |
|---|---|
| % | 任意多个字符 |
| _(下划线) | 任意单个字符 |
| [ ] | 指定范围的单个字符 |
| [ ^] | 不在指定范围内的单个字符 |
-
“%”通配符
可匹配0到任意字符
例：“ %洞 ”：匹配以’ 洞 结尾的字符串‘
--‘%’0到任意多个字符 通配符 select * from StudentInfo where StudentName like '%星' select * from StudentInfo where StudentName like'%'--这个和没有过滤条件是一样的 --查询地址以“洞”结尾的学生姓名、地址信息。 SELECT StudentName as 姓名 ,Address as 家庭住址 FROM Student WHERE Address LIKE '%洞'
-
“ _ ” 通配符
"_"只能匹配一个任意字符
例：‘_ _ 杨’ ：匹配形如‘ xx杨 ’ 的字符串 select * from StudentInfo where StudentName like'_ _杰'
-
“ [ ] ” 通配符
表示匹配中括号内有=所列字符或范围的一个。
例：’ [杨，王 ]%‘：匹配以’ 杨 ‘ 或 ’ 王‘开头的字符串 --查询学生表中姓林、刘的所有学生信息 select * from StudentInfo where StudentName like '[林,刘]%' select * from StudentInfo where ClassID like'[1-3]' -- 范围匹配,只用于数字\英文字母
-
“ [ ^]”：通配符
表示不再括号所列之内的单个字符
例：[ ^杨，王]%’：匹配不以‘杨’和‘王’开头的字符串。 select * from StudentInfo where StudentName like '[^林,刘]%' --不是林和刘开头的 select * from StudentInfo where ClassID like'[^1-3]'--不在这个里面的
8.3、聚合查询
聚合函数表：
| 聚合函数名 | 函数功能 |
|---|---|
| SUM | 返回结果中指定列所有值得和 |
| AVG | 返回结果中指定列所有值得平均值 |
| MAX | 返回结果中指定列所有值得最大值 |
| MIN | 返回结果中指定列所有值得最小值 |
| COUNT | 返回结果中指定列或记录的数量 |
-
SUM聚合函数查询
SUM函数用于对数据的求和，它只能返回数值列的总和，其他类型的数据列是无法使用SUM函数的。
--求和 select sum(ClassID)as 求和结果 from StudentInfo--求和 函数 SUM
-
AVG聚合函数
--求平均值 select avg(studentId) as 平均值结果 from StudentInfo
-
MAX、MIN聚合函数
-- 求最大值 select max(StudentID)as 最大值 from StudentInfo --求最小值 select min(StudentID)as 最小值 from StudentInfo
-
COUNT聚合函数
COUNT函数用于获取结果集的记录行数。
COUNT函数有两种使用方式：
-
COUNT(列名)：COUNT函数统计该列下值的数目，该列下如果有NULL值则不计入，即这是一种忽略NULL值的计数。
--统计年龄大于20且有邮箱地址信息的学生人数 SELECT COUNT(Email) AS 学生人数 FROM Student WHERE Age>20
-
COUNT(*):COUNT函数获取整个结果集的记录行数，即使某一记录行中存在NULL值也被记入。
select count(*)as 总计录数 from StudentInfo
-
第九章、DQL之分组查询
9.1、专业单词
| 单词 | 释义 |
|---|---|
| GROUP | 小组、分组 |
| BY | 根据、依据 |
| HAVING | 所有、持有 |
9.2、分组查询
使用GROUP BY 子句可对每组数据做统计与汇总。
语法：GROUP BY 分组依据
-
单个字段分组查询
--在成绩表Grade中，按照学生编号分组，查询每个学生的最高考试分数 SELECT Student AS 学生编号 ,MAX(SCORE) 最高分 FROM Grade GROUP BY StudentID --班级表Class中，按照专业分组，查询每个专业有几个班级,并按照班级数量降序排列 SELECT Majio AS专业名,COUNT(*) AS 班级数 FROM Class GROUP BY Majio ORDER BY COUNT(*) DESC --学生表Student中，按照班级编号分组，查询每个班有多少人以及每个班学生的平均年龄，结果按照班级人数升序排列，如果人数相同，按照班级平均年龄的降序排列. SELECT ClassID 班级编号,COUNT(*) 班级总人数,AVG(Age) 班级平均年龄 FROM Student GROUP BY ClassID ORDER BY 班级总人数 ASC,AVG(Age) DESC
-
多个字段分组查询
--在学生表Student中，统计每个班级的男女学生人数，按班级编号升序排列，如果班级编号相同按照人数降序排列。 SELECT ClassID AS 班级编号,Sex AS 性别,COUNT(*) AS 人数 FROM Student Group BY ClassID,Sex ORDER BY ClassID ASC,人数 DESC
注意：
-
GROUP BY 后接分组依据时只能写原始列名，不允许写别名，也不能写聚合函数。
-
分组依据字段中如果有NULL值，NULL值也会参与分组。
-
text，ntext，image 类型的数据不能作GROUP BY子句的分组依据。
-
ORDER BY 后接排序依据时既可以写原始列名，也可以写别名，甚至是聚合函数列。
-
ORDER BY 子句必须跟在 GROUP BY 子句之后。
-
9.3、使用HAVING 子句过滤分组数据
语法：
HAVING 条件表达式
--在成绩表Grade中，查询成绩平均分大于75的学生编号和平均分信息，按学生编号的降序排列。 SELECT StudentID AS 学生编号,AVG(Score) AS 平均分 FROM Grade GROUP BY StudentID HAVING AVG(Score)>75 ORDER BY StudentID DESC --在学生表Student中，统计每个班级学生人数、平均年龄，并显示学生人数大于2的分组信息按平均年龄的降序排列。 SELECT ClassID AS 班级编号,COUNT(*) AS 学生人数 ,AVG(Age) AS 平均年龄 FROM Student GROUP BY ClassID HAVING COUNT(*)>2 ORDER BY 平均年龄 DESC --在学生表Student中，统计班级编号小于5的每个班级学生人数、平均年龄，并显示学生人数大于2的分组信息按平均年龄的降序排列。 SELECT ClassID AS 班级编号,COUNT(*) AS 学生人数 ,AVG(Age) AS 平均年龄 FROM Student WHERE ClassID <5 GROUP BY ClassID HAVING COUNT(*) >2 ORDER BY 平均年龄 DESC
注意：HAVING 子句后面的条件表达式中不允许出现列的别名
-
HAVING和WHERE的区别-
WHERE关键字针对的是数据行，而HAVING针对数据分组。
-
WHERE关键字不能与聚合函数一起使用，HAVING语句可以，而且一般都和聚合函数结合使用。
-
WHERE关键字在分组前对数据进行过滤，HAVING语句只过滤分组后的数据，AVING语句通常都和GROUP BY 语句结合使用。
-
-
使用分组查询时语法限制问题
使用GROUP BY语句进行分组查询时，SELECT子句中的非聚合函数列必须出现在GROUP BY子句中。否则编译器会报如下错误。
-
WHERE子句和GROUP BY子句连用时需注意的问题在SELECT语句中如果同时出现了WHERE、GROUP BY、HAVING和ORDER BY子句，它们的排列顺序应该是先写WHERE子句,然后写GROUP BY子句，再其次写HAVING子句，最后写ORDER BY子句。否则编译器会报如下错误。
第十章、DQL之多表连接查询
10.1、专业单词
| 单词 | 释义 |
|---|---|
| JOIN | 加入、连接 |
| INNER | 内部、里面 |
| OUTER | 外部、外面 |
| LEFT | 左边的、左侧的 |
| RIGHT | 右边的、右侧的 |
| CROSS | 十字型、交叉的 |
10.2、多表查询概述
将多个表结合在一起的查询就是多表查询，也可称为连接查询。
多表查询分为：
-
内连接查询
-
外连接查询
-
交叉连接查询
10.3、内连接查询
-
标准的内连接查询
内连接使用比较运算符对各个表中的数据进行比较操作，并列出各个表中与条件相匹配的所有数据行。
语法：
SELECT 查询列表 FROMM 表1[INNER] JOIN 表2 ON 表1.列 = 表2.列 [其他子句]
参数说明：
查询列表：表1和表2中的全部或部分指定的字段列。
表1[INNER] JOIN 表2：将表1和表2进行内连接，INNER关键字可以省略。
ON 表1.列 = 表2.列：连接条件，其中列为表1与表2的关联列（通常是两张表相关联的主键列和外键列）。
[其他子句]：可选子句，如WHERE、GROUP BY 、ORDER BY子句等对结果集进行过滤、分组和排序功能，按需求选择使用。
-
内连接查询两张表的所有列数据
--查询学生编号小于18的学生信息，包括学生所在班级的名称。需对学生表Student和班级表Class做内连接查询。 SELECT * FROM Student INNER JOIN Class ON Student.ClassID = Class.ClassID WHERE StudentID < 18
-
内连接查询两张表部分列的数据
--查询学生姓名、所属班级编号和班级名称，并以班级编号升序排列. SELECT Student.StudentName,Student.ClassID,Class.ClassName FROM Student JOIN Class ON Student.ClassID = Class.ClassID ORDER BY Student.ClassID
-
内连接查询两张以上表的数据
--查询学生各科成绩，要求显示学生姓名，课程名称和相应分数。 SELECT S.StudentName AS 姓名,J.SubjectName AS 课程名称,G.Score AS 分数 FROM Student AS s JOIN Grade AS G ON S.StudentID = G.StudentID JOIN Subject AS j ON G.SubjectID = j.SubjectID
-
内连接与分组聚合查询结合使用
--查询每个班级学生人数和平均年龄，学生人数大于2的分组信息，分组信息中需出现班级的名称，按平均年龄降序排列。 SELECT C.ClassName 班级名称,COUNT(*) 班级人数,AVG(S.Age) 平均年龄 FROM Student AS S JOIN Class AS C ON S.ClassID = C.ClassID GROUP BY C.ClassName HAVING COUNT(*)>2 ORDER BY 平均年龄 DESC
-
-
简单多表查询完成内连接查询操作
在SQL Server中还可以在FROM子句中，直接列出所有要连接的表，在WHERE子句中指定连接条件。也可以完成内连接查询功能。
语法：
SELECT 查询列表 FROM 表1，表2 WHERE 表1.列 = 表2.列 [其他子句]
-
使用简单两个表查询
--查询学生姓名，学生所在班级编号和班级名称。 SELECT S.StudentName,S.ClassID,C.ClassName FROM Student S,Class C WHERE S.ClassID = C.ClassID
-
使用简单多表查询方式
--查询每个班级学生人数和平均年龄，显示学生人数大于2的分组信息，分组信息中需出现班级名称，按平均年龄降序排列。 SELECT C.ClassName 班级名称,COUNT(*) 班级人数,AVG(S。Age) 平均年龄 FROM Student AS S ,Class AS C WHERE S.ClassID = C.ClassID GROUP BY C.ClassName HAVING COUNT(*)>2 ORDER BY 平均年龄 DESC
-
10.4、自连接
在多表查询中，不仅可以对多个不同的表进行连接查询，还可以一个表与自己进行连接查询，即自连接。
原理：将表如同分身一样分成两个，使用不同的别名，成为两 个独立的表，之后的操作与多表连接操作一致。
使用场景：自连接在查询无限分级的表数据时可以发挥非常重 要的作用。
--在部门表Department中，查询每个部门名称及其上级部门名称。 SELECT D1.DepartmentName AS 部门名,D2.DepartmentName AS 上级部门名称 FROM Department D1 JOIN Department D2 ON D1.ParentDeptID = D2.DepartmentID
10.5、外连接
外连接通常用于相连接的表中至少有一个表需要显示所有匹配数据行。
外连接分为：
-
左外连接
-
右外连接
-
全外连接
-
左外连接
左外连接的结果集中包含了左表的所有匹配记录，而不仅仅是满足连接条件的记录。
语法：
SELECT 查询列表 FROM 表1 LEFT [OUTER] JOIN 表2
ON 表1.列 = 表2.列 [其他子句]
--查询学生姓名、课程编号和分数，结果集中需要包含没有成绩的学生姓名,以学生姓名升序排列。 SELECT S.StudentName AS 学生姓名, G.SubjectID AS 课程编号,G.Score AS 成绩 FROM Student s LEFT OUTER JOIN Grade G ON S.StudentID = G.StudentID ORDER BY 学生姓名
-
右外连接
右外连接的结果集中包含了右表的所有匹配记录，而不仅仅是满足连接条件的记录。
语法：
SELECT 查询列表 FROM 表1 RIGHT [OUTER] JOIN 表2
ON 表1.列 = 表2.列 [其他子句]
--查询学生姓名和班级名，查询结果中需要包含所有班级名，按学生姓名升序排列。 SELECT S.StudentName AS 学生姓名,C.ClassName AS 班级名称 FROM Student S RIGHT OUTER JOIN Class C ON S.ClassID = C.ClassID ORDER BY 学生姓名
-
全外连接
全外连接的结果集中包含了左表和右表中的所有匹配记录。
语法：
SELECT 查询列表 FROM 表1 FULL [OUTER] JOIN 表2
ON 表1.列 = 表2.列 [其他子句]
SELECT S.StudentName AS 学生姓名,C.ClassName AS 班级名称 FROM Student S FULL OUTER JOIN Class C ON S.ClassID = C.ClassID ORDER BY 学生姓名
10.6、交叉连接
交叉连接又称为非限制连接，将两个表组合在一起而不限制两源表之间的联系。
语法：
SELECT 查询列表 FROM 表1 CROSS JOIN 表2
[WHERE子句]
--显示学生表和班级表所有的数据组合情况 SELECT * FROM Student CROSS JOIN Class
交叉连接也可以使用WHERE限制条件，没有WHERE子句的交叉连接返回的结果也被称为“笛卡尔积”。
浙公网安备 33010602011771号