##Objectives of this unit
1, Why learn database
2, Related concepts of database
DBMS,DB,SQL
3, Characteristics of database storage data
4, Initial MySQL
Introduction to MySQL products
MySQL product installation ★
Start and stop of MySQL service ★
Login and logout of MySQL service ★
Common commands and syntax specifications for MySQL
5, DQL language learning ★
Basic query ★
Condition query ★
Sort query ★
Common functions ★
Grouping function ★
Group query ★
Connection query ★
Sub query √
Paging query ★
union joint query √

Vi DML Language learning    ★             
	Insert statement						
	Modify statement						
	Delete statement						
VII DDL Language learning  
	Library and table management	 √				
	Introduction to common data types  √          
	Common constraints  	  √			
VIII TCL Language learning
	Transaction and transaction processing                 
9, Explanation of view           √
10, Variable                      
11, Stored procedures and functions   
12, Process control structure       

##Database benefits
1. Persistent data to local
2. It can realize structured query and facilitate management

##Database related concepts
1. DB: database, a container that holds a set of organized data
2. DBMS: database management system, also known as database software (product), which is used to manage data in DB
3. SQL: structured query language, which is used to communicate with DBMS

##Characteristics of database storage data
1. Put the data into the table, and then put the table into the library
2. There can be multiple tables in a database. Each table has a name to identify itself. Table names are unique.
3. Tables have some features that define how data is stored in tables, similar to the design of "classes" in java.
4. A table consists of columns, which we also call fields. All tables are composed of one or more columns, and each column is similar to the "attribute" in java
5. The data in the table is stored in rows, and each row is similar to the "object" in java.

##Introduction and installation of MySQL products

###Start and stop of MySQL service
Method 1: Computer - right click management - Service
Method 2: run as Administrator
net start service name (start service)
net stop service name (stop service)

###Login and logout of MySQL service
Method 1: through the built-in client of mysql
root user only

Mode II: Adoption windows Built in client
 Sign in:
mysql [-h host name -P [port number]-u user name -p password

sign out:
exit or ctrl+C

###Common MySQL commands

1.View all current databases
show databases;
2.Open the specified library
use Library name
3.View all tables in the current library
show tables;
4.View all tables in other libraries
show tables from Library name;
5.Create table
create table Table name(

	Column name column type,
	Column name, column type,
	. . . 
);
6.View table structure
desc Table name;


7.View the version of the server
 Method 1: log in to mysql Server
select version();
Method 2: no login to mysql Server
mysql --version
 or
mysql --V

###Syntax specification of MySQL
1. It is not case sensitive, but it is recommended that keywords be capitalized and table names and column names be lowercase
2. Each command should preferably end with a semicolon
3. Each command can be indented or wrapped as needed
4. Notes
Single line notes: # note text
Single line note: - note text
Multiline note: / * note text*/

###Language classification of SQL
DQL (data query language): Data Query Language
select
DML (data manipulation language): Data Manipulation Language
insert ,update,delete
DDL (data definition language): Data Definition Language
create,drop,alter
TCL (Transaction Control Language): Transaction Control Language
commit,rollback

###Common commands for SQL

show databases； View all databases
use Library name; Open the specified library
show tables ; Displays all tables in the library
show tables from Library name;Displays all tables in the specified library
create table Table name(
	Field name field type,	
	Field name field type
); Create table

desc Table name; View the structure of the specified table
select * from Table name;Displays all data in the table

##DQL language learning
###Advanced 1: basic query
Syntax:
SELECT what to query
[FROM table name];

be similar to Java in :System.out.println(Something to print);
characteristic:
①adopt select The query result is a virtual table, not a real one
② The things to query can be constant values, expressions, fields and functions

###Advanced 2: condition query
Condition query: filter the data of the original table according to the conditions and query the desired data
Syntax:
select
Field | expression | constant value | function to query
from
surface
where
Conditions;

Classification:
1, Conditional expression
	Example: salary>10000
	Conditional operator:
	> < >= <= = != <>

2, Logical expression
 Example: salary>10000 && salary<20000

Logical operators:

	and(&&):If the two conditions are true at the same time, the result is true，Otherwise false
	or(||): If one of the two conditions holds, the result is true，Otherwise false
	not(!): If the condition holds, then not After false，Otherwise true

Three, fuzzy query
 Example: last_name like 'a%'

###Advanced 3: sort query

Syntax:
select
	What to query
from
	surface
where 
	condition

order by Sorted fields|expression|function|Alias[ asc|desc]

###Advanced 4: common functions
1, Single line function
1. Character function
concat splicing
substr intercept substring
Convert upper to uppercase
Convert lower to lowercase
trim removes the spaces and characters specified before and after
ltrim to the left space
rtrim to the right space
Replace replace
lpad left fill
rpad right fill
instr returns the index of the first occurrence of a substring
length gets the number of bytes

2,Mathematical function
	round rounding
	rand random number
	floor Round down
	ceil Round up
	mod Surplus
	truncate truncation
3,Date function
	now Current system date+time
	curdate Current system date
	curtime Current system time
	str_to_date Convert characters to dates
	date_format Convert date to character
4,Process control function
	if Handle double branches
	case Statement processing multiple branches
		Case 1: Processing equivalence judgment
		Case 2: judgment of processing conditions
	
5,Other functions
	version edition
	database Current library
	user Currently connected user

2, Grouping function

	sum Sum
	max Maximum
	min minimum value
	avg average value
	count count

	characteristic:
	1,The above five grouping functions are ignored null Value, except count(*)
	2,sum and avg It is generally used to deal with numerical type
		max,min,count Can handle any data type
    3,All can be matched distinct Used to count the results after de duplication
	4,count The following parameters can be supported:
		Fields*,Constant value, usually 1

	   Recommended use count(*)

##Advanced 5: grouping query
Syntax:
select query field, grouping function
from table
group by grouped fields

characteristic:
1,You can group by a single field
2,The fields queried together with the grouping function should preferably be grouped fields
3,Group filtering
		Table for	position			keyword
 Filter before grouping:	Original table		group by In front of		where
 Filter after grouping:	Grouped result set	group by Behind		having

4,You can group by multiple fields separated by commas
5,Sorting can be supported
6,having Aliases can be supported after

##Advanced 6: multi table join query

Cartesian product: appears if the connection condition is omitted or invalid
 Solution: add a connection condition

1, Connection in traditional mode: equivalent connection - non equivalent connection

1.Results of equivalent connection = Intersection of multiple tables
2.n Table connection, at least n-1 Multiple connection conditions
3.There is no order requirement for multiple tables regardless of primary and secondary
4.Aliases are generally used to improve readability and performance

2, sql99 syntax: realize connection through join keyword

Meaning: launched in 1999 sql grammar
 support:
Equivalent connection, non equivalent connection (internal connection)
External connection
 Cross connect

Syntax:

select Field,...
from Table 1
[inner|left outer|right outer|cross]join Table 2 on  Connection conditions
[inner|left outer|right outer|cross]join Table 3 on  Connection conditions
[where [filter criteria]
[group by [group fields]
[having Filter criteria after grouping]
[order by Sorted field or expression]

Benefits: in terms of statements, connection conditions and filter conditions are separated, which is concise and clear!

3, Self connection

Case: query employee name and direct superior name

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

##Advanced 7: subquery

meaning:

Another complete query is nested in one query statement select Statement in which the nested select Statement, called subquery or intra query
 The external query statement is called primary query or external query

characteristic:

1,Subqueries are placed in parentheses
2,Subqueries can be placed in from Back select Back where Back having Behind, but generally on the right side of the condition
3,The sub query takes precedence over the main query, which uses the execution results of the sub query
4,Sub queries are divided into the following two categories according to the number of rows in the query results:
① single-row subqueries 
	The result set has only one row
	Generally used with single line operators:> < = <> >= <= 
	Illegal use of sub query:
	a,The result of a subquery is a set of values
	b,The result of subquery is null
	
② Multiline subquery
	The result set has multiple rows
	Generally used with multiline operators: any,all,in,not in
	in:  It belongs to any one of the sub query results
	any and all It can often be replaced by other queries

##Advanced 8: paging query

Application scenario:

Practical web In the project, you need to submit the corresponding paged query according to the user's needs sql sentence

Syntax:

select field|expression,...
from surface
[where Conditions]
[group by [group fields]
[having Conditions]
[order by Sorted fields]
limit [Initial entry index,] entries;

characteristic:

1.Start entry index starts at 0

2.limit Clause is placed at the end of the query statement

3.Formula: select * from  surface limit (page-1)*sizePerPage,sizePerPage
 If:
Number of entries per page sizePerPage
 Number of pages to display page

##Advanced 9: joint query

introduce:
union Union

Syntax:

select field|constant|expression|Functions[ from [table][ where Conditions] union [all]
select field|constant|expression|Functions[ from [table][ where Conditions] union [all]
select field|constant|expression|Functions[ from [table][ where Conditions] union  [all]
.....
select field|constant|expression|Functions[ from [table][ where Conditions]

characteristic:

1,The number of columns of multiple query statements must be consistent
2,The column types of multiple query statements are almost the same
3,union Represents de duplication, union all Means no weight loss

##DML language

###Insert

Syntax:
insert into table name (field name,...)
Values (value 1,...);

characteristic:

1,Field types and value types are consistent or compatible, and correspond to each other one by one
2,A field that can be empty, without inserting a value, or with null fill
3,A field that cannot be empty must be inserted with a value
4,The number of fields and values must be consistent
5,Fields can be omitted, but all fields are used by default, and the order is consistent with the storage order in the table

###Modification

Modify single table syntax:

update Table name set field=New value,field=New value
[where Conditions]

Modify multi table syntax:

update Table 1 alias 1,Table 2 alias 2
set field=New value, field=New value
where Connection conditions
and Screening conditions

###Delete

Method 1: delete statement

Deletion of single table: ★
delete from table name [where filter criteria]

Deletion of multiple tables:
delete alias 1, alias 2
from table 1 alias 1, table 2 alias 2
where connection conditions
and screening conditions;

Mode 2: truncate statement

truncate table Table name

Difference between the two methods [interview questions]

#1.truncate cannot add a where condition, but delete can add a where condition

#2.truncate is more efficient

#3.truncate after deleting the table with self growing columns, if you insert data again, the data starts from 1
#delete after deleting a table with self growing columns, if data is inserted again, the data will start from the last breakpoint

#4.truncate deletion cannot be rolled back. delete deletion can be rolled back

##DDL statement
###Library and table management
Library management:

1, Create Library
create database Library name
 2, Delete Library
drop database Library name

Table management:
#1. Create table

CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME
	

);

DESC studentinfo;
#2. Modify table alter
 Syntax: ALTER TABLE Table name ADD|MODIFY|DROP|CHANGE COLUMN Type [field name];

#① Modify field name
ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

#② Modify table name
ALTER TABLE stuinfo RENAME [TO]  studentinfo;
#③ Modify field types and column level constraints
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

#④ Add field

ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
#⑤ Delete field
ALTER TABLE studentinfo DROP COLUMN email;


#3. Delete table

DROP TABLE [IF EXISTS] studentinfo;

###Common types

Integer:
	
Decimal:
	float 
	Fixed point type
 character:
Date type:
Blob Type:

###Common constraints

NOT NULL
DEFAULT
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY

##Database transaction
###Meaning
Through a group of logical operation units (a group of DML - sql statements), the data is switched from one state to another

###Characteristics
(ACID)
Atomicity: either execute or roll back
Consistency: ensure that the data status is consistent before and after operation
Isolation: when multiple transactions operate the same data of the same database at the same time, the execution of one transaction is not disturbed by another transaction
Persistence: once a transaction is committed, the data will be persisted locally unless it is modified by other transactions

Related steps:

1,Open transaction
2,Write a set of logical operation units (multiple) of a transaction sql Statement)
3,Commit transaction or rollback transaction

###Classification of transactions:

Implicit transactions, without obvious signs of opening and ending transactions

such as
insert,update,delete The statement itself is a transaction

Explicit transaction, with obvious signs of opening and ending transactions

	1,Open transaction
	Cancel the function of automatic transaction submission
	
	2,Write a set of logical operation units (multiple) of a transaction sql Statement)
	insert
	update
	delete
	
	3,Commit transaction or rollback transaction

###Keywords used

set autocommit=0;
start transaction;
commit;
rollback;

savepoint  breakpoint
commit to breakpoint
rollback to breakpoint

###Isolation level of transaction:

How does the transaction concurrency problem occur?

When multiple transactions operate on the same data of the same database at the same time

What are the concurrency problems of transactions?

Dirty read: one transaction reads uncommitted data from another transaction
 Non repeatable reading: in the same transaction, the data read multiple times is inconsistent
 Phantom reading: when one transaction reads data, another transaction updates, resulting in the first transaction reading data that has not been updated

How to avoid the concurrency of transactions?

By setting the isolation level of transactions
1,READ UNCOMMITTED
2,READ COMMITTED Dirty reading can be avoided
3,REPEATABLE READ It can avoid dirty reading, non repeatable reading and some unreal reading
4,SERIALIZABLE It can avoid dirty reading, non repeatable reading and unreal reading

Set isolation level:

set session|global  transaction isolation level Isolation level name;

View isolation level:

select @@tx_isolation;

##View
Meaning: understood as a virtual table

Differences between views and tables:

	Mode of use	Occupy physical space

view	Exactly the same	Not occupied, only saved sql logic

surface	Exactly the same	occupy

Benefits of view:

1,sql Statement improves reusability and efficiency
2,And tables are separated to improve security

###View creation
Syntax:
CREATE VIEW view name
AS
Query statement;
###View addition, deletion, modification and query
1. View view data ★

SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';

2,Insert data for view
INSERT INTO my_v4(last_name,department_id) VALUES('Phyllostachys pubescens',90);

3,Modify the data of the view

UPDATE my_v4 SET last_name ='Menggu' WHERE last_name='Phyllostachys pubescens';


4,Delete data from view
DELETE FROM my_v4;

###Some views cannot be updated
sql statements containing the following keywords: grouping function, distinct, group by, having, union, or union all
Constant view
Select contains subqueries
join
from a view that cannot be updated
The subquery of the where clause references the table in the from clause
###Update of view logic
#Mode 1:
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;

#Mode 2:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;

SELECT * FROM test_v7;

###View deletion
DROP VIEW test_v1,test_v2,test_v3;
###View of view structure
DESC test_v7;
SHOW CREATE VIEW test_v7;

##Stored procedure

Meaning: a set of precompiled sql statements
Benefits:

1,Improved sql The reusability of statements reduces the pressure of developers
2,Improved efficiency
3,Reduced transmission times

Classification:

1,No return no parameter
2,Only band in Type, no return parameter
3,Only band out Type, return without parameter
4,Both belt in Another belt out，Return with reference
5,belt inout，Return with reference
 be careful: in,out,inout Can take multiple in a stored procedure

###Create stored procedure
Syntax:

create procedure Stored procedure name(in|out|inout Parameter name parameter type,...)
begin
	Stored procedure body

end

Similar to method:

Modifier return type method name(Parameter type parameter name,...){

	Method body;
}

be careful

1,A new end tag needs to be set
delimiter New end tag
 Example:
delimiter $

CREATE PROCEDURE Stored procedure name(IN|OUT|INOUT Parameter name parameter type,...)
BEGIN
	sql Statement 1;
	sql Statement 2;

END $

2,There can be more than one stored procedure in the body sql Statement, if only one sql Statement can be omitted begin end

3,The meaning of the symbol before the parameter
in:This parameter can only be used as input (it cannot be used as return value)
out: This parameter can only be used as output (this parameter can only be used as return value)
inout: It can do both input and output

#Call stored procedure
call stored procedure name (argument list)
##Functions

###Create function

Learned functions: LENGTH, SUBSTR, CONCAT, etc
Syntax:

CREATE FUNCTION Function name(Parameter name parameter type,...) RETURNS Return type
BEGIN
	Function body

END

###Call function
SELECT function name (argument list)

###Differences between functions and stored procedures

		keyword		Call syntax	Return value			Application scenario
 function		FUNCTION	SELECT function()	It can only be one		It is generally used when the query result is a value and returned, when there is only one returned value
 stored procedure	PROCEDURE	CALL stored procedure()	There can be 0 or more		Generally used for updates

##Process control structure

###System variable
1, Global variable

Scope: valid for all sessions (connections), but not across restarts

View all global variables
SHOW GLOBAL VARIABLES;
View some system variables that meet the criteria
SHOW GLOBAL VARIABLES LIKE '%char%';
View the value of the specified system variable
SELECT @@global.autocommit;
Assign a value to a system variable
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

2, Session variable

Scope: valid for current session (connection)

View all session variables
SHOW SESSION VARIABLES;
View some session variables that meet the criteria
SHOW SESSION VARIABLES LIKE '%char%';
View the value of the specified session variable
SELECT @@autocommit;
SELECT @@session.tx_isolation;
Assign a value to a session variable
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

###Custom variable
1, User variable

Declare and initialize:

SET @Variable name=value;
SET @Variable name:=value;
SELECT @Variable name:=value;

Assignment:

Method 1: generally used to assign simple values
SET Variable name=value;
SET Variable name:=value;
SELECT Variable name:=value;


Method 2: generally used to assign field values in a table
SELECT Field name or expression INTO variable
FROM surface;

use:

select @Variable name;

2, Local variable

Statement:

declare Variable name type[ default Value];

Assignment:

Method 1: generally used to assign simple values
SET Variable name=value;
SET Variable name:=value;
SELECT Variable name:=value;


Method 2: generally used to assign field values in a table
SELECT Field name or expression INTO variable
FROM surface;

use:

select Variable name

The difference between the two:

		Scope			Define location		grammar

The user variable is marked with @ anywhere in the current session without specifying the type
A local variable defines its BEGIN END. Generally, the first sentence of BEGIN END does not need to add @, and the type needs to be specified

###Branching
1, if function
Syntax: if (condition, value 1, value 2)
Features: can be used in any position

2, case statement

Syntax:

Case 1: similar to switch
case expression
when Value 1 then Result 1 or statement 1(If it is a statement, you need to add a semicolon) 
when Value 2 then Result 2 or statement 2(If it is a statement, you need to add a semicolon)
...
else result n Or statement n(If it is a statement, you need to add a semicolon)
end [case](If it's on begin end Need to add case，If you put it select (not required later)

Case 2: similar to multiple if
case 
when Condition 1 then Result 1 or statement 1(If it is a statement, you need to add a semicolon) 
when Condition 2 then Result 2 or statement 2(If it is a statement, you need to add a semicolon)
...
else result n Or statement n(If it is a statement, you need to add a semicolon)
end [case](If it's on begin end Need to add case，If you put it select (not required later)

characteristic:
Can be used anywhere

3, If else if statement

Syntax:

if Case 1 then Statement 1;
elseif Case 2 then Statement 2;
...
else sentence n;
end if;

characteristic:
Can only be used in begin end!!!!!!!!!!!!!!!

Comparison of the three:
Application occasion
if function simple double branch
Multiple branches of case structure equivalence judgment
Multiple branches of interval judgment of if structure

###Circulation

Syntax:

[Label:] WHILE Cycle condition  DO
	Circulatory body
END WHILE [Label];

characteristic:

Can only be placed in BEGIN END inside

If you want to match leave Jump statements need to use labels, otherwise you can not use labels

leave be similar to java Medium break Statement, jump out of the loop!!!