Common operations of MySql at work

Log in to MySQL, and if you connect 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 # Local login

Update the permissions / privileges of the database.

Mysql > flush privileges

View the field format of the data table.

Mysql > desc [table name]

Delete a database.

Mysql > drop database [database name]

Delete a data table.

Mysql > drop table [table name]

Mysql > delete from table name; clear table record

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

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

Delete the column.

Mysql > alter table [table name] drop column [column name]

Add a column 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)

Add a unique column.

Mysql > alter table [table name] add unique ([column name])

Set the column value to be larger.

Mysql > alter table [table name] modify [column name] VARCHAR (3)

Delete the unique column.

Mysql > alter table [table name] drop index [colmn name]

Change the field name to change, change the type to modify, and modify the field name.

Mysql > alter table students change couese courses char (10) after name

Mysql > insert into students (name,sex) value ('gao','m'); insert data into the table

Mysql > insert into students values ('aa33','mysql','20','m'); if no field is specified, it is all fields

Mysql > update students set courses='long'; update modify data. Modify all data without using where

Mysql > update students set courses='aaa' where name='lei'; using where

Mysql > delete from students where courses='aaa'; delete a row

Mysql > create user 'www'@'%' identified by' www123'; create user www password as www123

Mysql > show grants for 'www'@'%'; to view the user's permissions

Mysql > grant all privileges on mydb.* to 'www'@'%'; grants weight to www for all tables of mydb

Create a new user. Log in as root. Switch to the mysql database, create users, and 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 (datasheet) from the sql file.

# mysql-u username-ppassword databasename < / tmp/databasename.sql