Database Review 2--sql Foundation

Database Review CH4 SQL

SQL (structured query Language, Structured Query language) is a common relational database system operation language, the following from several aspects to review the SQL Foundation

4.1 DDL

SQL statements can be divided into three categories depending on the nature of their operations:

• DDL (Data Definition Language)
• DCL (Data Constraint Language)
• DML (Data manipulation Language)

DCL to complete the integrity and security constraints can also be considered to belong to the DDL, the following describes the DDL section of the SQL statement except the DCL

DDL can define three structures: tables, views, and indexes, and view views will be described in a special section later

The SQL Build Table statement is simple:

create table <table_name>(<{attribute_name type}+>);

When creating a table, you can declare keys, add constraints, and so on in addition to the attribute list (see completeness)

Create TABLE is very simple, the following main review index

(1) Index

An index is a structure that accelerates the retrieval of data in a table, and the index is created by the user but is used by the DBMS (using transparent to the user), and the following is the SQL statement that creates the normal index:

<index_name><table_name>(<{attribute_name}+>);

The index is created for the attribute student_id of table student, so when querying student_id=pb12210134, you do not traverse the entire table but quickly find by index and index-optimized lookup algorithm

You can think of an index as a data structure-optimized key-value pair table, where the key is the value of the attribute, such as the number PB12210134 above, and the value is a pointer to a tuple in the table, and some optimizations (such as hash) can accelerate the lookup of key-value pairs

Indexes can be divided into 4 categories:

• Normal index
• Unique index
• Primary key Index
• Clustered index

Unique index , create unique index ... which does not allow any two rows in the table to have the same value, generally we do not directly create a unique index, but rather by declaring a primary key to implicitly create a special case of a unique index-- primary key index

The primary key index is the index that the DBMS automatically creates when the primary key is created, and the unique index and the primary key definition (see completeness), which does not allow any two rows in the table to have the same primary key

A clustered index is a "physical index" that allows tuples to be arranged in the order in which they are physically stored, so that they can effectively reduce I/O time and create clustered indexes when queried:create cluster index ...

It is important to note that the index is not a matter of profit and no harm:

• Creating an index consumes time and is proportional to the amount of data
• Storage indexes need to occupy physical space, building clustered index physical space needs greater
• Reduce data maintenance time by dynamically maintaining indexes when adding, modifying, and deleting data in tables

4.2 Basic Query

The syntax for the SQL base query statement is:

select A1,A2,...,An from t1,t2,...,tm where P;

(1) SELECT clause

The SELECT clause lists the properties required by the query (SQL is case insensitive), and select completes project operations (see Database Review 1)

The DBMS does not purge redundant data, and if a select-out attribute is required, no duplicates are required to declare the keyword distinct:select distinct a1 from t1

Properties can be manipulated when select, such as subtraction operations on numeric properties

(2) WHERE clause

The WHERE clause lists the assertions about the attributes in the relationship, and the function is to select the attributes that satisfy the requirement condition, where the restrict operation is completed

The condition p that follows the WHERE clause can be a compound condition that joins the logical join Word and, or, and not, and the relational operator can be =, ~=, or the size of the numeric comparison (and so on), such as the following statement:

select name from student where department = ‘computer science‘ and age > 20;

A special WHERE clause is a between A and B clause, which means greater than or equal to a and less than or equal to B, not commonly used

(3) FROM clause

The FROM clause lists the relationship/table to be scanned by the SELECT statement, with multiple tables in the clause, often with the where statement to complete the join operation:

selectfromwhere student.d# = dept.d#;

This SQL speaks of d# as a connection point join Student table and Faculty table

From multiple tables We often use renaming to distinguish between duplicate attributes or for shorthand expressions, and attributes and table names can be named with the AS keyword, such as:

select s.nameas sname, d.nameasfromasaswhere s.d# = d.d#;

C.j.date the renaming of relationships in the book is also called a tuple variable (tuple Variables)

4.3 Advanced query

select from whereis the most basic SQL query statement, based on which SQL also supports a variety of advanced extended query operations

(1) Serial operation

A Where condition can be set by using the LIKE keyword Mate string match, SQL supports two matches:

• % percent sign: percent sign matches any string (wildcard character)
• _ Underline: Underline matches any but character

For example, you can use the string matching operation to select the USTC12 class students:

select name from student where student_id like ‘PB12%‘;

There are many string operations, such as connection, case conversion, string length, intercept substring, etc. (not very common)

(2) Sort

SQL assign keyword ORDER by to support sorting the results of a SELECT, you can specify ascending ASC or Descending desc, by default ascending order

To give an example of a USTCCS12 class that requires a grade of GPA in order to allocate an insurance quota:

select name, GPA from student where student_id like ‘PB12%‘ and d# = ‘011‘ order by GPA desc;

(3) Set operation

SQL supports the following three sets of operations to produce intermediate results for the SELECT statement to be processed again:

• Union set
• Intersect to seek the intersection
• Except seeking difference set

The collection operation is automatically drained, and the Union does not care whether the type of the attribute matches, just the number of union attributes is guaranteed, as in the following strange union:

(select name, student_id from student) union (select d#, name from dept) 

This sentence can be executed, the resulting header is name and student_id, but the table has a yuan (d#, name) tuple

(4) Aggregation function

An aggregate function is an operation on the full value of one of the properties of the result of a select, and SQL supports five aggregation operations:

• AVG Averaging
• Min to find minimum value
• Max asks for maximum value
• Sum sum
• Count to find the number

For example, statistics USTCCS12 class on the number of students from the EE transferred:

select count(*) from student where d# = ‘011‘ and student_id like ‘PB1221%‘;

(PS: The answer is three)

The aggregation function also has a special action group by grouping that aggregates the aggregate function values by grouping by attribute, such as the following: We count the number of people in each department at level 12.

select d#, count(student_id) from student where student_id like ‘PB12%‘ group by d#;

Note that the properties of group by must appear in the properties of select

SQL statements that use a GROUP BY clause can also be added to the filter after grouping: Have a clause, need to pay attention to

• Having clauses are executed after grouping, and WHERE clauses are executed before grouping
• The HAVING clause can only restrict the aggregation function, while the where statement cannot restrict the aggregation function

For example, the following is a 12-level SQL statement for each department with more than 100 people:

select d#, count(student_id) from student where student_id like ‘PB12%‘ group by d# having count(student_id) > 100;

(5) Null empty value

When the relational database satisfies the integrity constraints, it supports a null null value for a property of a tuple, which has the following characteristics:

• Arithmetic operations acting on NULL get NULL
• Aggregate function ignores tuples with an aggregate property of NULL
• The WHERE clause asserts that a property is NULL with IS NULL
• A comparison that involves null returns a unknown
• Unknown can participate in logical operations, and the return value is consistent with its logical meaning, such as unknown and True = True, while unknown and false = Unknown
• For unknown conditions, P,dbms thinks it's false.
• Special Note that count (*) does not ignore unknown tuples

(6) Embedded sub-query

The embedded subquery is the result of filtering out the SELECT statement as an intermediate result, and the SQL statement that is further queried on this basis

where attr in (subquery)Is the most common use, for example, in the Dept table to find the Department of Engineering departments number d#, on this basis, then to count the number of departments, the following SQL statement:

select d#, count(student_id) from student where student_id like ‘PB12%‘ and d# in (select d# from dept where type = ‘engineering‘) group by d#;

The some and all qualifiers can be filtered in the WHERE clause with the relational operator, which is the classic example of the book:

Find employees with a higher salary than some (some) managers:

select ename from emp where salary > some (select salary from emp where e# in (select mgr from dept));

Find employees with a higher salary than all managers:

select ename from emp where salary > all (select salary from emp where e# in (select mgr from dept));

In addition, the WHERE clause can also be used exists and unique to do the existence and uniqueness of the test, the example, with not can also indicate that there is no (not exists) and not unique/at least two (not unique)

Note: Many DBMS also support embedded subqueries appearing in the SELECT clause and in the FROM clause

4.3 Modification actions (1) Delete

Delete a tuple that satisfies a condition:

delete from <table_name> where P;

The pattern of the modification operation is not as large as the query, but many advanced query syntax can also be used in the modification operation, such as the in of nested select, the comparison applied to the WHERE clause medium

Deleting a statement can cause data integrity to be compromised, such as removing the Jcguo the student has Jcguo results in the syllabus, which are described in the completeness constraints

(2) Insert

Insert a tuple:

insert into <table_name> values (completed attr list of tuple);orinsert into <table_name>(some attrs) values (matched partial attr list of tuple);

The value of the insert can also come from the absconded select subquery statement:

...);

(3) Update

The basic SQL syntax for update operations is:

update <table_name> set {attr = new attr value}+ where P;

4.4 Embedded SQL

Many high-level languages support SQL, and multi-cursor +execute SQL command interfaces are available to programmers (see Chapter Plsql)

4.5 Functions in SQL

Finally, here's a brief review of the built-in extension functions in SQL

It should be noted that these functions are rarely used, because most of these operations are given to SQL embedded external high-level language to complete, here is a table for review review

(1) Character function

Name of function	function Description
Lower (attr|string value)	Convert attribute attr or string literal value to lowercase
Upper (attr|string value)	Convert attribute attr or string literal value to uppercase
Initcap (attr|string value)	Capitalize the first letter of the attribute attr or string literal value (by sentence)
Concat (V1,V2)	Connect two attributes attr or string literal value
Lpad (COL|VALUE,N,C)	Left-padded v to n characters, if C is not empty then fill with C or fill with a space
Rpad (COL|VALUE,N,C)	Right fill v to n characters, if C is not empty use C padding otherwise fill with a space
SUBSTR (Col|value,pos,n)	Intercept the substring, intercept n bits from the POS
InStr (Col|value,c,pos,n)	Find the position where the character appears, POS is the start lookup bit, find the nth occurrence of the character C
Length (Col|value)	To find the string length

(2) Numerical functions

Slightly

(3) Date function

Slightly

(4) Conversion function

Slightly

Note: This section does not feel very useful, it does not summarize

Database Review 2--sql Foundation