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

This article mainly explains the "detailed explanation of common commands in mysql". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "detailed explanation of common commands in mysql".

First, connect MYSQL.

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

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

First, open the DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p. Enter prompts you to enter your password. If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing 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 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 can 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, 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\ "\"

In the last part, we talked about login, adding users, password change and so on. In the next section, 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 then use a semicolon as a closing mark to OK.

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 the 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命令打表所在的库。 五、备份数据库：（命令在DOS的\\mysql\\bin目录下执行） mysqldump --opt school>

School.bbb

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.

Mysql Command Line Common commands

The first step, the start and stop of mysql service

Net stop mysql

Net start mysql

The second step is to log in to mysql

The syntax is as follows: mysql-u username-p user password

Type the command mysql-uroot-p, enter and prompt you to enter your password, enter 12345, and then enter the mysql. The prompt for mysql is:

Mysql >

Note that if you are connected to another machine, you need to add a parameter-h machine IP

Third, add new users

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

For example, add a user's user1 password to password1, so that it can log in on the local computer, 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 user1@localhost Identified by "password1"

If you want the user to be able to log in to mysql on any machine, change localhost to "%".

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

Grant select,insert,update,delete on mydb.* to user1@localhost identified by ""

The fourth trick: operate the database

Log in to mysql and run the following commands at the mysql prompt, each ending with a semicolon.

1. Display the list of databases.

Show databases

There are two databases by default: mysql and test. Mysql stores the system and user rights information of mysql. When we change passwords and add new users, we actually operate on this library.

2. Display the data table in the library:

Use mysql

Show tables

3. Display the structure of the data table:

Describe table name

4. Build and delete databases:

Create database library name

Drop database library name

5. Create a table:

Use library name

Create table table name (field list)

Drop table table name

6. Clear the records in the table:

Delete from table name

7. Display the records in the table:

Select * from table name

Fifth move, export and import data

1. Export data:

Mysqldump-- opt test > mysql.test

Export the database test database to a mysql.test file, which is a text file

For example: mysqldump-u root-p123456-- databases dbname > mysql.dbname

Is to export the database dbname to the file mysql.dbname.

two。 Import data:

Mysqlimport-u root-p123456 < mysql.dbname.

You don't have to explain.

3. Import text data into the database:

The field data of text data is separated by tab.

Use test

Load data local infile File name into table table name

How to use common SQL commands:

(1) data record filtering:

Sql= "select * from data Table where Field name = Field value order by Field name [desc]"

Sql= "select * from data Table where field name like'% field value% 'order by field name [desc]"

Sql= "select top 10 * from data Table where Field name order by Field name [desc]"

Sql= "select * from data Table where field name in ('value 1') 'value 2')"

Sql= "select * from data Table where Field name between value 1 and value 2"

(2) update data records:

Sql= "update data Table set Field name = Field value where conditional expression"

Sql= "update data Table set field 1 = value 1, field 2 = value 2... field n = value n where conditional expression"

(3) Delete data records:

Sql= "delete from data Table where conditional expression"

Sql= "delete from data Table" (delete all records of the data table)

(4) add data records:

Sql= "insert into data Table (Field 1, Field 2, Field 3...) valuess (value 1, value 2, value 3...)"

Sql= "insert into destination data Table select * from Source data Table" (add records from the source data table to the target data table)

(5) Statistical function of data record:

AVG (field name) to get a table column average

COUNT (* | Field name) Statistics on the number of rows of data or the number of rows of data with values in a column

MAX (field name) gets the maximum value of a table column

MIN (field name) gets the minimum value of a table column

SUM (field name) adds the values of the data column

The method of referencing the above function:

Sql= "select sum (field name) as alias from datasheet where conditional expression"

Set rs=conn.excute (sql)

Use rs ("alias") to get the value of the system, and other functions are the same as above.

(VI) creation and deletion of data tables:

CREATE TABLE datasheet name (field 1 type 1 (length), field 2 type 2 (length)... )

Example: CREATE TABLE tab01 (name varchar (50), datetime default now ())

DROP TABLE Datasheet name (permanently delete a Datasheet)

Thank you for your reading, the above is the content of "detailed explanation of the commonly used commands of mysql". After the study of this article, I believe you have a deeper understanding of the detailed explanation of the common commands of mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.