Linux MySQL operation commands

1. commands for starting MySql in Linux:
Mysqladmin start
/ECT/init. d/MySQL start (the installation path of MySQL is earlier)

2. Command for restarting MySql in Linux:
Mysqladmin restart
/ECT/init. d/MySQL restart (the installation path of MySQL is earlier)

3. Run the following command to disable MySql in Linux:
Mysqladmin Shutdown
/ECT/init. d/MySQL Shutdown (the installation path of MySQL is earlier)

4. Connect to MySQL on the local machine:
Enter the MySQL \ bin directory, type the mysql-uroot-p command, and press enter to enter the password.
Exit MySQL command: exit (Press ENTER)

5. Modify the MySQL password:
Mysqladmin-u username-P old Password New Password
Or enter the MySQL command line set password for root = PASSWORD ("root ");

6. Add new users. (Note: commands in the MySQL environment are followed by a semicolon as the command Terminator)
Grant select on database. * To username @ login host identified by "password"
For example, if a user's Test password is added to 123, the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:
Grant select, insert, update, delete on *. * to "identified by" 123 ";

Ii. MySQL database operations
You must first log on to MySQL. The related operations are performed at the MySQL prompt, and each command ends with a semicolon.

1. display the Database List.
Show databases;
2. display the data tables in the database:
Use MySQL; // open the database
Show tables;
3. display the data table structure:
Describe table name;
4. database creation:
Create Database database name;

GBK: Create Database Test2 default Character Set GBK collate gbk_chinese_ci;
Utf8: Create Database 'test2' default Character Set utf8 collate utf8_general_ci;
5. Create a table:
Use Database Name;
Create Table Name (field setting list );
6. Delete databases and tables:
Drop database database name;
Drop table name;
7. Clear records in the table:
Delete from table name;

Truncate table name;
8. Display records in the table:
Select * from table name;

9. Encoding Modification
To change the encoding format of MySQL:
When MySQL is started, add the mysqld_safe command line
-- Default-character-set = GBK

To change the encoding format of a Database: Enter the command at the MySQL prompt
Alter database db_name default Character Set GBK;

10. rename a table

Alter table T1 rename T2;

11. View SQL statement Efficiency

Explain <table_name>

Example: Explain select * from T3 where id = 3952602;

12. load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into Table mytable;

Iii. Data Import and Export

1. Transfer text data to the database
Text data should conform to the format: field data is separated by the tab key, and null value is used instead. Example:
1 name duty 2006-11-23
Data Import command load data local infile "file name" into Table table name;

2. Export databases and tables
Mysqldump -- opt News> News. SQL (back up all the tables in the database news to the news. SQL file. News. SQL is a text file with any file name .)
Mysqldump -- opt news author Article> author. article. SQL (back up the author table and article table in the database news to author. article. SQL file, author. article. SQL is a text file with any file name .)
Mysqldump -- databases db1 DB2> News. SQL (back up database DBL and DB2 to the news. SQL file. News. SQL is a text file with any file name .)
Mysqldump-H host-u user-P pass -- databases dbname> file. Dump
Import the database dbname on the host named "user" and "password pass" to file. Dump.
Mysqldump -- all-databases> all-databases. SQL (back up all databases to all-databases. SQL files, a all-databases. SQL is a text file, any file name .)

3. Import Data
Mysql <all-databases. SQL (import database)
Mysql> source news. SQL; (run the MySQL command to import tables)

1. Connect to MySQL
Format: mysql-H host address-u user name-P User Password

1. Example 1: connect to MySQL on the local machine.

First, open the DOS window, enter the directory mysqlbin, then type the command mysql-uroot-P, and press enter to prompt you to enter the password. If you have just installed MySQL, super User Root has no password, so press enter to enter mysql. The MySQL prompt is: mysql>.

2. Example 2: connect to MySQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. Exit MySQL command: exit (Press ENTER ).

Ii. Change the password

Format: mysqladmin-u username-P old Password New Password

1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-Password ab12

Note: because the root account does not have a password at the beginning, the old-P password can be omitted.

2. Example 2: Change the root password to djg345.

Mysqladmin-uroot-pab12 password djg345

3. Add new users. (Note: Unlike the above, the following commands in the MySQL environment are followed by a semicolon as the command Terminator)

Format: grant select on database. * To username @ login host identified by \ "password \"

Example 1: Add a user named "test1" with the password "ABC" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

Grant select, insert, update,
Delete on *. * To test1 @ \ "% \" identified by \ "ABC \";

However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your MySQL database on any computer on the Internet and do whatever he wants for your data. For the solution, see Example 2.

Example 2: Add a user named "Test2" with the password "ABC" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the password of Test2, he cannot access the database directly from the Internet, but can only access the database through the web pages on the MySQL host.

Grant select, insert, update,
Delete on mydb. * To Test2 @ localhost identified by \ "ABC \";

If you do not want Test2 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb
. * To Test2 @ localhost identified \"\";

 

Start: Net start MySQL;
Enter: mysql-u root-P/MySQL-H localhost-u root-P databasename;
List databases: Show databases;
Select Database: Use databasename;
List tables: show tables;
Show columns from tablename;
Create a database: Source filename.txt;
Matching character: wildcard _ can be used to represent any character, and % represents any string;
Add a 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 words cannot be broken; when data is inserted or changed, the field strings cannot be expanded into multiple rows; otherwise, the hard press enter will be stored in the data;
Add an Administrator Account: grant all on *. * to user @ localhost identified by "password ";
After each statement is entered, enter the plus sign ';' At the end, or add '\ G;
Query time: select now ();
Query the current user: Select User ();
Query the database version: Select version ();
Query the currently used database: select database ();

1. Delete the students data table in the student_course database:
Rm-F student_course/students .*

2. Back up the database: (back up the database test)
Mysqldump-u root-P test> C: \ test.txt
Backup table: (back up the mytable table under the test database)
Mysqldump-u root-P test mytable> C: \ test.txt
Import the backup data to the database: (import back to the test database)
Mysql-u root-P Test

3. Create a temporary table: (create a temporary table zengchao)
Create temporary table zengchao (name varchar (10 ));

4. To create a table, first determine whether the table exists.
Create Table if not exists students (......);

5. Copy the table structure from an existing table
Create Table Table2 select * From Table1 where 1 <> 1;

6. Copy a 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 the column ID type to int unsigned
Alter table Table1 change ID Sid int unsigned; // modify the column ID name to Sid and the attribute to int unsigned

9. Create an 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 an index
Drop index idx_id on Table1;
Alter table Table1 drop index ind_id;

11. Combine characters or multiple columns (link the column ID with ":" And column name and "=)
Select Concat (ID, ':', name, '=') from students;

12. Limit (10 to 20 records selected) <the number of the first record set is 0>
Select * from students order by ID limit 9, 10;

13. functions not supported by MySQL
Transaction, view, foreign key and reference integrity, stored procedure and trigger

14. MySQL uses the index operator symbol
<, <=, >=, >,=, Between, in, like without the start of % or _

15. disadvantages of using Indexes
1) slowing down the speed of adding, deleting, and modifying data;
2) occupied disk space;
3) increase the burden on the query optimizer;
When the query optimizer generates an execution plan, it will consider indexes. Too many indexes will increase the workload for the query optimizer, resulting in the inability to select the optimal query solution;

16. Analyze index Efficiency
Method: Add explain before a general SQL statement;
Meaning of analysis results:
1) Table: The table name;
2) Type: Connection type (all/range/Ref ). Among them, ref is the most ideal;
3) possible_keys: query the available index names;
4) Key: actually used index;
5) key_len: the length (in bytes) of the used part in the index );
6) Ref: displays the column name or "const" (I don't understand what it means );
7) rows: displays the number of rows that MySQL considers to be scanned before finding the correct results;
8) Extra: MySQL recommendations;

17. Use a short fixed-length Column
1) try to use a shorter data type;
2) use the fixed-length data type as much as possible;
A) Replace varchar with Char, and process data with a fixed length faster than that with a variable length;
B) For tables that are frequently modified, disks are easy to form fragments, which affects the overall performance of the database;
C) In case of a data table crash, it is easier to reconstruct a table with a fixed length of data rows. When a data row with a fixed length is used, the start position of each record is a multiple of the fixed record length, which can be easily detected. However, it is not necessary to use a variable length data row;
D) For MyISAM data tables, although converting to a fixed-length data column can improve performance, it occupies a large space;

18. Use not null and enum
Define the column as not null as much as possible, so that data can be generated faster and less space is required. In addition, MySQL does not need to check whether there is a special case, that is, the null value, optimize the query;
If a column contains only a limited number of specific values, such as gender, validity, or enrollment year, you should consider converting it to the value of the enum column, mySQL processes faster, because all enum values are represented by an Identifier value in the system;

19. Use optimize table
For tables that are frequently modified, fragments are easily generated, so that more disk blocks must be read during database query to reduce query performance. All tables with variable lengths have disk fragmentation issues. This problem is more prominent for blob data types because of its large size changes. You can use optimize table to organize fragments to ensure that the database performance is not degraded and optimize the data tables affected by fragments. Optimize table can be used for MyISAM and bdb data tables. In fact, any fragment method uses mysqldump to store data tables, and then creates a new data table using the converted files;

20. Use procedure analyze ()
Procedure analyze () can be used to display the best type of recommendations. It is easy to use. Add procedure analyze () after the SELECT statement. For example:
Select * from students procedure analyze ();
Select * from students procedure analyze (16,256 );
The second statement requires that procedure analyze () should not contain more than 16 values or enum types containing more than 256 bytes. If there is no limit, the output may be very long;

21. Use query Cache
1) How to query the cache:
When a SELECT statement is executed for the first time, the server remembers the text content and query results of the query and stores them in the cache. When this statement is executed for the next time, the server returns the results directly from the cache; when a data table is updated, any cache query of the data table becomes invalid and is discarded.
2) Configure cache parameters:
Variable: query_cache _ type, the operation mode of the query cache. There are 3 moderate modes, 0: no cache; 1: cache query, unless it starts with select SQL _no_cache; 2: only queries starting with select SQL _cache are cached as needed; query_cache_size: set the maximum result set size of the query cache, which is greater than this value and will not be cached.

22. Adjust hardware
1) add more memory to the machine;
2) increase the speed of the hard disk to reduce the I/O wait time;
Track-seeking time is the main factor that determines performance. The speed of moving the head is the slowest word by word. Once the head is located, it is fast to read from the track;
3) re-allocate disk activities on different physical hard disk devices;
If possible, the busiest data inventory should be placed on different physical devices, which is different from using different partitions of the same physical device, because they will compete for the same physical resources (head ).

 

 

 

 

 

 

 

 

Delete a MySQL database

 

 

1. Create a database

 

Mysql> Create Database drop_database;
Query OK, 1 row affected (0.00 Sec)

2. delete an existing database.

 

Mysql> drop database drop_database;
Query OK, 0 Rows affected ( 0.00 Sec)

3. delete an uncertain Database

 

Mysql> drop database drop_database;
Error 1008 (hy000): Can't drop database 'drop _ database'; database doesn' t exist

// The 'drop _ database' database cannot be deleted because it does not exist.

Mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 Sec) // generates a warning indicating that this database does not exist

Mysql> Create Database drop_database;
Query OK, 1 row affected (0.00 Sec)

Mysql> drop database if exists drop_database; // If exists is used to determine whether the database exists or not.
Query OK, 0 rows affected (0.00 Sec)