mssql:SQLServer笔记

图标

豆瓜

豆瓜网

豆瓜网专栏

豆瓜 图标 2021-04-08 11:30:37

1>From 表
2>where 条件
3>group by 列
4>Having 筛选条件
5>select 5-1>被分组列,聚合函数列5-2>distinct>5-3>top
6>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 null
EmpDepId 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 =long
int =int
smallint =shotr
tinyint =byte 0-255
bit 1~0

小数型
decimal
numeric 两个一样

货币型
money

2.字符串类型
char
nchar
varchar
nvarchar
text
ntext
varchar(max)
nvarchar(max)

3.时间类型
datetime

4.二进制
binary 固定长度
vninary 可变长度

-------------------------------------------------------
带n的和不带n的区别
char(2) 表示可以储存两个字节。ab,12,胡(英文,数字1个字节,中文2个字节)

nchar(2) 表示无论存储中文还是英文,数字,每个字符都占用两个字节。ab,12,胡伟(英文,数字2个字节,中文2个字节)

不带n的这些数据类型,长度最长可以设置为8000,
带n的这些数据类型,长度最长可以设置为4000

char(8000)
varchar(8000)

nchar(4000)
nvarchar(4000)
-------------------------------------------------------
带var的和不带var的区别
nchar 不带var表示,固定长度
varchar 带var表示,可变长度

//固定长度,存储1字符也是要占用10个字节的,会自动补9个空格
char(10) 1 10字节
1111111111 10字节

//可变长度,会根据实际储存数据的大小动态重新分配存储空间,相对来说节省存储空间
varchar(10) 1 1字节
11111 5字节

//10,表示最多10个字节,如果存储的数据超过了10个字节,那么无论是固定长度还是可变长度都会报错的。
-------------------------------------------------------
text 淘汰了,等于varchar(max)
ntext 淘汰了,等于nvarchar(max)

varchar(max) max表示4G
nvarchar(max) max表示4G

-------------------------------------------------------数据库文件
数据文件 主要数据文件 有且只有一个 .mdf(primary data file 的缩写)
次要数据文件 0或者多个 .ndf(primary data file 的缩写)
日志文件 无 至少一个 .ldf(log data file 的缩写)

1. 字符串函数:
CHARINDEX CHARINDEX('ab', 'cdab') 返回 3 返回'ab'在'cdab'中的位置,
SUBSTRING SUBSTRING('abc', 2, 2) 返回 'bc' 从第2个位置开始截取长度为2的字符串
LEN LEN('1个逗逼') 返回 4 返回字符串的长度(非字节长度)
UPPER/LOWER UPPER('aBc')/LOWER('aBc') 返回'ABC'/'abc' 将字符串中的字母转换为大写/小写
LTRIM/RTRIM LTRIM(' abc')/RTRIM('abc ') 返回'abc'/'abc' 去掉字符串左边/右边的空格 同时去掉两边空格 RTRIM(LTRIM(' abc '))
REPLACE REPLACE('abc', 'b', 'x') 返回 'axc' 将'abc'中的'b'替换为'x'
STUFF STUFF('abcd', 1, 2, '你好') 返回 '你好cd' 删除从第1个字符开始,长度为2的字符串,并插入 你好
2. 日期函数
GETDATE GETDATE() 返回当前日期
DATEADD DATEADD(mm, -2, GETDATE()) 返回当前日期-2月
DATEDIFF DATEDIFF(dd, '1989-07-14', GETDATE()) 返回两个日期之间的间隔
DATENAME DATENAME(DW, GETDATE()) 返回 '星期几' 以字符串形式返回当前日期指定的部分
DATEPART DATEPART(DW, GETDATE()) 返回 一个星期的第几天 以整数形式返回当前日期指定的补分
3. 数学函数
CEILING/FLOOR CEILING(24.1)/FLOOR(24.4) 返回 25/24 返回大于24.1的最小整数、返回小于24.1的最大整数
ROUND ROUND(748.35, 1) 返回 748.40 四舍五入到小数点后1位
4. 系统函数
CONVERT CONVERT(VARCHAR(3), 123) 返回 '123' 转换数据类型
DATALENGTH DATALENGTH('1个逗逼') 返回 7 返回任何数据类型的字节数,汉字2字节

-------------------------------------------------------
--在这里编写SQL语句命令
--1.创建一个数据库
create database MyDatabaseOne
--2.删除数据库
drop database MyDatabaseOne
--3.创建数据库的时候设置一些参数选项
create database MyDatabaseOne
on--指定主文件的属性
(
--配置主数据的选项
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%--每次增长
)
-------------------------在数据库中创建一个表-------------------------
--将代码环境切换到MyDatabaseOne
USE MyDatabaseOne
create 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 字句--删除表中某一条

---创建一个员工表---
--<员工表>:
USE MyDatabaseOne
GO
create 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 Employees
use MyDatabaseOne
create 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//(主键)约束 唯一识别每一条记录的标志,可以由多列共同组成**只能有一个,不许重复,不许为null
identity//(自增)约束 列值自增,一般使用此属性设置的列作为主键identtity(1,1)
unique//(唯一)约束 可以使用unique约束确保在非主键列中部存在重复值,但是值可以为null

域完整性:
check(检查)约束//用于限制列中的值的范围()
foreign key(外键)约束//一个表中的foreign key指向另一个表中的primary key
default(默认值)约束//用于向列种插入默认值default'中国'
not null(非空)约束//用于强制列不接受null值
引用完整性:
引用完整性是指两个表的主键和外键的数据对应一致,它建立在外键和主键的关系之上,在sql server中,引用完整性的作用表现在一下3个方面
1.禁止在子表中添加主表中不存在的记录
2.禁止修改主表的值
3.禁止删除子表中的有对应记录的主表记录
用户自定义完整性:
主要是规则rule、约束constraint和触发器trigger
StudentNum 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_Teacher
insert into Tb_Teacher(TeacherName,TeacherTel) 插入部分值
values('火神','22022022022')
select * from Tb_Teacher
insert into Tb_Teacher
values('八神',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 union
select '200709004','张小飞',21,'中国','2007-09-02',2000.5 //最后一行不需要union


-----------------------------------------------------------------------强行插入
--启动自动编号列插入值
--启动某个表的“自动编号列”手动插入值得功能
set identity_insert Tb_Teacher on
insert into Tb_Teacher(TeacherID,TeacherName,TeacherTel)
values(10086,'移动SB','10086')
set identity_insert Tb_Teacher off
select * from Tb_Teacher

--在SQL语句中的直接写的字符串中,如果包含中文,一定在字符串前面加N
(因为当排序规则不是简体中文的时候会乱码)
例子
values(10086,N'移动SB','102016/7/13086')

-----------------------------------------------------------------------打开和关闭查询结果窗口:ctrl+r

-----------------------------------------------------------------------更新语句:
--update 表名 set 列=新值,列2=新值,....where 条件
--update语句如果不加where条件,那么表示对表中所有条件都进行修改,所以一定要加where条件
select * from Tb_Teacher
update Tb_Teacher set TeacherAge=TeacherAge+1,TeacherName = TeacherName+'(男)' where TeacherAge = 26
update Tb_Teacher set Age=30 where Name='大蛇丸' or Age<25
select * from Tb_Teacher

--删除数据语句
--delete from 表名 where....
--delete语句如果不加where条件,表示将表中所有的数据删除,加上where条件后,会按照where条件进行删除
--删除Tb_Teacher表中的所有数据,自动编号没有回复到默认,仍然继续编号
delete from Tb_Teacher
select * from Tb_Teacher
insert into Tb_Teacher
values('胡伟',25,200,'15271100220')
select * from Tb_Teacher
insert into Tb_Teacher
values('杨磊',26,300,'15271200220')
insert into Tb_Teacher
values('小军',27,400,'15271300220')
insert into Tb_Teacher
values('张衡',28,500,'15271400220')

外键关系-级联,
级联删除将先删除子表中的相应记录,再删除主表记录


--truncate table 表名
--如果要删除表中全部数据,那么建议使用truncate
--truncate特点:
--1.truncate语句不能跟where条件(无法根据条件来删除,只能全部删除)
--2.truncate同时自动编号恢复到初始值
--3.使用truncate删除表中的所有数据要比delete效率高的多的多。
--4.truncate 删除数据,不触发触发器
------------------------------------------------------------------------------------------------------------------------
use mstanford
drop table Tb_Student
create 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_Student
update Tb_Student set StuTime='2009-09-01' --更新数据

------------------------增加数据 INSERT INTO
select * from Tb_Student_Coures
insert 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' --更具条件更新数据


------------------------简单查询 SELECT
select * 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 BY
update 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 PERCENT
select top 3 * from Tb_Student_Coures --查询表中前3行
select top 1 percent * from Tb_Student_Coures --查询表中1%行(percent百分比)
select top 50 percent * from Tb_Student order by StudentNo desc,Tuition asc --查询表中1%行(percent百分比)StudentNo 降序,Tuition 降序

------------------------条件查询 WHERE --逻辑运算符 'NOT' 'AND' 'OR' 'IS NULL'返回TRUE 'IS NOT NULL'返回FALSE
select * 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_Coures
update mstanford.dbo.Tb_Student_Coures set CouresTime='2009-09-09',CouresName='jsp',StudentNo='200709004' where SCNo=43
delete 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=3
select 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 Products
select 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(*)>100
select 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(*)>100
select * from Orders

------------------------------------------------------------作业1
select 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 Products
UPDATE Products set ProductName='[杨磊牛逼]' where ProductID=1
select 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 Employees

select COUNT(*) AS 记录次数,COUNT(Region) AS Region字段值的个数 from Employees--计算记录次数和Region not is null 的次数
select * from Orders
select CustomerID,OrderID from Orders where OrderID>11011 and EmployeeID>2
select CustomerID,COUNT(*) as 订单数量 from Orders where OrderID>11011 group by CustomerID having COUNT(*)>2

select * from Customers
select Country as 国家,COUNT(CustomerID) as 客户数量 from Customers group by Country
select Country as 国家,COUNT(CustomerID) as 客户数量,CompanyName as 公司名称名称 from Customers where CompanyName like 'b%' group by Country,CompanyName
select 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,Products
where 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.C
select * from Categories
select * from Products
select * 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 o
where c.CategoryID=c.CategoryID
and p.ProductID=o.ProductID
and CategoryName in('beverage','condiments')
and ProductName like 'c%'
and o.OrderID>1060

------------------------------------------------------------内连接
--内连接也称为等同连接,返回的结果是两个表中所有相匹配的数据,舍弃不匹配的数据
select * from Products
select * from Categories
select Categories.CategoryID as 种类ID,Categories.CategoryName as 种类名称,
Products.CategoryID as 种类ID,Products.ProductName as 种类名称
from Categories join Products
on Categories.CategoryID=Products.CategoryID
where CategoryName in('beverages','condiments')
and ProductName like 'c%'

select * from Customers
select * from Orders

select kh.CompanyName as 客户公司,kh.ContactName as 客户名字,kh.Phone as 客户电话,dd.OrderID as 订单编号,dd.OrderDate as 订单日期
from Customers as kh,Orders as dd
where kh.CustomerID=dd.CustomerID
order 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 dd
on kh.CustomerID=dd.CustomerID
order by 订单编号

------------------------------------------------------------外连接
select * from Customers
select * from Employees
select kh.City as 客户所在城市,yg.FirstName+yg.LastName as 员工姓名,kh.ContactName as 客户姓名
from Employees as yg right join Customers as kh
on kh.City=yg.City


--全外部链接full on...on
select * from Customers
select * from Orders
select Orders.OrderID 订单编号,Orders.OrderDate 订单日期,Customers.CompanyName 客户公司
from Customers full join Orders
on Customers.CustomerID=Orders.CustomerID

-----------------------------------------------------------SQL SERVER执行顺序

--SELECT...
--FROM...
--WHERE...
--GROUP BY...
--HAVING...
--ORDER BY...


--查询供货商的公司名称和所供应的商品名称
select * from Suppliers
select * from Products
select * from [Order Details]
select s.CompanyName,p.ProductName
from Suppliers s,Products p
where s.SupplierID=p.SupplierID

select s.CompanyName,p.ProductName
from Suppliers s join Products p
on s.SupplierID=p.SupplierID

select s.CompanyName,p.ProductName,o.OrderID
from Suppliers s,Products p,[Order Details] o
where s.SupplierID=p.ProductID and p.ProductID=o.ProductID

select Suppliers.CompanyName,Products.ProductName,[Order Details].OrderID
from Suppliers join Products on Suppliers.SupplierID = Products.SupplierID join [Order Details] on Products.ProductID = [Order Details].ProductID

select * from Customers
select * from Suppliers
select c.CompanyName 客户姓名,c.[Address] 客户地址,s.CompanyName 供货商公司,s.ContactName 供货商联系人
from Customers c left join Suppliers s
on c.City=s.City

select * from Orders
select * from Employees
select * from Customers
select 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)图 vision
PowerDesigner 概念数据模型
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_DB
on(
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 Office
values ('C01','中山北路200号')
insert into Office
values ('C02','北京中路35号')
insert into Office
values ('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 Employee
values ('E001','Tom','凯撒大厦501','021-45364743','1980-10-02','2004-04-06','D01','C01')
insert into Employee
values ('E002','Jack','凯撒大厦502','021-45364743','1980-01-03','2004-05-16','D01','C01')
insert into Employee
values ('E003','White','凯撒大厦503','021-45364743','1980-01-04','2003-05-06','D02','C01')
insert into Employee
values ('E004','Smith','凯撒大厦504','021-45364743','1980-05-02','2000-05-22','D02','C02')
insert into Employee
values ('E005','John','凯撒大厦505','021-45364743','1980-06-01','2004-02-13','D02','C02')
insert into Employee
values ('E006','Slider','凯撒大厦506','021-45364743','1982-08-02','2005-03-12','D03','C02')
insert into Employee
values ('E007','Buth','凯撒大厦507','021-45364743','1983-02-12','2006-05-06','D03','C02')
insert into Employee
values ('E008','Jennifer','凯撒大厦508','021-45364743','1980-11-02','2004-05-16','D01','C03')
insert into Employee
values ('E009','Kelly','凯撒大厦509','021-45364743','1980-01-23','2004-05-22','D01','C03')
insert into Employee
values ('E010','Winston','凯撒大厦510','021-45364743','1978-10-02','2002-09-06','D01','C03')
insert into Employee
values ('E011','Joy','凯撒大厦511','021-45364743','1980-11-12','2004-12-06','D01','C03')

drop table Office
drop table Department
drop table Employee

select * from Office
select * from Department
select * from Employee

use Company_DB

declare @DeptNo nvarchar(10)='D01'--声明变量并赋值
select @DeptNo

declare @EmpNo nvarchar(10)='胡伟'--声明变量
print @EmpNo

--------------------------------------------------------------------------------
use Company_DB
go
--查询Dept='D10'的员工号
declare @DeptNo nvarchar(10)='D01'
select EmpNo from Employee where DeptNo=@DeptNo

[email protected]��部变量@EmpNo
declare @EmpNo nvarchar(10) --定义变量
declare @DeptNo nvarchar(10)='D01'--定义点亮并且赋值
select @EmpNo=EmpNo from Employee where DeptNo=@DeptNo
select @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 @EmpNo
update 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 @EmpNo
update Department set Principal=@EmpNo where DeptNo=@DeptNo

go
select * from Department
go

print '错误号码'+convert(nvarchar(225),@@error
select @@VERSION as '版本代号'

------------------------------------------------运算符-----------------------------------
declare @OptNumber int=12436
--print '转换之前'+@OptNumber 失败int要想和varchar格式一起打印需要把int转换成varchar
print '转换前:'+convert(varchar(5),@optNumber)
declare @reverse varchar(5)
--取除个位数
declare @unit int = @OptNumber%10--6
set @reverse=(CONVERT(varchar(1),@unit))--6赋值给倒序变量,赋值顺序左→右
print '逆序number:'+@reverse
--去掉个位数,变量OptNumber将变成1243
set @OptNumber=@OptNumber/10--1243
set @unit=@OptNumber%10--3
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse
set @OptNumber=@OptNumber/10--124
set @unit=@OptNumber%10--4
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse
set @OptNumber=@OptNumber/10--12
set @unit=@OptNumber%10--2
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse
set @OptNumber=@OptNumber/10--1
set @unit=@OptNumber%10--1
set @reverse=@reverse+CONVERT(varchar(1),@unit)
print '逆序number:'+@reverse

-----------------求1,2,3........100之间的奇数和
declare @tableNum table(Num int)--虽然求奇数不需要table类型变量,但是为了演示位运算符,所以定义table类型变量以便查询
declare @loop int =1
while @loop<=100--循环次数
begin
insert into @tableNum values(@loop)
set @loop=@loop+1
end
select SUM(num) from @tableNum where Num & 1=1
-----练习
declare @table table(num int)
declare @i int =1
while @i<=100
begin
insert into @table values (@i)
set @i=@i+1
end
select 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 Expertise

IF 2<=ALL(SELECT SKillLevel FROM Expertise)
PRINT '全部员工技能都通过了2级'
ELSE
PRINT '有员工技能不达标,还需要培训'


-------------------------------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级'
ELSE
PRINT '全部员工的技能不达标,需要加强练习'

-------------------------------EXISTS关键字---------------------
--Exists:判断查询的结果集中是否存在数据,若存在数据,则结果为true,否则结果为false
--注意:可以使用not exists
select * from Employee
if Exists (SELECT * FROM Employee WHERE EMPADDRESS IS NULL)
PRINT '有员工没有填写住址'
ELSE
PRINT '全部都填写住址了'
----------------------------------------------------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 Salary
select * from Expertise
select * from Office
select * from Department
select * from Employee

insert 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级以上且最高,给这个员工增加基本工资500
declare @emp nvarchar(10)
declare @level int
declare @salary money
--查询最高技能等级的员工
select @emp=EmpNo from Expertise order by SKillLevel
print @emp--E006
select @level=MAX(SKillLevel) from Expertise
print @level--4
if @level>=4
begin
select @salary=Salary from Salary where EmpNo=@emp
insert into Salary values (@emp,@salary+500,GETDATE())
end
GO

-------------------------------------IF ELSE 条件语句-------------------------------------
/**
if-else
作用:进行条件判断
工作原理:条件成立执行if语句块,不成立else语句块
注意:
else子句可以省略
在if,else子句均可以嵌套if-else结构
*/
declare @worktime int
declare @emp nvarchar(10)
declare @salary money
select @emp=EmpNo,@worktime=DATEDIFF(YYYY,hireDate,GETDATE()) from Employee order by DATEDIFF(YYYY,hireDate,GETDATE()) asc
print @emp
if @worktime>=4
begin
select @salary=Salary from Salary where EmpNo=@emp
insert into Salary values (@emp,@salary+1000,GETDATE())
end
select * from Salary

-------------------------------------WHILE 语句-------------------------------------
/*
while
作用:进行循环
工作原理:先判断后执行
**/
select * from Expertise

declare @count int
while(1=1)--无限循环
begin
select @count=COUNT(*) from Expertise where SKillName like '%c%' and SKillLevel<3
if(@count>0)
begin
update Expertise set SKillLevel+=1 where SKillName like '%c%' and SKillLevel<3
end
else
break--没有员工C语言技能低于3级后就退出循环
end
----------------------------------CASE 分支语句--------------------------------------------------
/*
case-end
作用:类似多重条件结构,用于进行多路分支
case 字段名
when 值1 then 返回值1
when 值2 then 返回值2
...
else 返回值n
end
工作原理:将字段的值逐一与when语句之后的值进行匹配,若存在匹配项,则返回then之后值,若不存在匹配项,则返回else之后返回值,其中else子句可以省略
case
when 条件1 then 返回值1
when 条件2 then 返回值2
...
else 返回值n
end
工作原理:将逐一判断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')
go
select * from TimeWork
drop table TimeWork

select 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 desc

select * 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 TimeWork
group by workdate
order by workdate desc
----------------------------------RETURN 语句--------------------------------------------------
/*
break:跳出循环结构
return:跳出当前的批处理,而进入下一个批处理的执行
goto:必须和label一起配合使用,跳转到相应label标签处
*/

declare @num int=0
while(1=1)
begin
set @num+=1
if(@num>10)
RETURN
PRINT @num
end
go
select @@CONNECTIONS as '连接数量'
go

----------------------------------GOTO 语句--------------------------------------------------
SELECT * FROM Expertise
declare @num1 int
declare @num2 int
LABEL1:
PRINT '最高的技能级别就是6级了,不能再升级了'
WHILE(0=0)
BEGIN
SELECT @num1 =COUNT(*) FROM Expertise WHERE SKillLevel<2
IF(@num1>0)
BEGIN
UPDATE Expertise SET SKillLevel=SKillLevel+1
SELECT @num2=COUNT(*) FROM Expertise WHERE SKillLevel>6--查看Expertise表中SKillLevel列有几行大于6的数据并且把这个数据赋值给@num2
IF(@num2>0)--如果@num2>0,
BEGIN
GOTO LABEL1--跳转到锚点LABEL1执行
END
END
END

PRINT @@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 orders
SELECT * FROM orders
-------------一下两张表union all的表
select
商品编号=ProductID,销售详情='销售数量',
[1月]=sum(case datepart(month,[Date])
when 1 then Number
else 0
end),
[2月]=sum(case datepart(month,[Date])
when 2 then Number
else 0
end),
[3月]=sum(case datepart(month,[Date])
when 3 then Number
else 0
end),
[4月]=sum(case datepart(month,[Date])
when 4 then Number
else 0
end),
[5月]=sum(case datepart(month,[Date])
when 5 then Number
else 0
end),
[6月]=sum(case datepart(month,[Date])
when 6 then Number
else 0
end),
[7月]=sum(case
when datepart(month,[Date])=7 then Number
else 0
end),
[8月]=sum(case
when datepart(month,[Date])=8 then Number
else 0
end),
[9月]=sum(case
when datepart(month,[Date])=9 then Number
else 0
end),
[10月]=sum(case
when datepart(month,[Date])=10 then Number
else 0
end),
[11月]=sum(case
when datepart(month,[Date])=11 then Number
else 0
end),
[12月]=sum(case
when datepart(month,[Date])=12 then Number
else 0
end)
from orders
group by ProductID
union all
select
商品编号=ProductID,销售金额='销售金额',
[1月]=sum(case datepart(month,[Date])
when 1 then [money]
else 0
end),
[2月]=sum(case datepart(month,[Date])
when 2 then [money]
else 0
end),
[3月]=sum(case datepart(month,[Date])
when 3 then [money]
else 0
end),
[4月]=sum(case datepart(month,[Date])
when 4 then [money]
else 0
end),
[5月]=sum(case datepart(month,[Date])
when 5 then [money]
else 0
end),
[6月]=sum(case datepart(month,[Date])
when 6 then [money]
else 0
end),
[7月]=sum(case
when datepart(month,[Date])=7 then [money]
else 0
end),
[8月]=sum(case
when datepart(month,[Date])=8 then [money]
else 0
end),
[9月]=sum(case
when datepart(month,[Date])=9 then [money]
else 0
end),
[10月]=sum(case
when datepart(month,[Date])=10 then [money]
else 0
end),
[11月]=sum(case
when datepart(month,[Date])=11 then [money]
else 0
end),
[12月]=sum(case
when datepart(month,[Date])=12 then [money]
else 0
end)
from orders
group by ProductID

----------------一下答案,参考上面容易理解c
select
商品编号=ProductID,
[1月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 1 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 1 then [money]
else 0
end)),
[2月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 2 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 2 then [money]
else 0
end)),
[3月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 3 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 3 then [money]
else 0
end)),
[4月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 4 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 4 then [money]
else 0
end)),
[5月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 5 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 5 then [money]
else 0
end)),
[6月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 6 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 6 then [money]
else 0
end)),
[7月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 7 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 7 then [money]
else 0
end)),
[8月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 8 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 8 then [money]
else 0
end)),
[9月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 9 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 9 then [money]
else 0
end)),
[10月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 10 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 10then [money]
else 0
end)),
[11月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 11 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 11 then [money]
else 0
end)),
[12月销清单]='数量'+convert(varchar,sum(case datepart(month,[Date])
when 12 then Number
else 0
end))+' '+'金额'+convert(varchar,sum(case datepart(month,[Date])
when 12 then [money]
else 0
end))
from orders
group 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(20

本文由豆瓜网专栏作家 豆瓜 投稿发布,并经过豆瓜网编辑审核。

转载此文章须经作者同意,并附上出处(豆瓜网)及本页链接。

若稿件文字、图片、视频等内容侵犯了您的权益,请联系本站进行 投诉处理

相关搜索

mssql
图标 图标

豆瓜

豆瓜网

豆瓜网专栏

全部评论

豆瓜

豆瓜网

豆瓜网专栏

  • springaop编程思想分享
  • android fragment创建方式介绍
  • android spinner控件介绍
  • c# 正则表达式介绍
  • net use命令说明
  • 我来说两句