目录
基础的MSSQL知识
Microsoft sql server management studio 软件快捷键
set statistics time on/off:在消息栏显示详细的执行时间
ctrl+e 执行
ctrl+r 隐藏消息栏
ctrl+l 计划任务,对sql语句进行分析(语句占用时间,开销)
distinct 去重
--R和S的交集
select * from R intersect select * from S
a) 插入新表
select *
into newtable
from table
b) 插入已经存在的表
insert into table
select * from table2 where...
1.数据类型差别
字符串类型 n 的区别:
有 n 表示 unicode 编码,每个字符占一个字节,有中文就选 n
没有n表示非 unicode 编码,英文或数字占一个字节,中文占两个字节
字符串类型 var 的区别:
有 var 表示:可变长度
没有 var 表示:不可变长度,如果长度不够,会在末尾补空格
devimal(5,2) 表示:5个数字,两个小数位 eg: 100=>100.00
2.约束
约束:实现数据的有效性检查
主键、唯一、默认、检查(需要写表达式生效)、外键
3.脚本操作(创建)
不区分大小写,字符串使用单引号,末尾不需要加分号
按照功能分类:
DDL:数据定义语言,用于进行各种数据库对象的创建
DML:数据管理语言,增删改查
DCL:数据控制语言,用于进行权限分配等
注释: -- 单行注释 /**/多行注释
3.1 创建数据库
create database 数据库名 on primary ( name='stuDB_data', --主数据文件的逻辑名称 filename='D:\stuDB_data.mdf', --主数据文件的物理名称 size=5mb, --主数据文件的初始大小 maxsize=100mb, --主数据文件增长的最大值 filegrowth=15% --主数据文件的增长率 ) log on ( name='stuDB_log', filename='D:\stuDB_log.ldf', size=2mb, filegrowth=1mb )
3.2创建表
--通过 select * from sysobjects where xtype='U' 可以查看所有存在的表
/*
主键:primary key
非空:not null
唯一:unique
默认:default()
检查:check()
外键:foreign key(列名) references 表名(列名)
*/
--创建表
create table ClassInfo
(
cId int not null primary key identity(1,1),
cTitle nvarchar(10)
)
--创建外键表
create table StudentInfo
(
sId int not null primary key identity(1,1),
sName nvarchar(10) not null,
sGender bit default(0),
sBirthday date,
sPhone char(11),
sEMail varchar(20),
cid int not null,
foreign key(cid) references ClassInfo(cId)
)
3.3插入数据
--md5加密(admin)密码:21232f297a57a5a743894a0e4a801fc3
use dbtest
--插入单列数据
insert UserInfo2(UserName)
values('刘先生')
--插入指定数据
insert into UserInfo2(UserName,UserPwd)
values('小笼包','21232f297a57a5a743894a0e4a801fc3')
--一次性写多个数据
insert into classInfo2
values('青龙'),('白虎'),('朱雀'),('玄武')
3.4修改数据
--修改数据
--将 userinfo2 表中所有密码都修改为 admin
update userinfo2 set UserPwd = 'admin'
select * from userinfo2
--将 userinfo2 表中id大于1的所有密码都修改为加密的字符串
update userinfo2 set UserPwd = '21232f297a57a5a743894a0e4a801fc3' where userid>1
3.5删除数据
--清空表
truncate table classinfo2
--删除数据
delete from userinfo2 where userid = 2;
--删除表
drop table StudentInfo
4.脚本操作(查询)
语法
select… ⑤ 投影 from… ① table→内存 where… ② 选取元组 group… ③ 分组 having… ④ 选择分组 [{union|…} ⑥ 查徇结果的集 合运算 select… ] ①~⑤ order by… ⑦ 排序输
4.1限制查询
--全查前5条
select top 5 * from student
--前20%的数据
select top 20 percent * from student
4.2查询常量列
--查询常量列
select s.*,'邯郸翱翔软件学院' as 学校 from Student s where Address is null or Address = ''
4.3查询(升降序)
--查询排序(按照出生日期降序,学号升序)
select * from student order by s_birthday desc,sno asc
4.4模糊查询
通配符:
-
一类字符,代替一个或多个真正的字符,与 like 关键字仪器使用
通配符 | 解释 | 示例 | 符合条件的值 |
---|---|---|---|
_ | 一个字符 | A LIKE 'C_' | CS、Cd等 |
% | 任意长度的字符串 | B LIKE 'CO%' | CONST、COKE等 |
[] | 括号中所指定范围内的一个字符 | C LIKE '9W0[1-2]' | 9W01或9W02 |
[^] | 不在括号中所指定范围内的一个字符 | D LIKE '9W0[/^1-]'(没有/) | 9W03或0W07 |
--模糊+排序(所有性王(开头)的并且按照学号排序)
select * from student where sname like '王%' order by sno--形式1
select * from student where sname like '王_' order by sno--形式2
select * from student where sname like '%王' order by sno --(王字结尾的)
select * from student where sname like '%王%' order by sno --(有王字的)
--is null模糊查询(没有写邮箱的人)
select * from student where email is null or Email =''
--between范围查询(成绩表数学成绩60-80的人)
select * from 成绩表 where 数学 between 60 and 80
--in范围查询(在这两个班级的学生)
select * from student where class in ('金融16','计算16')
--查询某出生日期之后的学生信息
select * from student where birthday between '2000-1-1' and GETDATE() order by birthday
4.5子查询
-
子查询是一个嵌套在Select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询
-
子查询一般都是作为查询条件出现的
--查询出生日期小于李斌的人并且按照出生日期升序排列
select * from student where birthday > (select birthday from student where sname='李斌') order by birthday
--查询没有参加过语文考试的学生信息
select * from student where sno not in(select sno from score where 语文编号=1)
--查询人数大于3人的年级信息
select * from Grade g where (select count(*) from Student where Gradeid = g.Gradeid) > 3
--分页查询学生信息(一页五条数据)
--查询第一页
select top 5 * from student
--查询第二页
select top 5 * from student
where Student not in(select top 5 StudentNo from Student)
--查询第三页
select top 5 * from student
where Student not in(select top 10 StudentNo from Student)
5.Sql Server函数
5.1字符串函数
函数名 | 描述 | 示例 |
---|---|---|
charindx() | 寻找一个指定的字符串在另一个字符串中的起始位置 | select charindex('JBNS','My Jbnx Course',1) 返回:4 |
len() | 返回传递给它的字符串长度 | select len('SQL Server课程') 返回:13 |
upper() | 把传递给它的字符串转换为大写 | select upper('sql server课程') 返回:SQL SERVER课程 |
ltrim() | 清楚字符左边的空格 | select ltrim(' 刘文鑫 ') 返回:刘文鑫 (后面的空格保留) |
rtrim() | 清楚字符右边的空格 | select rtrim(' 刘文鑫 ') 返回: 刘文鑫(前面的空格保留) |
right() | 从字符串右边返回指定数目的字符 | select right('买卖提.吐尔松',3) 返回:吐尔松 |
replace() | 替换一个字符串中的字符 | select replace('莫乐可切.杨可','可','兰') 返回:莫乐兰切.杨兰 |
stuff() | 在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串 | select stuff('ABCDEFG',2,3,'我的音乐') 返回:A我的音乐EFG |
substring() | 截取某个字符串的一段内容 | select substring('abcdefg',2,5) 返回:bcdef |
--将所有姓张的学生,改成刘
update student set studentName=REPLACE(studentName,'张','刘')
--为没有邮箱的用户随机填充一个邮箱地址
update student set email=convert(varchar(50),right(RAND(),6))+'qq.com' where email=''
5.2日期函数
函数名 | 描述 | 示例 |
---|---|---|
getdate() | 获取当前的系统事件 | select getdate() 返回:今天的日期 |
dateadd() | 将指定的数值添加到指定的日期部分后的日期 | select dateadd(mm,4,'01/01/2009') 返回:以当前的日期格式返回05/01/2009 |
datediff() | 两个日期之间的指定日期部分的间隔 | select datediff(mm,'01/01/2009','05/01/2009') 返回:4 |
datename() | 日期中指定日期部分的字符串形式 | select datename(dw,'01/01/2000') 返回:Saturday或星期六 |
datepart() | 日期中指定日期部分的整数形式 | select datepapt(day,'01/15/2000') 返回:15 |
--查询年龄超过20周岁的学生新信息
select * from Student
where DATEDIFF(YEAR,birthday,getDate()) > 20
--查询1月份过生日的学生信息
select * from student
where DATENAME(MONTH,birthday) = 1
--查询今天过生日的学生
select sname,depart from student
where datepart(day,birthday) = datepart(day,GETDATE())
or datename(month,birthday) = datename(month,GETDATE())
5.3数学函数
函数名 | 描述 | 示例 |
---|---|---|
rand() | 返回0到1之间的随机float值 | select rand() 返回:0.79288062146374 |
abs() | 取数值表达式的绝对值 | select abs(-43) 返回:43 |
ceiling() | 取大于或等于指定数值、表达式的最小整数 | select ceiling(43.5) 返回:44 |
floor() | 取小于或等于指定表达式的最大整数 | select floor(43.5) 返回:43 |
power() | 取数值表达式的幂值 | select power(5,2) 返回:25 |
round() | 将数值表达式四舍五入为指定精度 | select round(43.543,1) 返回:43.500 |
sign() | 对于整数返回+1,对于负数返回-1,对于0则返回0 | select sign(-43) 返回:-1 |
sqrt() | 取浮点表达式的平方根 | select sqrt(9) 返回:3 |
--产生一个四位随机数字
select right(rand(),4)
5.4系统函数
函数名 | 描述 | 示例 |
---|---|---|
convert() | 用来转变数据类型 | select convert(varchar(5),12345) 返回:字符串12345 |
current_user() | 返回当前用户的名字 | select current_user 返回:你登录的用户名 |
datalength() | 返回用于指定表达式的字节数 | select datalength('中国A联盟') 返回:5 |
host_name() | 返回当前用户所登录的计算机名字 | select host_name() 返回:你所登录的计算机的名字 |
system_user() | 返回当前所登录的用户名称 | select system_user 返回:你当前所登录的用户名 |
user_name() | 从给定的用户ID返回用户名 | select user_name(1) 返回:从任意数据库中返回"dbo" |
5.5聚合函数和分组查询
-
对一组值进行计算,并返回计算后的值,具有统计数据的作用,例如:最大值,平均数,求和等
-
sum(),avg(),count(),max(),min()
--统计学生的总数
select count(*) from Student
--分组查询(统计每个年级的人数)
select count(*) as 人数,GradeId
from student
group by GradeId
--统计男女学生的人数
select count(*) as 人数,Sex
from Student
group by Sex
6.表连接
-
内连接(inner join):两张表共有的数据才会输出
/*
内连接语法1
select ......
from 表1 inner join 表2
on ......
内连接语法2
select ......
from 表1,表2
where ......
*/
--示例(写法1)
select s.sname,sc.sno,sc.cno,s.age,s.gender,s.depart
from student s inner join score sc
on s.sno = sc.sno
--示例(写法2)
select Students.sname,Score.CourseId,Score.Score
from Students,Score
where Students.Scode = Score.StudentId
-
外连接:以某张表为基准,数据都会显示,如果没有对应的数据,显示为null
select 图书编号,图书名称,出版社名称
from 图书表
right join 出版社表
on 图书表.出版社编号 = 出版社表.出版社编号
左连接
select * from A left join B
on a.id=b.id