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

The following to understand the common methods of operating MySQL, I believe you will benefit a lot after reading, the text in the essence is not much, hope to operate the common methods of MySQL this short content is what you want.

MySQL common operations (1) change mysql database root password # set password for root users of mysql mysqladmin-uroot password' yourpassword'# login to local mysqlmysql-uroot-p (2) connect to remote database # remote_host_ip: remote or local LAN database server ip#-P: remote or local LAN database server mysql port mysql-uroot-p-hremote_host_ip-Pport (3) Mysql common operations (a) query the current library show databases (B) query the table of a library Use databasename;show tables; (c) query all fields of a table desc table_name;# query table definition show create table_name\ G; (d) see which user is currently select user (); (e) view the database currently in use select database (); (f) create a database create database db1; (g) create a new table use db1;# field name needs to be enclosed in backquotes create table tablename (`id`int (4), `name` char (40)) (h) View the current database version select version (); (I) view the current status of mysql # see show_status.txt document show status; (j) View mysql parameters # see variables.txt document show variables; (k) modify mysql parameter # symbol% similar to show variables like 'max_connect%';# such as modify max_connect_errorsset global max_connect_errors = 1000; (l) view the queue show processlist of the current server (M) create an ordinary user and authorize # authorize the local user # all: indicates all permissions (such as read, write, query, delete, etc.) # *. *: the former * represents the database name, and the latter * represents the name of the table in the database # *. *: represents all the data tables in all databases in the current mysql database # identified by: followed by the password grant all on *. * to user1 identified by '123123' # authorize other hosts on the network # separate grant all on *. * to 'user2'@'10.0.0.1.2' identified by' 111222 hosts and # hostnames with% instead of all hosts grant all on db1.* to 'user3'@'%' identified by' aaabbb'; (4) commonly used SQL statement (a) query statement select count (*) from mysql.user;select * from mysql.db;select db from mysql.db Select db,user from mysql.db;select * from mysql.db where host like '10.0.1% abc'; (b) insert a row insert into db1.t1 values (1,' abc'); (c) change a row update db1.t1 set name='aaa' where id=1; (d) empty a data table truncate table db1.t1; (e) delete table drop table db1.t1; (f) delete database drop database db1 (5) mysql data backup and recovery (a) mysql backup mysqldump-uroot-paired password' mysql > / tmp/mysql.sql; (b) restore mysql-uroot-paired password' mysql < / tmp/mysql.sql

After reading this article on how to operate MySQL, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.