“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!”

Introduction to SQL

1.1 overview of SQL

People need language to communicate with people, and people need language to communicate with databases, and the language specifically designed for programmers to interact with databases is SQL.

SQL: A Structured Query Language. Is a relational database standard language. Features: Simple, flexible, powerful.

1.2. SQL contains six parts

Data Query Language (DQL)

Its statement, also known as a “data retrieval statement,” is used to retrieve data from the table and determine how the data is presented in the application. The reserved word SELECT is the most commonly used verb in DQL (and all SQL). Other common reserved words in DQL are WHERE, ORDER BY, GROUP BY, and HAVING. These DQL reserved words are often used in conjunction with other types of SQL statements.

1.2.2 Data Manipulation Language (DML)

Its statements include the verbs INSERT, UPDATE, and DELETE. They are used to add, modify, and delete rows in a table, respectively. Also known as action language.

1.2.3 Data Definition Language (DDL)

Its statements include the verbs CREATE and DROP. Create a new TABLE or DROP a TABLE in the database (CREAT TABLE or DROP TABLE); Adding indexes to tables, etc. The DDL includes a number of reserved words related to data obtained from the human database catalog. It is also part of the action query.

1.2.4 Transaction Processing Language (TPL)

Its statement ensures that all rows of the table affected by the DML statement are updated in a timely manner. TPL statements include BEGIN TRANSACTION, COMMIT, and ROLLBACK.

1.2.5 Data Control Language (DCL)

Its statements GRANT or REVOKE permissions to determine access to database objects by individual users and groups of users. Some RDBMSS use grants or revoks to control access to individual columns of a form.

1.2.6 Pointer Control Language (CCL)

Its statements, such as DECLARE CURSOR, FETCH INTO, and UPDATE WHERE CURRENT, are used for individual operations on one or more forms.

1.3. Writing rules

In the database, SQL statements are case insensitive. For example, select, select., select. In order to improve readability, use upper case for keywords, and lower case for others.
SQL statements can be written in one or more lines, and semicolons are used to identify the end.
Use spacing and indentation to make the program readable

Table and ORM

2.1, table,

A two-dimensional table is a collection of attributes of the same kind of entities. Each entity corresponds to a row in the table, which is called a tuple in relation, and is equivalent to a common record. A column in a table represents an attribute, called a Field, which corresponds to a data item in a normal record, also called a column or Field. Row: represents an entity, a record column: field, data item.

2.2 Table and Object Relationships (ORM)

ORM: Oject Reraltional Mapping: Mapping the object table

In development, we need to query the data in the table and save it to memory, or save the data in memory to the database, at this time, we need to map the data in the data table and the objects in Java. This mapping association is known as the ORM idea.

MySQL service

3.1 MySQL Service

Before opening a database connection: make sure the MySQL service is enabled.

The net start command starts a service, such as net start MySQL.

The net stop command is used to shut down a server, for example, net stop MySQL

Connect to MySQL

Methods a

Way 2

Enter the command in Run (Win + R goes to CMD).

Format: mysql -u account -p Password -h Host -p database port on the database server

Mysql -uroot -padmin -h127.0.0.1 -p3306Copy the code

If the connected database server is on the local server and the port is 3306. Mysql -uroot -padmin

Iv. Database foundation

4.1 Basic Database Operations

Check the databases on the database server.SHOW DATABASES.
Use the specified database.USE database_name.
To view which tables are in the specified database:SHOW TABLES.
Creates a database with the specified name.CREATE DATABASE database_name.
Delete database:DROP DATABASE database_name.

4.2 Storage Engine

Data in MySQL is stored in files (or in memory) using various techniques. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides different capabilities and capabilities. By choosing different technologies, you can gain extra speed or functionality that improves the overall functionality of your app.

Simply put, a storage engine is how tables are stored.

MySQL > select * from ‘MySQL’ where ‘MySQL’ is stored

MyISAM: Has high insert, query speed, but does not support transactions, does not support foreign keys.
InnoDB: Supports transactions, supports foreign keys, supports row-level locking, low performance. The most secure

InnoDB storage engine provides transaction security with commit, rollback and crash recovery capabilities. However, compared to MyISAM, it is less efficient and takes up more disk space to preserve data and indexes. A system, especially the financial system, no transaction is very terrible things, generally choose InnDB.

MySQL column types

5.1 the most Common types

MsSQL	Java
INT	int
BIGINT	long
DECIMAL	BigDecimal
DATE/DATETIME	java.util.Date
VARCHAR	String

5.2. The type is an integer

Integer types have width indicators that specify the bit width.

For example, if the type of a field is INT(3) and there are less than three values, it can be automatically filled with 0 when retrieved from the database.

The width indicator does not affect the list value range. Generally, the bit width is not specified. .png)]

5.3. Decimal type

FLOAT[(s,p)] or DOUBLE[(s,p)]: decimal type, can store real and integer types, precision (p) and range (s).

DECIMAL: A high-precision type, with the amount currency preferred.

5.4. Character type

Char (size) : a fixed-length character, ranging from 0 to 255 bytes. Size indicates the number of N characters. If the number of inserted characters exceeds the specified length, the system will intercept and warn you.
Varchar (size) : variable length character (0-255 bytes). 65535 bytes are supported from MySQL5. If the number of inserted characters exceeds the specified length, it will be intercepted and warned in non-strict mode.

Generally, a large number of strings are stored, such as the plain TEXT of an article. You can choose the TEXT series type, which is variously long.

Note: In MySQL, character types must be specified in length and values must be enclosed in single quotes. Equivalent in the Java character (String and StringBuffer/StringBuilder);

5.5 Date type

Common DATE and time types are DATE and DATETIME.

Note: In MySQL, date-time values are enclosed in single quotes. Date, Calender

5.6 binary type

Binary type is mainly used to store graphics, sound and video, binary object, 0-4GB.

In development, we generally store binary file save path, so the above type will not be used without special requirements.

BIT, which generally stores 0 or 1, stores values of type Boolean /Boolean in Java (as needed).

Vi.Table operations (DDL)

Table operations mainly use DDL to create tables, delete tables, and so on

Create table

6.1.1, grammar,

CREATE TABLETable name (column name1Type of column [constraint], column name2Column types [constraints],.... Column name N column type constraint);-- Note: there is no comma in the last line
Copy the code

6.1.2, examples,

Create table t_student with id, name, email, age.

CREATE TABLE t_student (
	id BIGINT,
	name VARCHAR(15),
	email VARCHAR(25),
	age INT
);
Copy the code

Also 6.1.3, pay attention to

When creating a table, you cannot use the MySQL keyword or reserved word.

The solution:

# 1. Avoid using keywords as much as possible. Use other words or word combinations instead.
# 2. In general, it is customary to use T when creating tables_ starts the table name. # 3. Use backquotes (' ') to enclose the table name in ok (' order ').Copy the code

Delete table

6.2.1, grammar,

DROP TABLEThe name of the table.Copy the code

6.2.2, examples,

Delete the order table
DROP TABLE `order`;
Copy the code

6.2.3, pay attention to

If the table name is a database keyword or reserved word, backquote (‘) is required.

6.3 Table replication and batch insert

6.3.1 Table replication

Table replication essentially creates the query results as if they were tables.

create tableThe name of the tableas selectStatements;Copy the code

6.3.2 Batch insert of tables

Batch inserts of tables are essentially inserts of query results into another table.

insert into dept1 select * from dept;
Copy the code

6.5 Constraints on the table

Constraints are used to ensure the validity, validity, and integrity of the data in a table.

Non-null constraint: NOT NULL, which does NOT allow the contents of a column to be NULL.
Set the DEFAULT value for the column: DEFAULT.
UNIQUE constraint: UNIQUE. In this table, the contents of this column must be UNIQUE.
PRIMARY KEY constraint: PRIMARY KEY, which is non-empty and unique.
Primary key increment: AUTO_INCREMENT, starting from 1, step 1.
FOREIGN KEY constraint: FOREIGN KEY, FOREIGN KEY column in table A. The value of the foreign key column in table A must refer to A column in table B (the primary key of table B).

6.5.1 Primary Key Constraints

The primary key is the unique identifier of the row in the table, just like an ID number. A table can have only one primary key constraint.

The primary key constraint must be unique and cannot be NULL.

6.5.1.1 Classification of primary keys

Service primary key: Use the column with service meaning as the primary key (not recommended).
Natural primary key: Use columns with no business meaning as primary keys (recommended);

6.5.1.2 How to design the primary key

For primary keys, there are two primary key design principles:

Single-field primary key and single-column primary key are recommended.
Compound primary keys, using multiple columns as primary keys, not recommended.

Conclusion 6.5.1.3,

Use the natural primary key of a single field.

6.5.1.4, examples,

Select * from student where id is the primary key, name is unique, email is not empty, age is 18 by default.

Remove existing tables
DROP TABLE IF EXISTS `t_student`;
	CREATE TABLE t_student(
	id BIGINT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(25) UNIQUE,
	email VARCHAR(25) NOT NULL,
	age INT DEFAULT 18
);
Copy the code

6.5.2 Foreign Key Constraints

** The foreign key is the primary key of another table. ** For example, there is an association relationship between the employee table and the department table. The department number field in the employee table is a foreign key, which is relative to the foreign key of the department table.

The foreign key can be NULL and may not necessarily be the primary key of another table, but it must be unique, usually referencing the primary key of another table.

create table t_student(
	sno it,
    sname varchar(255),
    classno ,int
    foreign key (classno) references t_class(no) Add a foreign key constraint to the classno field of T_student, referring to the no field of T_calss
);
Copy the code

6.5.3. Unique Constraint

A field decorated by a unique constraint is unique and cannot be repeated, but can be NULL or both.

create table t_user(
	id int,
    username varchar(255) unique.-- Column level constraints
    pwd varchar(255))Copy the code

We can also add unique constraints to two or more columns at the same time.

-- This means that two fields cannot be joined twice, adding a constraint to both fields. Table level constraints
create table t_user(
	id.int,
    username varchar(255),
    pwd varchar(255),
    unique(username,pwd)
)
Copy the code

-- This means that neither field can be repeated, and that both fields have two constraints.
create table t_user(
	id.int,
    username varchar(255) unique,
    pwd varchar(255) unique
)
Copy the code

6.6 Relationships between Tables

6.6.1 One to one

For example, the T_Person table and the T_card table are people and ID cards. In this case, you need to find out who is the master table and who is the slave table. The person can do not have id card, but ID card must want to have talent to go, so the person is main table, and ID card is from the table. There are two ways to design a table:

Add a foreign key column to t_card (relative to T_user) and add a unique constraint to the foreign key;
Add a foreign key constraint to the primary key of t_card (relative to t_user), so that the primary key of T_card is also a foreign key.

6.6.2 One-to-Many (many-to-One)

One to many (many to one) : the most common one is one to many! One – to – many and one – to – one. The relationship between T_user and T_section is one to many for T_user, and many to one for T_section! In this case, you are creating foreign keys in multiple ways!

6.6.3 Many-to-many

For example, the T_stU and T_TEACHER tables mean that a student can have multiple teachers, and a teacher can have multiple students. This situation often requires the creation of intermediate tables to handle many-to-many relationships. For example, create another table t_STU_tea and give two foreign keys, one relative to t_STU and the other relative to T_TEACHER.

7. Add, delete and modify DML

All DML operations have an affected row, indicating how many rows of data were manipulated by the SQL execution.

7.1 Insert Operation

7.1.1, grammar,

INSERT INTOThe name of the table (column1Column,2Column,3...) VALUES(value1And the value2And the value3...) ;Copy the code

7.1.2, examples,

-- 1. Insert the complete data record
INSERT INTO t_student(name,email,age) VALUES('xiaoming'.'xiao@'.18);
-- 2. Insert part of the data record
INSERT INTO t_student(name,age) VALUES('xiaodong'.19);
-- 3. Insert multiple data records (MySQL only)
INSERT INTO t_student(name,email,age) VALUES('xiaohong'.'hong@'.17),
('xiaohong2'.'hong2@'.17), ('xiaohong3'.'hong@3'.17)
-- 4. Insert the query result
INSERT INTO t_student(name,email,age) SELECT name,email,age FROM t_student
Copy the code

7.1.3, pay attention to

Mybatis (Mybatis) uses a loop to insert multiple rows of data at a time (not recommended).

7.2 Modify Operations

7.2.1, grammar,

UPDATE the table nameSET 列1 = 值1Column,2 = 值2, column3 = value3...
WHERE[condition]Copy the code

7.2.2, practice

-- Change the three pieces to the west gate
UPDATE t_student SET name='Westgate blow water' WHERE name='Joe';
Select * from user where id = 3 and name = ye gucheng,email = ye@,age = 100
UPDATE t_student SET name=Ye Gu Cheng WHERE id=3;
Copy the code

7.2.3, pay attention to

If a condition is omitted, the entire table will be modified, so conditions are usually included
Modify statement not availablefromThe keyword.

7.3. Delete operations

7.3.1, grammar,

DELETE FROMThe name of the tableWHERE[condition]Copy the code

7.3.2, practice

Select * from student where id = 2
DELETE FROM t_student WHERE id=2;
-- Delete all information about Ye Gu Cheng
DELETE FROM t_student WHERE name=Ye Gu Cheng
Copy the code

7.3.3, pay attention to

FROMCan not writeFORM
If the WHERE clause is omitted, the entire table is deleted

8. DQL Query operations

8.1. Grammar description

SELECT 列1Column,2Column,3..FROMThe table name [WHERE];
-- SELECT SELECT the column to be queried
-- FROM provides data source (table, view, or other data source)
-- You can write * to query all columns, but it will not be used in actual development, which is low performance. In actual development, all fields will be listed
Copy the code

8.2. Common Query

8.2.1. Set an alias

8.2.1.1, grammar,

SELECTThe column nameASThe aliasFROMThe table name [WHERE];
SELECTThe column name The aliasFROMThe table name [WHERE]
Copy the code

8.2.1.2,

Change the column header.
As a result of the calculation.
As an alias for the column.
Single quotes are required if special characters are used in aliases, if case is enforced, or if Spaces are used.

8.2.1.3, examples,

-- Query all items id, name, 50 each, and the cost of shipping 1 yuan each (use alias)
SELECT id,productName,(costPrice + 1) * 50 1 AS allPrice FROM product
SELECT id,productName,(costPrice + 1) * 50 allPrice FROM product
Copy the code

8.2.2 Output according to the format

In order to facilitate the user to browse the query result data, sometimes you need to set the display format of the query result. You can use the CONCAT function to concatenate the string.

8.2.2.1, grammar,

CONCAT (string1The string2,...).Copy the code

8.2.2.2, actual combat

-- Check the name and retail price of the product. The retail price of XXX goods is ooO
SELECT CONCAT(productName,'The retail price of the goods is:',salePrice) FROM product
Copy the code

8.2.3. Eliminate duplicate data

Distinct cannot be preceded by other fields. It can only appear in the front of all fields. What he means is that all the following fields are combined to be deweighted.

SELECT DISTINCTThe column name,...FROMThe name of the table.Copy the code

8.2.4. Arithmetic operators

You can use the arithmetic operator to create an expression for data of type number. It has the following precedence:

Multiplication and division take precedence over addition and subtraction.
This is done from left to right.
The use of parentheses in an expression forces a change in the order of precedence operations

-- Query all item ID, name and wholesale price (wholesale price = selling price * discount)
SELECT id,productName,salePrice * cutoff FROM product
-- Query the id, name, and cost of 50 items each (cost =costPirce)
SELECT id,productName,costPrice * 50 FROM product
-- Query all goods ID, name, each 50, and the cost of shipping 1 yuan
SELECT id,productName,(costPrice + 1) * 50 FROM product
Copy the code

8.2.5. Comparison operator

There are several comparison operators:

=
>
<
< =
! = (<> Equivalent! =)

-- Query logitech G9X product information
SELECT * FROM product WHERE productName='logitech G9X';
-- Query all items with retail price less than or equal to 200
SELECT * FROM product WHERE salePrice < = 200
Query items with wholesale price greater than 350
SELECT *,salePrice * cutoff allPrice FROM product WHERE salePrice * cutoff > 350
Copy the code

8.2.6 Logical operators

The operator	meaning
AND	Return TRUE if both conditions are TRUE
OR	Returns TRUE if one of the combined conditions is TRUE
NOT	Return TRUE if the following condition is FALSE, or FALSE if TRUE

8.2.7 Range matching

Range matching: The BETWEEN AND operator, typically used on numeric ranges. But the same is true for character data and date types

To use. You need two pieces of data.

8.2.7.1, grammar,

WHEREThe column nameBETWEEN minValue AND maxValue; - closed interval
Copy the code

8.2.7.2, examples,

-- Query the information of goods with retail price between 300 and 400
SELECT * FROM product WHERE salePrice BETWEEN 300 AND 400
-- Query the information of goods whose retail price is not between 300 and 400
SELECT * FROM product WHERE NOT salePrice BETWEEN 300 AND 400
Copy the code

8.2.8 Querying a collection

Collection query: Uses the IN operator to determine whether the column values are IN the specified collection.

8.2.8.1, grammar,

WHEREThe column nameIN(value1And the value2....). ;Copy the code

8.2.8.2, examples,

Select id, item name from category 2,4
SELECT id,productName FROM product WHERE dir_id IN (2.4)
Select * from item where category number is not 2,4; select * from item where category number is not 2,4
SELECT id,dir_id,productName FROM product WHERE dir_id NOT IN (2.4)
Copy the code

8.2.9, found empty

IS NULL: determines whether the column value IS an empty value. If the column value IS not an empty string, use == to determine whether the column value IS an empty string.

8.2.9.1, grammar,

WHEREThe column nameIS NULL;
Copy the code

8.2.9.2, examples,

Query all item information with item name NULL.
SELECT * FROM product WHERE productName IS NULL;
SELECT * FROM product WHERE supplier =' '
Copy the code

8.2.9.3, pay attention to

= = = = = = = = = = = = = =

A string.

8.2.10. Filter and query

Use the WHERE clause to limit the returned records

8.2.10.1, grammar,

SELECT <selectList>
FROMThe name of the tableWHEREConditions;Copy the code

8.2.10.2, pay attention to

The WHERE clause comes after the FROM clause.
FROM clause: which table the data is to be queried FROM => WHERE clause: which rows are to be filtered => SELECT clause: which columns are to be filtered.

8.2.11. Fuzzy Query

Fuzzy query data uses the LIKE operator to perform a wildcard query. It has two wildcards:

% : indicates that there may be zero or any number of characters.
_ : indicates any character.

8.2.11.1, grammar,

WHEREThe column nameLike '%M_'
Copy the code

8.2.11.2, examples,

Select * from logitech M9_ where item name matches '% logitech M9_
SELECT * FROM product WHERE productName LIKE Logitech M9_ '%'
Copy the code

8.3. Result sorting

The ORDER BY clause is used to sort the results of the query. There are two types of sorting modes:

ASC: ascending (default).
DESC: descending order.

The ORDER BY clause appears at the end of the SELECT statement.

8.3.1, examples,

-- Single-column sorting: Select ID, item name, Category number, retail price and sort by retail price descending
SELECT id,productName,dir_id,salePrice FROM product ORDER BY salePrice DESC
-- Multi-column sorting: select ID, item name, category number, retail price in descending order by category number and then in ascending order by retail price
SELECT * FROM product ORDER BY dir_id DESC,salePrice ASC
Copy the code

8.3.2, pay attention to

Whoever comes first is first in order.
If the alias of the column is used' 'The sort by this alias is invalid.

- a counterexample
SELECT id,salePrice 'sp' FROM product ORDER BY 'sp'
Copy the code

8.4 Paging query

Limit is a mysql-specific mechanism that is used to retrieve portions of the result set. Oracle has a similar mechanism called RowNUM.

Limit is the last part of the SQL statement to execute.

8.4.1, grammar,

SELECT <selectList>
FROMThe table name [WHERE] LIMIT ? ,?-- The first? : Number of indexes in the start row beginIndex, the default value is 0
-- Second? : Indicates the maximum number of records to display on each page
-- Display 3 pieces of data per page
SELECT * FROM product LIMIT 0,3
SELECT * FROM product LIMIT 6,3
-- 8 页 : SELECT * FROM product LIMIT 21,3
-- currentPage: currentPage
-- Maximum number of records to display per page: pageSize
Copy the code

8.4.2 General standard paging SQL

beginIndex = (currentPage – 1) * pageSize

8.4.3, case

case

Find employees who rank between 4 and 6 in salary

select name,sal,from emp order by desc limit 3.6;
Copy the code

8.5 grouping functions

COUNT(*) : The number of records in the statistical table
SUM(column) : SUM of the summary columns
MAX(column) : Gets the maximum value of a column
MIN(column) : Gets the minimum value of a column
AVG(column) : Gets the average value of the columns

-- Query how much data is in the inventory
SELECT COUNT(*) FROM product
-- Calculate the total purchase price for all items
SELECT SUM(costPrice) FROM product
Copy the code

Note:

The group ignores null without additional filtering for the null condition. 支那
There is a syntax rule in SQL statements that grouping functions cannot be used directly in where clauses.
count(*)andcount(Specific field differences)
- count(*)Must be the total number of records, regardless of the field.
- Count (specific field)Is the number of records for which this field is not empty.

8.6 Group Query

8.6.1, group by

Group by: group by a field or certain fields.

The grouping of aggregate functions is used in conjunction with the group by statement, and any grouping function is not executed until the group by statement is executed. When an SQL statement does not have group by, the entire table is grouped by itself. There is a syntax rule in SQL statements that grouping functions cannot be used directly in where clauses. The reason is: Group BY is executed after where is executed. This SQL statement is incorrect:

select * from emp where sal > avg(sal);
Copy the code

When I get to avg(Sal), I haven’t executed group by yet, so I can’t execute the group function. You cannot execute a grouping function without having already grouped.

Need: Seek the highest salary for each job

select max(sal),job from emp group by job;
Copy the code

Conclusion: When a SQL statement contains group by, only group functions or fields are allowed after select.

Need: Find out the highest salaries for different jobs in each department)

select
	deptno ,job ,max(sql)
from 
	emp
group by
	deptno,job
Copy the code

8.6.2, having

Having: Having filters the grouped data again.

Demand: Find the average salary for each department and ask for data showing salaries greater than 2000

select val(sal),deptno from emp group by deptno having val(sal) > 2000;
Copy the code

8.6.3 Summary of group by and Having

Have a “where” with “group by” and “having” with “group by”.

8.6 Execution sequence of DQL sentences

select.from.where.group by.having.order by.limit.Copy the code

From: which table to look up the data from.
Where: Filters the desired row data.
Group by: Group by
Having: Re-filtering grouped data
SELECT: Filters the data of the columns to be displayed.
ORDER BY: Sort operations.

9. Multi-table query

9.1 Connection Query

In the actual development, most of the cases are not from a single table query data, is generally multiple tables joint query to get the final result, generally a business will correspond to multiple tables.

There are two categories of join queries:

SQL92 (old syntax, outdated).
SQL99 (syntax is new).

Cartesian product phenomenon

When two tables are joined in the query, there is no restriction, the final query result is the product of two table entries, this phenomenon is called cartesian product phenomenon.

There are two benefits of aliasing tables in development:

High execution efficiency.
Read well

select 
	e.ename,d.dname
from
	emp e,dept d
Copy the code

The number that appears in this way is the product of the number of the two tables.

Now that we have the Cartesian product, we want to avoid the Cartesian product, and the way to avoid the Cartesian product is to add conditions to filter, but by avoiding the Cartesian product, does that reduce the number of matches? The answer is no, the number is still the product of two tables, but it shows the number of valid records.

9.3 internal connection

Assuming that A and B two tables to connect, use the link, where A table and B on the table to match records will be queried, AB two tables is equal, no main points, this is in connection.

9.3.1 Equivalent connection

The biggest characteristic of the inner connection is that the condition is equal.

select.from.inner join -- The inner can be omitted. It's more readable with the inner.onJoin conditionwhere.Copy the code

select 
	e.name,d.name 
from 
	emp e 
inner join -- The inner can be omitted. It's more readable with the inner
	dept d 
on 
	e.deptno =  d.deptno
Copy the code

SQL99 has a clearer syntax structure and separates the join conditions of the table from the later WHERE filter conditions.

9.3.2 Non-equivalent connection

The relation in the connection condition is not an equal relation.

select e.name,e.sal,e.grade
from emp e
join salgrade s
on e.sal between s.local and s.hisal
Copy the code

9.3.3 self-connection

The biggest feature is that a table as two tables, linking themselves. (Not often used)

9.4 External connection

If table A and table B are joined, table A and table B are joined. If table B and table A are joined, table A and table B are joined. Table A and table B are joined.

When the data in the side table does not match the data in the main table, the side table automatically simulates a NULL match. The data of the main table will be unconditionally queried.

9.4.1 Classification of external connections

External connections fall into two categories:

LEFT outer join (LEFT join LEFT) : indicates that the table on the LEFT is the main table.
Outer RIGHT join (RIGHT join) : indicates that the table on the RIGHT is the main table.

There is a way to write a left join, and there is a way to write a right join. When LEFT is joined, it indicates that the table above (LEFT) is the main table.

9.4.1.1. Left connection

SELECT * FROM emp e LEFT OUTER JOINdept d ON e.deptno=d.deptno;
Copy the code

Note: OUTER can be omitted

The left join is to query the left table first, and then query the right table. In the right table, those that meet the conditions are displayed, and those that do not meet the conditions are displayed as NULL.

9.4.1.2 Right connection

The right join is to query all records in the right table, and then the left table meets the condition of display, not NULL display.

Requirements:

Department 40 in the DEPT table does not have any employees, but in the right join, if the DEPT table is the right table, the department 40 will still be found, but the corresponding employee information will be NULL.

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno
Copy the code

9.4.2, pay attention to

The inner join shows that the two tables are equal, there is no primary and secondary points.

The outer join indicates that one table is the main table and another table is the side table.

Foreign joins are most common in development because data is lost for internal join queries.

9.5 connect three tables

select.from.join.on.join.on. (conditions)Copy the code

9.6, the union

The union keyword can be used to add the query result set. It joins two query results and can be used to concatenate data from two unrelated tables.

Note: Union must be used to query two tables with the same number of columns, otherwise it will not be displayed.

case

The query jobs are MANAGER and SALESMAN employees

select ename,job from  emp where job = 'MANAGER'
union
select ename,job from  emp where job = 'SALESMAN'
Copy the code

10. Subqueries

10.1 Overview of Subqueries

Select statements are nested within a select statement. A nested SELECT statement is a subquery. It can appear after select, FROM, and WHERE.

10.2, in the WHERE clause

case

Find out about employees with above-average salaries

select * 
from emp 
where sal > (select avg(sal) from emp);
Copy the code

10.3,From is used after a sentence

case

Find a pay grade for each department with the average salary.

Figure out the average salary of each department (group by department number and find the average of Sal)

select deptno,avg(sal) avgsal from emp group by deptno
Copy the code

Make a temporary table t and join salgrade s at the top of the list: t.avgsal between s.losal and S.sal

select 
	t.*,s.grade
from
	(select deptno,avg(sal) avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal
Copy the code

10.4. Used after select

demand

Find out the name of each employee’s department and ask to show the employee name and the department name.

select 
	e.ename,
	(select d.dname from dept d where e.deptno = d.deptno) dname
from 
	emp e;
Copy the code

Xi. Business

11.1 what is a transaction

A transaction is a complete unit of business logic that cannot be divided. Transactions can guarantee atomicity for multiple operations, all or none. The transaction guarantees that the batch DML will either all succeed or all fail for the database.

The only statements related to transactions are DML statements because all three of them are related to data in a database table. Transactions exist to ensure data integrity and security.

11.2 Principle of Opening transactions

Let’s say we complete an operation and we need to perform an INSERT, then an UPDATE, and finally a DELETE.

11.3 Characteristics of transactions

Transactions have four characteristic ACIDS

Atomicity

A transaction is the smallest unit of work and is not subdivided. All operations in the entire transaction must be completed (cancelled) as a single unit.

Consistency

The transaction must ensure that multiple DML statements succeed or fail simultaneously.

Isolation (Isolation)

One transaction does not affect the operation of other transactions.

Durability (Durability)

Eventually the changes to the database made by the transaction are persisted in a hard disk file and the transaction is successful.

MySQL transactions are automatically committed by default and can be changed to manual by command.

start transaction;
Copy the code

11.4. Detailed explanation of isolation

11.4.1 Problems Caused by concurrent Access

#### 11.4.1.1 Dirty readCopy the code

A dirty read occurs when a transaction starts reading a row, but another transaction has updated the data but failed to commit in time.

11.4.1.2 Cannot be read repeatedly

In the same transaction, two reads of the same data by the same read operation produce different results, which is called non-repeatable reads.

11.4.1.3, phantom reads

A phantom read is a new row that appears due to the commit of another transaction that did not exist before in the same transaction. Illusion reading emphasizes the difference in the number of lines read before and after.

11.4.2 Isolation Level

InnoDB implements four isolation levels to control changes made by transactions and notify them to other concurrent transactions. The isolation levels are as follows from lowest to highest:

READ UMCOMMITTED
READ COMMITTED
REPEATABLE READ MySQL default
SERIALIZABLE

11.4.2.1 Read is not committed

The transaction has not committed yet, so our current transaction can read the uncommitted data from the other party. The isolation level is the lowest. The read is uncommitted. Dirty reads exist, indicating that dirty data is blocked.

11.4.2.2 Read committed

We can only read the data after the transaction is committed. This isolation level resolves the dirty read phenomenon, but causes the non-repeatable read phenomenon.

This level is the default isolation level for Oracle.

11.4.2.3 Repeatable read

We can no longer see committed transactions. This isolation level solves the problem of non-repeatable reads, but creates the problem of phantom reads. Let’s say a thread deletes all the data in a database, but we’re still reading the original data, the backup of the database.

Default MySQL level.

11.4.2.4 Serialization

Complete isolation of one transaction from other transactions. Two transactions cannot be concurrent, and threads need to be queued, also known as serialization. Although safe, the performance is poor and the customer experience is poor.

Xii. Index

12.1 What is an index

An index is equivalent to a book catalog, through which you can quickly find the corresponding resources. Indexes are used to quickly identify rows with a particular value in a column, and can effectively narrow down the scope of the scan. An index is added to a field or to a group of fields.

In the database, there are two ways to query a table:

A full table scan
Retrieval by index (efficient)

Although indexes can improve the efficiency of retrieval, they cannot be added at will, because indexes are objects in the database and need to be maintained continuously by the database, which requires cost. For example, if the data in a table is frequently modified, it is not appropriate to add an index, because once the data is modified, the index needs to be reordered.

When do I need to Create an index

The data is huge.
There are few DML operations on this field (because the field is modified and the index needs to be maintained).
This field often appears in the WHERE clause (often by which field to query)

Note: Primary keys and columns with unique constraints are automatically added to the index. His bottom is B+Tree.

 explain select * from emp where SAL = 1500;
Copy the code

When the value of the type field is ALL, a full table scan is performed (no indexes are added). Rows means 14 pieces of data were searched.

12.3. Adding an Index

Add index emp_sal_index (sal) to emP table
create index emp_sal_index on emp(sal);

-- Syntax format
create indexThe index nameonTable name (field name)Copy the code

12.4. Viewing indexes

-- View the syntax of the index
show index from emp;

-- Syntax format
show index fromThe name of the table.Copy the code

12.5. Delete an index

Delete index syntax
drop indexThe index nameonThe name of the table.Copy the code

12.6. Principles of indexing

The index of the underlying data structure is B + Tree, through the B + Tress narrowing the scope of scanning, the underlying index for sorting, partition, the index will carry on the physical address in the table, in the end by index after the retrieved data, access to the associated physical address, by physical address location to the data in the table, efficiency is the highest (table don’t go, walk hard disk).

select ename from emp where ename = 'SMITH';
Copy the code

The SQL statement is transformed by indexing

select ename from emp wherePhysical address ='Physical address retrieved by index'
Copy the code

12.7. Classification of indexes

Single index: Adds an index to that field.
Compound index: Adds an index to multiple fields.
Primary key Index: An index is automatically added to the primary key.
Unique index: An index is automatically added to a field that has a unique constraint.

12.8. The index is invalid

In fuzzy queries, if the first wildcard is %, the index is invalid because it does not know what character was originally matched.

Xiii. Views

13.1. What is a View

A view is a database object defined based on a query (that is, a SELECT expression) to get the local data you want to see and use. So it’s also called a virtual table.

A view is a view of data from a different perspective, the same table of data, from a different perspective.

We can add, delete, modify and check the view, which will affect the original table data, through the view to affect the original table data, is not directly operated on the original table. Only DQL statements can be created as view objects.

13.2. Creating a View

-- Syntax format
create viewView nameas selectstatementsDemonstration -
create view myview as select empo,ename from emp;
Copy the code

13.3. Modify views

-- Syntax format
updateView namesetThe column name ='value' whereConditions;Copy the code

13.4. Deleting a View

-- Syntax format
delete fromView namewhereConditions;Demonstration -
delete from myview where empo = '12134';
Copy the code

13.5 Functions of views

View can hide the implementation details of the table, the high level of security system, the database only provides external related views, view oriented object CRUD.

Fourteen, database design three paradigm

Design paradigms are the basis for designing tables, and tables designed according to these three paradigms do not have data redundancy. But in actual development, depending on customer requirements, data redundancy may be traded for execution speed and space for time.

14.1. First Normal form

Every table should have a primary key, and every field should be atomically non-divisible.

14.2. Second Normal form

Based on the first normal form, all non-primary key fields are completely dependent on the primary key, and no partial dependencies can be generated.

A typical example is to solve many to many problems, encountered many to many time, recite the formula: many to many? Three tables, relational table two foreign keys

14.3. Third Normal Form

Based on the second normal form, all non-primary key fields depend directly on the primary key, and no transitivity dependency can be created.

A typical example is one to many, when one to many problems when recite the formula: one to many? Two tables, more tables plus foreign keys.

14.4. One-to-one relationship design scheme

14.4.1 Primary Key sharing

T_user_login User login table

id(pk)	username	password
1	zs	123
2	ls	456

T_user_detail User detail table

id(pk+fk)	realname	tel
1	Zhang SAN	111
2	Li si	456

14.4.2 The foreign key is unique

T_user_login User login table

id(pk)	username	password
1	zs	123
2	ls	456

T_user_detail User detail table

id(pk)	realname	tel	userid(fk+unique)
1	Zhang SAN	111	2
2	Li si	456	2

view

-- Syntax format
delete fromView namewhereConditions;Demonstration -
delete from myview where empo = '12134';
Copy the code

13.5 Functions of views

View can hide the implementation details of the table, the high level of security system, the database only provides external related views, view oriented object CRUD.

Fourteen, database design three paradigm

14.1. First Normal form

Every table should have a primary key, and every field should be atomically non-divisible.

14.2. Second Normal form

Based on the first normal form, all non-primary key fields are completely dependent on the primary key, and no partial dependencies can be generated.

A typical example is to solve many to many problems, encountered many to many time, recite the formula: many to many? Three tables, relational table two foreign keys

14.3. Third Normal Form

Based on the second normal form, all non-primary key fields depend directly on the primary key, and no transitivity dependency can be created.

A typical example is one to many, when one to many problems when recite the formula: one to many? Two tables, more tables plus foreign keys.

14.4. One-to-one relationship design scheme

14.4.1 Primary Key sharing

T_user_login User login table

id(pk)	username	password
1	zs	123
2	ls	456

T_user_detail User detail table

id(pk+fk)	realname	tel
1	Zhang SAN	111
2	Li si	456

14.4.2 The foreign key is unique

T_user_login User login table

id(pk)	username	password
1	zs	123
2	ls	456

T_user_detail User detail table

id(pk)	realname	tel	userid(fk+unique)
1	Zhang SAN	111	2
2	Li si	456	2

The most complete MySQL knowledge point is organized

Introduction to SQL

1.1 overview of SQL

1.2. SQL contains six parts

Data Query Language (DQL)

1.2.2 Data Manipulation Language (DML)

1.2.3 Data Definition Language (DDL)

1.2.4 Transaction Processing Language (TPL)

1.2.5 Data Control Language (DCL)

1.2.6 Pointer Control Language (CCL)

1.3. Writing rules

Table and ORM

2.1, table,

2.2 Table and Object Relationships (ORM)

MySQL service

3.1 MySQL Service

Connect to MySQL

Iv. Database foundation

4.1 Basic Database Operations

4.2 Storage Engine

MySQL column types

5.1 the most Common types

5.2. The type is an integer

5.3. Decimal type

5.4. Character type

5.5 Date type

5.6 binary type

Vi.Table operations (DDL)

Create table

6.1.1, grammar,

6.1.2, examples,

Also 6.1.3, pay attention to

Delete table

6.2.1, grammar,

6.2.2, examples,

6.2.3, pay attention to

6.3 Table replication and batch insert

6.3.1 Table replication

6.3.2 Batch insert of tables

6.5 Constraints on the table

6.5.1 Primary Key Constraints

6.5.1.1 Classification of primary keys

6.5.1.2 How to design the primary key

Conclusion 6.5.1.3,

6.5.1.4, examples,

6.5.2 Foreign Key Constraints

6.5.3. Unique Constraint

6.6 Relationships between Tables

6.6.1 One to one

6.6.2 One-to-Many (many-to-One)

6.6.3 Many-to-many

7. Add, delete and modify DML

7.1 Insert Operation

7.1.1, grammar,

7.1.2, examples,

7.1.3, pay attention to

7.2 Modify Operations

7.2.1, grammar,

7.2.2, practice

7.2.3, pay attention to

7.3. Delete operations

7.3.1, grammar,

7.3.2, practice

7.3.3, pay attention to

8. DQL Query operations

8.1. Grammar description

8.2. Common Query

8.2.1. Set an alias

8.2.1.1, grammar,

8.2.1.2,

8.2.1.3, examples,

8.2.2 Output according to the format

8.2.2.1, grammar,

8.2.2.2, actual combat

8.2.3. Eliminate duplicate data

8.2.4. Arithmetic operators

8.2.5. Comparison operator

8.2.6 Logical operators

8.2.7 Range matching

8.2.7.1, grammar,