mysql常用命令-白红宇

mysql常用命令

阅读量：2449 次

发布时间：2019-05-10

本文共 9413 字，大约阅读时间需要 31 分钟。

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

--列出变量参数，如下

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

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

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

你可能感兴趣的文章

filetable_SQL Server FILETABLE用例

查看>>

ssis组件_SSIS脚本组件概述

查看>>

sql 触发器嵌套条件_SQL Server中的嵌套触发器

查看>>

SQL Server中的数据库快照

查看>>

power bi 实时_Power BI中的实时流

查看>>

power bi 背景图_Power BI桌面饼图树

查看>>

@sql 单元测试_SQL单元测试最佳实践

查看>>

sql组合索引和独立索引_SQL索引概述和策略

查看>>

SQL Server执行计划面试问题

查看>>

sql t-sql_增强的PolyBase SQL 2019-使用t-SQL的外部表

查看>>

清理搜狗输入法_清理输入内容：避免安全性和可用性灾难

查看>>

索引sql server_SQL Server索引与统计顾问的困境或麻烦

查看>>

aws s3 cli_了解AWS CLI –使用AWS CLI与AWS S3存储桶进行交互

查看>>

json 转对象函数_JSON_QUERY（）函数从JSON数据提取对象

查看>>

将PowerShell连接到SQL Server –使用其他帐户

查看>>

SQL Server Management Studio 2016的新功能–快速启动

查看>>

使用云SQL Server数据库备份和还原操作

查看>>

SQL Server Management Studio（SSMS）中的自定义键盘快捷方式

查看>>

透明加密tde_如何在SQL Server中配置透明数据加密（TDE）

查看>>

将SQL Server数据库备份到多个文件或从多个文件还原

查看>>