[the most complete MySQL summary in history] MySQL super detailed notes

Posted by ssmitra on Mon, 27 Dec 2021 04:24:05 +0100

[the most complete MySQL summary in history] MySQL super detailed notes

This article mainly introduces what MySQL is, its classification, SQL commands, MySQL installation precautions, classification and common commands in MySQL, Including: data query (basic query, sort query, condition query, time query, string query, aggregate function, group query, group filter, restricted query, sub query, merge query, table connection query), DML operation (add, delete, modify), library table operation, constraint, transaction, permission management, view, etc;

1. Write in front

We store data (variables, objects, arrays and collections) in Java. The data is stored in memory and belongs to instantaneous state data; There is also the way of file storage, which is stored on the hard disk and belongs to persistent state storage;

Although it can be stored, it will have obvious defects:

The program stops and the data is gone.
Data stored in files: there is no distinction between data types
No access security restrictions
There is no backup and recovery mechanism.

2. Introduction of database

2.1 what is a database?

Database is a warehouse that organizes, stores and manages data according to the data structure. It is an organized, shared and unified data set that can be stored in the computer for a long time

2.2 classification of database

Mesh database: store and access data in the form of nodes
Hierarchical database: IBM[IMS]. Oriented and ordered tree structure for storage and access.
Relational structure database: Oracle, MySQL, DB2 and SQL Server, which are stored in the form of tables. The association relationship between multiple tables is established, and the access is realized through classification, merging, connection and selection.
Non relational databases: MongDB and Redis use hash tables, which implement specific keys and specific data pointed by a pointer in the form of key value
ElastecSearch

3. Database management system

Database management system refers to a large-scale software for operating and managing the database, which is used to establish, use and maintain the database, manage and control the database uniformly, and ensure the security and integrity of the database. Users can access the data in the database through the database management system;

Common database management system

Oracle: it can run on mainstream operating systems such as UNIX and Windows, supports all industrial standards, and has obtained the highest level of ISO standard security certification.
DB2: IBM's to meet the needs of large and medium-sized companies
SQL Server: Microsoft quit.
SQLLite: Mobile Database
Mysql: free, suitable for small and medium-sized enterprises

4,MySQL

MySQL is a relational database management system developed by MySQL AB company in Sweden. It is a product of Oracle. It is one of the most popular relational database management systems and one of the best application software in WEB application.

4.1. Access and download

Official website: https://www.mysql.com

Download address: https//dev.mysql.com/downloads/mysql/

4.2 precautions during installation

No Chinese path, no special symbols or spaces
If it gets stuck during installation, it usually takes more than two or three minutes to uninstall (delete the registry, there is an uninstall program on the control panel, and delete the built-in Mysql in the program data hidden file in the C disk directory), restart the computer and install;
If auto configuration environment variable is not checked, it needs to be added manually

Right click this computer to select Properties and then click environment variables
Create MYSQL_HOME: C:\Program Files\MySQL\MySQL Server 5.7 (MySQL installation path)
Append Path:% MYSQL_HOME%\bin;

4.3. MySQL directory structure

Folder name	content
bin	Command related files
include	Library file
lib	Header file
Share	Character set, language and other information

4.4 MySQL configuration file

Find my. In the MySQL installation directory INI file and some configuration parameters of MySQL

parameter	describe
default-character-set	Client default character set
character-set-server	Server default character set
port	Port numbers of client and server
default-storage-engine	INNODB, the default storage engine for MySQL

5. Introduction to SQL

SQL is a structured query language, which is used to access data, update, query and manage relational database system;

It usually performs "add, delete, modify and query" on the database, which is referred to as C(Create), R(Read), U(Update) and D(Delete)

5.1. View all databases in MySQL

#Connect to MySQL
 SHOW DATABASES; #Displays all databases in the current MySQL

5.2. Create custom database

CREATE DATABASE

CREATE DATABASE dbtemp; #A database named dbtemp was created
CREATE DATABASE dbtemp CHARACTER SET gbk;#Create a database and set its default character set to GBK
CREATE DATABASE dbtemp CHARACTER SET GBK COLLATE gbk_chinese_ci;#Support simplified Chinese and traditional Chinese
CREATE DATABASE IF NOT EXISTS dbtemp;#If dbtemp does not exist, it is created; otherwise, it is not created

5.3. Delete database

DROP DATABASE

DROP DATABASE dbtemp;#Delete database

5.4. View database creation information

SHOW CREATE DATABASE

SHOW CREATE DATABASE dbtemp;# View basic information when creating a database

5.5. Modify database

ALTER DATABASE

ALTER DATABASE dbtemp CHARACTER SET UTF8;#Modify the character set of database dbtemp to utf-8

5.6. Using database

USE

USE dbtemp;#In the current environment, operate the dbtemp database

5.7. View the currently used database

SELECT DATABASE();

SELECT DATABASE();#View the database currently in use

6. Client tools

6.1 Navicat

Navicat is a fast, reliable and cheap database management tool, which is designed to simplify database management and reduce system management cost.

6.2 SQLyog

SQLyog also has a graphical interface. It has a wide range of predefined tools, query and friendly visual interface. Query result editing interface similar to Excel

6.3 DataGrid (idea development tool integration)

DataGrip is a product of Czech company. You need to pay. If you buy idea, DataGrid is universal

7. SQL language classification

DQL (Data Query Language): SELECT, WHERE, ORDER BY, GROUP BY, HAVING
Data definition language (DDL): create, ALTER, DROP
Data manipulation language (DML): INSERT, UPDATE, DELETE
Transaction process language (TPL): commit, ROLLBACK
Data control language (DCL): Grant, REVOKE

8. Data query

8.1 basic structure of data sheet

Relational structure database is stored in tables, which are composed of rows and columns

The result set returned by executing the query statement is a virtual table

8.2. Basic query

Syntax: SELECT column name FROM table name

keyword	describe
SELECT	Specify the columns to query
FROM	Specify the table to query

8.2. 1. Query all columns

#Query t_ All information of all employees in the employees table
SELECT * FROM t_employees;
SELECT All column names FROM t_employees;

In the production environment, column name query is preferred* Although the method looks convenient, it actually needs to be converted into full column names, which is inefficient and poor readability

8.2. 2. Query partial columns

#Number, last name and email address of all employees in the query table
SELECT EMPLOYEE_ID,FIRST_NAME,Email FROM t_employees;
#Number and department number of all employees in the query table
SELECT EMPLOYEE_ID,DEPARTMENT_ID FROM t_employees;

8.2. 3. Operate on the data in the column

#Query the number, name and annual salary of all employees in the employee table
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY * 13 FROM t_employees;

Arithmetic operator	describe
+	Add between columns
-	Subtraction between columns
*	Multiplication between columns
/	Divide between columns

Note:% in the database, it represents a placeholder, not a remainder operator

8.2. 4. Alias of the column

Column AS' column name '

#Query the number, name and daily salary of all employees in the employee table (column operation / 22). The column names are in Chinese
SELECT EMPLOYEE_ID AS 'number',FIRST_NAME AS 'surname',LAST_NAME AS 'name',SALARY / 22 AS'per diem' FROM t_employees;
#Aliasing does not affect the column names of the original table

8.2. 5. De duplication of query results

distinct column name

#Query the ID numbers of all managers in the employee table
SELECT DISTINCT MANAGER_ID AS 'Manager number' FROM t_employees;
#Query all wages in the employee table (remove duplicate)
SELECT DISTINCT SALARY FROM t_employees;

8.3 Sorting Query

Syntax: SELECT column name FROM table name ORDER BY sort column name [collation]

Sorting rules	describe
ASC	Sort in ascending order
DESC	Sort in descending order

8.3. 1. Sort by single column

#Query the number, name and salary of employees, and sort them in ascending order by salary
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees ORDER BY salary ASC;
#Query the number, name and salary of employees, and sort them in ascending order by name
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees ORDER BY FIRST_NAME ASC;
#Query the number, name and salary of employees, and sort them in descending order by salary
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees ORDER BY salary DESC;

When sorting in ascending order, the collation may not display the declaration. The default is ascending collation

8.3. 2. Sort by multiple columns

#Query employee number, name and salary. Sort by salary in ascending order. If the salary is equal, sort by number in descending order
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
ORDER BY SALARY ASC,EMPLOYEE_ID DESC
#Query employee number, name and salary, and sort by salary in ascending order. If the salary is equal, sort by name in descending order
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees
ORDER BY SALARY ASC,FIRST_NAME DESC

8.4 condition query

Syntax: SELECT column name FROM table name WHERE conditions

keyword	describe
WHERE	In query results, filter qualified query results. Condition is Boolean expression

8.4. 1. Equivalence judgment (=)

#Query employee information with salary of 2500
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE salary = 2500;
#Query the last name Steven
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME='Steven';

Note: different from Java (= =), equivalence judgment is used in MySQL=

8.4. 2. Unequal value judgment (>, <, > =, < =,! =, < >)

#Query the information of employees whose salary is greater than 6000
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees 
WHERE salary>=6000;
#Query employee information whose salary is not equal to 2500
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees 
WHERE salary<>2500;(!=Similarly)

8.4. 3. Logical judgment (and, or, not)

#Query employee information with salary of 6000 ~ 10000
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees
WHERE salary>=6000 AND salary<=10000;

#Query employee information with salary of 10000 or 9000
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees
WHERE salary =10000 OR salary = 9000;

#Query employee information with salary of 10000
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees
WHERE NOT salary =10000;

8.4. 4. Between and

#Interval judgment includes two values of interval boundary
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees
WHERE salary BETWEEN 6000 AND 10000;

Note: between and should follow between small value and large value;

8.4. 5. NULL value judgment (IS NULL, IS NOT NULL)

IS NULL

Column name IS NULL

IS NOT NULL

Column name IS NOT NULL

#Query out employees without manager number IS NULL
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NULL;

#No employee information other than manager number is found
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IS NOT NULL;

#No employee information other than the manager number is found (NOT here is negative. Two results)
SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees
WHERE NOT MANAGER_ID IS NULL;

8.4. 6. Enumeration query (IN (value 1, value 2, value n...)

#Enumeration query IN (value 1, value 2, value n...)
#Query employee information with department No. 70,80,90
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM t_employees
WHERE DEPARTMENT_ID IN(70,80,90);

#Enumerate and query employee information with manager numbers 124 and 100
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MANAGER_ID FROM t_employees
WHERE MANAGER_ID IN(124,100);

8.4. 7. Fuzzy query (,%)

LIKE _ (single arbitrary character)
- Column name LIKE'S'
LIKE% (0~n characters of any length)
- Column name LIKE'S% '

#Fuzzy query: query the employee information whose last name starts with S and length is 6
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE FIRST_NAME LIKE 'S_____';


#Fuzzy query, query all employee information of any length with surname beginning with S
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees
WHERE FIRST_NAME LIKE 'S%';

8.4. 8. Branch structure query

CASE
	WHEN Condition 1 THEN Result 1
	WHEN Condition 2 THEN Result 2
	WHEN Condition 3 THEN Result 3
	WHEN Condition 4 THEN Result 4
	ELSE result
END

By using CASE END for condition judgment, a value is generated for each data
Similar to the branch structure in Java

#Query employee information (No., name, salary, salary level < conditional expression >)
SELECT EMPLOYEE_ID,FIRST_NAME,salary,
	CASE 
		WHEN salary >=10000 THEN 'A'
		WHEN salary >=8000 AND salary<10000 THEN 'B'
		WHEN salary >=6000 AND salary<8000 THEN 'C'
		WHEN salary >=4000 AND salary <6000 THEN 'D'
		ELSE 'E'
	END AS 'Salary level'
FROM t_employees;

Note: the case branch structure produces a new column

8.5 time query

Syntax: SELECT Time function ([parameter list]);

When the time function query is executed, a virtual table (one row and one column) will be generated

Time function	describe
SYSDATE()	Current system time (year, month, day, hour, minute, second)
CURDATE()	Get current date
CURTIME()	Get current time
WEEK(DATE)	Gets the week ordinal of the year on which the specified date is obtained
YEAR(DATE)	Gets the year of the specified date
MONTH(DATE)	Gets the month of the specified date
DAY(DATE)	Gets the day of the specified date
HOUR(DATE)	Gets the hour value for the specified time
MINUTE(DATE)	Gets the minute value of the specified time
SECOND(DATE)	Gets the second value of the specified date
DATEDIFF(DATE1,DATE2)	Gets the number of days between DATE1 and DATE2
ADDDATE(DATE,N)	Add N days after the specified date

#1. Current system time
SELECT SYSDATE();
#2. Get the current date
SELECT CURDATE();
#3. Get the current time
SELECT CURTIME();
#4. What week is the specified date in a year
SELECT WEEK(CURDATE());
#5. Get the year in the specified date
SELECT YEAR(CURDATE());
#6. Get the month in the specified date
SELECT MONTH(CURDATE());
#7. Get the day in the specified date
SELECT DAY(CURDATE());
#8. When getting the in the specified date
SELECT HOUR(SYSDATE());
#9. Get the minutes in the specified date
SELECT MINUTE(SYSDATE());
#10. Get the second in the specified date
SELECT SECOND(SYSDATE());
#11. Get the number of days between Date1 and Date2
SELECT DATEDIFF(SYSDATE(),'2019-3-26');
#12. Add N days after the specified date
SELECT ADDDATE(SYSDATE(),6);

8.6. String query

Syntax: SELECT String function ([parameter list]);

Execute string function to generate a virtual table (one row and one column)

String function	explain
CONCAT(str1,str2,str3...)	Concatenate multiple strings
INSERT(str,pos,len,newStr)	Replace the contents of len length from the specified pos position in str with newStr
LOWER(str)	Converts the specified string to lowercase
UPPER(str)	Converts the specified string to uppercase
SUBSTRING(str,pos,len)	Specify the str string to the pos position and start intercepting len contents

#1. Concatenate multiple strings together
SELECT CONCAT('My','S','QL');

#2. Insert substitution (subscript starts from 1)
SELECT INSERT('This is MySQL database',3,5,'Oracle');

#3. Convert to lowercase
SELECT LOWER('MYSQL');

#4. Convert to capital
SELECT UPPER('mysql');

#5. Interception
SELECT SUBSTRING('kak Really handsome! Oh, my God',3,4);

8.7 aggregation function

Syntax: SELECT Aggregate function (column name) FROM table name;

The aggregate function performs statistics on a single column of multiple data and returns a row of results after statistics

Aggregate function	explain
COUNT()	Find the total number of rows
SUM()	Sum all rows in a single column
AVG()	Average all rows in a single column
MAX()	Find the maximum value of all rows in a single column
MIN()	Find the minimum value of all rows in a single column

8.7. 1. Find the total number of rows

#1. Query the total number of employees
SELECT COUNT(EMPLOYEE_ID) AS 'Total number of employees' FROM t_employees;
SELECT COUNT(MANAGER_ID) AS 'Total number of managers' FROM t_employees;
SELECT COUNT(*) FROM t_employees;

Note: in aggregate functions, null values are automatically ignored. No statistics

8.7. 2. Single column sum

#2. Query the total monthly salary of employees
SELECT SUM(salary) FROM t_employees;

8.7. 3. Single column average

#3. Query the average monthly salary of employees
SELECT AVG(salary) FROM t_employees;

8.7. 4. Single column maximum

#4. Query the highest monthly salary
SELECT MAX(salary) FROM t_employees;

8.7. 5. Single column minimum

#5. Query the lowest monthly salary
SELECT MIN(salary) FROM t_employees;

8.8 group query

Syntax: SELECT column name FROM table name WHERE condition GROUP BY (column name)

keyword	explain
GROUP BY	Grouping basis. If there is a WHERE, it will take effect after WHERE

8.8. 1. Query the total number of people in each department

#Idea:
#1. First group by department number (grouping basis: department_id)
#2. Count the number of people in each department
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID)
FROM t_employees
GROUP BY DEPARTMENT_ID;

8.8. 2. Query the average salary of each department

#Idea:
#1. First group by department number (grouping basis: department_id)
#2. Average the wages of each department (AVG())
SELECT DEPARTMENT_ID,AVG(salary) AS 'average wage',COUNT(EMPLOYEE_ID) AS'Number of people'
FROM t_employees
GROUP BY DEPARTMENT_ID;

8.8. 3. Query the number of people in each department and position

#thinking
#1. Group by department number (department_id)
#2. Grouping by position name (job_id)
#3. Make statistics on the number of people in each position in each department
SELECT DEPARTMENT_ID AS'department',JOB_ID AS'post',COUNT(EMPLOYEE_ID) AS'Number of people'
FROM t_employees
GROUP BY DEPARTMENT_ID,JOB_ID;

8.8. 4. Frequently asked questions

#Query the id, total number of people and first of each department_ name
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID),FIRST_NAME
FROM t_employees
GROUP BY DEPARTMENT_ID;

Note: in grouping query, the columns displayed in select can only be the columns based on grouping or aggregation function, and other columns cannot appear.

8.9. Group filtering query

Syntax: SELECT column name FROM table name WHERE condition GROUP BY (column name) HAVING filter rules

keyword	explain
HAVING	The filtering rule is to filter the grouped data

8.9. 1. Maximum wage in the statistical department

#Maximum wage of departments with statistics department numbers of 60, 70 and 80
#Idea:
#1. Determine the grouping basis: department_id
#2. Filter the grouped data. The filtering rule is 60 70 80 department number
#3. The filtered data is grouped and processed by max() function
SELECT DEPARTMENT_ID,MAX(salary)
FROM t_employees
GROUP BY DEPARTMENT_ID
HAVING DEPARTMENT_ID IN (60,70,80);
#HAVING is to filter the data after grouping

8.10. Restricted query

Syntax: SELECT column name FROM table name LIMIT start line, query line

keyword	describe
LIMIT offset_start,row_count	Limit the number of starting rows and total rows of query results

8.10. 1. Query the first 5 lines of records

#Information of the top five employees in the query table
SELECT * FROM t_employees LIMIT 0,5;

Note: the starting line starts from 0 and represents the first line. The second parameter represents the number of rows to query from the specified row

8.10. 2. Query range record

#Second and third page data in the query table
SELECT * FROM t_employees LIMIT 5,5;

SELECT * FROM t_employees LIMIT 10,5;

In the paging application scenario, the starting line changes with the number of pages, but the number of items displayed on a page does not change

8.11 query summary

8.11. 1. SQL statement writing order

SELECT Listing FROM Table name WHERE condition GROUP BY grouping HAVING Filter condition ORDER BY Sort column LIMIT Start line,Total number

8.11. 2. SQL statement execution order

Execute FROM: Specifies the data source table
Execute WHERE: filter the queried data for the first time
Execute GROUP BY: Group
Execute HAVING: filter the grouped data for the second time
Execute SELECT: query the value of each field
Execute ORDER BY: sort
Execute LIMIT: LIMIT query results

8.12 sub query (judged as condition)

Syntax: SELECT column name FROM table name WHERE condition (subquery result)

8.12. 1. Query employee information whose salary is greater than Bruce's

#thinking
#1. First query Bruce's salary (one row and one column)
SELECT SALARY FROM t_employees WHERE first_name = 'Bruce';#6000

#2. Query employee information greater than Bruce's salary
SELECT * FROM t_employees WHERE SALARY > 6000;

#3. Integrate 1 and 2 into one statement
SELECT * FROM t_employees WHERE salary >(SELECT SALARY FROM t_employees WHERE first_name = 'Bruce');

Note: the results of sub query "one row, one column" are used as the criteria of external query. Make a second query
The results obtained by subquery are row by column, which can be used as the equivalence or non equivalence judgment condition of external conditions

8.13. Sub query (as the condition of enumeration query)

Syntax: SELECT column name FROM table name WHERE column name In (subquery result)

8.13. 1. Query employee information of the same department as King

#thinking
#1. Query the department number of King (multiple lines and single column)
SELECT DEPARTMENT_ID FROM t_employees WHERE last_name='King';

#2. Use 80 and 90 as enumeration query conditions
SELECT EMPLOYEE_ID,FIRST_NAME,salary 
FROM t_employees
WHERE DEPARTMENT_ID IN (80,90)

#3. Integration
SELECT EMPLOYEE_ID,FIRST_NAME,salary 
FROM t_employees
WHERE DEPARTMENT_ID
IN 
(SELECT DEPARTMENT_ID FROM t_employees WHERE last_name='King')

The result of "multiple rows and one column" obtained from the sub query is used as the enumeration query condition of the external query for the second query

8.13. 2. Information on all persons in departments with wages higher than 60

#1. Query the salary of department No. 60
SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60;

#2. Query employee information with salary higher than 60 Department owners (higher than owners!)
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees 
WHERE salary > ALL
(SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60);

#3. Query the information of employees whose wages are higher than 60 Department owners (higher than some people!)
SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees 
WHERE salary > ANY
(SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60);

Note: when the sub query result set is multi row and single column, you can also use ALL to match ALL or ANY to match parts

8.14 sub query (as a table)

Syntax: SELECT column name FROM (subquery result set) WHERE conditions;

8.14. 1. Query the information in some columns of the table to obtain the salary greater than 15000

#thinking
#1. First query the information of some columns as a temporary table
 SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees;
 
#2. Use the temporary table obtained from sub query as the table of external query
SELECT EMPLOYEE_ID ,FIRST_NAME ,salary
FROM 
(SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees)AS temp
WHERE salary > 15000;

Experience: use the "multi row and multi column" results obtained from the sub query as a temporary table for external query for the second query

8.15. Consolidated query

Syntax:

SELECT column name FROM table name 1 UNION SELECT column name FROM table name 2
SELECT column name FROM table name 1 UNION ALL SELECT column name FROM table name 2

8.15. 1. Merge the results of two tables (remove duplicate records)

#Merge the results of t1 and t2 tables. Vertical merge to remove duplicate records
SELECT * FROM t1
UNION
SELECT * FROM t2

8.15. 2. Merge the results of two tables (keep duplicate records)

#Merge result sets without removing duplicate records
SELECT * FROM t1
UNION ALL
SELECT * FROM t2

Note: the number of columns of the two merged result sets must be the same, and the column type and column name can be different

8.16. Table connection query

Syntax: SELECT column name FROM table 1 Connection mode Table 2 ON connection condition;

8.16. 1. Inner join on

#Query the information of all employees with departments and display the Department name (excluding employees without departments)
SELECT * FROM t_employees
INNER JOIN t_departments
ON t_employees.`DEPARTMENT_ID` = t_departments.`DEPARTMENT_ID`;

#MYSQL standard
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME FROM 
t_employees,t_departments
 WHERE t_employees.`DEPARTMENT_ID` = t_departments.`DEPARTMENT_ID`;
 
#1. There must be association conditions for the connection query of two tables. However, the columns of association conditions are repeated. You need to specify which table column to query
#2. The table name is relatively long and appears repeatedly. Easy to confuse You can give aliases
SELECT EMPLOYEE_ID,FIRST_NAME,d.DEPARTMENT_ID,DEPARTMENT_NAME FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`;

In MySQL, you can use the second method, which does not comply with the SQL standard
The first belongs to the SQL standard, which is common with other relational databases

8.16. 2. Internal connection query

#Query the employee information of all positions and display the position name
SELECT EMPLOYEE_ID,FIRST_NAME,JOB_TITLE
FROM t_employees AS e
INNER JOIN t_jobs AS j
ON e.`JOB_ID` = j.`JOB_ID`;

8.16. 3. Three table connection query

#Query all employee job numbers, names, Department names, and the name of the city where the Department is located
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`
INNER JOIN t_locations AS l
ON d.`LOCATION_ID` = l.`LOCATION_ID`;

8.16. 4. Multi table join query

#Query the job number, name, department name, Department city name and country of the city of all employees
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY,COUNTRY_NAME
FROM t_employees AS e
INNER JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`
INNER JOIN t_locations AS l
ON d.`LOCATION_ID` = l.`LOCATION_ID`
INNER JOIN t_countries AS c
ON l.`COUNTRY_ID` = c.`COUNTRY_ID`;

Experience: when querying multiple tables, you should know which table is related to the connected table.

8.16. 5. Left join on

#Query all employee information and corresponding department names (employees without departments are also included in the query results, but the Department names are filled in with NULL)
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME 
FROM t_employees AS e
LEFT JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`;

Note: the left outer connection takes the left table as the main table, matches the right table in turn, and returns the correct result if it is matched
If no match is found, NULL value will be returned to fill in the display

8.16. 6. Right join on

#Query all department information and employee information in the Department
#(departments without employees are also in the query results, and the employee information is filled in with NULL)
SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME 
FROM t_employees AS e
RIGHT JOIN t_departments AS d
ON e.`DEPARTMENT_ID` = d.`DEPARTMENT_ID`;

Note: the right outer connection takes the right table as the main table, matches to the left in turn, matches to, and returns the correct result
If no match is found, NULL padding is returned

9. DML operation (add, delete, modify)

9.1. Add (INSERT)

INSERT INTO Table name (column 1, column 2, column 3...) values (value 1, value 2, value 3...)

#Add an employee information
INSERT INTO t_employees
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES('209','Ya','Suo','123@happy.com','515.123.6666','2010-03-18','Center',900,NULL,'123','50')

#Add multiple lines, append outside the value list, and then write a value list
,('208','Ya','Suo','YaSuo@happy.com','515.123.6666','2010-03-18','Center',900,NULL,'123','50');

#Add a city message
INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME)VALUES('AL','Albania');
SELECT * FROM t_departments;

#Add a department information
INSERT INTO t_departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
VALUES('280','Teach','111','1500')

Note: the column name list after the table name and the value list in VALUES should correspond one by one (number, order and type)

9.2. Modify (UPDATE)

UPDATE Table name SET column name 1 = new value 1, column name 2 = new value 2... WHERE condition

#Change the name of the employee with employee No. 208 to TOM Jackson
UPDATE t_employees SET FIRST_NAME='TOM', LAST_NAME = 'Jackson' WHERE EMPLOYEE_ID = '208';

Note: SET is followed by multiple columns = values. In most cases, the WHERE condition must be added to specify the target of modification, otherwise the whole table is updated

9.3 deletion

DELETE FROM table name WHERE condition

#Delete an employee with No. 207
DELETE FROM t_employees WHERE EMPLOYEE_ID = '207'

Note: when deleting, if the WHERE condition is not added, the data of the whole table will be deleted. Structure unchanged

9.4. Empty (TRUNCATE)

Empty the entire table: TRUNCATE TABLE table name;

#Empty t2 entire table
TRUNCATE TABLE t2;

Note: TRUNCATE and DELETE do not add WHERE. Deleting the whole table data is different:

DELETE only deletes the data, and the structure remains unchanged
TRUNCATE is to destroy the whole table, and then create a new table according to the format and structure of the original table

10. Library table operation

10.1. Database creation

CREATE DATABASE library name

#Create a database of default character sets
CREATE DATABASE MYDB1;

#Creates a database for the specified character set
CREATE DATABASE MYDB1 CHARACTER SET UTF8;

10.2. Modify database

ALTER DATABASE library name operation

10.2. 1. Modify the character set of the database

#Modify the character set of mydb1 to gbk
ALTER DATABASE MYDB1 CHARACTER SET GBK;

10.3 delete database

Delete database: DROP DATABASE library name

#Delete mydb1 database
DROP DATABASE MYDB1;

10.4 data type

MySQL can be roughly divided into three types: numeric, date / time, and string (character) types. It is very helpful for us to create tables and constrain the type of columns;

10.4. 1. Value type

type	size	Range (signed)	Range (unsigned)	purpose
INT	4 bytes	(-2147483648,2147483647)	(0,4294967295)	Integer value
DOUBLE	8 bytes	(-1.797E+308,-2.22E-308)	(0,2.22E-308,1.797E+308)	Double precision floating point value
DOUBLE(M,D)	8 bytes, M represents length and D represents decimal places	Ibid., subject to M and D. DOUBLE(5,2)-999.99-999.99	Ibid., subject to M and D	Double precision floating point value
DECIMAL(M,D)	Save exact values	Rely on M and D.	Dependent on M and D	Small value

10.4. 2. Date type

type	size	Range	format	purpose
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	Date value
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	Time value
YEAR	1	1901/2155	YYYY	Annual score
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM–DD HH:MM:SS	Mixed date time value

10.4. 3. String type

type	size	purpose
CHAR	0-255 characters	Fixed length string CHAR(10) 10 characters
VARCHAR	0-65535	Variable length string VARCHAR(10) 10 characters
BLOB(binary large object)	0-65535	Long text data in binary form
TEXT	0-65535	Long text

10.5 creation of data table

CREATE TABLE table name(

Column name data type [constraint],

...

Column name data type [constraint] //There is no need to add a comma at the end of the creation of the last column

)[charset=utf8] ; // Specify the character encoding set of the table as needed

#Create chart of accounts
#Account number, account name and account hours
#Subject
CREATE TABLE `Subject`(
	subjectId INT,
	subjectName VARCHAR(20),
	subjectHours INT
)CHARSET=utf8;

INSERT INTO `subject`(subjectid,subjectname,subjecthours)
VALUES(1,'Java',10);
INSERT INTO `subject`(subjectid,subjectname,subjecthours)
VALUES(2,'HTML5',20);
INSERT INTO `subject`(subjectid,subjectname,subjecthours)
VALUES(3,'BIGDATA',5);

11. Restraint

11.1 entity integrity constraints

A row of data in the table represents an entity. The entity integrity constraint is to identify that each row of data is not duplicate and the entity is unique.

11.1. 1. Primary key constraint

PRIMARY KEY It uniquely identifies a row of data in the table. The value of this column cannot be repeated and cannot be NULL

#Create a table, select a column suitable for primary key, and add a primary key constraint
CREATE TABLE Student(
	stuid INT PRIMARY KEY,#The number identifying each student is unique and cannot be NULL
	stuName VARCHAR(20),
	phone VARCHAR(11)
)CHARSET=utf8;

11.1. 2. Unique constraint

UNIQUE It is unique and identifies a row of data in the table. It cannot be repeated and can be NULL

#Add a unique constraint to the mobile phone number column in the table! Cannot be repeated, but can be NULL
CREATE TABLE Student(
	stuid INT PRIMARY KEY,#The number identifying each student is unique and cannot be NULL
	stuName VARCHAR(20),
	phone VARCHAR(11) UNIQUE
)CHARSET=utf8;

11.1. 3. Auto growth column

AUTO_INCREMENT Auto growth: add auto growth to the primary key numeric column. Start with 1 and add 1 each time. It cannot be used alone. It can be used with a primary key

#Add automatic growth for the primary key column in the table to avoid ID duplication and easy to forget
CREATE TABLE Student(
	stuid INT PRIMARY KEY AUTO_INCREMENT,#Start with 1 and add + 1 according to the order of adding data
	stuName VARCHAR(20),
	phone VARCHAR(11) UNIQUE
)CHARSET=utf8;

11.2 domain integrity constraints

Limit the data correctness of each cell of the column

11.2. 1. Non NULL constraint

NOT NULL Non empty, NULL is not allowed for each cell constraining this column

#Constraint columns with NOT NULL must have values
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	empName VARCHAR(20)NOT NULL,#The constraint name column must have a value
	address VARCHAR(50) NOT NULL
)CHARSET=utf8;
INSERT INTO emp(empName,address) VALUES(null,'Haidian District, Beijing');#error, the course name must have a value

11.2. 2. Default value constraint

DEFAULT Give DEFAULT values to columns. When no value is specified for new data, you can write DEFAULT to fill in with the defined DEFAULT values

#DEFAULT value constraint. If no value is specified, fill in DEFAULT, which is the DEFAULT value.
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	empName VARCHAR(20)NOT NULL,#The constraint name column must have a value
	address VARCHAR(50) NOT NULL,
	sex CHAR(1) DEFAULT 'female'
)CHARSET=utf8;

11.2. 3. Referential integrity constraints

Syntax: CONSTRAINT reference name FOREIGN KEY REFERENCES referenced table name (column name)

FOREIGN KEY refers to the value of a 5 column in the external table. When adding data, the value of this column must be the value existing in the referenced table

#Professional table
CREATE TABLE Speciality(
	id INT PRIMARY KEY AUTO_INCREMENT,
	SpecialName VARCHAR(20) UNIQUE NOT NULL#Unique and cannot be empty
)CHARSET=utf8;
#Class Schedule Card
CREATE TABLE `subject`(
	subjectid INT PRIMARY KEY AUTO_INCREMENT,
	subjecname VARCHAR(20) UNIQUE NOT NULL,
	subjecthours INT DEFAULT 20,
	specialid INT NOT NULL,
	CONSTRAINT fk_subject_specialid
	 FOREIGN KEY(specialid)
	  REFERENCES Speciality(id)
)CHARSET=utf8;
SELECT * FROM SUBJECT;

#Table with reference relationship. First add the referenced table data (primary key table) Then add the data of the reference table (foreign key table)
INSERT INTO Speciality (SpecialName) VALUES('Java');
INSERT INTO Speciality (SpecialName) VALUES('HTML5');

INSERT INTO `subject`(subjecname,subjecthours,specialid)
VALUES('JavaSE',10,1);
INSERT INTO `subject`(subjecname,subjecthours,specialid)
VALUES('JavaScript',20,2);
INSERT INTO `subject`(subjecname,subjecthours,specialid)
VALUES('BIGDATA',20,3);#error constraint: the primary key table does not exist 3 Therefore, the foreign key table cannot be inserted into 3

Note: when there is a reference relationship between two tables, you should pay attention to deleting the secondary table (reference table and foreign key table) first, and then the primary table (referenced table and primary key table)

11.3 constraint creation and integration

Create a table with constraints

11.3. 1. Create Grade table

Listing	data type	constraint	explain
GradeId	INT	Primary key, auto growth	Class number
GradeName	VARCHAR(20)	Unique, non empty	Class name

#Create Grade table
CREATE TABLE Grade(
	GradeId INT PRIMARY KEY AUTO_INCREMENT,
	GradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

SELECT * FROM grade;
INSERT INTO Grade(GradeName) VALUES('001');
INSERT INTO Grade(GradeName) VALUES('002');
INSERT INTO Grade(GradeName) VALUES('003');

11.3. 2. Create Student table

Listing	data type	constraint	explain
student_id	VARCHAR(50)	Primary key	Student number
student_name	VARCHAR(50)	Non empty	full name
sex	CHAR(2)	Default value. male	Gender
borndate	DATE	Non empty	birthday
phone	VARCHAR(11)	nothing	Telephone
GradeId	INT	Non empty, foreign key constraint: refers to the GradeId of the class table	Class number

#Create Student table
CREATE TABLE Student(
	student_id VARCHAR(50) PRIMARY KEY,
	student_name VARCHAR(50) NOT NULL,
	sex CHAR(2) DEFAULT 'male',
	borndate DATE NOT NULL,
	phone VARCHAR(11),
	GradeId INT NOT NULL,
	CONSTRAINT fk_student_gradeId FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)
)CHARSET=utf8;

SELECT * FROM student;
INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES('S1001','kak1',DEFAULT,'2001-06-01',NULL,2);
INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)
VALUES('S1002','kak2',DEFAULT,'1999-06-01',NULL,3);

Note: when creating related tables, you must first create the primary table (primary key) and then the secondary table (foreign key table)

11.4 modification of data sheet (ALTER)

Syntax: ALTER TABLE name modification;

11.4. 1. Add columns to an existing table

#Add columns to an existing table
ALTER TABLE Student ADD image BLOB;
#ADD new column name data type [constraint]

11.4. 2. Modifying columns in a table

ALTER TABLE student MODIFY phone VARCHAR(14) NOT NULL

Note: when modifying a column in a table, you need to write the name, data type and constraint of all columns

11.4. 3. Delete columns in table

ALTER TABLE student DROP image;

11.4. 4. Change column name

ALTER TABLE student CHANGE borndate birthday DATE NOT NULL;

Note: when changing the column name, specify the data type and constraint of the column while giving the new column name

11.4. 5. Modify table name

ALTER TABLE student RENAME stu;

11.5. Delete table (DROP)

Delete student table: DROP TABLE table name

DROP TABLE stu

12. Business

12.1 simulated transfer

In life, transfer is the money deducted by the transferor and the account price of the payee. Use database operation to simulate real transfer.

12.1. 1. Simulated account transfer

#Transfer 1000 yuan from 1 account to 2 account
#1. Account deduction
UPDATE account SET money = money - 1000 WHERE id = 1;

#2. Add money to the account
UPDATE account SET money = money + 1000 WHERE id = 2;

12.1. 2. Simulated transfer error

#Transfer 1000 yuan from 1 account to 2 account
#1. Account deduction
UPDATE account SET money = money - 1000 WHERE id = 1;
#Power failure, abnormality and error

#2. Add money to the account
UPDATE account SET money = money + 1000 WHERE id = 2;

If there is an exception or statement error in the above code during the money reduction operation, it will be found that the money reduction is still successful and the money increase fails
Each SQL statement is an independent operation! Any operation has a permanent impact on the database

12.2 concept of transaction

A transaction is an atomic operation. Is a minimum execution unit. It can be composed of one or more SQL statements. In the same transaction, when all SQL statements are successfully executed, the whole transaction is successful! One SQL statement failed to execute, and the whole transaction failed to execute!

12.3 boundary of affairs

Start: connect to the database and execute a DML statement. After the last transaction, another DML statement is entered, that is, the beginning of the transaction

end:

Submission:
- Show submission: COMMIT;
- Implicit commit: a DML statement. Normal exit (client exit link)
Rollback:
- Show rollback: ROLLBACK;
- Implicit rollback: abnormal exit (power failure, crash), execute the create and delete statements, but fail! Rollback will be performed for this invalid SQL statement.

12.4 principle of transaction

The database will maintain a spatially independent cache (rollback segment) for each client. The execution results of all add, delete and change statements in a transaction will be cached in the rollback segment. Only when all SQL statements in the transaction end normally (COMMIT), the data in the rollback segment will be synchronized to the database. Otherwise, if it fails for any reason, the whole transaction will be rolled back;

12.5 characteristics of transactions

Atomicity : indicates that all operations within a transaction are a whole, either all successful or all failed.
Consistency : indicates that when an operation fails in a transaction, all changed data must be rolled back to the state before modification.
Isolation Transaction: view the state of data during data operation, either before another concurrent transaction modifies the data or after another concurrent transaction modifies it. Transactions do not view data in the intermediate state
Durability : after the transaction is completed, the impact on the database is permanent.

12.6. Transaction application

Application environment: Based on the operation results of addition, deletion and modification statements (all return the number of rows affected after the operation), the transaction submission or rollback can be manually controlled through program logic

12.6. 1. Transaction completion transfer

#Open transaction
START TRANSACTION;#SET autoCommit = 0;#Method 2: set auto submission as 0, turn off auto submission | 1, turn on auto submission
#1. Account deduction
UPDATE account SET money = money - 1000 WHERE id = 1;

#2. Add money to the account
UPDATE account SET money = money + 1000 WHERE id = 2;
#Execute submit - successful
COMMIT;
#Execute rollback --- failed
ROLLBACK;

Note: after a transaction is started, all statements executed in the current transaction belong to the current transaction. COMMIT is executed successfully, and ROLLBACK is required for failure

13. Authority management

13.1. Create user

CREATE a user: CREATE USER username IDENTIFIED BY password

#Create user
CREATE USER 'kak' IDENTIFIED BY '123';

13.2 authorization

User authorization: GRANT ALL ON database Table name TO user name;

#Authorize the grade table in the companydb data to zhangsan
GRANT ALL ON companydb.`grade` TO 'kak';
#Authorize all tables in the companydb database to zhangsan
GRANT ALL ON companydb.* TO 'kak';

13.3 revocation of authority

Revoke user rights: REVOKE ALL ON database Table name FROM user name

REVOKE ALL ON companydb.grade FROM 'kak';

13.4. Delete user

Delete user: DROP USER user name;

DROP USER 'kak';

14. View

The view is also called virtual table. The result table queried from one or more tables plays the same role as the real table and contains a series of data with rows and columns. In the view, you can use the SELECT statement to query data, and you can also use INSERT, UPDATE and DELETE to modify records. The view can facilitate users' operation and ensure the security of the database system;

14.1 view features

advantage

Simplification, data WYSIWYG
Security, you can only query or modify the data that can be seen by the lock in the view
Logical independence can shield the impact of real table structure changes.

shortcoming

The performance is relatively poor, and simple queries are slightly more complex
It is inconvenient to modify. When the data of the view is a complex aggregate view, it cannot be modified.

14.2. View creation

Syntax: CREATE VIEW view name AS Statement for querying data source table;

14.2. 1. Create view

#Create a t_empinfo view. The data of this view is employee name, email address and mobile phone number
CREATE VIEW t_empinfo
AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;

14.2. 2. Use view

#Use view
#Query, WYSIWYG
SELECT * FROM t_empinfo WHERE FIRST_NAME='Steven' AND LAST_NAME='King';
#Modification. Can only be modified
UPDATE t_empinfo SET email = 'Kings' WHERE FIRST_NAME='Steven' AND LAST_NAME='King';

14.3 modification of view

14.3. 1. Modify view_ Mode 1

Replace the data if it exists, and create a new one if it does not exist: CREATE OR REPLACE VIEW view name AS Query the statement of the source table;

CREATE OR REPLACE VIEW t_empinfo AS
SELECT employee_id,FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;

14.3. 2. Modify view_ Mode II

ALTER VIEW view name AS Query the statement of the source table;

ALTER VIEW t_empinfo AS
SELECT FIRST_NAME,LAST_NAME,email,PHONE_NUMBER FROM t_employees;

14.5 deletion of view

DROP VIEW View name

14.5. 1 delete view

DROP VIEW t_empinfo;

Note: deleting the view will not affect the data of the original table

14.6 precautions for view

The view will not store data independently, the original table will change, and the data of the view will also change. Query performance was not optimized

A view is not updatable if it contains one of the following structures:

Result of aggregate function
GROUP BY results after grouping
HAVING filtered results
UNION, UNION ALL combined results

I've seen it here. If you have any questions, you're welcome to criticize and correct them in the comment area!

Topics: Database MySQL

Programmer Think

[the most complete MySQL summary in history] MySQL super detailed notes

[the most complete MySQL summary in history] MySQL super detailed notes

1. Write in front

2. Introduction of database

2.1 what is a database?

2.2 classification of database

3. Database management system

Common database management system

4,MySQL

4.1. Access and download

4.2 precautions during installation

4.3. MySQL directory structure

4.4 MySQL configuration file

5. Introduction to SQL

5.1. View all databases in MySQL

5.2. Create custom database

5.3. Delete database

5.4. View database creation information

5.5. Modify database

5.6. Using database

5.7. View the currently used database

6. Client tools

6.1 Navicat

6.2 SQLyog

6.3 DataGrid (idea development tool integration)

7. SQL language classification

8. Data query

8.1 basic structure of data sheet

8.2. Basic query

8.2. 1. Query all columns

8.2. 2. Query partial columns

8.2. 3. Operate on the data in the column

8.2. 4. Alias of the column

8.2. 5. De duplication of query results

8.3 Sorting Query

8.3. 1. Sort by single column

8.3. 2. Sort by multiple columns

8.4 condition query

8.4. 1. Equivalence judgment (=)

8.4. 2. Unequal value judgment (>, <, > =, < =,! =, < >)

8.4. 3. Logical judgment (and, or, not)

8.4. 4. Between and

8.4. 5. NULL value judgment (IS NULL, IS NOT NULL)

8.4. 6. Enumeration query (IN (value 1, value 2, value n...)

8.4. 7. Fuzzy query (,%)

8.4. 8. Branch structure query

8.5 time query

8.6. String query

8.7 aggregation function

8.7. 1. Find the total number of rows

8.7. 2. Single column sum

8.7. 3. Single column average

8.7. 4. Single column maximum

8.7. 5. Single column minimum

8.8 group query

8.8. 1. Query the total number of people in each department

8.8. 2. Query the average salary of each department

8.8. 3. Query the number of people in each department and position

8.8. 4. Frequently asked questions

8.9. Group filtering query

8.9. 1. Maximum wage in the statistical department

8.10. Restricted query

8.10. 1. Query the first 5 lines of records

8.10. 2. Query range record

8.11 query summary

8.11. 1. SQL statement writing order

8.11. 2. SQL statement execution order

8.12 sub query (judged as condition)

8.12. 1. Query employee information whose salary is greater than Bruce's

8.13. Sub query (as the condition of enumeration query)

8.13. 1. Query employee information of the same department as King

8.13. 2. Information on all persons in departments with wages higher than 60

8.14 sub query (as a table)

8.14. 1. Query the information in some columns of the table to obtain the salary greater than 15000

8.15. Consolidated query

8.15. 1. Merge the results of two tables (remove duplicate records)

8.15. 2. Merge the results of two tables (keep duplicate records)

8.16. Table connection query

8.16. 1. Inner join on