SQL command Encyclopedia

MySQL database common commands we often use

One, connect MySQL
Format: mysql-h host address-u user name-P user Password
1. Example 1: Connect to MySQL on this machine.
First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is: mysql>.
2. Example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: You and root can be used without spaces, others are the same)
3. Exit MySQL command: Exit (enter).

Second, change the password
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root ab12. First enter 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 can be omitted.
2, Example 2: Then change the root password to djg345.
MYSQLADMIN-UROOT-PAB12 Password djg345

Third, add new users. (Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)
Format: Grant Select on database. * To User name @ login host identified by \ "Password \"
Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,
Delete on *. * to [e-mail protected] identified by \ "Abc\";
If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on MyDB
. * to [e-mail protected] identified by \ "\";
In the above, the login, add users, password changes and other issues. Let's look at MySQL's operations on the database. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.
1. mysql Common commands
Create database name; Create a database
Use DatabaseName; Select Database
Drop database name deletes databases directly and does not alert
Show tables; Show Table
Describe TableName; Detailed description of the table
Add distinct in Select to remove duplicate fields
Mysqladmin drop database name is prompted before you delete the databases.
Show current MySQL version and current date
Select version (), current_date;
2. Change the password of root in MySQL:
Shell>mysql-u root-p
mysql> Update user Set Password=password ("Xueok654123″) where user= ' root ';
Mysql> flush Privileges//Refresh Database
Mysql>use dbname; Open the database:
Mysql>show databases; Show all databases
Mysql>show tables; Displays all tables in the database MySQL: use MySQL first;
Mysql>describe user; Displays column information for the user table in the MySQL database);
3. Grant
Create a full superuser who can connect to the server from anywhere, but must use a password something do this
Mysql> Grant all privileges on * * to [e-mail protected] identified by ' something ' with
Add new users
Format: Grant Select on database. * To User name @ login host identified by "password"
GRANT all privileges on * * to [email protected] identified by ' something ' with GRANT OPTION;
GRANT all privileges on * * to [e-mail protected] "%" identified by ' something ' with GRANT OPTION;
Remove Authorization:
Mysql> revoke all privileges on * * FROM [email protected] "%";
mysql> Delete from user where user= "root" and host= "%";
mysql> flush Privileges;
Create a user custom login on a specific client it363.com to access a specific database fangchandb
MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd
To rename a table:
mysql > ALTER table t1 rename T2;
4, Mysqldump

10. Operation Data
#插入表department
INSERT INTO department (name,description) VALUES (' System department ', ' System Department ');
INSERT INTO department (name,description) VALUES (' PR ', ' PR ');
INSERT INTO department (name,description) VALUES (' Customer service ', ' service department ');
INSERT INTO department (name,description) VALUES (' Finance department ', ' finance Department ');
INSERT INTO department (name,description) VALUES (' Test department ', ' Test Department ');
#插入表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 employees ');
#插入表depart_pos
Insert into Depart_pos (department_id,position_id)
Select a.ID department_id,b.id postion_id
From department a,s_position b;
#插入表staffer
INSERT INTO staffer (name,department_id,position_id) VALUES (' Chanda ', 1, 1);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Li Wenbin ', for each);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Ma Jia ', 1, 3);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Zhiqiang ', 5, 1);
INSERT INTO staffer (name,department_id,position_id) VALUES (' Yang Yuju ', 4, 1);
11, Query and delete operations
#显示系统部的人员和职位
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 ';
#显示系统部的人数
Select COUNT (*) from staffer A,department b
where A.department_id=b.id and B.name= ' System Department '
#显示各部门的人数
Select COUNT (*) cou,b.name
From staffer A,department b
where a.department_id=b.id
Group BY B.name;
#删除客服部
Delete from department where name= ' customer service department ';
#将财务部修改为财务一部
Update department set name= ' finance a ' where name= ' finance department;
12. Backup and Recovery
Backing up the database staffer
C:\mysql\bin\mysqldump-uroot-proot Staffer>e:\staffer.sql
The resulting staffer.sql is a SQL script that does not include the built-in statements, so you need to manually
Create a database before you can import
To restore the database staffer, you need to create an empty library staffer
C:\mysql\bin\mysql-uroot-proot Staffer<staffer.sql
If you do not want to create staffer manually later, you can
C:\mysql\bin\mysqldump-uroot-proot--databases Staffer>e:\staffer.sql
Mysql-uroot-proot >e:\staffer.sql
However, the system can not exist staffer library, and can not import other names of the database,
Of course you can manually modify the Staffer.sql file
13. Import data from text to database
1) using the tool C:\mysql\bin\mysqlimport
The purpose of this tool is to import files into and out of the table with the same name as the file extension, as
Staffer.txt,staffer are imported into the staffer table
Common options and functions are as follows
-D or--delete all information in the data table before the new data is imported into the datasheet
-F or--force Mysqlimport will force the data to be inserted regardless of whether or not an error is encountered
-I or--ignore mysqlimport skip or ignore those with the same unique
keyword, the data in the import file is ignored.
-L or-lock-tables data is inserted before locking the table, which prevents the
When you update a database, users ' queries and updates are affected.
-R or-replace This option is the opposite of the-I option; this option overrides
A record with the same unique keyword in the table.
--fields-enclosed-by= Char
Specifies what the data in the text file is to be recorded in, in many cases
The data is enclosed in double quotation marks. By default, the data is not surround by the word.
--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 a delimiter between data.
The default delimiter is Jump Geff (Tab)
--lines-terminated-by=str
This option specifies a delimited string of data between rows in a text file and lines
or character. By default, Mysqlimport with newline as the line delimiter.
You can choose to replace a single character with a string:
A new line or a carriage return.
The common options for the Mysqlimport command are the-v Display versions (version),
-P Prompt for password (password) and so on.
This tool has a problem, can not ignore some columns, so that our data import has a lot of trouble, although you can manually set this field, but there will be inexplicable results, we do a simple example
We define the following depart_no.txt, which are saved in the E-drive, spaced in tabs \ t
10 10
11 11
12 24
Execute the following command
C:\mysql\bin\mysqlimport-uroot-proot staffer E:\depart_pos.txt
There is no enclosing symbol for the column, and the partition uses the default \ t, because there are problems with other symbols,
Don't know why it's not windows
2) Load Data INFILE file_name into table_name (COLUMN1_NAME,COLUMN2_NAME)
This command is used at the mysql> prompt, and the advantage is that you can specify the column import, as shown below
C:\mysql\bin\mysql-uroot-proot staffer
Mysql>load data infile "E:/depart_no.txt" into Depart_no (department_id,position_id);

These two tools are used under Windows, do not know whether it is the cause of windows or the problem of Chinese,
And does not specify the column it produces a null value, which is obviously not what we want, so use these tools with caution
Enter Mysql:mysql-uuser-ppassword--port=3307
1: Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;
2:2. Create a database Mysqldata
mysql> Create DATABASE Mysqldata;
3: Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) )
4: See what tables exist in the current database
Mysql> SHOW TABLES;
5: Create a database table
Mysql> Create TABLE MYTABLE (name VARCHAR), sex CHAR (1));
6: Show the structure of the table:
Mysql> DESCRIBE MYTABLE;
7: Add a record to the table
mysql> INSERT INTO MYTABLE values ("HyQ", "M");
8: Loading data into a 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 the table
Mysql>delete from MYTABLE;
12: Update data in table
Mysql>update MYTABLE set sex= "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 rows in the existing table with a new value. The SET clause indicates which column to modify and what value they should give. WHERE
If the clause is given, specify which record line should be updated. Otherwise, all the record lines are updated. If the ORDER BY clause is specified, the record row is updated in the specified order.
If you specify the keyword low_priority,update execution will be delayed until no other client is reading the table.
If you specify the keyword IGNORE, the UPDATE statement will not abort abnormally, even if duplicate key errors occur during the update process. The row of records that caused the conflict will not be updated.
If you access a column 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;
The UPDATE assignment is calculated from left to right. For example, the following statement sets the age column to twice times its age and then adds 1:
mysql> UPDATE persondata SET age=age*2, age=age+1;
If you set the column as its current value, MySQL notices this and does not update it.
UPDATE returns the number of record rows that were actually changed. In MySQL 3.22 or later, the C API function Mysql_info ()
Returns the number of record rows that were matched and updated, and the number of warnings that occurred during the update.
In MySQL 3.23, you can use LIMIT # to make sure that only a given number of record rows is changed.
If an ORDER BY clause is used (supported from MySQL 4.0.0), the record row is updated in the specified order. This is actually only useful with limit.
Starting with MySQL 4.0.4, you can also perform an UPDATE operation with multiple tables:
UPDATE Items,month SET Items.price=month.price
WHERE items.id=month.id;
Note: Multiple table UPDATE is not allowed to use ORDER by or LIMIT.
Keyword: 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;
List table: Show tables;
Show table Column properties: Show columns from TableName;
Build database: Source FileName.txt;
Match characters: You can use wildcard characters _ to represent any one character, and% to represent 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 you cannot break the word, and when inserting or changing data, you cannot expand the string of the field into multiple lines, or the hard return will be stored in the data;
Add an Administrator account: Grant all on * * to [e-mail protected] identified by "password";
Fill in the end of each statement with a semicolon '; ', or add ' \g ' to it;
Query time: Select Now ();
Query Current User: Select User ();
Querying database version: Select version ();
Query the database currently in use: select databases ();
　　
1. Delete the students data sheet from the Student_course database:
Rm-f student_course/students.*
　　
2. Back up the database: (Backup DATABASE Test)
Mysqldump-u root-p Test>c:\test.txt
Backup table: (Back up the MyTable table under test database)
Mysqldump-u root-p Test Mytable>c:\test.txt
Import the backup data into the database: (Back to test database)
Mysql-u root-p Test
　　
3. Create temporary table: (Create temp table Zengchao)
Create temporary table Zengchao (name varchar (10));
　　
4. Create a table to determine if the table exists first
CREATE table if not exists students (...);
　　
5. Copying the structure of tables from existing tables
CREATE TABLE table2 SELECT * FROM table1 where 1<>1;
　　
6. Copy the table
CREATE TABLE table2 SELECT * FROM table1;
　　
7. Renaming the table
ALTER TABLE table1 Rename as table2;
　　
8. Modify the type of the column
ALTER TABLE table1 Modify ID int unsigned;//The type of the modified column ID is int unsigned
ALTER TABLE table1 change ID sid int unsigned;//the name of the modified column ID is SID, and the property is modified 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);//establishing a unique indexing
　　
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 to ":" and column name and "=")
Select Concat (ID, ': ', name, ' = ') from students;
　　
12, limit (choose 10 to 20) < The first Recordset is numbered 0>
SELECT * FROM students order by ID limit 9, 10;
　　
13, MySQL does not support the function
Transactions, views, foreign key and referential integrity, stored procedures, and triggers
　　
　　
14, MySQL will use the index operation symbol
<,<=,>=,>,=,between,in, like with no% or _
　　
15. Disadvantages of using indexes
1) Slow down or change the speed of data;
2) occupy disk space;
3) Increase the burden of the query optimizer;
When the query optimizer generates an execution plan, it takes into account the index, too many indexes will increase the workload for the query optimizer, resulting in the inability to select the optimal query scheme;
　　
16. Analyze Index efficiency
Method: Add explain before the general SQL statement;
Meaning of the analysis result:
1) Table: list name;
2) Type: Types of connections, (ALL/RANGE/REF). Where ref is the most desirable;
3) Possible_keys: The index name that the query can use;
4) Key: The actual use of the index;
5) Key_len: The length of the part used in the index (bytes);
6) Ref: Displays the column name or "Const" (does not understand what meaning);
7) Rows: Shows the number of rows that MySQL has to scan before finding the correct results;
8) Extra:mysql's recommendations;
　　
17. Use shorter fixed-length columns
1) Use a shorter data type whenever possible;
2) Use fixed length data type whenever possible;
a) using char instead of varchar, fixed-length data processing is faster than the longer;
b) for frequently modified tables, the disk is prone to fragmentation, thus affecting the overall performance of the database;
c) In the event of a data table crash, a table with fixed-length data rows is easier to reconstruct. With fixed-length data rows, each record's starting position is a multiple of a fixed record length and can be easily detected, but using variable-length data rows is not necessarily the same;
d) for MyISAM types of data tables, although conversion to fixed-length data columns can improve performance, but occupy a large space;
　　
18. Use NOT NULL and enum
Try to define the column as NOT NULL, which makes the data come out faster, requires less space, and when queried, MySQL does not need to check for a special case, or null value, to optimize the query;
If a column contains only a limited number of specific values, such as gender, whether it is valid or the year of entry, etc., it should be considered in this case to convert it to the value of the enum column, MySQL processing faster, because all enum values in the system are represented by the identification value;
　　
19. Using Optimize table
For frequently modified tables, it is easy to fragment, making it necessary to read more disk blocks when querying the database and to reduce query performance. A disk fragmentation problem exists with variable-length tables, which are more prominent for BLOB data types because their size varies greatly. You can defragment the data by using optimize table to ensure that database performance is not degraded and that the tables that are affected by fragmentation are optimized. Optimize table can be used for data tables of type MyISAM and BDB. In fact, any defragmentation method is to use mysqldump to dump the data table, and then use the dump file and re-create the data table;
　　
20. Using procedure analyse ()
You can use procedure analyse () to display the best type of recommendation, which is simple, with the addition of Procedure analyse () after 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 an enum type with more than 256 bytes, and that if there is no limit, the output may be very long;
　　
21. Using Query Caching
1) How the query cache works:
When a SELECT statement is executed for the first time, the server remembers the query's text content and query results, stores it in the cache, and returns the result directly from the cache the next time it encounters the statement, and when the data table is updated, any cached queries for that data table become invalid and discarded.
2) Configure the cache parameters:
Variables: Query_cache _type, querying the operating mode of the cache. There are 3 modes, 0: Not cached, 1: Cache queries, unless you start with select Sql_no_cache; 2: Only those queries that start 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 the performance, literally moving the head is the slowest, once the head positioning, from the track to read quickly;
3) Re-allocate disk activity on different physical hard disk devices;
If possible, you should store the busiest databases on different physical devices, which are different from the different partitions that use the same physical device, because they will compete for the same physical resources (heads).

SQL command Encyclopedia