1数据库的定义:
数据库是长期存储在计算机内、有组织、可共享的大量数据的集合。
2.数据库的特点:
长期存储、有组织和可共享是数据库所具有三个基本特点。
3.数据管理技术的3个发展阶段
人工管理阶段
文件系统阶段
数据库系统阶段
4.数据管理技术进入数据库系统阶段的标志是20世纪60年代末期至20世纪70年代初期数据管理技术领域发生的三件大事:
①1968年美国IBM公司推出基于层次数据模型的IMS(Information Management System)数据管理系统;
②1969年美国CODASYL(Conference On Data System
Language)组织发布了DBTG(Data Base Task Group)报告,提出网状模型;
③1970年美国IBM公司研究人员E.F.Codd发表了论文《大型共
享数据库数据的关系模型》,提出了关系模型,为关系数据库技术奠定了理论基础。
5.数据模型是数据库系统的基础,根据所采用的数据模型的不同,
数据库系统可分为第一代数据系统、第二代数据库系统和第三代数据库系统。
√第一代数据库系统:层次数据库系统、网状数据库系统
√第二代数据库系统:关系数据库系统
√第三代数据库系统:面向对象数据库系统、分布式数据库系统、并行数据库系统、多媒体数据库系统、Web数据库系统、工程数据库系统、空间数据库系统等都可以称为第三代数据库系统
5.
数据库的三个核心:
· 数据模型 (Data Model)
· 数据库管理系统 (DBMS)
· 查询语言 (Query Language)
6.
数据库的两个转换:
· 模式转换 (Schema Transformation)
· 数据转换 (Data Transformation)
3NF: 3NF不允许关系模式的属性之间有这样的非平凡函数依赖:X→Y,其中X不包含码,Y是非主属性。X不包含码有两种情况,一种情况X是码的真子集,这是2NF不允许的,另一种情况X不是码的真子集,这是3NF不允许的。
10.数据库设计 的过程 (六个阶段)
1.需求分析阶段 准确了解与分析用户需求(包括数据与处理) 是整个设计过程的基础,是最困难、最耗费时间的一步
2.概念结构设计阶段 是整个数据库设计的关键 通过对用户需求进行综合、归纳与抽象,形成一个独立于具体DBMS的概念模型
3.逻辑结构设计阶段 将概念结构转换为某个DBMS所支持的 数据模型 对其进行优化
4.数据库物理设计阶段 为逻辑数据模型选取一个最适合应用环境的物理结构(包括存储结构和存取方法)
5.数据库实施阶段 运用DBMS提供的数据语言、工具及宿主语言,根据逻辑设计和物理设计的结果 建立数据库,编制与调试 应用程序 ,组织数据入库,并进行试运行
6.数据库运行和维护阶段 数据库应用系统经过试运行后即可投入正式运行。
在数据库设计中,第三范式(3NF)是一个重要的规范,用于减少数据冗余和提高数据的完整性。如果一张表包含超过10个字段,并且我们希望这张表符合3NF,那么需要考虑以下几个问题:
1. 数据冗余
当表包含大量字段时,可能会有重复的数据。这不仅浪费存储空间,还增加了维护数据一致性的复杂性。例如,如果有多个字段保存相同的信息,更新某一条记录时可能需要在多个地方进行修改。
2. 插入异常
如果一张表设计不当,插入数据时可能会遇到问题。假设某些字段依赖于其他字段才能有意义,在插入部分数据时可能会导致不完整或无效的记录。例如,在一个包含顾客信息和订单信息的表中,如果没有订单就无法插入顾客信息,这会导致插入异常。
3. 删除异常
在一张表中删除某些记录时,可能会误删有用的信息。例如,在一个包含顾客和订单信息的表中,删除某个订单可能会导致顾客信息也被删除,如果这些信息都在同一张表中。
4. 更新异常
如果一张表中包含的字段之间存在复杂的依赖关系,更新某些字段时可能需要同时更新其他字段,以保持数据一致性。这增加了操作的复杂性,并且容易出错。例如,更新顾客的地址信息时,如果订单中也保存了地址信息,需要同时更新多个地方。
5. 第三范式(3NF)要求
为了让表符合第三范式,需要确保:
表中的所有非主属性都只依赖于主键,而不是依赖于其他非主属性。
没有传递依赖,即非主属性不依赖于其他非主属性。
例如,如果表中包含以下字段:{顾客ID, 顾客姓名, 顾客地址, 订单ID, 订单日期, 商品ID, 商品名称, 商品价格},为了符合3NF,可能需要将表拆分为多张表:
顾客表(Customer):顾客ID, 顾客姓名, 顾客地址
订单表(Order):订单ID, 顾客ID, 订单日期
商品表(Product):商品ID, 商品名称, 商品价格
订单商品表(OrderProduct):订单ID, 商品ID
这样设计可以减少数据冗余,避免上述提到的插入、删除和更新异常问题,并确保表符合3NF。
总结
在设计数据库时,特别是当表包含超过10个字段时,确保表符合第三范式非常重要。这不仅能减少数据冗余,提高数据一致性,还能避免插入、删除和更新时的各种异常情况。通过将表进行合理的拆分和规范化,可以提高数据库的效率和数据质量。
第一范式:确保每一列的原子性。如果每列都是不可再分的最小单位,即满足第一范式。
--第二范式:是对记录的惟一性,要求记录有惟一标识,即实体的惟一性,即不存在部分依赖
第三范式:每列都与主键相关,处理数据冗余,
--要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即不存在传递依赖
1.添加列
use "22063131"
alter table Table_1
add "奖学金" tinyint null
Go
2.删除列
use "22063131"
alter table Table_2
drop column num
go
3.修改列名
use "22063131"
exec sp_rename 'Table_1.id' , 'id1', 'column'
Go
4.添加约束
use "22063131"
alter table Table_2
add constraint we primary key(id)
5.查询,别名,条件查询,嵌套查询,聚合函数
use "22063131"
select *
from Table_1 a
where a.score1>(select avg(score1) from Table_1 )
6.删除约束
use "22063131"
alter table table_2
drop constraint we
Go
7.模糊查询
use "22063131"
select *
from Table_1
where ip like '[a-z]%'
Go
8.多表查询
use "22063131"
select *
from Table_1 a
inner join Table_2 b on a.id1=b.id
9.降序
use "22063131"
select *
from Table_1 a
order by score1 desc
10.复制表到新表
use "22063131"
select *
into newtable1
from Table_1
11.添加列
use "22063131"
insert Table_1 (sort,id,name)
values(13,22163131,'wuoei')
Go
12.删除列
use "22063131"
delete from
Table_1 where id1=22071215
13.修改数据
use "22063131"
update Table_1
set sort =1
14.删除表
use "22063131"
drop table student1
15.添加检查的视图
create view m123
as
select sort,id,name
from [22063131].dbo.Table_1
where sort=12
with check option
16.删除视图一行
use "22063131"
delete
from m12
where id=22163131
17.定义变量,注释语句,条件查询
use "22063131"
go
declare @we int =22063131
if exists(select * from Table_1 where id=@we)
print '存在'+cast(@we as nchar(20)) --如果存在打印@we
print '存在'+cast(@we as nchar(20))
18.循环
while exists(select score1 from [22063131].dbo.newtable1 where score1<50)
begin
update [22063131].dbo.newtable1
set score1=score1*1.1
end
print '此时表为'
select * from [22063131].dbo.newtable1
21.Case语句
use "22063131"
select
case
when score1<60 then '不合格'
when score1>=60 then '合格'
end 水平
from Table_1
22.游标的用法
use "22063131"
declare tu cursor scroll for
select score2 from table_2
open tu
--fetch absolute 1 from tu
--fetch last from tu
fetch first from tu
23.使用触发器
create trigger cv on Table_2 after insert
as
if exists(select score2 from inserted where score2>60)
print'合格'
else
print'不合格'
go
use "22063131"
insert Table_2 values(22062323,123456,37,77,1)
24.存储结构
create proc rt
as
select * from [22063131].dbo.Table_2
go
exec rt
25.存储和触发器的进阶
create trigger cv on Table_2 after insert
as
if exists(select score2 from inserted where score2>60)
print'合格'
else
print'不合格'
go
create proc ty
as
insert [22063131].dbo.Table_2 values(22062323,123456,37,77,1)
go
exec ty
1.创建一个表
USE "22063131"
Create Table Student
(stu_id char(8) primary key,
stu_name varchar(8) not null,
stu_sex char(2) not null Constraint sex check (stu_sex in('男','女')),
address varchar(100),
tel varchar(11)unique,
college varchar(20),
birthday datetime,
type tinyint default 1
)
2.对表的操作
use "22063131"
create table testa(column_a INT)
GO//创建了一个表和int列
alter table testa
add column_b varchar(20) null//增加了一个列
go
exec sp_help testa//存储表
go
drop table testa//删除表
Go
3.增加列
use "22063131"
alter table Table_1
add "奖学金" tinyint null
Go
4.删除列
use "22063131"
alter table Table_2
drop column num
go
5.改变列属性
use "22063131"
alter table Table_2
alter column tel char(10)
go
6.唯一约束
use "22063131"
alter table Table_2
add constraint we primary key(id)
7.修改表名
use "22063131"
Exec sp_rename student1,student
Go
7.删除主键约束
use "22063131"
ALTER TABLE Table_2
DROP CONSTRAINT [we];
GO
8.创建唯一索引
use "22063131"
CREATE UNIQUE INDEX idx_unique_email ON Table_2 (id);
删除索引
use "22063131"
go
drop index Table_2.idx_unique_email
9.加合两列
A.
use "22063131"
select name+'.'+sex 加合
from Table_1
10.表的查询
use "22063131"
select *from Table_1
go
B.
use "22063131"
select id 身份,sort 排序 from Table_1
go
11.查询前五行
use "22063131"
select top 5 id 身份,sort 排序 from Table_1
Go
12返回5%的数据
use "22063131"
select top 5 percent*
from Table_1
Go
13返回分数的80%还大于50的信息
use "22063131"
select *
from Table_2
where score1-score1*0.2>50
Go
14.查找小红的信息
use "22063131"
select *
from Table_1
where name='小红'
go
15.查找分数在50到100的信息
A.
use "22063131"
select *
from Table_1
where score1 between 50 and 100
Go
B.
use "22063131"
select *
from Table_1
where score1>=50 and score1<=100
Go
16.查找分数在50下80上的信息
use "22063131"
select *
from Table_1
where score1<50 or score1>80
Go
17.查找ip在北区和南区的
use "22063131"
select *
from Table_1
where ip in('北区','南区')
go
18.地址不在北区的人
use "22063131"
select *
from Table_1
where ip <>'北区'
go
19.查找sort 1开头的信息
use "22063131"
select *
from Table_1
where sort like '1%'
Go
20.查首字母是南或者北
use "22063131"
select *
from Table_1
where ip like '[北,南]%'
go
21查找首字母在a-z之间的
use "22063131"
select *
from Table_1
where ip like '[a-z]%'
go
21.成绩升序
use "22063131"
select *
from Table_1
order by score1
go
22.成绩降序
use "22063131"
select *
from Table_1
order by score1 desc
Go
23.成绩升序
use "22063131"
select *
from Table_1
order by score1 desc
go
24.笛卡尔乘积
use "22063131"
select *
from Table_1,Table_2
25.使用表格别名
use "22063131"
select *
from Table_1 qw ,Table_2 er
where qw.id=er.id
26.使用聚合函数查询表一的平均成绩
use "22063131"
select AVG(score1) 表平均成绩
from Table_1
27.总行数
use "22063131"
select count(score1) 总行数
from Table_1
28.统计成绩大于80的总人数
use "22063131"
select count(score1) 总行数
from Table_1
where score1>80
29.使用聚合函数
use "22063131"
select ip 地址,sum(score1)
from Table_1
group by ip
Go
30.使用子查询
use "22063131"
select *
from Table_1 a
where a.score1>(select avg(score1) from Table_1 )
31复制一张表
use "22063131"
SELECT *
INTO newtable3
FROM Table_3
1增加行
use "22063131"
insert Table_1 (sort,id,name)
values(13,22163131,'wuoei')
Go
2.将一个表的数据加到另一个表
use "22063131"
insert Table_1 (sort,id,name)
select sort,id,name from Table_3
where sort=24
3.
3.修改表数据
use "22063131"
update Table_1
set sort=12
where id=22061416
4.设置每一行的sort值为12
A.
use "22063131"
update Table_1
set sort=12
B.
use "22063131"
update Table_1
set sort=id,id=220987
where id=22061217
5.修改表的前5行
use "22063131"
update top(5) newtable1
set score1=99
6.删除一条数据
use "22063131"
delete
from Table_2
where id=22061217
7.创建视图
create view m12
as
select sort,id,name
from [22063131].dbo.Table_1
8.使用视图
select * from [22063131].dbo.m12
9.判断视图是否存在
A.
if exists(select * from [22063131].dbo.m12)
drop view dbo.m12
B.
if exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME='m12')
drop view m12
10.完整的创建视图
if exists(select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME='m12')
drop view m12
go
create view m12
as
select sort,id,name
from [22063131].dbo.Table_1
11.修改视图
alter view m12
as
select *
from [22063131].dbo.Table_1
12.
13.
14.添加检查的视图
create view m123
as
select sort,id,name
from [22063131].dbo.Table_1
where sort=12
with check option
15创建加密视图
create view m1234
with encryption
as
select sort,id
from [22063131].dbo.Table_1
where sort=12
with check option
15.查看加密视图
SELECT * FROM sys.sql_modules WHERE object_id = OBJECT_ID('m1234');
16.流程控制
declare @message varchar(255)
if exists( select score1 from [22063131].dbo.Table_1 where score1>=60)
begin
set @message='这些人成绩及格'
print @message
select score1 from [22063131].dbo.Table_1 where score1>=60
end
else
begin
set @message='这些人成绩不及格'
print @message
select score1 from [22063131].dbo.Table_1 where score1>=60
end
Go
17.将分数变高
while exists(select score1 from [22063131].dbo.Table_1 where score1<50)
begin
update [22063131].dbo.Table_1
set score1=score1*1.1
if not exists(select score1 from [22063131].dbo.Table_1 where score1<50)
break
end
select score1 from [22063131].dbo.Table_1
print'分高了'
18分级
select
case
when score1>90 then '优秀'
when score1>=60 then '及格'
when score1<60 then '不及格'
end 水平
from [22063131].dbo.Table_1
18.创建游标
declare ert cursor for
select score1 from [22063131].dbo.table_1
open ert
fetch next from ert
close ert
19.创建函数
use "22063131"
go
create function sumscore()
returns int
as
begin
declare @er int
select @er=sum(score1) from Table_1
return @er
end
go
select dbo.sumscore()
print dbo.sumscore()
20.创建表函数
use "22063131"
go
create function sumscore1()
returns table
as
return
select * from Table_1
go
select* from sumscore1()
21.
22.
23.事务
A.
use "22063131"
go
create procedure er
as
select * from table_1
order by score1
go
exec er
B.
use "22063131"
go
create procedure ert @stu_id int
as
select * from table_1
where id=@stu_id
order by score1
go
exec ert 22061213
24.触发器
A.
create trigger ertyt on Table_1
instead of insert --after insert
as
declare @type int
set @type=(select score1 from inserted)
if @type>60
print '及格'
else
print '不及格'
B.
create trigger ertyte on Table_2
instead of insert,update
as
declare @type int
set @type=(select score1 from inserted)
if @type>60
print '及格'
else
print '不及格'
C.
create trigger ertyteee on Table_2
after insert,update
as
declare @type int
set @type=(select score2 from inserted)
if @type>60
print '及格'
else
if @type<0
begin
print '数值不对'
rollback tran--回归没执行代码之前
end
else
print '不及格'
25.事务
先给table_1手动添加添加给score1不小于零的索引,然后执行代码
如果所减的数使score1小于0会报错,使@@errer不为零,从而回归没执行代码之前
use "22063131"
begin transaction
declare @Myerror int =0
update Table_1 set score1=score1-100 where id=22061213
set @Myerror=@Myerror+@@ERROR
update Table_2
set score1=score1-100 where id=22061213
set @Myerror=@Myerror+@@ERROR
if @Myerror=0
begin
commit transaction
print '修改成绩成功'
end
else
begin
rollback transaction
print '修改成绩失败'
end