数据库学习——关系数据库语言
一、SQL语言
1.数据定义语言(Data Definition Language,DDL)用于定义、撤销和修改数据模式
2.查询语言(Query Language,QL)用于查询数据
3.数据操纵语言(Data Manipulation Language,DML)用于增、删、改数据
4.数据控制语言(Data Control Language,DCL)用于数据访问权限的控制
二、SQL数据定义语言
1.SQL数据类型
Number类型:int,smallint,dec(m,n),float
Text类型:char(n),varchar(n),bit(n),bit varying(n)
Date类型:date,time,timestamp
create table student (sno char(7) not null,sname varchar(8) not null unique,sex char(2) not null ,primary key(sno));
create table course(cno char(6) not null,lhour smallint not null,primary key(cno));
create table sc(sno char(7) not null,cno char(6) not null,grade dec(4,1) default null,primary key(sno,cno),foreign key(sno) references student on delete cascade,foreign key(cno) references course on delete restrict);
restrict指被基表所引用的主键不得删除,cascade指如主表中删除了某一主键,则基表中引用此主键的行也随之被删除。
2.修改基表
增加列:alter table <表名> add <列名><类型>;
删基表:drop table<表名>;
补充定义主键:alter table <表名> add primary key(<列名表>);
撤销主键定义:alter table <表名> drop primary key;
补充定义外键:alter table <表名-1> add foreign key [<外键名>](<列名表>) references <表名-2> [on delete {restrict|cascade|set null}];
撤销外键定义:alter table <表名> drop <外键名>
3.索引的建立和撤销
create index s_index on student(sex);
create unique index sc_index on sc(sno desc,cno asc);asc指索引属性按升序排列,desc指索引属性按降序排列
drop index s_index;
三、SQL查询语言
1.分为select子句、from子句、where子句、group by子句和order by子句。
select [distinct] 表达式/[基表名/视图名] 列名/*
from [表创建者]基表名/视图名[别名] //别名只在本句中有效
where 条件
group by 列名 [having 条件]
order by 列名/列序号 [asc、desc]
2.查询条件
(1)条件:[not] 简单条件 [简单条件 and/or]
(2)简单条件:比较条件/betwwen条件/like条件/in条件/exists条件
(3)比较条件:表达式 is [not] null / =/<>/</<=/>/>= 表达式/[all/any/some] (子查询块)
(4)表达式:项[+/-/*//项]
(5)项:[+、-] [基表名./视图名.] 列名/常量/特殊常量/(表达式)/标量函数/聚集函数
(6)特殊常量:user/current date/time/timestamp/timezone
(7)标量函数:日期、时间函数/、数字、字符函数
(8)聚集函数:avg/max/min/sum([distinct]列名)
count(*/[distinct] 列名)
(9)between条件:表达式[not] between 表达式 and 表达式
(10)like条件:列名 [not] like '字符串' //"_"表示任意单字符 “%”表示包括长为零的任意长字符串
(11)in条件:表达式[not] in (子查询块/常量)
(12)exists条件:[not]exists (子查询块)
3.group by和order by 子句
(1)group by子句将表按列的值分组,列的值相同的分在一组。如果group by后有多个列名,则先按第一列名分组,再按第二列名在组中分组,原则上可以一直分下去,直到在所有基本组中,group by子句所指定的列都具有相同的值,having以后的条件是选择基本组的条件。group by子句常与聚集函数联用,此时聚集函数以基本组为计算对象。加了group by子句后,select子句所取的值必须在基本组中是唯一的,即只能是group by子句所指明的列或聚集函数。若无group by子句,则聚集函数以整个表为计算对象,此时select子句只能取聚集函数,而不能取某一列。
(2)order by子句可对查询结果按子句中指定的列的值排序。列可以用列名表示,也可用在select子句中出现的序号表示。后者书写起来比较简便,特别当选择的列是聚集函数或表达式时,由于没有列名,只有用序号表示。asc表示升序,desc表示降序,默认时表示升序。如果order by后面有多个列名,则首先按第一列名排序,然后对于具有相同第一列值的各行,再按第二列名排序,原则上可以如此继续下去。
4.包含union的查询
在做union运算时,必须消除结果中的重复项。要发现重复项必须做一次排序。如果结果很大,排序的开销很大,此时使用union运算,要慎重考虑。如果允许,可在union后加all,则不消除结果中的重复项。
四、SQL操纵语言
1.insert语句
insert into 表名[(列名)] values (数据项)/查询语句
在表名后附上列名的好处:一是当某列为null时,只要在列名表中不列出这个列名即可,当然,此列在定义中要允许为null;二是列的次序也不必严格地与表的定义语句一致,只要保证与values中的值一一对应即可。
2.delete语句
delete [表创建者] 表名 [where 条件]
3.update语句
update [表创建者] 表名 set 列名=表达式 [where 条件]
五、SQL中的视图
1.普通视图
视图是由基表或其他视图导出的虚表,只在数据目录中保留其逻辑定义,而不作为一个表存储在数据库中。
当视图参与数据库操作时,在简单情况下,可以通过修改查询条件,把对视图的查询转换成对基表的查询。在不能这样处理时,系统须按视图定义生成一个临时表,在查询处理完毕后,此临时表不再保留。
create view 视图名 [列名] as 查询语句
drop view 视图名
2.视图既是表,但又不同于基表。对于查询,视图在原则上可以像基表一样参与数据库操作。
对视图进行DML操作,直接影响基表中的数据,如delete xxview,则基表数据也被清空;
对基表进行DML操作,数据库自动维护更新视图;
drop xxview,对基表没有影响;
drop 基表,对应视图中数据清空,但是视图在数据字典中的定义仍然存在。
视图特点:简化查询操作,一个视图可能代表一串非常复杂的查询操作;限制数据访问;相同的数据不同的展现;提供特定的数据
注意事项:操作视图会比直接操作基表慢,应当尽量避免在大型表上创建视图;
尽量不要创建嵌套视图,就是在视图中使用视图,因为这样在查询时会多次重复访问基础表,带来性能损耗
尽量在视图只返回所需的信息,尽量不要在视图使用不需要访问的表
在大型表或者复杂定义的视图,可以使用存储过程代替
频繁使用的视图,可以使用索引视图来代替
六、SQL存储过程
存储过程是将常用的访问数据库的程序作为一个过程,经编译后,存储在数据库中,且在数据目录中登录,供用户调用。
好处:方便用户:用户只需要提供必要的参数,用过程调用语句即可访问数据库,不必编写程序。
改善性能:存储过程以编译后的形式存于数据库中,在调用时,不必再进行语法分析以及查询处理和优化。客户方与数据库服务器之间只需要传递参数和结果以及必要的消息,减少了通信量。
扩充功能:存储过程不仅可用SQL语句,还可以使用一些控制程序的语句,如CALL/RETURN等,且可说明局部变量,用set语句赋值,调用以宿主语言编写的函数和过程。
例子:EXEC SQL
CREATE PROCEDURE drop_student
(IN student_no CHAR(7),
OUT message CHAR(30))
BEGIN ATOMIC
DELETE FROM STUDENT
WHERE SNO=student_no;
DELETE FROM SC
WHERE SNO=student_no;
SET message=student_no||'droped';
END;
EXEC SQL
调用时,CALL drop_Student(...);