MySQL语法看这一篇就够啦

这一篇文章主要从功能出发来描述MySQL的基本用途~可以作为大家以后查找语法时候用呀！
目前进度在70%左右，还有一些功能还没补充上来~

1. MySQL介绍与登录

1.1 MySQL介绍

MySQL数据库的层次结构可以如下描述：[库 - 表 - 列 - 值]

-- database1
	|
	|__  table1
			|----------------------------------
			|__ col1  | col2  | col3  | col4  |
			-----------------------------------
			|__ val11 | val21 | val31 | val41 | 
			-----------------------------------
			|__ val12 | val22 | val32 | val42 | 
			-----------------------------------        
		

1.2 Mariadb安装与启动

个人开发者可以使用 MariaDB 代替MySQL，MariaDB 数据库管理系统是 MySQL 的一个分支，主要由开源社区在维护，采用 GPL 授权许可。在Linux下安装方便，无需额外配置。

1.2.1 在centos7环境下安装启动

• centos环境使用yum，如果本地有残留冲突，添加--allowerasing表示允许先卸载本地环境

# 常规安装
yum -y install mariadb-server mariadb

# 当本地有Mysql残留冲突时候
yum -y install mariadb-server mariadb --allowerasing

• 启动

systemctl start mariadb  #启动MariaDB
systemctl stop mariadb  #停止MariaDB
systemctl restart mariadb  #重启MariaDB
systemctl enable mariadb  #设置开机启动

• 查看版本与设置密码

mysqladmin --version

mysqladmin -u root password "new_password"

登录

• mysql -u用户名 -p
• 密码输入时不显示字符，放心输入然后回车即可

> mysql -uroot -p
> Enter password:

• 显式的输入密码 mysql -u用户名 -p密码

> mysql -uroot -p123456

---

数据库操作部分

增删改查：增 CREATE DATABASE

CREATE DATABASE 数据库名;
CREATE database if not exists 数据库名;

增删改查：删 DROP

drop database 数据库名;
Drop database if exists 数据库名；

增删改查：查找数据库 SHOW DATABASE

# 查看所有的数据库
SHOW DATABASES;

使用数据库：use

use 数据库名;

查看数据库中的数据表 show tables

# 默认显示当前数据库中的表;
SHOW TABLES;

# 查看任意数据库的表
SHOW TABLES FROM 数据库名;

---

数据表单与表列操作部分

增删改查：增 CREATE TABLE

新增数据表的命令可以分成如下几个代码快：引导头，表单列表，配置表引擎。
CREATE TABLE if not exists table_name (column_name column_type) 配置表单引擎;
其中：

1. 引导头

CREATE TABLE IF NOT EXISTS 表名是引导头

2. 表单列表配置

(column_name, column_type)是表单列表配置
表单可配置内容又可以分为如下结果部分： 列表名， 列数据类型，是否自增，是否为空, 是否带默认值，设置主键。

• 除了列名和列数据类型，其他的配置参数的关键词及用法如下表示
  • 自增标识符 AUTO_INCREMENT
• 不可为空标识符 NOT NULL
• 设置默认值 DEFAULT 值
• 设置主键，通常独立于(col_name, col_type)，PRIMARY KEY (列x, 列y, ...)

下面举几个栗子~
runoob_id INT UNSIGNED AUTO_INCREMENT,
runoob_title VARCHAR(100) NOT NULL,
runoob_author VARCHAR(40) NOT NULL,
submission_date DATE,

3. 表单引擎

表单引擎：ENGINE=InnoDB DEFAULT CHARSET=utf8;

增删改查：删 DROP TABLE

DROP TABLE 表单名;
drop table if exists 表单名;

增删改查：改 ALTER TABLE

1. 修改表名 RENAME TO

ALTER TABLE 旧表单名 RENAME TO 新表单名;

2. 新增表单的列表配置 ADD

在上文增加数据表时候提到过表单列表配置，使用alter命令进行配置的时候，可以补充任意表单可配置内容。

ALTER TABLE 表单名 ADD 列名 列类型 表单配置选项; # 默认添加在最后一列
ALTER TABLE 表单名 ADD 列名 列类型 表单配置选项 FIRST; # 添加在第一列

# 添加在已存在列表后面
ALTER TABLE 表单名 ADD 列名 列类型 表单配置选项 AFTER 已存在列表名;

3. 删除表单的列配置 DROP

ALTER TABLE 表单名 DROP 列表名;

4. 修改表单的列配置 MODIFY / CHANGE

修改列的配置信息其实和新增类似，也科比补充任意列可配置内容

• modify

ALTER TABLE 表单名 MODIFY 列名 列类型 可选列配置;

• change

ALTER TABLE 表单名 CHANGE 旧列名 新列名 列类型 可选配置;

• 常见的修改疑问
  有待补充

增删改查：查 SHOW COLUMNS FROM

SHOW COLUMNS FROM 表单名;

显示结果举例

+------+----+----+--------+---------------------+--------+
| dd   | id | ii | name   | date                | signin |
+------+----+----+--------+---------------------+--------+
|    0 |  1 |  0 | 小明   | 2016-04-22 15:25:33 |      1 |
|    0 |  2 |  0 | 小王   | 2016-04-20 15:25:47 |      3 |
|    0 |  3 |  0 | 小丽   | 2016-04-19 15:26:02 |      2 |
|    0 |  4 |  0 | 小王   | 2016-04-07 15:26:14 |      4 |
|    0 |  5 |  0 | 小明   | 2016-04-11 15:26:40 |      4 |
|    0 |  6 |  0 | 小明   | 2016-04-04 15:26:54 |      2 |
+------+----+----+--------+---------------------+--------+

---

值操作（数据操作）

增删改查：增 INSERT INTO

# 基本语法
INSERT INTO 表单名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

# 按默认的列顺序可以不用输入列名
INSERT INTO 表单名 VALUES (值1, 值2, ...);

增删改查：删 DELETE FROM … WHERE

删除表单数据时候，如果没有where语句会将整个表单里的所有内容都删掉。所以谨慎使用啊兄弟姐妹们，最好是在嵌入式SQL中做更高一层的写判断保护。不过delete操作支持rollback。

# 删除表单所有数据！谨慎使用，最好在嵌入式SQL中做额外的写保护。
DELETE FROM 表单名;

# 根据where判断删除对应的表单值
DELETE FROM 表单名 [WHERE Clause]

增删改查：改 UPDATE … SET

• 使用update进行修改时，需要使用where语法进行选择正确的数据行，否则同列的所有值都会被修改，因此一定也需要进行写判断保护啊！

# 使用update参数对某一列的参数批量修改
UPDATE 表单名 SET 列1=表达式1, 列2=表达式2 [WHERE Clause]

增删改查：查 SELECT … FROM …

• 查找表的时候，通常可以用*来直接返回表单的所有数据，也可以指定部分用户关心的col来返回。
• 同样的，支持where语句进行筛选，注意，where筛选字符串时候默认不区分大小写的，要主动区分需要添加BINARY，where的更多用法在where专题详细介绍。也可以使用where和like进行组合实现更高级的匹配功能。
• 你可以使用 LIMIT 属性来设定返回的记录数。

# 返回表单所有数据
SELECT * FROM 表单名;

# 返回指定列表
SELECT 列名1, 列名2, ... FROM 表单名;

# where筛选
SELECT * from 表单名 WHERE BINARY author='Hugo';

# where筛选 + 指定列表
select id,name from 表单名 where id>3;

表单的统计类操作

表单排序 ORDER BY

• 表单的排序并不会打乱表单原始的组织结构，只是在返送返回值的时候，返送的元组是一个排序完成的形式。
• order by排序方式有升序和降序两种，升序为ASC（默认配置），降序为DESC

# 从多个表中选出多个列，并按照order by顺序排序。
SELECT field1, field2,...fieldN FROM table_name1, table_name2... 
-> ORDER BY field1 排序方式, [field2 排序方式]

SELECT * FROM 表单 ORDER BY 列表名 ASC;

表的事务

一般来说，事务是必须满足4个条件（ACID）~~ 原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。事务操作通常目的是为了闭环一个修改提交，类似于git，是一个回退式的设计理念。

• 简单的使用：BEGIN 开始一个事务，ROLLBACK 事务回滚，COMMIT 事务确认

BEGIN # 开始一个事务
# ... 一些增删改查操作
COMMIT # 事务确认

BEGIN # 开始一个事务
# ... 一些增删改查操作，操作错了
ROLLBACK # 事务回滚
COMMIT # 事务确认

• 复杂的介绍(用不到，估计只有笔试会用…)

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。
InnoDB 存储引擎提供事务的隔离级别有
READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

数据的导入导出

导出数据为txt和csv格式 INTO OUTFILE

• 这种导出方法仅导出数据内容，一般用于我们需要对数据库中的数据做额外的处理和量化的时候会使用到。因此我们一般会导出为txt或者csv格式的文件。
• 使用select语法选取要导出的列内容，这也就意味着，可以筛选多个表的多个列进行输出，更高级的select语法详见select语法介绍
• 再用into outfile输入想保存的路径。

# 默认导出的样式，用txt展示
SELECT * FROM 表单名 INTO OUTFILE '/tmp/data_file.txt';

# 导出结果展示
vim ./tmp/data_file.txt
0       1       0       小明    2016-04-22 15:25:33     1
0       2       0       小王    2016-04-20 15:25:47     3
0       3       0       小丽    2016-04-19 15:26:02     2
0       4       0       小王    2016-04-07 15:26:14     4
0       5       0       小明    2016-04-11 15:26:40     4
0       6       0       小明    2016-04-04 15:26:54     2

• 使用INTO OUTFILE 时候可以在生成格式field和lines上选取断句参数terminated by, enclosed by。通过命令选项来设置数据输出的指定格式。如以下命令就可以按照CSV格式的形式导出csv文件

SELECT * FROM 表单名 INTO OUTFILE '/tmp/user_count.csv'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

导出数据为mysql脚本

通常需要做数据库迁移的时候我们需要将数据导出成mysql脚本，进行迁移。

• 我们需要在终端环境下操作mysqldump
• 导出一个数据库mysqldump -u用户名 -p 数据库名 > 导出的文件名
• 导出一个表mysqldump -u用户名 -p 数据库名 表名 > 导出的文件
• 仅导出格式mysqldump -u用户名 -p -d 数据库名 表名 > 文件
• 导出整个数据库mysqldump -u用户名 -p --all-databases > 文件

# 全量导出，包括数据格式和数据内容
> mysqldump -uroot -p database_name tbl_name > dump.sql

# 仅导出数据格式（库信息和表信息）
> mysqldump -uroot -p -d database_name tbl_name > dump.sql

# 导出整个数据库
> mysqldump -uroot -p --all-databases > dump.sql

导入mysql脚本

由于我们保存的格式有完整的数据库和单张表两种形式的脚本，因此导入方式也可以分为两种。

• 导入数据库mysql -u用户名 -p密码 < 导入脚本
• 导入数据表mysql -u用户名 -p密码 数据库名 < 导入脚本

插入txt或者csv数据 INTO TABLE

• LOAD DATA LOCAL INFILE '文件名' INTO TABLE 表名 (列x, 列y, 列z, ...);
• 也可以根据导入文件格式需要，添加FILES LINES的格式处理命令

-> LOAD DATA LOCAL INFILE 'dump.txt' 
-> INTO TABLE mytbl (b, c, a);

-> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a)
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';

WHERE 条件筛选的用法

SELECT FROM 提取用法

SELECT a,b,a+b FROM test_table;