数据库基本概念

5个基本概念:Data,DB,DBMS,DBA,DBS
3个发展阶段:人工、文件、数据库系统阶段
数据库系统4个特点:数据结构化、数据独立性高、数据共享性高冗余小、实施统一管理与控制
数据模型3要素:数据结构,数据操作,数据约束
数据模型3分类:概念模型、逻辑模型、物理模型
4个逻辑模型:层次、网状、关系、面向对象模型
数据库结构:3级模式和2级映像

数据库的四个概念

数据data
描述事物的符号,数据库中存储的基本对象
数据库Database,DB
长期存储在计算机内、有组织的、可共享的大量数据集合

数据库的特点
数据按一定的数据模型组织、描述和储存
可为各种用户共享
冗余度较小
数据独立性较高
易拓展

数据库管理系统 DataBase System,DBMS
位于用户与操作系统之间的一层数据系统软件

DBMS主要功能
数据定义功能:提供数据定义语言(DDL)
数据操纵功能:DML
数据控制功能:DCL

数据库系统 Database System,DBS
在计算机系统中引入数据库的系统后构成
(在不引起混淆的情况下通常把数据库系统简称为数据库)

由数据库、数据库管理系统、应用程序和数据管理员(DBA)组成

关系数据库

可以理解为由二维表组成的数据库

键码

码或键(key)
属性(或属性组)的值能用来唯一标识该关系的元组,则称这些为该关系的码或键
超码或超键(super key)
在码中去除某个属性,它仍然是这个关系的码
候选码或候选键(Candidate key)
在码中不能从中移去任何一个属性,否则它不再是这个关系中的码或键
主码/键(Primary Key)
在若干个候选码中指定唯一一个唯一标识关系的元组

主属性(Primary Key)
包含在任何一个候选码中的属性称为主属性或码属性。反之,为非主属性(Nonprimary Attribute)

全码或全键(All Key)
一个关系模式的所有属性集合是这个关系的主码或主键,这样的称为全码或全键
外码或外键
某个属性(或属性组)不是这个关系的主码或候选码,而是另一个关系的主码

关系

本质就是二维表。数学本质是集合和笛卡尔积

域是一组具有相同数据类型的值的集合
笛卡尔积
给定一组域D1,D2,…,Dn,这些域中可以有相同的域。则这组域的笛卡尔积为:
D1×D2×…×Dn

关系的六条性质:
1.行顺序无所谓
2.列顺序无所谓
3.列是同质的。即每列数据类型相同
4.不同的列可出自同一个域,但列名不能相同
5.任意两个元组的候选码不能取相同的值
6.分量必须取原子值,即列取值是不可分的数据项

关系模式

对关系的描述。

数据库设计

数据库逻辑结构设计:E-R图

实体之间的联系有一对一(1:1)、一对多(1:n)和多对多(m:n)三种联系类型

SQL SERVER初级

数据库的迁移

分离、附加

数据库文件需要数据库分离后才可复制粘贴取出。
缺点:分离后访问不了数据库

备份、还原

.bak文件

数据库脚本的保存

高级-要编写脚本的数据的类型:架构和数据
生成一个脚本文件.sql,保存带走,在新电脑双击执行一下就回来了

建库建表

  • 点击新建查询产生编码环境

建库

一、检查数据库名是否存在

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--这是注释
--删除数据库
if exists(select*from sys.databases where name = 'DBTEST')
drop database DBTEST --此方法仅限于学习阶段,免得误删,快进到删库跑路
--创建数据库
create database DBTEST
on --数据文件(mdf)
(
name = 'DBTEST', --逻辑名称习惯上与库名相同。在数据库中字符串用单引号。
filename = 'D\DATA\DBTEST.mdf',--物理路径和名称
size = 5MB,--文件初始大小
filegrowth = 2MB --文件增长方式可以写大小,也可以写百分比
)
log on -- 日志文件(ldf)
(
name = 'DBTEST_log',--逻辑名称
filename = 'D\DATA\DBTEST.ldf',
size = 5MB,
filegrowth = 2MB
)

建表

左上角可以切换数据库,也可以通过代码
use DBTEST
创建表基本语法

1
2
3
4
5
create table 表名
(
字段名1 数据类型,
字段名2 数据类型
)

eg.建表(部门,职级,员工)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--判断表是否存在,U表示用户定义的表
if exists(select*from sys.objects where name = 'Department' and type = 'U')
drop table Department
--创建部门表
create table Department
( --部门编号,primary key:主键,不能重复,identity:自增,初始值1,步长为1
DepartmentId int primary key identity(1,1),
--部门名称
DepartmentName nvarchar(50) not null,
--部门描述
DepartmentRemark text
)
-- char:定长,char(10),无论存储字节是否到达10,都要占用10个字节
-- varchar:变长,占用真实个字节
-- text:长文本
--char,varchar,text前面加n:存储unicode字符,对中文友好
varchar(100)存储100个字母或50个汉字
nvarchar(100)存储100个字母或100个汉字
--创建职级表
create table [Rank]--Rank是SQL关键字,所以用方括号括起来
(
RankId int primary key identity(1,1), --职级编号
RankName nvarchar(50) not null, --职级名称
RankRemark text --职级描述
)
--创建员工表
create table People
(
PeopleId int primary key identity(1,1),--编号
DepartmentId int referenences Department(DepartmentId),
--部门(引用外键),references:代表外键引用,将添加的值与引用列进行对比,如果不存在,则无法添加
RankId int referenences [Rank](RankId),--职级
PeopleName nvarchar(50) not null,--姓名
PeopleSex nvarchar(1) default('男') check(PeopleSex="男"or PeopleSex="女"),--check:建立约定,后续维护必须遵循
PeopleBirth smalldatetime not null,
--date年月日,datetime年月日时分秒,smalldatetime最近的日期时间,具体范围1900 年 1 月 1 日至 2079 年 6 月 6 日。为了保证表向下兼容,用datetime。
PeopleSalary decimal(12,2) check(PeopleSalary>=2000 and PeopleSalary<=100000),--decimal(长度,小数位数)
Peoplephone varchar(20) unique not null,--unique:唯一约束,不能重复
PeopleAddress varchar(300),
PeopleAddTime smalldatetime default(getdate()) --添加时间
)

表结构和约束的维护

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
(1)添加列
alter table 表名 add 列名 数据类型
--给员工表添加一列邮箱
alter table People add PeopleEmail varchar(200)
(2)删除列
alter table 表名 drop column 列名
--删除员工表的邮箱列
alter table People drop column PeopleEmail
(3)修改列
alter table 表名 alter column 列名 数据类型
--修改地址列的数据类型(varchar(300)变为varchar(200))
alter table People alter column PeopleAddress varchar(200)

维护约束(删除、添加)

删除约束

1
2
3
4
5
alter table 表名 drop constraint 约束名
--删除月薪的约束
alter table People drop constraint DF__People__PeopleSalary__2E7F3B7B
(建议给约束起别名。去sys.object里找)
实际很少用,一般有权限连接到管理工具直接删除

添加约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--添加一个check约束
alter table 表名 add constraint 约束名 check(表达式)

eg.添加工资字段约束,工资必须在1000-1000000之间
alter table people add constraint CK_People_PeopleSal1 check(PeopleSalary>=1000 and PeopleSalary<=1000000)

--添加约束(主键)
alter table 表名 add constraint 约束名 primary key(列名)
--添加约束(唯一)
alter table 表名 add constraint 约束名 unique(列名)
--添加约束(默认值)
alter table 表名 add constraint 约束名 default(默认值) for 列名
--添加约束(外键)
alter table 表名 add constraint 约束名 foreign key(列名) references 关联表名(列名(通常为主键))

数据的插入、更新、删除

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--向部门表插入数据
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','备注')
insert into Department(DepartmentName,DepartmentRemark)
values('硬件部','备注')

--简写(但最好将字段列表列出来)
insert into Department values('企划部','备注')

--一次性插入多行数据
insert into Department(DepartmentName,DepartmentRemark)
select'销售部','备注'union
select'财务部','备注'union
select'人事部','备注'

修改和删除数据

修改

1
2
3
4
5
6
7
8
9
--语法:
update 表名 set 字段1=新值1,字段2=新值2... where 条件

eg.1.工资调整
update People set PeopleSalary= PeopleSalary+1000(改每一条数据,没有where条件)
2.给员工编号为7的人加薪
update People set PeopleSalary=PeopleSalary+666 where PeopleId = 7
3.将部门编号为1的部门人员工资低于15000的调整成15000
update People set PeopleSalary=15000 where DepartmentId=1 and PeopleSalary<15000

删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--语法:
delete from 表名 where 条件

eg.删除市场部(部门编号3)中工资大于1w的人
delete from Peppple where DepartmentId = 3 and PeopleSalary > 10000

--关于删除(drop,truncate,delete)
drop table People --删除表对象
truncate table People --删除数据(清空数据)
--truncate 和 delete 的区别
1.truncate清空所有数据,不能有条件;delete可以带条件,删除符合条件的数据
2.自动编号;假设表中自动编号为1,2,3,4,5
使用truncate清空再添加数据,编号仍为1,2,3,4,5
使用delete删除数据再添加数据,编号变成了6,7,8,9,10(删除的自动编号将永远不存在)

查询

(运用SQL中常用运算符)

基本查询

查询所有列所有行
select * from Department
查询指定列(姓名,性别,生日,月薪)
select PeopleName,PeopleSex,PeopleBirth,PeopleSalary from People
查询指定列(姓名,性别,生日,月薪)(显示中文列名)(不会修改表)
select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪 from People
查询员工所在城市(不重复)
select distinct(PropleAddress) from People
假设准备加工资(上调20%),查询出加工资前和后的员工数据
select PeopleName,PeopleSex,PeopleSalary,PeopleSalary*1.2 加薪后工资 from People

条件查询

查询性别为女的员工信息
select * from People where PeopleSex = '女'
查询性别为女,工资大于等于100000元的员工信息(多条件)
select * from People where PeopleSex = '女'and PeopleSalary>=10000
查询月薪大于等于10000的员工,或者月薪大于等于8000的女员工

1
2
3
4
主要结构:
select * from People where 条件1 or 条件2

select * from People where PeopleSalary >= 10000 or (PeopleSex = '女'and PeopleSalary>=8000)

–排序
查询所有员工信息,根据(order by)工资排序,降序(asc:升序(默认),desc:降序)
select * from Peopke order by PeopleSalary desc
查询所有员工信息,根据名字长度排序(降序)
select * from Peopke order by len(PeopleName) desc
查询出工资最高的5个人的信息
select top 5 * from Peopke order by PeopleSalary desc
查询出工资最高的10%员工的信息
select top 10 precent * from Peopke order by PeopleSalary desc

–null:空值.注意区分空字符串(‘’)
查询出地址没有填写的员工信息
select * from People where PeopleAddress is null
查询出地址已经填写的员工信息
select * from People where PeopleAddress is not null

查询出80后员工信息

1
2
3
select * from People where PeopleBirth between '1980-1-1' and '1989-12-31'

select * from People where year(PeopleBirth) between 1980 and 1989

查询出工资比赵云高的人的信息
select * from People where PeopleSalary > (select PeopleSalary from People where PeopleName='赵云')
查询所有员工信息,添加一列,显示生肖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select *,
case year(PeopleBirth) %12
when 4 then '鼠'
when 5 then '牛'
when 6 then '虎'
when 7 then '兔'
when 8 then '龙'
when 9 then '蛇'
when 10 then '马'
when 11 then '羊'
when 0 then '猴'
when 1 then '鸡'
when 2 then '狗'
when 3 then '猪'
else '未知'
end 生肖
from People

定义一个函数,根据生日计算生肖

1
2
3
4
5
6
7
8
9
--定义函数
create function GetZodiac(@BirthDate smalldatetime)
returns nvarchar(10)
as
begin
declare @Zodiac nvarchar(10)
set @Zodiac = ''

select *,dbo.GetZodiac(PeopleBirth) as Zodiac from People

模糊查询

  • 模糊查询使用like关键字配合通配符实现,通配符具体含义如下:

    %:匹配任何字符串(包括空字符串)
    _:匹配有且只有1个字符
    []:代表匹配范围内
    [^]:代表匹配不在范围内

查询出员工信息,名字中包含’王’的员工信息
select * from People where PeopleName like '%王%'