黑马程序员-自学笔记-SQL Server与ADO.Net（三）

SQL Server与ADO.Net（三）

---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------

1、Top

l  获取前几条数据，放在列前面，与order by一起使用

l  排序

•    -> select * from 表名 order by 字段 asc

•    -> asc表示升序、desc表示降序

l  查询（百分比向右对齐）

•    -> select top 3 * from 表名 order by Age asc

•    -> select top 5 percent * from 表名

 

2、Distinct

l  distinct去除重复的数据（distinct是对整个结果集进行数据重复处理，不是针对某一列）

l  检查返回不重复的数据（对于整条记录不重复才会去除）

•    select distinct * from 表名

l  针对某一列去除重复，检索数据

•    select distinct(需要去除的字段),其他字段 from 表名

 

3、聚合函数

l  MAX(字段)

l  MIN(字段)

l  AVG(字段)

•    在计算时，对于null的数据不计入总是

l  SUM(字段)

l  COUNT(字段)

•    count(*)计算所有的列、count(字段)不计为null的列

l  -> select的字段可以当做变量名来进行计算

•    select (english+math)/2 as 平均分 form score

l  计算时null表示未知，既不是最大，也不是最小，与null的运算结果也为null

 

4、带条件的查询

l  select …from…where

•    查询没有及格的学生的学号

•    查询年龄在20-30岁之间的男学生

l  between…and …在之间，包含两端

•    查询年龄在20-30岁之间的男学生

•    查询math成绩在80-90分之间的所有学生

l  查询班级id为1，2，3的所有学生：in(val1,val2,val3)

•    select sName,sAge from studentwhere sClassId=1 or sClassId=2 or sClassId=3

•    select sName,sAge from studentwhere sClassId in (1,2,3)

 

5、带条件的查询-模糊查询

l  查询所有姓张的同学

•    select * from student whereleft(sName,1)=‘张‘   看上去很美,如果改成查询名字中带亮的学生怎么做？

l  换一种做法 like 

•    select  * from student where sName like ‘张%’    会吧所有姓张的都查询到，现在我想查询姓张并且名字是一个字的学生？

•    select  * from student where sName like ‘%亮%’

l  通配符 %多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

l  通配符_ 单字符匹配，它匹配单个出现的字符

l   [] 只匹配一个字符 并且这个字符必须是[]范围内的，或[_]

l  not与like一起使用：not like ….

 

6、空值处理

l  查询所有生日为null的姓名

•    -> select Fname from T_StudentFbirthday is null

l  判断null只能使用is或is not

•    is null和is not null

l  函数 isnull(字段名, 替换值)

•    查询时专门为空值的字段显示处理

•    select isnull(字段,123) from 表名

l  空字符串与null含义不同，空字符串依旧有内容

•    处理空字符可以使用len()函数和is null

 

7、数据排序

l  排序order by

l  如果有where筛选，order总放在后面

•    select * from … where … order by …

l  多列排序（从左至右）

•    select * from 表名 order by

•    字段1 [desc], 字段2 [desc], 字段3[desc]

l  datalength()系统函数，计算字节数

l  len()函数，计算字符数

l  注：ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。

 

8、数据分组

l  select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息

l  按性别分组

•    select FSex form T_Student groupby Fsex

l  按班级分组，计算总人数

•    select FClass, count(*) as 班级总人数 from T_Student group byFClass

l  筛选班级人数大于3的

•    where能使用吗？where是分组前对数据进行筛选

•    筛选人数，是将班级分完组后再统计的，这里使用having

•    having表示分组后对数据的筛选

•    select FClass, count(*) as 班级人数 from T_Student group byFClass having count(*)>3

l  在查询语句中使用了group by子句后，只能select聚合函数与分组的字段

l  查询列中使用聚合函数后，不允许出现除分组列外的其他列

l  注：可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息. 按照group by 子句中指定的表达式的值分组查询结果。

 

9、having语句

l  having 是group by的条件对分组后的数据进行筛选（与where类似，都是筛选，只不过having是用来筛选分组后的组的。）

l  在where中不能使用聚合函数，必须使用having，having要位于group by之后，

l  查询班级人数超过三个人的班级

•    select FClassId,count(FName) fromT_Student group by FClassId having count(FName)>3

•    注意having中不能使用未参与分组的列，having不能替代where。作用不一样，having是对组进行过滤

 

10、联合结果集

l  union就是将多个结果集合并成一个结果集

l  查询语句1 union 查询语句2

l  union会自动合并重复的数据

l  union all会保留重复数据

•    一般都是使用union all，效率会高点

l  联合需要注意类型一致

•    cast()函数可以实现数据的类型转换 ,如：cast(字段名或聚合函数 as 类型)

l  一次插入多条数据：

insert into Score(studentId,english,math)

select 1,80,100 union

select 2,60,80 union

select 3,50,59 union

select 4,66,89 union

select 5,59,100

 

11、一次插入多条数据

l  把现有表的数据插入到新表（表不能存在）

l  select * into newStudent fromstudent（newStudent表在select查询的同时自动建立。）

•    --把现有表的数据复制到一个已存在的表

•    通过这种方式复制，只能复制表中的数据，以及列的名字和数据类型，对于约束，不会复制过来。

l  insert into backupStudent select *from students  (backupStudent表必须提前建好)

 

12、字符串函数（掌握）

l  LEN() ：计算字符串长度（字符的个数。）

l  datalength();//计算字符串所占用的字节数，不属于字符串函数。

l  LOWER() 、UPPER () ：转小写、大写

l  LTRIM()：字符串左侧的空格去掉

l  RTRIM () ：字符串右侧的空格去掉

l  LTRIM(RTRIM('         bb        '))

l  LEFT()、RIGHT()  截取取字符串

•    SELECT LEFT('abcdefg',2)

l  SUBSTRING(string,start_position,length)

           参数string为主字符串，start_position为子字符串在主字符串中的起始位置，该位子从1开始

           length为子字符串的最大长度。

            SELECT SUBSTRING('abcdef111',2,3)

 

13、日期函数

l  获得系统当前时间：getdate()

•    select getdate()

•    当前是第几天：select day(getdate())

•    当前的年份：select year(getdate())

•    当前的月份：select month(getdate())

l  在当前时间加上一个时间间隔

•    dateadd(在哪个部分增加,增加多少,日期)

l  判断两个日期的间隔

•    datediff(单位,开始的时间，结束的时间)

l  实例：将学生按年龄分组（学生表要有出生年月）

•    selectdatediff(year,sBirthday,getdate()),count(*)

•    from student group bydatediff(year,sBirthday,getdate)

l  返回日期的特定部分

•    datepart()

•    实例：统计学生生日的年份数量（某一年的有多少个等）

•    selectdatepart(year,sBirthday),count(*)

•    from student group bydatepart(year,sBirthday)

 

14、类型转换函数

l  CAST ( expression AS data_type)

l  CONVERT ( data_type,expression,[style])

l  Select ‘您的班级编号’+ 1  错误这里+是数学运算符

•    SELECTFIdNumber,

•    CAST(RIGHT(sNo,3)AS INTEGER) as 后三位的整数形式,

•    CAST(RIGHT(sNo,3)AS INTEGER)+1 as 后三位加1,

•    CONVERT(INTEGER,RIGHT(sNo,3))/2as 后三位除以2

•    FROMstudent

l  对日期的转换。转换成各种国家格式的日期。

•    selectconvert(varchar(20),getdate(),104)

•    Style的格式，查sql帮助。（输入convert函数查询）

•    将日期转换为指定格式的字符串。日期→字符串

 

附：随机练习代码：

/*

    复习

    建数据库 create database 数据库名

    建表    create table 表名（字段类型默认值）

    建约束      至少知道如何通过设计器实现

   

    增删改查

       增     insert into 表名（字段列表）values(值列表)

       删     delete from 表名where 条件

       改     update 表名set 字段=值where条件

       查     select * from 表名

*/

use School

go

select * from MyStudent;

 

--==Top==

select top 10 percent

    --GETDATE() as 当前时间,

    Fid asid号,

    FName as姓名,

    FAge 年龄,

    FGender 性别,

    数学成绩=FMath,

    英语成绩=FEnglish,

    (FMath+FEnglish)/2 as 平均成绩,

    FMath+FEnglish as 总成绩

from MyStudent

where FMath+FEnglish>=100

order by FMath desc,FEnglish desc  --desc降序,默认asc升序

 

--==Distinct==

create table 重复数据

(

    id intidentity(1,1) primary key,

    ch_name nvarchar(10)

)

insert into 重复数据 values('张三');

insert into 重复数据 values('张三');

insert into 重复数据 values('张三');

insert into 重复数据 values('张三');

select distinct * from 重复数据;

--对于整条记录不重复才会去除

create table 重复数据

(

    id int,

    ch_name nvarchar(10)

)

insert into 重复数据 values(1,'张三');

insert into 重复数据 values(2,'张三');

insert into 重复数据 values(3,'张三');

insert into 重复数据 values(4,'张三');

select distinct * from 重复数据;

 

--====聚合函数====

select * from MyStudent order by FMath desc;

--max,min,avg,sum,count

select MAX(FMath),MIN(FMath),AVG(FMath),SUM(FMath),COUNT(FMath) from MyStudent;

--用法上需要注意的聚合函数

--avg     不对null数据进行处理，求平均值时null数据不计入总数

--count      当处理所有数据时，即*返回总行数，即使没有数据；当处理某个字段时，只考虑非null的数据

create table TBL

(

    id intidentity(1,1) primary key,

    [name] nvarchar(10),

    score int

)

go

insert into TBL([name],score) values('张三',100);

insert into TBL([name]) values('小张');

insert into TBL([name],score) values('李四',90);

insert into TBL([name]) values('小曼');

insert into TBL([name],score) values('王五',70);

 

select * from TBL;

select SUM(score)/5 as 除以,SUM(score)/3 as 除以,AVG(Score) 平均值 from TBL;

select COUNT(*),COUNT(Score),COUNT(1) from TBL;

 

--====条件查询===

--查询没有及格的学生的学号

--查询年龄在-30岁之间的男学生

select * from MyStudent;

select * from MyStudent where FMath<90 and FEnglish<90 or FMath<90 or FEnglish<90 order by FMath,FEnglish;

select * from MyStudent where FGender='男' and  FAge>=20 and FAge<=30;

 

--betweenand   （包括边界）

--查询年龄在-30岁之间的男学生

--查询math成绩在-90分之间的所有学生

select * from MyStudent where FAge between 20 and 30 and FGender='男';

select * from MyStudent where FMath between 80 and 90 order by FMath;

 

--查找id号为，，...的学生

select * from MyStudent where Fid in(1,11,111,2,22,222);

 

--模糊查询

--查找氖姓杜的学生

-- % 0个或多个  _ 一个

select * from MyStudent where FName like '杜%';

select * from MyStudent where FName like '_志_';

select * from MyStudent where FName not like '[赵,别][志][^强]';

select * from MyStudent where FName like '_[^志][^强]_';

select * from MyStudent where FName like '%强%' and FName not like '%志%';

 

--==null值处理==

select * from TBL;

--查询某一个字段数据为null的情况

select * from TBL where [name] is not null and score is null;

--为null数据默认添加一个指定的数据

select [name],ISNULL(score,0) from TBL;

--空字符串len()求长度

select [name],LEN([name]),Score from TBL where id=4;

 

--==数据排序==

select [name],LEN([name]) as [len],DATALENGTH([name]),Score from TBL;

select * from MyStudent order by FAge desc,LEN(FName);

 

--==数据分组==

select COUNT(*) from MyStudent where FGender='女';

--分组

select FGender,COUNT(FGender) from MyStudent group by FGender;

--只要有分组,那么数据查询中的字段必须包含在分组字段中,同时可以包含聚合函数.

 

--男女分组后求平均分和总分

select FGender as 性别,COUNT(FGender) as 总人数, AVG(FMath) as 数学平均分,AVG(FEnglish) 英语平均分 from MyStudent groupby FGender

 

--按照英语成绩进行分组,并统计平均分大于的人数

select

    FEnglish,COUNT(FEnglish) as 人数,AVG(FEnglish)

from MyStudent

group by FEnglish

having AVG(FEnglish)>80

--查询前的筛选

--  where

--查询后的筛选

--  having

 

--======练习===========

use MyBook

go

-- 检索

select * from T_Book where LEN(FProperty)=0;

--加上条件，分组条件

--1、按照属性分组

select FProperty from T_Book group by FProperty;

--分组后的数据，只能检索分组字段和聚合函数

select FProperty,COUNT(FProperty) from T_Book group by FProperty;

 

--2、按出版社分组

select FPublic,COUNT(FPublic) from T_Book group by FPublic;

 

--常常处理检索时的筛选

--1、将教材筛选出来，并执照出版社分组，统计各出版社书的总数

--先筛选、再检索

select FPublic,COUNT(FPublic) as 总数 from T_Book whereFProperty='教材' group by FPublic;

 

--2、在的基础上，统计出版社出版书籍大于本的出版社

--现在是先筛选还是先检索？

--这个是先检索，在结果中筛选

select

    FPublic,

    COUNT(FPublic)    --得到统计数据

from T_Book

where

     FProperty='教材'        --一开始将数据提取出来

group by

    FPublic             --按照出版社分组

having

    COUNT(FPublic)>=3

 

--========联合================

--1将教材大于本的出版社显示出来

--  筛选教材

--  统计总数，判断大于

--  结果是出版社

 

--2、将教辅的出版社输出出来，并显示其中出版数量

--  筛选教辅

--  显示出版社

--  统计数据

 

--3、汇总以上两个数据

--  SQL语句union SQL语句

select FPublic,COUNT(FPublic) from T_Book

where FProperty='教材' group by FPublic    having COUNT(FPublic)>=10

union

select FPublic,COUNT(FPublic) from T_Book

where FProperty='教辅' group by FPublic;

 

--联合多用于财务汇总

--查询学生的数学与英语成绩

use School

go

select Fname,Fmath from MyStudent

union

select '总成绩',SUM(Fmath) from MyStudent

union

select '平均成绩',avg(Fmath) from MyStudent

union

select '最高分',max(Fmath) from MyStudent

union

select '最低分',min(Fmath) from MyStudent;

--联合的时候必须保证列数相同

--联合的时候必须保证同列的类型相兼容

 

--关于联合的重复

select* into TBL1 from TBL;

select * from TBL

union             --联合会将重复数据过掉

select * from TBL1;

--处理union时一般保留重复数据

--会反复检查排除重复

select * from TBL

union all         --联合unionall保留重复数据

select * from TBL1;

 

--==练习==

--1要求一个表格中查询出学生的英语成绩最高分、最低分、平均分

select * from MyStudent;

select '最高分',MAX(FEnglish) from MyStudent

union

select '最低分',min(FEnglish) from MyStudent

union

select '平均分',avg(FEnglish) from MyStudent;

--2查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资

select tName,tSalary from teacher

union all

select '平均工资',avg(tSalary) from teacher

union all

select '最高工资',avg(tSalary) from teacher

 

--union实现一次加入多条数据

insert into Score(studentId,english,math)

select 1,80,100 union

select 2,60,80 union

select 3,50,59 union

select 4,66,89 union

select 5,59,100;

 

--==字符串函数==

select [name],LEN([name]),DATALENGTH([name]) from TBL;

--函数名（处理对象，相应参数）

select UPPER('abc'),LOWER('ABC')

 

--ltrim()  -- left trim

--rtrim()  -- right trim

select LEN('123456         '),LEN('         123456'),LTRIM('         123456'),DATALENGTH('123456         '),RTRIM('123456

         ');

--去两端

select LTRIM(RTRIM('         bb        '));

 

--替换

select REPLACE('a b c d e f g',' ','_');

 

--截取

select LEFT('abcdef',3); --abc

select RIGHT('abced',3); --ced

select SUBSTRING('abcdefg',1,3);   --abc

--SUBSTRING() 起始位置从开始

 

select UNICODE('abc');

select datalength(NCHAR(97)),DATALENGTH( CHAR(97));

 

---===时间函数==

create table BookOrder

(

    id int,

    [time] datetimedefault(getdate())

)

--1、一种给表一个默认约束

--2、insert的时候加上数据时间

select * from BookOrder;

insert into BookOrder(id) values(1);

insert into BookOrder values(2,GETDATE());

insert into BookOrder values(3,'2013-10-1');

 

--查询id=2的时间，并显示出年月日出来

select YEAR([time]),MONTH([time]),DAY([time]) from BookOrder where id=2;

 

--在年份上加一个dateAdd()

select DATEADD(yy,1,[time]) from BookOrder where id=2;

--判断两个日期的间隔

select datediff(yy,[time],[time]) from BookOrder where id=2;

--返回日期的特定部分

select DATEPART(ms,getdate());

 

--========类型转换函数在帮助文件的系统函数中

-- cast(数据as类型)

select CAST(1 as nvarchar(10))+CAST(10 as nvarchar(10));

select CAST('1' as int)+CAST('10' as int);

 

--convert(类型，数据)

select CONVERT(nvarchar(10),GETDATE(),102);

 

select CAST(year(getdate()) as nvarchar(10))+'年'+CAST(month(getdate()) as nvarchar(10))+'月'+CAST(day(getdate()) as nvarchar(10))+'日';

 

---====练习===

--创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。

--要求：输出所有数据中通话时间最长的条记录。orderby datediff

--输出所有数据中拨打长途号码（对方号码以开头）的总时长。like、sum

--输出本月通话总时长最多的前三个呼叫员的编号。

 

--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

CREATE TABLE [CallRecords]

(

    [Id] [int]NOT NULL identity(1,1),

    [CallerNumber] [nvarchar](50), --三位数字，呼叫中心员工编号（工号）

    [TelNum] [varchar](50),

    [StartDateTime] [datetime] NULL,

    [EndDateTime] [datetime] NULL  --结束时间要大于开始时间,默认当前时间

)

 

--主键约束

alter table [CallRecords]

add constraint PK_CallRecords primarykey(id)

 

--检查约束

alter table [CallRecords]

add constraint CK_CallRecords check(CallerNumber like '[0-9][0-9][0-9]')  

 

alter table [CallRecords]

add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime)

 

--默认约束

alter table [CallRecords]

add constraint DF_CallRecords default(getdate()) for EndDateTime

 

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80AS DateTime), CAST(0x00009DAF00A62E94 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0AS DateTime), CAST(0x00009DB000D68DC8 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60AS DateTime), CAST(0x00009DB000E92F50 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0AS DateTime), CAST(0x00009DB2015C4DA0 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70AS DateTime), CAST(0x00009DA4014E0308 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0AS DateTime), CAST(0x00009DB400DD5FE0 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40AS DateTime), CAST(0x00009DB200B9FC1C ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8AS DateTime), CAST(0x00009DB80141804C ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898AS DateTime), CAST(0x00009D9A00FE6118 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898AS DateTime), CAST(0x00009D9A00FE6118 ASDateTime));

 

 

 

--查询通话时间最长的条记录

select datediff(second,StartDateTime,EndDateTime) from CallRecords

 

select top 5 datediff(second,StartDateTime,EndDateTime),Id, CallerNumber, TelNum, StartDateTime, EndDateTime

from CallRecords order by datediff(second,StartDateTime,EndDateTime) desc

 

--查询长途的通话总时长

select sum(datediff(second,StartDateTime,EndDateTime)) from CallRecords

where TelNum like '0%'

 

--查询本月通话总时长最多的前三个呼叫员的编号

select top 3 [CallerNumber],sum(datediff(ss,[StartDateTime],[EndDateTime])) from CallRecords

--whereyear(StartDateTime) = year(getdate()) and month(StartDateTime)=month(getdate()) 

where datediff(month,[StartDateTime],'2010-07-1') = 0

group by [CallerNumber]

order by sum(datediff(ss, [StartDateTime],[EndDateTime])) desc

 

--查询本月拨打电话次数最多的前三个呼叫员的编号

select top 3 [CallerNumber],count(*)  from CallRecords

where datediff(month,[StartDateTae],'2010-07-1') = 0

group by [CallerNumber]

order by count(*) desc

 ---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------