Shulou(Shulou.com)06/01 Report--

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

Mysql adds columns, modifies column names, column attributes, and deletes column statements

Mysql modifies table name, column type, adds table column, deletes table column

Alter table test rename test1;-- modify the table name

Alter table test add column name varchar (10);-- add table columns

Alter table test drop column name;-Delete table columns

Alter table test modify address char (10)-- modify the table column type

| | alter table test change address char (40) |

Alter table test change column address address1 varchar (30)-- modify the table column name

1. In this way, you also need to log in to mysql with the root command and then execute:

SET PASSWORD FOR root=PASSWORD ('123456'); set the database password to 123456

SET PASSWORD FOR root=PASSWORD (''); clear the database password

Mysql-uroot-pOLDPASS-e "use mysql;UPDATE user SET password=PASSWORD ('123456') WHERE user='root';FLUSH PRIVILEGES;"

Where: OLDPASS is the original password

GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' PASSWORD'

Flush privileges

The mysql database root user establishes a remote connection. PASSWORD is the set password

Export

1. Export the full library backup to the local directory

Mysqldump-u$USER-p$PASSWD-h227.0.0.1-P3306-routines-- default-character-set=utf8-- lock-all-tables-- add-drop-database-A > db.all.sql

2. Export the specified library to a local directory (for example, mysql library)

Mysqldump-u$USER-p$PASSWD-h227.0.0.1-P3306-routines-- default-character-set=utf8-- databases mysql > db.sql

3. Export the table of a library to a local directory (for example, the user table of the mysql library)

Mysqldump-u$USER-p$PASSWD-h227.0.0.1-P3306-routines-- default-character-set=utf8-- tables mysql user > db.table.sql

4. Export the table of the specified library (data only) to the local directory (for example, the user table of the mysql library with filter conditions)

Mysqldump-u$USER-p$PASSWD-h227.0.0.1-P3306-routines-default-character-set=utf8-no-create-db-no-create-info-tables mysql user-where= "host='localhost'" > db.table.sql

5. Export all the table structures of a library

Mysqldump-u$USER-p$PASSWD-h227.0.0.1-P3306-- routines-- default-character-set=utf8-- no-data-- databases mysql > db.nodata.sql

6. Export the data of a query sql to a local directory in txt format (the data values are separated by "tabs")

For example, sql is' select user,host,password from mysql.user;'

Mysql-u$USER-p$PASSWD-h227.0.0.1-P3306-- default-character-set=utf8-- skip-column-names-B-e 'select user,host,password from mysql.user;' > mysql_user.txt

7. Export the data of some query sql to txt format file to MySQL server.

Log in to MySQL and change the default tab to a comma. Adapt to csv format files.

For the specified path, mysql must have write permission. It's best to use the tmp directory and delete the files after you run out of them!

SELECT user,host,password FROM mysql.user INTO OUTFILE'/ tmp/mysql_user.csv' FIELDS TERMINATED BY','

Import

Restore the full database data to MySQL, because it contains the permission table of the mysql library, and the FLUSH PRIVILEGES; command needs to be executed when the import is completed.

The first method: mysql-u$USER-p$PASSWD-h227.0.0.1-P3306-default-character-set=utf8

< db.all.sql第二种方法:登录MySQL,执行source命令,后面的文件名要用绝对路径.......mysql>

Source / tmp/db.all.sql

Restore the data of a library (user table of the mysql library)

The first method: mysql-u$USER-p$PASSWD-h227.0.0.1-P3306-default-character-set=utf8 mysql

< db.table.sql第二种方法:登录MySQL,执行source命令,后面的文件名要用绝对路径.mysql -u$USER -p$PASSWD -h227.0.0.1 -P3306 --default-character-set=utf8......mysql>

Use mysql;mysql > source / tmp/db.table.sql

Restore txt format files on the MySQL server (requires FILE permission, and data values are separated by "tabs")

Mysql-u$USER-p$PASSWD-h227.0.0.1-P3306-- default-character-set=utf8.mysql > use mysql;mysql > LOAD DATA INFILE'/ tmp/mysql_user.txt' INTO TABLE user

Restore csv format files on the MySQL server (requires FILE permission, separated by "commas")

Mysql-u$USER-p$PASSWD-h227.0.0.1-P3306-- default-character-set=utf8.mysql > use mysql;mysql > LOAD DATA INFILE'/ tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY','

Restore local txt or csv files to MySQL

Mysql-u$USER-p$PASSWD-h227.0.0.1-P3306-- default-character-set=utf8.mysql > use mysql;# txtmysql > LOAD DATA LOCAL INFILE'/ tmp/mysql_user.csv' INTO TABLE user;# csvmysql > LOAD DATA LOCAL INFILE'/ tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY','

Matters needing attention

About MySQL connection

-u$USER user name

-p$PASSWD password

-h227.0.0.1 if you connect to a remote server, replace it with the corresponding hostname or IP address

-P3306 port

-- default-character-set=utf8 specified character set

About mysql parameter

-- skip-column-names does not display the names of data columns

-B runs the mysql program in batch mode. The query results will be displayed in tab spacing format.

-e exit after executing the command

About mysqldump parameter

-A full database backup

-- routines backup stored procedures and functions

-- default-character-set=utf8 sets character set

-- lock-all-tables global consistency lock

Add-drop-database executes the DROP TABLE IF EXIST statement before each table-building statement

-- no-create-db does not output CREATE DATABASE statements

-- no-create-info does not output CREATE TABLE statements

-- databases resolves all the following parameters to the library name

-- the first parameter of tables is the library name, followed by the table name.

About LOAD DATA Grammar

If the LOAD DATA statement does not have the LOCAL keyword, read the file directly on the MySQL server and have FILE permission.

If you take the LOCAL keyword, read the data file locally on the client side and transfer it to MySQL.

The LOAD DATA statement, which is also logged to binlog, is an internal mechanism.

At this point, the study on "introduction of common commands in mysql database" 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.