mysql常用命令
公众号：杨建荣的学习笔记 · 作者：杨建荣 · 发布：2014-03-12 08:13:03 · 原文链接
这几天学习了一下mysql，对于mysql的命令总结如下，发现很多方面和oracle还是差别挺大的。
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14-enterprise-commercial-advanced
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql>
–列出相关的数据库
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
–修改密码
mysql> SET PASSWORD = PASSWORD(‘mysql’);
Query OK, 0 rows affected (0.00 sec)
–创建数据库
mysql> create database test;
ERROR 1007 (HY000): Can’t create database ‘test’; database exists
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+
4 rows in set (0.01 sec)
–切换到所在的库
mysql> use test;
Database changed
–列出相关的tables
mysql> show tables;
Empty set (0.00 sec)
–创建table,原来在mysql中是varchar，不是varchar2
mysql> create table mytable(name varchar2(20),sex char(1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘varchar2(20),sex char(1))’ at line 1
mysql> create table mytable(name varchar(20),sex char(1));
Query OK, 0 rows affected (0.13 sec)
–drop表
mysql> drop table mytable;
Query OK, 0 rows affected (0.05 sec)
–创建表
mysql> create table mytable(id int,name varchar(29));
Query OK, 0 rows affected (0.07 sec)
–列出表的信息，和oracle一样。 desc，describe都可以
mysql> desc mytable
-> ;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(29) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set (0.00 sec)
–插入数据
mysql> insert into mytable values(1,’aaa’);
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[mysql@oel2 app]$ pwd
/u02/app
[mysql@oel2 app]$ cat a.sql
insert into mytable values(1,’aaa’);
[mysql@oel2 app]$
–调用脚本内容，和oracle 中sqlplus 下 @的功能类似
mysql> load data local infile “/u02/app/a.sql” into table mytable;
Query OK, 1 row affected, 2 warnings (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 2
mysql>
–查询数据
mysql> select *from mytable;
+——+——+
| id | name |
+——+——+
| 1 | aaa |
| 0 | NULL |
+——+——+
2 rows in set (0.00 sec)
–导出数据
导出数据库（库名test)的数据，查看dump内容，直接是sql语句。
[mysql@oel2 app]$ mysqldump -u mysql test>a.data
[mysql@oel2 app]$ ll
total 12
-rw-r–r– 1 mysql dba 1878 Dec 8 23:38 a.data
-rw-r–r– 1 mysql dba 38 Dec 8 23:33 a.sql
drwxr-xr-x 2 mysql dba 4096 Dec 8 12:21 db
[mysql@oel2 app]$ less a.data
–重新建一个库，建一张表，来解释和oracle中的不同。
mysql> use test;
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> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
+——————–+
5 rows in set (0.00 sec)
mysql> use test2;
Database changed
–库2中创建的表test_table2可以直接引用库1的表 mytable.
mysql> create table test_table2 as select *from test.mytable;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+—————–+
| Tables_in_test2 |
+—————–+
| test_table2 |
+—————–+
1 row in set (0.00 sec)
mysql> desc test_tables;
ERROR 1146 (42S02): Table ‘test2.test_tables’ doesn’t exist
mysql> desc test_table2
-> ;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(29) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set (0.00 sec)
mysql>
–列出enginue的信息，innoDB 还是默认的引擎。
mysql> show engines;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)
mysql> show tables;
+—————–+
| Tables_in_test2 |
+—————–+
| test_table2 |
+—————–+
1 row in set (0.00 sec)
–列出创建表的DDL语句
mysql> show create table test_table2;
+————-+————————————————————————————————————————————+
| Table | Create Table |
+————-+————————————————————————————————————————————+
| test_table2 | CREATE TABLE `test_table2` (
`id` int(11) DEFAULT NULL,
`name` varchar(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+————-+————————————————————————————————————————————+
1 row in set (0.00 sec)
[mysql@oel2 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.14-enterprise-commercial-advanced MySQL Enterprise Server – Advanced Edition (Commercial)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
–查询版本和当前时间
mysql>
->select version(),current_date;
+—————————————+————–+
| version() | current_date |
+—————————————+————–+
| 5.6.14-enterprise-commercial-advanced | 2013-12-09 |
+—————————————+————–+
1 row in set (0.04 sec)
–可以并行运行两个sql语句。
->select version();select now();
+—————————————+
| version() |
+—————————————+
| 5.6.14-enterprise-commercial-advanced |
+—————————————+
1 row in set (0.01 sec)
+———————+
| now() |
+———————+
| 2013-12-09 18:20:01 |
+———————+
1 row in set (0.00 sec)
–查出当前时间
->select curdate();
+————+
| curdate() |
+————+
| 2013-12-09 |
+————+
1 row in set (0.00 sec)
–列出变量参数，如下
->show varialbes;
| slave_checkpoint_period | 300 |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600
–列出服务器运行的状态值
->show global status;
+———————————————–+————-+
| Variable_name | Value |
+———————————————–+————-+
…
| Handler_read_rnd | 2 |
| Handler_read_rnd_next | 2689 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 2636 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 181 |
| Innodb_buffer_pool_bytes_data | 2965504 |
—显示插件的信息
>SELECT * FROM information_schema.PLUGINS\G
*************************** 2. row ***************************
PLUGIN_NAME: mysql_native_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Native MySQL authentication
PLUGIN_LICENSE: PROPRIETARY
LOAD_OPTION: FORCE
*************************** 3. row ***************************
PLUGIN_NAME: mysql_old_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Old MySQL-4.0 authentication
PLUGIN_LICENSE: PROPRIETARY
LOAD_OPTION: FORCE