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

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

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.