Functions for MySQL

Common Functions

-- Mathematical operations

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

-- String function
SELECT CHAR_LENGTH('2323232') -- Return string length
SELECT CONCAT('I','233') -- Split String
SELECT INSERT('java',1,2,'cccc') -- Replace a length from a location
SELECT UPPER('abc') 
SELECT LOWER('ABC')
SELECT REPLACE('Persistence leads to success','Insist','Strive')

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

-- Time and Date Function (remember)
SELECT CURRENT_DATE() -- Get the current date
SELECT CURDATE() -- Get the current date
SELECT NOW() -- Get the current date
SELECT LOCATIME()  -- 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()

Aggregation Functions (Common)

Function Name	describe
COUNT()	count
SUM()	Summation
AVG()	average value
MAX()	Maximum
MIN()	minimum value
...

Database Level MD5 Encryption (Extended)

What is MD5

Major enhancements are algorithm complexity irreversibility.

MD5 is irreversible, specific MD5 is the same

MD5 cracking principle, there is a dictionary behind it, MD5 encrypted values, pre-encrypted values

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,'Zhang San','123456'),(2,'Li Si','123456'),(3,'King Five','123456')

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

-- Encrypt on insert

INSERT INTO testmd5 VALUES(4,'Xiao Ming',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'red',MD5('123456'))

-- How to verify the password passed by the user MD5 Encrypt and then compare the encrypted values
SELECT * FROM testmd5 WHERE `name`='red' AND pwd=MD5('123456')

affair

What is a transaction

Transaction Principles: ACID Principles Atomicity, Consistency, Isolation, Persistence (Dirty Read, Magic Read...)

Atomicity

Either all succeeded or all failed

Consistency

Consistent data integrity before and after transactions

Isolation

Do not interfere with each other when transactions produce multiple concurrencies

Durability - Transaction Commit

Once committed, transactions are irreversible and persisted to the database

Execute Transactions:

-- mysql Automatically open transaction commit
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 SQP 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 Points -- Delete Save Point

Simulate transfer transactions:

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),('B',10000)

-- Simulated Transfer: Transaction
SET autocommit = 0; -- Turn off automatic submission
START TRANSACTION -- Open transactions (a set of transactions)
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A Transfer to B
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B Received Money

COMMIT ; -- Submit Transaction
ROLLBACK ; -- RollBACK

SET autocommit=1 -- Restore Defaults

Indexes

MySQL index is very important for the efficient operation of MySQL. Indexing can greatly improve the speed of MySQL retrieval.

Classification of indexes

In a table, there can only be one primary key index and more than one unique index

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 be identified as unique indexes

General Index (KEY/INDEX)
Default, index,key keyword to set

Full-text Index (FULLTEXT)
Only under a specific database engine, MyISAM
Quick positioning data

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

-- Add an index
ALTER TABLE surface ADD FULLTEXT INDEX Index name (field name)

-- EXPLAIN Analysis sql Status of implementation
EXPLAIN SELECT * FROM student -- Non-Full-Text Index
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('Liu')

Test Index

Increasing query speed by adding 1 million data test indexes

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- Insert 1 million data
DELIMITER $$ --  Write function before writing
CREATE FUNCTION mock_data()
RETURNS INT 
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;

WHILE i<num DO
-- Insert statement
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('user',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));

SET i = i+1;
END WHILE;
RETURN i;


END;

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('user',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))


SELECT mock_data();

SELECT * FROM app_user WHERE `name`='User 9999' -- Almost half a second

EXPLAIN SELECT * FROM app_user WHERE `name`='User 9999'  -- Query 99999 records

-- id _ Table Name_Field name
-- create index on field
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='User 9999'  -- Query a record

Indexes are not useful when you have small data, but they differ significantly when you have large data.

Indexing principle

• Not as many indexes as possible
• Do not index data that changes frequently
• Tables with small amounts of data do not need to be indexed
• Indexes are typically added to fields commonly used for queries

Data structure of index

Hash type index

Btree: Default innodb data structure

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

Rights Management and Backup

user management

Visual Management of SQLyog

SQL Command Action
User table: mysql.user

Essential: Check this table for additions, deletions, and changes

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

-- Change password (change current password)
SET PASSWORD = PASSWORD('111111')


-- Modify password (change specified user password)

SET PASSWORD FOR sanjin = PASSWORD('111111')


-- rename  rename user Original name to New name
RENAME USER sanjin TO sanjin2

-- User Authorization   ALL PRIVILEGES All permissions libraries, tables
-- ALL PRIVILEGES Everything but authorization
GRANT ALL PRIVILEGES ON *.* TO sanjin2

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


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

-- delete user
DROP USER sanjin2

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 SQLLyog
• In the table or library you want to export, right-click to select Backup and Export

Specification database design

When the database is complex, we need to design it

Bad database design:

• Data redundancy, waste of space
• Database insertion and deletion can be cumbersome, exception [block 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 example)

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)
• Friends List (Friends Chain Information)
• Custom tables (system information, a key word, or some primary field)
• Talk about tables (express mood... id, content, time)

Identifying entities (dropping requirements on each field)

Identify relationships between entities

• Blog user ->blog
• Create category user - Category
• Focus on user ->user
• Friend Chain ->links
• Comment on user ->user

Three Paradigms

Why do I need data normalization?

• Repeated information
• Update Exception
• Insert Exception
• Delete Exception
  Unable to properly display exception
  Loss of valid information

Three Paradigms
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 and performance issues

• Table of associated query, no more than three tables
• Performance of databases is more important when considering the needs and objectives of commercialization (cost and user experience)
• When it comes to standardizing performance, you need to think about it appropriately.
• Intentionally add redundant fields to some tables (from multiple tables to single tables)
• Intentionally adding some computed columns (queries from large to small data volumes: indexes)