Database foundation-03

Posted by dkruythoff on Wed, 17 Nov 2021 12:03:38 +0100

3, Relational database standard query language SQL

1, SQL overview

(1) Composition of SQL language

1. Data definition (DDL)

——Define the logical structure of the database, including basic tables, views, indexes, etc

2. Data manipulation (DML)

——It includes query and update, and update also includes insertion, deletion and modification

3. Data control (DCL)

——Authorization, integrity rule description, transaction control, etc

4. Embedded SQL(ESQL)

——Rules for using SQL in the host language

(2) Features of SQL language

——Integrated and unified, set oriented operation mode, highly non procedural and unified syntax structure, providing two use modes (self-contained and embedded) and concise language

-DDL: create drop alter

-DML: select insert delete update

-DCL: grant revoke commit rollback

2, Data definition language (DDL)

(1) , define, delete and modify basic tables

1. Define basic table syntax

create table <Table name> (<Listing><data type>(Column level constraints)，<Listing><data type>(Column level constraints)，...，<Table set integrity constraints>);

2. Modify table syntax

alter table <Table name>    ADD<New column name><data type>(Column level constraints);
alter table <Table name>    MODIFY<Listing><data type>;
alter table <Table name>    DROP<Integrity constraints>;

3. Delete table syntax

drop table <Table name>;

(2) , building, and deleting indexes

1. Indexing syntax

create unique index <Index name> on <Table name>(<Column name 1><order>，...)
    <order>Can be ASC and DESC

2. Index deletion syntax


drop index [<Table name>]<Index name>

3, Data query for SQL (DML)

    SELECT <Target expression 1> ,<Target expression 2> ... ...
    FROM <Table name or view name 1> ，<Table name or view name 2>... ...
    WHERE <Conditional expression>
    GROUP BY <Column name expression 1>，<Column name expression 2> HAVING <Conditional expression>
    ORDER BY <Column name expression 1> ASC|DESC, <Column name expression 2> ASC|DESC

- first find the tuples that meet the conditions FROM the table / view specified in the FROM clause according to the WHERE clause (select)

- if there is a GROUP clause, the resu lt s are grouped according to the value of < column name expression >, and the tuples with equal value of < column name expression > are a GROUP, and the aggregate function is usually used in each GROUP.

- if the GROUP clause has a HAVING clause, the GROUP will be filtered and the GROUP meeting the conditions will be output

- SELECT the attributes in the tuple according to the target expression in the SELECT clause to form a result table (projection)

- if the ORDER clause, the resu lt s are sorted in ascending or descending ORDER by the value of < column name expression 1 >

(1) . single table query

——Eliminate duplicate rows

 SELECT DISTINCT SD FROM S

——Query tuples that meet the criteria

Comparison size: <, < =, >, > =, =, < >

 SELECT SN,SA FROM S WHERE SD='CS'
 SELECT * FROM S WHERE SA<20

Determination range: BETWEEN... AND

 SELECT * FROM S WHERE SA BETWEEN 20 AND 21

2. Determine the set: IN

 SELECT * FROM S WHERE SD IN ('CS','IS','MA')

three Character matching: LIKE, escape character '\'

 SELECT * FROM S WHERE S# LIKE 'TB%'
 SELECT * FROM S WHERE SN LIKE 'Liu_'

4. Query involving null value: IS NULL

SELECT * FROM SC WHERE GR IS NULL

5. Multi criteria query:

 SELECT * FROM S WHERE SD='CS' AND SA<20

——Query structure Sorting

ORDER BY <Field expression> ASC|DESC 
SELECT * FROM SC WHERE C#='3' ORDER BY GR DESC

——Using set (aggregate) functions

COUNT ,SUM,AVG,MAX,MIN
SELECT COUNT(*) FROM S
SELECT COUNT(DISTINCT S#) FROM SC
SELECT AVG(GR) FROM SC WHERE S#='95001'
SELECT MAX(GR) FROM SC WHERE C#='1'

——Query grouping: GROUP BY

SELECT C#,COUNT(*) FROM SC GROUP BY C# 
SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >3

(2) . connection query

——Equivalent and non equivalent link query natural connection

SELECT S.*,SC.* FROM S,SC WHERE S.S# = SC.S#

——Self connection

SELECT f.C#, s.CP FROM C f,C s WHERE f.CP=s.C#

——External connection

SELECT S#,SN,SS,SA,SD,C#,GR 
FROM S LEFT OUTER JOIN SC ON S.S#=SC.S#

——Composite conditional connection

#Retrieve all students with elective course number '2' and score above 90 
SELECT S.S# ,SN FROM S,SC 
WHERE S.S# = SC.S# AND SC.C#='2' AND SC.GR>=90

(3) , nested query

——Subquery with IN predicate

Retrieve the information of students in the same department as "Liu Chen"

SELECT S#,SN,SD FROM S WHERE SD IN
(SELECT SD FROM S WHERE SN＝'Liu Chen')


This example can be implemented through self connection

SELECT s1.S#, s1.SN, s1.SD FROM S s1, S s2
WHERE s1.SD = s2.SD AND s2.SN='Liu Chen'

——Subquery with comparison operation

When it is determined that the return value of the subquery is unique, the comparison operation can be used
 Symbol (note that the subquery is after the comparison symbol)

SELECT S#,SN FROM S WHERE SD=
(SELECT SD FROM S WHERE CN='Liu Chen')

——Subquery with ANY and ALL (used when the subquery returns multiple values)

Search the list of students younger than any student in IS department in other departments

SELECT S＃,SN FROM S WHERE SA < ANY
(SELECT SA FROM S WHERE SD＝'IS')
AND SD<>'IS'
ORDER BY SA DESC
 Equivalent to
SELECT S＃，SN FROM S WHERE SA < 
(SELECT MAX(SA) FROM S WHERE SD＝'IS')
AND SD <> 'IS'
ORDER BY SA DESC

——Subquery with EXISTS (no data is returned, only True and false are returned)

Retrieve the names of all students with elective course number '1'

SELECT SN FROM S WHERE EXISTS
(SELECT * FROM SC WHERE S# = S.S# AND C# = '1')

Note: the query criteria of the sub query in this example depend on the outer parent query, which is called related subquery. 

Equivalent connection implementation:

SELECT SN FROM S,SC WHERE S.S# = SC.S# AND C# = '1'

Note: there is no (any x)p in SQL, so it must be converted to ¬ (there is x(¬ p)), and p - > q should be equivalent to ¬ p ∨ q

(4) , set query

INTERSECT,UNION,MINUS

Retrieve the student number of elective course C01 or C02

SELECT S# FROM SC WHERE C＃＝'C01'
UNION  SELECT S# FROM SC WHERE C＃＝'C02'
Equivalent to:
SELECT S# FROM SC WHERE C# IN ('C01'，'C02')

4, Data update for SQL (DML)

(1) . data insertion

——Insert a single tuple

insert into <Table name> <Column name 1>,... values(<'Constant 1'>，<'Constant 2'>);

——Insert subquery results

insert into <Table name> <Column name 1>,... <Subquery>

(2) . data modification

update <Table name> set <Listing> = <expression> where<condition>

——Modify the value of a tuple

UPDATE S SET SA=22 WHERE S# ='S001'

——Modify the value of multiple tuples

UPDATE S SET SA＝SA＋1

——Modified statement with query

Set all students in the computer science department to zero

UPDATE SC SET GR=0
WHERE 'CS' = (SELECT SD FROM S WHERE S# = SC.S#)

(3) . data deletion

delete from <Table name> where <condition>

——Delete the value of a tuple

delete from S where S# = 'S001

——Delete values of multiple tuples

delete from SC

——Delete statement with query

Delete the course selection records of all students in the computer science department

DELETE FROM SC WHERE 'CS'=(
SELECT SD FROM S WHERE S#=SC.S#)(related sub query) 
DELETE from SC where S# in 
(SELECT S# from S where SD = 'CS') (unrelated subquery)

5, View

(1) Define view

1. Create a view

create view <View name> (<Column name 1>,...)
as <Subquery> with check option
with check option Indicates that the sub query criteria are automatically verified when the view is updated

Create a view that reflects the date of birth of the student

    CREATE VIEW BT_S(S＃，SN，SB)as
    SELECT S#, SN,2003－SA FROM S

Establish a view of student number and average score

    CREATE VIEW S_G(S＃，AVG_GR)AS 
    SELECT S#, AVG(GR) FROM SC GROUP BY S#

2. Delete view

drop view <View name>

(2) , query view

——The query of the view is transformed into the query of the basic table, which becomes the resolution of the view

SELECT S＃，SA FROM IS_S WHERE SA <20

Resolved as:

SELECT S# ,SA FROM S WHERE SD='IS' AND SA <20

SELECT * FROM S_G WHERE AVG_GR>90

Resolved as:

SELECT S#, AVG(GR) FROM SC WHERE AVG(GR)>90 GROUP BY S#  (error)
SELECT S#, AVG(GR) FROM SC GROUP BY S# Having AVG (GR) > 90 (correct)

(3) , update view

——View modification

Change the name of the student with No. S001 in the view of information department students to 'Liu Chen'

UPDATE IS_S SET SN='Liu Chen' WHERE S#='S001'

The view resolution is:

UPDATE S SET SN='Liu Chen' WHERE S#='S001'AND SD='IS'

——View insertion

INSERT INTO IS_S VALUES ('S001','Liu Chen',20)

View resolution:

INSERT INTO S VALUES ('S001','Liu Chen',NULL,20,'IS')

——View deletion

Delete records in the information department student view

DELETE FROM IS_S WHERE S#='S001'

View resolution:

DELETE FROM S WHERE S#='S001'AND SD='IS'

Note: update of view rules is not allowed

Export from more than two basic tables
The fields of the view are from constants or expressions. Only delete is allowed
The fields of the view come from the set function
The view contains group by
The meaning in the view is distinct
The view definition consists of nested queries, and the inner query involves exporting the basic table of this view
Updating a view defined on a view is not allowed

(4) Purpose of view

Simplify user operations
Enable users to view the same data from multiple perspectives
It provides a certain logical independence for reconstructing the database
Secure data

6, Data control language (DCL)

(1) . authorization

grammar

    GRANT {ALL PRIVILEGES <jurisdiction>}
    [on <object type><Object name>]
    TO {PUBLIC <user>,...}
    WITH GRANT OPTION

example

    GRANT SELECT ON TABLE S To USER1
    GRANT ALL Privileges ON TABLE S,C TO U2,U3
    GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION

(2) . withdraw authority

grammar


 REVOKE {ALL PRIVILEFES <jurisdiction>}
 ON <object type><Object name>
 FROM {PUBLIC<user>，..}

Example

REVOKE SELECT ON TABLE SC FROM PUBLIC;
REVOKE UPDATE(SD),SELECT ON TABLE S FROM U4;
REVOKE INSERT ON TABLE SC FROM U5;

7, Embedded SQL language

SQL language is non procedural, and most applications are procedural. Therefore, high-level language is used to make up for the deficiency of SQL process control.
Embedding SQL into high-level language for execution is called embedded SQL language

(1) General forms of embedded SQL

For the processing of ESQL, DBMS generally has two processing methods: precompiling, modifying and expanding the host language to process SQL
General form of ESQL: exec SQL < SQL statement >
ESQL is divided into two categories according to its different functions:
- Executable statement
- Descriptive statement

(2) Communication between embedded SQL statement and main language

The communication between the database work unit and the main language work unit is
- Pass the execution status of the SQL statement to the main language
- The main language provides parameters to SQL statements
- Submit the SQL statement query database results to the main language for further processing
The corresponding is realized through SQLCA, main variable and cursor
SQL communication area
- SQLCA is a data structure that defines statements
```
EXEC SQL INCLUDE SQLCA
```
- SQLCODE reflects the results of each SQL statement execution
Main variable
- Main functions: ESQL can use the variables of the main language to input and output data
- Classification: input, output main variable, indicator variable
- usage method
  - All main variables are defined in the definition area
  - It can appear anywhere in the SQL expression
  - In the SQL statement, add ';' before the main variable, There is no need to add in languages
  - The indicator variable is used to assign a null value to the input variable or to indicate whether the output variable is null
cursor
- Reason for use: SQL statements are set oriented, while the main language is record oriented
- Division of labor between main language and SQL language
  - SQL language is responsible for dealing directly with the database
  - The main language is used to control the program flow and further process the execution structure of SQl
  - SQL language uses the main variable to accept the execution parameters from the main language operation database - > the execution status of SQL language is sent to SQLCA by DBMS - > the main language takes out the status information from SQLCA, and then determines the next operation
  - The execution result of SQL is passed to the main language for processing through the main variable or cursor

(3) SQL statements without cursors

Descriptive statement

EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECALRE SECTION;
EXeC SQL END DECALRe SECTION;

Data definition statement

EXEC SQL CREATE TABLE S(
    S# char(10),
    SN char(10),
    SS char(2),
    SA int,
    SD char(5)));
    EXEC SQL DROP TABLE;
    EXEC SQL DROP TABLE :tablename;(Error, main variable not allowed)
)

Data control statement

to grant authorization: EXEC SQL GRANT SELECT ON TABLE S TO U1;
Connect to database: EXEC SQL CONNECT :user IDENTIFIED BY :pass USING :tnasname;
Error handling: EXEC SQL WHEREVER SQLERROR do sql_error()

The query structure is a single record SELECT statement
- grammar
```
EXEC SQL SELECT <Target expression>,.. INTO <Primary variable 1><Indicator variable 1>,.. FROM <Table name or view name>,... WHERE/GROUP BY/  HAVING/ ORDER BY..
```
- example
```
EXEC SQL SELECT S#,SN INTO :sno,:sn FROM S WHERE S# =: GibenSno
```
- be careful
  - Main variables can be used in into,where and having statements, which need to be declared in advance.
  - When a column of return value is NULL, the system will assign the indicator variable to - 1, and the main variable remains unchanged
  - For the records whose query results meet the conditions, the DBMS sets the sqlcode value to 100, and the normal result is 0
  - If there are more than one result and the program is wrong, the SQLCA contains the play information

UPDATE statement in non CURRENT form

EXEC SQL UPDATE SC SET GR = GR+:Raise WHERE C# = 'C01';
EXEC SQL UPDATE SC SET GR=:newgr WHERE S# = 'S001';
Grid = -1;
EXEC SQL UPDATE SC SET GR =:newgr :grid WHERE S# IN (SELECT S# FROM S WHERE SD = 'CS')

DELETE statement in non CURRENT form

   EXEC SQL DELETE FROM SC WHERE S# IN (SELECT S# FROm S WHERE SN=:name)

INSERT statement

grid = -1;
EXEC SQL INSERT INTO SC VALUES(:sno, :cno, :gr, :grid);

(4) SQL statements using cursors

The query result is a SELECT statement with multiple records
- Cursors bridge the gap between the set of SELECT statements and the main language's ability to process only one record at a time
- Cursor steps:
  - Description: exec SQL declare < cursor for > cursor for < select statement >
  - Open cursor: execute the corresponding query, put the result into the buffer, and point the pointer to the first record exec SQL open < tag name >
  - Read the current record and push the cursor pointer exec SQL fetch < tag name > into < main variable > < indicator variable >
  - Close cursor: exec SQL close < tag name >
UPDATE and DELETE statements in the form of CURRENT
- Operation steps
- Description: exec SQL declare < cursor for < select query > for update of < column name >
- OPEN cursor
- FETCH cursor
- Check whether you want to modify or delete
- Finished processing CLOSE cursor

(5) Dynamic SQL statements

If the following information cannot be obtained during precompiling, dynamic SQL technology must be used. Unknown information may include:
- SQL statement body
- Number of main variables
- Primary variable data type
- Data object referenced by SQL statement

8, Stored procedure

grammar

create procedure [owned.]procedure_name as<SQL_statements>

Language elements
- Statement block
```
begin <statement block> end
```
- variable
  - Those starting with @ are user variables and those starting with @ @ are global variables. Define variable: declare
- Condition control
  - if else
- Cycle control
  - while break continue
- Sequential control
  - label: goto label
- Return value
  - return
- Print information
  - print
- implement
  - excute
example

CREATE PROCEDURE get_gr @sno varchar(10), @GR int OUTPUT 
AS
DECLARE @cno varchar(5)
BEGIN
   SELECT top 1 @cno=C#,@GR=GR FROM SC WHERE S# = @sno
   IF (@cno ='C01')
      select @GR=@GR+1
   ELSE
     select @GR=@GR+2
END

Execution:

    declare @gr int
    execute get_gr 's001',@gr  output 
    select @gr or print @gr

Topics: C Database

Programmer Think