当前位置:网站首页>Summary of common SQL statements

Summary of common SQL statements

2020-11-06 21:19:46 Irving the procedural ape

One 、 Basics Sql sentence

1、 Create database :Create DataBase  dbName;

2、 Delete database :Drop  DataBase  dbName;

3、 Create new table :Create  Table tabName(col1  type1  [not null]  [primary key] ,col2  type2  [not null ], ........);

   There are two ways to create a new table from an existing table :A:Create  Table tab_new  like tab-old;

                 B:Create Table tab_new as Select  col1,col2,....from tab_old  definition only;

4、 Delete new table :Drop Table tabName;

5、 Add a column to the table :Alter  Table tabName  add  column col  type;

6、 Add and delete primary keys for tables : Add primary key :Alter  Table  tabName  add  primary key(col);

              Delete primary key :Alter  Table  tabName drop primary key(col);

7、 Create and drop indexes for tables : Create index :Create [unique]  index indexName on tabName(col ....)

             Delete index :Drop index indexName;

8、 Create and delete views : Create view :Create view viewName as Select statement;

          Delete view :Drop  view viewName;

9、 Basic sql sentence : Inquire about :Select *  from Table where  Range ;Select * from Table where  field1  like  ‘%value1%'( Fuzzy query )

          Insert :Insert into  Table(field1,field2,...) values(value1,value2,.....);

          Delete :Delete from  Table where Range ;

·            to update :Update Table  set  field1=value1  where  Range ;

            Sort :Select  *  from  Table  order  by  field1  Desc【 Descending 】| Asc【 Ascending 】;

          total :Select  count  as TotalCount  from Table ;

          Sum up :Select sum(field1) as sumVaule from Table;

          Average :Select  avg(field1)  as avgValue from Table;

          Maximum :Select max(field1) as  maxValue from Table;

          Minimum :Select min(field1) as  minVaule from Table;

10、Sql Several high-level query operation words in :

   A: UNION Operator  

  UNION Operator by combining the other two result tables ( for example TABLE1 and TABLE2) And eliminate any duplicate rows in the table to derive a result table . When ALL along with UNION When used together ( namely UNION ALL), Don't eliminate duplicate lines . In both cases , Each row of the derived table does not come from TABLE1 Is from TABLE2.

  B: EXCEPT Operator

  EXCEPT  Operator by including all in TABLE1 But not in TABLE2 The result table is derived by eliminating all duplicate rows . When ALL along with EXCEPT When used together (EXCEPT ALL), Don't eliminate duplicate lines . 

  C: INTERSECT Operator  

  INTERSECT  The passage of the character only includes TABLE1 and TABLE2 The result table is derived by eliminating all duplicate rows . When ALL  along with INTERSECT When used together (INTERSECT ALL), Don't eliminate duplicate lines .
   notes : The result lines of several queries using operands must be consistent .

11、 Connect with the table :

  (1) Internal connection (Inner Join):Inner Join  TableName  ON  condition;

  (2) Unequal value connection : Unequal value join means that less than can be used in the condition of connection (<)、 Greater than (>)、 It's not equal to (<>) Equal operator , It can also be used LIKE、BETWEEN AND Equal operator , You can even use functions . Example :Select  field1,field2 from table1 Inner Jion table2 on table1.field1=table2.field1 where table1.field3<table2.field4

  (3) Cross connect : Implicit :Select t1.field1,t2.field3 from table1 as t1,table2 as t2;

           Explicit :Select  t1.field1,t2.field3 from table1 as t1 cross  Jion  table2 as t2;

  (4) External connection :A:Left outer Join ( The left outer join ( Left connection ): The result set includes both the matching rows of the join table , It also includes all the rows of the left join table );

          B:Right outer Join ( Right connection ( The right connection ): The result set includes both the matching join rows of the join table , It also includes all rows of the right join table );

          C:Full outer Join( Full outer join : Not only the matching rows of the symbolic join table , It also includes all the records in the two connection tables );

 

12、 Usage grouping Group by: 

    Example :Select Category , Abstract ,sum(field2) as sumValue from tableName Group by Category ;

   notes : A watch , Once the grouping is complete , Only group related information can be obtained after query . Group related information :( Statistics ) count,sum,max,min,avg   Criteria for grouping ); stay SQLServer In the middle group : Can't use text,ntext,image Fields of type are grouped by ; stay select Fields in statistical functions , It can't be put together with normal fields . In the use of Group by When you implement grouping , If there is where Filter conditions , Must be written in Group by Before .

13、Having Use :

   For example, filter some groups , It needs to be used Having, Because aggregate functions can no longer Where Use in statement , So we have to use Having Instead of .

   notes : Use Having When clause , It should be located in Group by after , also Having Statement cannot contain ungrouped column names .

Two 、 complex Sql sentence

1、 Copy table ( Copy only table structure ):Select * into b from a where 1<>1;Select top 0 * into b from a;

2、 Copy table ( Copy the data ):Insert into b(a,b,c) Select d,e,f from a;

3、 Subquery :

  SELECT Statements can be nested in other statements , such as SELECT,INSERT,UPDATE as well as DELETE etc. , These are nested SELECT Statements are called subqueries , It can be said that subqueries can be used when a query depends on the results of another query . There are two types of subqueries , One is to return a single value subquery , It can be used in a place where a single value can be used , In this case, the subquery can be regarded as a function with a return value ; Another type of query is the return of a sub column , In this case, the subquery can be regarded as a temporary data table in memory .

(1) Single value subquery :

   The syntax of single valued subquery and common SELECT There is no difference in the statement , The only limitation is that the return value of a subquery must have only one row of records , And there can only be one column . Such subqueries are also called scalar subqueries , Scalar subqueries can be used in SELECT In the list of statements 、 Expression 、WHERE The sentence is medium in many occasions . (2).........

版权声明
本文为[Irving the procedural ape]所创,转载请带上原文链接,感谢