MYSQL Basics__ Query language DQL

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;

 

Tags: SQL MySQL Oracle Windows

Posted on Mon, 29 Jun 2020 03:51:57 -0400 by weevil