MySQL must have basic command

1.1 Landing database.

Mysql-uroot-poldboy123-s/data/3306/mysql.sock

1.2 View the database version and what the current logged-on user is.

Select version (); Select User ();

1.3 Create a database Oldboy for the GBK character set and view the complete statement of the built library.

Create DATABASE Oldboy Character Set GBK collate gbk_chinese_ci;show CREATE DATABASE Oldboy\g

1.4 Create user Oldboy so that it can manage database Oldboy.

Grant all on oldboy.* to ' oldboy ' @ ' localhost ' identified by ' oldboy123 ';

1.5 See what permissions the user Oldboy has created.

Show grants for [email protected]\g

1.6 See which users are in the current database.

Select User,host from Mysql.user;

1.7 Create Admin Account admin

Grant all on * * to ' admin ' @ ' localhost ' identified by ' admin123 ' with GRANT option;

1.8 Entering the Oldboy database

Use Oldboy;

1.9 Create test table: InnoDB engine, Character set is GBK, field ID int (4) and name varchar (+), View the build table structure and SQL statements.

CREATE TABLE test (ID int (4), name varchar ()) Engine=innodb DEFAULT Charset=gbk;desc test; #<== equivalent to command: Show columns from test;show CREATE TABLE Test\g

1.10 Inserting a piece of data 1,oldboy

INSERT into test values (' 1 ', ' Oldboy ');

1.11 Bulk INSERT Data 2, old boy, 3,etiantian. The requirement of Chinese cannot be garbled.

INSERT into test values (' 2 ', ' old boy '), (' 3 ', ' Etiantian ');

1.12 Queries insert all records, query name record for the Oldboy. A record with a query ID greater than 1.

SELECT * FROM test where name= ' Oldboy '; select * from Test where id>1;

1.13 Change the name of the data ID equal to 1 oldboy to Oldgirl.

Update test set name= ' Oldgirl ' where id=1;

1.14 Insert the Age field before the field name, type tinyint (2).

ALTER TABLE test add name tinyint (2) after ID;

1.15 Back up the Oldboy library and MySQL library.

Mysqldump-uroot-poldboy123-s/data/3306/mysql.sock--events-b oldboy mysql >/opt/bak_$ (date +%F). sql

Egrep-v "#|^$|--|\/"/opt/bak_2017-06-06-13 36 minutes and 37 seconds. sql

1.16 Delete all data from the table and view it.

TRUNCATE TABLE test; #<== physical deletion, disposable empty, can not rollbackdelete from test; #<== Tombstone, one row of the deletion, relatively slow, you can rollback

1.17 Delete the table test and Oldboy database and view

drop table Test;drop database Oldboy;

The 1.18 Linux command line recovers the data deleted above.

Mysql-uroot-poldboy123-s/data/3306/mysql.sock </opt/bak_2017-06-07-22 13 minutes and 20 seconds. sql

1.19 Modify the GBK character set to UTF8 (optional, note that this problem has a trap).

1. Pilot out the data in the table

Mysqldump-uroot-poldboy123-s/data/3306/mysql.sock-b oldboy >/opt/test.sqlegrep-v "^$|--|\/"/opt/test.sql #<= = You can see this line: Engine=myisam DEFAULT CHARSET=GBK;

2. Modifying the character Set

Sed-i ' s#charset=gbk#charset=utf8#g '/opt/test.sqlegrep-v "^$|--|\/"/opt/test.sql #<== validation:) ENGINE=MyISAM DEFAULT Charset=utf8;

3. Recovering data

Add a set names UTF8 to the SQL file and restore mysql-uroot-poldboy123-s/data/3306/mysql.sock Oldboy </opt/test.sql

1.20 MySQL password lost, how to find the actual combat?

[[email protected] ~]# netstat -tunlp|grep 3306 #<== first to see if the service is working tcp         0      0 0.0.0.0:3306             0.0.0.0:*           LISTEN      62358/mysqld[[email protected] ~]#  Kill 62358 #<==kill off the pid[[email protected] ~]# netstat -tunlp|grep  of the process 3306 #<==mysql process closed [[email protected] ~]# mysqld_safe --help #<== exploit Mysqld_ The safe command specifies the configuration file, skipping the authorization form to break the password [[email protected] ~]# mysqld_safe --defaults-file=/data/3306/my.cnf  --skip-grant-tables 2>&1 >/dev/null &[[email protected] ~]#  mysql -s /data/3306/mysql.sock #<== No password login into multiple instance 3306mysql> select user,host, Password from mysql.user where user= ' root '  and host= ' localhost ', +------+-----------+---------------------- ---------------------+| user | host      | password                                    |+------+----------- +-------------------------------------------+| root | localhost | * fe28814b4a8b3309dac6ed7d3237aded6da1e515 |+------+-----------+------------------------------------------- +1 row in set  (0.00 sec) #<== First look at the MySQL library, the field contents of the user table mysql> update  Mysql.user set password=password ("oldboy123")  where user= ' root '  and host= ' localhost ';  #<== use the update command to update the password of the [email protected] user mysql> flush privileges; # <== Remember to refresh the authorization form, otherwise it will not take effect immediately QUERY&NBSP;OK,&NBSP;0&NBsp;rows affected  (0.00 sec) [[email protected] ~]# sed -i  ' s#mysql_pwd= "oldboy456" #mysql_pwd = "oldboy123" #g '  /data/3306/mysql[[email protected] ~]# grep  mysql_pwd= /data/3306/mysql #<== Modify the password of the startup script to use the command to stop the service mysql_pwd= "oldboy123" [[email protected]  ~]# /data/3306/mysql stop #<== first stop the service, because there are parameters to skip the authorization table in Stoping mysql ... [1]+  done   mysqld_safe --defaults-file=/data/3306/my.cnf -- skip-grant-tables 2>&1 > /dev/null[[email protected] ~]# /data/3306/ mysql start #<== Start Starting mysql ... [[email protected] ~]# ss -tunlp|grep 3306 #<== listens for normal tcp     LISTEN     0      600           *:3306        *:*     &nBsp;  users: (("mysqld", 66695,12)) [[email protected] ~]# mysql -uroot - poldboy123 -s /data/3306/mysql.sock #<== Successful Login

1.21 MySQL in the Chinese data garbled principle and how to prevent garbled? (optional).

Ensure that the following character sets are consistent

mysql> show variables like  ' character_set% ';                +--------------------------+--------------+| variable_ name            | value         |+--------------------------+--------------+| character_set_client      | utf8         |#<== Client Character Set |  character_set_connection | utf8         |#<== Client connection character set, configuration file designation or build Library table designation | character_set_database   | utf8          |#<== the character set of the database | character_set_filesystem | binary        |#<== file system Character Set | character_set_results    | utf8          |#<== the client returns the result character set | character_set_server     | utf8          |#<== server character set, configuration file designation or build Library table designation | character_set_system      | utf8         |#<==linux system's Character Set |  character_sets_dir       | /application/mysql-5.5.49/share/ charsets/ |+--------------------------+-------------------------------------------+8 rows in  set  (0.01&NBSP;SEC)

1.22 set the ID column as the primary key and create a normal index on the name field.

ALTER TABLE test add primary key (ID); ALTER TABLE test add index index_name (name);

1.23 Insert the Phone number field (Shouji) after the field name, type char (11).

ALTER TABLE test add Shouji char (11);

1.24 Insert 2 records on all fields (self-setting data)

INSERT INTO Test val-ues (' 4 ', ' + ', ' Chen ', ' 15298914487 '), (' 5 ', ' + ', ' he ', ' 15298913929 ');

1.25 Create a normal index on the phone field for the first 8 characters.

ALTER TABLE test Add index Index_shouji (Shouji (8));

1.26 View information such as index and index type created.

Desc test;show CREATE TABLE Test\gshow index from TEST\G

1.27 Delete the index of the Name,shouji column.

ALTER TABLE test DROP INDEX index_name;alter table test Drop index Index_shouji;

1.28 A federated index is formed on the first 6 characters of the Name column and the first 8 characters of the phone column.

ALTER TABLE test DROP INDEX Index_shouji;

1.29 Check the phone number starts with 152, the record with the name Chen (this record is inserted in advance).

SELECT * FROM test where name= ' Chen ' and Shouji like ' 152% ';

1.30 Query the execution plan of the above statements (whether to use federated indexes, etc.).

Explain select * FROM Test where name= ' Chen ' and Shouji like ' 152% ' \g

1.31 change the engine of the test table to MyISAM.

ALTER TABLE Test Engine=myisam;

This article is from the "Chen was 007" blog, please be sure to keep this source http://chenfage.blog.51cto.com/8804946/1933390

MySQL must have basic command