学习数据库Sql Server（1）
一.数据库字符串的特性：（1）.---char 可以存储字符串，如果存储的字符串走出了指定的长度就报错，如果少于就会：char类型长度一旦确定就不会再更改
select LEN(char) from ChatTest--LEN得到存储的字符的个数，与中英文无关
select DATALENGTH(char) from ChatTest--DATALENGTH得到占据的字节长度
（2）.--varchar 可以存储字符串，如果存储的字符串走出了指定的长度就报错，如果少于就会：varchar类型长度可以自动收缩
select LEN(varchar) from ChatTest--LEN得到存储的字符的个数，与中英文无关
select DATALENGTH(varchar) from ChatTest--DATALENGTH得到占据的字节长度
(3).---当有中文的时候才需要考虑判断编码----也就意味着如果值中可能含有中文，那么就应该使用n
--nchar 可以存储字符串，如果存储的字符串走出了指定的长度就报错，如果少于就会：ncharr类型长度一旦确定就不会再更改n---unicode:不管什么字符都需要占据两个字节
select LEN(nchar) from ChatTest--LEN得到存储的字符的个数，与中英文无关
select DATALENGTH(nchar) from ChatTest--DATALENGTH得到占据的字节长度
(4).--nvarchar 可以存储字符串，如果存储的字符串走出了指定的长度就报错，如果少于就会：nvarchar类型长度可以自动收缩n---unicode:不管什么字符都需要占据两个字节
select LEN(nvarchar) from ChatTest--LEN得到存储的字符的个数，与中英文无关
select DATALENGTH(nvarchar) from ChatTest--DATALENGTH得到占据的字节长度
二.创建数据库
use master --切换当前使用的数据库
if exists( select * from sysdatabases where name='MyTest')
drop database MyTest --删除数据库
go
create database MyTest
on primary
(
name='MyTest_data', --逻辑名称，数据文件一般会添加data做为后缀
size=3mb,--初始大小，08版本最小是3MB
fileGrowth=5%, --文件增长，每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_data.mdf', --文件的全路径，需要添加扩展名
maxSize=100mb
),
(
name='MyTest_data1', --逻辑名称，数据文件一般会添加data做为后缀
size=3mb,--初始大小，08版本最小是3MB
fileGrowth=5%, --文件增长，每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_data1.ndf', --文件的全路径，需要添加扩展名
maxSize=100mb
)
log on
(
name='MyTest_log', --逻辑名称，数据文件一般会添加data做为后缀
size=1mb,--初始大小，08版本最小是3MB
fileGrowth=5%, --文件增长，每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_log.ldf', --文件的全路径，需要添加扩展名
maxSize=100mb
),
(
name='MyTest_log1', --逻辑名称，数据文件一般会添加data做为后缀
size=1mb,--初始大小，08版本最小是3MB
fileGrowth=5%, --文件增长，每次递增上一次大小的5%
fileName='D:\Classes\九期 .net加强+sql server增加\2013-10-15 sql server(1)\案例\MyTest_log1.ldf', --文件的全路径，需要添加扩展名
maxSize=100mb
)
三.数据库创建表:
use MSJDevice
if exists (select * from sysobjects where name='')
drop table
create table
(
(50) NOT NULL,
(50) NULL,
(30) NULL,
NULL,
(22, 2) NULL,
(22, 2) NULL,
(22, 2) NULL,
(22, 2) NULL,
(22, 2) NULL,
(22, 2) NULL,
NULL,
(22, 4) NULL,
(200) NULL,
(20) NULL,
(20) NULL,
(20) NULL,
NULL,
(20) NULL,
(20) NULL,
(20) NULL,
NULL,
)
四.插入数据（insert）
----语法: insert into 表名 (列名列表) values( 值列表) 列名列表相当于形参，值列表相当于实参，三个对应（类型对应，数量对应，顺序对应）在这里一样存在
use MyTest
--1.插入所有列的值
insertteacher (name,gender,age,salary,birthday) values('aa',1,20,3000,'1990-9-9')
--如果没有指定列名那么就会默认需要为所有的列添加值:列名或所提供值的数目与表定义不匹配。
insertteacher values('aa',1,20,3000)
--2.标识列不能人为添加值 "当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'Teacher' 中的标识列插入显式值。"
insertteacher (id,name,gender,age,salary,birthday) values(100,'aa',1,20,3000,'1990-9-9')
--3. 插入的值数量需要和指定的列的数量一致：INSERT 语句中列的数目小于 VALUES 子句中指定的值的数目。VALUES 子句中值的数目必须与 INSERT 语句中指定的列的数目匹配。
--4.如果列的属性是非空，那么就必须插入一个值：不能将值 NULL 插入列 'Birthday'，表 'MyTest.dbo.Teacher'；列不允许有 Null 值。INSERT 失败。
insertteacher (name,gender,age,salary) values('aa',1,20,3000)
--5.如果列的属性标记为可以为null，那么就可以不插入值:如果不想为可以为null的列添加值：要么不写，要么写null
insertteacher (name,gender,age,salary,Birthday) values('aa',1,20,null,'1900-8-8')
--6.如果列有默认值:要么不写这一列,那么就会添加默认值,要么可以在值列表中写default
insertteacher (name,gender,age,salary,Birthday) values('aa',1,-100,null,'1900-8-8')
--7.插入的值需要满足表的约束 INSERT 语句与 CHECK 约束"CK_Age"冲突。该冲突发生于数据库"MyTest"，表"dbo.Teacher", column 'Age'。
insertteacher (name,gender,age,salary,Birthday) values('aa',1,-100,null,'1900-8-8')
--8.当插入的值里面有日期的时候：如果没有添加''，那么得到的值是系统计算出来的默认值
insertteacher (name,gender,age,salary,Birthday) values('aa',1,20,null,'1990-8-8')
--9.在sql语言中，类型其实不那么严格,任何类型都能添加'',没有错误，除了null,default之外，但是如果是字符串没有添加''就会报错
--因为系统会将这个值转换为目标类型，如果可以转换则进行赋值，如果不可以转换就报错
insertteacher (name,gender,age,salary,Birthday) values('aa','1','20',null,'1990-8-8')
五.删除数据（delete和truncate）
---删除操作也是不可逆的，所以一定需要询问用户是否真的需要删除，同时往往并非真正的删除，而只是做一个删除过后的标记
--能够做为条件的一般都是主键或者是标识列或者是唯一键值
---语法：delete 【from】 表 where 条件
deletefrom Teacher where Id=14---delete 后面不能接字段，是因为删除是一行一行删除的
delete from Teacher where Gender=1 and Salary>5000
---truncate:一次性删除表中的所有记录
--语法：truncate table 表名
truncate table teacher
---delete 是一条一条删除，它每次删除都会将操作写入到日志文件中。 而truncate不会
---delete可以写条件，但是truncate不会
--delete是一条一条删除，效率低下
---delete删除后的表的标识列不会重新从1开始计算，而truncate会重置标识列到初始值
delete from Teacher
六.更新操作
----更新操作 一定要注意添加条件,多字段之间用,多条件使用not and or
---语法 :update 表 set 字段=新值, 字段2=新值2。。。 where 条件
update Teacher set Age=30 where Id='14'
--多字段修改
updateTeacher set Name='张三' ,Age=18 ,Salary='6000' where Id=17
--修改性别是男，同时工资在3500以下，为其加1000块工资
update Teacher set Salary=Salary+1000 where Gender=1 and Salary >=<60 and StudentResult=5 and Sex='男' order bycnt desc
--查询班级人数超过三个人的班级
select ClassId,COUNT(0) from Student group by ClassId having COUNT(*) in (3,4)
--查询每个参加考试的学员的平均分
select StudentNo,AVG(StudentResult) from Result where StudentResult is not null group by StudentNo order by AVG(StudentResult)
十三.模糊查询
---模糊查询：是对字符串类型的值而言---
--=是完全的严格的匹配
select * from Student where StudentName ='张莉'
--- %：代码任意个任意字符
---_代表单个字符
---[]:代表单个字符
select * from Student where StudentName like'张__' --like：喜欢 像。。。。一样
--总结：1.模糊查询必须使用关键字：like 2.不能使用=，否则就是完全的匹配
select * from Student where StudentNo not like '1'--如果有多个范围也可以一起来指定啰
select * from Student where StudentName like '张[^丽12345]'
---得到没有填写电子邮箱的学员信息
select * from Student where Email='null'
select * from Student where Email is null
---isnull 函数：如果查询的字段值是null值，那么就可以使用指定的值来替代，注意指定的值的类型需要和原始字段的类型一致
select StudentName,ISNULL(Email,'她没有写，打电话吧') from Student where ClassId=4
select StudentNo,ISNULL(StudentResult,'0') from Result where SubjectId=2
----排序：可以选择升充或者降序排序 如果有多个关键字，那么就先按第一个关键字排序，相同的记录再按第二个关键字排序，排序必须写在最后：因为排序是对结果集再进行数据的重新显示 ,排序默认就是升序排序 asc--升序desc降序
select * from Resultorder by StudentResult Desc,StudentNo asc
--1.查询6期 班所有姓陈的学员
select * from Student where ClassId=4 and StudentName like '张%'
--2.查询所有科目中包含 c 字符的科目信息
select * from Subject where SubjectName like '%c%'
--3.查询office最逝一次考试时间
select top 1 ExamDate from Result where SubjectId=1 order by ExamDate desc--升序
select SubjectId from Subject where SubjectName='office'
select MAX(ExamDate) from Result where SubjectId=(select SubjectId from Subject where SubjectName='office')
十四.Union
----联合查询：将多个结果集组成一个结果集：每一个select操作都会生成一个新的结果集(除了select into from )
---列数，与列的类型需要对应一致，如果类型不一致，也需字段的值可以相互的隐式转换,最终的列名由第一个结果集来决定
--不能在最后一个结果集以外的任何结果集查询语句中添加order by 排序
select ''+cast(StudentNo as CHAR(3)) as 学号,StudentName 姓名 from Student
union--去除重复,效率不高，因为需要做是否重复的判断操作
select cast(ClassId as CHAR(2)) as 班级ID,ClassName as 班级名称 from Grade ---order by ClassName 为什么报错：因为结果集由第一个结果集的列来决定，所以只有学号和姓名，同时order by是得到结果集之后再对结果集的数据做重排的，所以它不能使用第一个结果集以外的列
select * from Student where ClassId=3
union all
select * from Student where ClassId=3
select MAX(StudentResult),MIN(StudentResult),AVG(StudentResult)from Result --可以在同一行显示
select '最高分',MAX(StudentResult) from Result --分多行显示，但是在是同一个结果集中
union
select '最低分',min(StudentResult) from Result
union
select '平均分',avg(StudentResult) from Result
---查询每个学员的学号和成绩同时在最后一行显示最高分和最低分
select ''+cast (StudentNo as CHAR(4)),cast (StudentResult as CHAR(3)) from Result--空格是最小的，在排序的时候如果是升序排序，那么就会排在最前面
union
select ' 最高分','最低分'
union
select cast (MAX(StudentResult) as CHAR(3)),cast (MIN(StudentResult) as CHAR(3)) from Result
----一次插入多条数据
--1.select 字段列表 into新的目标表中from源表 目标表是系统自动创建的，不能先存在 ，如果已经存在 就会给你报错
--说明：通过这种方式可以创建一个表结构，同时复制表数据，但是需要注意的是：表结构仅仅有标识列属性，其它的，如（主键，唯一键，check约束，关系。。）都没复制，需要自己再重新创建
select LoginPwd,StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDelinto newstudent from Student
--select LoginPwd,StudentName,Sex,ClassId,Phone,Address,BornDate,Email,isDelinto student from newStudent
--将数据复制到一个已经存在 的表结构中。注意类型需要对应，列的数量也需要对应，还需要遵守目标表的约束及相应的规范
--insert into 目标表中 select字段列表 from 源表---目标表需要先存在 ，且与数据源(字段数量，字段的类型)能够对应
insert into Student select * from newstudent where email is not null
truncate table admin
---没有数据源也可以一次性插入多条记录 ---从来不用的
insert into Admin
select 'a','a' union all---union all不会去除重复的记录union会去除重复项
select 'b','b' union
select 'a','a' union
select 'a','a' union
select 'a','a' union all
select 'a','a'--最后一句不需要添加union
十五.类型转换
--CAST ( expression AS data_type)将指定的表达式expression强制转换为data_type所规定的类型，如果可以转换就转换，如果不可以转换就报错
select '我的成绩是：'+cast(90 as CHAR(2))
--CONVERT ( data_type, expression,) 第一个参数是目标类型，第二个参数是需要转换的表达式，第三个参数是：日期类型
select '我的成绩是：'+CONVERT(char(2),90)
十六.日期类型
select GETDATE()
--DATEADD 可以来添加一个指定的值DATEADD (datepart , number, date ):第一个参数指定你需要中的值的单位(年，月日，时分秒。。。)，第二个参数是你需要添加的具体的值，第三个参数是对那个日期值来做运算
select DATEADD(MM,-1,GETDATE())
--查询年龄超过18岁的学员信息
select * from Student where BornDate=25
--DATENAME可以得到日期的字符串表现形式
select DATENAME(w,getdate())
--DATEPART 得到具体的日期部分2013-10-18
select cast(DATEPART(yyyy,getdate()) as CHAR(4))+'-'+cast(DATEPART(mm,getdate()) as CHAR(2))+'-'+cast(DATEPART(dd,getdate()) as char(2))
十七.数字函数
--RAND 可以生成随机数 永远只能生成一个0~1之间的随机数，理论上包含0但是不包含1
select RAND()
--ABS取绝对值
select ABS(-100)
--CEILING可以获取比当前指定的数大的所有整数中的最小值
select CEILING(0.01)
--floor:地板：可以得到比当前指定的数小的所有整数中的最大值
select FLOOR(99.9)
--POWER 幂
select POWER(4,2)
--SQRT 求 开平方--根
select SQRT(10)
---ROUND 四舍五入
select ROUND(5.557245678,2)--多余的位数会补0
--SIGN 如果是正值，就是1，如果是负值就是-1.如果是0就是0
select SIGN(0)
select CONVERT(char(20),GETDATE(),1000) --style是对日期或者日间格式的值而言的。
十八.case ..and..
--case---end相当于switch...case
--1.做等值判断 ，，它会生成一个列 2.then后面的数据类型需要对应(互相转换)3.如果判断范围,那么when后面的表达式不一定判断同一列的值4.不能捕获到null值
select StudentName,
case ClassId--如果在case后面添加了表达式，那么这个case就只能进行等值判断
when 1 then '一期班'
when 2 then '2期班'
when 3 then '3期班'
when 4 then '4期班'
when 5 then '5期班'
when 6 then '6期班'
--when null then 'dsafs'
else '不知道'
end as 班级名称
from Student
---2.也可以判断范围
select StudentName,sex,
case --如果case没有接表达式，那么也可以进行范围的判断，相当于if
when BornDate>'2000-1-1' then '小姑娘'
when BornDate>'1990-1-1' and BornDate'1980-1-1' and BornDate=90 then 'A'
when StudentResult between 80 and 89 then 'B'
when StudentResult between 70 and 789 then 'C'
when StudentResult between 60 and 69 then 'D'
when StudentResult SubjectId then StudentNo
else SubjectId
end,
case
when SubjectId>StudentResult then SubjectId
else StudentResult
end
from Result
select eName,
case
when eSalary>=10000 then '王牌'
when eSalary> 8000 and eSalary=20
--案例3：查询学生姓名、年龄、班级及成绩
select Student.StudentName,DATEDIFF(yyyy,borndate,getdate()),Grade.ClassName,Result.StudentResult
from Student
inner join Grade on Student.ClassId=Grade.ClassId
inner join Result on Student.StudentNo=Result.StudentNo
--案例4：查询所有学生(参加及未参加考试的都算)及成绩
select Student.StudentNo,Student.StudentName,Result.StudentResult
from Student leftjoin Result on Student.StudentNo=Result.StudentNo
--案例5：请查询出所有没有参加考试（在成绩表中不存在的学生）
select Student.StudentNo,Student.StudentName,Result.StudentResult
from Student leftjoin Result on Student.StudentNo=Result.StudentNo
where Result.StudentResult is null and Result.StudentNo is null
---左连接和右连接
use MyTest
--查询参加了考试的学员信息
select * from Student inner join StuScore on Student.StuNo=StuScore.StuId
--查询学员考试信息,参加了和没有参加的都要显示
select * from Student right join StuScore on Student.StuNo=StuScore.StuId
---交叉连接
select * from Student cross join grade
use MySchoolMoreData
--查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格
select Student.StudentName,DATEDIFF(YYYY,borndate,GETDATE()),
case
when StudentResult>=60 then cast(StudentResult AS CHAR(3))
when StudentResult
页:
[1]