第十四周作业

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

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

 

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

 

MariaDB [hellodb]> SELECT name,age FROM students WHERE gender = 'M' AND age > 25;
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
+--------------+-----+
5 rows in set (0.00 sec)

 

 

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

 

MariaDB [hellodb]> SELECT classid,avg(age) FROM students GROUP BY classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       1 |  20.6667 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  25.3333 |
|       5 |  46.0000 |
|       6 |  20.3333 |
|       7 |  19.6667 |
+---------+----------+
7 rows in set (0.00 sec)

 

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

 

MariaDB [hellodb]> SELECT classid,avg(age) as 平均年龄 FROM students GROUP BY classid HAVING 平均年龄>30;
+---------+--------------+
| classid | 平均年龄     |
+---------+--------------+
|       2 |      36.0000 |
|       5 |      46.0000 |
+---------+--------------+
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、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

MariaDB [hellodb]> GRANT ALL ON hellodb.* TO magedu@'192.168.1.%' IDENTIFIED BY 'centos';
Query OK, 0 rows affected (0.00 sec)

 

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

MyISAM存储引擎特点：

• 不支持事务

• 表级锁定

• 读写相互阻塞，写入不能读，读时不能写

• 只缓存索引

• 不支持外键约束

• 不支持聚簇索引

• 读取数据较快，占用资源较少

• 不支持MVCC（多版本并发控制机制）高并发

• 崩溃恢复性较差

• MySQL5.5.5前默认的数据库引擎

InnoDB存储引擎特点：

• 行级锁

• 支持事务，适合处理大量短期事务

• 读写阻塞与事务隔离级别相关

• 可缓存数据和索引

• 支持聚簇索引

• 崩溃恢复性更好

• 支持MVCC高并发

• 从MySQL5.5后支持全文索引

• 从MySQL5.5.5开始为默认的数据库引擎