数据库系统（上）：模型与语言--第6讲 SQL语言之概述

目录

• SQL语言概述

• SQL语言之DDL——定义数据库

• SQL语言之DML——操纵数据库

• 重难点

  • SQL——DDL的基本语句：CREATE DATABASE,CREATE TABLE
  • SQL——DML的基本语句：INSERT,DELETE,UPDATE,SELECT
  • SQL-SELECT语句的训练：正确表达各种查询需求

SQL语言概述

• 历史：
  • 1974年，Boyce和Chamber提出
  • 1975-1979年，IBM的San Jose研究室在System R上首次实现，称为Sequel（Structured English QUEry Language）——>SQL（Structured Query Language）

 SQL到底怎么读？
 Sequel    /'siːkw(ə)l/

• SQL语言是集DDL（Data Definition Language）、DML（Data Manipulation Language）、DCL（Data Control Language）于一体的数据库语言
• SQL语言主要由以下9个单词引导的操作语句构成，但是每一种语句都能表达复杂的操作请求。

  • DDL语句引导词：Create（建立）、Alter（修改）Drop（撤销）

    • //模式的定义和删除，包括定义Database,Table,View,Index,完整性约束
      条件等，也包括定义对象(RowType行对象,Type列对象)

  • DML语句引导词： Insert , Delete , Update , Select

    • 各种方式的更新与检索操作，如直接输入记录，从其他Table(由SubQuery
      建立)输入
    • 各种复杂条件的检索，如连接查找，模糊查找，分组查找，嵌套查找等
    • 各种聚集操作，求平均、求和等，分组聚集，分组过滤等

  • DCL语句引导词： Grant , Revoke

    • 安全性控制：授权和撤消授权

SQL语言的分类

• 交互式SQL：DBMS环境中直接操作数据
• 嵌入式SQL：应用程序员在某一个高级程序语言中使用数据库语言编写对数据库的操作
• 动态SQL

学习SQL语言的要点

• 理解查询需求
• 用SQL精确表达

1.利用SQL语言建立数据库

• 建立数据库包括两件事：
  • 1.使用DDL定义数据库和表

  DDL: Data Definition Language的作用
  1.创建数据库(DB)—— Create Database
  2.创建DB 中的Table( 定义关系模式)—— Create Table
  3.定义Table 及其各个属性的约束条件（定义完整性约束）
  4.定义View （定义外模式及E-C映像）
  5.定义Index 、Tablespace… … 等(定义物理存储参数）
  6.上述各种定义的撤消与修正
  
  DDL通常由DBA来使用，也有经DBA授权后由应用程序员来使用
  
  

  • 2.使用DML向表中追加元组

 DML: Data Manipulation Language的作用
 1.向Table中追加新的元组：Insert
 2.修改Table中某些元组中的某些属性的值: Update
 3.删除Table中的某些元组: Delete
 4.对Table中的数据进行各种条件的检索: Select
 DML通常由用户或应用程序员使用，访问经授权的数据库

1. 创建Database

• 数据库（Database）是若干具有相互关联关系的Table/Relation的集合
• 数据库可以看作是一个集中存放若干Table的大型文件
• create database的简单语法形式：

create database 数据库名;

• 示例：创建课程学习数据库SCT

create database SCT;

2. 创建Table
   create table简单语法形式：

Create table 表名（列名 数据类型[Primary key|Unique][Not null]
				 [,列名 数据类型[Not null],...]);

• “[ ]”表示其括起的内容可以省略，“|”表示其隔开的两项可取其一
• Primary key：主键约束。每个表只能创建一个主键约束
• Unique：唯一性约束（即候选键）。可以有多个唯一性约束。
• Not null：非空约束。是指该列允不允许有空值出现，如果选择了Not null表明该列不允许有空值出现。
• 语法中的数据类型在SQL标准中有定义

在SQL-92标准中定义的数据类型
char(n):固定长度的字符串
varchar(n):可变长字符串
int:整数 	//有时不同系统也写作integer
numeric(p，q):固定精度数字，小数点左边p位，右边p-q位
real:浮点精度数字 //有时不同系统也写作float(n)，小数点后保留n位
date:日期(如 2003-09-12)
time :时间(如 23:15:003)
...
现行商用DBMS的数据类型有时和上面有些差异，请注意;
和高级语言的数据类型，总体上是一致的，但也有些差异

• 示例：定义学生表Student

Create Table Student(S# char(8) not null, Sname char(10), Ssex char(2), Sage integer, D# char(2), Sclass char(6));

• 示例：定义课程表Course

Create Table Course(C# char(3), Cname char(12), Chours integer, Credit float(1), T# char(3));

• 示例：定义院系表Dept

Create Table Dept(D# char(2), Dname char(10), Dean char(10));

• 示例：定义教师表Teacher

Create Table Teacher(T# char(3),Tname char(10), D# char(2), Salary float(2));

• 示例：定义选课表SC

Create Table SC(S# char(8), C# char(3), Score float(1));

3. 向表中追加元组
   insert into 简单语法形式：

Insert Into 表名[列名[,列名]...]
	Values (值[,值],...);

• values后面值的排列，需要与into子句后面的列名排列一致
• 若表名后的所有列名省略，则values后的值的排列，需要与该表存储中的列名排列一致
• 示例：追加学生表中的元组

Insert Into Student
Values('98030101','张三','男',20,'03','980301');

Insert Into Student(S#,Sname,Ssex,Sage,D#,Sclass)
Values('98030102','张四','女',20,'03','980301');

• 示例：追加课程表中的元组

Insert Into Course
Values('001','数据库',40,6,'001');

Insert Into Course(Cname,C#,Credit,Chours,T#)
Values('数据库','001',6,40,'001');

2.利用SQL语言进行简单查询

SQL提供了结构形式一致但功能多样化的检索语句Select

• Select的简单语法形式

Select 列名 [[,列名]...]
From 表名
[Where 检索条件];
//从表名所给出的表中，查询出满足检索条件的元组，并按给定的列名及顺序进行投影显示。

• 相当于：∏_{列名,…,列名}(σ_检索条件(表名))
• Select语句中的select…,from…,where…,等被称为子句，在以上基本形式基础上会增加许多构成要素，也会增加许多新的子句，满足不同的需求。
• 示例：检索学生表中所有学生的信息

Select S#,Sname,Ssex,Sage,Sclass,D#
From Student;

Select * From Student; //如投影所有列，则可以用*来简写

• 示例：检索学生表中所有学生的姓名及年龄

Select Sname,Sage //投影出某些列
From Student;

• 示例：检索学生表中所有年龄小于等于19岁的学生的年龄及姓名

Select Sage,Sname //投影的列可以重新排定顺序
From Student
Where Sage<=19;

• 示例：检索教师表中所有工资少于1500元或者大于2000元并且是03系的教师姓名？

Select Tname
From Teacher
Where (Salary<1500 or Salary>2000) and D#='03';

• 示例：求或者学过001号课程，或者学过002号课程的学生的学号

Select S#
From SC
Where C#='001' or C#='002';

结果唯一性问题

• 关系模型不允许出现重复元组。但现实DBMS ，却允许出现重复元组，但也允许无重复元组。
• 在Table 中要求无重复元组是通过定义Primary key 或Unique 来保证的;而在
  检索结果中要求无重复元组, 是通过DISTINCT保留字的使用来实现的。
• 示例：在选课表中，检索成绩大于80分的所有学号

Select DISTINCT S#
From SC
Where Score>80;

结果排序问题

• DBMS 可以对检索结果进行排序，可以升序排列，也可以降序排列。
• Select 语句中结果排序是通过增加order by 子句实现的

order by 列名 [asc][desc]
//意义为检索结果按指定列名进行排序，若后跟asc或省略，则为升序；若后跟desc,则为降序。

• 示例：按学号由小到大的顺序显示出所有学生的学号及姓名

Select S#, Sname From Student
Order By S# ASC ;

• 示例：检索002号课大于80分的所有同学学号并按成绩由高到低顺序显示

Select S# From SC Where C# = '002' and Score > 80
Order By Score DESC ;

模糊查询问题

• 比如检索姓张的学生，检索张某某；这类查询问题，Select语句是通过在检索条件中引入运算符like 来表示的
• 含有like运算符的表达式:

列名 [not] like “字符串”

• 找出匹配给定字符串的字符串。其中给定字符串中可以出现%, _ 等匹配符
• 匹配规则：

 “%” 匹配零个或多个字符
 “_” 匹配任意单个字符
 “ \ ” 转义字符，用于去掉一些特殊字符的特定含义，使其被作为普通字符看待，如用 “\%” 去匹配字符% ，用\_去匹配字符_

• 示例：检索所有姓张的学生学号及姓名

Select S#, Sname From Student
Where Sname Like '张%';

• 示例：检索名字为张某某的所有同学姓名

Select Sname From Student
Where Sname Like '张__';

• 示例：检索名字不姓张的所有同学姓名

Select Sname From Student
Where Sname Not Like '张%';

利用SQL语言进行多表联合查询

• 多表联合查询
  • 多表联合检索可以通过连接运算来完成，而连接运算又可以通过广义笛卡尔积后再进行选择运算来实现。
  • Select 的多表联合检索语句

Select 列名 [ [,列名]...]
From 表名1,表名2,...
Where 检索条件;

• 相当于∏_{列名,…,列名}(σ_检索条件(表名1×表名2×…))
• 检索条件中要包含连接条件，通过不同的连接条件可以实现等值连接、不等值连接及各种θ-连接

θ-连接之等值连接

• 示例：按“001”号课成绩由高到低顺序显示所有学生的姓名(二表连接)

Select Sname From Student, SC
Where Student.S# = SC.S# and SC.C# = ‘001’
Order By Score DESC;

• 多表连接时，如两个表的属性名相同，则需采用表名.属性名方式来限定该属性是属于哪一个表
  • 示例：按‘数据库’课成绩由高到低顺序显示所有同学姓名(三表连接)

Select Sname From Student, SC, Course
Where Student.S# = SC.S# and SC.C# = Course.C# and Cname = '数据库'
Order By Score DESC;

重名之处理

• 连接运算涉及到重名的问题，如两个表中的属性重名，连接的两个表重名(同一表的连接)等，因此需要使用别名以便区分
• select中采用别名的方式

Select 列名 as 列别名 [[,列名 as 列别名] ...]
From 表名1 as 表别名1,表名2 as 表别名2,...
Where 检索条件;
//上述定义中的as可以省略
//当定义了别名后，在检索条件中可以使用别名来限定属性

θ-连接之不等值连接

• 示例：求有薪水差额的任意两位教师

Select T1.Tname as Teacher1, T2.Tname as Teacher2
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary ;

• 示例：求年龄有差异的任意两位同学的姓名

Select S1.Sname as Stud1, S2.Sname as Stud2
From Student S1, Student S2
Where S1.Sage > S2.Sage;

• 示例：求既学过“001”号课又学过 “002”号课的所有学生的学号

Select S1.S# From SC S1, SC S2
Where S1.S# = S2.S# and S1.C#='001' and S2.C#='002' ;

• 示例：求“001”号课成绩比“002”号课成绩高的所有学生的学号

Select S1.S# From SC S1, SC S2
Where S1.S# = S2.S# and S1.C#='001'
	  and S2.C#='002' and S1.Score > S2.Score;

利用SQL语言增-删-改

• 元组新增Insert：新增一个或一些元组到数据库的Table中

• 元组更新Update:对某些元组中的某些属性值进行重新设定

• 元组删除Delete：删除某些元组

• SQL-DML既能单一记录操作，也能对记录集合进行批更新操作

• SQL-DML之更新操作需要利用前面介绍的子查询(Subquery)的概念，以便处理“一些”、“某些”等。

SQL- 之INSERT

• 元组新增Insert命令有两种形式
  • 单一元组新增命令形式：插入一条指定元组值的元组

insert into 表名 [(列名[,列名]...)]
values  (值[,值]...);

• 示例：单一元组新增

Insert Into Teacher (T#, Tname, D#, Salary)
Values ("005", "阮小七", "03", "1250");

Insert Into Teacher
Values ("006", "李小虎", "03", "950");

- **批数据**新增命令形式：插入子查询结果中的若干条元组。待插入的元组由子查询给出。

insert into 表名 [(列名[,列名]...)]
子查询;

• 示例：批元组新增
  • 新建立Table: St(S#, Sname), 将检索到的满足条件的同学新增到该表中

Insert Into St (S#,Sname)
	Select S#, Sname From Student
	Where Sname like '%伟';
	
Insert Into St(S#,Sname)
	Select S#, Sname From Student Order By Sname;

• 示例：新建Table: SCt(S#, C#, Score), 将检索到的成绩及格同学的记录新增到该表中

Insert Into SCt (S#, C#, Score)
	Select S#,C#,Score From SC
	Where Score>=60;

• 示例：追加成绩优秀同学的记录

Insert Into SCt (S#, C#, Score)
	Select S#,C#,Score From SC
	Where Score>=90 ;

• 示例：新建Table: St(S#, Sname, avgScore), 将检索到的同学的平均成绩新增到该表中

Insert Into St (S#, Sname, avgScore)
	Select S#, Sname, Avg(Score) From Student, SC
	Where Student.S#=SC.S#
	Group by Student.S#;

SQL- 之DELETE

• 元组删除Delete 命令：删除满足指定条件的元组

Delete From 表名 [Where 条件表达式];
//  如果Where条件省略，则删除所有的元组

• 示例：删除SC表中所有元组

Delete From SC ;

• 示例：删除98030101号同学所选的所有课程

Delete From SC Where S# ='98030101';

• 示例：删除自动控制系的所有同学

Delete From Student Where D# in
		(Select D# From Dept Where Dname ='自动控制');

• 示例：删除有四门不及格课程的所有同学

Delete From Student Where S# in
		(Select S# From SC Where Score < 60
		 Group by S# Having Count(*)>= 4);

SQL-之UPDATE

• 元组更新Update 命令：用指定要求的值更新指定表中满足指定条件的元组的指定列的值

Update 表名
Set 列名 = 表达式|(子查询)
	[[,列名 = 式 表达式|(子查询)]...]
[Wher条件表达式];
//如果Where 条件省略，则更新所有的元组

• 示例：将所有教师工资上调5%

Update Teacher
Set Salary = Salary*1.05;

• 示例：将所有计算机系的教师工资上调10%

Update Teacher
Set Salary = Salary*1.1
Where D# in
	(Select D# From Dept Where Dname ='计算机');

• 示例：当某同学001号课的成绩低于该课程平均成绩时，将该同学该门
  课成绩提高5%

Update SC
Set Score = Score*1.05
Where C# = '001' and Score < some
			(Select AVG(Score) From SC
			Where C# ='001');

• 示例：将张三同学001号课的成绩置为其班级该门课的平均成绩

Update SC
Set Score =(Select AVG(SC2.Score)
			From SC SC1,Student S1,SC SC2,Student S2
			Where S1.Sclass = S2.Sclass and SC1.S# = S1.S#
				and SC2.S# = S2.S# and S1.Sname='张三'
				and SC1.C# ='001' and SC1.C# = SC2.C# )
Where C# = '001' and S# in (Select S# From Student
			Where Sname ='张三');

利用SQL语言修改与撤销数据库

• 修正数据库： 修正数据库的定义，主要是修正表的定义

修正基本表的定义

alter table tablename
[add {colname datatype,...}] 	//增加新列
[drop { 完整性约束名}] 	//删除完整性约束
[modify {colname datatype,...}] 	//修改列定义

• 示例： 在学生表Student(S#,Sname,Ssex,Sage,D#,Sclass)基础上增加二列Saddr, PID

Alter Table Student Add Saddr char[40], PID char[18];

• 示例：将上例表中Sname列的数据类型增加两个字符

Alter Table Student Modify Sname char(10);

• 示例：删除学生姓名必须取唯一值的约束

Alter Table Student Drop Unique(Sname );

撤消基本表

drop table 表名

• 示例：撤消学生表Student

Drop Table Student;

• 示例：撤消教师表Teacher

Drop Table Teacher;

注意

• SQL-delete语句只是删除表中的元组,
• 撤消基本表droptable的操作是撤消包含表格式、表中所有元组、由该表导出的视图等相关的所有内容，所以使用要特别注意。

撤消数据库

drop database 数据库名;

• 示例：撤消SCT数据库

Drop database SCT;

指定当前数据库

use 数据库名;

关闭当前数据库

close 数据库名;

总结

典型DBMS交互环境简介-SQL Server

• Microsoft提供的一款关系数据库管理系统

系统提供的基本库

• Master ：是SQL Server中最重要的系统数据库，存储SQL Server 中的元数据。
• Model：模板数据库，在创建新的数据库时，SQL Server将会复制此数据库作为新数据库的基础。
• Msdb：代理服务数据库，提供一个存储空间。
• Tempdb：临时数据库，为所有的临时表、临时存储过程及其他临时操作提供存储空间，断开连接时，临时表与存储
  过程自动被删除。

文件类型

• 文件：有三种文件扩展名：.mdf 、.ndf 、.ldf
  • 主数据库文件：扩展名为. mdf，是存储数据库的启动信息和部分或全部数据。
    一个数据库可以有多个数据库文件，但主数据库文件只有一个。
  • 辅助数据文件：扩展名为. ndf，用于放置主数据库文件中所定义数据库的其它
    数据，可有多个。在数据庞大时，可以帮助存储数据。
  • 日志文件：扩展名.ldf 。每个数据库至少有一个事务日志文件。
• 页面：是SQL Server 存储的最小单位。一页为8K 或8192 字节。
• 空间(extent) ：是8 个连续的页面，即64K