SQL基础

SQL语言的功能

数据查询

通过select语句可以得到所需的信息.

数据操作(DML)

Data Manipulation Language 主要包括插入数据,修改数据以及删除数据

数据定义(DDL)

Data Definition Language 对数据库用户,基本表,视图,索引进行定义与撤销.

数据控制(DCL)

Data Control Language对数据库进行统一的控制管理,保证数据在多用户共享的情况下能够安全.

基本SQL语句的使用方式

	关键字	描述	语法格式
数据查询	select	选择符合条件的记录	select * from table where 条件语句
数据操作	insert	插入一条记录	insert into table (字段1,字段2,...) values (值1,值2,...)
	update	更新语句	update table set 字段名=字段值 where 条件表达式
	delete	删除记录	Delete from table where 条件表达式
数据定义	create	数据表的建立	create table tablename (字段1,字段2,...)
	drop	数据表的删除	drop table tablename
数据控制	grant	为用户授予系统权限	grant<系统权限> | <角色> to <用户名> | <角色> public [,<用户名> | <角色>] [with admin option]
	revoke	收回系统权限	revoke <系统权限> | <角色> from <用户名> | <角色> public [,<用户名> | <角色>]

例题练习

题目

教务管理系统中有三张表:

• 学生信息表S(SNO,SNAME,AGE,SEX),其属性分别表示学号,学生姓名,年龄和性别.
• 选课信息表SC(SNO,CNO,SCGRADE),其属性分别表示学号,课程号和成绩.
• 课程信息表C(CNO,CNAME,CTEACHER),其属性分别表示课程号,课程名称和任课老师姓名.

练习

1. 把SC表中每门课程的平均成绩插入到另外一个已经存在的表SC_C(CNO,CNAME,AVG_GRADE)中,其中AVG_GRADE表示每门课程的平均成绩.
   答:INSERT INTO SC_C SELECT sc.CNO,C.CNAME,AVG(SC.SCGRADE) FROM SC,C WHERE SC.CNO=C.CNO
2. 从SC表中把选何昊老师所授课程的女生的选课记录删除.
   答:DELETE FROM sc,s,c WHERE sc.SNO=s.SNO AND sc.CNO=c.CNO AND s.SEX=‘女’ AND c.CTEACHER='何昊’
3. 规定女生所选修何昊老师的课程的成绩都应该在80分以上(含80).
   答:ALTER TABLE sc,s,c ADD CONSTRAINT sc.SCGRADE CHECK(sc.SCGRADE >= 80)WHERE sc.CNO=c.CNO AND sc.SNO=s.SNO AND c.CTEACHER='何昊’
4. 找出没有选修过何昊老师的课程的所有学生的名字
   答:SELECT S.SNAME FROM s WHERE NOT EXISTS(SELECT * FROM sc,c WHERE sc.CNO=c.CNO AND c.CNAME=‘何昊’ AND sc.SNO=s.SNO)
5. 列出有两门以上(含两门)不及格课程(成绩小于60)的学生的姓名极其平均成绩.
   答:SELECT s.SNO,s.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM S,sc,(SELECT SNO FROM sc WHERE SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO) >=2) AS A WHERE S.SNO=A.SNO AND sc.SNO=A.SNO GROUP BY S.SNO,S.SNAME
6. 列出既学过"1"号课程,又学过"2"号课程的所有学生的姓名.
   答:SELECT S.SNO,S.SNAME FROM S,(SELECT SC.CNO=C.CNO AND C.CNAME IN(‘1’,‘2’) GROUP BY SNO HAVING COUNT(DISTINCT CNO)=2)sc WHERE S.SNO=sc.SNO

delete与truncate的区别

相同点:都可以用来删除一个表中的数据
不同点:

1. truncate 是一个数据定义语言(DDL),它会被隐式的提交,一旦执行不能回滚.delete执行的过程是每次从表中删除一行数据,同时将删除的操作与日志的形式保存,可以回滚.
2. 用delete操作后,被删除的数据占用的存储空间还在,还可以恢复.而用truncate操作删除数据后,被删除的数据会立即释放占用的存储空间,不能恢复.
3. truncate的执行速度比delete快.

内连接与外连接的区别

内连接也被称为自然连接,由于内连接是从结果表中删除与其他连接表中没有匹配的所有行,所以内连接可能会造成信息的丢失.
内连接语法:
select fieldlist from table1 [inner | left | right ] join table2 on 条件语句

内连接保证两个表的所有行都满足连接条件.外连接不仅包含符合连接条件的行,还包括左连接,右连接,全外连接中所有的数据行.
外连接有左外连接(left outer join), 右外连接(right outer join), 全外连接(full outer join)

什么是事务

事务是数据库中一个单独的执行单元,当在数据库中更改数据成功时,在事务中更改的数据便会提交,不再改变.否则事务就会回滚,更改无效

以网上购物为例,其交易过程至少包括以下几个步骤:

1. 更新客户所购商品的库存信息.
2. 保存客户付款信息
3. 生成订单并且保存到数据库中
4. 更新用户相关信息,例如购物数量等.
   这四步就是一个事务,只要有一步出错就会回滚事务.

事务的四个属性

1. 原子性(atomicity)
   事务是不可分割的整体,为了实现事务的总体目标,事务必须具有原子性,要么全执行,要么全不执行.
2. 一致性(consistency)
   一个事务执行之前和执行之后,数据库的数据必须保持一致性状态.例如银行转账前后两个账户金额之和应该保持不变.不一致性体现在:丢失数据修改,读’脏’数据,不可重复读和产生’幽灵’数据.
3. 隔离性(isolation)
   隔离性也称为独立性.为了保证数据的安全性,**将一个事务内部的操作与事务的操作隔离起来.**例如对于一对事务t1和t2,对t1而言,t2要么在t1开始前已经结束,要么在t1结束后才执行.

数据库的4个隔离级别:

• 不提交的读
• 提交的读
• 可重复的读
• 串行化

实现隔离性是解决临时更新与消除级联回滚问题的一种方式.

4. 持久性(durability)
   持久性也被称为永久性,事务完成后对数据库中的数据的修改是永久的.

COMMIT手动提交事务,ROLLBACK回滚事务.

什么是存储过程?它与函数有什么区别

SQL语句要先编译才能被执行.在大型数据库(比如MySQL)中被允许将语句集编译优化后存储在数据库服务器中,通过指定存储过程的名字来调用执行.

关于存储过程的语法

创建
create procedure sp_name @[参数名] [ 类型]
as
begin
…
end

调用
exec sp_name[参数名]

删除
drop procedure sp_name

存储过程不等于函数,比如:

1. 存储过程一般是作为独立的部分来执行,而函数可以作为查询语句的一个部分来调用.由于函数可以返回一个对象,因此它可以在查询语句中位于from关键字的后面.
2. 一般而言,存储过程实现的功能较复杂,而函数实现的功能针对性较强.
3. 存储过程可以返回多个参数,函数需要用括号包住输入的参数且只能返回一个值或表对象.
4. 函数可以嵌入在SQL中使用,可以在select中调用,存储过程则不行.
5. 存储过程在创建时即在服务器上进行了编译,其执行速度比函数快.
6. 函数不能直接操作实体表,只能操作内建表.

各种范式有什么区别

1. 1NF是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性.简而言之就是无重复的列.
2. 2NF要求数据库表中的每一个实例或行必须可以被唯一的区分,即新增或选中一列作为唯一标识符.
3. 3NF要求**关系模式R满足第二范式,且每一个非主属性都不传递依赖于R的候选键.**举个栗子:
   已知学生表(学号,姓名,课程号,成绩),其中学生姓名没有重复,因此这张表有两个候选码(学号,课程号)和(姓名,课程号),即既可以通过已知学号和课程号查出成绩也可以通过已知姓名和课程号查出成绩,有结论非主属性:成绩对候选码不存在部份依赖,也不存在传递依赖,所以这张表属于3NF.
4. BCNF要求**关系模式R是1NF且每个属性都不传递依赖于R的候选键.**举个例子.
   已知仓库管理关系表(仓库号,存储物品号,管理员号,数量),满足一个管理员只在一个仓库工作.一个仓库可以存储多种物品,则有如下关系:
   (仓库号,存储物品号)->(管理员号,数量)
   (管理员号,存储物品号)->(仓库号,数量)
   所以(仓库号,存储物品号)和(管理员号,存储物品号)都是仓库管理关系表的候选码,表中唯一非关键字段为数量,因此满足3NF.接着观察:
   (仓库号)->(管理员号)
   (管理员号)->(仓库号)
   存在关键字段决定关键字段的情况,不符合BCNF.
   我们可以把仓库管理关系表分成仓库管理表(仓库号,管理员号)和仓库表(仓库号,仓库物品号,数量),这样就满足BCNF了.
5. 4NF要求**在满足BCNF的基础上已知D是关系模式R的多值依赖集合,如果D存在凡多值依赖X->Y时,X必是R的超键.**举个例子:
   已知职工表(职工编号,职工孩子姓名,职工选修课程),在这张表中同一个职工可能会有多个职工孩子姓名,同样,同一个职工可能会有多个选修课程,因此我们要将其分表使它们只有一个多值事实,例如表1(职工编号,职工孩子姓名),表2(职工编号,职工选修课程).这时就满足4NF.

什么是触发器

触发器是一种特殊类型的存储过程,由事件触发而不是手动调用.
使用触发器可以用来保证数据的有效性和完整性

触发器与存储过程的区别

触发器	存储过程
当某类数据操作DML语句发生时隐式调用	从一个应用或过程中显式调用
在触发器体内禁止使用COMMIT和ROLLBACK	允许使用所有SQL块中都能使用的sql语句
不能接受参数输入	可以接受参数输入

触发器的作用

DML触发器由after(后置触发)和instead of(前置触发) 这两种触发器.
DDL触发器是在响应数据定义语言时执行的.
触发器的主要作用:

1. 增加安全性
2. 利用触发器记录所进行的修改以及相关信息,跟踪用户对数据库的操作,实现审计.
3. 维护那些通过创建表时的声明约束不可能实现的完整性约束以及对数据库中特定事件进行监控与响应.
4. 实现复杂的非标准的数据库相关完整性规则,同步实时的复制表中的数据.
5. 触发器大的自动特性可以实现自动计算数据值.

触发器使用的例子:
create or replace trigger update_on_weekends_check
before update of sal on emp
for each row
declare
my_count number(4);
begin
select count(u_name)
from weekend_update_ok into my_count
where u_name=user_name;
if my_count=0
then raise_application_error(20508,‘update not allowed’);
end if;
end;

前置触发与后置触发的区别?语句级触发与行级触发的区别?

前置触发执行在事件发生前,用于验证条件或进行准备工作.后置触发发生在事件之后,做收尾工作.
语句级触发可以在语句执行前或之后执行,而行级触发在触发器所影响的每一行触发一次.

什么是游标

游标提供了一种对从表检索出的数据的灵活手段,它实际上是一种能从包含多条数据记录的结果集中每次提取一条记录的机制.
游标是由结果集和结果集中指向特定记录的游标位置组成的,因此总是与一条SQL选择语句相关联.当决定对结果集进行处理时,必须声明一个指向该结果集的游标.
游标允许对查询语句select返回的结果集中的每一行进行相同或不同的操作.

例子:声明一个游标 student_cursor,用于访问数据库school中的"学生基本信息表",
use school
go
declare student_cursor cursor
from select * from 学生基本信息表

使用游标的优点:

1. 在使用游标的表中,对行提供删除和更新的功能.
2. 游标将面向集合的数据库管理系统和面向行的程序设计连接起来.

如果数据库日志满了会怎样

日志文件记录了所有对数据库数据的修改,主要是保护数据库以防故障发生,以及恢复数据时使用.其特点如下:

1. 每一个数据库至少包含两个日志文件组.每一个日志文件组至少包含两个日志文件成员.
2. 日志文件组以循环方式进行写操作.
3. 每一个日志文件成员对应一个物理文件.

通过日志文件来记录数据库事务可以最大限度地保证数据地一致性与安全性,但一旦数据库日志满了,就只能执行查询等读操作.

union和union all的区别

union在进行表 求并集 后会去掉重复的元素,所以会对产生的结果集进行排序运算,删除重复的记录再返回结果.

union all只是简单的将两个结果集 合并 后就返回结果.因此会包含重复数据.

什么是视图

视图是由数据库的基本表选取出来的数据组成的逻辑窗口(虚表),在数据库中存放的只是视图的定义.
视图的作用:

1. 简化数据查询语句
2. 使用户能从多角度看待同一数据,
3. 提高数据的安全性
4. 提供一定的逻辑独立性