Sql Server数据库的基本语法
SQL SERVER数据类型与C#数据类型对照表 - thinksea - 博客园 (cnblogs.com)
一、创建数据库的基本方法
--创建数据库 Create database StudentDB on primary ( name='StudentDB', filename='G:\数据库\三级数据库学习\data\StudentDb.mdf', size=3mb, maxsize=unlimited, filegrowth=1mb ), ( name='StuDB1', filename='G:\数据库\三级数据库学习\data\StuDB1.ndf', size=3mb, maxsize=unlimited, filegrowth=1mb ), ( name='StuDB2', filename='G:\数据库\三级数据库学习\data\StuDB2.ndf', size=3mb, maxsize=unlimited, filegrowth=1mb ) log on ( name='StudentDB_log', filename='G:\数据库\三级数据库学习\data\StudentDB_log.ldf', size=1mb, maxsize=1gb, filegrowth=10% ) --修改数据文件 alter database [StudentDB] modify file (name=StudentDB,size=5mb) --修改数据库：添加一个辅助数据文件到新的文件组 alter database Studentdb add filegroup GData go alter database Studentdb add file ( name='StuDB3', filename='F:\数据库\data\StuDB3.ndf' ) to filegroup Gdata --删除数据库的辅助数据文件StuDB3 alter database Studentdb remove file Studb3 --删除数据库,"drop"删除数据库中的对象 drop database StudentDB --分离数据库 exec sp_detach_db StudentDB --附加数据库 create database StudentDB on primary ( name='StuDB3', filename='F:\数据库\data\StudentDB.mdf' ) for attach
--创建架构 use StudentDB go create schema my --删除架构 drop schema my
二、安全创建数据库的方法
Use master --exists判断()里面的语句是否返回值，如果有值就返回True if exists( select * from sysdatabases where name='TestSchool') --删除数据库 drop database TestSchool go --开启外围服务配置,xp_cmdshell execute sp_configure 'show advanced options',1 RECONFIGURE execute sp_configure 'xp_cmdshell',1 RECONFIGURE go --自动创建文件夹，调用存储过程，让其帮助创建一个文件夹 execute xp_cmdshell 'mkdir G:\数据库\Mydir' --使用语句创建数据库 create database TestSchool on primary--在那个文件组上创建，默认是主文件组上创建主数据文件,可以省略 ( name='TestSchool_data', size=3mb, filegrowth=10%, maxsize=100mb, filename='G:\数据库\Mydir\TestSchool_data.mdf' ) log on ( name='TestSchool_log', size=3mb, filegrowth=10%, maxsize=100mb,--日志文件一般不限制最大容量 filename='G:\数据库\Mydir\TestSchool_log.ldf' )
三、创建表
use StudentDB --创建数据表 create table StudentDB.my.Tb_Stu_Info create table Tb_Stu_Info ( Stu_No varchar(20) primary key not null, Stu_Name nvarchar(10), Stu_Sex nvarchar(6), Stu_Birthday date, Stu_Address nvarchar(200) ) /*修改数据表[dbo].[Tb_Stu_Info]，增加名为Stestd lie , 数据类型为int,不为空。*/ alter table [dbo].[Tb_Stu_Info] add stest int not null --修改stest列的数据类型 alter table [dbo].[Tb_Stu_Info] alter column stest varchar(20) --删除stest列 alter table [dbo].[Tb_Stu_Info] drop column stest alter table Tb_Stu_Info drop column Stu_Birthday,Stu_Address alter table [dbo].[Tb_Stu_Info] add Stu_Birthday date, Stu_Address nvarchar(200) --删除数据表 drop table [dbo].[Tb_Stu_Info] --创建分区表 /*在数据表中，依据表StudentInfo的StdInfoYear列来创建分区函数YearOrderPartFunc, 边界值为2007和2009*/ --首先创建分区函数 create partition function YearOrderPartFunc(int) as range left for values('2007','2009') --其次创建分区方案 create partition scheme YearOrderPartScheme as partition YearOrderPartFunc to([primary],[primary],[primary])--to(分别对应的是文件组） --最后依据分区方案创建表 create table Tb_StudentInfo ( StdInfoID int identity(1,1) not null, StdName varchar(20) not null, StdYear int not null ) on YearOrderPartScheme(StdYear) ----------------------------------------------------------------- --查看所属分区 select *,$partition.YearOrderPartFunc(StdYear) as '所属分区' from dbo.Tb_StudentInfo --创建视图，查询每门课程的授课老师.没有数据无法测试 create view View_TeacherCourse (课程名称,老师姓名) as select CourseInfoName,TeachInfoName from dbo.CourseInfo a join dbo.TeachCourse b join dbo.TeachInfo c on b.TeachCourseID=c.TeachInfoID on a.CourseInfoID=b.CourseInfoID --查询视图数据 select * from dbo.View_TeacherCourse --查看视图的定义 sp_helptext View_TeacherCourse --修改视图 alter view View_TeacherCourse (课程名称,老师姓名,职称) as select CourseInfoName,TeachInfoName,TeachInfoKnowl from dbo.CourseInfo a join dbo.TeachCourse b join dbo.TeachInfo c on b.TeachCourseID=c.TeachInfoID on a.CourseInfoID=b.CourseInfoID --删除视图 drop view View_TeacherCourse
四、创建表以及表的增、删、改、查、聚合函数、、、、操作示例
use master create database StuSetDB on primary ( name='StuSetDB', filename='G:\数据库\三级数据库学习\WorkSpace\StuSetDB.mdf', size=3mb, maxsize=unlimited, filegrowth=1mb ) log on ( name='StuSetDB_log', filename='G:\数据库\三级数据库学习\WorkSpace\StuSetDB_log.ldf', size=1mb, maxsize=1gb, filegrowth=10% ) --删除数据库 exec sp_detach_db StuSetDB drop database StuSetDB use StuSetDB create table AdminUser ( AdminUserID int not null primary key, AdminUserName varchar(50) not null, AdminUserPwd varbinary(120) not null, AdminLoginTimer datetime not null ) --课程类型 /* 1 专业基础课 2 专业课 3 公共课 */ create table CourseType ( CourseTypeID int not null primary key, CourseTypeName varchar(50) not null ) --课程信息表 create table CourseInfo--正确设置 ( CourseInfoID int not null primary key, CourseTypeID int not null foreign key references CourseType(CourseTypeID), CourseInfoCode char(8) not null, CourseInfoName varchar(50) not null, CourseInfoProj varchar(max) not null, CourseInfoRstPer int, CourseInfoPraPer int, CourseInfoHotHrs int , CourseInfoCreHor int, CourseInfoRMK varchar(max) ) /* 老师类型 1 专职 2 兼职 3 外聘 */ create table TeachType--正确设置 ( TeachTypeID int primary key, TeachTypeName varchar(20) ) /* 系部 1 001 信息工程系 32 10 2 002 数字自动化工程系 26 11 3 003 计算机工程系 32 13 4 004 经济管理系 48 15 5 005 外语系 28 10 6 006 人文科学系 36 12 7 007 会计系 27 9 8 008 继续教育部 15 6 */ create table DepInfo ( DepInfoID int not null primary key, DepInfoCode char(10), DepInfoName varchar(50), DepInfoPreO int, DepInfoTeach int ) /* 老师信息表 1 3 1 030026 李静 女 研究生 硕士 计算机应用 副教授 毕业于... */ create table TeachInfo--正确设置 ( TeachInfoID int not null primary key identity(1,1), DepInfoID int not null foreign key references DepInfo(DepInfoID), TeachTypeID int not null foreign key references TeachType(TeachTypeID), TeachInfoNum int not null, TeachInfoName varchar(20) not null, TeachInfoSex varchar(10) not null, TeachInfoknowl varchar(20), TeachInfoDeg varchar(20), TeachInfoSpecil varchar(50), TeachInfoTilte varchar(50), TeachInfoRMK varchar(50) ) /* 专业 1 3 590109 图形图像制作 电子信息大类 以“***” 在广告，传媒... 3 2 580202 电气自动化技术 制造大类 本专业以“**” 在太阳能光伏... 5 3 590108 软件技术 电子信息大类 以教务管理系统... 在软件相关.... 7 3 5901022 网络技术 电子信息大类 8 3 590101 计算机应用技术 电子信息大类 培养系统掌握.. 主要从事.. 9 3 590110 动漫设计与制作 电子信息大类 培养动漫.... 与..... 10 3 590208 信息安全 电子信息大类 培养能适应.... 在电子行业... 11 1 590202 应用电子技术 电子信息大类 培养掌握... 适应..... 12 1 590201 电子信息工程 电子信息大类 通过... 主要..... 13 2 580110 计算机辅助设计 电子信息大类 培养从事... 主要在... 14 2 520104 汽车运用技术 电子信息大类 培养掌握... 主要在品牌... 15 4 620204 会计电算化 财经大类 培养... 会计电算化作为.... 16 4 620302 经济信息管理 财经大类 一企业经营... 随着信息化... */ create table SpecilInfo ( SpecilInfoID int not null primary key, DepInfoID int not null foreign key references DepInfo(DepInfoID), SpecilInfoCode char(10), SpecilInfoName varchar(50), SpecilInfoSubject varchar(20), SpecilInfoAm varchar(100), SpecilInfoProOne varchar(100) ) --班级信息表 create table ClassInfo--正确设置，老师ID教程没有设置外键约束 ( ClassInfoID int not null primary key, SpilInfoID int not null foreign key references dbo.ClassInfo(ClassInfoID), ClassInfoCode char(10) not null, ClassInfoName varchar(50) not null, TechInfoID int not null foreign key references TeachInfo(TeachInfoID), ClassInfoSum int , ClassInfoRMK varchar(max) ) /* 老师课程表 5 1 4 1 1 5 2 1 6 4 3 7 6 3 20 7 3 26 3 5 7 8 5 8 9 5 13 20 11 10 21 11 24 23 12 24 24 12 26 25 13 10 26 13 25 10 15 16 11 15 17 12 15 20 13 16 28 14 16 31 15 17 7 16 17 11 17 17 15 18 19 27 */ create table TeachCourse--正确设置 ( TeachCourseID int not null primary key identity(1,1), TeachInfoId int not null foreign key references TeachInfo(TeachInfoID), CourseInfoId int not null foreign key references CourseInfo(CourseInfoID) ) /*选课信息 1 1 2 78 0 0 0 null 5 2 2 56 0 0 0 null 10 5 5 99 0 0 0 11 1 5 90 0 0 0 12 2 5 40 0 0 0 13 3 6 88 10 0 0 14 5 6 45 0 0 0 15 1 6 67 0 0 0 16 2 6 99 0 0 0 17 6 38 45 0 0 0 18 7 38 89 0 0 0 19 6 45 0 0 0 0 20 4 49 0 10 0 0 22 6 50 0 0 0 0 23 7 50 0 0 0 0 25 4 52 0 10 0 0 26 7 52 0 0 0 0 26 7 52 0 0 0 0 27 4 54 0 10 0 0 28 7 54 0 0 0 0 29 3 17 0 10 0 0 */ create table StudentCourse--正确设置 ( StudentCourseID int not null primary key identity(1,1), TeachCourseID int not null foreign key references dbo.TeachCourse(TeachCourseID), StudentInfoID int not null, StudentCourseUsuGrd numeric(18,0), StudentCourseExamGrd numeric(18,0), StudentCourseTermGrd numeric(18,0), StudentCourseFlag int, TeachEvaluation varchar(max) ) /*学生信息表 2 31 03080110 刘莉 男 129456123 1990-01-01 00:00:00 汉 139086000 123@126.com 2008 3 31 03080404 张琳 男 */ create table StudentInfo --正确设置，教程没有设置主键和外键 ( StudentInfoID int not null primary key, ClassInfoID int not null foreign key references ClassInfo(ClassInfoID), StudentInfoNum char(8) not null, StudentInfoName varchar(50) not null, StudentInfoSex varchar(2) not null, StudentInfoCard varchar(18), StudentInfoBrth dateTime, StudentInfoNatns varchar(20), StudentInfoTel varchar(30), StudentInfoEmail varchar(30), StudentInfoYear int ) -----------------以上为数据库和表格的初始化 --insert语句 insert [dbo].[StudentCourse] values(1,50,0,0,0,null,null) --insert 插入多条 insert [dbo].[StudentCourse] values(2,50,0,0,0,null,null), (3,50,0,0,0,null,null), (4,50,0,0,0,null,null), (5,50,0,0,0,null,null) --insert 插入部分数据 insert [dbo].[TeachCourse](TeachInfoID,CourseInfoID) values(18,17) --插入其它表的数据 --将学生信息表StudentInfo中女同学的学号、性别、身份证号码、家庭地址、联系电话插入到新表StudentInfo1 create table StudentInfo1 ( StudentInfoNum varchar(20) not null, StudentInfoName varchar(30) not null, StudentInfoSex varchar(2) not null, StudentInfoCard varchar(18) , StudentInfoAddress varchar(60), StudentInfoTel varchar(30) ) insert into dbo.StudentInfo1 select StudentInfoNum,StudentInfoName,StudentInfoSex,StudentInfoCard, StudentInfoNatns,StudentInfoTel from dbo.StudentInfo where StudentInfoSex='女' --update 语句 更新 update dbo.StudentInfo set StudentInfoName='李婵林', StudentInfoTel='15858095197', StudentInfoEmail='15858095197@qq.com' where StudentInfoName='李婵' --更新兼职老师的TeachInfoRMK update dbo.TeachInfo set TeachInfoRMK='每周上课不超过10节' from TeachInfo a join TeachType b on a.TeachTypeID=b.TeachTypeID where TeachTypeName='兼职' --将“网页设计”课程考试成绩不及格的在原分数基础上加5分 update StudentCourse set StudentCourseExamGrd+=5 where StudentCourseExamGrd<60 and TeachCourseID in ( select TeachCourseID from TeachCourse where CourseInfoID=(select CourseInfoID from CourseInfo where CourseInfoName='网页设计') ) --delete 删除行 delete StudentCourse where StudentCourseID=50 and TeachCourseID=4 delete StudentCourse from StudentCourse a join StudentInfo b on a.StudentCourseID=b.StudentInfoID where StudentInfoName='刘莉' --删除表中的所有数据 delete StudentInfo1 --truncate也可以删除表中的所有数据，效率更高，使用的系统资源更少 truncate table StudentInfo1 --查询语句 --1.查询院系信息表的所有信息 select * from DepInfo --2查询教师信息表中的教师姓名。专业。和职称 select TeachInfoName,TeachInfoSpecil,TeachInfoTilte from TeachInfo --3、查询所有老师从事的专业 select TeachInfoSpecil from TeachInfo --4、查询所有老师从事的专业消除重复 select distinct TeachInfoSpecil from TeachInfo --5、查询班级信息表中的前8条记录的班级代号，班级名称。 select top 8 ClassInfoCode,ClassInfoName from ClassInfo --6、查询班级信息表中的前百分之二十的记录的班级代号，班级名称。 select top 20 percent ClassInfoCode,ClassInfoName from ClassInfo --7、查询课程信息表中课程名称，理论学时，实践学时 select CourseInfoName,CourseInfoRstPer,CourseInfoPraPer from CourseInfo --8、查询课程信息表中课程名称，理论学时，实践学时，总学时 select CourseInfoName,CourseInfoRstPer,CourseInfoPraPer, CourseInfoRstPer+CourseInfoPraPer from CourseInfo --9、查询课程信息表中课程名称，理论学时，实践学时，总学时，给每一列取名 select CourseInfoName as 课程名称,CourseInfoRstPer as 理论学时,CourseInfoPraPer as 实践学时, CourseInfoRstPer+CourseInfoPraPer as 总学时 from CourseInfo --方法二 select 课程名称=CourseInfoName,理论学时=CourseInfoRstPer,实践学时=CourseInfoPraPer, 总学时=CourseInfoRstPer+CourseInfoPraPer from CourseInfo --11、查询某年以后出生的学生的学号、姓名、性别和出生日期，结果按年龄从小到大排序 --默认为 asc 升序， 降序desc select StudentInfoNum,StudentInfoName,StudentInfoSex,StudentInfoBrth from StudentInfo where StudentInfoBrth>='1990-1-1' order by StudentInfoBrth desc --查询出生日期在1990-1-1之后的女生 select * from StudentInfo where StudentInfoBrth > '1990-1-1' and StudentInfoSex='女' --查询所有姓张的学生信息 select * from StudentInfo where StudentInfoName like '张%' --查询入学年份在2007到2009之间的学生信息 select * from StudentInfo where StudentInfoYear between 2007 and 2009 select * from StudentInfo where StudentInfoYear >= 2007 and StudentInfoYear <=2009 select * from StudentInfo where StudentInfoYear in ( 2007 ,2008,2009) --12、查询某年以后出生的学生的学号、姓名、性别和出生日期，结果按年龄从小到大排序 --当年龄相同时，按先女生后男生排序 select StudentInfoNum,StudentInfoName,StudentInfoSex,StudentInfoBrth from StudentInfo where StudentInfoBrth >='1990-1-1' order by StudentInfoBrth desc,StudentInfoSex desc --13、聚合函数 统计学生、教师的总人数 --统计系部教学编制的总数、最大、最小及平均值 select COUNT(*) from StudentInfo select COUNT(*) from TeachInfo select SUM(DepInfoPreO) as 总数, MAX(DepInfoPreO) as 最大值, MIN(DepInfoPreO) as 最小值, AVG(DepInfoPreO) as 平均值 from DepInfo --分组： --14、查询教师表中有各种学历的人数 select TeachInfoknowl,COUNT(TeachInfoKnowl) as 数量 from TeachInfo group by TeachInfoknowl --15、统计1990年以后出生的男生和女生各多少人 select StudentInfoSex,COUNT(*) from StudentInfo where StudentInfoBrth>'1990-1-1' group by StudentInfoSex --16、显示所有学生的相关信息，并汇总学生总人数 select StudentInfoName,StudentInfoSex,StudentInfoNatns from StudentInfo compute COUNT(StudentInfoName) --17、显示所有学生的相关信息，并按学生所属民族分类汇总 --分类汇总需要先排序 select StudentInfoName,StudentInfoSex,StudentInfoNatns from StudentInfo order by StudentInfoNatns compute COUNT(StudentInfoNatns) by StudentInfoNatns --连接查询 --内连接返回所有满足条件的结果，左连接左边的表不满足条件的也返回 --完全外连接左右表中不满足条件的都返回 --1、查询每个教师的基本信息及教师类别（内连接） select TeachInfoName,TeachInfoSex,TeachInfoTilte,TeachTypeName, TeachInfo.TeachTypeID from TeachInfo inner join TeachType on TeachInfo.TeachTypeID=TeachType.TeachTypeID --使用别名 select TeachInfoName,TeachInfoSex,TeachInfoTilte,TeachTypeName, a.TeachTypeID from TeachInfo a join TeachType b on a.TeachTypeID=b.TeachTypeID --2、查询每位老师的授课情况，不管老师有没有授课都要包括其情况（左外连接） select TeachInfoNum,TeachInfoName,TeachInfoSex,TeachInfoTilte, CourseInfoID from TeachInfo a left join TeachCourse b on a.TeachInfoID=b.TeachInfoID --3。查询所有老师授课课程的选修情况 （右外连接） select StudentCourseID,StudentInfoID,TeachInfoID,CourseInfoID from StudentCourse a right join TeachCourse b on a.TeachCourseID=b.TeachCourseID --4、使用完全外连接查询数据库中每位老师的授课情况 select TeachInfoNum,TeachInfoName,TeachInfoSex,TeachInfoTilte, TeachCourseID,CourseInfoID from TeachCourse a full join TeachInfo b on a.TeachInfoId=b.TeachInfoID --5、查询所有女生的所在班级信息 select StudentInfoName,StudentInfoSex,ClassInfoName from StudentInfo a join ClassInfo b on a.ClassInfoID=b.ClassInfoID where StudentInfoSex='女' --6、查询学生所属的班级信息和专业信息 注意ClassInfo需要写在中间位置 select StudentInfoName,SpecilInfoName,ClassInfoName from StudentInfo a join ClassInfo b on a.ClassInfoID=b.ClassInfoID join SpecilInfo c on c.SpecilInfoID=b.SpilInfoID --7、查询学生选课的课程数量由高到低排序 select StudentInfoName, COUNT(b.StudentInfoID) 课程门数 from StudentInfo a join StudentCourse b on a.StudentInfoID=b.StudentInfoID group by StudentInfoName order by 课程门数 desc --8、查看计算机工程系的教师授课课程的选修情况 select TeachInfoName,DepInfoName,COUNT(StudentInfoID) from DepInfo a join TeachInfo b on a.DepInfoID=b.DepInfoID join TeachCourse c on b.TeachInfoID=c.TeachInfoId join StudentCourse d on c.TeachCourseID=d.TeachCourseID where DepInfoName='计算机工程系' group by TeachInfoName,DepInfoName --9、查询每个学生的选课情况，列出学生的姓名、课程名称、教师名 select StudentInfoName,CourseInfoName,TeachInfoName from StudentInfo a join StudentCourse b on a.StudentInfoID=b.StudentInfoID join TeachCourse c on b.TeachCourseID=c.TeachCourseID join CourseInfo d on c.CourseInfoId=d.CourseInfoID join TeachInfo e on c.TeachInfoId=e.TeachInfoID --10、利用并运算查询老师和学生的姓名 select StudentInfoName 姓名 from StudentInfo union select TeachInfoName from TeachInfo --11、利用交运算查询彭欢老师和朱志奇老师教授的同一门课程，列出课程名称 select CourseInfoName from CourseInfo a join TeachCourse b on a.CourseInfoID=b.CourseInfoId join TeachInfo c on b.TeachInfoId=c.TeachInfoID where TeachInfoName='彭欢' intersect select CourseInfoName from CourseInfo a join TeachCourse b on a.CourseInfoID=b.CourseInfoId join TeachInfo c on b.TeachInfoId=c.TeachInfoID where TeachInfoName='朱志奇' --12、利用差运算查询彭欢老师授课而没有朱志奇老师授课的课程，列出课程名称 select CourseInfoName from CourseInfo a join TeachCourse b on a.CourseInfoID=b.CourseInfoId join TeachInfo c on b.TeachInfoId=c.TeachInfoID where TeachInfoName='彭欢' except select CourseInfoName from CourseInfo a join TeachCourse b on a.CourseInfoID=b.CourseInfoId join TeachInfo c on b.TeachInfoId=c.TeachInfoID where TeachInfoName='朱志奇' --子查询 --1、查找比所有课程类别ID为1的课程总学时都要高的课程 select CourseInfoName,CourseTypeID from CourseInfo where CourseInfoHotHrs > all (select CourseInfoHotHrs from CourseInfo where CourseTypeID=1) --2、查询数据库中所有已获‘副教授’职称的女教师的姓名、性别、学历、专业和职称 select * from ( select TeachInfoName,TeachInfoSex,TeachInfoknowl,TeachInfoSpecil,TeachInfoTilte from TeachInfo where TeachInfoTilte='副教授') as t where t.TeachInfoSex='女' --3、查找开设了课程ID为7的课程的老师的相关信息 --首先查询课程ID为7的课程有哪些老师授课 --其次查询上一个不收里面相关老师的信息 select TeachInfoName,TeachInfoSex,TeachInfoknowl,TeachInfoSpecil,TeachInfoTilte from TeachInfo where TeachInfoID in (select TeachInfoID from TeachCourse where CourseInfoId=7) --4、查找数据库中选修了7号课程的学生学号、姓名和联系电话 /*首先查询教授7号课程的老师信息，然后查询学生选课是否选了7号课程的老师*/ select StudentInfoNum,StudentInfoName,StudentInfoTel from StudentInfo where exists( select * from StudentCourse where StudentInfoID=StudentInfo.StudentInfoID and exists( select * from TeachCourse where CourseInfoId=7 and TeachCourseID=StudentCourse.TeachCourseID)) --5、查询与“刘莉”同班同学的信息 select * from StudentInfo where ClassInfoID=( select ClassInfoID from StudentInfo where StudentInfoName='刘莉') --6、查询课程考试不及格的学生的姓名、性别 select StudentInfoName,StudentInfoSex from StudentInfo where StudentInfoID in ( select StudentInfoID from StudentCourse where StudentCourseUsuGrd<60) --7、查询课程考试不及格的学生的姓名和课程名 select 姓名=(select StudentInfoName from StudentInfo where StudentInfoID=a.StudentInfoID), 课程名=(select CourseInfoName from CourseInfo where CourseInfoID in( select CourseInfoID from TeachCourse where TeachCourseID=a.TeachCourseID)) from StudentCourse a where StudentCourseUsuGrd<60 --8、查询网页设计课程不及格的学生姓名。性别和电话 select StudentInfoName,StudentInfoSex,StudentInfoTel from StudentInfo where StudentInfoID in ( select StudentInfoID from StudentCourse where StudentCourseUsuGrd<60 and TeachCourseID in( select TeachCourseID from TeachCourse where CourseInfoId=( select CourseInfoID from courseInfo where CourseInfoName='网页设计'))) --存储过程 --查询学生ID为6的学生姓名和已选课程门数，当选课门数在3门以上，输出“XX，已经完成了选课” --否则输出“XX，还需选课 declare @sname varchar(50),@num int select @sname=StudentInfoName from StudentInfo where StudentInfoID=6 set @num=(select COUNT(*) from StudentCourse group by StudentInfoID having StudentInfoID=6) if @num>=3 begin print @sname + '，你已经完成了选课！' end else print @sname + '，你还需要继续选课！' --2、简单case结构：查询学生信息表中学生的性别，若是女生显示“female”,否则显示“male” select StudentInfoName , StudentInfoSex,性别=case when StudentInfoSex='男' then 'male' when StudentInfoSex='女' then 'female' end from StudentInfo --3、循环结构：计算1-100内能被3整除的最大数 declare @x int =100 while(@x>=1) begin if(@x % 3=0) break set @x-=1 end print @x --4、定义一个存储过程，用于查询数据库中所有教师的姓名、性别、学历、职称和所教授的课程名称 --procedure可以简写为proc create procedure ShowTeaCourse as select TeachInfoName,TeachInfoSex,TeachInfoknowl,TeachInfoSpecil,TeachInfoTilte,CourseInfoName from TeachInfo a join TeachCourse b on a.TeachInfoID=b.TeachInfoId join CourseInfo c on b.CourseInfoId=c.CourseInfoID --5、执行存储过程的三种方式 execute ShowTeaCourse exec dbo.ShowTeaCourse dbo.ShowTeaCourse --4、创建多语句表值函数，用来查询指定教师任教的课程名称。实践课时、理论课时和总课时 select CourseInfoName, CourseInfoRstPer,CourseInfoPraPer,CourseInfoRstPer+CourseInfoPraPer from dbo.CourseInfo c join dbo.TeachInfo a join dbo.TeachCourse b on a.TeachInfoID=b.TeachInfoId on c.CourseInfoID=b.CourseInfoId where TeachInfoName='朱志奇' create function FSelCourse(@teachName varchar(20)) returns @tech_Course table ( 课程名称 varchar(80), 理论学时 int, 实践学时 int, 总学时 int ) as begin insert @tech_Course select CourseInfoName, CourseInfoRstPer,CourseInfoPraPer,CourseInfoRstPer+CourseInfoPraPer from dbo.CourseInfo c join dbo.TeachInfo a join dbo.TeachCourse b on a.TeachInfoID=b.TeachInfoId on c.CourseInfoID=b.CourseInfoId where TeachInfoName=@teachName return end --调用 select * from FSelCourse('朱志奇') --5、游标的使用 --1.声明游标；2.打开游标；3.提取数据；4.关闭游标；5.释放游标 --对学生信息表，定义一个查询“汉族，性别男”的学生姓名、性别、民族的游标，并输出游标结果 --声明相关变量 declare @sname varchar(20), @ssex varchar(5),@snatns varchar(5) --声明游标 declare student_cursor cursor for select StudentInfoName,StudentInfoSex,StudentInfoNatns from StudentInfo where StudentInfoNatns='汉' and StudentInfoSex='男' --打开游标 open student_cursor --提取数据 fetch next from student_cursor into @sname,@ssex,@snatns while @@FETCH_STATUS=0 begin print '学生姓名：' + @sname +'性别：' + @ssex +'民族：' +@snatns fetch next from student_cursor into @sname,@ssex,@snatns end --关闭游标 close student_cursor --释放游标 deallocate student_cursor --1、将windows账户中的用户“Teacher"添加到 SQL Server登陆中， --默认数据库为“master" create login [moon-PC\Teacher]--创建windows身份验证的用户 from windows with default_database=[master] --2、创建名为“Teachers”的SQL登陆，密码为“sql@123%",默认数据库为“master",强制实施密码策略 create login Teachers with password='sql@123%', default_database=[master], check_expiration=on, check_policy=on --3、修改SQL登陆Teachers，密码改为“sql10o123” alter login Teachers with password='sql10o123' old_password='sql@123%' --4、禁用名为“Teachers”的登陆 alter login Teachers enable--启用 alter login Teachers disable--禁用 drop login Teachers--删除 --用户管理： --6、创建名为 teachUser的登录名，在数据库中，创建用户 teach 与teachUser登录名对应 create login teachUser with password ='123', default_database=[master] go use StudentDB go create user teach for login teachUser --7、修改数据库中创建的用户teach 将名称改为teach2 alter user teach with name=teach2 --8、删除数据库中用户teach drop user teach --用户权限管理 --9、在数据库中，创建用户teacher,对应登录名为teachers,并将表teachinfo的select权限授予teacher create login teachers with password='123', default_database=[master], check_expiration=on, check_policy=on create user teacher for login teachers grant select on TeachInfo to teacher --10、在数据库中，拒绝用户teacher 查看adminUser表的权限 deny select on AdminUser to teacher --11、在数据库中，撤销用户teacher对表teachinfo的select权限 revoke select on TeachInfo to teacher --角色管理 --12、将SQL Server登录名 teachers 添加到sysadmin 固定服务器角色中，使其可以在数据库服务器上执行任何操作 exec sp_addsrvrolemember 'teachers','sysadmin' --13、将SQL Server登录名 teachers 从sysadmin 固定服务器角色中删除 exec sp_dropsrvrolemember 'teachers','sysadmin' --14、创建一个新的登陆test ,将其映射到数据库的用户test，设置数据库角色的成员，使之对该数据库中的数据只有只读的权限 create login test with password ='123', default_database=[master] go use StudentDB go create user test for login test go exec sp_addrolemember 'db_datareader' ,'test' --15、将数据库中的用户test从db_datareader角色中删除 exec sp_dropsrvrolemember 'db_datareader' ,'test' --16、将数据库中的添加角色MyTeacher,并将用户teacher1（对应登录名teach1）和teacher2（对应登陆名teach2）添加到该角色中， --赋予该角色在教师信息表Teachinfo上有插入、修改和删除的全检，拒绝该角色创建表的权限 create login teach1 with password='123', default_database=[master] go create login teach2 with password='123', default_database=[master] go use StudentDB go create user Teacher1 for login teach1 go create user Teacher2 for login teach2 go create role MyTeacher--创建角色 go exec sp_addrolemember 'Myteacher','Teacher1'--添加角色 go exec sp_addrolemember 'Myteacher','Teacher2'--添加角色 go grant insert,update,delete on TeachInfo to Myteacher with grant option go deny create table to Myteacher
存储过程
--https://zhuanlan.zhihu.com/p/72856084 use SchoolDB --新增学生 IF OBJECT_ID (N'PROC_INSERT_StudentInfo', N'P') IS NOT NULL DROP PROCEDURE PROC_INSERT_StudentInfo; GO CREATE PROCEDURE PROC_INSERT_StudentInfo @StudentName VARCHAR(50), @StudentAge int, @StudentSex varchar(2) AS INSERT INTO SchoolDB.dbo.StudentInfo([Name],[Age],[Sex]) VALUES(@StudentName,@StudentAge,@StudentSex) SELECT SCOPE_IDENTITY() AS [StudentId] GO --修改 IF OBJECT_ID (N'PROC_UPDATE_StudentInfo', N'P') IS NOT NULL DROP PROCEDURE PROC_UPDATE_StudentInfo; GO CREATE PROCEDURE PROC_UPDATE_StudentInfo @StudentId int, @StudentName VARCHAR(50), @StudentAge int, @StudentSex varchar(2) AS UPdaTE SchoolDB.dbo.StudentInfo SET Name=@StudentName,Age=@StudentAge,Sex=@StudentSex where StudentId=@StudentId GO --删除 IF OBJECT_ID (N'PROC_Delete_StudentInfo', N'P') IS NOT NULL DROP PROCEDURE PROC_Delete_StudentInfo; GO CREATE PROCEDURE PROC_Delete_StudentInfo @StudentId int AS delete from SchoolDB.dbo.StudentInfo where StudentId=@StudentId; GO
连接查询
内连接
关键字：inner join on
语句：select * from a_table a inner join b_table on a.a_id = b.b_id;
说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集。
左连接
关键字：left join on / left outer join on
语句：select * from a_table a left join b_table on a.a_id = b.b_id;
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
右连接
关键字：right join on / right outer join on
语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;
说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接 - New.Young - 博客园 (cnblogs.com)
