~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~basic structure of SQL queriesSet operation null value aggregation function nested subqueries database modification ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Basic Organization for SQL queriesthe basic mechanism of a SQL query is comprised of three clauses: Select,from,whereSelect A1,a2,a3 fron r1,r2,r3 where P
SELECT clauseNote: SQL does not allow the use of the character '-' in the attribute name, using dept_name instead of Dept-name SQL to distinguish the case of letters, so you can use uppercase letters, lowercase command tables, properties, and so on. SQL allows duplicate ganso in relational and SQL expression results to forcibly remove duplicates, you can add a keyword distinct example after a select: query all the names in the instructor relationship and remove duplicate select distinct Dept_ Name Fron instructor; SQL can also use the keyword all to explicitly indicate that no duplicates are removed (SQL defaults to all), select all dept_name from instructor; The asterisk * In the SELECT clause can be used to denote "all properties" select * from instructor; The SELECT clause can also contain an arithmetic expression with a + 、-、/operator, which can be a constant or a tuple's property. Select id,name,salary*1.05 from instructor;
WHERE clauseThe WHERE clause allows us to select only those ganso that satisfy a particular predicate in the result relationship of the FROM clause.
Example: Find the name of a teacher in computer science and pay more than $70,000 select name from instructor where dept_name = ' com.science ' and salary > 70000; The above SQL query statement, the corresponding relational algebra expression is:
SQL allows you to use logical conjunctions and,or and not in the WHERE clause, or you can use between to specify a range query. The arithmetic object of a logical conjunction can be an example of an expression that contains a comparison operator <,<=,>,>=,= and <>: Find the name of the teacher in the 90000 to 100000 select name from Instructor W Here salary > 90000 and Salary > 100000; Or select name from instructor where Salaty between 90000 and 100000;
From clauseThe FROM clause is a list of relationships that need to be accessed in a query evaluation, and a Cartesian product is defined by the FROM clause on the relationships listed in the clause. Find the Cartesian product of instructor and teacher select * from Instructor,teacher;
renaming operations SQL provides a mechanism for renaming relationships and attributes, using the AS clause, the AS statement can appear in the SELECT clause, or it can appear in the FROM clause to rename the relationship by using the rename operation, for example: Find out all teachers, and the identity of the courses they teach select T.na Me, s.course_id from instructor as T, teachers as S where t.id = s.id Example: Find out all the teacher's names, their wages are at least more than the biology of a teacher's work High SELECT distinct t.name from instructor as T, instructor as S where T.salary > S.salary and S.de Pt_name = ' biology ';
String OperationsThe most common operation for strings is to use the pattern match of the operator like, using two special characters to describe the pattern: percent (%): Match any substring underscore (_): matches any one of the following characters: Find all the names of the buildings that contain the substring ' Ryan ' Select Dept_name from department where building like '%ryan% ';
Database-fourth Chapter SQL statement query
