关系数据库系列文章之数据的操作(二)

本文为作者原创内容,未经许可,禁止转载。如您发现侵权行为,请联系我们

    上个文章探讨了关系数据库的定义,而我们只知道,数据库的组成有三大要素:数据结构、数据操纵、完整性约束。
    这篇文章将结合SQL了解下数据的操纵。

数据操纵:
    在关系模型中常用的关系操作包括,查询操作和插入、删除、修改操作两大部分。

    对数据库实现操作的是结构化查询语言(Structured Query Language,SQL),是关系数据库的标准语言,也是一个通用的、功能极强的关系数据库语言。下面进行详细介绍:

数据定义:

     SQL进行数据定义的操作有:


注:SQL标准不提供修改模式定义和修改视图定义的操作,用户如果想修改这些对象,只能先将它们删除之后再定义。而且SQL标准也没有提供索引的相关语句,但为了提高查询效率,商用关系数据库管理系统通常都提供了索引机制和相关语句。

补充内容:
在数据定义中有创建模式(create schema和drop schema),那什么是模式呢?

    书上这么解释:一个关系数据库管理系统的实例中可以建立多个数据库,一个数据库中可以建立多个模式,一个模式下通常包括多个表、视图和索引等数据库对象。

    通常来讲,模式是用来划分数据库表的应用的领域,举个具体的例子,在一个学校里有很多的表,比如,存储学生、课程、成绩等信息的关系表,还有存储老师、老师工资、老师等信息的许多关系表,还有很多用来存储学校的固定资产、财务信息的相关关系表,如果将这么多表混在一起进行管理的话,将十分麻烦。我们可以通过CREATE SCHEMA来划分区域,也就是跟这么多表划分其所属的命名空间。
比如,创建一个用来划分关于学生的数据表的模式:CREATE SCHEMA student,那么我们就可以通过,student.具体表名来对学生下的某张表进行管理操作。 如: select * from student.name where id = 5。
也就是说一个数据库下可以有很多模式,而一个模式里可以有很多张表,在同一个模式下的表,是不可以重名的。接下来说一个具体的应用场景:比如我们公司的业务在全国很多地方都有站点,但是这些站点的经营都有自己的特征,自己的经营人员,自己的供应商与特定区域的用户,而这些数据具有相当大的区域独立性,其数据的整个生命周期与流程都不参与到其他的区域中,预示着我们数据库不能将所有的数据进行混合管理,比如上海和北京的数据,我可以将定义两个模式,一个上海,一个北京,可以在相应的模式下定义属于该区域的数据表信息。


    数据库中有很多方面的SQL,比如关于操作索引、视图、数据库、基本表的SQL(建立,删除,修改),我们在这里就不一一进行赘述,具体可见后面关于具体的关系数据库的操作(如MySQL中的这些操作),本篇只具体讨论SQL中的数据查询。

    我们知道,数据查询是数据库的核心操作,SQL则提供了select语句进行数据查询,该语句具有灵活的使用方式和丰富的功能,一般格式如下:

select [all | distinct] <目标列表达式>[,<目标列表达式>]......
from <表明或试图名>[,<表明或试图名>......]
[where <条件表达式>]
[group by<列名1>[having<条件表达式>]]
[order by <列名2>[asc | desc]];

    下面将具体拆分的讲下上面格式中的应用。具体应用中如:

select:
select name,age from student;(获取学生表中姓名和年龄的所有数据);
select <目标表达式> 后不仅可以是表中的属性名,还可以是一个算术表达式,如select name,2018-age from student ;(获取姓名以及出生年份信息)

也不仅仅可以是算术表达式,也可以是字符串常量、函数等。如,select 'ceshi',2018-age,LOWER(name);

我们在查询之后,投影到某个列上可能会出现重复的值,可以使用distinct进行重复数据的消除,如:select distinct  age from student;如果没有指定distinct关键词,则默认为all,即保留结果表中取值重复的行。


where:

如果我们要查询指定条件的元组可以通过where字句实现。常用的查询条件如下:



例1,select name age from student where age=20;
关系数据库管理系统执行该查询的一种可能过程是:对student 表进行全表扫描,取出一个元组,检查该元组在age列的值是否等于20,如果相等,则取出name,age列形成一个新的元组进行输出,否则跳过该元组,取下一个元组。重复该过程,直到处理完student表的所有元组。
补充:如果全校有数万名学生,年龄等于20岁的有不到10%左右,我们可以在student表中的age列建立索引(具体的索引原理会在后面的文章中介绍),系统会利用该索引找出age=20的元组,从中去除name,age列形成结果关系。这就避免了对student表的全表扫描,加快了查询速度。但是如果学生较少,索引查找不一定能提高查询效率,系统仍会使用全表扫描。这将由查询优化器按照某些规则或估计执行代价来作出选择。

例2,select age,name from student where age between 20 and 24;查询年龄在20-24岁之间的学生的姓名、年龄。between后是范围的下限,and后是范围的上限。

例3,select age,name from student where age in(20,22);查询年龄为22和20的所有学生姓名和年龄。

例4,select age ,name form student where name is not null;查询姓名不为空的同学。

关于like完整的格式为:
[not] like '<匹配串>' [escape '<换码字符>']

例1,select age,name from student where name like '夏%';查询所有姓夏的同学。其中%代表任意长度的字符串。
例2,select age,name from student where name like '欧阳_';查询姓欧阳且全名只有三个字的同学。其中_代表单个字符。
如果我们想匹配_或者%呢,这时候就要用上换码字符了。

例3,select age,name from student where name like 'chandler\_bin%' escape '\';
查询名称前面带chandler_bin的同学。其中escape '\' 表示‘\’为换码字符。这样匹配串中紧跟在‘\’后面的字符'_',不再具有通配符的含义,转义为普通的‘_’字符。


order by:

    用户可以用order by 子句对查询结果按照一个或多个属性列的升序(asc)或降序(desc)排序,默认为升序。


例1,select age,name from student  where age = 20 order by id desc;按照主键进行降序排列。要注意的是,对于空值,排序时显示的次序,由具体系统实现来决定
例2,select age,name from student order by age desc,id asc;查询结果中,按照年龄进行降序,而所有相同的年龄的数据的排序,按照ID进行升序排列。


group by:

    group by子句将查询结果按某一列或多列的值分组,值相等的为一组。对查询结果分组的目的是为了细化聚集函数的作用对象,如果未对查询结果分组,聚集函数将作用于整个查询结果。分组后聚集函数将作用于每一个组,即每一组都有一个函数值。

什么是聚集函数?

    聚集函数是为了方便使用增加的检索功能,SQL提供了很多的聚集函数,主要有:


count(*) 统计元组的个数
count([distinct|all]<列名>) 统计一列中值的个数
sum([distinct|all]<列名>) 计算一列值的总和(此列必须为数值型)
avg([distinct|all]<列名>) 计算一列值的平均值(此列必须为数值型)
max([distinct|all]<列名>) 求一列值中的最大值
min([distinct|all]<列名>) 求一列值中的最小值
    如果指定distinct短语,则表示在计算时要取消指定列中的重复值。如果不指定或指定all(默认值),则表示不取消重复值。


例1,select count(*) from student ;查询学生的总人数。
注:当聚集函数遇到空值时,除了count(*) 外,其他的都跳过空值只处理非空值。因为count(*)统计的是元组的数量,不会因为一个或部分空值受到影响。

再讲回来group by 
举例如下:
select age,count(*) from student group by age;查询每一个年龄的学生数量。这其中的count(*)就是聚集函数。

补充:在MySQL5.7.5之后使用如下SQL语句
select id,age count(*) from student group by age;

将会报错,如: 
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因是MySQL数据库这个版本之后sql模式的默认配置是only_full_group_by,这个严格的规则要求:在group by 的时候,聚合函数的列之外的所有列都必须包含在group by的字段中。 所以上面的SQL语句会报错。

如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用having短语指定筛选条件。

如,select age,count(*) from student group by age having count(*) > 200;查询某一个年龄学生的数量大于200之后的结果。

注:where子句与having短语的区别在于作用对象不同。where字句作用于基本表或者视图,从中选择满足条件的元组。having短语作用于组,从中选择满足条件的组。也就是说上面的例子中,select age,count(*) from student where count(*)>200 group by age;就是错误的写法。

相比与SQL的查询操作,更新操作则显得相对简单。

插入数据:
    SQL的数据插入语句INSERT通常有两种形式,一种是插入一个元组,另一种是插入一个子查询的结果,后者可以一次插入多个元组。(元组就是数据表中的“一行”数据)

1、插入元组的格式为:
insert into <表名>[属性列1,属性列2...]
values(常量1、常量2...)

如:insert into student(name,age) values('张三',20);

    不过也可以只指出表名,不指出属性名。但是给出的值要对应表中的属性,一一对应。
如:Insert into student values('张三');此时第二列的年龄被自动置空,null。
为了防止漏掉或者为了占位,也可以手动置空Insert into student values('张三',null);

2、插入子查询的结果:
子查询不仅可以嵌套在select语句中用以构造父查询的条件,也可以嵌套在insert语句中用以生成要插入的批量数据。

如:现在表中的每一个系的学生求其平均年龄,并把结果存入数据库中。
insert into test_table(sdept,age) select sdept,avg(age) from student group by sdept.

修改数据:
同插入数据,修改数据也有两种分别如下:
修改一个元组的值:update student set age=22 where sno='JV144092'。
带子查询的修改:update student set age=22 where sno in (select from student where dept='CS');

如果是针对不同的值进行不同的修改怎么办呢,推荐如下的语句:
update student class = (case id when 2 then 1  when 459 then 1 when 460 then 1 end), age = (case id when 2 then 3   when 460 then 3 end), grade = (case id when 2 then 5 when 459 then 4 when 460 then 4 end) where id in(2,4,5,6,10,460) and is_del = 0

删除数据:
删除一个元组数据:delete from student where sno='JV144092'
删除多个元组:delete from student;删除学生表中的所有的数据(劝君谨慎操作)

带有子查询的删除:select form student where son in (select son from student where ....);


这里说一句,对关系表进行增删改的操作,将可能会破坏完整性,什么是完整性,我们接下来的文章将进行探讨。

(本文中的SQL仅仅是针对数据操作的SQL,针对表结构以及其他内容的操作本文尚未涉及,因为我们探讨的主题是数据的操纵)