Shulou(Shulou.com)06/03 Report--

This article mainly tells you about the common command methods of adding, deleting, changing and querying MySQL database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help.

Increase

Create a database

Create database if not exists database name default charset utf8 collate utf8_general_ci

If the database does not exist, create the database using the utf-8 default character set of utf8_general_ci

Create a tabl

Create table table_name

(field name 1 field type not null, field name 2 field type, primary key (field name 2))

While creating the table, add a non-empty constraint to field name 1 and set field name 2 as the primary key

Insert data

Insert into table name values (all field values)

Insert into table name (field name 1, field name 2) values (field 1 value, field 2 value) / / data has only part of the field value

Create a user

Create user "user name" @ "host" identified by "password" / / host is login ip, can be localhost or other ip address, if any ip can log in, use%

Delete

Delete database

Drop database if exists database name

Delete tabl

Drop table if exists table name

Delete data

Delete from table name where condition / / without where condition, all data will be deleted

Delete user

Drop user "user name" @ "host"

Change

Update data

Update table_name set field name = attribute new value where condition

Authorized user

Grant permissions on database. Table name to "user name" @ "host" with grant option

/ / permissions include select, update, insert, delete, etc. You can use all to represent all permissions. The following with grant option indicates that the user can grant this permission to other users.

Revoke user authorization and change grant to revoke. Other formats are the same.

Change password

Set password for "user name" @ "host" = password ("New password")

In command line mode, you can use the mysqladmin command

Mysqladmin-uroot-p Old password password New password / / only local MySQL user password can be changed

Check

Select * from table name where condition (groud by, order by,having)

Select is arguably the most commonly used command in the database.

1. * you can use the specified fields in the table instead, and the query results show only the specified fields.

2.select distinct to re-query, distinct should be followed by select

3.select * into table name 2 from table name 1 / / create a table 2 using query results (table 2 does not exist before table 2), into table name should precede from

4.groud by field 1, the query results are grouped by field 1, and the same results are grouped together (field 1 should be included in the query field)

5.order by field 1, the query results are sorted by field 1 (default ascending order), field 1 is followed by a space, desc is sorted in descending order, and ascending order is sorted by asc.

6.having condition, where and having are also filter conditions, difference

Where filters the data in the table directly, so you cannot use functions.

Having is to filter the filtered and grouped data, and you can use the function

If you use having instead of functions, you can just put the condition in where.

Query table structure

Desc table name

Query what databases are available

Show database

Query the currently used database name

Select datebase ()

Query users and corresponding login permissions host

Select host,user from mysql.user

Other common operation commands

Switch database

Use database name

Start / stop mysql

Net start/stop mysql

Login user, command line mode (not in mysql mode)

Mysql-u username-p password-h destination address

If you log in to the local mysql, you don't have to-h

Quit

Exit

Import database

Source path/*.sql

Export database, in command line mode

Mysqldump-u username-p password-h destination ip > path/*.sql

Last

Want to use a graphical interface to manage the database, personally recommend using navicat, a very easy to use database management software, the function is very powerful.

Add, delete, change and check the MySQL database commonly used command methods to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.