I. Common Operations
1. Copy the 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 common index, unique index, or 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
If the table associated with the view is corrupted, an error occurs in the view.
4. built-in functions
String:
Concat ("hello", "world") as hw link string
Lcase ("GAFG") // converts it to lowercase
Ucase ("jjj ")
Length (string) // length
Ltrim (string) // remove leading space
Rtrim (string) // remove the right-side Space
Repeat (string, count) // repeat count times
Replace (string, s1, s2) // replace s1 WITH s2 in string
Substring (str, position, [length]) // in str, the length starts from position.
Space (count) generates count Spaces
Mathematical functions:
Bin (number) // convert decimal to binary
Ceiling (number) // rounded up
Floor (number) // round down
Max (num1, num2) // obtain the maximum value
Min (num1.num2)
Sqrt (number) // Square
Rand () // returns a random value within 0-1.
Date functions:
Curdate () // returns the current date
Curtime ()
Unix_timestamp (date) // returns
Week (date) // returns the week number in the year of date.
Year (date) // returns the year of date.
Datediff (expr, expr2) // the number of days between two times
5. mysql pre-processing statement
Set stmt1 preprocessing:
Prepare stmt1 from 'select * from t where id>? '
Set a variable: set @ I = 1;
Execute stmt1 preprocessing: execute stmt1 using @ I
Set @ I = 5
Execute stmt1 using @ I
Delete preprocessing: drop prepare stmt1
6. mysql Transaction Processing
Disable Automatic submission: 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 original Restore Point
Alter table t engine = innodb; // transaction control is only useful to the innodb engine
7. mysql storage procedure
Create procedure p2 ()
Begin
Set @ I = 3;
While @ I <= 10 do
Insert into t (name) values (concat ('hh', @ I ));
Set @ I = @ I + 1;
End while;
End;
Shwo create procedure p2;
Call p2;
8. mysql triggers
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. Rearranging auto_increment
Use truncate to clear the table
Alter table t auto_increment = 1;
Ii. Common SQL skills
1Use of Regular Expressions (same as 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 $'
2Use rand () to extract the line
Random Number function between 0 and 1 select rand () * 100
Select * from t order by rand (); // sort table data immediately
Select * from t order by rand () limit 3; // retrieve three samples.
3Use the with roolup clause of group by for statistics. It cannot be used with order.
More group aggregation information can be retrieved.
Select * from t;
Select *, count (*) from t group by c1, c2 // first aggregates c1
Select *, count (*) from t group by c1, c2 with roolup
4Use bit group functions for Statistics
When using the group by statement, you can use bit_and and bit_or functions to complete statistics. These two functions are mainly used for numerical values.
Logical bitwise operation.
Select id, bit_or (kind) from t group by id
5Usage of Foreign keys
Create table t (id, name, foreign key (id) references t1 (id) on delete cascade on update cascade)
Innodb tables support foreign keys. New tables of the myisam class can be created successfully, but they do not work because they do not support foreign keys.
6Mysql help usage
? %
? Create
? Opti %
? Reg %
? Contents mysql All help information
Iii. SQL Optimization
1Optimization of SQL statements
Show status
Show [session | globe] status;
Show global status;
Show status like 'com _ %'
Show global status like "com _ %"
For innodb Storage engines only:
Innodb_rows_read: Number of select operations performed
Innodb_rows_updated \ inserted \ deleted
Show variables like 'slow %'
Show variables like 'long %'
Show status like connections to mysql
Show status like the number of seconds that the uptime server has been working on
Show status like slow_queries slow Query Count
Locate SQL statements with low execution efficiency
Explain/desc select * from table where id> 10
Desc select * from table where id> 10 \ G
2Index Problems
One of the common optimization methods
The myisam storage engine automatically stores data and indexes separately. Each is a unique file.
The table data and indexes of the innodb Storage engine are stored in the same tablespace, but can contain multiple files.
Mysql does not support function indexing, but can index the first part of the column. For example, for the name field, you can
Index with the first four characters. This feature can greatly reduce the size of the index file.
Create index index_name on t (name (4 ))
Mysql indexes are used to quickly find rows with specific values in a column. Using indexes on related columns is the best way to improve the performance of select operations.
1. Use Indexes
For creating multi-column indexes, the index is generally used as long as the leftmost column is used in the query conditions.
Create index iname on t (name, age) // match the index
2. There is an index but no cable is used
If mysql estimates that index usage is slower than full table scan, no index is used. For example, if the keypart is evenly distributed between 1 and, the index usage is not very good during query.
Eg: select * from t where keypart> 1 and keypart <99
If the t table is used and = is not used in the where condition, the index is not used.
If the column in the condition prior to or has an index, the column following the condition is not indexed,
The involved indexes are not used, and the indexes are required.
If the column type is a string, but an integer constant is assigned to the name of the column in the dense type during the query, the index on the name column will not be used.
3. Optimization of common SQL statements
Insert data in large batches: infile outfile
When using the load command to import data, you can set appropriate settings to increase the import speed,
4. Common SQL Optimization
Insert statement. Try to use the insert statements of multiple value tables. This can greatly reduce the consumption of closed connections between the customer and the database.
You can use the insert delayed statement to improve efficiency.
