Common mysql statements _ MySQL

Mysql common statement summary bitsCN.com

Mysqld regular MySQL server
Mysqld-opt optimizes the mysql server and provides some functions to mine better functions.
Mysqld-max is the same as mysqld, but it supports Update and more experimental functions (more unstable)

++ Install mysql
See the built-in INSTALL-SOURCE file
$./Configure? Prefix =/apps/mysql-5.0.51a? With-charset = utf8? With-extra-charsets = utf8, gb2312, utf8

++ Start/close mysql
$ Path/mysqld_safe-user = mysql &
$/Mysqladmin-p shutdown

++ Modify the root password
$ Mysqladmin-u root-p password 'new password'

++ View Server Status
$ Path/mysqladmin version-p

++ Connect to the remote mysql server
$ Path/mysql-u username-p # connect to the local machine
$ Path/mysql-h remote host IP address-u username-p # connect to remote MYSQL server

++ Create/delete a database or table
$ Mysqladmin-u root-p create xxx
Mysql> create database name;
Mysql> create TABLE items (
Id INT (5) not null AUTO_INCREMENT primary key,
Symbol CHAR (4) not null,
Username CHAR (8 ),
INDEX sym (symbol), INDEX .....
UNIQUE (username)
) Type = innodb;
Mysql> drop database [if exists] database name
Mysql> create table name;
Mysql> drop table name;

++ View databases and tables in databases
Mysql> show databases;
Mysql> show tables;
Mysql> show table status;
Mysql> desc table name; # View table structure information
Mysql> show create database db_name # display the statement for creating the db_name DATABASE
Mysql> show create table tbl_name # Display statements for creating a tbl_name TABLE

++ Create a user
Mysql> grant select, insert, update, delete, alter on mydb. * to test2 @ localhost identified by "abc ";
Mysql> grant all privileges on *. * to test1 @ "%" identified by "abc ";
Mysql> flush privileges;

++ User management
Mysql> update user set password = password ('000000') where user = 'test1'; # change the password of test1 to 11111
Mysql> delete from user WHERE User = "testuser" and Host = "localhost"; # DELETE a user account
Mysql> show grants for user1; # displays the grant statement FOR creating a user1 user

++ Mysql database backup and recovery
$ Mysqldump-uuser-ppassword-B DB_name [-- tables table1 -- tables table2]> exportfile. SQL
$ Mysql-uroot-p xxx <aaa. SQL # import table
$ Mysqldump-u username-p database name table name> exported file name # export a separate table

++ Export a database structure
$ Mysqldump-u wcnc-p-d? Add-drop-table smgp_apps_wcnc> wcnc_db. SQL
-D no data? Add-drop-table add a drop table before each create statement

++ Forget the mysql password
Stop all mysql service processes first
$ Mysqld_safe? Skip-grant-tables & mysql
Mysql> use mysql;
Mysql> update user set password = password ('000000') where user = 'root ';
Mysql> flush privileges;
Restart mysql and log on with a new password.

++ Currently used database
Mysql> select database ();

=== Routine database operation and maintenance ===
++ Create a table
Mysql> create table table_name
(Column_name datatype {identity | null | not null}, f_time TIMESTAMP (8 ),...) ENGINE = MyISAM AUTO_INCREMENT = 3811 default charset = utf8;
Example: create table guest (name varchar (10), sex varchar (2), age int (3), career varchar (10 ));
# Desc guest: View table structure information
# TIMESTAMP (8) YYYYMMDD where (2/4/6/8/10/12/14) corresponds to different time formats
Mysql> show create table tbl_name # Display statements for creating a tbl_name TABLE

++ Create an index
You can add index indexname (column name) to create an index when creating a table,
You can also manually generate create index index_name on table_name (col_name [(length)],… )
Mysql> create index number ON guest (number (10 ));
Mysql> show index from tbl_name [FROM db_name] # display existing indexes
Mysql> repair TABLE date QUICK; # The index is automatically rebuilt after the index column-related variables change.

++ Query and common functions
Mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
Mysql> select college, region, seed from tournament order by region, seed;
Mysql> select col_name from tbl_name WHERE col_name> 0;
Mysql> select DISTINCT ...... [The DISTINCT keyword can remove duplicate records]
Mysql> select DATE_FORMAT (NOW (), '% m/% d/% Y') as DATE, DATE_FORMAT (NOW (),' % H: % m: % S ') as time;
Mysql> select CURDATE (), CURTIME (), YEAR (NOW (), MONTH (NOW (), DAYOFMONTH (NOW (), HOUR (NOW ()), MINUTE (NOW ());
Mysql> select UNIX_TIMESTAMP (), UNIX_TIMESTAMP (20080808), FROM_UNIXTIME (UNIX_TIMESTAMP (); mysql> select PASSWORD ("secret"), MD5 ("secret"); # encryption PASSWORD
Mysql> select count (*) from tab_name order by id [DESC | ASC]; # DESC reverse/ASC forward

* Functions include count, AVG, SUM, MIN, MAX, and LENGTH. LTRIM removes the starting short positions, RTRIM removes trailing spaces, and TRIM (str) removes trailing spaces, LETF/RIGHT (str, x) returns x characters on the left/RIGHT side of the str string, SUBSTRING (str, x, y) returns the character mysql> select BINARY 'ros' IN ('Chandler', 'Joey ', 'Ross') from position x IN str to position y. # BINARY strictly checks the case sensitivity.

* Comparison operators IN, BETWEEN, is null, is not null, LIKE, REGEXP/RLIKE
Mysql> select count (*), AVG (number_xx), Host, user from mysql. user GROUP by user [DESC | ASC] HAVING user = root; # GROUP and count/average

++ UNIX_TIMESTAMP (date)
Returns a Unix timestamp (the number of seconds since '2017-01-01 00:00:00 'GMT)
Mysql> select UNIX_TIMESTAMP ();
Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ′);
Mysql> select FROM_UNIXTIME (875996580); # calculate the date based on the time stamp

++ Control condition functions
Mysql> select if (1 <100, 3), IF (55>, 'true', 'false ');
# The IF () function has three parameters. The first one is the expression to be judged. IF the expression is true, the second parameter is returned. IF the expression is false, the third parameter is returned.
Mysql> select case when (2 + 2) = 4 THEN "OK" WHEN (2 + 2) <> 4 THEN 'not OK 'END AS status;

++ System information functions
Mysql> select DATABASE (), VERSION (), USER ();
Mysql> select BENCHMARK (9999999, LOG (RAND () * PI () AS ace; # A mysql computing performance testing tool

++ Replace "old" with "new" in the post_content field of the wp_posts table"
Mysql> update wp_posts set post_content = replace (post_content, 'old', 'new ')

++ Alter table structure
Mysql> alter table table_name alter_spec [, alter_spec...]
Example: alter table dbname add column userid int (11) not null primary key auto_increment;
In this way, a field userid is added to the dbname table and the type is int (11 ).

++ Adjust the column sequence
Mysql> alter table tablename CHANGE id int (11) first;

++ Modify table data
Insert [into] table_name [(column (s)] values (expression (s ))
For example, mysql> insert into mydatabase values ('php', 'mysql', 'asp ', 'sqlserver', 'JSP ', 'Oracle ');
Mysql> create table user select host, user from mysql. user where 1 = 0;
Mysql> insert into user (host, user) select host, user from mysql. user;

++ Change the table name
Command: rename table original table name to new table name;

++ Table data update
Mysql> update table01 set field04 = 19991022 [, field05 = 062218] where field01 = 1;

++ Delete data
Mysql> delete from table01 where field01 = 3;
# If you want to clear all records of a table, we recommend that you use truncate table tablename instead of delete from tablename.

++ Run the SQL command at the SHELL prompt
$ Mysql-e "show slave statusG"

++ Bad database scan and repair
Cd/var/lib/mysql/xxx & myisamchk playlist_block

++ Insert into a (x) values ('11a ')
Appearance: ata truncated for column 'X' at row 1
Solution:
In my. ini, find
SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_Create_USER, NO_ENGINE_SUBSTITUTION"
Remove STRICT_TRANS_TABLES and restart mysql.

++ Copy a table
Mysql> create table target_table like source_table

++ Innodb supports transactions
New TABLE: create table TABLE-name (field-definitions) TYPE = INNODB;
Old TABLE: alter table TABLE-name TYPE = INNODB;
Mysql> start transaction # mark the start of a transaction
Mysql> insert ..... # Data change
Mysql> ROLLBACK or commit # roll back or submit
Mysql> set autocommit = 1; # SET automatic submission
Mysql> select @ autocommit; # check whether the current job is automatically submitted

++ Table locking
Mysql> lock table users READ; # READ-only LOCK on the user TABLE
Mysql> lock tables user READ, pfolios WRITE # multi-table LOCK control
Mysql> unlock tables; # No need to specify the name of the locked table. MySQL will automatically UNLOCK all TABLES.

===== Some mysql optimization and management =====
++ Management commands
Mysql> show variables # View all variable values
? Maximum number of connections allowed by the max_connections database,
# To increase max_connections, you can add set-variable = max_connections = 32000 to my. cnf. you can determine whether to increase the value with the following threads_connected value.

Show status [like...];
? Threads_connected current number of connection threads of the database
# Flush status can reset some counters

Show processlist;
Kill id;

++ My. cnf configuration
? Enable Slow Query Log
Long_query_time = 1
Log-slow-queries =/var/log/mysql/log-slow-queries.log
Log-queries-not-using-indexes

# Mysqldumpslow-s c-t 20 host-slow.log # The 20 most accessed SQL statements
# Mysqldumpslow-s r-t 20 host-slow.log # returns up to 20 SQL statements of the record set

? Others
Max_connections = 500 # SHOW Status like 'max _ used_connection ';
Wait_timeout = 10 # terminate all connections whose idle time exceeds 10 seconds
Table_cache = 64 # total number of opened tables at any time
Ax_binlog_size = 512 M # maximum size of binary logs before looping
Max_connect_errorrs = 100

Query_cache_size = 256 M # Query cache
# Use show status like 'qcache % 'to view the hit rate
# Flush status reset counter, flush query cache clear CACHE

Thread_cache = 40
# Thread usage, show status like 'Threads _ created % '; if the value increases rapidly, increase the value.

Key_buffer = 16 M
# Show status like '% key_read %'; Key_reads indicates the number of keyword requests that hit the disk
# A: How many Key Buffer settings are required? Q: MySQL only caches indexes (*. MYI). Therefore, refer to the total size of all MYI files.

Sort_buffer_size = 4 M # Size of the buffer zone that can be used for sorting, exclusive to 4 M per connection
# Show status like '% sort %'; if sort_merge_passes is large, it indicates increasing

Sort_buffer_sizesort_buffer_size = 6 M # The buffer size that can be used for sorting. this is the exclusive value of 6 M for each connection.
Read_buffer_size = 4 M # buffer size available for read query operations
Join_buffer_size = 8 M # buffer size used by the joint query operation
Skip-locking # cancel the external lock of the file system
Skip-name-resolve
Thread_concurrency = 8 # maximum number of concurrent threads, cpu x 2
Long_query_time = 10 # time threshold value of the Slow_queries counter

BitsCN.com