The first chapter is the basic syntax specification of SQL
1.sql is not case sensitive, but it is customary for keywords, system reserved words, function names to be uppercase, and table and column names to be lowercase
2.sql uses space or carriage return to separate each word. It is customary for each clause to wrap. Each condition in the WHERE clause occupies one line and the subquery occupies a separate line
3. It is better to add notes to complex statements for explanation
① . single line comment
#This is a single line comment example 1 --This is example 2 of single line annotation (Note: here is "-", and there are spaces at the end that cannot be ignored)② . multiline comment
/* This is an example of multiline annotation 1 This is a multiline annotation example 1 This is a multiline annotation example 1 This is a multiline annotation example 1 This is an example of multiline annotation 1 */4. The semicolon is used as the separator in SQL statement, and the system will execute the statement only when the semicolon is read
/* select column name 1, column name 2 The column name n defines the column where the query data is located from table name 1, table name 2 Table name n defines the table where the query data is located where qualifier 1 and/or qualifier 2... Defines the qualification (row) of query data group by column name 1, column name 2 Column name n defines the grouping method of grouping function having grouping condition 1 and/or grouping condition 2 Define the qualification conditions for the results of grouping functions order by column name 1 asc/desc, column name 2 asc/desc...... define the sorting method of query results */Chapter II basis of SELECT
1. * for all columns
SELECT * FROM emp; SELECT * FROM dept; SELECT * FROM salgrade; SELECT * FROM bonus;
2. Query the specified column
SELECT ename,job,sal,deptno FROM emp;
3. Calculate the query results
-- Query the annual salary of each employee SELECT ename,12*sal FROM emp; -- The employee's year-end bonus is set at 10% of the annual salary%Plus 500 SELECT ename,(12*sal)*0.1+500 FROM emp;
4. The result of null value participating in operation is also null value
-- Calculate employee's annual income SELECT ename,sal,comm,12*(sal+comm) FROM emp;
5. Alias
-- Aliases can be listed in the query results /* Column name alias Column name AS alias If the alias contains special characters (spaces), double quotation marks are required Double quotation marks only appear when you name an alias. You can only use single quotation marks everywhere, not double quotes */ SELECT ename xingming,job zhiwei FROM emp; SELECT ename AS xingming,job AS zhiwei FROM emp; SELECT ename full name,job position FROM emp; SELECT ename "xing ming",job "zhi wei" FROM emp; -- Alias is mainly used to identify the operation result SELECT ename,(12*sal)*0.1+500 AS nianzhongjiang FROM emp;
6. Expression
-- Expression can be divided into number expression, character expression and date expression according to different data types -- Manually entered expressions can be written in select Later, the expression is displayed as many times as there are rows in the table -- Manually input numeric expression can be written directly. Date and character expressions must be enclosed in single quotation marks SELECT 123.456,12*60*48,'I am the king' FROM emp; -- mysql Allow write only select，No writing from;oracle It has to be written select and from，Can't be missing -- If all the data to be queried are entered manually, it has nothing to do with the data in the table, and it can be left blank from SELECT 123.456,12*60*48,'I am the king'; -- Manual strings must be in single quotation marks, mysql stay windows The platform string is not case sensitive, but in the linux Case sensitive -- Other databases, including programming languages, are case sensitive SELECT 'who are you',ename FROM emp; SELECT job,'JOB' FROM emp;
7.distinct
SELECT job FROM emp; SELECT DISTINCT job FROM emp; SELECT DISTINCT deptno FROM emp; -- Multiple column de duplication SELECT DISTINCT job,deptno FROM emp; -- Note: ordinary columns cannot be queried with de duplicated columns at the same time, because the number of rows does not match and cannot form a table -- select ename,distinct job from emp;
8.limit paging query
SELECT * FROM emp; -- If you want to get a fixed number of rows in the query results, use the limit -- limit Write at the end of the query statement -- limit m,n From m Line start display n Row data, the line number of the first row is 0 SELECT * FROM emp LIMIT 2,5; -- limit n Indicates before display n Row data SELECT * FROM emp LIMIT 3;
The third chapter limits and sorts
1.where clause limits
/* where Clause must be followed by a complete logical expression with only two kinds of true or false results In the where clause, logical expression is usually a comparison operation, whether the comparison condition is satisfied or not = > < >= <= !=(<>) where Clause qualification is usually a column of data in the table as a condition to return the data that meets the condition */ -- 1.Query employee information of department 20 SELECT * FROM emp WHERE deptno = 20; -- 2.Query the information of employees whose salary is higher than 2000 SELECT * FROM emp WHERE sal > 2000; -- 3.Query position is not manager Employee information for SELECT * FROM emp WHERE job != 'MANAGER'; -- Manual input string should be enclosed with single quotation marks, and the case should be consistent when matching -- 4.Query employee information with annual salary less than 20000 -- Column alias cannot appear in where Clause SELECT ename,job,sal,12*sal nianxin,deptno FROM emp WHERE 12*sal < 20000; -- Other databases are not supported*Add other columns to the query, mysql allow SELECT *,12*sal nianxin FROM emp; -- where The condition in the clause can also be a comparison of two columns of data SELECT * FROM emp WHERE sal < comm; -- where Columns in clauses as conditions may not appear in select But the result of such a statement is not intuitive enough SELECT ename,job,deptno FROM emp WHERE sal <= 1500; /* where The data types at both ends of the comparison operator in the clause must be the same If it is another database, an error will be reported when the data type is inconsistent mysql Because it is a weak data type language, it will not report errors, but such statements are meaningless */ SELECT * FROM emp WHERE ename > 2; -- If no data meets the criteria, there is no row of data SELECT * FROM emp WHERE deptno > 200; -- If there is data that meets the conditions, at least one row will be returned, except that the value of the data may be null SELECT comm FROM emp WHERE ename = 'SMITH';
2. Common special comparison operations
-- in(value1,value,2...valueN)， List matching SELECT * FROM emp WHERE job IN ('MANAGER','CLERK','PRESIDENT'); -- between minimum value and Maximum value, range matching, the position of maximum value and minimum value cannot be changed SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000; /* like, Fuzzy matching (for characters) Wildcards, special symbols that match or represent other characters _ Represents 1 arbitrary character % Represents any arbitrary character, including 0, 1 and more than one character */ SELECT * FROM emp WHERE ename LIKE 'S%'; SELECT * FROM emp WHERE ename LIKE '%S'; SELECT * FROM emp WHERE ename LIKE '%A%'; SELECT * FROM emp WHERE ename LIKE '_A%'; SELECT * FROM emp WHERE ename LIKE '_____'; -- is null, Match null (cannot be written as =null) SELECT * FROM emp WHERE comm IS NULL;
3. Logical operation
-- and And operation, all conditions must be met to return the result SELECT * FROM emp WHERE deptno = 20 AND sal > 2000;
-- or Or operation, multiple conditions meet any one can return the result SELECT * FROM emp WHERE deptno = 20 OR sal > 2000;
-- not Non operation, return the result that does not meet the condition SELECT * FROM emp WHERE NOT sal > 2000; -- If it's a comparison of mathematical symbols, not It has to be written first -- If it's a comparison in English, not It can be written first or in the middle of the operator SELECT * FROM emp WHERE deptno NOT IN (10,20);
4. Operation priority: mathematical operation > comparison operation > not > and > or
-- You can use parentheses to change the priority of operations SELECT * FROM emp WHERE deptno = 20 OR sal > 2000 AND job IN ('MANAGER','CLERK'); SELECT * FROM emp WHERE (deptno = 20 OR sal > 2000) AND job IN ('MANAGER','CLERK');
5. Sort ORDER BY clause
/* order by Also after the query statement, but before limit asc It means ascending order. It can be left blank by default desc Indicates descending order */ SELECT * FROM emp ORDER BY sal ASC; -- All data types can be sorted SELECT * FROM emp WHERE deptno = 20 ORDER BY hiredate DESC; SELECT * FROM emp ORDER BY ename; -- You can use aliases to sort SELECT *,12*sal nianxin FROM emp ORDER BY nianxin DESC; -- Multi column sort SELECT * FROM emp ORDER BY deptno,sal DESC; -- In multi column sorting, columns with duplicate data are written first -- Query the information of the top three employees SELECT * FROM emp ORDER BY sal DESC LIMIT 3;