*** SELECT ***
select * (column names) from table_name (table) where column_name operator value ex :( host) select * from stock_information where stockid = str (nid) stockname = 'str_name' stockname like '% find this%' stockname like '[a -ZA-Z]% '--------- ([] Specified value range) Stockname Like' [^ fm]% '--------- (^ exclude specified range) ------- can only use wildcards in WHERE clauses using the LIKE key) or stockpath = 'stock_path' or stockNumber <1000 and stockindex = 24 Not stocksex = 'man' stockNumber Between 20 and 100 stocknumber in 10, 20, 30) Order by stockid desc (ASC) --------- Sort, Desc - Descending, ASC-Ascending Order By 1, 2 --------- BY list stockname = (Select stockname from stock_information where stockid = 4) -------- subquery --------- Unless you can make sure the inner SELECT returns a row, ------- - Otherwise, in the outer WHERE clause, use a IN Law Select Distinct Column_Name Form Table_Name --------- DistINCT specified to search the alone column value, do not repeat SELECT Stocknumber, "Stocknumber 10" = stockNumber 10 from table_name Select stockname, "stocknumber" = count (*) from table_name group by s TOCKNAME -------- Group By Put the table, specify the same value in the specified column HAVING Count (*) = 2 --------- Having Selected Group Select * from Table1, table2 where table1.id * = Table2.id -------- The left exterior connection, there is a Table1 in Table1 to indicate Table1.ID = * Table2.ID ------- - Right Exterior SELECT STOCKNAME from Table1 Union [All] ----- UNION Merge Query Result Set, All-Reserved Dress SELECT STOCKNAME FROM TABLE2UNION operator can combine the query result set of two or more SELECT statements It is a result set display to perform a joint query. Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect.
Inner Join Using the comparative operator to perform a comparison operation of the data (some) column data, and list the data lines matching the connection conditions in these tables. According to the comparative method used, the inner connection is divided into equivalent connection, natural connection, and not equal connection. The outer connection is divided into left-way connections (Right Outer Join, Right Outer Join or Right Join, and three full-end connections (Full Outer Join or Full Join). Unlike internal connections, the external connection does not only list rows matching the connection condition, but is listed on the left table (left external connection), the right table (right-external connection) or two tables (all external connection All data lines in line with search criteria are in line. Cross Join does not have a WHERE clause, it returns the Cartesian of all data lines in the connection table, and the number of data lines in the result set is equal to the number of data lines that meet the query conditions in the first table. The number of data lines in the table is in line with the query conditions. The on (join_condition) clause in the connection operation indicates the connection condition, which is constructed by the columns and comparison operators, logical operators, and the like in the connected table.
No matter which connection does not directly connect the text, ntext, and image data type columns
Example, the following example, the following uses the equivalent connection lists authors and publishing agencies in the same cities in the AUTHORS and PUBLISHERS:
Select * from authors as a inner join public, as a natural connection, remove the repeat column (City and State) in the Authors and Publishers tables in the selection list: SELECT A. *, P. Pub_id, pub_name, p.country from authors as a inner join publicishers asp on a.city = p.city
For example,
Select a. *, B. * From city as a full outer join user as b on a.username = B.USERNAME
In the case, there are 6 types of books in the Titles table, and 8 publishers in the Publishers table, the number of records retrieved in the following cross-connects will be equal to 6 * 8 = 48 lines.
SELECT TYPE, PUB_NAME from Titles Cross Join Publishers Order by Type
*** insert ***
INSERT INTO TABLE_NAME (STOCK_NAME, STOCK_NUMBER) Value ("XXX", "XXXXX") Value (SELECT stockname, stock_table2) --- value is a SELECT statement
*** UPDATE ***
Update table_name set stockname = "xxx" [Where stockid = 3] stockname = default stockname = null stocknumber = stockname 4
*** Delete ***
Delete from table_name where stockid = 3 truncate table_name ----------- Delete all rows in the table, still keep the integrity of the table DROP TABLE_NAME --------------- Fully delete table
*** alter table *** --- Modify Database Table Structure ALTER TABLE DATABASE.OWNER.TABLE_NAME Add Column_name Char (2) NULL ..... sp_help table_name ---- Display Table Existing Create Table Table_Name (Name CHAR (20), AGE SMALLINT, LNAME VARCHAR (30)) Insert Into Table_name Select ......... - - - Realize the method of deleting columns (Create a new table) ALTER TABLE TABLE_NAME DROP CONSTRAINT stockName_DEFAULT - - Delete the default constraint of stockname *** function (/ * Common function * /) ***
---- Statistical Function ---- AVG - Ask the average COUNT - Statistics Max - Search MIN - Summary SUM - Search
--AVGuse panguselect avg (e_wage) as dept_avgWagefrom employeegroup by dept_id --MAX-- seek the highest paid employee name use panguselect e_namefrom employeewhere e_wage = (select max (e_wage) from employee) --STDEV () - STDEV () function Returns the standard deviation of all data in the expression --stdevp () - stdevp () function Returns the total standard deviation - Var () - var () function returns a statistical variation of all values in the expression - Varp () --VARP () function Returns the total variation ---- Arithmetic function ---- / *** Triangle function *** / sin (float_expression) - Returns sinusoidal COS (FLOAT_EXPIPRESSION) with an arc indicated angle - Returns the cosine TAN (FLOAT_EXPRESSION) in the arc-represented angle - Returns the orthodal COT (FLOAT_EXPIPRESSION) in the angle represented - Returns the remainder of the angle represented / *** anti-triangle function *** / asin Float_expression) - Returns the sinusoidal angle ACOS (float_expression) - Return the cosine in the arc value is the angle ATAN (FLOAT_EXPRESSION) returned by the float value - Return to the angle ATAN2 indicated by the fluoat value (float_expression1, float_expression2) - Returns orthogonal is float_expression1 / float_expres-sion2 to returns arc to angle returns to the same data type as the expression in -integer / Money / REAL / FLOAT Type Radians (NuMeric_Expression) - Convert angle to radians Return to express the same data type to - ITEGER / MONEY / REAL / FLOAT type exp (float_expression) - Return Expression index log (float_expression) - - Returns the natural log of the expression log1 0 (FLOAT_EXPRESSION) - Return to the expression of 10-bottom-bottom-to-values SQRT (FLOAT_EXPIPRESSION) - Return to the square root of the expression / *** All similar value *** / ceiling (numeric_expression) - Return> = Expression The minimum integer returned data type and expression can be the same - ITEGER / MONEY / REAL / FLOAT Type FLOOR (Numeric_Expression) - Return <=
The data type of the minimum integer returned to the expression can be the same as the expression (NuMeric_Expression) - Returns the data-type and expressions returned by Integer_Expression-return data-type For Integer / Money / REAL / FLOAT Type ABS (NuMERIC_EXPIPRESSION) - The data type returned by the return expression is the same as the expression of the expression as the expression of -integer / money / real / float type Sign (Numeric_expression) - test parameters The positive and negative number returns 0 zero value 1 positive or -1 negative data type - the same can be used for Integer / Money / REAL / FLOAT type Pi () - return value π 3.1415926535897936rand ([integer_expression] ) - Use optional [integer_expression] to make seeds worth 0-1, random floating point numbers ---- ASCII () - Function Returns the ASCII code value of the left-end character of the character expression The char () - function is used to convert the ASCII code to a character - if the ASCII code value of the 0 ~ 255 is not entered between the ASCII code value CHAR function returns a NULL value Lower () - function transforms all the strings to lowercase Upper ( - Function Converts the string to the uppercase STR () - function converts numeric data to character data Ltrim () - function removes the space of the string head to the RTRIM () - function put the character string tail Space removes left (), right (), substring () - function Returns part of the string charIndex (), PatIndex () - Function Returns the start position of a specified substring in the string Soundex () - The function returns a four-bit character code - Soundex function can be used to find sound similar strings, but the SoundEx function returns only 0 Value Difference () - function returns the value of the two character expressions returned by the SoundEx function. Difference - 0 two SoundEx functions return values in the first character - 1 two Sou NDEX function return value The first character of the value is the same - 2 Two SoundEx functions return values The first two characters of the value of the same - 3 Two SoundEx functions return values The first two three characters of the value - 4 Soundex functions The return value is completely the same quotename () - function returns a string from a specific pen / * select quotename ('abc', '{') quotename ('ABC') running results --------- ------------------------- {{ABC} [abc] * / replicate () - Function Returns a string of repeating character_expression specified / * SELECT Replicate ('ABC', 3) REPLICATE ('ABC', -2) running results as follows ------------------- Abcabcabc null * / reverse ) The function reverses the character string of the specified string to the resplace () - function returns the string of the specified substring / * SELECT Replace ('ABC123G', '123', 'DEF') operation results --------------------- Abcdefg * /
Space () - Function Returns a blank string stuff () - function with a specified length with another sub-string to specify the subrout string of position length ---- Data Type Conversion Function ---- Cast () The function syntax is Cast () (
) - Nullif Function Returns NULL in Expression1 and Expression2 Returns the value of Expression1 when it is not equal --------------------------- ----------------------------- Description: Replication table (only copy structure, source name: A new table name: b) SQL : Select * INTO B from a where 1 <> 1
