SQL Server (Advanced) keywords use two

Second, advanced keywords--Introduction to use

8,top use (the TOP clause returns the number of records)

Select Top  Number|percentcolumn_name (s) fromtable_name orSELECT TOP 2 *  fromPersons Example:Select Top 5 *  fromYk_typk--Check only the first 5 columns of dataSelect Top 1  percent *  fromYk_typk--1% is the data

9,link 's use (link full text Search)

Select *  from Article  where  articlecontent   link  '% Liu Bei' or Select *  from where  like ' N% '   used with the LIKE operator.

Use of in (multiple values after use in where)

inch WHERE multiple values are specified in the clause.  SELECT*fromWHERE in (value1,value2,...)

One, the use ofbetween (between value range)

1,betweenoperator inwhereclause, which is used to select a range of data between two values. 2Operatorbetween... andA range of data between two values is selected. These values can be numeric, text, or date. SELECT *  fromPersonsWHERELastNamebetween 'Adams'  and 'Carter'

The full name of the AS: (alias) use (as the specified nickname)

by using SQL, you can specify aliases (alias) for column names and table names.  SELECT as as Name from Persons

The join / left join/right join/full join is used behind the From

JoinleftjoinRight joinfull join : Returns a row whenever there is a match in one of the tables

13.1, join= use of inner join (join within joins)

Joinused to query data from these tables based on the relationship between the columns in two or more tables. Sometimes in order to get the complete result, we need to get the results from two or more tables. We need to do it .Join. The Association of two tables, withoutInner Joinand use of innerJointhe comparison.---Do not use:SELECTPersons.lastname, Persons.firstname, Orders.orderno fromPersons, OrdersWHEREPersons.id_p=orders.id_p---Using INNER join:SELECTPersons.lastname, Persons.firstname, Orders.orderno fromPersonsINNER JOINOrders onPersons.id_p=orders.id_pORDER  byPersons.lastname

The use of the left join

 Left JOIN The keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2).  SELECT  column_name (s) from table_name1leftJOIN    on table_name1.column_name=table_name2.column_name Note: left The JOIN keyword returns all rows from the left table (Persons), even if there are no matching rows in the right table (Orders).

The use of right join

 Right JOIN The keyword will return all rows to the right table (table_name2), even if there are no matching rows in the left table (table_name1).  SELECT  column_name (s) from table_name1rightJOIN    on table_name1.column_name=table_name2.column_name Note: Right The JOIN keyword returns all rows from the right table (Orders), even if there are no matching rows in the left table (Persons).

The use of full joins (full join)

The fullJOIN keyword Returns a row whenever there is a match in one of the tables .  SELECT  column_name (s) from table_name1fullJOIN    on table_name1.column_name=table_name2.column_name Note: full The JOIN  keyword returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed. 

The use of the Union and Union All (union merge select query)

 UNIONoperator is used to merge two or moreSELECTThe result set of the statement. Remark:UNIONof internalSELECTStatement must have the same number of columns. The column must also have a similar data type. At the same time, each pieceSELECTThe order of the columns in the statement must be the same. Using UnionSELECTCOLUMN_NAME (s) fromtable_name1UNIONSELECTCOLUMN_NAME (s) fromtable_name2 Note: By default,UNIONoperator to select a different value. If duplicate values are allowed, use theUNION  All. Using Union AllSELECTCOLUMN_NAME (s) fromtable_name1UNION  AllSELECTCOLUMN_NAME (s) fromtable_name2 In addition:UNIONThe column names in the result set are always equal toUNIONThe first oneSELECTThe name of the column in the statement.

Select into uses ()

SELECT  into statement to select data from one table and then insert the data into another table. the SELECT into statement is commonly used to create a backup copy of a table or to archive records.

The use of CREATE Database

The use of CREATE TABLE (creation table creates tables in the database)

The use of SQL constraints (Constraints in the table column names are given)

The use of create Inder (create Inder index)

The use of drop (Drop DROP Index)

The use of ALTER TABLE (ALTER TABLE Add, modify, delete column)

The use of null (NULL)

isnull used (is null to take the null value of a column)

27.1, is notnull used (is not NULL to take a column that is not a null value)

SQL Server (Advanced) keywords use two