Chapter III Database Query

1. Preface
-"Query: a specific request to retrieve, create, modify, or delete data within a database."
-"Basic query of database: Simple query, group query, data Summary, subquery, connection query

2. Select query syntax format
SELECT [DISTINCT] [top n [percent]] *
From table1
Join table2 on ... join Table3 on ...
where ....
GROUP BY ... [With Cube |rollup]
Having ...
ORDER BY ...

-"for the table alias as
-"Query all columns, specify columns
For the column alias as
-"Query the first n part of the data:
Top N Column Name: Indicates the first n rows to view
Top N Percent Column name: Indicates the data for the first few percent of view
-"Sort: ASC Ascending desc Descending
Order BY Column name 1 asc|desc, column name 1 Asc|desc ...
-"Eliminate duplicate rows: Distinct
-"Conditional query: write in the back of where
Filters rows, returns a value of type bool, or joins a result set if the column data in a row satisfies a condition, otherwise it does not come out of the results set
Comparison operators: =,>,>=,<,<=,!= or <>
Between ... Expressed in a contiguous range
In means within a discontinuous range
Logical operators: And,or,not
Fuzzy query: A value used to handle string types, including: like% _ [] ^
% and _ write in [] mean meaning of itself
In [] indicates a continuous range can be used-
^ written at the beginning of [], indicating that no internal characters are used
NULL judgment: null is used when computed with other values, NULL is considered to be the smallest when evaluated with IS NULL or is not NULL
Precedence: parentheses, not, comparison operators, logical operators
-"Connection query: Join table name on association condition
Internal connection: Inner JOIN, exactly matching data in two tables
Left OUTER join: Left OUTER JOIN, data exactly matched in two tables, data unique in the table
Right outer joins: Right outer join, data exactly matched in two tables, data unique in the table
Full outer joins: Full outer join, data that is exactly matched in two tables, data unique to the left table, data unique in the right table
-"Aggregation function: Merging rows of data
Sum,avg,count,max,min
Operations are generally performed on columns of numeric types
Multiple aggregate functions can be written in a single query, but cannot be mixed with normal columns
Null problem in aggregation: not participating in the calculation
-"open Window function: Over ()
Distribute the statistics to each row in the original table
Combining aggregate functions, ranking functions using
-"group: Group By Column name 1, column Name 2 ...
Aggregate functions are generally used in conjunction with groups, and the data within the group is counted.
GROUP by a specified column
Post-group conditional filtering: Having ...

3, subquery
-"When a query relies on another query result, you can use a subquery."
-No subquery
• Compare subqueries: Compare the value of an expression to the single value returned by the subquery.
Example: In the "Teaching Library" query C007 exam results than "Zheng" high number and name.
Statement: Use library
Select Student table. School number, name from student table, choose timetable
where student table. Study Number = Choose a timetable. Student number and Course number = ' 007 '
and score > (select Grade form selected schedule Where course number = ' 007 '/*2, determine Zheng score, and compare test */
and number = (select study number from student table where name = ' Zheng '))/*1, OK number */
Some, any, all and in subquery
with Before a subquery, when comparing tests, any represents any one of the subquery sets, and
all represents all of the subquery sets, and
in is within the scope of the subquery set.
-"Related subqueries
• Compare queries
Get employee information for employees who are less than the average salary for their department
Select Empno,ename,sal,deptno from emp out
where Sal < (
S Elect avg (SAL) from emp inner
where Inner.deptno=out.deptno
)
exists get 80 employee number information in association subqueries

Select EMP No,ename,sal,hiredate,substr (hiredate,8,2)
from Emp out where exists (
select empno from emp inner
where out.em Pno=inner.empno
and substr (hiredate,8,2) = ' + ');

3. Joint Inquiry
-"Merges the result set of multiple queries into one result set
-"Joint Requirements:
Consistent number of result set columns
The corresponding column must be of the same type
-"Union (merge), union ALL, except (returns the left side query without the right side), intersect (returns a query that does not contain duplicates)
Select Column name from table name 1
Union | except | Intersect
Select Column name from table Name 2
-"Useful: Show totals at query results"

4. Use the SELECT clause in database operations
• Use in INSERT statements
INSERT into replica table (column name 1, column name 2,) SELECT * FROM source table
Where Condition expression
• In the UPDATE statement, use the
Update table name
Set Column name = value
Where Condition expression
NOTE: The SELECT clause is used in the conditional expression
• In the DELETE statement, use the
Delete Table name
Where Condition expression
NOTE: The SELECT clause is used in the conditional expression

5. Fast Backup
-"Store query results:
Select column name into new table name from source table name where query condition
Note: Select Into cannot be used with the COMPUTE clause
-"Backup:
Select column name into new table name from source table name
The backup table may not exist, a new table is created, the structure of the table is complete, but the constraints are not included
If you want to include only structures that do not contain data, you can add a top 0
-"Backup to an existing table: INSERT into Backup table name Select column name from source table name

6. Built-in functions
-"Type conversion function:
Cast (expression as data_type): Move any type to any type
Convert (Date_type,expression[,style]): Move any type to any type
If the target type is a string, the style can be formatted, and the specific format references help
-"String function: ASCII (ASCII value of the character), char (from ASCII to character),
Left,right,substring: String interception
Len: Returns the length of a string
Lower,upper: Turn lowercase, uppercase
Ltrim,rtrim: Go to Space
Note: The index starts at 1 instead of 0
-"Date function: GetDate (Gets the current datetime),
DATEADD (date plus),
DateDiff (date difference),
DatePart (a part of the date taken), Year,month,day
Note: The first parameter of DATEADD, DateDiff, and datepart uses double quotation marks

Chapter III Database Query