3.1 Overview of SQL Query Language
The American National Organization for Standardization (ANSI) and the International Organization for Standardization (ISO) publish SQL standards. The SQL language has several parts:
- Data-definition Language (DATA-definition languageDDL) defines, modifies, and deletes relational schemas
- Data-manipulation Language (DML), which queries information, inserts tuples, modifies tuples, and deletes tuples
- Integrity. The SQL DDL contains commands to define integrity constraints that must be met by data stored in the database
- View definition, the SQL DDL contains commands to define a view
- Transaction Control. SQL includes commands that define the start and end of a transaction
- Embedded SQL and Dynamic SQL, define how SQL is embedded in a general-purpose programming language
- Authorization, which defines access to relationships and views
The SQL features described in this chapter are based on the SQL-92 standard
3.2 SQL Data Definition
3.2.1 Basic Types
- Char (n) : a string of fixed length. The length is specified by the user. You can also use the full name character
- Varchar (n) : a string of variable length. The maximum length is n, which is equivalent to the full name of character VARYING
- Int: Integer type (finite self of machine-dependent integers), equivalent to full integer
- Unmeric (p, d) : the number of fixed points, and the longitude is specified by the user. This number has p bits (plus a sign bit) where d bits are to the right of the decimal point.
- Real, double precision: Floating point numbers and double precision floating point numbers, longitude is machine-related
- Float (n) : a floating point number with at least n bits of precision
Char, which fills in trailing Spaces if the actual length is less than the specified length, whereas vARCHar does not
3.2.2 Basic schema definition
The integrity constraints are listed below:
- primary key
- Foreign Key (A1, A2, An) Reference
- not null
The order of values in the INSERT statement needs to be the same as the order of attributes in the relational table. Drop TABLE deletes the entire relational table, while DELETE table deletes only the records in the table
3.3 Basic structure of SQL query
The abridged structure of an SQL query consists of three clauses: SELECT, FROM, and WHERE. The relationship listed in the FROM clause for the input to the query
3.3.1 Single-relationship Query
SQL allows duplication in relationships and SQL expression results. If you want to forcibly delete duplicates, add distinct after select
Select DISTINCT (A1, A2, An) from tableCopy the codeThe SELECT clause can take arithmetic expressions with +, -, *, and/operators, and the operands can be properties of constants or tuples. The WHERE clause allows the selection of the logical conjunctions AND, or, and not that satisfy certain predicates in the result relation of the FROM clause. Logical conjunctions can operate on expressions that contain the comparison operators <, <=, >, >=, =, and <>. The comparison operators can be used to compare strings, arithmetic expressions, and special types, such as date types
3.3.2 Multi-relationship Query
When an attribute appears in multiple relational tables at the same time, the relational Ming is used as a prefix to indicate which attribute we are using. This convention can be problematic in some cases, such as when you need to combine information from two different tuples of the same relationship. An SQL query can report three types of clauses:
- The SELECT clause is used to list the attributes needed in the query results
- The FROM clause is a list of relationships that need to be accessed in a query evaluation
- The WHERE clause is a predicate that acts on the attributes of the relationship in the FROM clause
The order of operations is first from, then WHERE, and then SELECT.
- Produces cartesian products (possible permutations of each tuple in multiple relationships) for the relationships listed in the FROM clause
- Apply the predicate specified in the WHERE clause on the result of step 1
- For each tuple in the step 2 result, the attributes specified in the SELECT clause are printed
In a real implementation of SQL, he would optimize by (as far as possible) producing only Cartesian product elements that satisfy the PREDICATE of the WHERE clause.
3.3.3 Natural Connection
Matching conditions in the FROM clause need to be equal on all attributes of matching names in the most general case. To simplify the writing of SQL statements in this case, SQL supports an operation called a natural join. The natural join operation operates on two relationships and produces a relationship as a result. Instead of the Cartesian product over two relations, he joins each tuple of the first with all the tuples of the second; Natural joins consider only pairs of tuples that have the same value on attributes that appear in both relational patterns. To avoid the danger of unnecessary columns with the same name, SQL allows users to specify which columns are required to be equal by joining R2 using (A1, A2)
3.4 Additional basic operations
3.4.1 Rename operation
The attribute names in the result are derived from the attribute names of the relationships in the FROM clause, but there are cases where you cannot derive the name using this method.
- First, there may be a property of the same name in two relationships of the from clause.
- Second, the select clause uses an arithmetic expression, so the result attribute has no name.
- Again, you want to change the attribute name in the result.
SQL provides a way to rename attributes in a result relationship using the AS clause
old-name as new-name
Copy the codeThe AS clause can also rename relational tables for two reasons:
- Replace the long relationship name with a short one for easy use elsewhere in the query
- To apply to cases where you need to compare tuples in the same relationship, such as yourself and yourself performing a Cartesian product
The identifier used to rename a relationship is called a correlation name in the SQL standard. It is also called a table alias or a correlation variable or tuple variable.
3.4.2 String Operations
SQL uses a pair of single quotes to identify strings, or two single quote characters if single quotes are part of a string. In the SQL standard, the equality operation on strings is case sensitive. Some database systems are implemented case-insensitive, and this default can be modified at the database level or at a specific attribute level. SQL also allows multiple functions on strings, such as concatenation, extracting zichuans, calculating string length, converting case, and removing Spaces after strings to implement pattern matching:
- Percent sign (%) : Matches any substring
- Underscore (_) : Matches any character
The schema is case-sensitive and uses the escape keyword in the like comparison to define the escape character, for example
Like 'ab\%d' escape '\' matches all strings starting with 'ab % CD 'like 'ab\% CD %' escape '\' matches all strings starting with 'ab\ CD'Copy the codeSQL allows search for mismatches using the not Linke comparison operator. SQL1999 also provides similar to operations with syntax similar to regular expressions in UNIX.
3.4.3 Attribute Description in the SELECT clause
Model * indicates All attributes, and the asterisk can be preceded by the table name. Represents all attributes in the table, for example
select instructor.* from instructor, teaches where instructor.ID = teaches.ID;
Copy the code3.4.4 Arranging the display order of tuples
The order by clause defaults to ascending order, which can be denoted by DESC for descending order or asC for ascending order
3.4.5 WHERE clause predicate
The between comparison operator states that a value is less than or equal to one value and greater than or equal to another value. The not between WHERE clause allows the comparison operator to be used on tuples in lexicographical order, for example:
select name, course_id
from instructor,teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
Copy the code3.5 Set Operation
The union, INTERSECT and except operations of SQL on relation correspond to union, intersection and difference in mathematical set theory
3.5.1 track of and operation
The union automatically removes duplicates, and if you want to keep all duplicates, you need to use union all
(select course_id
from section
where semster = 'Fall' and year = 2009)
union all
(select course_id
from secition
where semestr = 'Spring' and year = 2010);
Copy the code3.5.2 delivery operation
If you want to preserve duplicates, you must use INTERSECT all instead of intersect
(select course_id
from section
where semster = 'Fall' and year = 2009)
intersect all
(select course_id
from section
where semester = 'Spring' and year = 2010);
Copy the code3.5.3 minus operation
To preserve repetition, except all must be used instead of except
(select course_id
from section
where semster = 'Fall' and year = 2009)
except all
(select course_id
from section
where semester = 'Spring' and year = 2010);
Copy the code3.6 a null value
- If any input to an arithmetic expression is null, the arithmetic expression result (+, -, *, /) is null
- SQL treats the result of any comparison operation of the design control as unknown, which creates a third logical value in addition to true and false
- In Boolean operations:
- And: true and unknown is the result of unknown, false and unknown is the result of false, unknown and unknown is the result of unknown
- Or: true or unknown is true, false or unknown is unknown, and unknown or unknown is unknown
- The result of not: not unknown is unknown
- If the WHERE clause predicate evaluates false or unknown for a tuple, that tuple cannot be added to the result set
- The special keyword NULL is used in the predicate to test for null values
- The predicate is not null is true if the value on which it operates is not empty
- Some SQL implementations also support is Unknown and is not Unknown to test whether an expression result is unknown
- In the select DISTINCT case, if the two values in the comparison are not empty and have the same value, or both are empty, the comparison is considered to be identical. In the predicate, null= NULL ranges unknown. This method applies to the union, intersection, and difference operations of sets
3.7 Aggregation Function
A function that returns a single value that takes as input a set (set or multiple sets) of values. SQL provides five built-in aggregate functions
- Average value: AVG
- Minimum value: min
- Maximum value: Max
- Synthesis: the sum
- Count: the count
3.7.1 Basic aggregation
select count(*)
from course;
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2010;
select avg(aalary)
from instructor
where dept_name = 'Comp.Sci.';
Copy the code3.7.2 Group Aggregation
Tuples with the same value on all attributes in the group by clause will be grouped into a group and any attributes that do not appear in the group by clause if they appear in the SELECT clause, It can only appear inside the aggregate function. Each teacher in a particular group (defined by dept_name) has a different ID, and since each group outputs only one tuple, it is impossible to determine which ID value to choose as the output. The result is that SQL does not allow this
3.7.3 having clause
Predicates in the having clause do not come into play until groups are formed. The qualification is for the grouping of the group by clause
select dept_name, avg(salary) as org_salary
from instructor
group by dept_name
having avg(salary) > 42000;
Copy the codeThe meaning of a query with a group by or having clause can be defined by the following sequence of operations:
- Similar to queries without aggregation, a relationship is first calculated from the FROM clause
- If a WHERE clause occurs, the predicate in the WHERE clause is applied to the result relationship of the FROM clause
- If a group by clause is present, tuples that satisfy the WHERE predicate form groups through the Group by clause. If there is no group by clause, the entire set of tuples satisfying the WHERE predicate is treated as a group
- If a having clause appears, it applies to each group. Groups that do not satisfy the HAVING clause predicate are discarded
- The SELECT clause uses the remaining groups to produce a tuple in the query result, that is, applying the aggregate function to the group to get a single result grouping
3.7.4 Aggregation of null and Boolean values
Handling null values rule: All aggregate functions except count(*) ignore null values in the input set. Specifies that the count operation of an empty set has a value of 0, and all other aggregate operations return a null value if the input is an empty set. SQL1999 Can enter three Boolean types: true, false, and unknown. There are two aggregate functions: some and every
3.8 Nested subquery
Subqueries check into select-from-WHERE expressions in another query.
3.8.1 Set Membership
Subqueries appear in the WHERE clause and test whether a tuple is a member of a collection by using the in or not in connectives.
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
course_id in ( select course_id
from section
where semester = 'Spring' and year = 2010);
Copy the code3.8.2 Comparison of sets
SQL allows <some, <=some, >=some. = < > and some some. For example: Find the names of all teachers who earn more than at least one teacher in the Biology department
select name
from instructor
where salary > some (select salay
from instructor
where dept_name = 'Biology');
Copy the codeSQL allows <all, <=all, >=all, =all and <>all, <>all is equivalent to not in, but =all is not equivalent to in example: Find the names of all teachers who earn more than each teacher in the Biology department
select name
from instructor
where salary >all (select salay
from instructor
where dept_name = 'Biology');
Copy the code3.8.3 Empty relation test
The exists structure returns true if the subquery as an argument is non-null. Example: Find all classes that were taught at the same time in the fall 2009 semester and the spring 2010 semester
select corse_id
from section as S
where semester = 'Fall' and year = 2009 and
exists (select *
from section as T
where semester = 'Spring' and year = 2010 and
S.course_id = T.course_id);
Copy the codeA related name from the outer query (S in the above query) can be used in the subquery of the WHERE clause. Subqueries that use correlated names from outer queries are called correlated subqueries. Correlated subqueries can only be used in a subquery if they are defined by the subquery itself, or in any query that contains the subquery. A definition in a subquery is valid if a related name is defined in both the subquery and the query containing the subquery. This rule is similar to the common variable action rule in programming languages. The NOT EXISTS structure tests whether tuples do not exist in the subquery result set. Set inclusion (that is, superset) operations can be simulated using the NOT EXISTS structure. “Relation A contains relation B” can be written as “not exists (B except A)”
select S.ID, S.name
from student as S
where not exists((select course_id
from course
where dept_name = 'Biology')
except
(select T.course_id
from takes as T
where S.ID = T.ID));
Copy the codeHere, subqueries
(select course_id
from course
where dept_name = 'Biology')
Copy the codeFind the set of all classes in the Biology department, subquery
(select T.course_id
from takes as T
where S.ID = T.ID)
Copy the codeFind out all the courses that S.ID has taken. Thus, the outer select tests each student to see if the set of courses he or she takes includes the set of courses starting in the Biology department
This logic feels incomprehensible!!
3.8.4 Duplicate tuple Existence Test
The unique structure is used to determine if there are duplicate tuples in the result of the subquery as an argument, and if there are no duplicate tuples, flase is returned. Example: find all classes that started at most once in 2009
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id = R.course_id and
R.year = 2009);
Copy the codeThe unique predicate evaluates the truth value on an empty set. The not unique construct tests whether there are duplicate tuples in a subquery result. Note that if there are two tuples t1 and T2 in the relationship, t1= T2 is false if one of the fields of T1 or T2 is empty. The unique test might be true
Subquery in 3.8.5 from clause
The result returned by any select-from-WHERE expression is a relation and can therefore be inserted into another select-from-WHERE where any relation can appear example: Find out which departments have average salaries of more than 4200 teachers
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
Copy the codeExample: Find the department with the largest total salary of all the departments. The having clause is not solvable using the from clause
select max(tot_salary)
from (select dept_name, sum(salary)
from instructor
group by dept_name) as dept_total(dept_name, totl_salary);
Copy the codeSQL2003 allows subqueries in the from clause to be prefixed with the keyword lateral to access properties in the table or subquery that precedes the from clause. Example: Print the name of each teacher, along with their salary and the average job in their department
select name, salary, avg_salary
from instructor I1, lateral(select avg(salary) as avg_salary
from instructor I2
where I2.dept_name = I1.dept_name);
Copy the code3.8.6 with clause
The with clause provides a way to define a temporary relationship, which is only an example for queries that contain the with clause: find the system with the largest budget value
with max_budget(value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department, budget = max_budget.value;
Copy the codeExample: Find all departments whose total salaries are greater than the average of all departments
with dept_total(dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;
Copy the code3.8.7 Standard quantum query
SQL allows a subquery to appear anywhere an expression that returns a single value can, as long as the subquery returns only a single tuple containing a single attribute; A sub-query like this is called a scalar subquery: a scalar subquery lists all the departments and the number of teachers they have
select dept_name,
(select count(*)
from instructor
where department.dept_name = instructor.dept_name )
as num_instructors
from department;
Copy the codeStandard quanta queries can appear in select, WHERE, and HAVING clauses. It is not always possible to determine at compile time whether there are multiple tuples in the result returned by a subquery. If a subquery is executed and results in more than one tuple, a runtime error is generated.
Note that the result type of a scalar quantum query is still technically a relation, although it contains only a single tuple. Then when a scalar query is used in an expression where a single value appears, SQL retrieves the corresponding value from the cell group containing a single attribute in the relationship and returns the value.
3.9 Modifying the Database
3.9.1 delete
You can delete only a single tuple, not only values on certain attributes
delete from r
where P;
Copy the codeThe delete command operates intelligently on a relationship, and if you want to delete tuples from multiple relationships, you must use a delete command on each relationship. Select-from-where can be nested in the DELETE WHERE clause
Example: Delete records of faculty whose salaries are below the university average
delete from instructor
where salary < (select avg(salary)
from instructor);
Copy the codeIt is important to test all tuples before deleting them. In the above example, if some tuples are deleted before the rest of the tuples are tested, the average salary will change, so that the final result of delTE will depend on the order in which the jacks are processed.
3.9.2 insert
You can specify tuples to be inserted, or you can write a query statement to generate a collection of tuples to be inserted. Given that users may not remember the order in which relational attributes are arranged, SQL allows you to specify attributes in insert statements
insert into course(course_id, title, dept_name, credis)
values('CS-437', 'Database System', 'comp Sei', 4);
Copy the codeFor example, let each student of the Department of Music who has completed 144 credits become a teacher of the Department of Music, with a salary of $18,000
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_crd > 144;
Copy the code3.9.3 update
Update instructor set salary = salary * 1.05 where salary > 100000; Update instructor set salary = salary * 1.05 where salary <= 100000;Copy the codeYou can use case syntax instead
Update instructor set salary = case when salary <= 100000 THEN salary * 1.05 else salary * 1.03 endCopy the codeThe format of a case statement is as follows:
Case when preD1 then result1 when pred2 then result2...... when predn then resultn else result0; endCopy the codeA scalar quantum query can be used as an example in the set clause: set the tot_cred property of each student tuple to the sum of the credits that the student has successfully completed, assuming that the student has neither an “F” nor a blank grade in a given course
update student S
set tot_cred = (
select sum(credits)
from takes natural join course
where S.ID = takes.ID and
takes.grade <> 'F' and
takes.grade is not null);
Copy the codeIf you do not want to set the TOTl_CREd property to empty, use the following method
select case
when sum(credis) is not null then sum(credits)
else 0
end
Copy the code3.10 summarize
- SQL consists of several parts
- Data definition language (DDL), which defines, deletes, and modifies relational schemas
- Data manipulation language (DML), which includes the query language, as well as commands to insert tuples into, delete tuples from, and modify tuples in the database
- SQL’s data definition language is used to create relationships with a particular schema, and in addition to declaring the name and type of relationship attributes, it also allows you to declare integrity constraints, such as primary and foreign code constraints
- SQL provides a variety of language structures for querying databases, including SELECT, FROM, and WHERE clauses. SQL supports natural join operations
- SQL also provides mechanisms to rename properties and relationships, and to sort query results by specific properties
- SQL supports basic geometric operations on relationships, including union, intersection, and lookup operations
- SQL handles relationships and queries that contain null values by adding “unknown” to the generic truth values true and false
- SQL supports aggregation, which allows you to group relationships and apply aggregation individually to each group, as well as collection operations on groups
- SQL supports nested subqueries in the WHERE and FROM clauses of the outer query. Standard quantum queries are also supported wherever an expression returns a single value
- SQL provides structures for updating, inserting, and deleting information