数据库学习—SQL语言概述

课程来自于中国大学MOOC：哈尔滨工业大学 战德臣老师主讲的《数据库系统(上)》

SQL语言是集DDL(定义)、DML(操作)、DCL(控制)于一体的数据库语言，主要由以下9个引导词引导的的操作语句来构成：

• DDL语句引导词：CREATE(建立), ALTER(修改), DROP(撤销)，用于模式的定义和删除。
• DML语句引导词：INSERT(插入), DELETE(删除), UPDATE(更新), SELECT(选择)，实现各种对表的操作。
• DCL语句引导词：GRANT(授权), REVOKE(撤销授权)：用于安全性控制。

学习顺序：交互式SQL->嵌入式SQL->动态SQL等

一、创建数据库（创建库、创建表、添加元组）

1.创建数据库Craete datebase

语法形式：

Create datebase 数据库名;

/*创建一个名叫SCT的数据库*/
create datebase SCT; 

2.创建表Create table

语法形式：

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

• [ ]：表示能容可以省略，|表示可以二选一
• Primary：主键，每个表只能有一个主键
• Unique：候选键，可以有多个
• Not null ：不可为空约束

标准SQL92中数据类型：

• char(n)：长度为n的字符串
• varchar(n)：可变长字符串
• int：整数
• numeric(p,q)：固定进度数字，小数点左边p位，右边q位
• real：浮点精度数字，有时不同系统也写作float(n)，小数点后保留n位
• date：日期

/*创建学生表和选课表*/
Create Table Student ( Snum char(8) not null, Sname char(10), Ssex char(2), Sage integer, Dnum char(2), Sclass char (6) );
Create Table Course ( Cnum char(3), Cname char(12), Chours integer, Credit float(1), Tnum char(3) );
/*
	学生表Student：
	Snum | Sname | Ssex | Sage | Dnum | Scalss
	课程表Course：
	Cnum | Cname | Chours | Credit | Tnum 
*/

3.加入数据Insert Into

语法形式：

Insert into 表名[ 列名1, [列名2], [列名3], ...]
  values (值1, [值2], [值3], ...);

value后面值得排列必须和into后面列名顺序一样
into后面列名可以省略，若省略则value默认顺序就是定义的表标题

Insert Into  Student
  Values ( '98030101', '张三', '男', 20, '03', '980301' );     
/*省略了列名的*/

Insert Into Student ( Snum, Sname, Ssex, Sage, Dnum, Scalss )
  Values ( '98030202', '张四', '女', 20, '03', '980301' );     

Insert Into Course    
  Values ( '001', '数据库', 40, 6, '001');
/*省略了列名的*/

Insert Into Course( Cname, Cnum, Credit, Chours, Tnum)
  Values ( '001', '数据库', 40, 6, '001');

二、查询

1.简单查询Select From Where

语法形式：

Select 列名 [[列名], [列名], ...]
  From 表名
  Where [条件];

相当于一次选择和一次投影运算:

/*检索学生表中所有信息*/
Select Snum, Sname, Ssex, Scalss, Dnum
  From Student;		//所有信息也即投影所有列
/*等效于*/
Select * From Student;		//若投影所有列，则可以用'*'简写

/*检索学生表中所有学生姓名和年龄*/
Select Sname, Sage
  From Student;

/*检索学生表中所有学生年龄小于等于19岁学生的姓名和年龄*/
Select Sname, Sage
  From Student
  Where Sage <= 19; 

/*检索教师表中所有工资少于1500或工资大于2000且是03系教师的姓名*/
Select Tname
  From Teacher
  Where (salary < 1500 or salary > 2000) and Dnum = '03';	// and的优先级大于or，所以and前后加了括号

/*检索学过001号课程或学过002号课程的学生学号*/
Select Snum
  From SC
  Where Cnum = '001' or Cnum = '002';

2.结果唯一性问题：

关系模型基于集合，其中不允许出现重复元素，但现实的DBMS是基于表的，其中可能会包含重复元组。

在Table中要求无重复元组是通过主键Primary key或候选键Unique来保证的，而在检索结果中要求无重复元组是通过DISTINCT保留字的使用来实现的。

例：若要在选课表中检索满分同学的学号，则就可能出现重复，因为一个同学可能有多门课程满分。

用DISTINCT可以过滤重复的查询结果：

Select DISTINCT Snum
  From SC
  Where Score = 100;

3.查询结果排序问题

DBMS可以对查询结果排序，通过在Select语句中增加order by字句实现：

Order By 列名 [ASC | DESC]

ASC为升序，DESC为降序，省略默认为升序

/*按学校由小到大顺序显示出所有学生的学号及姓名*/
Select Snum, Sname
  From Student
  Order by Snum ASC;

/*检索002号课大于80分的所有同学学号并且按降序显示*/
Select Snum 
  From SC Where Cnum = '002' and Score > 80
  Order by DESC;

4.模糊查询问题

在检索时有时并不是按照某一精确关键字检索，比如“检索所有姓张的学生”，Select语句通过在检索条件中引入运算符like来表示模糊查询：

列名 [not] like "字符串"

匹配规则：

• “%”匹配零个或多个字符串
• “_”匹配任意单个字符
• “\“转义符

/*检索所有姓张的学生学号及姓名*/
Select Snum, Sname
  From Student
  Where Sname like '张%';

/*检索所有张某某学生的学号及姓名*/
Select Snum, Sname
  From Student
  Where Sname like '张____';	//'张____'一共四个下划线，汉字需要两个下划线表示

/*检索所有不姓张的学生的学号及姓名*/
Select Snum, Sname
  From Student
  Where Sname not like '张%';

5.多表联合查询

SQL中多表的连接通过广义笛卡尔积后在进行选择来实现：

语法形式：

Select 列名1, ...
 From 表1, 表2, ...		/*From后面写多个表名就表示多表乘积*/
 Where 检索条件			/*检索条件即选择条件*/

/*按001号课程成绩降序显示所有学生姓名*/
Select Sname
  From SC, Student
  Where Student.Smun = SC.Snum and SC.Cnum = '001'
  Order by Score DESC;

/*按数据库成绩降序显示所有学生姓名*/
Select Sname
  From Student, SC, Course
  Where Student.Snum = SC.Snum and SC.Cnum = Course.Cnum and Course.Cname = '数据库'
  Order by Score DESC;

6.同一个表的连接和重命名

SQL中某些操作会需要一个表和它自身连接起来，两个同名表为了区分，需要将其中一个改名。同理，列名和可以使用同样的方法更改，有时表名很长很复杂的时候也可以进行更名便于区分和书写：

语法形式：

Select 列名1 as 别名1, [列名2 as 别名2, ...]				  /*列可更名*/
  From 表名1 as 别名1, 表名2 as 别名2, ....		          /*表可更名*/
  Where [检索条件];
（as可省略）

/*求有薪水差额的任意两位教师*/
Select T1.Tname as Teacher1, T2.Tname as Teacher2	/*From后面对表的更名可以直接在Select和Where中使用，如T1,T2*/
  From Teacher as T1, Teacher as T2
  Where T1.Salary > T2.Salary;
/*注：Select后面跟的是列名(属性名)，为了区分两个Tname，需要用上面的形式对列更名为Teacher1和Teacher2*/

/*求年龄有差异的任意两位同学姓名*/
Select S1.Sname as Stu1, S2.Sname as Stu2
  From Student as S1, Student as S2
  Where S1.Sage > S2.Sage;

/*求既学过001号课程又学过002号课程所有学生的学号*/
Select SC1.Snum
  From SC as SC1, SC as SC2,
  Where SC1.Cnum = '001' and SC2.Cnum = '002' and SC1.Snum = SC2.Snum;

/*求001号课成绩比002号课成绩高的所有学生学号*/
Select SC1.Snum
  From SC as SC1, SC as SC2,
  Where SC1.Cnum = '001' and SC2.Cnum = '002' and SC1.Snum = SC2.Snum and SC1.Score > SC2.Score;

三、增删改操作

1.元组新增命令Insert Into

新增元组包括单一元组新增和批量新增：

单一元组新增(大标题二中已经提到)：

Insert into 表名[ 列名1, [列名2], [列名3], ...]
  values (值1, [值2], [值3], ...);

批量新增：

Insert Into 表名 [列名1, 列名2, ...]
  [子查询];

/*新建立Table:St(S#, Sname),将检索到的名字是"%伟"的同学新增到该表中*/
Create Table St( Snum char[8], Sname char[20] );
Insert into St(Snum, Sname)
  Select Snum, Sname
    From Student
    Where Sname like '%伟';

/*将Student表中的学生按Sname排序(默认升序)并插入St中*/
Insert Into St (Smun, Sname)
  Select Snum, Sname
    From Student
    Order By Sname;

/*新建Table:SCt(Snum, Cnum, Score), 将SC中检索到成绩及格的同学的记录新增到该表中*/
Create Table SCt( Snum char[8], Cnum char[3], Score int );
Insert Into SCt (Snum, Cnum, Score)
  Select Snum, Cnum, Score
    From SC
    Where Score >= 60;

2.元组删除命令DELETE

删除元组的语法与插入元组相似，其语法形式为：

Delete From 表名
  Where [条件];		/*若Where条件省略则表示删除所有元组*/

/*删除SC表中所有元组*/
Delete From SC;

/*删除98030101号同学所选的所有课程*/
Delete From SC
  Where Snum = '98030101';

/*删除斯莱特林院系的所有学生*/
Delete From Student
  Where Dnum in
  (Select Dnum
    From Dept
    Where Dname = '斯莱特林');
/*这是一套简单的嵌套子查询，后续章节会解释*/

3.元组修改(更新)命令UPDATE

“用指定要求的值更新指定表中满足指定条件的元组某一列的值”

解释： 对于满足Where条件的所有元组，将表达式i(或子查询i)的值赋给列名i这一列，可同时修改多列。

语法形式：

Update 表名
Set 列名1 = 表达式1 | (子查询1)
    列名2 = 表达式2 | (子查询2)
    ...
Where [表达式条件];		/*Where省略则更新所有元组*/

/*将所有教师工资上涨百分之5*/
Update Teacher
  Set Salary = Salary * 1.05;

/*将所有格里芬多院的教师工资上调百分之10*/
Update Teacher
  Set Salary = Salary * 1.1
  Where Dnum in
  (Select D#
     From Dept
     Where Dname = '格里芬多');

四、数据库的修正和撤销

1.修改表的定义命令ALTER ADD DROP MODIFY

语法形式：

Alter Table 表名 + ：
Add 列名1 类型1, 列名2 类型2, ...;
Drop 完整性约束名(列名);
Modify 列名 类型;

/*
学生表Student(Snum, Sname, Ssex, Sage, Sclass)
*/

/*在上表基础上增加两列Saddr, PID*/
Alter Table Student Add Saddr char[40], PID char[18];

/*在上表中修改Sname列数据类型为char[30]*/
Alter Table Student Modify Sname char[30];

/*删除学生姓名取唯一值的约束*/
Alter Table Student Drop Unique(Sname)

补充：关系数据库的三种完整性

1. 实体完整性：若属性A是某关系R的主属性，则属性A不能取空值。
2. 参照完整性： 对外键的约束。既关系R中的外键必须是另一个关系S的主键有效值，或者是NULL。
3. 用户自定完整性：用户定义的完整性规则，表示某个属性中的数据必须满足一定的语义要求，包括unique(唯一)、not null(非空)、check(满足某条件)。

2.撤销(删除)表和数据库命令DROP

语法形式：

Drop Table 表名；
Drop Datebase 数据库名；

注：DELETE命令删除的是表中的元组(行)，而DROP命令是将整张表全部删除。

/*撤销学生表*/
Drop Table Student;

/*撤销SCT数据库*
Drop Datebase SCT;

有些DBMS提供了操作多个数据库的能力，此时在数据库操作时要指定用哪个数据库，或是关闭某个数据库：

语法形式：

use 数据库名;
close 数据库名;