一篇文章带你玩转SQL server数据库,喜欢请三联
作者:mmseoamin日期:2023-12-13

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 = '张洪涛'