SQL Server高级查询与T-SQL编程笔记

一、数据库设计

1.数据流程图

1.1箭头表示数据流

1.2圆或椭圆表示加工

1.3双杠表示数据存储

1.4方框表示数据的源点或终点

2.数据字典

描述数据的信息集合，是对系统中使用的所有数据元素的定义的集合

3.E-R模型

3.1E-R图表示方法

3.1.1实体：矩形，矩形框内写明实体名称

3.1.2属性：椭圆，并用无向边将其与相应实体相连接

3.1.3联系：菱形：菱形框内写明联系名称，用无向边与相关实体相连接

3.2标识实体的原则

3.2.1实体通常是一个名词

3.2.2每一个实体仅描述一件事情或一个事物

3.2.3每个实体都是唯一的，即不能出现含义相同的实体

3.2.4联系通常是一个动词或动名词，其名称反应出实体之间的内在关联

4.联系的转换

横线（主键）

波浪线（外键）

4.1 1:1联系的转换

4.2 1:n

4.3 m:n

二、数据应用

1.DDL语句维护数据表结构

1.1创建和维护数据库

create database database-name 创建

drop database database-name 删除

1.2创建和维护数据表结构

1.2.1创建数据表

create table table-name(

        字段名1 数据类型 [列级别约束条件][默认值],

        字段名1 数据类型 [列级别约束条件][默认值],

        ......

)

create database shop(
    shopID int identity(1,1) primary key,   主键
    city varchar(50) default'武汉',         默认约束
    foreign key (customerID) references customer(customerID)    
    外键，参照客户表主键customerID
)

1.2.2新增字段

alter table 表名 add 字段名 数据类型

1.2.3删除字段

alter table 表名 drop column 字段名 

1.2.4修改字段名

alter table 表名 raname column 旧字段名 to 新字段名

1.2.5修改字段类型

alter table 表名 alter column 字段名 数据类型

2.DML语句维护数据表内容

DML——检索（查询）、更新（插入、删除、修改）

2.1新增表记录

insert [into] table-name[(字段列表)] values(值列表)

2.2更新表记录

update table-name set 字段名1=值1,字段名2=值2,.....,字段名n=值n  [where条件表达式]

2.3删除表记录

delete from table-name [where条件表达式]

3.select基本查询

3.1select基本结构语法

select <column1,column2,column3...> from <table-name>

[where <条件表达式>]

[group by column1,column2,column3.... having<条件表达式>]

[order by <column1,column2,column3....> [asc或desc]]

说明：

a.必须的子句只有select子句和from子句

b.where子句用于对查询结果进行过滤

c.group by子句根据指定列分组，having子句对分组后的结果进行过滤

d. order by子句用于对查询结果进行排序。默认asc升序

3.2查询结果排序

select title as 标题,originalPrice as 原价,currentPrice as 团购价 
from product 
order by categoryID,currentPrice desc

3.3distinct关键字

过滤重复字段信息

select distinct customerID from orders

3.4使用top n返回指定行数

返回前几行数据

select top n column1,column2.... from table

select top 3 title 标题,currentPrice 团购价 
from product 
order by currentPrice

3.5模糊查询  like

% 匹配0到多个任意字符

-匹配任意一个

[]匹配指定一个字符集合

select title as 标题,originalPrice as 原价,currentPrice as 团购价 
from product
where title like '%KTV%'

4.聚合函数

sum 总和

max 最大值

min 最小值

avg 平均值

count 计数

select max(currentPrice) 最高团购价,min(currentPrice) 最低团购价,
avg(currentPrice) 平均团购价,count(*) 数量,sum(salesCount) 销售数量合计
from product
where categoryID=7

注：

1.count(*)用于统计当前表所选取的行数

2.count(col)用于统计当前表所选取的col列值不为null的行数

3.sum(col)用于统计当前表所选取的col列的值

count统计数据行数，sum汇总数据

5.分组查询

group by 字段列表 [having 条件表达式]

5.1基本分组查询

select categoryID 商品类型编号,count(*) 商品数量,avg(currentPrice) 平均团购价
from product
group by categoryID 
order by count(*),平均团购价 desc

5.2带条件分组查询

select categoryID 商品类型编号,count(*) 商品数量,avg(currentPrice) 平均团购价
from product
where shopID is not null
group by categoryID 
order by count(*),平均团购价 desc

5.3having子句对分组结果进行过滤 

select categoryID 商品类型编号,count(*) 商品数量,avg(currentPrice) 平均团购价
from product
where shopID is not null
group by categoryID having avg(currentPrice)>100
order by count(*),平均团购价 desc

6.连接查询

6.1内连接查询

select fieldlist(字段列) from table1 [inner] join table2 on table1.column1=table2.column2

null不与任何值匹配包括它本身

内连接是从结果表中删除与其他被连接表中没有匹配上的所有行，所以内连接可能会丢失信息 

6.2简单多表查询

select fieldlist(字段列) from table1,table2 where table1.column1=table2.column2

三、子查询

1.单行子查询

1.1子查询实质

select语句的查询结果能作为另一个语句的输入值

可用于where子句中

可用于from子句中

可以字段的形式出现在select语句的选择列中

1.2子查询应用

select title 商品标题,currentPrice 商品团购价
from product 
where areaID=(select areaID from area where areaName='江汉路')

返回结果只有一行数据

可使用单行比较符=、>、<、>=、<=、<> (不等于)进行比较

1.3子查询使用经验

1.3.1子查询通常用在位于select语句的where子句中，且可以嵌套

1.3.2编写复杂的子查询的解决思路：逐层分解查询，从最内层开始

1.3.3子查询的执行过程遵循“由里及外”的原则

1.3.4一般情况下，连接查询可改为子查询实现，但子查询不一定可以改为连接查询

1.3.5当子查询执行结果的行数较大，而主查询执行结果的行数较小时，子查询执行效率较高

2.多行子查询

返回结果是多行数据

常见的多行比较符in、all、any、some

2.1in比较符

例：查询营业地点在江汉路、武广、亚贸地区的商品信息时，要求输出商品标题、商品团购价和区域编号

select title 标题,currentPrice 商品团购价
from product
where areaID in(select areaID 
                from area 
                where areaName='江汉路' or areaName='武广' or areaName='亚贸')

2.2all关键字子查询

表达式或字段 多行比较运算符 all(子查询)

all运算符的含义：<all 表示小于最大值 比所有小

                             >all 表示大于最小值 比所有大

例&#x