MariaDB表查詢以及使用者管理實踐

從mysql5.5後支援全文索引，mysql5.5開始預設的資料庫引擎，InnoDB資料庫檔案所有InnoDB表達資料和索引放置于同一個表空間中，表空間檔案是由主配置檔案中的datadir來指定存放的位置，資料檔案：ibddata1,idbdata2……，若每個表單獨使用一個表空間存儲表的資料和索引，可在主配置檔案中用innodb_file_per_table=ON來啟用，兩類檔案放在資料庫獨立目錄中，其中資料檔案（存儲資料和索引）是以标名加.ibd結尾的檔案，表格式檔案是表名加.frm結尾的檔案

1、導入hellodb.sql生成資料庫

[root@test-centos7-node1 ~]# rz
rz waiting to receive.
 zmodem trl+C ȡ

  100%       7 KB    7 KB/s 00:00:01       0 Errors
Transferring hellodb_innodb.sql...

[root@test-centos7-node1 ~]# ls
hellodb_innodb.sql
[root@test-centos7-node1 ~]# mysql < hellodb_innodb.sql 
[root@test-centos7-node1 ~]# mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.64-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 |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hellodb
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]>

　　說明：導入資料庫，可以用輸入重定向到方式，如上所示，也可以在資料庫裡用source sql腳本路徑的方式導入，兩種的方式核心思想就是把sql腳本交給mysql資料庫引擎執行一遍，生成資料。

(1) 在students表中，查詢年齡大于25歲，且為男性的同學的名字和年齡

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

MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)         | NO   |     | NULL    |                |
| Age       | tinyint(3) unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
6 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 |
+--------------+-----+
7 rows in set (0.00 sec)

MariaDB [hellodb]>

(2) 以ClassID為分組依據，顯示每組的平均年齡

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     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 |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

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

(3) 顯示第2題中平均年齡大于30的分組及平均年齡

ariaDB [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 avg(age) > 30;      
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       2 |  36.0000 |
|       5 |  46.0000 |
+---------+----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]>

(4) 顯示以L開頭的名字的同學的資訊

MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     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 |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

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)

MariaDB [hellodb]>

2、資料庫授權magedu使用者，允許192.168.1.0/24網段可以連接配接mysql

MariaDB [hellodb]> select user,host,password from mysql.user;
+------+--------------------+----------+
| user | host               | password |
+------+--------------------+----------+
| root | localhost          |          |
| root | test-centos7-node1 |          |
| root | 127.0.0.1          |          |
| root | ::1                |          |
|      | localhost          |          |
|      | test-centos7-node1 |          |
+------+--------------------+----------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> grant all on *.* to magedu@'192.168.1.%' identified by 'admin';
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select user,host,password from mysql.user;                     
+--------+--------------------+-------------------------------------------+
| user   | host               | password                                  |
+--------+--------------------+-------------------------------------------+
| root   | localhost          |                                           |
| root   | test-centos7-node1 |                                           |
| root   | 127.0.0.1          |                                           |
| root   | ::1                |                                           |
|        | localhost          |                                           |
|        | test-centos7-node1 |                                           |
| magedu | 192.168.1.%        | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+--------+--------------------+-------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]>

　　說明：此方式是建立使用者+授權一起做，當然也可以用先建立賬号，然後在授權，有關mysql的賬号建立和授權可以參考https://www.cnblogs.com/qiuhom-1874/p/9741166.html

3、總結mysql常見的存儲引擎以及特點。

　　MyISAM引擎特點：

　　　　1）不支援事務

　　　　2）支援表級别鎖定

　　　　3）讀寫互相阻塞，寫入不能讀，讀時不能寫

　　　　4）隻緩存索引

　　　　5）不支援外鍵限制

　　　　6）不支援聚簇索引

　　　　7）讀取資料較快，占用資源較少

　　　　8）不支援MVCC（多版本并發控制機制）高并發

　　　　9）崩潰恢複性較差

　　　　10）mysql5.5前預設的資料庫引擎

　　MyISAM存儲引擎适用于隻讀（或者寫較少）、表較小（可以接受長時間進行修複操作）的場景

　　MyISAM引擎檔案有三個.frm（表格式定義檔案） .MYD（資料檔案） .MYI（索引檔案）

　　InnoDB引擎特點：

　　　　1）支援事務，适合處理大量短期事務

　　　　2）支援行級别鎖

　　　　3）讀寫阻塞與事務隔離級别相關

　　　　4）可緩存資料和索引

　　　　5）支援聚簇索引

　　　　6）崩潰恢複性更好

　　　　7）支援MVCC高并發

　　　　8）從mysql5.5後支援全文索引，mysql5.5開始預設的資料庫引擎，InnoDB資料庫檔案所有InnoDB表達資料和索引放置于同一個表空間中，表空間檔案是由主配置檔案中的datadir來指定存放的位置，資料檔案：ibddata1,idbdata2……，若每個表單獨使用一個表空間存儲表的資料和索引，可在主配置檔案中用innodb_file_per_table=ON來啟用，兩類檔案放在資料庫獨立目錄中，其中資料檔案（存儲資料和索引）是以标名加.ibd結尾的檔案，表格式檔案是表名加.frm結尾的檔案

作者：Linux-1874

出處：https://www.cnblogs.com/qiuhom-1874/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利.

MariaDB表查詢以及使用者管理實踐

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述