---------------------------------查询
---------------------------------查询所有列 select * from Infom
----------------------------------查选择(部分)列显示 select age,name from Infom
----------------------------------where设置查询条件
----------------------------------查找年龄在19岁以上的学生 select name,age,address from Infom where age>=19 ---查询条件
----------------------------------查询地址第二个字是国字 select name,address from Infom
where address like '_国%' ------_代表占一个字符，%匹配任意字符
-------------------------------数据查询 基本查询
-------------------------------order by 排序 降序desc ，升序：asc 默认排序 select id, name,age,address,guidcol from infom
where address like '%昆明%'
order by age asc,score desc----多字段排序，先按第一个字段排序，如果字段相等再按第二个字段排
----------------------------成绩+年龄的平均值排序 select name,score,age from infom where age>20
order by (score+age)/2 desc
select name,score,age from infom where age>20
order by 3 desc -----------按第三个字段降序排序
-----------------------------------------------------------------修改表或者字段---
EXEC sp_rename 'student.年龄' , 'newname' 'column' ]
---------------------别名
---------------------1\\空格 别名
select name a姓名,address 地址 ---方法一 from infom
-------表 别名
select s.name ,s.address from infom s
--------------------2.as 别名
select name as 姓名,address as 地址 ---方法二 from infom
--------------------3.别名 = 列
select 姓名=name ,地址=address ---方法三 from infom
----------------------------------------------------------top关键字一般会跟排序混在一起用
--------------------top关键字 取结果集中的前几 --------------------top子句中必须是正数
select top 3 s.score,s.id, s.name ,s.address from infom s ------------成绩显示前三名
order by score desc
-------------------top 20 percent 表示取 20%，显示前百分比 select top 20 percent s.score,s.id, s.name ,s.address from infom s
order by score desc
----------------------------------------------------------distinct隐藏重复行,默认是all不隐藏重复行显示
select count(distinct name) from infom----------------------count 统计行数
----------------------------------------------------------where 条件的关键字
-- and \\or \\not \\like\\ botween and \\in(1,2,3)1或2或3，或者的关系 --<\\>\\<=\\>=\\!=、<>\\
----------------------------------------------------------条件通配符 ------%：任意字符，_ ：一个字符，
------[a-zA-Z] 字母, [0-9] 数字, [^1-3]不是123
------------------------------查询地址为null的行 select id,name,age,address from infom where address is null
------------------------------查询地址为 not null的行 select id,name,age,address from infom where address is not null
-----------------------------查询性别不是男 和 女 select id,name,sex,age,address from infom where sex not in('男','女')
-------------关键字like ，not like 模糊查询
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
---------------------------分组查询和表连接
------------------------------------------------------------------ ---------------------------聚合函数(单行函
数)--------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
select COUNT (ID)as 人数 from infom -----统计学生的总数
select COUNT (address) from infom ---------统计地址,统计具体字段时null 不会被统计
select COUNT (*) from infom ---------------count(*)会统计所有行,包括空值
--------注意!!---如果查询列表中既有聚合函数又有普通字段,那么普通字段必须是分组字段(在group by 字段 中)
select sum(score) as 总分, --------sum(字段) 求和 max(score) as 最高分, ---------max(字段) 最大值 min(score) as 最低分, ---------min(字段) 最小值 AVG(score) as 平均分 ---avg(字段) 平均值
--- AVG(score) as 平均分,name 错误的写法,name只能写在group by 语句中 from infom
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
---------------------------group by
--------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
---------------------按地址统计相同地址同学的总分
select SUM(score) as总分,AVG(score) as 平均分,address --------按地址分组,所以address能写在此行 from infom
group by address --------------分组,分组为了统计总分
having AVG(score)>80 --------------分组的筛选条件,没有出现group by 就不能出现having
----------------------所有70以上成绩,按地址分组,统计每组的总分\\平均分 select SUM (score) as 总分,AVG (score) as 平均分 ,address from infom
where score>70 ------结果集中数的筛选,在分组之前 group by address
select name,score from Info ----------------用子查询来实现最大值的输出 where score=(select MAX (score ) from Info)
--------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
---------------------------简单方式实现交叉联接(两个表联接所有的数据都被现实)--------------------------
--------------------------------------------------------------------------------------------------------------- ----做笛卡尔积
select s.id,s.name,sc.id,sc.sname,sc.score from infom s ,score sc
------内连接 写法一
select s.id,s.name,sc.id,sc.sname,sc.score from infom s ,score sc inner join score sc on s.id= sc.id ------内连接的条件 ------on s.id <>sc.id --------是全集 - 交集 ------where sc.score>80
------内连接 方法二
select s.id,s.name,sc.id,sc.sname,sc.score from infom s ,score sc where s.id= sc.id
------
-------------------------------------------------------外连接 左连接 --------------左表数据完全显示，右表中相同的数据显示，不同数据null select Student.name,score.score
from Student left join score -----------------先写的为左表 on Student.id=score .id -----------------连接条件
-------------------------------------------------------外连接 右连接 --------------右表数据完全显示，左表中相同的数据显示，不同数据显示null select Student.name,score.score from Student right join score on Student.id=score .id
-------------------------------------------------------全连接 full join -------------------------------------------------------左、右表的数据完全显示，相同的数据显示一次
select Student.name,score.score from Student full join score on Student.id=score .id
-------------------------------------------------------交叉联接
------------------------------------------交叉联接得到的是两表联接所有的数据组合
------------------------------------------（A表的数据记录* B 表的数据记录） -------------------------------------------方式一 select Student.*,score.* from Student,score
-------------------------------------------方式二 select score .*,Student.* from Student cross join score
-----------------------------------------------------多表联接
--------------------------------------要求查出张三 C#的考试成绩，涉及student，score，subject三个表 ---------方式一:
select student.name,subject.sname ,score .score from Student inner join score
on student.id= score.id inner join subject on score.id=subject.id
where Student.name='张三' and subject.sname='C#'
---------方式二:等值联接
select student.name,subject.sname ,score .score