DQL language learning - joint query

DQL language learning (Data Query Language)

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

Vi DML (data manipulation 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

modify

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 ((data define language))

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 Field name [field type];

#① 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 set of logical operation units (a set DML-sql Statement) to switch data from one state to another

characteristic

(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 transactions 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;

Deletion of view

DROP VIEW test_v1,test_v2,test_v3;

View of view structure

DESC test_v7;
SHOW CREATE VIEW test_v7;

Stored procedures and functions

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: it is generally used to assign field values in the 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!!!