SQL statements commonly used in projects

Abstract: for the rookie level like me, the contact of the project is relatively small, the usual practice, including three layers, generally can only be used to delete and change, of course, these additions and deletions to the sentence is to learn the introduction of the database statement, Baidu Search a large pile, The help document in W3cschool is also available for review.

Here is the usual use of additions and deletions to make a small summary, for some small projects is also more applicable, of course, good memory than bad pen, this time the paper version of the notes feel very deficient, because want to see, you have to find that book, and to turn over where, feel very inconvenient. and electronic version

Copy or whatever.

Commonly used SQL statements (increase and deletion) 1, add, insert statement

Insert  into Values ('val1','val2') Insert  into Values ('val1','val2')     -- Insert a piece of data

     Insert  intoTableName (columnname1,columnname2)Select 'Val1','Val2' Union  All     Select 'Val3','Val4' Union  All     Select 'VAL5','val6'                   --inserting more than one data     

Note: Of course, in a normal project, multiple insertions can be performed using StringBuilder to stitch the SQL statement, or the SQL statements are separated by semicolons, or can be executed multiple times.

2. Delete statements

Delete  from where columnname1='val1'      -- Delete a piece of data

truncate Table TableName        -- clears the data in the table (without deleting the table), restores the identity seed

Note: Of course, delete the statement and delete the column, delete the table and so on, but generally not commonly used, of course, emptying the table of data is also not used, here just to remember truncate

3. Modify (update) the data statement

Update set columnname1='val1'where columnname2='  val2'              -- Modify (update) Values

Note: Commonly used in the modification of the same way, it is easier to remember, of course, there is an alter, but generally the table or what the operation of it

4. Query data

Select *  from TableName  where columnname1='val1'select columnname1, Columnname2 from  where columnname1='val1'            -- query a single piece of data

Select *  from   TableName    Select from TableName       -- querying all data

Select Top 3  fromTableNamewhereColumnname1='Val1'       --Query the first three dataSelect Top  - percent  fromTableName--How much data is queried?Select *  fromTableNameOrder  byColumnname1ASC    --sort queries in ascending orderSelect *  fromTableNameOrder  byColumnname2desc     --Reverse order QuerySelect distinctColumnname1 fromTableName--eliminate duplicate queries for the entire result set

    Select MAX(columnname1) fromTableName--To find the maximum value        Select Min(columnname1) fromTableName--To find the minimum value    Select Sum(columnname1) fromTableName--Find Sum        Select AVG(columnname1) fromTableName--Averaging (of course, the average is not calculated with NULL, and the exact sum should be divided by the quantity)

Select Count(*) fromTableName--query data Total number of barsSelect *  fromTableNamewhereColumnname1 like '%val1%'      --Fuzzy QuerySelectColumnname1,Count(*) fromTableNameGroup  byColumnname1--Group Queries

Note: These queries are generally used as single-table queries, and many combinations can get the desired query results, which depends on the requirements to determine the SQL statement

5. Advanced Inquiry

(1) INNER JOIN

-- returns a row when there is at least one match in the table (only data that matches two tables can be queried) Select column_name (s)  from table1 Inner Join table2  on Table1.column_name=table2.column_name;

(2) LEFT Join

-- The keyword returns all rows from the left table (table1), even if there is no match in the right table (table2). If there is no match in the right table, the result is NULL.  Select  column_name (s) from table1leftjoin  table2   on table1.column_name=table2.column_name;

(3) Right Join

-- all rows are returned from the right table (table2), even if there is no match in the left table (table1). If there is no match in the left table, the result is NULL.  Select  column_name (s) from table1rightjoin  table2   on table1.column_name=table2.column_name;

(4) Union

-- The UNION operator is used to combine the result set of two or more SELECT statements.  --Each SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.  Select from table1UnionSelect from table2;

Summary: This is a relatively simple SQL statement, feel database this thing, said difficult, but before also spent a lot of time to explore, to understand, and even to recite those SQL statements, in fact, I think the application is also added and deleted to change it, of course, create a database, create a table and so on, It is also necessary to learn. However, you can also do this from the page

To operate, there is no need to write SQL statements. If you want to use more, or a lot of commonly used statements and functions, you need to check the Help document, the usage is explained in detail.

SQL statements commonly used in projects