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

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


  • 155 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

SQL

DDL (Data Definition Language)

DML (Data Manipulation Language)

DCL (Data Control Language)

TCL (Transaction Control Language)


Sql

SQL (. Structured Query Language )

  • , , .

  • , SQL - , .

  • SQL .


Sql

SQL

  • (. Data Definition Language, DDL)

  • (. Data Manipulation Language, DML)

  • (. Data Control Language, DCL)


Sql

1: DDL

SQL


In 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


Sql

  • DDL- :

  • Table elements:

:


Sql

FotballMatch


Sql

:


Sql

:


Sql

:


Sql

  • .

:


Check

CHECK


Unique

UNIQUE

  • :

  • : NULL

  • UNIQUE , -


Primary key

PRIMARY KEY

  • PRIMARY KEY(PK)

  • PK

  • PK NULL

  • PK


Primary key1

PRIMARY KEY

  • PK

    • PK

      <column name> . . . PRIMARY KEY

    • - PK

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

  • PRIMARY KEY UNIQUE!


D fault

(DFAULT)

  • :


Forein key

FOREIN KEY

  • - FOREIN KEY(FK)

  • :

PK

:


Sql

FK

() A () B

FK

: FK () A B


Sql

2: DL

SQL


Sql

SQL

  • SQL - SELECT-FROM-WHERE


Sql

: Select-From-Where


Sql

[1]


Sql

[2]

  • 46 * 77 = 3542


Sql

[3]

  • WHERE .

    ( 46 )


Sql

[4]

.

:


Sql

WHERE-

  • 6 :

  • - :


Sql


Sql

  • :

  • :


Sql

  • year. year population :

  • :


Sql

(Alias)

  • :

  • :


Sql


Sql

  • A, B

SQL


Sql


Sql

SQL


Sql

SELECT-FROM-WHERE ()

  • A B

    cond

    R S

    :

: 1) 2) 3)


Sql

JOIN

:

: JOIN

JOIN FROM.

INNER JOIN


Sql

IN


Sql

NOT IN


Sql

JOIN

( MS Access) INTERSECT. JOIN:

. :


Sql

DESTINCT


Sql

SQL

  • COUNT

  • SUM

  • AVG

  • Maximum

  • Minimum


Sql

3

3 4


Sql

:

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

GROUP BY groupexpr [HAVING requirement]

groupexpr

WHERE condition

HAVING requirement


Sql

  • SQL () .

    :


Sql

GROUP BY


Sql

Result

3-

SELECT AVG(mtest), class FROM student

WHERE sex="M" GROUP BY class

HAVING COUNT(*) >= 3


Sql

  • ( GROUP BY )

    :

  • :

  • ASC ( )

  • DESC


Sql

NULL


Sql

NULL

  • NULL , ();

  • NULL . , .

  • NULL, , NULL, .

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


Sql

NULL

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


Sql

NULL

NULL


Sql

  • :

    • INSERT

    • UPDATE

    • DELETE


Sql

INSERT

  • :

  • :

1)

2)


Primary key2

PRIMARY KEY

create table MY_TABLE

(

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

);

--

drop table MY_TABLE;


Sql

UPDATE DELETE

  • :

  • :

  • :

  • :


Sql

3: DL

  • DCL (data control language)

  • SQL GRANT

  • :

    GRANT {INSERT|SELECT|DELETE}

    ON EMP TO {USER}

    ( , EMP USER)


Sql

4: TL

TCL (transaction control language)

  • COMMIT

  • ROLLBACK

  • SAVEPOINT


  • Login