SQLServer笔记

------------------------------------------------Data Base Management System-----------------------------------------------------------begin transactioncommit transactionrollback transactionexistsproceduredeclarecreatesysobjectsdistincttriggerexec sp_configure 'show advanced options',1使用系统存储过程sp_configure启用高级选项execute xp_cmdshell 'copy /b D:\1.txt + D:\2.txt  D:\3.txt'使用DOS命令操作文件或目录-----------------------------------------------------SQL语句的执行顺序------------------------------------------------------------------------1>From 表2>where 条件3>group by 列4>Having 筛选条件5>select 5-1>被分组列,聚合函数列5-2>distinct>5-3>top6>order by 列-----------------------------------------------------GROUP BY-----------------------------------------------------------------------------------当使用了分组语句(group by)或者是聚合函数的时候，在select的查询列表中不能再包含其他列名，--除了该列同时也出现了group by字句中，或者该列也包含了在某个聚合函数中分组查询语法:SELECT 被分组列, 聚合函数列 FROM 表名 WHERE 普通列 GROUP BY 被分组列 HAVING 被分组列, 聚合函数列 ORDER BY 被分组列, 聚合函数列----------------------------------------------------------------------------------------------------------------------------------------------update 表 set 列 (select 列 from 表)--子查询 delete from 表名 where....----delete语句如果不加where条件，表示将表中所有的数据删除，加上where条件后，会按照where条件进行删除对应的行insert into 表名(列1，列2，列3) values(值1，值2，值3)------------------------------------------------建表约束----------------------------------------------------------create table Employees(EmpId int identity(1,1) primary key,EmpName varchar(50) not null unique check(len(EmpName)>2),EmpGender char(2) default('男'),EmpAge int check(EmpAge>0 and EmpAge<120),EmpEmail varchar(100) unique,EmpAddress varchar(500) not nullEmpDepId int foreign key references Department(DepId) on delete cascade--on delete cascade级联删除)--------------------------------------------------------------------------------------------------------------------------------------Connection负责用来连接数据库ResultSet结果集PreparedStatement负责用来执行sql语句要用statement类的executeQuery()方法来下达select指令以查询数据库executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用第一步：加载驱动程序Class.forName(DRIVER);第二步：连接数据库Connection conn = DriverManager.getConnection(URL, "sa", "123456");第三步：发送sql语句(增删改)PreparedStatement pst = conn.prepareStatement(sql);第四步：执行查询ResultSet rs = st.executeQuery();while(rs.next()){System.out.println(rs.getString("studentName")+"|"+rs.getString("studentAge"));}备份数据库backup database 数据库名称 to disk='路径'恢复数据库restore detabase 数据库名称 from disk='备份路径'SQL server中的常用数据类型1.数字数据类型整数型bigint=longint=intsmallint=shotrtinyint=byte 0-255bit1~0小数型decimalnumeric两个一样货币型money2.字符串类型charncharvarcharnvarchartextntextvarchar(max)nvarchar(max)3.时间类型datetime4.二进制binary固定长度vninary可变长度-------------------------------------------------------带n的和不带n的区别char(2)表示可以储存两个字节。ab，12，胡(英文，数字1个字节，中文2个字节)nchar(2)表示无论存储中文还是英文，数字，每个字符都占用两个字节。ab，12，胡伟(英文，数字2个字节，中文2个字节)不带n的这些数据类型，长度最长可以设置为8000，带n的这些数据类型，长度最长可以设置为4000char(8000)varchar(8000)nchar(4000)nvarchar(4000)-------------------------------------------------------带var的和不带var的区别nchar不带var表示，固定长度varchar带var表示，可变长度//固定长度，存储1字符也是要占用10个字节的，会自动补9个空格char(10)110字节111111111110字节//可变长度，会根据实际储存数据的大小动态重新分配存储空间，相对来说节省存储空间varchar(10)11字节111115字节//10，表示最多10个字节，如果存储的数据超过了10个字节，那么无论是固定长度还是可变长度都会报错的。-------------------------------------------------------text淘汰了，等于varchar(max)ntext淘汰了，等于nvarchar(max)varchar(max)max表示4Gnvarchar(max)max表示4G-------------------------------------------------------数据库文件数据文件主要数据文件有且只有一个.mdf(primary data file 的缩写)次要数据文件0或者多个.ndf(primary data file 的缩写)日志文件无至少一个.ldf(log data file 的缩写)1. 字符串函数:CHARINDEXCHARINDEX('ab', 'cdab')返回 3返回'ab'在'cdab'中的位置，SUBSTRINGSUBSTRING('abc', 2, 2) 返回 'bc'从第2个位置开始截取长度为2的字符串LENLEN('1个逗逼') 返回 4返回字符串的长度(非字节长度)UPPER/LOWERUPPER('aBc')/LOWER('aBc') 返回'ABC'/'abc'将字符串中的字母转换为大写/小写LTRIM/RTRIMLTRIM('  abc')/RTRIM('abc  ') 返回'abc'/'abc'去掉字符串左边/右边的空格同时去掉两边空格 RTRIM(LTRIM('  abc  '))REPLACEREPLACE('abc', 'b', 'x') 返回 'axc'将'abc'中的'b'替换为'x'STUFFSTUFF('abcd', 1, 2, '你好') 返回 '你好cd'删除从第1个字符开始，长度为2的字符串，并插入 你好2. 日期函数GETDATEGETDATE()返回当前日期DATEADDDATEADD(mm, -2, GETDATE())返回当前日期-2月DATEDIFFDATEDIFF(dd, '1989-07-14', GETDATE())返回两个日期之间的间隔DATENAMEDATENAME(DW, GETDATE())返回 '星期几'以字符串形式返回当前日期指定的部分DATEPARTDATEPART(DW, GETDATE())返回 一个星期的第几天以整数形式返回当前日期指定的补分3. 数学函数CEILING/FLOORCEILING(24.1)/FLOOR(24.4) 返回 25/24返回大于24.1的最小整数、返回小于24.1的最大整数ROUNDROUND(748.35, 1) 返回 748.40四舍五入到小数点后1位4. 系统函数CONVERTCONVERT(VARCHAR(3), 123) 返回 '123'转换数据类型DATALENGTHDATALENGTH('1个逗逼') 返回 7返回任何数据类型的字节数，汉字2字节---------------------------------------------------------在这里编写SQL语句命令--1.创建一个数据库create database MyDatabaseOne--2.删除数据库drop database MyDatabaseOne--3.创建数据库的时候设置一些参数选项create database MyDatabaseOneon--指定主文件的属性(--配置主数据的选项name='MyDatabaseOne',--住数据文件的逻辑名称filename='E:\MySQLServerDatabase.mdf',--住数据文件的实际保存路径size=5MB,--初始化大小maxsize=200MB,--最大文件大小filegrowth=10%--每次增长)log on--指定日志文件的属性(--配置日志文件的选项name='MyDatabaseOne_log',--日志文件的逻辑名称filename='E:\MySQLServerDatabase.ldf',--日志文件的实际保存路径size=5MB,--日志文件的初始大小filegrowth=10%--每次增长)-------------------------在数据库中创建一个表---------------------------将代码环境切换到MyDatabaseOneUSEMyDatabaseOnecreate table Departments(Auto int identity(1,1)primary key,Departments nvarchar(50) not null,)--通过代码，删除master数据库下的某些表USE master--drop database --删除数据库drop table Table_1--删除表drop table Table_1 where 字句--删除表中某一条---创建一个员工表-----<员工表>：USEMyDatabaseOneGOcreate table Employees(EmpID int identity(1,1) primary key,//主键EmpIDCard varchar(18) not null,EmpName nvarchar(50) null,EmpGender bit not null,EmpJoinDate datetime,EmpAge int,EmpAddress nvarchar(300),EmpPhone varchar(100),DeptId int not null,EmpEmail varchar(100),)drop table Employeesuse MyDatabaseOnecreate table Employees(EmpID int identity(1,1) primary key,EmpIDCard varchar(18) not null,EmpName nvarchar(50) null,EmpGender bit not null,EmpJoinDate datetime,EmpAge int,EmpAddress nvarchar(300),EmpPhone varchar(100),DeptId int not null,EmpEmail varchar(100),)实体完整性：primary key//(主键)约束唯一识别每一条记录的标志，可以由多列共同组成**只能有一个，不许重复，不许为nullidentity//(自增)约束列值自增，一般使用此属性设置的列作为主键identtity(1,1)unique//(唯一)约束可以使用unique约束确保在非主键列中部存在重复值，但是值可以为null域完整性：check(检查)约束//用于限制列中的值的范围()foreign key(外键)约束//一个表中的foreign key指向另一个表中的primary keydefault(默认值)约束//用于向列种插入默认值default'中国'not null(非空)约束//用于强制列不接受null值引用完整性：引用完整性是指两个表的主键和外键的数据对应一致，它建立在外键和主键的关系之上，在sql server中，引用完整性的作用表现在一下3个方面1.禁止在子表中添加主表中不存在的记录2.禁止修改主表的值3.禁止删除子表中的有对应记录的主表记录用户自定义完整性：主要是规则rule、约束constraint和触发器triggerStudentNum int references Tb_Student(StudentNum)引入例子！-------------------------SQL语句入门-------------------------DDL(数据定义语言，建表，建库等语句)DML(数据操作语言)DCL(数据控制语言)SQL语句中字符串用单引号、单等号SQL语句不区分大小写(取决与排序规则)-----------------------------------------------------------------------向学生表中插入一条记录--insert into 表名(列1，列2，列3) values(值1，值2，值3)--1，自动编号列，默认就会自动增长，所以不需要(默认情况下也不能向自动编号列插入值)注意：如果字段类型为varchar或者datetime，则必须使用单引号引起来赋值与查看例子insert into Tb_Teacher(TeacherName,TeacherAge,TeacherSalary,TeacherTel)插入所有值(没省略)values('大蛇丸',25,999999,'15271000220')select * from Tb_Teacherinsert into Tb_Teacher(TeacherName,TeacherTel)插入部分值values('火神','22022022022')select * from Tb_Teacherinsert into Tb_Teachervalues('八神',25,88888,'11011011011')插入所有值(省略)select * from Tb_Teacher同时插入多行数据insert into Tb_Student--values('200709002','凯奇',21,'法国','2007-09-02',3000.5)//values插入值select '200709002','凯奇',21,'法国','2007-09-02',3000.5 union//select插入值(内容不需要())select '200709003','saha',25,'印度','2007-09-02',1000.5 unionselect '200709004','张小飞',21,'中国','2007-09-02',2000.5//最后一行不需要union-----------------------------------------------------------------------强行插入--启动自动编号列插入值--启动某个表的“自动编号列”手动插入值得功能set identity_insert Tb_Teacher oninsert into Tb_Teacher(TeacherID,TeacherName,TeacherTel)values(10086,'移动SB','10086')set identity_insert Tb_Teacher offselect * from Tb_Teacher--在SQL语句中的直接写的字符串中，如果包含中文，一定在字符串前面加N（因为当排序规则不是简体中文的时候会乱码）例子values(10086,N'移动SB','102016/7/13086')-----------------------------------------------------------------------打开和关闭查询结果窗口:ctrl+r-----------------------------------------------------------------------更新语句：--update 表名 set 列=新值，列2=新值，....where 条件--update语句如果不加where条件，那么表示对表中所有条件都进行修改，所以一定要加where条件select * from Tb_Teacherupdate Tb_Teacher set TeacherAge=TeacherAge+1,TeacherName = TeacherName+'(男)' where TeacherAge = 26update Tb_Teacher set Age=30 where Name='大蛇丸' or Age<25select * from Tb_Teacher--删除数据语句--delete from 表名 where....--delete语句如果不加where条件，表示将表中所有的数据删除，加上where条件后，会按照where条件进行删除--删除Tb_Teacher表中的所有数据，自动编号没有回复到默认，仍然继续编号delete from Tb_Teacherselect * from Tb_Teacherinsert into Tb_Teachervalues('胡伟',25,200,'15271100220')select * from Tb_Teacherinsert into Tb_Teachervalues('杨磊',26,300,'15271200220')insert into Tb_Teachervalues('小军',27,400,'15271300220')insert into Tb_Teachervalues('张衡',28,500,'15271400220')外键关系-级联，级联删除将先删除子表中的相应记录，再删除主表记录--truncate table 表名--如果要删除表中全部数据，那么建议使用truncate--truncate特点:--1.truncate语句不能跟where条件(无法根据条件来删除，只能全部删除)--2.truncate同时自动编号恢复到初始值--3.使用truncate删除表中的所有数据要比delete效率高的多的多。--4.truncate 删除数据，不触发触发器------------------------------------------------------------------------------------------------------------------------use mstanforddrop table Tb_Studentcreate table Tb_Student(StudentNo varchar(20) primary key,StudentName nvarchar(20) not null,StudentAge int not null check(StudentAge>=20 and StudentAge<=30),County nvarchar(20) not null default('中国'),StuTime datetime not null,Tuition money not null)select * from Tb_Studentupdate Tb_Student set StuTime='2009-09-01' --更新数据------------------------增加数据INSERT INTOselect * from Tb_Student_Couresinsert into Tb_Student_Coures values ('200709003','.net','2009-09-09','通过')--增加数据insert into Tb_Student_Coures values ('200709004','jap','2009-09-09','NULL')insert into Tb_Student_Coures values ('200709002','java','2009-09-09','通过')update Tb_Student_Coures set CouresTime='2008-08-08',Notes='通过'where StudentNo='200709003' --更具条件更新数据------------------------简单查询SELECTselect * from mstanford.dbo.Tb_Student --查询数据select studentno from mstanford.dbo.Tb_Student order by studentno desc --查询并且按降序排列 ASC(缩写ascending)表示升序 DESC(缩写descending)表示降序insert into mstanford.dbo.Tb_Student_Coures values('200709004','jsp','2008-08-08','取消考试')--增加数据insert into mstanford.dbo.Tb_Student_Coures values('200709004','java',2008-01-02,'NULL')select StudentNo,Notes from mstanford.dbo.Tb_Student_Coures--查询多列数据select * from mstanford.dbo.Tb_Student_Coures--查看所有数据简写select SCNo,StudentNo,CouresName,CouresTime,Notes from mstanford.dbo.Tb_Student_Coures--查看所有数据完整写select distinct studentno from mstanford.dbo.Tb_Student_Coures--distinct查看数据并且去掉重复select StudentNo as 学号,SCNo as 编号,CouresName as 课程名称,CouresTime as 课程时间,Notes as 备注 from mstanford.dbo.Tb_Student_Coures--查询使用别名------------------------排序查询ORDER BYupdate Tb_Student_Coures set CouresTime='2008-01-02' where SCNo='43'--更改select * from Tb_Student_Coures order by CouresTime DESC--查询所有某列降序select * from Tb_Student_Coures order by StudentNo desc,CouresTime desc--查询所有多列降序------------------------查询限定行TOP N PERCENTselect top 3 * from Tb_Student_Coures--查询表中前3行select top 1 percent * from Tb_Student_Coures--查询表中1%行(percent百分比)select top 50 percent * from Tb_Studentorder by StudentNo desc,Tuition asc--查询表中1%行(percent百分比)StudentNo 降序,Tuition 降序------------------------条件查询WHERE--逻辑运算符'NOT' 'AND''OR''IS NULL'返回TRUE'IS NOT NULL'返回FALSEselect * from mstanford.dbo.Tb_Student_Coures where Notes='通过'-------------单条件查询(使用比较运算符)select * from mstanford.dbo.Tb_Student_Coures where SCNo>=40-------------单条件查询(使用比较运算符)select * from mstanford.dbo.Tb_Student_Coures where SCNo>40 AND notes='通过'-------------多条件查询(使用逻辑运算符)select * from mstanford.dbo.Tb_Student_Coures where Notes is NULL-------------查询表中 Notes值NULL空的行select * from mstanford.dbo.Tb_Student_Coures where Notes is not null-------------查询表中 Notes值为NOT NULL的行select * from mstanford.dbo.Tb_Student_Coures where Notes != '通过'-------------查询表中 notes值不是'通过'的行(不检查值为NULL)的行select * from mstanford.dbo.Tb_Student_Coures where not Notes = '通过'-------------查询表中 notes值不是'通过'的行(不检查值为NULL)的行select * from mstanford.dbo.Tb_Student_Coures where (StudentNo='200709002' or StudentNo='200709003') and CouresName='.net'-------------理解运算优先级ADN > OR，()限制优先级------------------------SQL Server内置函数，可以与INSERT UPTATE DELETE等一起使用：1.字符串函数，2.日期函数，3.数字函数，4.系统函数select STUFF(PName,1,0,'拳皇_') as 名称,WeaponID,SkillID from DB_King_Fighters.dbo.player--使用stuff删除并且插入字符select upper(CouresName) as 课程名称 from mstanford.dbo.Tb_Student_Coures------------- 查询表中课程名称，并将小写转换成大写select * from mstanford.dbo.Tb_Student_Coures where len(CouresName)>3-------------查询表中课程名称大于3的选课信息select * from mstanford.dbo.Tb_Student_Coures where CouresTime<GETDATE()-------------查询选课日期再当前日期之前的选课信息select * from mstanford.dbo.Tb_Student_Coures where DATENAME(DW,CouresTime)='星期三'-------------查询选课为星期三的选课信息select CONVERT(int,SUBSTRING(StudentNo,1,2))+CONVERT(int,SUBSTRING(StudentNo,3,2)) as 学号 from mstanford.dbo.Tb_Student_Coures-------------理解substring,convert--CONVERT(int,SUBSTRING(StudentNo,1,2))--SUBSTRING(实例,截取开始位,截取结束位)select * from mstanford.dbo.Tb_Student_Couresupdate  mstanford.dbo.Tb_Student_Coures set CouresTime='2009-09-09',CouresName='jsp',StudentNo='200709004' where SCNo=43delete from mstanford.dbo.Tb_Student_Coures where SCNo=39--理解删除表中某一行select top 2 StudentNo,Notes from mstanford.dbo.Tb_Student_Coures where Notes='通过' order by StudentNo desc--理解查表行数，查表列数，查表条件，查表排序一起运用select * from mstanford.dbo.Tb_Student_Coures where SUBSTRING(CouresName,1,1)='j' and Notes is not null--理解is not null的用法，不能使用!= null------------------------------------------------------------LIKE 运算符------------------------------------------------------------通配符% 任意0个或者多个字符select ProductName,UnitPrice from Products where ProductName like 'c%'--通配符%表示任意字符的匹配(以C开头)select ProductName,UnitPrice from Products where ProductName like '%t'--(以T结尾的)select ProductName,UnitPrice from Products where ProductName like 'c%t'--(以c开头以t结尾)select ProductName,UnitPrice from Products where ProductName like '%t%'--(包含t)------------------------------------------------------------通配符_ 任意单个字符select ProductName,UnitPrice from Products where ProductName like 't_fu'select ProductName,UnitPrice from Products where ProductName like '_____'--产品名称长度是5个字符的产品select ProductName,UnitPrice from Products where ProductName like '_e%'--查询第二个字节为e的产品名称select ProductName,UnitPrice from Products where ProductName like '_a%' and QuantityPerUnit like '%pkgs%'------------------------------------------------------------通配符[] 指定一系列的字符，只要满足这些字符其中之一且出现在[]通配符的位置的字符串就满住查询条件select ProductName from Products where ProductName like '%[_]%'--名称中带有_的数据update Products set ProductName='abc_123' where ProductName='huwei_520' --更改数据select ProductName,UnitPrice from Products where ProductName like '%[abfg]'--名称最后一位是[abfg]的数据------------------------------------------------------------IN 运算符(相对于or，IN简捷，后面可以是SQL语句)select ProductName,SupplierID from Products where SupplierID=1 or SupplierID=4 or SupplierID=3select ProductName,SupplierID from Products where SupplierID in(1,3,4)--同上两句相等------------------------------------------------------------BETWEEN 运算符select ProductName,UnitPrice from Products where UnitPrice between 6 and 10 order by UnitPrice desc--unitprices价格在6-10之间的产品名称和单价数据,unitprices按降序排列,排序规则放在语句最后select LAStName,BirthDate from Employees where BirthDate between '1952-01-01' and '1960-01-01'--查询出生区间出生日期between------------------------------------------------------------聚合函数SUM MAX MIN AVG COUNT--DATE type 求min/max按照时间的先后排列的，日期越早月小，--CHAR type 求min/max按照搜首字母A-Z的顺序排列，越后越大--汉字 type 求min/max按照全拼拼音进行比较，若首字母形同则比下一个字符------------------------------------------------------------SUM聚合函数select top 10 SUM(UnitPrice) AS 前十价格之和 from Products--前十价格之和select * from Productsselect SUM(UnitPrice*Quantity) AS 所有商品价格和 from [Order Details] where OrderID='10249'--OrderID是10249所有商品价格的和------------------------------------------------------------MAX/MIN函数select MAX(UnitPrice) AS 最高价格产品 from Products--最高价格产品select MIN(BirthDate) AS 年纪最大员工 from Employees--年龄最大的员工生日------------------------------------------------------------AVG集函数select AVG(UnitPrice) AS 商品价格平均价 from Products------------------------------------------------------------COUNT函数select COUNT(ProductName) AS 商品个数 from Products--商品个数select COUNT(*) AS 记录数 from Products--查询所有记录数(包括空值)------------------------------------------------------------多聚合函数一起用select COUNT(*) AS 总记录数,AVG(UnitPrice) AS 平均价格, MAX(UnitPrice)AS 最高价格 from Products--多聚合函数的使用------------------------------------------------------------分组查询------------------------------------------------------------GROUP BY字句分组查询语法:SELECT 分组列, 聚合列 FROM 表名 WHERE 普通列 GROUP BY 分组列 HAVING 分组列, 聚合列 ORDER BY 分组列, 聚合列select EmployeeID,MIN(OrderDate) AS 每个员工最早订单时间 from Orders group by EmployeeID order by EmployeeID ASc--聚合函数与分组查询共用------------------------------------------------------------HAVING字句select EmployeeID,COUNT(*) AS 订单数量 from Orders group by EmployeeID having COUNT(*)>100select EmployeeID,COUNT(*) AS 订单数量 from Orders group by EmployeeID having COUNT(*)>100 and EmployeeID>2--和下一句对比，如果不是判断结果集，那么可以用下面的写法select EmployeeID,COUNT(*) AS 订单数量 from Orders where EmployeeID>2 group by EmployeeID having COUNT(*)>100select * from Orders------------------------------------------------------------作业1select FirstName,LAStName,HomePhone from Employees where HomePhone like '(%)_5%122' --%_用法select count(ProductName) AS 商品数, avg(unitprice) AS 平均价格,SUM(unitprice) AS 单价和, max(unitprice) AS 最高价, min(unitprice) AS 最低价 from Products--聚合函数使用select ProductID AS 产品编号, MAX(UnitPrice*Quantity) AS 订单额 from [Order Details] where ProductID>70 and OrderID>11020 GROUP BY ProductID order by MAX(UnitPrice*Quantity) desc--聚合函数 group by的使用------------------------------------------------------------上机 1模糊查询select ProductName,UnitPrice from Products where ProductName like 'c_[a-f][^g-z]%'select * from ProductsUPDATE Products set ProductName='[杨磊牛逼]' where ProductID=1select ProductName,UnitPrice from Products where ProductName like '%[%]%'--包含%的产品名称和单价select ProductName from Products where ProductName like '%[_]%'select ProductName from Products where ProductName like '%[[]%]%'select * from Employees where (City='london' or City='kirkland' or City='seattle') and HomePhone like '%2'--和下面的一样select * from Employees where City in ('london','kirkland','seattle') and HomePhone like '%2'------------------------------------------------------------2聚合函数select AVG(datediff(YY,BirthDate,GETDATE())) AS 平均年纪,MAX(datediff(YY,BirthDate,GETDATE())) AS 最大年纪 from Employeesselect COUNT(*) AS 记录次数,COUNT(Region) AS Region字段值的个数 from Employees--计算记录次数和Region not is null 的次数select * from Ordersselect CustomerID,OrderID from Orders where OrderID>11011 and EmployeeID>2select CustomerID,COUNT(*) as 订单数量 from Orders where OrderID>11011 group by CustomerID having COUNT(*)>2select * from Customersselect Country as 国家,COUNT(CustomerID) as 客户数量 from Customers group by Countryselect Country as 国家,COUNT(CustomerID) as 客户数量,CompanyName as 公司名称名称 from Customers where CompanyName like 'b%' group by Country,CompanyNameselect Country as 国家,COUNT(CustomerID) as 客户数量,CompanyName as 客户公司名称,Country as 国家 from Customers where CompanyName like 'b%' and LEN(Country) between 5 and 10  group by Country,CompanyName,Country--理解集合函数，between运算符group by的用法------------------------------------------------------------表的基本连接------------------------------------------------------------两表连接use Northwind--查询属于beverages和condiments类的商品名，切商品名以'c'开头select Categories.CategoryID,Categories.CategoryName,--种类ID，种类名称Products.CategoryID,Products.ProductName--商品.种类ID，商品.商品名称from Categories,Productswhere Categories.CategoryID=Products.CategoryID--两张表的连接条件and CategoryName in('beverages','condiments')--查询类别(查询这两个种类)and ProductName like 'c%'--select TB_A.A,TB_B.C FROM TB_A,TB_B WHERE TB_A.C=TB_B.Cselect * from Categoriesselect * from Productsselect * from [Order Details]--TB_A 表和 --TB_B都存在字段C，所以在select语句中使用该字段时，一定要知名其所在的表，如TB_A.C、TB_B.C，其他的充满字段要需要进行同样的处理，否则数据库系统会报错--使用sel server关键字作为表名，列名的时候，需要使用“[]”包括起来，例如create table [order]--select语句首先执行from字句，由于定义表别名是在from字句中执行，而在其他子句中使用，所以在select语句的任何子句中都可以使用表的别名select c.CategoryID,c.CategoryName,p.CategoryID,p.ProductName,o.OrderID from Categories as c,Products as p,[Order Details] as owhere c.CategoryID=c.CategoryIDand p.ProductID=o.ProductIDand CategoryName in('beverage','condiments')and ProductName like 'c%'and o.OrderID>1060------------------------------------------------------------内连接--内连接也称为等同连接，返回的结果是两个表中所有相匹配的数据，舍弃不匹配的数据select * from Productsselect * from Categoriesselect Categories.CategoryID as 种类ID,Categories.CategoryName as 种类名称,Products.CategoryID as 种类ID,Products.ProductName as 种类名称from Categories join Productson Categories.CategoryID=Products.CategoryIDwhere CategoryName in('beverages','condiments')and ProductName like 'c%'select * from Customersselect * from Ordersselect kh.CompanyName as 客户公司,kh.ContactName as 客户名字,kh.Phone as 客户电话,dd.OrderID as 订单编号,dd.OrderDate as 订单日期from Customers as kh,Orders as ddwhere kh.CustomerID=dd.CustomerIDorder by 订单编号 select kh.CompanyName as 客户公司,kh.ContactName as 客户名字,kh.Phone as 客户电话,dd.OrderID as 订单编号,dd.OrderDate as 订单日期from Customers as kh left join Orders as ddon kh.CustomerID=dd.CustomerIDorder by 订单编号------------------------------------------------------------外连接select * from Customersselect * from Employeesselect kh.City as 客户所在城市,yg.FirstName+yg.LastName as 员工姓名,kh.ContactName as 客户姓名from Employees as yg right join Customers as khon kh.City=yg.City--全外部链接full on...onselect * from Customersselect * from Ordersselect Orders.OrderID 订单编号,Orders.OrderDate 订单日期,Customers.CompanyName 客户公司from Customers full join Orderson Customers.CustomerID=Orders.CustomerID-----------------------------------------------------------SQL SERVER执行顺序--SELECT...--FROM...--WHERE...--GROUP BY...--HAVING...--ORDER BY...--查询供货商的公司名称和所供应的商品名称select * from Suppliersselect * from Productsselect * from [Order Details]select s.CompanyName,p.ProductNamefrom Suppliers s,Products pwhere s.SupplierID=p.SupplierIDselect s.CompanyName,p.ProductNamefrom Suppliers s join Products pon s.SupplierID=p.SupplierIDselect s.CompanyName,p.ProductName,o.OrderIDfrom Suppliers s,Products p,[Order Details] owhere s.SupplierID=p.ProductID and p.ProductID=o.ProductIDselect Suppliers.CompanyName,Products.ProductName,[Order Details].OrderIDfrom Suppliers join Products on Suppliers.SupplierID = Products.SupplierID join [Order Details] on Products.ProductID = [Order Details].ProductIDselect * from Customersselect * from Suppliersselect c.CompanyName 客户姓名,c.[Address] 客户地址,s.CompanyName 供货商公司,s.ContactName 供货商联系人from Customers c left join Suppliers son c.City=s.Cityselect * from Ordersselect * from Employeesselect * from Customersselect Orders.OrderID as 订单编号,Employees.FirstName+Employees.LastName as 负责人姓名,Customers.CompanyName as 下订单公司名称from Employees right join Orders on Orders.EmployeeID=Employees.EmployeeID left join Customers on Orders.CustomerID=Customers.CustomerID---------------------------------------------------------------------------第二阶段1、数据库设计阶段需求分析：收集信息概念设计：标识实体、标识属性、标识关系-》E-R图[实体(Entity)关系(Relationship)图] 软件vision逻辑设计：E-R图转换成相应的表并通过3大范式进行审核物理设计：选择合适物理实现实施：运行和维护：范式(Normal Formate)NF第一范式确保每一列的原子性，不可再拆分第二范式除了主键外，所有的列都依赖于主键，并且没有一个实体是组合主键不符合第二范式会有问题1数据冗余2跟新异常3插入异常4删除异常第三范式非关键字对非主键的间接函数依赖ER模型E-R模型图实体(Entity)关系(Relationship)图visionPowerDesigner概念数据模型PhysicalDiagram物理图实体：实体是现实生活中区别于其他事物，具有自己属性的对象，同一类实体的所有实例就是构成该对象的实体集。属性：属性是实体的特征关系：实体之间存在的联系关系分类1对1关联1:1属于班级辅导员一个班级只有一个辅导员一个辅导员只负责一个班级1对多关联1:N拥有班级学生一个班级有多个学生多个学生属于一个班级多对1关联N:1办事处与员工之间是一对多的关系，反之员工与办事处之间就是多对一的映射基础多对多关联N:M学生教师一个学生有多名老师(语文数学)一个老师教多名学生部门中拥有很多员工（1:N）一个员工可以管理一个部门(1:1)办事处中拥有很多员工(1:N)员工可以存在很多技能(M:N)映射基数：1：1（员工管理部门）1：N（部门拥有员工）N：1（员工属于部门）M:N（员工和技能）--------------------------------------------------------------------------------------------------变量 运算符 批处理语句 流程控制语句全局变量@@ERROR返回执行的上一个语句的错误号@@IDENTITY返回最后插入的标识值@@ROWCOUNT返回受上一语句影响的行数@@SERVERNAME返回运行 SQL Server 的本地服务器的名称@@MAX_CONNECTIONS返回允许同时进行的最大用户连接数print @@version局部变量：由用户定义的变量，其作用域在定义它的代码块中（指存储过程，函数，匿名的T—SQL代码块），一般是在两个GO之间局部变量：以@开头，由用户定义，先声明再赋值最后使用声明：declare @变量名 数据类型声明并赋值：declare @变量名 数据类型=值给变量赋值：set @变量名 = 值select @变量名 = 值select@变量名 = 列名 from 表使用变量：打印变量：print  @变量名select @变量名select @变量名 as 别名set将已经确定的常量赋值给局部变量select将从数据库中查询的结果赋值给局部变量go语句的作用：go前面的语句执行完之后，才会执行其后的代码作为批处理语句的结束，go之前声明的变量，在go之后不能使用--------------------------------------------------------------------------------使用T-SQL编程create database Company_DBon(name='Company_DB',filename='E:\MySQLServerDatabase\Company_DB.mdf')log on(name='Company_DB_log',filename='E:\MySQLServerDatabase\Company_DB.ldf')create table Office(OfficeCode nvarchar(20) primary key,--办公司代号OfficeAddress nvarchar(100) not null unique--办公司地址)create table Department(DeptNo nvarchar(20) primary key, --部门代号DeptName nvarchar(20) not null unique,--部门名称Principal nvarchar(20) --部门负责人)create table Employee(EmpNo nvarchar(20) primary key,--员工号EmpName nvarchar(20) not null,--员工名字EmpAddress nvarchar(100) not null,--员工地址EmpPhone varchar(12) not null,--员工电话Birthday datetime not null,--员工生日HireDate datetime not null,--入职时间DeptNo nvarchar(20) foreign key references Department(DeptNo),--外键OfficeCode nvarchar(20) foreign key references Office(OfficeCode)--外键)insert into Officevalues ('C01','中山北路200号')insert into Officevalues ('C02','北京中路35号')insert into Officevalues ('C03','福州路10号')insert into Department(DeptNo,DeptName)values ('D01','技术部')insert into Department(DeptNo,DeptName)values ('D02','市场部')insert into Department(DeptNo,DeptName)values ('D03','行政部')insert into Employeevalues ('E001','Tom','凯撒大厦501','021-45364743','1980-10-02','2004-04-06','D01','C01')insert into Employeevalues ('E002','Jack','凯撒大厦502','021-45364743','1980-01-03','2004-05-16','D01','C01')insert into Employeevalues ('E003','White','凯撒大厦503','021-45364743','1980-01-04','2003-05-06','D02','C01')insert into Employeevalues ('E004','Smith','凯撒大厦504','021-45364743','1980-05-02','2000-05-22','D02','C02')insert into Employeevalues ('E005','John','凯撒大厦505','021-45364743','1980-06-01','2004-02-13','D02','C02')insert into Employeevalues ('E006','Slider','凯撒大厦506','021-45364743','1982-08-02','2005-03-12','D03','C02')insert into Employeevalues ('E007','Buth','凯撒大厦507','021-45364743','1983-02-12','2006-05-06','D03','C02')insert into Employeevalues ('E008','Jennifer','凯撒大厦508','021-45364743','1980-11-02','2004-05-16','D01','C03')insert into Employeevalues ('E009','Kelly','凯撒大厦509','021-45364743','1980-01-23','2004-05-22','D01','C03')insert into Employeevalues ('E010','Winston','凯撒大厦510','021-45364743','1978-10-02','2002-09-06','D01','C03')insert into Employeevalues ('E011','Joy','凯撒大厦511','021-45364743','1980-11-12','2004-12-06','D01','C03')drop table Officedrop table Departmentdrop table Employeeselect * from Officeselect * from Departmentselect * from Employeeuse Company_DBdeclare @DeptNo nvarchar(10)='D01'--声明变量并赋值select @DeptNodeclare @EmpNo nvarchar(10)='胡伟'--声明变量print @EmpNo--------------------------------------------------------------------------------use Company_DBgo--查询Dept='D10'的员工号declare @DeptNo nvarchar(10)='D01'select EmpNo from Employee where DeptNo=@DeptNo--把查询的EmpNo的查询结果最后一条记录值赋值给局部变量@EmpNodeclare @EmpNo nvarchar(10) --定义变量declare @DeptNo nvarchar(10)='D01'--定义点亮并且赋值select @EmpNo=EmpNo from Employee where DeptNo=@DeptNoselect @EmpNo----------------------------------------------------------------------------------找出一部门中工龄最大的员工（按照工龄升序排列,自动取最后一条数据赋值）declare @EmpNo nvarchar(10)declare @DeptNo nvarchar(10)='D01'select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo group by EmpNo order by MAX(DATEDIFF(yyyy,hiredate,getdate())) asc print @EmpNo--输出显示--更新部门负责人               E010                'D01'update Department set principal = @EmpNo where DeptNo = @DeptNo--找出二部门中工龄最大的员工(按照工龄升序，自动取最后一条数据赋值)set @DeptNo='D02'--为变量@DeptNo重新复制select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo group by EmpNo order by MAX(DATEDIFF(yyyy,hiredate,getdate()))print @EmpNoupdate Department set Principal=@EmpNo where DeptNo=@DeptNo--找出三部门中工龄最大的员工(按照工龄升序，自动取最后一条数据赋值)set @DeptNo='D03'select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo group by EmpNo order by MAX(DATEDIFF(yyyy,hiredate,getdate()))print @EmpNoupdate Department set Principal=@EmpNo where DeptNo=@DeptNogoselect * from Departmentgoprint '错误号码'+convert(nvarchar(225),@@errorselect @@VERSION as '版本代号'------------------------------------------------运算符-----------------------------------declare @OptNumber int=12436--print '转换之前'+@OptNumber 失败int要想和varchar格式一起打印需要把int转换成varcharprint '转换前：'+convert(varchar(5),@optNumber)declare @reverse varchar(5)--取除个位数 declare @unit int = @OptNumber%10--6set @reverse=(CONVERT(varchar(1),@unit))--6赋值给倒序变量，赋值顺序左→右print '逆序number:'+@reverse--去掉个位数，变量OptNumber将变成1243set @OptNumber=@OptNumber/10--1243set @unit=@OptNumber%10--3set @reverse=@reverse+CONVERT(varchar(1),@unit)print '逆序number:'+@reverseset @OptNumber=@OptNumber/10--124set @unit=@OptNumber%10--4set @reverse=@reverse+CONVERT(varchar(1),@unit)print '逆序number:'+@reverseset @OptNumber=@OptNumber/10--12set @unit=@OptNumber%10--2set @reverse=@reverse+CONVERT(varchar(1),@unit)print '逆序number:'+@reverseset @OptNumber=@OptNumber/10--1set @unit=@OptNumber%10--1set @reverse=@reverse+CONVERT(varchar(1),@unit)print '逆序number:'+@reverse-----------------求1，2，3........100之间的奇数和declare @tableNum table(Num int)--虽然求奇数不需要table类型变量，但是为了演示位运算符，所以定义table类型变量以便查询declare @loop int =1while @loop<=100--循环次数begininsert into @tableNum values(@loop)set @loop=@loop+1endselect SUM(num) from @tableNum where Num & 1=1-----练习declare @table table(num int)declare @i int =1while @i<=100begininsert into @table values (@i)set @i=@i+1endselect sum(num) from @table where num % 2=1------------------------------ALL关键字------------------------ALL：将特定值与查询的结果集中所有的数据进行比较，若结果集中数据都满足该条件则返回结果为true，否则结果为false--语法：--特定值 比较运算符 ALL(查询语句)create table Expertise(SKillName nvarchar(20),SKillLevel int,EmpNo nvarchar(20))insert into Expertise values('C','2','E010')insert into Expertise values('C','1','E03')insert into Expertise values('C','2','E02')insert into Expertise values('C','3','E008')insert into Expertise values('C','2','E005')insert into Expertise values('C#','4','E006')insert into Expertise values('C#','3','E007')insert into Expertise values('DELPHI','1','E009')insert into Expertise values('JAVA','2','E004')SELECT * FROM ExpertiseIF 2<=ALL(SELECT SKillLevel FROM Expertise)PRINT '全部员工技能都通过了2级'ELSEPRINT '有员工技能不达标，还需要培训'-------------------------------ANY关键字-----------------------ANY：将特定值与查询的结果集中所有的数据进行比较，若结果集中任意一个数据满足该条件则返回结果为true，否则结果为false--语法：--特定值 比较运算符 ANY(查询语句)select * from Expertise--if 0<=ANY(SELECT SKillLevel FROM Expertise)if 2<=ANY(SELECT SKillLevel FROM Expertise where EmpNo='E010')PRINT '已经有员工技能超过了2级'ELSEPRINT '全部员工的技能不达标，需要加强练习'-------------------------------EXISTS关键字-----------------------Exists：判断查询的结果集中是否存在数据，若存在数据，则结果为true，否则结果为false--注意：可以使用not existsselect * from Employeeif Exists (SELECT * FROM Employee WHERE EMPADDRESS IS NULL)PRINT '有员工没有填写住址'ELSEPRINT '全部都填写住址了'----------------------------------------------------SQL流程控制语句---------------BEGIN-END--作用：相当于JAVA,C#中{}--注意--当语句块中语句多于一句时，需要使用begin-end--degin-end之间必须只有存在一条语句create table Salary(Id int identity(1,1) primary key,EmpNo NVARCHAR(20) FOREIGN KEY references Employee(EmpNo),Salary money,StartTime datetime)select * from Salaryselect * from Expertiseselect * from Officeselect * from Departmentselect * from Employeeinsert into Salary values ('E001','5000','2004-04-06 00:00:00')insert into Salary values ('E002','6500','2004-05-16 00:00:00')insert into Salary values ('E003','5500','2003-05-06 00:00:00')insert into Salary values ('E004','7200','2000-05-22 00:00:00')insert into Salary values ('E005','5000','2004-02-13 00:00:00')insert into Salary values ('E006','8000','2005-03-12 00:00:00')insert into Salary values ('E007','11000','2006-05-06 00:00:00')insert into Salary values ('E008','4800','2004-05-16 00:00:00')insert into Salary values ('E009','6700','2004-05-22 00:00:00')insert into Salary values ('E010','8000','2002-09-06 00:00:00')insert into Salary values ('E011','9500','2004-12-06 00:00:00')--如果某个员工的技能等级已经达到4级以上且最高，给这个员工增加基本工资500declare @emp nvarchar(10)declare @level intdeclare @salary money--查询最高技能等级的员工select @emp=EmpNo from Expertise order by SKillLevel print @emp--E006select @level=MAX(SKillLevel) from Expertiseprint @level--4if @level>=4beginselect @salary=Salary from Salary where EmpNo=@empinsert into Salary values (@emp,@salary+500,GETDATE())endGO-------------------------------------IF ELSE 条件语句-------------------------------------/**if-else作用：进行条件判断工作原理：条件成立执行if语句块，不成立else语句块注意：else子句可以省略在if，else子句均可以嵌套if-else结构*/declare @worktime intdeclare @emp nvarchar(10)declare @salary moneyselect @emp=EmpNo,@worktime=DATEDIFF(YYYY,hireDate,GETDATE()) from Employee order by DATEDIFF(YYYY,hireDate,GETDATE()) ascprint @empif @worktime>=4beginselect @salary=Salary from Salary where EmpNo=@empinsert into Salary values (@emp,@salary+1000,GETDATE())endselect * from Salary-------------------------------------WHILE 语句-------------------------------------/*while作用：进行循环工作原理：先判断后执行**/select * from Expertisedeclare @count intwhile(1=1)--无限循环beginselect @count=COUNT(*) from Expertise where SKillName like '%c%' and SKillLevel<3if(@count>0)beginupdate Expertise set SKillLevel+=1 where SKillName like '%c%' and SKillLevel<3endelsebreak--没有员工C语言技能低于3级后就退出循环end----------------------------------CASE 分支语句--------------------------------------------------/*case-end作用：类似多重条件结构，用于进行多路分支case 字段名when 值1 then 返回值1when 值2 then 返回值2...else 返回值nend工作原理：将字段的值逐一与when语句之后的值进行匹配，若存在匹配项，则返回then之后值，若不存在匹配项，则返回else之后返回值，其中else子句可以省略case when 条件1 then 返回值1when 条件2 then 返回值2...else 返回值nend工作原理：将逐一判断when语句之后的条件，若条件为真，则返回then之后值，若条件为假，则返回else之后返回值，其中else子句可以省略*/create table TimeWork(Id int identity(1,1) primary key,EmpNo nvarchar(10),WorkState nvarchar(20),WorkDate datetime)go--创建表insert into TimeWork values ('E001','病假','2008-10-11')insert into TimeWork values ('E001','调休','2008-10-10')insert into TimeWork values ('E001','正常上班','2008-10-08')insert into TimeWork values ('E001','正常上班','2008-10-09')insert into TimeWork values ('E001','正常上班','2008-10-12')insert into TimeWork values ('E002','加班','2008-10-11')insert into TimeWork values ('E002','休年假','2008-10-08')insert into TimeWork values ('E002','休年假','2008-10-09')insert into TimeWork values ('E002','正常上班','2008-10-10')insert into TimeWork values ('E003','病假','2008-10-08')insert into TimeWork values ('E003','正常上班','2008-10-09')insert into TimeWork values ('E003','正常上班','2008-10-10')insert into TimeWork values ('E003','正常上班','2008-10-11')insert into TimeWork values ('E004','请假','2008-10-11')insert into TimeWork values ('E004','休息','2008-10-08')insert into TimeWork values ('E004','正常上班','2008-10-09')insert into TimeWork values ('E004','正常上班','2008-10-10')insert into TimeWork values ('E005','调休','2008-10-10')insert into TimeWork values ('E005','正常上班','2008-10-08')insert into TimeWork values ('E005','正常上班','2008-10-09')insert into TimeWork values ('E005','正常上班','2008-10-11')goselect * from TimeWorkdrop table TimeWorkselect WorkDate as 日期,COUNT(case WorkState when '病假' then '0' end) as 病假,COUNT(case WorkState when '调休' then '0' end) as 调休,COUNT(case WorkState when '请假' then '0' end) as 请假,COUNT(case WorkState when '正常上班' then '0' end) as 正常上班,COUNT(case WorkState when '加班' then '0' end) as 加班,COUNT(case WorkState when '休年假' then '0' end) as 休年假,COUNT(case WorkState when '休息' then '0' end) as 休息,COUNT(case WorkState when '其他' then '0' end) as 其他from TimeWork group by WorkDate order by WorkDate descselect * from TimeWork select WorkDate,病假=sum(case workstate when '病假' then 1 else 0 end),调休=sum(case workstate when '调休' then 1 else 0 end),请假=sum(case workstate when '请假' then 1 else 0 end),   正常上班=sum(case workstate when '正常上班' then 1 else 0 end),加班=sum(case workstate when '加班' then 1 else 0 end), 休年假=sum(case workstate when '休年假' then 1 else 0 end),其他=sum(case workstate when '其他' then 1 else 0 end)from TimeWorkgroup by workdate order by workdate desc----------------------------------RETURN 语句--------------------------------------------------/*break：跳出循环结构return：跳出当前的批处理，而进入下一个批处理的执行goto：必须和label一起配合使用，跳转到相应label标签处*/declare @num int=0while(1=1)beginset @num+=1if(@num>10)RETURNPRINT @numendgoselect @@CONNECTIONS as '连接数量'go----------------------------------GOTO 语句--------------------------------------------------SELECT * FROM Expertisedeclare @num1 intdeclare @num2 intLABEL1:PRINT '最高的技能级别就是6级了，不能再升级了'WHILE(0=0)BEGINSELECT @num1 =COUNT(*) FROM Expertise WHERE SKillLevel<2IF(@num1>0)BEGINUPDATE Expertise SET SKillLevel=SKillLevel+1SELECT @num2=COUNT(*) FROM Expertise WHERE SKillLevel>6--查看Expertise表中SKillLevel列有几行大于6的数据并且把这个数据赋值给@num2IF(@num2>0)--如果@num2>0,BEGINGOTO LABEL1--跳转到锚点LABEL1执行ENDENDENDPRINT @@IDENTITY--------------------------------------课后操作题-----------------------------------------------create table orders(OrdersID nvarchar(100),ProductID nvarchar(100),[Date]datetime,Number int,[Money] int)insert into orders values ('SD-90102001','HW03202','1990-10-20 03:20:00','5','340')insert into orders values ('SD-90112001','HW03212','1990-11-12 10:22:00','10','1880')insert into orders values ('SD-90112001','HW03205','1990-11-24 12:25:35','30','2400')insert into orders values ('SD-90102001','HW03211','1990-10-12 05:06:23','20','500')insert into orders values ('SD-90102002','HW03211','1990-10-15 06:38:36','10','250')insert into orders values ('SD-90082002','HW03212','1990-08-24 11:39:09','5','950')insert into orders values ('SD-90082003','HW03202','1990-08-26 10:21:17','5','340')insert into orders values ('SD-90052003','HW03223','1990-05-01 11:45:18','10','240')insert into orders values ('SD-90062003','HW03224','1990-06-01 05:40:54','20','5000')insert into orders values ('SD-90102003','HW03223','1990-10-17 06:26:25','5','350')insert into orders values ('SD-90012005','HW03212','1990-01-08 07:28:22','7','1300')insert into orders values ('SD-90022005','HW03223','1990-02-02 08:05:02','10','700')insert into orders values ('SD-90012005','HW03202','1990-01-07 11:11:08','5','340')insert into orders values ('SD-90062005','HW03202','1990-06-22 12:17:30','5','340')drop table ordersSELECT * FROM orders-------------一下两张表union all的表select商品编号=ProductID,销售详情='销售数量',[1月]=sum(case datepart(month,[Date])when 1 then Numberelse 0end),[2月]=sum(case datepart(month,[Date])when 2 then Numberelse 0end),[3月]=sum(case datepart(month,[Date])when 3 then Numberelse 0end),[4月]=sum(case datepart(month,[Date])when 4 then Numberelse 0end),[5月]=sum(case datepart(month,[Date])when 5 then Numberelse 0end),[6月]=sum(case datepart(month,[Date])when 6 then Numberelse 0end),[7月]=sum(case when datepart(month,[Date])=7 then Numberelse 0end),[8月]=sum(case when datepart(month,[Date])=8 then Numberelse 0end),[9月]=sum(case when datepart(month,[Date])=9 then Numberelse 0end),[10月]=sum(case when datepart(month,[Date])=10 then Numberelse 0end),[11月]=sum(case when datepart(month,[Date])=11 then Numberelse 0end),[12月]=sum(case when datepart(month,[Date])=12 then Numberelse 0end)from ordersgroup by ProductIDunion allselect商品编号=ProductID,销售金额='销售金额',[1月]=sum(case datepart(month,[Date])when 1 then [money]else 0end),[2月]=sum(case datepart(month,[Date])when 2 then [money]else 0end),[3月]=sum(case datepart(month,[Date])when 3 then [money]else 0end),[4月]=sum(case datepart(month,[Date])when 4 then [money]else 0end),[5月]=sum(case datepart(month,[Date])when 5 then [money]else 0end),[6月]=sum(case datepart(month,[Date])when 6 then [money]else 0end),[7月]=sum(case when datepart(month,[Date])=7 then [money]else 0end),[8月]=sum(case when datepart(month,[Date])=8 then [money]else 0end),[9月]=sum(case when datepart(month,[Date])=9 then [money]else 0end),[10月]=sum(case when datepart(month,[Date])=10 then [money]else 0end),[11月]=sum(case when datepart(month,[Date])=11 then [money]else 0end),[12月]=sum(case when datepart(month,[Date])=12 then [money]else 0end)from ordersgroup by ProductID----------------一下答案，参考上面容易理解cselect商品编号=ProductID,[1月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 1 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 1 then [money]else 0end)),[2月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 2 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 2 then [money]else 0end)),[3月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 3 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 3 then [money]else 0end)),[4月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 4 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 4 then [money]else 0end)),[5月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 5 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 5 then [money]else 0end)),[6月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 6 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 6 then [money]else 0end)),[7月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 7 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 7 then [money]else 0end)),[8月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 8 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 8 then [money]else 0end)),[9月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 9 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 9 then [money]else 0end)),[10月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 10 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 10then [money]else 0end)),[11月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 11 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 11 then [money]else 0end)),[12月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])when 12 then Numberelse 0end))+'    '+'金额'+convert(varchar,sum(case datepart(month,[Date])when 12 then [money]else 0end))from ordersgroup by ProductID--查看某个时间的月份select datepart(month,[Date]) from orders where [date]='1990-10-20 03:20:00'--上机阶段1---------------------------变量的定义和使用/*用户状态：待审核，已审核，被屏蔽用户角色：待审核会员，普通会员，VIP会员，超级管理员用户在刚注册时状态为待审核，角色是待审核会员，管理员进行确认和管理*/create table UserState(Id int identity(1,1) primary key,[State] nvarchar(100))create table RoleInf(Id int identity(1,1) primary key,Rolename nvarchar(100),Roledesc nvarchar(100),Discount DECIMAL(3,2))create table UserInf(UserName nvarchar(50),[PassWord] varchar(50),RealName nvarchar(50),Gender nvarchar(10),IDCardNo varchar(20),Email varchar(100),Phone varchar(11),[Address] nvarchar(200),Balance money,UserStateId int foreign key REFERENCES UserState(id),UserRoleId int foreign key REFERENCES RoleInf(id),PicUrl varchar(200))drop table UserStatedrop table RoleInfdrop table UserInfselect * from UserStateselect * from RoleInfselect * from UserInf--阶段1--指导部分/*用户状态：待审核、已审核、被屏蔽用户角色：待审核会员、普通会员、VIP会员、超级管理员用户在刚注册时状态为待审核，角色是待审核会员。管理员进行确认和管理*/declare @stateid int,@roleid intinsert into UserState values('待审核')select @stateid=@@IDENTITY--使用select为局部变量赋值//当userstate用户状态表中添加数据以后@stateid数据会递增改变insert into RoleInf values('待审核会员','','1')set @roleid=@@IDENTITY--使用set为局部变量赋值--使用insert select union 语句多行插入insert into userinfselect 'tom123','123456','tom','1','321032198008152919','[e-mail]','','','1000',@stateid,@roleid,'d:\prt\images\tom.jpg' union allselect 'jack123','admin123','jack','1','321032198612126746','[e-mail]','','','1000',@stateid,@roleid,'d:\prt\images\jack.jpg' union allselect 'Slider123','as123','Slider','1','321032198208213232','[e-mail]','','','1000',@stateid,@roleid,'d:\prt\images\Slider.jpg' union allselect 'Micky123','888888','Micky','1','321032198910102322','[e-mail]','','','1000',@stateid,@roleid,'d:\prt\images\Micky.jpg' GOselect * from UserStateselect * from RoleInfselect * from UserInfGOdrop table UserStatedrop table RoleInfdrop table UserInf--练习部分，向UserState、RoleInf表中插入新的数据,分别是用户状态数据和用户数据insert into UserState values ('已审核')insert into UserState values ('被屏蔽')insert into RoleInf values ('普通会员','','0.9')insert into RoleInf values ('VIP会员','','0.8')insert into RoleInf values ('超级管理员','','0.7')--用户表添加一个新数据用于//测试数据insert into UserInf values ('大神','84000508','胡伟','1','420621198909193414','[e-mail]','','','1000',1,1,'d:\prt\images\Micky.jpg')select * from UserInfdelete from UserInf where username='大神'--审核用户大神//把大神的UserState值修改成2('已审核')update UserInf set Userstateid=2 where username='大神'---------------------------检查用户是否已审核，如果是已审核用户把用户userroleid改成普通会员declare @count intdeclare @username nvarchar(20)='大神'declare @pwd nvarchar(20)='84000508'select @count=COUNT(*) from userinf where username=@username and[password]=@pwd and userstateid=2if(@count>0)beginupdate UserInf set Userroleid=2 where username='大神'print'登陆成功！欢迎'+@username+'您已经通过审核,已经成为普通会员'endelseprint'登陆失败'go--阶段2---------------------------IF ELSE和WHILE流程控制语句create table CardType(Id int identity(1,1) primary key,CardtypeName varchar(20),ImageUrl nvarchar(100),Price money )create table CardState(Id int identity(1,1) primary key,cstate varchar(20))create table CardInf(CardNo varchar(20),CardPwd varchar(20),CardtypeId int foreign key references CardType(Id),CardStateId int foreign key references CardState(Id),CardDesc varchar(100),validityTime datetime)drop table CardInf drop table CardType drop table CardStateselect * from CardStateselect * from CardTypeselect * from Cardinf--添加游戏卡状态和分类GOdeclare @stateid int=1declare @cardtype1 int,@cardtype2 int,@cardtype3 int,@cardtype4 intdeclare @cnt int=0insert into cardstate values('未售出')--保存Id在后续代码中使用set @stateid=@@identityPRINT '此时 CardState 标识列ID为：@stateid = ' + CONVERT(VARCHAR(20), @@IDENTITY)insert into cardstate values('已售出')insert into CardType values('泡泡堂游戏卡','',100)set @cardtype1=@@identityPRINT '此时 CardType 标识列ID为：@cardtype1 = ' + CONVERT(VARCHAR(20), @@IDENTITY)insert into cardtype values('传奇游戏卡','',50)set @cardtype2=@@IDENTITYPRINT '此时 CardType 标识列ID为：@cardtype2 = ' + CONVERT(VARCHAR(20), @@IDENTITY)insert into cardtype values('冒险岛游戏卡','',150)set @cardtype3=@@IDENTITYPRINT '此时 CardType 标识列ID为：@cardtype3 = ' + CONVERT(VARCHAR(20), @@IDENTITY)insert into cardtype values('英雄联盟游戏卡','',200)set @cardtype4=@@IDENTITYPRINT '此时 CardType 标识列ID为：@cardtype4 = ' + CONVERT(VARCHAR(20), @@IDENTITY)--游戏卡批量添加，每种类型的卡添加10张GOdeclare @seed nvarchar(8)='84000508'declare @cnt int=0declare @stateid int=1if(@@ERROR=0)--没有任何错误beginwhile(@cnt<50)--循环添加卡begininsert into cardinf(cardno,cardpwd,cardtypeid,cardstateid,validitytime)values(@seed+Convert(nvarchar(2),@cnt),--把@cnt=1 int类型转换成nvarchar格式之后与@seed nvarchar格式拼接CONVERT(nvarchar(2),@cnt)+@seed,--把@cnt=1 int类型转换成nvarchar格式之后与@seed nvarchar格式拼接5,--魔兽世界卡类型@stateid,--未出售'2016-1-1')set @cnt+=1endwhile(@cnt<100)--循环添加卡begininsert into CardInf(cardno,cardpwd,cardtypeid,cardstateid,validitytime)values(@seed+CONVERT(nvarchar(2),@cnt),CONVERT(nvarchar(2),@cnt)+@seed,6,--诛仙游戏卡@stateid,--未出售'2016-1-1')set @cnt+=1endwhile(@cnt<150)--循环添加卡begininsert into CardInf(cardno,cardpwd,cardtypeid,cardstateid,validitytime)values(@seed+CONVERT(nvarchar(4),@cnt),CONVERT(nvarchar(4),@cnt)+@seed,7,--天堂游戏卡@stateid,--未出售'2016-1-1')set @cnt+=1endwhile(@cnt<200)--循环添加卡begininsert into CardInf(cardno,cardpwd,cardtypeid,cardstateid,validitytime)values(@seed+CONVERT(nvarchar(4),@cnt),CONVERT(nvarchar(4),@cnt)+@seed,8,--NBA篮球卡@stateid,--未出售'2016-1-1')set @cnt+=1endendGOselect * from CardStateselect * from CardTypeselect * from CardInf--练习部分，需求说明：--实现添加用户投诉功能（只有登陆后的用户才能投诉）--实现思路，--首先必须确定用户是否可以登录(参考指导部分代码)，当用户成功登陆以后，就可以进行投诉，用户投诉就是向表中Advice中写入数据。create table Advice(Id int identity(1,1) primary key,UserName nvarchar(20),Content nvarchar(100))declare @cnt intdeclare @username nvarchar(20)='大神'declare @pwd nvarchar(20)='84000508'select @cnt=COUNT(*) from UserInf where username=@username and [password]=@pwd and userstateid=2if(@cnt>0)beginprint '登陆成功！欢迎'+@usernameinsert into Advice values (@username,'为什么,这破游戏还在运营,早点关门吧')endelseprint '登陆失败!'GOselect * from advice--阶段3---------------------------IF ELSE和WHILE流程控制语句/*使用if else 语句控制流程使用while语句循环执行sql使用case-end语句进行分支判定需求说明实现普通会员和VIP会员的购卡功能1，需要先确保登陆成功（即用户名和密码正确，并且是已经审核用户）2，需要检查账户是否有足够的余额，并且检查需要购买的游戏卡状态是否未售出3，购买时需要一句不同角色计算出实际金额4，将购买的游戏卡存入购物车表和购物历史表中实现思路若要购卡必须先登录，登陆时就可以确定角色，保存到局部变量中，在购卡时依据其角色确定不同的则扣。购卡时要先验证用户的账户余额，然后验证游戏卡状态。将选择的卡放入购物车，保存到ShoppingCard表中，并记录到购卡历史表ShopHistory中。实现步骤(1)，新建查询，保存为Chap2-3.sql(2)，在Chap2-3.sql中编写T-sql代码，根据用户名就，密码，用户状态ID查询数据行将查询的数据行赋值给局部变量@cnt代码登陆*/--新建ShopHistory和shoppingCart表create table ShopHistory(Id int identity(1,1) primary key,UserName nvarchar(50),CardNo nvarchar(50),ShopTime datetime)create table shoppingCart(Id int identity(1,1) primary key,UserName nvarchar(50),CardTypeId int,Num int)select * from ShopHistoryselect * from shoppingCart--用户表select * from UserStateselect * from RoleInfselect * from UserInf--游戏卡select * from CardStateselect * from CardTypeselect * from Cardinf--------------------------------购卡代码START godeclare @cnt int=0declare @username nvarchar(20)='大神'declare @pwd nvarchar(20)='84000508'declare @roleid intdeclare @stateid int=2select @cnt=COUNT(*) from UserInf where username=@username and [password]=@pwd and UserStateId=@stateid--根据用户名就，密码，用户状态ID查询数据行将查询的数据行赋值给局部变量@cnt--(3)根据变量@cnt是否大于0判断用户能否登陆成功。如果登陆成功，还要判断此用户的账户余额是否足够购买两张'魔兽世界游戏卡'--代码(接上一代码)if(@cnt>0)beginprint @username+'您好！'print'1.登陆成功！'--开始购卡(购买两张魔兽世界游戏卡)--检查账户是否有足够的余额，还要检查要购买的游戏卡状态是否为未售出declare @balance money--保存账户余额select @balance=balance from UserInf where UserName=@username--获取用户的余额select @roleid=UserRoleid from UserInf where Username=@username--获取用户的会员级别declare @requiremoney money--保存需要用掉的金额declare @dicount decimal(10,2)--不同角色的则扣select @dicount=discount from RoleInf where Id=@roleid--根据会员级别获取打折率--魔兽世界游戏卡类型ID为1select @requiremoney=@dicount*price*2 from CardType where Id=1--获取需要用掉的金额if(@balance>=@requiremoney)--如果用户账户金额>=需要用掉的金额beginprint'2.开始买卡！'--满足了余额要求，再检查游戏卡状态declare @state int--用于检查游戏卡状态变量select @state=CardStateid from Cardinf where CardNo='200301452'--检查游戏卡的状态if(@state=1)--如果游戏卡的状态为未出售beginprint'3.点卡200301452未出售！'select @state=CardStateid from CardInf where CardNo='200301453'--检查游戏卡的状态if(@state=1)beginprint'4.点卡200301453未出售！'--购卡成功，添加数据到两张表insert into ShopHistory values (@username,'200301452',GETDATE())insert into ShopHistory values (@username,'200301453',GETDATE())insert into shoppingCart values (@username,1,2)if(@@ERROR=0)beginprint'5.成功添加数据到ShopHistory和shoppingCart！'update CardInf set cardstateid=2 where cardno in ('200301452','200301453')update UserInf set Balance -= @requiremoney where username=@usernameif(@@ERROR=0)beginprint'6.购卡成功！更新CardInf和UserInf，完成扣费和卡状态更新已售出'endendendendendelseprint @username+',您的账户余额不足,请在线充值'endgo--------------------------------购卡代码END--执行完毕后，检查表shoppingcart，shophistory，userinf，cardinf的数据select * from CardInfselect * from UserInfselect * from ShopHistoryselect * from shoppingCart-----------------------------------------------------------------------上机作业/*1,添加一个管理员账户，用户名为'admin',密码为'admin123'2,用户tom123回款500元3,汇款经过管理员admin审核以后可以使用4,汇款状态有3种：待审核，已审核，挂起实现思路先添加汇款状态和管理员账号*/create table ApproveState(Id int identity(1,1),[State] nvarchar(50))create table PostRecord(Id int identity(1,1) primary key,UserName nvarchar(20),Bank nvarchar(50),[Money] money,PostTime datetime,PostDesc nvarchar(225),ApproveStateId int)------------添加汇款状态------------declare @state1 int,@state2 int,@state3 intinsert ApproveState values('待审核')set @state1=@@IDENTITYinsert ApproveState values('已审核')set @state2=@@IDENTITYinsert ApproveState values('挂起')set @state3=@@IDENTITY------------添加管理员------------declare @admin nvarchar(20)='admin'declare @adminpwd nvarchar(20)='admin123'insert into UserInf values(@admin,@adminpwd,'胡伟',1,'420621198909193414','[e-mail]','','',888888,2,4,'')------------汇款审核------------godeclare @username nvarchar(20)='大神'declare @pwd nvarchar(20)='84000508'declare @stateid int=2declare @money money=500declare @cnt int=0declare @id intselect @cnt=count(*) from UserInf where username=@username and [password]=@pwd and UserStateId=@stateidif(@cnt>0)beginprint '登陆成功！欢迎'+@usernameinsert into PostRecord values (@username,'中国工商银行',@money,GETDATE(),'购买魔兽世界游戏卡','')set @id=@@identityif(@@ERROR=0)beginprint'向PostRecord表中插入购买信息成功，等待审核！'update PostRecord set ApproveStateId= 2 where Id=@idif(@@ERROR=0)beginprint'审核成功！正在转账到账户中！'update UserInf set Balance += @money where UserName=@usernameif(@@ERROR=0)beginprint'充值成功！'select balance as 您当前金额 from UserInf where username=@usernameendendendendelseprint'登陆失败！'+@username+'请核实再登陆'goselect * from PostRecordselect * from UserInfselect * from ApproveState--------------------------------------------------------------------------------深入SQL高级子查询use HRselect * from Employeeselect * from Salaryselect * from Expertiseselect * from Department--查询编号为'E001'的员工属于同一个部门的员工--使用变量方法declare @deptno nvarchar(10)--定义一个变量记录部门select @deptno=DeptNo from Employee where EmpNo='E001'select * from Employee where DeptNo=@deptno and EmpNo <> 'E001'--查找这个部门的所有员工信息除了'E001'工号--使用子查询select * from Employee where deptno=(select deptno from Employee where EmpNo='E001') and EmpNo<>'E001'--查询工资在5000元以上，并且拥有的技能等级最高的员工信息--这个查询涉及Employee,Salary，Expertise--1.查询工资大于5000的员工工号select EmpNo from Salary where salary>5000 --2.根据1查村出最高技能等级select MAX(SkillLevel) from Expertise where EmpNo in (select EmpNo from Salary where salary>5000)--3.根据2查询出此员工工号select Empno from Expertise where SkillLevel=(select MAX(SkillLevel) from Expertise where EmpNo in (select EmpNo from Salary where salary>5000))--4.根据3查询出此员工信息select * from Employee  where Empno=(select Empno from Expertise where SkillLevel=(select MAX(SkillLevel) from Expertise where EmpNo in (select EmpNo from Salary where salary>5000)))---------------------------------------------UPDATE,DELETE,INSERT语句中使用子查询--1，在update语句中使用查询select * from Departmentselect * from Employeeselect * from Expertiseselect * from Salary--查询'E010'所在的部门select DeptNo from Department where Principal='E010'--查询和'E010'在同个部门的员工select EmpNo from Employee where DeptNo=(select DeptNo from Department where Principal='E010')--根据员工编号更新技能等级update Expertise set SkillLevel+=1 from Expertise where EmpNo in (select EmpNo from Employee where DeptNo=(select DeptNo from Department where Principal='E010'))--2，在Delete语句中使用查询--查询技术部所在的部门select DeptNo from Department where DeptName='技术部'--根据部门查询所有员工的工号select EmpNo from Employee where DeptNo=(select DeptNo from Department where DeptName='技术部')--查询准备删除的这些数据select * from Salary where EmpNo in (select EmpNo from Employee where DeptNo=(select DeptNo from Department where DeptName='技术部')) and DATEDIFF(YYYY,StartTime,'2014-10-10 00:00:00')>=5--最后删除这个数据delete from Salary where EmpNo in (select EmpNo from Employee where DeptNo=(select DeptNo from Department where DeptName='技术部')) and DATEDIFF(YYYY,StartTime,'2014-10-10 00:00:00')>=5---------------------------------------------在INSERT语句中使用子查询--基本语法：INSERT INTO 表名(字段列表) SELECT 字段列表 FROM 表名--根据其它表的数据插入到被插入的表中，要求被插入的表必须存在--备份整个表语法：select * into EmpHistory from Employee--备份表结构：select * into EmpHistory from Employee where 1<>1--备份表结构：select top 0 * into EmpHistory from Employee--使用 insert into 被插入表 子查询语句 where 条件功能备份数据，要求被插入的表必须存在--使用 select * into 被插入表from 原表 where 条件功能备份数据，被插入的表必须不能存在select * from Employeeselect * from EmpHistory--(1)备份表结构select * into EmpHistory from Employee where 1<>1--(2)使用子查询将表Employee中的3个离职员工信息添加到表EmpHistory--使用 insert into 被插入表 子查询语句 where 条件功能备份数据，要求被插入的表必须存在insert into EmpHistory select * from Employee where EmpNo in ('E002','E003','E008')--使用 select * into 被插入表from 原表 where 条件功能备份数据，被插入的表必须不能存在select * into EmpHistory from Employee where EmpNo in ('E002','E003','E008')--(3)从员工技能表和员工表中删除这3个已经离职的员工信息delete from Employee where EmpNo in ('E002','E003','E008')delete from Expertise where EmpNo in ('E002','E003','E008')select * from Employeeselect * from Expertise---------------------------------------------高级子查询语句/*---------------------使用IN,NOT IN的子查询当子查询与比较运算一起使用时，要求子查询返回的结果必须是一行记录或空记录。如果子查询返回的结果是多行，可以将比较运算符改为IN，IN后面的子查询允许返回多行记录，用于从一个范围来限制主查询条件*/--1查询技能的级别在3级以上的员工信息select * from Employee where empno in (select EmpNo from Expertise group by EmpNo having MAX(SkillLevel)>=3) --2删除Phone表中重复的数据select * from Phone--按Phone,OfficeCode分组(多列分组)--查询出经过多列分组之后Id较大的数据//指最新数据select MAX(Id) from Phone group by Phone,OfficeCode--删除数据不包括分组之后Id较大的数据//指最新数据delete from Phone where id not in (select MAX(Id) from Phone group by Phone,OfficeCode)select * from EmpHistoryselect * from TimeWorkselect * from Employee--查询考情表中有但员工表中没有的记录select * from TimeWork where EmpNo not in (select empno from Employee where Employee.EmpNo=TimeWork.EmpNo)--查询员工表中所有员工编号select empno from Employee group by empno--查询考情表中有但员工表中没有的记录select * from TimeWork where EmpNo NOT IN (select empno from Employee group by empno)--查询技术部有哪些员工select * from Departmentselect * from Employee--select * from Employee where exists (select * from Department where Department.DeptNo=Employee.DeptNo and DeptName='技术部')--相关子查询select * from Employee where DeptNo=(select DeptNo from Department where DeptName= '技术部')--独立子查询select * from (select EmpNo,EmpName,Empaddress,Empphone,Birthday,Hiredate,Employee.DeptNo,Officecode,DeptName,Principal from Employee left join Department on Employee.DeptNo=Department.DeptNo) as ED where DeptName='技术部'--独立子查询 select EmpNo,EmpName,Empaddress,Empphone,Birthday,Hiredate,Employee.DeptNo,Officecode from Employee join Department on Department.DeptNo=Employee.DeptNo and DeptName='技术部'---------------------使用exists,not exists的子查询--exists与子查询在一起使用，用来对子查询的查询结果进行存在的测试。只要子查询的结果有一个行或者一行以上的数据就返回真，否则返回假。--因为结果只取决于是否返回行，而不取决于这些行的内容。所以exists查询的条件子查询输出列表通常是无关紧要的，只要有一个字段即可。--查找出具有C#技能的员工，并显示详细信息select * from Employeeselect * from Expertiseselect * from Skillselect * from Employee where exists (select * from Expertise where Expertise.EmpNo=Employee.EmpNo and SkillId=(select SkillId from Skill where SkillName='C#'))---------------------使用ALL的子查询/*通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一行进行对比*/select * from Salaryselect * from Department/*分析1.先查询select Principal from Department，从部门表中查询出所有部门负责人的编号2.再查询select Salary from Salary where EmpNo in(select Principal from Department)，从工资表中查询出所有的部门负责人的工资3.执行整个查询语句，Salary表中的salary列的数据依次和（部门负责人的工资）列数据进行对比，满足条件的筛选出来*/select EmpNo , Salary from Salarywhere Salary > all(select Salary from Salary where EmpNo in(select Principal from Department))---------------------使用ANL/SOME的子查询/*ANY与子查询在一起使用时，按照比较运算符，表达式或字段对子查询的结果每一行进行依次计算和比较。*/--查询只要员工工资大于任何一个部门负责人，就显示这些员工的信息select EmpNo, Salary   from Salarywhere Salary > some (select Salary from Salary where EmpNo in (select Principal from Department)) and EmpNo not in (select Principal from Department)select EmpNo , Salary  from Salarywhere Salary > (select MIN(Salary) from Salary where EmpNo in (select Principal from Department)) and EmpNo not in (select Principal from Department)---------------------使用别名的相关子查询//没有懂select * from Salaryselect EmpNo from Salaryselect * from Expertiseselect empNo from Expertiseselect * from Employeeselect empNo from Employeeselect * from Employee e join Expertise p on e.EmpNo=p.EmpNoselect * from Salary s join Employee e on e.EmpNo=s.EmpNo--=要求查询在员工表与技能表中都存在，并且月工资为5000元的一个员工，这个查询会涉及到3张表：Employee，Expertise，Salary--57页select distinct e.empno,e.empnamefrom Employee e join Expertise p on e.EmpNo=p.EmpNo where 5000 in (select Salary from Salary s where e.empNo=s.EmpNo)--上机阶段1---------------------------使用基本子查询，in子查询/*训练内容使用基本子查询1，在update语句中使用子查询2，在insert语句中使用子查询3，使用in/not in的子查询需求说明实现用户“jack123”购卡流程：用户“jack”想一次购买5张NBA篮球卡，要求实现此用户的购卡流程。购买游戏卡需要6步骤：1，查询用户需要购买的卡的类型数量是否充足2，检查用户账户的余额是够足够购买这些卡3，将购买的游戏卡保存到购物历史纪录中4，将购买的游戏卡信息保存到购物车5，跟新用户的账户余额6，跟新游戏的状态*/--代码实现 select * from CardInf select * from UserInf select * from shoppingCart select * from shophistory  --1：查询用户需要购买的卡的类型数量是否充足 --说明：检查可以出售的NBA篮球卡是否够5张使用子查询来查询卡的类型ID和状态ID GO declare @CardTypeName nvarchar(20)='冒险岛游戏卡' declare @UserName nvarchar(20)='葫芦娃' declare @cnt int select @cnt=COUNT(*) from CardInf where CardTypeId=(select id from CardType where CardTypeName=@CardTypeName) and CardStateId=(select id from CardState where cstate='未售出') print '游戏卡还有'+convert(nvarchar,@cnt) if(@cnt>=5) begin --2：检查用户账户的余额是够足够购买这些卡 --说明：因为用户会依据角色有一定的折扣，所以用5张卡的面额乘以折扣率 --2.1：查询账户的余额 declare @balance money,@requiremoney money select @balance=balance from UserInf where UserName = @UserName print @UserName+'账户的余额'+convert(nvarchar,@balance) --2.2购买5张卡需要的余额 select @requiremoney=5*price from CardType where Id=(select id from CardType where CardTypeName=@CardTypeName) print '购买5张卡的价格'+convert(nvarchar,@requiremoney) --2.3根据会员等级打折 set @requiremoney=@requiremoney*(select discount from RoleInf where id=(select userroleid from userinf where username=@UserName)) print '打折后需要的价格'+convert(nvarchar,@requiremoney)if(@balance>=@requiremoney)begin --3：将购买的游戏卡保存到购物历史纪录中 --说明：取出NBA篮球卡的前五张，保存到购卡历史纪录中，因为已经购买了游戏卡，在后面的代码中还要修改其状态，所以使用table类型的变量进行保存 --3.1：5张卡号后面还要使用，所以保存到变量中 declare @temptable table(id int identity(1,1),CardNo nvarchar(20)) insert into @temptable select top 5 cardno from cardinf  where cardstateid=(select id from cardstate where [cstate]='未售出') and cardtypeid=(select id from cardtype where cardtypename=@CardTypeName) --3.2：循环购卡 declare @id int=1 while(@id<=5) begininsert into shophistory values(@UserName,(select cardno from @temptable where id=@id),getdate())set @id+=1 end --4：将购买的游戏卡信息保存到购物车 --说明：@@ERROR全局变量等于0，保证了它前面的T-SQL已经正确执行,在insert语句中使用了子查询，查询出游戏卡的类型，在更新已购买的游戏卡状态时， --  在UPDATE语句中也使用了子查询，批量更新这5条游戏卡的状态ID为 2 (已售出)。变量@temptable保存已经选购的游戏卡的卡号 if(@@ERROR=0) begininsert into shoppingcart values(@UserName,(select id from cardtype where cardtypename=@CardTypeName),5)--4.1：更改游戏卡状态update cardinf set cardstateid=2 where cardno in (select cardno from @temptable) end --5：更新用户的账户余额 if(@@ERROR=0) begindeclare @surplus moneyupdate UserInf set balance -= @requiremoney where UserName=@UserNameselect @surplus=balance from userinf where UserName=@UserNameprint '购买成功!您的账户余额为'+convert(nvarchar,@surplus)endendelseprint @username+'您的账户余额为'++convert(nvarchar,@balance)+',余额不足,请在线充值!' end GO /*练习部分 需求说明使用子查询统计在所有已经销售的游戏卡中滞销的游戏卡类型，热销的游戏卡类型及还没有销售量的游戏卡类型。其总销量小于10张的为滞销的游戏卡类型，总销售量大于1000张的为热销的游戏卡类型，总销售量为0的是没有销售量的游戏卡类型实现思路以查询滞销游戏卡为例：使用group by和having查询出销售量小于10的游戏卡类型ID，然后依次作为WHERE条件，使用in与这些结果比较，查询出游戏卡的类别名称 */ select * from CardType select * from CardInf select * from ShophiStory select * from UserInf select * from ShoppingCart select * from CardType  update UserInf set balance=10000 where username='葫芦娃'  select cardtypename from CardType where id =ANY (select c.CardTypeId from ShophiStory s,CardInf c where s.CardNo = c.CardNo group by c.CardTypeId having count(s.CardNo)<10)use Company_DB select *, '销售情况'=casewhen 销售数量>=10 then '热销'when 销售数量>0 then '滞销'else '还没销售'end FROM  (select cardtypename, count(ShophiStory.cardno) as 销售数量 from CardType,CardInf,ShophiStory  where CardInf.CardTypeId=CardType.id AND CardInf.cardno=ShophiStory.cardno  GROUP by cardtypename) as a --代码实现 --查找销量小于10张的为滞销卡的名称 select cardtypename from CardType where id =ANY (select c.CardTypeId from ShophiStory s,CardInf c where s.CardNo = c.CardNo group by c.CardTypeId having count(s.CardNo)<10)  --查找没有销售量的游戏卡名称 select cardtypename from CardType where id in (select c.CardTypeId from CardInf c left join ShophiStory s on s.CardNo = c.CardNo group by c.CardTypeId having COUNT(s.CardNo)=0)   --上机阶段2---------------------------使用相关子查询，ANY子查询/*训练内容使用相关子查询1，相关子查询2，多表链接查询3，Any子查询需求说明1，在用户每次购买游戏卡，系统都应该根据其消费金额为用户升级（指角色升级），角色级别越高，用户则扣越高，要求实现用户角色升级功能。2，用户角色升级的条件为：单日消费达到200元或总消费达到1000元时可升一级，但是最高不能升级成‘超级管理员’。要求分别使用相关子查询和联接查询实现实现思路思路一：使用连接查询用户的消费记录保存在购物历史纪录表ShophiStory中，用户购买的类型不同，价格就不同所以要联接表CardType查询价格，同时，要统计用户每天购卡的金额就必须根据不同类型的卡将每日购买的金额进行分组统计，因为单张卡的类型ID在表CARDINF中所以必须与表CARDINF做联接查询思路二：使用相关子查询先通过相关子查询查询出用户‘大神’所有买的所有卡的卡号和金额*/----------代码实现----------相关查询/*说明：1，明确外层查询的表和列 2，明确内层查询的表和列 3，明确内层查询与外层查询的主外键关系 4，明确外层查询的where条件*/SELECTss.username,ss.cardno,ss.shoptime,(SELECT price from CardInf ci,CardType ct WHERE ci.cardtypeid=ct.id AND ss.cardno=ci.cardno )from ShophiStory as ssWHERE ss.username = '大神'SELECTShophiStory.username,ShophiStory.cardno,ShophiStory.shoptime,(SELECT price from CardInf,CardType WHERE CardInf.cardtypeid = CardType.id and CardInf.cardno = ShophiStory.cardno)from ShophiStoryWHERE username = '大神'select ShophiStory.username,ShophiStory.cardno,ShophiStory.shoptime,(SELECT price from CardInf join CardType on CardInf.CardTypeid=CardType.id and CardInf.cardno=ShophiStory.cardno)from ShophiStory where username='大神'----------连接查询select * from ShophiStoryselect * from CardInfselect * from CardTypeSELECT ss.username,ss.cardno,ss.shoptime,ct.price from CardInf ci,CardType ct,ShophiStory ss WHERE ss.cardno=ci.cardno AND ci.cardtypeid=ct.id AND ss.username='大神'/*实现步骤   方法一：连接查询实现*/--对用户'葫芦娃'的角色ID进行升级，按照用户每日购卡消费金额的合计(每日的合计，不包括全部的合计)，使用any检查，只要有一天消费金额大于等于200，即可升级。--获取管理员的角色ID和当前用户的角色ID--代码实现use Company_DBgo--1，查询用户当前的角色declare @role intselect @role=userroleid from UserInf where username='葫芦娃'--获取用户的身份等级declare @adminrole intselect  @adminrole=MAX(id) from RoleInf where rolename <> '超级管理员'--获取管理员的身份等级if(@role<@adminrole)--如果用户的身份等级低于管理员身份begin--获取需要升级的用户角色等级ID，该角色等级ID是比用户当前的角色等级ID大的所有的角色等级ID中最小的一个(每次只能升级一级)。在第二步的begin与end之间输入代码进行判断declare @sharprole int -- 需要升级的角色等级IDselect @sharprole=MIN(id) from RoleInf where id > @role--从角色等级表中取出用户要升级的角色等级ID--对用户'葫芦娃'的角色ID进行升级，按照用户每日购卡消费金额的合计(每日的合计，不包括全部的合计)，使用any检查，只要有一天消费金额大于等于200，即可升级。--   在第三步的基础上继续输入更新用户角色的代码：--联接查询方法实现;update UserInf set userroleid=@sharprole where username='葫芦娃' and 200 < any (select SUM(ct.price) as '每日消费' from ShopHistory s,CardInf ci,CardType ct where s.CardNo=ci.cardno and ci.cardtypeid=ct.id and s.UserName='葫芦娃'group by CONVERT(char(10),s.ShopTime,120))--把datetime类型转换成char类型保yyyy-mm-ddif(@@ERROR=0)--执行未出错print 'OK'endgo--按用户消费金额的总额进行升级(总消费满1000元即可升级)。use Company_DBgodeclare @role intselect @role=userroleid from UserInf where UserName ='葫芦娃'declare @adminrole intselect @adminrole=MAX(id) from RoleInf where RoleName <> '超级管理员'if(@role<@adminrole)begindeclare @sharprole int--要升级到角色，一次只能升级一次select @sharprole=MIN(id) from RoleInf where Id > @roleupdate userinf set UserRoleId=@sharprole where UserName='葫芦娃'and (select SUM(ct.Price) as '总消费' from ShopHistory s,CardInf ci,CardType ct where s.CardNo=ci.CardNo and ci.CardTypeId=ct.Id and s.UserName='葫芦娃')>=1000if(@@ERROR=0)--执行未出错print 'OK'endgo/*   方法二：相关子查询实现*/--对用户'葫芦娃'的角色ID进行升级，按照用户每日购卡消费金额的合计(每日的合计，不包括全部的合计)--获取管理员的角色ID和当前用户的角色ID--思路参考代码：！！！--查询括号中的类容是关键 他通过CardInf表与CardType表进行联接，然后与外部的ShopHistory表进行逐步行筛选(相关子查询)，查询的结果作为一个独立值，其别名为sumPayselectSUM(re.sumPay) sum_pay from (select s.UserName,s.CardNo,s.ShopTime,(select SUM(price) from CardInf as ci, CardType as ct where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo) as sumPayfromShopHistory as s where s.UserName='葫芦娃') as regroup byCAST(CAST(year(re.shoptime)as varchar)+'-'+CAST(month(re.shoptime)as varchar)+'-'+CAST(day(re.shoptime)as varchar)as datetime)selects.UserName,s.CardNo,s.ShopTime,(select SUM(price) from CardInf as ci, CardType as ct where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo) as sumPayfromShopHistory as s where s.UserName='葫芦娃'--思路细节：！！！select price from CardInf as ci, CardType as ct where ci.CardtypeId=ct.id select * from CardInf as ci, CardType as ct,ShopHistory as s where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo and s.UserName='葫芦娃'select SUM(price) from CardInf as ci, CardType as ct,ShopHistory as s where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo and s.UserName='葫芦娃' select SUM(price) from CardInf as ci, CardType as ct,ShopHistory as s where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo and s.UserName='葫芦娃' group by CONVERT(char(10),s.ShopTime,120)select * from ShopHistoryselect * from UserInfselect * from RoleInfupdate UserInf set UserRoleId=1 where UserName='大神' or UserName='葫芦娃' --代码实现----按照用户每日购卡消费金额的合计(每日的合计，不包括全部的合计)use Company_DBgodeclare @role intselect @role=userroleid from UserInf where UserName='葫芦娃'declare @adminrole intselect @adminrole=MAX(id) from RoleInf where RoleName <> '超级管理员'if(@role<@adminrole)begindeclare @sharprole int--要升级成的角色select @sharprole=MIN(id) from RoleInf where Id>@role update userinf set userroleid = @sharprole where username='葫芦娃' and 1000 < any(select SUM(re.sumPay) sum_pay from (select s.UserName,s.CardNo,s.ShopTime,(select SUM(price) from CardInf as ci, CardType as ct where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo) as sumPayfromShopHistory as s where s.UserName='葫芦娃') as regroup byCAST(CAST(year(re.shoptime)as varchar)+'-'+CAST(month(re.shoptime)as varchar)+'-'+CAST(day(re.shoptime)as varchar)as datetime))if(@@ERROR=0)print 'ok'endgo----按用户消费金额的总额进行升级(总消费满1000元即可升级)。use Company_DBgodeclare @role intselect @role=userroleid from UserInf where UserName='葫芦娃'declare @adminrole intselect @adminrole=MAX(id) from RoleInf where RoleName <> '超级管理员'if(@role<@adminrole)begindeclare @sharprole int--要升级成的角色select @sharprole=MIN(id) from RoleInf where Id>@role update userinf set userroleid = @sharprole where username='葫芦娃' and 1000 < any(select SUM(re.sumPay) sum_pay from (select s.UserName,s.CardNo,s.ShopTime,(select SUM(price) from CardInf as ci, CardType as ct where ci.CardtypeId=ct.id and ci.CardNo=s.CardNo) as sumPayfromShopHistory as s where s.UserName='葫芦娃') as re)if(@@ERROR=0)print 'ok'endgo--------------------------------------------------------------------------------函数和存储过程/*1、函数定义：一种封装一条或多条SQL语句的结构好处：减少代码冗余，提高代码重用性预编译的，提高代码执行效率分类：聚合函数、系统函数、自定义函数（标量值函数 和 表值函数）标量值函数：返回值为一个数据语法：create function 函数名(   参数1 类型,参数2 类型,......参数n 类型 )returns 返回值类型asbegin   函数体   return 值end表值函数：返回值为一个数据表分类：多语句表值函数和内联表值函数多语句表值函数：语法：create function 函数名(   参数1 类型,参数2 类型,......参数n 类型 )returns 返回值 table(表结构)asbegin   函数体   returnend内联表值函数：语法：create function 函数名(   参数1 类型,参数2 类型,......参数n 类型 )returns tableas  return (查询)*/---------------------------5.1.2标量值函数/*标量值函数的返回值是基本数据类型的单个值或单个值得表达式。函数体既可以是一条语句（可以省略begin end），也可以是多条语句（多条语句必须放在begin end之间）标量值函数可以被另外的标量值函数或表值函数调用*/--5.1函数的功能是通过参数传入员工的编号，返回该员工的工资标准use HRgocreate function getEmpSalary( @empno varchar(20) )--参数returns money--返回值类型begindeclare @salary money; --最新工资数据select @salary=Salary from Salary where Salary = (select top 1 Salary from Salary where EmpNo=@empno order by StartTime desc)return @salary--返回值endgo --5.2直接输出标量值函数的返回值goselect dbo.getempsalary('E006') as 员工E006的工资标准go--5.3将标量值函数的返回值存入变量declare @salary money select @salary = dbo.getEmpSalary('e006')print '员工E006的月薪是'+convert(varchar(10),@salary)--5.4新创建函数getDateMaxSlary，在该函数中调用示例5.1中创建的getEmpSalary，用来统计一个部门的最高月薪use HRgocreate function getDeptmaxsalary(@deptno varchar(10)) returns money--定义函数名称和形参变量asbegin declare @maxsalary money=0--定义保存部门的最高月薪/*因为一个部门有多少员工，使用个基本类型的变量无法保存这些员工的编号，所以使用table类型的变量临时保存*/declare @emp table(id int identity(1,1),empno varchar(10))--将这个部门的员工编号保存到table类型的变量中insert into @emp select empno from employee where deptno=@deptno declare @cnt int = 0,@i int=0--循环变量declare @empno varchar(10)--保存员工编号declare @salary money--保存员工工资select @cnt=count(*) from @emp--确定循环次数，查新新临时表中有几条数据while(@i<@cnt)begin--查询每个员工的编号select @empno=empno from @emp where id=(@i+1)--调用自定义的标量值函数计算当前员工的工资标准select @salary=dbo.getempsalary(@empno)if(@salary>@maxsalary)beginset @maxsalary=@salary--将最高的工资保存到变量@maxsalary中endset @i+=1endreturn @maxsalaryend--返回最高工资go--5.5传入部门编号调用改方法select * from Departmentuse HRgoselect dbo.getDeptmaxsalary('d03') as 最高工资go---------------------------5.1.3表值函数--1，多语句表值函数--多语句表值函数要求返回类型为table类型--需求：返回所有员工的当前工资标准use HRgo--函数执行完毕后返回table类型的变量@salarytablecreate function getEmployeesSalary()returns @salarytable table(id int,EmpNo nvarchar(20),Salary money,StartTime datetime)asbegin--为table类型的变量赋值 insert into @salarytableinsert into @salarytableselect * from Salary where StartTime in (select MAX(StartTime) from Salary group by EmpNo)return --无需再写值或表达式，直接返回变量@salarytableendgo--使用getEmployeesSalary表值函数select * from dbo.getEmployeesSalary()--使用原表salaryselect * from Salary--5.8带参数的表值寒素--需求：通过传入部门编号，返回指定部门所有员工的当前工资标准use HRgo--参数要求传入的是部门编号create function getEmpSalaryByDept(@deptno varchar(20))returns @salarytable table(id int,EmpNo nvarchar(20),Salary money,StartTime datetime)asbegin--在子查询中使用到参数@deptnoinsert into @salarytable--查询最新入职时间       --查找对应部门的员工编号        --按员工分组select * from salary where starttime in (select max(starttime) from salary where empno in (select empno from employee where deptno=@deptno) group by empno) returnendgo--5.9内联表值函数--根据传入的参数返回员工的技能use HRgocreate function getEmpertiseByEmp(@empno varchar(10))returns tableas return (select * from Expertise where EmpNo=@empno)goselect * from getEmpertiseByEmp('e003')select * from Expertise----------------------------------------------------存储过程！！！！！！！--------------------------------------------------------------------------------------------存储过程！！！！！！！------------常用的系统存储过程use mastergoexec sp_renamedb 'HR','hr_prj'--改变单用户访问的数据库名称gouse cardsale--进入到名称为hr_prj的数据库下goexec sp_databases--返回当前实例中的所有数据库的基本信息exec sp_tables--查看数据库hr_prj中可查看对象的列表exec sp_help employee--查看表employee的所有信息exec sp_helpconstraint employee--查看表employee的约束exec sp_helptext 'dbo.vw_empsalary'--查看识图vm_empsalary的定义exec sp_stored_procedures--查看当前数据库中所有的存储过程use mastergoexec sp_renamedb 'hr','HR'--将数据库名称更改回来gouse mastergo--使用系统存储过程sp_configure启用高级选项exec sp_configure 'show advanced options',1goreconfigure--重新配置goexec sp_configure 'xp_cmdshell',1--启用xp_cmdshell扩展过程goreconfigure--重新配置goexec xp_cmdshell 'mkdir d:\prj'--调用dos命令'mkdir'创建操作系统目录if(exists(select * from sysdatabases where name='onlineexam'))drop database onlineexamgocreate database OnLineExamon(name='OnLineExam_data',filename='d:\prj\OnLineExam_data.mdf',size=5mb )log on(name='OnLineExam_log',filename='d:\prj\OnLineExam_log.ldf',size=5mb)goexec xp_cmdshell 'dir d:\prj\'-- 查看创建数据库文件------------用户自定义存储过程------------------------------1.创建不带参数的存储过程--创建存储函数过程，显示HR数据库中月薪最低的员工use hrgoif exists(select * from sysobjects where name='UP_Salary_SelectMinSalary')drop procedure UP_Salary_SelectMinSalarygocreate proc up_Salary_SelectMinSalaryasselect empname,minsal.salary                  from (select top 1 empno,sal.Salary as salary from (select * from Salary where StartTime in (select MAX(StartTime)from Salary group by EmpNo))as sal order by sal.salary) as minsal,Employee where minsal.EmpNo=Employee.empno--思路select MAX(StartTime)from Salary group by EmpNo--查询记录员工工资的最后记录时间select * from Salary where StartTime in (select MAX(StartTime)from Salary group by EmpNo)--查询工资表，根据每个员工的最后记录时间select top 1 empno,sal.Salary as salary from (select * from Salary where StartTime in (select MAX(StartTime)from Salary group by EmpNo))as sal order by sal.salary--查询最低工资和员工号select * from(select top 1 empno,sal.Salary as salary from (select * from Salary where StartTime in (select MAX(StartTime)from Salary group by EmpNo))as sal order by sal.salary) as minsal,Employee where minsal.EmpNo=Employee.empno--联接查询最低工资员工的信息select empname,minsal.salaryfrom(select top 1 empno,sal.Salary as salary from (select * from Salary where StartTime in (select MAX(StartTime)from Salary group by EmpNo))as sal order by sal.salary) as minsal,Employee where minsal.EmpNo=Employee.empno   --联接查询最低工资员工的工资和姓名--执行存储过程，查询工资最低的员工use hrgoexec UP_Salary_SelectMinSalary------------------2.创建带参数的存储过程--创建测试表create table timework(empno nvarchar(20),[state] nvarchar(20),dtime datetime)use hrgoif exists(select * from sysobjects where name='UP_TimeWork_Insert')drop procedure UP_TimeWork_Insertgocreate proc UP_TimeWork_Insert(--输入参数列表@empno varchar(10),--传入的员工编号@state nvarchar(20),--传入的工作状态@dtime datetime--传入的工作日(注意：最后一个参数不能有逗号))as--存储过程执行添加数据(形参列表)insert into timework values(@empno,@state,@dtime)if(@@ERROR=0)print 'ok'elseprint 'error'goexec UP_TimeWork_Insert 'E005','休假','2010-1-1'go--查询结果select * from timework------------------------3.带输出参数的存储过程--如果需要存储过程返回一个值或者多个值，可以使用输入参数。输出参数必须在存储过程定义时使用output关键字进行声明--存储过程可以通过return返回值，但一般只是返回一些执行状态值--创建一个带输入参数和输出参数的存储过程UP_Salary_Insert,当使用输入参数执行添加一条工资标准后，再通过输出参数返回工资表中最高工资的员工use Company_DBgoif exists(select * from sysobjects where name='UP_Salary_Insert')drop procedure UP_Salary_Insertgocreate proc UP_Salary_Insert(@maxsalary money output,--传出参数，最高工资标准@empname nvarchar(20) output,--传出参数，工资最高的员工姓名@empno nvarchar(10),@salary money=10000,@stime datetime)asinsert into Salary values(@empno,@salary,@stime)--为传出参数赋值select @empname=empname,@maxsalary=maxsal.salary from(select top 1 empno,sal.Salary as salary from (select * from Salary where StartTime in(select MAX(StartTime) from Salary group by EmpNo ))as sal order by sal.Salary desc) as maxsal,Employeewhere maxsal.empno=employee.empno--在调用带传出参数的存储过程时，需要先定义对应的变量作为实际参数，并且在实际参数后面必须使用output关键字，--执行存储过程成功后，就可以通过变量得到存储过程传出的参数值--先定义变量，与传出参数保持类型一致declare @maxsal money,@emp nvarchar(20)--执行存储过程时，将变量作为实际参数，并使用output关键字进行说明execute UP_Salary_Insert @maxsal output,@emp output,'E011',13000,'2010-01-01'--执行完毕后，通过变量得到存储过程传出的值print @empprint '工资'+convert(nvarchar(20),@maxsal)go--------------------4.错误处理use Company_DBgoif exists(select * from sysobjects where name='UP_Salary_Insert')drop procedure UP_Salary_Insertgocreate proc UP_Salary_Insert(@maxsalary money output,--传出参数，最高工资标准@empname nvarchar(20) output,--传出参数，工资最高的员工姓名@empno nvarchar(10),@salary money=10000,@stime datetime)asif(@stime<getdate())begin--自定义错误，以便执行时获取详细的错误信息，错误级别15，状态1为默认值raiserror('新的工资标准执行日期必须大于当前日期',15,1)return--退出存储过程的执行endinsert into salary values(@empno,@salary,@stime)select @empname=empname,@maxsalary=maxsal.salary from (select top 1 empno,Sal.salary as salary from (select * from Salary where StartTime in (select MAX(StartTime) from Salary group by EmpNo)) as sal order by sal.Salary desc) as maxsal,employeewhere maxsal.empno=employee.empnogo--执行存储过程，捕获错误declare @maxsal money=0,@emp nvarchar(20)=''exec UP_Salary_Insert @maxsal output,@emp output,'e011',8888888888,'2000-01-01'declare @err intset @err=@@ERRORif(@err<>0)beginprint '错误号：'+convert(varchar(10),@err)return --退出批处理，后续语句将不再执行endprint @empprint '工资'+ convert(varchar(100),@maxsal)go--上机阶段1---------------------------创建和使用标量值函数，创建和使用多语句表值函数/*指导部分训练类型创建标量值函数将标量值函数作为列的默认值使用创建多语句表值函数需求说明创建标量值函数，完成判断‘游戏点卡销售系统’中用户注册时间，当注册时间处于每天的0点到7点是，赠送用户50元购物卡金额，在用户表中添加默认值约束，将函数的返回值作为新用户余额的默认值创建一个多语句白哦之函数，完成根据接受的年份返回此用户每月的消费金额实现思路在标量值函数中徐需要判定系统时间，根据时间返回不同的值，如果时间在0点到7点之间，就返回50元，否则返回0.多语句表值函数内，返回一个包含用户名，月份，月消费合计3个列的table类型的数据记录*/--实现步骤--1，无参数的表量值函数，返回值为money类型use cardsalegocreate function DefaultMoney()returns moneyasbegindeclare @money money=0--0点到7点之间if(datepart(hh,getdate())>=0 and datepart(hh,getdate())<=7)--如果当前时间是在0-7点之间，通过datepart()获取当前时间的小时set @money=50return @moneyendgo--2，使用系统函数检查函数是否创建成功execute sp_helptext 'DefaultMoney'go--3,使用T-SQL 为UserInf表添加默认值约束alter table userinf add constraint DF_Balance default(dbo.DefaultMoney()) for Balancego--4,调用系统存储过程sp_helpconstraint查看USERINF表的约束，exec sp_helpconstraint userinf--5,将计算机系统时间调至0点到7点之间，输入测试数据，检查函数是否能成功作为默认值使用insert into UserInf values('测试001',123456,'程序员',1,'','','','',50,'2','1','')--6,创建一个标值函数，函数接收一个datetime类型的参数，返回含有3个字段的table类型集合，--查询的时候线通过3张表连接查询出所有用户一年年的消费记录，然后将该结果集作为子查询，--按用户的月份进行分组合计alter function getsalebymonth(@date datetime)returns @sales table--返回表值(username varchar(20),smonth int,amount money)asbegininsert into @sales select uname,MONTH(stime),SUM(price)from (select s.UserName uname,s.ShopTime stime,ct.Price price from  ShopHistory s,CardInf ci, CardType ct where s.CardNo=ci.CardNo and ci.CardTypeId=ct.Id and year(s.ShopTime)=year(@date)) SaleInf group by uname,MONTH(stime)returnend--7，使用select测试该表值函数declare @date datetime='2016-8-27'--调用函数钱要先声明变量select * from getsalebymonth(@date)/*练习部分(一)需求说明：创建一个无参数的存储过程，调用阶段1指导部分创建的标值函数，实现如下业务规则：本年度消费金额满500元的用户，返回购卡金额的10%到用户账户使用IN*/--使用函数进行查询，查询结果作为更新的条件update userinf set Balance=Balance+500 where UserName in(select distinct username from getsalebymonth(GETDATE())where amount>=500)/*练习部分（二）需要说明：使用系统存储过程，将D盘中的两个文件1.TXT和2.TXT进行连接，生产一个新的文件使用xp.cmdshell*/exec sp_configure 'show advanced options',1execute xp_cmdshell 'copy /b D:\1.txt + D:\2.txt  D:\3.txt'--上机阶段2---------------------------编写带参数的存储过程，使用系统存储过程/*指导部分：编写带输入和输出的存储过程使用系统存储过程将用户自定义消息写入windows事件查看器执行存储过程需求说明1，创建存储过程，实现管理员登录功能2，创建存储过程，要求时间用户购买游戏卡过程，如果用户要购买的游戏卡的金额超过了用户账户余额，则引发用户自定义的错误，并使用系统存储过程将错误写入到windows时间查看器，完成存储过程，检查windows事件查看器中写入的错误信息实现思路实现管理员登录功能只需要传入参数，即用户名和密码。实现用户购买游戏卡存储过程时，如果用户的余额不足购买游戏卡，则使用raiserror引发自定义的错误，在执行存储过程后，使用xp_logevent扩展过程将用户自定义的错误号写入windows事件查看器中实现步骤1，创建存储过程UP_Admin_Login*/use Company_DBgocreate proc UP_Admin_Login(@uname varchar(20),--输入用户名@pwd varchar(20),--输入密码@tag int output--传出是否登录成功的标志(1表示登录成功 0表示登录失败))asset @tag=0if((select COUNT(*) from UserInf where UserName=@uname and [password]=@pwd and UserRoleId=4)>0)beginset @tag=1end--2, 执行存储过程declare @flag intexec UP_Admin_Login 'admin','admin123', @flag outputif(@flag=1)print '管理登录成功！'elseprint '身份验证失败，无法登录！'go--3，输入代码，创建用户购买游戏卡的存储过程if(exists (select * from sysobjects where name='UP_BuyCard'))drop proc UP_BuyCardgo--4,创建存储过程，存储过程一次购买一张卡，要求输入卡类型，用户名。alter procedure UP_BuyCard(@CardtypeName nvarchar(50),--游戏卡名称@user varchar(20)--用户名)asdeclare @typeid int--卡类型变量declare @sumerror int=0--错误变量declare @cnt int--次数变量select @typeid=id from CardType where CardtypeName=@CardtypeName--根据用户提供的游戏卡名称查询这个游戏的类型IDselect @cnt=COUNT(*) from CardInf where CardtypeId=@typeid--根据游戏的类型ID和游戏卡'未出售'ID查询这个游戏卡的库存剩余数量and CardStateId=(select id from CardState where cstate='未售出')if(@cnt>=1)--检查是否有可以售出的卡begindeclare @balance money,@requiremoney money--声明变：剩余钱，需要钱select @balance=balance from UserInf where UserName=@user--根据用户提供的用户名查找这个用户的账户余额select @requiremoney=price from CardType where Id=@typeid--根据游戏要购买的游戏卡的类型ID查询这个游戏卡的价格select @requiremoney=@requiremoney*(select discount from RoleInf--根据用户提供的用户名查询这个用户的身份ID，再根据身份ID查询则扣率，最后根据则扣率*需要钱，从而查询出这个用户购买游戏卡需要花费的钱where Id=(select UserRoleId from UserInf where UserName =@user))begin--开始购卡begin transaction --开始事物declare @cardno varchar(20)--可以出售的某类游戏卡中帐号的一张select @cardno=MAX(cardno) from CardInf whereCardtypeId=@typeid and CardStateId=(select id from CardState where cstate='未售出')--根据游戏卡类型和游戏状态查找这类游戏的帐号，找出一张赋值给@cardno变量insert into shoppingCart values(@user,(select id from CardType where CardtypeName=@CardtypeName),1)set @sumerror+=@@errorupdate CardInf set CardStateId=2 where CardNo=@cardno--更新卖出去的这张卡号的状态set @sumerror+=@@error--给@sumerror赋值用于查看上一个增删改操作是否出错update UserInf set balance -= @requiremoney where UserName=@user--跟新用户的钱set @sumerror+=@@errorif(@sumerror>0)beginprint 'NO'rollback transaction--回滚事物endelsebeginprint 'OK'commit transaction--提交事物endendendelsebegin--引发用户自定义的错误raiserror('账户的余额不够，请及时充值!',16,1)endgodeclare @err intexec UP_BuyCard '英雄联盟游戏卡','我有钱'set @err=@@ERROR+150505050--消息日志中只能写入消息号大于50000的消息if(@err<>0)--写入错误信息到windows消息日志execute xp_logevent @err, '账户余额不足，请及时充值',informational--联系部分--需求说明--使用存储过程实现用户汇款和用户投诉功能，要求用户汇款后立即审核，并将用户的账户余额返回，汇款银行使用默认值‘工商银行’。存储过程编写完毕后，为用户‘我有钱’汇款1000元--实现思路--用户汇款后要求能够通知用户当前的账户余额，可以通过传出参数实现。汇款银行通过传入参数添加默认值实现goalter procedure UP_Postmonry(@user nvarchar(20),@money money,@bank nvarchar(20)='工商银行',@desc nvarchar(1000),@balance money output)asbegin--汇款代码-------------------------------------------------------1,判断用户的余额是否够购买游戏卡declare @err intselect @balance=Balance from UserInf where UserName=@userif(@balance>=@money)beginbegin transaction--开始事物update UserInf set Balance=Balance+@money where UserName='admin'set @err+=@@ERRORupdate UserInf set Balance=Balance-@money where UserName=@userset @err+=@@ERRORif(@err > 0)rollback transactionelsebeginprint '转账成功!'commit transactionendendelsebeginprint '您的账户余额不足，请及时充值！汇款失败！'end--审核代码-------------------------------------------------------为传出参数赋值----------------------------------------------select @balance=Balance from UserInf where UserName='我有钱'endgodeclare @amount money--参数@bank没有传入具体值，参数使用默认值execute UP_Postmonry @user='我有钱',@money=1000,@desc='购买天堂游戏卡',@balance=@amount outputprint '用户我有钱的余额是'+convert(nvarchar(20),@amount)-----------------------------------------------------视频资料-----------------------------------------------------------------------------------------------------------------------------------------use PPTDemocreate table Tb_Student(TId int identity(1,1) primary key,TGender nvarchar(2),TSalary money,TAge int,TBirthday datetime)-----------------------------------------create table Employees(EmpId int identity(1,1),EmpName varchar(50),EmpGender char(2),EmpAge int,EmpEmail varchar(100),EmpAddress varchar(500))create table Department(DepId int identity(1,1),DepName varchar(50))select * from Employeesselect * from Departmentdrop table Employees---------------------------------------手动增加约束--手动删除一列alter table Employees drop column EmpAddress--手动增加一列alter table Employees add EmpAddress nvarchar(1000)--修改一下EmpEmail的数据类型(varchar(200))alter table Employees alter column EmpEmail varchar(200)--为EmpId增加一个主键约束alter table Employees add constraint PK_Employees_EmpId primary key(EmpID)--非空约束，为EmpName增加一个非空约束(修改列)alter table Employees alter column EmpName varchar(50) not null--为EmpName增加一个唯一约束alter table Employees add constraint UQ_EEmployees_EmpName unique(EmpName)--为EmpGender增加一个默认约束，默认‘男’alter table Employees add constraint DF_Employees_EmpGender default('男') for EmpGender--为EmpGender增加一个检查约束，要求只能是'男'or'女'alter table Employees add constraint CK_Employees_EmpGender check(EmpGender='男' or EmpGender='女')--为年龄增加一个检查约束；年龄在0-120岁之间alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)--创建一个部门表，然后为Eemployee表增加一个DepId列alter table Employees add Employees int --为Department表设置主键。主键列是：DepIdalter table Department add constraint PK_Department_DepId primary key(DepId)--增加外键约束alter table Employees add constraint PK_Employees_Department foreign key(EmpDepId) references Department(DepId)--------------------------------------------------------------------------------------------------删除约束（多个一起删除）-------------------------------------------------------------alter table Employees drop constraint PK_Employees_EmpId,UQ_EEmployees_EmpName,CK_Employees_EmpAge,CK_Employees_EmpGender,DF_Employees_EmpGender-----增加约束（多个一起增加）-------------------------------------------------------------alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120),constraint CK_Employees_EmpGender check(EmpGender='男' or EmpGender='女'),constraint DF_Employees_EmpGender default('男') for EmpGender,constraint UQ_Employees_EmpName unique(EmpName),constraint PK_Employees_EmpId primary key(EmpID)create table Employees(EmpId int identity(1,1) identity(1,1) primary key,EmpName varchar(50) not null unique check(len(EmpName)>2),EmpGender char(2) default('男'),EmpAge int check(EmpAge>0 and EmpAge<120),EmpEmail varchar(100) unique,EmpAddress varchar(500) not nullEmpDepId int foreign key references Department(DepId) on delete cascade--on delete cascade级联删除)--查询所有行所有列select * from Employees--查询所有行部分列select EmpId,EmpName,EmpGender from Employees--查询部分行所有列(使用where条件筛选部分行显示)select * from Employees where EmpAge='20'--给查询结果集中的列起别名（只是把当前查询出来的结果集的列名修改了，对原表没有修改）select EmpId as 员工编号,EmpName as 学生姓名,EmpGender as 学生性别 from Employeesselect 员工编号=EmpId,学生姓名=EmpName,学生性别=EmpGender from Employees--给查询结果集中新增加列select 员工编号=EmpId,学生姓名=EmpName,学生性别=EmpGender,婚否='' from Employees/**SELECT不必和FROM配合使用，可以单独使用获取系统当前系统时间*/select 当前系统时间=GETDATE()select GETDATE() as 当前系统时间select  打野='皇子', 中单='莫干娜', ADC='女警', 辅助='风女'  ------------------------------------------------TOP和DISTINCT-----------------------------------去除重复失败,因为有自增主键，所以原表数据没有重读select distinct * from Employees--DISTINCT关键字，针对已经查询出的结果集进行去除重复--此处的DISTINCT关键字的作用是针对select EmpName,EmpGender from Employees查询结果集去重复，EmpName和EmpGender的值完全一样，可以成功select DISTINCT EmpName,EmpGender from Employees--TOP获取前几条数据，TOP一般都与order by一起使用--TOP--查询年龄最高的前5名select top 5 * from Employees order by EmpAge desc--如果TOP后面不是数字，而是一个表达式一定要使用()把表达式括起来select top (5*20) * from Employees order by EmpAge desc--查询前分之分30select top 30 percent * from Employees order by EmpAge desc--order by 列名--按照年龄，降序排序select * from Employees order by EmpAge desc--按照年龄， 升序排序select * from Employees order by EmpAge asc--默认升序------------------------------------------------聚合函数-----------------------------------统计出所有人的年龄的总和select SUM(EmpAge) as 年龄的综合 from Employees--统计出表中一共有多少条记录select COUNT(*) as 一共的记录 from Employees--统计出表中所有人的平均年龄（因为整数/整数=整数，为了让结果成为小数，可以*0.1）select (select SUM(EmpAge) as 年龄的综合 from Employees)*1.0/(select COUNT(*) as 一共的记录 from Employees) as 平均年龄 from Employees--年龄最大的select MAX(EmpAge) from Employees--年龄最小的select MIN(EmpAge) from Employees--计算平均值select AVG(EmpAge*1.0) from Employees----------------------------------------------------聚合函数的一些其它的问题--------------------------1.聚合函数不统计空值--2.如果使用聚合函数的时候，没有手动group by分组，那么聚合函数会把整个表中的数据作为一组来统计 select *from Employees select count(EmpEmail) from Employees  select AVG(EmpAge) from Employees--AVG()也不统计空值  -------------------------------带条件查询 --select 列 --from 表 --where 条件 --查询没有及格的学生(假设：数学或英语，只要有一门没有及格就叫没有及格)的学号 select tSId from TblScore where tEnglish<60 or tMath<60  --查询年龄在20-30之间的男学生AND select * from MySudent where FAge>=20 and FAge <=30 and FGender='男'  --between...and   在....之间(闭区间，包含两个端点值)BETWEEN select * from MySudent where FAge between 20 and 30 and FGender='男'  --查询出所有班级Id为3，4，5的那些学生 OR select * from MySudent where classid=3 or classid=4 or classid=5 select * from MySudent where classid in(3,4,5)  --对于in或者or查询，如果查询中的条件是连续的几个数字，最好使用>= <=或者between...and 不要使用or或者and  select * from MySudent where classid>=3 and classid<=5     -------------------------------模糊查询   --_ 表示任意的当个字符   --% 表示任意多个任意的字符   --[]表示筛选，范围          -------------------------------空值处理-----------------------------------------------null值无法使用=或<>来进行比较    --查询所有年龄是null的同学信息select * from MySudent where Age is null   --查询所有年龄不是null的同学select * from MySudent where Age is not null--任何值与null进行运算，结果还是nullselect 2000+null-----------------------------------执行顺序---------------------------------------------select *--3.from Score--1.where english>60 and math>60--2.order by english desc,math desc--4.--------------------------------------GROUP BY---------------------------------------------重点！！！/*在使用select查询的时候，又是需要对数据进行分组汇总(即：将现在有的数据按照某列来汇总统计)，这时候就用到GROUP BY语句。select语句中可以使用group by字句将行划分成较小的组，然后，使用聚合函数返回每一个组的汇总信息。分组一般都和聚合函数连用*/create table studenttest(xh int not null,xm varchar(10) not null,xb varchar(2) not null,km varchar(10) not null,cj int )drop table studenttestinsert into studenttest values(1,'韩晓青','女','C语言',55);insert into studenttest values(2,'洪少侠','男','C语言',56);insert into studenttest values(3,'胡友松','男','C语言',57);insert into studenttest values(4,'蒯卫','女','C语言',58);insert into studenttest values(5,'雷雨慧','男','C语言',59);insert into studenttest values(6,'李欢欢','女','C语言',60);insert into studenttest values(7,'李文杰','男','C语言',61);insert into studenttest values(8,'刘梅','女','C语言',62);insert into studenttest values(9,'路俊鹏','男','C语言',63);insert into studenttest values(10,'吕钢','男','C语言',64);insert into studenttest values(11,'潘桂琴','女','C语言',65);insert into studenttest values(12,'钱欢','男','C语言',66);insert into studenttest values(13,'邵旭','男','C语言',67);insert into studenttest values(14,'水玉帅','男','C语言',68);insert into studenttest values(15,'宋元芬','女','C语言',69);insert into studenttest values(16,'王斌','男','C语言',70);insert into studenttest values(17,'闻雪','女','C语言',71);insert into studenttest values(18,'向瑶','女','C语言',72);insert into studenttest values(19,'谢守鹏','男','C语言',73);insert into studenttest values(20,'杨丽敏','女','C语言',74);insert into studenttest values(21,'杨文财','男','C语言',75);insert into studenttest values(22,'张红','女','C语言',76);insert into studenttest values(23,'张明婷','女','C语言',77);insert into studenttest values(24,'张鹏宇','男','C语言',78);insert into studenttest values(25,'张帅','男','C语言',79);insert into studenttest values(1,'韩晓青','女','JAVA语言',22);insert into studenttest values(2,'洪少侠','男','JAVA语言',23);insert into studenttest values(3,'胡友松','男','JAVA语言',24);insert into studenttest values(4,'蒯卫','女','JAVA语言',25);insert into studenttest values(5,'雷雨慧','男','JAVA语言',26);insert into studenttest values(6,'李欢欢','女','JAVA语言',27);insert into studenttest values(7,'李文杰','男','JAVA语言',28);insert into studenttest values(8,'刘梅','女','JAVA语言',29);insert into studenttest values(9,'路俊鹏','男','JAVA语言',30);insert into studenttest values(10,'吕钢','男','JAVA语言',31);insert into studenttest values(11,'潘桂琴','女','JAVA语言',32);insert into studenttest values(12,'钱欢','男','JAVA语言',33);insert into studenttest values(13,'邵旭','男','JAVA语言',34);insert into studenttest values(14,'水玉帅','男','JAVA语言',35);insert into studenttest values(15,'宋元芬','女','JAVA语言',36);insert into studenttest values(16,'王斌','男','JAVA语言',37);insert into studenttest values(17,'闻雪','女','JAVA语言',38);insert into studenttest values(18,'向瑶','女','JAVA语言',39);insert into studenttest values(19,'谢守鹏','男','JAVA语言',40);insert into studenttest values(20,'杨丽敏','女','JAVA语言',41);insert into studenttest values(21,'杨文财','男','JAVA语言',42);insert into studenttest values(22,'张红','女','JAVA语言',43);insert into studenttest values(23,'张明婷','女','JAVA语言',44);insert into studenttest values(24,'张鹏宇','男','JAVA语言',45);insert into studenttest values(25,'张帅','男','JAVA语言',46);insert into studenttest values(26,'赵恒桥','男','JAVA语言',47);insert into studenttest values(27,'赵鹏','男','JAVA语言',48);insert into studenttest values(28,'周亚坤','男','JAVA语言',49);insert into studenttest values(29,'邹瑜','男','JAVA语言',50);insert into studenttest values(30,'左云','女','JAVA语言',51);insert into studenttest values(31,'大神','女','JAVA语言',51);insert into studenttest values(1,'韩晓青','女','HTML',52);insert into studenttest values(2,'洪少侠','男','HTML',53);insert into studenttest values(3,'胡友松','男','HTML',54);insert into studenttest values(4,'蒯卫','女','HTML',55);insert into studenttest values(5,'雷雨慧','男','HTML',56);insert into studenttest values(6,'李欢欢','女','HTML',57);insert into studenttest values(7,'李文杰','男','HTML',58);insert into studenttest values(8,'刘梅','女','HTML',59);insert into studenttest values(9,'路俊鹏','男','HTML',60);insert into studenttest values(10,'吕钢','男','HTML',61);insert into studenttest values(11,'潘桂琴','女','HTML',62);insert into studenttest values(12,'钱欢','男','HTML',63);insert into studenttest values(13,'邵旭','男','HTML',64);insert into studenttest values(14,'水玉帅','男','HTML',65);insert into studenttest values(15,'宋元芬','女','HTML',66);insert into studenttest values(16,'王斌','男','HTML',67);insert into studenttest values(17,'闻雪','女','HTML',68);insert into studenttest values(18,'向瑶','女','HTML',69);insert into studenttest values(19,'谢守鹏','男','HTML',70);insert into studenttest values(20,'杨丽敏','女','HTML',71);insert into studenttest values(21,'杨文财','男','HTML',72);insert into studenttest values(22,'张红','女','HTML',73);insert into studenttest values(23,'张明婷','女','HTML',74);insert into studenttest values(24,'张鹏宇','男','HTML',75);insert into studenttest values(25,'张帅','男','HTML',76);insert into studenttest values(26,'赵恒桥','男','HTML',77);insert into studenttest values(27,'赵鹏','男','HTML',78);insert into studenttest values(28,'周亚坤','男','HTML',79);insert into studenttest values(29,'邹瑜','男','HTML',80);insert into studenttest values(30,'左云','女','HTML',81);insert into studenttest values(31,'哈哈','女','HTML',81);insert into studenttest values(32,'王云','女','HTML',81);insert into studenttest values(33,'旺财','女','HTML',81);select * from studenttest--查询每个科目的人数select km,max(xh) as 科目人数 from studenttest group by km--统计性别人数select 性别=xb,COUNT(xb) from studenttest group by xb--统计每个科目男同学的人数，和男同学的平均分select km,COUNT(*) as 男同学人数,avg(cj) 平均成绩--4from studenttest--1where xb='男'--2group by km--3--当使用了分组语句(group by)或者是聚合函数的时候，在select的查询列表中不能再包含其他列名，--除了该列同时也出现了group by字句中，或者该列也包含了在某个聚合函数中-------------------------------HAVING与where-------------------------------对分组以后的数据进行筛选：使用having--对分组以前的数据进行筛选：使用whereselect km,COUNT(*) as 男同学人数,avg(cj) 平均成绩--4from studenttest--1group by km--2having avg(cj)>60--3-- 因为先执行的having后执行的select所以不可以使用别名，再它之前别名还没有创建--order by可以使用别名，因为order by永远是最后执行，再它之前别名已经创建好了---------------------------------类型转换函数CAST,CONVERTselect '1000'+100print '1000'+100select 100.0+'1000'--失败，将 varchar 转换为数据类型 numeric 时出现算术溢出错误。select 100.0+CAST('1000' as int)select 100.0+CONVERT(int,'1000')select '你的班级编号是:'+1--在将 varchar 值 '你的班级编号是:' 转换成数据类型 int 时失败。select '你的班级编号是:'+CONVERT(nvarchar(1),1)--TableConvert表中bid列是varchar类型的数字，要求按数字大小排序，需要转换类型select * from TableConvert order by CONVERT(int,bid) desc--转换时间格式print convert(varchar(100),getdate(),120)print convert(varchar(10),getdate(),120)---------------------------------UNION联合结果集（集合运算符）-----------------------------------联合：指把结果集的行联合起来5行+4行结果集9行--连接：指吧结果集的列连接起来3列+4列 结果集7列select * from TBStudentunion allselect * from MyStudent--联合不了，因为列数不同，数据类型不兼容----------------UNION进行联合，区别在于：使用union联合会去除重复，从新排列----------------UNION ON进行联合，区别在于：不会去除重复也不会从新排列----------------大多数情况下，联合的时候不需要去除重复，同时要保持数据的顺序，所以一般建议使用 UNION ALLselect tname,tgender,tage from TBStudentunion all select tname,tgender,tage from MyStudent--可以联合，列数相同，数据类型兼容(不会去除重复也不会从新排列)select tname,tgender,tage from TBStudentunion select tname,tgender,tage from MyStudent--可以联合，列数相同，数据类型兼容(使用union联合会去除重复，从新排列)--查询出成绩表中的：最高分，最低分，平均分select MAX(cj) as 最高分,min(cj) as 最低分,avg(cj) as 平均分from studenttestselect最高分=(select MAX(cj) from studenttest),低分分=(select MIN(cj) from studenttest),平均分=(select AVG(cj) from studenttest)select 名称='最高分',分数=MAX(cj) from studenttestunion allselect 名称='最低分',分数=MIN(cj) from studenttestunion allselect 名称='平均分',分数=avg(cj) from studenttest-----------------使用UNION一次插入多行数据SELECT * FROM studenttestINSERT into studenttestselect '37','大王','男','修仙','500' union allselect '37','观音','女','修仙','600' union allselect '37','猪八戒','男','修仙','700' union allselect '37','悟空','男','修仙','800'--在使用union进行插入数据的时候也要注意union会去除重复的。--下面union插入只会插入4条数据。建议使用union allINSERT into studenttestselect '37','大王','男','修仙','500' union select '37','观音','女','修仙','600' union select '37','猪八戒','男','修仙','700' union select '37','悟空','男','修仙','800'union select '37','大王','男','修仙','500' union select '37','观音','女','修仙','600' union select '37','猪八戒','男','修仙','700' union select '37','悟空','男','修仙','800'---------------------------------------向表中插入多条记录--------------------------------查询所有表中所有的数据保存到另外一张表中--用于备份表--意思是从studenttest表中查询数据保存到tempstustudenttest这张表中，如果预先没有tempstustudenttest这张表，--当执行insert into语句的时候会自动创建tempstustudenttest表，--tempstustudenttest表结构和studenttest表结构和自动偏好列一样，但是tempstustudenttest表中没有studenttest表中的约束--insert into语句不能够重复执行，因为每次执行都会创建一个tempstustudenttest表--studentselect * into tempstustudenttest from studenttest--select * from tempstustudenttest--select * from studenttestdrop table tempstustudenttest--用于拷贝表结构，不拷贝数据select * into tempstustudenttest from studenttest where 1<>1select top 0 * into tempstustudenttest from studenttestselect * from tempstustudenttest--使用insert into 表 select。。。from 表--把studenttest表中的数据添加到已有数据的tempstustudenttest表中select * from tempstustudenttestinsert into tempstustudenttest values(50,'大黄蜂','男','机械化',120);insert into tempstustudenttest values(55,'机械公敌','男','科技化',250);insert into tempstustudenttest select * from studenttest where xb='女'---------------------------------------常用的字符串函数--------------------------------1.len() 计算字符的个数print len('我爱你520')--datalength()返回所占用的字节的个数，这个不是字符串函数print datalength('我爱你520')--9print datalength(N'我爱你520')--12--2.print upper('hello world')--转换大写print lower('HELLO WORLD')--转换小写--3.去掉两段的空格print '========='+'           hello           '+' =============='print '========='+ltrim('           hello           ')+' =============='--去掉左边空格print '========='+rtrim('           hello           ')+' =============='--去掉右边空格print '========='+rtrim(ltrim('           hello           '))+' =============='--去掉两段的空格--4.字符串的截取--4.1 left()print left('大野太菜了！！！',5)--大野太菜了--4.2 right()print right('大野太菜了！！！',3)--！！！--4.3 substring()截取字符串print substring('大野太菜了！！！',3,3)--太菜了print substring('大野太菜了！！！',-2,5)--------------------------------------------日期函数------------------------------------------print getdate()print sysdatetime()print dateadd()--增加时间select dateadd(day,200,getdate())select dateadd(month,200,getdate())select dateadd(year,200,getdate())select dateadd(minute,200,getdate())select dateadd(second,200,getdate())select dateadd(hour,200,getdate())--查询入学大于一年的学生，用DATEADD()select * from studenttestwhere DATEADD(DAY,365,jointime)<=GETDATE()--计算两个时间的差select DATEDIFF(YEAR,'1991-5-31',GETDATE())--查询出入职N年的人的个数select 入职时间=DATEDIFF(YEAR,jointime,GETDATE()),人数=COUNT(*) from studenttest group by DATEDIFF(YEAR,jointime,GETDATE())--获取日期的某部分的值@return intprint datepart(year,getdate())print year(getdate())print datepart(month,getdate())print month(getdate())print datepart(day,getdate())print day(getdate())--返回日志的某部分值@return stringprint datename(year,getdate())--查询出，不同年份入职的员工的个数select 入学日期=YEAR(jointime),COUNT(*)from studenttest group by jointime-------------------------------题目create table tonghua(id int identity(1,1) primary key,CellNumber varchar(20),TelNum varchar(20),StartDateTime datetime,EndDateTime datetime)drop table tonghuaselect * from tonghuainsert into tonghua select '001','02088888','2010-07-10 10:00:00','2010-07-10 10:05:03' union allselect '001','02088888','2010-07-11 13:00:00','2010-07-11 13:01:10' union allselect '001','89898989','2010-07-11 14:06:00','2010-07-11 14:09:00' union allselect '002','98987676','2010-07-13 21:06:00','2010-07-13 21:08:08' union allselect '002','02188839389','2010-06-29 20:11:00','2010-06-29 20:16:06' union allselect '001','767676766','2010-07-15 13:16:00','2010-07-15 13:26:00' union allselect '003','0227864656','2010-07-13 11:16:00','2010-07-13 11:17:09' union allselect '003','676765777','2010-07-19 19:26:02','2010-07-19 19:30:33' union allselect '001','89977653','2010-06-19 15:16:02','2010-06-19 15:26:10' union allselect '004','400400400','2010-06-19 23:40:02','2010-06-20 10:10:00'--输出所有数据中通话时间最长的5条记录。select top 5 *,通话时长=datediff(second,startdatetime,enddatetime)from tonghuaorder by 通话时长 desc--输出所有数据中拨打长途号码(对方号码以0开头)的总时长.select * ,通话时间=DATEDIFF(SECOND,startdatetime,enddatetime)from tonghuawhere telnum like '0%'--假设今天是'2010-07-31'--输出本月通话时间总时长最多的前三个呼叫员的编号select top 3 cellnumber,通话时长=sum(datediff(second,startdatetime,enddatetime))from tonghuawhere DATEDIFF(MONTH,startdatetime,'2010-07-31 00:00:00')=0--表示是本月group by cellnumberorder by 通话时长 desc--输出本月拨打电话次数最多的前三额呼叫元的编号select top 3 cellnumber, 拨打电话次数=COUNT (*)from tonghuawhere DATEDIFF(MONTH,startdatetime,'2010-07-31 00:00:00')=0group by cellnumberorder by 拨打电话次数 desc----------------------------------------------第一阶段结束-------------------------------------------------------------------------------------------------------内连接--------------------------------------------------------create table PhoneType(ptId int identity(1,1) primary key,ptName nvarchar(20))create table PhoneNum(pId int identity(1,1) primary key,pTypeId int,pName nvarchar(20),pCellPhone varchar(20),pHomePhone varchar(20))drop table PhoneTypedrop table PhoneNuminsert into PhoneNumselect '1','刘备','13000000000','7000000' union allselect '1','关羽','13000000001','7000001' union allselect '1','张飞','13000000002','7000002' union allselect '2','曹操','13000000003','7000003' union allselect '2','大乔','13000000004','7000004' union allselect '3','孙权','13000000003','7000003' union allselect '3','小乔','13000000004','7000004' insert into PhoneTypeselect '朋友' union allselect '同事' union allselect '同学' union allselect '家人' select * from PhoneTypeselect * from PhoneNumselect * from PhoneType,PhoneNum--笛卡尔积select * from PhoneNum inner join PhoneType on PhoneNum.pTypeId=PhoneType.ptId--7条select * from PhoneNum inner join PhoneType on PhoneNum.pTypeId<>PhoneType.ptId--21条--查询的时候，如果表中有重名的列，此时，应该在通过 表名.列名 的方式来限定指定哪张表中的select pn.pid,pn.pname,pn.pcellphone,pt.ptnameFROM PhoneNum as pn inner join PhoneType as pt on pn.pTypeId=pt.ptId------------------------------------------------CASE多分支语句--------------------------------------------------------create table [user]([uid] int identity(1,1) primary key,name nvarchar(20),[level] int,  )insert into [user]select '犀利哥','1' unionselect '小月月','2' unionselect '芙蓉姐姐','3' select * from [user]------相当于if-elseselect*,头衔=case when [level]=1 then '菜鸟'when [level]=2 then '老鸟'when [level]=3 then '大师'else '骨灰级大师'endfrom [user]------相当于switchselect*,头衔=case [level]when 1 then '菜鸟'when 2 then '老鸟'when 3 then '大师'else '骨灰级大师'endfrom [user]select * from studenttestselect *,等级=case when cj>90 then '优秀'when cj>80 then '良好'when cj>70 then '中等'when cj>59 then '及格'when cj>0  then '不及格'else '没有参加考试'endfrom studenttest--当A列大于B列时候选择A，当B列大于C列的时候选择ccreate table ABC(a int,b int,c int)insert into ABCselect '10','20','30' unionselect '20','30','10' unionselect '30','20','20' unionselect '10','20','30' select * from ABCselect 新A=casewhen a>b then aelse bend,   新B=casewhen b>c then belse cendfrom abc-------------------create table qiudui(scoreId int identity(1,1) primary key,teamName nvarchar(20),gameDate datetime,gameResult nvarchar(20))insert into qiuduiselect '公牛','2012-05-01','胜' unionselect '小牛','2012-06-01','胜' unionselect '奇才','2012-05-15','负' unionselect '湖人','2012-07-10','胜' unionselect '公牛','2012-06-02','胜' unionselect '公牛','2012-07-09','胜' unionselect '奇才','2012-03-12','负' unionselect '公牛','2012-09-11','负' select * from qiudui-----第一步select 球队名称=teamname,胜=casewhen gameResult='胜' then 1else 0end,负=casewhen gameResult='负' then 1else 0endfrom qiudui-------第二步，第一步和第二步一起执行看看就懂了select 球队名称=teamname,胜=sum(casewhen gameResult='胜' then 1else 0end),负=sum(casewhen gameResult='负' then 1else 0end)from qiuduigroup by teamname-------上面题目使用count实现select 球队名称=teamname,胜=count(casewhen gameResult='胜' then '胜'else nullend),负=count(casewhen gameResult='负' then '负'else nullend)from qiuduigroup by teamname-------------------create table StudentScroe(aotuid int identity(1,1),studentid int,coursename nvarchar(20),score int)drop table StudentScroeinsert into StudentScroeselect '001','语文','90' union allselect '001','数学','99' union allselect '001','英语','95' union allselect '002','语文','80' union allselect '002','数学','89' union allselect '002','英语','91' union allselect '003','语文','86' union allselect '003','数学','92' union allselect '003','英语','77' -----第一步select * from StudentScroeselect studentid,语文=casewhen coursename='语文' then scoreelse nullend,数学=casewhen coursename='数学' then scoreelse nullend,英语=casewhen coursename='英语' then scoreelse nullendfrom StudentScroe-----第二步select studentid,语文=max(casewhen coursename='语文' then scoreelse nullend),数学=max(casewhen coursename='数学' then scoreelse nullend),英语=max(casewhen coursename='英语' then scoreelse nullend)from StudentScroegroup by studentid-------------------------------------------索引-----------------------------------------------1.索引的目的，提高查询效率--2.索引分两种--2.1聚集索引(物理)，一个表中只能有一个聚集索引--2.2非聚集索引(逻辑)，一个表中可以有多个聚集索引--3.增加索引后，会增加额外的存储空间。同时降低了增加新纪录，修改,删除的效率--创建非聚集索引create nonclustered index 索引名称 on [column]--列--创建唯一非聚集索引create unique nonclustered 索引名称 index [column]--列--创建聚集索引create clustered index 索引名称 on [column]--列--删除索引drop index [column]--列-------------------------------------------子查询---------------------------------------------/*子查询：把一个查询的结果在另外一个查询中使用叫子查询子查询的基本分类1.独立子查询：子查询可以独立运行2.相关子查询子查询中引用了父查询中的结果*/---------------------------------------分页查询------------------------------------------------------------------------------使用TOP分页--要分页查询，或者分页显示，首先要确定按照上面排序，然后才能确定哪些记录该在第一页，哪些记录该在应该在第二页--第一页显示7条数据--第1页select top 7 * from studenttest order by xh asc--查询前两页的数据select top (7*2) * from studenttest order by xh asc--第2页--2.1先查询出(2-1)页的数据的xhselect top 7 * from studenttest where xh not in (select top 7 xh from studenttest order by xh asc)order by xh---------------------------------------使用row_number()实现分页--1.为数据排序人，然后编号。select *,Rn=row_number() over(order by xh asc) from studenttest--2.根据用户要查看的每页记录条数，以及要查看第几页。确定应该查询第几条到第几条--每页显示7条，要查看第4页--从3*7+1.....4*7select *from(select *,Rn=row_number() over(order by xh asc) from studenttest) as twhere t.Rn between 3*7+1 and 4*7---------------------------------------连接查询-----------------------------------------内连接：值显示那些两张表中可以匹配的数据--左外连接：坐标为主，左表的数据全部显示，左右表匹配的数据显示在右表，不匹配的数据显示NULL-----------------------------------事务，索引，视图，同义词/*1.事务的概念事务时一种机制，它包含了一组数据库操作命令，而且将所有的命名作为一个整体一起向数据库提交或撤销，这组命令要么都执行，要么都不执行，所以事务时一个不可分割的逻辑工作单元2.事务的特点虽然事务的每个执行单元都不相同，但所有的事物都具有4个特征，原子性，一致性，隔离性，持久性，简称ACID3.事务的隔离级别事务的4个特征中，隔离性用来解决多用户操作中的并发冲突问题。隔离级别：未提交读(read uncommitted)已提交读(read committed)重复读 (repeatable read)可串行化(serializable)未提交读：事务之间最低的隔离级别。改级别只能保证不会读取损坏的数据，即事务之间没有什么隔离，事务能够读取其他食物正在修改并未提交的数据。这种隔离的级别无法确保数据的正确性已提交读：SQL SERVER 默认的隔离级别。改隔离级别能保证其他事务不能读取当前事务正在修改但未提交的记录。重复读：这中隔离级别比较高，能确保其他事务不能修改当前事务中正在读取但未提交的数据可串行化：最高的隔离级别。事务之间完全隔离，事务之间按串行的方式执行，所以在这种级别的隔离下不存在并行化的操作。要访问其他事务操作的数据，一定要等其他食物完全完成提交以后才能进行*/----------------------------------视图--视图是虚拟表，只能存查询语句，--特点：安全，简化操作--create view viewname as <select 语句>--视图的查询语句不要order by排序------------------------------开始事务begin transactiondeclare @sum int=0--在转账之前最好通过if-else判断，不要让程序发生异常报错update bank set balance=balance-1 where name='你好'set @sum=@sum+@@errorupdate bank set balance=balance+1 where name='胡伟'set @sum=@sum+@@error--只要有一条sql执行出错，那么最后的@sum就不是0if @sum<>0begin--表示出错了--回滚rollbackendelsebegin--如果没有出错，则提交该事务commitend--自动提交事务--当执行一条sql语句的时候，数据库自动帮我们打开一个事务，当语句执行成功，数据库自动提交事务，执行失败，数据库自动回滚事务--隐式事务--每次执行一条sql语句的时候，数据库自动帮我们打开一个事务，但是需要我们手动提交事务，或者回滚事务set IMPLICIT_TRANSACTIONS onset IMPLICIT_TRANSACTIONS offINSERT INTO BANK VALUES ('有钱人','888888')SELECT * FROM bankcommit--显示事务：需要手动打开事务，手动提交事务或者回滚事务begin trancommit tranrollback tran-------------------存储过程create proc usp_helloworldasbeginprint 'hello world'endexec usp_helloworldcreate proc usp_selectEmployeeasbeginselect * from Employeeendexec usp_selectEmployee--修改存储过程alter proc usp_selectEmployeeasbeginselect * from Employee where DeptNo='d01'endexec usp_selectEmployee--创建一个带两个参数的存储过程create proc usp_add_number@n1 int,@n2 intasbeginselect @n1+@n2endexec usp_add_number 100,500--use DB_Teachercreate proc usp_DB_Teacher@gender char(2),@studentaddress char(20)asbeginselect * from student where studentsex=@gender and studentaddress=@studentaddressendexec usp_DB_Teacher '男','湖北襄樊'--设置存储过程的参数的默认值--设置参数的默认值以后，调用存储过程的时候要明确实参赋值的形参对象create proc usp_add_number1@n1 int,@n2 int=50asbeginselect @n1+@n2endexec usp_add_number1 100create proc usp_add_number2@n1 int=80,@n2 intasbeginselect @n1+@n2endexec usp_add_number2 @n2=100---------------------------------------带输出参数的存储过程--当在存储过程当中需要返回多个值的时候，就可以使用输出参数来返回这些值。use DB_Teachergocreate proc usp_show_student@gender char(2),@count int output --输出参数asbeginselect * from student where studentsex=@gender--把查询语句查询到的记录条数赋值给变量@countset @count=(select COUNT(*) from student where studentsex=@gender)end--调用存储过程--调用带有输出参数的存储过程的时候，需要定义变量，将变量传递给输出参数，--在存储过程中使用的输出参数，其实就是你传递进来的变量。declare @count intexec usp_show_student @gender='男',@count=@count outputprint @count-------------使用存储过程编写分页查询-------------use PPTDemogoselect * from studenttestgoalter procedure usp_fenye@pagesize int=7,--每页记录条数@pageindex int=1,--当前要查看第几页记录@recordcount int output,--总的记录条数@pagecount int output--总的页数asbegin--1，编写查询语句，要把用户的数据查询出来select t.xh, t.xm, t.xb, t.km, t.jointime  from(select *,rn=ROW_NUMBER() over(order by xh asc) from studenttest ) as twhere t.rn between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize--2,计算总的记录条数set @recordcount=(select COUNT(*) from studenttest)--3,计算总的页数set @pagecount=ceiling(@recordcount*1.0/@pagesize)end--执行存储过程declare @i int,@j intexec usp_fenye @recordcount=@i output,@pagecount=@j outputprint '所有数据'+convert(varchar(10),@i)print '页数'+convert(varchar(10),@j)------------------------------ 通过set赋值，与select赋值的区别use PPTDemogodeclare @a int--set @a=(select COUNT(*) from studenttest)--set赋值select @a=COUNT(*) from studenttest--select赋值print @aset @a=1select @a=1--当通过set为变量赋值的时候，如果查询语句返回的不止一个值的时候直接报错--但是，当通过select变量赋值的时候，如果查询语句返回的不止一个值的时候，那么会将最后一个结果赋值给该变量------------------------------------触发器use Company_DBcreate table HongFengHai(studentid int,studentname nvarchar(20),studentgender nvarchar(2),studentclass nvarchar(20))select * from HongFengHaiselect top 0 * into 测试触发器 from HongFengHaigo--创建一个触发器create trigger trigger_delete_HongFengHai on HongFengHaiafter deleteasbegininsert into 测试触发器 select * from deletedenddelete from HongFengHai where studentclass='c语言'