MySQL Basic operations Summary

Basic operations of the database

1. Create a database

# CREATE DATABASE xxx;

2. View the database

# show databases;

3. Deleting a database

# drop database xxx;

4. Using/Switching databases

# Use XXX;

Basic operation of the table
1. View all tables in the database

# show Tables;

2. Create a table

# CREATE TABLE xxx;

3. View table structure

# desc XXX;

4. View table Detail Structure

# Show create table xxx;

5. Delete a table

# drop table xxx;

6. Modify the table name

# ALTER TABLE XXX rename yyy;

7. Modifying the data type of a field

char (+);

8. Modify the field name and data type

char (50);

9. Add fields

# ALTER TABLE indicates the Add new field name data type
# ALTER TABLE GG add phone varchar (Int (4) not null; # ALTER TABLE GG add address varchar
     (null after phone;

10. Delete a field

# ALTER TABLE GG drop age;

11. Delete a record

# Delete from table name where Condition # Delete from indicates                  // Delete all records

12. Modify the table's storage engine

# ALTER TABLE A Engine=myisam;

13. Delete a foreign KEY constraint for a table

# ALTER TABLE Name drop FOREIGN key foreign key alias # ALTER TABLE yy2 drop foreign key y_fk;

14. Delete a table

# drop table xxx; Delete parent table: Delete the foreign key of all associated child tables first, then delete the parent table

User login and management related actions

1. Create a user

-Create user ' test1 ' @ ' localhost ' identified by ' 123456 ';-> create user ' test2 ' @ ' localhost ' identified by ' PASSWORD (123456) ';-> insert INTO Mysql.user (host,user,password,ssl_cipher,x509_issuer,x509_subject) VALUES (' Hostname ', ' test3 ', PASSWORD (' 123456 '), ' , ', ', ');

2. View user Information

Select Host,user,password from Mysql.user where user='test1 ' or user= ' test2 ' or user= ' test3 '; +-----------+------ -+-------------------------------------------+| Host | User | Password |+-----------+-------+-------------------------------------------+| localhost | Test1 | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | | localhost | Test2 | *A4734F92867FE18E0DD415197D5C33D2E705F0DC | | | Test3 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+-------+-------------------------------------------+

3. Delete a user

' test2 ' @ ' localhost ';-> delete from mysql.user where host= ' localhost ' and user= ' test3 ';

4. Change the root user password

' New password '   Enter password:                   ---> here to enter the correct old password , update mysql.user set password=passworD (' New password ') where user='root' and host='localhost ';

5. Modify the normal user password

# mysql-u Test1-p-Set Password=pasSWORD (' aixocm '); For ' test1 ' @ ' localhost ' =password (' aixocm ');

6. Resolve the Forgotten root user password problem

#/etc/init.d/mysqld start--skip-grant-tables
# mysql-u Root-p   ---> This can be logged in without a password. Update mysql.user set Password=passworD (' New password ') where user= ' root ' and host= ' localhost ';

User Rights Management actions

1. Add Permissions

Grant ' username '@'Host'
// Create a new user and increase permissions Select , UPDATE-on *. *---> all tables for all databases ', with grant option; ' test5 ' @ ' localhost ' with GRANT option;

2. Remove Permissions

revoke Delete' test5 ' @ ' localhost ';
// Remove all permissions revoke all privileges,grant option from ' test5 ' @ ' localhost ';

3. View Permissions

SelectUser,host,select_priv,update_priv,delete_priv from Mysql.user where user='Test5';+-------+-----------+-------------+-------------+-------------+| User | Host | Select_priv | Update_priv | Delete_priv |+-------+-----------+-------------+-------------+-------------+| Test5 |    localhost |     Y |      Y | Y |+-------+-----------+-------------+-------------+-------------+ -Show grantsFor ' root ' @ ' localhost ' \g;***************************1. Row ***************************Grants for[Email protected]: GRANT all privileges on * * to' root ' @ ' localhost ' identified by PASSWORD ' *5dba547161e5d9c6508c3c6cf5d1b26940e3bc13 ' with GRANT option********** 2. Row ***************************grants for [e-mail protected]: GRANT PROXY on "@" to "root" @ ' localhost ' with GRANT option< /c0>

4. Refresh (load) permissions

Flush privileges;

Storage Engine related Operations
1. View the storage engines supported by the database

# show engines;# show Engines\g; Engine: Name support: Supported (YES/DEFAULT) Comment: Description transaction: Whether transaction XA is supported: whether the XA specification for Distributed transaction processing is supported Savapoint: whether the savepoint is supported 

2. View Storage Engine Details

# show engine InnoDB status\g;

3. View the default storage engine

' Storage_engine ';

4. Modify the default storage engine

# vim/etc/my.cnf (config file) default-storage-engine=xxx

MySQL Basic operations Summary