DB学习之SQLServer（一）
DB学习之SQLServer（一）
零： 关系范式（设计数据库中 表的基本原则）
0、基本思想：
消除关系中的数据冗余
消除数据依赖中的不合适部分
以解决数据插入、更新、删除操作中的异常问题
1、第一范式 1NF(First Normal Form)
表中的每个属性不可再分
（不允许属性以数组、集合等方式存储）
Stu表
name age course
- - - - - - - - - - - -
Tom 23 JavaSE、Oracle 不满足1NF
结论：满足1NF的数据库设计，才是关系型数据库（最基本的范式）
2、第二范式 1NF(Seconde Normal Form)
在1NF的基础上，添加一个主键（主属性）
Stu表
id(PK) name age cid cname
1 Tom 23 101 JavaSE
改为CoreJava的话，所有的101课程都要改
2 Tom 21 102 Oracle
3 James 23 101 JavaSE
结论：可能出现数据的冗余（不够独立）（多个JavaSE）、不一致（一处改，到处都要改，容易造成不一致）
3、第三范式 3NF
在2NF的基础上，解除非主属性之间的依赖关系
Stu表(学生表)
id(PK) name age
1 Tom 23
2 Tom 21
3 James 23
Course表(课程表)
id(PK) name
101 JavaSE 解除了数据冗余，数据独立，仅存一份
102 Oracle
103 Web
sc表(学生课程关系表)(没有独立主键，只有联合主键，sid和cid的组合是唯一的)
sid cid grade
1 101 99
1 102 98
1 103 99
2 101 87
2 102 97
2 103 82
3 101 78
3 102 87
3 103 67
总结：使用第三范式 避免了数据的冗余、不一致问题
常见的数据库的设计都使用3NF
一: DBMS(数据库管理系统)
SqlServer2008 Oracle10g DB2 MySql
0：基本概念
基本思路：通过DBMS来管理DB，从而管理DB中的表（table）以及其它数据资源
DB:DataBase(数据库) 受DBMS的管理
物理结构：
*.mdf:主数据文件（MSSqlServer中）
*.ldf: 日志文件（MSSqlServer中）
逻辑结构：
管理器(DBMS)中可见的部分，可以通过sql来操作
关系型数据库
核心概念：实体关系模型 E-R Model
(数据模型、域模型)
属于数据库设计范畴，表示不同实体（表）之间的关系
借助于E-R图来表示
工具：纸和笔 E-R Win
Sybase PowerDesigner
table:表 二维表 表示关系
表名：实体名（Entity）、关系名、在数据库中区分不同的表
行： row 记录（元组）
列： column 字段（属性、域、Field）
字段名、字段数据类型、宽度、约束
1：如何操作SqlServer?
使用其管理器 - > 需要权限验证登录：默认采用Windows身份登录
- > 登录到DBMS - > 创建数据库 - > 创建表
database table
数据库的组成：
文件类型：
MDF(main data file)主数据文件，属于主文件组（只有1个）
LDF(log data file) 日志文件（可以借助日志文件，进行数据还原），不属于任何文件组
NDF 辅助数据文件（后来添加的文件）
文件组：（管理、分配数据文件）
主文件组：只有一个，Primary
次文件组
初始大小：文件一开始创建时候的大小
自动增长：
路径：实际工作中出于安全考虑，不要将MDF和LDF将在同一路径下，
数据库至少需要以下2个物理文件：
student.mdf 主数据文件 保存业务数据
student_log.ldf 日志文件 数据恢复等管理
数据库的操作：
1)分离数据库：
将某个数据库脱离管理器(DBMS)管理
注意：不能是当前正在使用的数据库
选中数据库-》右键-》任务-》分离
2)附加数据库：
将某个数据库加入到管理器(DBMS)管理
选中数据库-》右键-》附加-》添加-》选择.mdf文件
数据的完整性：数据准确性、一致性
1)实体完整性 (行完整性)行的唯一
2)域完整性 (列完整性)列的要求
3)引用完整性 (参照完整性)比如员工表的部门id要参照部门表id
实现方式：约束 constraint(保证数据库的完整性[准确性，一致性])
完整性：实体完整性 PK UK 行的唯一性
域完整性 NN UK CK default 字段的约束，列的约束
参照完整性 FK
约束也是数据库中的一种对象，一定是针对某张表的 某个字段 进行约束
约束受数据库管理，要求约束名是唯一的，表如果被删除，那约束自动解除
约束的分类： 同的数据库产品统一
PK primary key
FK foreign key references
UK unique
NN not null
CK check
default
1)主键约束 PK primary key
PK = UK + NN 唯一 且 非空
保证实体(每一行)的完整性
一张表是无法同时定义多个PK
联合主键：联合起来，唯一且非空（只能使用表级约束实现）
列级约束：约束直接定义在字段之后
create table emp(
id int constraint emp_id_pk primary key,
name varchar(30) not null,
salary float
);
表级约束：所有的字段之后，追加约束（可以实现联全主键）
create table emp1(
id int,
name varchar(30) not null,
salary float,
constraint emp1_id_pk primary key(id)
);
联合主键应用：
create table stu1(
id1 int,
id2 int,
name varchar(30),
constraint stu1_id1_id2_pk primary key(id1,id2)
);
2)唯一约束 UK unique
唯一
constraint 约束名 unique(字段)
alter table stu add constraint stu_name_uk unique(name);
3)外键约束 FK foreign key
引用完整性/参照完整性（参照的表中的字段，一定是唯一的(PK或者UK),用表级约束）
结论:通过主外键关系 实现了实体间的 1对多的关系(1(父表)———*(子表))
父表 dept部门表
子表 emp员工表
规则 父先于子存在，子先于父消失
先删子表(子表依赖于父表)，再删父表
先加父表(子表依赖于父表)，再加子表
只能用表级约束/创建完表在用alter add constraint添加外键约束，不能用列级约束，切记
constraint 约束名 foreign key(子表/本表 中的字段a) references 父表(字段b) 约束名:当前表_父表名_当前表中被添加FK约束的字段名_fk
———————————————————————————
dept表(父表) 1——-* emp表(子表)
id(PK) name(UK NN) id(PK) name dept_id(FK)
1 销售 101 Tom 1
2 行政 102 James 1
3 研发 103 Mary 2
———————————————————————————
use worker;
drop table emp;
drop table dept;
create table dept(
id int constraint dept_id_pk primary key,
name varchar(30) not null unique
);
create table emp(
id int primary key,
name varchar(30) not null,
salary float,
dept_id int,
constraint emp_dept_deptid_fk foreign key(dept_id) references dept(id)
);
4)非空约束 NN not null
非空
5)检查约束 CK check
6)默认值约束 default
alter table 表名 add constraint 约束名 default 默认值 for 字段;
alter table test1 add constraint test1_age_dk default 20 for age;
数据库中包含的对象：
表 table
索引 index(主要用于提高查询效率)
视图 view
共同点: create 创建 drop 删除
表 table
索引 index(主要用于提高查询效率)
聚集(聚簇)索引(只能有1个)
记录的索引顺序与数据表中数据行的物理顺序相同
create clustered index 索引名 on 表名(字段名);
drop index 表名.索引名;
非聚集(非聚簇)索引(可以有多个，实际工作中常用)
记录的索引顺序与数据表中数据行的物理顺序不相同
建立非聚集索引 没有clustered,可以针对多个字段建立非聚集索引，
常用，因为id默认是聚集索引(是不能创建多个聚集索引的，只能有1个)
create index 索引名 on 表名(字段名);
drop index 表名.索引名;
索引的底层原理：
使用数据结构：B-Tree 平衡二叉查找树
特点：查询效率很高
索引的特点和使用场合：
适合：从海量数据中查询少量结果(大海捞针),很少修改数据的情况，提高查询效率，
比如：性能 瓶颈 sql效率
数据库优化：针对字段建立索引
注意：无法删除PK默认的索引
建立索引会带来开销
1.占据表空间 空间开销
2.降低增、删、改的效率 时间开销(因为需要重新调整索引结构)
视图 view()
用途：简化sql语句，访问权限设定,本质就是保存一条sql语句，
通过视图名来代替一条sql语句，
不会占据表空间、不会提高查询效率
一般将比较复杂的sql创建视图
创建视图：
create view my_view0 as select * from myemp where salary>5000.0;
更改视图：
alter view my_view0 as select * from myemp where salary>6000.0;
通过视图，插入数据：本质基础表中插入数据
insert into my_view0 values(4, 'Tony', 9000.0);
删除视图：
drop view my_view0;
数据库中的函数：数据库提供的函数可以简化sql
1)单行函数： 一条记录 -》 一个结果
LEN(‘ABC’) 返回3，表示3个字符长度
2)多行函数：(组函数、聚集函数) 多条记录 -》 一个结果
SUM() 求总和
AVG() 求平均值
COUNT() 统计记录条数
MAX() 最大值
MIN() 最小值
组函数经常和group by子句配合使用
语法：where之后，
如果需要在分组后进一步过滤，可以使用having子句
如果不写group by，默认将所有 记录作为一组
注意：使用了组函数，就要求select之后的内容
要么也使用组函数
要么使用group by 字段
(总之必须和组相关)
2：SQL（Structured Query Language）结构化查询语言
(1)DDL 数据定义语言 定义（数据库、表）结构
create 创建
create database 数据库名 on primary() log on(); 创建数据库
create table 表名(); 创建表
create table 表名 add constraint ; 创建表中字段对应的约束
create index/view 创建索引/视图
drop 删除
drop database 数据库名；
drop table 表名;
drop index/view 删除索引/视图
alter 改变
alter index/view 改变索引/视图
alter table 表名 add constraint 约束名 primary key(字段); 添加约束(PK)
alter table 表名 add constraint 约束名 default 默认值 for 字段; 添加约束(DK)
alter table 表名 drop constraint 约束名 primary key(字段); 删除约束()
(2)DML 数据操纵语言 操纵表中的数据（一行数据，一行的无组）
DML:select DQL Query查询
增删改查CURD（Create Update Read Delete）
insert
delete
update
select DQL(数据查询语言)
[] 表示里面的内容可省略
* 表示所有列/通配符
- - 单行注释
/**/ 多行注释
insert 插入数据
insert [into] 表名 values(列值1，列值2，所有字段值); 插入所有的列
insert [into] 表名(字段名1，字段名2，) values(列值1，列值2，); 插入指定的列
（不插入的列，默认是空值，null）
delete 删除数据，能删除：0、 1、 n行记录
delete from 表名; 删除所有行数据
delete from 表名 where 记录匹配条件; 删除表中的数据（记录、行）
update 修改表中的数据
update 表名 set 字段1＝新值，字段2=新值，; 修改所有行
update 表名 set 字段1＝新值，字段2=新值， 修改指定行
where 记录的匹配条件;
select 查询数据
语法结构：(可以不写from，仅限MSSQL中，Oracle必须要写)
select *，字段名，表达式，常量，函数调用
from 表1，表2，(可多表查询)
where 查询条件(分组前的过滤条件) 可以组合(and or not)
= > >= < <= != <> between a and b in
is null is not null like % _ [] [^]
group by 字段名 根据该字段来分组
having 分组后的过滤条件
order by 字段名 排序规则
select * from 表名; 查询所有行
select后面跟的内容：
as 给查询的字段取别名（as可省略）
字段名 [as] 别名
+ 查询结果的拼接，将多个列合并成1个列显示
select之后可加常量（查询结果新增加1列，第行都显示’试用‘,新增加显示的列没有字段名）
select name,salary,'试用' from worker;
select 1+2; (仅限MSSQL中，可以这样写)
select getdate() 当前系统时间; (仅限MSSQL中，可以这样写)
select ‘试用’ as 类型; (仅限MSSQL中，可以这样写)
distinct 去除查询结果中的重复值(只能位于，selece和字段之间)
去重(排重)、排序(某些数据库版本中同时还自动排好序)
from 表1，表2...
多表查询
为什么需要多表查询？
为了满足3NF(拆表)，避免了数据的冗余，不一致性，
为了形成良好的设计，进行拆表，数据分布在多张表中(合久必分)，
实际开发中，需要使用一条sql，同时查找多表的数据(分久必合)
- - - 表连接(多表查询)emp(员工表) 8条记录 dept(部门表) 4条记录
select * from emp,dept; 8*4=32条记录——笛卡尔积
笛卡尔积：所有记录都会匹配一次，不合理，只有8条是合理的
去除笛卡尔积，用where过滤
-- 查询员工信息以及所在部门名称
select *
from emp e, dept d
where e.dept_id=d.id;
-- 查询员工id,name,salary以及部门名称，要求薪水大于7000.0
select e.id, e.name, e.salary, d.name
from emp e, dept d
where e.dept_id=d.id and e.salary>7000.0
给表象起别名的好处：
1.简化表名
2.区分不同表 相同字段 e.id d.id
3.提高sql效率
where:选择、过滤出需要 行（记录）
比较运算符：
= > >= < <= !> !<
!= <>(也表示不等于) ==
字段名 between a and b———[a,b]之间的值，则返回真
字段名 in(m,n,k,) 只要出现在括号中，则返回真
字段名 like ‘匹配字符串’ 模糊查询
匹配字符串：
% 0个或多个字符（任意个字符）
_ 任意1个字符
[] 在范围内的1个字符
[^] 不在范围内的1个字符
null 空值null带来的影响
空值和任何值包括空值和空值自己比较，都返回假
is null 判断是否为空
is not null 判断是否不为空
逻辑表达式：
and 并且 逻辑与
or 或者 逻辑或
not 非 逻辑非
group by 字段名 根据该字段来分组（写在where之后）
如果不写group by，默认将所有 记录作为一组
组函数经常和group by子句配合使用
语法：where之后，
如果需要在分组后进一步过滤，可以使用having子句
如果不写group by，默认将所有 记录作为一组
-- 查询出所有员工的总薪水和平均薪水
select sum(salary) 总薪水, avg(salary) 平均薪水 from emp;
-- 查询出每个部门的总薪水和平均薪水,以及每个部门的员工人数
select dept_id, sum(salary) 总薪水, count(id) 该部门员工数, avg(salary) 平均薪水 from emp group by dept_id;
-- 求出每个部门中最高薪水和最低薪水
select dept_id, max(salary) 最高薪水, min(salary) 最低薪水 from emp
group by dept_id;
having 分组后的过滤条件
-- 求出12 部门的平均工资(分组前过滤),巧妙
select max(dept_id), avg(salary) 平均工资
from emp
where dept_id=12;
-- 求出12 部门的平均工资(分组前过滤)
select dept_id, avg(salary) 平均工资
from emp
where dept_id=12
group by dept_id;
-- 求出12 部门的平均工资(分组后过滤)
select dept_id, avg(salary) 平均工资
from emp
group by dept_id having dept_id=12;
order by 字段名 可以根据不同的字段对查询结果排序
位置: select语句的最后
本质: 在返回的查询结果后，改变结果的返回顺序
order by 字段 排序规则,;
排序规则:
[asc] 升序,默认可不写
desc 降序
select * from myemp order by name, salary desc;——先对name按升序，相同则按salary按降序排序
sp_helpdb 查询当前DBMS中所有的数据库
select * from sys.databases; 查询当前DBMS中所有的数据库
＝ show databases;(MySQL中的写法)
select * from sys.tables; 查询当前数据库中所有的表
＝ show tables;(MySQL中的写法)
sp_help 表名； 查询表的结构
select * into 新表名 from 源表名； 复制表（整张表全部复制，内容会复制）
会根据源表的结构创建新表
同时将查询回的结果插入到新表中
select * into 新表名 from 源表名 where 条件； 复制表（表的局部复制，内容会复制）
select * into 新表名 from 源表名 where 1<>1； 复制表（表的结构的复制，内容不复制，无记录）
(3)TCL 事务控制语言 Transcation事务
事务：要么一起成功，要么一起失败的 一组原子操作
银行转账操作就是一个事务，不可分割的操作—原子操作
A账户：-5000
B账户：+5000
事务和日志有关，根据日志恢复数据
begin transaction; 开始事务(sqlserver中这么写)
各种sql操作
commit 提交事务，全部成功
rollback 回滚事务，全部失败
- - 单行注释
/**/ 多行注释
3：SQLServer常用的数据类型
数值类型
int 整数
float 浮点数
字符类型
char(10) 定长，固定分配10个字符空间
‘abc’ 分配10个字符空间，其余使用空白补充
== ‘abc ’
varchar(10) 可变长，最多分配10个字符空间，
‘abc’ 只分配3个字符空间，节约数据库空间
日期类型
datatime
注意：添加时，要加’’，2个单引号
4：SQL脚本
4.1创建数据库
利用SQL语句，创建一个数据库，数据库名字Lib
磁盘中的位置：D:\SQLServer\Lib
mdf文件：
初始化的大小是5W,
每次增长1，
大小没有限制
名字lib_dat
log文件：
初始化的大小是3W，
每次增长10%，
最大的容量是8W
名字lib_log
创建数据库的基本的格式：
create database 数据库名字
on primary（ 数据库中主要存储文件）
log on( 数据库中日志文件)
create database Lib
on primary
(
name = lib_dat,
filename = 'C:\SQLServer\lib\libdat.mdf',
size = 5,
maxsize = unlimited,
filegrowth = 1
)
log on
(
name = lib_log,
filename = 'C:\SQLServer\lib\liblog.ldf',
size = 3,
maxsize = 10,
filegrowth = 10%
)
4.2创建表
3种方式：
1）使用SQL语句来创建表（最常用的）
SQL语句来创建表的基本格式
use 数据库名字
CREATE TABLE 表的名字
（
字段名1（属性名/列名） 数据类型 （各种约束），
字段名2 数据类型 （各种约束），
字段名3 数据类型 （各种约束），
...
）
[GO]
主键约束：primary key（固定的格式）
非空约束：添加数据的时候，被非空约束，所修饰的列，必须有内容 ，
not null除了主键约束以后，其他的约束，是根据需要才添加
2） 利用工具在数据库中来创建表
a）特点：方便，简单，使用极少
（大型数据库，一般要么是用专业建表工具来生成表，要么通过Sql来建表，
只有SQLServer系列数据库等才支持用工具建表）
b）通过工具来往表中添加数据
c）select * from 表名：显示当前表中所有的数据
3） 利用模板来创建表（开发中基本不用）
浙公网安备 33010602011771号