运维开发网

MariaDB练习

运维开发网 https://www.qedev.com 2020-10-18 12:28 出处:51CTO 作者:蜗牛漫步者
1、导入hellodb.sql生成数据库(1)在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄[[email protected]~]$systemctlstartmariadb[[email protected]~]$ss-tnlStateRecv-QSend-QLocalAddress:PortPeerAddress:PortLISTEN0100127.0.0.1:25*:*LISTEN05

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

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

[[email protected] ~]$systemctl start mariadb
[[email protected] ~]$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                                    [::]:*

[[email protected] ~]$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 [email protected]'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常见的存储引起以及特点

MariaDB练习

常用的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

其它存储引擎

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

设置默认的存储引擎

[[email protected] ~]$vim /etc/my.cnf
[[email protected] ~]$head -2 /etc/my.cnf
[mysqld]
default_storage_engine=MyISAM
...
[[email protected] ~]$systemctl restart mariadb
[[email protected] ~]$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 |
+-------+----------------------------------------------------------------------------------------+

扫码领视频副本.gif

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号