一、登陆和退出

1. 远程主机

shell> mysql -h host -u user -p

Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql>

2. 登陆本机

shell> mysql -u user -p

3. 退出mysql

mysql> QUIT

二、输入查询

查询版本号和当前日期

mysql> SELECT VERSION(), CURRENT_DATE;

+-----------+--------------+

| VERSION() | CURRENT_DATE |

+-----------+--------------+

| 5.7.24    | 2018-11-12   |

+-----------+--------------+

1 row in set (0.01 sec)

mysql>

大小写不敏感

把mysql当计算器用

mysql> SELECT SIN(PI()/4), (4+1)*5;

+--------------------+---------+

| SIN(PI()/4)        | (4+1)*5 |

+--------------------+---------+

| 0.7071067811865476 |      25 |

+--------------------+---------+

1 row in set (0.01 sec)

在一行上输入多个语句。分号结束每一个命令:

mysql> SELECT VERSION(); SELECT NOW();

+-----------+

| VERSION() |

+-----------+

| 5.7.24    |

+-----------+

1 row in set (0.00 sec)

+---------------------+

| NOW()               |

+---------------------+

| 2018-11-13 00:10:14 |

+---------------------+

1 row in set (0.00 sec)

mysql 通过 查找终止分号;来确定语句的结束位置

mysql> SELECT

    -> USER()

    -> ,

    -> CURRENT_DATE;

+----------------+--------------+

| USER()         | CURRENT_DATE |

+----------------+--------------+

| root@localhost | 2018-11-13   |

+----------------+--------------+

1 row in set (0.00 sec)

mysql> SELECT USER(),CURRENT_DATE;

+----------------+--------------+

| USER()         | CURRENT_DATE |

+----------------+--------------+

| root@localhost | 2018-11-13   |

+----------------+--------------+

1 row in set (0.00 sec)

通过键入以下内容取消该查询:\c

mysql> SELECT

    -> USER()

    -> \c

三、创建和使用数据库

展示存在的数据库

mysql> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| falcon             |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

使用数据库

mysql> USE test

Database changed

shell> mysql -h host -u user -p menagerie

Enter password: ********

3.1 创建和选择数据库

mysql> CREATE DATABASE menagerie;

mysql> USE menagerie

Database changed

3.2 创建表

mysql> SHOW TABLES;

Empty set (0.00 sec)

创建表

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

展示表单：

mysql> SHOW TABLES;

+---------------------+

| Tables_in_menagerie |

+---------------------+

| pet                 |

+---------------------+

1 row in set (0.00 sec)

描述表单

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)

3.3 将数据加载到表中

建立表格以后需要：

• 导入数据：LOAD DATA
• 插入数据：INSERT

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

保存为pet.txt

从excel中直接复制到pet.txt,空值用\N 表示

Whistler        Gwen    bird    \N      1997-12-09      \N

3.3.1 导入数据

LOAD DATA LOCAL INFILE 'D:/Temp/pet.txt' INTO TABLE pet;

win编辑器

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet

    -> LINES TERMINATED BY '\r\n';

在运行 os x 的 apple 计算机上, 您可能想要使用。LINES TERMINATED BY '\r'

3.3.2 插入语句

mysql> INSERT INTO pet

    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

3.4 操作表格

SELECT 语法：

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

3.4.1 查询所有数据

mysql> SELECT * FROM pet;

+----------+--------+---------+------+------------+------------+

| name     | owner  | species | sex  | birth      | death      |

+----------+--------+---------+------+------------+------------+

| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |

| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |

| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |

| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |

| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |

| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |

| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |

| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |

| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |

+----------+--------+---------+------+------------+------------+

update 语句修复错误记录 :

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

3.4.2 查询某行

选择特定行:

mysql> SELECT * FROM pet WHERE name = 'Bowser';

+--------+-------+---------+------+------------+------------+

| name   | owner | species | sex  | birth      | death      |

+--------+-------+---------+------+------------+------------+

| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

+--------+-------+---------+------+------------+------------+

选择特定条件：

mysql>  SELECT * FROM pet WHERE birth >= '1998-1-1';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | 0000-00-00 |

| Puffball | Diane | hamster | f    | 1999-03-30 | NULL       |

+----------+-------+---------+------+------------+------------+

条件and：

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

+-------+--------+---------+------+------------+------------+

| name  | owner  | species | sex  | birth      | death      |

+-------+--------+---------+------+------------+------------+

| Buffy | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |

+-------+--------+---------+------+------------+------------+

条件or：

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | 0000-00-00 |

| Whistler | Gwen  | bird    |      | 1997-12-09 | 0000-00-00 |

| Slim     | Benny | snake   | m    | 1996-04-29 | 0000-00-00 |

+----------+-------+---------+------+------------+------------+

and 、 or 混合使用：

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')

    -> OR (species = 'dog' AND sex = 'f');

+-------+--------+---------+------+------------+------------+

| name  | owner  | species | sex  | birth      | death      |

+-------+--------+---------+------+------------+------------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |

| Buffy | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |

+-------+--------+---------+------+------------+------------+

3.4.3 查询某列

选择某列，按照 ，分隔

mysql> SELECT name`,` birth FROM pet;

+----------+------------+

| name     | birth      |

+----------+------------+

| name     | 0000-00-00 |

| Fluffy   | 1993-02-04 |

| Claws    | 1994-03-17 |

| Buffy    | 1989-05-13 |

| Fang     | 1990-08-27 |

| Bowser   | 1989-08-31 |

| Chirpy   | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim     | 1996-04-29 |

| Puffball | 1999-03-30 |

+----------+------------+

选择动物主人：

mysql> SELECT owner FROM pet;

+--------+

| owner  |

+--------+

| owner  |

| Harold |

| Gwen   |

| Harold |

| Benny  |

| Diane  |

| Gwen   |

| Gwen   |

| Benny  |

| Diane  |

+--------+

使用DISTINCT去重

mysql> SELECT DISTINCT owner FROM pet;

+--------+

| owner  |

+--------+

| owner  |

| Harold |

| Gwen   |

| Benny  |

| Diane  |

+--------+

使用WHERE条件查询

mysql> SELECT name, species, birth FROM pet

    -> WHERE species = 'dog' OR species = 'cat';

+--------+---------+------------+

| name   | species | birth      |

+--------+---------+------------+

| Fluffy | cat     | 1993-02-04 |

| Claws  | cat     | 1994-03-17 |

| Buffy  | dog     | 1989-05-13 |

| Fang   | dog     | 1990-08-27 |

| Bowser | dog     | 1989-08-31 |

+--------+---------+------------+

3.4.4 排序行

使用ORDER BY ,默认升序

mysql>  SELECT name, birth FROM pet ORDER BY birth;

+----------+------------+

| name     | birth      |

+----------+------------+

| name     | 0000-00-00 |

| Buffy    | 1989-05-13 |

| Bowser   | 1989-08-31 |

| Fang     | 1990-08-27 |

| Fluffy   | 1993-02-04 |

| Claws    | 1994-03-17 |

| Slim     | 1996-04-29 |

| Whistler | 1997-12-09 |

| Chirpy   | 1998-09-11 |

| Puffball | 1999-03-30 |

+----------+------------+

默认不区分大小写排序，如果要区分使用：

ORDER BY BINARY col_name

BINARY：转换为二进制字符,方便排序

使用ORDER BY ,默认升序，如果降序，添加DESC

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

+----------+------------+

| name     | birth      |

+----------+------------+

| Puffball | 1999-03-30 |

| Chirpy   | 1998-09-11 |

| Whistler | 1997-12-09 |

| Slim     | 1996-04-29 |

| Claws    | 1994-03-17 |

| Fluffy   | 1993-02-04 |

| Fang     | 1990-08-27 |

| Bowser   | 1989-08-31 |

| Buffy    | 1989-05-13 |

| name     | 0000-00-00 |

+----------+------------+

多个列排序：

先按照动物种类升序，然后按照出生日期降序

mysql> SELECT name, species, birth FROM pet

    -> ORDER BY species, birth DESC;

+----------+---------+------------+

| name     | species | birth      |

+----------+---------+------------+

| Chirpy   | bird    | 1998-09-11 |

| Whistler | bird    | 1997-12-09 |

| Claws    | cat     | 1994-03-17 |

| Fluffy   | cat     | 1993-02-04 |

| Fang     | dog     | 1990-08-27 |

| Bowser   | dog     | 1989-08-31 |

| Buffy    | dog     | 1989-05-13 |

| Puffball | hamster | 1999-03-30 |

| Slim     | snake   | 1996-04-29 |

| name     | species | 0000-00-00 |

+----------+---------+------------+

3.4.5 ‎日期计算

TIMESTAMPDIFF()：时间差函数

mysql> SELECT TIMESTAMPDIFF(YEAR,'1989-08-31',CURDATE());

+--------------------------------------------+

| TIMESTAMPDIFF(YEAR,'1989-08-31',CURDATE()) |

+--------------------------------------------+

|                                         29 |

+--------------------------------------------+

常用时间函数：

mysql> SELECT NOW(),CURDATE(),CURTIME();

+---------------------+------------+-----------+

| NOW()               | CURDATE()  | CURTIME() |

+---------------------+------------+-----------+

| 2018-11-13 10:21:28 | 2018-11-13 | 10:21:28  |

+---------------------+------------+-----------+

查询显示每个宠物的出生日期、当前日期，用age做别名

mysql> SELECT name, birth, CURDATE(),

    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

    -> FROM pet;

+----------+------------+------------+------+

| name     | birth      | CURDATE()  | age  |

+----------+------------+------------+------+

| Fluffy   | 1993-02-04 | 2018-11-13 |   25 |

| Claws    | 1994-03-17 | 2018-11-13 |   24 |

| Buffy    | 1989-05-13 | 2018-11-13 |   29 |

| Fang     | 1990-08-27 | 2018-11-13 |   28 |

| Bowser   | 1989-08-31 | 2018-11-13 |   29 |

| Chirpy   | 1998-09-11 | 2018-11-13 |   20 |

| Whistler | 1997-12-09 | 2018-11-13 |   20 |

| Slim     | 1996-04-29 | 2018-11-13 |   22 |

| Puffball | 1999-03-30 | 2018-11-13 |   19 |

+----------+------------+------------+------+

查询同时 按照姓名排序

mysql> SELECT name, birth, CURDATE(),

    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

    -> FROM pet ORDER BY name;

+----------+------------+------------+------+

| name     | birth      | CURDATE()  | age  |

+----------+------------+------------+------+

| Bowser   | 1989-08-31 | 2018-11-13 |   29 |

| Buffy    | 1989-05-13 | 2018-11-13 |   29 |

| Chirpy   | 1998-09-11 | 2018-11-13 |   20 |

| Claws    | 1994-03-17 | 2018-11-13 |   24 |

| Fang     | 1990-08-27 | 2018-11-13 |   28 |

| Fluffy   | 1993-02-04 | 2018-11-13 |   25 |

| Puffball | 1999-03-30 | 2018-11-13 |   19 |

| Slim     | 1996-04-29 | 2018-11-13 |   22 |

| Whistler | 1997-12-09 | 2018-11-13 |   20 |

+----------+------------+------------+------+

查询同时 按照年龄排序

mysql> SELECT name, birth, CURDATE(),

    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

    -> FROM pet ORDER BY age;

+----------+------------+------------+------+

| name     | birth      | CURDATE()  | age  |

+----------+------------+------------+------+

| Puffball | 1999-03-30 | 2018-11-13 |   19 |

| Chirpy   | 1998-09-11 | 2018-11-13 |   20 |

| Whistler | 1997-12-09 | 2018-11-13 |   20 |

| Slim     | 1996-04-29 | 2018-11-13 |   22 |

| Claws    | 1994-03-17 | 2018-11-13 |   24 |

| Fluffy   | 1993-02-04 | 2018-11-13 |   25 |

| Fang     | 1990-08-27 | 2018-11-13 |   28 |

| Buffy    | 1989-05-13 | 2018-11-13 |   29 |

| Bowser   | 1989-08-31 | 2018-11-13 |   29 |

+----------+------------+------------+------+

查询已经死亡的宠物

把默认导入的数值从'0000-00-00' 改成'NUll'

关于NULL

UPDATE pet SET death = NULL WHERE death= 0000-00-00;

mysql> SELECT name, birth, death,

    -> TIMESTAMPDIFF(YEAR,birth,death) AS age

    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

+--------+------------+------------+------+

| name   | birth      | death      | age  |

+--------+------------+------------+------+

| Bowser | 1989-08-31 | 1995-07-29 |    5 |

+--------+------------+------------+------+

MySQL 的一些日期函数：

• YEAR()：返回年

• MONTH()：返回月

• DAYOFMONTH()：返回日

• DATE_ADD() ：添加时间
  
  ​

mysql> SELECT YEAR('1987-01-01');

+--------------------+

| YEAR('1987-01-01') |

+--------------------+

|               1987 |

+--------------------+

mysql> SELECT MONTH('2008-02-03');

+---------------------+

| MONTH('2008-02-03') |

+---------------------+

|                   2 |

+---------------------+

mysql> SELECT MONTHNAME('2008-02-03');

+-------------------------+

| MONTHNAME('2008-02-03') |

+-------------------------+

| February                |

+-------------------------+

现在是今年的 多少天 多少周

mysql> SELECT DAYOFYEAR(now()),WEEKOFYEAR(now());

+------------------+-------------------+

| DAYOFYEAR(now()) | WEEKOFYEAR(now()) |

+------------------+-------------------+

|              317 |                46 |

+------------------+-------------------+

2008-01-03第多少天，多少周

mysql> SELECT DAYOFYEAR('2008-01-03'),WEEKOFYEAR('2008-01-03');

+-------------------------+--------------------------+

| DAYOFYEAR('2008-01-03') | WEEKOFYEAR('2008-01-03') |

+-------------------------+--------------------------+

|                       3 |                        1 |

+-------------------------+--------------------------+

查询宠物出生月

mysql> SELECT name, birth, MONTH(birth) FROM pet;

+----------+------------+--------------+

| name     | birth      | MONTH(birth) |

+----------+------------+--------------+

| Fluffy   | 1993-02-04 |            2 |

| Claws    | 1994-03-17 |            3 |

| Buffy    | 1989-05-13 |            5 |

| Fang     | 1990-08-27 |            8 |

| Bowser   | 1989-08-31 |            8 |

| Chirpy   | 1998-09-11 |            9 |

| Whistler | 1997-12-09 |           12 |

| Slim     | 1996-04-29 |            4 |

| Puffball | 1999-03-30 |            3 |

+----------+------------+--------------+

查询5月出生的宠物

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

+-------+------------+

| name  | birth      |

+-------+------------+

| Buffy | 1989-05-13 |

+-------+------------+

2018-05-01 添加1天

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);

+---------------------------------------+

| DATE_ADD('2018-05-01',INTERVAL 1 DAY) |

+---------------------------------------+

| 2018-05-02                            |

+---------------------------------------+

查询下个月是哪个月

mysql> SELECT  MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

+---------------------------------------------+

| MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)) |

+---------------------------------------------+

|                                          12 |

+---------------------------------------------+

查询即将生日的宠物，距离目前最近的一个月

• 方法1：当前月份+1个月

mysql> SELECT name, birth FROM pet

    ->  WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

+----------+------------+

| name     | birth      |

+----------+------------+

| Whistler | 1997-12-09 |

+----------+------------+

• 方法2：当前月份取余+1

mysql> SELECT name, birth FROM pet

    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

+----------+------------+

| name     | birth      |

+----------+------------+

| Whistler | 1997-12-09 |

+----------+------------+

• MOD(12, 5)

mysql> SELECT MOD(12, 5);

+------------+

| MOD(12, 5) |

+------------+

|          2 |

+------------+

3.4.6 使用空值

NULL value ：a missing unknown value，要和其他值区别对待

测试NULL，使用

• IS NULL
• IS NOT NULL

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

+-----------+---------------+

| 1 IS NULL | 1 IS NOT NULL |

+-----------+---------------+

|         0 |             1 |

+-----------+---------------+

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

+-----------+---------------+------------+----------------+

| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |

+-----------+---------------+------------+----------------+

|         0 |             1 |          0 |              1 |

+-----------+---------------+------------+----------------+

不能使用算术笔记用算符判断null

=, <, or <>

3.4.7 匹配方式

1. 标准的 sql 模式匹配
2. 正则表达式的模式匹配
3. 默认不区分大小写

标准匹配

使用：

• LIKE

• NOT LIKE

查找b开头的名字：

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

+--------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth      | death      |

+--------+--------+---------+------+------------+------------+

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |

| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

查找fy结尾的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |

查找名字包含一个w:

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

查找名字是5个字符的：

mysql> SELECT * FROM pet WHERE name LIKE '_____';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

正则匹配

使用：

• REGEXP
• NOT REGEXP

RLIKE NOT RLIKE 和上述等价

查找b开头的名字：

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

+--------+--------+---------+------+------------+------------+

| name   | owner  | species | sex  | birth      | death      |

+--------+--------+---------+------+------------+------------+

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |

| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

若要强制regexp比较区分大小写, 请使用binary关键字将其中一个字符串设置为二进制字符串。

匹配名称由小写:b开头

SELECT * FROM pet WHERE name REGEXP BINARY '^b';

查找fy结尾的名字：

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

+--------+--------+---------+------+------------+-------+

| name   | owner  | species | sex  | birth      | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |

| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |

+--------+--------+---------+------+------------+-------+

查找名字包含一个w:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

+----------+-------+---------+------+------------+------------+

| name     | owner | species | sex  | birth      | death      |

+----------+-------+---------+------+------------+------------+

| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |

| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |

+----------+-------+---------+------+------------+------------+

查找名字是5个字符的

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

或者^.{5}$ ：^开头$结尾

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

+-------+--------+---------+------+------------+-------+

| name  | owner  | species | sex  | birth      | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |

| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |

+-------+--------+---------+------+------------+-------+

3.4.8 统计行

COUNT(*) 统计函数

统计行数

mysql> SELECT COUNT(*) FROM pet;

+----------+

| COUNT(*) |

+----------+

|        9 |

+----------+

统计每个人有多少只宠物

mysql>  SELECT owner, COUNT(*) FROM pet GROUP BY owner;

+--------+----------+

| owner  | COUNT(*) |

+--------+----------+

| Benny  |        2 |

| Diane  |        2 |

| Gwen   |        3 |

| Harold |        2 |

+--------+----------+

统计每种动物的数量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

+---------+----------+

| species | COUNT(*) |

+---------+----------+

| bird    |        2 |

| cat     |        2 |

| dog     |        3 |

| hamster |        1 |

| snake   |        1 |

统计物种性别：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

+------+----------+

| sex  | COUNT(*) |

+------+----------+

| NULL |        1 |

| f    |        4 |

| m    |        4 |

+------+----------+

物种和性别的组合：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

+---------+------+----------+

| species | sex  | COUNT(*) |

+---------+------+----------+

| bird    | NULL |        1 |

| bird    | f    |        1 |

| cat     | f    |        1 |

| cat     | m    |        1 |

| dog     | f    |        1 |

| dog     | m    |        2 |

| hamster | f    |        1 |

| snake   | m    |        1 |

+---------+------+----------+

只针对某个字段筛选，不用筛选整行

选择cat 和 dog种类，按照性别物种组合

mysql> SELECT species, sex, COUNT(*) FROM pet

    -> WHERE species = 'dog' OR species = 'cat'

    -> GROUP BY species, sex;

+---------+------+----------+

| species | sex  | COUNT(*) |

+---------+------+----------+

| cat     | f    |        1 |

| cat     | m    |        1 |

| dog     | f    |        1 |

| dog     | m    |        2 |

+---------+------+----------+

列出已知动物性别：

mysql> SELECT species, sex, COUNT(*) FROM pet

    -> WHERE sex IS NOT NULL

    -> GROUP BY species, sex;

+---------+------+----------+

| species | sex  | COUNT(*) |

+---------+------+----------+

| bird    | f    |        1 |

| cat     | f    |        1 |

| cat     | m    |        1 |

| dog     | f    |        1 |

| dog     | m    |        2 |

| hamster | f    |        1 |

| snake   | m    |        1 |

+---------+------+----------+

ONLY_FULL_GROUP_BY

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';-- 启用

mysql> SELECT owner, COUNT(*) FROM pet;

ERROR 1140 (42000):

ONLY_FULL_GROUP_BY 关闭后，结果不确定，系统随意添加一个值，最好关闭

mysql> SET sql_mode = '';   -- 关闭

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;

+--------+----------+

| owner  | COUNT(*) |

+--------+----------+

| Harold |        9 |

+--------+----------+

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

+--------+----------+

| owner  | COUNT(*) |

+--------+----------+

| Benny  |        2 |

| Diane  |        2 |

| Gwen   |        3 |

| Harold |        2 |

+--------+----------+

3.4.9 使用多个表

此表格跟踪记录你的宠物，当你想记录他们的信息，比如看兽医、生幼崽、买玩具等事件，就需要另外一个表单记录，需要如下信息：

• 宠物名称：name
• 事件日期：date
• 事件类型：type
• 事件描述：emark

CREATE ABLE

创建表一个符合上述要素的表单:

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,

    -> type VARCHAR(15), remark VARCHAR(255));

将下述内容存储为event.txt

name	date	type	remark
Fluffy	1995-05-15	litter	4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter	5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter	3 puppies, 3 female
Chirpy	1999-03-21	vet	needed beak straightened
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	Gave him a new chew toy
Claws	1998-03-17	birthday	Gave him a new flea collar
Whistler	1998-12-09	birthday	First birthday

导入内容到表单

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

假设：想知道每个宠物下崽时的年龄，宠物妈妈下崽时间在event表单，但是，出生日期在pet表单，所以要同时查询2个表单。

mysql> SELECT pet.name,

    -> TIMESTAMPDIFF(YEAR,birth,date) AS age,

    -> remark

    -> FROM pet INNER JOIN event

    ->   ON pet.name = event.name

    -> WHERE event.type = 'litter';

+--------+------+------------------------------+

| name   | age  | remark                       |

+--------+------+------------------------------+

 |Fluffy |    2 | 4 kittens, 3 female, 1 male

 |Buffy  |    4 | 5 puppies, 2 female, 3 male

         |    5 | 3 puppies, 3 female

+--------+------+------------------------------+

jion表单:

• 是为从两张表获取信息
• 需要指定匹配规则，上述例子都有'name'
• 需要指定前缀ON pet.name = event.name

表的自身连接

宠物的雌雄配对：

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

    -> FROM pet AS p1 INNER JOIN pet AS p2

    ->    ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

+--------+------+--------+------+---------+

| name   | sex  | name   | sex  | species |

+--------+------+--------+------+---------+

| Fluffy | f    | Claws  | m    | cat     |

| Buffy  | f    | Fang   | m    | dog     |

| Buffy  | f    | Bowser | m    | dog     |

+--------+------+--------+------+---------+

3.4 获取数据库和表的信息

• SHOW DATABASES
• DATABASE()

查看目前使用的数据库

mysql> SELECT DATABASE();

+------------+

| DATABASE() |

+------------+

| menagerie  |

+------------+

查看表

mysql> SHOW TABLES;

+---------------------+

| Tables_in_menagerie |

+---------------------+

| event               |

| 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    |       |

+---------+-------------+------+-----+---------+-------+

3.5 批处理模式

在mysql内运行脚本：

mysql> source filename;

mysql> \. filename

来源：MySQL官网

最新文章

1. music player界面
2. pair<>结构体模版的用法
3. (IOS)Swift Music 程序分析
4. Shiro源码分析-初始化-Realm
5. Centos7 install Openstack - (第三节)添加镜像服务(Glance)
6. uva202：循环小数（循环节+抽屉原理）
7. 8款替代Dreamweaver的开源网页开发工具
8. Excel 用row()函数 在Excel中自动添加序号,
9. ulua c#调用lua中模拟的类成员函数
10. jmeter 压力测试安装教程
11. JavaScript获取扫码枪相关资料
12. 洛谷 P1538 迎春舞会之数字舞蹈
13. Spark学习之路 （十）SparkCore的调优之Shuffle调优
14. 火车时刻表WebApp
15. SharePoint REST API - 基本操作（一）
16. Java 3-Java 基本数据类型
17. BZOJ4326或洛谷2680 运输计划
18. ubuntu安装Anaconda2-4.4.0+TensorFlow
19. GTD实践2周年后一些体会
20. 20165332实验四 Android开发基础

热门文章

1. MySQL之视图、触发器、事务、存储过程、函数
2. ubuntu 16.04 LTS - 谷歌拼音输入法
3. mongodb安装4.0（rpm）
4. Golang 入门系列(十) mysql数据库的使用
5. 深入剖析Redis系列： Redis集群模式搭建与原理详解
6. openstack安装过程遇到的问题
7. [Alpha阶段]第四次Scrum Meeting
8. React Native之支付集成(微信 支付宝)(ios android)
9. DAY12、装饰器
10. js对象取值的两种方式