MySQL常用命令

Jev Tse • • Linux干货, 数据库运维

本文大纲

MySQL命令             （0%）

交互式CLI工具    
服务端命令
mysqld服务器程序
数据类型

DDL语句                   （40%）

数据库操作
表操作

DML语句                  （60%）

插入：INSERT    
删除：DELETE    
更新：UPDATE
检索：SELECT    
单表查询
多表查询
子查询

 

MySQL命令

 

1、mysql：交互式CLI工具；命令化运行；

2、客户端命令：于客户端执行；

3、服务端命令：SQL语句，需要一次性完整地发往服务端；语句必须有结束符；

 

交互式CLI工具

 

交互式CLI工具：mysql [options] db_name

-h host_name，–host=host_name

服务端地址，默认为localhost；

-u user_name，–user=user_name

用户名，默认为root；

-p[password]，–password[=password]

用户密码，默认为空密码；

-P port_num ，–port=port_num

服务端端口，默认为3306；

–protocol={TCP|SOCKET|PIPE|MEMORY}

本地通信：基于本地回环地址进行请求，将基于本地通信    协议；Linux：SOCKET；Windows：PIPE，MEMORY

非本地通信：使用非本地回环地址进行的请求 TCP协议；

-S path，–socket=path

SOCKET路径

-D db_name ，–database=db_name

指明连接到服务器后，默认进入的数据库

-C，–compress

数据压缩传输

-e statement，–execute=statement

非交互模式执行SQL语句；

-E，–vertical

查询结果纵向显示；

 

服务端命令

 

1、服务端命令：SQL语句，需要一次性完整地发往服务端；语句必须有结束符；

服务端命令：SQL语句，需要一次性完整地发往服务端；语句必须有结束符

?                (\?) Synonym for `help'.

clear            (\c) Clear the current input statement.

connect            (\r) Reconnect to the server. Optional arguments are db and host.

delimiter            (\d) Set statement delimiter.

edit                (\e) Edit command with $EDITOR.

ego                (\G) Send command to mysql server, display result vertically.

exit                (\q) Exit mysql. Same as quit.

go                (\g) Send command to mysql server.

help                (\h) Display this help.

nopager            (\n) Disable pager, print to stdout.

note                (\t) Don't write into outfile.

pager            (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print            (\p) Print current command.

prompt            (\R) Change your mysql prompt.

quit                (\q) Quit mysql.

rehash            (\#) Rebuild completion hash.

source            (\.) Execute an SQL script file. Takes a file name as an argument.

status            (\s) Get status information from the server.

system            (\!) Execute a system shell command.

tee                (\T) Set outfile [to_outfile]. Append everything into given outfile.

use                (\u) Use another database. Takes database name as argument.

charset            (\C) Switch to another charset. Might be needed for processing binlog                     with multi-byte charsets.

warnings            (\W) Show warnings after every statement.

nowarning        (\w) Don't show warnings after every statement.

2、mysql命令的使用帮助：

# man mysql
# mysql  --help  --verbose

3、sql脚本运行：

mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT

mysqld服务器程序

 

mysqld服务器程序：工作特性的定义方式

1、服务器参数/变量：设定MySQL的运行特性；

mysql> SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]；

2、状态（统计）参数/变量：保存MySQL运行过程中的统计数据或状态数据；通常不能修改；

mysql> SHOW [GLOBAL | SESSION] STATUS [like_or_where];

3、显示单个变量设定值的方法：

mysql> SELECT @@[global.|session.]system_var_name

%：匹配任意长度的任意字符；

_：匹配任意单个字符；

4、变量/参数级别：

全局：global，为所有会话设定默认；

会话：session，跟单个会话相关；会话建立会从全局继承；

5服务器变量的调整方式：

运行时修改：

global：仅对修改后新建立的会话有效；

session：仅对当前会话有效，且立即生效；

通过配置文件修改，重启后生效；

6、运行时修改服务器变量值操作方法：

mysql> HELP SET
SET [GLOBAL | SESSION] system_var_name = expr
SET [@@global. | @@session. | @@]system_var_name = expr

7、安装完成后的安全初始化：

#mysql_secure_installation

8、运行前常修改的参数：

innodb_file_per_table=ON    修改InnoDB为独立表空间模式
skip_name_resolve=ON       禁止MySQL对外部连接进行DNS解析
sql_safe_updates=ON         阻止不带条件更新操作

 

数据类型

 

MySQL的数据类型：字符型、数值型、日期时间型、内建类型

SQL中的字符类型：  通过mysql> help 'Data Types'命令可获取帮助

字

符

型

定长字符型：

CHAR(#) 不区分字符大小写

BINARY(#) 区分字符大小写

变长字符型：

VARCHAR(#) 不区分字符大小写,需要在字符长度加1,最多65536个字符

VARBINARY(#)：不区分字符大小写,需要在字符长度加1,最多65536个字符

对象存储：

TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT

BLOB：TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB

数

值

型

精确数值型：

INT(TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT)整型

DECIMAL十进制数据

近似数值型：

 

FLOAT 单精度浮点型

DOUBLE 双精度浮点型

REAL

BIT

日期时间型

DATE 日期型

TIME 时间型

DATETIME 日期时间型

TIMESTAMP 时间戳

YEAR(2) 2位年数

YEAR(4) 4位年数

内建类型

SET     集合

ENUM   枚举

类型修饰符

字符型：NOT NULL，NULL，DEFALUT 'STRING'，CHARACET SET 'CHARSET'，COLLATION 'collocation'

整型：NOT NULL， NULL， DEFALUT value，AUTO_INCREMENT，UNSIGNED

日期时间型：NOT NULL， NULL， DEFAULT

 

SQL MODE：定义mysqld对约束等违反时的响应行为等设定；

常用的MODE：

 

TRADITIONAL 

严格模式，保证错误数据不能插入，报error错误。

STRICT_TRANS_TABLES 

如果给定的值非法，则放弃该语句，返回错误并继续执行剩余的行。

STRICT_ALL_TABLES 

如果给定的值非法，则放弃该语句，返回错误并忽视剩余的行。

修改方式：

mysql> SET GLOBAL sql_mode='MODE'; 

mysql> SET @@global.sql_mode='MODE'; 

MySQL中字符大小写

1.SQL关键字及函数不区分大小写

2.数据库、表及视图名称的大小写区分与否取决于底层OS和FS

3.存储过程、存储函数及时间调度器的名字不区分大小写，但触发器区分大小写

4.表别名区分大小写

5.对字段中的数据，如果字段类型为binary类型，则区分大小写，非binary不区分大小写

 

DDL语句

 

DDL：Data Defination Language，数据库定义语言

mysql> HELP Data Definition
     CREATE, ALTER, DROP
     DATABASE, TABLE
     INDEX, VIEW, USER
     FUNCTION, FUNCTION UDF, PROCEDURE, TABLESPACE, TRIGGER, SERVER

 

数据库操作

 

新建数据库：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name；

修改数据库：

ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name  COLLATE [=] collation_name；

删除数据库：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name；

表操作

 

1、创建新表：CREATE

(1)、直接创建表：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]；
CREATE TABLE [IF NOT EXISTS] tble_name (col_name  data_typ|INDEX|CONSTRAINT)；

(2)、直接创建表，并将查询语句的结果插入到新创建的表中；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement；

(3)、复制某存在的表的结构来创建新的空表；

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }；

2、删除表：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]；

3、修改表：

ALTER  TABLE tbl_name [alter_specification [, alter_specification] ...]；

可修改内容：

(1) table_options

(2) 添加定义：字段、字段集合、索引、约束

ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]；

(3) 修改字段：

修改字段名称，并设置字段的位置：

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]；

修改字段类型及属性及位置：

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]；

(4) 删除操作：字段、索引、约束

DROP [COLUMN] col_name；

表重命名 

RENAME [TO|AS] new_tbl_name

4、表查看

查看表结构定义：

DESC tbl_name；

查看表定义：

SHOW CREATE TABLE tbl_name

查看表属性信息：

SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

各参数详解：

【IF NOT EXISTS】：如果不存在则执行操作，，用于避免mysql报错；

【IF EXISTS】：如果存在则执行操作，用于避免mysql报错；

【CHARACTER SET [=] charset_name】：设置默认的字符集；

【COLLATE [=] collation_name】：设置默认的排序方式；

【TEMPORARY】：创建临时表；

【create_definition】：这是表的列属性部分，创建表时，表要至少包含一列；

【(create_definition,…)】参数：定义字段、字段集合、索引、约束等；

 col_name type [NOT NULL|NULL] [DEFAULT] [UNIQUE] [AUTO_INCREMENT] [PRIMARY KEY][FOREIGN KEY] [reference_definition]

    col_name：表示表中字段的名称

    type：表示字段的数据类型

    NOT NULL|NULL：指出该列是否允许是空值，系统一般默认允许为空值，所以当不允许为空值时，必须使用NOT NULL；

    DEFAULT：为该字段设置默认值

    UNIQUE：表示该字段的值是唯一的

    AUTO_INCREMENT：表示该字段的值是自动编号（自动增加），每个表只能有一个AUTO_INCREMENT列，并且必须被索引；

    PRIMARY KEY：设置该字段为的主键，如果没设定，而某些应用程序需要，MySQL会将第一个没有任何NULL列的唯一键，作为主键；

    FOREIGN KEY：表示该字段为该表的外键，是与之联系的某表的主键；

    reference_definition：为字段添加注释；

【table_options】参数：定义表的特性参数

   ENGINE [=] engine_name：定义存储引擎；从5.5开始默认为InnoDB，版本小于5.5默认为MyISAM；

      查看支持的所有存储引擎：mysql> SHOW ENGINES;

      查看指定表的存储引擎：mysql> SHOW TABLE STATUS LIKE clause;

   AUTO_INCREMENT [=] VALUE ：指定AUTO_INCREMENT的起始值

   [DEFAULT] CHARACTER SET [=] charset_name ：指定字符集

   CHECKSUM [=] {0 | 1} ：是否使用校验值，不建议使用

   [DEFAULT] COLLATE [=] collation_name：指定排序规则

   COMMENT [=] 'string'：表的注释信息

   DELAY_KEY_WRITE [=] {0 | 1} ：是否启用间延迟写入

   TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 定义表空间

   ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}：定义表的格式

     DEFAULT默认,DYNAMIC动态,FIXED静态,COMPRESSED压缩,REDUNDANT冗余,COMPACT紧致

【select_statement】：SELECT语句，用于快速创建表

【AS】：别名

索引：数据结构

创建：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)；

查看：

SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]；

删除：

DROP  INDEX index_name ON tbl_name

索引类型：

聚集索引、非聚集索引：索引是否与数据存在一起；

主键索引、辅助索引

稠密索引、稀疏索引：是否索引了每一个数据项；

BTREE（B+）、HASH、R Tree、FULLTEXT

BTREE：左前缀；

EXPLAIN：分析查询语句的执行路径；

视图：VIEW

虚表：存储下来的SELECT语句；

创建：

CREATE  VIEW view_name [(column_list)] AS select_statement；

修改：

ALTER  VIEW view_name [(column_list)] AS select_statement；
删除：
DROP VIEW [IF EXISTS] view_name [, view_name] ...；

 

DML语句

 

DML: Data Manipulation Language，数据库操纵语言

查看所有DML语句：

mysql> HELP Data Manipulation
     INSERT/REPLACE，DELETE，UPDATE，SELECT

 

插入：INSERT

 

单行插入

批量插入

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
      {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]
 
Or:
 
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name
      SET col_name={expr | DEFAULT}, ...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]
 
Or:
 
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
      [INTO] tbl_name [(col_name,...)]
      SELECT ...
      [ ON DUPLICATE KEY UPDATE
      col_name=expr
      [, col_name=expr] ... ]

 

删除：DELETE

DELETE  FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

 

注意：一定要有限制条件，否则将清空整个表；

限制条件：

[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]

 

更新：UPDATE

UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]

注意：一定要有限制条件，否则将修改整个表中指定字段的数据；

限制条件：

[WHERE where_condition]
[ORDER BY ...] [LIMIT row_count]

注意：sql_safe_updates变量可阻止不带条件更新操作；

 

检索：SELECT

 

Query Cache：缓存查询的执行结果；

key：查询语句的hash值；

value：查询语句的执行结果；

SQL语句的编写方式：

SELECT name FROM tbl2;
select name from tbl2;

查询执行路径：

请求–>查询缓存：命中后返回；

请求–>查询缓存–>解析器–>预处理器–>优化器–>查询执行引擎–>存储引擎–>缓存–>响应

默认不缓存，非确定结果不缓存  大于或小于某范围不缓存

SELECT语句的执行流程：

FROM  –> WHERE –> Group By –> Having –> Order BY –> SELECT –> Limit

 

单表查询

 

SELECT
      [ALL | DISTINCT | DISTINCTROW ]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
      select_expr [, select_expr ...]
      [FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]

 

用法：

SELECT col1, col2, ... FROM tble_name；
SELECT col1, col2, ... FROM tble_name WHERE clause；
SELECT col1, col2, ... FROM tble_name [WHERE clause] GROUP BY col_name [HAVING clause]；

分组的目的在于聚合计算：avg, max, min, count, sum,

 

DISTINCT：数据去重；

SQL_CACHE：显式指定缓存查询语句的结果；

SQL_NO_CACHE：显式指定不缓存查询语句的结果；

 

query_cache_type服务器变量有三个值：

ON：启用；

SQL_NO_CACHE：不缓存；默认符合缓存条件都缓存；

OFF：关闭；

DEMAND：按需缓存；

SQL_CACHE：缓存；默认不缓存；

字段可以使用别名 ：

col1 AS alias1, col2 AS alias2, ...

 

WHERE子句：指明过滤条件以实现"选择"功能；

过滤条件：布尔型表达式；

[WHERE where_condition]

WHERE操作符：  [WHERE where_condition]

算术操作符：

"+"、 "-"、 "*"、 "/"、 "%"

比较操作符：

"="、 "<>"、 "!= "、 "<=>"、 ">"、 ">="、 "<"、 "<="

 注："<>"、"!= "均为不等于； "<=>"类似"="，用于与空值比较

逻辑操作符

"AND"；  "OR"；  "NOT"；  "XOR"

IS NULL

是否为空

IS NOT NULL

是否不为空

BETWEEN min AND max

指定区间

IN

给定条件列表

LIKE

模糊比较，"%"、"_"

RLIKE或REGEXP

使用正则匹配

GROUP BY：根据指定的字段把查询的结果进行"分组"以用于"聚合"运算；

聚合运算：GROUP BY

avg()

平均值

max()

最大值

sum()

求和

min()

最小值

count()

计数

   

ASC

升序

DESC

降序

HAVING

对分组聚合后的结果进行条件过滤

LIMIT：对输出结果进行数量限制

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

offset：偏移的行数； row_count：显示行数；

 

多表查询

 

连接操作：

    交叉连接：笛卡尔积；

   内连接：

            等值连接：让表之间的字段以等值的方式建立连接；

           不等值连接：

           自然连接

           自连接

      外连接：

             左外连接：

     FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col

             右外连接：

     FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col

 

子查询

 

子查询：在查询中嵌套查询；

 

用于WHERE子句中的子查询；

(1) 用于比较表达式中的子查询：子查询仅能返回单个值；

(2) 用于IN中的子查询：子查询可以返回一个列表值；

(3) 用于EXISTS中的子查询：

用于FROM子句中的子查询；

SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause;

 

联合查询：将多个查询语句的执行结果相合并；

UNION
     SELECT clause UNION SELECT cluase；

 

 

 

 

本文完【返回页首】

 

原创文章，作者：Jev Tse，如若转载，请注明出处：http://www.178linux.com/68929

赞 (0)
Jev TseJev Tse
0
上一篇 2017-02-16 15:26
下一篇 2017-02-16 17:39

相关推荐

  • 关于ip_conntrack跟踪连接满导致网络丢包问题的分析

    http://ixdba.blog.51cto.com/2895551/1737642

    Linux干货 2016-06-03

  • N25第二周作业 文件管理类命令cp mv rm以及 bash特性之文件名通配

    Linux上的文件管理类命令都有哪些，其常用的使用方法及其相关示例演示。         文件管理类命令主要有cp, mv, rm          cp用于单个文件的复制,例如：  &nb…

    Linux干货 2016-12-13

  • N22-妙手-第四周博客作业

    1、复制/etc/skel目录为/home/tuser1，要求/home/tuser1及其内部文件的属组和其它用户均没有任何访问权限。 [root@localhost ~]# cp -r /etc/skel /home/tuser1 [root@localhost ~]# chmod&nb…

    Linux干货 2016-09-05
  • Iptables基础 Linux干货

    Iptables基础

       Firewall：防火墙，工作于主机或网络的边缘处，对于进出本主机或网络的报文根据事先定义好的检查规则左匹配检测，对于能够被规则所匹配到的报文进行相应的处理的组件。   防火墙可分为：硬件防火墙，在硬件级别实现部分功能的防火墙；软件防火墙，应用软件处理逻辑运行于通用硬件平台之上的防火墙；主机防火墙，服务范围为当前主机；网络…

    2017-06-19

  • 马哥教育网络班22期+第2周课程练习

    一、Linux上的文件管理类命令都有哪些，其常用的使用方法及其相关示例演示。     Linux上文件管理类命令常用的有：pwd、ls、cd、cp、touch、mv、rm、rmdir （1）pwd：显示当前路径,打印工作目录(Print Working Directory) （2）ls：查看指定目录下的内容 参数 说明 -a 列举目录中的…

    Linux干货 2016-08-29

  • 自制Linux系统

    自制Linux系统 1.首选准备一台Linux虚拟机并添加一块新硬盘 2.分区并创建文件系统，创建两个必要的分区根分区和boot分区 fdisk /dev/sdb  分区 mke2fs -t ext4 /dev/sdb1    创建boot文件系统 mke2f…

    Linux干货 2016-09-16
电话咨询
在线咨询