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

This article mainly explains "the summary of MYSQL commands in linux". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the summary of MYSQL commands in linux.

1. To sum up:

The command to start mysql under 1.linux:

Mysqladmin start

/ ect/init.d/mysql start (previous is the installation path of mysql)

Command to restart mysql under 2.linux:

Mysqladmin restart

/ ect/init.d/mysql restart (previous is the installation path of mysql)

The command to turn off mysql under 3.linux:

Mysqladmin shutdown

/ ect/init.d/mysql shutdown (previous is the installation path of mysql)

4. Connect the mysql on this computer:

Enter the directory mysql\ bin, type the command mysql-uroot-p, and press enter to prompt for the password.

Exit mysql command: exit (enter)

5. Change the mysql password:

Mysqladmin-u username-p old password password new password

Or enter the mysql command line SET PASSWORD FOR root=PASSWORD ("root")

6. Add new users. (note: commands in the mysql environment are followed by a semicolon as a command Terminator)

Grant select on database. * to user name @ login host identified by "password"

Such as adding a user's test password to 123, 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" 123"

II. Operations related to mysql database

You must first log in to mysql, all at the mysql prompt, and each command ends with a semicolon

1. Display the list of databases.

Show databases

2. Display the data table in the library:

Use mysql; / / Open the library

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in the table:

Delete from table name

8. Display the records in the table:

Select * from table name

9. Modification of the code

To change the encoding format of the entire mysql:

When you start mysql, the mysqld_safe command line joins

-- default-character-set=gbk

To change the encoding format of a library: enter the command at the mysql prompt

Alter database db_name default character set gbk

III. Import and export of data

1. Transfer the text data to the database

The format that the text data should conform to: the field data is separated by the tab key, and the null value is used instead. Example:

1 name duty 2006-11-23

Data input command load data local infile "file name" into table table name

2. Export databases and tables

Mysqldump-- opt news > news.sql (back up all the tables in the database news to a news.sql file, where news.sql is a text file with any file name. )

Mysqldump-- opt news author article > author.article.sql (back up the author table and the article table in the database news to the author.article.sql file, where author.article.sql is a text file with any file name. )

Mysqldump-- databases db1 db2 > news.sql (back up the databases dbl and db2 to a news.sql file, where news.sql is a text file with any file name. )

Mysqldump-h host-u user-p pass-- databases dbname > file.dump

Is to import the database dbname with the name user and password pass on host into the file file.dump

Mysqldump-- all-databases > all-databases.sql (back up all databases to an all-databases.sql file, where all-databases.sql is a text file with any file name. )

3. Import data

Mysql

< all-databases.sql（导入数据库） mysql>

Source news.sql; (executed under the mysql command to import tables)

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 test1@\ "%\" Identified by\ "abc\"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your MySQL database on any computer on internet and can do whatever he or she wants with your data.

Example 2, add a user's test2 password to abc, so that he can only log in on localhost and query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that even if the user knows the test2 password, he can not access the database directly from the internet, but only through the web page on the MySQL host.

Grant select,insert,update

Delete on mydb.* to test2@localhost 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 test2@localhost 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.

First, operation skills

1. If you enter the order and find that you forgot to add the semicolon, you don't have to type the command again, just hit the semicolon and enter. In other words, you can type a complete command into several lines and finish it with a semicolon.

You can use the cursor up and down keys to call up the previous command. But an old version of MySQL that I used before doesn't support it. I am using mysql-3.23.27-beta-win now.

Second, display commands

1. Display a list of databases:

Show databases

At the beginning, there were only two databases: mysql and test. The MySQL library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library:

Use mysql; / / Open the library, those who have studied FOXBASE must be familiar with it.

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in the table:

Delete from table name

8. Display the records in the table:

Select * from table name

An example of building a database and table as well as inserting data

Drop database if exists school; / / delete if SCHOOL exists

Create database school; / / build library SCHOOL

Use school; / / Open the library SCHOOL

Create table teacher / / create table TEACHER

(

Id int (3) auto_increment not null primary key

Name char (10) not null

Address varchar (50) default 'Shenzhen'

Year date

); / / end of table creation

/ / the following are insert fields

Insert into teacher values (', 'glchengang',' Shenzhen No.1 Middle School', '1976-10-10')

Insert into teacher values (', 'jack',' Shenzhen No.1 Middle School', '1975-12-23')

Note: in the establishment of the table (1) set ID to the length of 3 number field: int (3) and let it automatically add one for each record: auto_increment can not be empty: not null and let him become the main field primary key (2) set NAME to 10 character field (3) set ADDRESS to 50 character field, and the default value is Shenzhen. What's the difference between varchar and char? we'll have to wait for a later article. (4) set YEAR to the date field.

It's OK if you type the above command at the MySQL prompt, but it's not easy to debug. You can write the above command as is in a text file assuming school.sql, then copy it to c:\\, enter the directory\\ mysql\\ bin in the DOS state, and type the following command:

Mysql-uroot-p password

< c:\\school.sql 如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。 四、将文本数据转到数据库中 1、文本数据应符合的格式：字段数据之间用tab键隔开，null值用\\n来代替。 例： 3 rose 深圳二中 1976-10-10 4 mike 深圳一中 1975-12-23 2、数据传入命令 load data local infile \"文件名\" into table 表名。 注意：你最好将文件复制到\\mysql\\bin目录下，并且要先用use命令打表所在的库。 五、备份数据库： 1、mysqldump --opt school>

School.bbb

Mysqldump-- opt school > school.bbb

(the command is executed in the\\ mysql\\ bin directory of DOS)

Note: back up the database school to the school.bbb file. School.bbb is a text file with any file name. Open it and you will find something new.

Postscript: in fact, the operation of MySQL on the database is more or less the same as other SQL databases, you'd better find a book to read SQL. I only introduce some basics here, in fact, that's all I know, hehe. The best MYSQL tutorial is the MYSQL Chinese reference Manual translated by Yanzi. It is not only free to download from every relevant website, but also the most authoritative. Unfortunately, it is not in the chm format like\ "PHP4 Chinese manual\", so it is not convenient to find function commands.

2. Change the login password

1) mysqladmin-u username-p old password password new password:

Example: mysqladmin-u root password 21century

Note: since root does not have a password at the beginning, the-p old password can be omitted.

2) directly modify the root user password in the user table:

Mysql > user mysql

Mysql > update user set pasword=password ('21century') where user='root'

Mysql > flush privileges

Note: flush privileges means to force the memory authorization table to be flushed, otherwise the password in the buffer will be used.

3. Test whether the password has been modified successfully:

1) Log in without password:

[root@test1 local] # mysql

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

An error is displayed indicating that the password has been changed.

2) Log in with the modified password:

[root@test1 local] # mysql-u root-p

Enter password: (enter the modified password 21century)

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 177 to server version: 3.23.48

Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

Mysql >

Success!

This is to change the password through the mysqladmin command, or you can change the password by modifying the library.

4. Start and stop:

Startup: Mysql has been changed since version 3.23.15. By default, the service should be started by MySQL users after installation, and root users are not allowed to start it.

If you have to start with the root user, you must add the-- user=root parameter (. / safe_mysqld-- user=root &) to stop: mysqladmin-u root-p shutdown.

5. Export meeting database:

Mysqldump-uroot-p21century meeting > db_meeting.sql

Guide dbname database:

Mysqldump-uroot-p21century dbname < xxx.sql

Importing a database can also execute a large number of sql statements at a time in a way similar to @ my_script.sql in oracle, which is useful when using mysqldump does not work.

Example: #. / mysql-uroot-p < / home/xuys/db_meeting_2003.sql

(note: create database, use databasename, create table and insert into statements can all be written in the footstep file above)

6. Rename the table:

RENAME TABLE ztemp TO ztemp4

7. Modify field properties:

ALTER TABLE bbabase CHANGE news_id id VARCHAR (5) NOT NULL

8. Add a field after content in the table:

ALTER TABLE bbabase ADD leave_time DATETIME NOT NULL AFTER

At this point, I believe you have a deeper understanding of the summary of MYSQL commands in linux, so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.