sqlserver数据库的基础理论：

数据库术语

dbs：数据库系统
dbms：是一款软件，主要用于操作db
dba：是数据库管理员
db：数据库

sqlserver数据库分类

系统数据库
    master
    model
    msdb
    tempdb
用户自定义数据库

jdbc、mybatis、mybatisplus链接数据库需要提供四大参数：

1、驱动【不同数据库驱动不一样】
2、链接
3、账号【sqlserver：sa     mysql：root   oracle：orcl】
4、密码【123】

sqlserver数据库文件

.mdf    主要数据文件  ==1 有且只有一个
.ldf    日志文件        >=1 至少有一个
.ndf    次要数据文件  >=0 可有可无可多个

sqlserver数据库迁移技术

①分离---附加     正常境况下，cv不能够进行数据库的复制和粘贴，需要先进行分离。
    ②脱机---联机     
    ③备份---还原     先进行备份，然后再删除，在点击还原。

如何创建表：

①在新创建的数据库里面，找到表，右键单击表，选择创建表。
②根据自己的需求分别把列名和数据类型填好，允许null值，等到后面有字段可以进行联表的时候，在勾选。
③选中第一行第一列（通常都是id）单击右击设置主键
④找到下面的列属性，点击标识规范，双击是标识。
⑤然后点击ctrl＋s保存，编辑表的名称

如何进行联表操作：

①找到外键表，单击右键，选择设计点击。
②点击右键，找到关系，点击左下角的添加。
③找到表和列的规范哪一行，看到最右边的三个点，点击。
④通过主键表的下拉框选择主键，属性就是主键表的id。
⑤找到右边的外键表，进行选择，属性就是主键表的主键。
⑥然后点击确定，就可以完成

如何设置数据库关系图

①找到数据库下面的，数据库关系图，单击右键，新建数据库关系图。
②选中要进行设置关系图的表，然后点击添加，即可

如何进行数据添加

①单击右键，点击编辑前200行，然后就可以进行数据的插入。

数据完整性：

实体完整性

    作用：保证数据表中每行记录都是唯一的
        分类：
            自增  identity(1,1)
            主键  primary key     
            唯一  unique
注意：
  主键约束要求列植非空，而唯一键约束允许列植为空。
注意：
  外键的列名可以与主表的主键不同名，但一般约定，外键名与主表的主键同名。

域完整性

作用：保证列中数据的有效、合法、合乎规则
        分类：
            默认约束    default(默认值)
            检查约束    check(字段表达式)
            非空      not null

引用完整性

作用：两张表之间的主外键
        references 主表(键)

SQL Server2012 常见数据类型

①整数类型 int                  年龄：21、商品件数：500
②小数类型 decimal              实发工资：9860.3
③固定长度非Unicode 字符 char   存储固定长度非 Unicode 字符，最多可存储8000 个字符      公司名：“天智立”
④可变长度非Unicode 字符  varchar    存储可变长度非 Unicode 字符但根据实际存储的字符数改变存储空间。一个字符占一个字节，最多可存储 20 个字节。varchar(20)最多只能存储 20 个字符，不够 20 个字符按实际容量存储        公司地址：荆州市长江大学东校区汉科 3 巷 9 号
⑤可变长度Unicode 字符  nvarchar       与 varchar 类似，存储可变长度 Unicode 字符数据，最多可存储 4000 个字符。一个字符占两个字节，可将全世界的文字囊括在内。    可存储中文、日文、韩文等双字节字符信息。
⑥日期  date        出生年月：2014-06-24
⑦日期时间   datetime      订单生成时间：2013-05-20 11：00：00

sqlserver的DML操作：

1. insert 添加语句
2. update 更新语句
3. delete 删除语句
4. select 查询语句

使用 insert 语句插入数据

一次添加一行数据：

语法：**insert [into] table_name [(column1,column2)] values (value1,value2……)**

语法说明：

（1） table_name：表名。
​
（2） column：列名（字段名）。
​
（3） value：需要添加到表字段的值。
​
（4） into 可省略。 

注意：

value 值列表顺序及个数与字段列表顺序及个数要一一对应
​
（1）添加数据时，如果字段类型为字符类型或日期时间类型，必须用单引号‘’，而不能用双引号“”。
​
（2）insert 语句对字段名的顺序并无要求，但是值必须与字段名一一对应。
如：insert Employee(Name,Gender ,RankID) values ( ‘干亚军’,’男’ , ’203’)。
​
（3）添加记录时，自增的列无须显式地添加。

示例1：

insert Employee (Name,Gender,Birthday,RankID)values ('许宏涛','男','1989-04-12','205')

经验：

表中每列数据都添加值时，字段名可以省略。如上述 insert 语句，Rank 表的两个字段 RankID 和 RankName
都要添加值，所以在表名 Rank 之后，可以省略字段列表(Rank, RankName)。

对允许为 NULL 的字段添加数据时，有以下两种方式：

1） 直接使用 NULL 作为值进行添加，如：
​

insert Employee (Name, Gender,RankID) values (‘梅毅君’ , ’女’ , NULL)

​
2） 添加时不加指定列名。允许为空的字段值均置为 NULL，如：
​

insert Employee (Name, Gender) values (‘段成瑞’ , ’男’ )

示例2：

insert Rank values(‘301’,’董事会秘书’) 

经验：

表中每列数据都添加值时，字段名可以省略。如上述 insert 语句，Rank 表的两个字段 RankID 和 RankName
都要添加值，所以在表名 Rank 之后，可以省略字段列表(Rank, RankName)。

一次添加多条数据

向数据表添加数据时，还可以使用 union 关键字实现一次添加多行数据。

union 译为“联合、合并”，在 SQL Server 中可用于多条数据的合并。

语法：

insert [into] table_name (column1,column2..)
select value1,value2.. union
select value1,value2..

示例1：

insert Employee (Name, Gender,Age,Rank)
select ‘张立星’ , ’男’ , 27 , ’204’ union
select ‘刘晓慧’ , ’女’ , 28, ’105’

注意：

使用 union 一次添加多行数据时，只要按照语法结构顺序写出需要添加的数据即可，注意最后一条数据之
后无须添加“union”。

使用 update 语句更新数据

语法：

update table_name set column1=value1 , column2=value2……
[where condition]

condition 表示条件，省略时表示更新表中所有记录的指定字段值。更新多个列时，每个“列=值”对之间要用逗号隔开。

示例1：更新所有行单个字段值

update Employee set Address = ’深圳市龙岗区坂田’

示例2：更新多个字段值

update Employee set Country = ’中华人民共和国’ , Address = ’深圳市’

示例3：根据指定条件更新记录

 update Employee set Salary=Salary+1000 where RankID= ’ 204 ’

使用 delete 语句删除数据

语法：

delete from table_name[where condition]

注意：

WHERE 条件子句为可选，若不存在 where 子句，则删除整张表中的全部数据。

删除表中所有数据

delete from Employee

注意：

delete 语句仅删除表中的内容，并不删除表结构。

按指定条件删除记录

delete from Employee where RankID=’106’

经验：

delete 语句中的 where 条件与 update 语句中的 where 条件作用相同，均为筛选符合条件的记录。

删除主表记录

delete from Rank where RankID= ’106’
在有关联的情况下，直接删除主表，会报错的。
​
Employee 表的 RankID 列是外键，它参照（引用）Rank 表的主键 RankID，而在从表即 Employee表中，有记录的 RankID 值为 ’106’

经验：

通过设置从表 Employee 的外键约束的“删除规则”，可以解决删除被从表引用的主表记录时所发生的异常。

方法一：

修改“删除规则”为“设置 NULL”
​
修改“删除规则”为“设置 NULL”之后，当删除主表记录时，从表中引用了主表记录的外键值均被系统赋值为null

方法二：

修改“删除规则”为“级联”
​
此时执行 delete from Rank where RankID=’106’，不仅表 Rank 表 RankID 为 106 的这条记录被删除，而且 Employee 中 RankID 为 106 的员工记录也一并被删除。

使用 select 语句查询数据

语法：

select * from table_name
table_name 为表名。

示例1：

select * from Employee

DML操作的练习：

添加歌曲类型数据：

insert Type (TypeName) values (‘热门流行’)

添加歌手数据：

insert Singer(SingerName,SingerType,Birthday,Area,PhotoPath)
values (‘李谷一’,’女’,’1944-11-10’,’大陆’,’李谷一.jpg’ )

添加歌曲数据：

insert Song(Title,PingTitle,TypeID,SingID,URL)values ('传说','cs',1,4,'传说.mp3')

将名为“动漫”的歌曲类型名修改为“游戏动漫”:

update Type set TypeName=’游戏动漫’ where TypeName=’动漫’

将歌手名为“东方神起”的歌手类型修改为“女”，Birthday 值修改为“1949-08-15”：

update Singer set SingerType=’女’,Birthday=’1949-08-15’where SingerNmae=’东方神起’

删除歌手地区名为“日韩”的歌手信息:

delete from Singer where Area=’日韩’

删除名为“游戏动漫”的歌曲类型:

delete from Type where TypeName=’游戏动漫’

数据查询基础

\1. select 基本结构

\2. 选择列表

\3. 条件查询

\4. 查询结果排序

\5. 使用 top N 返回指定行数

select 基本语句查询 :

语法：

select 
from 
[where <条件表达式>]
[group by column1 , column2 , column3… | having <条件表达式>]
[order by < column1 , column2 , column3… >[asc 或 desc]]

说明：

(1) 必须的子句只有 select 子句和 from 子句。
(2) where 子句用于对查询结果进行过滤。
(3) group by 子句根据指定列分组，having 子句对分组后的结果进行过滤
(4) order by 子句用于对查询结果进行排序。asc 表示升序排序，desc 表示降序排序，默认按 asc 排序。

选择列表 :

选择列表在 select 关键字之后，用于指定需要查询返回的结果集中包含的字段（列）

所有列 :

当查询结果鸡需要返回表中的全部列时，可使用“*”代替全部列名。

示例1：查询华为所有员工的全部信息。

select * from Employee

部分列 :

。部分查询时，各列之间用“，”隔开。

示例1：查询华为公司职级表 Rank 中的所有记录的职级名称

select RankName from Rank

注意：

SQL Server 2012 对表名、列名及关键字，都不区分大小写。如 Rank 可写成 rank,SELECT 也可写成 select。
但 SQL Server 2012 命名规范要求，关键字一般使用大写。

示例2：查询职级表中编号 RankID 和职级名 RankName 的信息

select RankID，RankName from Rank

列标题 ：

。在 select 查询语句中，可以用 as 或空格使查询结果中的列名显示为

列标题（也可以称为列别名）。

别名的三种形式：

使用=关键字 显示列标题。

语法：

select  职级编号=RankID，职级名称=RankName from Rank
go

①使用 AS 关键字显示列标题

语法：

select 字段名 1 AS 别名 1，字段名 2 AS 别名 2…from 表名

示例1：

select RankID AS 职级编号，RankName AS 职级名称 from Rank
go

②使用空格显示列标题名：

语法：

select 字段名 1 别名 1，字段名 2 别名 2… from 表名
go

示例2：

select RankID 职级编号，EmployeeID 员工编号，Name 员工姓名 Salary月基本工资 from Employee
go

③使用 distinct 过滤重复行 ：

语法：

select distinct 字段名 from 表名

示例1：

select distinct RankID from Employee

④选择列为表达式 :

select 子句中选择列可以直接基于正在查询的表的各个列，也可以在此基础上做进一步的处理。如查询所有员工的姓名、月基本工资，以及增长 10%之后的月基本工资

select Name 姓名，Salary 月基本工资，Salary*1.1 加薪后月基本工资 from Employee
go

条件查询:

数据表中包含大量的数据，根据需要，有时只需显示满足一定条件的数据。使用 where 子句，可对查询结果进行过滤。在 where 子句的表达式中，经常使用两种运算符：比较运算符和逻辑运算符

SQL Serve 2012 比较运算符:

运算符      含义
=          等于
>          大于
<          小于
>=         大于等于
<=         小于等于
<>         不等于
!=         不等于

SQL Server 2012 逻辑运算符:

运算符         含义
and           并且
or            或者
not           取反
is null       如果列值为 null 则返回 true，否则返回 false

单条件查询 :

单条件查询是指在 where 子句后只有一个查询条件。

示例1：列出职级号为 203 的职级信息。

select * from Rank where RankID=’203’

示例2：列出基本工资不低于 20000 的员工信息。

select * from Employee where Salary>=20000

示例3：列出职级名不为“董事会秘书”的所有职级信息。

select * from Rank where RankName !=’董事会秘书’
或
select * from Rank where RankName<>’ 董事会秘书’

多条件查询 :

多条件查询是指在 where 子句中包括多个查询条件。

示例1：列出出生年月晚于’1983-01-01’，且月基本工资高于 7000 的员工信息

select * from Employee where Birthday>=’ 1983-01-01’ and Salary>7000

示例2：列出月基本工资高于 20000 或职级名称为“工程师”的员工信息，职级名称“工程师”的职级编号为 204

列出月基本工资高于 20000 或职级名称为“工程师”的员工信息，职级名称“工程师”的职级编号为 204

查询结果排序:

使用 order by 子句对查询结果进行排序

单列排序 :

单列排序是指按照某一列的升序或降序排列，即 order by 子句之后只有一个列名。

示例1：显示所有职级信息，按职级编号升序排序

select RankID, RankName from Rank order by RankID asc

由于排序默认为升序排序，所以可省略 asc

select RankID, RankName from Rank order by RankID

示例2：查询全部员工号姓名职级号和月基本工资信息，按月基本工资降序排列。

select EmployeeID 员工编号，Name 员工姓名，RankID 职级编号 Salary 月基本工资 from Employee order by Salary desc

多行排序 :

除了可以进行单行排序之外，还可以按多行进行排序。排序原则是：依照排序列从左至右的次序确定优先级。

示例1：查询员工信息，要求按职级号升序排序，如果职级相同，则按月基本工资降序排列。

select * from Employee order by RankID, Salary desc

使用 top N 指定返回行数:

select 语句所查询到的所有符合条件的记录行，而使用可选的 top选项可以指定返回的行数

top N :

在 SQL Server中 select 语句，使用“top N”返回结果集的前 N 行数据，其中 N 为正整数

语法：

select top N < column1，column2，column3… > from 

示例1：查询月基本工资最高的 5 个员工

select top 5 * from Employee order by Salary desc

top N percent :

与“top N”不同，“top N percent”按百分比返回数据行，如“top 20 percent”返回前 20%行数据。

示例1：按月基本工资降序排序，显示前 20%的员工。

select top 20 percent * from Employee order by Salary desc

任务 1 使用 select 查询歌手信息 ：

◎ 查询歌手表中中国大陆男歌手的部分信息。

select s.SingerID 歌手编号，s.SingerName 歌手姓名，s.SingerType 歌手类型，s.Area 歌手所在地区 from Singer s where s.SingerType=‘男’and s.Area=“大陆”。

◎ 查询歌手表中台湾女歌手的全部信息。

select * from singer where singertype = '女' and area = '台湾'

◎ 查询歌手表中欧美歌手和香港女歌手信息。

select * from Singer whereArea=’欧美’ or (Area=‘香港‘ AND SingerType=’女’)

显示出生年月晚于 1980-1-1 的台湾女歌手的全部信息：

select * from singer s where s.area = '台湾' and s.singertype = '女' and s.birthday > '1980-01-01'

任务 2 使用** TOP N 和 ORDER BY 显示歌曲、歌手信息

◎ 按出生年月升序显示男歌手信息

select * from singer s where s.singertype = '男' order by s.birthday  asc 

◎ 显示歌曲排行榜中前五名的歌曲信息

select top 5 s.singid 歌曲编号,s.title 歌曲名称,s.typeid 歌曲类型,s.singerid 歌手编号,s.hit 点歌率  from song s order by s.hit 

◎ 按歌曲名长度升序和点歌次数降序显示显示歌曲信息

select * from song s order by len(s.title) ,s.hit desc 

模糊查询和聚合函数 :

1，模糊查询：like、in、between..and

2．聚合函数：sum、max、min、avg、count

模糊查询 ：

模糊查询时利用数据的部分信息进行查找的一种查询方式。如果仅知道查询条件的部分信息，则可使用 like 运算符进行模糊查询。

LIKE 运算符 ：

通过使用通配符来实现 LIKE 运算。通配符是一种在 where 子句中拥有特殊意义的字符，通配符包括“%”，“_”和“[]”。

注意：

只有字符类型的数据才能使用 like 运算符和通配符

“%”通配符:

匹配0到多个任意字符

用法1： 开头、结尾匹配。

示例1：查找华为职员表中，以“李”字开头的所有职员姓名，SQL语句如下：

select Name from Employee where Name like ‘李%’

示例2：查询职员表中职级编号以“1”结尾的所有职员信息，SQL语句如下

select * from Employee where RankID like ‘%1’

用法2：中间匹配。

示例1：查询职员表中职员姓名以“许”字开头、“涛”字结束的所有职员信息

select * from Employee where Name like ‘许%涛’

用法3：两端匹配。

示例1：查询职员表中职员姓名含“军”字的职员信息，

select * from Employee where Name like ‘%军%’

“_”通配符

“_”通配符的功能与“%”类似，仅匹配任意一个字。如需匹配两个字符，则使用“__”。

用法1：匹配一个字符。

示例1：查询职员表中职员姓名以“张”开头，“星”字结束且姓名为3个字的职员信息

select * from Employee where Name like ‘张_星’

用法2：匹配多个字符。

示例1：查询职员表中职员姓名以“李”字开头且姓名为3个字的职员信息

select * from employee where name kile '李__'

“_”与“%”综合应用

使用模糊查询时，有时需要综合“_”与“%”来完成

示例1：查询职员表中职员姓名中第二个字符为“晓”的职员信息

select * from empliyee where name like '_晓%'

“[]”通配符:

方括号“[]”用于指定一个字符集合。注意：

“[]”中的字符不能是通配符。如 LIKE 5[%]，其中，“[]”内的“%”不能理解为“通配符”，它仅表示“%”字符

查询条件表达式         匹配数据示例
like’5[a]’          5a
like’5[%]’          5%，“%”在[]中不能作为通配符使用
like’5[_]’          5_,“_”在[]中不能作为通配符使用
like’[a,b,c]def’    Adef或bdef或cdef
like’[abc]def’      Adef或bdef或cdef
like’[a-f,M-T]’     a、b、c、d、e、f、M、N、O、P、Q、R、S、T
like’abc[_]d’       Abc_d
like’a[^a]d%’       不能为aad...，^a表示不能取a

in 运算符 :

in 运算符也称为“成员条件运算符”，用于测试一个值是否在一个指定的数据集合之内

示例1：如查询职员的工作地点在武汉或南京的职员信息

select * from employee where address in('南京','武汉')

使用 or 运算符也可以完成上述查询

select * from employee where address = '南京' or addrsss = '武汉'

in运算符与OR运算符相比，其优点是：当选择条件很多时，采用in运算符运行效率更高

between..and 运算符 ：

在where子句中，可以采用between..and运算符选取介于两个值之间的数据，值可以是数字、字符和日期类型（取值包含范围边界）

用法1：比较值为数字类型数据。

示例1：查询月基本工资在20000~50000之间的职员信息

select * from employee where salary between 20000 and 50000

用法2：比较值为日期的类型数据。

示例1:查询员工出生日期在‘1970-01-01’到‘1980-01-01’之间的员工信息

select * from employee e where bitrhday between '1970-01-01' and '1980-01-01'

聚合函数:

聚合函数可以统计记录行数，计算某个字段值的总和，以及它的最大值、最小值和平均值等

函数名称          函数功能
sum             返回选取的某列值的和
max             返回选取的某列的最大值
min             返回选取的某列的最小值
avg             返回选取的某列的平均值
count           返回选取的某列或记录的行数

sum 函数 :

sum函数用于对数据求和，返回选取结果集中某列值的总和，可以计算单列或多列的总和

示例1：本工资总额

select sum(salary) 月工资总和 from employee

max/min 函数:

max、min 函数分别用于返回数据类型列的最大值和最小值

示例1：查询华为职员的最高月基本工资

select max(salary) 月最高工资 from employee

示例2：查询华为职员的最低基本工资

select min(salary) 月最低工资 from employee

示例3：查询华为年龄最大的职员的出生你年月

select min(birthday) 年龄最大的员工 from employee

avg 函数:

avg函数用于返回数据列的平均值（列值为NULL的不参与运算）

示例1：查询华为职员月基本工资平均值

select avg(saslary) 平均工资 from employee

count 函数 :

count函数用于统计记录行数。使用count 函数时，必须指定一个列的名称或使用“*”可获取整张表的记录行数

注意：

当使用 count（column1） 统计 column1 列的值的数目时，column1 列值为 NULL 的不计入。除 count(*)之外，聚合函数均忽略空值。

示例1：统计华为职员的总人数

select count(*) 总人数 from emoloyee
select count(employeeid) 总人数 from employee

示例2：统计出生年月大于‘1983-1-1’的华为职员的人数

select count(*) 人数 from employee where birthday > '1983-01-01'

示例3：统计有出生记录（即列Birth的值不为NULL）的员工数

select count(birthday) from employee

聚合函数总和应用 :

示例1：查询华为出生年月大于‘1983-1-1’的职员总人数、平均月基本工资、最高月基本工资和最低月基本工资

select count(*) 总人数,avg(salary) 平均工资,max(salary) 最高月工资,min(salary) 最低月工资,from employee where birthday > '1983-01-01'

示例2：查询职级为204的职员人数、平均月基本工资、最高月基本工资和最低月基本工资

select avg(salary) 平均工资,max(salary) 最高月工资,min(salary) 最低月工资 from employee where rankid = '204'

任务 1 使用模糊查询查找歌手信息 :

1）使用 day（d）函数返回指定 d 在当月的第几天。

2）使用 month(d)函数返回指定日期 d 的月份。

3）使用 year(d)函数返回指定日期 d 的年份值

（1）显示李姓歌手信息。

select * from singer where singername like '李%'

（2）显示蔡姓且姓名为 3 个字的歌手信息。

select * from singer where singername like '蔡__'

（3）显示年龄在 30~40（含 30 到 40）之间的歌手信息。

select * from singer where year(getDate()) - year(birthday) between 30 and 40

（4）显示地区为欧美和日韩的歌手信息。

select * from singer where area in('欧美','日韩')

（5） 显示“一”开头的歌曲信息。

select * from song where title like '_%'

（6） 显示歌曲名长度为 4 的歌曲信息。

select * from song where len(title) = 4

（7） 显示经典老歌和影视金曲歌曲信息。

select * from song s,type t where t.typename in ('经典老歌','影视金曲') and t.typeid = s.typeid

（8） 显示点击次数在 30~60 的歌曲信息。

select * from song s where s.hit between 30 and 60 

任务 2 使用聚合函数统计歌手信息:

（1）查询香港和台湾歌手中年龄最小和最大的女歌手生日。

select max(birthday) 最大歌手生日,min(birthday) 最小歌手生日 from singer where area in('香港','台湾')

（2）计算大陆男歌手平均年龄

select avg(year(getDate()) - year(birthday)) 平均年龄 from singer where area = '大陆' and  singertype = '男'

（3）统计香港和台湾女歌手数量。

select count(*) 数量 from singer where singertype = '女' and area in('台湾','香港')

（4） 汇总歌曲点歌次数（注意：使用 sum 函数而不是 count 函数）。

select sum(hit) 总点歌数 from song

（5） 查询演唱者编号为 12 的最受欢迎歌曲的点击次数。

select max(hit) 最受欢迎,sum(hit) 总点歌数 from song 
where singerid = 12

（6） 查询类型名为“热门流行”的最不受欢迎的歌曲的点歌次数。

select min(s.hit) 最不受欢迎 from song s,type t where t.typename = '热门流行'

（7） 计算所有歌曲的平均点歌次数。

select avg(hit) 总点歌次数 from song

（8） 计算歌曲类型名为“经典老歌”的歌曲数量

select count(*) 歌曲数量 from song s,type t where t.typename = '经典老歌'

分组查询和联接查询：

1. 使用 group by 子句进行分组查询

2. 使用 having 子句对分组结果进行过滤

3. 语句联接查询

4. 简单多表查询

使用 group by 分组：

group by 从英文字面的意义上可以理解为“根

据（by）一定规则进行分组（Group）”。它的作用是通过一定的规则将一个数据集划分若干个小的区域，然后针对若干个小区域进行统计汇总

语法：

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

其中，“字段”表示进行分组所依据的字段名称，可以为多个字段。“having <条件表达式>”用于指定 group by 分组显示时需要满足的限定条件。

创建分组:

group by 子句通常与聚合函数同时使用，如 MAX、MIN、COUNT、SUM 和 AVG

示例1:按职级分组计算员工数、月基本工资平均值和月基本工资总额，按员工数升序排列，如果员工数相同，则按月平均工资升序排列

select rankid 职位编号,count(*) 员工数,avg(salary) 平均工资,sum(salary) 总工资 from employee group by rankid order by count(*),avg(salary)

示例2：按职级分组计算行政职级员工数、月平均基本工资和月基本工资总额，按员工数升序排列，如果员工数相同，则按月平均基本工资升序排列

select rankid 职位编号,count(*) 人数,avg(salary) 平均工资，sum(salary) 总和 from employee where rankid like '1__'
group by rankid order by count(*),avg(salary)

示例3：查询每个职级的员工的最高月基本工资，按职级号升序排列

select rankid 职位编号,max(salary) 最高月工资 from employee
froup by rankid order by rankid

多字段分组 :

使用 group by 可以根据多个字段进行分组，分组层次从左至右，即先按第一个字段分组，然后在第一个字段值相同的记录中，在根据第二个字段的值进行分组……依次类推。

示例1：获取不同职级的员工在不同城市工作的人数，按员工职级升序排列。

select rankid 职位编号,count(address) 人数,address 工作地址,from employee group by rankid,address order by rankid 

使用 HAVING 对分组结果进行过滤:

HAVING 子句的作用是筛选满足条件的组，即在分组之后过滤数据。HAVING 子句在 GROUP BY 子句之后，常包含聚合函数

示例1：查询每个职级的员工最高月基本工资大于 10000 的职级工资信息（显示职级号和最高月基本工资），按职级号升序排列

select rankid 职位编号,max(salary) 最高工资 from employee group by rankid having max(salary) > 10000 order by rankid

示例2：计算每个职级的员工数、月平均基本工资和月基本工资总额，并显示员工数大于 1 的分组信息，按员工数升序排列

select rankid 职位编号,count(*) 人数,avg(salary) 平均工资,sum(salary) 总工资 from employee group by rankid having count(*) > 1 order by 人数

示例3：计算不同行政职级员工数、月平均基本工资和月基本工资总额，并显示员工数大于 1 的分组信息，按员工数升序排列

select rankid 职位编号, count(*) 员工人数,avg(salary) 平均工资,sum(salary) 月基本工资 from employee where rankid like '1%' group by rankid having count(*) > 1 order by 员工人数

注意：

HAVING 子句和 WHERE 子句均用于过滤数据，两者的区别在于：HAVING 在数据分组之后进行过滤；而WHERE 在分组之前过滤记录。

联接查询:

通过联接查询可将多个表作为一个表进行处理。联接查询分为：交叉联接、内部联接和外部链接。

交叉联接:

交叉联接使用 cross join 进行联接，没有 where 子句的交叉联接将返回被连接的两个表的笛卡尔积

语法：

select fieldlist from table1 
cross join table2

交叉联接中的列数=表 table1 和 table2 中列数之和（相加）

交叉联接中的行数=表 table1 和 table2 中列数之积（相乘）

示例1：获取员工表和职级表的笛卡儿积

select * from employee cross join rank

内联接 :

在进行两张表的联接查询中，仅返回匹配数据的联接方式称为内联接

语法：

select fieldlist from table1 [inner] join table2
on table1.column= table2.column

示例1：查询员工信息，包括员工职级的具体名称

select * from employee  inner join rank on employee.rankid = rank.rankid

示例2：查询员工姓名、职级号、职级名和月基本工资

select e.name 员工姓名,r.rankid 职位号,r.rankname 职位名称,e.salary 基本工资 from employee e inner join rank r on e.rankid = r.rankid

示例3：查询职级名为“工程师”的员工人数

select count(e.employeeid) 工程师人数 from employee e inner join rank r on e.rankid = r.rakid where r.rankname = '工程师'

select count(e.employeeid) 工程师人数 from employee e inner join rank r on e.rankid = r.rankid and r.rankname = '工程师'

示例4：查询每个职级的员工数和平均月基本工资，要求列出职级名。

select count(e.employeeid) 员工数,avg(salary) 平均工资,r.rankname from employee e inner join rank r on e.employeeid = r.rankid group by r.rankname 

示例4：计算每个职级的员工数和月平均基本工资，但仅显示月平均基本工资低于 30000 的分组信息，要求列出职级名。

select count(e.employeeid) 员工人数,avg(salary) 平均工资,r.rankname 职位名称 from employee e inner join rank r on e.employeeid = r.rankid group by r.rankname having 平均工资 < 30000

外联接 :

外联接分为左外联接、右外联接和全外联接

左外联接（left[outer] join）

左外联接使用（left[outer] join）进行联接，outer 可省略。左外联接的结果集包括 left join 子句中指定的左表的所有的行。如果左表的某行在右表没有匹配行，则结果集中的右表列均显示为空值。

示例1：使用左外联接查询员工姓名、职级号和职级名，按员工姓名升序排列

select e.name 员工姓名,r.rankid 职位编号,r.rankname 职位名 from rank r left join employee e on r.rankid = e.rankname order by e.name

右外联接（right[outer] join）

右外联接使用（right[outer] join）进行联接，是左外连接的反方向联接。右外联接的结果集包括 right join句中指定的右表的所有的行。如果右表的某行在左表没有匹配行，则结果集中的右表列均显示为空值。

select r.rankid 职位编号,r.rankname 职位名称,e.name 员工姓名 from rank r right join employee e on r.rankid = e.employeeid order by r.rankid, e.name

全外联接（fill[outer] join）

全外联接使用 full join 进行连接，将返回左表和右表的所有行。当某一行在另一个表中没有匹配时，则另一个表的列数据显示为空值。

select r.rankid 职位编号,r.rankname职位名称,e.name 员工姓名 from rank r fill join employee e on r.rankid = e.employeeid order by r.rankid, e.name

自联接

如果在一个联接查询中，涉及的两个表都是同一张表，这种查询称为自联接查询。自联接是一种特殊的内联接，它是指互相联接的两个表在物理上为同一张表，但可以 在逻辑上分为两张表。

示例1：获取每个部门的名称及上级部门的名称

select d1.deptname 部门名称,d2.deptname 上级部门名称,from dept d1 join dept d2 on d1.parentdeptid = d2.deptid

简单多表查询:

在 from 子句中，直接列出所有要连接的表，在 WHERE 子句中指定选择连接条件，与内联接功能相同。

语法：

select fieldlist from table1 , table2
where table1.column=table2.column

注意：

WHERE 子句必须包括 table1.column=table2.column，否则结果为笛卡尔积。

示例1：使用简单 多表查询方式 查询员工姓名、职级号、职级名。

select e.name,r.rankid,r.rankname from employee e,rank r where e.rankid = r.rankid

示例2：计算不同行政职级的员工数和最高月基本工资，按最高基本工资升序排列。

select max(e.salary) 最高月工资,r.rankname 员工姓名,count(e.employeeid) 员工人数 from employee e,rank r where e.rrankid = r.rankid and r.rankid like  '1__' group by r.rankid order by max(e.salary)

任务 1 使用 GROUP BY 分组统计歌手信息:

（1） 根据歌手地区分组，统计歌手人数，要求显示歌手地区和歌手人数

select area 歌手地区,count(*) 歌手人数 from singer group by area 

（2） 根据歌手地区和歌手类型分组，统计歌手人数，要求显示歌手地区，歌手类型和歌手人数。

select area 歌手地区,singertype 歌手类型,count(*) 歌手人数 from singer group by area,singertype

（3） 根据歌手地区和歌手类型分组，显示歌手人数大于 1 的分组信息，要求显示歌手地区，歌手类型和歌手人数。

select area 歌手地区,singertype 歌手类型,count(*) 歌手人数 from singer group by area,singertype having count(*) > 1

（4） 根据歌手地区和歌手类型分组，显示歌手人数大于 1 的分组信息，但不显示组合歌手的分组信息，要求显示歌手地区，歌手类型和歌手人数。

select area 歌手地区,singertype 歌手类型,count(*) 歌手人数 from singer group by area,singertype having count(*) > 1 and singertype <> '组合'

select area 歌手地区,singertype 歌手类型,count(*) 歌手人数 from singer group by area,singertype having count(*) > 1 and singertype  no like '%组合%'

任务 2 使用内联接查询歌手和歌曲信息表：

（1） 查询张国荣所唱的歌曲，要求列出歌手名和歌曲名

select r.singername 歌手姓名,s.title 歌曲名 from singer r  join song s on  r.singerid = s.singerid and r.singername = '张国荣'
go

（2） 统计每位歌手所唱的歌曲名，要求列出歌手名和歌曲数，并按歌曲数量升序排序。

select r.singername 歌手姓名,count(s.songid) 歌曲数 from singer r join song s on r.singerid = s.singerid 
group by r.singername order by count(s.songid)

（3） 计算每位歌手所唱歌曲的点歌数总和，要求列出歌手名和歌曲总点歌数，并按总点歌数升序排列。

select r.singername 歌手姓名,count(s.hit) 点歌总数 from song s join singer r on s.singerid = r.singerid group by r.singername order by count(s.hit)

设计 easyShopping 数据库:

在修改姓名为“王传华”的电话号码为 01062111234

update customer set phone = '01062111234' where customername = '王传华'

列出订单信息，按下单时间升序（降序）排序：

select orderID 订单号,OrderDate 订单日期,DeliveryDate 送达时间 from order order by OrderDate desc

列出订单信息。按下单日期升序排序，如果下单日期相同，则按订单金额降序排列:

select OrderId 订单号,OrderDate 订单日期,DeliverDate 送达时间 from order order by OrderDate,Amount desc 

列出订单信息。按下单日期升序排序，如果下单日期相同，则按订单金额降序排列：

select top 5 * from order order by OrderDate,Amount desc

。查询商品种类为“书籍”或“生活电器”的商品信息:

select * from Goods where Category in('书籍','生活电器')

IN 运算符可以被逻辑运算符 OR 取代：

select * from Goods where Category = '书籍' or Category = '生活电器'

查询商品单价在 50~100 的商品信息：

select * from Goods where initprice between 50 and 100

BETWEEN 运算符可以被逻辑运算符 AND 取代:

select * from Goods where unitPrice >= 50 and unitPrice <= 100

查询客户地址为上海市或武汉市的客户信息:

select * from cumstor where address = '上海' or address = '武汉'
select * from cumstor where address like '%上海%' or address like '%武汉%'

查询商品种类为“生活电器”，且单价小于 200 的商品信息：

select * from Goods where Category = '生活电器' and uniPrice <200

计算查询生活电器中单价最高和最低的商品信息

select max(uniPrice) 最高单价,min(uniPrice) 最低单价 from Goods where Category = '生活电器'

计算订单数量和订单总金额：

select count(*) 订单数量,sum(Amount) 总金额 from orders

按商品种类统计商品件数和最高单价:

select couont(*) 商品件数,max(Amount) 最高单价 from orders group by Category

按商品种类统计商品件数和最高单价，并显示商品件数大于3的信息：

select count(*) 商品件数,max(Amount) 最高单价 from Goods group by Category having count(*) > 3

：统计每个客户每天所订订单数，并按客户号升序排列:

select o.customerId 客户编号,o.orderDate 下单日期,count(*) 订单数  from orders o group by o.customerId ,o.orderDate order by o.customerId 

：统计每个客户每天的订单数仅显示订单数大于 1 的统计信息，按客户号升序排列:

select o.customerId 客户编号,o.orderDate 下单日期,count(*) 订单数  from orders o group by o.customerId ,o.orderDate having count(*) > 1 order by o.customerId

统计每个客户每天所订订单数，并按客户号升序排列，要求显示客户名、下单日期和订单数:

select c.sustomername 客户名,o.orderDate 下单日期,count(o.orderID) 订单数 from customer c  join orders o on o.customerId = c.customerId group by c.customername order by c.customerId

计算每个订单的订购总金额:

select od.orderId 订单号,sum(g.unitPrice * od.Quantity) 总金额 from Goods g join orderDetail od on where g.GoodsId = od.GoodsId 

（1） 显示单价大于 100 元的商品信息，并按单价升序排序。

select * from Goods where Amount > 100 order by Amount

（2） 显示单价大于 50 元的商品信息，并按商品种类升序、单价降序排序。

select * from Goods where Amount > 50 order by Category asc,Amount desc

（3） 显示单价在 50~100 元的商品信息。

select * from Goods where Amount between 50 and 100

（4） 统计每种商品的平均单价、最高单价、最低单价。

select avg(Amount) 平均单价,max(Amount) 最高单价,min(Amount) 最低单价 from Goods group by Category

（5） 统计每种商品件数大于 1 的数据，显示商品种类和件数。

select g.categoey 商品种类,count(g.GoodsId) 件数 from Goods g group by category having count(g.GoodsId) > 1

（6） 显示客户“张洪涛”所购商品信息，显示客户名、商品名、商品单价.

select s.customername 客户名,g.GoodsName 商品名称,g.unitPrice 商品单价 from customer s join Goods g on c.customername = '张洪涛'