sql server 建库建表查询

--EXEC xp_cmdshell 'mkdir D:\SQL' --调用DOS命令创建文件夹
CREATE DATABASE StudyDB
ON PRIMARY( --PRIMARY 可选指定主文件组中的文件

NAME = 'StudyDB1_data', --主数据文件的逻辑名
FILENAME = 'D:\tools\StudyDB1_data.mdf' , --主数据文件的物理名
SIZE = 3MB,  --主数据文件初始大小
MAXSIZE = 5MB,  --主数据文件最大大小
FILEGROWTH = 20%  --主数据文件的增长率
), --注意","号分隔
(

NAME = 'StudyDB2_data', --主数据文件的逻辑名
FILENAME = 'D:\tools\StudyDB2_data.ndf' , --主数据文件的物理名
SIZE = 512 KB,  --主数据文件初始大小
MAXSIZE = 5MB,  --主数据文件最大大小
FILEGROWTH = 20%
)
LOG ON
(

NAME = 'StudyDB1_log',
FILENAME = 'D:\tools\StudyDB_log1.ldf' ,
SIZE = 512KB,
MAXSIZE = 3MB,
FILEGROWTH = 20%
),  --注意","号分隔
(

NAME = 'StudyDB2_log',
FILENAME = 'D:\tools\StudyDB_log2.ldf' ,
SIZE = 512KB,
MAXSIZE = 3MB,
FILEGROWTH = 20%
)
use StudyDB
--学生表
create table Student
(Sno char(8) primary key ,
 Sname char(20) unique ,--取唯一值
 Sex char(2),
 Sage smallint,
 Sdept char(20)
)
--课程表
drop table Course
create Table Course(
 Cno char(4)  primary key,
 Cname char(40) ,
 Cpno char(4) foreign key  references Course(Cno),
 Ccredit smallint
)
--选课表
create table SC(
Sno char(8),
Cno char(4),
Grade smallint,
primary key (Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)

)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*
  alter table <表名>
[add <新列名><数据类型>[完整性约束]]
[drop <完整性约束名>]
[alter column<列名><数据类型>]
*/
--向STUDENT表中添加“入学时间”列，其数据类型为日期型-----------------------------
  alter table Student add S_entrance datetime;
  select * from Student/*查询*/
/*将年龄的数据类型由字符型改为整型*/
alter table Student alter column Sage int;
--增加课程名称必须取唯一值的约束条件--------------------------------------------
alter table Course add unique(Cname)
select * from Course]
/*删除基本表*/
drop table Student--受外键约束删除表及结构(有外键则不能删除)
delete Student---不受外键约束删除表里的内容
--若表上建有视图，选择restrict 时表不能删除，cascade时可以删除表，视图也自动被删除--
create view IS_Student  /*Student 表上建立视图*/
as select Sno,Sname,Sage from Student
where Sdept = 'IS';

drop table Student /*有（外键约束）约束条件删除不了*/
select * from IS_Student;

/*建立索引：聚集，非聚集

聚集索引在一个表中只能有一个，默认情况下在主键建立的时候创建，它是规定数据在表中的物理存储顺序，对其最常用的一个字段或者多个字段建立聚集索引或者组合的聚集索引，它就是SQL Server会在物理上按升序(默认)或者降序重排数据列，这样就可以迅速的找到被查询的数据。

所以我们在选择创建聚集索引的时候要注意以下几个方面：

　　1) 对表建立主键时，就会为主键自动添加了聚集索引，如自动编号字段，而我们没有必要把聚集索引浪费在主键上，除非你只按主键查询，所以会把聚集索引设置在按条件查询频率最高的那个字段或者组合的字段。

　　2) 索引的建立要根据实际应用的需求来进行，并非是在任何字段上建立索引就能提高查询速度。聚集索引建立遵循下面几个原则：

• 包含大量非重复值的列。
• 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。
• 被连续访问的列。
• 返回大型结果集的查询。

--非聚集索主要是数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。

*/

--为学生-课程数据库中的student course,sc3个表建立索引----------------------------
--student表按学号升序建唯一索引--------------------------------------------------
create unique index Stusno on Student(Sno);
--course 表课程号升序建立索引
create unique index Coucno on Course(Cno);
--SC表按学号升序，课程号降序排列
create unique index Scno on SC(Sno ASC,Cno desc);
--删除索引
--select 灵活查询--
/*select * <目标列表达式>
from student <表名>
 where  <条件表达式>
 groub by <列名> 分组查询
 having 条件表达式
 order by <列名> [asc|desc]
*/
select Sno ,Sname from Student
/*插入数据*/
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('200215129','李阳','男','20','cu')
select * from dbo.Course
select Sname,2004-Sage from Student;--目标列表达式不仅可以是算术表达式，还可以是函数，字符串常量等。
select Sname,'Year of Birth:',2004-Sage,LOWER(Sdept)
            from Student;
select Sname  NAME,'Year of Birth:'  BIRTH,2004-Sage  BIRTHDAY,LOWER(Sdept) DEPARTMENT
            from Student;-----起用别名，可以用 as 或者 直接 空格接别名
---取消重复的行，元组
--两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行，可以用distinct取消它们
select Sno from SC--从选课表中查询选了课的学生学号
select distinct Sno from SC--distinct 消除重复的行
select all Sno from SC---查询全部，一般为默认
delete Student--删除student
select * from Student
insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5','4')
/*--------------------------------------------------------------------
查询条件        
1  比较       |=,>,<,>=,<=,!=,!>,!<,not+上述比较运算符
2确定范围     |between and ,not between and  值是升序放置1 and 10;
3确定集合     |in ,not in
4字符匹配     |like ,not like
5  空值        |is null, is not null
6多重条件     |and ,or, not
*/------------------------------------------------------------------------
select Sname from Student where Sdept ='cs'
select Sname,Sage from Student where Sage<20
--一个学生有多门课不及格时，他的学号只列一次用distinct--------------------------
select distinct Sno from SC where Grade <60;
select Sname,Ssex from Student where Sdept in ('cs','ma','is')
select * from Student where Sdept not in ('ma','is');
select * from Student where Sno like '200215121'
select * from Student where Sname like '欧阳_'
select * from Student where Sname like '_阳%';
select * from Course where Cname like 'DB\_design'ESCAPE'\';
select * from Course where Cname  like 'DB\_%i__'escape'\';
select Sno ,Cno from SC where Grade is null\
select count(*)from student
select count (distinct Sno) from sc
select avg(grade) from sc where Cno ='1'
select max(Grade) from sc where Cno='1'
select sum(Ccredit) from sc , Course  where Course.Cno='200215121' and sc.Cno=Course.Cno;
--group by----将查询结果按某一列或多列的值分组，值相等的为一组---------------------
select Cno,count(Sno) as count from sc
group by Cno--对各个课程的选课进行统计
having count(*)>2--对各个课程选课大于2个的课程进行统计
--多表联查-------------------------------------------------------------------------
select Student.*,SC.*
from Student ,SC
where Student.Sno=SC.Sno
--自然连接去掉两表重复的列，----------------------------------------------------
--自身连接
select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno=second.Cno
--左外连接保留左边表的所有数据--------------------------------------------------
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student
left outer join SC
on Student.Sno=SC.Sno;
--右外连接-----------------------------------------------------------------------
select student.sno,sname ,ssex,sage,sdept,cno,grade
from student
right outer join sc
on (student.sno=sc.sno);
--多表连接---------------------------------------------------------------------
select Student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;
--- 选修了全部课程的学生------------------------------------------------------
select Sname
from student
where not exists
(select * from Course
  where not exists
       (select * from SC
                 where Sno=Student.Sno
                    and Cno= Course.Cno));
----没有选修一号课程的学生
select Sname
from student
where not exists
 (select *
    from sc
       where sno=student.sno and cno='1')
---查询选修了1号课程的所有学生
select *
 from student
   where exists
     (select * from sc
       where sno=student.sno and cno='1' )
---查询每门课程选修的人数且选修课在3门以上的
select cno, count(Sno) as total
from sc
group by cno
having count(*)>3
-----连接查询
--等值与非等值连接查询
--当连接运算符为‘=’时，称为等值连接
--若在等值连接中把目标列中重复的属性列去掉则为：自然连接
--自身连接-----------------------------------
select First.cno,Second.cpno    ----
from course First,course Second ----
where First.cpno=Second.cno     ----
---------------------------------------------
--SQL语言允许多层嵌套查询，子查询的select 语句中不能使用order by子句，order by 只能对最终查询结果排序
--查询与刘晨在同一个系学习的学生
select Sno ,Sname,Sdept  ----------
from Student             ----------
where Sdept in           ----------
 (select Sdept           ----------
   from Student          ----------
     where Sname='刘晨')  ---------
----用自身连接求解
select s1.sno,s1.sname,s1.sdept
from student s1,student s2
where s1.sdept=s2.sdept and
        s2.sname='刘晨'
--------------------------------------
--找出每个学生超过他选修课程平均成绩的课程号
--子查询返回单个值可以用比较运算符
select sno ,cno
from sc x
where grade >=(select avg(grade)
       from sc y
         where y.sno=x.sno)
--------------------------------------------------------------------------
--返回多值时要用any,使用any或all 谓词时则必须同时使用比较运算符
---  >  any 大于查询结果中的某个值
---  >  all 大于子查询结果中的所有值
---  <  any 小于子查询结果中的某个值
---  <  all 小于子查询结果中的所有值
---  >= any 大于等于子查询结果中某个值
---  >= all 大于等于子查询结果中所有值
---  <= any 小于等于子查询结果中某个值
---  <= all
--   =  any
---  =  all
---  <> any 不等于查询结果中的某个值
--------查询其他系比计算机系某一学生年龄小的----------------------------------
select sname,sage
from student
where sage <
(select max(sage)
  from student
       where sdept ='cs')
and sdept <> 'cs'
------------集合查询-----------------------
--集合操作，包括并操作union,交操作intersect,差操作except
--集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同
select *
from student
where sdept = 'cs'
union
select *
from student
where sage <='19'
---------------------
select *
from student
where sdept = 'cs'
intersect
select *
from student
where sage <= '19'
--------------------
select *
from student
where sdept = 'cs'
except
select *
from student
where sage <= '19'
------查询即选修了课程1又选修了课程2的学生-----------
select Sno
from SC
where Cno='1'
intersect --也可以用and
select Sno
from SC
where Cno='2'
---------------------------
insert into student
values('200215127','忽忽','女','25','ma','')
insert into sc
values('200215127','1',null)
-----对每个系，求学生的平均年龄-------
----------带子查询的插入语句
create table Dept_age
(Sdept char(15),
   Avg_age smallint);
----将得到的数据保存到数据库
insert into
dbo.Dept_age(sdept,avg_age)
select sdept,avg(sage)
from student
group by sdept;
-----------------------------------------
update sc
set grade=''--设置为0
where 'cs' =
       (select sdept
            from student
            where student.sno=sc.sno);
---------------------------------------------------------------------------------------
--定义视图
--create view <视图名>[(<列名>[，<列名>].....)]
--as <子查询>
--[with check option]
--子查询可以是任意复杂的select 语句，但通常不允许含有order by 子句和distinct短语
--with  check option 表示对视图进行update;insert;delete;操作时要保证更新，插入，删除
--的行满足视图定义中的谓词条件；
--以下情况必须明确指定组成视图的所有列名
--(1)某个目标列不是单纯的属性名，而是聚集函数或列表达式
--(2)多表连接时选出了几个同名列作为视图的字段
--(3)需要在视图中为某个列启用新的更合适的名字
---------------------------------------------------------------------------------------
drop view IS_Student--删除view
create view IS_Student--建立信息系学生的视图
as
select sno,sname,sage
from student
where sdept='is'
with check option
--由于定义在view 上的视图加上了with view option 子句，以后对该视图执行增删改操作时，
--RDBMS会自动加上sdept='is'的条件
--若一个视图是从基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们
--称这类视图为行列子集视图。视图可以建立在多个基本表上,分组视图(带有group by聚集函数的)
--------------------------
--定义一个反映学生出生年份的视图
create view bt_s(sno,sname,sbirth)
as
select sno,sname,2004-sage
from student;
--将学生的学号及它的平均成绩定义一个视图
create view S_G(Sno,Gavg)
as
select Sno,avg(Grade)
from sc
group by Sno
--删除视图----------------------------------------------------------------
--执行查询视图时，首先进行有效性检查，查询是否存在，即视图消解。
--通过基本表和视图表进行联查
--查询平均成绩90分以上的学生学号和平均成绩
select *
from S_G
where Gavg >=90;
select sno, avg(grade) as avg
from sc
group by sno
--where 子句中不能用聚集函数
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90
--------跟新视图------------------------------------------------------------------
--在关系数据库中，并不是所有的视图都是可以跟新的
--一般行列子集视图可以更新，其他大部分不行
--------------------------------------------------------------------------------
--DB2数据库规定：
--若视图是由两个以上的基本表导出，则不允许跟新
--若视图的字段来自字段表达式或常数，只允许执行delete操作，其他不行
--,,来自聚集函数。
-- 含有group by 。
-- 含有distinct 短语。
--视图定义中有嵌套查询，并且内层查询的from 子句中设计的表也是导出该视图的基本表