Mysql database operation command

MySQL is one of the most commonly used databases. Summarize some common commands for daily use.

I. about the operation of the library

1. View the database

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | ty | | womenscms3 | +-+

2. Create a database and set the character encoding to utf8.

Mysql > create database test001 default character set utf8;Query OK, 1 row affected (0.01 sec)

3. Delete the library (operate cautiously)

Mysql > drop database womenscms3;Query OK, 112 rows affected (3.94 sec)

4. User authorization

Mysql > grant select,insert,update,delete on *. * to 'root'@'%';Query OK, 0 rows affected (0.01sec)

5. View user permissions

Mysql > show grants for root +-+ | Grants for root@% | +-- + | GRANT ALL PRIVILEGES ON *. * TO 'root'@' %'| +-- + 1 row in set (0.00 sec) mysql > select * from mysql.user where user='root'\ G * * 1. Row * * Host: localhost User: root Select_priv: y Insert_priv: y Update_priv: y Delete_priv: y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create _ tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: YCreate_ view_priv: Y Show_view_priv: YCreate_ routine_priv: Y Alter_routine_priv: YCreate_ user_priv: Y Event_priv: Y Trigger_priv: YCreate_tablespace_priv: Y Ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: * F846B31F10DD4389C384272E70B9BBA3AD9E1F94 password_expired: n password_last_changed: 2019-06-24 18:10:54 password_lifetime: NULL account_locked: n

Common command

Create databasename; create database use databasename; select database drop databasename; delete database directly, do not remind show tables; to display table describe tablename; table detailed description select add distinct to remove duplicate fields to display the current mysql version and current date select version (), current_date; modify the password of root in mysql: shell > mysql-u root-pmysql > update user set password=password ("root123") where user='root';mysql > flush privileges refresh permission mysql > use dbname Open database mysql > show databases; display all databases mysql > show tables; display all tables in database mysql mysql > desc user; show column information of user table in mysql database) grant to create a supermanaged account that can connect to the server from anywhere, you must assign a password mysql > grant all privileges on *. * to 'user_name'@'localhost' identified by' password' Format: grant select on database. * to user name @ login host identified by "password" delete authorization: mysql > revoke all privileges on *. * from root@ "%"; mysql > delete from user where user= "root" and host= "%"; mysql > flush privileges; rename table: mysql > alter table T1 rename T2; backup: mysqldump-hhostname-uusername-ppassword databasename > backup.sql; restore: mysql-hhostname-uusername-ppassword databasename < backup.sql

Appendix

Mysql file distribution

/ etc/rc.d/init.d/mysqld:MySQL server startup script

/ usr/bin/mysqlshow: displays database, table, and column information

/ usr/libexec/mysqld: server's process program file

/ usr/libexec/mysqlmanager: instance manager file

/ usr/share/doc/: directory where the description files are stored

/ usr/share/man/man 1max.: catalogue for storing manual pages

/ var/lib/mysql/: server database file storage directory

Log file of / var/log/mysqld.log:MySQL server