SQL statement (part of DQL language)

1. Query statement

1.1 Basic Statements

Note: SQL statements are case-insensitive, so do not create tables with id int, id vARCHar, etc.

Query can be checked: constants, expressions, functions;

SELECT 100;

SELECT 100- 92.;

SELECT VERSION();
Copy the code

Use distinct if you want to look up a field that contains duplicates and you want to remove duplicates yourself.

# show which department numbers are availableSELECT DISTINCT department_id FROM employees;
Copy the code

+ + + + + + + + + + + + + + + + + + + For example, two operations, if they can be evaluated directly, are evaluated. If they can’t, they are converted to numeric and then evaluated. If the conversion fails, they are converted to 0.

# select first and last name from employeeSELECT first_name+last_name`departments` 'name' FROMemployees; # concatenate (str1,str2......) functionSELECT CONCAT(first_name,last_name) AS 'name' FROMemployees; # This can also calculate success, result213
SELECT 123 + '90'; # as a result,123
SELECT 123 + 'abv'; # as a result,0
SELECT 'abc' + 'abv'; # the results fornull
SELECT 90 + null;
Copy the code

If there is a null value in a field, the concatenation will be null, and the IFNULL() function will be used.

IFNULL(' field name ',0);	=># this indicates the value of a fieldnullValues into0
Copy the code

Where 1.2 – screening

# grammarSELECTList of queriesFROMThe name of the tableWHEREScreening conditions;Copy the code

There are several categories of screening criteria

  • Filter by conditional expression: =, <, >, <=, >=, <>
  • According to the logical expression screening: && (with), | | (or),! (non) ===>and, or, not
  • Fuzzy query: like, between… And, in, is null; Like fuzzy query%Represents any number of characters,_Represents any character; between… The value type of in must be the same
  • Is NULL and <=> : Is null can only be used to check null values. <=> can also check other values besides null values. It is recommended to use IS NULL to check null values because of high readability
# such aslikeFields containing special characters need to be escaped, the second character of the query name is _SELECT * FROM employees WHERE last_name LIKE '_ \ _ %'; # check that a column value isnullThe user information can be usedis nullCan also be used< = >, is recommended hereis nullBecause of its high readabilitySELECT last_name,job_id,commission_pct FROM employees WHERE commission_pct < = > NULL;
Copy the code

1.3 Order by

Aliases are supported after order by. The following query is complete annual alias, can be sorted by alias.

# query all employees' information and annual salary, and sort by annual salarySELECT *,salary * 12 * (1 + IFNULL(commission_pct,0) -- earningFROM employees ORDER BYYearly salary; # when there are two or more columns in order, write it directlySELECT salary,employee_id FROM employees ORDER BY salary,employee_id DESC;
Copy the code

The ORDER by clause can be followed by a single field, multiple fields, expressions, functions, and aliases.

The ORDER by clause is usually placed at the end of the query, except for the limit clause.

1.4 Common Functions — Single line functions

The program is wrapped so that we can use it directly, increasing the reusability of the code.

Classification:

  • Single-line functions: concat, length, ifNULL, etc
  • Grouping function: also known as statistical function, aggregation function, grouping function, group function
(1) Character function
  • Length: indicates the number of bytes to obtain the parameter value.

Default UTF-8 character set: A letter contains 1 byte, a Chinese character contains 3 bytes, and different character sets contain different bytes.

How do I view the current character setSHOW VARIABLES LIKE '%char%'; # you can see that the result has a Variable_nameValueCharacter_set_client UTf8 #select length('Zhang Sanfeng Loveyou'); # The result above is:3The Chinese characters3*3 = 9.7A letter for7, the length is9 + 7 = 16
Copy the code
  • Concat: concatenates a string
# concatenation can use CONCAT, CONCAT(str1,str2......) functionSELECT CONCAT(first_name,last_name) AS 'name' FROM employees;
Copy the code
  • Upper and lower: Converts to upper and lower case
SELECT UPPER('zhangSanfEng');SELECT LOWER('zhangSanfEng');
Copy the code
  • Substr, substring:Intercepts the string from the indexIndex starts at 1, unlike Java
# from subscript to6The place begins to intercept: the result is Murong FuSELECT SUBSTR('Zhang Sanfeng vs. Murong Fu'.6) 'winner'; # from subscript to4Cut back two places: the result is a warSELECT SUBSTR('Zhang Sanfeng vs. Murong Fu'.4.2) 'winner';
Copy the code

First letter uppercase, other letters lowercase

SELECT CONCAT(UPPER(SUBSTR(last_name,1.1)),LOWER(SUBSTR(last_name,2))) FROM employees;
Copy the code
  • Instr: Query the index position of string A in string B, return 0 if not found
Where is the hahaha in Zhang SanfengSELECT INSTR('Zhang Sanfeng hahaha'.'Hahaha');
Copy the code
  • Trim: Trim the characters before and after, with the following ‘ ‘Spaces removed
# Result is Zhang SanfengSELECT LENGTH(TRIM(' ' FROM 'Zhang SAN Feng')); # The result is Zhang Sanfeng aaaaaaa Zhang SancrazySELECT TRIM('a' FROM 'Aaaaaaaaaaaa, Chang SAN Feng, Chang SAN feng, Chang SAN crazy, aaaaaaaaaaaa.');
Copy the code
  • Lpad: left padding with the specified character of the specified length; Same with rpad: fill right
# this indicates that the initial field is' Liu Bei ', and then fill it with big Meng meng10A character, so the final result is: big meng Meng Meng Meng Liu BeiSELECT LPAD('liu bei'.10.'Big Cute');
Copy the code
  • Replace: replace
I am Liu Huangshu. Liu Huangshu is me. There are only Liu Huangshu and Cao Cao as heroes in the worldSELECT REPLACE("Liu Xuande, I am Liu Xuande. Liu Xuande is me. There are only Liu Xuande and Cao Cao.".'xuande'.A 'sexy book');Copy the code
(2) Mathematical functions
  • Round: round
# can put values, this is roundingSELECT ROUND(11.24); We can also put two numbers to indicate the number of digitsSELECT ROUND(122.237323.2);
Copy the code
  • Ceil: round up; Floor: round down
SELECT ceil(1.24); # the results for2SELECT floor(1.24); # the results for1
Copy the code
  • Truncate: truncation
# after small1Bit truncation1.6SELECT TRUNCATE(1.65.1);
Copy the code
  • The mod take over
SELECT MOD(10.3); # the results for1
Copy the code
(3) Date function
  • Now: Returns the current system date, including time
SELECT NOW();
Copy the code
  • Curdate: Returns the current system date, excluding the time
SELECT CURDATE();
Copy the code
  • Curtime: indicates the current system time, excluding the date
SELECT CURTIME();
Copy the code
  • Gets the specified part, year, month, day, hour, minute, second, and so on
SELECT CONCAT(YEAR(NOW()),'years'.MONTH(NOW()),'month'.DAY(NOW()),'day'.HOUR(NOW()),'when'.MINUTE(NOW()),'points'.SECOND(NOW()),'秒');
Copy the code
  • Str_to_date: Converts the characters in the date format to the specified format
SELECT STR_TO_DATE('1994-10-10'.'%Y-%m-%d');
Copy the code
  • Date_format: Converts dates to characters
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') as out_put;
Copy the code
  • Date_diff: Calculates the difference of days between dates
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;SELECT DATEDIFF('2021-09-04'.'1998-10-02');
Copy the code
(4) Other functions
  • Version: indicates the version number
  • The database, select the database ();
  • User: select the user ();
(5) Process control function
  • If: similar to if else
# Similar to the ternary operatorSELECT IF(10 < 5.'big'.'small');
Copy the code
  • Case: The first way: Switch the effect of case
# Depending on the department, different departments have different percentages of salarySELECT salary,department_id,
CASE department_id
	WHEN 30 THEN salary * 1.1
	WHEN 40 THEN salary * 1.2
	WHEN 50 THEN salary * 1.3
	ELSE salary
END AS 'New salary'
FROM employees;

-------------------------------------------------# Another exampleselect 
case 3
when 3 then 'this is the 3'
WHEN 4 then 'this is 4'
WHEN 5 then 'this is 5'
else 'I don't know that.'
end as 'digital';
Copy the code

1.5 Common functions — Grouping functions

Pass a set of values into a single value.

  • The sum, sum
  • Avg: average
  • Max: maximum value
  • Min: indicates the minimum value
  • Count: Indicates the number to be calculated

Grouping function features:

  1. Sum and AVG support only numerical types.
  2. Count, Max, min can be used for any type;
  3. All grouping functions ignore null values, and this column is not calculated directly. For example, if 20 out of 100 people have null values, then the avG divided by 80 is calculated.
  4. All grouping functions can be paired with distinct to implement deduplication.
  5. Count (*) and count(column name), but if count(column name) is null,SELECT COUNT(1) FROM employees;It’s equal to adding a column of 1’s and counting the number of 1’s, it’s equal to counting the total number, but it’s hardly used;

Under myISam, count(*) is the most efficient because it contains a counter that returns the number directly;

In the InnoDB storage engine, count(*) is about as efficient as count(1), but a little more efficient than count(column name) because count(column name) does a null check

Note: The fields queried with grouping functions are limited

SELECT AVG(salary),employee_id from employees;
Copy the code

Above one check is a line, one check is multiple lines, so found out is problematic, certainly wrong.

1.6 Group by: indicates groups

# query the highest salary for each jobSELECT MAX(salary),job_id FROM employees GROUP BY job_id;
Copy the code

1.7 Having — Query result as query condition

Select * from department where there are more than two employees in the departmentSELECT COUNT(*) ASThe number of department_idFROM employees GROUP BY department_id HAVINGThe number of> 2; # query the highest salary of a bonus employee for each job category>12000Job type number and highest salaryCopy the code

SQL > select * from table where number > 2; SQL > select * from table where number > 2; SQL > select * from table where number > 2; SQL > select * from table where number > 2;

The group condition of HAVING

You can also group or alias by expression or function (duh) :

SELECT COUNT(*),LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5;

SELECT COUNT(*)ASQuantity, LENGTH (last_name) lenFROM employees GROUP BY LENGTH(last_name) HAVINGThe number of> 5;
Copy the code

The group by clause supports both single field grouping and multiple field grouping.

2, multi-table join query

2.1 Connection conditions and classification

Join a girl from beauty to a boy from boys with the following SQL statement:

SELECT `name`,boyname FROM beauty,boys;
Copy the code

This query is syntactically sound, but produces cartesian products; Each piece of data in table A has to be joined with each piece of data expressed by B to produce data results that are not what we want. Query conditions need to be added to avoid cartesian products.

So the conditions are as follows:

SELECT 
	`name`,boyname 
FROM 
	beauty,boys 
WHERE 
	boyfriend_id = boys.id;
Copy the code

This can be avoided by adding valid connection conditions, which can be divided into:

  1. Sql92 standard: only internal connection and external connection are supported.
  2. Sql99 standard: internal connection, external connection (left outside + right outside), cross connection.

Classification:

  • In the connection
    • Contour connection
    • Unequal connection
    • Since the connection
  • Outer join
    • The left outer join
    • Right connection
    • Full outer join
  • Cross connection

2.2 the sql92 grammar

(1) Equivalent connection

A multi-table equivalent join is the intersection part of a multi-table. An N-table join requires at least n-1 join conditions.

Select employee name from department nameSELECT 
	last_name,department_name 
FROM 
	employees,departments 
WHERE 
	employees.`department_id` =departments.`department_id`; # select * from employee where id = 1SELECTLast_name,employees.job_id,job_title # Represents by the field nameFROM 
	employees,jobs
WHERE
	employees.`job_id` = jobs.`job_id`;
Copy the code

If the table join fields are always repeated, the two tables are prone to ambiguity, so you can use aliases.

SELECT 	e.last_name,e.job_id,j.job_titleFROM 	employees e,jobs jWHERE	e.`job_id` = j.`job_id`;
Copy the code
(2) non-equivalent connection

So the equal condition up here becomes the not equal condition.

# query employee's salary and salary levelSELECT 	e.`salary`,j.`grade_level`FROM	employees e,job_grades jWHERE 	e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
Copy the code
(3) Self-connection

The connection is itself.

Select * from employee where name = 'employee'SELECT 	e.`last_name`,f.`last_name`FROM 	employees e,employees fWHERE 	e.`manager_id` = f.`employee_id`;
Copy the code

2.3 sql99 grammar

# grammar:SELECTQuery listFROM 
	表1The aliasjoin 
	表2The aliasonJoin conditionwhereScreening conditions;Copy the code

The above is more readable because the join condition and filter condition used to be together, but now they are separated, as in e.manager_id = f.employee_id; Join conditions are placed in where.

Sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99 sql99

Inner (outer), left (outer), right (outer), full (outer), cross (cross).

(1) internal connection: equivalent, unequal, self-connection
Select * from employee where name = 'employee'SELECT 
	e.`last_name`,f.`last_name`
FROM
	employees e
INNER JOIN 
	employees f
ON
	e.`manager_id` = f.`employee_id`;
Copy the code
(2) left outer connection and right outer connection
Select * from employee; select * from employeeSELECT 
	e.`last_name`,f.`last_name`
FROM
	employees e
LEFT JOIN 
	employees f
ON
	e.`manager_id` =f.`employee_id`; # right outer joinSELECT 
	e.`last_name`,f.`last_name`
FROM
	employees e
RIGHT JOIN 
	employees f
ON
	e.`manager_id` = f.`employee_id`;    	
Copy the code

If A joins B, then A is the master table, B is the slave table, and all data in A will be displayed. If the data cannot be matched from the table, null will be used to match.

Left join: the left join is the primary table and the right join is the slave table.

Right join: the primary table on the right and the secondary table on the left;

(3) full external connection

Mysql is not supported here. Just to show you the syntax.

SELECT 
	b.*,bo.*
FROM 
	beauty b,
FULL OUTER JOIN 
	boys bo
ON
	b.`boyfriend_id` = bo.id;
Copy the code

Select * from both tables where null = null;

(4) Cross connection
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
Copy the code

A bit like an unconditional query, containing a Cartesian product.

3. Subquery

3.1 Classification of subqueries

Nested query, external query statement called the main query or external query, internal query statement called sub-query or internal query.

classification

According to the location of the sub-query, it can be divided into:

  • After SELECT: only scalar quanta queries are supported
  • Table subquery is supported
  • Where or having: supports scalar, column, and (less frequently) row subqueries
  • Table subqueries are supported in exists

According to the number of rows and columns in the result set:

  • Scalar quantum query (result set only has one row and one column)
  • Column subquery (result set with one column and multiple rows)
  • Row subquery (result set with one row and multiple columns)
  • Table subquery (result set with multiple rows and columns)

3.2 Where or having after

Features: all in parentheses, all to the right of the condition.

  • Standard quantum query: generally with single line operators: >, <, =, >=, <=,! =
# Who gets paid more than AbelSELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
Copy the code
salary
11000.00

The result of the SELECT subquery is, indeed, row by column.

  • Column subqueries: usually with multi-line operators: IN, ANY, SOME, ALL

    A > min(10,20,30) ==> min(10,20,30)

    A > all(10,20,30) ==> Max (10,20,30)

# return location_id is1400or1700The names of all employees in the departmentSELECT 
	last_name
FROM 
	employees
WHERE 
	department_id IN(
		SELECT DISTINCT 
			department_id
		FROM 
			departments
		WHERE 
			location_id IN (1400.1700))Copy the code
  • Row subquery: row with multiple columns or row with multiple columns
Select * from employee where employee number is smallest and salary is highestSELECT 
	*
FROM
	employees
WHERE
	employee_id = (
		SELECT 
			MIN(employee_id)
		FROM
			employees
	)
AND
	salary = (
		SELECT
			MAX(salary)
		FROMEmployees) # Row subquery modeSELECT 
	*	
FROM
	employees
WHERE
	(employee_id,salary) =(
		SELECT 
			MIN(employee_id),MAX(salary)
		FROM 
			employees
	)
Copy the code

3.3 select the back

# query the number of employees in each departmentSELECT d.*, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id`) 'number'FROM departments d;
Copy the code

3.4 the from behind

Select * from salary level where the average salary is for each departmentSELECT 
	ag_dep.*,g.`grade_level`
FROM E
	SELECT 
		AVG(salary) ag,department_id 
	FROM 
		employees 
	GROUP BY 
		department_id
) ag_dep
INNER JOIN 
	job_grades g
ON
	ag_dep.ag BETWEEN lowest_sal AND highest_sal;
Copy the code

3.5 After exists (Related Subquery)

SELECT EXISTS (SELECT employee_id from employees);
Copy the code

4, paging query

# grammar:SELECTQuery listFROMTable # may have a joinjoinClause 】 #onJoin conditionWHEREfilterGROUP BYThe grouping fieldHAVINGFilter limits after groupingoffset, the size; #offsize: The starting index of the entry to display (the starting index starts from0Start) #size: indicates the number of entries to displayCopy the code
# Display the first five employees' informationSELECT * FROM employees LIMIT 0.5;
Copy the code

Formula: the number of pages to display is page: the number of entries per page is size.

The initial index is (page-1)×size.

Limit (page-1)×size,size.

5, joint query

Combine the results of multiple query statements into a single result.

# query department id>90Or the email contains a's employee informationSELECT * FROM employees WHERE department_id > 90
UNION
(SELECT * FROM employees WHERE email LIKE '%a%');
Copy the code
# syntax: query statement1
UNIONThe query2;
Copy the code

When do you need it?

For example, when the query results come from different tables and the tables are not related, but the query information is the same, you can use join query. And both queries need to have the same number of resulting columns. Otherwise, an error message will be displayed. To ensure that the query results meet your requirements, the columns of the two result sets must be in the same order.

The union keyword is de-duplicated by default and can contain duplicates if union all is used.