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

Введение в язык SQL PowerPoint PPT Presentation


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

Введение в язык SQL. DDL (Data Definition Language) ‏ DML (Data Manipulation Language) ‏ DCL (Data Control Language) ‏ TCL (Transaction Control Language) ‏. SQL (англ. Structured Query Language — язык структурированных запросов) ‏.

Download Presentation

Введение в язык 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


SQL

DDL (Data Definition Language)

DML (Data Manipulation Language)

DCL (Data Control Language)

TCL (Transaction Control Language)


SQL (. Structured Query Language )

  • , , .

  • , SQL - , .

  • SQL .


SQL

  • (. Data Definition Language, DDL)

  • (. Data Manipulation Language, DML)

  • (. Data Control Language, DCL)


1: DDL

SQL


in SQL

  • :

    • CHAR(20)-- fixed length

    • VARCHAR(40)-- variable length

  • :

    • BIGINT, INT, SMALLINT, TINYINT

    • REAL, FLOAT -- differ in precision

  • :

    • DATE

    • TIMESTAMP

    • DATETIME-- SQL Server


  • DDL- :

  • Table elements:

:


FotballMatch


:


:


:


  • .

:


CHECK


UNIQUE

  • :

  • : NULL

  • UNIQUE , -


PRIMARY KEY

  • PRIMARY KEY(PK)

  • PK

  • PK NULL

  • PK


PRIMARY KEY

  • PK

    • PK

      <column name> . . . PRIMARY KEY

    • - PK

      PRIMARY KEY(<list-of-column column-names>)

  • PRIMARY KEY UNIQUE!


(DFAULT)

  • :


FOREIN KEY

  • - FOREIN KEY(FK)

  • :

PK

:


FK

() A () B

FK

: FK () A B


2: DL

SQL


SQL

  • SQL - SELECT-FROM-WHERE


: Select-From-Where


[1]


[2]

  • 46 * 77 = 3542


[3]

  • WHERE .

    ( 46 )


[4]

.

:


WHERE-

  • 6 :

  • - :



  • :

  • :


  • year. year population :

  • :


(Alias)

  • :

  • :



  • A, B

SQL



SQL


SELECT-FROM-WHERE ()

  • A B

    cond

    R S

    :

: 1) 2) 3)


JOIN

:

: JOIN

JOIN FROM.

INNER JOIN


IN


NOT IN


JOIN

( MS Access) INTERSECT. JOIN:

. :


DESTINCT


SQL

  • COUNT

  • SUM

  • AVG

  • Maximum

  • Minimum


3

3 4


:

SELECT ...... FROM ...... WHERE condition ;

GROUP BY groupexpr [HAVING requirement]

groupexpr

WHERE condition

HAVING requirement


  • SQL () .

    :


GROUP BY


Result

3-

SELECT AVG(mtest), class FROM student

WHERE sex="M" GROUP BY class

HAVING COUNT(*) >= 3


  • ( GROUP BY )

    :

  • :

  • ASC ( )

  • DESC


NULL


NULL

  • NULL , ();

  • NULL . , .

  • NULL, , NULL, .

  • ( expr IS [NOT] NULL), (TRUE), expr ( ) NULL FALSE .


NULL

  • NULL ( NULL = NULL), (UNKNOWN). NULL ( ), FALSE ( IF <-> = NULL THEN <1> ELSE <2> END IF 2).


NULL

NULL


  • :

    • INSERT

    • UPDATE

    • DELETE


INSERT

  • :

  • :

1)

2)


PRIMARY KEY

create table MY_TABLE

(

S_ID integer generated by default as identity (start with 1) primary key

);

--

drop table MY_TABLE;


UPDATE DELETE

  • :

  • :

  • :

  • :


3: DL

  • DCL (data control language)

  • SQL GRANT

  • :

    GRANT {INSERT|SELECT|DELETE}

    ON EMP TO {USER}

    ( , EMP USER)


4: TL

TCL (transaction control language)

  • COMMIT

  • ROLLBACK

  • SAVEPOINT


  • Login