mysql常用命令

最新推荐文章于 2024-08-11 16:01:20 发布

congbao6525

最新推荐文章于 2024-08-11 16:01:20 发布

阅读量68

点赞数

文章标签：数据库

这几天学习了一下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

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

-> ;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | 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

-> ;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>

--列出enginue的信息，innoDB 还是默认的引擎。

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| 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)

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)

--列出变量参数，如下

...
| 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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1062933/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1062933/