【澳门金沙vip】SQL语句实例学习汇总

复制代码 代码如下:Select * from
T_Employee select FName,FAge from T_Employee select FName from
T_Employee where FSalary 5000 select FName as 姓名,FAge as 年龄,FSalary
as 月薪from T_Employee where FSalary 5000 select FName as 姓名,FAge as
年龄,FSalary as 月薪,getdate() as 当前时间from T_Employee 3.3.1
SELECT命令的格式与基本使用 Ø数据查询是数据库中最常见的操作。
ØSQL语言提供SELECT语句,通过查询操作可得到所需的信息。
ØSELECT语句的一般格式为: SELECT〈列名〉[{,〈列名〉}]
FROM〈表名或视图名〉[{,〈表名或视图名〉}] [WHERE〈检索条件〉]
[GROUP BY 列名1[HAVING 条件表达式]] [ORDER BY 列名2[ASC|DESC]];
ØØ查询的结果是仍是一个表。 ØSELECT语句的执行过程是:
Ø根据WHERE子句的检索条件,从FROM子句指定的基本表或视图中选取满足条件的元组,再按照SELECT子句中指定的列,投影得到结果表。
Ø如果有GROUP子句,则将查询结果按照列名1相同的值进行分组。
Ø如果GROUP子句后有HAVING短语,则只输出满足HAVING条件的元组。
Ø如果有ORDER子句,查询结果还要按照列名2的值进行排序。 Ø例3.21
查询全体学生的学号、姓名和年龄。 SELECT SNO, SN, AGE FROM S Ø例3.22
查询学生的全部信息。 SELECT * FROM S Ø用‘ *
‘表示S表的全部列名,而不必逐一列出。 Ø例3.23 查询选修了课程的学生号。
SELECT DISTINCT SNO FROM SC Ø查询结果中的重复行被去掉
ØØ上述查询均为不使用WHERE子句的无条件查询,也称作投影查询。
Ø另外,利用投影查询可控制列名的顺序,并可通过指定别名改变查询结果的列标题的名字。
例3.24 查询全体学生的姓名、学号和年龄。 SELECT SNAME NAME, SNO, AGE FROM
S Ø其中,NAME为SNAME的别名 3.3.2 条件查询
Ø当要在表中找出满足某些条件的行时,则需使用WHERE子句指定查询条件。
ØWHERE子句中,条件通常通过三部分来描述: 1. 列名; 2. 比较运算符; 3.
列名、常数。 3.3.2.1 比较大小 例3.25
查询选修课程号为‘C1’的学生的学号和成绩。 SELECT SNO,SCORE FROM SC WHERE
CNO=’C1’Ø例3.26 查询成绩高于85分的学生的学号、课程号和成绩。 SELECT
SNO,CNO,SCORE FROM SC WHERE SCORE85 Ø3.3.2.2 多重条件查询
Ø当WHERE子句需要指定一个以上的查询条件时,则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。
Ø其优先级由高到低为:NOT、AND、OR,用户可以使用括号改变优先级。 Ø例3.27
查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。 SELECT
SNO,CNO,SCORE FROM SC WHERE AND SCORE=85 3.3.2.3 确定范围 例3.28
查询工资在1000至1500之间的教师的教师号、姓名及职称。 SELECT TNO,TN,PROF
FROM T WHERE SAL BETWEEN 1000 AND 1500 Ø等价于 SELECT TNO,TN,PROF FROM T
WHERE SAL=1000 AND SAL=1500 Ø例3.29
查询工资不在1000至1500之间的教师的教师号、姓名及职称。 SELECT
TNO,TN,PROF FROM TWHERE SAL NOT BETWEEN 1000 AND 1500 3.2.2.4 确定集合
Ø利用“IN”操作可以查询属性值属于指定集合的元组。 例3.30
查询选修C1或C2的学生的学号、课程号和成绩。 SELECT SNO, CNO, SCORE FROM
SC WHERE CNO IN(‘C1′, ‘C2′) Ø此语句也可以使用逻辑运算符“OR”实现。 SELECT
SNO, CNO, SCORE FROM SC WHERE CNO=‘C1′ OR CNO= ‘C2’Ø利用“NOT
IN”可以查询指定集合外的元组。 例3.31
查询没有选修C1,也没有选修C2的学生的学号、课程号和成绩。 SELECT SNO,
CNO, SCORE FROM SC WHERE CNO NOT IN(‘C1′, ‘C2′) Ø等价于: SELECT SNO,
CNO, SCORE FROM SC WHERE CNO!=‘C1′ AND CNO!= ‘C2’Ø 3.3.2.5 部分匹配查询
Ø上例均属于完全匹配查询,当不知道完全精确的値时,用户还可以使用LIKE或NOT
LIKE进行部分匹配查询。 ØLIKE定义的一般格式为: 属性名 LIKE 字符串常量
Ø属性名必须为字符型,字符串常量的字符可以包含如下两个特殊符号:
Ø%:表示任意知长度的字符串; Ø_:表示任意单个字符。 例3.32
查询所有姓张的教师的教师号和姓名。 SELECT TNO, TN FROM T WHERE TN LIKE
‘张%’ 例3.33 查询姓名中第二个汉字是“力”的教师号和姓名。 SELECT TNO, TN
FROM T WHERE TN LIKE ‘_ _力%’Ø注:一个汉字占两个字符。 3.3.2.6
空值查询 Ø某个字段没有值称之为具有空值。
Ø通常没有为一个列输入值时,该列的值就是空值。
Ø空值不同于零和空格,它不占任何存储空间。
Ø例如,某些学生选课后没有参加考试,有选课记录,但没有考试成绩,考试成绩为空值,这与参加考试,成绩为零分的不同。
例3.34 查询没有考试成绩的学生的学号和相应的课程号。 SELECT SNO, CNO FROM
SC WHERE SCORE IS NULL Ø注意:这里的空值条件为IS
NULL,不能写成SCORE=NULL。

澳门金沙vip 1 

 

表操作  

       
 最近一直在看数据库方面的问题,总结了一下SQL语句,这是部分详细的SQL问题,思路讲解:

 

  • 第一步:创建数据库表,及插入数据信息**
  • 1 –Student(S#,Sname,Sage,Ssex) 学生表

    2      CREATE TABLE student(
    3             sno        VARCHAR2(5) PRIMARY KEY,
    4             sname      VARCHAR2(30) NOT NULL,
    5             sage       NUMBER(3),
    6             ssex       VARCHAR2(5)       
    7      );    
    

      

    8      INSERT INTO student(sno,sname,sage,ssex) VALUES('001','张三','20','男');                    
    9      INSERT INTO student(sno,sname,sage,ssex) VALUES('002','李四','21','女');    
    

    10 INSERT INTO student(sno,sname,sage,ssex) VALUES(‘003′,’王五’,’20’,’男’);
    11 INSERT INTO student(sno,sname,sage,ssex) VALUES(‘004′,’王八’,’20’,’男’);
    12 COMMIT;
    13 SELECT FROM student;
    14
    15 –Teacher(T#,Tname) 教师表
    16 CREATE TABLE teacher(
    17 tno VARCHAR2(5) PRIMARY KEY,
    18 tname VARCHAR2(30) NOT NULL
    19 );
    20 INSERT INTO teacher VALUES(‘001′,’孔老师’);
    21 INSERT INTO teacher VALUES(‘002′,’李老师1’);
    22 INSERT INTO teacher VALUES(‘003′,’李老师2’);
    23
    24 –提交
    25 COMMIT;
    26
    27 SELECT
    FROM teacher;
    28
    29 –Course(C#,Cname,T#) 课程表
    30
    31 CREATE TABLE course(
    32 cno VARCHAR2(5) PRIMARY KEY,
    33 cname VARCHAR2(30) NOT NULL,
    34 tno VARCHAR2(5)
    35 );
    36 –创立外键关系
    37 ALTER TABLE course
    38 ADD CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno);
    39
    40 INSERT INTO course VALUES(‘001′,’JAVA面向对象’,’001′);
    41 INSERT INTO course VALUES(‘002′,’JSP/SERVLET网站开发’,’001′);
    42 — INSERT INTO course VALUES(‘003′,’Oracle数据库’,’001′);
    43
    44 INSERT INTO course VALUES(‘003′,’JAVA基础’,’002′);
    45 INSERT INTO course VALUES(‘004′,’C#开发’,’002′);
    46 INSERT INTO course VALUES(‘005′,’数据库基础’,’002′);
    47
    48 SELECT FROM course;
    49
    50 COMMIT;
    51
    52 –SC(S#,C#,score) 成绩表
    53
    54 CREATE TABLE sc(
    55 sno VARCHAR2(5) NOT NULL,
    56 cno VARCHAR2(5) NOT NULL,
    57 score NUMBER(4,1) NOT NULL
    58 );
    59
    60 –创立外键关系
    61 ALTER TABLE sc
    62 ADD CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno);
    63
    64 –创立外键关系
    65 ALTER TABLE sc
    66 ADD CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno);
    67
    68 –创立外键关系
    69 ALTER TABLE sc
    70 ADD CONSTRAINT fk_sno_cno PRIMARY KEY(sno,cno);
    71
    72 INSERT INTO sc VALUES(‘001′,’001′,’90’);
    73 INSERT INTO sc VALUES(‘001′,’002′,’85’);
    74 INSERT INTO sc VALUES(‘001′,’003′,’80’);
    75
    76 INSERT INTO sc VALUES(‘002′,’001′,’95’);
    77 INSERT INTO sc VALUES(‘002′,’002′,’87’);
    78 INSERT INTO sc VALUES(‘002′,’003′,’78’);
    79
    80
    81 INSERT INTO sc VALUES(‘003′,’002′,’86’);
    82 INSERT INTO sc VALUES(‘003′,’003′,’74’);
    83
    84 –提交
    85 COMMIT;
    86
    87 select
    from sc; –成绩表
    88 select from Teacher; –教师表
    89 select
    from Course; –课程表
    90 select * from Student; –学生表

  • Student(SNO,Sname,Sage,Ssex) 学生表

  • Course(CNO,Cname,TNO) 课程表
  • SC(SNO,CNO,score) 成绩表
  • Teacher(TNO,Tname) 教师表
    • 问题:1、查询“001”课程比“002”课程成绩高的所有学生的学号;

    •       思路:先查出001课程的学生成绩,在查出002课程的学生成绩,
    •       使用内联的方式查询
    • 1 SELECT A.sno ,
      2 A.score,B.score
      3 FROM
      4 (SELECT FROM sc WHERE cno=’001′) A
      5 INNER OIN
      6 (SELECT
      FROM sc WHERE cno=’002′) B
      7 ON A.sno=B.sno
      8 WHERE A.score>B.score;
    • 2 、查询平均成绩大于85分的同学的学号和平均成绩;
    •       思路:使用学生分组的形式
    • 1 SELECT SNO,AVG(SCORE) FROM SC GROUP BY SNO HAVING (AVG(SCORE )>85);
    •   3 、查询所有同学的学号、姓名、选课数、总成绩;
    •       思路:先把学生编号,计算选课数、总成绩  分组查出来     
    •              再使用左联接的方式把要查的数据查出  
    •              知识点补充:
    •              –INNER JOIN 是查询两张表之间共同拥有的部分的数据
    •              –LEFT JOIN 
      是以左边的数据表为基准先查,而右边的表相关的数据查询出来填充左边的表数据中,如果没有用null填充
    •              –RIGHT JOIN  
      是以右边的数据表为基准先查,而左边的表相关的数据查询出来填充右边的表数据中,如果没有用null填充
    • 1 SELECT S.SNO,S.SNAME,A.C_NO,A.S_SCORE
      2 FROM STUDENT S
      3 LEFT JOIN
      4 (SELECT SNO ,COUNT(CNO) AS C_NO,SUM(SCORE) AS S_SCORE FROM SC GROUP BY SNO) A
      5 ON S.SNO=A.SNO
      6

    •   4 、查询姓“李”的老师的个数;

    •     思路:使用模糊查询的方法
    • 1
      SELECT COUNT(TNAME) FROM TEACHER WHERE TNAME LIKE ‘李%’;
    •   5 、查询没学过孔老师课的同学的学号、姓名;
    •   
      思路:使用逆向思维把学过孔老师课的学生查出来(两表之间用内联,也可用子查询)
    •          根据学生学的课程,查询所学的科目里没有孔老师所教的科目
    •          知识补充:DISTINCT 查询唯一的不重复的列
    • 1 –方式一:
      2 SELECT SNO,SNAME FROM STUDENT WHERE SNO NOT IN(
      3
      4 SELECT DISTINCT SNO FROM SC WHERE CNO IN(
      5
      6 SELECT C.CNO FROM COURSE C INNER JOIN TEACHER T ON C.TNO=T.TNO WHERE TNAME=’孔老师’)
      7 )
      8
      9 –方式二:
      10 SELECT SNO,SNAME FROM student WHERE not exists(
      11 SELECT 1 FROM sc WHERE exists (
      12 SELECT 1
      13 FROM Course c INNER JOIN Teacher t ON c.tno=t.tno
      14 WHERE t.tname=’孔老师’ and sc.cno=c.cno
      15
      16 )
      17 and student.sno= sc.sno
      18 )     

    •    6
      、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

    •     思路:先根据成绩表查出学过001课程的同学
    •           再查出学过002课程的同学
    •           再两个表内联查询都学过的部分
    • 1 SELECT SNO ,SNAME FROM STUDENT S WHERE SNO IN(
      2 SELECT A.SNO FROM
      3 ( SELECT SNO FROM SC WHERE CNO =’001′) A
      4 INNER JOIN
      5 ( SELECT SNO FROM SC WHERE CNO =’002′) B
      6 ON A.SNO=B.SNO
      7 )

    •   7 、查询学过“孔老师”所教的所有课的同学的学号、姓名;

    •     思路:先查处孔老师教过哪些课程,教了几门课程
    •           再看看哪些学生学过孔老师的课程(三表联查)
    • 1 SELECT SNO,SNAME FROM STUDENT WHERE SNO IN (
      2 SELECT A.SNO FROM
      3 ( SELECT SC.SNO ,SC.CNO FROM SC
      4 INNER JOIN COURSE C ON SC.CNO=C.CNO
      5 INNER JOIN TEACHER T ON C.TNO=T.TNO
      6 WHERE T.TNAME=’孔老师’
      7 )A
      8 GROUP BY A.SNO HAVING (COUNT(1)=
      9 (SELECT COUNT(CNO) FROM COURSE C
      10 INNER JOIN
      11 TEACHER T ON C.TNO=T.TNO
      12 WHERE TNAME=’孔老师’
      13 )
      14 )
      15 )
      16

    •     8 、查询所有课程成绩小于90分的同学的学号、姓名;

    •     思路:先查出同学所有科目的最高分
    •           如果最高分小于90分的话,就查出该学生的学号和姓名
    • 1 SELECT SNO ,SNAME FROM STUDENT WHERE SNO= (
      2 SELECT SNO, MAX(SCORE) FROM SC GROUP BY SNO HAVING (MAX(SCORE)<90));

    •     9
      、查询各科成绩最高和最低的分:形式显示:课程编号,最高分,最低分

    •     思路:使用函数查询,并根据学生编号来分组
    • 1
      SELECT CNO,MAX(SCORE) MAX_SC,MIN(SCORE) MIN_SC FROM SC GROUP BY CNO;
    •     10 、查询每门课成绩最好的前两名
    •     思路:
    •        先查出每门课的成绩进行排序
    •        在根据课程来排序
    •       
      知识补充:DENSE_RANK(),名词排序,如果同名词的,则依次排列
    • 1 SELECT * FROM (
      2 SELECT SNO,CNO ,SCORE, DENSE_RANK() OVER(PARTITION BY CNO ORDER BY SCORE DESC ) AS DRK FROM SC
      3 )
      4 WHERE DRK<=2 ORDER BY CNO,DRK;

例 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

 

多表操作