（1）在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
[root@centos7 ~]$systemctl start mariadb
[root@centos7 ~]$ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 128 [::]:22 [::]:*
[root@centos7 ~]$mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
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 [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> source hellodb_innodb.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> select name,age from students where age > 25 and gender=‘M‘;
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
+--------------+-----+（2）以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select classid, avg(age) from students group by ClassID;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> select classid, avg(age) from students group by ClassID having classid is not null;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set (0.00 sec)（3）显示第二题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select classid, avg(age) from students group by ClassID having avg(age)> 30 and classid is not null;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+（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、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql
MariaDB [hellodb]> grant all on *.* to magedu@‘192.168.1.%‘ identified by ‘centos‘;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select user,password,host from mysql.user;
+--------+-------------------------------------------+-------------------+
| user | password | host |
+--------+-------------------------------------------+-------------------+
| root | | localhost |
| root | | centos7.localhost |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | centos7.localhost |
| magedu | *128977E278358FF80A246B5046F51043A2B1FCED | 192.168.1.%3、总结mysql常见的存储引起以及特点
常用的MyISAM存储引擎和InnoDB存储引擎的特点
MyISAM存储引擎：
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读写数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据库引擎
- MyISM存储引擎适用场景
- 只读（或者写较少）
- 表较小（可以接受长时间进行修复操作）
- MyISAM引擎文件
- tbl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
InnoDB存储引擎：
- 行级锁
- 支持事务，适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
- InnoDB数据库文件
- 所有InnoDB表的数据和索引放置于同一个表空间中
- 表空间文件：datadir定义的目录下
- 数据文件：ibddata1，ibddata2，...
- 每个表单独使用一个表空间存储表的数据和索引
- 启用：innodb_file_per_table=ON
- 参考：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table
- ON (>= MariaDB 5.5)
- 两类文件存放在数据库独立目录中
- 数据文件（存储数据和索引）：tb_name.ibd
- 表格式定义：tb_name.frm
- 所有InnoDB表的数据和索引放置于同一个表空间中
其它存储引擎
- Performance_Schema：Performance_Schema数据库使用
Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库 - Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
- Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
- BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
- Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
- CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
- BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
- example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
MariaDB支持的其它存储引擎
* OQGraph
* SphinxSE
* TokuDB
* Cassandra
* CONNECT
* SQUENCE管理存储引擎
查看mysql支持的存储引擎
MariaDB [hellodb]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
查看当前默认的存储引擎
MariaDB [hellodb]> show variables like ‘%storage_engine%‘;
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine | InnoDB |
+------------------------+--------+设置默认的存储引擎
[root@centos7 ~]$vim /etc/my.cnf
[root@centos7 ~]$head -2 /etc/my.cnf
[mysqld]
default_storage_engine=MyISAM
...
[root@centos7 ~]$systemctl restart mariadb
[root@centos7 ~]$mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
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 [(none)]> SHOW VARIABLES LIKE ‘%storage_engine%‘;
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
| storage_engine | MyISAM |
+------------------------+--------+查看数据库中所有表使用的存储引擎
MariaDB [hellodb]> show table status from hellodb;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| classes | InnoDB | 10 | Compact | 8 | 2048 | 16384 | 0 | 0 | 9437184 | 9 | 2020-10-17 14:52:11 | NULL | NULL | utf8_general_ci | NULL | | |
| coc | InnoDB | 10 | Compact | 14 | 1170 | 16384 | 0 | 0 | 9437184 | 15 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
| courses | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 0 | 9437184 | 8 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
| scores | InnoDB | 10 | Compact | 15 | 1092 | 16384 | 0 | 0 | 9437184 | 16 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
| students | InnoDB | 10 | Compact | 25 | 655 | 16384 | 0 | 0 | 9437184 | 26 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
| teachers | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 0 | 9437184 | 5 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
| toc | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 9437184 | 1 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
7 rows in set (0.00 sec)查看库中指定表的存储引擎
MariaDB [hellodb]> show table status like ‘students‘;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| students | InnoDB | 10 | Compact | 25 | 655 | 16384 | 0 | 0 | 9437184 | 26 | 2020-10-17 14:52:12 | NULL | NULL | utf8_general_ci | NULL | | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show create table students\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum(‘F‘,‘M‘) NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)设置表的存储引擎
MariaDB [hellodb]> CREATE TABLE test (id int) ENGINE=MyISAM;
MariaDB [hellodb]> SHOW CREATE TABLE test;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------
MariaDB [hellodb]> ALTER TABLE test ENGINE=InnoDB;
MariaDB [hellodb]> SHOW CREATE TABLE test;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------+