概念

1.术语
DB(Database)
DBMS(Database Management System)
DBS(Database System)

2.关系数据模型
关系：二维表
元组：行
属性：列（属性名唯一）
主码（主键）：唯一确定一个元组的属性组
域：属性的取值范围
分量：元组中的一个属性值

数据模型

每个实体转为一个二维表
多对多关系必须有关系表（中间表）

关系运算

三种传统集合运算

1.并

并集运算

2.差

差集运算

3.交

交集运算

特有运算

1.笛卡尔积RxS

R表
m行n列
S表
p行q列

RxS 的结果有
mp行，n+q列
S表的每一行分别与R表的每一行连接

关系代数

1.选择

σF(R)
F表示选择条件，是一个逻辑表达式
表达式

σ性别="男" and 年龄>20(学生)
σ学号="010125" and 年龄>20(学生)

2.投影

π学号,年龄(R)

3.连接

σ条件(RxS)

σ学.学号=选.学号(学生x选修)

意义：实现跨表查询

案例

1.σ性别="男" and 系="信息"(S)

2.π姓名(σ性别="男"(S))

SQL语句

数据库操作

创建数据库

create database StudScore_DB1

修改数据库

alter database databasename

删除数据库

drop database StudScore_DB2

表操作

创建表

create table StudInfo(
  StudNo varchar(15) primary key,
  StudName varchar(20) not null,
  StudSex char(2) default '男' not null,
  StudBirthDay date null,
  ClassID varchar(10) not null
);

复合主键 / check约束

显式命名：constraint [复合主键名字]
隐式命名: 系统自己给一个名字

Constraint PK_S_C primary Key(StudNo,CourseID)
primary Key(StudNo,CourseID)

create table StudScoreInfo(
  StudNo varchar(15),
  CourseID varchar(10),
  StudScore numeric(4,1) default 0 check(StudScore>0 and StudScore<=100),
  -- check 约束
  Constraint PK_S_C primary Key(StudNo,CourseID)
  -- 建立复合主键
);

标识符identity

概括：设置自增的值
数据类型：decimal int numeric smallint bigint tinyint

create table StudScoreInfo(
  Seq_ID int identity(100001,1),
  -- 初值为100001,步长为1
  StudNo varchar(15) Primary Key,
  StudName Varchar(30) not null
);

修改表

增加自增编号的新列

alter table StudScoreInfo Add Seq_ID int Identity(1001,10)

删除复合主键

alter table StudScoreInfo drop constraint PK_S_C

设置主键

alter table StudScoreInfo add Constraint PK_T_C primary key(StudNo,CourseID)

删除列

alter table StudScoreInfo drop colume Seq_ID

删除表

drop table StudScoreInfo

数据操作

插入数据/新增数据

insert into ClassInfo
  (ClassName,ClassID)
values
  ('20000704','计算机2000'),
  ('20000705','高数2024');

省略写法，但是要一一对应

insert into ClassInfo
values
  ('20000704','计算机2000'),
  ('20000705','高数2024');

更新数据/修改数据

update ClassInfo
set ClassName='计科2000级',ClassDesc=NULL
where ClassID='20000704'

删除数据

delete from ClassInfo
where ClassID='20000704'

truncate table删除所有记录

功能相当于不带where字句的delete命令
不能用于被别的表依赖的表
不会对事务处理日志进行数据删除记录，因此不能激发触发器

truncate table StudScoreInfo

查询数据

全部列

select * from StudInfo

部分列

select StudNo,StudName,ClassID from StudInfo

连接列（+）

select StudNo+StudName,ClassID from StudInfo

计算列

select StudNo,CourseID,StudScore*0.8 from StudScoreInfo

distinct 去重

select distinct StudSex from StudInfo

结果：男，女

top n / top percent

top n：前n条数据
top percent：前percent%的数据(百分比)

select top 10 * from StudInfo
select top 10 percent * from StudInfo

with ties

别名

select StudNo as 学号,姓名=StudName,ClassName 班级编号 from StudInfo

into

将查询结果创建一个新的数据表

select StudNo as 学号 into StudInfoBack from StudInfo

from

[表名].[列名]
表示某表下的列
为join多表连接做准备，防止不同表的相同列名冲突

select s.StudNo,s.StudName from StudInfo as s 

group by 和聚合函数

聚合函数通常与group by 子句一起使用，对给定字段分组之后的结果进行分类汇总

avg() 求平均值

select StudNo,avg(StudScore) AvgScore
from StudScoreInfo
group by StudNo

用cast函数保留小数位数

select StudNo,cast(avg(StudScore) as numeric(4,1)) AvgScore
from StudScoreInfo
group by StudNo

count() 计算数量

select StudNo,count(*) CourseCount
from StudScoreInfo
group by StudNo

having

指定分组搜索条件，是对分组之后的结果再次筛选

查询平均分80分以上的学生记录 having

select StudNo,sum(StudScore) as SumScore,count(*) CourseCount,
  cast(avg(StudScore) as numeric(4,1)) AvgScore
from StudScoreInfo
group by StudNo
having avg(StudScore) >= 80

统计80分以上的平均分，where

select StudNo,sum(StudScore) as SumScore,count(*) CourseCount,
  cast(avg(StudScore) as numeric(4,1)) AvgScore
from StudScoreInfo
where StudScore >= 80
group by StudNo

统计60分以下10门以上的学生平均分，where + having

select StudNo,avg(StudScore) as AvgScore
from StudScoreInfo
where StudScore<60
group by StudNo
having count(*)>=10

order by

排序
升序：ASC(ascending)
降序：DESC(descending)

查询学生成绩，并按成绩高低排序

select * from StudScoreInfo where StudNo='20050319001'
order by StudScore desc

按成绩高低排序，成绩相同的按课程编号升序排序

select * from StudScoreInfo where StudNo='20050319001'
order by StudScore desc,CourseID asc

统计平均分，并排序

select StudNo,avg(StudScore) as 平均分
from StudScoreInfo
group by StudNo
order by 平均分 desc

where子句

比较运算符

>:大于
<:小于
<> / != :不等于
= :等于

select * from StudScoreInfo where StudScore>70

逻辑运算

select * from StudScoreInfo where StudScore>=60 and StudScore<=70

范围运算 between and
和[列名] >= [值] and [列名]<=[值] 功能相同

select * from StudScoreInfo where StudScore between 70 and 80

in

select * from StudInfo where Studno in ('20050319001','99070405')  

模式查询 like

通配符

%
包含零个或更多字符的任意字符串

where StudName='%丽%'

_
任意单个字符

-- 
where StudName like '_丽'

[]
指定该范围内包含的任意单个字符

-- 大写ABC字母开头的
where CourseID like '[A-C]%'

[^]

函数

系统内置函数

字符串函数

left

select * from stsudinfo where left(right(studname,2),1)='丽'

substring

select * from StudInfo where substring(StudName,2,1)='丽'

charindex

-- 输出2
select charindex('B','ABC')

-- 输出0
select charindex('AD','ABCD')

-- 从第四个开始查询，输出4
select charindex('B','ABCBDE',4)

replace

-- 将StudName中，所有‘李’，替换为‘张’
update StudInfo set StudName=replace(StudName,'李','张')