文档章节

SQL语句实例学习汇总

深圳大道
 深圳大道
发布于 2016/12/29 15:38
字数 2706
阅读 3
收藏 0

 

表操作  

 

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

   CREATE  TABLE  STUDENTS

   (SNO      NUMERIC (6, 0) NOT NULL

   SNAME    CHAR (8) NOT NULL

   AGE      NUMERIC(3,0)

   SEX      CHAR(2)

   BPLACE  CHAR(20)

   PRIMARY KEY(SNO))

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

        CREATE  TABLE  ENROLLS

        (SNO      NUMERIC(6,0)  NOT NULL

        CNO     CHAR(4)  NOT NULL

        GRADE   INT

        PRIMARY KEY(SNO,CNO)

        FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)

        FOREIGN KEY(CNO) REFERENCES COURSES(CNO)

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

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

        CREATE TABLE GIRL

        AS SELECT SNO, SNAME, AGE

        FROM STUDENTS

        WHERE SEX=' 女 ';


 

例 4  删除教师表 TEACHER 。

        DROP  TABLE  TEACHER

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

       ALTER TABLE TEACHERS

       ADD (ADDR CHAR(50))

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

        ALTER TABLE STUDENTS

        DROP BPLACE CASCADE

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

       ALTER TABLE ENROLLS

       ADD PRIMARY KEY (SNO,CNO) ;

 

视图操作(虚表)

 

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

        CREATE VIEW FACULTY

        AS SELECT TNO, TNAME, AGE

        FROM TEACHERS

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

        CREATE VIEW GRADE_TABLE

        AS SELECT  SNAME,CNAME,GRADE

        FROM  STUDENTS,COURSES,ENROLLS

        WHERE  STUDENTS.SNO = ENROLLS.SNO AND

        COURSES.CNO=ENROLLS.CNO

例 11  删除视图 GRADE_TABLE

        DROP VIEW GRADE_TABLE RESTRICT

 

索引操作

 

例 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


 

多表操作

 

例 23  找出成绩为 95 分的学生的姓名。(子查询)

        SELECT SNAME

        FROM   STUDENTS

        WHERE   SNO =

              (SELECT SNO

               FROM   ENROLLS

               WHERE  GRADE = 95)

例 24  找出成绩在 90 分以上的学生的姓名。

        SELECT  SNAME

        FROM   STUDENTS

        WHERE  SNO IN

                (SELECT SNO

                FROM ENROLLS

                WHERE GRADE > 90)

        或

        SELECT  SNAME

        FROM   STUDENTS

        WHERE  SNO = ANY

                (SELECT SNO

                FROM ENROLLS

                WHERE GRADE > 90)

例 25  查询全部学生的学生名和所学课程号及成绩。(连接查询)

        SELECT  SNAME, CNO, GRADE

        FROM   STUDENTS, ENROLLS

        WHERE  STUDENTS.SNO = ENROLLS.SNO

例 26  找出籍贯为山西或河北,成绩为 90 分以上的学生的姓名、籍贯和成绩。(当构造多表连接查询命令时,必须遵循两条规则。第一,连接条件数正好比表数少 1 (若有三个表,就有两个连接条件 ) ;第二,若一个表中的主关键字是由多个列组成,则对此主关键字中的每一个列都要有一个连接条件(也有少数例外情况))

        SELECT  SNAME, BPLACE, GRADE

        FROM   STUDENTS, ENROLLS

        WHERE  BPLACE IN (‘ 山西 ’ , ‘ 河北 ’)  AND  GRADE >= 90  AND   STUDENTS.SNO=ENROLLS.SNO

例 28  查出课程成绩在 80 分以上的女学生的姓名、课程名和成绩。( FROM 子句中的子查询)

        SELECT  SNAME,CNAME, GRADE

        FROM   (SELECT SNAME, CNAME , GRADE

                        FROM  STUDENTS, ENROLLS,COURSES

                        WHERE  SEX = ' 女 ')

        AS TEMP (SNAME, CNAME,GRADE)

        WHERE  GRADE > 80

 

 

 

 

表达式与函数的使用

 

例 29  查询各课程的学时数。(算术表达式由算术运算符+、-、 * 、/与列名或数值常量所组成。)

        SELECT  CNAME,COURSE_TIME = CREDIT*16

        FROM   COURSES

例 30  找出教师的最小年龄。(内部函数: SQL 标准中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函数,称之为聚集函数( Set Function )。 COUNT 函数的结果是该列统计值的总数目, SUM 函数求该列统计值之和, AVG 函数求该列统计值之平均值, MAX 函数求该列最大值, MIN 函数求该列最小值。)

        SELECT  MIN(AGE)

        FROM   TEACHERS

例 31  统计年龄小于等于 22 岁的学生人数。(统计)

        SELECT  COUNT(*)

        FROM   STUDENTS

        WHERE  AGE < = 22

例 32  找出学生的平均成绩和所学课程门数。

        SELECT  SNO, AVG(GRADE), COURSES = COUNT(*)

        FROM   ENROLLS

        GROUP  BY SNO

 

 

例 34  找出年龄超过平均年龄的学生姓名。

SELECT SNAME

FROM STUDENTS

WHERE AGE >

      (SELECT  AVG(AGE)

        FROM   STUDENTS)


 

例 35  找出各课程的平均成绩,按课程号分组,且只选择学生超过 3 人的课程的成绩。( GROUP BY 与 HAVING

        GROUP BY 子句把一个表按某一指定列(或一些列)上的值相等的原则分组,然后再对每组数据进行规定的操作。

        GROUP BY 子句总是跟在 WHERE 子句后面,当 WHERE 子句缺省时,它跟在 FROM 子句后面。

        HAVING 子句常用于在计算出聚集之后对行的查询进行控制。)

         SELECT CNO, AVG(GRADE), STUDENTS = COUNT(*)

         FROM ENROLLS

         GROUP BY CNO

         HAVING COUNT(*) >= 3

 

相关子查询

 

例 37  查询没有选任何课程的学生的学号和姓名。(当一个子查询涉及到一个来自外部查询的列时,称为相关子查询( Correlated Subquery) 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ,以及 ALL 、 ANY ( SOME )等。)

        SELECT SNO, SNAME

        FROM   STUDENTS

        WHERE  NOT EXISTS

              (SELECT *

              FROM ENROLLS

              WHERE ENROLLS.SNO=STUDENTS.SNO)

例 38   查询哪些课程只有男生选读。

        SELECT DISTINCT CNAME

        FROM   COURSES C

        WHERE ' 男 ' = ALL

               (SELECT SEX

                FROM  ENROLLS , STUDENTS

                WHERE ENROLLS.SNO=STUDENTS.SNO AND

                      ENROLLS.CNO=C.CNO)

例 39  要求给出一张学生、籍贯列表,该表中的学生的籍贯省份,也是其他一些学生的籍贯省份。

        SELECT  SNAME, BPLACE

        FROM   STUDENTS A

        WHERE  EXISTS

               (SELECT *

                 FROM STUDENTS B

                 WHERE A.BPLACE=B.BPLACE AND

                       A.SNO < > B.SNO)


 

 

例 40  找出选修了全部课程的学生的姓名。

        本查询可以改为:查询这样一些学生,没有一门课程是他不选修的。

        SELECT  SNAME

        FROM   STUDENTS

        WHERE  NOT EXISTS

               (SELECT *

                FROM COURSES

                WHERE  NOT EXISTS

                        (SELECT *

                         FROM ENROLLS

                         WHERE ENROLLS.SNO = STUDENTS.SNO

                             AND ENROLLS.CNO = COURSES.CNO))

关系代数运算

 

例 41  设有某商场工作人员的两张表:营业员表 SP_SUBORD 和营销经理表 SP_MGR ,其关系数据模式如下:

        SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)

        SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)

        其中,属性 SALPERS_ID 为工作人员的编号 , SALPERS_NAME 为工作人员的姓名 , MANAGER_ID 为所在部门经理的编号 , OFFICE 为工作地点。

若查询全部商场工作人员,可以用下面的 SQL 语句:

        (SELECT * FROM SP_SUBORD)

        UNION

        (SELECT * FROM SP_MGR)

        或等价地用下面的 SQL 语句:

        SELECT *

        FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)

( 2 ) INTERSECT

         (SELECT * FROM SP_SUBORD)

         INTERSECT

         (SELECT * FROM SP_MGR)

        或等价地用下面的 SQL 语句:

        SELECT *

        FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)

        或用带 ALL 的 SQL 语句:

        (SELECT * FROM SP_SUBORD)

        INTERSECT ALL

        (SELECT * FROM SP_MGR)

        或

        SELECT *

        FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)

( 3 ) EXCEPT

        (SELECT * FROM SP_MGR)

        EXCEPT

        (SELECT * FROM SP_SUBORD)

        或等价地用下面的 SQL 语句:

        SELECT *

        FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)

        或用带 ALL 的 SQL 语句:

        (SELECT * FROM SP_MGR)

        EXCEPT  ALL

        (SELECT * FROM SP_SUBORD)

例 42  查询籍贯为四川、课程成绩在 80 分以上的学生信息及其成绩。(自然连接)

        (SELECT * FROM STUDENTS

         WHERE BPLACE=‘ 四川 ’)

        NATURAL JOIN

        (SELECT * FROM ENROLLS

         WHERE GRADE >=80)

例3.43          列出全部教师的姓名及其任课的课程号、班级。

(外连接与外部并外连接允许在结果表中保留非匹配元组,空缺部分填以 NULL 。外连接的作用是在做连接操作时避免丢失信息。

        外连接有 3 类:

( 1 )左外连接( Left Outer Join )。连接运算谓词为 LEFT [OUTER] JOIN ,其结果表中保留左关系的所有元组。

( 2 )右外连接( Right Outer Join )。连接运算谓词为 RIGHT [OUTER] JOIN ,其结果表中保留右关系的所有元组。

( 3 )全外连接( Full Outer Join )。连接运算谓词为 FULL [OUTER] JOIN ,其结果表中保留左右两关系的所有元组。)

          SELECT TNAME, CNO, CLASS

          FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)

 

SQL 的数据操纵

 

例 44  把教师李映雪的记录加入到教师表 TEACHERS 中。(插入)

        INSERT INTO TEACHERS

        VALUES(1476 , ' 李映雪 ' , 44 , ' 副教授 ')

例 45  成绩优秀的学生将留下当教师。

        INSERT INTO TEACHERS (TNO , TNAME)

        SELECT DISTINCT SNO , SNAME

        FROM   STUDENTS , ENROLLS

        WHERE STUDENTS.SNO = ENROLLS.SNO AND GRADE >= 90

例 47  把所有学生的年龄增加一岁。(修改)

        UPDATE STUDENTS

        SET AGE = AGE+1

例 48  学生张春明在数据库课考试中作弊,该课成绩应作零分计。

        UPDATE ENROLLS

        SET GRADE = 0

        WHERE CNO = 'C1' AND

             ' 张春明 ' =

             (SELECT SNAME

             FROM STUDENTS

             WHERE STUDENTS.SNO=ENROLLS.SNO)

例 49  从教师表中删除年龄已到 60 岁的退休教师的数据。(删除)

        DELETE FROM TEACHERS

        WHERE  AGE >= 60

 

SQL 的数据控制

 

例 50  授予 LILI 有对表 STUDENTS 的查询权。(表/视图特权的授予

        一个 SQL 特权允许一个被授权者在给定的数据库对象上进行特定的操作。授权操作的数据库对象包括:表 / 视图、列、域等。授权的操作包括: INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 、 UNDER 、 USAGE 、 EXECUTE 等。其中 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 有对表做相应操作的权限,故称为表特权。)

        GRANT SELECT ON STUDENTS

        TO LILI

        WITH GRANT OPTION

例 51  取消 LILI 的存取 STUDENTS 表的特权。

        REVOKE ALL

        ON STUDENTS

        FROM LILI CASCADE

  

本文转载自:http://blog.csdn.net/smartsmile2012/article/details/7919982

深圳大道
粉丝 3
博文 877
码字总数 0
作品 0
深圳
架构师
私信 提问
MSSQL · 实现分析 · SQL Server实现审计日志的方案探索

摘要 这篇文章介绍四种实现MSSQL Server审计日志功能的方法探索,即解析数据库事务日志、SQL Profiler、SQL Audit以及Extended Event。详细介绍了这四种方法的具体实现,以及通过优缺点的对比...

阿里云RDS-数据库内核组
2017/06/07
0
0
Oracle实现分页查询的SQL语法汇总

本文实例汇总了Oracle实现分页查询的SQL语法,整理给大家供大家参考之用,详情如下: 1.无ORDER BY排序的写法。(效率最高) 经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据...

Sheamus
2015/02/11
102
0
数据库实例性能调优利器-Performance Insights最佳实践

Performance Insights是什么 阿里云RDS Performance Insights是RDS CloudDBA产品一项专注于用户数据库实例性能调优、负载监控和关联分析的利器,以简单直观的方式帮助用户迅速评估数据库负载...

阿里云官方博客
09/11
16
0
RDS CloudDBA - Performance Insights最佳实践

title: RDS CloudDBA - Performance Insights最佳实践 author: 风移 Performance Insights是什么 阿里云RDS Performance Insights是RDS CloudDBA产品一项专注于用户数据库实例性能调优、负载...

风移
07/08
0
0
Oracle group by 用法实例详解

1. Group by的语法 Select [filed1,fild2,]聚合函数(filed), [Grouping(filed),] [Grouping_id(filed1,filed2,…)] From tablename Where condition [Group by {rollup|cube}(filed,filed......

rudy_gao
2013/10/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

PostgreSQL 11.3 locking

rudi
今天
5
0
Mybatis Plus sql注入器

一、继承AbstractMethod /** * @author beth * @data 2019-10-23 20:39 */public class DeleteAllMethod extends AbstractMethod { @Override public MappedStatement injectMap......

一个yuanbeth
今天
10
1
一次写shell脚本的经历记录——特殊字符惹的祸

本文首发于微信公众号“我的小碗汤”,扫码文末二维码即可关注,欢迎一起交流! redis在容器化的过程中,涉及到纵向扩pod实例cpu、内存以及redis实例的maxmemory值,statefulset管理的pod需要...

码农实战
今天
4
0
为什么阿里巴巴Java开发手册中不建议在循环体中使用+进行字符串拼接?

之前在阅读《阿里巴巴Java开发手册》时,发现有一条是关于循环体中字符串拼接的建议,具体内容如下: 那么我们首先来用例子来看看在循环体中用 + 或者用 StringBuilder 进行字符串拼接的效率...

武培轩
今天
8
0
队列-链式(c/c++实现)

队列是在线性表功能稍作修改形成的,在生活中排队是不能插队的吧,先排队先得到对待,慢来得排在最后面,这样来就形成了”先进先出“的队列。作用就是通过伟大的程序员来实现算法解决现实生活...

白客C
今天
81
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部