sql入门(4)------SQLServer4

sql入门(4)------SQLServer4

---查询

create database mydb4;

use mydb4;

create table info

(

id int primary key identity(1,1),

name varchar(20) not null,

gender varchar(20) not null,

class varchar(20),

score int

)

go

insert into info values('余胜军','男','s1t80',92)

insert into info values('黄辉','男','s1t80',91)

insert into info values('汪保国','男','s1t80',78)

insert into info values('商宇彤','男','s1t80',98)

insert into info values('应明珠','女','s1t81',95)

insert into info values('廖一帆','男','s1t81',52)

insert into info values('陈杰','男','s1t80',83)

insert into info values('胡文鑫','女','s1t81',82)

insert into info values('刘坤','男','s1t80',53)

insert into info values('蒋文哲','男','s1t81',64)

insert into info values('冷泽华','男','s1t81',72)

insert into info values('曹敏','女','s1t81',66)

insert into info values('陈秦定','女','s1t80',89)

insert into info values('赵娇','女','s1t80',73)

insert into info values('柳星','男','s1t82',77)

insert into info values('胡月','女','s1t82',87)

insert into info values('郑克','男','s1t82',68)

--查询所有数据

select * from info;

--查询的时候对数据进行排序:   order by     desc降序,  asc升序(默认升序)

select * from info order by score desc;--按成绩排序

select * from info where gender='男' order by score desc;--查询男生,排序

--聚合函数:        count,sum,avg,max,min

select max(score) from info;  --查询最大值

select min(score) from info; --查询最低分

select sum(score) from info;

select avg(score) from info; 

select count(*) from info; 

--给查询到每一列,起别名

select id as 编号,name as 姓名,gender,class,score from info;

--as  用于给字段起别名的关键字  as关键字也可以省略

select id 编号,name 姓名 from info;

select count(*) 总记录数 from info;

select count(*) 总人数,max(score) 最高分,min(score) 最低分,

avg(score) 平均分 from info;

--查询有哪几个班级

select class 班级 from info;

--分组函数   group by 

语法:  select  要查询的字段名  from 表名 group by 分组的字段名;

示例:

select class 班级 from info group by class;

--------------------------

在查询语句,如果使用了分组函数,可以查询的字段,只有:

1、用来分组的字段可以查询

2、聚合字段可以查询

--------除此之外，都不允许查询

select class,avg(score) 平均分 from info group by class;

--任务：查询每一个班平均分,并且降序

select class,avg(score) 平均分 from info group by class order by avg(score) desc;

--如果在查询语句，又有分组函数，又出现了排序函数

select 字段名 from 表  group by  字段 order by 字段

--统计每一个班，考试合格的人数: score>=60

select class,count(*) from info where score>=60  

group by class order by  count(*) asc

select * from 表   where .....group by ...... order by...

--查询每个班级的平均分，只显示平均分在75分以上的信息

班级名称   平均

--having关键字，用于在分组以后，设置查询条件，它不能单独用，必须配合分组函数

--一起使用

--统计每一个班，各自有多少人

select class 班级名称,count(*) 人数 from  info group by class;

--统计每一个班的平均分

select class 班级名称,avg(score) 平均分 from  info group by class;

--统计每一个班的平均分，只显示平均分在75以上的

select class 班级名称,avg(score) 平均分 from  info 

group by class having avg(score)>=75;

--统计每一个班的平均分，只显示平均分在75以上的，并且，排序

select class 班级名称,avg(score) 平均分 from  info 

group by class having avg(score)>=75 order by avg(score) asc;

----

多字段分组

select * from info;

统计每个班，各有几名学生

select class,gender 性别,count(*) 人员总数 from info 

group by class,gender;

--统计每个班级中，考试合格男女各自人数

select class,gender,count(*) from info 

where score>=60 

group by class,gender;

create table stu

(

id int primary  key identity(1,1),

name varchar(20),

english int,

chinese int,

math int

)

go

insert into stu values('张三',95,90,100);

insert into stu values('李四',90,98,97);

insert into stu values('王五',91,70,80);

insert into stu values('赵六',92,88,89);

--按照数学成绩排序降序

select * from stu order by math desc;

--按总分数排序，同分情况下，语文成绩优先

select id,name,english,chinese,math,(english+chinese+math) 总分 

from stu 

order by (english+chinese+math) desc,chinese desc;

--按总分数排序，同分情况下，语文成绩优先(只显示总分数在250分以上的)

---SQLServer函数的分类:

1、字符串函数

2、日期函数

3、数学函数

4、系统函数

----------------------------------------------

字符串函数:  

1、len()----用于计算一个字符串的长度

--select len('大家好!');

select name,len(name) 字符长度 from info;

2、upper() ---用于将字符中的字母，全部转换成大写字母

--select upper('abcdef');

select upper(name) from inf;

3、lower()----用于将字符中的字母，全部转换成小写字母

select lower('ABCD');

--**************************************************************

4、right(字符串,长度)  ----------用于从字符串，从右向左取几个字符

--select right('abcd12345',3);

select right(name,1) from info;

5、replace(字符串，原来旧数据，新数据) ;

--将字符串，指定部份的内容，替换成新数据

select replace('张三，你好','张三','李四')

select replace('13986141027','86141','*****');

6、substring(字符串，开始位置，截取的字符长度);

--截取字符串，从指定位置开始，截取指定长度

select substring('abc123hello',4,3);

select tel from stu;

select *,replace(tel,substring(tel,4,5),'*****') from stu;

7、stuff('字符串',开始位置，长度,'新的字符串')

作用：用于将字符串中，指定位置开始的指定长度内容，替换成新的字符串

select stuff('13986141027',4,5,'*****');

select name,stuff(tel,4,5,'*****') 手机号码 from stu;

--****************************************************

日期类型的数据:

create table stu

(

sid int primary key identity(1,1),

name varchar(20),--姓名

gender char(2),--性别

tel varchar(20),--电话

idCard varchar(20),--身份证号

bir datetime--出生日期

)

insert into stu values('jack','男','12312312312','12345','19790912');

insert into stu values('jack','男','12312312312','12345','1990-02-12');

insert into stu values('jack','男','12312312312','12345','1998');

select name,bir from stu where bir>'1990-01-01'

查询1990-1-1-----------1993-1-1之间出生的(两种方式)

select name,bir from stu where bir >'1991-1-1' and bir < '1993-1-1';

select name,bir from stu where bir  between '1991-1-1' and '1993-1-1';

-------------日期函数

getDate() -------------得到当前系统时间

select getDate();

dateadd(时间符号,数值,指定时间);-----

作用:用于在指定时间上的指定时间符号的部份，加上数值，形成一个新的时间

select dateadd(yyyy,3,'2014-1-1');

select dateadd(mm,3,'2014-1-1');

select dateadd(yyyy,-3,'2014-1-1');

select name,dateadd(yyyy,-20,bir) 日期 from stu;

select dateadd(yyyy,-20,getDate());

datediff(时间符号,第一个时间，第二个时间);

--用于计算两个时间之间，所间隔的时间差,时间差的单位由时间符号决定

select datediff(mm,'2012-5-13','2014-5-13');

姓名   出生日期   年龄

select name,bir,datediff(yyyy,bir,getDate()) from stu;

22-25之间

select name,bir,datediff(yyyy,bir,getDate()) from stu

where datediff(yyyy,bir,getDate()) between 22 and 25

order by bir asc;

dateName(时间符号,日期);

---用于取得指定时间部份的值

datePart(时间符号,日期);

---用于取得指定时间部份的值

select dateName(dw,getDate());

select datePart(dw,getDate());

getDate()   dateadd();  datediff()   datename()     datepart()

-------------------

系统函数

convert()  ----它是一个转换函数，可以将字符串转换成数字，

--也可以将数字转换成字符串,也可以用指定格式输出内容

select convert(varchar(20),getDate(),120); --去掉时间部份的毫秒

select convert(varchar(20),getDate(),119); 

日期函数的几个符号:

yyyy--年份

mm --月份

dd --天

hh --小时

mi --分钟

ss  --秒钟

ww --这个星期是一年中的第几个星期

dw --今天是一个星期的几天