A query statement1 Distinctkeyword eliminate repeated linesWhen repeated data appears in the result data of the query. In the query condition, add Distinctkeyword to eliminate repeated lines;Such as: SELECT distinct Sno from SC;2 like match wildcard% and _%: A string that represents a random length (containing 0), such as: Like ' a%b ', which means starting with a. A random-length string ending in B._: Represents a random single character. Note that the character set of the database is ASCII when a Chinese character needs two _, and when the character set is GBK, only one _ is required. For example: Like ' Ouyang __ ';Note: When you want to query the string itself contains a wildcard character% or _, then you have to use escape< ' swap character ' > phrase. The wildcard character is escaped, for example: select Cno,ccredit from Course where Cnamelike ' db\_design ' escape ' \; Escape ' \ ' means ' \ ' as a newline character, so that the character "_" immediately after "\" in the matching string no longer has the meaning of a wildcard character. Escaped as a normal "_" character3 queries that involve null valuesWhen a field value is queried for null, the field and field values are "is" and cannot be substituted with "=". For example: Select Sno,cno from SC where Grade is NULL;4 About Ascending Descendingsuch as: SELECT * from Student ORDER by sdept, Sage desc;The query results are arranged in ascending order (sdept) of the department and students in the same department are sorted by age5 Some Notes on the aggregation functionWhen encountering null values in a clustered function such as count () sum () avg () max () min (), the null value is skipped and only non-null values are processed. Note: In the WHERE clause, you cannot use the aggregate function as a conditional expression.6 A having phrase in a group by grouping condition specifies a filter conditionAssume that group by is grouped. It is also required to filter these groups according to certain conditions, and finally to output only those groups that meet the specified criteria, and to use the having phrase to specify the filter criteria;such as: Select Sno from SC GROUP by Sno have count (*) > 3; Here first group by words are grouped by Sno. Then use the aggregate function count for each group count, having a phrase that gives the criteria for selecting the group. Only groups that meet the criteria (that is, the number of tuples >3) will be selected.NOTE: The WHERE clause acts on the base table or view from which you select a tuple that satisfies the criteria. Having a phrase acting on a group from which to select the group that satisfies the condition7 Notes on nested queriesA nested query is a query that nests a query block (a subquery) in the condition of a where or having phrase that also has a query block (the parent query).It should be noted that the ORDER BY clause cannot be used in the SELECT statement of a subquery, and that the ORDER BY clause can only sort the results of the query finally8 subquery with EXISTS predicateexists represents the existence of quantifiers. So a subquery with a exists predicate does not return no matter what data, just produce logic true ' true ', or logical false ' false '.
For example: Select Sname from Student where exists (SELECT * from SC where Sno=student.sno and cno= ' 1 ');A subquery derived from exists, whose target column expression is often used *, because a subquery with exists returns only true or false values, giving an alias with no practical meaning9 Integrated QueryThe set operation mainly includes and operates the union, the intersection operation intersect and the difference operation except.The use of the Union is much more, merging multiple query results, the system will take the initiative to remove the repeated tuples, assuming that you want to retain the repeating tuple with the union ALL operator.The use of Intersect is to get the same result in multiple query results (that is, fetch intersection)The use of except is to subtract the second query result (that is, the difference set) with the previous query result. Two INSERT statements1 inserting sub-query resultsA subquery can be nested not only in a SELECT statement. can also be nested within an INSERT statement. Used to generate the bulk data to be insertedFor example: INSERT INTO Dept_age (sdept, avg_age) Select Sdept, AVG (Sage) from Student Group by sdept; three change statements1 changing the value of all data in a tablesuch as: Update Student set sage=sage+1, four-mode deleteDelete Schema statement: Drop schema < schema name > <cascade | Restrict>Select CASCADE (Cascade), which means that all database objects in the schema are deleted together at the same time in the delete modeSelect Restrict (limit) to indicate that database objects (such as tables, views, and so on) that have subordinates defined in the pattern are assumed. The run of the DELETE statement is rejected. Ability to run the drop SCHEMA statement only when there are no objects of any subordinate in this mode
Points of attention in SQL action statements
