Mysql database commands

Mysql database commands

Common MySQL commands

1. Connect to MySQL

Format: mysql-h host address-u user name-p User Password

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

First, open the DOS window, enter the directory mysqlbin, then type the command mysql-uroot-p, and press enter to prompt you to enter the password. If you have just installed MYSQL, super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>.

2. Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. exit MYSQL command: exit (Press ENTER ).

Ii. Change the password

Format: mysqladmin-u username-p old password New password

1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-password ab12

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

2. Example 2: Change the root password to djg345.

Mysqladmin-uroot-pab12 password djg345

3. Add new users. (Note: Unlike the above, the following commands in the MySQL environment are followed by a semicolon as the command Terminator)

Format: grant select on database. * to username @ login host identified by \ "password \"

Example 1: Add a user named "test1" with the password "abc" so that the user can log on to any host and have the permission 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 test2 @ localhost identified by \ "abc \";

If you do not want test2 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb

. * To test2 @ localhost identified \"\";

The above describes logon, user addition, password change, and other issues. Next, let's take a look at the database operations in MySQL. Note: you must first log on to MySQL. The following operations are performed at the MySQL prompt and each command ends with a semicolon.

1. MySQL Common commands

Create database name; create a database

Use databasename; select database

Drop database name directly deletes the database, no reminder

Show tables; displays tables

Describe tablename; Detailed description of the table

Add distinct to the select statement to remove duplicate fields.

Before mysqladmin drop database name deletes a database, a message is displayed.

Display current mysql version and current date

Select version (), current_date;

2. Modify the root password in mysql:

Shell> mysql-u root-p

Mysql> update user set password = password ("xueok654123") where user = 'root ';

Mysql> flush privileges // refresh the database

Mysql> use dbname; open the database:

Mysql> show databases; displays all databases

Mysql> show tables; display all tables in mysql: use mysql first; then

Mysql> describe user; displays the column information of the user table in the mysql database );

3. grant

Create a full Super User that can connect to the server from anywhere, but you must use a password something to do this

Mysql> grant all privileges on *. * to user @ localhost identified by 'something'

Add new users

Format: grant select on database. * to username @ login host identified by "password"

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;

Delete authorization:

Mysql> revoke all privileges on *. * from root @ "% ";

Mysql> delete from user where user = "root" and host = "% ";

Mysql> flush privileges;

Create a User custom to log on to it363.com on a specific client and access the specific database fangchandb.

Mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom @ it363.com identified by 'passwd'

Rename a table:

Mysql> alter table t1 rename t2;

4. mysqldump

Back up database

Shell> mysqldump-h host-u root-p dbname> dbname_backup. SQL

Restore database

Shell> mysqladmin-h myhost-u root-p create dbname

Shell> mysqldump-h host-u root-p dbname <dbname_backup. SQL

If you only want to unload the table creation command, the command is as follows:

Shell> mysqladmin-u root-p-d databasename> a. SQL

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows:

Shell> mysqladmin-u root-p-t databasename> a. SQL

What should I do if I only want data and do not want any SQL commands?

Mysqldump-T./phptest driver

Only when the-T parameter is specified can the plain text file be detached, indicating the directory where the data is detached and./indicates the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.

5. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input. You can use the shell to type the redirection utility to do this. For example, if the file my_file. SQL contains a query

Query:

For example, if you want to write a table prefix in SQL .txt:

Mysql> mysql-h myhost-u root-p database <SQL .txt

1. installation environment:

Windows XP

Mysql 4.0.17 you need to use mysql-uroot-proot to log on to Mysql

You can use mysql-h 172.5.1.183-uroot to log on remotely or locally. This is determined based on the policy of the second line.

The permission modification takes effect:

1) net stop mysql

Net start mysql

2) c: \ mysql \ bin \ mysqladmin flush-privileges

3) use the flush privileges statement after logging on to mysql.

6. Create a database staffer

Create database staffer;

7. The following statement is executed in the mysql Environment

Show databases;

Switch to the staffer database use staffer;

Show tables with permissions in the current database;

The desc staffer structure of the staffer table is displayed;

8. Create a test environment

1) Create a database staffer

Mysql> create database staffer

2) create tables staffer, department, position, depart_pos

Create table s_position

(

Id int not null auto_increment,

Name varchar (20) not null default 'manager', # Set the default value

Description varchar (100 ),

Primary key PK_positon (id) # Set the primary key

);

Create table department

(

Id int not null auto_increment,

Name varchar (20) not null default 'System amount', # Set the default value

Description varchar (100 ),

Primary key PK_department (id) # Set the primary key

);

Create table depart_pos

(

Department_id int not null,

Position_id int not null,

Primary key PK_depart_pos (department_id, position_id) # Set compound and primary keys