SQL21 Day Self-study note (1)

Finding non-repeating data

DISTINCT

Query Date

The format in Access is yyyy-mm-dd,query executed in a statement with ' # ' +formatdata (' yyyy-mm-dd ', date) + ' # '

SQL operator

Take the mold%,mod ()

+,-,*,%

Comparison operation (True,false,unknow)

Comparison operation (<,>,<>,!=)

Logical Operations (And,or,not)

In order to improve efficiency generally do not use

|| Connection, CONCAT

Like wildcard character%,_

Sort uppercase letters in SQL are always behind lowercase, whether in ASCII or EBCDIC

SQL is not case sensitive, but the database is case sensitive

Judgment field is empty, illegal

Select * FROM student where number is NULL

is not NULL

Set operation (set)

It's a collection operation in high school math.

Union and set

INTERSECT intersection

[.? Nt?r ' Sekt]

The entire result of the UNION all two dataset

Minus subtraction

In statistic function

Conut,sum,avg,max,min

Variance VARIANCE

Standard deviation STDDEV

Date function

Add_months (date,2) plus two months

Last_day (date) Specifies the last day of the month

Positive

Months_between (s,e) 0

Negative value (E earlier than s)

New_time Modifying the time zone

Next_day (date, ' FRIDAY ') date last Friday, week or next week

Sysdate system Time

Mathematical functions

ABS Absolute

CELL minus the number of decimal parts

Floor minus decimal plus 1

Trigonometric Cos,cosh,sin,sinh,tan,tanh (all radians)

EXP Natural Index

LN natural logarithm

LOG (n,m) logmn

MOD

POWER (A, b) A of the second party

Sign (n) to determine if n is positive

sqrt square root

Character functions

CHR is the same as Delphi's usage.

CONCAT | |

Initcap the COME into COME.

Lower,upper

Lpad,rpad

Ltrim,rtrim

Replace (field, ' Target ', ' Replace with ')

SUBSTR, like the copy function Usage of Delphi, is inverted when index is negative

TRANSLATE (field, target collection, replace with collection)

INSERT (field, ' Target ', startindex,endindex)

LENGTH

Conversion functions

To_char,to_number

Other

Greatest the maximum value in the collection

Minimum value in the least

User username

Attention

Summary functions cannot be used in where

Wherestring with similar like (exp%) ORDER Bygroup by

SQL cannot combine normal columns and summary functions with the GROUP by sub-

Sentence it can be grouped after the results of a select in the Apply summary function query SELECT * FROM CHECKS

14 rows returned and SELECT payee SUM AMOUNT from CHECKS GROUP by

Payee divides the returned 14 rows into 7 groups and then applies a summary function to each group.

Cannot use except grouping columns in a SELECT clause when a grouping result is required to return multiple values

Columns that will result in incorrect return values you can group by using columns that are not listed in the SELECT

Having

You can use the summary function later.

Union of tables

Combination of cross joint equivalence and unequal equivalence

Join Onright OUTER Join on

Returns all contents of the right table, non-conditional return control values

Left OUTER JOIN on

The first clause of the where part of the Union is the condition of the Union

Sub-query

A subquery should return only one value

Inexists

Returns true if the subquery result is NOT NULL

Any,some

Returns a true value for each truth in the query result

In comparison, in is quite the use of multiple election numbers; Any,some can use other comparison operations.

All

Generally used to exclude

WHERE Name<>all (sub-query)

SQL21 Day Self-study note (1)