MySQL系列文章 - 02 基本语法1. 简单示例会按照上面的格式，详细的展示每个字段的名称及其值。就是看起来这样

原文链接 MySQL系列文章 - 02 基本语法

基本语句

1. 简单示例

开胃菜

SQL 中语句不区分大小写，例如

mysql> SELECT VERSION(), SIN(PI()/10), (10+3)*5, NOW(), CURRENT_DATE;
+-----------+--------------------+----------+---------------------+--------------+
| VERSION() | SIN(PI()/10)       | (10+3)*5 | NOW()               | CURRENT_DATE |
+-----------+--------------------+----------+---------------------+--------------+
| 5.7.20    | 0.3090169943749474 |       65 | 2017-12-08 14:26:03 | 2017-12-08   |
+-----------+--------------------+----------+---------------------+--------------+
1 row in set (0.00 sec)

如果 column 特别多，一行显示不完，折行后看起来杂乱，可以按字段展示，例如

mysql> SELECT VERSION(), SIN(PI()/10), (10+3)*5, NOW(), CURRENT_DATE\G;
*************************** 1. row ***************************
   VERSION(): 5.7.20
SIN(PI()/10): 0.3090169943749474
    (10+3)*5: 65
       NOW(): 2017-12-08 14:30:52
CURRENT_DATE: 2017-12-08
1 row in set (0.00 sec)

会按照上面的格式，详细的展示每个字段的名称及其值。

取消输入

如果输入了一部分语句需要取消，输入 \c即可

mysql> SELECT VERSION(), SIN(PI()/10), (10+3)*5, NOW(), CURRENT_DATE\c

就是看起来这样，什么都不会发生。

2. 数据库

查看数据库列表

mysql> SHOW DATABASES;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| mysql                 |
| performance_schema    |
| sys                   |
| test                  |
+-----------------------+
7 rows in set (0.01 sec)

选择 test 数据库

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

创建数据库 下面的命令将创建并选择 sample 数据库。

mysql> CREATE DATABASE `sample` DEFAULT CHARACTER SET = `utf8mb4`;
Query OK, 1 row affected (0.00 sec)

这里 character set 采用了 utf8mb4，方便以后类似 VARCHAR, LONGTEXT 字段使用 unicode 字符集。

比如要支持 emoji 的话，除了数据库和表的 CHARACTER SET 为 utf8mb4外，还需要将 MySQL 的参数配置中，修改

character_set_server = utf8mb4

创建数据库表

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| pet              |
+------------------+
1 row in set (0.00 sec)

查看 pet 表的结构

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

从文件中向表中载入数据。

mysql> LOAD DATA LOCAL INFILE '~/sample_pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\n'

如果不知道文件换号是什么特殊字符，可以用下面的方式来得到，需要 Python 支持

➜ python3
Python 3.6.3 (default, Nov  5 2017, 21:37:14)
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.38)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> f = open('1.txt', 'r')
>>> print(f)
<_io.TextIOWrapper name='1.txt' mode='r' encoding='UTF-8'>
>>> print(f.readlines())
['1\n', '2\n']

插入数据

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)

3. 从表中获取数据

SELECT *
FROM PET
WEHRE NAME != null;

4. 通过生日计算年龄

mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age -> FROM pet ORDER BY name;

5. 几种函数

YEAR() 获取年
MONTH() 获取月份
DAYOFMONTH() 获取天
DATE_ADD() 例如 DATE_ADD(CURDATE(), INTERVAL 1 MONTH) 当前日期加一个月

MOD 取模

mysql> SELECT MOD(13, 11);
+-------------+
| MOD(13, 11) |
+-------------+
|           2 |
+-------------+

6. NULL 相关

测试是否为 NULL

IS NULL
IS NOT NULL

不能使用 = < <> > 来对 NULL 做布尔判读。

在 GROUP BY 和 ORDER BY 语法中，两个 NULL 会被认为相等。

7. 模式匹配

SELECT * FROM pet WHERE name like 'b%';

a% 以 a 为前缀
a% 以 a 为后缀
_ 精确匹配 N 个字符，N 与 _ 的数量匹配；例如 like '___' 表示精确匹配 3 个字符

支持正则

SELECT * FROM pet WHERE name REGEXP  '^b';
SELECT * FROM pet WHERE name REGEXP  'b$';
SELECT * FROM pet WHERE name REGEXP  '.....$';  -- 点表示单个字符
SELECT * FROM pet WHERE name REGEXP  '.{5}$';

8. 计数

COUNT(*)

配合 GROUP BY 使用，可以统计每个 field 中，各类值的数量，比如

SELECT
	d.amount,
	count(d.amount)
FROM data d
GROUP BY d.amount;

|amount| count(d.amount)|
|------|----------------|
|  1000|              10|
|  1200|               9|

如果想不加 GROUP BY 就可以 SELECT filed_1, count(field_2) 需要将

SET SQL_MODE = 'ONLY_FULL_GROUP_BY';

改为

SET SQL_MODE = '';