标准sql语言 mysql_数据库—SQL—标准语言SQL

3.1 SQL概述

3.1.2 SQL的特点1.综合统一 2.高度非过程化 3.面向集合的操作方式 4.用同一种语法结构提供多种使用方式 5.简洁

3.3数据定义

3.3.1 模式的定义和删除

模式定义语句

CREATE SCHEMA AUTHORIZATION

例1： 为用户WANG定义一个模式 S-T

CREATE SCHEMA AUTHORIZATION 如果不指定模式名 默认为用户名

模式的删除

DROP SCHEMA

CASCADE(级联)  删除模式时同时删除所有数据库对象

RESTRICT(限制)   如果已经定义了模式下的数据库对象  如表、图等，则不能删除该模式

二者必选其一

例： DROP SCHEMA　ZHANG CASCADE    删除模式ZHANG以及所有数据库对象

3.3.2 基本表的定义、删除与修改

例：建立一个学生表Student

CREATE TABLE Student

( Sno CHAR (9) PRIMARY KEY,//列级完整性约束条件  Sno是主码

Sname CHAR(20) UNIQUE,//Sname取唯一值

Ssex CHAR(2),

Sage SMALLINT,

Sdept CHAR(20)

);

执行语句后会在数据库中建立一个新的空的 学生 表 Student ，并将有关学生表的定义及有关约束条件存放在数据字典中。

数据类型：每一种属性选用一种数据类型，常用的数据类型有CHAR，INT，DATE，TIME等。

*模式和表  每一个基本表都属于某一个模式，一个模式包含多个表，

定义表时定义模式的方法：

1.在表中明显的给出模式名 CREATE TABLE “S-T”.Student(........);   //Student表所属的模式是S-T

2.在创建模式语句中同时创建表

例如：CREATE SCHEMA  TEST AUTHORIZATION ZHANG

CREATE TABLE TAB1(.......);

3.设置所属的模式，在创建表时不用给出模式名

修改基本表：SQL语句用 ALTER TABLE 语句修改基本表

例：向Student表增加入学时间列，数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE；

例：将年龄的数据类型由字符型改为整数

ALTER TABLE Student ALTER COLUMN Sage INT；

例：增加课程名必须取唯一值的约束条件

ALTER TABLE Course ADD UNIQUE(Cname)；

删除基本表：用 DROP TABLE 可以删除基本表

例：删除Student表

DROP TABLE Student CASCADE;//若该表上建有视图，则用RESTRICT不能删除，用CASCADE则可以删除

索引的建立和删除(略)

3.4 数据查询

3.4.1 单表查询

例如： 查询全体学生的学号和姓名

SELECT Sno，Sname

FROM Student；

查询全体学生的姓名、学号、所在系

SELECT Sname，Sno，Sdept

FROM Student；

查询全部列

SELECT *

FROM Student；

上式等价于

SELECT Sno，Sname，Ssex，Sage，Sdept

FROM Student；

查询经过计算的值：

查询全体学生的姓名和出生年份

SELECT Sname，2014-Sage  //当时时间减去年龄  结果为算数表达式

FROM Student；

查询出的结果可以是算数表达式、字符串常量、函数，例如：

查询全体学生的姓名，出生年份，所在院系，要用小写字母表示所在院系

SELECT Sname, 'Year of Birth:',2004-Sage,LOWER(Sdept)

FROM Student;

消除重复的行：

SELECT DISTINCT Sno

FROM SC；

如果没有DISTINCT  则缺省值为ALL  等价于

SELECT ALL Sno

FROM SC；

查询满足条件的元组：

例：查询计算机科学系全体学生的名单：

SELECT Sname

FRMO Student

WHERE Sdept = 'CS';

查询年龄在20岁以下的学生姓名及年龄

SELECT Sname，Sage

FROM Student

WHERE Sage<20;

查询考试成绩不合格的学生学号

SELECT DISTINCT Sno

FROM SC

WHERE Gread<60;

查询年龄在20--30岁之间的学生姓名、系别和年龄

SELECT Sname，Sdept，Sage

FROM Student

WHERE Sage BETWEEN 20 AND 30；

确定集合：IN可以用来查找指定集合的元组

查找CS系，MA系，IS 系的学生姓名和性别

SELECT Sname，Ssex

FROM Student

WHERE Sdept IN ('CS','MA','IS');

同理，查找不在集合的元组则用 NOT IN。

字符匹配：

查询学号为200215121的学生的详细情况

SELECT *

FROM Student

WHERE Sno LIKE ‘200215121’；

等价于

SELECT *

FROM Student

WHERE Sno = ‘200215121’；

查找所有刘姓的学生的姓名，学号，性别

SELECT Sname，Sno，Ssex

FROM Student

WHERE Sname LIKE ‘刘%’；

查询姓欧阳，且名字是三个字的学生姓名，学号，性别

SELECT Sname，Sno，Ssex

FROM Student

WHERE Sname LIKE ‘欧阳_’；

查询 DB_Design课程的课程号和学分

SELECT Cno,Ccredit

FROM Course

WHERE Cname LIKE 'DB\_Design' ESCAPE '\';// ESCAPE '\' 表示'\'为换码字符 所以此时 '_' 不具有通配符的含义 是普通的字符

涉及空值的查询：查询没有成绩的(不是0分)的学生学号和课程号

SELECT Sno，Cno

FROM SC

WHERE Grade IS NULL;

查询所有学生的学号和课程号

SELECT Sno，Cno

FROM SC

WHERE Grade IS NOT NULL;

多重条件查询：

查询计算机科学系年龄在20以下的学生姓名

SELECT Sname

FROM Student

WHERE Sdept = 'CS' AND Sage<20;

ORDER BY 子句

使用ORDER BY 子句可使查询结果升序(ASC)或降序(DESC)排序，缺省值为升序

例：查询选修了3号课程的学生学号及成绩，结果按分数降序排序

SELECT Sno，Grade

FROM SC

WHERE Cno = ‘3’

ORDER BY Grade DESC；

聚集函数：COUNT 统计元组个数

SUM 计算总和

AVG 计算平均值

MAX MIN 最大和最小值

例：查询学生总人数

SELECT COUNT(*)

FROM Student；

查询选修了课程的总人数

SELECT COUNT(DISTINCT Sno)

FROM SC；

计算1号课程的学生平均成绩

SELECT AVG (Grade)

FROM SC

WHERE Cno = ‘1’；

查询选修1号课程学生的最高分

SELECT MAX (Grade)

FROM SC

WHERE Cno = ‘1’；

3.4.2 连接查询

例：查询每个学生及其选修课情况

SELECT Student.*,SC.*

FROM Student,SC

WHERE Student.Sno = SC.Sno;   //将Student和SC同一学生的元组连接起来

多表连接

例：查询每个学生的学号 姓名 选修的课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno AND SC.Sno = Course.Cno;

3.4.3 嵌套查询

例：查询与刘晨在同一个系的学生

1，确定刘晨所在系名

SELECT Sdept

FROM Student

WHERE Sname = ‘刘晨’；

结果为 CS

2，查找在CS系中的学生

SELECT Sname

FROM Student

WHERE Sdept = ‘CS’；

将第一步查询潜入到第二步中：

SELECT Sname

FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname = ‘刘晨’);

带有ANY 或ALL 的查询

例：查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄

SELECT Sname，Sage

FROM Student

WHERE  Sage< ALL

(SELECT Sage

FROM Student

WHERE Sdept = 'CS')

AND Sdept <> 'CS';

3.5 数据更新

3.5.1插入数据  使用INSERT语句

例：将一个新学生元组插入到Student表中

INSERT

INTO Student (Sno，Sname，Ssex，Sdept，Sage)

VALUES(‘200215128’，‘陈东’，‘男’，‘IS’，18)；

如果没有指明属性列名，则新插入的元组必须在每个属性列上均有值。

例：插入一条选课记录

INSERT

INTO SC(Sno，Cno)

VALUES (‘200215128’，‘1’)；

3.5.2 修改数据

例：将学生200215121的年龄改为21岁

UPDATE

SET Sage = 21

WHERE Sno = ‘200215121’；

例：将所有学生年龄加一

UPDATE

SET Sage = Sage + 1;

带子查询的修改语句：

例：将计算机科学系全体学生成绩置零

UPDATE

SET Grade = 0

WHERE ‘CS’ =

(SELECT Sdept

FROM Student

WHERE Student.Sno = SC.Sno)；

3.6  删除数据

删除数据用DELETE

例：删除学号为200215128的学生记录

DELETE

FROM Student

WHERE Sno = ‘200215128’；

例：删除所有学生选课记录

DELETE

FROM SC；

SELECT Sname

FROM Student

WHERE Sdept = ‘CS’；