mysql command summary

1. Connect to MySQL
Format: mysql -h host address -u username -p user password
1. Example 1: Connect to MYSQL on this machine.
First open the DOS window, then enter the directory mysqlbin, and then type the command mysql -uroot -p. After pressing Enter, you will be prompted to enter your password. If MYSQL has just been installed, the super user root does not have a password, so you can enter directly by pressing Enter. In MYSQL, the MYSQL prompt is: mysql>.
2. Example 2: Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:
mysql -h110.110.110.110 -uroot -pabcd123
(Note: u and root do not need to add spaces, and the same applies to others)
3. Exit the MYSQL command: exit (Enter).
2. Change password
Format: mysqladmin -u username -p old password password new password

1. Example 1: Add a password ab12 to root. First, enter the directory mysqlbin under DOS, and then type the following command:
mysqladmin -uroot -password ab12
Note: Because root does not have a password at the beginning, the -p old password item 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 are commands in the MySQL environment, so they 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 test1 with the password abc, so that he can log in on any host and have query, insert, modify, and delete permissions on all databases. First connect to MySQL as the root user, and then type the following commands:
grant select,insert,update,
delete on *.* to [email=test2@localhost]test2@localhost[/email] identified by "abc";
If you don't want test2 to have a password, you can type another command to eliminate the password.
grant select,insert,update,delete on mydb
.* to [email=test2@localhost]test2@localhost[/email] identified by "";
I talked about login, adding users, password changes and other issues above. Let's take a look at the database operations in MySQL. Note: You must first log in to MySQL. The following operations are performed at the MySQL prompt, and each command ends with a semicolon.
1. MySQL common commands
create database name; create database
use databasename; select database
drop database name to delete the database directly without reminder
show tables; display table
describe tablename; detailed description of table
select remove by adding distinct Duplicate fields
mysqladmin drop database name There is a prompt before deleting the database.
Display the current mysql version and current date
select version(),current_date;
2. Modify the root password in mysql:
shell>mysql -u root -p
mysql> update user set password=password("xueok654123") where user='root';
mysql> flush privileges //Refresh the database
mysql>use dbname; Open the database:
mysql>show databases; Show all databases
mysql>show tables; Show all tables in the database mysql: first use mysql ;Then
mysql>describe user; displays the column information of the user table in the mysql database);
3. grant
Create a complete super user who can connect to the server from anywhere, but must use a password something to do this
mysql> ; grant all privileges on *.* to [email=user@localhost]user@localhost[/email] identified by 'something' with
Add new user
Format: grant select on database.* to username@login host identified by "Password"
GRANT ALL PRIVILEGES ON *.* TO [email=monty@localhost]monty@localhost[/email] IDENTIFIED BY 'something' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO [email=monty@% E2%80%9D%]monty@”%[/email]” IDENTIFIED BY 'something' WITH GRANT OPTION;
Remove authorization:
mysql> revoke all privileges on *.* from [email=root@%E2%80% 9D%]root@”%[/email]”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;
Create a user custom on a specific client it363.com Log in to access the specific database fangchandb
mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ' passwd'
Rename table:
mysql > alter table t1 rename t2;
4, mysqldump
Backup database
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
Restore database
shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump - h host -u root -p dbname < dbname_backup.sql
If you only want to unload the table creation command, the command is as follows:
shell> mysqladmin -u root -p -d databasename > a.sql
If you only want to unload the table creation command The sql command to insert data without creating a table command is as follows:
shell> mysqladmin -u root -p -t databasename > a.sql
So if I only want the data and not any sql command , how should it be done?
　 Mysqldump -T./ phptest driver
Among them, only the -T parameter can be specified to unload the plain text file, which indicates the directory to unload the data. ./ indicates the current directory, which is the same directory as mysqldump. If driver is not specified table, the entire database data will be unloaded. Each table will generate two files, one is a .sql file, including table creation execution. The other is a .txt file that only contains data and no sql instructions.
5. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input. You can use the shell type redirection utility to do this. For example, if there are queries stored in the file my_file.sql, you can execute these queries as follows: For example, if you want to write the table creation statement in sql.txt in advance:
mysql > mysql -h myhost -u root -p database < sql.txt
1. Installation environment:
Windows This machine can be logged in using mysql -h 172.5.1.183 -uroot. This is determined based on the policy in the second line. The permission modification takes effect:
1)net stop mysql
2)c:mysqlbinmysqladmin flush-privileges
3) Log in After mysql, use the flush privileges statement
6. Create database staffer
create database staffer; 7. The following statement is executed in the mysql environment
Display the databases that the user has permissions show databases;
Switch to the staffer database use staffer;
Show the current Tables with permissions in the database show tables;
Show the structure of the table staffer desc staffer;
8. Create a test environment
1) Create database staffer
mysql> create database staffer
2) Create tables staffer, department, position, department_pos
create table s_position
                                                                                                           table s_position                                                                                          . key
); B Create table department
K_Department (ID) #Set the main key
);
create table depart_pos
(
department_id int not null,
position_id int not null,
primary key PK_depart_pos (department_id, position_id) #Set the composite primary key
​​ Auto_increment Primary Key, #Set the main key
name varchar (20) not null default 'unknown', #Set the default value
department_id int Null,
posity_id int Null,
U u nique (department_id, posity_id) #Set the unique value
);
3) Delete
mysql>
drop table depart_pos;
drop table department;
drop table s_position;
drop table staffer database staffer;
9. Modify the structure
mysql>
#Table position adds column test
  alter table position add(test char(10));
  #Table position modify column test
  alter table position modify test char(20) not null;
  #Table position modify column test default value
  alter table position alter test set default 'system';
     #Table position removes the test default value
       alter table position alter test drop default;
    #Table position removes the column test
                                                                           
#Table depart_pos add primary key
alter table depart_pos add primary key PK_depart_pos (department_id,position_id);
10. Operation data
#Insert table department
insert into department(name,description) values('system department','system department') ;
insert into department(name,description) values('Public Relations Department','Public Relations Department');
insert into department(name,description) values('Customer Service Department','Customer Service Department');
insert into department( name,description) values('Finance Department','Finance Department');
insert into department(name,description) values('Test Department','Test Department');
#Insert into table s_position
insert into s_position(name ,description) values('director','director');
insert into s_position(name,description) values('manager','manager');
insert into s_position(name,description) values('ordinary employees', 'Ordinary employee');
#Insert table depart_pos
insert into depart_pos (department_id, position_id)
Select a.id department_id, B.id Postion_id
sition b; # #Insert table staffer
insert into staffer (name ,department_id,position_id) values('Chen Dazhi',1,1);
insert into staffer(name,department_id,position_id) values('Li Wenbin',1,2);
insert into staffer(name,department_id,position_id) values ('Ma Jia',1,3);
insert into staffer(name,department_id,position_id) values('Kang Zhiqiang',5,1);
insert into staffer(name,department_id,position_id) values('Yang Yuru', 4,1);
11. Query and delete operations
#Display the personnel and positions of the system department
select a.name,b.name department_name,c.name position_name
from staffer a,department b,s_position c
where a. department_id=b.id and a.position_id=c.id and b.name='System Department';
#Show the number of people in the system department
select count(*) from staffer a,department b
where a.department_id=b. id and b.name='System Department'
  #Display the number of people in each department
  select count(*) cou,b.name
  from staffer a,department b
  where a.department_id=b.id
  group by b.name ;
    #Delete Customer Service Department
                                                                                                  will delete from department where name='Customer Service Department'; Backup and recovery
  Back up database staffer
                                                                    using using   using  -                                                                                                                ’     ’ s ’ s ’ s ’ s        out. T Restore the database staffer. You need to create an empty library Staffer
C: MySQLBINMYSQL -UROOT -PROOT Staffer & LT; Staffer.sql
. OT -Databases Staffer & GT; E: Staffer .sql
         mysql -uroot -proot >e:staffer.sql
      But in this case, the staffer library cannot exist in the system, and databases with other names cannot be imported,
     Of course you can manually modify the staffer.sql file
13. From the text Import data into the database
1) Use the tool c:mysqlbinmysqlimport
The function of this tool is to import the file into a table with the same name without the file extension, such as
staffer.txt, staffer is imported into the staffer table
Common options The functions are as follows
-d or --delete deletes all information in the data table before new data is imported into the data table
-f or --force Regardless of whether an error is encountered, mysqlimport will force to continue inserting data
-i or -- ignore mysqlimport skips or ignores lines with the same unique
keyword, and the data in the imported file will be ignored.
-l or -lock-tables locks the table before data is inserted, which prevents user queries and updates from being affected when
you update the database.
-r or -replace This option has the opposite effect of the -i option; this option will replace
There are records with the same unique key in the table.
--fields-enclosed- by= char
Specifies how to enclose the data records in the text file. In many cases,
data is enclosed in double quotes. By default data is not enclosed in characters.
--fields-terminated- by=char
Specifies the delimiter between the values ​​of each data. In a period-delimited file, the
delimiter is a period. You can use this option to specify the delimiter between data.
The default delimiter is Tab
--lines-terminated- by=str
This option specifies the string
or character that separates data between lines in the text file. By default, mysqlimport uses newline as the line separator.
You can choose to replace a single character with a string:
a new line or a carriage return.
Commonly used options for the mysqlimport command include -v to display version,
-p to prompt for password, etc.
There is a problem with this tool. Certain columns cannot be ignored, which will cause a lot of trouble for our data import. Although
you can set this field manually, there will be inexplicable results. Let’s make a simple example
We define it as follows department_no.txt, saved in the e drive, the interval is tab character t
10 10
11 11
12 24
Execute the following command
                                                                                                                                                                                             Surrounding symbols, split using the default t, because there will be problems using other symbols,
I don’t know if it is because of windows
2) Load Data INFILE file_name into table_name(column1_name,column2_name)
This command is used at the mysql> prompt The advantage is that the guidance can be specified. For example, C: mysqlbinMySQL -UROOT -PROOT Staffer
MySQL & GT; LOAD DATA Infile "E: /Depart_no.txt" Into Depart_ID, POSITION _id); 个 These two tools are used under Windows There are problems, I don’t know if it is a Windows problem or a Chinese problem,
And it produces null values ​​for unspecified columns, which is obviously not what we want, so use these tools with caution
Enter MySQL: mysql -user -ppassword - -port=3307
1: Use the SHOW statement to find out what databases currently exist on the server:
mysql> SHOW DATABASES;
2: 2. Create a database MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3: Select the one you created Database
mysql> USE MYSQLDATA; (Press the Enter key and Database changed appears, indicating that the operation is successful!)
4: Check what tables exist in the current database
mysql> SHOW TABLES;
5: Create a database table
mysql> Create TABLE MYTABLE (name VARCHAR(20), *** CHAR(1));
6: Display the structure of the table:
mysql> DESCRIBE MYTABLE;
7: Add records to the table
mysql> insert into MYTABLE values ​​("hyq" ,"M");
8: Load data into the database table in text mode (for example, D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;
9: Import .sql file command (for example, D:/mysql.sql)
mysql>use database;
mysql>source d:/mysql.sql;
10: Delete table
mysql>drop TABLE MYTABLE;
11: Clear table
mysql> ;delete from MYTABLE;
12: Update data in the table
mysql>update MYTABLE set ***="f" where name='hyq';
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2= expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
or
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2= expr2 ...]
[WHERE where_definition]
UPDATE updates the columns of existing table rows with new values. The SET clause indicates which columns are to be modified and the values ​​they should be given. The WHERE
clause, if given, specifies which row should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, rows will be updated in the specified order.
       If you specify the keyword LOW_PRIORITY, the execution of UPDATE will be delayed until no other clients are reading the table.
If you specify the keyword IGNORE, the update statement will not abort abnormally, even if a duplicate key error occurs during the update process. The row causing the conflict will not be updated.
       If a column is accessed from tbl_name in an expression, UPDATE uses the current value of the column. For example, the following statement sets the age column value to its current value plus 1:
mysql> UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to right. For example, the following statement sets the age column to twice its value and then adds 1:
mysql> UPDATE persondata SET age=age*2, age=age+1;
If you set the column to its current value, MySQL notices this and does not update it.
UPDATE returns the actual number of changed record rows. In MySQL 3.22 or newer, the C API function mysql_info() returns the number of matched rows that were updated, and the number of warnings that occurred during UPDATE.
In MySQL 3.23, you can use LIMIT # to ensure that only a given number of rows are changed.
      If an ORDER BY clause is used (supported since MySQL 4.0.0), the rows will be updated in the specified order. This is really only useful in conjunction with LIMIT
.
Starting from MySQL 4.0.4, you can also perform an UPDATE operation containing multiple tables:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
Note: Multiple tables UPDATE cannot use ORDER BY or LIMIT.
Keywords: mysql
Start: net start mySql;
Enter: mysql -u root -p/mysql -h localhost -u root -p databaseName;
List databases: show databases;
Select database: use databaseName;
Column Out of the table: show tables;
Show the properties of the table columns: show columns from tableName; ：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: Be careful not to break words; when inserting or changing data, you cannot The string of the field is expanded into multiple lines, otherwise the hard return will be stored in the data;
Add an administrator account: grant all on *.* to [email=user@localhost]user@localhost[/email] identified by "password";
After entering each statement, add a semicolon ';' 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
Back up the table: (Back up the mytable table under the test database)
Mysqldump -u root -p test mytable>c:test.txt
Import the backup data into the database: (import 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 The table first determines whether the table exists
　create table if not exists students(...);
　5. Copy the structure of the 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 type of column
　alter table table1 modify id int unsigned;//Modify the type of column id to int unsigned
　　alter table table1 change id sid int unsigned;//Modify the name of the column id to sid, and change the attribute 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 characters or more Column (connect column id with ":" and column name with "=")
　select concat(id,':',name,'=') from students;
　12. limit (select 10 to 20 items)< ;The number of the first record set is 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
14. MySQL will use index operation symbols
　<,<=,>=,>,=,between,in, without % or like starting with _
　15. Disadvantages of using index
　1) Slow down The speed of adding, deleting and modifying data;
　2) Occupying 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 of the query optimizer. As a result, the optimal query plan cannot be selected;
　16. Analyze index efficiency
Method: Add explain before the general SQL statement;
Meaning of the 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 index name that can be used;
　4) key: the actual index used;
　5) key_len: the length of the used part of the index (bytes);
　6) ref : Display column names or "const" (don't understand what it means);
　7) rows: Display the number of rows that MySQL thinks must be scanned before finding the correct result;
　8) extra: MySQL's suggestion;
　17. Use shorter Fixed-length columns
　1) Use shorter data types as much as possible;
　2) Use fixed-length data types as much as possible;
　a) Use char instead of varchar. Fixed-length data processing is faster than variable-length ones;
　b) For For tables that are frequently modified, the disk can easily become fragmented, thus affecting the overall performance of the database;
　c) In the event of a data table crash, tables using fixed-length data rows are easier to reconstruct. Using fixed-length data rows, the starting position of each record is a multiple of the fixed record length, which can be easily detected, but using variable-length data rows is not necessarily the case;
　d) For MyISAM type data tables , although converting to a fixed-length data column can improve performance, it also takes up a lot of space;
18. Use not null and enum
Try to define the column as not null, so that the data can come out faster and the space required Less, and when querying, MySQL does not need to check whether there is a special case, that is, a null value, so as to optimize the query;
If a column only contains a limited number of specific values, such as gender, whether it is valid or the year of enrollment, etc., in this case You should consider converting it to an enum column value. MySQL processes it faster because all enum values ​​are represented by identification values ​​in the system;
　19. Use optimize table
　For tables that are frequently modified, fragmentation is easy to occur , so that more disk blocks must be read when querying the database, reducing query performance. Tables with variable lengths all have disk fragmentation problems. This problem is very important for The blob data type stands out because its size varies so much. This can be done by using optimize table to defragment, ensure that database performance does not decrease, and optimize those data tables affected by fragmentation. optimize table can be used for MyISAM and BDB type data tables. In fact, any defragmentation method uses mysqldump to dump the data table, and then uses the dumped file to rebuild the data. Table;
　20. Use procedure analyse()
You can use procedure analyse() to display the best type of suggestions. It is very simple to use. Just add procedure analyse() 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 recommend enum types with more than 16 values ​​or more than 256 bytes. If there is no limit, output It may be very long;
21. Use query cache
1) How query cache works:
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. Next time When this statement is encountered, the results are returned directly from the cache; when the data table is updated, any cached queries for the data table become invalid and will be discarded.
　2) Configure cache parameters:
　Variable: query_cache _type, query cache operation mode. There are 3 modes, 0: no caching; 1: cache query, unless used with select Starting with sql_no_cache; 2: Only cache those queries starting with select sql_cache as needed; query_cache_size: Set 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 a faster hard drive to reduce I/O waiting time;
　Seek time is the main factor that determines performance, and moving the head literally is the most important factor. Slow, once the head is positioned, reading from the track is very fast;
3) Redistribute disk activity on different physical hard disk devices;
If possible, the busiest databases should be stored on different physical devices, which is different from using Different partitions of the same physical device are different because they will compete for the same physical resource (heads).