MySQL basic command summary of common commands

Launch: net start mysql;
Entry: ES5en-ES6en ES7en-ES8en/ES9en-ES10en ES11en-ES12en ES13en-ES14en databaseName;
List the database: show databases;
Select database: use databaseName;
List of forms: show tables;
Display table column attributes: show columns from tableName;
Establish database: source ES32en. txt;
Matching characters: Wildcard _ for any 1 character, % for any string;
Add 1 field: alter table tabelName add column fieldName dateType
Add multiple fields: alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
Multi-line command input: note that the word cannot be disconnected; When inserting or changing data, the string of the field cannot be expanded into multiple lines, otherwise the hard return will be stored in the data;
Add 1 administrator account: grant all on *.* to user @localhost by "password";
Put a semicolon at the end of each statement; ', or '\g';
Query time: select now();
Query current user: select user();
Query database version: select version();
Query the currently used database: select database();

1. Delete students data table in student_course database:

rm -f student_course/students.*

2. Backup database :(back up database test)

mysqldump -u root -p test>c:\test.txt 
 Backup table: ( The backup test Under the database mytable form ) 
mysqldump -u root -p test mytable>c:\test.txt 
 Import backup data into the database: ( Guide to test The database ) 
mysql -u root -p test

3. Create temporary table (create temporary table zengchao)

create temporary table zengchao(name varchar(10));

4, to create a table is to determine whether the table exists

create table if not exists students( ... );

5, from the already existing table copy table structure

create table table2 select * from table1 where 1<>1;

6. Copy the table

create table table2 select * from table1;

7. Rename the table

alter table table1 rename as table2;

8. Modify the type of the column

alter table table1 modify id int unsigned;// Modify the column id The type of int unsigned 
alter table table1 change id sid int unsigned;// Modify the column id Name for sid , and change the property to int unsigned

9. Create indexes

alter table table1 add index ind_id (id); 
create index ind_id on table1 (id); 
create unique index ind_id on table1 (id);// Set up only 1 Sex index 

10. Delete the index

drop index idx_id on table1; 
alter table table1 drop index ind_id;

11. Joint characters or columns (join column id with ":" and column name with "=")

select concat(id,':',name,'=') from students;

12. limit(select 10 to 20) < The first recordset is numbered 0 >

select * from students order by id limit 9,10;

13. Features not supported by MySQL
Transactions, views, foreign keys and referential integrity, stored procedures and triggers

MySQL will use the operation symbol of the index

<,<=,>=,>,=,between,in, Don't take % or _ At the beginning of like

15. Disadvantages of using indexes
1) To slow down the speed of adding, deleting and revising data;
2) Occupy disk space;
3) Increase the burden on the query optimizer;
When the query optimizer generates an execution plan, indexes will be considered. Too many indexes will increase the workload of the query optimizer, leading to the inability to choose the optimal query scheme.

16. Analyze index efficiency
Method: Add explain to the 1-like SQL statement;
Implications of the analysis results:
1) table: Table name;
2) type: Type of connection, (ALL/Range/Ref). ref is the most ideal;
3) possible_keys: Index name available for query;
4) key: indexes actually used;
5) key_len: the length (in bytes) of the part used in the index;
6) ref: displays the column name or "const";
7) rows: Display the number of rows that MySQL believes must be scanned before finding the correct result;
8) extra: Suggestions of MySQL;

17. Use shorter fixed-length columns
1) Use as short a data type as possible;
2) Use fixed-length data types as far as possible;
a) USES char instead of varchar. Fixed-length data processing is faster than variable length data processing.
b) For frequently modified tables, disk fragmentation can easily affect the overall performance of the database;
c) 10000 data table crashes and tables with fixed length data rows are easier to reconstruct. Using data rows of fixed length, the starting position of each record is a multiple of the fixed record length, which can be easily detected, but using data rows of variable length is not fixed.
For data tables of type MyISAM, converting to fixed-length data columns can improve performance, but it also takes up a lot of space;

18. Use not null and enum
Try to define the column as not null, which can make the data come out faster and require less space. Moreover, MySQL does not need to check the existence of special cases, namely null value, to optimize the query.
If column 1 contains only a limited number of specific values, such as gender, validity, or year of enrollment, in this case, it should be considered to convert it to the value of column enum, which processes faster because all enum values are represented in the system as identified values;

Use optimize table
For frequently modified tables, fragmentation is easy, resulting in more disk blocks having to be read when querying the database, reducing query performance. Tables with variable length have the disk fragmentation problem, which is more pronounced for the blob data type because of its large size variation. By using optimize table to defragment, database performance can be guaranteed and tables affected by fragmentation can be optimized. optimize table can be used with data tables of type MyISAM and BDB. In fact, any defragmenting method USES mysqldump to save the data table, and then re-creates the data table with the saved file.

20. procedure analyse()
You can use procedure analyse() to display the best type of advice, which is as simple as adding procedure analyse() to the select statement. Such as:

select * from students procedure analyse(); 
select * from students procedure analyse(16,256); 

The second statement asks procedure analyse() not to suggest enum types with more than 16 values, or enum types with more than 256 bytes. The output can be long if there is no limit;

21. Use query cache
1) Working mode of query cache:
The first time an select statement is executed, the server remembers the text content and query results of the query, stores them in the cache, and returns the results directly from the cache the next time the statement is encountered. When a table is updated, any cached queries for that table become invalid and are discarded.
2) Configure cache parameters:
Variable: query_cache _type, operation mode of query cache. There are 3 modes, 0: no caching; 1: Cache queries unless they start with select sql_no_cache; 2: Cache only those queries starting with select sql_cache as needed; query_cache_size: Sets the maximum result set size of the query cache. Anything larger than this value will not be cached.

22. Adjust the hardware
1) Install more memory on the machine;
2) Add faster hard disks to reduce I/O wait time;
Track seeking time is a major determinant of performance. Moving the head verbatim is the slowest. Once the head is positioned, reading from the track is fast.
3) Redistribute disk activities on different physical hard disk devices;
If possible, keep the busiest databases on different physical devices, which is different from using different partitions on the same 1 physical device because they will be competing for the same physical resources (heads)