Summary of common SQL statements

Time:2020-10-26

1、 Basic SQL statement

1. Create database:Create DataBase  dbName;

2. Delete database:Drop  DataBase  dbName;

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

There are two ways to create a new table based on the 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 the primary key: alter table tabname drop primary key (Col);

7. Create and drop indexes for the table:Create index: create [unique] index indexname on tabname (Col…)

Delete index: drop index indexname;

8. To create and delete Views:Create view: create view VIEWNAME as select statement;

Delete view: drop view VIEWNAME;

9. Basic SQL statement:Query: select * from table where range; select * from table where field1 like ‘% value1%’ (fuzzy query)

Insert into table (field1, field2,…) values (value1, Value2,…);

Delete: delete from table where range;

·Update: update table set field1 = value1 where range;

Sort: select * from table order by field1 desc [descending] / ASC [ascending];

Total number: select count as totalcount from table;

Sum: select sum (field1) as sum vaule 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. Several advanced query operation words in SQL:

   A: UNION operator 

  UNIONOperator by combining two other result tables (for exampleTABLE1 andTABLE2)And eliminate any duplicate rows in the table to derive a result table. WhenALL along withUNION When used together (i.eUNION ALL), does not eliminate duplicate lines. In both cases, each row of the derived table does not come fromTABLE1 It’s fromTABLE2

  B:Except operator

  EXCEPT Operator by including all in TABLE1 In but not inTABLE2 Row in and eliminate all duplicate rows to derive a result table. WhenALL along withEXCEPT When used together(EXCEPT ALL), does not eliminate duplicate lines. 

  C: INTERSECT operator 

  INTERSECT The symbol contains only TABLE1 andTABLE2 A table of operation results is derived by eliminating all duplicate rows. WhenALL along with INTERSECT When used together(INTERSECT ALL), does not eliminate duplicate lines.
Note:Several query result rows using an operation word must be consistent

11. Use table join:

 (1) Inner join: inner join tablename on condition;

(2) Unequal value connection: unequal value connection means that operators such as less than (<), greater than (>), and not equal to (< >) can be used in connection conditions, and operators such as like, between and, and even functions can be used. Example: select field1, field2 from table1 inner Jian table2 on table1.field1 = table2.field1 where table1.field3 < table2.field4

(3) Cross connection: implicit: select t1.field1, t2.field3 from table1 as T1, table2 as T2;

Explicit: select t1.field1, t2.field3 from table1 as T1 cross Jian table2 as T2;

(4) A: left outer join: the result set includes both the matching rows of the join table and all the rows of the left join table;

B: Right outer join (right outer join): the result set includes not only the matching join rows of the join table, but also all the rows of the right join table;

C: Full outer join (including not only the matching rows of the symbolic join table, but also all the records in the two join tables);

 

12. Use group by: 

  Example: select category, summary, sum (field2) as sumvalue from tablename group by category;

  notes: a table, once groupedAfter the query is completed, only group related information can be obtained.Group related information:(Statistics) count,sum,max,min,avg  Criteria for grouping); inWhen grouping in SQL Server: fields of text, ntext and image cannot be used as grouping basis; fields in select statistical function cannot be put together with ordinary fields. When using group by to implement grouping, if there is a where filter condition, it must be written before group by.

13. Using:

  If you want to filter some groups, you need to use having. Because the aggregate function can’t be used in the where statement, you have to use having instead.

  Note:When using the having clause, it should be after group by, and the having statement cannot contain ungrouped column names.

2、 Complex SQL statement

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

2. Copy table (copy 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 and delete. These nested select statements are called subqueries. It can be said that when a query depends on the results of another query, subqueries can be used. There are two types of subquery: one is the subquery that only returns a single value. In this case, 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; the other is a subquery that returns a column of values. In this case, the subquery can be regarded as a temporary data table in memory.

(1) Single valued subquery:

The syntax of a single valued subquery is no different from that of a normal select statement. The only limitation is that the return value of a subquery must have only one row of records and only one column. Such subquery is also called scalar subquery. Scalar subquery can be used in the list of select statement, expression, where statement and so on.
(2) Column value subquery:

Unlike scalar subqueries, column value subqueries can return a result set of multiple rows and columns. This kind of subquery is also called table subquery. Table subquery can be regarded as a temporary table. Table subquery can be used in many occasions such as from clause, insert statement, join clause and in clause of select statement.
(3) Scalar subquery in select list:
Example:

  SELECT field1,fileld2,
  (
    SELECT MAX(field3)
    FROM Table2
    WHERE Table2. field1= Table1.field1
  )
  FROM Table1
(4) Scalar subquery in where clause:
Example:
  SELECT field2 FROM Table1
  WHERE field1=
  (
    SELECT field1 FROM Table2
    WHERE field2=’Story’
  )
(5) Set operators and subqueries:
If the subquery is a multi row and multi column table subquery, it can be used as a temporary data table. If the subquery is a multi row and single column table subquery, the result set of such subquery is actually a set. SQL provides operators for operating on such a set, including in, any, all and exists.

 

 Example of in operator:

 

  SELECT * FROM T_Reader

 

  WHERE FYearOfJoin IN

 

  (

 

    select  FYearPublished  FROM T_Book

 

  )

 

  Any operator example:
  SELECT * FROM T_Reader

 

  WHERE FYearOfJoin =ANY

 

  (

 

    select FYearPublished FROM T_Book

 

  )
 Example of all operator:
  SELECT * FROM T_Book
  WHERE FYearPublished<ALL
  (
     SELECT FYearOfJoin FROM T_Reader
  )
Examples of exists operators:
  SELECT * FROM T_Book
   WHERE EXISTS
  (
    SELECT * FROM T_Reader  WHERE FProvince=’ShanDong’
  )
(6) Sub query application in other types of SQL statements:
  A. Application example of subquery in insert statement:
  INSERT INTO T_ReaderFavorite2(FCategoryId,FReaderId)
  SELECT FCategoryId,
  (CASE
    WHEN FReaderId<=10 THEN FReaderId
    ELSE FReaderId- FCategoryId
  END
  )
  FROM T_ReaderFavorite
  B. Application example of subquery in UPDATE statement:
  UPDATE T_Book
  SET FYearPublished=
  (SELECT MAX(FYearPublished) FROM T_Book)
  C. Application example of subquery in delete statement:

  DELETE FROM T_Book b1
  WHERE
  (
    SELECT COUNT(*) FROM T_Book b2
    WHERE b1. FCategoryId=b2. FCategoryId
  )>3
 

 

4. The usage of between is as follows:Select * from tableName where time between time1 and time2;

Select a, B, C from tablename where a not between values 1 and 2;

 Note:betweenBoundary values are included when limiting the range of query data,not betweenbarring;

5. Two associated tables. Delete the information in the primary table that is not in the secondary tableDelete from table1 where not exists (Select * from table2 where table1.field1=table2.field2);

6. Joint query of four tablesselect * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ….

7. Query the top ten data:Select top 10 * from tablename where range;

8. Ten pieces of data were randomly extractedSelect top 10 * from TableName order  by  newid();

9、Select in each groupbIn data with the same valueaThe largest record of all information:Select a,b,c from table1 as t1 where a=(Select max(a) from table2 as t2 where t1.b=t2.b);

10、Include all in TableA In but not in TableBandTableC Row in and eliminate all duplicate rows to derive a result table:(select a from tableA ) except (select a from tableB) except (select a from tableC)

11、List all table names in the database:select name from sysobjects where type=’U’ // URepresentative use

12、List everything on the listName:select name from syscolumns where id=object_id(‘TableName’)

13. Delete duplicate records in the table:(1)Delete from TableName where id not in (Select max(id) from TableName group  by col1,col2 ……);

            (2)Select  distinct *  into temp from TableName 

                                                   delete from TableName

              insert into Tablename  seletct * from temp

           (3)alter table tablename

–Add an auto increment column

             add  column_b int identity(1,1)
               delete from tablename where column_b not in(
               select max(column_b)  from tablename group by column1,column2,…)
               alter table tablename drop column column_b

14. One SQL statement to achieve Database Paging:(1)Select  top  num *  from TableName

                where  field1>(Select max(field1) from (Select totalNum field1 from TableName order by fireld1) A) 

                 order by field1

                (2)Select * from (Select row_number() over (order by field1) rownumber,* from TableName) A 

                Where rownumber between  startNum  And  [start+count-1]; 

Note:For more information about SQL query paging, please refer to the blog(https://www.cnblogs.com/zcttxs/archive/2012/04/01/2429151.html), and it’s not going to be repeated here.

                

 

Recommended Today

Mongodb splitchunk caused routing table refresh, resulting in slow response

Mongodb splitchunk caused routing table refresh, resulting in slow response Mongodb sharding instance fromVersion 3.4Upgrade toVersion 4.0In the future, the performance of the insertion was significantly reduced, and a large number ofinsertRequest slow log: 2020-08-19T16:40:46.563+0800 I COMMAND [conn1528] command sdb.sColl command: insert { insert: “sColl”, xxx} … locks: {Global: { acquireCount: { r: 6, w: […]