Cool breeze AAA 2022-02-13 07:35:14 阅读数:30
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
1946 year , The world's first computer was born , Now , The Internet developed by this computer has become a Jianghu . In these decades , Countless technologies 、 Industry rises and falls in this Jianghu , Some are in the ascendant , Some have gone up and down for several scenes . But in this vast wave , One technology has never disappeared , even to the extent that “ Hale and hearty ”, That's it SQL.
SQL The half-life of can be said to be very long
了 .Whether it's a front-end Engineer , Back end algorithm engineer , Are bound to deal with data , We all need to know how to extract the data we want quickly and accurately . Not to mention data analysts , Their job is to deal with data , Organize different reports , To guide business decisions .
SQL(Structured Query Language, Structured query language ) yes Database application language using relational model , Dealing directly with data
, from IBM
In the last century 70 Developed in the s . Then by the National Bureau of standards (ANSI) Start working on SQL standard , have SQL-86
,SQL-89
,SQL-92
,SQL-99
Equal standard .
Different database manufacturers support SQL sentence , But they all have their own content .
since SQL Joined the TIOBE Programming language leaderboards , Just keep it Top 10.
SQL The function of language is mainly divided into the following 3 Categories: :( Different people may be classified differently )
DDL(Data Definition Languages、 Data definition language ), These statements define different databases 、 surface 、 View 、 Index and other database objects , It can also be used to create 、 Delete 、 Modify the structure of database and data table .
CREATE
、DROP
、ALTER
etc. .DML(Data Manipulation Language、 Data operation language ), Used to add 、 Delete 、 Update and query database records , And check data integrity .
INSERT
、DELETE
、UPDATE
、SELECT
etc. .DCL(Data Control Language、 Data control language ), Used to define the database 、 surface 、 Field 、 User's access rights and security level .
GRANT( Give relevant permissions to the transaction )
、REVOKE
、COMMIT
、ROLLBACK
、SAVEPOINT
etc. .Because query statements are used very frequently , So many people bring out the query statement list :DQL( Data query language ).
And alone will
COMMIT
( Submit )、ROLLBACK( Roll back )
Take it out and call it TCL (Transaction Control Language, Transaction control language ).
;
or \g or \G end ( If there is only one statement to write, you can't use a semicolon , If multiple statements must be written with semicolons .)You can use the annotation structure in the following format
Single-line comments :# Note text (MySQL In a particular way )
Single-line comments :-- Note text (-- Must be followed by a space .)
Multiline comment :/* Note text */
give an example :
# The following two sentences are the same , Case insensitive
show databases;
SHOW DATABASES;
# Create a table
#create table student info(...); # Table name error , Because the table name has spaces
create table student_info(...);
# among order Use `` Floating horn , because order It has the same name as the predefined identifier such as system keyword or system function name
CREATE TABLE `order`(
id INT,
lname VARCHAR(20)
);
select id as " Number ", `name` as " full name " from t_stu; # When it comes to aliases ,as All can be omitted
select id as Number , `name` as full name from t_stu; # If there is no space in the field alias , Then you can omit ""
select id as Ed Number , `name` as surname name from t_stu; # error , If there are spaces in the field alias , Then you can't omit ""
Mode one :Dos Window command line :source + The full pathname of the file .
Mode two : Using graphical tools , such as SqlYog: choice " Tools "–“ perform sql Script ”—“ Choose xxx.sql that will do ”
Log in to the command line client mysql, Use source Command import
mysql> source d:\mysqldb.sql
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
SELECT 1; # There are no clauses
SELECT 9/2; # There are no clauses
# Equivalent to
select 1 from dual; #dual False watch , Simple operation, there is no need to query the table, so use pseudo table instead .
# Fields can be written in multiple fields , Separated by commas
SELECT Field 1, Field 2,... FROM Table name
# *: All fields in the table ( Column )
# The result of a database query is called a result set
SELECT * FROM departments;
In general , Unless you need to use all the field data in the table , It is best not to use wildcards ‘*’. Using wildcards can save time in entering query statements , But get unnecessary column data It usually reduces the efficiency of queries and applications used . The advantage of wildcards is , When you don't know the name of the desired column , You can get them through it .
In production environment , It is not recommended that you use
SELECT *
The query .
# You can wrap
SELECT department_id, location_id
FROM departments;
MySQL Medium SQL Statements are case insensitive , therefore SELECT and select The effect is the same , however , Many developers are used to capitalize keywords 、 Data columns and table names are lowercase , Readers should also develop a good programming habit , The code written in this way is easier to read and maintain .
Rename a column
Easy to calculate
Keep up with the column name , It's fine too Add keywords between column names and aliases AS, Aliases use double quotes , To include spaces or special characters in the alias and be case sensitive .
AS It can be omitted
It is recommended that aliases be short , See the name and know the meaning
give an example
SELECT last_name AS name, commission_pct comm
FROM employees;
# Be careful : If an alias is a word, you don't need to add "", If there are multiple separate words "Annual Salary" You need to add "", Otherwise, I think Annual Alias , Overall error reporting .
# List the recommended double quotes , Don't use single quotes .mysql Grammar is not rigorous in oracle If you use single quotation marks in, you will report an error .
# Single quotation marks are recommended for strings and dates
SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;
By default , The query will return all rows , Include repeating lines .
# Query which departments are in the employee table id Well ?
# FALSE : No weight loss
SELECT department_id FROM employees;
stay SELECT Use keywords in statements DISTINCT Remove duplicate lines
# Correct : De duplication
SELECT DISTINCT department_id FROM employees;
Aim at :
# Just not reporting an error , But it has no practical significance . Different means different as a whole : As long as the colleague 2 If the two columns are not completely consistent, it will be displayed .
SELECT DISTINCT department_id,salary FROM employees;
Here are two points to note :
SELECT salary, DISTINCT department_id FROM employees
Will report a mistake .DISTINCT department_id
that will do , There is no need to add other column names .# 1. Null value :null
# 2. null Not equal to 0,'','null'
SELECT * FROM employees;
#3. Null values participate in the operation : The result must also be empty .
SELECT employee_id,salary " Monthly wages ",salary * (1 + commission_pct) * 12 " Annual wage ",commission_pct
FROM employees;
# Solutions to practical problems : introduce IFNULL
SELECT employee_id,salary " Monthly wages ",salary * (1 + IFNULL(commission_pct,0)) * 12 " Annual wage ",commission_pct
Here you must pay attention to , stay MySQL Inside , A null value is not equal to an empty string . The length of an empty string is 0, The length of a null value is null . and , stay MySQL Inside , Null values take up space .
# When querying, it indicates that the keyword is the same as that of the database , Just a couple `` After marking , You can query successfully .
mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1
mysql> SELECT * FROM `ORDER`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
We need to ensure that the fields in the table 、 Table names, etc. have no and reserved words 、 Database systems or common methods conflict . If it's really the same , Please be there. SQL A pair of... Is used in the statement ``( mark of emphasis ) Lead up .
SELECT The query can also query constants . Right , Is in the SELECT Add a fixed constant column to the query result . The value of this column is specified by us , Not dynamically extracted from the data table .
You might ask why we have to query constants ?
SQL Medium SELECT Grammar does provide this function , Generally speaking, we only query data from one table , There is usually no need to add a fixed constant column , But if we want to integrate different data sources , Use the constant sequence as the marker of this table , You need to query constants .
for instance , We want to be right employees Query the employee name in the data table , At the same time, add a column of fields corporation
, The fixed value of this field is “ Silicon Valley ”, It can be written like this :
# It means if ` Silicon Valley ' as long as 1 That's ok ,123,employee_id There are many lines in other fields 107 That's ok , Then the result of the query is 107 Rows, and each row adds ` Silicon Valley '
SELECT ' Silicon Valley ',123,employee_id,last_name
FROM employees;
Use DESCRIBE or DESC command , Express Table structure .
# You can view data types , Primary key , Is it allowed to be Null, Constraints etc. .
DESCRIBE employees;
or
DESC employees;
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
among , The meanings of each field are explained as follows :
SELECT Field 1, Field 2
FROM Table name
WHERE Filter conditions
Use WHERE Clause , Filter out rows that do not meet the criteria
WHERE Clause in the wake of FROM Clause
give an example
# Filter conditions , The statement in FROM Behind the structure
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
Arithmetic operators are mainly used for mathematical operations , It can connect two values or expressions before and after the operator , Add... To a value or expression (+)、 reduce (-)、 ride (*)、 except (/) And modulus (%) operation .
1. Addition and subtraction operators
#1. Arithmetic operator : + - * / div % mod
mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual;
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 | 100 | 150 | 120 | 135.5 | 64.5 |
+-----+---------+---------+----------+--------------+------------+------------+
1 row in set (0.00 sec)
# stay SQL in ,+ No connection , It means addition . here , Will convert the string to a numeric value ( Implicit conversion )
SELECT 100 + '1' # stay Java In language , The result is :1001.
FROM DUAL;
SELECT 100 + 'a' # At this time will be 'a' regard as 0 Handle
FROM DUAL;
SELECT 100 + NULL # null Value participates in the operation , The result is null
FROM DUAL;
The following conclusions can be drawn from the calculation results :
- An integer type value adds and subtracts integers , The result is still an integer ;
- An integer type value adds and subtracts floating-point numbers , The result is a floating point number ;
- Addition and subtraction have the same priority , The result of adding before subtracting is the same as that of subtracting before adding ;
- stay Java in ,+ If there is a string on the left and right sides of , Then it represents the splicing of strings . But in MySQL in + It only means that the values are added . If a non numeric type is encountered , First try to convert to a value , If the transfer fails , Just press the 0 Calculation .( Add :MySQL String functions are used in string splicing CONCAT() Realization )
2. Multiplication and division operators
# If the denominator is 0, The result is null
mysql> SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
| 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 /3 | 100 DIV 0 |
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
| 100 | 100 | 100.0 | 100.0000 | 50.0000 | 105.0000 | 33.3333 | NULL |
+-----+---------+-----------+-----------+---------+-----------------+---------+-----------+
1 row in set (0.00 sec)
# Calculate the annual basic salary of employees
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;
The following conclusions can be drawn from the calculation results :
- A number times an integer 1 And divided by an integer 1 After that, you still get the original number ;
- A number times a floating point number 1 And divided by floating point numbers 1 Then it becomes a floating point number , The value is equal to the original number ;
- A number divided by an integer , Whether or not we can eliminate , The result is a floating point number ;
- Divide one number by another , When there is no end to it , The result is a floating point number , And keep it after the decimal point 4 position ;
- Multiplication and division have the same priority , Perform the multiply then divide operation and the divide before multiply operation , The results are the same .
- In mathematics ,0 Cannot be used as a divisor , stay MySQL in , A number divided by 0 by NULL.
3. modulus ( Seeking remainder ) Operator
take t22 Fields in the table i Yes 3 and 5 Perform modulus calculation ( Seeking remainder ) operation .
# Modulus operation : % mod
# You can see that the positive and negative of the query result have nothing to do with the divisor , Just the same as the divisor .
SELECT 12 % 3,12 % 5, 12 MOD -5,-12 % 5,-12 % -5
FROM DUAL;
# select employee_id It's an even number of employees
SELECT * FROM employees
WHERE employee_id MOD 2 = 0;
You can see ,100 Yes 3 The result after finding the module is 3, Yes 5 The result after finding the module is 0.
The comparison operator is used to compare the operands on the left and right of the expression , If the comparison result is true, it returns 1, If the comparison result is false, it returns 0, Other cases return to NULL.
Comparison operators are often used as SELECT Use the conditions of the query statement , Return qualified result records .
1. The equal sign operator
The equal sign operator (=) Judge the values on both sides of the equal sign 、 Whether the string or expression is equal , If equal, return 1, Return if not equal 0.
When using the equal sign operator , Follow these rules :
contrast :SQL The assignment symbol in... Is used :=
SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' # The string has an implicit conversion . If the conversion of values is unsuccessful , As 0
FROM DUAL;
SELECT 'a' = 'a','ab' = 'ab','a' = 'b' # If there are strings on both sides , According to ANSI Compare with the rules of comparison .
FROM DUAL;
SELECT 1 = NULL,NULL = NULL # As long as there is null Participate in judgment , The results for null
FROM DUAL;
SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000;
WHERE commission_pct = NULL; # Execute at this time , There will be no result
mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual;
+-------+-----------+-----------+
| 1 = 2 | 0 = 'abc' | 1 = 'abc' |
+-------+-----------+-----------+
| 0 | 1 | 0 |
+-------+-----------+-----------+
1 row in set, 2 warnings (0.00 sec)
# Inquire about salary=10000, Pay attention to Java The comparison is ==
SELECT employee_id,salary FROM employees WHERE salary = 10000;
2. Security equals operator
Security equals operator (<=>) And equals operator (=) It's similar , The only difference
yes ‘<=>’ It can be used for NULL Judge . In both operands are NULL when , Its return value is 1, Not for NULL; When an operand is NULL when , Its return value is 0, Not for NULL.
mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual;
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL |
+-----------+---------+-------------+---------------------+-------------+---------------+
| 1 | 0 | 1 | 1 | 0 | 1 |
+-----------+---------+-------------+---------------------+-------------+---------------+
1 row in set (0.00 sec)
# Inquire about commission_pct be equal to 0.40
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;
# If you put 0.40 Change to NULL Well ?
You can see , When using the safe equals operator , The values of the operands on both sides are NULL when , The result returned is 1 instead of NULL, Other returned results are the same as the equal operator .
3. Not equal to the operator
Not equal to the operator (<> and !=) Used to judge the numbers on both sides 、 Whether the values of strings or expressions are not equal , Returns if not equal 1, Equal returns 0. Not equal to operator cannot judge NULL value . If either of the values on both sides is NULL, Or on both sides NULL, The result is NULL.
SQL Examples of statements are as follows :
mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
+--------+--------+------------+----------------+-------------+--------------+
| 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL |
+--------+--------+------------+----------------+-------------+--------------+
| 0 | 1 | 1 | 1 | NULL | NULL |
+--------+--------+------------+----------------+-------------+--------------+
1 row in set (0.00 sec)
Besides , There are also non symbolic operators :
4. Air transport operator
Air transport operator (IS NULL perhaps ISNULL) Determines whether a value is NULL, If NULL Then return to 1, Otherwise return to 0.
SQL Examples of statements are as follows :
mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
| 1 | 1 | 0 | 0 |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)
# Inquire about commission_pct be equal to NULL. Compare the following four ways of writing
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
5. Non air transport operators
Non air transport operators (IS NOT NULL) Judge whether a value is not NULL, If not for NULL Then return to 1, Otherwise return to 0.
SQL Examples of statements are as follows :
mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL;
+------------------+-----------------+---------------+
| NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL |
+------------------+-----------------+---------------+
| 0 | 1 | 1 |
+------------------+-----------------+---------------+
1 row in set (0.01 sec)
# Inquire about commission_pct It's not equal to NULL
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);
6. Minimum operator
The grammar format is :LEAST( value 1, value 2,…, value n). among ,“ value n” Indicates that there is... In the parameter list n It's worth . In the case of two or more parameters , Return minimum .
mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
| 0 | a | NULL |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)
As can be seen from the results , When the parameter is an integer or floating point number ,LEAST Will return the smallest value ; When the parameter is a string , Returns the first character in the alphabet ; When there is... In the comparison value list NULL when , Cannot judge size , The return value is NULL.
7. Maximum operator
The grammar format is :GREATEST( value 1, value 2,…, value n). among ,n Indicates that there is... In the parameter list n It's worth . When there are two or more parameters , The return value is the maximum value . If any of the independent variables is NULL, be GREATEST() The return value of is NULL.
mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
| 2 | c | NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)
As can be seen from the results , When the parameter is an integer or floating point number ,GREATEST Will return the largest value ; When the parameter is a string , Returns the last character in the alphabet ; When there is... In the comparison value list NULL when , Cannot judge size , The return value is NULL.
8. BETWEEN AND Operator
BETWEEN The format used by the operator is usually SELECT D FROM TABLE WHERE C BETWEEN A AND B, here , When C Greater than or equal to A, also C Less than or equal to B when , The result is 1, Otherwise, the result is 0.
mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';
+-------------------+----------------------+-------------------------+
| 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' |
+-------------------+----------------------+-------------------------+
| 1 | 0 | 1 |
+-------------------+----------------------+-------------------------+
1 row in set (0.00 sec)
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
9. IN Operator
IN Operator is used to determine whether a given value is IN A value in the list , If so, return 1, Otherwise return to 0. If the given value is NULL, perhaps IN List exists NULL, The result is NULL.
mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
10. NOT IN Operator
NOT IN Operator is used to determine whether a given value is not IN A value in the list , If not IN A value in the list , Then return to 1, Otherwise return to 0.
mysql> SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
+--------------------------+----------------+
| 'a' NOT IN ('a','b','c') | 1 NOT IN (2,3) |
+--------------------------+----------------+
| 0 | 1 |
+--------------------------+----------------+
1 row in set (0.00 sec)
11. LIKE Operator
LIKE Operators are mainly used to match strings , Usually used for fuzzy matching , If the condition is met, return 1, Otherwise return to 0. If the given value or matching condition is NULL, Then the returned result is NULL.
LIKE Operators usually use the following wildcards :
“%”: matching 0 Characters or more .
“_”: Only one character can be matched .
SQL Examples of statements are as follows :
mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL;
+-----------------+-----------------+
| NULL LIKE 'abc' | 'abc' LIKE NULL |
+-----------------+-----------------+
| NULL | NULL |
+-----------------+-----------------+
1 row in set (0.00 sec)
SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';
SELECT last_name
FROM employees
WHERE last_name LIKE '_o%';
ESCAPE
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT\_%‘;
SELECT job_id
FROM jobs
WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;
12. REGEXP Operator
REGEXP Operators are used to match strings , The grammar format is :expr REGEXP Matching condition
. If expr Meet the matching conditions , return 1; If not satisfied , Then return to 0. if expr Or any one of the matching conditions is NULL, The result is NULL.
REGEXP Operator when matching , There are several commonly used wildcards :
(1)‘^’ Matches a string that begins with the character after the character .
(2)‘$’ Matches a string that ends with a character that precedes the character .
(3)‘.’ Match any single character .
(4)“[...]” Match any character in square brackets . for example ,“[abc]” matching “a” or “b” or “c”. To name the range of characters , Use one ‘-’.“[a-z]” Match any letter , and “[0-9]” Match any number .
(5)‘*’ Match zero or more characters before it . for example ,“x*” Match any number of ‘x’ character ,“[0-9]*” Match any number of numbers , and “*” Match any number of any characters .
SQL Examples of statements are as follows :
mysql> SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
+------------------------+------------------------+-------------------------+
| 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' |
+------------------------+------------------------+-------------------------+
| 1 | 1 | 1 |
+------------------------+------------------------+-------------------------+
1 row in set (0.01 sec)
mysql> SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]';
+--------------------------+-------------------------+
| 'atguigu' REGEXP 'gu.gu' | 'atguigu' REGEXP '[ab]' |
+--------------------------+-------------------------+
| 1 | 1 |
+--------------------------+-------------------------+
1 row in set (0.00 sec)
Logical operators are mainly used to judge whether an expression is true or false , stay MySQL in , The return result of the logical operator is 1、0 perhaps NULL.
MySQL Chinese support 4 The logical operators are as follows :
1. Logical nonoperator
Logic is not (NOT or !) Operator means when the given value is 0 When to return to 1; When the given value is non 0 Value returns 0; When the given value is NULL when , return NULL.
mysql> SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
+-------+-------+----------+--------+----------+
| NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL |
+-------+-------+----------+--------+----------+
| 0 | 1 | 0 | 1 | NULL |
+-------+-------+----------+--------+----------+
1 row in set, 1 warning (0.00 sec)
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
2. Logic and operators
Logic and (AND or &&) Operator is when all values given are non 0 value , And not for NULL when , return 1; When a given value or values are 0 When you return to 0; Otherwise return to NULL.
mysql> SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;
+----------+---------+------------+------------+
| 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL |
+----------+---------+------------+------------+
| 1 | 0 | 0 | NULL |
+----------+---------+------------+------------+
1 row in set (0.00 sec)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%';
3. Logical or operator
Logic or (OR or ||) The operator is when the given value is not NULL, And any value is non 0 When the value of , Then return to 1, Otherwise return to 0; When a value is NULL, And the other value is non 0 When the value of , return 1, Otherwise return to NULL; When both values are NULL when , return NULL.
mysql> SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
+---------+--------+-----------+-----------+--------------+
| 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL |
+---------+--------+-----------+-----------+--------------+
| 1 | 1 | 1 | NULL | NULL |
+---------+--------+-----------+-----------+--------------+
1 row in set, 2 warnings (0.00 sec)
# Query basic salary is not in 9000-12000 Between employee number and base salary
SELECT employee_id,salary FROM employees
WHERE NOT (salary >= 9000 AND salary <= 12000);
SELECT employee_id,salary FROM employees
WHERE salary <9000 OR salary > 12000;
SELECT employee_id,salary FROM employees
WHERE salary NOT BETWEEN 9000 AND 12000;
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';
Be careful :
OR You can talk to AND Use it together , But pay attention to the priority of the two when using , because AND Has a higher priority than OR, So first of all AND The operands on both sides operate , And again OR The operands in .
4. Logical XOR operator
Logical XOR (XOR) Operator is when any one of the given values is NULL when , Then return to NULL; If two are not NULL The values are all 0 Or it doesn't mean 0 when , Then return to 0; If a value is 0, The other value is not 0 when , Then return to 1.
mysql> SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;
+----------+---------+---------+------------+---------------+---------------+
| 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 |
+----------+---------+---------+------------+---------------+---------------+
| 0 | 1 | 0 | NULL | 1 | 0 |
+----------+---------+---------+------------+---------------+---------------+
1 row in set (0.00 sec)
select last_name,department_id,salary
from employees
where department_id in (10,20) XOR salary > 8000;
Bit operators are operators that evaluate on binary numbers . Bit operators first convert operands to binary numbers , And then we do bit operations , Finally, the calculation result is changed from binary to decimal .
MySQL The supported bitwise operators are as follows :
1. Bitwise and operator
Bitwise AND (&) Operator performs logical and operation bit by bit on the binary number corresponding to the given value . When the value of the binary bit corresponding to the given value is 1 when , Then the bit returns 1, Otherwise return to 0.
mysql> SELECT 1 & 10, 20 & 30;
+--------+---------+
| 1 & 10 | 20 & 30 |
+--------+---------+
| 0 | 20 |
+--------+---------+
1 row in set (0.00 sec)
1 The binary number of is 0001,10 The binary number of is 1010, therefore 1 & 10 As the result of the 0000, The corresponding decimal number is 0.20 The binary number of is 10100,30 The binary number of is 11110, therefore 20 & 30 As the result of the 10100, The corresponding decimal number is 20.
2. bitwise or operator
Press bit or (|) Operator performs logical or operation bit by bit on the binary number corresponding to a given value . When one or two of the binary values corresponding to a given value are 1 when , Then the bit returns 1, Otherwise return to 0.
mysql> SELECT 1 | 10, 20 | 30;
+--------+---------+
| 1 | 10 | 20 | 30 |
+--------+---------+
| 11 | 30 |
+--------+---------+
1 row in set (0.00 sec)
1 The binary number of is 0001,10 The binary number of is 1010, therefore 1 | 10 As the result of the 1011, The corresponding decimal number is 11.20 The binary number of is 10100,30 The binary number of is 11110, therefore 20 | 30 As the result of the 11110, The corresponding decimal number is 30.
3. bitwise exclusive or operator
Bitwise XOR (^) Operator performs logical XOR operation bit by bit on the binary number corresponding to the given value . When the value of the binary bit corresponding to the given value is different , Then the bit returns 1, Otherwise return to 0.
mysql> SELECT 1 ^ 10, 20 ^ 30;
+--------+---------+
| 1 ^ 10 | 20 ^ 30 |
+--------+---------+
| 11 | 10 |
+--------+---------+
1 row in set (0.00 sec)
1 The binary number of is 0001,10 The binary number of is 1010, therefore 1 ^ 10 As the result of the 1011, The corresponding decimal number is 11.20 The binary number of is 10100,30 The binary number of is 11110, therefore 20 ^ 30 As the result of the 01010, The corresponding decimal number is 10.
Another example :
mysql> SELECT 12 & 5, 12 | 5,12 ^ 5 FROM DUAL;
+--------+--------+--------+
| 12 & 5 | 12 | 5 | 12 ^ 5 |
+--------+--------+--------+
| 4 | 13 | 9 |
+--------+--------+--------+
1 row in set (0.00 sec)
4. Bitwise negation operator
According to the not (~) Operator negates the binary number of a given value bit by bit , the 1 Turn into 0, take 0 Turn into 1.
mysql> SELECT 10 & ~1;
+---------+
| 10 & ~1 |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
Because bitwise inversion (~) The operator takes precedence over bitwise AND (&) Operator precedence , therefore 10 & ~1, First , Pairs of numbers 1 Reverse by bit , The result is except that the lowest is 0, The others are 1, Then with 10 Do bit and operation , The result is 10.
5. Bitwise shift right operator
Right shift to position (>>) Operator shifts all bits of the binary number of a given value to the right by the specified number of bits . Shift the specified number of bits to the right , The lower value on the right is removed and discarded , The left high position is empty with 0 A filling .
mysql> SELECT 1 >> 2, 4 >> 2;
+--------+--------+
| 1 >> 2 | 4 >> 2 |
+--------+--------+
| 0 | 1 |
+--------+--------+
1 row in set (0.00 sec)
1 The binary number of is 0000 0001, Move right 2 Position as 0000 0000, The corresponding decimal number is 0.4 The binary number of is 0000 0100, Move right 2 Position as 0000 0001, The corresponding decimal number is 1.
6. Bitwise shift left operator
Move left according to position (<<) Operator shifts all bits of the binary number of a given value to the left by the specified number of bits . Move the specified number of bits left , The higher left value is removed and discarded , On the right side, the low position is left by 0 A filling .
mysql> SELECT 1 << 2, 4 << 2;
+--------+--------+
| 1 << 2 | 4 << 2 |
+--------+--------+
| 4 | 16 |
+--------+--------+
1 row in set (0.00 sec)
1 The binary number of is 0000 0001, Move two to the left to 0000 0100, The corresponding decimal number is 4.4 The binary number of is 0000 0100, Move two to the left to 0001 0000, The corresponding decimal number is 16.
The larger the number , The higher the priority , Operators with higher priority are calculated first . You can see , The assignment operator has the lowest priority , Use “()” Enclosed expressions have the highest priority .
Regular expressions are often used to retrieve or replace text content that conforms to a pattern , Matches a special string in the text that meets the requirements according to the specified matching pattern . for example , Extract phone numbers from a text file , Find repeated words in an article or replace some sensitive words entered by the user , Regular expressions can be used in these places . Regular expressions are powerful and flexible , It can be applied to very complex queries .
MySQL Use in REGEXP Keyword specifies the character matching pattern of the regular expression . The following table lists them REGEXP A list of common character matches in the operator .
1. Query records starting with a specific character or string
character ‘^’ Matches text that begins with a specific character or string .
stay fruits In the table , Inquire about f_name Fields are represented by letters ‘b’ The first record ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
2. Query records ending with a specific character or string
character ‘$’ Matches text that ends with a specific character or string .
stay fruits In the table , Inquire about f_name Fields are represented by letters ‘y’ The record at the end ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
3. Use symbols "." To replace any character in the string
character ‘.’ Matches any character .
stay fruits In the table , Inquire about f_name The field value contains the letters ‘a’ And ‘g’ And there is only one letter between two letters ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
4. Use "*“ and ”+" To match multiple characters
asterisk ‘*’ Match the preceding character any number of times , Include 0 Time . plus ‘+’ Match the preceding character at least once .
stay fruits In the table , Inquire about f_name Field values are in letters ‘b’ And ‘b’ The letters appear after them ‘a’ The record of ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
stay fruits In the table , Inquire about f_name Field values are in letters ‘b’ And ‘b’ The letters appear after them ‘a’ At least one record ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
5. Matches the specified string
A regular expression can match a specified string , As long as this string is in the query text , To match multiple strings , Use separator between multiple strings ‘|’ separate .
stay fruits In the table , Inquire about f_name The field value contains a string “on” The record of ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
stay fruits In the table , Inquire about f_name The field value contains a string “on” perhaps “ap” The record of ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
Previously introduced ,LIKE Operator can also match the specified string , But with REGEXP Different ,LIKE If the matching string appears in the middle of the text , You can't find it , The corresponding line will not return .REGEXP Match within text , If the matched string appears in the text ,REGEXP It will be found , The corresponding line will also be returned . The comparison results are as follows .
stay fruits In the table , Use LIKE Operator query f_name The field values for “on” The record of ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name like 'on';
Empty set(0.00 sec)
6. Matches any one of the specified characters
square brackets “[]” Specify a character set , Match only any of these characters , This is the text you are looking for .
stay fruits In the table , lookup f_name The field contains letters ‘o’ perhaps ‘t’ The record of ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
stay fruits In the table , Inquire about s_id The field contains 4、5 perhaps 6 The record of ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';
7. Matches characters other than the specified characters
“[^ Character set ]”
Matches any character that is not in the specified set .
stay fruits In the table , Inquire about f_id The field contains letters ae And number 12 Records of characters other than ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
8. Use {n,} perhaps {n,m} To specify the number of consecutive occurrences of the string
“ character string {n,}” Means at least match n The next preceding character ;“ character string {n,m}” Indicates that the matching preceding string is not less than n Time , Not more than m Time . for example ,a{2,} For letters a Continuous occurrence of at least 2 Time , It can also be greater than 2 Time ;a{2,4} For letters a Continuous occurrence of the least 2 Time , Not more than 4 Time .
stay fruits In the table , Inquire about f_name A letter appears in the field value ‘x’ At least 2 The second record ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
stay fruits In the table , Inquire about f_name The field value appears as a string “ba” least 1 Time 、 most 3 The second record ,SQL The statement is as follows :
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
#1. Sort
# If no sort operation is used , By default, the data returned by the query is displayed in the order of adding data .
# Yes ORDER BY The key word , Don't write in ascending order The descending keyword defaults to ASC Ascending , If even orderby Keywords are not written , By default, the data returned by the query is displayed in the order of adding data .
SELECT * FROM employees;
# 1.1 Basic use
# Use ORDER BY Sort the queried data .
# Ascending :ASC (ascend)
# Descending :DESC (descend)
# practice : according to salary Display employee information from high to low
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
# practice : according to salary Display employee information from low to high
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary; # If in ORDER BY If there is no explicit naming sort after , Sort in ascending order by default .
#2. We can use column aliases , Sort
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
# Column aliases can only be used in ORDER BY Use in , Can't be in WHERE Use in .
# The following operation reports an error !
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600;
#3. Emphasis format :WHERE Need to declare in FROM after ,ORDER BY Before .
# The sorted field is not necessarily the field of the query , No mistake. .
/* Why is it where Column aliases cannot be used in ??? because :sql The query is not written according to sql From front to back : First step : Go ahead from see It's the watch , Looking at the screening criteria where. The second step : Let's see what you want to find out first select, At this point, you may define an alias . Third parts : At this time, there is already an alias, and the column alias can only be used after sorting , Before using where There is no column alias yet, so you can't use . Similarly, the table alias is in where There was... Before , So you can use .*/
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;
#4. Secondary sorting
# practice : Show employee information , according to department_id Descending order of , stay department_id Based on the descending order of salary In ascending order
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
background 1: The query returned too many records , It's inconvenient to check , How to implement paging query ?
background 2: There are 4 Data , We just want to show 2、3 What about this data ?
Pagination principle
Pagination display , The result set in the database , Show the required conditions paragraph by paragraph .
MySQL Use in LIMIT Implement paging
Format :
LIMIT [ Position offset ,] Row number
first “ Position offset ” Parameters indicate MySQL Which line to start with , Is an optional parameter , If you don't specify “ Position offset ”, It will start with the first record in the table ( The position offset of the first record is 0, The position offset of the second record is 1, And so on ); The second parameter “ Row number ” Indicates the number of records returned .
give an example
#2. Pagination
#2.1 mysql Use limit Realize pagination display of data
# demand 1: Each page shows 20 Bar record , The... Is displayed 1 page
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;
# demand 2: Each page shows 20 Bar record , The... Is displayed 2 page
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;
# demand 3: Each page shows 20 Bar record , The... Is displayed 3 page
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;
# demand : Each page shows pageSize Bar record , The... Is displayed pageNo page :
# The formula :LIMIT (pageNo-1) * pageSize,pageSize;
#2.2 WHERE ... ORDER BY ...LIMIT The order of declaration is as follows :
# LIMIT The format of : Strictly speaking :LIMIT Position offset , Number of entries
# structure "LIMIT 0, Number of entries " Equivalent to "LIMIT Number of entries "
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;
# practice : There are 107 Data , We just want to show 32、33 What about this data ?
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
#2.3 MySQL8.0 New characteristics :LIMIT ... OFFSET ...
# LIMIT Followed by the number displayed on each page ,OFFSET Write the position offset later , It's equivalent to reversing .
# practice : There are 107 Data , We just want to show 32、33 What about this data ?
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;
# practice : Query the information of the highest paid employee in the employee table
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;
MySQL 8.0 Can be used in “LIMIT 3 OFFSET 4”, It means to get from 5 A record starts after 3 Bar record , and “LIMIT 4,3;” The results returned are the same .
SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;
The number of results returned by the constraint can Reduce the network transmission of data tables
, It's fine too Improve query efficiency
. If we know that the return result is only 1 strip , You can use LIMIT 1
, tell SELECT Statement only needs to return a record . The good thing is SELECT There is no need to scan the complete table , You only need to retrieve a qualified record to return .
In different DBMS The keywords used in may be different . stay MySQL、PostgreSQL、MariaDB and SQLite Use in LIMIT keyword , And it needs to be put in SELECT At the end of the statement .
TOP
keyword , such as :SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
FETCH FIRST 5 ROWS ONLY
Such keywords :SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
ROWNUM
To count the number of lines :ROWNUM
Equivalent to a hidden column , Each column has .SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;
It should be noted that , This statement is taken out before 5 Data rows , And then follow hp_max Sort from high to low . But the result is different from that of the above method . I'll talk about sub query later , You can use
SELECT rownum, last_name,salary
FROM (
SELECT last_name,salary
FROM employees
ORDER BY salary DESC)
WHERE rownum < 10;
The results are consistent with the above methods .
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
Multi-table query , Also known as associative queries , It means that two or more tables complete the query operation together .
Prerequisite : There is a relationship between these tables queried together ( one-on-one 、 One to many ), There must be associated fields between them , This associated field may establish a foreign key , Or you may not have established a foreign key . such as : Employee table and department table , These two tables depend on “ Department number ” Association .
Why multi table query ???
# Check the name of the employee 'Abel' Which city do people work in ?
SELECT *
FROM employees
WHERE last_name = 'Abel';# In the employee form, name according to known conditions , You can find out the of that department id by 80
SELECT *
FROM departments
WHERE department_id = 80;# In the Department table, according to the Department where this person belongs id, Find out the location of this department id
SELECT *
FROM locations
WHERE location_id = 2500;# In the location table, according to the location id, Query the city .
explain : This is how to query 3 It can also be found out once , But each query requires a network connection, and the interaction takes time , Write 3 This query must be
Slower than once , So you don't have to query like this .
Why is it divided into 3 Zhang Biao , Can you make a table with me ???( A table is composed of several columns )
Sure , But the advantages and disadvantages .
Scene one : Suppose that the Department table IT department , Corresponding in the employee table 300 Data . The Department table writes the details of the Department , Now the Department table only needs to write 1 Data , The employee form needs to write
300 Data Only one department needs to be written after each data id Just number . If you write in one piece Each employee in the employee table should write a department simply from the original id Become a departmental
Details , Too many words make it redundant .
Scene two : Suppose that many employees now have no department , Now you only need to display in the Department table as null that will do , If they are written together, the employees of the Department should write detailed information ( Location , department , department id Wait a lot of fields ) Employees without departments cannot write department information , As a result, many fields are empty , Obviously not . This is the 2 A watch , More tables are more serious .
More fields, more questions :io The fields loaded at one time are limited , The more fields, the more loading times , Less efficient .
Getting data from multiple tables :3 The tables are : The employee table Departmental table Location table
# Case study : Query the employee's name and department name
# Wrong way 1
SELECT last_name, department_name
FROM employees, departments; # Query out 2889 Bar record
perhaps :
# Wrong way 2
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;# Query out 2889 Bar record
# analysis : There is a Cartesian product error Reason for the error : The connection condition of multiple tables is missing
Query results :
+-----------+----------------------+
| last_name | department_name |
+-----------+----------------------+
| King | Administration |
| King | Marketing |
| King | Purchasing |
| King | Human Resources |
| King | Shipping |
| King | IT |
| King | Public Relations |
| King | Sales |
| King | Executive |
| King | Finance |
| King | Accounting |
| King | Treasury |
...
| Gietz | IT Support |
| Gietz | NOC |
| Gietz | IT Helpdesk |
| Gietz | Government Sales |
| Gietz | Retail Sales |
| Gietz | Recruiting |
| Gietz | Payroll |
+-----------+----------------------+
2889 rows in set (0.01 sec)
Analyze the error :
SELECT COUNT(employee_id) FROM employees;
# Output 107 That's ok The employee list has 107 That's ok
SELECT COUNT(department_id)FROM departments;
# Output 27 That's ok The Department table has 27 That's ok
SELECT 107*27 FROM dual; #107*27=2889, Explain that all employees are matched under each department , This is called the present Cartesian product error .
We call the problem in the above multi table query as : The error of Cartesian product .
Cartesian product is a mathematical operation . Suppose I have two sets X and Y, that X and Y The Cartesian product of is X and Y All possible combinations of , That is, the first object comes from X, The second object comes from Y All the possibilities of . The number of combinations is the product of the number of elements in the two sets .
SQL92 in , Cartesian product is also called Cross connect
, English is CROSS JOIN
. stay SQL99 It is also used in CROSS JOIN Means cross connect . Its function is to connect any table , Even if the two tables are not related . stay MySQL Cartesian product will appear in the following cases :
# Query employee name and department name
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
The error of Cartesian product will occur under the following conditions :
To avoid Cartesian product , Sure stay WHERE Add a valid connection condition .
After adding connection conditions , The query syntax :
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; # Connection condition
stay WHERE Clause .
Write it correctly :
# Case study : Query the employee's name and its corresponding department name 107 An employee should find out 107 Data and corresponding department
#1. The employee table has 107 Data , Query out 106 Data , Because an employee has no department for null.
SELECT last_name, department_name
FROM employees, departments
# Join condition of two tables
WHERE employees.department_id = departments.department_id;
#2. If there are fields in multiple tables in the query statement , You must specify the table where this field is located .
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
# Suggest : from sql Optimization angle , It is recommended to query multiple tables , Each field is preceded by the table in which it is located .
reason : If there is no indication First, find out whether there is this field in each table for comparison . If specified, no other table will be found .
#3. You can alias a table , stay SELECT and WHERE Aliases for tables used in .
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
# If you alias a table , Once in a SELECT or WHERE If table names are used in , You must use the alias of the table , Instead of using the original name of the table . Column names do not have this usage .
# The following operation is wrong :
# reason : still sql The question of execution order , Go ahead from And alias the table , To walk again where At this time, it has been overwritten with aliases. Only table aliases can be used , To walk again select.
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;
#4. Conclusion : If there is n Multiple tables realize the query of multiple tables , At least n-1 Connection conditions
# practice : Check the employee's employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
expand 1: Multiple connection conditions and AND The operator
expand 2: Distinguish between duplicate column names
Table name
Distinguish .SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
expand 3: The table alias
Using aliases can simplify queries .
Using table name prefix before column name can improve query efficiency .
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
It should be noted that , If we use the alias of the table , In the query field 、 Only aliases can be used in the filter conditions , Cannot use the original table name , Otherwise you will report an error .
Ali Development Specification
:【
mandatory
】 For queries and changes to table records in the database , As long as multiple tables are involved , You need to add the alias of the table before the column name ( or Table name ) To limit .
explain
: Query records for multiple tables 、 Update record 、 When deleting records , If there is no alias of the qualified table for the operation column ( Or watch name ), And when the operation column exists in multiple tables , I'm going to throw an exception .
Example
:select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
Counter example
: In a business , Because the multi table associated query statement does not add the alias of the table ( Or watch name ) The limitation of , Two years after normal operation , Recently Add a field with the same name to a table , After making database changes in the pre release environment , The online query statement appears 1052 abnormal :Column ‘name’ in field list is ambiguous.
expand 4: Join multiple tables
summary : Connect n Tables , Need at least n-1 Connection conditions . such as , Join three tables , At least two join conditions are required .
practice : Query the employee's last_name,department_name, city
# The employee table Wage register this 2 There is no association relationship between tables
SELECT *
FROM job_grades;
# Find out the salary grade of the employee table ? The results displayed are from 2 A watch , this 2 The tables are not related to each other. You can use non equivalent queries .
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;
subject : Inquire about employees surface , return “Xxx works for Xxx”
# Query employees and their superiors , The data displayed in the query comes from the same table , Aliases need to be distinguished 2 A watch , When using equivalent query criteria .
SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
practice : Query out last_name by ‘Chen’ Of our employees manager Information about .
In addition to querying the records that meet the conditions , External join can also query records that one party does not meet the conditions .
1. Internal connection : Merging rows of more than two tables with the same column , There are no rows in the result set that do not match one table with another
2. External connection : In addition to returning rows that meet the connection conditions during the connection of two tables Also return to the left ( Or right ) Rows in the table that do not meet the criteria , This connection is called left ( Or right ) External connection . When there are no matching rows , The corresponding column in the result table is empty (NULL).
3. Classification of external connections : The left outer join 、 Right connection 、 Full outer join
3.1 The left outer join : In addition to the rows that meet the connection conditions, the two tables also return the rows that do not meet the conditions in the left table , This connection is called the left outer connection .
3.2 In addition to the rows that meet the connection conditions, the two tables also return the rows that do not meet the conditions in the right table , This connection is called the right outer connection .
4. If it's a left outer connection , The table on the left of the join condition is also called Main table
, The table on the right is called From the table
.
If it's a right outer connection , The table on the right in the join condition is also called Main table
, The table on the left is called From the table
.
stay SQL92 Used in (+) Represents the location of the slave table . That is, in the left or right outer connection ,(+) Indicates which is from the table .
Oracle Yes SQL92 Better support , and MySQL Do not support SQL92 External connection of .
Be careful : The multi table query connections mentioned above are SQL92 Standard internal connection .
#SQL92 Syntax implementation external connection : Use + ----------MySQL I won't support it SQL92 The writing of Chinese and foreign connections in grammar !oracle Support
# The left outer join
# That is to say + The representative from the table
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);# Main table From the table : The description is the left outer connection
# Right connection
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;# From the table Main table : Description is the right outer connection
SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 and table2 The connection condition of
JOIN table3 ON table2 and table3 The connection condition of
Its nesting logic is similar to what we use FOR loop :
for t1 in table1:
for t2 in table2:
if condition1:
for t3 in table3:
if condition2:
output t1 + t2 + t3
SQL99 This nested structure is very refreshing 、 More hierarchical 、 More readable , Even if more tables are connected, they are clearly visible . If you take SQL92, Readability will be greatly reduced .
Syntax description :
/* The keywords of the previous conditional statements are where, And in the sql99 It is customary to write conditional statements and keywords in grammar : Association conditions use on( Association statement : In general, and join In combination with ) Other common conditions use where, Of course where It's fine too , But separate writing is more readable .*/
SELECT Field list
FROM A surface INNER JOIN B surface
ON The associated condition
WHERE And so on ;
subject 1:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
subject 2:
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
# The query result is A
SELECT Field list
FROM A surface LEFT JOIN B surface
ON The associated condition
WHERE And so on ;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
# The query result is B
SELECT Field list
FROM A surface RIGHT JOIN B surface
ON The associated condition
WHERE And so on ;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
It should be noted that ,LEFT JOIN and RIGHT JOIN Is only found in SQL99 And later standards , stay SQL92 Does not exist in the , Only use (+) Express .
# Full outer join :mysql I won't support it FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
Merge query results
utilize UNION keyword , You can give more than one SELECT sentence , And combine their results into a single result set . When merging , The number of columns and data types of the two tables must be the same , And correspond to each other . each SELECT Use... Between statements UNION or UNION ALL Keyword separation .
Grammar format :
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION The operator
UNION Operator returns the union of the result sets of two queries , Remove duplicate records .
advantage : It's going to be weightless . shortcoming : Low efficiency .
UNION ALL The operator
UNION ALL Operator returns the union of the result sets of two queries . For duplicate parts of two result sets , No weight removal .
advantage : Efficient . shortcoming : No weight removal .
Because use UNION ALL The query efficiency of is higher , However, the disadvantage of query is that it does not need to redo , Then we want to use UNION ALL Query improves efficiency , And can remove weight
How to do it ???
You can use the left outer connection to remove the weight + The right external connection does not remove the duplication, so it realizes the whole external connection , Or the left outer connection is not de duplicated + The right outer connection is de duplicated . See below for details 5.7 All external connection query in .
Be careful : perform UNION ALL Statement requires more resources than UNION Few statements . If you know clearly that there is no duplicate data in the result data after data consolidation , Or you don't need to remove duplicate data , Try to use UNION ALL sentence , To improve the efficiency of data query .
give an example : Check department number >90 Or the mailbox contains a Employee information
# The way 1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
# The way 2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
give an example : Query the information of men in Chinese users and middle-aged men in American users
SELECT id,cname FROM t_chinamale WHERE csex=' male '
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';
# Chinese : Internal connection A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
# Top left : The left outer join
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
# Top right : Right connection
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# Middle left :A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
# Middle right :B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
# Bottom left : Full outer join
# The way 1: Top left UNION ALL Middle right
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# The way 2: Middle left UNION ALL Top right
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# Bottom right
# Middle left + Middle right A ∪B- A∩B perhaps (A - A∩B) ∪ (B - A∩B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
# Realization A - A∩B
select Field list
from A surface left join B surface
on The associated condition
where Associate fields from the table is null and And so on ;
# Realization B - A∩B
select Field list
from A surface right join B surface
on The associated condition
where Associate fields from the table is null and And so on ;
# The query result is A∪B
# Use the left outside A,union Right outside B
select Field list
from A surface left join B surface
on The associated condition
where And so on
union
select Field list
from A surface right join B surface
on The associated condition
where And so on ;
# Realization A∪B - A∩B or (A - A∩B) ∪ (B - A∩B)
# Use the left outside (A - A∩B) union Right outside (B - A∩B)
select Field list
from A surface left join B surface
on The associated condition
where Associate fields from the table is null and And so on
union
select Field list
from A surface right join B surface
on The associated condition
where Associate fields from the table is null and And so on
SQL99 stay SQL92 Provides some special syntax based on , such as NATURAL JOIN
Used to represent natural connections . We can understand natural connection as SQL92 The equivalent connection in . It will help you automatically query the two connection tables All the same fields
, Then proceed Equivalent connection
.
stay SQL92 In the standard :
#2 Connection conditions , More flexible , Write just a few... Using a few connection conditions .( Recommended )
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
stay SQL99 You can write :
# It will automatically help you query all connection conditions . shortcoming : inflexible , Force all connection conditions , But if you want to use a connection condition, there is no way to solve .
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
When we connect ,SQL99 Also supports the use of USING Specify... In the data table Fields with the same name
Make an equivalent connection . But only with JOIN Use it together . such as :
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
You can see the connection with nature NATURAL JOIN The difference is ,USING Specifies the specific same field name , You need to USING The brackets of () Fill in the field with the same name to be specified . Use at the same time JOIN...USING
Can be simplified JOIN ON Equivalent connection . It is related to the following SQL The query results are the same :
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
There are three ways to constrain table joins :WHERE, ON, USING
WHERE: Applicable to all associated queries
ON
: And the only JOIN Use it together , Only association conditions can be written . Although Association conditions can be combined to WHERE Write... With other conditions , But it's better to write separately .
USING: And the only JOIN Use it together , And ask for Two Associated fields have the same name in the associated table , And it can only mean that the associated field values are equal
# The associated condition
# Write the correlation condition in where Back
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
# Write the correlation condition in on Back , And the only JOIN Use it together
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
# Write the associated fields in using() in , And the only JOIN Use it together
# And the associated fields in the two tables must have the same name , And it can only mean =
# Query employee name and basic salary
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n Zhang table correlation , need n-1 Two association conditions
# Check the name of the employee , Basic salary , Department name
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
# 3 A table query can write a inner join stay on Write it separately , You can also write one like this on
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
Be careful :
We want to Control the number of join tables
. Multiple table join is equivalent to nesting for The cycle is the same , Very resource intensive , Will make SQL Query performance has been severely degraded , So don't join unnecessary tables . In many DBMS in , There will also be a limit on the maximum connection table .
【 mandatory 】 More than three tables prohibited join. need join Field of , Data types are absolutely consistent ; When multi table associated query , Ensure that the associated field needs to have an index .
explain : Even if the double meter join Also pay attention to table indexes 、SQL performance .
source : Alibaba 《Java Development Manual 》
When we officially start talking about the types of connection tables , We need to know first SQL There are different versions of standard specifications , Because the table join operations under different specifications are different .
SQL There are two main criteria , Namely SQL92
and SQL99
.92 and 99 Represents the time when the standard was proposed ,SQL92 Namely 92 Standards and specifications proposed in . Except, of course, SQL92 and SQL99 outside , There is still SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 and SQL:2016 Other standards .
So many standards , Which should we learn ? Actually, the most important thing is SQL The standard is SQL92 and SQL99. Generally speaking SQL92 The form is simpler , But it's written SQL The statements will be longer , Poor readability . and SQL99 Compared with SQL92 Come on , The grammar is more complicated , But more readable . We can also see from the number of pages published by these two standards ,SQL92 There are 500 page , and SQL99 The standard exceeds 1000 page . Actually from SQL99 after , Few people can master all the content , Because there are so many . It's like we use Windows、Linux and Office When , Few people can master all the content . We just need to master some core functions , Just meet the needs of daily work .
**SQL92 and SQL99 It's classic SQL standard , Also called SQL-2 and SQL-3 standard .** It was after the release of these two standards ,SQL More and more influence , Even beyond the database field . Today, SQL It is not only the mainstream language in the field of database , It is also the mainstream language of information processing in the information field . In graphics retrieval 、 Image retrieval and voice retrieval can see SQL The use of language .
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
Function runs through the use of computer language , What does the function do ? It can encapsulate the code we often use , Call directly when necessary . This is both Improved code efficiency
, also Improved maintainability
. stay SQL In, we can also use functions to operate on the retrieved data . Use these functions , Can greatly Improve the efficiency of user management of database
.
From the perspective of function definition , We can divide the function into Built in functions
and Custom function
. stay SQL In language , It also includes built-in functions and custom functions . Built in function is a general function built in the system , The custom function is written according to our own needs , This chapter and the next chapter explain SQL Built in functions for .
We are using SQL When it comes to language , Not dealing directly with the language , Instead, it uses different database software , namely DBMS.DBMS There are great differences between , Much greater than the difference between different versions of the same language . actually , Only a few functions are called DBMS Supported at the same time . such as , majority DBMS Use (||) perhaps (+) To make a splice , And in the MySQL The string splicing function in is concat(). Most of the DBMS Will have their own specific functions , That means use SQL The code portability of functions is very poor , Therefore, special attention should be paid to when using functions .
MySQL Provides a wealth of built-in functions , These functions make data maintenance and management more convenient , It can better provide data analysis and statistical functions , To some extent, it improves the efficiency of data analysis and Statistics .
MySQL The built-in functions provided are from From the perspective of function
Can be divided into numerical functions 、 String function 、 Date and time functions 、 Process control functions 、 Encryption and decryption functions 、 obtain MySQL Information functions 、 Aggregate functions, etc . here , I subdivide these rich built-in functions into two categories : Multiline functions
、 Aggregate functions ( Or grouping function or single line function )
.
Two kinds of SQL function
One line function
function | usage |
---|---|
ABS(x) | return x The absolute value of |
SIGN(X) | return X The symbol of . A positive number returns 1, A negative number returns -1,0 return 0 |
PI() | Returns the value of the pi , Note that there is no need to write parameters |
CEIL(x),CEILING(x) | Returns the smallest integer greater than or equal to a value |
FLOOR(x) | Returns the largest integer less than or equal to a value |
LEAST(e1,e2,e3…) | Returns the minimum value in the list |
GREATEST(e1,e2,e3…) | Returns the maximum value in the list |
MOD(x,y) | return X Divide Y Remainder after |
RAND() | return 0~1 The random value |
RAND(x) | return 0~1 The random value , among x The value of is used as the seed value , same X Values produce the same random number |
ROUND(x) | Returns a pair x After rounding the value of , Closest to X The integer of |
ROUND(x,y) | Returns a pair x The value of is rounded to the nearest X Value , And keep it after the decimal point Y position |
TRUNCATE(x,y) | Return to digital x Truncated to y The result of decimal places |
SQRT(x) | return x The square root of . When X When the value of is negative , return NULL |
give an example :
# Basic operation
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
# Explain incomprehensible functions :
CEIL(x),CEILING(x):2 Two functions are equivalent , Rounding up . example :32 ,33 Whether it's 32.1, still 32.9 Round up to 33.
FLOOR(x): Just the opposite , Rounding down , example :32 ,33 Whether it's 32.1, still 32.9 Rounding down is 32.
MOD(x,y): return X Divide Y Remainder after .
# Take random number
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
# explain :
RAND(): obtain 0~1 Random number between
RAND(10),RAND(10): If 2 The parameters in a random function are the same , Then the random number obtained is the same .
rounding , Truncation operation
SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
explain : rounding
SELECT ROUND(123.556): By default, only integers are retained , Round the decimal .123.556---->124
ROUND(123.456,0): Retain 0 Decimal place ,123.456--->123
ROUND(123.456,1): Retain 1 Decimal place ,123.456--->123.5
ROUND(123.456,2): Retain 2 Decimal place ,123.456--->123.46
ROUND(123.456,-1): Keep until 2 This position ,3 dissatisfaction 5 Give up ,123.456--->120
ROUND(153.456,-2): Keep until 1 This position ,5 Satisfy ,3 dissatisfaction 5 Give up ,123.456--->200
explain : Truncation operation . There is no way to write a parameter .
TRUNCATE(123.456,0): Keep to integer , 123.456--->123
TRUNCATE(123.496,1): Keep until 1 Decimal place ,123.496--->123.4
TRUNCATE(129.45,-1): Keep until 2 The location of ,129.45 --->120
# Single line functions can be nested
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;
function | usage |
---|---|
RADIANS(x) | Convert angles to radians , among , Parameters x Is the angle value |
DEGREES(x) | Convert radians to angles , among , Parameters x Is the radian value |
# Exchange of angle and radian
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;
function | usage |
---|---|
SIN(x) | return x The sine of , among , Parameters x Is the radian value |
ASIN(x) | return x The arcsine of , That is, the sine obtained is x Value . If x The value of is not in -1 To 1 Between , Then return to NULL |
COS(x) | return x Cosine of , among , Parameters x Is the radian value |
ACOS(x) | return x The arccosine of , That is, get the cosine as x Value . If x The value of is not in -1 To 1 Between , Then return to NULL |
TAN(x) | return x The tangent of , among , Parameters x Is the radian value |
ATAN(x) | return x The arctangent of , That is, the tangent value returned is x Value |
ATAN2(m,n) | Returns the arctangent of two parameters |
COT(x) | return x Cotangent of , among ,X Is the radian value |
give an example :
ATAN2(M,N) Function returns the arctangent of two parameters .
And ATAN(X) Compared to the function ,ATAN2(M,N) Two parameters are required , For example, there are two points point(x1,y1) and point(x2,y2), Use ATAN(X) The function calculates the arctangent as ATAN((y2-y1)/(x2-x1)), Use ATAN2(M,N) Calculate the arctangent as ATAN2(y2-y1,x2-x1). It can be seen from the way of use , When x2-x1 be equal to 0 when ,ATAN(X) The function will report an error , and ATAN2(M,N) Function can still calculate .
ATAN2(M,N) An example of using the function is as follows :
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))
FROM DUAL;
function | usage |
---|---|
POW(x,y),POWER(X,Y) | return x Of y Power |
EXP(X) | return e Of X Power , among e It's a constant ,2.718281828459045 |
LN(X),LOG(X) | Return to e At the bottom of the X The logarithmic , When X <= 0 when , The result returned is NULL |
LOG10(X) | Return to 10 At the bottom of the X The logarithmic , When X <= 0 when , The result returned is NULL |
LOG2(X) | Return to 2 At the bottom of the X The logarithmic , When X <= 0 when , return NULL |
mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
-> FROM DUAL;
+----------+------------+------------------+-------------------+-----------+---------+
| POW(2,5) | POWER(2,4) | EXP(2) | LN(10) | LOG10(10) | LOG2(4) |
+----------+------------+------------------+-------------------+-----------+---------+
| 32 | 16 | 7.38905609893065 | 2.302585092994046 | 1 | 2 |
+----------+------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)
function | usage |
---|---|
BIN(x) | return x Binary code of |
HEX(x) | return x Hexadecimal encoding |
OCT(x) | return x Octal encoding |
CONV(x,f1,f2) | return f1 The base number becomes f2 Hexadecimal number |
mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
-> FROM DUAL;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010 | A | 12 | 2 |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)
function | usage |
---|---|
ASCII(S) | Return string S Of the first character in ASCII Code value |
CHAR_LENGTH(s) | Return string s The number of characters . The functions and CHARACTER_LENGTH(s) identical |
LENGTH(s) | Return string s Bytes of , It's about character sets |
CONCAT(s1,s2,…,sn) | Connect s1,s2,…,sn For a string |
CONCAT_WS(x, s1,s2,…,sn) | Same as CONCAT(s1,s2,…) function , But add... Between each string x |
INSERT(str, idx, len, replacestr) | The string str From idx Position start ,len Substrings of characters long are replaced with strings replacestr |
REPLACE(str, a, b) | Use string b Replace string str All the strings that appear in a |
UPPER(s) or UCASE(s) | The string s All the letters of are converted to capital letters |
LOWER(s) or LCASE(s) | The string s All the letters of are converted to lowercase letters |
LEFT(str,n) | Return string str The leftmost n Characters |
RIGHT(str,n) | Return string str Far right n Characters |
LPAD(str, len, pad) | Use string pad Yes str Fill on the far left , until str The length of is len Characters |
RPAD(str ,len, pad) | Use string pad Yes str Fill on the far right , until str The length of is len Characters |
LTRIM(s) | Remove string s The space on the left |
RTRIM(s) | Remove string s The space on the right |
TRIM(s) | Remove string s Start and end spaces |
TRIM(s1 FROM s) | Remove string s The beginning and the end s1 |
TRIM(LEADING s1 FROM s) | Remove string s At the beginning s1 |
TRIM(TRAILING s1 FROM s) | Remove string s At the end s1 |
REPEAT(str, n) | return str repeat n Results of |
SPACE(n) | return n A space |
STRCMP(s1,s2) | Compare strings s1,s2 Of ASCII The size of the code value |
SUBSTR(s,index,len) | Return from string s Of index Position it len Characters , The functions and SUBSTRING(s,n,len)、MID(s,n,len) identical |
LOCATE(substr,str) | Return string substr In string str The first place in , Act on POSITION(substr IN str)、INSTR(str,substr) identical . Not found , return 0 |
ELT(m,s1,s2,…,sn) | Returns the string at the specified location , If m=1, Then return to s1, If m=2, Then return to s2, If m=n, Then return to sn |
FIELD(s,s1,s2,…,sn) | Return string s The position of the first occurrence in the string list |
FIND_IN_SET(s1,s2) | Return string s1 In string s2 Where in . among , character string s2 Is a comma separated string |
REVERSE(s) | return s The inverted string |
NULLIF(value1,value2) | Compare two strings , If value1 And value2 equal , Then return to NULL, Otherwise return to value1 |
Be careful :MySQL in , The position of the string is from 1 At the beginning .
give an example :
mysql> SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma')
-> FROM DUAL;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
| 0 | 2 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL | mysql |
+-------------------------+---------------------+
1 row in set (0.00 sec)
function | usage |
---|---|
CURDATE() ,CURRENT_DATE() | Return current date , Only years 、 month 、 Japan |
CURTIME() , CURRENT_TIME() | Return current time , When only 、 branch 、 second |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | Returns the current system date and time |
UTC_DATE() | return UTC( World standard time ) date |
UTC_TIME() | return UTC( World standard time ) Time |
give an example :
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;
function | usage |
---|---|
UNIX_TIMESTAMP() | With UNIX Returns the current time in the form of a timestamp .SELECT UNIX_TIMESTAMP() ->1634348884 |
UNIX_TIMESTAMP(date) | Time date With UNIX Return in the form of a timestamp . |
FROM_UNIXTIME(timestamp) | take UNIX The time of the timestamp is converted to the time of the normal format |
give an example :
mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1576380910 |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
| 1576339200 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
| 1576380969 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
| 1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10 |
+---------------------------+
1 row in set (0.00 sec)
function | usage |
---|---|
YEAR(date) / MONTH(date) / DAY(date) | Return the specific date value |
HOUR(time) / MINUTE(time) / SECOND(time) | Return specific time value |
MONTHNAME(date) | Return to the month :January,… |
DAYNAME(date) | Day of the week :MONDAY,TUESDAY…SUNDAY |
WEEKDAY(date) | What day of the week , Be careful , Zhou 1 yes 0, Zhou 2 yes 1,... Sunday is 6 |
QUARTER(date) | Returns the quarter corresponding to the date , The scope is 1~4 |
WEEK(date) , WEEKOFYEAR(date) | Go back to the week of the year |
DAYOFYEAR(date) | The return date is the day of the year |
DAYOFMONTH(date) | The return date is the day of the month |
DAYOFWEEK(date) | What day of the week , Be careful : Sunday is 1, Monday is 2,... Saturday is 7 |
give an example :
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
function | usage |
---|---|
EXTRACT(type FROM date) | Returns a specific part of a specified date ,type Specifies the value to be returned |
EXTRACT(type FROM date) Function type The value and meaning of :
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
function | usage |
---|---|
TIME_TO_SEC(time) | take time Convert to seconds and return the result value . The conversion formula is : Hours *3600+ minute *60+ second |
SEC_TO_TIME(seconds) | take seconds The description is converted to include hours 、 Minutes and seconds |
give an example :
mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
| 78774 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54 |
+--------------------+
1 row in set (0.12 sec)
The first 1 Group :
function | usage |
---|---|
DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type) | Returns the difference between the given date and time INTERVAL Date time of the time period |
DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type) | Return and date Difference between INTERVAL The date of the interval |
In the above functions type The value of :
give an example :
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, # It could be negative
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 # Need single quotes
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;
The first 2 Group :
function | usage |
---|---|
ADDTIME(time1,time2) | return time1 add time2 Time for . When time2 When it is a number , It stands for second , Can be negative |
SUBTIME(time1,time2) | return time1 subtract time2 After time . When time2 When it is a number , It stands for second , Can be negative |
DATEDIFF(date1,date2) | return date1 - date2 Number of days between dates |
TIMEDIFF(time1, time2) | return time1 - time2 Time interval of |
FROM_DAYS(N) | Return from 0000 year 1 month 1 The date of ,N Days later |
TO_DAYS(date) | Return date date distance 0000 year 1 month 1 Days of the day |
LAST_DAY(date) | return date The date of the last day of the month |
MAKEDATE(year,n) | Returns a date for a given year and the number of days in the year |
MAKETIME(hour,minute,second) | Set the given hour 、 Minutes and seconds are combined into time and return |
PERIOD_ADD(time,n) | return time add n After time |
give an example :
SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;
mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50) |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '-1:-1:-1');
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD(20200101010101,1);
+------------------------------+
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
| 20200101010102 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
| 737773 |
+----------------+
1 row in set (0.00 sec)
give an example : Inquire about 7 How many new users are there in a day ?
SELECT COUNT(*) as num
FROM new_user
WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
function | usage |
---|---|
DATE_FORMAT(date,fmt) | According to the string fmt Format date date value |
TIME_FORMAT(time,fmt) | According to the string fmt Format time time value |
GET_FORMAT(date_type,format_type) | Returns the display format of the date string |
STR_TO_DATE(str, fmt) | According to the string fmt Yes str To analyze , Resolve to a date |
Above Not GET_FORMAT
Function fmt Common format characters for parameters :
Format symbol | explain | Format symbol | explain |
---|---|---|---|
%Y | 4 The number of digits indicates the year | %y | Two digits for the year |
%M | The name of the month means the month (January,…) | %m | Two figures indicate the month (01,02,03...) |
%b | Abbreviated month name (Jan.,Feb.,…) | %c | The number represents the month (1,2,3,…) |
%D | The English suffix indicates the number of days in the month (1st,2nd,3rd,…) | %d | Two figures indicate the number of days in the month (01,02…) |
%e | The number of days in the month (1,2,3,4,5…) | ||
%H | Two figures represent decimals ,24 hourly (01,02…) | %h and %I | Two figures represent hours ,12 hourly (01,02…) |
%k | Hours in digital form ,24 hourly (1,2,3) | %l | The number represents the hour ,12 hourly (1,2,3,4…) |
%i | Two figures represent minutes (00,01,02) | %S and %s | Two digits are seconds (00,01,02…) |
%W | The name of the week of the week (Sunday…) | %a | The abbreviation of a week (Sun.,Mon.,Tues.,…) |
%w | Number the days of the week (0=Sunday,1=Monday…) | ||
%j | With 3 The number of digits represents the number of days in the year (001,002…) | %U | Number the week of the year ,(1,2,3..) among Sunday For the first day of the week |
%u | Number the week of the year ,(1,2,3..) among Monday For the first day of the week | ||
%T | 24 hourly | %r | 12 hourly |
%p | AM or PM | %% | Express % |
GET_FORMAT Function date_type and format_type The parameter values are as follows :
give an example :
mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34 |
+--------------------------------+
1 row in set (0.00 sec)
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y |
+-------------------------+
1 row in set (0.00 sec)
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;
mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Process processing functions can be based on different conditions , Perform different processes , Can be in SQL Statement to implement different conditional choices .MySQL The process processing functions in mainly include IF()、IFNULL() and CASE() function .
function | usage |
---|---|
IF(value,value1,value2) | If value The value of is TRUE, return value1, Otherwise return to value2 |
IFNULL(value1, value2) | If value1 Not for NULL, return value1, Otherwise return to value2 |
CASE WHEN Conditions 1 THEN result 1 WHEN Conditions 2 THEN result 2 … [ELSE resultn] END | amount to Java Of if…else if…else… |
CASE expr WHEN Constant values 1 THEN value 1 WHEN Constant values 1 THEN value 1 … [ELSE value n] END | amount to Java Of switch…case… |
SELECT IF(1 > 0,' correct ',' error ')
-> correct
SELECT IFNULL(null,'Hello Word')
->Hello Word
SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
->1 > 0
SELECT CASE 1
WHEN 1 THEN ' I am a 1'
WHEN 2 THEN ' I am a 2'
ELSE ' who are you '
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN ' High salaries '
WHEN salary>=10000 THEN ' potential share '
WHEN salary>=8000 THEN ' Prick silk '
ELSE ' grassroots ' END " describe "
FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN ' Unpaid '
WHEN 2 THEN ' Paid '
WHEN 3 THEN ' Shipped '
WHEN 4 THEN ' Confirm receipt '
ELSE ' Invalid order ' END
FROM t_order;
mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
| -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
practice : The inquiry department number is 10,20, 30 Employee information , If the department number is 10, Print their salary 1.1 times , 20 Department No , Print their salary 1.2 times , 30 Department No. prints its salary 1.3 Multiple .
The encryption and decryption function is mainly used to encrypt and decrypt the data in the database , To prevent data from being stolen by others . These functions are very useful in ensuring database security .
function | usage |
---|---|
PASSWORD(str) | Return string str The encrypted version of ,41 Bit long string . Encryption result Irreversible , Commonly used for user password encryption |
MD5(str) | Return string str Of md5 Encrypted value , It's also a form of encryption . If the parameter is NULL, Will return NULL |
SHA(str) | From the original plaintext password str Calculate and return the encrypted password string , When the parameter is NULL when , return NULL.SHA Encryption algorithm than MD5 More secure . |
ENCODE(value,password_seed) | Return to use password_seed Encrypt as an encryption password value |
DECODE(value,password_seed) | Return to use password_seed Decrypt as an encrypted password value |
You can see ,ENCODE(value,password_seed) Function and DECODE(value,password_seed) Functions are inverse functions to each other .
give an example :
mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql') | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)
SELECT md5('123')
->202cb962ac59075b964b07152d234b70
SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50
mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg ¼ ìÉ |
+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)
MySQL There are some built-in functions that can be queried MySQL Functions of information , These functions are mainly used to help database developers or operation and maintenance personnel better maintain the database .
function | usage |
---|---|
VERSION() | Returns the current MySQL Version number of |
CONNECTION_ID() | Returns the current MySQL Number of connections to the server |
DATABASE(),SCHEMA() | return MySQL The database where the command line is currently located |
USER(),CURRENT_USER()、SYSTEM_USER(),SESSION_USER() | Return to the current connection MySQL Username , The format of the returned result is “ Host name @ user name ” |
CHARSET(value) | Return string value The character set of the argument |
COLLATION(value) | Return string value Comparison rules of |
give an example :
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC') |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)
MySQL There are some functions in that cannot be specifically classified , But these functions are MySQL It can not be ignored in the process of development, operation and maintenance .
function | usage |
---|---|
FORMAT(value,n) | Returns a pair of numbers value Formatted result data .n Express rounding Keep it after the decimal point n position |
CONV(value,from,to) | take value The value of is converted between different hexadecimals |
INET_ATON(ipvalue) | Will be separated by dots IP The address is converted to a number |
INET_NTOA(value) | Put... In digital form IP The address is converted to dot separated IP Address |
BENCHMARK(n,expr) | Expression expr repeat n Time . Used for testing MySQL Handle expr The time taken by the expression |
CONVERT(value USING char_code) | take value The character encoding used is modified to char_code |
give an example :
# If n Is less than or equal to 0, Then only the integer part
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12 | 124 | 123 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);+-----------------+------------------+-------------------+| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |+-----------------+------------------+-------------------+| 10000 | 22B8 | NULL |+-----------------+------------------+-------------------+1 row in set (0.00 sec)
mysql> SELECT INET_ATON('192.168.1.100');+----------------------------+| INET_ATON('192.168.1.100') |+----------------------------+| 3232235876 |+----------------------------+1 row in set (0.00 sec)# With “192.168.1.100” For example , The calculation method is 192 multiply 256 Of 3 Power , add 168 multiply 256 Of 2 Power , add 1 multiply 256, Plus 100.
mysql> SELECT INET_NTOA(3232235876);+-----------------------+| INET_NTOA(3232235876) |+-----------------------+| 192.168.1.100 |+-----------------------+1 row in set (0.00 sec)
mysql> SELECT BENCHMARK(1, MD5('mysql'));+----------------------------+| BENCHMARK(1, MD5('mysql')) |+----------------------------+| 0 |+----------------------------+1 row in set (0.00 sec)mysql> SELECT BENCHMARK(1000000, MD5('mysql')); +----------------------------------+| BENCHMARK(1000000, MD5('mysql')) |+----------------------------------+| 0 |+----------------------------------+1 row in set (0.20 sec)
mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));+------------------+----------------------------------------+| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |+------------------+----------------------------------------+| utf8mb4 | utf8 |+------------------+----------------------------------------+1 row in set, 1 warning (0.00 sec)
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
We talked about... In the last chapter SQL One line function . actually SQL There is another kind of function , Called polymerization ( Or gather together 、 grouping ) function , It is a function that summarizes a set of data , The input is a set of data , The output is a single value .
Aggregate functions act on a set of data , And return a value for a set of data .
Aggregate function type
Aggregate function syntax
It can be done to Numerical data Use AVG and SUM function .
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
#1.1 AVG / SUM : Only applicable to fields of numeric type ( Or variable )
SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
# The following actions are meaningless , Cannot use... On a string ,mysql The string used in is 0,oracle Direct error reporting in .
SELECT SUM(last_name),AVG(last_name),SUM(hire_date)
FROM employees;
It can be done to Any data type Data usage of MIN and MAX function .
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
#1.2 MAX / MIN : For numeric types 、 String type 、 Fields of date time type ( Or variable )
# explain , Can sort, you can find the maximum and minimum . For example, by name abcd Alphabetical order .
SELECT MAX(salary),MIN(salary)
FROM employees;
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)
FROM employees;
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
#1.3 COUNT:
# ① effect : Calculate the number of specified fields in the query structure ( It doesn't contain NULL It's worth it )
# Can a field be a field in a table .COUNT(1)
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)
FROM employees ;
SELECT *
FROM employees;
# If there are many records in the calculation table , How to achieve ?
# The way 1:COUNT(*)
# The way 2:COUNT(1) Represents a record as a 1, Figure out how many 1.COUNT(2) Represents a record as a 2 To view .
# The way 3:COUNT( Specific fields ) : Not necessarily !
#② Be careful : When calculating the number of occurrences of the specified field , It is not calculated NULL It's worth it .
SELECT COUNT(commission_pct) #35
FROM employees;
SELECT commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#③ The formula :AVG = SUM / COUNT
# Above these AVG SUM MAX MIN COUNT Will automatically filter out null values null
SELECT AVG(salary),SUM(salary)/COUNT(salary),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;
# demand : Query the average bonus rate in the company
# FALSE !
SELECT AVG(commission_pct)
FROM employees;
# Correct :
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0)) # by null A column of as 0, There are several records 0 Record . Not for Null Several records of , Add up to all the records .
FROM employees;
# How to count the number of records in a table , Use COUNT(*)、COUNT(1)、COUNT( Specific fields ) Which is more efficient ?
# If you are using MyISAM Storage engine , Then the efficiency of the three is the same , All are O(1)
# If you are using InnoDB Storage engine , Then the efficiency of the three :COUNT(*) = COUNT(1)> COUNT( Field )
problem : use count(*),count(1),count( Name ) Who's good ?
Actually , about MyISAM There's no difference between the engine's tables . There is a counter inside the engine that maintains the number of rows .
Innodb The watch of the engine count(*),count(1) Read lines directly , Complexity is O(n), because innodb I really have to count it . But better than concrete count( Name ).
problem : Can I use count( Name ) Replace count(*)?
Do not use count( Name ) To replace count(*)
,count(*)
yes SQL92 The syntax for defining the number of standard statistics lines , It's not about the database , Follow NULL He Fei NULL irrelevant .
explain :count(*) The statistical value is NULL The line of , and count( Name ) This column is not counted NULL Row of values .
have access to GROUP BY Clause divides the data in a table into groups
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
clear :WHERE It must be on FROM Back
stay SELECT All columns in the list that are not included in the group function should be included in GROUP BY clause
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
Included in GROUP BY Columns in Clause need not be included in SELECT In the list
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
#2. GROUP BY Use
# demand : Query the average salary of each department , Maximum wage
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id
# demand : Check each one job_id The average wage of
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
# demand : Check each one department_id,job_id The average wage of ( Multiple grouping conditions , For example, the salary of the same type of work in the same department )
# The way 1:
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
# The way 2: The first division of labor has the same effect in sub sectors , Because no matter the Department before the type of work , Or the type of work first and then the Department , The final result is the same department and the same type of work together .
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
Use WITH ROLLUP
After keyword , Add a record after all the queried grouping records , This record calculates the sum of all the queried records , That is, count the number of records .
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
# FALSE !
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;
# Conclusion 1:SELECT The fields of non group functions appearing in must be declared in GROUP BY in .
# conversely ,GROUP BY Fields declared in can not appear in SELECT in .
Non group functions : Fields that do not evaluate multiline functions , AVG SUM MAX MIN COUNT.
# Conclusion 2:GROUP BY The statement in FROM Back 、WHERE Back ,ORDER BY front 、LIMIT front ,
namely :from where grouping Sort Pagination . If there are all, sort according to this .
# Conclusion 3:MySQL in GROUP BY Use in WITH ROLLUP
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
# demand : Query the average salary of each department , In ascending order of average wage
# AVG(salary) avg_sal Calculate the average salary of everyone in the company .
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;
# explain : When using ROLLUP when , Can't be used at the same time ORDER BY Clause to sort the results , namely ROLLUP and ORDER BY Are mutually exclusive .
# FALSE :
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;
Be careful :
When using ROLLUP when , Can't be used at the same time ORDER BY Clause to sort the results , namely ROLLUP and ORDER BY Are mutually exclusive .
Filter grouping :HAVING Clause
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
#3. HAVING Use ( effect : Used to filter data )
# practice : Query the highest salary ratio in each department 10000 High department information
# The wrong way to write :
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;
# requirement 1: If an aggregate function is used in the filter condition , Must be used HAVING To replace WHERE. otherwise , Report errors .
# requirement 2:HAVING It must be stated that in GROUP BY Behind .
# Correct writing :
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
# requirement 3: In development , We use HAVING The premise is SQL Used in GROUP BY.
# practice : Inquiry Department id by 10,20,30,40 this 4 The highest wage ratio in a department 10000 High department information
# The way 1: recommend , Execution efficiency is higher than mode 2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
# The way 2:
problem 1:where Multiline functions cannot be used in ( Group ) function , and having You can write group functions or ordinary conditions in , that
still more where What for? , All use having Not to go ????
answer : First having Used with groups , If there is no grouping, you can only use where Filter conditions , Moreover, even if there is 2 Filter conditions , One
Is a grouping function for having in , The other is the common filter condition, which is recommended to use where Not all written in having in , because where Of
Efficient .
problem 2: Since the way 1 And way 2 That's right , What about the more efficient one ????
answer : The way 1, The general filter conditions are written in where Zhongbi is written in having Medium efficiency , Explain the following SQL The underlying execution principle .
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
# Conclusion : When there is an aggregate function in the filter condition , Then this filter condition must be declared in HAVING in .
# When there is no aggregate function in the filter condition , Then this filter condition is declared in WHERE Medium or HAVING Either way . however , It is suggested that you declare in WHERE in .
/* WHERE And HAVING Comparison of 1. In terms of scope of application ,HAVING Has a wider scope of application . 2. If there is no aggregate function in the filter condition : In this case ,WHERE The efficiency of implementation is higher than HAVING */
#4. SQL The underlying execution principle
#4.1 SELECT The complete structure of the statement
/* #sql92 grammar : SELECT ....,....,....( There are aggregate functions ) FROM ...,....,.... WHERE Connection conditions of multiple tables AND Filter conditions that do not contain aggregate functions GROUP BY ...,.... HAVING Filter conditions containing aggregate functions ORDER BY ....,...(ASC / DESC ) LIMIT ...,.... #sql99 grammar : SELECT ....,....,....( There are aggregate functions ) FROM ... (LEFT / RIGHT)JOIN ....ON Connection conditions of multiple tables (LEFT / RIGHT)JOIN ... ON .... WHERE Filter conditions that do not contain aggregate functions GROUP BY ...,.... HAVING Filter conditions containing aggregate functions ORDER BY ....,...(ASC / DESC ) LIMIT ...,.... */
#4.2 SQL Statement execution :
#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT ->
# ORDER BY -> LIMIT
explain 1: Why do filter criteria place where Medium efficiency :. Because of the where Filter out unnecessary data before grouping ,
And put it in having You need to filter by grouping first , Such unnecessary data should also be grouped. Naturally, the efficiency is slow .
explain 2: Why? where If you include an aggregate function, you can't put it in where What about China? , Because the execution order of grouping functions is having After or select Back , All are
After grouping , and where Of course, you can't use grouping functions before grouping .
difference 1:WHERE You can directly use the fields in the table as filter criteria , However, you cannot use the calculation function in the group as the filter condition ;HAVING Must be with GROUP BY In combination with , You can use the grouping calculation function and grouping field as the filter criteria .
To determine the , When it is necessary to make grouping statistics on data ,HAVING Can finish WHERE Tasks that cannot be completed . This is because , In the query syntax structure ,WHERE stay GROUP BY Before , Therefore, the grouping results cannot be filtered .HAVING stay GROUP BY after , You can use group fields and calculation functions in groups , Filter the grouped result set , This function is WHERE What can't be done . in addition ,WHERE Excluded records are no longer included in the group .
difference 2: If you need to obtain the required data from the associated table through connection ,WHERE Filter first and then connect , and HAVING Connect first and then filter . This point , This determines that in the association query ,WHERE Than HAVING More efficient . because WHERE You can screen first , Join with a smaller filtered dataset and associated tables , This takes up less resources , The implementation efficiency is also relatively high .HAVING You need to prepare the result set first , That is, association with unfiltered data sets , Then filter the large data set , This takes up more resources , Execution efficiency is also low .
The summary is as follows :
advantage | shortcoming | |
---|---|---|
WHERE | Filter the data before associating , High execution efficiency | You cannot filter using the calculation function in the group |
HAVING | You can use the calculation function in the group | Filter in the final result set , Low execution efficiency |
Choices in development :
WHERE and HAVING It's not mutually exclusive , We can use both in one query WHERE and HAVING. The conditions containing grouping statistical functions are HAVING, For ordinary conditions WHERE. such , We're taking advantage of WHERE Efficient and rapid of conditions , Played again HAVING You can use the advantages of query conditions that include grouping statistics functions . When the amount of data is very large , There will be a big difference in operating efficiency .
# The way 1:
SELECT ...,....,...
FROM ...,...,....
WHERE Connection conditions of multiple tables
AND Filter conditions that do not contain group functions
GROUP BY ...,...
HAVING Contains filter conditions for group functions
ORDER BY ... ASC/DESC
LIMIT ...,...
# The way 2:
SELECT ...,....,...
FROM ... JOIN ...
ON Connection conditions of multiple tables
JOIN ...
ON ...
WHERE Filter conditions that do not contain group functions
AND/OR Filter conditions that do not contain group functions
GROUP BY ...,...
HAVING Contains filter conditions for group functions
ORDER BY ... ASC/DESC
LIMIT ...,...
# among :
#(1)from: From which tables to filter
#(2)on: When associating multiple table queries , Remove Cartesian product
#(3)where: Conditions filtered from the table
#(4)group by: Group by
#(5)having: Sift through the statistical results again
#(6)order by: Sort
#(7)limit: Pagination
You need to remember SELECT Two order of query :
1. The order of keywords cannot be reversed :
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
2.SELECT The order in which statements are executed ( stay MySQL and Oracle in ,SELECT The execution sequence is basically the same ):
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT Field of -> DISTINCT -> ORDER BY -> LIMIT
For example, you wrote a SQL sentence , Then its keyword order and execution order are as follows :
SELECT DISTINCT player_id, player_name, count(*) as num # The order 5
FROM player JOIN team ON player.team_id = team.team_id # The order 1
WHERE height > 1.80 # The order 2
GROUP BY player.team_id # The order 3
HAVING num > 2 # The order 4
ORDER BY num DESC # The order 6
LIMIT 2 # The order 7
stay SELECT When the statement executes these steps , Each step produces a Virtual table
, Then pass this virtual table into the next step as input . It should be noted that , These steps are implicit in SQL During the execution of , It's invisible to us .
SELECT It's to execute first FROM This step of . At this stage , If multiple tables are used for associated query , The following steps will be followed :
Of course, if we operate more than two tables , The above steps will be repeated , Until all tables are processed . This process yields our raw data .
When we get the original data of the query data table , That is, the final virtual table vt1
, On this basis WHERE Stage
. At this stage , Will be based on vt1 Filter the results of the table , Get the virtual table vt2
.
Then go to step 3 and step 4 , That is to say GROUP and HAVING Stage
. At this stage , It's actually in the virtual table vt2 Group and group filtering based on , Get the middle virtual table vt3
and vt4
.
When we have finished the condition screening section , You can filter the fields extracted from the table , That is to say, to enter SELECT and DISTINCT Stage
.
First, in the SELECT The phase will extract the desired fields , And then in DISTINCT Stage filters out duplicate rows , Get the middle virtual table vt5-1
and vt5-2
.
When we extract the desired field data , You can sort according to the specified fields , That is to say ORDER BY Stage
, Get the virtual table vt6
.
Last in vt6 On the basis of , Take out the record of the specified line , That is to say LIMIT Stage
, Get the final result , The corresponding virtual table vt7
.
Of course we're writing SELECT At the time of statement , Not all keywords exist , The corresponding stage will be omitted .
Also because SQL It is a structured query language similar to English , So we're writing SELECT At the time of statement , Also pay attention to the corresponding keyword order , The so-called principle of bottom operation , That's the execution sequence we just talked about .
lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )
Official website :http://www.atguigu.com
Subquery refers to a query that is nested within another query statement by one query statement , This feature comes from MySQL 4.1 Start introducing .
SQL The use of neutron queries has greatly enhanced SELECT The ability to query , Because many times queries need to get data from the result set , Or you need to calculate a data result from the same table first , Then with this data result ( It could be some scalar , It could also be a collection ) Compare .
Existing solutions :
#1. By a specific need , Introduce subquery
# demand : Whose pay ratio is Abel The height of ?
# The way 1: Write 2 strip sql Low efficiency , Because you need to query 2 Time , You need to interact in the background 2 Time , Considering network connection, etc .
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
# The way 2: Self join
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary` # Connection conditions of multiple tables
AND e1.last_name = 'Abel';
# The way 3: Subquery
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#2. The standard of appellation : External query ( Or main query )、 Internal query ( Or subquery )
/* - Subquery ( Internal query ) Execute once before the main query . - The results of the sub query are by the main query ( External query ) Use . - matters needing attention - Subqueries should be enclosed in parentheses - Place the subquery to the right of the comparison criteria - Single line operators correspond to single line subqueries , Multiline operators correspond to multiline subqueries */
# Not recommended : The condition is written on the left , Poor readability
SELECT last_name,salary
FROM employees
WHERE (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
) < salary;
Classification 1:
We return one or more records according to the results of internal query , Sub queries are divided into Single line sub query
、 Multi line sub query
.
Classification 2:
We check whether the query is executed more than once , Divide the subquery into relevant ( Or associated ) Subquery
and Unrelated ( Or unrelated ) Subquery
.
The subquery queries the data results from the data table , If this data result is executed only once , The data result is then executed as a condition of the main query , Then such a subquery is called an unrelated subquery .
Again , If the subquery needs to be executed multiple times , That is, in the form of circulation , Start with an external query , Every time a subquery is passed in to query , The results are then fed back to the outside world , This nested execution is called related subquery .
/* 3. Classification of subqueries angle 1: The number of entries of results returned from the internal query Single line sub query ( It turns out that 1 individual ) vs Multi line sub query ( It turns out that there are multiple ) angle 2: Whether the internal query is executed more than once Correlation subquery vs Uncorrelated subqueries such as : Requirements for related sub queries : Query the information of employees whose salary is greater than the average salary of their department .( The results of subqueries vary with departments , Compare it with the value of this change every time ) Requirements for unrelated subqueries : Query the information of employees whose salary is greater than the average salary of the company .( The result of the subquery is a constant , Compare with this fixed value every time ) */
The operator | meaning |
---|---|
= | equal to |
> | greater than |
>= | greater than or equal to |
< | less than |
<= | less than or equal to |
<> or != | not equal to |
subject : Query salary greater than 149 Information of the employee whose salary is No
# Writing skills of sub query ( Or steps ):① Write from the inside out ② Write from the outside to the inside
#4. Single line sub query
#4.1 Single line operators : = != > >= < <=
# subject : Query salary greater than 149 Information of the employee whose salary is No
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE employee_id = 149
);
subject : return job_id And 141 Same as employee No ,salary Than 143 The name of the employee with a large number of employees ,job_id And wages
SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
subject : Return to the lowest paid employees of the company last_name,job_id and salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
subject : Query and 141 Number or 174 Of employee No manager_id and department_id Of the same other employees employee_id,manager_id,department_id
Realization way 1: Unpaired comparison
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
Realization way 2: Compare in pairs : If the conditions are the same, you can write together , Almost efficient .
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
subject : The minimum wage is greater than 50 The Department of minimum wage id And its minimum wage
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
stay CASE Single column subqueries are used in expressions :
subject : Explicit employee employee_id,last_name and location. among , If employee department_id And location_id by 1800 Of department_id identical , be location by ’Canada’, The rest are ’USA’.
SELECT employee_id, last_name,
(CASE department_id
WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
# The inner layer of the subquery does not return a row, which is equivalent to null, The outer query does not return any rows
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
The subquery does not return any rows
# problem : The return value of the inner sub query is multi line, which indicates that it is a multi line sub query , and = It is a single line sub query symbol, so an error is reported
# solve : Use multiline subquery symbols
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Multi line subqueries use single line comparators
The operator | meaning |
---|---|
IN | Equal to... In the list Any one |
ANY | It needs to be used with the single line comparison operator , And subqueries return One of them Value comparison |
ALL | It needs to be used with the single line comparison operator , And subqueries return all Value comparison |
SOME | It's actually ANY Another name for , The same effect , Commonly used ANY |
experience ANY and ALL The difference between
# IN:
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
subject : Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of any low paid employee in the Department 、 full name 、job_id as well as salary
# ANY / ALL:
# subject : Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of any low paid employee in the Department 、
# full name 、job_id as well as salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
subject : Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of all employees in the Department with low salary 、 full name 、job_id as well as salary
# subject : Return to other job_id Middle ratio job_id by ‘IT_PROG’ Employee number of all low paid employees in the Department 、
# full name 、job_id as well as salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
subject : Query the Department with the lowest average wage id
# Wrong writing :MySQL Aggregate functions in cannot be nested , Single line functions can .oracle Can be used without requirements .
select min(AVG(salary))
from employees
GROUP BY department_id;
# Solutions : Treat the result of the query as a new table , There is only one ordinary column in it , Of course, you can use multiline functions .
step :
First, query the average salary of each department : List the query as an alias :avg_sal
select AVG(salary) avg_sal
from employees
GROUP BY department_id;
secondly : Treat the results of the whole query as a table , And need a table alias , Otherwise, the report will be wrong :
select avg_sal
from (select AVG(salary) avg_sal
from employees
GROUP BY department_id) t_dept_avg_sal;
Then query the minimum wage of the average wage of each department : This column should be an ordinary column , You can use multiline functions .
select min(avg_sal)
from (select AVG(salary) avg_sal
from employees
GROUP BY department_id) t_dept_avg_sal;
Finally, query the minimum wage of the average wage of each department id: The result of the above operation is the minimum wage of the average wage of the Department , Which department do you need to find the minimum salary now .
# The way 1:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =( select min(avg_sal)
from (select AVG(salary) avg_sal
from employees
GROUP BY department_id
) t_dept_avg_sal
);
# explain :3 Layer loop nesting , From inside to outside : The average salary of each department , The average wage of the smallest Department , The average wage in that department is the lowest
# The way 2: Optimize , Outer layer : Query the average salary of a department is less than or equal to Inner layer : So the average salary , Only one of the query results of the outer layer is less than all the average wages
Then the average wage of this outer department is the smallest .
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
# Multi line sub query , There are multiple results in the query , One of the multiple results of the internal query is null, This whole is null.
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);
If the execution of a subquery depends on an external query , Usually, it is because the tables in the subquery use external tables , Conditional correlation is carried out , So every time you execute an external query , All subqueries have to be recalculated , Such a subquery is called Associated subquery
.
Related sub queries are executed row by row , Each row of the main query executes a subquery .
explain : The columns in the main query are used in the subquery
subject : Query the salary of employees whose salary is greater than the average salary of the Department last_name,salary And its department_id
Mode one : Correlation subquery
#6. Correlation subquery
# review : Query the salary of employees whose salary is greater than the average salary of the company last_name,salary And its department_id
#6.1
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
# subject : Query the salary of employees whose salary is greater than the average salary of the Department last_name,salary And its department_id
# The way 1: Use related subqueries
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE department_id = e1.`department_id`
);
Mode two : stay FROM Use subqueries in
# The way 2: stay FROM Zhongshengmingzi query
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal
from Subquery of type : Subquery is used as from Part of , The subquery should use () Lead up , And alias this subquery ,
Think of it as a “ Temporary virtual table ” To use .
stay ORDER BY Use subqueries in :
subject : Check the employee's id,salary, according to department_name Sort
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
);
# Conclusion : stay SELECT in , except GROUP BY and LIMIT outside , Other locations can be queried by the voice !
/* SELECT ....,....,....( There are aggregate functions ) FROM ... (LEFT / RIGHT)JOIN ....ON Connection conditions of multiple tables (LEFT / RIGHT)JOIN ... ON .... WHERE Filter conditions that do not contain aggregate functions GROUP BY ...,.... HAVING Filter conditions containing aggregate functions ORDER BY ....,...(ASC / DESC ) LIMIT ...,.... */
subject : if employees In the table employee_id And job_history In the table employee_id The same number is not less than 2, Output these same id Of our employees employee_id,last_name And its job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
subject : Query company managers employee_id,last_name,job_id,department_id Information
Mode one :
# The way 1: Self join
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;
Mode two :
# The way 2: Subquery
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
Mode three :
# The way 3: Use EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e1.`employee_id` = e2.`manager_id`
);
subject : Inquire about departments In the table , There is no in employees Of the departments in the table department_id and department_name
# The way 1:
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# The way 2:
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
);
/* Writing skills of sub query ( Or steps ):① Write from the inside out ② Write from the outside to the inside How to choose ? ① If the subquery is relatively simple , It is suggested to write from the outside to the inside . Once the subquery structure is complex , It is suggested to write from the inside out ② If it is a related subquery , It's usually written from the outside to the inside . */
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Use related subqueries to update data from one table to another .
subject : stay employees Add one department_name Field , The data is the Department name corresponding to the employee
# 1)
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2)
UPDATE employees e
SET department_name = (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Use related subqueries to delete data from one table based on data from another .
subject : Delete table employees in , And emp_history Data from all tables
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);
** problem :** Whose pay ratio is Abel The height of ?
answer :
# The way 1: Self join
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
# The way 2: Subquery
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
** problem :** Are the above two methods good or bad ?
** answer :** Good self connection mode !
Subqueries can be used in the title , You can also use self connection . In general, it is recommended that you use self connection , Because in many DBMS In the process of processing , The processing speed of self join is much faster than sub query .
It can be understood in this way : Sub query is actually the condition judgment after query through unknown table , The self connection is to judge the conditions through the known self data table , So in most cases DBMS Self join processing has been optimized in .
copyright:author[Cool breeze AAA],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130735004464.html