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

This article will explain in detail what commands are added, deleted, changed and searched in the MySQL database, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

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

About the MySQL database which add, delete, change and search commands to share 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.