mysql-Common Commands

MySQL common command, record down, keep the spare

Get table structure

desctablecolumnsfromtable;

Change table name

renametableto 新表名;

Delete a field from a table

ALTER TABLE name Drop Col_name[,col_name .......]

altertabledrop weight;

Add fields to the table

altertableaddint(4default‘0‘;-- 在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0

Change Column type

ALTER TABLE name Modify COL_NAME type

altertablemodify--改变weight的类型：

Log in to MySQL

Mysql-uroot-h127.0.0.1-p33315-p

U: User Name
H: Server IP
Port where the P:mysql is located
P: Password (enter password directly after P,enter)

Authorized

GRANTALLPRIVILEGESONTO‘root‘@‘%‘IDENTIFIEDBY‘root‘WITHGRANTOPTION

Displays the value of the specified variable

LIKE‘%max_allowed_packet%‘;

Query SQL Execution frequency

Show [Session|global] Status
Session: (default) indicates the current connection
Global: Indicates the database has been started so far

eg.

globallike‘Com_%‘globallike‘Com_%‘;

Backup

‘路径‘

-L Single Lock
-F-Flush logs Regenerate the new log file.

Recovery

mysql -uroot -proot test -v -f > sql

-V View detailed information about the person
-F is an error in which you can skip

What storage engine is now available for MySQL

show engines;

What engine is used for a table (the storage engine that the table is currently using after the parameter engine in the display result)

createtable 表名;

To view the threads that the database is running

If you have root privileges, you can see all the threads. Otherwise, you can only see the logged-on user's own thread, usually only showing 100 bars if you want to see more than you can use the full decoration (show full processlist)

Parameters

param	value
Id	ID identification, which is useful when you want to kill a statement
Use	Current Connected user
Host	Shows which IP port this connection was issued from
Db	Database name
Command	Connection status, typically hibernation (sleep), query, connection (connect)
Time	Connection duration, units in seconds
State	Displays the status of the current SQL statement
Info	Show this SQL statement

Number of connections settings

After the MySQL database installation is complete, the default maximum number of connections is 100, general traffic slightly larger forum or website, this connection number is not enough, increase the default MySQL connection number method has two.
1. Go to MySQL installation directory to open MySQL config file my.ini or my.cnf find max_connections=100 modify to max_connections=1000 service restart MySQL
2. Set the new maximum number of connections to 200:mysql> set GLOBAL max_connections=200

mysql-Common Commands