I haven't done this for a long time. By the way, I was asked about it during the interview
1, distinct
1) Function
Remove duplicate items
2) Grammar
select distinct Column name from Table name;
- give an example
Before use:(Here you can see that 3 repeat) select client ID from Orders;
After use:(It can be seen here that only once 3) select distinct customer ID from Orders;
3) Shortcomings
When two columns are selected, it is possible that the first column has no duplicate removal, but the timestamp of the second column is unique
2, Usage of top
1) Function
Get the row data of the top few or the top percentage
2) Grammar
select top The name of the numeric column from Name of the table; or select top Name of percentage column from Name of the table;
3) Use examples:
1)Get the first 3 items of the table select top 3 * from Customers; 2)After getting the table, 40%Record of(The last expression is in reverse order) select top 40 Percent * from Customers order by customer ID desc; 3)Get random 40%Record of select top 40 Percent * from Customers order by customer ID NEWID();
3, And and or
- Use examples:
1)and give an example, or It's the same select * from Customers where full name='Zhang San' and city='Beijing' 2)and and or Combined use(The conditions are separated by parentheses) select * from Orders where (client ID=3 OR customer ID=1) AND deliver goods ID=4
4, group by
1) Function
Combined with aggregation function, the result set is grouped according to one or more columns
2) Grammar
select The name of the column, Aggregate function (column name) FROM Table name WHERE Column name operator value GROUP BY Column name
3) Use examples:
Query all city data
1)How many customers live in each city? SELECT city, COUNT(*) AS Number of customers FROM Customers GROUP BY city 2)Largest customers in different cities ID How much is it? SELECT city, MAX(List names, such as customers ID) AS Largest customer ID FROM Customers GROUP BY city
1) Count the number of customers living in each city
2) Check the largest customer ID in different cities
5, having
1) Function
It is mainly used to further filter the grouped data and find records that meet the grouping conditions
(having is generally used in combination with group by. It is usually used in front of the aggregation function to filter the grouped data, (MAX, MIN, COUNT, SUM, etc.)
2) Syntax (Order: after group by, before order by)
SELECT column1,column2 FROM table1,table2 WHERE [conditions] GROUP BY column1,column2 HAVING [conditions] ORDER BY column1,column2
3) Use examples:
1)Query all records with city population greater than or equal to 2 SELECT city,COUNT(*) Number FROM Customers GROUP BY city HAVING COUNT(1) >1 2)--Statistics of departments with more than 300 employees select deptName, count(deptName) from user having count(deptName) > 300
6, order by
1) Function
Sort results
2) Grammar
SELECT Column name FROM Table name ORDER BY Column name ASC|DESC
3) Use examples:
1)Ascending order SELECT * FROM Customers ORDER BY province ASC 2)Descending order SELECT * FROM Customers ORDER BY province DESC 3)Mixed ascending and descending SELECT * FROM Customers ORDER BY province ASC, full name DESC
1) Ascending order
2) Descending order
7, LIKE
1) Function
In the WHERE sentence, it is used to specify a pattern to search for data, usually called fuzzy search
2) Grammar
SELECT column1,column2,... FROM table_name WHERE columnN LIKE pattern; Two common wildcards and LIKE Operators together % : The percent sign indicates zero, one or more characters _ : The underline represents a single character
- Usage of LIKE operator
WHERE address LIKE 'a%' Find to a Any value at the beginning WHERE address LIKE '%a' Find to a Any value at the end WHERE address LIKE '%or%' Find anywhere containing or Value of WHERE address LIKE '_r%' In the second location, find any r Value of WHERE address LIKE 'a_%_%' Find to a A value that begins with and is 3 characters long WHERE address LIKE 'a%o' Found with a Begin with o End value
8, IN
1) Function
It is allowed to specify multiple values in the WHERE clause, which can be understood as the abbreviation of the OR condition
2) Grammar
1)Grammar I SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); 2)Grammar II SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);
3) Examples of use
1)Find provinces in'Shanghai','Beijing','Guangdong Province'All users of SELECT * FROM Customers WHERE province IN ('Shanghai' , 'Beijing' ,'Guangdong Province'); 2)Find not in'Zhejiang Province','Shanghai'or'Beijing'All customers in SELECT * FROM Customers WHERE province NOT IN ('Zhejiang Province','Shanghai','Beijing'); 3)Find suppliers'Suppliers'Customer information from the same city as the customer SELECT * FROM Customers WHERE city IN (SELECT city FROM Suppliers);
9, BETWEEN AND
1) Function
Find all data between two values, including start value and end value, which is equivalent to > = and<=
2) Grammar
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
3) For example:
1)Find items with prices between 30 and 60 SELECT * FROM Products WHERE Price BETWEEN 30 AND 60; 2)Find items with prices between 30 and 60 SELECT * FROM Products WHERE Price NOT BETWEEN 30 AND 60; 3)Date boundary problem(Find order date,Remember, the default date is 00:00:00) SELECT * FROM Orders WHERE Order date: BETWEEN '2018-06-28' AND '2018-09-28';
10, UNION
1) Function
Combine the result sets of two or more SELECT statements
2) Premise of use
1) Each select statement in UNION must have the same number of columns
2) These columns must also have similar data types
3) The columns in each SELECT statement must also be arranged in the same order
3) Grammar
SELECT column_name(s) FROM table1 UNION (ALL) SELECT column_name(s) FROM table2;
4) Use examples:
- Data source (two tables)
1)Select two different cities (only different values) SELECT city FROM customers_bak UNION #De duplication of the two results SELECT city FROM Suppliers 2)from customers_bak and Suppliers Select all cities in (exclude all values, including duplicate values) SELCET city FROM customers_bak UNION ALL #The two results are not duplicated, and they are spliced together SELECT city FROM Suppliers;
1)
2)
11, JOIN
1) Function
- concept
join is used to combine rows from two or more tables and make an association based on the common fields of these tables - Common join types
1) INNER JOIN
2) LEFT JOIN
3) RIGHT JOIN right link
4)FULL JOIN
5)CROSS JOIN
2) Grammar
- Internal connection
SELECT A.Num_a,B.Num_B FROM A #Table A is inline with table B, and the INNER here can be omitted INNER JOIN B ON A.Num_A = B.Num_B #ON indicates that this column is used for association
- Left link (first show all the left tables, and then match the similar values on the right)
SELECT A.Num_a,B.Num_B FROM A #The coordinates will be displayed, and NULL will be displayed if there is no match LEFT JOIN B ON A.Num_A = B.Num_B;
- Full join (both tables are displayed, and null is displayed if there is no match)
SELECT A.Num_a,B.Num_B FROM A FULL JOIN B ON A.Num_A = B.Num_B;
- Cross join (product matching)
SELECT A.Num_a,B.Num_B FROM A CROSS JOIN B;
- explain
Each parameter of table A matches each parameter of table B
3) Use examples:
12, SELECT INTO
1) Function
2) Examples:
13, CREATE
14, VIEW usage
15, Usage of constraints
16, SQL execution order
- Sequential ranking
1)SELECT 2) 3) 4) 5) 6) 7) 8) 9) 10) 11)