返回非空值的计数:Count(*) 用于数字或字符类型的列
例:select Count(*) as 及格人数 from Student where Score>=60;——》查询返回的是:分数60分以上的所有同学的人数
SQL Server 基础
一.基础
1.SQL Server 使用的是SQL(Structured Query Language,结构化的查询语言)语言,他只是数据库能识别的指令,T-SQL(Transact-SQL )是标准的SQL加强版,由:数据库的定义语言(DDL),数据库的操作语言和数据库的控制语言组成;
注:SQL中的下标从1开始;SQL中不分大小写
2.主数据库文件:*.mdf
次数据库文件:*.ndf 可多个,如:可分别在三个驱动盘上建立3个次数据库文件(1.ndf,2.ndf和3.ndf)
日志文件 :*.ldf
3.注释:
(1).单行注释:--
(2).多行注释:
4.快捷键:
F5 :执行语句
Ctrl F5 :检查语法
5.登陆及权限的管理:
(1).添加登陆账户:
1).添加Windows账户:exec sp_grantlogin '账户名\密码'
例:exec sp_grantlogin 'wzy\123123'
2).添加SQL账户:exec sp_addlogin '账户名',',密码'
例:exec sp_addlogin 'wzy','12313'
删除登陆账户:exec sp_droplogin '账户名'
例:exec sp_droplogin 'wzy'
(2).添加数据库用户:
exec sp_grantdbaccess '数据库账户','密码'
例:exec sp_grantdbaccess 'wzydb','123'
删除数据库用户:
exec sp_revokedbaccess '数据库账户'
例:exec sp_revokedbaccess 'wzydb'
(3).管理数据库用户的权限:
为数据库的用户分配权限:
grant 权限,权限 on 表名 to 数据库账户
例:grant insert,delete,check on Studnet to wzy
——赋予wzy数据库用户对Studnet表的添加,删除,查找的权限
删除数据库的用户的权限:
revoke 权限,权限 on 表名 from 数据库账户
例:revoke insert,delete on Student from wzy
——删除wzy数据库用户对Studnet表的添加和删除的权限
二.
1.SQL中的表达式:
= 等于;>大于;< 小于;>= 大于或等于;<=小于或等于;<> 不等于;! 非
2.通配符:
‘_’ :表示一个字符 ;例:A Like ‘C_’;
% :表示任意长度的字符串;例:A Like ‘C_%’;
[ ] :括号中所指定范围内的任意一个字符;例:A Like ‘C_WO[1-2]’;
[^ ] :不在括号中所指定范围内的任意一个字符;例:A Like ‘C_WO[^1-2]’;
3.逻辑表达式:
AND,OR,NOT (先是Not,再是AND,最后是Ro)
例:Not(付款方式='信用卡')OR(信用卡 IN ('牡丹卡','龙卡','阳光卡'))????????????????
注:IN是用来限制范围的;
三.函数
1.字符串函数:
1).字符串的长度:len
例:select len(name) from Student where id=1;——》返回id为1的学生姓名的字符串长度:
2).大小写转换:lower,upper
例:select lower(name) from Student where id=1;——》返回id为1的学生姓名的字符串的小写
例:select upper(name) from Student where id=1;——》返回id为1的学生姓名的字符串的大写
3).寻找一个字符串在另一个字符串中的起始下标:charindex
例: select charindex('Accp','My Accp',1);——》4
4).清楚左边的空格:ltrim
例:select ltrim(' 哈哈');——》查询返回的是:哈哈(前面没有空格)
5).清楚右边的空格:rtrim
例:select ltrim('哈哈 ');——》查询返回的是:哈哈(后面没有空格)
6).从字符串右边数返回指定数目的字符:right
例:select right('哈市咔哒收视费',3) ;——》查询返回的是:收视费
7).替换字符串中的字符:replace
例:select replace('师傅师傅是爽肤水爱仕达阿达','师','爽');——》查询返回的是:爽傅爽傅是爽肤水爱仕达阿达
8).在字符串中删除指定长度的字符串字符,用另一个字符串代替:stuff
例:select stuff('ABCDEFGHRJ',2,6,'我的地盘我做主');——》查询返回的是:A我的地盘我做主HRJ
注:返回字符串中从2开始的6个字符的被替换后的字符串;SQL中的下标从1开始;
2.日期函数:(yy:年;mm:月;dd:日 dw:星期(从1开始,表示周日);)
1).取得当前的系统的日期:getDate()
例:select getDate();——》查询返回的是:今天的日期:2009-03-25 15:39:24.870
2).推迟指定日期的:DateAdd()
例:select DateAdd(mm,4,'01/03/2009');——》查询返回的日期是:2009-05-03 00:00:00.000
3).两个日期之间指定日期部分的差值(前-后):DateDiff()
例:select DateDiff(mm,'01/03/2009','07/12/2008');——》查询返回的日期是:-6
4).返回日期中指定部分的字符串形式:DateName()
例:select DateName(dw,'01/01/2000');——》查询返回的日期是:星期六
5).返回指定日期部分的整数形式:DatePart()
例:select DatePart(day,'07/12/2008');——》查询返回的日期是:12
Select DatePart(dw,getDate());——》查询返回的日期是:6(今天是星期5)
3.数学函数:
1).取绝对值:Abs()
例:select Abs(-23);——》查询返回的是:23
2).大于或等于指定数值或表达式的最小整数:Ceiling()
例:select Ceiling(23.5);——》查询返回的是:24
3).小于或等于指定数值或表达式的最大整数:Floor()
例:select Floor(23.5);——》查询返回的是:23
4).取数值表达式的幂:Power()
例:select Power(2,4);——》查询返回的是:16
5).将数值表达式精确到指定的位数:Round()
例:select Round(23.5329323,3);——》查询返回的是:23.5330000
6)判断正负(正数的返回 1;负数的返回 1;0返回0):Sign()
例:select Sign(-234);——》查询返回的是:-1
7).取平方根:Sqrt()
例:select Sqrt(9);——》查询返回的是:3.0
4.系统函数:
1).转换数据类型:Convert
例:select Convert(Varchar(5),12345)——》查询返回的是:"12345";(把12345转换成字符串)
例:select Convert(int,'12345')——》查询返回的是:12345;(把字符串12345转换成整形数字)
三.插入
1.插入一条语句:
insert into 表名(列名)values(值列表)
例:Insert into Students(name,address,age)values('老李','北京',23);
注:1).(列名)和(值列表)要完全对应,实现实体(行)的完整性
2).如果允许为空或者有默认值时,且(列名)中没写该字段,(值列表)中对应的项均可以不填
3).如果允许为空或者有默认值时,且(列名)中写该字段,(值列表)中对应的项必填,(列名)中默认值对应(值列表)中用 default,(列名)中允许为的对应(值列表)中用null(表示表中该列对应的字段没有应用对象)或' '(表示表中该列对应的 字段类型的默认值)代替
4).要完全符合检查约束:(列名)中的字段有约束时,(值列表)中对应的项必须符合约束,否则添加失败
2.一次插入多条语句:
1).插入的新表必须预先存在:
例:insert into User (name,address,Email)
select name,address,Email from Student
注:User表必须预先存在,查询到的数据的个数,顺序,数据类型等,必须与插入的项保持一致
2).插入的新表预先不存在:
例:select Student.name,Studnet.Address,Student.Emial
into User
from Student
注:User表必须预先不存在,语句运行的过程中自己创建的
向新表插入数据的时候需要创建标示例:
例:select identity(int,1(标示的种子),1(增长量)as Student.Id,Student.name,Studnet.Address,Student.Emial
into User
from Student
四.修改
update 表名 set 列名='',列名='',... where 列名=''
例:update Student set name='老王',address='南京' where Student.Id=1;
update Scores set scores=scores 5 where scores<=90;
注:1).如果不用where 限制,整个表所有的数据都被更新
2).update 可以更新一条数据,也可以更新多条数据,还可以更新整张表
五.删除
(1).删除表中的数据
delete from 表名 where 列名=''
例; delete from Scores where scores<=60;
注:如果不用where 限制,整个表所有的数据都被删除
(2).删除表中的所有数据,表存在
truncate table 表名: 删除没有主外建约束的表
delete 表名: 可删除有主外建约束的表
(3).删除表中的所有数据,表也被删掉
drop table 表名: 完全删除表的所有
六.查询
1.简单的查询:
(1).查询表中的所有数据:select * from 表名;
例:select * from Students
(2).带条件的查询:select * from 表名 where 列名='';
例:select * from Students where name='老王';
(3).指定列名的查询:select 列名1,列名2,... from 表名 where 列名='';
例:select name,address from Students where id=1;
(4).查询空行:select 列名1,列名2,... from 表名 where 列名 is null; (或者is Not null)
例:select name,address from Students where Email is null; ——》查询Email为空的实体
(5).查询后显示标题的为汉字,查询后多出一列(常量列):
例:select 姓名=name,地址=address,学校名称='北大青鸟' from Students
或:select name as 姓名,address as 地址,'北大青鸟' as 学校名称 from Students
(6).查询显示限制行数:
例:select Top 5 姓名=name,地址=address,'北大青鸟' as 学校名称 from Students where Email is Not null;
或:select Top 20 percent 姓名=name,地址=address,'北大青鸟' as 学校名称 from Students where Email is Not null;
注:percent :表示百分之几;TOP :表示从上往下
Top 20 percent:所有查询的数据的前20%条
(7).合并列名的查询:
例:select FirstName '.' LastName as 姓名 from User
(8).排序查询结果:
1).使用order by语句;并不是where的条件
例:select StudentId as 学员编号,(Scores*0.9 9) as 学员成绩 from Student order by Scores
2).ASC为升序;DESC为降序;默认为ASC升序排列;
例:select StudentId as 学员编号,(Scores*0.9 9) as 学员成绩 from Student where (Scores*0.9 5)<60 order by Scores
注: order by Scores——》查出的结果按成绩的升序排序
3).也可以按多个字段进行排序显示查询结果,注意排序是按字段的先后顺序的:
例:select StudentId as 学员编号,Scores as 学员成绩 from Student where Scores>60 order by Scores,StudentId
注:order by Scores,StudentId——》查出的结果先按按成绩的升序排序,再按学员编号的升序排序
2.模糊查询:
(1).使用Like:只用于字符串,仅与Char和Varchar数据的类型联合使用
例:select * from Studnet where name Like '张%';——》查询返回的是:姓张的所有学生
例:select * from Card where id Like '%[^8]%[A,C]%';——》查询返回的是:第三位不是8的A卡或C卡
(2).使用Between...and...或Not Between...and...进行范围查询:
例:select * from Scores where Score Between 60 and 80; ——》查询返回的是:分数是60到80的成绩
例:select * from Student where birthday Between '1993-2-3' and '2009-3-4';——》查询返回的是:出生在那时间段你的学生
(3).使用in或not in进行范围查询:
例:select * from Student where address in ('北京','上海','天津'); ——》查询返回的是:住址是这三个地的所有学生
3.聚合函数查询:
(1).数字的汇总:Sum ,不能汇总字符,日期等其他函数
例:select Sum(Score) from Student where name like '张%'; ——》查询返回的是:姓张的所有同学的分数总和
注:查询只能返回一个数字,不能与返回多行的数据一起查询,如加上返回查询address,姓张的同学可能有与不同的地址(多行)
(2).查询数字的平均值:Avg ,不能汇总字符,日期等其他函数
例:select Avg(Score) from Student where name like '张%'; ——》查询返回的是:姓张的所有同学的分数平均值
例:select avg(Score) from Student where Score>=60;——》查询返回的是:分数60分以上的所有同学的平均值
注:查询只能返回一个数字,不能与返回多行的数据一起查询,如加上返回查询address,姓张的同学可能有与不同的地址(多行)
(3).查询最大值或最小值:Max,Min; 能用于数字,字符,日期等其他函数
例:select Age(Score) as 平均分,Max(Score) 最高分,Min(Score) as 最低分 from Student where Score>=60
——》查询返回的是:分数大于60分的同学的平均分,最高分,最低分
注:查询只能返回一个数字,不能与返回多行的数据一起查询,如加上返回查询address,姓张的同学可能有与不同的地址(多行)
(4).返回非空值的计数:Count(*) 用于数字或字符类型的列
例:select Count(*) as 及格人数 from Student where Score>=60;——》查询返回的是:分数60分以上的所有同学的人数
4.分组查询:
(1).使用Group By 进行分组查询:先分组在进行查询,并不是where的条件
例:select CourseId as 课程id ,Avg(Score) as 课程的平均成绩 from Score Group By CourseId
——》查询返回的是:按课程的id进行分组查询,返回每门课程的平均成绩
例:select StudnetId as 学生id,CourseId as 课程id,Avg(Score) as 课程的平均成绩 from Score
Group by CourseId,Studentid
——》查询返回的是:先按课程的id进行分组,再按学生的id进行分组,然后查询返回每个学生每门课程的平均成绩
(还是每个学生每门课程的成绩,因为select语句中只能出现为每个分组返回一个值得表达式)
注:分组查询中:select语句中只能出现:(1)被分组的列(2)为每个分组返回一个值得表达式(如聚合函数)
(2).使用Having进行分组筛选:
例:select * from StudnetId as 学生id,CourseId as 课程id,Avg(Score) as 课程的平均成绩 from Score
Group by CourseId,Studentid Having Count(Score)>1
——》查询返回的是:先按课程的id进行分组,再按学生的id进行分组,并且分组后出现1次以上的列
然后查询返回每个学生每门课程的平均成绩
(还是每个学生每门课程的成绩,因为select语句中只能出现为每个分组返回一个值得表达式)
注:查询语句中:筛选顺序是:where,Group By ,Having
where是从数据源中去掉不符合搜索条件的语句
Group By从剩下的符合条件的列中,进行分组,表达式(函数)为各个组进行计算统计
Having对分组进行筛选,去掉不符合组搜索条件的各组中的数据行
5.多表查询:
(1).内联查询:表之间存在主外键关系,表之间的地位平等
1).在where语句中制定查询条件:
例:select Studnet.name as 学生姓名,Course.name as 课程名称,Scores.Score as 课程成绩 from Studnet,Scores,Course
where Student.id = Scores.StudentId,Course.Id=Scores.CourseId
==》select S.name as 学生姓名,C.name as 课程名称,SC.Score as 课程成绩
from Studnet as S
inner Join Scores as SC on (S.id = SC.StudentId)
inner Join Course as C on (C.Id=SC.CourseId)
——》查询返回的是:查询学生表和成绩表,返回每个学生的每门课程的成绩
2).通过(inner)Join...on...语句中制定查询条件:
例:select student.name,Scores.CourseId,Scores.Score from Studnet,Scores
where Student.id = Scores.StudentId
==>select S.name as 学生姓名,C.CourseId as 课程id,C.Score as 课程成绩 from Studnet as S inner join Scores as C
on S.id = C.StudentId
(2)外联查询:表之间存在主外键关系,表之间的地位有主次之分
1).左外链接查询:包含左表中所有匹配的行,如果右表中有的项左表中没有对应的项,以空填充
例:select S.name as 学生姓名,C.CourseId as 课程id,C.Score as 课程成绩
from Studnet as S
left outer Join Scores as C
on S.id=C.StudentId
——》查询返回的是:以学员信息表为主表,成绩表为次表,查询符合条件的列中的某些学员没有对应的科目和成绩,以空填充
2).右外链接查询:包含右表中所有匹配的行,如果右表中有的项左表中没有对应的项,以空填充
例:select S.name as 学生姓名,C.CourseId as 课程id,C.Score as 课程成绩
from Studnet as S
right outer Join Scores as C
on S.id=C.StudentId
——》查询返回的是:以成绩表为主表,学员信息表为次表,查询符合条件的列中的某些成绩没有对应的学员和课程,以空填充
七.变量:
(1).声明变量:局部变量用'@',系统(全局)变量用'@@',不能赋值
DeClare @变量名 变量的类型
例:deClare @name varchar(10)
set @name='马文才'
select * from Student where name= @name
——查找马文才的信息
(2).为变量赋值:
1). set 专用的赋值常量,一次只能为一个变量赋值
例:deClare @name varchar(10)
set @name='马文才'
2).select 给从表中查询的结果赋值,可以给多个变量赋值,用逗号隔开
例:deClare @name varchar(10)
set @name='马文才'
deClare @seat int
select @seat=Seat from Student where StuName=@name ——》查找马文才的座位号并赋值给变量@seat
select * from Student where (Seat=@seat 1) or (Seat=@seat 1) ——》查找马文才的左右同桌
(3).最常用的全局变量:@@error , @@identity
1).@@error :判断最近一条增删改的语句是否正确;成功:0;不成功:非0
例:print '当前的错误号:' Convert(varchar(5),@@error)
——》输出错误号,大于0,表示上一条语句执行错误
2).@@identity:最新的标识列号
例:insert into Studnet (id,names)values(1,'wzy')
print '当前的错误号:' Convert(varchar(5),@@identity)
——》输出最后一条添加语句的id