数据库原理与应用之数据库标准语言SQL

SQL数据定义功能包括定义模式、基本表、视图、索引

• 创建表                                                                            注：

定义基本表                                                             primary key —— 指定关键字段          create table 表名                                                  unique ——  指定候选关键字段                                                                                         not null —— 制定该列不能为空行

{                                                                         

    列名  数据类型  （列级完整性约束），        

                            .                                                                                                                               

                     .                                                         

                 .                                                         

}

• 表的修改与删除

修改基本表

alter table 表名 (add<增加新列或新的完整性约束条件> 新列名 数据类型 （完整性约束）)    （drop<删除指定的完整性约束条件>）（drop column 列名）  （alter<用于修改列名和数据类型> 列名 数据类型）

删除基本表

drop table 表名

• 索引的定义与删除

建立索引

create （unique）（cluster） index 索引名 on 表名 （列名 次序），....

注："次序"指定索引值的摆列次序 升序ASC 降序DESC

       unique表示此索引的每一个索引值只对应唯一的数据记录。

       cluster（聚簇索引）的特点是基表中的数据也要按指定的聚簇属性值的升降序存放。

删除索引

drop index 索引名

• 数据查询

• select 语句格式

select查询语句格式

select （all<所有的>/distinct<去掉重复的>）目标列表表达式

from 表名或视图名

（where 条件表达式）

  （group by 列名 （having 条件表达式））

  （order by 列名 ,<用于按一个或多个属性列排序>（ASC/DESC））

• 单表查询

选择表中的若干个列

1.选择表中的若干个列

2.选择所有列

3.更改列标题

4.删除重复行（distinct）

5.限制返回的行数：top n(percent)    top n 说明返回几行；top n (percent) 说明n是百分数，指定返回的行数等于总行数的百分之几。

from子句

表名 As 别名 或 表名 别名 可为表和视图指定别名

where子句

特定运算符 is null 是当查询表中字段值为空时所用的运算符

select * from 数据库表名 where 要查询的字段名 is null；

模式匹配符包括 like，not like

（not) like '匹配串'

通配字符： 百分号%

                   下划线_

                  方括号【】：指定一个字符、字符串或范围，要求匹配对象为他们中的一个。

                   【^】:要求匹配对象为指定字符外的任一个字符

聚集函数

计数count（（distinct/all）*）或count（（distinct/all）列名）

计算总和sum（（distinct/all）列名）

计算平均值Ave（（distinct/all）列名）

求最大值max（（distinct/all）列名）

求最小值min（（distinct/all）列名）

例题：

查询学生总人数

select count （*） from studenta；

查询结果分组（group by）

group by子句的作用对象是查询的中间结果表，分组方式是按指定一列或多列的值分组，值相等的为一组。使用group by子句后，select 子句的列名列表只能出现分组属性和聚集属性。如未对查询结果分组，聚集函数将作用于整个查询结果，得到一个函数值；如果对查询结果分组，聚集函数将分别作用于每个组，得出来的是每组的函数值。

例题：

求每个部门的缴费人数。

select gid，count（gid）from jf group by gid

• 连接查询

通过连接运算符可实现多个表查询或连接查询，主要包括等值查询，非等值查询，连接查询，自身连接查询，外连接查询和复合条件连接查询。连接查询是关系数据库区别于其他类型数据库管理系统的一个标志。

在select语句的from子句或where子句中建立。

from子句的连接语句格式：

from 表名1 连接类型 表名2 （on 连接条件）

注：

连接类型分为内连接、外连接和交叉连接 

 
 
   内连接列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值，作用于两个不同的表。分为：

     等值连接——连接运算符为“=”，结果表中有重复列。

例：

查询每个学生的缴费情况

select studenta.*,jf.*

from studenta,jf

where studenta.stuaid=jf.stuaid

     不等值连接

     自然连接——它在连接条件中使用等于运算符比较连接列的列值，但它使用选择列表指出查询结果集合中所包含的列，并删除连接表中的重复列

      2.自身连接对同一个表连接，对同一个表取两个别名

例：

查询缴费金额相同而部门不同的部门号   

select distinct gid

from jf x, jf y

where x.gid≠y.gid and x.金额=y.金额

      3.外连接返回到查询结果集合中的不仅包含符合连接条件的行，而且包含左表（左外连接时）右表（右外连接时）或两个连接表（完全连接）中的所有数据行。

       左外连接：查询结果中不仅包含符合连接条件的行记录，而且包含左表中所有数据的行记录信息 。运算符是“=*”或“=+”

        右外连接：查询结果中不仅包含符合条件的行记录，而且包含右表中所有数据的行记录信息。运算符是“*=”或“+=”

       完全连接：  查询结果中不仅包含符合条件的行记录，而且包含左右表中所有数据的行记录信息。运算符是“**=”或“++=”                                                                                   例：         

查询每个学生的缴费情况，用左外连接完成

select ·······

from studenta left joinjf

on studenta.stuaid = jf.stuaid 

       4.复合条件连接 在where子句中包含多个连接条件。

                5.多表连接                      

• 嵌套查询

在进行嵌套查询时，内层查询不能使用order by子句，但最外层可能使用，所以，order by只能用在最终查询结果的排序。

select 目标表达式

from 表或视图名

where（表达式）

（select 目标表达式

from 表或视图名

（group by 分组条件

   having 比较运算符））

1. 简单子查询（单值比较）
2. 带有（not）in谓词的子查询
3. 带有any（some）或all谓词的子查询
4. 带有（not）exists谓词的子查询

• 集合查询

将不同查询返回的不同数据集合结果合并成为一个数据集合

1.并操作（union）

要求集合A或集合B列数必须相同，对应项的数据类型也必须相同

union返回两个查询结果并集，并去掉相同的部分

union all 返回两个查询结果之并集，但不去掉重复部分

select 字段名 from 表1 union/union all select 字段名 from 表2

例题：：

查询汉族的学生和年龄大于20岁的学生

select * from studenta  where stumz ='汉族'

union

select * from studenta where stuage >20;

2.交操作（intersect）

intersect 只返回两个查询中都有的行

select 字段名 from 表1 intersect select 字段名 from 表2

3.差操作minus

属于集合A且不属于集合B的元素总和就是二者的差集。

select 字段名 from 表1 minus select 字段名 from 表2

• 数据操作语言

包括插入语句（insert）、更新语句（update）和删除语句（delete）

• 插入语句

1.插入单个元组

insert

into 表名 （属性列.....）

values （常数值1......）

功能：将新纪录行插入指定表中。其中：属性列X与常数列X是一一对应的关系，并且数据类型一致；若没有指定属性列，表示要插入的是一条完整的元祖，且属性列属性与表定义的顺序一致；若指定部分属性列，其余没有指定属性的列会自动取空值。

例:

insert

into studenta

vulues(1111,'2009007001','王梅'，‘女’，18，‘汉族’，‘山东省’，‘英语’......)

2.插入子查询结果

insert into 表名 （属性列1......）子查询；

将子查询结果批量数据插入指定表中。子查询结果中属性列数目与into子句的属性列数目相同。

例：

对每个缴费情况进行求和，并把结果存入数据表

create table stusum (stuaid int , stusum float (8,2));

insert into stusum (stuaid,stusum);

select stuaid sum (jf) from studenta group by stuaid;

插入的元组必须遵守已定义的完整性规则（实体完整性、参照完整性、用户自定义完整性）：

•    对于有NOT NULL 约束的属性列是否提供了非空值
• 对于有UNIQUE约束的属性列是否提供了非重复值
• 对于有值域约束的属性列所提供的属性值是否在值域范围内
•  更新语句       

             修改数据表中的某一个记录的值或修改多个记录的值                                                                                                                      update 表名 set 列名=表达式..... （where 条件）                                                                                                          1.修改某一个元组的值

例：

 将姓名为“小强”的学生记录的新生ID号改为1020，民族改为满族

update studenta set stuaid =1020,stumz='满族'where stuname='小强'；

2. 修改多个元组的值

例：

将所有学生的年龄增加5岁

update     studenta set stuage=stuage+5;

3.带子查询的修改语句

例：

将所有学生上交到教材管理部门的钱款清零

update jf

set jf=0

where gid=(select  gid from group where gname='教材管理')；    

• 删除语句

      delete from 表名 （where 条件）

1.删除某一记录的值

delete

from group

where gid='0002'

2.删除多条记录的值

delete from jf

3.带子查询的删除

delete

from jf

where  gid=(select gid from group where gname='教材管理')；

•   视图                                                                                                                   视图其数据本身比部分不存在于数据库中，是一个虚表。是从若干个基本表或视图导出来的表 
• 生成视图      

   create view 视图名（列名....）

   as

   子查询

（with check option）

例：

创建来自辽宁省学生的信息档案

create view LN_stusheng1

as

select stuname,stusheng

from studenta

where stusheng='辽宁省'

• 更新视图

限制：

视图定义时，视图的数据源来自两个基本表或视图，则此视图不允许更新操作。

视图定义时，视图的字段是通过计算得到的值，

视图定义时，使用了group by 和 having子句

视图定义时，使用了distinct关键字

视图定义时，含有嵌套查询，涉及多个表或视图

视图定义时，引用了不可更新视图

例：

（修改）    update

LN_Stusheng1

set stuname ='刘晓东'

where stuname='刘海'

 （插入）  insert

into LN_Stusheng2

values(....)

    (删除)delete 

from LN_Stusheng3

where

stuname='刘海'

• 删除视图

drop view 视图名