Learn the problems and solutions for using mysql

Learn the Problems and Solutions encountered by using mysql. If the installation fails and there is no response, it doesn't matter. The previous installation was successful, but it was not configured. After the service is disabled, run the following command to enable the Service. Enable Service net start mysql5 first -- the service name specified during installation should be blank when connecting to localhost with navicat lite software. The username is root in the user after navicat lite login, you can change the root password, and then click save to www.2cto.com -- it is best to disable mysql5 and enable it again. In the command: mysql-u root-p and then enter the password modified in navicat lite -- to add a user pkm, the password is 123456 grant all on *. * to pkm @ localhost identified by "123456"; Use the ip address and user name pkm to remotely connect to the computer mysql. the following error occurs: 1130-host '**-pc' is not allowed to connect to this mysql server solution. Authorize the pkm user to allow remote connection: grant all privileges on *. * TO 'pkm' @ '%' identified by '000000' with grant option; this is enough. Some basic mysql commands are as follows: 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 table: show tables; show table column attributes: show columns from tableName; Create Database: source fileName.txt; matched characters: 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 fieldNa Me2 dateType; multi-line command input: note that words cannot be broken; when inserting or changing data, the field strings cannot be expanded into multiple rows, otherwise, press enter to store 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. * www.2cto.com 2. Back up the database: (back up the database test) mysqldump-u root-p te St> 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: (export 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 has the 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 the table create table table2 select * from table1; 7. rename 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, modify the attribute to int unsigned www.2cto.com 9 and create the 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 the index drop index idx_id on table1; alter table table1 drop index ind_id; 11. Union character or multiple columns (concatenating 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. MySQL does not support function transactions, views, foreign keys, and reference integrity. Stored Procedures and triggers 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) occupying disk space; 3) increasing 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 for the query optimizer, resulting in the inability to select the optimal query solution. www.2cto.com 16. Index Efficiency Analysis Method: add the explain statement before a general SQL statement; meaning of analysis results: 1) table: table Name; 2) type: Connection type (ALL/Range/Ref ). Among them, ref is the most ideal; 3) possible_keys: query the available index name; 4) key: actually used index; 5) key_len: length of the part used in the index (in bytes); 6) ref: displays the column name or "const" (What do you mean?); 7) rows: display the number of rows that MySQL considers to be scanned before finding the correct results; 8) extra: MySQL advice; 17. Use a short fixed-length column; 1) use a shorter data type as much as possible; 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 longer length. B) for frequently modified tables, disks are prone to fragmentation, this 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 of a fixed length is used, the start position of each record is a multiple of the fixed record length, which can be easily detected, but the use of a variable length data row is not necessarily; d) for MyISAM data tables, although converting to a fixed-length data column can improve performance, it occupies a large space; www.2cto.com 18. Use not null and enum to define the column as not null as much as possible, so that data can be generated faster and less space is required, mySQL does not need to check whether a special case exists, that is, null value, to optimize the query. If a column contains only a limited number of specific values, such as gender, validity, or admission year, in this case, we should consider converting it to the value of the enum column. MySQL can process it faster, because all enum values are represented by the identification value in the system; www.2cto.com 19. If optimize table is used 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. You can use procedure analyze () to use procedure analyze () it is recommended to display the optimal type. It is easy to use. You can 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 procedure analyze () not to be recommended to contain more than 16 values, or enum type containing more than 256 bytes. If there is no limit, the output may be very long; www.2cto.com 21, use query cache 1) query cache: When a select statement is executed for the first time, the server remembers the text content and query results of the query, stored in cache The next time you touch this statement, the result is returned directly from the cache. After the data table is updated, any cache query of the data table becomes invalid and will be discarded. 2) configure the cache parameter: variable: query_cache _ type, and query the cache operation mode. 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. Www.2cto.com 22. Adjust hardware 1) attach more memory to the machine; 2) increase the speed of the hard disk to reduce the I/O wait time; Seek time is the main factor that determines the performance, the speed of moving the head is the slowest. Once the head is located, it is fast to read from the track; 3) disk activity is reassigned on different physical hard disk devices; if possible, the busiest data inventory should be placed on different physical devices, which is different from the different partitions using the same physical device because they will compete for the same physical resources (head ).