Linux运维必会MySQL 30道基础命令

lsyf8

1.1登录数据库
　　mysql-uroot -poldboy123 -S/data/3306/mysql.sock
1.2查看数据库版本及当前登录用户是什么？
　　mysql> select version();
　　+-----------+
　　| version() |
　　+-----------+
　　| 5.6.36   |
　　+-----------+
　　mysql>select user();
　　+----------------+
　　| user()        |
　　+----------------+
　　| root@localhost |
　　+----------------+
1.3创建GBK字符集的数据库oldboy，并查看已建库的完整语句
　　mysql> create database oldboy2 character set gbk collate gbk_chinese_ci;
　　Query OK, 1 row affected (0.06 sec)
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| oldboy             |
　　| oldboy2            |
　　| performance_schema |
　　| world              |
　　+--------------------+
　　mysql> show create database oldboy;
　　+----------+-----------------------------------------------------------------+
　　| Database | Create Database                                                 |
　　+----------+-----------------------------------------------------------------+
　　| oldboy   |CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
　　+----------+-----------------------------------------------------------------+
1.4创建用户oldboy，使之可以管理数据库oldboy
　　grant all on oldboy2.* to oldboy2@'localhost'identified by '123'
1.5查看创建的用户oldboy拥有哪些权利
　　mysql>show grants for oldboy2@'localhost';
　　+----------------------------------------------------------------------------------------------------------------+
　　| Grants for oldboy2@localhost                                                                                  |
　　+----------------------------------------------------------------------------------------------------------------+
　　| GRANT USAGE ON *.* TO 'oldboy2'@'localhost'IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
　　| GRANT ALL PRIVILEGES ON `oldboy2`.* TO'oldboy2'@'localhost'                                                  |
　　+----------------------------------------------------------------------------------------------------------------+
1.6查看当前数据库里有哪些用户
　　mysql>select user,host from mysql.user;
　　+---------+------------+
　　| user    |host       |
　　+---------+------------+
　　| oldboy1 | 10.0.0.%   |
　　| root    |127.0.0.1  |
　　| oldboy  |172.16.1.% |
　　| oldboy2 | localhost  |
　　| root    |localhost  |
　　+---------+------------+
1.7进入oldboy数据库
　　mysql> use oldboy2
1.8创建一innodb引擎字符集为GBK表test，字段为id和namevarchar(16)，查看建表结构及语句
1.8.1创建
　　mysql> use oldboy2
　　Database changed
　　mysql> create table test(

　　->>　　-> name varchar(16)
　　-> ) engine=innodb default
　　Query OK, 0 rows affected (0.09 sec)
　　mysql> show tables;
　　+-------------------+
　　| Tables_in_oldboy2 |
　　+-------------------+
　　| test             |
　　+-------------------+
1.8.2查看表结构
1.8.2.1 方法一：
　　mysql> desc test;
　　+-------+-------------+------+-----+---------+-------+
　　| Field | Type        | Null | Key | Default | Extra |
　　+-------+-------------+------+-----+---------+-------+

　　|>　　| name  |varchar(16) | YES  |     | NULL   |       |
　　+-------+-------------+------+-----+---------+-------+
1.8.2.2 方法二：
　　mysql>show columns from test;
　　+-------+-------------+------+-----+---------+-------+
　　| Field | Type        | Null | Key | Default | Extra |
　　+-------+-------------+------+-----+---------+-------+

　　|>　　| name  |varchar(16) | YES  |     | NULL   |       |
　　+-------+-------------+------+-----+---------+-------+
1.8.3查看表结构SQL语句
　　mysql>show create table test;
　　+-------+-------------------------------------------------------------------------------------------------------------------------+
　　| Table | Create Table                                                                                                           |
　　+-------+-------------------------------------------------------------------------------------------------------------------------+
　　| test  |CREATE TABLE `test` (
　　`id`int(4) DEFAULT NULL,
　　`name`varchar(16) DEFAULT NULL
　　) ENGINE=InnoDB DEFAULT CHARSET=gbk |
　　+-------+-------------------------------------------------------------------------------------------------------------------------+
1.9插入一条数据1,oldboy
　　mysql> insert into test values(1,'oldboy');
　　mysql> select * from test;
　　+------+--------+

　　|>　　+------+--------+
　　|    1 |oldboy |
　　+------+--------+
1.10批量插入数据 2，老男孩，3，etiantian.要求中文不能乱码
　　mysql> insert into test values(2,'老男孩'),(3,'etiantian');
　　mysql> select * from test ;
　　+------+-----------+

　　|>　　+------+-----------+
　　|    1 |oldboy    |
　　|    2 | 老男孩    |
　　|    3 |etiantian |
　　+------+-----------+
1.11查询插入的所有记录，查询名字为oldboy的记录。查询id大于1的记录
1.11.1查看插入的所有记录
　　mysql>select * from test
　　-> ;
　　+------+-----------+

　　|>　　+------+-----------+
　　|    1 |oldboy    |
　　|    2 | 老男孩    |
　　|    3 |etiantian |
　　+------+-----------+
1.11.2查询名字为oldboy的记录
　　mysql> select * from test where name='oldboy';
　　+------+--------+

　　|>　　+------+--------+
　　|    1 |oldboy |
　　+------+--------+
1.11.3查看id大于1的记录

　　mysql>select * from test where>　　+------+-----------+

　　|>　　+------+-----------+
　　|    2 | 老男孩    |
　　|    3 |etiantian |
　　+------+-----------+
1.12把数据id等于1的名字oldboy更改为oldbgirl

　　updatetest set  name='oldgirl' where>　　mysql>select * from test;
　　+------+-----------+

　　|>　　+------+-----------+
　　|    1 |oldgirl   |
　　|    2 | 老男孩    |
　　|    3 |etiantian |
　　+------+-----------+
1.13在字段name前插入age字段，类型tinyint(2)

　　mysql>alter table test add age tinyint(2)after>　　mysql> desc test;
　　+-------+-------------+------+-----+---------+-------+
　　| Field | Type        | Null | Key | Default | Extra |
　　+-------+-------------+------+-----+---------+-------+

　　|>　　| age   |tinyint(2)  | YES  |     |NULL    |       |
　　| name  |varchar(16) | YES  |     | NULL   |       |
　　+-------+-------------+------+-----+---------+-------+
1.14备份oldboy库及Mysql库
　　mysqldump –B –compact oldboy > /opt/bak.sql
1.15删除表中的所有数据，并查看
1.15.1delete是逻辑删除表中的数据，一列一列的删除表中的数据，速度比较慢
　　delete from rest;
1.15.2truncate是物理删除表中的数据，一次性全部都给清空表中数据，速度很快
　　truncate table test;
1.16删除表test和oldboy数据库并查看
1.16.1删除表
　　drop table test;
1.16.2删除数据库oldboy
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| oldboy             |
　　| performance_schema |
　　+--------------------+
　　4 rows in set (0.00 sec)
　　mysql> drop database oldboy;
　　mysql> show databases;
　　+--------------------+
　　| Database          |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| performance_schema |
　　+--------------------+
1.17linux命令行恢复以上删除数据
　　mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show variables like'character_set%';
　　+--------------------------+-------------------------------------------+
　　| Variable_name            | Value                                     |
　　+--------------------------+-------------------------------------------+
　　| character_set_client     | utf8                                      |
　　| character_set_connection | utf8                                      |
　　| character_set_database   | utf8                                      |
　　| character_set_filesystem | binary                                    |
　　| character_set_results    | utf8                                      |
　　| character_set_server     | utf8                                      |
　　| character_set_system     | utf8                                      |
　　| character_sets_dir       |/application/mysql-5.6.36/share/charsets/ |
　　+--------------------------+-------------------------------------------+
1.18.2设置字符集
　　mysql> set global character_set_database =utf8;
1.19Mysql密码丢了，如何找回实战
1.20Mysql内中文数据乱码的原理及如何防止乱码
1.20.1数据库字符集
　　mysql> show variables like 'character_set%';
　　+--------------------------+-------------------------------------------+
　　| Variable_name            | Value                                     |
　　+--------------------------+-------------------------------------------+
　　| character_set_client     | utf8                                      |
　　| character_set_connection | utf8                                      |
　　| character_set_database   | utf8                                      |
　　| character_set_filesystem | binary                                    |
　　| character_set_results    | utf8                                      |
　　| character_set_server     | utf8                                      |
　　| character_set_system     | utf8                                      |
　　| character_sets_dir       |/application/mysql-5.6.36/share/charsets/ |
　　+--------------------------+-------------------------------------------+
1.20.2系统字符配置文件
　　[root@mysql data]# cat /etc/sysconfig/i18n
　　LANG="en_US.UTF-8"
　　SYSFONT="latarcyrheb-sun16"
1.21 在把id列设置为主键，在name字段上创建普通索引
　　mysql> desc oldboy.test;
　　+-------+-------------+------+-----+---------+-------+
　　| Field | Type        | Null | Key | Default | Extra |
　　+-------+-------------+------+-----+---------+-------+

　　|>　　| name  |varchar(16) | YES  |     | NULL   |       |
　　| age   |int(4)      | YES  |     |NULL    |       |
　　+-------+-------------+------+-----+---------+-------+
　　alter table oldboy.test add primarykey(id);
1.21.1在name字段上创建普通索引
　　方法一：alter table oldboy,test addindexindex_name(name)
　　方法二：create index index_name onoldboy.test(name);
1.22在字段name后插入手机号字段（shouji）,类型char(11)

　　mysql>>　　mysql> desc test;
　　+--------+-------------+------+-----+---------+-------+
　　| Field  |Type        | Null | Key | Default |Extra |
　　+--------+-------------+------+-----+---------+-------+

　　|>　　| name   |varchar(16) | YES  |     | NULL   |       |
　　| shou   |char(11)    | YES  |     |NULL    |       |
　　| age    |int(4)      | YES  |     |NULL    |       |
　　| shouji | char(11)    | YES |     | NULL    |      |
　　+--------+-------------+------+-----+---------+-------+
1.23所有字段上插入两条记录（自行设定数据）
　　mysql> insert into test values(6,'mysql','mei','21','1794225527'),(7,'mysql1','mein','21','15095,'15095445967');
　　mysql> select * from test;
　　+------+---------+------+------+-------------+

　　|>　　+------+---------+------+------+-------------+
　　|    1 |oldboy  | NULL |   21 | NULL        |
　　|    3 |oldboy3 | NULL |   21 | NULL        |
　　|    4 |oldboy4 | NULL |   21 | NULL        |
　　|    5 | 小明    | NULL |   21 | NULL        |
　　|    6 |mysql   | mei  |   21| 1794225527  |
　　|    7 |mysql1  | mein |   21 | 15095445967 |
　　+------+---------+------+------+-------------+
1.24在手机字段上对前八个字符创建普通索引

　　mysql>>　　mysql> desc test;
　　+--------+-------------+------+-----+---------+-------+
　　| Field  |Type        | Null | Key | Default |Extra |
　　+--------+-------------+------+-----+---------+-------+

　　|>　　| name   |varchar(16) | YES  |     | NULL   |       |
　　| shou   |char(11)    | YES  |     |NULL    |       |
　　| age    |int(4)      | YES |     | NULL    |      |
　　| shouji | char(11)    | YES | MUL | NULL    |       |
　　+--------+-------------+------+-----+---------+-------+
1.25查看创建的索引及索引类型等信息
　　mysql>mysql> show index from test\G
　　*************************** 1. row ***************************
　　Table: test
　　Non_unique: 1
　　Key_name: name_idx
　　Seq_in_index: 1

　　Column_name:>　　Collation: A
　　Cardinality: 6
　　Sub_part: NULL
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　*************************** 2. row***************************
　　Table: test
　　Non_unique: 1
　　Key_name: index_shouji
　　Seq_in_index: 1
　　Column_name: shouji
　　Collation: A
　　Cardinality: 6
　　Sub_part: 8
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　2 rows in set (0.00 sec
1.26删除name，shouji列的索引

　　mysql>>　　mysql> show index from test\G
　　*************************** 1. row***************************
　　Table: test
　　Non_unique: 1
　　Key_name: name_idx
　　Seq_in_index: 1

　　Column_name:>　　Collation: A
　　Cardinality: 6
　　Sub_part: NULL
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　1 row in set (0.00 sec)
1.27对name列的前6个字符以及手机列的前8个字符组建联合索引
　　alter table test add indexindex_name_shouji(name(6),shouji(8));
　　mysql> show index from test\G
　　*************************** 1. row***************************
　　Table: test
　　Non_unique: 1
　　Key_name: name_idx
　　Seq_in_index: 1

　　Column_name:>　　Collation: A
　　Cardinality: 6
　　Sub_part: NULL
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　*************************** 2. row***************************
　　Table: test
　　Non_unique: 1
　　Key_name: index_name_shouji
　　Seq_in_index: 1
　　Column_name: name
　　Collation: A
　　Cardinality: 6
　　Sub_part: 6
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　*************************** 3. row***************************
　　Table: test
　　Non_unique: 1
　　Key_name: index_name_shouji
　　Seq_in_index: 2
　　Column_name: shouji
　　Collation: A
　　Cardinality: 6
　　Sub_part: 8
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　3 rows in set (0.00 sec)
1.28查询手机号以136开头的，名字为oldboy的记录（次记录要提前插入）
　　mysql> select * from test where name='mysql'and shouji like '179%';
　　+------+-------+------+------+------------+

　　|>　　+------+-------+------+------+------------+
　　|    6 |mysql | mei  |   21 | 1794225527 |
　　+------+-------+------+------+------------+
1.29查询上述语句的执行计划（是否使用联合索引等）
　　mysql> explain select * from test wherename='oldboy' and shouji like '1%';
　　+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+

　　|>　　+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+
　　|  1 |SIMPLE      | test  | range | index_name_shouji |index_name_shouji | 32      | NULL |    1 | Using where |
　　+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+
　　mysql> explain select * from test where name='oldboy' and shouji like '1%'\G
　　*************************** 1. row***************************
　　id: 1
　　select_type: SIMPLE
　　table: test
　　type: range
　　possible_keys: index_name_shouji
　　key: index_name_shouji
　　key_len: 32
　　ref: NULL
　　rows: 1
　　Extra: Using where
　　1 row in set (0.00 sec)
1.30把test的引擎改成Mysql
　　mysql> show create table test\G
　　*************************** 1. row***************************
　　Table: test
　　Create Table: CREATE TABLE `test` (
　　`id`int(4) DEFAULT NULL,
　　`name`varchar(16) DEFAULT NULL,
　　`shou`char(11) DEFAULT NULL,
　　`age`int(4) DEFAULT NULL,
　　`shouji`char(11) DEFAULT NULL,
　　KEY`name_idx` (`id`),
　　KEY`index_name_shouji` (`name`(6),`shouji`(8))
　　) ENGINE=InnoDB DEFAULT CHARSET=gbk

　　mysql>>　　Query OK, 5 rows affected (0.01 sec)
　　Records: 5 Duplicates: 0  Warnings: 0
　　mysql> show create table oldboy.test\G
　　*************************** 1. row***************************
　　Table: test
　　Create Table: CREATE TABLE `test` (
　　`id`int(4) NOT NULL DEFAULT'0',
　　`age`tinyint(2) DEFAULTNULL,
　　`name`varchar(16) DEFAULTNULL,
　　`shouji`char(11) DEFAULTNULL,
　　PRIMARYKEY (`id`),
　　KEY`index_name_shouji`(`name`(6),`shouji`(8))
　　) ENGINE=MyISAM DEFAULT CHARSET=gbk
　　1 row in set (0.00 sec)