Q1、 导入hellodb.sql生成数据库
[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.25-MariaDB-log Source distribution
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)]> create database testguest;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testguest |
+--------------------+
5 rows in set (0.01 sec)
MariaDB [(none)]> use testguest;
Database changed
MariaDB [testguest]> source /tmp/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 tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
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)
(1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
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)
(2) 以ClassID为分组依据,显示每组的平均年龄
MariaDB [hellodb]> select classid,avg(age) as 'average age' from students group by classid;
+---------+-------------+
| classid | average 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.01 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select classid,avg(age) as 'average age' from students group by classid having avg(age)>30;
+---------+-------------+
| classid | average age |
+---------+-------------+
| NULL | 63.5000 |
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+-------------+
3 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)
Q2、数据库授权magedu用户,允许192.168.1.0/24网段可以连接mysql
MariaDB [hellodb]> grant all on *.* to 'magedu'@'192.168.1.0/24' identified by 'P@ssw0rd' with grant option;
Query OK, 0 rows affected (0.01 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 | | localhost.localdomain |
| root | | 127.0.0.1 |
| root | | ::1 |
| | | localhost |
| | | localhost.localdomain |
| magedu | *8232A1298A49F710DBEE0B330C42EEC825D4190A | 192.168.1.0/24 |
+--------+-------------------------------------------+-----------------------+
7 rows in set (0.00 sec)
Q3、总结mysql常见的存储引擎以及特点。
MyISAM
①不支持事务
②表级锁定
③读写相互阻塞,写入不能读,读时不能写
④只缓存索引
⑤不支持外键约束
⑥不支持聚簇索引
⑦读取数据较快,占用资源较少 ,不支持MVCC(多版本并发控制机制)高并发
⑧崩溃恢复性较差
⑨MySQL5.5.5前默认的数据库引擎 ,查询速度快、存储空间小,原因是在磁盘上分成三个文件存储:.frm(存储表定义),.MYD(MYData,存储数据),.MYI(MYIndex,存储索引),SELECT COUNT() FROM TABLE时,避免了全表扫描。
1、MyISAM存储引擎适用场景
只读(或者写较少)、表较小(可以接受长时间进行修复操作),适合查询多、写入少的业务场景
2、MyISAM引擎文件
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件
InnoDB
①InnoDB,最大的特点就是支持事务和行锁,是现在Mysql的默认存储引擎。
②对事务和写并发的支持使InnDB成为互联网公司最常使用的存储引擎。
InnoDB引擎特点
①行级锁 ,支持行级锁及外键约束,所以在可以支持写并发。
②支持事务,适合处理大量短期事务 ,支持ACID的事务,支持事务的四种隔离级别。
③读写阻塞与事务隔离级别相关
④可缓存数据和索引
⑤支持聚簇索引
⑥崩溃恢复性更好
⑦支持MVCC高并发
⑧从MySQL5.5后支持全文索引 ,SELECT COUNT(*) FROM TABLE时会进行全表扫描,效率低于MyISAM。
⑨从MySQL5.5.5开始为默认的数据库引擎
MEMORY
①MEMORY是一种特殊的存储引擎,特点是使用存储在内存中的内容来创建表,并且数据全部放在内存中。
②每个基于MEMORY存储引擎的表实际对应一个磁盘文件。
③该文件的文件名与表名相同,类型为frm类型。
④该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。
⑤需要注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。
⑥如果不需要了,可以释放内存,甚至删除不需要的表。
⑦将所有数据存储在RAM中,以便在需要快速查找参考和其他类似 数据的环境中进行快速访问。适用存放临时数据。
⑧引擎以前被称为HEAP引擎
⑨MEMORY默认使用哈希索引,速度比使用B树索引快。当然如果你想用B型树索引,可以在创建索引时指定。
注意,MEMORY用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。
⑩如果重启或者关机,所有数据都会消失。
因此,基于MEMORY的表的生命周期很短,一般是一次性的。
所有的数据都在内存中,处理速度快,但是安全性不高。对表的大小有要求,依赖内存,不能建立太大的表。
Archive
为存储和检索大量很少参考的存档或安全审核信息,只支持 SELECT和INSERT操作;支持行级锁和专用缓存区
NDB
①基于集群的引擎-数据被自动切分并复制到数个机器上(数据节点), 适合于那些需要极高查询性能和高可用性的应用,
②NDB提供了高达99.999%的可靠性,在读操作多的应用中表现优异。
③对于有很多并发写操作的应用, 还是推荐用InnoDB.
④如果你网站并发高,数据量不会大到需要去集群管理 ,还是推荐用innodb.
⑤如果只是小应用 没什么数据请求量 MyISAM的读取速度更快些
Federated存储引擎
Federated存储引擎不存放数据,它至少指向一台远程MySQL数据库服务器上的表,非常类似于Oracle的透明网关。
Maria存储引擎
Maria存储引擎是新开发的引擎,其设计目标是用来取代原有的MyISAM存储引擎,从而成为MySQL默认的存储引擎。