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 MySQL Product introduction MySQL Product installation ★ MySQL Start and stop of services ★ MySQL Login and logout of services ★ MySQL Common commands and syntax specifications for V DQL Language learning ★ Basic query ★ Condition query ★ Sort query ★ Common functions ★ Grouping function ★ Grouping query ★ join query ★ Subquery √ 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), 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
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 exit 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
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.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 Define Languge): 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 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
1, Single line function
1,Character function concat Splicing substr Intercept substring upper Convert to uppercase lower Convert to lowercase trim Remove the spaces and characters specified before and after ltrim Go to the left space rtrim Go to the right space replace replace lpad padding-left rpad Right fill instr Returns the index of the first occurrence of a substring length Get 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 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 | In front of group by | where |
| Filter after grouping: | Grouped result set | Behind group by | 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: connect through the 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 select statement is nested in a query statement. The nested select statement is called sub query or inner 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
Import: Union, union, 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
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
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 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 you insert data again, the data starts 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 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 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
Containing the following keywords sql Statements: grouping functions distinct,group by,having,union perhaps union all Constant view Select Contains subqueries join from A view that cannot be updated where The subquery of clause references from Table in 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
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
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
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
loop
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!!!