MySQL based

A SQL.

Since my internship a few years ago, I have successively written a lot of Markdown notes. Most of my early notes were made by myself and shared with some big bull blogs at B station. The early notes system was relatively clear, which was also my learning and growth route.

The following notes also include some sharing with industry leaders, the official website forum,github to learn a little deeper things and some work experience and stumbles. In the future, I will organize my early notes and my current experiences in study and work into modules and share them irregularly. If you have different opinions, I hope you can express your opinions in the comments section.

I hope we can keep this thinking and love forever.

1. The concept

1. Structured Query Language (SQL

DML data manipulation language is used to manipulate data contained in a databaseINSERT UPDATE DELETEThe DDL data definition Language is used for operations such as creating and deleting database objectsCREATE DROP ALTERThe DQL data query language is used to query data in a databaseSELECTDCL data control language is used to control access permissions, access permissions, and so on for database componentsGRANT COMMIT ROLLBACK
Copy the code

2, including arithmetic operators, assignment operators, comparison operators, logical operators

Arithmetic operator A symbol used for comparisons and mathematical operations between columns or variables

The operator	Said Ming
+	To find the sum of two numbers or expressions, such as 6+8
–	To find the difference between the subtraction of two numbers or expressions
*	To find the product of two numbers or expressions
/	To find the quotient of the division of two numbers or expressions, such as the value of 5/3 is 1
%	Modular operation, find the remainder of the division of two numbers or expressions, such as: 5% of 3 is 2

The assignment operator

The operator	Said Ming
=	To assign a number or variable or expression to another variable, as set @name=’ Wanghua ‘

Logical operator

The operator	Said Ming
AND	Returns true if and only if both Boolean expressions are true.
OR	Return false if and only if both Boolean expressions are false.
NOT	Boolean expressions take the opposite value

Comparison operator

The operator	Said Ming
=	Equals, for example, age=23
>	Greater than, for example, price>100
<	Less than
<>	Is not equal to
> =	Greater than or equal to
< =	Less than or equal to
! =	Does not equal (non-SQL-92 standard)

3. System database

Information_schema: information about database objects in major storage systems, such as user table information, field information, permission information, character set information, and partition information. Performance_schema: performance parameters of the primary storage database server mysql: user permission information of the primary storage system test: a test database automatically created by the mysql database management system. Any user can use the databaseCopy the code

2.DDL

Database definition language

 show databases;   //Viewing a Databasecreate database MySchool; //Create database use myschool;//Select databasedrop database myschool; //Delete database myschool;Copy the code

1. Data types

type	instructions	Value range	Storage requirements
TINYINT	Very small data	Signed value: -27 to 27-1 Unsigned value: 0 to 28-1	1 byte
SMALLINT	Smaller data	Signed value: -215 to 215-1 Unsigned value: 0 to 216-1	2 –
MEDIUMINT	Medium size data	Signed value: -223 to 223-1 Unsigned value: 0 to 224-1	3 bytes
INT	Standard integer	Signed value: -231 to 231-1 Unsigned value: 0 to 232-1	4 bytes
BIGINT	Larger integer	Signed value: -263 to 263-1 Unsigned value: 0 to 264-1	8 bytes
FLOAT	Single-precision floating point number	Plus or minus 1.1754351 e – 38	4 bytes
DOUBLE	A double – precision floating – point number	E – 308-2.2250738585072014 mm	8 bytes
DECIMAL	A floating point number as a string	Decimal (M, D)	M + 2 bytes

2, create table and drop table

1, create a tablecreate table1. A single line of comment in mysql:-- Comment contentMulti-line comments:/ *... * /  

 CREATE TABLE [IF NOT EXISTS] table name (field1Data type [field attribute|[index][comment]... Field n Data type [Field attribute|Constraint [index][comment])[table type][table character set][comment];CREATE TABLE `student`(
    `studentNo` INT(4) NOT NULL COMMENT 'student id' PRIMARY KEY,
	`loginPwd` VARCHAR(20) NOT NULL COMMENT 'password',  
	`studentName` VARCHAR(50) NOT NULL COMMENT 'Student name',
	`sex` CHAR(2) DEFAULT 'male' NOT NULL  COMMENT 'gender', 
	`gradeId` INT(4)  UNSIGNED COMMENT 'Grade Number', 
	`phone` VARCHAR(50)  COMMENT 'Contact Number',
	`address` VARCHAR(255)  DEFAULT 'Address unknown'COMMENT 'address', 	
    `bornDate` DATETIME  COMMENT 'Time of birth',
	`email` VARCHAR(50) COMMENT'Email account',
	 ` identityCard ` VARCHAR(18)  UNIQUE KEY COMMENT 'Id Number'
) COMMENT='Student List';


2Delete table,drop tableThe name of the tableDROP TABLE [IF  EXISTSThe name of the table;DROP TABLE IF EXISTS `student`;
Copy the code

3, table constraints

The name of the	The keyword	instructions
Not null constraint	NOT NULL	Fields cannot be empty
The default constraints	DEFAULT	Assign a default value to a field
The only constraints	UNIQUE KEY(UK)	The value of the setting field is uniquely allowed to be null, and can contain multiple null values, which can be set to not NULL to be non-null
Primary key constraint	PRIMARY KEY(PK)	Setting this field as the primary key of the table uniquely identifies the table records
Foreign key constraints	FOREIGN KEY(FK)	To establish a relationship between two tables, you need to specify which field of the main table is referenced
Automatic growth	AUTO_INCREMENT	Setting the default increment of each column by 1 is usually used to set the primary key

Coding CHARSET='gbk'	set names 'gbk'Tell the server GBK encoded dataCopy the code

4. Modify the table

1Alter table name alter table nameALTER TABLEOld table name RENAME [TO] new table name; # add fieldALTER TABLEThe name of the tableADDField name Data type [attribute]; # change fieldALTER TABLETable name CHANGE Old field name new field name data type [attribute]; # delete fieldALTER TABLEThe name of the tableDROPThe field name. Check the table definitionDESCRIBE student;
	DESC student;
	
	DROP TABLE IF EXISTS`demo01`; # to create tableCREATE TABLE  `demo01`(
       `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
       `name` VARCHAR(8) NOT NULL); Alter table nameALTER TABLE`demo01` RENAME `demo02`; # add fieldALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL; # change fieldALTER TABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL; # delete fieldALTER TABLE ` demo02 ` DROP `password`;


2Key Adds the primary keyALTER TABLEThe name of the tableADD CONSTRAINT[Primary key name]PRIMARYKEY table name (primary KEY);ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARYKEY `grade`(`gradeId`); Add a foreign keyALTER TABLEThe name of the tableADD CONSTRAINT[Foreign key name]FOREIGNKEY (foreign KEY field)REFERENCESAssociated table names (associated fields);ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) 
REFERENCES `grade` (`gradeId`);

Copy the code

3.DML

Storage engines

2, MyISAM and InnoDB types main differences (figure) Applicable occasions use MyISAM: no transaction, small space, mainly for query access use InnoDB: More delete, update operation, high security, transaction processing and concurrency controlCopy the code

The name of the	InnoDB	MyISAM
Transaction processing	support	Does not support
Row lock	support	Does not support
Foreign key constraints	support	Does not support
The full text indexing	Does not support	support
Tablespace size	Larger, about 2 times	smaller

	3# Check the database storage engineSHOWENGINES; View the current default storage engineSHOW VARIABLES LIKE 'storage_engine%'; Create tables using MYISAM storage engineCREATE TABLE student(
		)ENGINE=MYISAM;
Copy the code

2. Storage location of data table

MYD: data file.MYI: index file InnoDB table file. FRM: table structure definition file IBDatA1 fileCopy the code

3. Add data

	1, add a dataINSERT INTOTable name [(table of field names)]VALUES(Value list);INSERT INTO student (studentNo,loginPwd,studentName,sex,gradeId,phone,address,bornDate,email,identityCard)
	VALUE (1003.'333333'.'ccc'.'male'.1.'33333333'.'Beijing'.'1990-10-8'.'[email protected]'.'33333333');

	INSERT INTO student(studentNo,loginPwd,studentName,sex,gradeId,bornDate)
	VALUE (1004.'444444'.'ddd'.'male'.2.'20000404');
	
	2, Add multiple pieces of dataINSERT INTONew table (list of field names)VALUES(list of values1), (list of values2),... ,(value list n);INSERT INTO SUBJECT(subjectName,classHour,gradeId)
	VALUES ('java'.200.1), ('html'.100.2), ('mysql'.100.2);

	3Create a new table and insert the data into the new table (only once)CREATE TABLE studentInfo (SELECT studentNo,loginPwd,studentName,sex FROM student);

	4Insert data from a table into an existing table (multiple times, data redundancy)INSERT INTO studentinfo (SELECT studentNo,loginPwd,studentName,sex FROM student);
Copy the code

4. Modify data

UPDATE the table nameSETfield1=value1, the field2=value2,... , the field n=The value of n [WHEREconditions1 ANDconditions2];
	
	1UPDATE student with conditionalSET address="Changping Beijing"WHERE address="Beijing Haidian ";2UPDATE student; UPDATE studentSET address="Guangzhou ", phone=10010, loginPwd=Awesome! WHERE studentNo=1002 AND studentName='zhao';
Copy the code

5. Delete data

1,DELETE FROMThe table name [WHEREConditions]; Delete data (all delete)DELETE FROMstudentinfo; Conditions for deleting a data stripDELETE FROM studentinfo WHERE studentNo=1001;
	
2,TRUNCATE TABLEThe name of the table. Delete allTRUNCATE TABLE studentinfo;

3,TRUNCATETable structure and its fields, constraints, indexes remain unchanged, execution speed ratioDELETEStatement is fastdeleteDeleting a row generates logs for each row, which is inefficient.truncateA one-timeTRUNCATEDelete all of them, and almost no log information is generateddelete 和 truncateRetains the structure of the table (fields, indexes, and constraints remain unchanged),deleteDoes not reset the increment column,truncateResets the increment columnCopy the code

4.DQL

1. Query data

A query produces a virtual table that sees the result as a table, but the result is not actually stored. Each time the query is executed, the data is extracted from the table and displayed as a table.SELECT    <The column name|expression|function|constant> 
FROM      <The name of the table> 
[WHERE    <Query condition expression>] 
[ORDER BY <The name of the sorted column>[ASCorDESC]].1Select * from student table*All the columnsSELECT * FROM student;

2, query partial band conditionsSELECT studentNo,loginPwd,studentName FROM student WHERE studentNo=1001;

3, column aliasas.asCan be omittedSELECT studentNo AS 'student id',loginPwd AS 'password',studentName AS 'name' FROM student;

4Query student id, password, and name into a single column using concat();SELECT CONCAT(studentNo,The password for 'is'.,loginPwd,'Name is',studentName) AS 'information' FROM student;

5, query email asNullStudents useisDon't use=
	SELECT * FROM student WHERE email IS NULL;

6, query definition constant column to add school classSELECT studentNo,loginPwd,studentName ,'qian feng' AS 'school' FROM student;
Copy the code

2, SQL execution sequence

1. The handwritten:

2. The machine to read:

With the update of Mysql version, its optimizer is also constantly upgraded. The optimizer will analyze the performance consumption caused by different execution sequences and dynamically adjust the execution sequence. Here is the order of the most common queries:

3. Conclusion:

3. Common functions

Common functions – aggregate functions

The function name	role
AVG()	Returns the average value of a field
COUNT()	Returns the number of rows for a field
MAX()	Returns the maximum value of a field
MIN()	Returns the minimum value of a field
SUM()	Returns the sum of a field

Common functions – string functions

The function name	As with	For example
CONCAT(str1, str1… strn)	String conjunction	SELECT CONCAT(‘My’,’S’,’QL’); Returns: MySQL
INSERT(str, pos,len, newstr)	String substitution	SELECT INSERT(‘ this is SQL Server database ‘, 3,10,’MySQL’); Return from 1: this is the MySQL database,
LOWER(str)	Converts a string to lowercase	SELECT LOWER(‘MySQL’); Returns: mysql
UPPER(str)	Uppercase the string	SELECT UPPER(‘MySQL’); Returns: MYSQL
SUBSTRING (str,num,len)	String interception	SELECT SUBSTRING (‘ JavaMySQLOracle ‘, 5, 5); Return from 1: MySQL

Commonly used functions – time and date functions

The function name	role	Examples (results related to current time)
CURDATE()	Get the current date	SELECT CURDATE(); Returns: the 2016-08-08
CURTIME()	Get the current time	SELECT CURTIME(); Returns: 19:19:26
NOW()	Gets the current date and time	SELECT NOW(); Return: 2016-08-08 19:19:26
WEEK(date)	Returns the date date as the week of the year	SELECT WEEK(NOW()); Returns: 26
YEAR(date)	Returns the year of the date date	SELECT YEAR(NOW()); Returns: 2016
HOUR(time)	Returns the hour value of time	SELECT HOUR(NOW()); Returns: 9
MINUTE(time)	Returns the minute value of time	SELECT MINUTE(NOW()); Returns: 43
DATEDIFF(date1,date2)	Returns the number of days between date parameters date1 and date2	SELECT DATEDIFF(NOW(), ‘2008-8-8’); Returns: 2881
ADDDATE(date,n)	Computes the date argument date plus the date in n days	SELECT ADDDATE(NOW(),5); Return: 2016-09-02 09:37:07

Commonly used functions — mathematical functions

The function name	As with	For example
CEIL(x)	Returns the smallest integer greater than or equal to the value x	SELECT CEIL(2.3) returns: 3
FLOOR(x)	Returns the largest integer less than or equal to the value x	SELECT FLOOR(2.3) returns: 2
RAND()	Returns a random number between 0 and 1 (including 0 and 1)	SELECT RAND() returns 0.5525468583708134

4. Subquery

	1, subquery (if the comparison operator is used, the result of the subquery can only have one value, one column)SELECT * FROM student WHERE borndate>(SELECT borndate FROM student WHERE studentname='aaa'); Query the highest and lowest scores of students who took the most recent Java examSELECT MAX(studentResult),MIN(studentResult)  FROM result 
		WHERE examDate=(SELECT MAX(examdate) FROM result 
			WHERE subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java'))
		AND subjectNo=(SELECT subjectNo FROM SUBJECT WHERE subjectName='java');

	2,INCommon subqueriesINReplace subqueries equal to (=)INThe subsequent subquery can return multiple records to query Java scores for60Student Information ofSELECT * FROM student WHERE studentNo 
		IN (SELECT studentNo FROM result WHERE studentResult=60 AND subjectNo=1); Query not participated2017- 8 -- 19Java exam student listSELECT * FROM student WHERE studentNo 
		NOT IN(SELECT studentNo FROM result WHERE examDate='2017-8-19' AND subjectNo=1);
Copy the code

5. EXISTS sub query

	SELECT...FROMThe name of the tableWHERE EXISTS(subquery); The subquery returns a result:EXISTSThe sub-query result isTRUENo result is returned from the outer query:EXISTSThe sub-query result isFALSE, the outer query is not executedSELECTStatement,WHERE,GROUP BY,HAVINGClauses are executed in the following order:1,WHEREClause removes data from the data source that does not meet its search criteria;2,GROUP BYClause collects rows of data into groups;3,HAVINGClause removes groups of rows that do not meet its group search criteria.4,Order BY    
	5And the LimitCopy the code

6, grouping, sorting, paging query

The GROUP BY clause

	SELECT...FROM  <The name of the table>  
	WHERE...GROUP BY...SELECT...FROM  <The name of the table>
	WHERE...GROUP BY...HAVING...where 和havingThe difference betweenWHEREClause is used to filterFROMThe row generated by the operation specified in the clauseGROUP BYClauses are used for groupingWHEREClause outputHAVINGClauses are used to filter rows from grouped resultsSELECT subjectNo ,AVG(studentResult) AS 'Average score' 
	FROM result 
	GROUP BY subjectNo
	HAVING AVG(studentResult)> =80;
Copy the code

The ORDER BY clause

ORDER BYClause to display query results in a certain order to reduce the results10%After add5Score, and then query the score, and according to the score from high to low sortSELECT `studentNo` ASStudent ID (studentResult)*0.9+5 ) ASComprehensive performanceFROM `result`
WHERE (`studentResult`*0.9+5) > =60
ORDER BY studentResult DESC;
Copy the code

LIMIT clause

SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM))))) [LIMIT [position offset,] line number];Copy the code

7, the JOIN

1, Seven JOIN

What is shared and what is exclusive? Common: all the names that satisfy a.deptid = B. ID are common A unique: all the data in table A that do not satisfy the join relation of A. deptid = B. ID refer to the join diagram

Build table SQL:

    CREATE TABLE `t_dept` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `deptName` VARCHAR(30) DEFAULT NULL,
        `address` VARCHAR(40) DEFAULT NULL.PRIMARY KEY (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    CREATE TABLE `t_emp` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
         `name` VARCHAR(20) DEFAULT NULL,
         `age` INT(3) DEFAULT NULL,
         `deptId` INT(11) DEFAULT NULL.PRIMARY KEY (`id`),
         KEY `fk_dept_id` (`deptId`)
         #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



    INSERT INTO t_dept(deptName,address) VALUES('hua'.'hua');
    INSERT INTO t_dept(deptName,address) VALUES('gai'.'the luoyang');
    INSERT INTO t_dept(deptName,address) VALUES('emei'.'Mount Emei');
    INSERT INTO t_dept(deptName,address) VALUES('wudang'.'Wudang Mountain');
    INSERT INTO t_dept(deptName,address) VALUES('zoroastrianism'.'Bright Top');
    INSERT INTO t_dept(deptName,address) VALUES('the shaolin'.'Shaolin Temple');

    INSERT INTO t_emp(NAME,age,deptId) VALUES('The wind is clear'.90.1);
    INSERT INTO t_emp(NAME,age,deptId) VALUES(Yue Buqun.50.1);
    INSERT INTO t_emp(NAME,age,deptId) VALUES(Linghu Chong.24.1);

    INSERT INTO t_emp(NAME,age,deptId) VALUES('Hong Qi Gong'.70.2);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('look crazy'.35.2);

    INSERT INTO t_emp(NAME,age,deptId) VALUES('Extinct master tai'.70.3);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('Zhou Zhiruo'.20.3);

    INSERT INTO t_emp(NAME,age,deptId) VALUES('Zhang SAN Feng'.100.4);
    INSERT INTO t_emp(NAME,age,deptId) VALUES('Zhang Wuji'.25.5);
    INSERT INTO t_emp(NAME,age,deptId) VALUES(Trinket.18.null);
Copy the code

Seven join:

 
1Tables A and B are in commonselect * from t_emp a inner join t_dept b on a.deptId = b.id;
 
2Tables A and B are in common+A uniqueselect * from t_emp a left join t_dept b on a.deptId = b.id;
 
3Tables A and B are in common+B's uniqueselect * from t_emp a right join t_dept b on a.deptId = b.id;
 
4A uniqueselect * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 
 
5B's uniqueselect * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;  
 
6AB # all MySQLFull JoinImplementation because MySQL does not supportFULL JOINHere are the alternatives #left join + union(Duplicate data can be removed)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId =B.id cannot take into account the small table driving the large table for the sake of federation. Can only useright join. Make sure the numbers are consistent.7A unique+B's uniqueSELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;
Copy the code

2. Internal connection

Inner joins alias tables by matching rows in two tables with values in each table's generic column using the comparison operatorSELECT s.`studentNo`,s.`studentName`,r.`subjectNo`,r.`studentResult` 
		FROM student AS s INNER JOIN result AS r 
		ON s.`studentNo`=r.`studentNo`; Traditional inner connectionSELECT s.`studentNo`,s.`studentName`,r.`subjectNo`,r.`studentResult` 
		FROM result AS r ,student AS s 
		WHERE s.`studentNo`=r.`studentNo`;
Copy the code

3. External connection

The primary table (left table) matches student table by tableresultThe data in the1.Matches and returns to the result set2.No matching,NULLReturn value to result set left outer join (left main table)SELECT s.`studentNo`,s.`studentName`,r.`subjectNo`,r.`studentResult`
	FROM student AS s LEFT  JOIN result  AS r
	ON s.`studentNo`=r.`studentNo`
Copy the code

4, Union connection

	union: Merge connectionsSELECT * from t_emp a LEFT JOIN t_dept b on a.deptId = b.id
    UNION
    SELECT * from t_emp a RIGHT JOIN t_dept b on a.deptId = b.id;
Copy the code

2. The transaction

1. The concept:

A series of operations on the business logic either all succeed or all fail. For example, in a relational database, a transaction can be a SINGLE SQL statement or a bunch of SQL statements or an entire program. Mysql supports transactions by default and automatically manages transactions. (mysql can handle this manually)

What can business do? Ensure data operation is accurate and consistent.

2. Methods related to transaction operations

How does the DOS Black Window handle transactions? Related methods

Start transaction begin; Or start the transaction. Close transaction (commit transaction) commit; Rollback;Copy the code

How does JDBC operate transactions? Related methods Java code related apis

Enable transaction connection.setautoCommit (false); Close transaction (commit transaction) connection.mit (); Rollback transaction connection.rollback ();Copy the code

Take transfer as an example:

public class TransformServiceImpl implements TransformService {
	TransformDao transformDao = new TransformDaoImpl();
	@Override
	public void transformAccount(int fromId, int toId, double money) {
	Select fromId and toId from fromId and toId; select fromId and toId from toId; select fromId and toId from fromId and toId; Update fromId and toId Money update (query Money by ID) */
		try {
            // Start the transaction
            ManagerTransactionUtils.startTransaction();
            double   fromMoney = transformDao.queryMoneyById(fromId);
            double   toMoney = transformDao.queryMoneyById(toId);
            // add and subtract
            fromMoney = fromMoney-money;
            toMoney =toMoney+money;
            int rowCount1 =  transformDao.updateMoneyById(fromId, fromMoney);
            // Simulate an exception
            //int i=10/0;
            int rowCount2=  transformDao.updateMoneyById(toId, toMoney);
            if (rowCount1>0&&rowCount2>0) {
                System.out.println("Transfer successful");
            }else {
                System.out.println("Transfer failed");
            }
            // Close the transaction
            ManagerTransactionUtils.closeTransaction();
        } catch (Exception e) {
            // If an exception occurs, the object is rolled back directlyManagerTransactionUtils.rollBackTransaction(); }} ManagerTransactionUtils utility class:public class ManagerTransactionUtils {
	// To get a Connection, make sure that the same thread uses the same Connection.
	ThreadLocal stores only the data of the thread to which it is bound, and that data is private static
	//ThreadLocal stores thread-local variables
	private static  ThreadLocal<Connection> threadLocal =new ThreadLocal<>();
	public  static Connection getConnection(a) {
		//1. Start with threadLocal
		Connection connection = threadLocal.get();
		if (connection==null) {
			// Get the connection from the data source
			 connection =C3P0Utils.getConnection();
			 // Use threadLocal to store the Connection used
			 threadLocal.set(connection);
		}
		return connection;
	}
	// Start the transaction
	public static void  startTransaction(a) {
		getConnection().setAutoCommit(false);
	}
	// Close the transaction
	public static void  closeTransaction(a) {
		getConnection().commit();
	}
	// Rollback the transaction
	public static void  rollBackTransaction(a) { getConnection().rollback(); }}Copy the code

3. Transaction features (4) :

1. Atomicity: A transaction is the smallest unit and cannot be divisible. The operations in the transaction either all succeed or all fail. 2. Consistency: A transaction remains consistent from one state to another. Take money transfers. The total amount before and after transfer remains unchanged. 3. Isolation: When multiple users concurrently access the database, each operation is independent and each transaction does not affect each other. 4. Durability: Data changes to the database are permanent once a transaction is committed. If it changes again it starts a new transaction.Copy the code

4. Transaction concurrency problems:

Dirty read: indicates that a transaction has read data from an uncommitted transaction. Non-repeatable read: Read the same data multiple times in the same transaction. Multiple reads give different results. Example: Transaction A reads data, and transaction B modifies the data that transaction A is reading. As A result, transaction A reads different data for several times. Phantom read (virtual read) : When transaction A operates, the entire table is involved. Transaction B inserts or deletes the table, which affects transaction A. (Focus on the entire table) Example: A company has 500 people, A transaction query company 500 people, not finished, A new person joined the company, B transaction added new people to the number of people, A re-read the number of people changed. Set transaction Isolation level One of the isolation levelsCopy the code

5. Isolation level: From low to high

	1.Read uncommitted; There may be dirty reads, unrepeatable reads, and phantom reads.2.Read committed; Avoid dirty reads, but there may be non-repeatable reads, phantom reads.3.Repeatable Read The default mysql level avoids dirty reads and cannot be repeated, but phantom reads may occur. However, some versions of mysql have solved magic reading.4.Serializable serialiable: Avoids all transaction concurrency problems. The reason is excessive consumption of resources. You do not need to check the default mysql levelselect @@tx_isolation; Set the isolation level first:setTransaction Isolation Level One of the isolation levels temporary.Copy the code

Presentation:

	1.Demonstrate dirty reads: transactions1The transaction2(read uncommitted transaction3(read committed)
		1  begin;
		2  insertInsert data3                     			select * from. (Read about transactions1Uncommitted transaction, resulting in dirty read)select * from.4.  commit;5.                    			select * from. Indicates that committed data is read.select * from. Conclusion: If dirty reads occur, set the isolation level to resolve the problem. General Actual situation Read Uncommitted Basically no.2.Non-repeatable read: transactions1(read committed transaction2	
		1         begin;2         select * from user where id =1;
		3                                                      		update user set name="pp" where id =1;             
		4         select * from user where id =1; (Not repeatable)5         commit; Conclusion: Read committed simply ensures that committed data is read. The results of multiple reads in the same transaction are inconsistent and cannot be repeated read Repeat read: transaction1Repeatable read transaction2 (repeatable read )  	
		1          begin;2          select * from user where id =1;
		3                                                   			update user set name="ww" where id =1;             
		4          select * from user where id =1;
		5          commit;
		6	   select * from user where id =1; If non-repeatable reads occur, change the isolation level to REPEATable read3.Phantom reading Demo: transactions1Repeatable read transaction2 (repeatable read )  	
		1          begin;2          select * from user;
		3							    		insert into user (name,money) values(5.'yy'.20);  
		4          select * from user;
		5          insert into user (name,money) values(5.'yy'.20);   
		6	   select * from user; Conclusion: The second query data is the same as the first query data, but the database already has the database, insert the same data again directly error. In practice, there are almost no lost updates (understood) : Type 1 lost updates: In the absence of transaction isolation, two transactions update one data at the same time, but the second data update fails, resulting in two failed data modifications. The second type of lost update: a special case of non-repeatable reads. Two transactions read a row at the same time, and then one transaction changes and the other commits. The first change is invalid.Copy the code

Three variables.

1. System variables

Note: You need to add the global keyword for global variables and the session keyword for session variables. If this parameter is not specified, the default session level is used

Use steps: 1, to see all system variables show global | “session” the variables; 2, check the meet the conditions of a part of the system variables show global | “session” variables like ‘% char %’; 3, look at the specified system, the value of the variable select @ @ global | variable name “session” system; 4, as a system variable assignment method one: the set global | variable name = value “session” system; Method 2: set @ @ global | variable name = value “session” system;

1. Global variables

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

View all global variablesSHOW GLOBALVARIABLES; View some of the system variables that meet the conditionsSHOW GLOBAL VARIABLES LIKE '%char%'; Views the value of the specified system variableSELECT @@global.autocommit; Assign a value to a system variableSET @@global.autocommit=0;
SET GLOBAL autocommit=0;
Copy the code

2. Session variables

Scope: Valid for the current session (connection)

View all session variablesSHOWSESSION VARIABLES; View some of the session variables that meet the criteriaSHOW SESSION VARIABLES LIKE '%char%'; View the value of the specified session variable (session can be omitted)SELECT @@autocommit;
SELECT @@session.tx_isolation; Assign a value to a session variable (session can be omitted)SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
Copy the code

2. Customize variables

Note: Variables are user-defined, not provided by the system

Use steps: 1, declare 2, assign 3, use (view, compare, calculate, etc.)

1. User variables

Scope: Valid for the current session (connection). Scope is the same as session variables

① Declare and initialize:

The assignment operator := or :=

SET@ the variable name=Value;SET@ Variable name:=Value;SELECT@ Variable name:=Value;Copy the code

② Assign (update the value of a variable)

Method 1: Generally used to assign simple valuesSET@ the variable name=Value;SET@ Variable name:=Value;SELECT@ Variable name:=Value; # Method 2:SELECTfieldINTO@ the variable nameFROMTable;Copy the code

Check the value of a variable:

select@ Variable name;Copy the code

2. Local variables

Scope: only the first sentence in begin End is valid in the BEGIN end block where it is defined

(1) statement:

Declare: a statement

DECLAREVariable name type;DECLAREVariable name typeDEFAULTValue;Copy the code

② Assign (update variable value) :

A:SETLocal variable name=Value;SETLocal variable name:=Value;SELECTLocal variable name:=Value; Method 2:SELECTfieldINTOHave variable namesFROMTable;Copy the code

Check the value of a variable:

SELECTLocal variable name;Copy the code

3, case

Declare two variables, sum and print:

# user variablesSET @m=1;
    SET @n=1;
    SET @sum=@m+@n;
    SELECT @sum; # local variablesDECLARE m INT DEFAULT 1;
    DECLARE n INT DEFAULT 1;
    DECLARE SUM INT;
    SET SUM=m+n;
    SELECT SUM;
Copy the code

4. User variables versus local variables

	scope	Define the location	grammar
The user variables	The current session	Anywhere in the session	Add the @ sign without specifying the type
A local variable	Define it in BEGIN END	BEGIN the first sentence	Usually, you don’t need to add @, you need to specify the type

4. Stored procedures and functions

Stored procedures and functions: Similar to methods in Java benefits: 1. Increased code reuse; 2

1. Stored procedures

A set of pre-compiled SQL statements that understand batch processing 1, improve code reuse 2, simplify operations 3, reduce compile times and reduce the number of connections to the database server, improve efficiency

1. Create a syntax

    CREATE PROCEDUREStored procedure name (parameter list)BEGINStored procedure body (a set of legalSQLStatement)END
Copy the code

Note:

	1The parameter list contains three parts. Parameter Mode Parameter Name Parameter Type Example:in stuname varchar(20) Parameter mode:in: This parameter can be used as input, that is, it needs to be passed in by the callerout: This parameter can be used as output, that is, this parameter can be used as a return valueinout: This parameter can be used as either input or output, meaning that this parameter requires both passing and returning values2If the stored procedure body is just one sentence,begin endYou can omit each entry in the stored procedure bodysqlA semicolon is required at the end of a statement. You can reset the syntax at the end of the stored procedure using DELIMiter: DELIMiter End tag case: DELIMiter $Copy the code

2. Call syntax

CALLStored procedure name (argument list);Copy the code

3. Case demonstration

1). Empty parameter list example: Insert five records into the admin table

    SELECT * FROM admin;

    DELIMITER $
    CREATE PROCEDURE myp1()
    BEGIN
        INSERT INTO admin(username,`password`) 
        VALUES('john1'.'0000'), ('lily'.'0000'), ('rose'.'0000'), ('jack'.'0000'), ('tom'.'0000');
    END$# callCALL myp1()$
Copy the code

2). Create a stored procedure with an in mode parameter

Case 1: Create a stored procedure to query the information about the goddess based on the goddess name

    CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
    BEGIN
        SELECT bo.*
        FROM boys bo
        RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
        WHERE b.name=beautyName;
    END$# callCALL myp2('Ada') $Copy the code

Case 2: Create a stored procedure and check whether the user logs in successfully

    CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
    BEGIN
        DECLARE result INT DEFAULT 0; Declare and initializeSELECT COUNT(*) INTO result# assignmentFROM admin
        WHERE admin.username = username
        AND admin.password = PASSWORD;

        SELECT IF(result>0.'success'.'failure'); # useEND$# callCALL myp3('zhang fei'.'8888') $Copy the code

3). Create out mode parameters of the stored procedure case 1: according to the input goddess name, return the corresponding male god name

    CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
    BEGIN
        SELECT bo.boyname INTO boyname
        FROM boys bo
        RIGHT JOIN
        beauty b ON b.boyfriend_id = bo.id
        WHERE b.name=beautyName ;
    END $
Copy the code

Case 2: According to the entered goddess name, return the corresponding male goddess name and charm value

    CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
    BEGIN
        SELECT boys.boyname, boys.usercp INTO boyname, usercp
        FROM boys 
        RIGHT JOIN 
        beauty b ON b.boyfriend_id = boys.id 
        WHERE b.name=beautyName ;
    END$# callCALL myp7('small zhao'.@name.@cp) $SELECT @name.@cp$
Copy the code

4). Create a stored procedure with inout mode parameters

Case 1: Two values a and B are passed in, and eventually both a and B are doubled and returned

    CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
    BEGIN
    	SET a=a*2;
    	SET b=b*2;
    END$# callSET @m=10$
    SET @n=20$
    CALL myp8(@m.@n) $SELECT @m.@n$
Copy the code

4. Delete the stored procedure

Grammar:drop procedureStored procedure nameDROP PROCEDUREp1; # you cannot delete multiple files at the same timeDROP PROCEDUREp2,p3; # errorCopy the code

5. View the information about the stored procedure

	SHOW CREATE PROCEDURE  myp2;
Copy the code

6, problem sets

Insert the user name and password into the admin table DELIMITER $CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
BEGIN
	INSERT INTO admin(admin.username,PASSWORD)
	VALUES(username,loginpwd);
END$$$$$$$$$$$$$$$$CREATE PROCEDURE test_pro2(IN id INT.OUT NAME VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
	SELECT b.name ,b.phone INTO NAME,phone
	FROM beauty b
	WHERE b.id = id;

ENDCreate a stored procedure or function that passes in two goddess birthdays and returns sizeCREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN
	SELECT DATEDIFF(birth1,birth2) INTO result;
END$$$$$$$$$$$$$$$$$$CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strDate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(NOW(),@str) $SELECT @strCreate a stored procedure or function and pass in the goddess nameandMale god format string such as passed: xiao Zhao return: Xiao ZhaoANDZhang mowgliDROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20), OUT str VARCHAR(50))
BEGIN
	SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str
	FROM boys bo
	RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
	WHERE b.name=beautyName;
END $

CALL test_pro5('Ada'.@str) $SELECT @strSQL > select * from 'beauty'; SQL > select * from 'beauty'DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6(IN startIndex INT.IN size INT)
BEGIN
	SELECT * FROM beauty LIMIT startIndex,size;
END $

CALL test_pro6(3.5) $Copy the code

2. The function

A set of pre-compiled SQL statements that understand batch processing 1, improve code reuse 2, simplify operations 3, reduce compile times and reduce the number of connections to the database server, improve efficiency

Difference: Stored procedure: can have 0 returns, can also have multiple returns, suitable for batch insert, batch update function: there is only one return, suitable for processing data after returning a result

1. Create a syntax

    CREATE FUNCTIONFunction name (argument list)RETURNSThe return typeBEGINThe body of the functionENDNote:1.The parameter list consists of two parts: Parameter name Parameter type2.Function body: DefinitelyreturnStatement if there is no error ifreturnStatements not placed at the end of the function body are not reported, but are not recommended3.If there is only one sentence in the function body, it can be omittedbegin end
	4.Set the end tag using the DELIMiter statementCopy the code

2. Call syntax

	SELECTFunction name (argument list)Copy the code

3. Case demonstration

1)

# Example: Return the number of employees in the company DELIMITER $CREATE FUNCTION myf1() RETURNS INT
    BEGIN
        DECLARE c INT DEFAULT 0; # define local variablesSELECT COUNT(*) INTOC # assignmentFROM employees;
        RETURN c;
    END $
    SELECT myf1()$
Copy the code

2)

Case #1: Returns the salary of the employee based on the employee nameCREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
        SET @sal=0; Define user variablesSELECT salary INTO @sal# assignmentFROM employees
        WHERE last_name = empName;
        RETURN @sal;
    END $
    SELECT myf2('k_ing') $# case2: Returns the average salary for the department based on the department nameCREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
    BEGIN
        DECLARE sal DOUBLE ;
        SELECT AVG(salary) INTO sal
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE d.department_name=deptName;
        RETURN sal;
    END $
    SELECT myf3('IT') $#3: creates a function that implements passing in twofloat, returns the sum of the twoCREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
    BEGIN
        DECLARE SUM FLOAT DEFAULT 0;
        SET SUM=num1+num2;
        RETURN SUM;
    END $
    SELECT test_fun1(1.2) $Copy the code

4. Look at functions

	SHOW CREATE FUNCTION myf3;
Copy the code

5. Delete functions

	DROP FUNCTION myf3;
Copy the code

5. Process control structure

Sequence, branch, loop

1. Branch structure

1. If function

Grammar:

If (condition, value1And the value2)
Copy the code

Function: Implements double branch applications in and outside of begin End

2, case structure

Grammar:

situation1: Similar to switchcaseVariable or expressionwhen 值1 thenstatements1;
	when 值2 thenstatements2; .elseStatements n;endsituation2:case 
    whenconditions1 thenstatements1;
    whenconditions2 thenstatements2; .elseStatements n;endThe application inbegin endIn or outsideCopy the code

Case demonstration:

Case #1: creates a function that implements passing grades if grades>90, returns A if the grade>80, returns B if the grade>60, returns C, or DCREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56) $Copy the code

3, if structure

Grammar:

If condition1 thenstatements1; Elseif conditions2 thenstatements2; .elseStatements n;endif; Function: Similar to multiple if can only be applied inbegin end 中
Copy the code

Case demonstration:

Case #1: creates a function that implements passing grades if grades>90, returns A if the grade>80, returns B if the grade>60, returns C, or DCREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

SELECT test_if(87) $#2: Create stored procedure if payroll<2000, then delete, if5000>wage>2000, raise wages1000Or get a raise500

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal> =2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
END $

CALL test_if_pro(2100) $Copy the code

2. Loop structure

Categories: while, loop, repeat

Cycle control:

Iterate is similar to continue, end the loop, continue the next leave is similar to break, break out, end the current loop

1, while

Grammar:

【 tag :】while condition do loop body;endWhile label; Association: while(loop condition){loop body; }Copy the code

Case demonstration:

    #1.SQL > insert into admin table; insert into admin tableCREATE PROCEDURE pro_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        WHILE i< =insertCount DO
            INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
            SET i=i+1;
        END WHILE;
    END $

    CALL pro_while1(100)$


    #2.Add leave statement # example: insert multiple records into admin table according to times, if times>20The stopCREATE PROCEDURE test_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        a:WHILE i< =insertCount DO
            INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
            
            IF i> =20 THEN LEAVE a;
            END IF;
            
            SET i=i+1;
        END WHILE a;
    END $

    CALL test_while1(100)$


    #3.Add ITERATE statement # Example: Batch insert, insert multiple records into the admin table by number of times, insert only an even number of timesTRUNCATE TABLE admin$
    DROP PROCEDURE test_while1$
    CREATE PROCEDURE test_while1(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 0;
        a:WHILE i< =insertCount DO
            SET i=i+1;
            IF MOD(i,2)! =0 THEN ITERATE a;
            END IF;

            INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
        END WHILE a;
    END $

    CALL test_while1(100) $Copy the code

2, loop

Grammar:

Loop body;endLoop 【 label 】; Can be used to simulate a simple infinite loopCopy the code

3, repeat

Grammar:

【 tag: 】 Repeat; Until terminates the loop conditionendRepeat 【 label 】;Copy the code

4. Classic cases

    Insert a specified number of random strings */ into the table
    DROP TABLE IF EXISTS stringcontent;
    CREATE TABLE stringcontent(
        id INT PRIMARY KEY AUTO_INCREMENT,
        content VARCHAR(20)); DELIMITER $CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
    BEGIN
        DECLARE i INT DEFAULT 1;
        DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
        DECLARE startIndex INT; # represents the initial indexDECLARE len INT; # represents the truncated character length WHILE I< =insertcount DO
            SET len=FLOOR(RAND()*(20-startIndex+1)+1); # stands for intercept length, random range1-(20-startIndex+1)SET startIndex=FLOOR(RAND()*26+1); # stands for initial index, random range126 -
            INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
            SET i=i+1;
        END WHILE;

    END $

    CALL test_randstr_insert(10) $Copy the code

​