Sql查询相关语句
-
Select语句
-
查询特定列数据
select 学生编号,学生姓名 from tb_Student select * from tb_Student --查询数据表中所有的数据列--
| select | 查询语句中,select关键字后面可以添加要查询数据列的名称
| 学生姓名 | 数据列名称
| from | from关键字用于指定要查询哪张表的数据
| tb_Student | 数据表名
执行原理 From是最先运行的,会根据后面的表生成一个虚拟的表,然后根据Select语句中特定的列名称将虚拟表的数据提取出来,得到最终的虚拟表就是用户需要的结果集
-
使用列别名 (AS)
select 学生姓名 AS 姓名 from tb_Student
| AS | AS前为列名称 | AS后为列的别名
-
在列上加入计算
select 学生编号,高数+外语 AS 高数和英语的分数 from tb_Grade
注意点: 在数据列加入计算后,数据列是没有名称的 可以使用AS 或空格来设置别名
-
使用函数设置条件
select 学生姓名,年龄 from tb_Student where 学生编号 > 2
| where | 对查询设置条件| where 后面设置你所需要的条件也可以使用AND和OR
-
-
查询常量
-
查询常量
select 学生姓名,年龄 from tb_Student where 学生编号 = 2
-
查询字符串
select 学生编号,年龄 from tb_Student where 学生姓名 ='张三'
-
查询日期数据
select 学生姓名,学生年龄 from tb_Student where 出生年月='1998/12/2'
可以使用Between查询在指定日期区间内的日期信息
-
查询逻辑型数据
select 学生编号,年龄 from tb_Student where 统招否='true'
-
查询空(或null)数据
select 学生编号,年龄 from tb_Student where 家庭住址 Is null Or 家庭住址 =''
-
-
模糊查询
-
利用“ -”通配符进行查询
select 学生编号,年龄 from tb_Student where 学生姓名 like '张_'
可以使用多个通配符 列如 张__
-
利用“%”通配符进行查询
select 学生编号,年龄 from tb_Student where 学生姓名 like '张%'
注意点 “% ” 和 “” 的区别,“” 代表一个字符,"%"代表多个字符
-
利用“[]”通配符进行查询
select 学生编号,学生姓名 from tb_Student where 年龄 like '2[0-9]'
注意点 “[]”代表方括号范围内的单个字符,可以多个使用
-
利用“[^]”通配符进行查询
select 学生编号,学生姓名 from tb_Student where 年龄 like '^[A-Z]'
注意点 “[^]”代表非方括号范围内的单个字符
-
-
Top和Percent限制查询结果
-
查询前10名数据
select top 10 学生编号,学生姓名 from tb_Student Order By 学生编号 ASC
注意点 在查询语句中,如果使用Top获取前若干条记录,尽量使用Order By 配合使用
-
取出统计结果的后10名数据
select top 10 学生编号,学生姓名 from tb_Student Order By 学生编号 DESC
DESC 降序排列 ASC升序排列
-
查询第10到第20名的数据
select top 10 * from (select top 20 * from tb_Student order by 总分 ASC ) as tb Order by 总分 DESC
tb 表别名
先在表中升序找到1-20 然后再在这里面按降序排 选10位
-
查询销售量占前50%的图书信息
select top 50 percent * from tb_book order by 图书编号 ASC
-
-
数值查询
-
判断是否为数值
-
select 学生姓名, isnumeric(年龄) from tb_Student
select 年龄, case when isnumeric(年龄)=1 then '是数值' else '不是数值' End from tb_Student
-
在查询时对数值进行取整
select dm as 数值取整前 Ceiling(dm) AS 取整后 from tb_money
ceiling 向上取整 3.14 -4
floor 向下取整 3.14 - 3
-
对查询到的数值进行四舍五入
select dm as 四舍五入前 Round(dm,2) as 四舍五入后 from tb_money
round(dm,2) 第一个参数是小数数值 第二个参数是四舍五入保留的位数
-
求数值的绝对值
select value as 数值 Abs(value) as 求绝对值之后 from tb_value
-
根据生成的随机数查询记录
select * from tb_Student where 学生编号=1000+floor(rand()*10)
-
根据查询数值的符号显示具体文本
select value as 数值 case when sign(value)=1 then '正数' else case when sign(value)=-1 then '负数' else '零' end end as 判断值 from tb_value
-
-
字符差查询
-
实现字符串的大小写转换
select friendname as 字符串 Lower(friendname) as 转换为小写 Upper(friendname) as 转换为大写 from tb_string
-
返回字符串在字符串出现的次数
select 学生姓名,len(学生姓名) as 字符数量 from tb_Student
-
返回字符串中的子串
select 学生姓名,所在学院 substring(所在学院,2,3) as 所在学院字符串 from tb_Student
substring 返回指定数据列中的字符串
接收三个参数 第一个 字符串 第二个 截取字符串的索引位置 第三个 截取字符的数量
-
删除字符串中的子串
select 所在学院 as 删除字符串之前 stuff(所在学院,2,3,'') as 删除字符串后 from tb_Student
stuff 删除指定长度的字符,并在删除的字符位置插入另一组字符
接收四个参数 第一个 字符串 第二个 截取字符串的索引位置 第三个 截取字符的数量
第四个表示移除子串的位置插入的字符串
-
查询并替换字符串
select 所在学院 charindex('学院',所在学院) as 索引位置 from tb_student select 所在学院 replace(所在学院,'学院','xueyuan') as 替换后的信息 from tb_student
charindex 获取字符串中指定字符的索引位置
replace 替换字符 接收三个参数 第一个 字符串 第二个 将要替换的子串 第三个 替换的子串
-
-
周期、日期查询
-
判断是否为日期
select 学生编号,学生姓名 case when isdate(出生年月)=1 then 日期时间 else 非日期时间 end as 生日时间判断 from tb_student
-
查询指定时间段的数据
select * from tb_student where 出生年月 between '1998/01/22' and '2000/01/11'
-
按年、月或日查询数据
select * from tb_student where year(日期)='2013' and month(日期)='02' and day(日期)='23'
-
返回当前日期时间及对应的日期
select getdate() as 当前时间 dataname(day,getdate())
dataname接收两个参数 第一个用于规定日期的那一部分(年,月,日)
第二个参数 为datetime的表达式
-
查询指定时间间隔的数据
select 学生编号,出生年月 datediff(year,出生年月,getdate()) as 学生年龄 from tb_student
datediff 接收三个参数 第一个 用于规定时间差的哪一部分 年(year)月(month)日(day)
第二个参数 为datetime类型的表达式
第三个参数 为datetime类型的表达式
-
-
比较、逻辑、重复查询
-
利用运算符查询指定条件的数据
select * from tb_student where 学生姓名 like '李%'
-
NOT与谓词进行组合条件的查询
select * from tb_grade where 软件工程 >90 and 外语 not between 70 and 85
-
查询时不显示重复记录
select distinct 学生编号,学生姓名 from tb_student
distinct 不显示重复记录, 会降低性能
-
列出数据中的重复记录和纪录条数
select count(书号) as 记录条数,书号,书名 from tb_book group by 书号,书名 having count(书号) > 1
group by 去除重复记录,并显示重复记录的条数 having 设置分组查询条件
-
-
在查询中使用OR和And
-
利用OR运算符进行查询
select * from tb_grade where 外语 > 90 or 高数 > 90
-
利用AND运算符进行查询
select * from tb_grade where 高数 > 70 and 外语 >80
-
同时利用OR、AND运算符进行查询
select * from tb_grade where (外语 > 70 or 高数>70) and 软件工程 > 80
-
-
排序和分组统计
-
数组分组统计
select 出版社,sum (金额) as 总金额 from tb_book group by 出版
-
在分组查询中使用ALL关键字
select 书名,出版社,sum(金额) as 总金额 from tb_book where 出版社="人邮" group by all 书名,出版社
使用分组查询,select 中的非聚合函数的数据列应包含在group by 子句中
使用all关键字,查询结果将包括由group by子句产生的所有组
-
在分组查询中使用CUBE运算符
select 所属部门,性别,avg (工资) as 平均工资 from tb_Employee group by 所属部门,性别 with cube
cube 运算符对分组信息进行分组汇总(多维数据集)
-
在分组查询中使用ROLLUP
select 所属部门,性别,avg (工资) as 平均工资 from tb_Employee group by 所属部门,性别 with rollup
rollup 对查询的分组信息进行分组汇总运算
rollup和cube的区别:
cube生成的结果集显示所选列中值得所有组合的聚合数据,
rollup生成的结果集显示所选列中值得某一个层次结构的聚合数据
-
对数据进行降序查询
select 学生编号,学生姓名 from tb_grade order by 学生编号 desc
-
对数据进行多条件排序
select * from tb_grade order by 语文 asc, 高数 desc
-
对统计结果进行排序
select top 5 书号,书名,作者,出版社, sum(销售数量) as 合计销售数量 from tb_book group by 书号,书名,作者,出版社 order by sum(销售数量) desc
-
按姓氏拼音排序
select 学生编号,性别 from tb_student order by 学生姓名 collate chinese_prc_cs_as
-
使用Compute
select * from tb_employee order by 所属部门 compute sum(工资)
compute对员工表中全部员工的工资情况进行汇总
-
使用Compute By
select * from tb_employee order by 所属部门 compute sum(工资) by 所属部门
compute by 将与员工中的员工工资按所属部门汇总
-
-
聚合函数
-
利用聚合函数Sum求数学总成绩
select sum(数学) as 数学总成绩 from tb_book
select sum(数学) as 数学总成绩 from tb_book
-
利用聚合函数AVG求某班数学的平均年龄
select avg(数学) as 数学平均成绩 from tb_book
-
利用聚合函数MIN求最低数学成绩
select min(数学) as 数学最低成绩 from tb_grade
-
利用聚合函数MAX求最高数学成绩
select max(数学) as 数学最高成绩 from tb_grade
-
利用聚合函数Count求商品数
select count(distinct 商品名称) from tb_ware
-
利用聚合函数First或Last求数据表中的第一条或最后一条
select first(boooknames) as bookname, last(bookscore) as bookscore from tb_books
-
-
多表查询(连接查询)
-
利用FROM子句进行多表查询
select a.学号,a.姓名, b.成绩 from student as a ,grade as b where a.学号= b.学号
-
合并多个结果集
select 学号,姓名 from student union select 数学成绩 ,英语成绩 from grade
union 从结果集删除重复的行
union all 包含所有行不会删除重复行
-
笛卡尔乘积查询
select a.学号,a.姓名, b.成绩 from student as a ,grade as b
笛卡尔乘积查询是一个特例,是一种无连接规则的查询,不需要设置连接规则
-
-
嵌套查询
-
简单嵌套查询
select 学生编号, 学生姓名,性别 from student where 学生编号 in (select 学生编号 from grade where 数学成绩>60)
-
复杂嵌套查询
-
嵌套查询在查询统计的应用
select * from grade where 数学成绩 > all (select 数学成绩 from grade where 学生姓名 in ('张三','李四'))
'>' all 表示大于条件每一个值 大于最大值
’>‘ any 表示至少大于条件中的一个值 大于最小值
-
-
子查询
-
使用Exists运算符引入子查询
select * from student as a where exists (select * from grade as b a.学生编号=b.编号 and a.姓名='张三')
-
在Having子句使用子查询过滤数据
select 学生编号,学生姓名,年龄 (select avg(年龄) from student )as 平均年龄 from student group by 学生编号,学生姓名,年龄 having 年龄 > (select avg(年龄) from student)
-
在Update语句应用子查询
update student set 学生编号 =(select 学生编号 from grade where 数学成绩=89)where 学生姓名 ='张三'
-
使用子查询删除数据
delete from student where 学生编号 in (select 学生编号 from grade where 数学成绩 =89)
-
-
组合语句
-
使用组合查询
select 学生编号,学生姓名,年龄 from student where 性别='女' union select 学生编号,学生姓名,年龄 from student where 学生编号>70
通过union 组合查询语句,将两个或者多个查询的结果集组合为一个结果集
-
多表联合查询
select 学生姓名 from studnet union select convert(varchar(20,总分) from grade 总分 > 600
convert将某种数据类型的表达式转换为另一个数据类型
-
对组合查询后的结果进行排序
select 学生姓名 from studnet union select convert(varchar(20,总分) from grade 总分 > 600 order by 学生姓名 asc
-
获取组合查询中两个结果集的交集
select 学生编号,学生姓名 from student intersect select 学生编号,学生姓名 from grade
intersect 用于查询和返回两个结果集中相同的数据记录
-
获取组合查询中两个结果集的差集
select 学生编号,学生姓名 from student except select 学生编号,学生姓名 from grade
except 用于其左侧不包含右侧查询结果集中的数据记录
-
-
内连接查询
-
简单内连接查询
select a.学生编号,b.数学成绩 from student as a inner join grade as b on a.学生编号=b.学生编号
inner join 来连接两个表 on设置连接规则
-
复杂内连接查询
select a.学生编号,b.数学成绩,c.出勤率 from student as a inner join grade as b on a.学生编号=b.学生编号 inner join studentTimeCard as c as a.学生编号=c.学生编号
-
使用INNER Join实现自身连接
select a.* from student as a inner join student as b on a.学生编号=b.学生编号 and a.学生姓名='张三'
自连接查询效率高于子查询
-
使用INNER Join实现不等连接
select a.* from student as a inner join student as b on a.学生编号<>b.学生编号 and a.学生姓名='张三'select
-
使用内连接选择一个表与另一个表中相关的所有行
-
-
外连接查询
-
Left Outer Join查询
select a.学生编号,a.学生编号,b.总分 from student as a left outer join grade as b on a.学生编号=b.学生编号
-
Right Outer Join查询
select a.学生编号,a.学生编号,b.总分 from student as a right outer join grade as b on a.学生编号=b.学生编号
-
使用外连接进行多表联合查询
-
-
利用IN进行查询
-
使用In查询表中的记录信息
select 学生姓名,性别,年龄 from student where 学生编号 in (select 学生编号 from grade where 总分 >600)
-
使用In引入子查询限定查询范围
-
使用Not In运算符引入子查询
select 学生姓名,性别,年龄 from student where 学生编号 not in (select 学生编号 from grade where 数学成绩 <60 and 数学成绩 >95 )
-
-
交叉表查询
-
利用Transform分析数据(assess)
trancform sum(数量) as 库存数量 select 语言类别 from 图书排行 where 语言类别 in ('语文','数学') group by (语言类别) pivot 分析时间
transform aggfunction selectstatement pivot pivotfield (value[,value2])
| aggfunction | 运算符所选数据的sql合计函数
| selectstatement | select语句
| pivotfield | 在查询结果集用来创建列标题的字段
| value ,value1 | 用来创建列标题的固定值
-
利用Tranform动态分析数据
-
静态交叉表(sqlserver)
-
动态交叉表
-
-
函数查询
-
在查询语句中使用格式化函数
select 姓名,出生日期 as 格式化前出生日期 format ('出生日期','yyyy年mm月dd日') as 格式化日期 from student
-
在查询语句中使用字符串函数
-
在查询中使用日期函数(assess)
-
-
索引查询
-
使用Unique关键字创建唯一索引
create unique index index_name on 数据表名称 (数据列)
-
使用Clustered关键字创建聚簇索引
create clustered index 索引名称 on 数据表名称 (数据列)
为了提高查询性能,在数据库中设计聚簇索引
聚簇索引 可以按照物理磁盘相近的位置索引数据记录,一个数据表中只允许有一个聚簇索引
聚簇索引改变了表中数据存放的物理位置
-
使用索引视图查询数据
select
必须先为视图创建唯一的聚簇索引,才能为该视图才能创建非聚簇索引
索引视图限制:
1.定义索引视图的select 语句中不能包含top,distinct,compute,having,union关键字和子查询
2.在任何联合表中,均不允许进行outer join 操作
-
删除索引
drop index 表名称 索引名称
-
-
应用存储过程
-
使用存储过程查询多表中的数据
create proc proc_student as select * from student
关键字 proc
为了提高安全性和提高存储效率
使用存储过程向表中添加数据
create proc pro_select ( @id int , @name varchar(30) ) as select * from student where 编号=@id and 姓名=@name go exec proc_select 2,"张三"
exec 调用
-
使用存储过程删除表中的数
create proc proc_delete @id int as delete student where 编号=@id
-
-
Having语句
-
利用Having子句过滤分组数据
select 所在学院,count(*) as 人数,avg(年龄) as 平均年龄 from student group by 所在学院 having avg(年龄) >22
having 过滤分组数据(指定分组和聚合的查询条件)
-
Having子句应用在多表查询中
-