SQL-带条件的查询

最新推荐文章于 2024-05-07 16:08:58 发布

Charles0371

最新推荐文章于 2024-05-07 16:08:58 发布

阅读量929

点赞数

分类专栏：数据库开发

本文链接：https://blog.csdn.net/Charles0391/article/details/23134841

版权

数据库开发专栏收录该内容

7 篇文章 0 订阅

订阅专栏

----------------------------带条件的查询---------------------------
select * from TblClass
select * from TblStudent
select * from TblScore
--------------------------查询没及格的学生的学号-------------
select tSId from TblScore where tEnglish<60 or tMath<60
-------------------------查询年龄在20-30之间的男学生---------------
select * from TblStudent where tsgender='男' and (tsage>=20 and tsage<=30)
select * from TblStudent where tSGender='男'
and tSAge between 20 and 30
------------------------查询数学成家在80到90之间的所有学生---------------
select * from TblScore where tMath between 80 and 90

select * from TblStudent where tsclassid=1 or tsclassid=5 or tsclassid=3
select * from TblStudent where tSClassId in(1,3,5)
select * from TblStudent where tSClassId between 1 and 2
select * from TblStudent where tsclassid>3 and tsclassid<5

----------------------------模糊查询------------------------------------------
-----—/ _ / % / [] / ^ ------------------------------
---------------_表示任意单个的字符-----------------------------
select * from TblStudent where tSName like '张_'
select * from TblStudent where tSName like '张__'
---------------%表示任意的字符-------------------------------
select * from TblStudent where tSName like '张%'
---------------[]表示从一个范围中筛选------------------------
select * from tblstudent where tsname like '张[a-z]妹'
select * from TblStudent where tSName like '张[0-9]妹'
select * from TblStudent where tSName like '张[_]妹'--把字符放到【】中表示一个字符没有意义
select * from TblStudent where tSName like '张[xc]妹'
select * from TblStudent where tSName like '张[^xc]妹' --加^表示非
select * from TblStudent where tSName not like '张[xc]妹'

--------------------------------空值的处理--------------------------------------
select * from TblStudent where tSAge is null ------------表示年龄的是不知道的
select * from TblStudent where tSAge is not null------------表示年龄不是空值的
update TblStudent set tSAge=tSAge+1 where tSId=1 or tSId=2--null和任何值计算结果还是null
select * from TblStudent

-------------------------------order by排序------------------------------
--order by永远在查询语句的最后面执行，所有的子句都在order by 之前执行
--order by asc 表示升序
--order by desc 表示降序、不写表示默认升序
-----------------按照多列来排序----------------------
select * from TblScore order by tEnglish desc,tMath desc
----------------order by不仅来根据列来排序，还可以根据表达式来排序-----
select *, 平均分=(tenglish+tmath)*1.0/2
from tblscore
order by 平均分 desc

--------------------分组group by-----------------------------------
select * from TblStudent
---------------------查询出每个班的班级id和班级人数----------------
select
tsclassid as 班级,
count(*) as 班级人数
from tblstudent group by tsclassid
------------聚合函数必须配合分组使用---------------
select tsclassid as 班级id,
COUNT(*) as 班级男同学的人数
from TblStudent where tSGender='男' group by tSClassId
----------------------------------------------------
select tsclassid as 班级id,
COUNT(*) as 班级男同学的人数
from TblStudent where tSGender='男' group by tSClassId
having count(*)>3

select tsclassid as 班级id,
COUNT(*) as 班级男同学的人数
from TblStudent where tSGender='男' group by tSClassId
order by 班级男同学的人数 desc
---------------------分组后再进行筛选就要用having来配合使用了-------------------
--where是对每条记录进行筛选，having是对分组后的集合再进行筛选的-----
---------------------sql语句的执行顺序-------------------

--先执行from->where中不能用聚合函数->group by->having->select->选择列->distinct->order by->top