【原创】总结一些SQL server知识（标准SQL语句）

[huayang]

使用T-sql创建数据库：
create  database   database_name
on  primary
(
name='',             逻辑文件名
filename='',         物理文件名
size=,                文件大小
maxsize=,             文件最大值
filegrowth=           文件增长量
)
log  on
(
name='',             逻辑文件名
filename='',         物理文件名
size=,                文件大小
maxsize=,             文件最大值
filegrowth=           文件增长量
)
collate  Chinese_PRC_CI_AS
管理数据库：  鼠标    键盘
1.打开数据库：use
2.查看数据库信息：  sp_helpdb
sp:system  procedure 系统存储过程
exec：   execute    执行
exec  sp_helpdb  database_name
3.修改数据库容量：alter  database   database_name
(1)add  file  添加数据文件----辅助数据文件.ndf
add  file
(
name=,
filename='',
size=,
maxsize=,
filegrowth=
  )
数据库名_data   数据库名_data2  数据库名_data3  数据库名_data4...
数据库名_log    数据库名_log2   数据库名_log3   数据库名_log4...   
(2)add  log  file   添加日志文件 
add log file
(
name=,
filename='',
size=,
maxsize=,
filegrowth=
  )
（3）修改文件大小   modify  file
modify  file
(name=,size=)
(4)删除数据库文件：
remove  file   逻辑文件名
4.设定修改数据库选项：   sp_dboption    2016
（1）查看数据库选项：
 exec   sp_dboption   'database_name'
（2）修改数据库选项：
exec  sp_dboption  'databasename','option_name',{true|false}
5.数据库更名：    sp_renamedb
------一般情况下，我们不对数据库更名
exec   sp_renamedb  'oldname','newname'
6.分离和附加数据库：
分离数据库：   sp_detach_db
exec  sp_detach_db  'database_name'
附加数据库：-----抓主数据文件  .mdf
create  database  database_name
on(filename='')
for attach
7.删除数据库：
(1)drop  database    database_name
(2)sp_dbremove
  exec  sp_dbremove  database_name
例  student
多文件分组创建数据库：10个文件，1个主数据，8个辅助数据，1个日志文件
文件分组：filegroup
primary:1个主数据文件，2个辅助数据文件
filegroup  StuGroup1  : 3个辅助数据文件
filegroup  StuGroup2  : 3个辅助数据文件
log:----1个日志文件
D:\SQLDATA\
数据的添加与表的查看：
一、向表中添加数据：
1.用鼠标操作：
 *  全部   所有
2.用SQL语句添加数据：
 insert  into   table_name
 values
 ('','',.....)
二、查看表的信息:  sp_help
exec   sp_help    table_name
三、表的修改与删除：
1.修改数据库重中的表：
2.修改表：alter  table   table_name
alter  table  dbo.xbb
drop  column  xzr 
3.删除数据库中的表：
有外键关联的表，一定要先去删除外键所在的主键表，然后再删除外键存在的表
 drop  table   table_name
数据表的基本操作
1.表的概念和数据类型：
表：是数据库对象    excel
关系模型   ：数据的逻辑结构    二维表（行、列）
student
学生表：学号、姓名、性别 。。。。。。。入学时间
教师表：教工号、姓名、性别、职称
课程表
学生表  
2.数据类型：int  float   double   char  数据的特征
indentity：自动增长列  indentity列
学号：
001    1
002
003
004
005
。。。
注意：
表名、字段名一律用英文单词或者汉语拼音首字母缩写形式表示，不允许用中文来表示。
系部代码：DepartmentID      xbdm
系部名称:DepartmentName     xbmc
系主任： DepartmentMaster   xzr
创建表的语法：
create   table   table_name
(
字段1   数据类型（长度）    not  null,
字段2   数据类型（长度）    not  null,
字段3   数据类型（长度）    not  null,
......
)
not  null：不为空
 null    ：为空
设置主键：constraint   pk_主键名   primary  key
constraint:约束
pk：主键， primary  key
设置外键：
外键：其实是用来进行表跟表直接的连接，起桥梁作用，其实是另一张表的主键
学生表：学号（主键）、姓名、性别
课程表：课程号（主键）、课程名、学号（外键）
constraint   fk_外键名   references   主键表（主键）
constraint    fk_xh     references     xsb(xh) 
constraint    fk_xh1     references     xsb(xh) 
constraint    fk_xh2     references     xsb(xh) 
fk:foreign  key
在表中字段上创建自增长列： identity
xh
001   1  间隔因子
002
003
004
005
006......
identity(起始值，步长值)  not  for   replication
数据的基本操作
增   删    改    查
增：insert
数据的添加：
1.最简单的insert语句：
insert    table_name
(column_list)
values
(expression)
2.省略清单的insert语句：系统会默认给表中所有的字段添加数据
insert    table_name
values
(expression)
3.省略valuse清单的insert语句：select查询的结果来代替valuse子句
例 创建系部表的一个副本 系部1 表，将系部表的全部数据添加到系部1表中。
3.数据的删除：delete;   truncate  table
(1)delete:删除数据
 delete   table_name
 delete   from   table_name
例1.删除系部备份表中所有的记录。
生成新表：into
例2.删除教师表中没有专业的记录。
为空：is  null  
不为空：not   is   null
例3.删除“课程注册备份表”中姓名为“刘永晖”，课程号为“0001”的选课信息。
姓名   学生表   xh
课程号    课程注册备份表    xh
（2）truncate  table:清空表格
truncate  table   table_name
例4.用truncate  table 语句删除“课程注册备份表”中的数据 
delete和truncate  table的区别：
使用truncate  table清空表格比delete语句快。truncate  table不记录日志
操作，删除的数据是不可以恢复。而delete语句不同，删除数据的时候要记录日志
信息。推荐使用delete来进行数据的删除。
数据：增  删   改   查
简单查询：-----单表查询   select
1.查询的基本语法：
select....from....where.....    查询块
2.基本概念：
into:生成新表
group  by:对查询的结果进行分组
having：对查询的结果进行条件筛选，having子句通常与group by子句一起使用
       尽管having子句前面不必有group by子句
order  by:对查询结果进行排序
升序：asc----默认排序
降序：desc
order  by  xh  desc
1.查询课程表中的课程号和课程名称。
2.查询学生表中全体学生的信息。
3.查询学生表中全体学生的姓名和年龄。
select  xm,year(getdate())-year(csrq)  from  student
getdate() 获取当前日期
year（）  获取年份
month()  获取月份
day()    获取日
date()   获取日期
1.为结果集内的列指定别名：
列名  as   别名
列名    别名
别名=列名
1.查询学生表中全体学生的姓名、性别及出生日期，要求结果集列标题为中文。
 select   xm  as 姓名,xb  性别,出生日期=csrq  from student
2.选择表中的若干记录：
去掉重复行：distinct
1.查询选修了课程的学生的学号。
  select  xh  from  courseregist
3.限制返回行数：top  n
                top   n   percent
1.查询课程注册表中的前20%的记录，只列出注册号、学号、课程号和教师编号
  
  select  top 20 percent  zch,xh,kch,jsbh   from courseregist 
4.查询满足条件的元组：
查询条件：
比较大小：=   >   <    >=   <=   !=    <>   !>   !<
 
         not+运算符
确定范围：between  and          not  between  and
确定集合：in                    not in
字符匹配：like   模糊查询       not  like
通配符：
%  代表任意长度的字符串     a%b   ahshshsjb    ahjuwiow899b
_  代表任意单个字符         a_b    ahb   ajb   a8b   a9b
[]  代表在括号内的任意一个字符   a[bcde]   ab  ac  ad  ae
                                 a[b-e]    ab  ac  ad  ae
[^] 代表不在括号内的任意一个字符
空值：  is  null                not  is  null
多重条件：  and     or     not
1.查询年龄小于或等于41岁教师的信息。
  select  *   from  teacher   where  year(getdate())-year(csrq)<=41
2.查询出生日期在1971年至1980年之间的教师编号、姓名和出生日期。
select  jsbh,xm,csrq  from teacher   where  csrq 
 between  '1971-01-01'  and '1980-12-31'
select jsbh，xm，csrq from teacher where year(csrq) between 
1971 and 1980
3.查询信息工程系、电子工程系的班级名称和班级代码。
 select  bjmc,bjdm  from  class  where   xbdm  in('01','02')
4.查询学生表中姓“刘”的同学的信息。
  select   *  from  student  where   xm  like  '刘%'
5.有一门课程名字叫“Delphi_6.0”,查询它的课程号和课程名称。
select   kch,kcmc  from  dbo.course  where  kcmc like 'Delphi/_6.0'  escape '/'
escape '/'：换码字符，在转码过程中，不参与任何匹配。
6.查询班级表中备注字段为空的班级信息。
select  *   from class  where  bz  is  null 
7.查询教师表中出生日期在1971年至1980年之间的女教师信息。
select  *    from  teacher  where  csrq  between  '1971-01-01' and 
'1980-12-31' and  xb='女'
 1.对查询结果进行排序：order by
order by  xh asc
升序：asc----默认排序
降序：desc
1.查询男教师的基本信息，按年龄降序排列。
2.查询全体学生的信息，查询结果按照所在班级的班级代码降序排列，同一个班
按照学号升序排列。
  
 select  *  from  student
 order by  bjdm  desc,xh asc
2.对数据进行统计：
集合函数：
count()   统计个数
sum()     求和
avg()     求平均值
max()     求最大值
min()     求最小值
1.查询学生表中的学生总数。
 select  count(*)  as   xszs   from  student
2.查询学生的平均年龄。
  select  avg(year(getdate())-year(csrq))  as  pjnl  from student
3.对结果进行分组：group  by      "各"
 对分组后的结果进行再次筛选或者统计：having
 group  by  和  having经常放在一起使用
 having  作用于组，where作用于表
1.查询课程注册表中课程选课人数3人以上的各个课程号和相应的选课人数。
select  kch,count(*) as  xkrs  from courseregist
group  by  kch
having   count(*)>3
4.compute子句：分类汇总统计
先排序，后汇总。
order  by
compute   集合函数   [by]
注意：compute子句仅限于SQL低版本，对高版本不支持！！！
1.查询所有学生所有成绩的总和。
select  *  from  courseregist
order by  xh
compute  sum(cj)
2.对每个学生的所有课程的成绩求和。
select  *  from  courseregist
order by  xh
compute  sum(cj)  by  xh
5.用查询结果生成新表：into  into不能与compute子句一起使用
1.创建班级表的一个副本。
 select   *  into  bjfb  from  class
 select *  from  bjfb
2.创建一个空的“学生”表副本。
 select  *  into xsfb  from student  where 1=2
6.合并结果集。union  将多个查询结果集合并为一个结果集  并操作
参加union操作的各结果列数必须相同，对应的数据类型也必须相同
1.查询课程注册表中0101专业的学生信息及课程成绩大于78分的学生信息
select *  from  courseregist  where   zydm='0101'
union
select  *  from  courseregist  where  cj>78
2.查询课程注册表中选修了0001号课程或者选修了0002号课程的学生。
select  xh,'选修了0001号课程'  as  备注  from courseregist
where kch='0001'
union
select  xh,'选修了0002号课程'  as  备注  from courseregist
where kch='0002'
数据的高级操作--多表查询
一、连接查询：join
内连接：[inner]  join
1.交叉连接查询：非限制连接，或者称为广义笛卡尔积。它是将两张表不加约束的连接
在一起，连接产生的结果集为两个表记录的交叉乘积，结果集的列为两个表属性列的和
cross  join
select ...from  表1  cross  join  表2
交叉连接查询产生的结果一般没有什么实际的应用意义，所有这种连接很少使用。
2.等值连接与非等值连接查询
连接条件：用来连接两个表的条件
表名.列名
比较运算符：=  >  <  >=   <=   !=
当比较运算符为“=”时，称为等值连接。其他运算符的连接就是非等值连接
等值连接的语法：
select ...from  表1  inner  join  表2  on  表1.列名=表2.列名
on  主键=外键
3.自然连接：在等值连接的基础上，去掉重复的列或属性，得到的查询称为自然连接
列名  as  别名
表的别名：
表名  as  别名
表名  别名
4.自身连接：一个表与其自身进行的连接。一个表与其副本连接
 在进行自身连接时，必须为表取别名。
例 查询选修了两门或两门以上课程的学生的学号和课程号。
外连接   [outer]  join
1.左外连接查询:left  outer   join
以左表为主表，主表中的所有记录分别与右表中的每一条记录进行连接组合，结果
集中除了满足条件的记录外，不满足的条件的记录在右表中相应位置填充NULL
2.右外连接查询:right  outer  join
以右表为主表，主表中的所有记录分别与左表中的每一条记录进行连接组合，结果
集中除了满足条件的记录外，不满足的条件的记录在左表中相应位置填充NULL
3.全外连接查询:full   outer  join
在左、右表的相应列上填充NULL
例 查询成绩在75分以上的学生的学号、姓名、专业代码和专业学级，选修课的
学期、课程号、成绩，任课教师的教师编号、姓名。
分析：
学生表：学号、姓名     as   a
xh
课程注册表：专业代码和专业学级，选修课的学期、课程号、成绩    as  b
jsbh
教师表：任课教师的教师编号、姓名   as  c
on   主键=外键
复合连接条件查询
select a.xh,a.xm,b.zydm,b.zyxx,b.xq,b.kch,b.cj,c.jsbh,c.xm from
student as a  join courseregist as b on a.xh=b.xh  and b.cj>75
join teacher as c on b.jsbh =c.jsbh
select xh,xm   into  学生1   from dbo.student  where  1=2
select  xh,cj   into  单科成绩   from dbo.courseregist   where  1=2
insert  dbo.学生1
values
('11','张三')
insert  dbo.学生1
values
('12','李四')
insert  dbo.学生1
values
('14','王五')
insert  dbo.单科成绩
values
('11',90)
insert  dbo.单科成绩
values
('13',80)
insert  dbo.单科成绩
values
('15',89)
select  *  from dbo.学生1   cross  join  dbo.单科成绩
用等值连接学生1表和单科成绩表。
select  *  from  dbo.学生1  inner  join  dbo.单科成绩
on  dbo.学生1.xh=dbo.单科成绩.xh
用自然连接学生1表和单科成绩表。
select  dbo.学生1.xh,xm,cj   from  dbo.学生1  inner  join  dbo.单科成绩
on  dbo.学生1.xh=dbo.单科成绩.xh
select  xm,dbo.单科成绩.xh,cj   from  dbo.学生1  inner  join  dbo.单科成绩
on  dbo.学生1.xh=dbo.单科成绩.xh
用表别名自然连接学生1表和单科成绩表。
select   s.xh,xm,cj   from 学生1  as  s  inner join  单科成绩  as  c
on  s.xh=c.xh
例 查询选修了两门或两门以上课程的学生的学号和课程号。
select  distinct  a.xh,a.kch   from  dbo.courseregist  as   a
join  dbo.courseregist  as  b
on  a.xh=b.xh  and  a.kch!=b.kch
用左外连接学生1表和单科成绩表：
select  *  from dbo.学生1  left  join dbo.单科成绩
on  dbo.学生1.xh=dbo.单科成绩.xh
用右外连接学生1表和单科成绩表：
select  *  from dbo.学生1  right  join dbo.单科成绩
on  dbo.学生1.xh=dbo.单科成绩.xh
用全外连接学生1表和单科成绩表：
select  *  from dbo.学生1  full  join dbo.单科成绩
on  dbo.学生1.xh=dbo.单科成绩.xh
select a.xh,a.xm,b.zydm,b.zyxj,b.xq,b.kch,b.cj,c.jsbh,c.xm from
student as a  join courseregist as b on a.xh=b.xh  and b.cj>75
join teacher as c on b.jsbh =c.jsbh
二、子查询
1.查询块：select ..from..where..
2.嵌套查询：将一个查询块嵌套在另一个查询块中的where子句或
         者having条件中的查询
select ..from..where..
  （select ..from..where..
     （select ..from..where..
        （select ..from..where..）））
3.父查询
4.子查询
5.对于嵌套查询的求解：由里向外处理，每个子查询在上一级查询处理之前求解，
 子查询的结果用于建立父查询的查找条件
 嵌套的层数不能超过32层。
6.子查询的分类：
（1）带有in运算符的子查询：结果是一个集合  true  或者  false
 in        not  in
例1 使用系部表和班级表，查询信息工程系与经济与管理系的班级信息。
跟在父查询where后面的字段就是子查询select后面的字段。
例2  查询选修了课程名称为SQL Server2008的学生学号和姓名。
（2）带有比较运算符的子查询：结果是一个单值
比较运算符：>   <  >=   <=  !=   <>
例3  查询选修了0001号课程，其成绩高于该课程平均分的学生信息。
（3）带有any或者all运算符的子查询
any \all 不能单独使用，必须跟比较运算符搭配一起使用。
>any  >all   <any  <all   >=any  <=all
例4  查询比060101001班中某一学生年龄小的其他班的学生学号和姓名。
（4）带有exists运算符的子查询---了解
 不返回任何数据，只产生逻辑值  true   false
 由exists运算符引出的子查询目标列一般为 *
 exists：存在
 not  exists:不存在
例5  查询所有选修了0001号课程的学生的学号和姓名。
 select  xh,xm  from student   where  exists
  (select *  from  courseregist  where  xh=student.xh  and kch='0001')
例6 查询一门课程也没有选修的学生学号和姓名。
select  xh,xm  from  student   where not  exists
(select  *  from  course where  exists(select  *  from
courseregist  where  student.xh=xh  and kch=course.kch))
使用系部表和班级表，查询信息工程系与经济与管理系的班级信息
select  *    from  dbo.class  where   xbdm in
 (select  xbdm  from dbo.department  where
     xbmc='信息工程系'  or  xbmc='经济与管理系')
查询选修了课程名称为SQL Server2008的学生学号和姓名
。
select  xh,xm  from dbo.student  where xh  in
(select  xh  from  courseregist   where  kch  in
  (select  kch   from  course  where  kcmc='SQL Server2008'))
  select   *  from dbo.course
查询选修了0001号课程，其成绩高于该课程平均分的学生信息。
select  *  from  dbo.student  where  xh  in(select  xh  from
courseregist  where  cj >(select  AVG(cj) from  courseregist  where
kch='0001')  and  kch='0001' )
查询比060101001班中某一学生年龄小的其他班的学生学号和姓名。
select  xh,xm  from dbo.student  where csrq >any
(select  csrq from dbo.student  where  bjdm='060101001')
and  bjdm  != '060101001'
查询比060101001班中所有学生年龄小的其他班的学生学号和姓名。
select xh,xm from student where csrq > all
(select csrq from student where bjdm = '060101001')
and bjdm != '060101001'
 select  xh,xm  from student   where  exists
  (select *  from  courseregist  where  xh=student.xh  and kch='0001')
select  xh,xm  from  student   where not  exists
(select  *  from  course where  exists(select  *  from
courseregist  where  student.xh=xh  and kch=course.kch))

[/huayang]

FROM：浅浅淡淡[hellohy]