Mysql database



To view the installation path of MySQL:

1. select @@basedir as basePath from dual;
2. show variables like "%char%";
   - character_ sets_ This is the installation path.

View the data path of MySQL:

1. select @@datadir as dataPath from dual;
2. show variables Like '%datadir%';

Command line connection to database:

MySQL - H localhost - U user name (MySQL default user root) - P (this is the option to enter the password band)
PS: the command is MySQL - H localhost - U root - proot. The root after - P here is the password. Note here that there is no space between - P and password. If there is a space, the system will not treat the following string as a password

Switch current database: use database name;

View all databases: databases; schema_name from information_schema.schemataG

View all users: select user, host from mysql.user ;

View the running status of MySQL database: status;

To view the current database:

  1. select database();
  2. After the command show tables; view tables_ in_ Database name
  3. After the status; command, view the current database: This is followed by the current database.

View table structure: desc table_ name;

Query database connection: show full processlist;

Query database connection without connecting to database: mysqladmin – uroot – P – processlist

View the maximum number of connections: show status like% max_ used_ connections%’;

Current connections: show status like% threads_ connected%’;

Table lock: show status like% table_ lock%’;

Row locking: show status like ‘InnoDB_ row_ lock%’;

Query Cache: show status like% qcache%;

Query Cache: Show variables like% query_ cache%”;

View the query cache usage status value: show status like ‘qcache%’;

View MySQL binlog mode: Show variables like% binlog%;

Because the client did not close the connection correctly, the number of abandoned connections is: show status like ‘abandoned_ clients’;

View the maximum number of connections: Show variables like% max_ connections%’;

View timeout: Show variables like% timeout%;

Check whether the log is started: Show variables like ‘log_ %’;

Format output random query: add g before

Users and permissions:

View the current MySQL user permissions: Show grants;

View the permissions of a user: Show grants for user name @ host;

MySQL user creation: create user ‘user name’ @ ‘host’ identified by ‘password’;

Give MySQL user permission:

Grant {privileges} on *. * to 'user name' @'host ';
Privileges represents permissions, which permissions should be given to view MySQL documents by yourself
*. * represents all tables of all databases. If it is a specific database, for example: test*

Recommended Today Application of regular expression

1. Balanced group / recursive matching (?’ Group ‘), which is called the corresponding content of group, and counts it on the stack;(?’- Group ‘), and count the corresponding content named group out of the stack(?!) Zero width negative look ahead assertion. Since there is no suffix expression, attempts to match always failRegular example:,{0,1}”5″:\[[^\[\]]*(((?’Open’\[)[^\[\]]*)+((?’-Open’\])[^\[\]]*)+)*(?(Open)(?!))\],{0,1} Test […]