MySQL common operation in work

Log in to MySQL, if you are connecting to a remote database, you need to specify hostname with-H.

#mysql-H hostname-u root-p

#mysql-uroot-p-s/data/3306/mysql.sock #本地登录

Update permissions/privileges for the database.

mysql> flush Privileges;

View the field format of a data table.

mysql> desc [table name];

Deletes a database.

mysql> drop Database [database name];

Deletes a data table.

mysql> drop table [table name];

Mysql>delete from table name; Clear table record

Delete the row in the table [user] = ' blog '.

Mysql> DELETE from [table name] WHERE [user] = ' blog ';

Deletes a column.

mysql> ALTER TABLE [table name] Drop column [column name];

Add columns to db.

mysql> ALTER TABLE [table name] Add column [new column name] varchar (20);

Change the column name.

mysql> ALTER TABLE [table name] change [old column name] [new column name] varchar (50);

Adds a unique column.

mysql> ALTER TABLE [table name] Add unique ([column name]);

Set the column value to a large point.

mysql> ALTER TABLE [table name] modify [column name] VARCHAR (3);

Deletes a unique column.

mysql> ALTER TABLE [table name] DROP INDEX [COLMN name];

Change the name of the field with change, type with modify, modify the field name;

Mysql> ALTER TABLE students change Couese courses char (TEN) after name;

Mysql> INSERT into students (Name,sex) value (' Gao ', ' m '); Inserting data into a table;

Mysql> INSERT into students values (' aa33 ', ' MySQL ', ' + ', ' m '); All fields are not specified;

Mysql> Update students set courses= ' long '; Update modifies data without using where to modify all data

Mysql> Update students set courses= ' AAA ' where name= ' lei '; Use where

Mysql> Delete from students where courses= ' AAA '; Delete a row

mysql> create user ' www ' @ '% ' identified by ' www123 '; Create user www password for www123

Mysql> show grants for ' www ' @ '% '; To view a user's permissions

Mysql> grant all privileges on mydb.* to ' www ' at '% '; Grant MyDB all the tables of the right to www

Create a new user. Log in as root. Switch to MySQL database, create user, refresh permissions.

# mysql-u Root-p

mysql> use MySQL;

Mysql> INSERT into User (Host,user,password) VALUES ('% ', ' www ', Password (' 123456 '));

mysql> flush Privileges;

Mysql> ALTER TABLE students add Couese char (100); Add Field

Export a database.

#mysqldump-u username-ppassword--databases databasename >/tmp/databasename.sql

Export a table from a database.

#mysqldump-C-u username-ppassword databasename tablename >/tmp/databasename.tablename.sql

Restore the database (data table) from the SQL file.

# mysql-u Username-ppassword DatabaseName </tmp/databasename.sql

This article is from the "Bill of Operations Notes" blog, please be sure to keep this source http://chenshoubiao.blog.51cto.com/6159058/1843029

MySQL common operation in work