数据库-基础2

-- 创建数据库
use master;
go


if DB_ID('HeiMa8') is not null
drop database HeiMa8;
create database HeiMa8
on(
name='HM’
, filename='e:\db\HM\HM.mdf'
)
log on(
name='HM_log'
, filename='e:\db\HM\HM_log.ldf'
);
go


use HM;
go
-- 创建架构 


create schema Exe1 authorization dbo;
go
-- 创建表
create table Exe1.tblStudent
(
stuId int identity(1,1) not null -- 主键
, stuName nvarchar(10) not null -- 姓名
, stuSex char(1) null -- 性别
, stuAge int null -- 年龄
);


-- 添加约束
alter table Exe1.tblStudent
add 
constraint PK_tblStudent_stuId primary key(stuId)
, constraint CK_tblStudent_stuSex check(stuSex='f' or stuSex='m' or stuSex is NULL)
, constraint CK_tblStudent_stuAge check((stuAge>=0 and stuAge<=150) or stuSex is NULL);
go
/*
1、在之前创建的数据库中添加学生表
2、插入3条数据
3、将年龄统一修改为10岁
4、将3号学生的性别修改为女
5、删除所有数据
*/
-- 添加数据
insert into Exe1.tblStudent(stuName,stuAge,stuSex)
values('杨', 30, 'm');
insert into Exe1.tblStudent(stuName,stuSex)
values('李', 'f');
insert into Exe1.tblStudent(stuName, stuSex, stuAge)
values('刘', 'm', 31);
go


-- 查一下
select stuId, stuName, stuSex, stuAge from Exe1.tblStudent;
-- 将年龄统一修改为10岁
update HeiMa8.Exe1.tblStudent set stuAge=10;
-- 将3号学生的性别修改为女
update Exe1.tblStudent set stuSex='f' where stuId=3;
go
/*
1、为学生表添加邮箱列、班级列和IsDel列
2、添加非空约束
3、为年龄与性别添加检查约束
4、为邮箱添加唯一约束
5、添加一张班级表tblCourse
有班级id、班级名字和班级描述
6、为两张表添加主外键关系
*/
-- 修改表结构
alter table Exe1.tblStudent
add stuEmail varchar(20) null;
alter table Exe1.tblStudent
add classId int null;
alter table Exe1.tblStudent
add stuIsDel bit not null
constraint DF_tblStudent_stuIsDel default(0);
-- 软删除 isDel： is Deleted   默认 false
-- 邮箱的垃圾邮件箱是一个意思
go
-- 添加班级表
create table Exe1.tblClass
(
classId int identity(1,1) not null -- 班级表
constraint PK_tblClass_classId primary key
, className nvarchar(20) not null
, classDesc nvarchar(500) null
);


-- 建立主外键关系
-- 迷惑主键表与外键表的概念
-- 用一个字段指向一张表
-- 
alter table Exe1.tblStudent
add constraint FK_tblStudent_tblClass_classId
foreign key(classId) references Exe1.tblClass(classId);




-- 数据库设计
-- 评估(联机事务处理, OLTP, 数据多变的数据库; 数据仓库, 提供数据进行检索)
-- 收集数据






-- 关系？
-- 关系型数据库，那什么叫关系？就是表
-- 集合A与集合B作笛卡尔积，其子集称为一个关系
-- n元有序组






------------------------------------------------
-- 完整的select语句(重要)
------------------------------------------------
-- 基本格式
-- 执行流程
-- 各个细节


/*
select distinct | top 数字 [percent]
字段 as 别名
, 包含字段表达式
, 函数
, 常量
from
表 或 结果集
where
逻辑条件 | 模糊处理 | 范围处理 | null值处理
group by
分组字段
having
筛选条件
order by
排序依据;
*/
-- 执行流程 ：
-- from子句 -> where子句 -> group by子句 -> having子句 -> select子句 -> order by子句




---------------------------------
-- from子句  
-- 寻找数据源
-- form后面可以跟  表、视图、表值函数、结果集等


-- where子句 
-- 对from得到的临时表做第一次筛选，是直接在结果中将筛选得到的结果在组成一个临时表
select * 
from MyThirdDataBase.MySecondSchema.PersonTbl 
where stuSex='f';
-- 逻辑条件 | 模糊处理 | 范围处理 | null值处理
-- 逻辑
-- 有关>、<、=、>=、<=、<>  (!=, !>, !<)的判断
-- 多个条件用 and or 和 not 处理逻辑关系
select * 
from MyThirdDataBase.MySecondSchema.PersonTbl 
-- where stuAge >= 19;
-- where stuAge >= 19 and stuAge <=23;
where not(stuSex='m')



-- 在SQL Server中最好不要考虑逻辑中断
/*
int n = 10;
n > 10 && n > 13  ? n=11 : n=9;
-- 赋值表达式是有值的
n > 10 && (n=13) > 0
*/


-- 模糊处理
select * from MyThirdDataBase.MySecondSchema.PersonTbl 
where stuName like '赵%';
-- 模糊处理语法使用:  字段 like 模糊匹配字符串
-- % 相当于多个任意的字符
-- _ 相当于一个任意的字符
-- [^] 不出现在[]号中的一个字符(正则的用一样)
-- [a-z] 表示所有的小写字母
-- 否定
-- not(字段 like 匹配字符串)
-- 字段 not like 匹配字符串
select * from MyThirdDataBase.MySecondSchema.PersonTbl 
where stuName like '[^赵刘汤杨]%'


-- 范围处理 
-- 找年龄在19到21岁的人
-- stuAge >= 19 and stuAge <= 21
-- stuAge between 19 and 21 -- 连续范围
-- 找19岁的或21岁或者23岁
-- stuAge=21 or stuAge=19 or stuAge=23
-- stuAge in (19,21,23) -- 离散


-- 可以用在约束的check表达式中
-- check(stuSex in ('m', 'f'))


-- null值处理
-- 判断一个字段是否为null使用
-- 字段 is [not] null
-- null表示的是不知道，凡是与null参与的运算得到的结果都是null和不知道


select * 
from MyThirdDataBase.MySecondSchema.PersonTbl 
-- where stuAge <> null


-- 案例
-- 添加一个分数表
create table Exe1.ScoreTbl
(
scoreId int identity(1,1) not null primary key,
stuId int not null,
scoreNum int check(scoreNum>=0 and scoreNum<=100),
scoreLast int check(scoreLast>=0 and scoreLast<=100)
);


-- 添加分数数据
insert into Exe1.ScoreTbl(stuId, scoreNum, scoreLast)
values(1,60,55),(2,75,40),(3,95,85),(5,86,75),(6,90,95);


select * from Exe1.ScoreTbl
-- 考试及格
-- 期中
select * from Exe1.ScoreTbl where scoreNum>=60
-- 期末
select * from Exe1.ScoreTbl where scoreLast>=60
-- 都及格
select * from Exe1.ScoreTbl where scoreNum>=60 and scoreLast>=60
-- 3-7开
select * from Exe1.ScoreTbl where (scoreNum * .3 + scoreLast * .7) >= 60;


select * from Exe1.ScoreTbl


-- stuName like '%虎%'








-- group by子句 
-- 将结果做分组处理
create table Exe1.StudentTest
(
stuId int identity(1,1) not null
, stuName nvarchar(10) not null
, stuSex char(1) null
, stuAge int null
);
alter table Exe1.StudentTest
add constraint PK_StudentTest_stuId primary key(stuId);


insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('牛亮亮', 'm', 30);
insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('王成伟', 'm', 28);
insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('赵晓虎', 'm', 29);
insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('李艳茹', 'f', 19);
insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('牛亮亮', 'f', 22);
insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('苏坤', 'm', 30);
insert into Exe1.StudentTest(stuName, stuSex, stuAge) values('苏坤', 'f', 27);




select * from Exe1.StudentTest
-- 查看表中所有的名字
-- 分组就是将相同的字段合并看做成一个字段
-- group by stuName  按照姓名分组
-- 在有分组的查询中，select子句的列出的成员必须是在分组中的字段、聚合函数或常量
select stuName/*, stuAge*/ from Exe1.StudentTest group by stuName;


select stuName, stuAge, 123 from Exe1.StudentTest


-- 聚合函数
-- 就是将多条记录变成一条记录的函数
-- count、max、min、avg、sum
select COUNT(*) from Exe1.StudentTest


select stuName, COUNT(*), avg(stuAge) from Exe1.StudentTest group by stuName;
















-- 聚合函数的用法以及开窗函数
create table Exe1.ScoreAggregation
(
sId int identity(1,1) not null
, stuId int null
, scoreNum int null
, scoreLast int null
);
alter table Exe1.ScoreAggregation
add constraint PK_ScoreAggregation_sId primary key (sId);
-- 添加数据
insert into Exe1.ScoreAggregation(stuId, scoreNum, scoreLast) values(1, 85, 80);
insert into Exe1.ScoreAggregation(stuId, scoreLast) values(2, 75);
insert into Exe1.ScoreAggregation(stuId, scoreLast) values(3, 85);
insert into Exe1.ScoreAggregation(stuId, scoreNum) values(4, 80);
insert into Exe1.ScoreAggregation(stuId, scoreNum, scoreLast) values(5, 85, 80);


select * from Exe1.ScoreAggregation;
-- 聚合函数默认忽略null值
-- 聚合函数的基本语法： 函数名(字段)


select AVG(scoreNum) from Exe1.ScoreAggregation;
select COUNT(scoreNum) from Exe1.ScoreAggregation;


select * from Exe1.ScoreAggregation;


-- count比较特殊 sum、avg、max、min都是处理数字类型的字段
-- count可以处理任何类型
select COUNT(sId) from Exe1.ScoreAggregation;
-- count有一个特殊的用法，就是用*表示所有字段，意义为得到数据表中总数据的条数
select COUNT(*) from Exe1.ScoreAggregation;




-- 开窗函数
-- 开窗的含义表示在一行数据中开出很多行数据
select * from Exe1.ScoreAggregation;


-- 开窗函数的语法     聚合函数 over()


-- isnull(字段, 数字)
select avg(isnull(scoreNum,0)*.3 + isNUll(scoreLast,0)*.7) from Exe1.ScoreAggregation;


select 
* 
, avg(isnull(scoreNum,0)*.3 + isNUll(scoreLast,0)*.7) over()
from Exe1.ScoreAggregation;
-------------------------------------------


-- 练习




-- 订单表
create table Exe1.OrdersTable
(
orderId int identity(1,1) not null -- 订单ID
, productId int not null -- 产品ID
, custId int not null -- 用户ID
, unitprice money not null -- 单价
, quantity int not null -- 数量
, discount numeric(4, 3) not null -- 折扣
);
alter table Exe1.OrdersTable
add
constraint PK_OrdersTable_orderId primary key(orderId)


-- 添加数据
insert into Exe1.OrdersTable(productId, custId, unitprice, quantity, discount)
values
(1, 1, 19.5, 11, 0.75)
, (2, 2, 20, 4, 0.85)
, (2, 1, 20, 9, 0.85)
, (3, 2, 10, 1, 1)
, (1, 3, 19.5, 3, 0.75)
, (1, 4, 19.5, 2, 0.75)
, (1, 5, 19.5, 14, 0.75)
;


-- 查询各个商品售出多少件
-- 按照商品分组，就知道了卖出来几种商品
select productId from Exe1.OrdersTable group by productId;
--  聚合，由于商品分组了(看图)
select productId, COUNT(*), SUM(quantity) from Exe1.OrdersTable group by productId;


-- 查询订单大于100元的商品
select * from Exe1.OrdersTable where (unitprice * quantity * discount) > 100;


-- 统计各个用户销售金额
-- 根据用户分组实现统计用户数据
select custId from Exe1.OrdersTable group by custId;
-- 聚合得到结果
select custId, sum(unitprice * quantity * discount ) from Exe1.OrdersTable group by custId;


select * from Exe1.OrdersTable;




-- having子句 
--  在聚合与分组以后，表结构发生了一定变化，需要重新进行数据统计
--  统计购物次数超过2次(包含)的顾客ID


--  要得到顾客的购物次数，分组聚合
--  要筛选出次数超过2次的， count() >= 2
select
custId
, COUNT(*) 
from 
Exe1.OrdersTable
group by
custId
having
COUNT(*) >= 2
;
-- 返回所有订单总价大于120的所有顾客的ID
select * from Exe1.OrdersTable;
-- 每一个顾客的总价
select 
custId
, SUM(unitprice * quantity * discount) 
from
Exe1.OrdersTable 
group by 
custId
having
SUM(unitprice * quantity * discount) > 120;








-- select子句 
-- 在from找数据源，用where进行第一次筛选，在经过分组聚合重构数据结构
-- 再用having第二次筛选 用select显示出要显示的数据出来
select 
custId as "用    户    I    D"
, COUNT(*) as [用 户 订 单 数]
, case when COUNT(*) > 1 then '高级用户' else '第一次访问用户' end as 用户等级
, SUM(unitprice * quantity * discount) '用 户 总 金 额'
, 测试='测试数据'
from
Exe1.OrdersTable 
group by 
custId
having
SUM(unitprice * quantity * discount) > 120;

-- 功能： 列出需要显示的数据


-- select 列出成员的用法
-- 字段，常量，表达式，函数等


-- 别名
-- select列 as 别名 (推荐)
-- select列 别名 (不推荐)
--  别名=select列 (推荐)


-- 代码规范统一


-- 选项 distinct 和 top
-- distinct 表示将结果中的重复数据剔除掉
--  top表示选出结果中前多少条记录
create table Exe1.Customer
(
cId int not null
, cName nvarchar(10) not null
, cAge int null
, cSex char(1) null
);
insert into Exe1.Customer(cId, cName, cAge, cSex) values(1, '牛', 19, 'm');
insert into Exe1.Customer(cId, cName, cAge, cSex) values(2, '牛', 29, 'm');
insert into Exe1.Customer(cId, cName, cAge, cSex) values(3, '赵', 22, 'm');
insert into Exe1.Customer(cId, cName, cSex) values(4, '路', 'f');
insert into Exe1.Customer(cId, cName, cSex) values(5, '赵', 'm');
insert into Exe1.Customer(cId, cName, cAge, cSex) values(6, '王', 24, 'm');


-- distinct
select distinct 
-- cId
cName
, cAge
, cSex 
from 
Exe1.Customer;

update Exe1.Customer set cAge=30 where cName='牛亮亮';


-- top
select top (3*10) percent
*
from
Exe1.Customer; 


-----------------------------------------------
-- 结果集
-- top的序问题
-----------------------------------------------
-- 无序的表结构的数据内容整个的称谓结果集
-- 表结构、无序、列名
select * from Exe1.Customer;
update Exe1.Customer set cAge=31 where cId=2;
select --distinct 
cId
, cName
, cAge
, cSex 
from 
Exe1.Customer;


select top 1 * from Exe1.Customer where cName like '[牛王赵]%';


-- top表示取出前几行


-- order by子句
--  就是将结果按照某个字段或某几个字段进行排序
-- order by 字段 desc|asc, 字段 desc|asc, ...


select * from Exe1.ScoreTbl;


select * from Exe1.ScoreTbl order by scoreLast desc ;
-- 排完序以后结果为Cursor(是一个ANSI-SQL的一个规范类型，翻译为游标)






----------------------------
-- select 与结果集的补充
----------------------------
-- 获得结果集
-- 可以显式数据，可以构造结果集
select 1 as num, 2 as '123', 3 as 哈哈哈




-----------------------------
-- 联合(结果集的集合运算，并操作)
-----------------------------
-- 将两张表简单的合并
-- 列数相同、列类型兼容
-- 如何联合
/*
结果集
union all
结果集;
*/


select 123 as 列1, 345 as 列2, 789 as 列3 --, 99
union all
select 1.2 as n1, 3.4 as n2, 7.8 as n3


-- 联合结果集的列名由第一个结果集决定
-- 联合结果集union是联合的含义，all表示默认不筛选出重复的数据
-- 一般使用都是使用union all，考虑性能过滤时会消耗资源
select 123 as 列1, 345 as 列2, 789 as 列3
union
select 123 as 列1, 345 as 列2, 789 as 列3
union
select 123 as 列1, 345 as 列2, 789 as 列3
union
select 123 as 列1, 345 as 列2, 789 as 列3


-- 数据类型一致
select 'a' as 列1, 345 as 列2, 789 as 列3
union all
select cast(123 as varchar(3)) as 列1, 345 as 列2, 789 as 列3




-- 应用
-- 汇总数据
---------------------
-- 项目1      100
-- 项目2  -100
-- 项目3  35
-- 。。。


-- 汇总  sum(*)


/*
select projName, moneyChange from tbl
union all
select '汇总', sum(moneyChange) from tbl
*/




-- 批量插入数据，构造结果集


-- insert 结果集语法
create table Exe1.StuSetInto
(
stuId int not null
, stuName nvarchar(10)
, stuSex char(1)
, stuAge int
);
select * from Exe1.StuSetInto;
-- 
-- insert into 表名(列1, 列2, ...) 结果集;
insert into Exe1.StuSetInto(stuId, stuAge, stuSex, stuName)
select 1, 19, 'f', '杨'
union all
select 2, 30, 'm', '刘'
;


-- select union连用实现批量插入数据比较老(SQL Server 2005)
-- SQL Server 2008+ 表值构造函数
insert into Exe1.StuSetInto(stuId, stuName, stuAge, stuSex)


values 
(3, '赵', 19, 'm')
,(4, '杨', 30, 'm')
,(5, '苏', 29, 'm')
,(6, '赵', 28, 'm')


-- 表值构造函数一般用来构造临时结果集使用
-- select * from 
-- (表值构造函数) as 别名(列名);


select * from (
values (1, '杨中科', 'Boss')
,(2, '苏', 'MiniBoss')
,(3, '赵', 'teacher')
,(4, '王', '伟')
,(5, '马', 'horse')
) as tbl(id, name, title);




-- select into语法
-- 
/*
select 
字段等
into 数据库名.架构名.表名
from
数据源
其他子句
*/
select
*
into Exe1.CreateFromSelect
from (
values (1, '杨', 'Boss')
,(2, '苏', 'MiniBoss')
,(3, '赵', 'teacher')
,(4, '王', '伟')
,(5, '马', 'horse')
) as tbl(id, name, title);


-- 
select * from Exe1.CreateFromSelect where 1>2;


select * 
into tempdb..ReadTempTable
from Exe1.CreateFromSelect;


--
-- select * into 新表名 from 数据源 where 1 > 2;
-- 通过select into创建的表不会将键和约束索引等数据一并创建过来
-- 除了自动增长