MySQL basic operations, SQL tips, and common optimizations for SQL

First, common operation

1. Duplicate table structure CREATE table t2 like T1

Copy table data INSERT INTO t2 SELECT * from t1

2. mysql Index
ALTER TABLE is used to create a normal index, a unique index, or a primary key index
ALTER TABLE T add index index_name (column_list)
ALTER TABLE T add unique (column_list)
ALTER TABLE T add primary key (column_list)

CREATE INDEX index_name on table (column_list)
Create unique index index_name on table (column_list)

Drop index index_name on t
ALTER TABLE T DROP INDEX index_name
ALTER TABLE t drop PRIMARY key

ALTER TABLE t modify ID int NOT NULL
Show Index Form T

3. View
Create View v as select * from T where id>5
Insert into T (name) VALUES ("User"), ("User1")
?

View |? Index
Alter VIEW | CREATE VIEW | Drop view
View errors occur once the table associated with the view is corrupted
4. Built-in functions
String:
Concat ("Hello", "World") as HW link string
LCase ("GAFG")//Turn lowercase
UCase ("JJJ")
Length (string)//Lengths
LTrim (String)//Remove front-end spaces
RTrim (String)//Remove right-end spaces
Repeat (string,count)//Repeat Count times
Replace (STRING,S1,S2)//convert S1 to S2 in string
SUBSTRING (str,position,[length])//In Str, starting from position to take the length of characters
Space (count) generates count of spaces

Mathematical functions:
Bin (number)//decimal goto Binary
Ceiling (number)//rounding up
Floor (number)//down rounding
Max (NUM1,NUM2)//Take maximum value
Min (num1.num2)
sqrt (number)//open Square
RAND ()//returns a random value within 0-1
Date function:
Curdate ()//return current date
Curtime ()
Unix_timestamp (date)//returns the current date
Week (date)//returns date as the week ordinal of the year
Year (date)//return date of day
DateDiff (EXPR,EXPR2)//Two-time interval between days


5. mysql preprocessing statements
To set up STMT1 preprocessing:
Prepare STMT1 from "select * from t where Id>?

‘
Set a variable: set @i = 1;
Run STMT1 preprocessing: Execute STMT1 using @i
Set @i=5
Execute STMT1 using @i
Delete preprocessing: Drop prepare STMT1


6. mysql Transaction processing
Turn off your own unsolicited commit function: Set autocommit=0.
Delete from t where id = 11;
SavePoint P1;
Delete from t where id = 12;
SavePoint P2;
Rollback to P1;
rollback;//Restore to the most original restore point

ALTER TABLE T Engine=innodb. Transaction control is only useful for INNODB engines

7. mysql Storage procedure
CREATE PROCEDURE P2 ()
Begin
Set @i = 3;
While @i<=10 do
Insert into T (name) VALUES (concat (' hh ', @i));
Set @[email protected]+1;
End while;
End

SHWO CREATE PROCEDURE P2;
Call P2;

8. mysql Trigger
Create trigger TG before insert on T for each row
Begin INSERT INTO T2 (name) values (new.name)
End

Create trigger TG before delete on t for each row
Begin delete from t2 where name = Old.name
End

9. Rearrange auto_increment
Use truncate when emptying the table
ALTER TABLE t auto_increment = 1;

ii. Common SQL Tips
1, the use of regular expressions (as in other languages)
Select "Linux is very good!" regexp ". *"//Match All
Select "Linux is very good!" regexp "^linux"
Select email from user where emial regexp "@163[.,]com$"
=
Select email from user where emial like '%@163.com$ ' or emial like '%@163,com$ '

2and skillfully use RAND () to extract the line
Random number function between 0-1 select rand () *100
SELECT * FROM T ORDER by Rand ();//Arrange table data immediately
SELECT * FROM T ORDER by rand () limit 3;//remove three samples
3, using the group by's with Roolup sentence statistics, cannot be used with the order by
Ability to retrieve a number of other packet aggregation information
select * from T;
SELECT *, COUNT (*) from the T Group by c1,c2//first aggregation to C1
SELECT *, COUNT (*) from the T Group by C1,C2 with Roolup
4, using bit group functions to do statistics
You can use the Bit_and, bit_or functions to complete the statistical work when using the group by statement. The function of these two functions is mainly to do the numerical
The logical bitwise operation between.
Select Id,bit_or (kind) from the T Group by ID
5, the use of foreign keys to pay attention to the problem
CREATE table T (ID, name,foreign key (id) references T1 (ID) on the DELETE cascade on UPDATE cascade)
InnoDB types of tables support foreign keys. The MyISAM class is a new table despite the success of creating a foreign key. However, the main reason is that foreign keys are not supported.

6, the use of help in MySQL
？ %
？ Create
? opti%
? reg%
？ Contents all the Help information in MySQL


third, SQL optimization
1, half of the steps to optimize SQL statements
Show status learn about the frequency of various SQL operations
Show [session|globe] status;
Show global status;
Show status like ' com_% '
Show global status like "com_%"

For the InnoDB storage engine only:
Innodb_rows_read number of Run select operations
innodb_rows_updated\inserted\deleted
Show variables like ' slow% '
Show variables like ' long% '
Show status like connections link MySQL number
Show status like uptime the number of seconds that the server has been working
Show status like slow_queries number of slow queries

Locating SQL statements that run inefficiently
EXPLAIN/DESC select * FROM table where id>10
DESC SELECT * FROM table where id>10\g
2, indexing problems
One of the most common optimization methods
MyISAM stores the data and indexes of the engine when it is actively stored separately. A single file, respectively.
The data and indexes of the tables of the INNODB storage engine are stored in the same table space, but can have multiple files
MySQL does not support function indexing, but it can index a portion of a column before it, such as the Name field, to the name
The first 4 characters are indexed, which can greatly reduce the size of the index file
CREATE index index_name on t (name (4))
MySQL indexes are used to find rows with specific values in a column at high speed, which is the best way to improve the performance of select operations when using indexes on related columns
1. Use index
For creating multi-column indexes, the leftmost column is used only for the criteria to be queried, and the index is usually used
CREATE index iname on T (name,age)//Compliance Index
2, there is an index but do not use the cable
Assuming that MySQL expects to use an index slower than a full table scan, the index is not used, such as keypart evenly distributed between 1-100. Using indexes when querying is not very good
Eg:select * from T where keypart>1 and keypart<99
Suppose you use the T table and the Where condition does not use = to index the column. The index is not used.
Conditions separated by or are assumed to have indexes on the columns in the condition before or. There are no indexes in the subsequent columns,
The indexes involved are not used. Indexes are required for indexing
Suppose the column type is a string, but at query time an integer constant is paid to the character column name. Then there's an index on the name column that doesn't work.

3, often use the optimization of SQL
Mass Insert data: infile outfile
When importing data with the load command, the appropriate settings can increase the speed of the import.


4. Frequently used SQL optimizations
Insert statement, use the INSERT statement of multiple value tables as much as possible. This can greatly reduce the consumption of client and database connection shutdown
Ability to use Insert delayed statements for higher efficiency

MySQL basic operations, SQL tips, and common optimizations for SQL