MariaDB [hellodb]>select name,age,gender from students where age>25 and gender='M';
+--------------+-----+--------+
| name | age | gender |
+--------------+-----+--------+
| Xie Yanke |53| M || Ding Dian |32| M || Yu Yutong |26| M || Shi Qing |46| M || Tian Boguang |33| M || Xu Xian |27| M || Sun Dasheng |100| M |
+--------------+-----+--------+
7 rows inset(0.00 sec)
(2) 以ClassID为分组依据,显示每组的平均年龄
MariaDB [hellodb]>select classid,avg(age)平均年龄 from students group by classid ;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 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 inset(0.00 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]>select classid,avg(age)平均年龄 from students group by classid having 平均年龄>30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| NULL |63.5000||2|36.0000||5|46.0000|
+---------+--------------+
3 rows inset(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 inset(0.00 sec)
2、数据库授权magedu用户,允许192.168.1.0/24网段可以连接mysql
MariaDB [hellodb]> create user magedu@"192.168.1.%" identified by "123456";Query OK, 0 rows affected (0.00 sec)