Linux运维必会的MySql题之（二）
删除test表中的所有数据，并查看
mysql> use mingongge; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> delete from test; Query OK, 3 rows affected (0.00 sec) mysql> select * from test; Empty set (0.00 sec)
删除表test和mingongge数据库并查看
mysql> drop table test; Query OK, 0 rows affected (0.03 sec) mysql> show tables; Empty set (0.00 sec) mysql> drop database mingongge; Query OK, 0 rows affected (0.07 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
不退出数据库恢复以上删除的数据
mysql> system mysql -uroot -p123 </root/mingongge_bak.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mingongge | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use mingongge Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables -> ; +---------------------+ | Tables_in_mingongge | +---------------------+ | test | +---------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+------+--------------+ | d | age | name | +------+------+--------------+ | 1 | NULL | mgg | | 2 | NULL | ljj | | 3 | NULL | mingonggeedu | +------+------+--------------+ 3 rows in set (0.00 sec)
把库表的GBK字符集修改为UTF8
mysql> alter database mingongge default character set utf8; Query OK, 1 row affected (0.00 sec) mysql> alter table test default character set utf8; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> show create database mingongge; +-----------+--------------------------------------------------------------------+ | Database | Create Database | +-----------+--------------------------------------------------------------------+ | mingongge | CREATE DATABASE `mingongge` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table test; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `d` int(4) DEFAULT NULL, `age` tinyint(2) DEFAULT NULL, `name` varchar(16) CHARACTER SET gbk DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
把d列设置为主键，在Name字段上创建普通索引
mysql> alter table test add primary key(d); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index mggindex on test(name(16)); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | d | int(4) | NO | PRI | NULL | | | age | tinyint(2) | YES | | NULL | | | name | varchar(16) | YES | MUL | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
在字段name后插入手机号字段(shouji)，类型char(11)
mysql> alter table test add shouji char(11); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | d | int(4) | NO | PRI | NULL | | | age | tinyint(2) | YES | | NULL | | | name | varchar(16) | YES | MUL | NULL | | | shouji | char(11) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
所有字段上插入2条记录（自行设定数据）
mysql> insert into test values('4','23','li','13700000001'),('5','26','zhao','13710000001'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test; +---+------+--------------+-------------+ | d | age | name | shouji | +---+------+--------------+-------------+ | 1 | NULL | mgg | NULL | | 2 | NULL | ljj | NULL | | 3 | NULL | mingonggeedu | NULL | | 4 | 23 | li | 13700000001 | | 5 | 26 | zhao | 13710000001 | +---+------+--------------+-------------+ 5 rows in set (0.00 sec)
在手机字段上对前8个字符创建普通索引
mysql> create index SJ on test(shouji(8)); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
查看创建的索引及索引类型等信息
mysql> show index from test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | test | 0 | PRIMARY | 1 | d | A | 5 | NULL | NULL | | BTREE | | | | test | 1 | mggindex | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | | test | 1 | SJ | 1 | shouji | A | 3 | 8 | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `d` int(4) NOT NULL, `age` tinyint(2) DEFAULT NULL, `name` varchar(16) CHARACTER SET gbk DEFAULT NULL, `shouji` char(11) DEFAULT NULL, PRIMARY KEY (`d`), KEY `mggindex` (`name`), KEY `SJ` (`shouji`(8)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> show keys from test\G *************************** 1. row *************************** Table: test Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: d Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: test Non_unique: 1 Key_name: mggindex Seq_in_index: 1 Column_name: name Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: test Non_unique: 1 Key_name: SJ Seq_in_index: 1 Column_name: shouji Collation: A Cardinality: 3 Sub_part: 8 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec)
删除Name，shouji列的索引
mysql> drop index SJ on test; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index mggindex on test; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
对Name列前6个字符以及手机列的前8个字符组建联合索引
mysql> create index lianhe on test(name(6),shouji(8)); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `d` int(4) NOT NULL, `age` tinyint(2) DEFAULT NULL, `name` varchar(16) CHARACTER SET gbk DEFAULT NULL, `shouji` char(11) DEFAULT NULL, PRIMARY KEY (`d`), KEY `lianhe` (`name`(6),`shouji`(8)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
查询手机号以137开头的，名字为zhao的记录（提前插入）
mysql> select * from test where shouji like '137%' and name = 'zhao'; +---+------+------+-------------+ | d | age | name | shouji | +---+------+------+-------------+ | 5 | 26 | zhao | 13710000001 | +---+------+------+-------------+ 1 row in set (0.07 sec)
查询上述语句的执行计划（是否使用联合索引等）
mysql> explain select * from test where name = 'zhao' and shouji like '137%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test partitions: NULL type: range possible_keys: lianhe key: lianhe key_len: 40 ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
把test表的引擎改成MyISAM
mysql> alter table test engine=MyISAM; Query OK, 5 rows affected (0.14 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `d` int(4) NOT NULL, `age` tinyint(2) DEFAULT NULL, `name` varchar(16) CHARACTER SET gbk DEFAULT NULL, `shouji` char(11) DEFAULT NULL, PRIMARY KEY (`d`), KEY `lianhe` (`name`(6),`shouji`(8)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
收回mingongge用户的select权限
mysql> revoke select on mingongge.* from mingongge@localhost; Query OK, 0 rows affected (0.00 sec)
mysql> show grants for mingongge@localhost; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for mingongge@localhost | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mingongge'@'localhost' | | GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mingongge`.* TO 'mingongge'@'localhost' | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
删除mingongge用户下数据库mingongge
drop user migongge@localhost;
drop database mingongge;
使用mysqladmin关闭数据库
[root@localhost ~]# mysqladmin -uroot -p123 shutdown mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]# lsof -i :3306
