mysql select 命令基础及存储引擎特点

1、 导入 hellodb.sql 生成数据库

• 准备备份文件 hellodb_innodb.sql

[root@localhost data]# ll
total 8
-rw-r--r-- 1 root root 7786 Mar 23 14:47 hellodb_innodb.sql

• 安装并启动 mariadb

yum install -y mariadb-server
systemctl start mariadb

• 恢复

mysql < hellodb_innodb.sql

• 验证数据

[root@localhost data]# mysql hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)

(1) 在 students 表中，查询年龄大于 25 岁，且为男性的同学的名字和年龄

MariaDB [hellodb]> select * from students where age>25 and gender='M';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    24 | Xu Xian      |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
7 rows in set (0.00 sec)

(2) 以 ClassID 为分组依据，显示每组的平均年龄

MariaDB [hellodb]> select classid as 班级ID,round(avg(age),0)  as 平均年龄 from students where classid is not null group by classid;
+----------+--------------+
| 班级ID   | 平均年龄       |
+----------+--------------+
|        1 |           21 |
|        2 |           36 |
|        3 |           20 |
|        4 |           25 |
|        5 |           46 |
|        6 |           21 |
|        7 |           20 |
+----------+--------------+
7 rows in set (0.00 sec)

(3) 显示第 2 题中平均年龄大于 30 的分组及平均年龄

MariaDB [hellodb]> select classid as 班级ID,round(avg(age),0)  as 平均年龄 from students where classid is not null group by classid having 平均年龄>30;
+----------+--------------+
| 班级ID   | 平均年龄       |
+----------+--------------+
|        2 |           36 |
|        5 |           46 |
+----------+--------------+
2 rows in set (0.00 sec)

(4) 显示以 L 开头的名字的同学的信息

MariaDB [hellodb]> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

2、数据库授权 solin 用户，允许 192.168.1.0/24 网段可以连接 mysql

# 用户 solin 已存在
MariaDB [hellodb]> grant all on testdb.* to solin@'192.168.1.%'

# 用户 Solin 不存在
MariaDB [hellodb]> grant all on testdb.* to solin@'192.168.1.%' identified by 'password';

3、总结 mysql 常见的存储引擎以及特点

（1）MyISAM

• 不支持事务；
• 表级锁定；
• 读写相互阻塞，写入不能读，读时不能写；
• 只缓存索引；
• 不支持外键约束；
• 不支持聚簇索引；
• 读取数据较快，占用资源较少；
• 不支持 MVCC（多版本并发控制机制）高并发；
• 崩溃恢复性较差；
• MySQL 5.5.5 前默认的数据库引擎；
• MyISAM 存储引擎适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）。

（2）InnoDB

• 行级锁；
• 支持事务，适合处理大量短期事务；
• 读写阻塞与事务隔离级别相关；
• 可缓存数据和索引；
• 支持聚簇索引；
• 崩溃恢复性更好；
• 支持 MVCC 高并发；
• 从 MySQL 5.5 后支持全文索引；
• 从 MySQL 5.5.5 开始为默认的数据库引擎；
• InnoDB 数据库文件：所有 InnoDB 表的数据和索引放置于同一个表空间中；每个表单独使用一个表空间存储表的数据和索引。

（3）Performance_Schema

Performance_Schema 数据库使用。

（4）Memory

将所有数据存储在 RAM 中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为 HEAP 引擎。

（5）MRG_MyISAM

使 MySQL DBA 或开发人员能够对一系列相同的 MyISAM 表进行逻辑分组，并将它们作为一个对象引用。适用于 VLDB(Very Large Data Base) 环境，如数据仓库。

（6）Archive

为存储和检索大量很少参考的存档或安全审核信息，只支持 SELECT 和 INSERT 操作；支持行级锁和专用缓存区。

（7）Federated 联合

用于访问其它远程 MySQL 服务器一个代理，它通过创建一个到远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独 MySQL 服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境。

（8）BDB

可替代 InnoDB 的事务引擎，支持 COMMIT、ROLLBACK 和其他事务特性。

（9）Cluster/NDB

MySQL 的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

（10）CSV

CSV 存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用 CSV 引擎以 CSV 格式导入和导出其他软件和应用程序之间的数据交换。

（11）BLACKHOLE

黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储。

（12）example

“stub” 引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎。