MySQL Common command Collection (linux/windows) _mysql

View database status:service mysqld Status

Start database:service mysqld start

Access to the database:mysql-u root-p123456;

List database: showdatabases;

Select database: UsedatabaseName;

List table: showtables;

Displays the properties of the table column: Showcolumns from TableName;

Establish database:source fileName.txt;

Match character: You can use the wildcard character _ to represent any one character,% for any string;

Add a field:ALTER TABLE tabelname add column fieldName datetype;

Add more fields:ALTER TABLE tabelname add column fieldName1 datetype,add columns fieldName2 datetype;

Multiline command input: Note that the word cannot be broken; when inserting or changing data, you cannot expand a field's string into multiple lines, otherwise hard return will be stored in the data;

Add an Administrator account:Grant all on *.* to user@localhost identified by "password";

Enter the end of each statement with a semicolon '; ', or add ' \g ';

Query time:select Now ();

Query Current User:Select User ();

Query database version:select version ();

Query the database currently in use:Select db ();

1 . Delete the students datasheet in the Student_course database:

Rm-f student_course/students.*

2, back up the database: (Database test Backup)

 Mysqldump-u root-p Test>c:\test.txt

Backup form: (Back up the MyTable table under test database)

 Mysqldump-u root-p Test Mytable>c:\test.txt

To import backup data to a database: (Back to test database)

 Mysql-u root-p Test

3, create temporary table: (Create temporary table Zengchao)

 Create temporary table Zengchao (name varchar (10));

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

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

5. Copy the structure of a table from an existing table

 CREATE TABLE table2 SELECT * FROM table1 where 1<>1;

6. Copy table

 CREATE TABLE table2 SELECT * FROM table1;

7, rename the table

 ALTER TABLE table1 Rename as table2;

8, modify the column type

 ALTER TABLE table1 Modify ID int unsigned;//modify column ID type int unsigned

 ALTER TABLE table1 change ID sid int unsigned;//modify Column I D is named Sid, and the attribute is modified to int unsigned

9. CREATE index

 ALTER TABLE table1 ADD index ind_id (ID);

 CREATE INDEX ind_id on table1 (ID);

 Create unique index ind_id on table1 (ID);//Create a unique index

10. Delete Index

 Drop index idx_id on table1;

 ALTER TABLE table1 DROP INDEX ind_id;

11, union character or multiple columns (Connect column ID with ":" and column name and "=")

 Select Concat (ID, ': ', name, ' = ') from students;

12, limit (10 to 20) < number of the first Recordset is 0>

 SELECT * FROM students order by ID limit 9, 10;

13, the MySQL does not support the function

Transactions, views, foreign keys and referential integrity, stored procedures, and triggers

14, MySQL will use the index operation symbol

<,<=,>=,>,=,between,in, with no% or _ beginning like

15, the use of the index disadvantage

1) slowing down the speed of data modification;

2) occupy disk space;

3 Increase the burden of the query optimizer;

When the query optimizer generates an execution plan, the index is considered, and too many indexes increase the workload for the query optimizer, resulting in the inability to select the optimal query scheme;

16. Index Efficiency analysis

Methods: Add explain to the general SQL statement;

Analysis of the meaning of the result:

1) Table: list name;

2 Type: Types of connections, (ALL/RANGE/REF). Where ref is the ideal;

3) Possible_keys: The index name of the query can be used;

4 key: The actual use of the index;

5) Key_len: The length of the part used in the index (bytes);

6 Ref: Display column name or "Const" (do not understand what meaning);

7 rows: Displays the number of lines that MySQL believes must be scanned before the correct results are found;

8) Extra:mysql's recommendations;

17, using a shorter fixed-length column

1 Use a shorter data type as far as possible;

2 Use fixed-length data type as far as possible;

(a) using char instead of varchar, the fixed-length data processing ratio becomes longer and faster;

b for frequently modified tables, the disk is easily fragmented, thus affecting the overall performance of the database;

c A table with fixed-length data rows is easier to reconstruct in the event of a data table crash. With fixed-length rows of data, the start of each record is a multiple of the length of the fixed record and can be easily detected, but the use of variable-length data rows is not necessarily the point;

D for MyISAM types of data tables, although converted to fixed-length data columns can improve performance, but occupy a large space;

18. Use NOT NULL and enum

Try to define a column as NOT NULL, which makes the data faster, requires less space, and when querying, MySQL does not need to check for exceptions, or null values, to optimize the query;

If a column contains only a limited number of specific values, such as gender, validity, or year of entry, in this case you should consider converting it to the value of the enum column, which is handled faster because all the enum values are represented in the system by an identity value;

19. Use Optimize table

For frequently modified tables, it is easy to fragment, making it necessary to read more disk blocks and reduce query performance when querying the database. Disk fragmentation issues exist with variable-length tables, which are more prominent for BLOB data types because of their very large size changes. You can defragment the database by using the Optimize table, to ensure that it does not degrade, and to optimize the data tables that are affected by the fragmentation. Optimize table can be used for MyISAM and BDB types of data tables. In fact any defragmentation method is to use mysqldump to dump the data table, and then use the transferred file and then create a new data table;

20. Use procedure analyse ()

You can use the procedure analyse () to display the best types of recommendations, which are simple to use, followed by the procedure analyse () at the end of the SELECT statement, for example:

 SELECT * From students procedure analyse ();

 SELECT * From students procedure analyse (16,256);

The second statement requires procedure analyse () not to suggest that there are more than 16 values or enum types that contain more than 256 bytes, and that the output can be long if there are no restrictions;

21, use the query cache

1 How the query cache works:

The first time a SELECT statement is executed, the server remembers the query's textual content and query results, stored in the cache, and returns the result directly from the cache the next time the statement is encountered, and when the data table is updated, any cached queries for the datasheet become invalid and discarded.

2 Configure Cache Parameters:

Variables: Query_cache _type, query caching mode of operation. There are 3 medium modes, 0: not cached; 1: Cached queries, unless with select Sql_no_cache, 2: Only those queries that begin with select Sql_cache are cached as needed; Query_cache_size: Sets the maximum result set size of the query cache. Larger than this value will not be cached.

22, adjust the hardware

1 Install more memory on the machine;

2 Increase the speed of the hard drive to reduce I/O wait time;

Seek time is the main factor to determine performance, the movement of the head is the slowest, and once the head is positioned, it is quickly read from the track;

3 Redistribution of disk activity on different physical hard disk devices;

If possible, store the busiest databases on different physical devices, which are different from the different partitions that use the same physical device, because they will contend for the same physical resource (head).