空间数据库上课笔记

常见数据结构：层次模型 网状模型 关系模型

1. 层次模型：树形结构
2. 网状模型：网状结构
3. 关系模型：表

数据库系统结构：

数据库系统组成：

数据模型：三要素

概念模型 E-R模型 三种主要数据库模型（关系 层次 网状）

Chapter2 关系数据库模型标准语言SQL

2.1 SQL概述

集数据查询语言（DQL），

数据定义语言（DDL ）

数据操作语言（DML)

数据控制语言(DCL)于一体 ，采用集合操作方式，是独立的语言 ，嵌入式的语言（可嵌入到C++/C/Java），操作对象可以是元组的集合。

2.2 学生-课程 数据库

学生表 Student(Sno,Sname,Ssex,Sage,Sdept)

课程表 Course(Cno,Cname,Cpno,Ccredit)

学生选课表 SC(Sno,Cno,Grade)

2.3数据定义,数据库的相关操作

2.3.1 创建数据库：CREATE DATABASE "DATABASENAME"

SQL中域的概念用数据类型来实现，定义表的类型时，需要指明其数据类型及长度，选用哪种数据类型【取值范围(约束），要做哪些运算】

常见数据类型：

CHAR(n,定长) VARCHAR( n,可变化的长度) INT, SMALLINT, NUMERIC(p,d)定点数 REAL(精度浮点数) FLOAT DATE TIME

2.3.2 创建表 CREATE TABLE "TABLENAME(

…..

表级约束（columnname1,columnname2…)

）；

常用约束：primary key(主键约束，相当于not null+unique) unique（唯一） not null（非空） check（检查限制约束） foreign key（外键约束）

Primary key:唯一的主键行，可以唯一确定某一行；not null+ unique

Foreign key:连接多个表的属性，约束检查表中是否有该内容

表级约束：CREATE TABLE STUDENT(SNO CHAR ,SSEX VARCHAR,UNIQUE,PRIMARY KEY(SNO))，表级约束后面必须跟上属性名。

对约束进行自定义命名时，可以写为 ：sage int /constraint u_1 primary key (sage)// u_1即为自定义的约束名，约束类型为主键，作用字段为sage

添加/删除/约束约束：alter table tablename add/drop/alter constraint constraintname unique/primary key/not null (column); //

特别注意！！！：not null不能设置为表级约束！

外键约束：foreign key (sno) references student(sno);// sno是外码，被参照表是student. 要求必须是同种约束，且是主键约束。

Eg:

创建学生选课表，从psql界面创建即有

例：--学生借阅图书表

create table borrow(

sno number(8),

bno number(5),

primary key(bno,sno), --联合主键

foreign key (bno) references book(bno), --借阅表中的外键bno是book表中的主键

foreign key (sno) references stu(sno)

);

添加属性字段： alter table student add s_entrance date;

增加、删除字段 ：ALTER TABLE STUDENT ADD/DROP SAGE INT CHECK(SAGE>18);

删除基本表：

DROP TABLE STUDENT CASCADE(直接删除）

2.3 数据库索引的建立与删除

例 CREATE （UNIQUE） CLUSTER INDEX Stusname ON student(sname);

聚簇索引作用：保证插入的数据是按顺序插入（存储）的。 CLUSTER INDEX

唯一索引：唯一约束是靠唯一索引实现的。UNIQUE INDEX

删除索引：DROP INDEX<INDEXNAME>

2.4数据查询

2.4.1单表查询

Select *FROM STUDENT(找出所有的信息）

SELECT +算术表达式、字符串常量，函数，列别名+from student

例如查找哪一年出生：select sno,sname,2021-sage as/<space> birthday(as的作用就是起别名） from student.

Select sno,sname,"birthday" as year(常量字符串）,

Select sno,sname 姓名，"出生年“as year ,2021-sage as birthday from student

函数：select sno,sname,lower(sdept),length(sdept) from student.

Select sno,sname,”出生年“ as year, lower(sdept),length(sname)。

2.4.2消除取值重复的行

指定DISTINCT

SELECT DISTINCT SDEPT， FROM STUDENT;(只显示sdept)；

选择列的唯一值，可作用于

选择若干元组，where用于限制条件和查询条件

• 常用查询条件：

Select       sname,sno from sc where sno=XXXXXX/SAGE BETWEEN 18 AND 20;
Select       sname,sno from sc where grade='null';
Select       *from student where sdept (not)in ('CS','MA');
Select * from       student where sage =18 and/or sage =19(and的话找不出来,不可能年龄有两个） and sdept ='MA';
Select * from       student where ssex='男' and       sdept='CS';
Select *       from student where sdept in('MA','CS');
Select *       from student where sage between 18 and 20;
Select *       from student where length(sname)=4;
 

2.4.3匹配串为含通配符的字符串；

例：

Select sname,sno,ssex from student where sname like'刘%';(%是匹配多个字符的符号，%在后面严格匹配顺序）或者like '%勇';或者like '%勇%';
Select sname from student where sname like'欧阳_';或者like '_勇';
例如查找第二个字符为”勇“的信息；
Select sname from student where sname like'_勇%';

2.4.4

2.4.4.1查询最高，最低的数据

select * from student limit 2; //取前两行数据；

按顺序 order by

select * from student order by Grade desc limit  1; //查询限制一个最高分的信息

复合查询条件：

select * from student order by sdept, sage  desc; //多个查询条件
select 学号,成绩 from sc order by Grade desc limit 3;

2.5 聚集函数

2.5.1 计数

select count (sno) from student; //返回sno的个数

2.5.2最值函数

select max/min(Grade) from sc; //返回Grade的最值

2.5.3平均函数

select avg(Grade) from sc; //返回Grade的平均值

2.5.4 求和函数

select sum(Grade) from sc; //返回Grade的总和

2.5.5.查询选修课程总人数

select count(distinct sno) from sc; //返回sno不重复的个数
select sno,max(Grade) from sc where sno=XXXXXXX; //返回sno=xxxxxxx的最高分
select count(sno),max(Grade),min(Grade),avg(Grade)  from sc where cno='3';

2.6 GROUP BY语句 分组

select sno,max(Grade),min(Grade),avg(Grade) from  sc group by sno; //GROUP BY是按照sno分组，然后max,min,avg分别作用于分组后的数据；

having 可以用于限制进组条件

select sno,max(Grade),min(Grade),avg(Grade) from  sc group by sno having count(*)>2，avg(Grade)>89;//个数大于2的分组才会显示出来。
select max(sage),avg(sage),count(sno) from student group by sdept;

求每门课的课程号，平均分，最高分，要求分数大于80才计算。

select sno,avg(Grade),max(Grade) from sc where  grade>80 group by sno.

对满足条件的数据进行分组，group by grade having avg(Grade)>89;

2.4.2 连接查询

联合查询：

select student.sno, sage,sname,ssex,cno,grade from  student,sc where student.sno=sc.sno; //选了课的同学查询其学号，姓名,采用循环嵌套法

自身连接：

例：查询每一门课的先修课

select  cpno First.cno,Second.cno from course First,course Second where  First.cno=Second.cpno.

外连接：

左外连接

select student.sno,sname,ssex,sage,sddept,cno,grade FROM  LEFT OUT JOIN SC ON(student.sno=sc.sno); //左表全部显示出来

复合条件查询

select sname,cno,grade from student1,sc where student1.sno=sc.sno  and cno=2 and grade>88;
select sname,student1.sno,grade from student1,sc where  student1.sno=sc.sno and sdept='CS' and cno is not null;
select sname,student1.sno from student1,sc where  student1.sno=sc.sno and sage>18 and grade>88;

多表查询：

select course.cno,cname,grade,student1.sno from  sc,course,student1 where sc.cno=course.cno and student1.sno=sc.sno; //选了课的同学的学号，姓名，相应的课程名称，序号，分数显示出来

凡是选了课的都可以认为是多表连接查询；

嵌套查询

select sname from student where sno in (select sno  from sc where cno='2');
select sname from student,sc where student.sno=sc.sno  and cno ='2';
select sname from student1 where sno in(select  sno from sc where grade>88 and cno='2'); //先根据学号找出来，sno为子条件,在sc表里查找满足条件的sno作为返回条件从student表查询sname
select  sname from student where sno in //第三步，把学号对应的姓名找出来，从student表找
select  sno from sc  where cno= //第二步，把课程号对应的学号找出来，从sc表查询
(select  cno from course  where cname='数据库')} //第一步，把数据库的课程号找出来，从course表查询

集合查询UNION:

select sno from sc where cno='2' union select sno from sc where  cno='1';

INTERSECT:交集查询

select * from student1 where sdept ='CS' intersect select *from  student where sage<19;

子表查询：

select sname from student where sno in(
select sno from sc group by sno having count(sno)>=2);

思路：先分组学号查询出对应选了两门课的学号，然后返回学号给student表，然后根据sno找出sname；

不相关子查询：

子查询不依赖于父查

返回多值查询 in表示

select * from student where sdept  in(XXXXXXXXXXX)

except 排除查询

SQL数据更新

插入元组：

insert into student(sno,sname,ssex,sdept,sage)  values('200215128','Mike','Male','IS',18);

插入子查询：

insert into student(sno,sname,sage) values('200215121','Tom',19);

修改数据：

update student set sage=29;
update student set sage=sage+5 where sdept='CS';