mysql学习记录（二十四）

11.replace:文本替换工具

二、实践：

abc@ubuntu:~$ mysql -uroot -p(密码) --default-character-set=utf8Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 47Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> show variables like 'char%';+--------------------------+----------------------------------+| 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       | /usr/local/mysql/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.00 sec)mysql> show variables like 'char%';Ctrl-C -- exit!Abortedabc@ubuntu:~$ mysql -uroot -p(密码) --default-character-set=gbk;Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 48Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> show variables like 'char%';+--------------------------+----------------------------------+| Variable_name            | Value                            |+--------------------------+----------------------------------+| character_set_client     | gbk                              || character_set_connection | gbk                              || character_set_database   | utf8                             || character_set_filesystem | binary                           || character_set_results    | gbk                              || character_set_server     | utf8                             || character_set_system     | utf8                             || character_sets_dir       | /usr/local/mysql/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.00 sec)mysql> Ctrl-C -- exit!Abortedabc@ubuntu:~$ mysql -uroot -p(密码);Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 49Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> use sakila;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedabc@ubuntu:~$ mysql -uroot -p(密码) -e 'use sakila;select * from payment limit 5;';+------------+-------------+----------+-----------+--------+---------------------+---------------------+| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |+------------+-------------+----------+-----------+--------+---------------------+---------------------+|          1 |           1 |        1 |        76 |   2.99 | 2005-05-25 11:30:37 | 2006-02-15 22:12:30 ||          2 |           1 |        1 |       573 |   0.99 | 2005-05-28 10:35:23 | 2006-02-15 22:12:30 ||          3 |           1 |        1 |      1185 |   5.99 | 2005-06-15 00:54:12 | 2006-02-15 22:12:30 ||          4 |           1 |        2 |      1422 |   0.99 | 2005-06-15 18:02:53 | 2006-02-15 22:12:30 ||          5 |           1 |        2 |      1476 |   9.99 | 2005-06-15 21:08:46 | 2006-02-15 22:12:30 |+------------+-------------+----------+-----------+--------+---------------------+---------------------+abc@ubuntu:~$ mysql -uroot -p(密码) -e 'use sakila;select * from payment limit 5;' -E;*************************** 1. row ***************************  payment_id: 1 customer_id: 1    staff_id: 1   rental_id: 76      amount: 2.99payment_date: 2005-05-25 11:30:37 last_update: 2006-02-15 22:12:30*************************** 2. row ***************************  payment_id: 2 customer_id: 1    staff_id: 1   rental_id: 573      amount: 0.99payment_date: 2005-05-28 10:35:23 last_update: 2006-02-15 22:12:30*************************** 3. row ***************************  payment_id: 3 customer_id: 1    staff_id: 1   rental_id: 1185      amount: 5.99payment_date: 2005-06-15 00:54:12 last_update: 2006-02-15 22:12:30*************************** 4. row ***************************  payment_id: 4 customer_id: 1    staff_id: 2   rental_id: 1422      amount: 0.99payment_date: 2005-06-15 18:02:53 last_update: 2006-02-15 22:12:30*************************** 5. row ***************************  payment_id: 5 customer_id: 1    staff_id: 2   rental_id: 1476      amount: 9.99payment_date: 2005-06-15 21:08:46 last_update: 2006-02-15 22:12:30abc@ubuntu:~$ cd ~/Downloads/abc@ubuntu:~/Downloads$ mkdir mysqlabc@ubuntu:~/Downloads$ cd mysql/abc@ubuntu:~/Downloads/mysql$ vi a.sql;(在此步添加了一些内容)abc@ubuntu:~/Downloads/mysql$ more a.sql;insert into t2 values (1);insert into t2 values (2222222222222222222222222);insert into t2 values (3);abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码);Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 53Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> use test;Database changedmysql> create table t2(    -> id int(11)    -> ) engine = innodb charset = utf8;Query OK, 0 rows affected (0.05 sec)mysql> Ctrl-C -- exit!Abortedabc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test < a.sql;ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';+------+| id   |+------+|    1 |+------+abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -f < a.sql ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';+------+| id   |+------+|    1 ||    3 |+------+abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -f -v ~/test.txt;abc@ubuntu:/usr/local/mysql/bin$ cd ~/abc@ubuntu:~$ more ~/test.txt -- MySQL dump 10.13  Distrib 5.5.44, for Linux (x86_64)---- Host: localhost    Database: test-- -------------------------------------------------------- Server version 5.5.44-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `t2`--DROP TABLE IF EXISTS `t2`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `t2`--LOCK TABLES `t2` WRITE;/*!40000 ALTER TABLE `t2` DISABLE KEYS */;INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);/*!40000 ALTER TABLE `t2` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2015-11-06  5:15:01abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -d test t2 > ~/Downloads//t2.txtabc@ubuntu:/usr/local/mysql/bin$ more ~/Downloads/t2.txt;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -c test t2 > ~/Downloads/t2c.txtabc@ubuntu:/usr/local/mysql/bin$ more ~/Downloads/t2c.txt/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `t2` (`id`) VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);abc@ubuntu:/usr/local/mysql/bin$ mysqld --verbose --help | grep 'default-character-set' | grep -v name ;151106  5:18:52 [Warning] option 'table_definition_cache': unsigned value 100 adjusted to 400151106  5:18:52 [Note] mysqld (mysqld 5.5.44-log) starting as process 74255 ...151106  5:18:52 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test151106  5:18:52 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test151106  5:18:52 [Warning] One can only use the --user switch if running as rootmysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13)151106  5:18:52 [ERROR] Abortingabc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact test t2 > ~/Downloads/testA.txtabc@ubuntu:/usr/local/mysql/bin$ more ~/Downloads/testA.txt /*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact --default-character-set=latin1 test t2 > ~/Downloads/testA.txtabc@ubuntu:/usr/local/mysql/bin$ more ~/Downloads/testA.txt /*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);abc@ubuntu:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码);+--------------------+|     Databases      |+--------------------+| information_schema || mysql              || performance_schema || sakila             || test               || test1              |+--------------------+abc@ubuntu:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) --count;+--------------------+--------+--------------+|     Databases      | Tables |  Total Rows  |+--------------------+--------+--------------+| information_schema |     40 |        20863 || mysql              |     24 |         2214 || performance_schema |     17 |           14 || sakila             |     33 |        50132 || test               |      1 |           13 || test1              |     20 |           67 |+--------------------+--------+--------------+6 rows in set.abc@ubuntu:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test --count;Database: test+--------+----------+------------+| Tables | Columns  | Total Rows |+--------+----------+------------+| t2     |        1 |         13 |+--------+----------+------------+1 row in set.abc@ubuntu:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test a --count;mysqlshow: Cannot get record count for db: test, table: a: Table 'test.a' doesn't existabc@ubuntu:~/Downloads$ more t2c.txt/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t2` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `t2` (`id`) VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);abc@ubuntu:~/Downloads$ mysqlshow+--------------------+|     Databases      |+--------------------+| information_schema || test               |+--------------------+abc@ubuntu:~/Downloads$ mysqlshow -uroot -p(密码) test t2 --count;Database: test  Table: t2  Rows: 13+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+| id    | int(11) |           | YES  |     |         |       | select,insert,update,references |         |+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+abc@ubuntu:~/Downloads$ mysql -uroot -p(密码);Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 88Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> use sakila;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedabc@ubuntu:~/Downloads$ mysqlshow -uroot -p(密码) test t2 -k;Database: test  Table: t2+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+| id    | int(11) |           | YES  |     |         |       | select,insert,update,references |         |+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+Table has no keysabc@ubuntu:~/Downloads$ mysqlshow -uroot -p(密码) sakila actor -k;Database: sakila  Table: actor+-------------+----------------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+---------+| Field       | Type                 | Collation       | Null | Key | Default           | Extra                       | Privileges                      | Comment |+-------------+----------------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+---------+| actor_id    | smallint(5) unsigned |                 | NO   | PRI |                   | auto_increment              | select,insert,update,references |         || first_name  | varchar(45)          | utf8_general_ci | NO   |     |                   |                             | select,insert,update,references |         || last_name   | varchar(45)          | utf8_general_ci | NO   | MUL |                   |                             | select,insert,update,references |         || last_update | timestamp            |                 | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references |         |+-------------+----------------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+---------++-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| actor | 0          | PRIMARY             | 1            | actor_id    | A         | 201         |          |        |      | BTREE      |         |               || actor | 1          | idx_actor_last_name | 1            | last_name   | A         | 201         |          |        |      | BTREE      |         |               |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+