​

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), is used for management DB Data in
3,SQL:Structured query language for and DBMS Language of communication

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 properties that define how data is stored in a table, similar to java Design of "class" in.
4,A table consists of columns, which we also call fields. All tables consist of one or more columns, each similar java Properties in
5,The data in the table is stored in rows, and each row is similar to java Objects in.

Introduction and installation of MySQL products

Click to view

Start and stop of MySQL service

Method 1: Computer - right click management - Service
 Mode 2: run as administrator in the command line window
net start Service name (start service)
net stop Service name (stop service)

Login and logout of MySQL service

Method 1: through mysql Built in client
 Limited to root user

Mode 2: Pass 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

- View all types of data
? data types

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

Operate on columns in a table

-- Add a column
alter table tb_student add column stu_addr varchar(50) default '' comment 'address'
-- Delete a column
alter table tb_student drop colum stu_addr
-- Modify a column and redefine the column name directly
alter table tb_student change column stu_sex stu_gender boolenan default 1 comment 'Gender'
-- Modify the data type of the column
alter table tb_student modify column stu_gender char(1) default 'male' comment 'Gender'

Syntax specification of MySQL

1.Case insensitive,However, 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 note:#Annotation text
	Single line note:-- Annotation text
	Multiline comment:/* Annotation text  */

Language classification of SQL

DQL(Data Query Language): Data query language
	select 
DML(Data Manipulate Language):Data manipulation language
	insert ,update,delete

DDL (data definition language): Data Definition Language
​ create,drop,alter

​ ------------create database school default charset utf8/uf8mb4; (mysql default utf8)

DCL: data control language

​ grant revoke

TCL (Transaction Control Language): Transaction Control Language
​ commit,rollback
​

Common commands for SQL

engine = innodb  Specify engine
show collation like 'utf8%'; Show all collations
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

-- 
-- Delete table
drop database if exists `school`;
-- Create table
create database `school` default character set utf8mb4;
-- Switch database
use `school`;
-- Create table
create table `tb_student` 
(
	`stu_id` integer not null,
	`stu_name` varchar(10) not null,
	`stu_sex` char(1) default 'M' not null,
	`stu_birth` date,
	primary key (`stu_id`)
);

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 
	Fields to query|expression|constant value|function
from 
	surface
where 
	condition ;

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

I. 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 round
rand random number
floor rounding down
ceil rounded up
mod remainder
truncate
3. Date function
now current system date + time
Current system date
curtime current system time
str_to_date converts characters to dates
date_format converts the date to characters
4. Process control function
if handle double branch
case statements handle 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, Aggregate 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 fields, grouping functions
from surface
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. Result of equivalent connection = intersection of multiple tables
2.n table connection, at least n-1 connection conditions are required
3. There is no order requirement for multiple tables regardless of primary and secondary
4. Generally alias the table to improve readability and performance

2, sql syntax: connect 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 use limit 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 all union and merge

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 data
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 data

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[ [filter criteria]

Delete data

Method 1: delete statement

**Deleting a 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 sentence

	truncate table Table name

Differences between the two methods [key points]

#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 without logging. 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 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 data types
	Integer:
		int
	Decimal:
		float --->folat/double
		Fixed point type---> decimal--->decimal(5,2)
	character:
		- char : Limit character
		--varchar: Variable length string
		- longtext    longblob(Binary large object)---->Release resource path
	Date type:
		- date  time datetime
		-- timestamp(time stamp)
	Blob Type:
		- boolean --->tinyint(1):--->0/1
		
	Enumeration type: 
		enum :  'M'/'F'

Common constraints

NOT NULL
DEFAULT
UNIQUE
CHECK
PRIMARY KEY : Primary key --->A field that uniquely identifies a record
FOREIGN KEY : Foreign key --->The primary key of other tables to establish the connection between tables

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

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 sql statements) of transactions
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;  Auto submit settings
start transaction;  Start transaction
commit; Submit
rollback; RollBACK 

view

Meaning: understood as a virtual table
Differences between views and tables:

	Differences in usage	Occupy physical space
surface	Exactly the same	occupy
view	Exactly the same	Only sql logic is saved

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 view data
​ DELETE FROM my_v4;
About view updates
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)

function

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

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
User variable	Current session	The @ sign is added anywhere in the session without specifying the type
local variable	Define it in BEGIN END	Generally, the first sentence of BEGIN END does not need to add @, and the class needs to be specified

branch

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

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

Simple summary

MySQL is a database used more frequently in relational databases, which is worth our time to study. For those who take the post of data analyst, the most important thing in MySQL is DQL (data query language). In most of their work, they need to get data from the database for data analysis. However, for beginners, SQL statements are not so easy and pleasant to write. They are often understood at a glance and some are discarded. SQL statement can be regarded as the simplest programming language in the world. You only need to know few words to understand it, but generally this language is very flexible and needs strong logical inference ability.

Learning quotations

Although the road is endless and faraway, I still want to pursue the truth in the world. I'm not afraid of slow, I'm afraid of standing.