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

This article mainly explains "what are the basic commands of MYSQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what are the basic commands of 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.

All http://vip.6to23.com/wocienyoung provided by novelty World iNENS

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 assumed to be school.sql, then copy it to c:, enter the directory mysqlin in DOS state, and type the following command:

Mysql-uroot-p password

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

Thank you for your reading, the above is the content of "what are the basic commands of MYSQL". After the study of this article, I believe you have a deeper understanding of what the basic commands of MYSQL have, 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.