MySQL常用命令(一)

1. 常用的显示命令

查看数据集： show character set;
查看排序规则：show collation;
查看已用的数据库：show databases;
查看表的结构：desc tb_name;
显示表的描述信息：show table status like ‘tb1’; 或者 show table status like ‘tb1’\G（以列的形式显示）
显示表的索引： show indexes from tb1;或者show indexes from tb1\G（以列的形式显示）
显示表的创建语句：show CREATE TABLE tb_name;

2 DDL (Database Definition Language)

2.1 创建数据库（help create database;）

格式：create DATABASE|SCHEMA IF NOT EXISTS nuist CHARACTER SET ‘gbk’ COLLATE ‘gbk_chinese_ci’;
创建数据库的本质就是在对应的Data目录下创建一个nuist目录，nuist目录下有一个db.opt的文件保存的就是指定的字符集和排序规则，内容如下：

default-character-set=gbk
default-collation=gbk_chinese_ci

2.2 修改数据库（修改字符集或者排序规则）(Help alter database;)

mysql> help alter database;
Name: 'ALTER DATABASE'
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name] alter_specification ...
ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name| [DEFAULT] COLLATE [=] collation_name

ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.

2.3 删除数据库（help drop database;）

2.4 创建表

2.4.1 直接定义一张空表

create table tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name char(20) not null,age TINYINT not null);
create table tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT ,name char(20) not null,age TINYINT not null, primary key(id),unique key(name),index(age));

唯一键、主键、索引的区别？键也称作约束，可用作索引，是特殊的索引（有特殊限定），都是B+Tree结构的形式，可以使用create index命令单独创建索引。

2.4.2 从其他表中查询数据，并以之创建新表

create table other_agents select * from agents where AgentNo > 100; //注意：这种方式创建表，不会继承之前表的属性。

2.4.3 以其他表为模板创建一张新表

create table other_agents2 like agents; //注意：表格式的属性完全和之前的一样

显示表的描述信息：show table status like ‘tb1’; 或者 show table status like ‘tb1’\G
显示表的索引： show indexes from tb1;或者show indexes from tb1\G
显示表结构： desc other_agents;

2.4.4 指定索引

① 单字段： PRIMARY KEY，UNIQUE KEY
② 单或多字段：PRIMARY KEY(col, …)，UNIQUE KEY(col,…)，INDEX（col,…）

2.5 修改表 (help alter table ;)

2.6 删除表 (help drop table;)

2.7 索引 (help drop table;)

索引只能不创建、查看和删除，不能被修改

• 创建索引：(help create index;)create index index_name on tb_name(col_name,…);
• 查看索引：show indexes from tb_name;
• 删除索引：(help drop index;),DROP INDEX index_name ON tbl_name

3. DML(Database Manipulation Language)

3.1 Select

mysql> help select;
Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [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}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

3.1.1 简单查询语句

select field1,field2,field3 fromt tb_name;  //投影
select * from tb_name where conditions;     //选择

from子句：表示要查询的关系， 表、多个表、其他SELECT语句
where子句：布尔表达式，=，>，>=，<，<=，<>,!=,<=>
逻辑关系：AND，OR，NOT
特殊操作符

• between … and …， like (其中，%表示任意长度，任意字符；_表示任意单个字符)
• rlike，regexp，支持正则化比较；RLIKE ‘^[m,n,y].*$’，注意此时索引就失效了。
• in，作离散取值；
• is null，is not null：和null做比较；
• 
• oder by [ASC|DESC]，将查询后的结果进行排序
• as 字段别名
• limit 显示有限的行，limit NUMBER表示显示前NUMBER个；limit NUM1, NUM2，表示从NUM1开始取NUM2个。

聚合函数: AVG()、 MAX()、MIN()、SUM()、COUNT()
GROUP BY: 根据条件进行分组
Having qualification： 要Group by结合使用，用于对Group by的结果进行过滤。

3.1.2 多表查询

连接方式：

• 交叉连接，笛卡尔乘积
• 自然连接（内连接），仅仅保留具有等值关系的行。
• 外连接之左连接：tb_name1 left jion tb_name2 on conditions
• 外连接之右连接: tb_name1 right jion tb_name2 on conditions
• 自连接

3.1.3 子查询

• 在比较操作中使用子查询：子查询的结果一定要是一个单值，否则会出错；
• 在in中使用子查询：子查询的结果可以是一个集合；
• 在from中使用子查询；
• 联合查询：union；

3.1.4 视图

视图

视图就是存储下来的Select语句，基于基表的查询结果。一般不建议直接向视图中插入或者更新数据，视图一般称之为虚表。

创建视图： (help create view；)
删除视图： (help drop view；)
注意：MySQL不支持物化视图，也不建议在MySQL中使用视图；

3.2 Delete

mysql> help delete
Name: 'DELETE'
Description:
Syntax:
Single-table syntax:

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

清空表：truncate tb_name，能够清空表，并重置Autoincrement计数器。

3.3 Insert into

insert into tb_name (col1,col2,…) values(val1,val2,…),[,(val1,val2,…),…]
在有些场景下，批量插入可以提高性能；
注意插入的格式：字符型的值需要用单引号引起来，数值型不需要引号，日期时间型也不需要引号，空值要写成null，”。
查看插入帮助：help insert

mysql> help insert
Name: 'INSERT'
Description:
Syntax:
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] ... ]

3.4 UPDATE

mysql> help update
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:

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