Super core learning manual series 2 query article - MySQL knowledge points, learning collection essential

This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

The vast sea of thousands of thousands, thank you for this second you see here. Hope my article is helpful to you!

Wish you in the future, keep love, go to the mountains and seas!

1. Learn DQL

It is mainly used for querying tables, and queries do not modify the data in the database, just a way to display the data. This chapter is arguably the most important part of MySQL. The keyword to be queried is SELECT.

The next step is to learn in a step-by-step manner. Now that we’re going to learn how to do queries, we’re going to have to have data, so I’m just going to give you the data, so we don’t have to insert or modify any other commands, but we’re just going to practice queries. I’ve prepared the data from my study database in the silicon Valley online video. Thank you very much. I have learned deeply and explained in great detail. I was going to write the data right here, but it’s too long.

If everyone has the condition, still go to my Baidu net disk link to download, also not big, download very quickly. And then I’ll show you how to do it, okay

Link: pan.baidu.com/s/1SdwP62ks… Extract code: FXJO

Now, by default, you’re done, and you have the data, so let’s move on.

Advanced 1. Basic query

1.1 Basic Grammar

Let’s look at the syntax first:
```
SELECTQuery the list (field name1The field name2...)FROMThe name of the table.Copy the code
```
When this query comes out, it actually looks a bit like system.out.println () in Java to print to the console.

Another syntax is, if I want to query all the field names in a table, do we have to type them out by hand? Of course not. We can be

Use * to query all field names in the table, that is, query all.
```
SELECT * FROMThe name of the table.Copy the code
```
Features:
- The query list can be constant values, expressions, fields, or functions.
- The result of our query is a virtual table, not a real table.

Next, the actual operation:

-- 1. Query a single field in the table
SELECT `name` FROM beauty; 
-- Name is a keyword, but we also use it as a field. Although we can execute it, we can avoid ambiguity by adding the symbol ' '.

-- 2. Query multiple fields in the table
SELECT `name` , sex FROM beauty;

-- 3. Query all fields in the table
SELECT * FROM beauty;
Copy the code

One detail to note here:

Since some fields may be keywords in MySQL, we can avoid ambiguity by adding the ‘ ‘character:’ name ‘.
In addition to querying fields, you can also query constant values, expressions, and functions.
```
#4.Query constant valueSELECT 100;
SELECT 'john';
 
#5.Query expressionSELECT 100%98;
 
#6.Query functionSELECT VERSION();
Copy the code
```
I’m not going to show you the results here, just try it out and get the results.

1.2 names

The reason why we have aliases is because some fields have the same name, so we can alias them.

Grammar:

SELECTThe field name1 ASAlias, field name2 ASThe alias...FROMThe name of the table.Copy the code

Note: The keyword AS can be omitted, but the space in it must be included.

Features:
- When you specify an alias for a column or table, use the AS keyword.
- The advantage of using aliases is the ease of viewing and processing the queried data.
- If the fields to be queried have the same name, use the alias to distinguish them.

Here’s a demonstration:

-- The original query
SELECT last_name, first_name FROM employees;
-- Alias using AS
SELECT last_name ASSurname, first_nameAS 名 FROM employees;
-- Omit AS, and the space cannot be omitted
SELECTLast_name Name, first_name nameFROM employees;
Copy the code

1.3 to heavy

If the data we query has a bunch of duplicate values, we can eliminate DISTINCT values by using the keyword DISTINCT.

Grammar:

SELECT DISTINCTThe field nameFROMThe name of the table.Copy the code

Presentation:

Query all department numbers in the employee table
SELECT department_id FROM employees;
SELECT DISTINCT department_id FROM employees;
Copy the code

1.4 Query Result Calculation

The result of our query can also participate in the calculation.

Grammar:

-- A column of data and fixed value operations
SELECTThe field name+ 值 FROMThe name of the table.-- A column of data and other columns of data to participate in the operation
SELECTfield1 +field2 FROMThe name of the table.-- Direct operation
SELECT 值 +Value;Copy the code

Operator + features:
1. If both operands are numeric, the addition is performed.
2. As long as one of the sides is a character, an attempt is made to convert a character value to a numeric value, where the addition continues if the conversion succeeds, and if the conversion fails, the character value is converted to 0.
3. As long as either side is NULL, the result must be NULL.

Here’s a demo:

-- A column of data and fixed value operations
SELECT lowest_sal + 20 FROM job_grades;

-- A column of data and other columns of data to participate in the operation
SELECT lowest_sal + highest_sal FROM job_grades;

-- Direct operation
If both operands are numeric, add.
SELECT 90 + 10;

If one of the parties is a character and an attempt is made to convert a character value to a numeric value,
-- Where, if the conversion succeeds, the addition operation continues,
SELECT '99' + 88 ;
If the conversion fails, the character value is converted to 0
SELECT 'hello' + 100;

If either side is null, the result must be null
SELECT NULL + 100;
Copy the code

Advanced 2: Query by condition

All of our previous queries have queried all of the data, but sometimes we just want to get the data that meets the criteria.

2.1 Basic Grammar

Add the keyword WHERE to the original query syntax.

Grammar:
```
SELECTQuery listFROMThe name of the tableWHEREScreening conditions;Copy the code
```
So how do we write this filter? It can be classified as follows:
1. Filter by conditional expression
  
  Simple conditional operators: > < =! = <> >= <=.
2. Filter by logical expression
  
  Logical operators: && | |! And the or not.
3. Fuzzy query:
Like between and in is null.

How do you use it? I’ll go into more detail.

2.2 Filtering conditional expressions

Comparison operators:

> < = : Greater than, less than, and equal to.
! = < >: These two are not equal to.
> = < =: Greater than or equal to, less than or equal to.

Here’s a demonstration:

Select * from employee where salary >12000
SELECTLast_name Employee name, salaryFROM employees 
WHERE salary > 12000;

Query the employee name and department number whose department number is not equal to 90
SELECTLast_name Employee name, department_id Department IDFROM employees 
WHERE department_id <> 90;
SELECTLast_name Employee name, department_id Department IDFROM employees 
WHERE department_id ! = 90;

-- query employee name and employee id <=110
SELECTLast_name Indicates the employee name. Employee_id Indicates the employee IDFROM employees 
WHERE employee_id < = 110;
Copy the code

2.3 Filtering logical expressions

&& and: Multiple conditions are met simultaneously.
|| or: Satisfies one of the conditions.
! not: Not satisfied.
Usually we use and or notThere are many.

Here’s a demonstration:

Select * from employee where z = 10000 and z = 20000
SELECTLast_name Employee name, salaryFROM employees
WHERE salary > = 10000 AND salary < = 20000;
SELECTLast_name Employee name, salaryFROM employees
WHERE salary > = 10000 && salary < = 20000;

Select * from employee whose salary is less than 10000; select * from employee whose salary is more than 20000
SELECTLast_name Employee name, salaryFROM employees
WHERE salary < = 10000 OR salary > = 20000;
SELECTLast_name Employee name, salaryFROM employees
WHERE salary < = 10000 || salary > = 20000;

Select * from employee where employee id is not between 100 and 150
SELECTLast_name Indicates the employee name. Employee_id Indicates the employee IDFROM employees
WHERE NOT(employee_id > = 100 AND employee_id < = 150);
SELECTLast_name Indicates the employee name. Employee_id Indicates the employee IDFROM employees
WHERE !(employee_id > = 100 && employee_id < = 150);
Copy the code

2.4 Fuzzy Query

2.4.1 The like keyword

LIKE: Usually used with two wildcards:
- % : any number of characters, including 0 characters
- _ : Any single character

Grammar:

SELECTQuery listFROMThe name of the tableWHEREThe field nameLIKE 'Wildcard string';
Copy the code

Specific demonstration:

-- Query employee names that start with a
SELECTLast_name staff nameFROM employees
WHERE last_name LIKE 'a%';

-- Query employee names that end with character a
SELECTLast_name staff nameFROM employees
WHERE last_name LIKE '%a';

Query employee name whose name contains character a
SELECTLast_name staff nameFROM employees
WHERE last_name LIKE '%a%';

Select * from employee whose third character is' s' and fifth character is' I '
SELECTLast_name Employee name, salaryFROM employees
WHERE last_name LIKE '__s_i%';

Query employee name whose second character is _
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * MySQL has a unique way of defining a character followed by the keyword ESCAPE and then defining 'character'. * /
SELECTLast_name Employee name, salaryFROM employees
WHERE last_name LIKE '_ $_ %' ESCAPE '$';
Copy the code

Note: If we need to look for characters that contain our wildcard characters, etc., then we can use escape characters:

You can use \ to escape as in Java.
- MySQL has a unique way of defining a character followed by the keyword ESCAPE and defining the character. As shown above.

2.4.2 Between and Keywords

Between and: Include the critical values and do not exchange the order of the two critical values.

Direct demonstration:

Query information about employees whose ID is between 100 and 120
SELECT * FROM employees
WHERE employee_id BETWEEN 100 AND 120;

-- Error, no error, but no result.
SELECT * FROM employees
WHERE employee_id BETWEEN 120 AND 100;
Copy the code

2.4.3 In keywords

In: The value types of the in list must be consistent or compatible, and wildcards are not supported in the in list.

Direct demonstration:

-- Query an employee whose job number is IT_PROG, AD_VP, AD_PRES
SELECT last_name, job_id FROM employees
WHERE job_id IN( 'IT_PROT' ,'AD_VP'.'AD_PRES');
Copy the code

2.4.3 IS NULL keyword

Is nul:

Because = or <> cannot be used to determine NULL values, we can use is NUl or is not NUl to determine NULL values.

Direct demonstration:

Select * from employee with no bonus; select * from employee with no bonus
SELECTLast_name Indicates the employee name, commission_pct Bonus rateFROM employees
WHERE commission_pct IS NULL;

Select * from employee where bonus is available
SELECTLast_name Indicates the employee name, commission_pct Bonus rateFROM employees
WHERE commission_pct IS NOT NULL;
Copy the code

Advanced 3: Sort queries

We find that we may query out of order, then we can find the table in a certain order. Keywords: ORDER BY

Grammar:

SELECTQuery listFROMThe name of the table"whereScreening conditionsORDER BYThe field nameASC|DESC;
Copy the code

Features:
1. ASC stands for ascending order and can be omitted, while DESC stands for descending order.
2. The ORDER BY clause can support a single field (single column), an alias, an expression, a function, or multiple fields (multiple columns).
3. The ORDER BY clause is at the end of the query, except for the LIMIT clause. I’ll talk about that later.

Actual combat demonstration:

Sorting in a single field is single-column sorting:

Sort by single field (single-column sort)
Query employee name and salary in descending order
SELECT last_name, salary 
FROM employees
ORDER BY salary DESC;
-- Add filters and sort again
-- Query the name and number of employees whose department number is >=90 in descending order
SELECT last_name, employee_id
FROM employees
WHERE employee_id > = 90
ORDER BY employee_id;
Sort by expression
Query employee name, salary, annual salary in descending order of annual salary
-- Annual salary = salary*12*(1+IFNULL(commission_pct,0))
SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0) -- earningFROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
Sort by alias
-- query employee name, salary, annual salary in ascending order of annual salary
SELECT last_name, salary, salary*12*(1+IFNULL(commission_pct,0) -- earningFROM employees
ORDER BYAnnual salaryASC;
Sort by function
Query employee name, name length, and descending order by name length
-- The length function is the length() function
SELECTLast_name, LENGTH (last_name) LENGTHFROM employees
ORDER BY LENGTH(last_name) ASC;
Copy the code

What if the same fields appear in the sorted order, and the other columns are not sorted properly? So can we sort by multiple fields, multiple columns? No, of course.

How to do it?

Sort by multiple fields (sort by multiple columns)
Query employee name and salary in descending order and then ascending order by employee name
SELECT last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC;
Copy the code

Advanced 4: Common functions

Contains functions in MySQL, divided into single line functions and group functions (aggregate function, statistical function, group function)

4.1 One-line functions

4.4.1 classification

Single-line functions can be divided into:

Character function
Mathematical function
Date function
Other functions
High-level functions (control statements)

See below for details:

4.1.2 Character functions

Common character functions:

function	describe
LENGTH	Get the number of bytes (utF-8 a Chinese character represents 3 bytes, GBK is 2 bytes)
CHAR_LENGTH	Returns the number of characters in a string
CONCAT	Concatenated string
SUBSTR	Intercept string
INSTR	Returns the index of the first occurrence of the substring, or 0 if not found
TRIM	Remove Spaces before and after, or specify characters before and after
UPPER and LOWER	Make the letter uppercase or smaller
LPAD, RPAD	Fills the specified length (left or right) with the specified character
REPLACE	Replace the source string in the string with the new string

Here’s a demonstration:

-- length: obtain the number of bytes (UTF-8, a Chinese character represents 3 bytes, GBK is 2 bytes)
SELECT LENGTH('hello'); 5 -
SELECT LENGTH('China'); - 6

-- char_length: Returns the number of characters in the string
SELECT CHAR_LENGTH('hello');5 -
SELECT CHAR_LENGTH('China');2 -

-- concat: Concatenates a string
SELECT CONCAT(last_name, '_', first_name) nameFROM employees;

-- substr: intercept
Note: the MySQL index starts at 1
-- Intercepts all characters following the specified index
SELECT SUBSTR('Xiao Longnu and Yang Guo'.5); - John Steinbeck
-- Intercepts a character of the specified character length from the specified index
SELECT SUBSTR('Xiao Longnu and Yang Guo'.1.3); - little dragon female

-- instr: returns the index of the first occurrence of the substring, or 0 if not found
SELECT INSTR('Xiao Longnu and Yang Guo'.'dragon lady'); 2 -
SELECT INSTR('Longnu Xiao Longnu and Yang Guo'.'dragon lady'); 1 -
SELECT INSTR('Longnu Xiao Longnu and Yang Guo'.'the dragon male'); Return 0 if 0 is not found

-- trim: remove the space before and after, or specify characters before and after
SELECT CHAR_LENGTH(Polaris);  9 -
SELECT CHAR_LENGTH(TRIM(Polaris)); - 3
-- you can specify characters before and after
SELECT TRIM('a' FROM 'AAA North AA Polar star AAA');-- The middle of the north AA polar star was not removed

-- "upper" and "lower" : write in upper and lower letters
SELECT UPPER('hello');-- HELLO
SELECT LOWER('heLLo');-- hello

- lpad, rpad: specified characters implementing left | right fill the specified length
SELECT LPAD('hello'.10.'a'); -- aaaaahello
SELECT RPAD('hello'.10.'ab'); -- Helloababa is only 10 characters long.
-- The test does not exceed the original character
SELECT LPAD('hello'.2.'a');  - he truncation
SELECT RPAD('hello'.3.'ab'); - hel truncation

-- replace: Replace the source string in the string with the new string
SELECT REPLACE('Yang Guo and Xiao Longnu and Yang Guo'.'John Steinbeck'.'我'); -- Me and Xiaolongnu and me
Copy the code

Note:

The MySQL index starts at 1.

4.1.3 Mathematical functions

Common mathematical functions:

function	describe
ROUND	rounded
RAND	Returns a random number from 0 to 1
CEIL	Rounded up, returns >= the smallest integer of the parameter
FLOOR	Rounding down, returns <= the maximum integer of the parameter
TRUNCATE	truncation
MOD	Take more than

Here’s a demonstration:

-- round: Round
SELECT ROUND(1.65); 2 -
SELECT ROUND(1.45); 1 -

-- rand: returns a random number from 0 to 1
SELECT RAND(); -- 0.6992990986437528 is not fixed

-- ceil: Rounded up. >= The smallest integer of the parameter
SELECT CEIL(1.2); 2 -
SELECT CEIL(1.6); 2 -

-- floor: Round down, returning <= the maximum integer for this parameter
SELECT FLOOR(1.2); 1 -
SELECT FLOOR(1.6); 1 -

-- truncate: truncate
SELECT TRUNCATE(1.8999.1); - 1.8
SELECT TRUNCATE(RAND(), 1); - 0.9

-- mod: mod
SELECT MOD(10.3); 1 -
Copy the code

4.1.4 Date function

Common date functions:

function	describe
NOW	Returns the current system date + time
CURDATE	Returns the current system date, without time
CURTIME	Returns the current time, without date
DATEDIFF	Calculate the number of days between two dates
YEAR, MONTH, MONTHNAME DAY, HOUR, MINUTE, and SECOND	Gets the specified portion, year, month, day, hour, minute, second
STR_TO_DATE	Converts a character to a date in the specified format
DATE_FORMAT	Converts dates to characters

Here’s a demonstration:

-- now: returns the current system date + time
SELECT NOW();

-- curdate: returns the current system date without time
SELECT CURDATE();

-- curtime: returns the current time, without date
SELECT CURTIME();

-- datediff: Calculates the number of days between two dates
SELECT DATEDIFF('2020-01-01'.'1988-01-01');
SELECT DATEDIFF('1988-01-01'.'2020-01-01');

-- Can get specified parts, year, month, day, hour, minute, second
-- year: Specifies the year
SELECT YEAR(NOW());
SELECT YEAR('2020-02-02');
-- month: Obtains the specified month
SELECT MONTH(NOW());
-- monthname: gets the specified month word
SELECT MONTHNAME(NOW());
-- day: Gets the specified day
SELECT DAY(NOW());
-- hour: Obtain the specified hour
SELECT HOUR(NOW());
-- minute: Obtains a specified minute
SELECT MINUTE(NOW());
-- second: Gets the specified second
SELECT SECOND(NOW());

-- str_to_date: Converts a character to a date in the specified format
SELECT STR_TO_DATE('2020-02-02'.'%Y-%m-%d');
SELECT STR_TO_DATE('02/02 2020'.'%m/%d %Y');

-- date_format: Converts dates to characters
SELECT DATE_FORMAT('2020-02-02'.'%Y year %m month %d day ');
SELECT DATE_FORMAT(NOW(), '%y year % C month %d day ');
SELECT DATE_FORMAT(NOW(), '%m-%d %Y');
Copy the code

Note:

When using the str_to_date and date_format functions, the second argument defines the format:

4.1.5 Other functions

Other common functions:

function	describe
version	Return the current MySQL version
database	Returns the database currently in use
user	Returns the currently used user

Here’s a demonstration:

-- version: Returns the current MySQL version
SELECT VERSION(); - 5.5.40

-- database: Returns the database currently in use
SELECT DATABASE(); -- data_test

-- user: returns the current user
SELECT USER(a);-- root@localhost
Copy the code

4.1.6 Advanced Functions (Control functions)

If function: can be equivalent to if else.

Grammar:

IF (condition,'Value returned if the condition is true'.'Value returned if the condition is not true')
Copy the code

Presentation:

SELECT IF(66>88.'big'.'small'); Small -
-- Query the name of the employee whose salary is more than 20000, show oops good yo, otherwise show I think not, please continue to work hard
SELECT last_name,
IF(salary > 20000.'Oh, good for you.'.'I don't think so. Please keep going.')
FROM employees;
Copy the code

Case functions: Case functions can be used in two ways:

Method 1: Can be equivalent to the switch case effect.

Grammar:

selectThe field name1The field name2.caseThe field or expression to determinewhen 值1 thenThe value returnedwhen 值2 thenThe value returned...elseValue n or statement n to display;endThe aliasCopy the code

Usage:

/* Query the employee's salary, ask the department number =30, display the salary is 2 times department number =40, display the salary is 3 times department number =50, display the salary is 4 times other department, display the salary is original salary */
SELECTSalary,department_id,CASE department_id
WHEN 30 THEN salary*2
WHEN 40 THEN salary*3
WHEN 50 THEN salary*4
ELSE salary
ENDThe new salaryFROM employees;
Copy the code

Method 2: Can be equivalent to multiple if effects.

Grammar:

selectThe field name1The field name2.case 
whenJudge conditions1 thenThe value to display1Or statements1
whenJudge conditions2 thenThe value to display2Or statements2.elseValue n or statement n to displayend
Copy the code

Usage:

/* Select * from employee where employee is paid */ * select * from employee where employee is paid */
SELECT salary,
CASE 
WHEN salary > 20000 THEN 'Oh, good for you.'
WHEN salary > 15000 THEN 'Fine.'
WHEN salary > 10000 THEN 'That's great.'
ELSE 'Keep up the good work'
ENDWage descriptionFROM employees;
Copy the code

4.2 Grouping Functions (Aggregate functions)

The grouping function is actually a vertical query that evaluates a column of values and returns a resulting value. In addition, grouping functions ignore the NULL value.

4.2.1 Five grouping functions

function	describe
COUNT	The number of records in a specified column is counted. The number of records in a NULL column is not counted
SUM	Computes the numeric sum of the specified column, or 0 if it is not of numeric type
AVG	Calculates the average value of the specified column
MAX, MIN,	Computes the maximum value of the specified column

4.2.2 characteristics

All of the above five grouping functions can ignore NULL values.
Sum and AVg are generally used to handle numeric types, while Max, min, and count can handle any data type.
Can be used with distinct to calculate the result after deduplication.
Parameter of count can support: field, *, constant value: generally put 1.

Holdings usage

-- 1, simple use
SELECT SUMThe sum (salary),AVG(salary) on average,MAXThe highest (salary),MIN(salary),COUNT(salary) numberFROM employees; -- 691400.00， 6461.682243， 24000.00， 2100.00， 107

What types of parameters are supported
SELECT SUM(last_name), AVG(last_name) FROM employees; - 0, 0
SELECT SUM(hiredate), AVG(hiredate) FROM employees; -- No error, but what's the point

SELECT MAX(last_name),MIN(last_name) FROM employees;-- Zlotkey, Abel
SELECT MAX(hiredate),MIN(hiredate) FROM employees;-- 2016-03-03 00:00:00, 1992-04-03 00:00:00

SELECT COUNT(commission_pct) FROM employees; - 35
SELECT COUNT(last_name) FROM employees;  - 107

-- 3. Whether to ignore null
SELECT commission_pct FROM employees; -- This line contains a null value
SELECT COUNT(commission_pct) FROM employees; -- 35 ignores null values
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees; -- 0.40, 0.10 ignores null values
SELECT SUM(commission_pct) ,AVG(commission_pct), SUM(commission_pct)/35.SUM(commission_pct)/107 -- Ignores null values
FROM employees; -- 7.80, 0.222857, 0.222857, 0.072897

(4)
SELECT salary FROM employees;  -- This line contains duplicate values
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; - 397900.00, 691400.00
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; - 57, 107

-- 5. Count function
SELECT COUNT(salary) FROM employees; - 107
SELECT COUNT(*) FROM employees;- 107
SELECT COUNT(1) FROM employees;- 107
SELECT COUNT(commission_pct) FROM employees; -- 35 Is not recorded as NULL

The fields queried with the group function are limited
SELECT employee_id FROM employees; - multiple lines
SELECT AVG(salary),employee_id  FROM employees; -- 6461.682243, 100 displays only one line.
Copy the code

Note:

count(*)Generally used to count the number of parameters, relative to the field, because sometimes there may be NULL in the field, so the statistics will not be carried out, can not be correctly counted all the data in the table.
The group function is used to query a single row of data, while other fields are used to query multiple rows. However, MySQL does not report an error. It displays a single row of data directly, so it is meaningless. If you want to add other fields, you can usegroup byAfter the field.group byI’m going to talk about that.

Advanced 5: Group query

Is to display the query information in groups. The principle is to group the same content in the result of grouping fields. It is mainly used with grouping functions. The purpose of grouping is to get better statistics.

Concept introduction: For example, in the employee table, there is a field for the department number. The repeated department number means that all the employees are under this department. Can we count the number of employees in each department? For example, in a class, there are boys and girls, can we count how many boys and how many women there are?

How do you use it? The keyword for grouping is group by.

5.1 grammar

Common syntax format:
```
SELECTQuery listFROMThe name of the tableGROUP BYThe field name.Copy the code
```
The query list here is a group function, and the field name is the field name after group by.

Copy syntax format:

With the query keyword we learned earlier, we can write:

SELECTQuery listFROMThe name of the table"whereScreening conditionsGROUP BYThe field name"order bySorted fields 】;Copy the code

5.2 Actual operations

Query how many people there are in each department
SELECT COUNT(*), department_id 
FROM employees 
GROUP BY department_id;

Add a filter to query the number of people in the department whose department id is not Null and whose department id is between 20 and 60
SELECT COUNT(*), department_id FROM employees
WHERE department_id IS NOT NULL 
AND department_id BETWEEN 20 AND 60
GROUP BY department_id;

In this case, we need the keyword having
-- Query the number of employees in a department >5
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
WHERE COUNT(*) > 5;  You can't call where if you want to filter after grouping

SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Here's another example to try using aliases:
-- The number of jobs and the highest salary for each type of employee with the highest salary >12000
SELECT MAX(salary) maximum salary, job_idFROM employees
GROUP BY job_id
HAVINGThe highest salary> 12000;

-- Grouping and sorting
-- The number of jobs with the highest salary >6000 for each bonus employee, in ascending order of highest salary
SELECT MAX(salary) maximum salary, job_idFROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVINGThe highest salary> 6000
ORDER BYThe highest salaryASC;

-- Multiple fields can also be grouped
Query the minimum wage in each department for each type of work, and the minimum wage is greater than 7000, in descending order by minimum wage
SELECT MIN(salary), job_id, department_id
FROM employees
GROUP BY job_id, department_id
HAVING MIN(salary) > 7000
ORDER BY MIN(salary) DESC;
Copy the code

Note:

If we want to filter after grouping, we will get an error if we write the “where” keyword, and the “having” keyword.

So the difference between the two:

whereThe keyword is applied to the original table for filtering, and the position is ingroup byBefore, that is, before grouping.
havingKeywords are used ingroup byThe following result set is filtered again, and the position isgroup byAfter, that is, after grouping.
havingAnd then you can use the grouping function,whereGrouping functions cannot be used later

5.3 the characteristics of

You can group a single field or multiple fields, separated by commas.
The fields queried with the grouping function are preferably grouped.
The keyword for group filtering is having. It is best to use pre-group screening, try to use pre-group screening, improve efficiency.
Aliases can be supported after having.
The use of sorting can be supported.

Advanced 6: Join queries

Also known as a multi-table query, our join query is used when the query has fields from multiple tables.

Let’s introduce the concept: for example, in our goddess table, there is a corresponding boy ID, this ID corresponds to the male god table ID. At this time, multiple tables are needed to query. For example, if we want to find the information of the corresponding department through the department number in the employee table, we also need multiple tables for query.

6.1 classification

Chronologically
- Sql92: Only inner joins (implicit inner joins) are supported.
- Sql99 (more recommended) : Support inner join (explicit inner join) + outer join (left outer and right outer) + cross join.
By function
- Internal connection: equivalent connection, non-equivalent connection, self connection
- External connection: left external connection, right external connection, full external connection
- Cross connection

6.2 Cartesian product phenomenon

6.2.1 What is cartesian product

Typically occurs in multi-table queries.

Let’s see when this happens first.

Let’s find out who the goddess is. This is where you need to use multiple table queries. Let’s see:

SELECT * FROM beauty;
SELECT * FROM boys;
SELECT * FROM beauty, boys;
Copy the code

The above data is actually a combination of each data in the left table and each data in the right table. There are 12 items in the left table and 4 items in the right table, and the final combination of 12*4=48 items.

Each piece of data in the left table is combined with each piece of data in the right table, an effect known as the Cartesian product.

Why does this happen?

Because we did not add a valid join condition when querying. Some data is not what we want, a goddess should correspond to a male god, no male god information should not be displayed, right? So we need to manually filter out some of the useless data that we don’t want.

6.2.2 Eliminate cartesian product

The filter we need to add is called a table join condition.

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

6.3 the sql92

Support all internal connection: equivalent connection, non-equivalent connection, self connection. This supported inner join can be called an implicit inner join.

6.3.1 inside connection

Obtain the required data when querying multiple tables.

Classification:
1. Contour connection
2. Nonequivalent connection
3. Since the connection

6.3.2 Equivalent connections

Features:
1. The result of equivalent join of multiple tables is the intersection part of multiple tables.
2. N Table join. At least N-1 join conditions are required.
3. There is no requirement for the order of multiple tables.
4. You generally need to alias tables to improve readability and performance. However, the name of the original table cannot be used to perform operations.
5. It can be used with all of the previously described clauses, such as sort, group, filter.

Case demonstration:

Query the name of the employee and the corresponding department
SELECT last_name, department_name
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;

You can use an alias to query the employee name, job number, job name
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM employees e, jobs j
WHERE e.`job_id` = j.`job_id`;

You can change the order of the table to query the employee name, job number, job name
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM  jobs j, employees e
WHERE  j.`job_id` = e.`job_id`;

-- Can add filter, group, sort
Select * from 'employee' where 'employee' = 'employee'; select * from 'employee' where 'employee' = 'employee'; select * from 'employee' where 'employee' = 'employee' where 'employee' = 'employee'
SELECT j.`job_title`, COUNT(*) Number of employeesFROM employees e, jobs j
WHERE e.`job_id` = j.`job_id`
AND e.`commission_pct` IS NOT NULL 
GROUP BY j.`job_title`
HAVINGThe number of employees> 2
ORDER BYThe number of employeesDESC;

You can query the name of the employee, the name of the department, and the city, and the city starts with o.
SELECT e.`last_name`, d.`department_name`, l.`city`
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` LIKE 'o%';
Copy the code

Note:

Because some fields may be repeated in different tables, MySQL does not know who to refer to and will report an error. That’s why you have aliases.

After the alias is created, you can only use the alias to manipulate the table, and the original table name is not used.

6.3.3 Non-equivalent connections

The concept is introduced that I want to query the fields in other fields that are containing state rather than equal state. For example, the salary in the employee table is queried in the salary scale as follows: For example, the small salary is 10000, and the salary scale from 1000 to 15000 is grade D. Then I need to query for the corresponding level, at which point I can use the non-equivalent join.

Case demonstration:

Query the employee's salary and pay scale
SELECT e.`salary`, g.`grade_level`
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY g.`grade_level` DESC;
Copy the code

6.3.4 since the connection

A self-join is also equivalent to a equivalent join, except that a equivalent join is multiple tables, whereas a self-join is a self-join.

Case presentation

Query the name of the employee and the name of the superior
SELECT e.`last_name`, m.`last_name`
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;
Copy the code

6.4 sql99

Compared with SQL92, only on the original basis of more [JOIN type] JOIN… With the ON keyword, use ON to add the join condition instead of where. The separation of connection condition and filter condition is realized and readability is increased. But also can use external connection, cross connection.

6.4.1 Syntax Format

Up to now, the full join query syntax is available:

Select queries list from table 1 alias "connection type" the join table 2 alias on connection conditions where the filter group by group 】 【 】 【 having filter 】 【 sort order by list]Copy the code

Note:

The type of connection here depends on who you are:
1. The connection:innerKey words,innerYou can leave it out.
2. Outer connection: left outerLeft "outer"Keyword, right outsideRight "outer"Keyword, all outsideFull "outer"The keywordouterYou can leave it out.
3. Cross connection:cross The keyword.

6.4.2 inside connection

The use is similar to the inner join of SQL92 syntax, except that the filter condition is placed after where and the join condition is placed after ON. The inner keyword can be omitted.

The same can be divided into equivalent connection, non-equivalent connection, self – connection.

Actual demonstration: before sql92 syntax use cases to SQL99 syntax format.

-- Equivalent connection
Query the name of the employee and the corresponding department
SELECT last_name, department_name
FROM employees
INNER JOIN departments 
ON  employees.`department_id` = departments.`department_id`;

-- can be aliased, inner can be omitted
-- Query employee name, job number, job name
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM employees e
JOIN jobs j
ON e.`job_id` = j.`job_id`;

You can change the order of the table to query the employee name, job number, job name
SELECT e.`last_name`, e.`job_id`, j.`job_title`
FROM  jobs j
JOIN employees e
ON j.`job_id` = e.`job_id`;

-- Can add filter, group, sort
Select * from 'employee' where 'employee' = 'employee'; select * from 'employee' where 'employee' = 'employee'; select * from 'employee' where 'employee' = 'employee' where 'employee' = 'employee'
SELECT j.`job_title`, COUNT(*) Number of employeesFROM employees e
JOIN jobs j
ON e.`job_id` = j.`job_id`
WHERE e.`commission_pct` IS NOT NULL 
GROUP BY j.`job_title`
HAVINGThe number of employees> 2
ORDER BYThe number of employeesDESC;

You can query the name of the employee, the name of the department, and the city, and the city starts with o.
If two tables do not have a join condition, they cannot be join conditions
SELECT e.`last_name`, d.`department_name`, l.`city`
FROM employees e
JOIN departments d ON e.`department_id` = d.`department_id`
JOIN locations l ON d.`location_id` = l.`location_id`
WHERE l.`city` LIKE 'o%';

-- Non-equivalent connection
Query the employee's salary and pay scale
SELECT e.`salary`, g.`grade_level`
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY g.`grade_level` DESC;

- since the connection
Query the name of the employee and the name of the superior
SELECT e.`last_name`, m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id` = m.`employee_id`;
Copy the code

As you can see, the SQL99 syntax does a better job of figuring out which is the join condition and which is the filter condition.

6.4.3 outer join

Query records that are present in one table but not in another table.

Concept introduction: we in the above query goddess corresponds to the male god information, this result is called the inner join, and the goddess in the male god table can not find the corresponding male god relationship, and we can use the outer join, to query this similar search is not in the male god table goddess name.

Classification:
1. Left outer join: The data that meets the requirements is displayed, and the data that does not meet the requirements in the left table is also displayed, ensuring that all the data in the left table is displayed. The main table is the left table, the secondary table is the right table, use the left 【outer】 keyword.
  
  Simple use:
```
SELECT b. 'name', bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b. 'boyfriend_id' = bo. 'id' ORDER BY bo.Copy the code
```
  As you can see, you get the result of the inner join + the result that does not satisfy the data in the left table. We can use this to get information about goddesses that are not in the table (i.e. those that have NULL values).
2. Outer right join: The data that meets the requirements is displayed, and the data that does not meet the requirements in the right table is also displayed, ensuring that all the data in the left table is displayed. The main table is the right table, and the secondary table is the left table, using the right [outer] keyword.
  
  Simple use:
```
- the right connection
SELECT b.`name`, bo.*
FROM boys bo
RIGHT JOIN beauty b
ON b.`boyfriend_id` = bo.`id`
ORDER BY bo.`id`;
Copy the code
```
  This approach yields the same results as left join. So the two are actually interchangeable.
3. Full external join: The data that meets the requirements is displayed, and the data that the left table does not meet the requirements and the right table does not meet the requirements are also displayed, that is, all display. Use the keyword full [outer]. This approach is not currently available in MySQL. Just so you know, there’s a concept.
Features:
1. All records in the main table are listed as follows:
  
  If there is a match from the table, the matching value is displayed, and if there is no match from the table, NULL is displayed. Can be equivalent to outer join query result = inner join result + records in primary table but not in secondary table.
2. The left is the main table. The main table is on the right.
3. The same effect can be achieved by switching the order of the two tables left – and right-outer.
4. Full outer join = result of inner join + present in Table 1 but not in Table 2 + present in Table 2 but not in Table 1.

Case demonstration:

-- The name of a goddess that is not in the name of a man
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id` IS NULL;
 
Query which department has no employees
SELECT d.*,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
Copy the code

6.4.4 Cross Connection

This corresponds to the Cartesian product phenomenon that occurs in SQL92 syntax.

Let’s go straight to the demo:

SELECT b.* , bo.*
FROM beauty b
CROSS JOIN boys bo;
Copy the code

Advanced 7: subquery

Select statements that appear in other statements are called subqueries or inner queries, while external queries are called primary or outer queries.

7.1 classification

7.2 Simple Usage

How do you use it? The result of a subquery is part of the main query, which means that the subquery gets the result before the main query. Let’s learn how to use it first.

How to use subqueries?
-- Who gets paid more than Abel?
= Abel's salary = Abel's salary
SELECT salary
FROM employees
WHERE last_name = 'Abel';

-- Query the employee information and screen the results whose salary is higher than Abel's.
SELECT * FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);
Copy the code

You can see that the subquery is placed in () and is part of the main query.

7.3 Score by Column and column of the result Set

According to the number of rows and columns obtained by our query to be divided:

Scalar query (row by column)
```
SELECT salary FROM employees WHERE last_name = 'Abel';
Copy the code
```
Commonly used with single-line operators are: <> = >= <= <>! =
Column subquery (multiple rows in one column)
```
SELECT DISTINCT department_id FROM departments
WHERE location_id IN(1400.1700);
Copy the code
```
It is usually used with the multi-line operator: in any all.

Row subquery (one row with multiple columns)

SELECT MIN(employee_id),MAX(salary) FROM employees;
Copy the code

Table subquery (multiple rows and columns)
```
SELECT * FROM employees;
Copy the code
```

7.4 Points by position

7.4.1 After where or having

After where or having, it supports scalar queries, column subqueries, and row subqueries.

Scalar quantum query

-- Scalar quantum query
-- Can implement multiple subqueries
Select * from employee whose job_id is the same as employee 141 and whose salary is more than that of employee 143
-- The first time a more detailed analysis, write out, then directly write out
-- 1. Query the job_id of employee 141
SELECT job_id FROM employees
WHERE employee_id = 141;

-- 2. Query the salary of employee 143
SELECT salary FROM employees
WHERE employee_id = 143;

Select * from employee where job_id=1 and salary>2
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
);

Last_name,job_id, and salary of the employee with the lowest salary in the company
Select (last_name,job_id, salary) from company where salary=1; select (last_name,job_id, salary) where salary=1
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

- after having
Select * from department where minimum wage is greater than 50; select * from department where minimum wage is greater than 50
-- 1. Query the minimum wage in department 50 2. Query the minimum wage in department 50 3. On the basis of 2, min(Salary)>1 is satisfied
SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50

);
Copy the code

Example query

-- column subquery
-- Return all employee names (in) in a department with a location_id of 1400 or 1700
Select * from department where location_id is 1400 or 1700. Query the name of the employee and ask that the department number is one of the 1 list
SELECT last_name FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id 
	FROM departments 
	WHERE location_id IN(1400.1700));Job_id (' IT_PROG '); job_id (' IT_PROG '); salary (any);
Select * from 'IT_PROG' where job_id = 'IT_PROG'; Query the employee number, name, job_id, and any one of salary<(1)
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary FROM employees 
	WHERE job_id = 'IT_PROG'
);

Job_id = 'IT_PROG'; job_id = 'IT_PROG'; salary (all);
The idea is similar to above
SELECT last_name, employee_id, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary FROM employees 
	WHERE job_id = 'IT_PROG'
);
Copy the code

Muck query

It’s not particularly useful.

Query the employee with the smallest number and the highest salary
-- 1. Smallest employee number 2. Highest salary 3
-- Past practices
SELECT * FROM employees
WHERE employee_id = (SELECT MIN(employee_id) FROM employees)
AND salary = (SELECT MAX(salary) FROM employees);
- Row subquery practices
SELECT * FROM employees
WHERE (employee_id, salary)=(
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
);
Copy the code

7.4.2 After select

Only scalar quantum queries are supported

- select the back
-- Query the number of employees in each department
SELECT d.*, (SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id =D. ` department_id `) numberFROM departments d;

Select * from department where employee id =102
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102) Department name;Copy the code

7.4.3 After from

This is equivalent to treating the subquery as a table, but it must be aliased.

- the from behind
Query the average salary for each department
-- 1. Query the average salary for each department
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;
Select * from job_GRADES; select * from lowest_sal; select * from highest_sal
SELECT avg_sa.* , g.`grade_level`
FROM (
	SELECT AVG(salary) sa, department_id
	FROM employees
	GROUP BY department_id
) avg_sa
INNER JOIN job_grades g
ON avg_sa.sa BETWEEN g.`lowest_sal` AND g.`highest_sal`;
Copy the code

7.4.4 Exists (Related subquery)

Grammar:

Exists (complete query statement).

The result is usually a 1 or a 0.

Query the name of the department that has employees
SELECT department_id
FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e
	WHERE e.`department_id` = d.`department_id`
);

-- Query information about goddesses without gods
SELECT b.* 
FROM beauty b
WHERE EXISTS(
	SELECT bo.`id`
	FROM boys bo
	WHERE bo.id = b.`boyfriend_id`
);
Copy the code

Advanced 8: Paging query

When we want to display the data, a page display is not complete, it is necessary to paging submit SQL request. Common application scenarios are, when we brush Taobao, JD.com and other e-commerce sites, look at an item may have hundreds of thousands of data, it can not be a page display completely, this needs paging to achieve. The keyword for paging is LIMIT. ,

8.1 grammar

selectQuery listfromThe table"join type join 表2
onJoin conditionwherefiltergroup byThe grouping fieldhavingFiltering after groupingorder bySelect * from 'limit'offset】 the size;Copy the code

Explain:

The 【】 of which can be written or not written. Offset The start index of the entry to be displayed (the start index starts from 0), size the number of entries to be displayed.

8.2 usage

-- Query the information of the first five employees
SELECT * FROM  employees LIMIT 0.5;
If offset is 0, it can be omitted
SELECT * FROM  employees LIMIT 5;


-- Query item 11 -- Item 15 employee information
SELECT * FROM employees LIMIT 10.5;


-- Information about employees with bonuses, and the top 10 salaries are shown
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
Copy the code

8.3 the characteristics of

The LIMIT statement is placed at the end of the query statement.
Offset, if it’s 0, can be omitted.
Formula: number of pages to display page, number of entries per page size, can be written as

The size limit (page 1) * and size.

Advanced 9: Federated query

Combine the results of multiple query statements into one result. The key word for union: union. The main application scenario is when the query results are from multiple tables that have no direct join relationship but the query information is consistent.

9.1 grammar

The query1
unionThe query2
union.Copy the code

9.2 usage

Query information about employees whose department number is >90 or whose email address contains a
-- Former usage
SELECT * FROM employees
WHERE employee_id > 90 OR email LIKE '%a%';
-- Use federated queries
SELECT * FROM employees WHERE employee_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
Copy the code

9.3 the characteristics of

The number of query columns in multiple query statements must be consistent.
Multiple query statements query columns of almost the same type.
unionIt means to go heavy,union allIt means no weight.

conclusion

With the query here, see the full syntax:

SELECTQuery listFROMTable name [connection type]join 表2
onJoin conditionwherefiltergroup byThe grouping fieldhavingFiltering after groupingorder bySorted field limit [offset】 the size;Copy the code

Of course you can think of this as the order in which a query is executed.

2. The constraint

Some field types currently restrict data, but this restriction is not comprehensive enough. We need to further restrict the data in the table in order to ensure the accuracy and reliability of the data in the table.

2.1 classification

PRIMARY KEY: specifies the PRIMARY KEY constraint

When can I use it? Instead of using the business field as the primary key, you typically design a separate field with an ID for each table, using the ID as the primary key. The primary key is used by the database and the program, not the end customer. So it doesn’t matter if the primary key has meaning, as long as it’s not repeated and it’s not empty.
UNIQUE: Indicates the UNIQUE constraint

The main function is to make the value of the field unique and cannot be repeated.
NOT NULL: indicates a non-null constraint

The main function is to make the value of this field not null.
DEFAULT: Constraints on the DEFAULT value

The main function is to use the default value if the field is not set.
FOREIGN KEY: FOREIGN KEY constraint

When do you need it? Foreign key constraints can be added when a field in a table needs to refer to the primary key of another table. This field is also called the foreign key.

Primary table: The table in which the primary key resides, constrains other tables, and assigns data to other tables. Secondary table/secondary table: table where the foreign key resides, table that is bound, table that uses other people’s data.
CHECK: CHECK constraints. Do not support, understand.

It can also be divided into column level constraints and table level constraints.

Column level constraints: All six constraints are syntactically supported, but foreign key constraints have no effect.
Table level constraints: all but non-null, default constraints are supported.

The syntax can be written like this:

CREATE TABLETable name (field name field type column level constraint, field name field type, table level constraint);Copy the code

2.2 When to Use constraints

When creating a table.
When modifying a table.

2.3 Usage when Creating a Table

To use a foreign key table, create a table:

Create table major
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20));Copy the code

2.3.1 Column level Constraints

Syntax: Append the constraint type to the field name and type.

CREATE TABLETable name (field name, field type, column level constraint, field name, field type, column level constraint,);Copy the code

Example:

-- Column level constraints
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, - the primary key
	stuName VARCHAR(20) NOT NULL.- is not empty
	gender CHAR(1) CHECK(gender='male' OR gender ='woman'), - check
	seat INT UNIQUE.- the only
	age INT DEFAULT  18.-- Default constraint
	majorId INT REFERENCES major(id) - the foreign key
);

You can run the following command to view the table structure and index information
DESC stuinfo;
SHOW INDEX FROM stuinfo;
Copy the code

Features:

You can see that check constraints do not report errors, and foreign key constraints do not report errors, but there is no effect.

2.3.2 Table-level Constraints

Syntax: at the bottom of each field

CREATE TABLETable name (field name, field type, field name, field type,constraintConstraint name constraint type (field name));Copy the code

Example:

-- table level constraints
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT.CONSTRAINT pk PRIMARY KEY(id),- the primary key
	CONSTRAINT uq UNIQUE(seat),- the only key
	CONSTRAINT ck CHECK(gender ='male' OR gender  = 'woman'), - check
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) - the foreign key
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;

-- [CONSTRAINT name] This can be omitted
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT.PRIMARY KEY(id),- the primary key
	UNIQUE(seat),- the only key
	CHECK(gender ='male' OR gender  = 'woman'), - check
	FOREIGN KEY(majorid) REFERENCES major(id) - the foreign key
);
SHOW INDEX FROM stuinfo;
Copy the code

2.3.3 summary

The common way to write this is as follows:

B: Something like this will do
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT.CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
Copy the code

2.4 Usage when Modifying a Table

Against 2.4.1 grammar

-- When modifying a table
-- Add column level constraints
ALTER TABLEThe name of the table the MODIFYCOLUMNNew constraints on field name and field type;-- Add table level constraints
ALTER TABLEThe name of the tableADD 【constraintConstraint name constraint type (field name) [foreign key reference];Copy the code

2.4.2 Adding constraint Usage

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
);
#1.Add non-null constraintsALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2.Adding default ConstraintsALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.Add primary key #① column level constraintALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARYKEY; # 2 Table level constraintsALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.Add unique #① column level constraintALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE; # 2 Table level constraintsALTER TABLE stuinfo ADD UNIQUE(seat);

#5.Add a foreign keyALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

Copy the code

2.4.3 Constraints on the Deletion

Delete constraint when modifying a table
-- Delete non-null constraints
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

-- Delete the default constraint
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

-- Delete primary key
ALTER TABLE stuinfo DROP PRIMARY KEY;

-- Delete unique
ALTER TABLE stuinfo DROP INDEX seat;

Delete the foreign key
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
Copy the code

2.5 Primary keys and unique Differences

Both primary keys and uniqueness guarantee the uniqueness of fields.
Primary keys do not allow fields to be empty, but only can be.
There can be at most one primary key in a table, but there can be more than one unique key.
Both primary keys and unique combinations are allowed.

2.6 Features of foreign Keys

Requires a foreign key relationship to be set in the slave table.
The type of the foreign key column of the secondary table must be consistent or compatible with the type of the associated column of the primary table, but the name is not required.
The associated column of the main table must be akey(Usually primary or unique).
To insert data, the primary table is inserted before the secondary table, and to delete data, the secondary table is deleted before the primary table.

2.7 Foreign key Cascading

How can we update or delete the foreign key of a secondary table while modifying and deleting the primary key of the primary table?

The operation in which we update or delete the foreign key of the secondary table while modifying and deleting the primary key of the primary table is called a cascading operation.

Grammar:

ONAfter the primary key of the primary table is changed, the data in the secondary table is also changed.ON DELETEAfter the primary key is deleted, data from the secondary table is also deleted.Copy the code

The specific use

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT.CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON UPDATE CASCADE ON DELETE CASCADE
);
Copy the code

3. The identity column

Also known as self-growing columns, you do not need to manually insert values; the system provides default sequence values

3.1 grammar

Field Name Indicates the field typePRIMARY KEY AUTO_INCREMENT
Copy the code

Keywords: AUTO_INCREMENT; AUTO_INCREMENT; AUTO_INCREMENT;

3.2 usage

Set the identity column when creating the table
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20));-- Insert 5 pieces of data
INSERT INTO tab_identity (id,NAME) VALUES(NULL.'hello');
SELECT * FROM tab_identity;

Add an identity column when modifying a table
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20));ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

Delete the id column
ALTER TABLE tab_identity MODIFY COLUMN id INT;
Copy the code

3.3 the characteristics of

The identity column must be paired with a key, not necessarily a primary key.

CREATE TABLE tab_identity(
	id INT UNIQUE AUTO_INCREMENT,
	NAME VARCHAR(20));Copy the code

A table can have up to one identity column.

CREATE TABLE tab_identity(
	id INT UNIQUE AUTO_INCREMENT,
	NAME INT UNIQUE AUTO_INCREMENT -- will report an error
);
Copy the code

The type of the identification column must be numeric only.

CREATE TABLE tab_identity(
	id INT,
	NAME VARCHAR(20) UNIQUE AUTO_INCREMENT -- Error will also be reported if the type is incorrect
);
Copy the code

Identifying columns can be done by setting auto_INCREment_INCREMENT = number; You can also manually insert a value to set the start value.

-- Set step 5
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20));-- View the step size
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment=5;
Set the start index to the start position.
INSERT INTO tab_identity (id,NAME) VALUES(100.'hello');
Copy the code

Completion of 4.

Believe you the reader see query, feel a little query will have so many knowledge points, and we in practical applications, the complexity of the query statements or pretty big, so the foundation, not only for the sake of learning, and they can face the interview investigation SQL statements do not panic! And the interview frequently asked in the interview questions have MySQL figure, so MySQL learning is also essential drop!

Let’s just wrap up a little bit:

DQL: Is used to query tables.SELECTQuery listFROMTable name [connection type]join 表2
onJoin conditionwherefiltergroup byThe grouping fieldhavingFiltering after groupingorder bySorted field limit [offset】 the size; Constraints: Column level constraints:CREATE TABLETable name (field name, field type, column level constraint, field name, field type, column level constraint,); Table level constraints:CREATE TABLETable name (field name, field type, field name, field type,constraintConstraint name constraint type (field name));-- Add column level constraints
ALTER TABLEThe name of the table the MODIFYCOLUMNNew constraints on field name and field type;-- Add table level constraints
ALTER TABLEThe name of the tableADD 【constraintConstraint name constraint type (field name) [foreign key reference]; Set identity column: Field name Field typePRIMARY KEY AUTO_INCREMENT

Copy the code

Of course, more sentences command some details, I hope you can learn carefully!

So far, the world is closed for today, good night! Although this article is over, BUT I still, never finished. I will try to keep writing articles. The coming days are long, and the horse is slow!

Thank you for seeing this! May you be young and have no regrets!

Note: If there are any mistakes and suggestions, please leave a message! If this article is also helpful to you, I hope you give a lovely and kind attention, thank you very much!

Super core learning manual series 2 query article – MySQL knowledge points, learning collection essential

1. Learn DQL

Advanced 1. Basic query

1.1 Basic Grammar

1.2 names

1.3 to heavy

1.4 Query Result Calculation

Advanced 2: Query by condition

2.1 Basic Grammar

2.2 Filtering conditional expressions

2.3 Filtering logical expressions

2.4 Fuzzy Query

2.4.1 The like keyword

2.4.2 Between and Keywords

2.4.3 In keywords

2.4.3 IS NULL keyword

Advanced 3: Sort queries

Advanced 4: Common functions

4.1 One-line functions

4.4.1 classification

4.1.2 Character functions

4.1.3 Mathematical functions

4.1.4 Date function

4.1.5 Other functions

4.1.6 Advanced Functions (Control functions)

4.2 Grouping Functions (Aggregate functions)

4.2.1 Five grouping functions

4.2.2 characteristics

Holdings usage

Advanced 5: Group query

5.1 grammar

5.2 Actual operations

5.3 the characteristics of

Advanced 6: Join queries

6.1 classification

6.2 Cartesian product phenomenon

6.2.1 What is cartesian product

6.2.2 Eliminate cartesian product

6.3 the sql92

6.3.1 inside connection

6.3.2 Equivalent connections

6.3.3 Non-equivalent connections

6.3.4 since the connection

6.4 sql99

6.4.1 Syntax Format

6.4.2 inside connection

6.4.3 outer join

6.4.4 Cross Connection

Advanced 7: subquery

7.1 classification

7.2 Simple Usage

7.3 Score by Column and column of the result Set

7.4 Points by position

7.4.1 After where or having

7.4.2 After select

7.4.3 After from

7.4.4 Exists (Related subquery)

Advanced 8: Paging query

8.1 grammar

8.2 usage

8.3 the characteristics of

Advanced 9: Federated query

9.1 grammar

9.2 usage

9.3 the characteristics of

conclusion

2. The constraint

2.1 classification

2.2 When to Use constraints

2.3 Usage when Creating a Table

2.3.1 Column level Constraints

2.3.2 Table-level Constraints

2.3.3 summary

2.4 Usage when Modifying a Table

Against 2.4.1 grammar

2.4.2 Adding constraint Usage

2.4.3 Constraints on the Deletion

2.5 Primary keys and unique Differences

2.6 Features of foreign Keys

2.7 Foreign key Cascading