Basic tutorial 4: database language SQL

The previous sections introduce relational algebra, relational design theory, and high-level concept representation. This section describes the SQL language.

Currently, mainstream databases such as MySQL and Oracle only implement the SQL standard and have extensions. For details, you need to query the documents of each database. This section is just an introduction to SQL.

MySQL documentation: http://dev.mysql.com/doc/

Oracle document library: http://www.oracle.com/technetwork/cn/indexes/documentation/index.html

6. SQL Query Language

6.1 simple query

A typical query is in the select-from-where format. The from clause provides the relationship referenced by the query. The WHERE clause is used to provide selection conditions. Select determines which attributes should be displayed.

First, an example is provided: Select title as name, length as duration from movie where studioname = 'disene' or studioname like '% Ney %' order by duration;

From the perspective of relational algebra, select is used to project the relational field. This field can be renamed using. You can also project constant expressions or attribute expressions based on extensions.

Use order by <ATTR,...> to sort the results. The default value is ASC, Which is ascending. You can also add DESC to sort the results in descending order. After any statement, order by sorts the results of from and where, and then applies select. The list after order by can also contain expressions.

The where clause is used to implement the selection operation. conditions can be combined by the logical operators "and", "or" and "not", which are "and", "or" and "not.

In addition, SQL provides pattern matching for string comparison. The usage is like 'pattern'. The pattern is a string that may use % or _, and % can match any string of any length in the source.

SQL allows the attribute to have a special value of null. In the WHERE clause, there are two important rules for calculating NULL: first, if null and any value are used for arithmetic operations, the result is still null. Second, when a comparison operator is used, null is compared with any value and the result is unkonwn. If you want to determine whether an attribute is null, you need to use the keyword is null or is not null.

The comparison of null will generate the bool value of unkonwn, which is in parallel with the normal true/false values. We can regard true as 1, false as 0, and unkonwn as 0.5. The rule is as follows:

First, the and operation between two boolean values takes the smallest value of the two. For example, the comparison result of "false" is "false", while that of "true" and "unkonwn" is "unkonwn.

Second, the OR operation between two boolean values obtains the maximum value of the two values.

Third, the non-of the Boolean value x. That is, trun and false can interact with each other, while unknown is not itself.

6.2 multi-link queries

SQL uses a simple method to process multiple links in a query, that is, to list each link in the from clause, then, any attribute that appears in the from clause is referenced in the select and where clauses.

Example: Select name from movie as M, moveexec as E where M. Title = 'Star war 'and M. produceno = E. certno

When a query involves multiple links, the link may have duplicate attributes. In this case, we need to explicitly specify the link attribute we selected. SQL solves this problem by adding a link name and a vertex before the attribute.

In relational algebra, a set operation can be used to combine a link. The corresponding operations in SQL are applied to the query results. Keyword union, intersect, and except T correspond to U, M, and-respectively ,-. When this keyword is used for two queries, the query should be enclosed in parentheses.

6.3 subquery

When a query is a part of another query, it is called a subquery. Subqueries can also have subqueries at the next level, so they are recursive. The preceding section describes how to merge the results of two subqueries by using a set operation. The following describes three new subqueries.

6.3.1 The subquery returns a single value for the WHERE clause.

You can use this type of query in the WHERE clause of the upper-level query.

For example, select name from moveexec where certno = (select produceno from move where title = 'Star war ');

6.3.2 subqueries for returned relationships are used in the WHERE clause

SQL defines the following operators to act on the relationship and return the bool result.

S in R if s exists in R

Exist R link R is not empty

S> All r indicates S> MAX (r)

S> Any R indicates S> min (r)

Based on the above definition, if a tuples and a link have the same number of attributes, you can use the above operator to combine subqueries. For example:

Select producerno from movies where (title, year) in (select mtitle, myear from starsin where startname = 'harrison Ford ');

The subquery described above only needs to be calculated once, and the returned results are returned to the upper-layer query. More complex queries require that subqueries be computed multiple times. Each time an external tuple variable is transferred from the upper layer to a subquery, this type of query is called an associated subquery.

Select title form movies as old where year <Any (select year from movies where title = old. Title );

6.3.3 subqueries of return relationships are used in the from clause

The returned link must have a name for the from clause. For example, select title from movies, (select * from movies) as old where old. Title = title and old. year <year;

6.3.4 SQL connection expression

The join operators described in this section are redundant and can be replaced by the Select from where expression. The simplest connection is the Cartesian product, but it is rarely used in the form of R cross join S. Corss can be omitted.

θ join form: R join S on <condition>

Example: Select * from movies join starsin on Title = mtitle and year = myear;

6.3.5 θ Outer Join

Format: R [left | right] Outer Join s on <condition>

6.3.6 natural connection

Natural connection form: R natural join S

Because the auto-join behaviors are connected to attributes with the same name and the same value, no additional conditions are required. Therefore, when the title and year fields of the two tables have the same name, the preceding example can be simplified:

Select * from movie natural join starsin.

MySQL uses inner join to represent a natural join, but you can use using to specify the columns used. In addition, MySQL determines the order of loading tables by itself during internal connections. You can also specify the loading order. This requires the MySQL extension connection keyword straight_join. Its syntax is r straight_join s on.

6.3.7 external natural connections

External natural connection form: R natural [left | right] Outer Join s

Example: Select * from movies natural left join starsin;

6.4 full Link Operations

6.4.1 eliminate duplicates

If you want no duplicate tuples to appear in the result, you can use select distinct. However, it is costly to eliminate duplicates. Only in sorting or grouping can the same tuples be placed together.

Unlike select, union and other set operations do not eliminate duplicates by default. If you want to retain duplicates in the set operation, you need to add the all keyword after the set operator.

We can also repeat messages from the clustering operator by adding distinct to the column in the clustering operator, so that we can eliminate duplicates before the clustering operator.

6.4.2 Group

Group by can be used to group and aggregate columns. Note the following rules when the tuples contain null values:

NULL values are ignored in any clustering operation. Except for the count, empty packets are clustered and the result is null. The empty package count is 0.

6.4.3 having clause

If you want to select a group based on the clustering properties of some groups, you can add the having clause after gourp by, and the having clause is followed by the condition of a group. Indicates that the Group must meet the given conditions before it can be returned as a result.

For example, list the number of movies produced by producers who have made movies before 2000.

Select name, count (title) from movie, movieexec where movie. 'producerc # '= movieexec. 'cert # 'group by name having min (year) <2000;

6.5 database updates

6.5.1 insertion of tuples

Insert into R (A1, A2 ,... an) values (V1, V2 ,... the attribute list can be omitted. In this case, the attribute values must be in the same order as the Standard Order of The Link property. You can insert a computed set of tuples into a link through a subquery.

For example, insert into movies values ('Star war ', 1980,120, 'pop ').

6.5.2 Delete

Delete from R where <condition>. If no condition exists, all tuples are deleted.

6.5.3 update

Update R set <Col = Val,...> where <condition>.

Transaction 6.6

First, we will introduce the concept of serializability. In order to support transaction concurrency, transactions must be serializable. That is, these transactions must be executed serially without overlapping parallelism.

In addition to serialization, the other concept is atomicity, that is, the transaction is either executed successfully or not, and there is no execution part of the problem.

6.6.1 submit a transaction in SQL

SQL supports transactions. Start transaction is used to identify the start of a transaction, and commit is used to commit a transaction or rollback a transaction.

Generally, multiple read transactions can be executed concurrently, but the specific situation must be considered when writing a transaction. SQL allows you to tell the system whether the transaction to be executed is read-only or read/write.

SET transaction read [Only | write]

6.6.2 dirty read and isolation level

Dirty data is a general term for data written by a transaction that has not been submitted. Dirty reading refers to reading dirty data. The risk of dirty reading is that the transaction writing data may fail, so the data read is invalid. Dirty reads are sometimes important, and sometimes dirty reads are irrelevant. Dirty reads can improve performance.

The SQL device has four isolation layers. By setting the transaction isolation layer, we can control how to process dirty data.

Isolation	Dirty read	Non-repeated read	Phantom read
Read uncommitted	Y	Y	Y
Read committed	N	Y	Y
Repeatable read (default)	N	N	Y
Serializable	N	N	N

Set the isolation level for transactions. For example, SET transaction isolation level read commited.

For read submission layers, SQL allows the same query to be executed multiple times and obtain different results. As long as the result reflects the data that has been committed for transaction write.

For a Repeatable read level, if a single tuples are retrieved for the first time, the same tuples will be retrieved again after this query is repeated. However, phantom tuples may be encountered in subsequent operations. For example, when other transactions are inserted into the new Meta Group, and this transaction also wants to insert the same new Meta Group, the conflict arises, and the phantom tuples suddenly "appear.

At the serial level, the transaction can be executed only after the previous execution is completed.

7 constraints and triggers

7.1 key and foreign key constraints

7.1 create a foreign key

We have seen two primary key definition methods in Section 2. Foreign keys are similar to each other, and there are also two definition methods:

Foreign key on a single attribute: Add reference <Table> (<ATTR>) after the attribute Declaration)

If the foreign key has multiple attributes, you must append the declaration of foreign key (<ATTR list>) Reference <talbe> (<Table ATTR list>) after the attribute list of the create table statement)

Example: Create Table Studio (name char (20) primary key, presc # int, foreign key (presc #) Reference movieexec (CERT #));

7.1.2 maintain integrity of reference

When a foreign key constraint is violated, there are three different processing methods:

First, reject illegal operations by default;

Second, in the cascade principle, changes to referenced attributes are applied to foreign keys.

Third, if null is set, the update on the referenced link affects the foreign key, and the foreign key is set to null.

The three options can be deleted and modified independently and declared together with the foreign key. The declaration method is to add the set null or cascade option after on delete or on update.

Example: Create Table Studio (name char (20) primary key, presc # int, foreign key (presc #) Reference movieexec (CERT #) on Delete set null on update set cascade );

7.1.3 check of latency Constraints

7.2 attributes and tuples Constraints

7.2.1 non-empty Constraint

The simple constraint connecting to the attribute in create table is not null, which does not allow NULL for this attribute of tuples.

7.2.2 attribute-based check Constraints

A more complex constraint is to attach the condition enclosed by the reserved word check and parentheses to the attribute declaration. This condition must be met for each value of this attribute. In principle, the check condition can be any description that is allowed in the WHERE clause. The check constraint is checked when the tuples obtain new values for this attribute. If this attribute does not change, the check is not executed.

Example: Create Table..., Gender char (1) Check (Gender in ('F', 'M '))

7.2.3 check Constraints Based on tuples

If check involves multiple attributes, You need to declare check after the create table attribute definition, which is similar to a key constraint.

Example: when adding a movie star, if it is a male, it cannot start with Miss.

Create Table moviestar (name char (30) primary key, Gender char (1), check (Gender in ('F', 'M') or name not like 'Ms. % ')

7.3 constraint Modification

7.3.1 constraint naming

Add the constaint and constraint name before the constraint.

For primary key: Name char (30) Constraint name_pk primary key

Constraints: Gender char (1) constaint no_mr check (Gender in ('F', 'M '))

7.3.2 modification of constraints

After naming constraints, you can modify them according to their names.

Constraint deletion: alter table tbl_name drop constraint cons_name;

Constraint addition: alter table tbl_name add constraint cons_name check/primary key ()/foreign key ()

7.4 assertions

Create assertion <Name> check (<condition>)

Drop assertion name

MySQL does not support assertions and is not described here in detail.

7.5 trigger

8 views and Indexes

Generally, a view is a virtual video, which is not stored but can be queried as if it exists in a database. Views can be materialized and need to be constructed and stored on a regular basis. An index is a materialized view.

8.1 virtual view

In SQL, the visual view is defined as create view <viewname> as <SELECT statement>, that is, the view is actually a virtual storage of a query result for later use. After a view is defined, it can be used in the from and where clauses like a real relational table. You can also rename the attribute when defining a view by adding a pair of parentheses after the view name to enclose the attribute in the brackets.

Example: Create view disneymovie (film, year) as select title, year from movies where studioname = 'disene ';

Delete view: Drop view viewname; Delete view does not affect the data of the original table.

SQL specifies that some simple views can be inserted, deleted, and updated, which we do not describe here.

8.2 SQL index and selection

8.2.1 create and delete Indexes

The attribute index in a link is a data structure that improves the query efficiency of the corresponding attribute. The common implementation is the binary search tree, and the key is the possible value of the attribute, the value corresponding to the key is the storage location of the corresponding group. Indexes can greatly increase the search speed of corresponding columns. If no index is available, we have to traverse all the tuples to filter out the matching parts.

Index creation Syntax: Create index idx_name on R (column ,...), create an index on the corresponding column of the relational R. You can perform a joint index on multiple attributes. Pay attention to the order of the attribute list.

Index deletion: drop index idx_name

In addition to the index creation method described above, another common index creation method is to declare the corresponding columns as keys when creating a table, so that DBMS will create indexes for the corresponding columns by default.

8.2.2 selection of Indexes

Indexes not only speed up search, but also speed up operations when the corresponding link is connected. However, index maintenance is costly. When the corresponding column has a new Meta Group inserted, deleted, and modified, you must update the index.

The most useful index in the link is the primary key creation. Secondly, if some attributes are not keys but can be used as keys, you can also create indexes. Finally, if the tuples are clustered in certain attributes, you can add an index for this attribute.

In order to calculate the optimal index, it is necessary to count the attributes based on the relationship that query and update occur most frequently. After sorting the frequency, adding indexes from high to low will bring us the greatest efficiency.

8.3 Materialized View

The SQL standard supports materialized views, but it is not supported in MySQL.

Materialized View creation: Create materialized view viewname as <select ......>

Update of materialized views does not need to be performed when the basic table changes. It can be executed offline when the database is not busy.

Views and materialized views simplify query statements.

9 Stored Procedure

10 permission management

Authorization: grant <permission list> On <dB element> to <user> [with grant option]

Authorization: revork <permission list> On <dB element> from <user> [cascade | restrict]

DBMS uses an authorization diagram to track the granted permissions.

References

MySQL isolation level http://blog.csdn.net/taylor_tao/article/details/7063639