mysql非常有用的几个命令

最新推荐文章于 2023-12-21 15:09:41 发布
最新推荐文章于 2023-12-21 15:09:41 发布
阅读量204 收藏
点赞数
分类专栏： 数据库 mysql&oracle 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhongweij/article/details/83982379
版权

 

mysql> use test;
Database changed
mysql> select @@version;  
+-----------+
| @@version |
+-----------+
| 5.5.15    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show column from mytable;
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 'column from mytable' at line 1
mysql> show columns from mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a     | int(11)  | NO   | PRI | NULL    |       |
| b     | char(18) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> use mysql;
Database changed
mysql> show index from db;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| db    |          0 | PRIMARY  |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| db    |          0 | PRIMARY  |            2 | Db          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| db    |          0 | PRIMARY  |            3 | User        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| db    |          1 | User     |            1 | User        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> show privilges;
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 'privilges' at line 1
mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table


                                                                                                                                       |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| user  | CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

mysql>
zhongweij
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql查询并生产表_mysql 查询结果创建表
weixin_33788424的博客
01-18 2306
用 SELECT 的结果创建表关系数据库的一个重要概念是，任何数据都表示为行和列组成的表，而每条 SELECT 语句的结果也都是一个行和列组成的表。在许多情况下，来自 SELECT 的“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在 MySQL 3.23 以前，如果想将 SELECT 的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排：1) 运行 DESCRIBE 或 SHOW ...
入门学习Linux常用必会60个命令实例详解doc/txt
06-09
建议在/mnt里建几个/mnt/cdrom、/mnt/floppy、/mnt/mo等目录，当作目录的专用挂载点。举例而言，如要挂载下列5个设备，其执行指令可能如下 (假设都是Linux的ext2系统，如果是Windows XX请将ext2改成vfat)： 软盘 ==...
二、SQL，如何实现表的创建和查询
2301_79149013的博客
08-17 2504
（2）基础： 示例： 示例： （2）示例：
mysql查看创建表的sql语句
a13568hki的博客
09-13 5068
mysql> show create table history; mysql> show create table history; +---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql 如何查看建表语句
艾斯比
03-11 2万+
由于公司都是使用 linux 跳板机连接 mysql, 而某个项目又没有测试环境, 于是只能通过跳板机查看某张表的详细信息. 具体语句如下 这里以查看 ups_auth_info 表为例 # \G 表示以垂直形式查看结果 show create table ups_auth_info \G; ...
SQL建表方式
feiyuxc的博客
05-30 6836
SQL3种建表语句
mysql administrator v1.2.17
11-02
　几乎所有的任务都可以用命令提示符下的mysqladmin 和mysql命令来完成，但是MySQL Administrator再下列方面将是非常的有优点。  　1. 它的图形化的用户界面为用户提供了非常直观的接口。  　2. 它提供了较好的...
PHP和MySQL Web开发第4版
08-13
2.10.1 使用普通文件的几个问题 2.10.2 RDBMS是如何解决这些问题的 2.11 进一步学习 2.12 下一章 第3章 使用数组 3.1 什么是数组 3.2 数字索引数组 3.2.1 数字索引数组的初始化 3.2.2 访问数组的内容 ...
PHP和MySQL Web开发第4版pdf以及源码
10-13
2.10.1 使用普通文件的几个问题 2.10.2 RDBMS是如何解决这些问题的 2.11 进一步学习 2.12 下一章 第3章 使用数组 3.1 什么是数组 3.2 数字索引数组 3.2.1 数字索引数组的初始化 3.2.2 访问数组的内容 ...
MySQL核心技术手册(第二版)][1].（美）戴尔.扫描版.part2
05-13
　·给出了几个指导章节用于帮助新手入门。 　·为php、perl和c语言提供了应用程序编程接口(api)。 　·在每章的api开头部分提供了简要的指南。 　·新增复制、触发器和存储过程章节。 　·提供了许多mysql的实用...
mysql查看数据库中所有的表的建表语句
最新发布
stone_tomcate的博客
12-21 570
其中，"table_name"为你要查看的表的名称。这条命令将返回数据库中所有表的列表。
PLSQL如何查看建表语句
weixin_47086720的博客
04-11 1万+
PLsql查看建表语句
MySQL查看建库建表语句
a10534126的博客
04-22 1万+
有时候我们需要查看mysql的建表语句，以下是具体操作 查看数据库创建语句 SHOW CREATE DATABASE ZOOG; 类似的查看建表语句： 查看所有存储引擎：SHOW ENGINES G; 查看默认存储引擎:SHOW VARIABLES LIKE ‘storage_engine’; 查看当前使用数据库:SELECT DATABASE(); 或者使用命令：STATUS win下修改默认存储引擎，找到文件my.ini，将[mysqld]字段下面的default-storage-engin
怎么在SQLyog中查看建表语句DDL
、抓不住的沙、、
06-30 3569
前言一、可视化操作二、SQL查询主要有两种方式：1、全可视化操作，2、SQL语句查询出DDL语句代码如下（示例）：
mysql使用SQL语句创建表
热门推荐
LTLTLT_的博客
04-20 3万+
打开数据库： 步骤：新建查询（Ctrl+Q）→写SQL语句→运行→刷新→创建表成功 创建表的SQL语句一般格式： CREATE TABLE <表名>( <列名> <数据类型> [列级完整性约束定义] {, <列名> <数据类型> [列级完整性约束定义] … } [,表级完整性约束定义] ) 下面来举个列子来说明一下就更清晰了，...
MySQL 如何查看建表语句
Hiraet_h的博客
06-20 532
建表语句如何查看
SQL语句建表、查询（一）
qq_43254543的博客
04-03 1421
1、建表 create Table Student( Sno char(9) not null, Sname char(20) not null, Ssex char(2) null, Sage smallint null, Sdept char(20) null, primary key (Sno) ); create Table Course( Cno char(4) not null, Cn...
MySQL｜基础操作+8大查询方式汇总
二哥为啥不像程序员？
10-24 3247
MySQL中可以创建多个数据库用于存储不同场景的表结构，学习MySQL之前，我们要先理清如下的关系：数据库 --> 数据表 --> 字段抛开数据库存储上限去考虑，每个数据库中可以包含无数个数据表，而每个数据表又可以包含无数个字段，因此我们的学习也应该从创建数据库开始。use 数据库名;
Mysql查看表的建表语句DDL
龙倾心的博客
04-24 4041
第一种：通过命令查看 列如查看xxx表的建表语句 SHOW CREATE TABLE XXX表名 第二种是通过可视化界面查看： 比如Navicat中：
mysql这个自动完成功能是什么作用？请详细说明一下
05-29
MySQL的自动完成功能是指在MySQL命令行工具中使用Tab键时，MySQL会自动补全表名、列名和函数名等信息的功能。...总之，自动完成功能是MySQL命令行工具中非常有用的一个功能，可以帮助用户提高工作效率，减少输入错误。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值