1. MySQL functions

Official website: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1.1. Common Functions

-- ==================== Common Functions =============================

-- Mathematical operations
SELECT ABS(-8)  -- absolute value
SELECT CEILING(9.4) -- ceil
SELECT FLOOR(9.4)  -- Rounding Down
SELECT RAND() -- Return a 0~1 Random number between
SELECT SIGN(10)   -- Judging the sign 0 of a number-0   Negative Return-1，Positive number returns 1

-- String function
SELECT CHAR_LENGTH('Even the smallest sail can go far') -- String Length
SELECT CONCAT('I','love','You') -- Split String
SELECT INSERT('I love programming helloworld',1,2,'Super love') -- Query, replacing a length from a location
SELECT LOWER('KuangShen') -- Lowercase letters
SELECT UPPER('KuangShen') -- Capital
SELECT INSTR('kuangshen','h') -- Returns the index of the first occurrence of the substring
SELECT REPLACE('Mania says perseverance will succeed','Insist','Strive') --	Replace the specified string that appears
SELECT SUBSTR('Mania says perseverance will succeed',4,6) -- Returns the specified substring (Source string,Location of intercept, length of intercept)
SELECT REVERSE('I get on horse in the morning') -- Reversal

-- Query classmates with last name Zhou, first name Zou
SELECT REPLACE(studentname,'week','Zou') FROM student
WHERE studentname LIKE 'week%'

-- Time and date functions (remember)
SELECT CURRENT_DATE() -- Get the current date
SELECT CURDATE() -- Get the current date
SELECT NOW() -- Get the current time
SELECT LOCALTIME() -- Local Time
SELECT SYSDATE() -- system time

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- system
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

1.2. Aggregate functions (commonly used)

--===========Aggregate function================Aggregate function========
--are able to count the data in a table (to query how many records are in a table, use this count())
SELECT COUNT (`BornDate`) FROM student; --Count (field), ignores all null values
 SELECT COUNT(*) FROM student; --Count(*), does not ignore null values, essentially calculates the number of rows
 SELECT COUNT(1) FROM result; --Count(1) does not ignore that all null values are essentially rows counted


SELECT SUM(`StudentResult`) AS Sum FROM result
 SELECT AVG(`StudentResult`) AS Average Score FROM result
 SELECT MAX(`StudentResult`) AS Highest Score FROM result
 SELECT MIN(`StudentResult`) AS Minimum FROM result

1.3, Database Level MD5 Encryption (Extension)

MD5: The main enhancements are algorithm complexity and irreversibility.

md5 is not reversible, the md5 of the specific value is the same

How MD5 cracks websites, there is a dictionary behind it, the value encrypted by MD5, the value encrypted before

-- =========test MD5 encryption=======

CREATE TABLE `testmd5`(
   `id` INT(4) NOT NULL,
   `name` VARCHAR(20) NOT NULL,
   `pwd` VARCHAR(50) NOT NULL,
   PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- enable password
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- encryption
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

UPDATE testmd5 SET pwd=MD5(pwd)  -- Encrypt all passwords

-- Encrypt when inserting
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))

-- How to verify: Passwords passed in by users md5 Encrypt and then compare the encrypted values
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

2. Transactions

11:11

1. SQL Executes A to B Transfer A 1000->200 B 200

2. Money A 800 - > B 400 received by SQL Execution B

11:11

Place a set of SQL in a batch to execute ~

Reference Blog Connection: https://blog.csdn.net/dengjili/article/details/82468576

Atomicity

Either all succeeded or all failed

Consistency

Ensure consistent data integrity before and after transactions, 1000

Durability - Transaction Commit

Once a transaction is committed, it is irreversible and persisted to the database!

Isolation

Transaction isolation is when multiple users access the database concurrently. The transactions opened by the database for each user cannot be interfered with by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other.

Dirty reading:

Refers to a transaction that has read uncommitted data from another transaction.

Non-repeatable reading:

Reading a row of data in a table within a transaction results in different reads.(This is not necessarily an error, it's just something wrong)

False reading (fantasy reading)

Refers to reading data inserted by another transaction within one transaction, resulting in inconsistent reading.

-- ==================== affair =========================

-- mysql Is the default open transaction autocommit
SET autocommit = 0 /* Close */
SET autocommit = 1 /* On (default) */

-- Manual transaction
SET autocommit = 0 -- Turn off automatic submission

-- Transaction Open
START TRANSACTION  -- Marks the beginning of a transaction, after which sql All in the same transaction

INSERT xx
INSERT xx

-- Submit: Persistence (Success!)
COMMIT
-- Rollback: back to the original (failed!)
ROLLBACK

-- End of Transaction
SET autocommit = 1 -- Turn on automatic submission

-- understand
SAVEPOINT Save Point Name -- Set a save point for a transaction
ROLLBACK TO SAVEPOINT Save Point Name -- Rollback to savepoint
RELEASE SAVEPOINT Save Point Name -- Undo Save Point

-- Transfer accounts
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- Simulated Transfer: Transaction
SET autocommit = 0; -- Turn off automatic submission
START TRANSACTION -- Open a transaction (set of transactions)

UPDATE account SET money=money-500 WHERE `name` = 'A' -- A Minus 500
UPDATE account SET money=money+500 WHERE `name` = 'B' -- A Add 500

COMMIT; -- Commit a transaction and it's persisted!
ROLLBACK;  -- RollBACK

SET autocommit = 1; -- Restore Defaults

3. Index

3.1. Classification of indexes

• Primary Key Index (PRIMARY KEY)
  • Unique identification, primary key is not repeatable, only one column can be used as primary key
• Unique Index (UNIQUE KEY)
  • Avoid duplicate listings, unique indexes can be duplicated, and multiple columns can identify bitwise unique indexes
• General Index (KEY/INDEX)
  • By default, index.key keyword to set
• FullText Index
  • Only under a specific database engine, MyISAM
  • Quick positioning data

Basic Grammar

-- Use of indexes
-- 1,Adding an index to a field when creating a table
-- 2,Add index after creation

-- Show all index information
SHOW INDEX FROM student

-- Add a full-text index (index name) column name
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPLAIN Analysis sql Status of implementation 

EXPLAIN SELECT * FROM student; -- Non-Full-Text Index

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('Liu');

3.2. Test Index

CREATE TABLE `app_user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) DEFAULT '' COMMENT 'nickname',
  `email` VARCHAR(50) NOT NULL COMMENT 'email',
  `phone` VARCHAR(20) DEFAULT '' COMMENT 'Cell-phone number',
  `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT 'Gender (0):Male; 1: female)',
  `password` VARCHAR(100) NOT NULL COMMENT 'Password',
  `age` TINYINT(4) DEFAULT '0' COMMENT 'Age',
  `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='app User table'


-- Insert 1 million data 55.176 sec
DELIMITER $$  -- Function must be written before writing, flag
CREATE FUNCTION mock_data() 
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
     INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('user',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i = i+1;
   END WHILE;
   RETURN i;
END;
SELECT mock_data();


SELECT * FROM app_user WHERE `name` = 'User 9999'; --  0.993 sec
SELECT * FROM app_user WHERE `name` = 'User 9999'; --  1.098 sec
SELECT * FROM app_user WHERE `name` = 'User 9999'; --  0.788 sec

EXPLAIN SELECT * FROM app_user WHERE `name` = 'User 9999';

SELECT * FROM student

-- id _ Table Name _ Field name
-- CREATE INDEX Index Name on surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name` = 'User 9999'; -- 0.001 sec
SELECT * FROM app_user WHERE `name` = 'User 9999'; 
EXPLAIN SELECT * FROM app_user WHERE `name` = 'User 9999';

Indexes have small users in small amounts of data, but the difference is obvious in large amounts of data ~

3.3. Indexing Principles

• Not as many indexes as possible
• Do not index process change data
• Tables with small amounts of data do not need to be indexed
• An index is usually added to a field commonly used for queries!

Hash type index~

Btree:InnoDB default data structure~

Read: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

4. Rights Management and Backup

4.1. User Management

User table: mysql.user

Essential: Read this table for additions and deletions

-- Create User CREATE USER User name IDENTIFIED BY 'Password'
CREATE USER wode  IDENTIFIED BY '123456'

-- Change password (change current user password)
SET PASSWORD = PASSWORD('123456')

-- Modify password (change specified user password)
SET PASSWORD FOR wode = PASSWORD('123456')

-- rename RENAME USER Original name TO New name
RENAME USER wode TO wode2

-- User Authorization ALL PRIVILEGES Full permissions, Libraries.surface
-- ALL PRIVILEGES Everything but authorizing others to do it   
GRANT ALL PRIVILEGES ON *.* TO wode2

-- Query permissions
SHOW GRANTS FOR wode2   -- View permissions for specified users
SHOW GRANTS FOR root@localhost  

-- ROOT User rights: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- Revoke Permission REVOKE Which permissions, which library to revoke, to whom to revoke
REVOKE  ALL PRIVILEGES ON *.* FROM wode2

-- delete user
DROP USER wode

4.2, MySQL backup

Why backup:

• Ensure important data is not lost
• Data Transfer

How MySQL database is backed up

• Direct Copy of Physical Files

• Export manually in a visualization tool like Sqlyog

  • In the table or library you want to export, right-click and choose Backup or Export

• Export mysqldump command line usage using the command line

  # Mysqldump-h host-u username-p password database table name>physical disk location/file name
  mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
  
  # Mysqldump-h host-u username-p password database table 1 Table 2 Table 3 > physical disk location/file name
  mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql
  
  # Mysqldump-h host-u username-p password database>physical disk location/file name
  mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql
  
  # Import
  # Switch to the specified database with login
  # source backup file
  source d:/a.sql
  
  mysql -u User name -p Password Library Name< Backup Files
  

Suppose you want to back up the database to prevent data loss.

Make database a friend!sql file to someone else!

5. Specification of database design

5.1. Why design is needed

When the database is complex, we need to design it

Bad database design:

• Data redundancy, waste of space
• Inserting and deleting databases can be cumbersome and unusual [blocking the use of physical foreign keys]
• Poor program performance

Good database design:

• Save memory space
• Ensure the integrity of the database
• Convenient for us to develop the system

In software development, about database design

• Analysis Requirements: Analyze business and database needs to be addressed
• Summary Design: Design Diagram E-R Diagram

Steps to design a database: (personal blog)

• Collect information and analyze requirements
  • User table (user login logoff, user profile, blog, category creation)
  • Categories (article categories, who created them)
  • Article Table (article information)
  • Comment Table
  • Friends List (Friends Chain Information)
  • Custom table (system information, a key word, or some primary field) key:value
  • Talk about tables (express your mood...id...content....create_time)
• Identifying entities (dropping requirements on each field)
• Identify relationships between entities
  • Blog: user --> blog
  • Create categories: user -> category
  • Attention: user ->user
  • Friend chain: links
  • Comment: user-user-blog

5.2, Three Paradigms

Why do I need data normalization?

• Repeated information
• Update Exception
• Insert Exception
  • Information cannot be displayed properly
• Delete Exception
  • Loss of valid information

First Norm (1NF)

Atomicity: guarantees that each column is indivisible

Second Norm (2NF)

Prerequisite: satisfy the first paradigm

Each table describes only one thing

Third Norm (3NF)

Prerequisite: satisfy the first and second paradigms

The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, not indirectly.

(specification of database design)

Specification and performance issues

Associated queries must have no more than three tables

• Consider the needs and objectives of commercialization, (cost, user experience!)Database performance is more important
• When it comes to standardizing performance, you need to think about it appropriately!
• Intentionally add redundant fields to some tables.(from multi-table queries to single-table queries)
• Intentionally adding some computed columns (queries from large to small data volumes: indexes)