Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the commonly used sentences in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mysqld regular MySQL server

Mysqld-opt optimizes the mysql server and provides some features to mine better features.

Mysqld-max is the same as mysqld, but can support newer, more experimental features (more unstable)

+ + install mysql

See the INSTALL-SOURCE file that comes with it

/ configure? prefix=/app/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 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 user name-p # connect to the local computer

$path/mysql-h remote host IP-u username-p# connect to the remote MYSQL server

+ + create / delete database or table

$mysqladmin-u root-p create xxx

Mysql > create database 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 table name

Mysql > drop table table name

+ + View the database and the tables under the database

Mysql > show databases

Mysql > show tables

Mysql > show table status

Mysql > desc table name; # View specific table structure information

Mysql > SHOW CREATE DATABASE db_name # shows the statement to create the db_ name library

Mysql > SHOW CREATE TABLE tbl_name # shows the statement that creates the tbl_ name table

+ + create a user

Mysql > grant select,insert,update,delete,alter on mydb.* to identified by "abc"

Mysql > grant all privileges on *. * to "identified by" abc "

Mysql > flush privileges

+ + user management

Mysql > update user set password=password ('11111') where user='test1'; # change test1 password to 111111

Mysql > DELETE FROM user WHERE User= "testuser" and Host= "localhost"; # Delete user account

Mysql > SHOW GRANTS FOR user1; # shows the grant statement that creates the user1 user

Backup and recovery of + + mysql Database

$mysqldump-uuser-ppassword-B DB_name [--tables table1-- tables table2] > exportfile.sql

$mysql-uroot-p xxx

< aaa.sql #导入表 $ mysqldump -u 用户名 -p 数据库名 表名>

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 adds 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 ('111111') where user='root'

Mysql > flush privileges

Then restart mysql and log in with a new password

+ + currently used database

Mysql > select database ()

= = database daily 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 can view table structure information

# TIMESTAMP (8) YYYYMMDD where (2, 4, 6, 8, 10, 12, 14) correspond to different time formats

Mysql > SHOW CREATE TABLE tbl_name # shows the statement that creates the tbl_ name table

+ + create an index

You can add index indexname (column name) to create an index when creating a table.

You can also 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] # Show existing indexes

Mysql > repair TABLE date QUICK; # automatically rebuild the index when the relevant variables of the index column 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... [DISTINCT keyword removes duplicate records]

Mysql > select DATE_FORMAT (NOW (),'% m _ AS TIME% d _ max% Y') as DATE, DATE_FORMAT (NOW (),'% HGV% mVR% 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 order / ASC positive order

* function count,AVG,SUM,MIN,MAX,LENGTH character length, LTRIM removes leading spaces, RTRIM removes trailing spaces, TRIM (str) removes leading trailing spaces, LETF/RIGHT (str,x) returns x characters to the left / right of string str, SUBSTRING (str,x,y) returns characters mysql > select BINARY 'ross' IN (' Chandler','Joey', 'Ross') from x position to position y in str; # BINARY strictly checks case

* 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; # grouping and counting times / average

+ + UNIX_TIMESTAMP (date)

Returns a Unix timestamp (seconds from '1970-01-01 00:00:00'GMT)

Mysql > select UNIX_TIMESTAMP ()

Mysql > select UNIX_TIMESTAMP ('1997-10-04 22 purge 23purl 00')

Mysql > select FROM_UNIXTIME (875996580); # calculate the date based on the timestamp

+ + control condition function

Mysql > select if (1100 for truth and truth, for false)

The # IF () function takes three arguments, the first is the judged expression, if the expression is true, the second argument is returned, if false, the third argument is returned.

Mysql > select CASE WHEN (2x2) = 4 THEN "OK" WHEN (2x2) 4 THEN 'NOT OK' END AS status

+ + system information function

Mysql > select DATABASE (), VERSION (), USER ()

Mysql > select BENCHMARK (9999999dLog (RAND () * PI () AS PERFORMANACE; # A tool for testing mysql computing performance

+ + replace the text "old" with "new" in the post_content field in the wp_ posts table

Mysql > update wp_posts set post_content=replace (post_content,'old','new')

+ + change the 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

Thus, a field userid of type int (11) is added to the table dbname.

+ + adjust column order

Mysql > alter table tablename CHANGE id id int (11) first

+ + modify data in the table

Insert [into] table_name [(column (s))] values (_ expression (s))

Example: mysql > insert into mydatabase values ('', 'mysql','','sqlserver','','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

Data update of + + table

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 in the table, it is recommended to use truncate table tablename instead of delete from tablename.

Run the SQL command from the + + SHELL prompt

$mysql-e "show slave statusG"

+ + scan repair of bad library

Cd / var/lib/mysql/xxx & & myisamchk playlist_block

+ + insert into a (x) values ('11a')

Appears: ata truncated for column'x' at row 1

Solution:

Find it in my.ini.

Sql-mode= "STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION"

Remove the STRICT_TRANS_TABLES, and then restart mysql to ok.

+ + copy the table

Mysql > create table target_table like source_table

+ + innodb supports transactions

New table: create TABLE table-name (field-definitions) TYPE=INNODB

Old watch: alter TABLE table-name TYPE=INNODB

Mysql > start transaction # marks the beginning of a transaction

Mysql > insert into... .. # data changes

Mysql > ROLLBACK or commit # rollback or commit

Mysql > SET AUTOCOMMIT=1; # set autocommit

Mysql > select @ @ autocommit; # to see whether it is currently automatically submitted

+ + table locking correlation

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; # does not need to specify the name of the locked table, MySQL will automatically unlock all tables

= some mysql optimization and management =

+ + Administrative commands

Mysql > show variables # View all variable values

? Maximum number of connections allowed by max_connections

# if you need to increase the max_connections, you can add set-variable = max_connections=32000 to the my.cnf, and you can determine whether it needs to be increased according to the threads_ connected value below.

Show status [like....]

? Current number of connection threads in the threads_connected 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//log-slow-queries.log

Log-queries-not-using-indexes

# mysqldumpslow-s c-t 20 host-slow.log # 20 sql statements visited the most

# mysqldumpslow-s r-t 20 host-slow.log # returns a maximum of 20 sql in the recordset

? others

Maximum number of connections used by max_connections=500 # SHOW Status like 'max_used_connection'

Wait_timeout=10 # terminates all connections that are idle for more than 10 seconds

Table_cache=64 # Total number of tables opened at any time

The maximum size of binary logs before the ax_binlog_size=512M # loop

Max_connect_errors = 100

Query_cache_size = 256m # query cache

# use SHOW STATUS LIKE 'qcache%'; to check 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, consider increasing.

Key_buffer = 16m

# show status like'% key_read%'; Key_reads represents the number of keyword requests that hit the disk

# A: how much should be set for Key Buffer? Q: MySQL only Cache indexes (* .MYI), so refer to the total size of all MYI files

Sort_buffer_size = 4m # buffer size that can be used for query sorting, 4m per connection

# show status like'% sort%'; if the sort_merge_passes is very large, it means larger

Sort_buffer_sizesort_buffer_size = 6m # buffer size that can be used when sorting queries, which is a unique value of 6m per connection

Read_buffer_size = 4m # buffer size that can be used by read query operations

Join_buffer_size = 8m # buffer size that can be used by federated query operations

Skip-locking # removes the external lock on the file system

Skip-name-resolve

Thread_concurrency = 8 # maximum number of concurrent threads, number of cpu * 2

Long_query_time = 10 # Slow_queries counter query time threshold

This is the end of the content of "what are the commonly used sentences in mysql"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.