MySQL command

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Start: net start mySql;
Get into: mysql -u root -p/mysql -h localhost -u root -p databaseName;
Listed in the database: show databases;
Select the database: use databaseName;
Listed in the table: show tables,
To display the table column properties: show columns from tableName,
Establish database: source fileName.txt;
Character: can use wildcards _ represents any one character,% represents any string;
To add a field: alter table tabelName add column fieldName dateType;
Add more fields: alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
Multi line command: be careful not to word off; when the insert or change data, can not be expanded to many fields string line, otherwise hard return will be stored to the data;
Add an administrator account: grant all on *.* to user@localhost identified by "password";
Each statement after the input at the end to fill the semicolon ';', or adding'\g'can also,
Query time: select now();
Query the current user: select user();
Query the database version: select version();
The use of the database query: select database();
1, Delete the students data table in the student_course database:
rm -f student_course/students.*
2, Backup database (the database: Test backup)
mysqldump -u root -p test>c:\test.txt
Backup table: (backup of test database under the mytable form)
mysqldump -u root -p test mytable>c:\test.txt
The backup data into database: (back to test database)
mysql -u root -p test
3, Create temporary table: (a temporary table Zengchao)
create temporary table zengchao(name varchar(10));
4, Create table table to determine whether there is
create table if not exists students(……);
5, From the structure has some form of the replicated table
create table table2 select * from table1 where 1<>1;
6, A replicated table
create table table2 select * from table1;
7, Table rename
alter table table1 rename as table2;
8, Modify the column types
alter table table1 modify id int unsigned;//Modify column ID of type int unsigned
alter table table1 change id sid int unsigned;//Modify column ID name for SID, but also to modify the properties of 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 unique index
10, Drop index
drop index idx_id on table1;
alter table table1 drop index ind_id;
11, Combined with characters or multiple column (column ID and ":" and name column and "=" connection)
select concat(id,':',name,'=') from students;
12, Limit (choose 10 to 20) <the first record number is 0>
select * from students order by id limit 9,10;
13, MySQL does not support function
Affairs, view, foreign key and referential integrity, stored procedures and triggers
14, MySQL will use the index operation symbols
<,<=,>=,>,=,between,in,Like without% or _ beginning
15, The disadvantages of using index
1)Slow down to the speed of data,
2)Use disk space,
3)Increase the burden of the query optimizer,
When the query optimizer generates execution plans, will consider the index, too many index will increase the workload for the query optimizer, unable to select the optimal scheme of,
16, Analysis of the efficiency index
Methods: the explain SQL statement in general with,
Analysis of the implication of the results:
1)table: Table name,
2)type: The type of connection, (ALL/Range/Ref). Where ref is the most ideal,
3)possible_keys: A query can use an index name,
4)key: The actual use of the index,
5)key_len: Part of the length is used in the index (byte),
6)ref: Display column names or "const" (don't know what that means.),
7)rows: Display MySQL that must be scanned rows before finding the correct results,
8)extra: The recommendations of the MySQL,
17, Using short fixed length columns
1)The use of shorter data types as possible,
2)The use of fixed length data types as possible,
a)Use char instead of varchar, fixed length data processing than grow faster,
b)For frequent changes in surface, easy to form debris disk, thus affecting the overall database performance,
c)In case of data table collapse, the use of fixed length data rows of the table easier to re structure. Using data for fixed length, each recording start position is fixed record length ratio, can be detected easily, but the data for the use of variable length may not.,
d)For the MyISAM data table, although the conversion into fixed length data columns can improve performance, but also occupy space,
18, The use of not null and enum
As the definition of a column as not null, which can make the data out faster, less space required, and in the case of a query, MySQL does not need to check whether there are special cases, namely null value, so as to optimize the query,
If a column contains only a limited number of specific values, such as sex, whether valid or school year, in this case should convert it to consider the value in the enum column of the MySQL, faster processing, because all of the enum value in the system is expressed in the identification number,
19, The use of optimize table
Changes to the regular form, prone to debris, which must be read from disk block more when querying databases, reduced query performance. With variable length table are disk fragmentation, the problem of the BLOB data type is more prominent, because of its size change is very big. You can use the optimize table to defragment, ensure the database performance is not reduced, optimization of those affected by the influence of data table. Optimize table can be used for MyISAM and BDB types of table. In fact any defragmentation method is using mysqldump to transfer data table, and then use the dump file and re build data table,
20, The use of procedure analyse()
You can use analyse (procedure) indicate that the best type of advice, very simple to use, plus the procedure analyse in the select statement () can be had; for example:
select * from students procedure analyse();
select * from students procedure analyse(16,256);
The second statement requires analyse (procedure) do not suggest the containing more than 16 values, or containing more than 256 bytes of enum type, if no restrictions, the output may be very long,
21, Using the query cache
1)Query cache work method:
The first implementation of a select statement, the text and the query results server remember this query, stored in the cache, the next time you encounter this statement, return directly from the cache results; when the update data table, any cache the data table query have become invalid, and will be discarded.
2)The configuration cache parameters:
Variable: query_cache _type, the query cache mode of operation. There are 3 patterns, 0: 1: no cache; caching query, unless begins with select sql_no_cache; 2: according to the need of the caching only those with select sql_cache at the beginning of the query; query_cache_size: set the maximum query results cached set size, greater than this value will not be slow.
22, The adjustment of hardware
1)In a machine with more memory,
2)Increased faster hard disk to reduce the waiting time of I/O,
Seek time is the main factor which determines the performance, literally move the head is the slowest, once the head positioning, from the track to read quickly,
3)Re allocation of disk activity in the physical hard disk device on different,
If possible, should be the busiest database stored on different physical devices, it is different with different partition uses the same physical equipment, because they will compete for the same physical resource (head).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Aries at December 10, 2013 - 5:05 PM