use db_book --选择加载的表
go --执行加载
-- 1.查询中用到的关键词主要包含六个,并且他们的顺序依次为
-- select--from--where--group by--having--order by
-- 其中select和from是必须的,其他关键词是可选的,
-- 这六个关键词的执行顺序与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行
-- from--where--groupby--having--select--order by,
-- from:需要从哪个数据表检索数据
-- where:过滤表中数据的条件
-- group by:如何将上面过滤出的数据分组
-- having:对上面已经分组的数据进行过滤的条件
-- select:查看结果集中的哪个列,或列的计算结果
-- order by :按照什么样的顺序来查看返回的数据
-- 2.from后面的表关联,是自右向左解析的
-- 而where条件的解析顺序是自下而上的。
-- 也就是说,在写SQL文的时候,尽量把数据量大的表放在最右边来进行关联,
-- 而把能筛选出大量数据的条件放在where语句的最下面。
-- Ctrl+Shift+U 选中区域快速转换成大写; Ctrl+Shift+L 选中区域快速转换成小写
select * from table_1
select top 3 * from table_1
select ID ,address from dbo.Table_1
-- desc 倒序排列; asc 正序排列。
-- by 根据查询前面的第几个条件或者具体什么条件
select ID ,address,grade,class from dbo.Table_1 order by ID desc -- 根据grade进行desc排序,orderby默认正序排列
select ID,grade,address from dbo.Table_1 order by grade desc,ID --根据grade进行desc排序,再根据ID进行升序排列
select ID,name,address,class from dbo.Table_1 order by 4
select ID,name,address,class from dbo.Table_1 order by 4
-- isnull之后,列名也将为空
select ID,name,address,isnull(grade,''),isnull(class,'') from dbo.Table_1
-- as 关键字,给表列起“别名”
select ID,name,address,isnull(grade,'')as grade111,isnull(class,'')as class111 from dbo.Table_1
-- + 关键字:将“列”与"字符串"连接起来
select ID,'name is '+name as name,address,grade,class from dbo.Table_1
-- +、-、*、/、()运算符
select ID,name,address,grade*2+100,class from dbo.Table_1
select ID,name,address,round(grade*2+100,2) as name111,class from dbo.Table_1 -- 小数点后保留位,四舍五入
select ID,name,address,round(grade*2+100,0),class from dbo.Table_1 -- 小数点后保留位,四舍五入
-- where 语句使用< > = <= <> >= --
select * from Table_1 where grade=24 -- int 与string区别
select * from Table_1 where name='小华'
select * from Table_1 where grade>60
-- or 、and 的使用--
select * from Table_1 where grade>60 and ID>5
select * from Table_1 where grade>60 or ID>5
select * from Table_1 where (address='山东' or ID>8) and class is null
-- between 的使用--
select * from Table_1 where name='小李' and grade between 24 and57 -- grade在到之间
-- where语句中使用Like"%" 或"_" 通配符
select * from Table_1 where name like '小李'
select * from Table_1 where name like '%小%'
select * from Table_1 where name like '小_明'
-- where语句中使用in或notin
select * from Table_1 where class in(2,3,4,5)
select * from Table_1 where address in('山东','贵州') -- address是山东或贵州
select * from Table_1 where class not in(2,3,4,5)
-- where语句中使用isnull 或is not null
select * from Table_1 where class is null
select * from Table_1 where class is not null
-- 聚合函数
select COUNT(class) from dbo.Table_1 where class is not null -- 计算在dbo.Table_1表中,class不为null共有多少行
select distinct(grade) from dbo.Table_1 where grade is not null -- 输出在dbo.Table_1表中,grade唯一一次且不为空的元素
select COUNT(distinct(grade)) from dbo.Table_1 where grade is not null
select AVG(grade) as Avg_grade from dbo.Table_1 -- 计算grade这一列所有的平均值
select AVG(grade) as Avg_grade from dbo.Table_1 where grade is not null -- 与上句结果相同,空值占的比例也会被计算进去
select AVG(grade) as Avg_grade ,Min(grade)as Min_grade,MAX(grade) as Max_grade,Sum(grade) as Sum_grade from dbo.Table_1
select address, AVG(grade) as Avg_grade from dbo.Table_1 --报错,选择列表中的列'dbo.Table_1.address' 无效,因为该列没有包含在聚合函数或GROUP BY 子句中。
select address, AVG(grade) as Avg_grade from dbo.Table_1 group by address
-- having 条件
select address,class, AVG(grade) as Avg_grade from dbo.Table_1
group by address,class
having class>5