SQL server 练习 sql server入门新手教程

目录

基础的MSSQL知识

1.数据类型差别

2.约束

3.脚本操作(创建)

3.1 创建数据库

3.2创建表

3.3插入数据

3.4修改数据

3.5删除数据

4.脚本操作(查询)

4.1限制查询

4.2查询常量列

4.3查询(升降序)

4.4模糊查询

4.5子查询

5.Sql Server函数

5.1字符串函数

5.2日期函数

5.3数学函数

5.4系统函数

5.5聚合函数和分组查询

6.表连接

---

基础的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