SQL笔记

数据库的层次结构：目录名-模式名-表名

模式

创建模式

CREATE SCHEMA Teaching_db  AUTHORIZATION Hang;

删除模式

DROP SCHEMA Teaching_db  CASCADE;

 

表

创建表

CREATE    TABLE       Student

         (sno           NUMBERIC(6),

         sname      CHAR(8)            NOTNULL,

         age            NUMBERIC(2),

         sex            CHAR(2),

         dept          CHAR(20),

PRIMARY          KEY(sno));

 

表的删除

DROP TABLE    Student             CASCADE;

 

增加新列

ALTER       TABLE     Student

ADD          (place       CHAR(20),        addrCHAR(20));

 

删除列(CASCADE所有引用该列的视图和约束也自动删除，RESTRRICT则不行)

ALTER       TABLE       Student

DROP        addr          CASCADE/RESTRRICT；

 

修改列的类型

ALTER       TABLE     Student

MODIFY place         CHAR(8);

 

补充主键

ALTER       TABLE       Student

ADD          PRIMARY KEY  (sno);

 

删除主键

ALTER       TABLE       Student

DROP        PRIMARYKEY;

 

 

 

定义域，用于建立用户自定义属性的一个特定数据类型，由带有约束的数据类型和缺省值构成。

CREATE   DOMAIN           ITEM_ID          NUMBERIC(4)           DEFAULT  0

 

表的查询

单表查询

SELECT     DISTINCT          cno            --结果去重

FROM       SC;

 

SELECT    cno,cname

FROM       Course

WHERE    credit=3   OR    pcno=’001’;

 

SELECT     sno

RORM      SC

WHERE    cno=’002’                   AND        grade       BETWEEN         80 AND 90;

 

SELECT    sname,sno,sex

FROM       Student

WHERE    Sname    LIKE ‘刘%’        OR  sname LIKE  ‘_晓%’;

 

SELCET *

FROM      Course

WHERE    cname      LIKE           ‘数据\_%原__’         ESCAPE  ‘\;    --表示有换码字符

 

SELECT     sname,     sex

FROM       Student

WHERE    age<20     AND place IN (‘湖南’,’湖北’);

 

SELECT     sno,sname

FROM      Student

WHERE    sex=’男’

ORDER     BY    dept,  age      DESC;       --缺省为升序，第一个为主序。

 

表达式与函数的使用

SELECT    COUNT(DISTINCT     sno)

FROM       SC;  

 

SELECT     COUNT(*),        AVG(age),

FROM       Student

WHERE    dept=’文学院’;

 

SELECT     COUNT(*),        MAX(Grade),           MIN(Grade)

FROM       SC

WHERE    cno=’1’     and  grade>=60;

 

SELCET     A,     B,     A*B,         SQRT(B)            --B的算术平方根

FROM       T;

 

SELECT     cname      AS    course_name, credit*18          AS     course_time    --查询结果重命名

FROM       Course;

 

SELECT     sno,AVG(Grade)       AS     avf_grade         --查询结果分组

FROM       SC

GROUP     BY    sno

HAVING   avg_grade>80;

 

多表查询

 

子查询（where语句包含子查询，即嵌套查询）--子查询不能使用order by 语句

SELCET    sname

FROM      Student

WHERE    age>

                   (SELCET    AVG(age)

                   FROM       Student);

 

SELCET     sno

FROM      SC

GROUP     BY    snO

         HAVING   AVG(grade)>=ALL    --X>SOME,，X<ALL，X=SOME等价IN，<>ALL等价NOT IN

(SELECT    AVG(grade)

FROM       SC

GROUP   BY    sno);

 

条连接件查询（可替换大部分嵌套查询）

SELCET     Student.sno,sname, cno,garde

FROM       Student,SC

WHERE    Student.sno=SC.sno;

 

自身连接查询

SELECT     s1.sname,s1.age

FROM       Student  AS   s1,    Student    AS     s2

WHERE   s1.age>s2.ageAND s2.sname = ‘王莹’;

 

FROM字句中的子查询 --Temp为导出关系，名字可以自定，可在外层查询直接对表操作

SELECT    sname，cname ,grade

FROM       (SELECTsname,cname,grade

                   FROMStudent ,SC,Course

                   WHEREStudent.sno=SC.sno AND SC.cno=Course.cno AND sex=’女’)

                            ASTemp (sname,cname ,grade)    

WHERE    grade>=80;

 

相关子查询（子查询涉及外部查询的列）使用EXISTS ,NOTEXISTS,ALL,SOME

SELECT     sname     

FROM       Student

WHERE    EXISTS

                   (SELECT    *

                            FROM       SC

                            WHERE    SC.sno =Student.sno AND cno=’005’);

 

 

 

 

集合运算         UNION     INTERSECT       EXCEPT

SELECT *  FROM        St1

UNION

SELECT    *  FROM         St2

 

SELECT     *       FROM      St2

EXCEPT

SELECT     *       FROM       St3

 

 

 

数据更新

 

插入数据

 

子句插入数据

INSERET INTO Student

VALUES    (200510,’王雪’,20,’女’,’英语系’);

子查询插入数据

INSERT     INTO         St1_grade(gno,name,avg)

SELECT     Student.sno,    sname,     AVF(grade)       AS    avg_good

FROM       Student,            SC

WHERE    Student.sno     =       SC.sno

GROUP     BY    sno

HAVING   avg_good>=90;

 

修改数据

 

字句修改数据

UPDATE   Student

SET           age=age+1;

 

子查询修改数据

UPDATE   SC

SET   grade=0

WHERE cno=’001’   AND         sno=

         (SELECTsno

         FROM       Student

         WHERE    sname= ’左皮‘）;

 

删除数据(行)

DELETE

FROM       Teacher

WHERE    age>60;

 

视图

创建视图—AS 子查询

CREATE VIEW  S_st (no,name,age)

AS    SELECT     sno,sname,age

FROM       Student

WHERE    sex=’女’;

WITH CHECK OPTION;              --对该视图操作时需满足wehre语句的条件；

 

可以在一张视图的基础上再建立视图

 

删除视图

DROP      VIEW        S_good     RESTRICT/CASCADE;

 

更新视图将转化为对表的操作，有些视图是不允许更新的。