Mysql Command Instance Rollup _mysql

The example of this article summarizes the various operation commands used by MySQL. Share to everyone for your reference, specific as follows:

MySQL installation directory

Database directory

/var/lib/mysql/

Configuration file

/usr/share/mysql (mysql.server command and configuration file)

Related commands

/usr/bin (mysqladmin mysqldump order)

Startup script

/etc/init.d/mysql (Directory of startup script files MySQL)

System Management

Connect MySQL

Format: mysql-h host address-u user name-P user Password

Example 1: Connect to MySQL on this computer.

Copy Code code as follows:

hadoop@ubuntu:~$ Mysql-uroot-pmysql;

Example 2: Connect to MYSQL on the remote host.

Copy Code code as follows:

hadoop@ubuntu:~$ mysql-h 127.0.0.1-uroot-pmysql;

Modify New Password

In the terminal input: Mysql-u username-p password, enter MySQL.

> Use MySQL;
> Update user Set Password=password (' New password ') where user= ' username ';
> Flush Privileges; #更新权限
> quit; #退出

Add new users

Format: Grant Select on database. * To User name @ Login host identified by ' password '

Example:

Example 1: Add a user test1 password to ABC, so that he can log on any host, and all databases have
Permissions to query, insert, modify, and delete. First connect the root user to MySQL, and then type the following command:

Copy Code code as follows:

Mysql>grant select,insert,update,delete on *.* to root@localhost identified by ' MySQL ';

Or

Copy Code code as follows:

Grant all privileges on *.* to root@localhost identified by ' MySQL ';

Then refresh the permission settings.

Copy Code code as follows:

Flush privileges;

Example 2: If you do not want to root password operation database "MyDB" in the data table, you can make another command to eliminate the password.

Copy Code code as follows:

Grant Select,insert,update,delete on mydb.* to root@localhost identified by ';

Delete User

hadoop@ubuntu:~$ mysql-u user name-p password mysql>delete from user
where user= ' username ' and host= ' localhost ';
Mysql>flush privileges;
Delete User's database
mysql>drop db dbname;

Database operations

Show all the Databases

Copy Code code as follows:

mysql> show databases;

(Note: Last has an S)

Creating a Database

Copy Code code as follows:

mysql> CREATE DATABASE test;

Connecting to a database

Copy Code code as follows:

mysql> use test;

View the currently used database

Copy Code code as follows:

Mysql> Select Database ();

Table information contained in the current database

Copy Code code as follows:

Mysql> Show tables;

(Note: Last has an S)

Delete Database

Copy Code code as follows:

mysql> drop database test;

Table Operations

Note: You should connect to a database using use < database name > before the Operation .

Building a table

Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];

Example:

Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char (a) not null,
   > sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

Get table structure

Command: DESC table name, or Show columns from table name

Example:

Mysql> describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;

Delete Table

Command: DROP table < table name >

For example, to delete a table named MyClass

Copy Code code as follows:

mysql> drop table MyClass;

Inserting data

Command: INSERT into < table name > [< field name 1>[,.. < field name n >]] VALUES (value 1) [, (value N)]
Example:

Copy Code code as follows:

mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

Data in a query table

Query All Rows

Copy Code code as follows:

Mysql> select * from MyClass;

Query the first few lines of data

For example: View the first 2 rows of data in table MyClass

Copy Code code as follows:

Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;

Or

Copy Code code as follows:

Mysql> select * from MyClass limit 0, 2;

Delete data in a table

Command: Delete from table name where expression

For example: Delete a record in table MyClass that is numbered 1

Copy Code code as follows:

Mysql> Delete from MyClass where id=1;

Modify data in a table

Command: Update table name SET field = new value,... WHERE condition

Copy Code code as follows:

mysql> Update MyClass set name= ' Mary ' where id=1;

Add fields to a table

Command: ALTER TABLE name add field type other;

For example, a field passtest is added to the table MyClass, the type is int (4), and the default value is 0

Copy Code code as follows:

Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

Change table name

Command: Rename table original table name to new name;

For example: In the table MyClass the name is changed to Youclass

Copy Code code as follows:

Mysql> Rename table MyClass to Youclass;

Update Field Contents

Command: Update table name set field name = new Content

Update table name set field name = Replace (field name, ' old content ', ' new content ');

For example: Add 4 spaces before the article

Copy Code code as follows:

Update article Set Content=concat (", content);

Database Import Export

To export a database file from a database

Use the "mysqldump" command

First into the DOS interface, and then do the following operation.

1) Export all databases

Format: mysqldump-u [database user name]-p-a>[save path for backup file]

2) Export data and structure

Format: mysqldump-u [Database user name]-p [database name to be backed up]>[save path for backup file]

Example:

Example 1: Export the database mydb to the E:\MySQL\mydb.sql file.

Open Start-> run-> enter "cmd" and enter command line mode.

Copy Code code as follows:

c:\> mysqldump-h localhost-u root-p mydb >e:\mysql\mydb.sql

Then enter the password and wait for the export to succeed and check for success in the target file.

Example 2: Export the mytable in the database mydb to the E:\MySQL\mytable.sql file.

Copy Code code as follows:

c:\> mysqldump-h localhost-u root-p mydb mytable>e:\mysql\mytable.sql

Example 3: Export the structure of the database mydb to the E:\MySQL\mydb_stru.sql file.

Copy Code code as follows:

c:\> mysqldump-h localhost-u root-p mydb--add-drop-table

Note:-h localhost can be omitted, it is generally used on the virtual host.

3 only export data do not export structure

Format:

mysqldump-u [Database user name]-p-t [database name to be backed up]>[save path for backup file]

4) Export events in the database

Format:

mysqldump-u [Database user name]-p-e [Database user name]>[save path to backup file]

5 Export stored procedures and functions in the database

Format:

mysqldump-u [Database user name]-p-r [Database user name]>[save path to backup file]

Import from an external file into a database

1) using the "source" command

First go to the MySQL command console, and then create the database, and then use the database. Finally, perform the following action.

Mysql>source [Save path for backup files]

2 Use "<" symbol

First go to the "MySQL" Command console, and then create the database, and then exit MySQL, into the DOS interface. Finally, perform the following action.

Mysql-u Root–p < [save path for backup files]

Common MySQL Statement supplements:

1. Use the show statement to find out what databases are currently on the server:

Copy Code code as follows:

Mysql> show DATABASES;

2.2, create a database Mysqldata

Copy Code code as follows:

mysql> CREATE DATABASE Mysqldata;

3. Select the database you created

Copy Code code as follows:

mysql> use Mysqldata;

(press ENTER to appear when the database changed the operation success!) )
4. See what tables exist in the current database

Copy Code code as follows:

Mysql> show TABLES;

5. Create a database table

Copy Code code as follows:

Mysql> CREATE TABLE MYTABLE (name VARCHAR (), Sex CHAR (1));

6. Display the structure of the table:

Copy Code code as follows:

Mysql> DESCRIBE MYTABLE;

7. Add a record to the table

Copy Code code as follows:

mysql> INSERT INTO MYTABLE values ("HyQ", "M");

8. Loading data into a database table (e.g. D:/mysql.txt) in a textual manner

Copy Code code as follows:

mysql> LOAD DATA Local INFILE "D:/mysql.txt" into TABLE MYTABLE;

9. Import. sql file commands (e.g. D:/mysql.sql)

Copy Code code as follows:

Mysql>use database;
Mysql>source D:/mysql.sql;

10. Delete Table

Copy Code code as follows:

Mysql>drop TABLE MYTABLE;

11. Clear the Table

Copy Code code as follows:

Mysql>delete from MYTABLE;

12. Update the data in the table

Copy Code code as follows:

Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';

Here's what you'll see on the web with MySQL management experience:

In Windows, MySQL exists as a service, and you should make sure that the service is started and that the available net start MySQL command is not started before you use it. Linux can be started with the "/etc/rc.d/init.d/mysqld start" command, and notice that the initiator should have administrator privileges.

The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, where the anonymous account deletion, the root account set the password, you can use the following command:

Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';

If you want to limit the logon terminals used by the user, you can update the host field for the corresponding user in the users table, restart the database service after making the above changes, and at this point you may be able to log on to the following similar commands:

Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;

The command parameters above are part of the common parameters, and the details refer to the documentation. The mydb here is the name of the database to log on to.

In the development and practical applications, users should not only use root to connect the database, although the use of root users to test is very convenient, but it will bring significant security risks to the system, but also not conducive to the improvement of management technology. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete data. MySQL user management is implemented through the user table, there are two ways to add new users, one is to insert the corresponding data rows in the user table, set the appropriate permissions, and the second is to create a user with some kind of permission through the grant command. One of the common uses of grant is as follows:

Grant all on mydb.* to newusername@hostname identified by "password";
Grant usage on *.* to newusername@hostname identified by "password";
Grant Select,insert,update on mydb.* to newusername@hostname identified by "password";
Grant Update,delete on MyDB. TestTable to Newusername@hostname identified by "password";

To give this user the ability to manage his or her permissions on the object, add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption, in case the malicious person steals the password. For those who have not used the user should be given clearance, permissions of the user should be timely recall permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.

The following gives an explanation of the common rights that I get from other sources (www.cn-java.com):

Global Administrative permissions:

File: Read and write files on the MySQL server.
PROCESS: Displays or kills a service thread belonging to another user.
RELOAD: Overload access Control table, refresh log, etc.
SHUTDOWN: Turn off MySQL service.
Database/data Table/Data column permissions:
Alter: Modify existing data tables (for example, add/Remove Columns) and indexes.
Create: Create a new database or datasheet.
Delete: Deletes a table record.
Drop: Deletes a datasheet or database.
Index: Create or delete indexes.
INSERT: Adds a record of the table.
SELECT: Displays/searches the records of the table.
UPDATE: Modifies records that already exist in the table.
Special permissions:
All: Allow to do anything (like root).
USAGE: Only Allow login – nothing else is allowed.

I hope this article describes the design of MySQL database to help.