习惯了oracle，初次接触mysql，连一些基本的命令都不知道如何下手。参考官方文档，尝试了几个命令。记录下。

点击(此处)折叠或打开

1、登录、退出
[root@hqh ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 15
Server version: 5.6.19 MySQL Community Server (GPL)
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> quit
Bye
登录时直接进入test数据库
[root@hqh ~]# mysql test -u root -p
2、查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
sql语句于;结束，或者\\g
mysql> show databases\\g
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
遇到数据很长时，用\\G更清晰
mysql> show databases\\G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
*************************** 4. row ***************************
Database: test
4 rows in set (0.00 sec)
3、切换数据库
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> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
4、查看该数据库里面的所有表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| animals |
| shop |
| t1 |
+----------------+
3 rows in set (0.00 sec)
5、查看表结构
mysql> desc shop;
+---------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| article | int(4) unsigned zerofill | NO | PRI | 0000 | |
| dealer | char(20) | NO | PRI | | |
| price | double(16,2) | NO | | 0.00 | |
+---------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc shop price;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| price | double(16,2) | NO | | 0.00 | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
查看shop表中以cle结尾的列定义
mysql> desc shop \'%cle\';
+---------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| article | int(4) unsigned zerofill | NO | PRI | 0000 | |
+---------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
查看shop表中以cle结尾的列定义
mysql> show columns from shop like \'%cle\';
+---------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| article | int(4) unsigned zerofill | NO | PRI | 0000 | |
+---------+--------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
6、mysqlshow命令
使用show命令看到的信息都可以用mysqlshow查看
列出数据库
[root@hqh ~]# mysqlshow -u root -p
Enter password:
+--------------------+
| Databases |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
列出数据库test中的表
[root@hqh ~]# mysqlshow test -u root -p
Enter password:
Database: test
+---------+
| Tables |
+---------+
| animals |
| shop |
| t1 |
+---------+
7、更多show命令
mysql> help show
Name: \'SHOW\'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN \'log_name\'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE \'pattern\'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE \'pattern\'
part, \'pattern\' is a string that can contain the SQL \"%\" and \"_\"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
8、取消执行\\c
mysql> select * from shop where dealer =
-> \\c
mysql> select * from shop where dealer =\'A;
\'> \\c
\'> \'\\c
9、没有from的select
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2014-07-13 17:38:25 |
+---------------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
10、用select做简单的数学运算
mysql> select 1+2
-> ;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql> select 100*20-20;
+-----------+
| 100*20-20 |
+-----------+
| 1980 |
+-----------+
1 row in set (0.00 sec)
用oracle的话，至少还要加from dual，mysql简洁了些。
mysql> select DATE_ADD(now(),INTERVAL 10 YEAR);
+----------------------------------+
| DATE_ADD(now(),INTERVAL 10 YEAR) |
+----------------------------------+
| 2024-07-13 17:45:45 |
+----------------------------------+
1 row in set (0.00 sec)

ITPub博客

Mysql基本命令

最新文章