--查询语句详细分析
--1.字段别名
字段名 as 别名***
字段名 别名
别名=字段名
select name,sex,city from student
select name as 姓名,性别=sex,city 城市 from student
select * from student

select name,birthday,city,sex from student
select name,year(getdate())-year(birthday) as 年龄,city,sex from student
 

--2.DISTINCT  : 针对纪录集，不是针对字段的 ，是查询记录集中不重复的记录
all   :所有信息记录
distinct :不重复的信息纪录
select * from student
select all * from student
select distinct * from student

select city,sex from student
select all city,sex from student
select distinct city,sex from student

select city from student
select distinct city from student
update student set city='广州' where city='34'

select distinct sex from student
select distinct class_type from student

--3.top
top n   前n条记录
top n percent 前n%条记录

select * from student
select top 3 * from student
select top 50 percent * from student
--4.between...and... 两者之间   数字
between...and... 在两者之间
not between...and... 不在两者之间
select * from student
alter table student add age as datepart(year,getdate())-year(birthday)
--age>=26
select * from student where age>=26
--age<=40
select * from student where age<=40
--age>=26    age<=40  [26,40]
select * from student where age>=26 and age<=40
select * from student where age between 26 and 40
--不在 [26,40]
select * from student where not (age>=26 and age<=40)
select * from student where age<26 or age>40
select * from student where not age between 26 and 40
select * from student where age not between 26 and 40

--5.in  
IN   在某个范围内
NOT IN  不在某个范围内
主要用于字符串,本身可以使用于任意类型

--北京
select * from student where city='北京'
--上海
select * from student where city='上海'
--北京  上海
select * from student where city='北京' or city='上海'
--北京  上海  香港
select * from student where city='北京' or city='上海' or city='香港'
select * from student where city in('北京','上海','香港')
--除北京  上海  香港
select * from student where not(city='北京' or city='上海' or city='香港')
select * from student where city!='北京' AND city!='上海' AND city!='香港'

select * from student where NOT city in('北京','上海','香港')
select * from student where city NOT in('北京','上海','香港')

select * from student
19 22 26
select * from student where age=19 or age=22 or age=26
select * from student where age in(19,22,26)

select * from student where birthday in('2000-1-1','2008-11-3')
print year(getdate())
print month(getdate())
print day(getdate())
--int
select name,sex from student
cast(year(birthday) as char(4))+'-'+
cast(month(birthday) as varchar(2))+'-'+
cast(day(birthday) as char(4))

--在将 varchar 值 'f' 转换成数据类型 int 时失败。
--类型转换函数 cast
cast(字段/变量 as 新类型)

--别名只是为了显示直观,功能有限制,
--只能在排序语法中直接使用,其他所有语法都不能使用
select name,birthday,cast(year(birthday) as char(4))+'-'+cast(month(birthday) as varchar(2))+'-'+cast(day(birthday) as char(4)) as birth,sex from student
where cast(year(birthday) as char(4))+'-'+cast(month(birthday) as varchar(2))+'-'+cast(day(birthday) as char(4)) in('2000-1-1','2008-11-3')
--查询统计今天过生日的学生信息
2008-11-6
--出生月份=当前日期月份
--出生日子=当前日期日子
select * from student
where month(birthday)=month(getdate())
and day(birthday)=day(getdate())
--6.查询中统配符的使用(模糊查询):(针对字符串)
%：代表任意多个字符，比如：“%S%”   类似windows *
_（下划线）：代表任意单个字符   类似windows ?

select * from student where name like '张%'
select * from student where name like '海%'
select * from student where name like '%海'
--包含 海
select * from student where name like '%海%'

select * from student where name like '_海%'
select * from student where name like '%海_'
select * from student where name like '海_'
select * from student where name like '_海'
 
--查询电话号码第一位是1的
select * from student where tele like '1%'
select * from student where tele like '2%'
select * from student where tele like '3%'
select * from student where tele like '4%'
--查询电话号码第一位是1 或2 或3 或4 的
select * from student
where tele like '1%' or tele like '2%' or tele like '3%' or tele like '4%'
--error:select * from student where tele in('1%','2%','3%','4%')
select * from student
where tele like '1%' or tele like '2%' or tele like '3%' or tele like '4%'

select * from student where tele like '[1234]%'
select * from student where tele like '[1,2,3,4]%'
select * from student where tele like '[1-4]%'
 
--不是1 2 3 4
select * from student
where not(tele like '1%' or tele like '2%' or tele like '3%' or tele like '4%')
select * from student
where tele not like '1%' and tele not like '2%' and tele not like '3%' and tele not like '4%'
 

select * from student where not tele like '[1234]%'
select * from student where tele not like '[1234]%'
select * from student where tele not like '[1,2,3,4]%'
select * from student where tele not like '[1-4]%'
select * from student where tele like '[^1234]%'
select * from student where tele like '[^1,2,3,4]%'
select * from student where tele like '[^1-4]%'
--张 王
select * from student where name like '[张王]%'
select * from student where name like '%[飞海]'
select * from student where class_type like '%[程]_'
--查询1980  1985  1990  1995出生的
select * from student
where cast(year(birthday) as char(4)) like '19[89][05]'
select * from student
where year(birthday) like '19[89][05]'

--查询2003  2004  2005入学的
select * from student
where stu_no like '200[3-5]%'
select * from student
where stu_no like '[2003,2004,2005]%'
 
--字符串函数
print substring('abcdef',2,3)
print substring('abcdef',2,30)+'dsdf'
print left('abcdef',3)
print right('abcdef',3)
print len('abc')
print len('abc   ') --len函数会自动去掉尾随空格
print lower('asdEdFDSd')
print upper('asdEdFDSd')
print replace('abcdefcdw','cd','1234')
print replace('abcdefcdw','cxd','1234')

select * from student where name like '张%'
select * from student where substring(name,1,1) = '张'
select * from student where left(name,1) = '张'

--统计全体学生有哪些姓
select left(name,1) as fname,name,city from student
select left(name,1) as ddd,name,city from student

update student set name='张'+name where name like '[0a]%'

select distinct left(name,1) as fname from student
 

000000000000000000000000000
--在查询的基础上创建表 表复制
select 字段列表 into 新表名 from 表名 where 条件

select * from student where city='北京'

select * into beijingTable from student where city='北京'
select * from beijingTable

select name,sex,city,class_type into t001  from student
where city='北京' and sex='男' and class_type='java开发工程师'
select * from t001
--表的备份
select * into student_bak from student
select * from student
 
delete from student_bak
--排序：  ORDER BY 字段名称 [ ASC 升序 | DESC 降序]
select * from student order by age
 
select * from student order by age asc
select * from student order by age desc
--top
select top 1 * from student order by age asc
select top 1 * from student order by age desc
select * from relation order by mark desc

select top 1 * from relation order by mark desc
select top 1 with ties * from relation order by mark desc
select top 3 with ties * from relation order by mark desc

select name,sex,city,class_type,age from student
order by sex desc,city asc,class_type asc,age asc
 
nan < nv
a~z  97~122
 
select name,sex,city,class_type,age from student
order by 2 desc,3 asc,4 asc,5 asc

select * from student order by age
select * from student order by 9

--查询语句中函数的使用： 聚合(统计)函数
--① avg、min、max、sum
select avg(age) from student
select
avg(age) as 平均年龄,
min(age) as 最小年龄,
max(age) as 最大年龄,
sum(age) as 年龄总和,
sum(fee) as 费用合计,
max(birthday) as 最大生日
from student

select
avg(age) as 平均年龄,
min(age) as 最小年龄,
max(age) as 最大年龄,
sum(age) as 年龄总和,
sum(fee) as 费用合计,
max(birthday) as 最大生日
from student where city='北京' and sex='男'
 
--② COUNT：
-- count(字段名称):只计算指定字段不为空(null)的个数
-- count(*)计算所有的行（包括有空值的行）
select * from student
update student set tele=null where id=1
select count(tele) as num from student

select count(city) as num from student

select count(distinct city) as num from student
--判断某个字段是否有重复值
select count(city) as num1,count(distinct city) as num2 from student
select count(stu_no) as num1,count(distinct stu_no) as num2 from student
delete from student where stu_no='200802008'
select count(stu_no) as num1,count(distinct stu_no) as num2 from relation
 
select avg(age) as avgage,count(*) as num from student

select count(*) as num from student where sex='男'
select count(*) as num from student where sex='男' and city='上海'
select
avg(age) as 平均年龄,
min(age) as 最小年龄,
max(age) as 最大年龄,
sum(age) as 年龄总和,
sum(fee) as 费用合计,
max(birthday) as 最大生日,count(*) as 人数
from student where city='北京' and sex='男'
 
--全体学生的平均年龄26
select avg(age) as avgage from student
--统计年龄大于26的学生信息
select * from student where age>26
--统计年龄大于全体学生的平均年龄的学生信息
select * from student
where age>(select avg(age) as avgage from student)
--子查询
--统计陈刚的总分
select sum(mark) as summark from relation
where stu_no=(select stu_no from student where name='陈刚')
--统计陈刚的总分和几门课
select sum(mark) as summark,count(*) as num from relation
where stu_no=(select stu_no from student where name='陈刚')

--统计陈刚的学号200507001
select stu_no from student where name='陈刚'
--统计200507001的总分
select sum(mark) as summark from relation where stu_no='200507001'

--统计200507001的relation信息
select * from relation where stu_no='200507001'
 

--统计平面设计课程的平均分和选修人数
select avg(mark) as avgmark,count(*) as num from relation
where c_no =(select c_no from course where c_name='平面设计')
 
--统计陈刚学习平面设计课程的成绩
select mark from relation
where stu_no=(select stu_no from student where name='陈刚')
and c_no=(select c_no from course where c_name='平面设计')
 

select mark from relation
where stu_no='200507001' and c_no='c_002'
陈刚的学号200507001
select stu_no from student where name='陈刚'
平面设计课程编号c_002
select c_no from course where c_name='平面设计'
 
--统计北京地区学生学习平面设计课程的人数
select count(*) as num from relation
where stu_no in(select stu_no from student where city='北京')
and c_no in (select c_no from course where c_name='平面设计')
 

select count(*) as num from relation
where stu_no in(select stu_no from student where city='北京')
and c_no=(select c_no from course where c_name='平面设计')

北京地区学生编号
200301001 200308002 200501011
select stu_no from student where city='北京'
平面设计课程编号c_002
select c_no from course where c_name='平面设计'
--统计200301001 200308002 200501011学习c_002课程的人数
select * from relation
where stu_no in('200301001', '200308002', '200501011')
and c_no in('c_002')

select * from student
--统计今天过生日的信息网络工程师学生学习平面设计课程的及格人数
select count(*) as num
from relation
where stu_no in(
select stu_no from student
where month(birthday)=month(getdate()) and day(birthday)=day(getdate()) and class_type='信息网络工程师')
and c_no in(select c_no from course where c_name='平面设计')
and mark>=60
 
今天过生日的信息网络工程师学生编号
select stu_no from student
where month(birthday)=month(getdate()) and day(birthday)=day(getdate()) and class_type='信息网络工程师'
平面设计课程编号
select c_no from course where c_name='平面设计'
 
--统计北京地区的年龄大于北京地区男生平均年龄的男生信息

select * from student
where city='北京' and sex='男'
and age>(select avg(age) as avgage from student where city='北京' and sex='男')
 
统计北京地区的男生信息
select * from student
where city='北京' and sex='男'
and age>(select avg(age) as avgage from student where city='北京' and sex='男')
 
北京地区男生平均年龄28
select avg(age) as avgage from student where city='北京' and sex='男'
--Sql环境中的常用日期函数：
 getdate()
 getutcdate()
 year()
 month()
 day()
 datepart()
 datename()
 dateadd()
 datediff()
--getdate()
格式:getdate()
功能:ff
返回值类型:datetime
print getdate()

select getdate()
select getdate() as currentDate,* from student
 
--getutcdate()
格式:getutcdate()
功能:返回表示当前的 UTC 时间（通用协调时间或格林尼治标准时间）
的 datetime 值。当前的 UTC 时间得自当前的本地时间和运行 Microsoft SQL Server 实例的计算机操作系统中的时区设置。
返回值类型:datetime
select getdate(),getutcdate()
--year month day   --int
select year(getdate()),month(getdate()),day(getdate())
 
--datepart()
格式: datepart(参数,日期)
功能:返回指定日期按照参数部分的返回值
返回值类型:int

print datepart(year,getdate())
print datepart(month,getdate())
print datepart(day,getdate())
print datepart(hour,getdate())
print datepart(minute,getdate())
print datepart(second,getdate())
print datepart(millisecond,getdate())
print datepart(year,getdate())+datepart(month,getdate())+datepart(day,getdate())
print 2008+11+8

print datepart(quarter,getdate())  --季度
print datepart(quarter,'2008-4-1')  --季度

print datepart(dayofyear,'2008-4-1')   
print datepart(dayofyear,getdate())   
print datepart(dayofyear,'2008-12-31')   
print datepart(dayofyear,'2007-12-31')   

print datepart(week,'2007-12-31')    --周

print datepart(week,getdate())    --周
print datepart(weekday,getdate())    --星期几
1 2 3 4 5 6 7
天一二 三四五六
 
---//
--datename()
格式: datename(参数,日期)
功能:返回指定日期按照参数部分的返回值
返回值类型:varchar

print datename(year,getdate())
print datename(month,getdate())
print datename(day,getdate())
print datename(hour,getdate())
print datename(minute,getdate())
print datename(second,getdate())
print datename(millisecond,getdate())
print datename(year,getdate())+datename(month,getdate())+datename(day,getdate())
print 2008+11+8

print datename(quarter,getdate())  --季度
print datename(quarter,'2008-4-1')  --季度

print datename(dayofyear,'2008-4-1')   
print datename(dayofyear,getdate())   
print datename(dayofyear,'2008-12-31')   
print datename(dayofyear,'2007-12-31')   

print datename(week,'2007-12-31')    --周

print datename(week,getdate())    --周
print datename(weekday,getdate())    --星期几
--dateadd()
格式:dateadd(参数,整数,日期)
功能:给指定日期按照参数增加整数个值
返回值类型:datetime

print dateadd(year,2,getdate())
select dateadd(year,2,getdate())
select dateadd(year,-2,getdate())
select dateadd(month,2,getdate())
select dateadd(month,-2,getdate())
select dateadd(day,2,getdate())
select dateadd(day,-2,getdate())
select dateadd(day,2,'2008-12-31')

select dateadd(hour,2,getdate())
--datediff()
格式:datediff(参数,开始日期,结束日期)
功能:返回结束日期按照参数减去开始日期的返回值
类型:int
print datediff(year,'1980-9-21',getdate())
print datediff(month,'1980-9-21',getdate())
print datediff(day,'1980-9-21',getdate())
print datediff(hour,'1980-9-21',getdate())
print datediff(minute,'1980-9-21',getdate())
print datediff(second,'1980-9-21',getdate())
print datediff(millisecond,'1980-9-21',getdate())
 

新
24小时  fabu
if datediff(hour,fabu,getdate())<=24 jfvfg
 
7天
if datediff(day,fabu,getdate())<=7 jfvfg
6)select year(getdate())-year(min(birthday)) as 最高年龄  from student
7)select datediff(yyyy,min(birthday),getdate()) as 最高年龄  from student
8)select max(datediff(yyyy,birthday,getdate())) as 最高年龄  from student

year yyyy/yy
00000000000000000000000000000
--查找表中某个字段的重复值
--判断student表中name字段是否有重复值，有输出
select name,count(*) as num from student group by name
having count(*)>1

select stu_no,count(*) as num from relation group by stu_no
having count(*)>1

select * from t001

insert into t001(name,sex,city,class_type) values('张星雨','男','dd','ddd')
delete from t001 where name in(
select name from t001 group by name
having count(*)>1
)
--多表连接查询
--1.交叉连接 Cross  join/,

create table test_table1
(
 name varchar(20),
 city varchar(20)
)
go
insert into test_table1(name,city) values('张三','北京')
insert into test_table1(name,city) values('李四','上海')
insert into test_table1(name,city) values('王明明','上海')
go
create table test_table2
(
 name varchar(20),
 sex  char(2),
 age  int
)
go
insert into test_table2(name,sex,age) values('张三','男',22)
insert into test_table2(name,sex,age) values('李倩','女',34)
insert into test_table2(name,sex,age) values('王明明','男',19)
go
select * from test_table1
select * from test_table2

select * from test_table1 cross join test_table2
select * from test_table1 , test_table2

--内连接
inner join... on 条件
,... where 条件

select * from test_table1 inner join test_table2
on test_table1.name=test_table2.name
select * from test_table1 , test_table2
where test_table1.name=test_table2.name
--/
select test_table1.*,test_table2.sex,test_table2.age from test_table1 inner join test_table2
on test_table1.name=test_table2.name

select a.*,b.sex,b.age from test_table1 a inner join test_table2 b
on a.name=b.name

select * from test_table1 , test_table2
where test_table1.name=test_table2.name

select test_table1.*,test_table2.sex,test_table2.age from test_table1 , test_table2
where test_table1.name=test_table2.name

select a.*,b.sex,b.age from test_table1 a, test_table2 b
where a.name=b.name
 
select * from student
select * from relation
--name c_no  mark

select a.name,b.c_no,b.mark
from student a inner join relation b
on a.stu_no=b.stu_no

select a.name,b.c_no,b.mark
from student a , relation b
where a.stu_no=b.stu_no

select * from course
select * from relation
--stu_no  c_name  mark
select b.stu_no,a.c_name,b.mark
from course a inner join relation b
on a.c_no=b.c_no
select b.stu_no,a.c_name,b.mark
from course a , relation b
where a.c_no=b.c_no

select * from student
select * from course
select * from relation
--name c_name mark
select a.name,c.c_name,b.mark
from student a inner join relation b
on a.stu_no=b.stu_no inner join course c
on c.c_no=b.c_no
select a.name,b.c_name,c.mark
from student a,course b,relation c
where a.stu_no=c.stu_no and b.c_no=c.c_no
 
--姓名和总分(连接查询和分组查询综合)
--1.先连接再分组
select a.name,sum(b.mark) as summark from student a inner join  relation b
on a.stu_no=b.stu_no
group by a.name
--2.先分组再连接
select a.name,b.summark
from student a inner join
(select stu_no,sum(mark) as summark from relation group by stu_no) b
on a.stu_no=b.stu_no
 
drop table tempTable
--学号和总分
select stu_no,sum(mark) as summark from relation group by stu_no
select stu_no,sum(mark) as summark into tempTable from relation group by stu_no

select a.name,b.summark from student a inner join tempTable b
on a.stu_no=b.stu_no
 
--select * from course
--select * from (select * from (select * from course)b) a
 
--课程名称和平均分和选修人数(连接查询和分组查询综合)
--1.先连接再分组
select a.c_name,avg(b.mark) as avgmark,count(*) as num
from course a,relation b
where a.c_no=b.c_no
group by a.c_name
select a.c_name,avg(b.mark) as avgmark,count(*) as num
from course a inner join relation b
on a.c_no=b.c_no
group by a.c_name
--2.先分组再连接
select a.c_name,b.avgmark,b.num
from course a,
(select c_no,avg(mark) as avgmark,count(*) as num from relation group by c_no) b
where a.c_no=b.c_no
select a.c_name,b.avgmark,b.num
from course a inner join
(select c_no,avg(mark) as avgmark,count(*) as num from relation group by c_no) b
on a.c_no=b.c_no
--左连接
left outer join ... on 条件
select * from test_table1
select * from test_table2
select * from test_table1 a inner join test_table2 b on a.name=b.name
select * from test_table1 a left outer join test_table2 b on a.name=b.name
--右连接
right outer join ... on 条件
select * from test_table1 a right outer join test_table2 b on a.name=b.name
--完全连接
full outer join ... on 条件
select * from test_table1 a full outer join test_table2 b on a.name=b.name
 

--内连接  左连接  右连接
00000000000000000000000000000000000