数据库基础知识

嗯……

Posted by YZune on May 4, 2018

关系数据库

在一个给定的应用领域中,所有实体及实体之间联系的集合构成一个关系数据库。关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。

关系模型结构

  1. 表:关系数据库的表采用二维表格来存储数据,是一种按行与列排列的具有相关信息的逻辑组,一个数据库可以包含任意多个数据表。一个关系模型的逻辑结构是一张二维表,由行和列组成,这个二维表就叫关系。一个关系对应一张表。
  2. 元组/记录:表中的一行即为一个元组,或称为一条记录。
  3. 属性/字段:数据表中的每一列称为一个字段,表是由其包含的各种字段定义的,每个字段描述了它所含有的数据的意义。数据表的设计实际上就是对字段的设计。
  4. 属性值:行和列的交叉位置表示某个属性值。
  5. 关系键:关系键是一个表中的一个或几个属性,用来标识该表的每一行,或与另一个表产生联系。
    \(主键\subset候选键\subset超键\)

    1. 超键:在数据库关系模式设计中能够唯一标识一行数据的属性集。包含所有属性的集,叫做明显超键/平凡超键。
    2. 候选键:最小超键,也就是不含有多余属性的超键。它是某个关系变量的一组属性所组成的集合,需要同时满足下列两个条件:
      • 这个属性集合始终能确保在关系中唯一标识元组。
      • 在这个属性集合中找不出真子集能够满足上个条件。即,这个集合中,去掉任何一个元素,就不能唯一标识元组了。
    3. 主键:多组候选键中的一组,是人为规定的。
    4. 外键:一个数据表的主键放在另一个数据表,当做属性以创建彼此的关系,这个属性就是外键。

关系代数运算

集合运算符

运算符 含义 英文
\(\bigcup\) Union
\(\bigcap\) Intersection
\(-\) Difference
\(\times\) 笛卡尔积 Cartesian Product

关系运算符

运算符 含义 英文
\(\sigma\) 选择 Selection
\(\prod\) 投影 Projection
\(⋈\) 自然连接 Natural Join
\(÷\) Division

自然连接

自然连接是一种特殊的等值连接,它要求两个关系中进行比较的分量必须是相同的属性组,并且会在结果集里,将重复的属性列去掉。

内连接

内连接跟自然连接差不多,不过不会把结果集里面重复的属性列给去掉。

左连接和右连接

总结性的一句话:

左连接只影响右表
右连接只影响左表

也就是说,假设左表没有空值,左连接后,空值只有可能出现在右表里面。

全连接

就是将左连接的结果与右连接的结果作并集。

除法运算

\[R\]
姓名 课程名称
小明 高等数学
小明 离散数学
小明 线性代数
小红 离散数学
小红 软件工程
\[S\]
课程名称 老师
高等数学
离散数学
\[R÷S\]
姓名
小明

除法运算的步骤:

  1. 将两张表中相同的属性找出来,例子中是课程名称
  2. Group By R表中另一个属性列,得到的是按学生分组的名单。
  3. R÷S即看哪一个学生选的课的集合包含了S中的课程。

SQL

Structured Query Language 结构化查询语言

下面的SQL语句都能在MySQL上执行。不同的数据库都有自己一套“方言”,略有区别。

建库建表

create database 库名
create table xsb
(
xh varchar(20),
xm varchar(20),
xb varchar(10),
age int,
bjdm varchar(20),
primary key (xh), # 设置主键
foreign key (bjdm) references bjb(bjdm) # 设置外键
)

插入数据

插入数据有两种方式。一种是不指定具体的字段,所以要做到个数、类型上完全匹配:

insert into zyb
values ('z01', '计算机软件');
insert into zyb
values ('z02', '软件工程');
insert into zyb
values ('z03', '计算机科学');

另一种就是显式地指定表中具体的字段:

insert into zyb (zydm, zymc)
values ('z02', '软件工程');

查询数据

选择某个表中的全部数据:

select * from xsb

如要查询表中指定字段的数据,只需把*换成字段名称,字段之间用逗号分隔即可。

更多实例:

select xh, xm, bjmc from (select * from xsb natural join bjb) as temp;
# MySQL中的自然连接似乎是不需要on的,但是在SqlServer中就需要。

select xh, xm, bjmc from xsb natural join bjb where xm like '李%';
# like是一种模糊查询,%是通配符,表明“李”字后面可以是任意的东西。这里是实现了查找李姓学生的查询。

select xh, xm from xsb where xm like '李%' and age <=19 and age >= 18 order by age limit 0,1;
# 这里对age范围的约束可以简化成between 18 and 19
# order by根据某个字段排序,默认是升序,后面加desc变降序,asc是升序。
# limit 0,1中,0表示输出记录的起始位置,1表示偏移量。这样子就是输出排序后的第一条记录。

使用limit前应该考虑,最值对应的元组并不是唯一的。比如要查询年龄最小的学生,得到的结果往往不止一条,简单粗暴地使用limit 0,1是不行的。此例中可以用这个年龄再去做一次查询,或者使用rank函数求得排名,选择排名为1的学生,MySQL中没有rank函数。

select xm, bjdm, bjmc from xsb natural join bjb as temp
where xm in 
(select xm from xsb 
GROUP BY xm HAVING
count(xm) > 1)
# 这里关注的是having的用法。MySQLHAVING子句在SELECT语句中是用来为某一组行或聚合指定过滤条件。
# MySQLHAVING子句通常与GROUP BY子句一起使用。当它在GROUP BY子句中使用时,我们可以应用它在GROUP BY子句之后来指定过滤的条件。
# count(字段值)用于计数。

使用子查询时,要特别注意子查询返回的是怎样一个结果。大部分情况下返回的是一个数据集,当然也有返回一个标量的时候。当返回一个标量时,自然可以用where将某个值和子查询结果进行比较;但是当返回的是一个结果集时,就不能直接用where去将一个值和多个值进行比较了。这时可以这样做(我就直接写where语句了):

where value = any(集合)   // value是否落在集合中,相当于in

where value <> any(集合)   // value不在集合中,相当于not in

where value > any(集合)   // value大于集合中任意一个值即可,即大于集合中的最小值

where value < any(集合)   // value小于集合中任意一个值即可,即小于集合中的最大值

where value > all(集合)   // value大于集合中的所有值,即大于集合中的最大值

where value < all(集合)   // value小于集合中的所有值,即小于集合中的最小值

SQL中也有条件语句这种东西。比如,统计每个班级的男生和女生的人数,要求显示班级名称和对应人数。

select bjmc,count(bjdm) 班级人数,
sum(case when xb='男' then 1 else 0 end) 男生人数,
sum(case when xb='女' then 1 else 0 end) 女生人数
from xsb natural join bjb
group by bjmc
# when后面是条件,其实一个case后面可以跟多个when,就像if语句那样子。
# 一个when对应一个thenthen后面是满足条件时要执行的操作。以end结束。

想写一下in和exits的区别的,但是好像表达不出来orz。可以通过两句效果相同的语句来体会一下。

select zymc from zyb WHERE zyb.zydm not in (SELECT zydm from bjb);

SELECT zymc FROM zyb WHERE NOT EXISTS (SELECT zydm FROM bjb where zyb.zydm = bjb.zydm);

可以在select后面加上distinct,来对查询结果进行去重。

更新数据

update xsb set age = age + 1
# 很基本的一条update语句,没有用where来限定。

看一道题目,将小于19岁的学生的年龄增加1岁,大于等于19岁的学生的年龄增加2岁。

错误示范:

update xsb set age = age + 1 where age < 19;
update xsb set age = age + 2 where age >= 19

首先,两条语句的执行次序很不妥当。没有考虑到执行第一条语句之后,那些原来小于19岁的年龄有可能等于19岁,然后又满足了第二条的条件。较为妥当的做法应该是将两条语句的顺序调换。

但即使是调换,也并不完美,没有考虑到并发的情况。最好的解决方案不应该是分开两条语句执行的。联想到上面查询语句中用到的case when语句:

update xsb set age = 
(case when age < 19 then age + 1 else age + 2 end)

删除数据

删除表中符合条件的数据,用delete语句,用法与select语句类似。

删除数据库或者整张表,用drop语句,用法与create语句类似。

权限管理

允许root远程登录:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION;
FLUSH PRIVILEGES; 
# all privileges即所有权限,on后面限定的是赋予权限的数据库和表。
# 第一个*的位置表示数据库,第二个*的位置表示数据库中的表。
# 'root'@'%'中的%还是通配符,在这里的含义是允许任意iproot身份登录数据库。
# 当然你可以把%改成你想限定的ip

创建一个用户

CREATE USER 'yz'@'%' IDENTIFIED BY '密码';
FLUSH PRIVILEGES; 

刚刚创建好一个用户,使用这个用户去访问服务器,是不能访问里面的数据库的,因为没有被赋予访问的权限。下面是一些赋权语句:

GRANT ALL ON yz.* TO 'yz'@'%';
# 给用户yz赋予数据库yz的所有权限。

GRANT SELECT(xm, xh, xb) ON yz1.xsb TO 'yz'@'%';
# 给用户yz赋予对数据库yz1中表xsb的某些列的选择权限。

GRANT DROP ON yz1.xsb TO 'yz'@'%';
# 给用户yz赋予删除数据库yz1中表xsb的权限。

GRANT UPDATE(xb) ON yz1.xsb TO 'yz'@'%';
# 给用户yz赋予更新数据库yz1中表xsbxb字段的权限。

SET PASSWORD FOR 'yz'@'%' = PASSWORD('新密码');
# 修改用户yz的密码。

在MySQL命令行中登录远程数据库服务器,并显示其中所有的数据库。

mysql -uroot -h10.211.55.3 -p
# -u后面是用户名,-h后面是服务器地址,这条语句回车之后会提示让你输入登录密码。
# 若登录成功,下面就会进入MySQL的命令行模式。

mysql> show databases
    -> \g
# 命令以\g或者分号结尾。