MySQL database-the basics of the whole process

1. Operating the database

1. Create a database

CREATE DATABASE IF NOT EXISTS user;

2. Delete the database

DROP DATABASE IF EXISTS user;

3. Use the database

-- 如果使用的表名或者字段名是一个特殊字符,则需要带``
USE `user`;

4. View the database

-- 查看所有的数据库
SHOW DATABASE;

Two, the column type of the database

1. Numerical type

TINYINT1 byte(-128, 127)(0, 255)Small integer value
SMALLINT2 bytes(-32 768, 32 767)(0, 65 535)Large integer value
MEDIUMINT3 bytes(-8 388 608, 8 388 607)(0, 16 777 215)Large integer value
INT or INTEGER4 bytes(-2 147 483 648, 2 147 483 647)(0, 4 294 967 295)Large integer value
BIGINT8 bytes(-9,223,372,036,854,775,808, 9 223 372 036 854 775 807)(0, 18 446 744 073 709 551 615)Very large integer value
FLOAT4 bytes(-3.402 823 466 E+38, -1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E+38)0, (1.175 494 351 E-38, 3.402 823 466 E+38)Single-precision floating-point value
DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308, -2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)Double-precision floating-point value
DECIMALFor DECIMAL(M,D), if M>D, M+2 otherwise D+2Depends on the value of M and DDepends on the value of M and DDecimal value

2. Date and time type

DATE31000-01-01/9999-12-31YYYY-MM-DDDate value
TIME3'-838:59:59'/'838:59:59'HH:MM:SSTime value or duration
YEAR11901/2155YYYYYear value
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SSMixed date and time values
TIMESTAMP41970-01-01 00:00:00/2038 end time is 2147483647 second, Beijing time 2038-1-19 11:14:07, GMT 03:14:07 on January 19, 2038YYYYMMDD HHMMSSMixed date and time values, timestamp

3. String type

CHAR0-255 bytesFixed-length string
VARCHAR0-65535 bytesVariable length string
TINYBLOB0-255 bytesBinary string of no more than 255 characters
TINYTEXT0-255 bytesShort text string
BLOB0-65 535 bytesLong text data in binary form
TEXT0-65 535 bytesLong text data
MEDIUMBLOB0-16 777 215 bytesMedium-length text data in binary form
MEDIUMTEXT0-16 777 215 bytesMedium-length text data
LONGBLOB0-4 294 967 295 bytesVery large text data in binary form
LONGTEXT0-4 294 967 295 bytesVery large text data

Three, MySQL creates a data table

1. General grammar

-- CREATE TABLE table_name (column_name column_type);

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `version` int(10) NOT NULL DEFAULT '1' COMMENT '版本',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_update` datetime DEFAULT NULL COMMENT '修改时间',
  `is_delete` int(1) DEFAULT NULL COMMENT '伪删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  • If you don't want the field to be NULL, you can set the attribute of the field to NOT NULL . If you enter the data in the field as NULL when operating the database, an error will be reported.
  • AUTO_INCREMENT defines the column as a self-incrementing attribute, which is generally used for the primary key, and the value is automatically increased by 1.
  • The PRIMARY KEY keyword is used to define the column as the primary key. You can use multiple columns to define the primary key, separated by commas.
  • ENGINE sets the storage engine, CHARSET sets the encoding, COLLATE collation

Fourth, delete the database table

1. General grammar

-- DROP TABLE table_name
DROP TABLE `user`;

Five, insert data

1. General grammar

--INSERT INTO table_name (field1, field2, field3...) VALUES (values1, values2, values2...);

-- 字段与value一一对应
INSERT INTO `user` (name, age, email, version, gmt_create, gmt_update, is_delete) VALUES ('李子捌', '18', '[email protected]', 1, NOW(), NOW(), 1);

-- 没有字段名则需要顺序一致
INSERT INTO `user` VALUES (8, '李子捌', 18, '[email protected]', 1, NOW(), NOW(), 1);

INSERT INTO `user` SET name = '李子捌', age = 18;

Six, query statement

1. General grammar

--SELECT column_name,column_nameFROM table_name[WHERE Clause][LIMIT N][ OFFSET M]

select * from `user`;
  • You can use one or more tables in the query statement, separate the tables with a comma (,), and use the WHERE statement to set the query conditions.
  • The SELECT command can read one or more records.
  • You can use an asterisk (*) to replace other fields, the SELECT statement will return all the field data of the table
  • You can use the WHERE statement to include any conditions.
  • You can use the LIMIT property to set the number of records returned.
  • You can use OFFSET to specify the data offset at which the SELECT statement starts to query. By default, the offset is 0.

Seven, MySQL WHERE clause

1. General grammar

--SELECT field1, field2,...fieldN FROM table_name1, table_name2...[WHERE condition1 [AND [OR]] condition2.....

SELECT name, age FROM `user` WHERE name = '李子捌' or age = 18;
  • You can specify any conditions in the WHERE clause.
  • You can use AND or OR to specify one or more conditions.

Eight, MySQL UPDATE update

1. General grammar

-- UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
UPDATE `user` SET age = 3 WHERE name = '李子捌' AND email = '[email protected]';
  • One or more fields can be updated at the same time.
  • You can specify any conditions in the WHERE clause.
  • The data can be updated simultaneously in a single table.

Nine, MySQL DELETE statement

1. General grammar

-- DELETE FROM table_name [WHERE Clause]
DELETE FROM `user` WHERE name = '李子捌';
  • If no WHERE clause is specified, all records in the MySQL table will be deleted.
  • Any conditions can be specified in the WHERE clause

Ten, MySQL LIKE clause

1. General grammar

-- SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT name, age, email FROM `user` WHERE name LIKE '%捌';

-- 百分号可在左边、右边、两边
-- '%捌'      '捌%'        '%捌%'
-- 以捌结尾    以捌开头      包含捌

https://blog.csdn.net/daemon329/article/details/106170743

11. DQL language

DQL (Data Query Language)

  • Query database data, such as select statements
  • Simple single-table query or complex query and nested query that won the bid
  • Is the core and most important language in database languages
  • The most frequently used language

1. Select complete syntax

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]}
FROM table_name [as table_alias]
[left | right | inner join table_name2]  -- 联合查询
[WHERE ...] -- 指定满足结果的条件
[GROUP BY ...] -- 指定结果按照那些字段来分组
[HAVING] -- 过滤分组必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按照一个或者多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询记录从哪条到哪条
                

Note: [] means optional, {} means required

2. Specify the query field

-- 查询所有的学生信息(速度慢不推荐使用)
SELECT * FROM student;

-- 查询指定列(学号,姓名)
SELECT studentno,studentname FROM student;
Use the AS clause as an alias

effect:

  • You can take a new alias for the data column
  • You can give the table a new alias
  • You can replace the calculated result with an alias
-- 为列取一个别名
SELECT studentno AS 学号, studentname AS 姓名 FROM student;

-- 为表取一个别名
SELECT studentno AS 学号, studentname AS 姓名 FROM student AS s;

-- 使用AS为查询的结果取一个名字 CONCAT()函数字符串拼接,在studentname数据前面拼接 姓名:
SELECT CONCAT('姓名:', studentname) AS 新姓名 FROM student;
Use of the DISTINCT keyword

effect:

  • Remove the duplicate records in the records of the SELECT query
-- 查看哪些同学参加了考试(学号) 去除重复项
-- 查询成绩表
SELECT * FROM result;   
-- 查询成绩表中的学号,及查询哪些同学参加了考试(有成绩的学生)
SELECT studentno FROM result; 
-- 根据学号去重
SELECT DISTINCT studentno FROM result; 
Use expression columns

Expressions in the database: generally composed of text, column values, null, functions, and operators

Application scenarios:

  • Used in the return result column of the SELECT statement
  • Use of ORDER BY, HAVING and other clauses in the SELECT statement
  • The expression used in the where conditional statement in the DML statement
-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 88*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT studentno,studentresult+1 AS '分数加1' FROM result;

3. Where conditional statement

effect:

  • Used to retrieve the records that meet the conditions in the data table

The search condition can be composed of one or more logical expressions, and the result is generally true or false

Logical operator
calculating signseffect
NOT or!Logical negation
ANDLogical and
ORLogical OR
XORLogical exclusive OR
-- 指定字段全表
SELECT Studentno,StudentResult FROM result;

-- 查询成绩在95-100之间的  ADN 也可以写成 && 
SELECT studentno,studentresult 
FROM result
WHERE studentresult >=95 AND studentresult <=100;

-- 模糊查询(对应的词:精确查询)
SELECT studentno,studentresult 
FROM result
WHERE studentresult BETWEEN 95 AND 100;

-- 除了888号同学,要其他同学的成绩
SELECT studentno,studentresult 
FROM result
WHERE studentno != 888

-- 除了888号同学,要其他同学的成绩 使用not
SELECT studentno,studentresult 
FROM result
WHERE NOT studentno = 888
Arithmetic Operator
Operatoreffect
+addition
-Subtraction
*multiplication
/ Or DIVdivision
% Or MODTake the remainder
Comparison operator
symboldescriptionRemarks
=equal
<>, !=not equal to
>more than the
<Less than
<=Less than or equal to
>=greater or equal to
BETWEENBetween two values>=min&&<=max
NOT BETWEENNot between two values
INIn the collection
NOT INNot in the collection
<=>Strictly compare whether two NULL values ​​are equalWhen both opcodes are NULL, the resulting value is 1; when one opcode is NULL, the resulting value is 0
LIKEFuzzy matching
REGEXP or RLIKERegular expression matching
IS NULLIs empty
IS NOT NULLnot null

note:

  • Arithmetic operators can only be used between numeric types
  • Comparison between the same data types
-- 模糊查询 between and \ like \ in \ null

-- LIKE
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
-- 查询姓李的同学的学号及姓名
SELECT studentno, studentname FROM student 
WHERE studentname LIKE '李%';

-- 查询姓李的同学,后面只有一个字的   _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '李_';

-- 查询姓李的同学,后面只有两个字的   _ (一个字符)
SELECT studentno,studentname FROM student
WHERE studentname LIKE '李__';

-- 查询姓名中含有 捌 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%捌%';

-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'


-- IN
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查询地址在深圳,长沙,娄底的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('深圳','长沙','娄底');


-- NULL
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

4. Connect query

Operator namedescription
INNER JOINIf at least one match in the table, return
LEFT JOINReturn all rows in the left table, matching rows in the right table
RIGHT JOINReturn all rows in the right table, matching rows in the left table

[External link image transfer failed. The source site may have an anti-leech link mechanism. It is recommended to save the image and upload it directly (img-LQR1TnC2-1622729395022)(C:\Users\Leon Plious\Desktop\u=813688978,869557195&fm=15&gp=0 .jpg)]

/*
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
       
等值连接和非等值连接

自连接
*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno;

-- 右连接实现
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno;

-- 左连接(注意左表为student,没有成绩的学生信息也会被查出)
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno;

-- 等值连接
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s, result r
WHERE s.studentno = r.studentno;

-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno, studentname, subjectno, studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL;

-- 查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno, studentname, subjectno, subjectname, studentresult
FROM student s
INNER JOIN result r
ON  s.studentno = r.studentnos
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
Self-connection
/*
自连接
   数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目', b.categoryName AS '子栏目'
FROM category AS a, category AS b 
WHERE a.categoryid = b.pid

5. Sorting and paging

-- 排序
/**
   语法 : ORDER BY
   ORDER BY 语句用于根据指定的列对结果集进行排序。
   ORDER BY 语句默认按照ASC升序对记录进行排序。
   如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。
*/

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.sudentno, studentname, subjectname, sudentresult
FROM student s
INNER JOIN result r
ON r.sudentno = s.sudentno
INNER JOIN subject sub
ON sub.subjectno  = r.subjectno
WHERE  subjectname = ' 数据库结构-1'
ORDER BY sudentresult DESC;


-- 分页
/**
 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
 
 pageNo  pageSize
 LIMIT (pageNo - 1)*pageSize, pageSize
*/
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
-- 每页显示100条数据
SELECT s.sudentno, studentname, subjectname, sudentresult
FROM student s
INNER JOIN result r
ON r.sudentno = s.sudentno
INNER JOIN subject sub
ON sub.subjectno  = r.subjectno
WHERE  subjectname = ' 数据库结构-1'
ORDER BY sudentresult DESC
LIMIT 0, 100;

6. Subqueries

/*============== 子查询 ================
什么是子查询?
   在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
   嵌套查询可由多个子查询组成,求解的方式是由里及外;
   子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/


-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
SELECT studentno, subjectno, studentresult
FROM result
WHERE subjectno=(
	SELECT subjectno FROM subjct 
    WHERE subjectname = '数据库结构-1'
) ORDER BY studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
SELECT studentno, studentname
FROM student
WHERE studentno IN(
	SELECT studentno 
    FROM result 
    WHERE studentresult >=80 AND subjectno = (
        SELECT subjectno 
        FROM subjct 
        WHERE  subjectname = '高等数学-2'
    )
)

Twelve, MySQL functions

1. Data function

/* 取绝对值 */
SELECT ABS(-10);

/* 向上取整 */
SELECT CEILING(9.1);

/* 向下取整 */
SELECT FLOOR(9.9);

/* 返回一个0-1之间的随机数  0.7898709874505838 */
SELECT RAND();

/* 符号函数 正数返回1、负数返回-1、0返回0*/
SELECT SIGN(-1)

2. String functions

/* 返回字符串的长度 */
SELECT CHAR_LENGTH('成功绝不是偶然')

/* 字符串拼接 */
SELECT CONCAT('李','子','捌')

/* 字符串插入 注意第一个参数是开始替换的位置索引从1开始,不是0开始, 第二个参数是替换的长度*/
SELECT INSERT('我是李子捌我热爱三国',7,2,'非常热爱');

/* 转大写 */
SELECT UPPER('liziba');

/* 转小写 */
SELECT LOWER('LIZIBA');

/* 从左边截取 */
SELECT LEFT('李子捌热爱编程',3);

/* 从右边截取 */
SELECT RIGHT('李子捌热爱编程',4);

/* 替换指定字符串 */
SELECT REPLACE('李子捌说李子捌热爱编程','李子捌','借力好风');

/* 字符串截取 第一个参数为起始索引,第二个参数为字符串长度*/
SELECT SUBSTR('李子捌说李子捌热爱编程',5, 7);

3. Date and time functions

/* 获取当前日期 2021-01-01*/
SELECT CURRENT_DATE();

/* 获取当前日期 2021-01-01*/
SELECT CURDATE();

/* 获取当前日期 + 时间 2021-01-01 13:04:48 */
SELECT NOW();


/* 获取当前日期 + 时间 2021-01-01 13:05:48*/
SELECT LOCALTIME();

/* 获取当前日期 + 时间 2021-01-01 13:05:53*/
SELECT SYSDATE();


/* 获取年、月、日、时、分、秒 */
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

4. System information function

/* 获取版本号 8.0.15*/
SELECT VERSION();

/* 用户  [email protected]*/
SELECT USER();

5. MD5 encryption

-- 创建测试表
CREATE TABLE `md5` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `pwd` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- 插入测试数据(无md5加密)
INSERT INTO md5 (`name`, `pwd`) VALUES('liziba', '123456'),('xieli', '123456'),('newnow', '123456');

-- 插入测试数据(有md5加密)
INSERT INTO md5 (`name`, `pwd`) VALUES('liziba', MD5('123456')),('xieli', MD5('123456')),('newnow', MD5('123456'));

-- MySQL md5加密采用MD5()函数实现
MD5('STR');

-- 查询用户登录信息
SELECT * FROM `md5` WHERE `name` = 'liziba' AND `pwd` = MD5('123456');


6. Summary

-- MySQL内置函数
 -- 数值函数
 abs(x)            -- 绝对值 abs(-10.9) = 10
 format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
 ceil(x)            -- 向上取整 ceil(10.1) = 11
 floor(x)        -- 向下取整 floor (10.1) = 10
 round(x)        -- 四舍五入去整
 mod(m, n)        -- m%n m mod n 求余 10%3=1
 pi()            -- 获得圆周率
 pow(m, n)        -- m^n
 sqrt(x)            -- 算术平方根
 rand()            -- 随机数
 truncate(x, d)    -- 截取d位小数
 
 -- 时间日期函数
 now(), current_timestamp();     -- 当前日期时间
 current_date();                    -- 当前日期
 current_time();                    -- 当前时间
 date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
 time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
 date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间
 unix_timestamp();                -- 获得unix时间戳
 from_unixtime();                -- 从时间戳获得时间
 
 -- 字符串函数
 length(string)            -- string长度,字节
 char_length(string)        -- string的字符个数
 substring(str, position [,length])        -- 从str的position开始,取length个字符
 replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换search_str
 instr(string ,substring)    -- 返回substring首次在string中出现的位置
 concat(string [,...])    -- 连接字串
 charset(str)            -- 返回字串字符集
 lcase(string)            -- 转换成小写
 left(string, length)    -- 从string2中的左边起取length个字符
 load_file(file_name)    -- 从文件读取内容
 locate(substring, string [,start_position])    -- 同instr,但可指定开始位置
 lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为length
 ltrim(string)            -- 去除前端空格
 repeat(string, count)    -- 重复count次
 rpad(string, length, pad)    --在str后用pad补充,直到长度为length
 rtrim(string)            -- 去除后端空格
 strcmp(string1 ,string2)    -- 逐字符比较两字串大小
 
 -- 聚合函数
 count()
 sum();
 max();
 min();
 avg();
 group_concat()
 
 -- 其他常用函数
 md5();
 default();

13. Affairs

1. Introduction

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。
																	——《百度百科》

2. ACID characteristics of transactions

1. Atomic

一个事务被视为一个不可分割的最小工作单元,这个事务里的所有操作要么全部成功执行,要么全都不执行,不能只执行其中的一部分操作。实现事务的原子性,要支持回滚操作,在某个操作失败后,回滚到事务执行之前的状态。    

回滚是一个抽象概念,大多数数据库在实现事务时是在事务操作的数据快照上进行,并不修改实际的数据,发生错误时并不提交。

2. Consistency

一致性是指事务使得系统从一个一致性的状态转换到另一个一致性的状态。在实际的工程项目中事务可以有不同程度的一致性:
强一致性:读操作可以立即读到提交的更新数据。
弱一致性:提交的更新操作,不一定立即会被读操作读到,这种情况存在不一致窗口,指的是读操作要延迟一定时间才能读到最新值。
最终一致性:弱一致性的特例。事务更新一份数据,最终一致性保证在没有其他事务更新同样的值的话,最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生,不一致窗口的大小依赖于通信延迟、系统负载等。

其他一致性变体还有:
单调一致性:如果一个进程已经读到一个值,那么后续不会读到更早的值。
会话一致性:保证客户端和服务器交互的会话过程中,读操作可以读到更新操作后的最新值。

3. Isolation

一个事务所做的修改在最终提交以前对其他事务是不可见的。

4. Durability

一旦事务提交,则所做的修改会永久保存在数据库中。

3. Four isolation levels

1. Read Uncommitted

最低隔离级别。事务可以读取到未提交的数据,也即脏读(Dirty read)。存在脏读、不可重复读、幻读的问题。实际应用中一般很少使用这个隔离级别。

2. Read Committed

大多数数据库系统默认的隔离级别(mysql不是)。只有在事务提交后,其更新结果才会被其他事务看见。该级别也叫 不可重复读(nonrepeatable read),两次执行同样的查询语句可能得到不同的结果。可以解决脏读问题,存在不可重复读、幻读的问题。

不可重复读发生的一个场景:事务A需要多次读取同一个数据,当再次读取该数据的时候 另一个事务B修改了该数据,导致事务A读到的该数据 与 上一次读到的数据不一致。(侧重在数据被修改了update)

3. Repeated Read (repeatable read)

mysql默认的事务隔离级别。在同一个事务中多次读取同样记录的结果总是一致的。可以解决脏读、不可重复读,存在幻读(Phantom read)问题。幻读指的是当某个事务在读取某个范围内的记录时,会产生幻行(Phantom Rows)。

幻读发生的一个场景:select检测某数据是否存在,当不存在时插入数据,但在执行insert 语句插入数据时发现此记录已经存在了,不能再插入,此时即发生了幻读。

另一个场景:事务A执行 select语句1 返回 5条记录,再次执行 select语句1 时返回了 6条 记录,也即与上次返回的结果集不一致。(侧重在插入了新数据insert)

InnoDB引擎通过使用 NK锁(Next-Key Locks)解决幻读问题。

4. Serialization (serializable)

事务串行化执行,隔离级别最高,牺牲了系统的并发性。解决脏读、不可重复读、幻读,可保证事务安全。通过强制事务串行执行避免了幻读问题,它在读取的每一行数据上都加锁,会导致大量的超时和锁争用问题。实际应用中很少使用这个隔离级别。

note:

不可重复读的重点于在修改 -- 同样的检索条件读取数据,再次读取出来时发现值不一致

幻读的重点在于新增或者删除 -- 同样的条件,第1次和第2次读出来的记录数量不一样

4. Basic grammar of affairs

-- 由于MySQL默认是开启自动提交所以在使用事务的时候我们要先关闭自动提交 SET AUTOCOMMIT = 0;
-- 关闭和开启自动提交模式
SET AUTOCOMMIT = 0; -- 关闭
SET AUTOCOMMIT = 1; -- 开启(MySQL默认是开启自动提交)

-- 开始一个事务
START TRANSACTION;

-- 事务提交
COMMIT;

-- 事务回滚
ROLLBACK;

-- 事务结束后设置MySQL数据的自动提交
SET AUTOCOMMIT = 1;-- 开启(MySQL默认是开启自动提交)

-- 保存点
-- 使用保存点,可以在回滚操作时只回滚到该保存点即可,而非事务的开头。
SAVEPOINT `保存点名称` --设置一个事务的保存点
ROLLBACK TO SAVEPOINT `保存点名称` -- 回滚到保存点
RELEASE SAVEPOINT `保存点名称` -- 删除保存点


-- 测试题
/*
李子捌在线买一款价格为500元商品,网上银行转账.
李子捌的银行卡余额为2000,然后给商家李子柒支付500.
商家李子柒一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/
-- 创建数据库shop
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
-- 创建表account
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入2条数据
INSERT INTO account (`name`,`cash`)
VALUES('李子捌',2000.00),('李子柒',10000.00)

-- 通过事务控制转账的实现
SET AUTOCOMMIT=0;  -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE account SET cash=cash-500 WHERE `name` = '李子捌'; 
UPDATE account SET cash=cash+500 WHERE `name` = '李子柒';
COMMIT; -- 事务提交
# ROLLBACK; -- 事务会滚(出现异常)
SET AUTOCOMMIT=1; -- 开启自动提交

14. Index

1. The concept of index

索引是一种特殊的文件,包含着对数据表中所有的记录的引用指针,数据库索引好比是一本书前面的目录,能加快数据库的查询速度,数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

2. The role of index

建立索引的目的是加快对表中记录的查找或排序,为表设置索引要付出代价;
一是增加了数据库的存储空间
二是在插入和修改数据时要花费更多的时间(因为索引也会随之改变)。

(1)设置合适的索引之后,数据库利用各种快速的定位技术,可以大大加快数据的查询速度,这也是创建索引的最主要的原因。
(2)当表很大的时候,或者查询涉及到多个表时,使用索引可以使查询速度快上成千倍。
(3)可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本。
(4)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
(5)在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间。

3. Classification of index

1.普通索引:列值可以取空值或重复值。创建使用关键字INDEX或KEY;

2.唯一索引:列值不能重复;即索引列值必须是唯一的,但可以是空值;创建使用关键字UNIQUE;

3.主键索引:主键索引是系统自动创建的主键索引,并且是唯一的。与唯一索引区别是;列值不能为空;

4.聚簇索引:就是数据存储的物理存储顺序,非聚簇索引就是索引顺序与数据的物理顺序无关。一个表只能有一个聚簇索引。目前只有InoDB和solidDB支持。

5.全文索引:只能创建在varchar或text的列上;建立全文索引能够在全文索引的列上进行查找。

(1)单列索引:就是一个索引只包含表中的一个列;比创建一个学号ID的索引;以name再创建一个姓名的单列索引。即每个索引包含一个列。

(2)组合索引(复合索引或多列索引):就是表中的两个列或多个列来创建成一个索引;比如;以用户ID、用户名Name、用户年龄Age来创建的索引就是联合索引。

4. The principle of creating an index

(1)表的主键、外建必须有索引,主键具有唯一性,索引值也是唯一性,查询时可以快速定位到数据行,外键一般关联的是另一个表的主键,所有在多表查询时也可以快速定位。
(2)数据量过300行的表应该有索引,数据量较大的时候,如果没有索引,需要把表遍历一遍,严重影响数据库的性能。
(3)经常与其他表进行表连接的表,在连接字段上应该建立索引。
(4)唯一性太差的字段不适合建立索引,如果索引字段的数据唯一性太差,是不适合创建索引。
(5)更新太频繁的字段不合适创建索引,在表中进行增加、删除、修改操作时,索引也有相应操作产生、字段更新得过于频繁,对于系统资源占用也会更多。
(6)经常出现在where(条件判断)字句中的字段,特别是大表的字段,应该建立索引。
(7)索引应该建立在选择性高的字段上,如果很少的字段拥有相同值,即有很多独特值,则选择性很高。
(8)索引应该建在小字段上,对于大的文本段甚至超长字段,不要建索引。

6, the basic syntax of index creation

1. Use the CREATE INDEX statement

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

2. Use the CREATE TABLE statement

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

FOREIGN KEY <索引名> <列名>

在使用 CREATE TABLE 语句定义列选项的时候,可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时,则不能使用这种方法,只能用在语句的最后加上一个 PRIMARY KRY(<列名>,…) 子句的方式来实现。

3. Use the ALTER TABLE statement

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

ADD PRIMARY KEY [<索引类型>] (<列名>,…)

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

ADD FOREIGN KEY [<索引名>] (<列名>,…)

7. Example of index creation

1. Create a normal index

-- 创建一个表 tb_stu_info,在该表的 height 字段创建普通索引。
CREATE TABLE tb_stu_info(
 `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
 INDEX(`height`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Create a unique index

-- 创建一个表 tb_stu_info2,在该表的 height 字段上使用 UNIQUE 关键字创建唯一索引

CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  UNIQUE KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

8. Test index

1. Create table user

CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='user用户表'

2. Batch insert data 100W

-- This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法
/*
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句,当然也不会修改数据
3 READS SQL DATA 只是读取数据,当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句

其中在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
*/
set global log_bin_trust_function_creators=TRUE;


DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO `user`(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '[email protected]', CONCAT('22', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

3. No index test

-- 耗时0.570S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.621S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.625S
SELECT * FROM user WHERE name = '用户8888';

4. Test the general index

-- 创建普通索引
CREATE INDEX idx_user_name ON user(name);

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

5. Test the unique index

-- 创建唯一索引
ALTER TABLE user ADD UNIQUE KEY uidx_user_name (`name`);

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.023S
SELECT * FROM user WHERE name = '用户8888';

-- 耗时0.022S
SELECT * FROM user WHERE name = '用户8888';