#进阶3：排序查询
/*
#引入
SELECT * FROM employees
语法：执行顺序
select 查询列表 3
from 表 1
【where 筛选条件】 2
order by 排序列表 【asc | desc】 4
特点：
1.asc代表的是升序，desc代表的是降序，如果不写默认是升序
2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名
（多个字段排序，先根据xx进行排序，当xx相同的时候根据yy进行排序）
3.order by 子句一般是放在查询语句的最后面，limit子句除外
*/
#案例：查询员工信息、要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC
SELECT * FROM employees ORDER BY salary
#案例2 查询部门编号 >= 90 的员工信息，按入职时间的先后进行排序 【添加筛选条件】
SELECT * FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC
#案例3 按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *, salary *12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary * 12 * (1 + IFNULL(commission_pct,0)) DESC
#案例4 按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *, salary *12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC
#案例5 按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) AS 字节长度, last_name, salary FROM employees ORDER BY 字节长度 DESC
#案例6 查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
SELECT * FROM employees
ORDER BY salary ASC , employee_id DESC
#案例 7 查询员工的姓名和部门好和年薪，按年薪降序，按姓名升序
SELECT last_name, department_id, salary * 12 * (1 + IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC, last_name ASC
#案例8 选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name, salary 3
FROM employees 1
WHERE salary NOT BETWEEN 8000 AND 17000 2
ORDER BY salary DESC 4
#案例9 查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC
#进阶4 常见函数
/*
概念：类似于java的方法，将一组逻辑语句封装再方法体中，对外暴露方法名
好处：1.隐藏了实现细节 2、提高代码的重用性
调用 select 函数名(实参列表) 【from 表】
特点：
1、叫什么（函数名）
2、干什么（函数功能）
分类：
1、单行函数
如concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、组函数
*/
#一、字符函数
#length 获取参数值的字节个数
SELECT LENGTH('john')
SELECT LENGTH('张山峰hahaha')
#2.concat 拼接字符串
SELECT CONCAT(last_name, '_', first_name) AS 姓名 FROM employees
#3.upper、lower 大小写
SELECT UPPER ('john')
SELECT LOWER ('joHn')
#示例：将姓名大写、名变小写，然后拼接
SELECT CONCAT (UPPER(last_name), '_', LOWER(first_name)) AS 姓名 FROM employees
#4.substr、substring 截图字符
#注意：索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元', 7) out_put
#截取从指定索引处指定字符长度
SELECT SUBSTR('李莫愁爱上了陆展元', 1, 3) out_put
#案例：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), '_', LOWER(SUBSTR(last_name,2))) AS out_put
FROM employees
#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠', '殷六侠') AS out_put
#6/trim 去掉前后空格
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put
SELECT TRIM('aa' FROM 'aaaaa张翠山aaaaaaaa') AS out_put
#7. lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素', 10, '*') AS out_put
#8. rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素', 10, 'ab') AS out_put
#9. replace
SELECT REPLACE('张无忌爱上了周芷若', '周芷若', '赵敏') AS out_put
#二、数学函数
#round 四舍五入
SELECT ROUND(1.65)
SELECT ROUND(-1.65)
SELECT ROUND(-1.657,2)
#ceil 向上取整，返回 >=该参数的最小整数 2-1
SELECT CEIL(1.002)
SELECT CEIL(1.00)
#floor 向下取整，返回小于等于该参数的最大整数 9
SELECT FLOOR(9.99)
#truncate 截断，后面是什么，都不要1.6
SELECT TRUNCATE(1.65,1)
#mod 取余 1 -1
/*
mod(a,b) : a-a/b*b
mod(-10,-3) : -10-(-3) * (-3) = -1
*/
SELECT MOD(10,3)
SELECT MOD(-10,-3)
SELECT 10%3
#三、日期函数
#now 返回当前系统日期时间
SELECT NOW()
#curdate 返回当前系统日期，不包含其他
SELECT CURDATE()
#curtime 返回当前时间，不包含日期
SELECT CURTIME()
#可以获取指定的部分、年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) AS 年
SELECT YEAR('1998-01-02') AS 年
SELECT YEAR (hiredate) AS 年 FROM employees
SELECT MONTH(NOW()) AS 月
SELECT MONTHNAME(NOW()) AS 月
#str_to_date：将日期格式的字符转换成指定格式的日期
#select datediff(year, '1991-05-31', getdate()) 计算两个日期的差值
#获取日期的某部分的值
datepart(year, getdate())
year(getdate())
#返回日期的某部分，字符串表示形式，datepart()是数字表示形式
/*
%Y 四位的年份
%y 两位的年份
%m 月份(01,02..)
%c 月份(1,2...)
%d 日(01,02...)
%H 小时(24小时)
%h 小时(12小时)
%i 分钟(00,01,,,59)
%s 秒(00,01...59)
*/
SELECT STR_TO_DATE('1998-03-02', '%Y-%c-%d') AS out_put
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3'
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y')
#案例：查询出入职n年的人的个数
select 工龄=datediff(year, JoinDate, getdate()), count(*) as 人数
from exam_emps
group by datediff(year, JoinDate, getdate())
select 工龄=datediff(day, JoinDate, getdate()) / 365,
count(*) as 人数
from exam_emps
group by datediff(year, JoinDate, getdate())
#案例：查询出，不同年份入职的员工的个数
select
入职年份=year(JoinDate),
count(*) as 人数
from exam_emps
group by year(JoinDate)
#案例：输出所有数据中通话时间最长的5条记录。
select top 5
* , '通话时长(秒)'= datediff(second, StartDateTime, EndDateTime)
from CallRecouds
order by '通话时长(秒)' desc
#案例：输出所有数据中拨打长途号码（对方号码0开头）的总时长
select
总时长 = sum（datediff(second, StartDateTime, EndDateTime)）
from CallRecouds
where TelNum like '0%'
#案例：输出本月通话时长最多的前三个呼叫员的编号
select top (3)
呼叫员编号 = callerNumber,
通话时长=sum(datediff(second, StartDateTime, EndDateTime))
from CallRecouds
where datediff(month, StartDateTime, '2010-07-31') = 0 --表示是本月
group by CallerNumber
order by 通话时长 desc
#案例：输出本月拨打电话次数最多的前三个呼叫员的编号
select top (3)
callerNumber,
拨打电话次数=count(*)
from CallRecouds
where datediff(month, StartDateTime, '2010-07-31') = 0 --表示是本月
group by CallerNumber
order by 拨打电话次数 desc
# date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') AS out_out
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name, DATE_FORMAT(hiredate, '%m月/%d日 %Y年') FROM employees WHERE commission_pct IS NOT NULL
#四、其他函数 P48
#版本号
SELECT VERSION()
#查看当前的数据库
SELECT DATABASE()
#查看当前用户
SELECT USE()
#分组函数
/*
聚合函数一般结合分组使用，进行分组内的数据进行统计，根据指定列进行分组
分组后条件筛选：having...
当使用了分组语句（group by）或者是聚合函数的时候，再select的查询列表中不能再包含其它的列名，除非该列同时也出现再了 group by 字句中，或者该列也包含在了某个聚合函数中
select distinct top n * 5
from t1 join t2 on ... join t3 on ... 1
where ... 2 where是对每条进行筛选，不能使用聚合函数
group by ... 3
having... 4 having是对分组后的每一组数据进行筛选
order by ... 6
*/
select 班级id = scalssid,
男同学人数=conut(*)
年龄 = tsage 【这个没出现在group by 子句中，所以不能显示】
from tblStudent
WHERE tsgender = '男'
group by tsclassid
# having 对分组以后的数据进行筛选
--having与where都是对数据进行筛选，where是对分组前的每一行数据进行筛选，而having是对分组后的每一组数据进行筛选
SELECT 语句的处理顺序
1 FROM
2 JOIN
3 ON
4 WHERE
5 GROUP BY
6 WITH CUBE 或 WITH ROLLUP
7 HAVING
8 SELECT
9 DISTINCT
10 ORDER BY
11 TOP
#案例：请统计销售总价超过3000元的商品名称和销售总价，并按照销售总价降序排序
select
商品名称
销售总价 = sun(销售数量 * 销售价格)
FROM MyOrders
group by 商品名称
having sun(销售数量 * 销售价格) 》 3000
order by 销售总价 desc
# 案例：统计各个客户对‘可口可乐’的喜爱度（即统计每个购买人对‘可口可乐’的购买量）
select
购买人,
购买可口可乐数量=sum(销售数量)
FROM MyOrders
where 商品名称 = '可口可乐'
group by 购买人
order by 购买可口可乐数量 dese
子查询
select
sum(T.销售数量) 喜爱度,
T.购买人 客户
from (select * from MyOrders where 商品名称 '可口可乐') as T
group by T.购买人
order by 喜爱度 desc
#类型转换
--cast (表达式 as 数据类型)
--convert(数据类型， 表达式)
select 100.0 + cast('1000' as int)
select 100.0 + convert(int, '1000')
#联合，union
/*
连接(join)是连接列，联合(union)是把行连接起来
基本原则：每个结果集必须有相同的列数，每个结果集的列必须类型兼容
联合：将多个结果集合并成一个结果集，union去除重复，相当于默认应用了distinct，union all
*/
#使用union all联合结果集
select tsname, tsgender, tsage from TblStudent
union all
select fname, fgender, fage from MyStudent
--使用union和union all 都能进行联合，区别在于：使用union联合会去除重复、重新排列数据，而union all不会去除重复，也不会重新排列
#使用union联合结果集
select tsname, tsgender, tsage from TblStudent
union
select fname, fgender, fage from MyStudent
--大多数情况下，联合的时候不需要去除重复，同时要保持数据的顺序，所以一般建议使用union
#案例：从MyOders表中统计每种商品的销售总价，并且底部做汇总
select
商品名称，
销售总价 = sum(销售价格*销售数量)
from MyOrders
group by 商品名称
union all
select `总销售价格`, sum(销售价格 * 销售数量) from MyOrders
order by 销售总价 asc
使用union向表中插入多条数据
insert into TblStudent
select '赵宇星', '男', '北京', '13168887498', 18, '1990-09-19', '1111223435435435435',3
union all 不会去除重复
select '赵宇星1', '男', '北京', '13168887498', 18, '1990-09-19', '1111223435435435435',3
内连接：只列出匹配的记录，只显示那些两张表中可以匹配的数据
左外连接：左表列出全部，右表只列出匹配的记录。出现在left join左边的表就叫左表，出现在left join右边的表就叫右表
右外连接：右表列出全部，左表只列出匹配的记录。
--查询数据时，当需要将多个表中的列共同显示到一个结果集中的时候，可以使用连接查询
select * from PhoneNum(外键) inner join PhoneType(主键) or PhoneNum.pTypeId=PhoneType.ptid
CASE
表中有ABC三列，用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列，否则选择C列
--在订单列表中，统计每个销售员的总销售金额，列出销售员名、总销售金额、称号（>6000金牌，>5500银牌，>4500铜牌，否则普通）
select
X=case
when A>B then A
else B
end,
Y=case
when B > C then B
else C
end,
from TestA
索引Index
--1.索引的目的：提高查询效率
--2.索引分两种
--2.1聚集索引（物理），一个表中只能有一个聚集索引
--2.2非聚集索引（逻辑），一个表中可以有多个非聚集索引。
--3.增加索引后，会增加额外的存储空间。同时降低了增加新记录，修改，删除的效率。
预读取、物理读取、逻辑读取
-Sql Server 存取数据都是以页为单位（每页总大小8KB）
-逻辑读取：从缓存中读取数据
-物理读取：从磁盘中读取数据
-预读取：一种性能优化机制（在执行查询时先预测执行“查询计划”所需的数据和索引页。然后在查询实际使用这些页之前将它们读入缓冲区高速缓存）
---------------------子查询----------------
--查询高二二班的所有学生，所有子查询都可以改成相关子查询实现
select * from TblStudent where tSClassId=
(select tClassId from TblClass where tClassName='高二二班')
// 相关子查询
select * from TblStudent as ts where
exists(select * from TblClass as tc where ts.tSClassId=tc.tClassId and tc.tClassName='高二二班')
exists(BOOL值)，只要这个里面是否查到了数据，就为真，返回这条数据 select * from tStudeng where 1=1
SELECT * FROM (SELECT * FROM Student where sAge < 30) as t
SELECT
(select max(tenglish) from tblscore),
(select min(tenglish) from tblscore),
(select avg(tenglish) from tblscore)
select * from TblStudent where tsclassid in【=】 (select tclassid from TblClass)，=只能有一个值
select * from student where sClassId in (select cId from class where cName='高一一班' or cName='高二二班')
select * from student where exist (select * from where (cName='高一一班') or cName='高二二班') and class.cid=student.sclassid)
----------- 分页查询 -----------
每页显示7条数据，首先要确认按照什么排序，然后才能确定哪些记录应该再第一页，哪些记录应该再第二页
select top 7 * from Customers order by CustomerID asc // 第一页
--第二页：先查询出已经看过前一页的数据(2-1)也的数据的customerID，在查询出不在没看过的记录，排序，多少条
select top 7 * from Customers where CustomerID not in
(select top (7 * 2-1) CustomerID from Customers order by CustomerID asc)
order by CustomerID asc
---------- 使用row_number()实现分页 ----------
--1.为数据排序，然后编号，编号的时候，按照指定的规则编号
select *, Rn=row_number() over(order by CustomerId asc) from Customers
--2.根据用户要查看的每页记录条数，以及要查看第几页，确定应该查询第几条到第几条
--每页显示7条，要查看第8页 (8-1) * 7 + 1... 8*7
select * from (elect *, Rn=row_number() over(order by CustomerId asc) from Customers) as t
where t.Rn between (8-1) * 7 + 1 and 8*7
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
--请查询出那些所有没有参加考试的同学的tsid姓名和年龄
通过子查询实现：
select * from student where tsid not in (select tsid from soure)
--通过连接查询实现，应该通过左外联实现
select t1,* t2,* from Tstudent as t1 left join Tscore as t2 on t1.tsid = t2.tsid where t2.tscoureId is null
--通过右外联来实现
select t1,* t2,* from Tscore as t2 right join Tstudent as t1 on t1.tsid = t2.tsid where t2.tscoureId is null
----------使用外连接时需要注意的一个问题 --------------
select t1,* t2,* from Tstudent as t1 left join Tscore as t2 on t1.tsid = t2.tsid
where t1.tsgender='男' --筛选条件要放到where字句当中
--下面的这个写法是有问题的
select t1,* t2,* from Tstudent as t1 left join Tscore as t2 on t1.tsid = t2.tsid and t1.tsgender='男'【false】
--下面这种写法是可以正常显示要查询的数据
select t1,* t2,* from Tstudent as t1 inner join Tscore as t2 on t1.tsid = t2.tsid and t1.tsgender='男'
--当使用连接查询的时候，如果同时要指定查询的条件，那么一定要使用where语句，不要直接在on条件后面跟and来编写其他查询条件
-------------------------
视图概述
--视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上
--视图在操作上和数据表没有什么区别，但两者的差异是其本质不同；数据表是实际存储记录的地方，然而视图并不保存任何记录
--相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）
--视图的目的是方便查询，所以一般情况下不能对视图进行增删改
优点：
--筛选表中的行/降低数据库的复杂程度
--防止未经许可的用户访问敏感数据
create View vw_TblArea
as
select t1.AreaId 城市编号，t2.AreaName 城市名称，t1.AreaName 省份名称 from TblArea as t1
inner join TblArea as t2 on t1.AreaId=t2.AreaPid
--如果视图中的查询语句中包含了重名的列，此时必须为重名的列起别名
select * from vw_TblArea
------------------T-SQL编程------------------
--1、声明变量
declare @name nvarchar(50)
declare @age int
declare @name nvarchar(50), @age int
--2、为变量复制
set @name = '刘亚龙'
--3、输出
select '姓名', @name
select '年龄', @age
--vhile循环
declare @i int = 1 --声明变量的同时赋值
while @I <= 100
begin
print 'Hello'
set @I=@I + 1
end
--计算1-100之间的所有整数的和
declare @i int = 1 --声明变量的同时赋值
declare @sum int = 1 --声明变量的同时赋值
while @i <= 100
begin
set @sum=@sum + @i
set @i=@i+1
end
--两个@@符号开头的一般都是系统变量
print @@version
事务-为什么需要事务
如，转账问题：假定钱从A转到B，至少需要两步：
-A的资金减少
-然后B的资金相应增加
--怎么保证两条SQL语句同时执行成功或者同事执行失败呢？
--使用事务来保证
update bank set balance=balance-100 where cid = '0002'
update bank set balance=balance+100 where cid = '0001'
--通过事务执行转账
--1.打开一个事务
begin transaction
declare @sum int = 0
--在转账之前最好通过if-else判断，不要让程序发生异常或者错误！
update bank set balance=balance-100 wehre cid='0002'
set @sum = @sum+@@error
update bank set balance=balance+100 wehre cid='0001'
set @sum = @sum+@@error
--只要有任何一条SQL语句执行出错，那么最后的@sum就不是0
if @sum<>0
begin
--标识程序执行出错了
--回滚
rollback
end
else
begin
--如果没有出错，则提交该事务
commit
end
--自动提交事务
--当执行一条sql语句的时候，数据库自动帮我们打开一个事务，当语句执行成功，数据库自动提交事务，执行失败，数据库自动回访事务
--insert into bbbb values(fsd)
--隐式事务
--隐式事务，每次执行一条sql语句的时候，数据库自动帮我们打开一个事务，但是需要我们手动提交事务，或者回滚事务
SET IMPLICIT_TRANSACTIONS(ON | OFF) 隐式事务
--打开隐式事务
SET IMPLICIT_TRANSACTIONS ON
insert into bank values('0003', '1000')
commit
--显示事务
begin tran
--...
commit tran
rollback transaction
--------- 存储过程 ---------
1.系统存储过程
--1.返回当前实例中的所有的数据库的基本信息
exec sp_database
----------创建自己的存储过程----------
create proc usp_say_hello
as begin
print 'HELLO WORLD'
end
exec usp_say_hello
drop proc usp_select_tblStudent
alter proc usp_select_tblStudent
create proc usp_select_tblStudent
as begin
select * from TblStuent where tsgendr='男'
end
--调用存储过程
exec usp_select_tblStudent
--创建一个带两个参数的存储过程
create proc usp_add_number
@n1 int=100 -- 设置参数的默认值
@n2 int=10
as begin
select @n1 + @n2
end
exec usp_add_number @n2=80
--设置存储过程的参数的默认值
create proc usp_select_tblstudent_by_condition
@gender char(2)
@age int
as begin
select * from TblStudent where tsage>=@age and tsgender=@gender
end
exec usp_select_tblstudent_by_condition @gender='男',@age=20
----------带输出参数的存储过程----------
--当在存储过程当中需要返回多个值的时候，就可以使用输出参数来返回这些值
create proc usp_show_students
@gender char(2)
@recordcount int output --输出参数
as
begin
select * from MyStudent where fgender=@gender
--把查询语句查询到的记录的条数赋值给变量@recordcount
set @recordcount=(select count(*) from MyStudent where fgender=@gender)
end
--调用存储过程
--调用带有输出参数的存储过程的时候，需要定义变量，将变量传递给输出参数，在存储过程中使用的输出参数，其实就是你传递进来的变量
declare @rc int
exec usp_show_students @gender='男', @recordcount=@rc output
print @rc
---------使用存储过程编写一个分页查询----------
go
create proc usp_getMyStudentsDataByPage
@pagesize int = 7, --每页记录条数
@pageindex int = 1, --当前要查看第几页的记录
@recordcount int output, -- 总的记录的条数
@pagecount int out put, --总的页数
as
begin
--1.编写查询语句，把用户要的数据查询出来
select t.fid, t.fname,t.gage, t.fmath, t.classid, t.fbirthday
from(select *, rn=row_number() over(order by fid asc) from MyStudent) as t
where t.rn between(@pageindex-1)*@pagesize+1 and @pagesize*@pageindex
--2.计算总的记录条数
set @recordcount=(select count(*) from MyStudent)
--3.计算总页数
set @pagecount=ceiling(@recordcount*1.0/@pagesize)
end
declare @rc int , @pc int
exce usp_getMyStudentsDataByPage @pagesize=7, @recordindex=1, @recordcount=@rc output,
pagecount=@pc output
print @rc
print @pc
网友评论