Shulou(Shulou.com)05/31 Report--

This article mainly introduces the "Collection of Common MySQL commands". In daily operation, I believe many people have doubts about the common problems of MySQL commands. The editor has consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "the Collection of Common MySQL commands"! Next, please follow the editor to study!

A complete Collection of Common MySQL commands

1. Connect MySQL

Format: mysql-h host address-u user name-p user password

1. Example 1: connect to the MYSQL on this machine.

First of all, open the DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p. Enter prompts you to enter the password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter the MYSQL directly by entering the enter. The prompt for MYSQL is: mysql >.

Example 2: connect to the MYSQL on the remote host. Suppose the IP of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command:

Mysql-h210.110.110.110-uroot-pabcd123

(note: U and root do not have to add spaces, and so do others)

3. Exit the MYSQL command: exit (enter).

Second, change the 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: since root does not have a password at the beginning, the-p old password can be omitted.

2. Example 2: change the password of root to djg345.

Mysqladmin-uroot-pab12 password djg345

Third, add new users. (note: unlike the above, the following is a command in the MySQL environment, so it is followed by a semicolon as the command Terminator)

Format: grant select on database. * to user name @ login host identified by\ "password\"

Example 1. Add a user's test1 password to abc, so that he can log in on any host and have the authority 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\ "abc\"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb

. * to identified by\ "\"

Above talked about login, the increase of users, password change and other issues. Let's take a look at database operations in MySQL. Note: you must first log in to MySQL, all of the following are done at the MySQL prompt, and each command ends with a semicolon.

1. MySQL common commands

Create database name; creates a database

Use databasename; Select Database

Drop database name deletes the database directly without reminding you

Show tables; display table

Detailed description of the describe tablename; table

Add distinct to select to remove duplicate fields

Mysqladmin drop database name is prompted before deleting the database.

Displays the current mysql version and current date

Select version (), current_date

2. Modify 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 the database

Mysql > use dbname; Open the database:

Mysql > show databases; shows all databases

Mysql > show tables; displays all the tables in the database mysql: use mysql; first and then

Mysql > describe user; displays the column information of the user table in the mysql database)

3 、 grant

Create a full superuser that can connect to the server from anywhere, but you must do this with a password something

Mysql > grant all privileges on *. * to 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 IDENTIFIED BY 'something' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON *. * TO "IDENTIFIED BY 'something' WITH GRANT OPTION

Delete Authorization:

Mysql > revoke all privileges on *. * from "

Mysql > delete from user where user= "root" and host= ""

Mysql > flush privileges

Create a user custom to log in to a specific client it363.com and access a specific database fangchandb

Mysql > grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by 'passwd'

Rename the table:

Mysql > alter table T1 rename T2

4 、 mysqldump

Backup database

Shell > mysqldump-h host-u root-p dbname > dbname_backup.sql

Restore the database

Shell > mysqladmin-h myhost-u root-p create dbname

Shell > mysqldump-h host-u root-p dbname

< dbname_backup.sql 如果只想卸出建表指令，则命令如下： shell>

Mysqladmin-u root-p-d databasename > a.sql

If you only want to uninstall the sql command that inserts the data, and you don't need the table creation command, the command is as follows:

Shell > mysqladmin-u root-p-t databasename > a.sql

So what if I just want data and don't want any sql commands?

Mysqldump-T. / phptest driver

Where the plain text file can be unloaded only if the-T parameter is specified, which represents the directory where the data was unloaded, and. / represents the current directory, that is, the same directory as mysqldump. If you do not specify the driver table, the data from the entire database will be unloaded. Each table generates two files, one of which is a .sql file that contains the execution of the table creation. The other is a .txt file that contains only 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 to type the redirect utility to do this. For example, if there is a check in the file my_file.sql

Query, you can execute these queries as follows:

For example, if you want to write the table statement in advance in sql.txt:

Mysql > mysql-h myhost-u root-p database

< sql.txt 1、安装环境： Windows XP Mysql 4.0.17 从 下次就需要用mysql -uroot -proot才可以登陆 在远程或本机可以使用 mysql -h 172.5.1.183 -uroot 登陆，这个根据第二行的策略确定 权限修改生效： 1)net stop mysql net start mysql 2)c:\mysql\bin\mysqladmin flush-privileges 3)登陆mysql后，用flush privileges语句 6、创建数据库staffer create database staffer; 7、下面的语句在mysql环境在执行 显示用户拥有权限的数据库 show databases; 切换到staffer数据库 use staffer; 显示当前数据库中有权限的表 show tables; 显示表staffer的结构 desc staffer; 8、创建测试环境 1)创建数据库staffer mysql>

Create database staffer

2) create table staffer,department,position,depart_pos

Create table s_position

(

Id int not null auto_increment

Name varchar (20) not null default 'Manager', # set default value

Description varchar (100)

Primary key PK_positon (id) # set the primary key

);

Create table department

(

Id int not null auto_increment

Name varchar (20) not null default 'system Department', # set default values

Description varchar (100)

Primary key PK_department (id) # set the primary key

);

Create table depart_pos

(

Department_id int not null

Position_id int not null

Primary key PK_depart_pos (department_id,position_id) # sets the complex and primary key

);

Create table staffer

(

Id int not null auto_increment primary key, # set the primary key

Name varchar (20) not null default 'John Doe', # set the default value

Department_id int not null

Position_id int not null

Unique (department_id,position_id) # sets a unique value

);

3) Delete

Mysql >

Drop table depart_pos

Drop table department

Drop table s_position

Drop table staffer

Drop database staffer

9. Modify the structure

Mysql >

# add column test to table position

Alter table position add (test char (10))

# Table position modify column test

Alter table position modify test char (20) not null

# Table position modifies column test default values

Alter table position alter test set default 'system'

# Table position remove the default value of test

Alter table position alter test drop default

# Table position removes column test

Alter table position drop column test

# Table depart_pos delete primary key

Alter table depart_pos drop primary key

# Table depart_pos adds 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 ('Systems Department', 'Systems Department')

Insert into department (name,description) values ('Public Relations', 'Public Relations')

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 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 employee', 'ordinary employee')

# insert table 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

# insert table staffer

Insert into staffer (name,department_id,position_id) values ('Chen Dazhi', 1pc1)

Insert into staffer (name,department_id,position_id) values ('Li Wenbin', 1pc2)

Insert into staffer (name,department_id,position_id) values ('Ma Jia', 1pr 3)

Insert into staffer (name,department_id,position_id) values ('Kang Zhiqiang', 5pm 1)

Insert into staffer (name,department_id,position_id) values ('Yang Yuru', 4jin1)

11. Query and delete operations

# display the personnel and positions of the systems 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. nameplate 'Systems 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. nameplate 'Systems Department'

# show 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

Delete from department where name=' customer Service Department'

# change the Finance Department to Finance Department

Update department set name=' Finance Department 'where name=' Finance Department'

12. Backup and restore

Backup database staffer

C:\ mysql\ bin\ mysqldump-uroot-proot staffer > e:\ staffer.sql

The resulting staffer.sql is a sql script that does not include statements to build the library, so you need to do it manually

Create a database to import

To restore the database staffer, you need to create an empty library staffer

C:\ mysql\ bin\ mysql-uroot-proot staffere:\ staffer.sql

Mysql-uroot-proot > e:\ staffer.sql

However, in this way, the staffer library cannot exist and the database with other names cannot be imported.

Of course, you can modify the staffer.sql file manually.

13. Import data from text to database

1) use the tool c:\ mysql\ bin\ mysqlimport

The purpose of this tool is to import the file into a table with the same name as the file extension, as shown in

Staffer.txt,staffer is imported into the staffer table.

Common options and functions are as follows

-d or-- delete delete all information in the data table before the new data is imported into the data table

-- for-- force will force data insertion to continue regardless of whether an error is encountered or not

-I or-- ignore mysqlimport skips or ignores those that have the same unique

Keyword, the data in the import file will be ignored.

-l or-lock-tables data locks the table before it is inserted, which prevents

When you update the database, the user's queries and updates are affected.

The-r or-replace option is the opposite of the-I option; this option replaces the

A record with the same unique keyword in the table.

-- fields-enclosed- by= char

Specifies what is enclosed when recording data in a text file, in many cases

The data is enclosed in double quotes. By default, the data is not enclosed in characters.

-- fields-terminated- by=char

Specifies the separator between the values of each data, in a period-delimited file

The delimiter is a full stop. You can use this option to specify a separator between data.

The default delimiter is the jumper (Tab)

-- lines-terminated- by=str

This option specifies a delimited string of data between lines in a text file

Or characters. By default, mysqlimport uses newline as the line delimiter.

You can choose to replace a single character with a string:

A new line or a carriage return.

Another common option for the mysqlimport command is the-v display version (version)

-p prompt for password (password), etc.

The problem with this tool is that some columns cannot be ignored, which is very troublesome for our data import, although

You can set this field manually, but there will be an inexplicable result. Let's do a simple example.

We define the following depart_no.txt, stored on e disk, spaced by tabs\ t

10 10

11 11

12 24

Execute the following command

C:\ mysql\ bin\ mysqlimport-uroot-proot staffer e:\ depart_pos.txt

The column bounding symbol is not used here, and the default\ t is used for segmentation, because there will be problems with using other symbols.

I don't know if it's 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 you can specify column imports, as shown in the following example

C:\ mysql\ bin\ mysql-uroot-proot staffer

Mysql > load data infile "e:/depart_no.txt" into depart_no (department_id,position_id)

There are problems with the use of these two tools under Windows. I don't know if it is because of Windows or Chinese.

And the unspecified column produces a null value, which is obviously not what we want, so use these tools carefully

Enter MySQL:mysql-uuser-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 database you created

Mysql > USE MYSQLDATA; (if Database changed appears by pressing enter key, 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 (20), sex 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 database tables in text (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 the table

Mysql > drop TABLE MYTABLE

11: clear the table

Mysql > delete from MYTABLE

12: update data in the 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 the rows in the existing table with the new values. The SET clause indicates which column to modify and the value they should be given. WHERE

If a clause is given, specify which record row should be updated. Otherwise, all record rows are updated. If the ORDER BY clause is specified, the record rows are updated in the specified order.

If you specify the keyword LOW_PRIORITY,UPDATE, the 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 a duplicate key error occurs during the update process. The record row 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 add 1 to its current value:

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 its size, and then adds 1:

Mysql > UPDATE persondata SET age=age*2, age=age+1

If you set the column to its current value, MySQL takes note of this and does not update it.

UPDATE returns the number of record rows that have actually been changed. In MySQL 3.22 or later, the C API function mysql_info ()

Returns the number of record rows that were matched and updated, as well as 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 of records are changed.

If an ORDER BY clause is used (supported from MySQL 4.0.0 onwards), the record rows will be updated in the specified order. This is actually only in conjunction with LIMIT

It only works together.

Starting with MySQL 4.0.4, you can also perform a UPDATE operation that contains multiple tables:

UPDATE items,month SET items.price=month.price

WHERE items.id=month.id

Note: multi-table UPDATE cannot use ORDER BY or LIMIT.

Keyword: mysql

Launch: 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

Display the properties of the table column: show columns from tableName

Set up the database: source fileName.txt

Matching characters: you can use the wildcard character _ to represent any 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 words cannot be disconnected; when inserting or changing data, the string of the field cannot be expanded into multiple lines, otherwise hard enter will be stored in the data

Add an administrator account: grant all on *. * to identified by "password"

Add a semicolon at the end of each statement after typing, or you can add'\ g'.

Query time: select now ()

Query the current user: select user ()

Query version: select version ()

Query the currently used database: select database ()

1. Delete the students table in the student_course database:

Rm-f student_course/students.*

2. Back up the database: (back up the database test)

Dump-u root-p test > c:\ test.txt

Backup table: (backup mytable table under test database)

Mysqldump-u root-p test mytable > c:\ test.txt

Import backup data into the database: (import back to 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 is to determine whether the table exists or not.

Create table if not exists students (…)

5. Copy the structure of the table from the existing table

Create table table2 select * from table1 where 11

6. Copy the 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;// modifies the type of column id to int unsigned

Alter table table1 change id sid int unsigned;// changed the name of the column id to sid and changed the property 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); / / build uniqueness index

10. Delete the index

Drop index idx_id on table1

Alter table table1 drop index ind_id

11. Join characters or multiple columns (connect column id with ":" and columns name and "=")

Select concat (id,':',name,'=') from students

12. Limit (10 to 20 selected)

Select * from students order by id limit 9 and 10

13. Features not supported by MySQL

Transactions, views, foreign keys and referential integrity, stored procedures and triggers

14. MySQL will use the operation symbol of the index

, =, between,in, without like starting with% or _

15. Disadvantages of using indexes

1) slow down the speed of adding, deleting and correcting data

2) take up disk space

3) increase the burden of the query optimizer

When the query optimizer generates the execution plan, it will consider the index. Too many indexes will increase the workload of the query optimizer and make it impossible to choose the best query scheme.

16. Analyze the efficiency of index

Method: add explain before the general SQL statement

The meaning of the analysis results:

1) table: table name

2) type: type of connection, (ALL/Range/Ref). Among them, ref is the most ideal

3) possible_keys: the index name that can be used by the query

4) key: the index actually used

5) key_len: the length of the part used in the index (in bytes)

6) ref: displays the column name or "const" (I don't know what it means)

7) rows: displays the number of rows that MySQL believes must be scanned before finding the correct results

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 whenever possible

A) using char instead of varchar, fixed length data processing is faster than longer data processing

B) for tables that are frequently modified, the disk is easily fragmented, thus affecting the overall performance of the database

C) in the event of a data table crash, tables that use 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 not necessarily using variable-length data rows.

D) for MyISAM-type data tables, although converting to fixed-length data columns can improve performance, it also takes up a lot of space.

18. Use not null and enum

Define the column as not null as far as possible, so that the data can come out faster and require less space, and when querying, MySQL does not need to check whether there is a special case, that is, a null value, thus optimizing the query

If a column contains only a limited number of specific values, such as gender, validity or school year, in this case, you should consider converting it to the value of the enum column. MySQL processes it faster, because all enum values are expressed as identification values in the system.

19. Use optimize table

For frequently modified tables, it is easy to produce fragments, so that more disk blocks must be read when querying the database, which reduces the query performance. All tables with variable length have a disk fragmentation problem, which is more prominent for blob data types because of their large size changes. You can use optimize table to defragment to ensure that database performance does not degrade and optimize data tables that are affected by fragmentation. Optimize table can be used for data tables of both MyISAM and BDB types. In fact, any defragmentation method is to use mysqldump to dump the data table, and then use the transferred file and recreate the data table.

20. Use procedure analyse ()

You can use procedure analyse () to display the best type of suggestion, which is as simple as adding procedure analyse () to the select statement; for example:

Select * from students procedure analyse ()

Select * from students procedure analyse (16256)

The second statement asks procedure analyse () not to recommend enum types with more than 16 values or 256bytes. If there is no limit, the output may be very long.

21. Use query caching

1) how the query cache works:

The first time a select statement is executed, the server remembers the text content and the query results of the query, stores them in the cache, and returns the results directly from the cache the next time the statement is encountered; when the data table is updated, any cached queries of the data table become invalid and will be discarded.

2) configure cache parameters:

Variable: query_cache _ type, the operation mode of the query cache. There are 3 modes, 0: do not cache; 1: cache queries unless they start with select sql_no_cache; 2: cache only those queries that start with select sql_cache as needed; query_cache_size: set the size of the maximum result set of the query cache, and those 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 the waiting time of Iripple O

Seek time is the main factor that determines performance. Moving the head word by word is the slowest. Once the head is positioned, it will be read quickly from the track.

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 different partitions using the same physical device, because they will compete for the same physical resources (heads).

At this point, the study of the "complete Collection of Common MySQL commands" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.