Five types of data: character, text, numeric, logical and date
Character Varcharvs Char They are used to store characters with a string length of less than 255. Text-based text when you need to store large strings of characters, you should use textual data. Text data does not have a length, whereas the character data described in the previous section is of a length. numeric integer int, decimal numeric, money money a tinyint type data occupies only one byte; an int data occupies four bytes. A numeric field can store a number from -10^38 to 10^38 range. Language features non-procedural: only the operational requirements, not to describe the operating procedures, and do not need to navigate. Just tell the computer "what to do" instead of telling it "what to do" when you use it. Use a brief
A Select
*
Col_1,col_2
Col_1 as name_1,col_2 as name_2
Distinct delete duplicate rows
Limit the number of rows returned TOP n [PERCENT] Oracle does not use
Two
The FROM clause can specify a maximum of 256 tables or views in the FROM clause, separated by commas.
You can alias as or do not add
Three
The WHERE clause WHERE clause sets the query condition and filters out the unwanted data rows Where clause can include variousConditional operators:comparison operators (size comparison):>;, >=, =, <;, <=, <>;,! >;,! <The range operator (whether the expression value is in the specified range): Between ... And ... Not between ... And ...list operator (determines whether the expression is a specified item in the list): In (item 1, item 2 ...). Not in (item 1, item 2 ...). )Pattern Match (determines whether the value matches the specified character wildcard format): Like, not likenull-valued judge (determines whether an expression is empty):Is null, is not NULLlogical operators (for multi-conditional logical connections): not, and, OR1, range operator Example: age between and 30 equals age>=10 and age<=302, list operator Example: Country in (' Germany ', ' China ') 3, Pattern Match example: commonly used for fuzzy lookups, which determines whether a column value matches a specified string format. Available for type queries such as char, varchar, text, ntext, datetime, and smalldatetime.You can use the following wildcard characters:Percent percent: can match any type and length of the character, if it is Chinese, please use two percent sign is a percentage. Underline _: Matches a single arbitrary character, which is commonly used to limit the character length of an expression. square brackets []: Specifies a character, string, or range that requires matching objects to be any of them. [^]: The value is also [] the same, but it requires that the matched object be any character other than the specified character. Four query results sort correlation functions SQL Aggregate (collection) functionCalculates the value obtained from the column, returning a single value. [3] Useful Aggregate functions:
- AVG ()-return average
- COUNT ()-Returns the number of rows
- First ()-Returns the value of the number one record
- Last ()-Returns the value of the final record
- Max ()-Returns the maximum value
- MIN ()-Returns the minimum value
- SUM ()-Returns the sum
SqlScalar functionThe SQL Scalar function returns a single value based on the input value. [3] Useful Scalar functions:
- UCASE ()-Converts a field to uppercase
- LCASE ()-Converts a field to lowercase
- MID ()-extracts characters from a text field
- LEN ()-Returns the length of a text field
- ROUND ()-Rounding the specified number of decimal digits for a numeric field
- Now ()-Returns the current system date and time
- Format ()-Formats how a field is displayed
Data types for Sql-2-sql