This presentation is the property of its rightful owner.
Sponsored Links
1 / 213

第 5 章 关系数据库标准语言 ——SQL PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on
  • Presentation posted in: General

第 5 章 关系数据库标准语言 ——SQL. 本章概要. SQL 语言发展史 SQL 语言是当前最为成功、应用最为广泛的关系数据库语言,其发展主要经历了以下几个阶段: 1974 年由 CHAMBERLIN 和 BOYEE 提出,当时称为 SEQUEL(STUCTURED ENGLISH QUERY LANGUAGE) ; IBM 公司对其进行了修改,并用于其 SYSTEM R 关系数据库系统中; 1981 年 IBM 推出其商用关系关系数据库 SQL/DS ,并将其名字改为 SQL ,由于 SQL 语言功能强大,简洁易用,因此得到了广泛的使用;

Download Presentation

第 5 章 关系数据库标准语言 ——SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


5 sql

5 SQL


5 sql

  • SQL

    SQL

    • 1974CHAMBERLINBOYEESEQUEL(STUCTURED ENGLISH QUERY LANGUAGE)

    • IBMSYSTEM R

    • 1981 IBMSQL/DSSQLSQL

    • SYBASEINFORMIX ORACLEDB2INGRESFOXPROACCESS


5 1 sql

5.1 SQL

1SQL

  • SQLSQLSQL.

  • SQL

  • SQL

  • SQLDBMSDBMS

  • DBMSSQL


5 1 sql1

5.1 SQL

2.SQL

  • SQLStructured Query Language:


3 sql

3SQL

  • SQL

    • SQLDBMSSQLDBMS

    • SQLSQLSQL,SQL

    • SQL

    • SQL


3 sql1

3SQL


3 sql2

3SQL

  • SQL

    • SQLTable

    • SQL,

    • View


5 sql

    • BASE TABLE

    • VIEW


5 sql

    • STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT)

    • STUDENT_MALE(SNO,SNAME,SAGE,SDEPT)

      • STUDENTSSEX=SNO,SNAME,SAGE,SDEPT

      • STUDENT_MALESTUDENT_MALE


5 sql

SQL

1

2

1

2

3

4

1

2

  • SQL

SQL


5 sql

SQL

  • SQL:

    • DEFINITION

    • QUERY

    • MANIPULATION

    • CONTROL

  • SQL SERVER


5 2 sql

5.2 SQL

  • SQLDATA DEFINITION LANGUAGEDDL


5 sql

SQL

  • ,<> ;

  • []

  • {}|,

  • [,N]


5 sql

5.2

  • CREATE TABLE [<.>]<>

    <> <> [] [,<> <> []]

    [, n]

    [,<>][, n]

    1<>128S,SC,C

    2($_ <=128)

    2

    3


Sql p118

SQLP118

  • SQL-92

    • charn

    • varcharn

    • int

    • smallint

    • numericpdpq

    • real

    • double precision

    • date

    • time

    • intervaldatetime


Sql server

SQLServer

1SQL

2SQL SERVER 9

  • 1. BIT, INT , SMALLINT, TINYINT

  • 2. NUMERICDECIMALPS0<=S<=PS=0123.08000.56


5 sql

3.

FLOATREAL1.23E+10

4.

DATETIMESMALLDATETIME1998-06-08 15:30:00

5.

CHAR, VARCHAR, TEXT

6.

UNIQUEIDENTIFIER TIMESTAMPTIMESTAMPUNIQUEIDENTIFIER


5 sql

INT

-2^312^31-1

4BYTES

SMALLINT

BIT

-2^152^15-1

0, 1, NULL

1BIT1BYTEBIT1BYTE

2BYTES

TINYINT

0255

1BYTES


5 sql

NUMERIC

-10^38-110^38-1

1-95BYTES

10-199BYTES

20-2813BYTES

29-3817BYTES

DECIMAL

-10^38-110^38-1

5-17BYTESNUMERIC

FLOAT

-1.79E+3061.79E+308, 53

8BYTES

REAL

-3.40E+383.40E+3824

4BYTES


5 sql

DATETIME

1753/1/19999/12/31

8BYTES

SMALLDATETIME

1900/1/12079/6/6

4BYTES

CHAR

1-8000

11B

VARCHAR

1-8000

11B

TEXT

2^31-1

12B2GB


5 sql

  • 12345.67883


5 sql

  • NPS

    • BINARY(N) -------- BINARY(10)

    • CHAR(N) -------- CHAR(20)

    • NUMERIC(P,[S]) ------- NUMERIC(8,3)

    • INT4100104


5 sql

  • 3

    • USE STUDENT

    • CREATE TABLE S

      (SNO CHAR(8) ,

      SN VARCHAR(20),

      AGE INT,

      SEX CHAR(2),

      DEPT VARCHAR(20));


5 sql

3.

  • SQL SERVER

    • [ CONSTRAINT <> ] <>


5 sql

SSN+SEX

  • USE STUDENT

    CREATE TABLE S

    ( SNO CHAR(5),

    SN CHAR(8),

    SEX CHAR(2),

    CONSTRAINT S_UNIQ UNIQUE(SN,SEX));


5 sql

  • USE STUDENT

  • CREATE TABLE S

    (SNO CHAR(10) NOT NULL ,

    SN VARCHAR(20),

    AGE INT,

    SEX CHAR(2) DEFAULT '' ,

    DEPT VARCHAR(20));


5 sql

  • SQL SERVER

    1NULL/NOT NULL

    • NULL

    • NULL0NULL

    • NOT NULL

    • [CONSTRAINT <> ][NULL|NOT NULL]


5 sql

3 SSNONOT NULL

  • USE STUDENT

  • CREATE TABLE S

    (SNO CHAR(10) CONSTRAINT S_CONS NOT NULL

    SN VARCHAR(20),

    AGE INT,

    SEX CHAR(2) DEFAULT ,

    DEPT VARCHAR(20));

  • SNONOT NULLNULL

  • S_CONS


  • 5 sql

    2UNIQUE

    • UNIQUE

    • UNIQUE

    • NULL

    • UNIQUE

    • UNIQUE

      [CONSTRAINT <>] UNIQUE

    • SSN

      • USE STUDENT

      • CREATE TABLE S

        (SNO CHAR(6),

        SN CHAR(8) CONSTRAINT SN_UNIQ UNIQUE,

        SEX CHAR(2),

        AGE NUMERIC(2));


    5 sql

    • SN_UNIQ

      • USE STUDENT

      • CREATE TABLE S

        (SNO CHAR(6),

        SN CHAR(8) UNIQUE,

        SEX CHAR(2),

        AGE NUMERIC(2));

    • UNIQUE

      [CONSTRAINT <>] UNIQUE<>[{,<>}]


    5 sql

    3 SSN+SEX

    • USE STUDENT

    • CREATE TABLE S

      ( SNO CHAR(5),

      SN CHAR(8),

      SEX CHAR(2),

      CONSTRAINT S_UNIQ UNIQUE(SN,SEX));

  • SN+SEX

    3PRIMARY KEY

  • PRIMARY KEYNULL


  • 5 sql

    • PRIMARY KEYUNIQUE

      • PRIMARY KEYUNIQUE

      • PRIMARY KEYUNIQUE

    • UNIQUEPRIMARY KEY

    • PRIMARY KEY

    • PRIMARY KEY

      CONSTRAINT <> PRIMARY KEY


    5 sql

    • 3 SSNOS

      • USE STUDENT

      • CREATE TABLE S

        (SNO CHAR(5) NOT NULL CONSTRAINT S_PRIM PRIMARY KEY,

        SN CHAR(8),

        AGE NUMERIC(2));

    • PRIMARY KEY

      [CONSTRAINT <>] PRIMARY KEY (<>[{<>}])


    5 sql

    • 3 SCSNO+CNOSC

      • USE STUDENT

      • CREATE TABLE SC

        (SNO CHAR(5) NOT NULL,

        CNO CHAR(5) NOT NULL,

        SCORE NUMERIC(3),

        CONSTRAINT SC_PRIM PRIMARY KEY(SNO,CNO));


    5 sql

    4FOREIGN KEY

    • FOREIGN KEY

    • FOREIGN KEY

    • [CONSTRAINT <>] FOREIGN KEY REFERENCES <> (<>[{<>}])


    5 sql

    • SCSNO,CNOSC

      • USE STUDENT

      • CREATE TABLE SC

        (SNO CHAR(5) NOT NULL CONSTRAINT S_FORE FOREIGN KEY REFERENCES S(SNO),

        CNO CHAR(5) NOT NULL CONSTRAINT C_FORE FOREIGN KEY REFERENCES C(CNO),

        SCORE NUMERIC(3),

        CONSTRAINT S_C_PRIM PRIMARY KEY (SNO,CNO));


    5 sql

    5CHECK

    • CHECK0-100

    • CHECK

    • [CONSTRAINT <>] CHECK (<>)

    • 3SCSCORE 0100

      • USE STUDENT

      • CREATE TABLE SC

        (SNO CHAR(5),

        CNO CHAR(5),

        SCORE NUMERIC(5,1) CONSTRAINT SCORE_CHK CHECK(SCORE>=0 AND SCORE <=100));


    5 sql

    • USE STUDENT

    • CREATE TABLE S

      (SNO CHAR(6) CONSTRAINT S_PRIM PRIMARY KEY,

      SN CHAR(8) CONSTRAINT SN_CONS NOT NULL,

      AGE NUMERIC(2) CONSTRAINT AGE_CONS NOT NULL

      CONSTRAINT AGE_CHK CHECK (AGE BETWEEN 15 AND 50),

      SEX CHAR(2) DEFAULT '',

      DEPT CHAR(10) CONSTRAINT DEPT_CONS NOT NULL);


    5 sql

    • SQLALTER TABLE

      1. ADD

    • CREATE TABLE

      ALTER TABLE <> ADD <> | <>

    • 3 S

      • USE STUDENT

      • ALTER TABLE S ADD CLASS_NO CHAR(6), ADDRESS CHAR(40)


    5 sql

    • NULLNOT NULL

    • SCSCORE0-100

      • USE STUDENT

      • ALTER TABLE SC

        ADD

        CONSTRAINT SCORE_CHK CHECK(SCORE BETWEEN 0 AND 100)


    5 sql

    2. ALTER

    • ALTER TABLE<>

      ALTER COLUMN <><>[NULL|NOT NULL]

    • SSNO8

      • USE STUDENT

      • ALTER TABLE S

        ALTER COLUMN

        SNO CHAR(8)


    5 sql

    NOT NULL

    NULL|NOT NULL

    3.DROP

    • ALTER TABLE<>

      DROP CONSTRAINT <>


    5 sql

    SAGE_CHK

    • USE STUDENT

    • ALTER TABLE S

      DROP

      CONSTRAINT AGE_CHK

    • RENAME

      RENAME <> TO <>

  • SSTUDENT

    • USE STUDENT

    • RENAME S TO STUDENT


  • 5 sql

    • DROP TABLE <>

    • STUDENT

      • USE STUDENT

      • DROP TABLE STUDENT


    5 sql

    5.2.2

    1

    • SQLSQL

    • SQL SERVER

    • 1.

    • 2.


  • 5 sql

    2

    1.

    • 2.

    • PRIMARY KEYPRIMARY KEY

    • UNIQUESQL SERVER

    • PRIMARY KEYSQL SERVERPRIMARY KEY

      3.


    5 sql

    3

    • CREATE INDEX

      CREATE [UNIQUE] [CLUSTER] INDEX <> ON <> (<> [] [{,<>}] [])

      • UNIQUE

      • CLUSTER

      • ASCDESCASC

    • SCSNOCNO

      • USE STUDENT

      • CREATE UNIQUE INDEX SCI ON SC(SNO,CNO)


    5 sql

    • SCSCI

    • SNOCNOSCSNOSNOCNO

    • UNIQUE(SNO,CNO)

    • TTN

      CREATE CLUSTERED INDEX TI ON T(TN)

    • TTITTN


    5 sql

    • 1.

      2.

    • DROP INDEX

      DROP INDEX .

    • SCSCI

      • DROP INDEX SC.SCI


    5 sql


    5 sql

    1

    DEPT

    PC

    1

    N

    DP

    S

    N

    N

    SC

    PROF

    N

    M

    PC

    COURSE

    M

    ER


    5 sql

    DEPT(D# , DNAME , DEAN)

    PROF(P# , PNAME, AGE, D# , SAL)

    PC(P# , C#)

    S(S# , SNAME , SEX , AGE , D#)

    SC(S# , C# , SCORE)

    COURSE(C# , CN , CREDIT)


    5 sql

    SQL

    • SQL


    5 sql

    • SQL-92

      • charn

      • varcharn

      • int

      • smallint

      • numericpdpq

      • real

      • double precision

      • date

      • time

      • intervaldatetime


    5 sql

      • create domain

      • create domainperson-name char20

        C

        typedefADDRESS_LIST{

        char name[10];

        char telephone[20];

        char location[20]

        char email[20]};

        ADDRESS_LIST tom;


    5 sql

    • create database

    • drop database

    • database

    • close database


    5 sql

    Create database college

    Drop database college


    5 sql

    • CREATE

      • create table

        [default] [not null]

        [ [default ] [not null]]

        [primary key [] ]

        [foreign key []

        references [] ]

        [check]


    5 sql

    Create table DEPT(D# char(4),

    DNAME char(20) ,

    DEAN char(4),

    primary key (d#))


    5 sql

    • CREATE TABLE S( S# CHAR(4),

      SNAME CHAR(8) NOT NULL,

      AGE SMALLINT,

      SEX CHAR(1),

      PRIMARY KEY (S#),

      CHECK (SEX=0 OR SEX=1)

      )


    5 sql

    CREATE TABLE C( C# CHAR(4),

    CNAME CHAR(30) NOT NULL,

    Credit CHAR(8),

    PRIMARY KEY (C#),

    )


    5 sql

    CREATE TABLE SC

    (S# CHAR(4),

    C# CHAR(4),

    GRADE smallint ,

    PRIMARY KEY (S#,C#),

    FOREIGN KEY (S#)

    REFERENCES S(S#),

    FOREIGN KEY (C#)

    REFERENCES C(C#),

    CHECK((GRADE IS NULL) OR

    GRADE Between 0 AND 100))


    5 sql

    • create table PROF

      ( P# char(4),

      pname char(20) not null,

      SAL int,

      AGE int,

      D# char(4),

      locate char(30),

      primary key (P#),

      foreign key (D#)

      references DEPT(D#),

      check (SAL > 0))


    5 sql

    create table PC(

    P# char(4) ,

    C# char(4),

    primary key (p#,c#),

    foreign key (p#)

    references prof(p#),

    foreign key (c#)

    references c(c#))


    5 sql

    • ALTER

      • alter table

        [add]

        [drop ]

        [modify ]

      • alter table PROF

      • add LOCATION char(30)


    5 sql

    • drop

      • drop table

      • drop tableDEPT

      • DANGER


    5 sql

    C

    use college

    insert into c values('C001','',2)

    insert into c values('C003','',6)

    insert into c values('C004','C',5.5)

    insert into c values('C005','',4)

    insert into c values('C006','',2)

    insert into c values('C007','',2.5)

    insert into c values('C008','',4)

    insert into c values('C009','',2)

    insert into c values('C010','',3)

    insert into c values('C011','',2)

    insert into c values('C012','VC++',3)

    go


    5 sql

    S

    use college

    insert into s values('s001','',21,1)

    insert into s values('s003','',16,0)

    insert into s values('s004','',25,1)

    insert into s values('s005','',18,1)

    insert into s values('s006','',22,0)

    insert into s values('s007','',21,0)

    insert into s values('s008','',21,1)

    insert into s values('s009','',23,1)

    insert into s values('s010','',23,0)

    insert into s values('s011','',20,1)

    insert into s values('s012','',17,0)

    go


    5 sql

    prof

    use college

    insert into prof values('p001','',921,23,'d001','')

    insert into prof values('p003','',1260,43,'d002','')

    insert into prof values('p004','',2506,51,'d003','')

    insert into prof values('p005','',3800,31,'d004','')

    insert into prof values('p006','',2621,30,'d005','')

    insert into prof values('p007','',2211,55,'d006','')

    insert into prof values('p008','',821,42,'d001','')

    insert into prof values('p009','',923,31,'d001','')

    insert into prof values('p010','',3123,40,'d002','')

    insert into prof values('p011','',2320,21,'d002','')

    insert into prof values('p012','',1907,24,'d005','')

    go


    5 sql

    PC

    use college

    insert into Pc values('p001','C001')

    insert into Pc values('p003','C002')

    insert into Pc values('p004','C003')

    insert into Pc values('p005','C004')

    insert into Pc values('p006','C005')

    insert into Pc values('p007','C006')

    insert into Pc values('p008','C001')

    insert into Pc values('p009','C001')

    insert into Pc values('p010','C002')

    insert into Pc values('p011','C002')

    insert into Pc values('p012','C005')

    go


    5 sql

    SC

    use college

    insert into sc values('s001','C001',90)

    insert into sc values('s003','C002',89)

    insert into sc values('s004','C003',56)

    insert into sc values('s005','C004',40)

    insert into sc values('s001','C005',50)

    insert into sc values('s003','C006',87)

    insert into sc values('s004','C001',35)

    insert into sc values('s005','C001',70)

    insert into sc values('s001','C002',64)

    insert into sc values('s001','C006',87)

    insert into sc values('s003','C007',68)

    go


    5 sql

    Dept

    use college

    insert into dept values('D001','','P001')

    insert into dept values('D002','','P002')

    insert into dept values('D003','','P003')

    insert into dept values('D004','','P004')

    insert into dept values('D005','','P005')

    insert into dept values('D006','','P006')

    Go


    5 sql

      • create [unique/distinct] [cluster] index

        on ( [asc/desc] [ , asc/desc]])

        uniquedistinct

        cluster

        asc/descasc


    5 sql

    • create index s_index on S(S#)

    • drop index


  • 5 sql


    5 sql

    :


    5 sql


    5 sql

    SQL

    • SQL


    5 sql

    SQL

    • SQL

    • select

    • from

    • where


    5 sql

    SQL


    5 sql

    SQL

    • selectA1 , A2 , , An

      fromr1 ,r2 , , rm

      whereP

      A1 , A2 , , An(p(r1 r2 rm))


    5 sql

    Sql

    Select [All | DISTINCT] <> [ ],<> [],

    FROM <>[]

    [, <>[]

    [where <>]

    [Group By<1>[Having <>]]

    [Order By<2>[Asc|Desc]];


    5 sql

    Sql

    • Selectwhere ,group,having order by

    • Wherehaving

      • Havinggroup bygroup

      • Havingwhere


    5 sql

    SQL

    • select PNAME

      from PROF


    Select

    select

    • *

      • *

        select *

        fromPROF


    Select1

    select

    • select PNAME,SAL * 0.95

      from PROF


    5 sql

    • alldistinctunique

    • select distinct S#

      from SC


    5 sql

    from

      • from

      • 500

        select PNAME , SAL , DNAME

        from PROF , DEPT

        where SAL < 500

        and PROF.D# = DEPT.D#


    5 sql

    from

    • select PROF.p# , PNAME,cName

      from PROF , PC , C

      where PROF.p# = PC.P#

      and PC.C# = C.C#

      and C.CNAME like ''


    Where

    where

        • =

        • andornot

      • between


    Where1

    where

      • 500~800

        select PNAME

        from PROF

        where SAL between 500 and 1000


    5 sql

    • old_name as new_name

      selectfrom

      as


    5 sql

      • select PNAME,SAL*0.05 as taxi,

        SAL*0.95 as incoming

        from PROF

        AS SQL SEVER


    5 sql

    • select P1.PNAME,P1.SAL

      from PROF as P1,PROF as P2,DEPT

      where P1.D# = DEPT.D#

      and DEPT.DEAN = P2.P#

      and P1.SAL > P2.SAL


    5 sql

      • [not] like


    5 sql

      • %

      • Escape

        • escape \ \ \%%\

    \


    5 sql

      • select *

        from PROF

        where PNAME like '%'


    5 sql

    • 43d2_

      select *

      from PROF

      where PNAME like '% _d \_'


    5 sql

    • order by [asc | desc]

      • select DNAME,PNAME

        from PROF,DEPT

        where PROF.D# = DEPT.D#

        order by DNAME asc,PNAME desc


    5 sql

    • union

      intersect

      except

      • all


    5 sql

      • c001c002

        (select S#,c#from SC

        where C# ='c001')

        union

        (select S#,c#

        from SC

        where C#='c002')SQL


    5 sql

    • c001c002

      select S#,c# from SC

      where C# ='c001' and s# in

      (select S#

      from SC

      where C#='c002')


    5 sql

    • c001c002c003

      (select S#

      from SC

      where C# = 'c001' or C# ='c002')

      except

      (select s#

      from SC

      where C# = 'c003')

      SQL SEVERexcept


    5 sql

    select S#

    from SC

    where (C# = 'c001' or c# = 'c002')

    and s# not in

    (select S#

    from SC

    where C# = 'c003')


    5 sql

    • group by [having]

      • group by

      • havingGroup byhaving.

      • Group BySelect Group By


    5 sql

    • Select A1,A2An

    • From T1,T2Tn

    • Where P1

    • Group by Ti Having


    5 sql

    82

    {

    90

    92

    {

    85

    92

    {

    92

    group by S#

    group by C#


    5 sql

      • avg

      • min

      • max

      • sum

      • count

    • count*count


    5 sql

      • select D#,max(SAL),min(SAL),avg(SAL)

        from PROF

        group by D#


    5 sql

    • select c#,count(s#)

      from sc

      group by c#


    5 sql

    • select count (S# )

      from SC

      select PNAMEmax(SAL)

      from PROF

      select D#,avg(SAL)

      from PROF

      group by D#

      where age>10

    select count (S# )

    from S

    where s# in (select s# from sc

    )

    select count (distinct s#) from sc


    5 sql

    • select S#,avg(grade)

      from SC

      group by S#

      having min(grade) >= 60

      select S#,avg(grade)

      from SC

      where grade>=60

      group by S# /**/


    5 sql

    • select avg(grade)

      From sc

      Where grade>=60

      select avg(grade) from sc

      where s# not in(

      select s# from sc

      where grade<60)


    5 sql

    • 50

      select AGE,count(S#)

      from S

      where SEX = 'M'

      group by AGE

      having count(*) > 50


    5 sql

    • 150

      select s#,sname from s where s.s# in

      (select s#

      from sc

      group by s#

      having sum(grade)>150)


    5 sql

    • is [not] null

    • select PNAME

      from PROF

      where AGE is null

      where AGE = null


    5 sql

      • is [not] null

      • nullnull

      • nullfalseSQL-92unknown

      • nullcount(*)null


    5 sql

    select sum(Grade)

    from SC

    select count(*)

    from SC

    360

    6


    5 sql


    5 sql

    • in

      [not] in

      • select *

        from S

        where SNAME in ('','')


    5 sql

    • c001

      select S#,SNAME

      from S

      where S# in

      (select S#

      from SC

      where C# = 'c001')


    5 sql

    • c001c002

      select S#

      from SC

      where SC.C# = 'c001'

      and S# in

      (select S#

      from SC

      where C# = 'c002')


    5 sql

    • some/all

      • some

      • all


    5 sql

      • select s#

        from sc

        group by s#

        having avg(grade)>=all(

        select avg(grade)

        from sc

        group by s#)

    select s#,sum (grade)

    from sc

    group by s#


    5 sql

    • [not] exists

    inexists


    5 sql

    • c001

      select S#,SNAME

      from S

      where exists

      (select *

      from SC

      where C# ='c001'

      and S# = S.s#)


    5 sql

    • c001c002

      select S#

      from SC SC1

      where SC1.C# ='c001'

      and exists

      (select S#

      from SC SC2

      where SC2. C# ='c002'

      and SC2.S# = SC1.S#)


    5 sql

    • select SNAME

      from S

      where not exists

      (select C#

      from C

      where not exists

      (select *

      from SC

      where SC.C# = C.C#

      and SC.S# = S.S#))


    5 sql

    001

    select sname

    from s

    where not exists(select *

    from sc sc1

    where sc1.s#='s001' and not exists

    (select * from sc sc2

    where s.s#=sc2.s#

    and sc2.c#=sc1.c#));

    001

    001


    5 sql

    001

    001

    • 001

      select SNAME

      from S

      where not exists

      (select C#

      from C

      where exists

      (select *

      from SC

      where SC.C# = C.C#

      and SC.S# ='s001')

      and not exists

      (select *

      from SC

      where SC.C# = C.C#

      and SC.S#=S.S#))


    5 sql

    • unique (

      true


    5 sql

      • select PNAME

        from PROF

        where unique /*unique*/

        select PNO

        from PC

        where PC.PNO = PROF.PNO)

        sql severunique ,


    5 sql

    • select PNAME

      from PROF

      where p# in

      (select p#

      from PC

      group by p#

      having count(c#)=1 )


    5 sql

    • select SNAME

    • from S

    • where not unique

    • (select S#

    • from SC

    • where SC.s# = S.s#)

    to TRUE or not to TRUE , that is the question

    unique { (a , b , null) , (a , b , null) } ?


    5 sql

    • as

      SQL-92from

    Vs


    5 sql

      • select sc.S# , avg(grade)

        from S,SC

        where SC.S# = S.S#

        group by SC .S#


    5 sql

    select rname, AVG_SCORE

    from

    (select sc.s#,avg(grade)

    from S,SC

    where SC.S# = S.S#

    group by SC.S#)

    as result(rname,AVG_SCORE )

    where AVG_SCORE >= 60


    5 sql

    • create view view_name[([] )]

      as

      [with check option]

      with check optioninsertupdate

    • drop view view_name


    5 sql

    • create view COMPUTER_PROF

      as (select prof.P#,PNAME , SAL

      from PROF,DEPT

      where PROF.D# = DEPT.D#

      and DEPT.DNAME ='')


    5 sql

    create view DEPTSAL( DNO, LOW, HIGH, AVERAGE, TOTAL )

    as ( select D#, min(SAL), max(SAL), avg(SAL), sum(SAL)

    from PROF

    group by D# )


    5 sql

    • 800

      select PNAME

      from COMPUTER_PROF

      where SAL > 800

    • select LOW , HIGH , AVERAGE , TOTAL

      from DEPTSAL , DEPT

      where DEPTSAL.Dno = DEPT.D#

      and DEPT.DNAME =''


    5 sql

    SQL


    5 sql

    • insert into [[]]

      values []

      insert into [[]]


    5 sql

    • insert into PROF

      values ( 'P014', '',498 ,35, 'D005','')

      insert into PROF (P#, PNAME, D#)

      values ( 'P016', '', 'D005')

      SALlocation


    5 sql

    • 90EXCELLENT

      create table excell( sno char(4),

      s_name char(6),

      score int

      )

      insert into excell(sno,score)

      select s#, avg(grade)

      from SC

      group by (S#)

      having avg(grade)>50


    5 sql

    insert into excell(s_name)

    select sname

    from s,excell

    where sno=s.s#

    Excellsnoprimary key


    5 sql

    EXCELL

    update excell

    set s_name=

    (select sname from s

    where sno=s#)


    5 sql

    insert into PROF

    select *

    from PROF


    5 sql

    • delete from [where]

      where


    5 sql

      • delete from SC

      • delete from PC

        where P# in

        (select P#

        from PROF

        where PNAME = '')


    5 sql

    • delete from PROF

      where SAL <

      (select avg(SAL)

      from PROF)


    5 sql

    • update

      set = |

      = [ | ]

      [where]


    5 sql

      • 5%

        update PROF

        set SAL = SAL * 1.05


    5 sql

    • D01

      update PROF

      set SAL = (select avg(SAL)

      from PROF

      where D# ='D001')

      where P# = (select DEAN

      from DEPT

      where D# ='D001')


    5 sql

    • C0015%

      update SC

      set GRADE = GRADE * 1.05

      where C# = 'C001'

      and GRADE < (select avg(GRADE)

      from SC

      where C# ='C001')


    5 sql

    • 200010%5%

      update PROF

      set SAL = SAL * 0.9

      where SAL > 2000

      update PROF

      set SAL = SAL * 0.95

      where SAL <= 2000


    5 sql

    • create view P_SAL

      as (select P# , PNAME , SAL

      from PROF )

    insert into P_SAL

    values ( 'P017' , '' , 750 )

    insert into PROF

    values ( P017 , , null , null , 750 )


    5 sql

    create view SN_AGE

    as (select SNAME, AGE

    from S )

    insert into SN_AGE

    values ( ,23 )

    insert into S

    values (null, , 23, null)


    5 sql

    create view S_G(S#, AVERAGE )

    as ( select S#, avg(GRADE)

    from SC

    group by S# )

    update S_G SC

    set AVERAGE = 85

    where S# = 'S001'


    5 sql

    • create view S_C_G

      as (select SNAME, CNAME , GRADE

      from S, C, SC

      where S.S# = SC.S#

      and C.C# = SC.C# )

      insert into S_C_G

      values ( '' , '',97 )

      SC97


    5 sql

      • select

      • selectuniquedistinct

      • group by


    5 sql

    SQL


    5 sql


    5 sql

      • DBAUiUjUiUj


    5 sql

    U1

    U1

    U4

    U4

    U2

    U2

    DBA

    DBA

    U5

    U5

    U3

    U3

    U2

    U2

    U2

    U2

    DBA

    DBA

    DBA

    DBA

    U3

    U3

    U3

    U3


    5 sql

    • grant on { | } to

      { [] | public}

      [with grant option]

      select, update, insert, delete, index, alter, drop, resourceallselect , update

      with grant option


    5 sql

    • grant select , insert on S to Liming

      with grant option

    • grant all on S to public


    5 sql

    • revoke on { | } from

      { [] | public}

      DBA

    • revoke insert on S from Liming


    5 sql

    • grantrevoke

    • revoke all on S from public

      grant select on S to public


    5 sql

    • grant to { [] | public}

      connectdatabase

      resourceconnect+

      dbaresource + connectresourcedba

      dbadba

    dbadba


    5 sql

      • DBMS(Garbage In Garbage Out)


    5 sql


    5 sql

        • DBA

        • ELSE


    5 sql

        • PRIMARY KEY(S#)

        • S# CHAR(4) PRIMARY KEY


    5 sql


    5 sql

      • 310123


    5 sql

        • SCFOREIGN KEY (S#) REFERENCES S(S#)

        • S#SCSS#SCSS#


    5 sql

      • RESTRICT

      • CASCADE

      • SET NULL

      • FOREIGN KEY (S#) REFERENCES S(S#) [ON DELETE [CASCADE | SET NULL] ]


    5 sql

      • RESTRICT

      • CASCADE

      • SET NULL

      • FOREIGN KEY (S#) REFERENCES S(S#) [ON UPDATE [CASCADE | SET NULL] ]


    5 sql

        • SNAME CHAR(8) NOT NULL

        • CHECK(AGE>15)

        • CREATE DOMAIN CHECK

        • CREATE DOMAIN AGE SMALLINT

          CHECK((VALUE >= 15) AND VALUE <= 25))


    5 sql

      • CREATE TABLE SC

        (S# CHAR(4),

        C# CHAR(4),

        GRADE SMALLINT,

        PRIMARY KEY (S#, C#),

        CHECK(S# IN (SELECT S# FROM S)),

        CHECK(C# IN (SELECT C# FROM C)))

    SCHECKSC


    5 sql

      • CONSTRAINT <>

      • S# CHAR(4) CONSTRAINT S_PK PRIAMRY KEY

        AGE SAMLLINT CONSTRAINT AGE_VAL

        CHECK(AGE >= 15 AND AGE <= 25)


    5 sql

    • alter drop

      alter add

    • alter table S drop constraint S_PK

      alter table SC add constraint SC_CHECK

      check(S# in select S# from S)


    5 sql

    • create domain AGE_DOMAIN smallint

      constraint DC_AGE check(value <= 25 and value >= 15)

      alter domain AGE_DOMAIN

      add constraint DC_AGE check(value <= 35 and value >= 15)

      alter domain AGE_DOMAIN

      drop constrain DC_AGE


    5 sql

      • CREATE ASSERTION <> CHECK <>

      • X, P(X)not exists X P(X)


    5 sql

    • create assertion ASSE2 check

      (not exists

      (select *

      from SC

      where C# in

      (select C#

      from C

      where TEACHER = '')

      and S# in

      (select S#

      from S

      where SEX= '1')))


    5 sql

    • 50

      create assertion ASSE1 check

      (50 >= all(select count(SC.S#)

      from S, SC

      where S.S# = SC.S#

      and SEX = M

      group by C#)))

    • drop assertion

      drop assertion ASSE1


    5 sql

    SQL

        • Insert deleteupdate


    5 sql

    begin

    after

    {

    }

    create trigger trigger-name

    insert

    delete

    update

    {

    }

    on table-name

    [of column-name]

    old row as identifier

    new row as identifier

    {

    }

    referencing

    for each row

    when(search-condition)

    {

    }

    begin atomic

    triggered-SQL-statement

    end

    SQL


    5 sql

    SQL

    EMP(ENO, ENAME, SAL, JOB)

    10%

    create triggerRAISE_LIMIT

    after update of SAL onEMP

    referencing new row asnrow old row asorow

    for each row

    when(nrow.SAL > 1.1 * orow.SAL)

    begin atomic

    signal SQLSTATE 7500 (Salary increase 10%)

    end


    5 sql

    SQL

    0

    create triggeroverdraft-trigger after update onaccount

    referencing new row asnrow for each row

    whennrow.balance < 0

    begin atomic

    insert intoborrower

    (select customer-name, account-number

    fromdepositor

    wherenrow.account-number = depositor.account-number);

    insert intoloan values(nrow.account-number,

    nrow.branch-name, nrow.balance);

    updateaccount set balance = 0

    where account.account-number = nrow.account-number

    end


    5 sql

    SQL

    • SQL

    • SQL


    5 sql

    SQL

    • SQL

      • SQLSQLSQL4.10


    5 sql

    + SQL

    +

    SQL


    5 sql

    • SQLC

      SQLEXEC SQL(;) END_EXEX

      EXEC SQL delete from PROF

      where DNO = 10


    5 sql

    • SQLC

        • CCSQL

        • C

          EXEC SQL BEGIN DECLARE SECTION

          EXEC SQL END DECLARE SECTION


    5 sql

    EXEC SQL BEGIN DECLARE SECTION

    int prof_no;

    char prof_name[30];

    int salary;

    EXEC SQL END DECLARE SECTION


    5 sql

    SQL:

    SQLselectfetchinto

    EXEC SQL select PNAME , SAL

    into :prof_name , :salary

    from PROF

    where PNO = prof_no ;


    5 sql

      • Cnull

      • = 0

      • = 1

      • > 0


    5 sql

      • ( : )indicator

        EXEC SQL BEGIN DECLARE SECTION

        int prof_no;

        char prof_name[30];

        int salary;

        short name_id;

        shortsal_id;

        EXEC SQL END DECLARE SECTION

        EXEC SQL select PNAME , SAL

        into :prof_name : name_id , :salary: sal_id

        from PROF

        where PNO = prof_no ;


    5 sql

    • SQL

        • SQL

        • C

        • SQL

        • SQL


    5 sql

      • select

        EXEC SQL

        select PNAME , SAL

        into :prof_name : name_id, :salary : sal_id

        from PROF

        where PNO = prof_no ;


    5 sql

    • insert

      EXEC SQL insert into PROF values (:prof_no, :prof_name , :salary , :dept_no , : salary) ;

    • delete

      EXEC SQL delete from PROF

      values PNO > :prof_no ;

    • update

      EXEC SQL update PROF

      set SAL = :salary

      where PNO = : prof_no ;


    5 sql

    • select

      selecT


    5 sql


    5 sql

      • declare

        select

        declare [scroll] cursor for

        select[for update [of]]

        for update


    5 sql

    • open

      open

    • fetch

      fetch [next | prior | first | last | current | relative n | absolute m]

      into []


    5 sql

    • close

      open

      close

    • free

      open

      free


    5 sql

    • SQL

      • ,SQL

      • SQLSQLCA,SQLSQLCA, SQLCASQL,

      • (USERCA),:

        EXEC SQL INCLUDE SQLCA;


    5 sql

    ODBC


    5 sql

    ODBC

    • ODBC

      • -SQLAPI

      • ODBC APIODBCODBC

      • ODBC

      • ODBC APICLI()SQLCLI


    5 sql

    ODBC

    Oracle

    DB2


  • Login