SQL语法（更新中）

r囧r小猫 2023-09-27 17:12 20阅读 0赞

**目录**

1、增加表的属性

2、删除基本表

3、表的更改

4、索引的建立与删除

5、数据查询

基于派生表的查询

定义视图

建立视图

删除视图

查询视图

视图更新

--------------------

#### 1、增加表的属性 ####

--1、向Student表增加“入学时间”列，其数据类型为日期
    
    ALTER TABLE Student ADD S_entrance DATE;
    
    --注意不论基本表中原来是否已有数据，新增加的列一律为空值（null）
    
    --2、将年龄的数据类型由字符型改为整型
    ALTER TABLE Student ALTER COLUMN Sage INT;
    
    
    --3、增加课程名称必须 取唯一值的约束条件
    ALTER TABLE Course ADD UNIQUE(Cname);

#### 2、删除基本表 ####

语句格式：DROP TABLE <表名>\[RESTRICT | CASCADE \]

restrict:限制删除

cascade:删除该表没有限制（级联，相关的依赖对向一起删除）例如：SC表里的学号是参考Student里面的学号，如果删了Student表同时会把SC表删了 。

注意：在同的数据库中使用 R和C 操作的结果可能是不同的。

#### 3、表的更改 ####

Alter Table Student        --更改学生表
        alter column Sage tinyint  --把Sage所在的列的类型改为tinyint类型
    Alter Table Student 
        add Sentrance smalldatetime   --在Student中添加一个时间属性（列）
    alter table Studnet 
        add constraint ck_stu_grade Check(Sage between 0 and 100) --年龄添加约束条件
    select* into aaa from Student   --复制Student表，表名为aaa
    drop table aaa  --删除表aaa
    select sno,sname from Student  -- 从Student表中选出 sno,sname 的属性

select cno ,AVG(grade) as 平均成绩
    from SC
    group by Cno
    
    select *
    from SC
    where grade <60

![63c6cb9f7f084607a3a0c2a0799c911b.png][]

#### 4、索引的建立与删除 ####

建立索引的目的：加快查询速度。

DBMS一般会在：PRIMARY KEY 或 UNIQUE 上自动建立索引

索引是关系数据库内部实现技术，属于内模式的范畴

语句格式：CREATE \[UNIQUE\] \[CLUSTER\] INDEX<索引名> ON<表名>

①QNIQUE表明此索引每一个索引值只对应唯一的数据

--为学生-课程数据库中的Student，Course，SC三个表建立三个索引
    CREATE UNIQUE INDEX Stusno ON Student(Sno);
    CREATE UNIQUE INDEX Coucno ON Course(Cno);
    CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); --其中ASC（升序），DESC（降序）

②CLUSTER 表示要建立聚簇索引。指对实际物理存储调换位置

CREATE CLUSTER INDEX Stuname ON Student(Sname);

删除索引

语句格式：DROP INDEX <索引名>;

删除索引时，系统会从数据字典中删去有关该索引的描述。

--删除Student表的Stusname索引
    DROP INDEX Stusname;

数据字典记录了数据库中所有的定义信息

#### 5、数据查询 ####

查询之前我们要知道1、你要去哪个表里面查 2、你要按照什么条件查  
3、你最后显示什么内容。

语句格式：

SELECT <要显示的数据>

FROM<要查的表的名字或视图>

WHERE <查询的条件>

注意：①语句中的字母不区分大小写。②\[ \]里面的内容 可有可无的

Ⅰ单表查询

功能：对一个表的内容进行查询。

--查询全体学生的学号与姓名
    SELECT Sno,Sname FROM Student;
    --查询全体学生的详细记录
    SELECT * FROM Student;
    --查询全体学生的姓名及出生年份。
    SELECT Sname,2004-Sage FROM Student;

显示结果：（注意这里2004 - Sage 是时间减去年龄）

<table style="width:400px;"> 
 <tbody> 
  <tr> 
   <td>Sname</td> 
   <td>&nbsp; &nbsp; 2004 - Sage</td> 
  </tr> 
  <tr> 
   <td>张三</td> 
   <td>1982</td> 
  </tr> 
  <tr> 
   <td>李四</td> 
   <td>1983</td> 
  </tr> 
 </tbody> 
</table>

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

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

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>Sname</td> 
   <td>'Year' of Birth:'</td> 
   <td>2004 - Sage</td> 
   <td>LOWER(Sdept)</td> 
  </tr> 
  <tr> 
   <td>张三</td> 
   <td>'Year' of Birth:'</td> 
   <td>1982</td> 
   <td>cs</td> 
  </tr> 
  <tr> 
   <td>李四</td> 
   <td>'Year' of Birth:'</td> 
   <td>1983</td> 
   <td>cs</td> 
  </tr> 
 </tbody> 
</table>

所以在SELECT 后面可以跟 表里面的属性、字符串、算数表达式、函数。

使用列别名改变查询结果的列标题

SELECT Sname NAME FROM Student ;

Ⅱ选择表中的若干元组

（1）取消重复的行 ——使用DISTINCT关键字

--查询选修了课程的学生学号
    SELECT Sno FROM SC;
    --等价于：SELECT ALL Sno FROM SC;

【输出结果】

Sno

200215121

200215122

--指定DISTINCT，去掉表中重复的行
    SELECT DISTINCT Sno FROM SC ;

【输出结果】

Sno

200215121

200215122

（2）查询满足条件的元组

使用where

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>查询条件</td> 
   <td>谓词</td> 
  </tr> 
  <tr> 
   <td>比较</td> 
   <td>&lt;, &gt; , = , !=，&lt;&gt;</td> 
  </tr> 
  <tr> 
   <td>确定范围</td> 
   <td>betwwen&nbsp; and , not betwwen and</td> 
  </tr> 
  <tr> 
   <td>确定集合</td> 
   <td>in，not in</td> 
  </tr> 
  <tr> 
   <td>字符匹配</td> 
   <td>like , not like</td> 
  </tr> 
  <tr> 
   <td>空值</td> 
   <td>is null, is not null</td> 
  </tr> 
  <tr> 
   <td>多重条件（逻辑运算）</td> 
   <td>and, or, not</td> 
  </tr> 
 </tbody> 
</table>

例

比较

--查询计算机科学系全体学生的名字
    SELECT Sname FROM Student WHERE Sdept = 'CS';
    
    --查询所有年龄在20岁以下的学生姓名及年龄
    SELECT Sname,Sage FROM Student WHERE Sage < 20;
    
    --查询考试成绩有不及格的学生的学号
    SELECT DISTINCT Sno FROM SC WHERE Grade < 60;

确定范围

--查询年龄在20~23岁（包含20岁和23岁）之间的学生的姓名、系别、和年龄
    SELECT Sname,Sdept,Sage
    FROM Student
    WHERE Sage BETWWEN 20 AND 23 ;
    
    --查询年龄 不 在20~23岁（包含20岁和23岁）之间的学生的姓名、系别、和年龄
    SELECT Sname,Sdept,Sage
    FROM Student
    WHERE Sage NOT BETWWEN 20 AND 23 ;

确定集合

--in<值表> ,not in<值表>
    --查询信息系（IS）、数学系（MA）、计算机科学系（CS）学生的姓名和性别
    select Sname,Ssex
    from Student
    where Sdept in('IS','MA','CS')

字符匹配

--查询学号为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 
    FROM Student 
    WHERE Sname LIKE '欧阳_' ;
    
    --查询名字中第二字为“阳”字的学生的姓名和学号
    SELECT Sname,Sno 
    FROM Student
    WHERE LIKE '_阳%';
    
    
    --查询所有不姓刘的学生姓名、学号和性别
    SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE '刘%' ;

注意 使用转义字符 /

涉及空值的查询

--查询缺少成绩学生的学号和相应的课程号
    select Sno,Cno 
    from SC
    where Grade is null;

多重条件查询

and的优先级高于or

--查询信息系，数学系和计算机科学系学生的姓名和性别
    select Sname,Ssex
    from Student
    where Sdept in('IS','MA','CS')
    
    --或者改写为
    
    select Sname,Ssex
    from Student
    where Sdept = 'IS' or Sdept = 'MA' or Sdept = 'CS' ;

带有exists谓词的子查询

-- 查询所有选项修了1号课程的学生姓名
    
    # 使用连接运算
    select Sname
    from Student.SC
    where Student.Sno = SC.Sno AND SC.Cno = '1';
    
    # 使用嵌套查询（占用空间小，效率高）
    select Sname
    from Student
    where exists(
    select *
    from SC
    where Sno = Student.Sno ANF Cno = '1'
    );

【例】查询与"刘晨"在同一个系学习的学生。用带exists谓词的子查询

select Sno,Sname,Sdept
    from Student S1
    where exists (
    select *
    from Student S2
    where S2.Sdept = S1.Sdept AND S2.Sname = '刘晨'
    );

-- 查询选修了全部课程的学生姓名
    -- 等价于: 没有一门课是他不选修的
    
    select Sname from Student
    where not exists (
    select * from Course
    where not exists(
    select * from SC
    where Sno = Student.Sno AND Cno = Course.Cno
    )
    );

#### 基于派生表的查询 ####

子查询不仅可以出现在where子句中还可以出现在from子句中

# 找出每个学生超过他自己选修课平均成绩的课程号
    select Sno,Cno
    from SC,(select Sno,Avg(Grade) from SC group by (Sno))
    AS Avg_sc(avg_sno,avg_grade)
    where SC.Sno = Avg_sc.avg_sno AND SC.Grade >= Avg_sc.avg_grade;

### 定义视图 ###

#### 建立视图 ####

（1）语句格式

create view<视图名字>\[列名\]

【例】

-- 建立信息系学生的视图
    create view IS_Student as
    select Sno,Sname,Sage
    from Student where Sdept = 'IS';
    
    -- 建立信息学生的视图，并要求进行修改和插入操作时，仍需要保证该视图只有信息系的学生
    create view IS_Student as
    select Sno,Sname,Sage 
    from Student where Sdept = 'IS' WITH CHECK OPTION; # 检查信息完整性的约束
    
    -- 基于多个表的视图
    -- 建立信息系选修了1号课程的学生视图
    create view is_s1(Sno,Sname,Grade)
    as
    select Student.Sno,Sname,Grade
    from Student,SC
    where Sdept = 'IS' AND Student.Sno = SC.Sno AND SC.Cno = '1';
    
    -- 基于视图的视图
    -- 建立信息系选修了1号课程且成绩在90分以上的学生视图
    create view is_s2
    select Sno,Sname,Grade
    from is_s1 where Grade >= 90;
    
    -- 带有表达式的视图
    -- 定义一个反映学生出生年份的视图
    create view bt_s(Sno,Sname,Sbirth)
    as select Sno,Sname,2023-Sage from Student;
    
    -- 分组视图
    -- 将学生的学号及他的平均成绩定义为一个视图
    create view s_g(Sno,Gavg)
    as select Sno,AVG(Grade) from SC GROUP BY Sno;
    
    -- 不指定属性列
    -- 将Student表中所有男生记录定义为一个视图
    create view F_Student(F_Sno,name,sex,age,dept)
    as select * from Student where Ssex = '男';

#### 删除视图 ####

语法格式：

DROP view <视图名>\[CASCADE\]

其中CASCADE的意思时级联的意思，当删除此表时与其相关的都会被删去。

> 注意：删除基本表时，相应的视图需要用drop view 语句删除

【例】

（1）删除bt\_s: drop view bs\_s;

(2)删除视图is\_s1（因为这个图有和其他图级联）:drop view is\_s1 CASCADE;

#### 查询视图 ####

【例】

-- 在信息系学生的视图中找出年龄小于20岁的学生
    select Sno,Sage from IS_Student where Sage < 20;
    
    -- 查询选修了1号课程的信息系学生
    select IS_Student.Sno,Sname
    from IS_Student,SC
    where IS_Student.Sno = SC.Sno AND SC.Cno = '1';

#### 视图更新 ####

（插入、删除、修改）

【例】

update IS\_student set Sname = '刘晨' where Sno = '200215122';

转换后的语句：

update IS\_student set Sname = '刘晨' where Sno = '200215122' AND Sdept = 'IS' ;

【例】向信息系学生视图中插入一个新的学生记录：200215129，赵新，20岁:

insert into IS\_Student values ('200215129','赵新',20);

转为对基本表的更新：

insert into Student(Sno,Sname,Sage,Sdept)

values ('200215129','赵新',20,'IS');

【例】删除信息系学生视图IS\_Student中学号为200215129的记录