SQL基本语句

CREATE  TABLE <基本表名>

 (<列名><数据类型> [列级完整性约束条件]                  

 [,<列名><数据类型> [列级完整性约束条件]]

               ……

 [,表级完整性约束条件]）；

      如果完整性约束条件涉及到该表的多个属性列时，必须在表级定义该约束条件，否则既可以定义在列级，也可以定义在表级。

例1：建立职工关系模式

 emp(eno,ename,dno,sex,birthday)

 CREATE TABLE emp

 ( eno number(10),

     ename  varchar2(10),

     dno  varchar(6),

  sex  varchar(2),

  birthday  date)；

（3）约束条件

主键约束（PRIMARY KEY）：体现了实体完整性。要求某一列的值既不能为空，也不能重复。

外键约束（FOREIGN KEY）：体现参照完整性。外键的取值或者为空或者参考父表的主键。

属性约束：体现了用户定义的完整性。属性约束主要限制某一属性的取值范围。分为以下几类：

?非空约束(NOT NULL)：要求某一属性的值不允许为空值。 ?唯一约束（UNIQUE）：要求某一属性的值不允许重复。 ?检查约束（CHECK）：CHECK约束可以对某一个属性列的值加以限制。

列级约束:          

CREATE TABLE emp

 ( enonumber(6) primary key,

   enamevarchar2(10) unique,

 sex varchar2(3) not null,

 dnovarchar2(10)referencesdept(dno),

 salnumber(6) check(sal>1000 and

                      sal<=10000))；

表级约束:          

CREATE TABLE emp

 ( enonumber(6),

   enamevarchar2(10),

 sex varchar2(3)not null,

 dnovarchar2(10),

 salnumber(6),

 primary key(eno),

 unique(ename),

 foreign key(dno)references dept(dno),

 check(salbetween 1000 and 10000))；

      基本表的完整性约束可定义为两级：列级约束和表级约束。

      上述五种约束条件均可作为列级完整性约束条件，但非空约束不可以作为表级完整性约束条件，而其他四种也可以作为表级完整性约束条件。

   

例1：建立一个学生表Student，所有约束条件均为列级完整性约束。

CREATE TABLE Student

(Sno  CHAR(8) PRIMARY KEY,  /*主键约束*/

Sname CHAR(10)  UNIQUE,        /*唯一约束*/

Sex CHAR(3)  NOTNULL,           /*非空约束*/

Age INT  CHECK(Age>16),          /*检查约束*/

Dept VARCHAR(15)

 );

 

例2：建立一个课程表Course，所有约束条件均为列级完整性约束。

   CREATETABLE Course

   (Cno  CHAR(8) PRIMARY KEY,        /*主键约束*/

    CnameVARCHAR(10) NOT NULL, /*非空约束*/

    Tname  VARCHAR(10),

     Cpno  CHAR(8)  REFERENCES  Course(Cno),   

                                                               /*外键约束*/

     CreditNUMBER);

例3：建立一个学生选课表SC，所有约束条件均为表级完整性约束。

   CREATETABLE SC

  ( SnoCHAR(8),

    CnoCHAR(8),

    GradeNUMBER,

    PRIMARY KEY(Sno,Cno),               /*主键约束*/

   FOREIGN KEY(Sno)REFERENCES Student(Sno),      

                                                              /*外键约束*/

   FOREIGNKEY (Cno) REFERENCES Course(Cno)

                                                              /*外键约束*/

   );  

2.修改基本表结构

   修改基本表的结构包括增加、删除和修改表的属性列，默认值和约束条件。

语法：

ALTER TABLE<基本表名>

  [ADD<新列名> <数据类型>[列级完整性约束]]

  [DROPCOLUMN<列名>]

  [MODIFY<列名> <新的数据类型>]

  [ADDCONSTRAINT<完整性约束>]

  [DROPCONSTRAINT<完整性约束>];

例：向Student表中增加一个身高“Height”属性列，数据类型为INT。

   ALTER TABLE Student ADDHeight INT;

例：将Student表中的“Height”属性列的数据类型改为real。

  ALTER TABLE Student MODIFYHeight real;

例：删除Student表中新增加的“Height”属性列。

   ALTER TABLE Student DROP

                             COLUMNHeight;

增加完整性约束

例：给Student表中“Height”属性列增加一个CHECK约束，要求学生的身高要超过140厘米才行。

ALTER TABLE Student ADDCONSTRAINTChk1CHECK(Height>140);

 ★Chk1是Height属性列上新增加的CHECK约束的名字。

例：删除“Height”属性列上的Chk1约束。

ALTER TABLEStudent  DROP

                         CONSTRAINTChk1;

3.基本表的删除

语法:

     DROP TABLE <表名> [CASCADECONSTRAINTS];

    例：DROPTABLE emp；
      DROPTABLE dept；

 ★表一旦被删除，则无法恢复。如果表中有数据，则表结构连同数据，约束一起删除。

    只有表的创建者或者拥有DROPTABLE权限的用户才能删除表。

    如果两张表有主外键约束的话，先删从表（外键表），再删主表。

二、索引的定义和删除

v有两种重要的索引：聚簇索引（clustered index）和非聚簇索引（non-clustered index）。

1．创建索引

在SQL语言中，建立索引使用CREATEINDEX语句，其一般格式为：

CREATE[UNIQUE][CLUSTER]INDEX <索引名>

ON<基本表名>（<列名>[<次序>]，[，<列名>[<次序>]]…）;

v说明：

①UNIQUE：规定此索引为唯一性索引。每一个索引值只对应于表中唯一的记录。

②CLUSTER：规定此索引为聚簇索引。省略CLUSTER则表示创建的索引为非聚簇索引。

③<次序>：建立索引时指定列名的索引表是ASC（升序）或DESC（降序）。若不指定，默认为升序。

例：为Student，Course，SC三张表建立索引。其中Student表按学号Sno升序建唯一索引， Course表按课程号Cno降序建唯一索引，SC表按学号Sno升序和课程号Cno降序建唯一索引。

 

vCREATE UNIQUE INDEX  Index_stu

                                ON  Student(Sno ASC);

 

vCREATE UNIQUE INDEX  Index_Cou

                                ON    Course(Cno DESC);

vCREATE UNIQUE INDEX Index_SC

                                 ON    SC(SnoASC,Cno DESC); 

2．删除索引

v索引可以加快查询速度，但如果数据的增、删、改操作很频繁，系统就会花许多时间来维护索引，导致系统开销增加。

  删除索引的格式为：

        DROP INDEX<索引名>;

例：删除Course表的Index_Cou索引。

         DROP INDEXIndex_Cou;

1．插入单个元组

    向基本表中插入数据的语法格式如下：

INSERTINTO <基本表名> [(<列名1>，<列名2>，…，<列名n>)]VALUES（<列值1>，<列值2>，…，<列值n>） 

【例4-64】向学生表中指定的属性列插入数据

         INSERT INTOStudent1(Sno,Sname,Sex)

VALUES(‘05880112‘,‘王晓五‘,‘女‘);

2．插入多个元组

向基本表中插入数据的语法格式如下：

 INSERT INTO<基本表名>[(<列名1>，<列名2>， …，<列名n>)] 子查询;

【例4-65】如果已经创建了课程平均成绩记录表course_avg（cno,ave），其中ave表示每门课程的平均成绩，向course_avg表中插入每门课程的平均成绩。

INSERTINTOCourse_avg(Cno,Ave)

SELECTCno,AVG(Grade)

FROMSC

GROUPBY Cno;

4.4.2修改数据

如果表中的数据出现错误，可以利用UPDATE命令进行修改。

    UPDATE语句用以修改满足指定条件的元组信息。

UPDATE语句一般语法格式为：

UPDATE<基本表名>

SET<列名1>= <表达式>[，<列名2> = <表达式>]…

[WHERE<条件>] ;

 ★其中，UPDATE关键字用于定位修改哪一张表，SET关键字用于定位修改这张表中的哪些属性列，WHERE<条件>用于定位修改这些属性列当中的哪些行。

【例4-66】将java课程的学分改为5学分。

        UPDATECourse

        SET Credit=5

        WHERECname=‘java‘;

2．修改多个元组的值

【例4-67】将所有男同学的年龄增加2岁。

                  UPDATEStudent

                  SETAge=Age+2

                  WHERESex=‘男‘;

【例4-68】 将所有课程的学分减1。

                  UPDATECourse

                  SETCredit=Credit-1;

3．带子查询的更新

【例4-69】将所有选修java课程的学生成绩改为0分。

    

      UPDATE SC

      SET Grade=0

      WHERE‘java‘= (SELECT Cname

                                  FROM Course

                                  WHERE Course.Cno=SC.Cno);

4.4.3删除数据

DELETE语句的一般语法格式为：

      DELETE FROM<表名>[WHERE<条件>] ;

1．删除某一个元组的值

【例4-70】删除学号为“05880110”的学生记录。

                  DELETE  FROM  Student 

                  WHERESno=‘05880110‘;

2．删除多个元组的值

【例4-71】删除学号为“05880102”学生的选课记录。

             DELETE FROM SC

             WHERESno=‘05880102‘;

       每一个学生可能选修多门课程，所以DELETE语句会删除这个学生的多条选课记录。

【例4-72】删除所有学生的选课记录。

                  DELETE FROM SC;

3．带子查询的删除

【例4-73】删除张三同学的选课记录。

     DELETEFROMSC

     WHERE‘张三‘= ( SELECT Sname

                                 FROM  Student

                                 WHERE Student.Sno=SC.Sno);

▲SELECT 语句完整的句法：

    其基本格式为：

SELECT[ALL|DISTINCT] <目标列表达式>[，<目标列表达式>]…

FROM<表名或视图名>[，<表名或视图名>]…

[WHERE<条件表达式>]

[GROUPBY<列名1> [ HAVING <组条件表达式>]]

[ORDER BY<列名2> [ ASC|DESC]];

其中：

①SELECT子句说明要查询的数据。ALL表示筛选出数据库表中满足条件的所有记录，一般情况下省略不写。DISTINCT表示输出结果中无重复记录。

②FROM子句说明要查询的数据来源。。

③WHERE子句指定查询条件。

④GROUPBY子句表示在查询时，可以按照某个或某些字段分组汇总。HAVING子句必须跟随GROUPBY一起使用，表示在分组汇总时，可以根据组条件表达式筛选出满足条件的组记录。

⑤ORDERBY子句表示在显示结果时，按照指定字段进行排序。ASC表示升序，DESC表示降序，省略不写默认情况下是ASC。

（2）查询全部列

【例】查询全部课程的详细记录。

                SELECT  * 

                FROM    Course;

（4）指定别名来改变查询结果的列标题

         方法就是：在列名的后面加上一个空格或者是“as”，然后写上它的别名。在查询结果显示时就用别名代替列名了。

【例】查询全体学生的姓名、性别及其出生年份。

    SELECT Sname,Sex,2011-age  出生年份

   FROM Student;

2．选择表中若干行

    选择表中若干行，这就是选择运算。

（1）消除取值重复的行

【例】查询所有选修了课程的学生学号。

                SELECTSno

                FROM    SC;

        由于存在一名同学选修多门课程的情况，所以查

询的结果中包含了许多重复的行。如果想去掉重复的

行，必须指定DISTINCT关键字。

                SELECTDISTINCTSno

                FROMSC;

2．选择表中若干行

    选择表中若干行，这就是选择运算。

（1）消除取值重复的行

【例】查询所有选修了课程的学生学号。

                SELECTSno

                FROM    SC;

        由于存在一名同学选修多门课程的情况，所以查

询的结果中包含了许多重复的行。如果想去掉重复的

行，必须指定DISTINCT关键字。

                SELECTDISTINCTSno

                FROMSC;

    查询满足条件的元组是通过WHERE子句实现。在WHERE子句中常用的查询条件如表所示。

①比较大小

【例】查询数学系全体学生的姓名。

                 SELECTSname

                 FROM Student

                 WHERE Dept =‘数学系‘;

【例】查询年龄超过20岁的学生姓名及其年龄。

                    SELECTSname,Age

                    FROM Student

                    WHERE Age>20;

【例】查询考试成绩有不及格的学生的学号。

             SELECTDISTINCTSno

             FROM   SC

             WHERE Grade<60;

        语句中使用了DISTINCT关键字，目的是当某一个

学生有多门课程不及格时，他的学号只显示一次。

②确定范围（谓词BETWEEN AND）

【例】查询年龄在16至20岁（包括16岁和20岁）之间的学生姓名和年龄。

               SELECTSname,Age

               FROM Student

               WHEREAge BETWEEN 16 AND 20;

③确定集合（谓词IN）

【例】查询计算机系、日语系和管理系的学生姓名和性别。

SELECTSname,Sex

FROMStudent

WHEREDept IN(‘计算机系‘, ‘日语系‘, ‘管理系‘);

④字符匹配（谓词LIKE）

  

    谓词LIKE可以用来进行字符串的匹配。

v其中：

    % (百分号)代表任意长度(长度可以为0)的字符串。

     __(下横线)代表任意单个字符。

【例】查询所有姓张的学生姓名、年龄和系别名称。

   

                   SELECTSname,Age,Dept

                   FROMStudent

                   WHERESnameLIKE ‘张%‘;

     如果换成NOT LIKE，表示不姓张的同学。

【例】查询姓名中，第二个汉字是“七”的学生姓名和年龄。

              SELECTSname,Age

              FROM    Student

              WHERESnameLIKE ‘_七% ‘;

如果用户查询的匹配字符串本身就含有%或_，这时就要使用ESCAPE’<换码字符>’短语对通配符进行转义。

【例】查询以“jsp_”开头，且倒数第2个字符为g的课程的详细信息

SELECT   *

FROM     Course

WHERE  Cname LIKE ‘jsp\_%g_‘   ESCAPE ‘\‘;

⑤涉及空值的查询

【例】查询选修了课程，但没有成绩的学生学号和相应的课程号。

                    SELECTSno,Cno

                    FROM    SC

                    WHEREGrade IS NULL;

注意：这里“IS”不能用等号 “=” 代替。

【例】查询选修了课程，并且有成绩的学生学号和相应的课程号。

                      SELECTSno,Cno

   &n