非常不错的SQL语句学习手册实例版第1/3页

表操作 例1对于表的教学管理数据库中的表STUDENTS,可以定义如下:复制代码
代码如下:CreateTABLESTUDENTS(SNONUMERIC(6,0)NOTNULL SNAMECHAR(8)NOTNULL
AGENUMERIC(3,0) SEXCHAR(2) BPLACECHAR(20)
PRIMARYKEY(SNO))例2对于表的教学管理数据库中的表ENROLLS,可以定义如下:复制代码
代码如下:CreateTABLEENROLLS(SNONUMERIC(6,0)NOTNULL CNOCHAR(4)NOTNULL
GRADEINT PRIMARYKEY(SNO,CNO) FOREIGNKEY(SNO)REFERENCESSTUDENTS(SNO)
FOREIGNKEY(CNO)REFERENCESCOURSES(CNO)
CHECK((GRADEISNULL)or(GRADEBETWEEN0AND100)))例3根据表的STUDENTS表,建立一个只包含学号、姓名、年龄的女学生表。复制代码
代码如下:CreateTABLEGIRLASSelectSNO,SNAME,AGE
FROMSTUDENTSWhereSEX=’女’;例4删除教师表TEACHER。 DropTABLETEACHER
例5在教师表中增加住址列。 AlterTABLETEACHERSADD(ADDRCHAR(50))
例6把STUDENTS表中的BPLACE列删除,并且把引用BPLACE列的所有视图和约束也一起删除。
AlterTABLESTUDENTSDropBPLACECASCADE 例7补充定义ENROLLS表的主关键字。
AlterTABLEENROLLSADDPRIMARYKEY(SNO,CNO); 视图操作
例9建立一个只包括教师号、姓名和年龄的视图FACULTY。(在视图定义中不能包含orDERBY子句)
CreateVIEWFACULTYASSelectTNO,TNAME,AGEFROMTEACHERS
例10从学生表、课程表和选课表中产生一个视图GRADE_TABLE,它包括学生姓名、课程名和成绩。
CreateVIEWGRADE_TABLEASSelectSNAME,CNAME,GRADE
FROMSTUDENTS,COURSES,ENROLLS WhereSTUDENTS.SNO=ENROLLS.SNOAND
COURSES.CNO=ENROLLS.CNO 例11删除视图GRADE_TABLE
DropVIEWGRADE_TABLERESTRICT 索引操作 例12在学生表中按学号建立索引。
CreateUNIQUEINDEXSTONSTUDENTS(SNO,ASC) 例13删除按学号所建立的索引。
DropINDEXST 数据库模式操作
例14创建一个简易教学数据库的数据库模式TEACHING_DB,属主为ZHANG。
CreateSCHEMATEACHING_DBAUTHRIZATIONZHANG
例15删除简易教学数据库模式TEACHING_DB。
(1)选用CASCADE,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除。
(2)选用RESTRICT,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除。
DropSCHEMATEACHING_DBCASCADE 123阅读全文

图片 1 

范式

表操作  

第一范式(1NF)无重复的列

 

       
所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。

例 1  对于表的教学管理数据库中的表 STUDENTS ,可以定义如下:

第二范式(2NF)属性完全依赖于主键

   CREATE  TABLE  STUDENTS

       
第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。

   (SNO      NUMERIC (6, 0) NOT NULL

实例:

   SNAME    CHAR (8) NOT NULL

选课关系 SCI(SNO,CNO,GRADE,CREDIT),其中SNO为学号,
CNO为课程号,GRADEGE 为成绩,CREDIT 为学分。
关键字为组合关键字(SNO,CNO)

   AGE      NUMERIC(3,0)

在应用中使用以上关系模式有以下问题:
a.数据冗余,假设同一门课由40个学生选修,学分就
重复40次。
b.更新异常,若调整了某课程的学分,相应的元组CREDIT值都要更新,有可能会出现同一门课学分不同。
c.插入异常,如计划开新课,由于没人选修,没有学号关键字,只能等有人选修才能把课程和学分存入。
d.删除异常,若学生已经结业,从当前数据库删除选修记录。某些门课程新生尚未选修,则此门课程及学分记录无法保存。
原因:

   SEX      CHAR(2)

非关键字属性CREDIT仅函数依赖于CNO,也就是CREDIT部分依赖组合关键字(SNO,CNO)而不是完全依赖。
解决方法:

   BPLACE  CHAR(20)

分成两个关系模式
SC1(SNO,CNO,GRADE),C2(CNO,CREDIT)。新关系包括两个关系模式,它们之间通过SC1中的外关键字CNO相联系,需要时再进行自然联接,恢复了原来的关系

   PRIMARY KEY(SNO))

第三范式(3NF)属性不依赖于其它非主属性

例 2  对于表的教学管理数据库中的表 ENROLLS ,可以定义如下:

实例:

        CREATE  TABLE  ENROLLS

学生表Student(学号,姓名,
年龄,性别,系别,系办地址、系办电话),关键字为单一关键字”学号”

        (SNO      NUMERIC(6,0)  NOT NULL

因为存在如下决定关系:
(学号)→ (姓名,
年龄,性别,系别,系办地址、系办电话)
但是还存在下面的决定关系
(学号) → (所在学院)→(学院地点,
学院电话)
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖,这样会存在数据冗余,
根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

        CNO     CHAR(4)  NOT NULL

学生:(学号, 姓名, 年龄, 性别,系别);
系别:(系别, 系办地址、系办电话)。

        GRADE   INT

索引

        PRIMARY KEY(SNO,CNO)

       
索引是一种特殊的文件,包含了对数据表中所有记录的引用指针,如果想知道对某个特定话题的讨论都在书中的哪些地方出现过,索引可以让读者无需把书从头到尾翻一遍就能把他们查出来。当然,索引也是有缺陷的,因为每修改一个数据索引就要刷新一次,(有些sql命令里面有一个delay_key_write,可以暂停插入数据时对索引的刷新),插入效率会受影响;另外一个索引会在硬盘上占据相当大的空间。

        FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)

普通索引:唯一任务就是加快对数据的访问

        FOREIGN KEY(CNO) REFERENCES COURSES(CNO)

唯一索引:普通索引是允许数据重复的,如果确定了某列数据不会重复,则可以创建唯一索引,这样有两个好处,索引更有效率;插入新数据的时候如果重复了,mysql会拒绝插入新纪录

        CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)))

主索引:这个就是为主键创建的索引,应该是默认都要创建的

例 3  根据表的 STUDENTS
表,建立一个只包含学号、姓名、年龄的女学生表。

全文索引:文本字段上的普通索引只能加快对出现在字段内最前面的字符串进行的检索操作,如果字段里存放的是由几个或者多个单词构成的大段文字,普通索引就不行了,这种场合用全文索引比较合适

        CREATE TABLE GIRL

       
只有当数据库里已经有足够多的数据时,测试索引才有实际参考价值,因为不管有没有索引,数据库在执行第一条查询命令之后就被加载到内存中了,后面查起来都非常快,因此只有当数据库里记录超过1000条、数据总量也超过内存的总量时,测试结果才有意义。

        AS SELECT SNO, SNAME, AGE

视图

        FROM STUDENTS

       
视图使得人们可以为一个或者多个数据表定义一个特殊的表现形式。视图在表现行为上与表没有差别,可以select查询,也可以用insert,update,delete来修改数据。

        WHERE SEX=’ 女 ‘;

       
一般使用视图的理由有两个,一个是安全,例如一个表中包含了员工的个人资料,那像电话姓名这些是所有人都可以查询的,但是像薪水这些东西就只有特定的人能查询了,因此最好的办法就是将所有人都可以访问的数据部分创建为一个视图,供别人查询。另一个是方便,视图使用起来很方便,不用输入复制的命令

 

     
在视图里修改数据:能不能修改某个视图中的数据取决于视图的select命令,可刷新的视图需要满足以下几个条件:

例 4  删除教师表 TEACHER 。

(1)当初定义的select中不得包含group
by、distinct、limit、union或having等子命令

        DROP  TABLE  TEACHER

(2)如果视图中的数据来自一个以上的表,那它总是不可刷新的

例 5  在教师表中增加住址列。

(3)视图中应包含主键索引,唯一索引,外间约束条件所涉及到的所有数据列

       ALTER TABLE TEACHERS

事务

       ADD (ADDR CHAR(50))

        为什么要使用事务操作?
有助于提高数据库系统的运行效率和安全性,举个例子,要从一个人的账户装100美元到另一个的账户中,需要两步操作,更新第一个人的账户余额,更新第二个人的账户余额,如果第一个操作执行完以后系统出了问题,那后果就严重了。如果有事务的话,如果成功了,那就最好,如果在执行到一半的时候失败了,那执行了的部分也会被撤销。事务也可以保证同一批数据不会被两位用户同时修改,并发控制。

例 6  把 STUDENTS 表中的 BPLACE 列删除,并且把引用 BPLACE
列的所有视图和约束也一起删除。

ACID原则:

        ALTER TABLE STUDENTS

原子性:这意味事务就想原子那样是不可分割的

        DROP BPLACE CASCADE

稳定性:这意味着事务执行完毕后数据库必须处于一个稳定的状态

例 7  补充定义 ENROLLS 表的主关键字。

隔离性:多个事务可以独立运行,不受彼此干扰,如果有一个事务提交了,所有受到影响的事务将自动撤销并返回错误信息,这样便于用户进行操作处理

       ALTER TABLE ENROLLS

可靠性:事务一定能够经受住软、硬件或者其他意外故障,故障消除后能够继续执行

       ADD PRIMARY KEY (SNO,CNO) ;

     
 强调一下:只有InnoDB数据表支持数据,MyISAM数据表不支持事务

 

事务与锁定:

视图操作(虚表)

共享锁:select …lock in share mode
 ,这确保了事务过程中读取的数据记录不会被其他用户正在修改和删除操作

 

排它锁:select …. for update
,排它锁不禁止其他客户使用普通的select来读取锁定的数据记录,但是会禁止其他共享锁和排它锁

例 9  建立一个只包括教师号、姓名和年龄的视图 FACULTY 。 (
在视图定义中不能包含 ORDER BY 子句 )

存储过程(stored procedure)

        CREATE VIEW FACULTY

       
由mysql服务器直接存储和执行的定制过程或者函数。几个特点:更快的速度、避免代码冗余、提高数据库的安全性

        AS SELECT TNO, TNAME, AGE

触发器

        FROM TEACHERS

     
 在insert、update、delete命令之前或者之后对sql命令或者sp的自动调用。mysql对触发器有许多限制,比如:

例 10  从学生表、课程表和选课表中产生一个视图 GRADE_TABLE ,
它包括学生姓名、课程名和成绩。

(1)触发器代码里无法访问任何数据表,连触发器为之定义的那个数据表也不能访问,类似sp函数的情况,select、insert、update等命令不允许在触发器代码里出现

        CREATE VIEW GRADE_TABLE

(2)触发器不能用call命令调用一个sp

        AS SELECT  SNAME,CNAME,GRADE

(3)触发器中不能调用事务命令

        FROM  STUDENTS,COURSES,ENROLLS

Mysql服务器优化内存管理:

        WHERE  STUDENTS.SNO = ENROLLS.SNO AND

       调整/etc/my.cnf or
 my.ini文件中的一些参数:

        COURSES.CNO=ENROLLS.CNO

key_buffer_size(默认8MB)
索引缓冲区长度,这个越大,对数据表里有索引的数据列访问速度就会越快

例 11  删除视图 GRADE_TABLE

table_cache(默认64B)可以同时打开数据表的个数

        DROP VIEW GRADE_TABLE RESTRICT

sort_buffer(默认为2MB),排序缓冲区长度,如果没有索引,带order
by或者group
bye字句的select命令将使用这个缓冲区对数据进行排序,如果这个太小,就需要用到一个临时文件,这可就慢多了。

 

        另外还有几个,这里就不具体再说明了。

索引操作

 

例 12  在学生表中按学号建立索引。

        CREATE  UNIQUE  INDEX  ST

        ON STUDENTS (SNO,ASC)

例 13  删除按学号所建立的索引。

        DROP INDEX ST

 

数据库模式操作

 

例 14  创建一个简易教学数据库的数据库模式   TEACHING_DB ,属主为
ZHANG 。

        CREATE SCHEMA TEACHING_DB  AUTHRIZATION  ZHANG

例 15  删除简易教学数据库模式 TEACHING_DB 。(( 1 )选用 CASCADE
,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除。(
2 )选用 RESTRICT
,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除。)

        DROP SCHEMA TEACHING_DB CASCADE

单表操作

 

例 16  找出 3 个学分的课程号和课程名。

         SELECT CNO, CNAME

         FROM   COURSES

         WHERE   CREDIT = 3

例 17  查询年龄大于 22 岁的学生情况。

         SELECT  *

         FROM   STUDENTS

         WHERE  AGE > 22

例 18   找出籍贯为河北的男生的姓名和年龄。

         SELECT SNAME, AGE

         FROM   STUDENTS

         WHERE   BPLACE = ‘ 河北 ‘  AND  SEX = ‘ 男 ‘

例 19  找出年龄在 20 ~ 23
岁之间的学生的学号、姓名和年龄,并按年龄升序排序。 (ASC (升序)或 DESC
(降序)声明排序的方式,缺省为升序。 )

         SELECT SNO, SNAME, AGE

         FROM   STUDENTS

         WHERE  AGE BETWEEN 20 AND 23

         ORDER  BY  AGE

例 20  找出年龄小于 23
岁、籍贯是湖南或湖北的学生的姓名和性别。(条件比较运算符=、<
和逻辑运算符 AND
(与),此外还可以使用的运算符有:>(大于)、>=(大于等于)、<=(小于等于)、<>(不等于)、
NOT (非)、 OR (或)等。

谓词 LIKE 只能与字符串联用,常常是 “ <列名>   LIKE  pattern”
的格式。特殊字符 “_” 和 “%” 作为通配符。

谓词 IN
表示指定的属性应与后面的集合(括号中的值集或某个查询子句的结果)中的某个值相匹配,实际上是一系列的
OR (或)的缩写。谓词 NOT IN
表示指定的属性不与后面的集合中的某个值相匹配。

谓词 BETWEEN 是 “ 包含于 … 之中 ” 的意思。)

        SELECT SNAME, SEX

        FROM   STUDENTS

        WHERE  AGE < 23  AND  BPLACE  LIKE’ 湖% ‘

        或

        SELECT SNAME, SEX

        FROM   STUDENTS

        WHERE  AGE < 23  AND  BPLACE  IN  ( ‘ 湖南 ‘ , ‘ 湖北 ‘ )

例 22  找出学生表中籍贯是空值的学生的姓名和性别。(在 SQL
中不能使用条件:<列名>= NULL 。在 SQL
中只有一个特殊的查询条件允许查询 NULL 值:)

       SELECT SNAME, SEX

       FROM   STUDENTS

       WHERE  BPLACE IS NULL

 

多表操作