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

Let me tell you a little bit about the common command operations of mysql database. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it is more or less helpful to read the common command operations of the mysql database.

Make good use of help help

It is common to master basic additions, deletions, changes and queries, and to build a database and a table index.

Basic addition, deletion, modification, creation and deletion

Show databases; View Library

Show tables; View Table

Select database (); view the current database

Select now (); view the current time

Select user (); view the current user

Library operation:

Create database dddd character set gbk collate gbk_chinese_ci; creates a dddd library of gbk characters

Show create database dddd\ G to view the database building statements, the character permissions of the library, etc.

Drop database dddd; delete database dddd

Table operation:

Create table dong (id int (4) not null,name char (10) not null); create dong table

Show create table dong\ G View Table creation statement

Desc table_name; query table structure

Drop table (s) table_name; delete table

Insert into table_name (lie,lie,lie) values; insert data into the table

Delete from table where id=1 and name= "dongshizhang"; delete the data in the table according to where conditions

Can + U start the database to prevent misoperation (alias alias can be set)

Truncate table_name emptying table

Modify field type, field name

Alter table table_name modify | change

Rename table_name name to name1; modifies the table name

User authorization:

Grant all on. To user@ "%" identified by '111111; authorized user

Show grants for user@'localhost'; view user authorization

Revoke insert user@'localhost'; removes user-specified permissions

About the index:

Alter table ling change id id int primary key auto_increment; adds a self-increasing primary key

Atler table student drop peimary key; delete primary key (with auto_increment self-increment primary key, delete self-increment first)

Alter table table_name modify id int; [modify column type]

Create unique index index_index on table (age); create a unique index (that is, add a unique before index)

Atler table student add index index_name (name (8)); name column to create a normal index

Create index index_name on student (name (8); creates an index based on the first N characters of the name column

Create index inde_name_dept on student (name,dept); create federated indexes based on multiple columns

Alter table student drop index index_name; delete index

Drop index index_name on table; delete index

Create index index_name on table (age); create index

The creation of federated index is similar to that of ordinary index.

The federated index has the feature of prefix validity.

Only the three query conditions of index can go to the index, but the index can not be used by the other three query conditions.

Database slow query

Mysql-uroot-p "passwd"-h227.0.0.1-e "show processlist;" | grep slow query database slow query

Show databases like 'specified field query library'

System echo $LANG executes commands outside the database

Database table backup

Mysqldump-u-p-B library name > / root/ back up the library to the specified location

Grep-E-v "# |\ / | ^ $|--". / lingling_utf8.sql view the contents of the backup library

Mysqladmin-uroot-p flush-log log cutting

What do you think of the common command operation of mysql database? what do you think of this article? is it fruitful? If you want to know more about it, you can continue to follow our industry information section.

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.