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

This article introduces how to set up an account remote in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will help you.

Using Daquan for mysql statement

Download mysql5.0 database installation files

Download mysql-front v3.2

Download mysql-front v5.0

Download mysql-front v5.0 Registration Code

Download mysql user manual

Download the driver (mysql-connector-net-5.0.8.1.rar) required for .net connection to 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.

A) when you open the DOS window (hint: click run in the start menu, enter cmd in the text box and enter enter).

B) enter the bin directory under the mysql installation directory (hint: default cd C:\ Program Files\ MySQL\ MySQL Server 5.0\ bin)

C) 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, user

Name is root and 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. You can also connect visualization tools (MySQL-Front.exe); (download)

4. 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

Another way:

Shell > mysql-u root-p

Mysql > SET PASSWORD FOR root=PASSWORD ("root")

Use mysql

Update user set Password=password ('newpassword') where User='root'; (restart mysql service)

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 a 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

Permissions to query, insert, modify, and delete. First use the root user to connect to mysql, and then type the following

Your order:

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, he can use the

Log in to your mysql database on any computer on internet and do whatever you want with your data.

See example 2 for the solution.

Example 2. Add a user's test2 password to abc, so that it can only log in on localhost, and can access the database

Mydb queries, inserts, modifies, and deletes (localhost refers to local host, that is, mysql data)

The host where the library is located), so that users cannot go straight from the internet even if they know the password of test2

Then you can access the database 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 ""

Operations related to the database. Note: you must log in to mysql first. The following actions are mentioned in mysql.

Symbol, 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 type a semicolon back

The car will be fine. In other words, you can type a complete command into several lines and end it with a semicolon.

The logo is 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 mysql system information.

When we change passwords and add users, we actually use this library to operate.

2. Display the data table in the library:

Use mysql; / / Open the database you want to use

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 construction of the table

(1) set ID to a numeric field of length 3: int (3), and let it automatically add one to each record: auto_increment

Cannot be empty: not null, and make it the main field primary key

(2) set NAME to a character field with a length of 10

(3) set ADDRESS to a character field with a length of 50, and the default is Shenzhen. What's the difference between varchar and char?

Well, 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 use the above command

Write it as is to a text file assumed to be school.sql, then copy it to c:, and in the DOS state, enter the bin directory under the mysql installation directory, 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 表名; 注意：你最好将文件复制到mysql的bin目录下，并且要先用use命令选表所在的库。 五、导出和导入数据：(命令在DOS的mysql的bin目录下执行) 导出表 mysqldump --opt school >

School.sql

Note: back up all the tables in the database school to a school.sql file, where school.sql is a text file

Take any name of the file, open it and you will find something new.

Mysqldump-- opt school teacher student > school.teacher.student.sql

Note: back up the teacher and student tables in the database school to the school.teacher.student.sql text

School.teacher.student.sql is a text file with any file name. Open it and you will find something new.

Import tabl

Mysql

Mysql > create database school

Mysql > use school

Mysql > source school.sql

(or replace school.sql with school.teacher.sql / school.teacher.student.sql)

Export database

Mysqldump-- databases db1 db2 > db1.db2.sql

Note: back up the database dbl and db2 to the db1.db2.sql file, where db1.db2.sql is a text file with a file name

Ren Jie, open it and you will find something new.

(for example:

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.)

Import database

Mysql

< db1.db2.sql 复制数据库 mysqldump --all-databases >

All-databases.sql

Note: back up all databases to an all-databases.sql file, where all-databases.sql is a text file

Take any file name.

Import database

Mysql > source all-databases.sql; (or mysql after exit exits mysql

< all-databases.sql) 3.打开数据库：use dbname； 显示所有数据库：show databases; 显示数据库mysql中所有的表：先use mysql；然后show tables; 显示表的列信息：describe user;(显示表mysql数据库中user表的信息）； 4.创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个 GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION; 5.删除授权： REVOKE ALL PRIVILEGES ON *.* FROM root@"%"; USE mysql; DELETE FROM user WHERE User="root" and Host="%"; FLUSH PRIVILEGES; 6. 创建一个用户custom在特定客户端weiqiong.com登录，可访问特定数据库bankaccount mysql>

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.*

TO custom@weiqiong.com IDENTIFIED BY 'stupid'

7. Rename the table:

ALTER TABLE t1 RENAME t2

To change column a, change from INTEGER to TINYINT NOT NULL (same name)

And change column b from CHAR (10) to CHAR (20), and rename it from b to c:

ALTER TABLE T2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR (20)

Add a new TIMESTAMP column named d:

ALTER TABLE t2 ADD d TIMESTAMP

Add an index to column d and make column a the primary key:

ALTER TABLE T2 ADD INDEX (d), ADD PRIMARY KEY (a)

Delete column c:

ALTER TABLE t2 DROP COLUMN c

Add a new AUTO_INCREMENT integer column, named c:

ALTER TABLE T2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c)

Notice that we index c because the AUTO_INCREMENT column must be indexed, and we declare c to be NOT NULL

Because the indexed column cannot be NULL.

8. Delete record:

DELETE FROM T1 WHERE C > 10

6. Change a few lines:

UPDATE t1 SET user=weiqiong,password=weiqiong

7. Create an index using the first 10 characters of the name column:

CREATE INDEX part_of_name ON customer (name (10))

Application of left join:

Table 1: (song ranking table)

Idsong_idranklisenter1114222333334442

Table 1 shows:

Field English name field type whether the field size can be an empty initial value Note IDbigintNOSONG_IDbigintNORANKintNOLisenterIntNO

Table 2 (repertoire soaring list)

Idsong_idrank_change112223331

Table 2 shows:

Field Chinese name field English name field type field size can be empty initial value Note No IDbigintNO song IDSONG_IDbigintNO ranking change RANK_CHANGEintNO

The requirements of the topic are:

Show the ranking and ranking changes of the songs (Note: there is no change in ranking for the new songs on the list)

Download mysql-front v3.2

Download mysql user manual

Download the driver (mysql-connector-net-5.0.8.1.rar) required for .net connection to mysql

Download mysql5.0 database installation files

Download mysql-front v3.2

Download mysql-front v5.0

Download mysql-front v5.0 Registration Code

Download mysql user manual

Download the driver (mysql-connector-net-5.0.8.1.rar) required for .net connection to mysql

On how to set up accounts in MySQL remote sharing here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.