数据库练习

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

```html/xml
[root@localhost ~]# mysql -uroot -p'Root123!' < ./hellodb.sql
mysql: [Warning] Using a password on the command line interface can be insecu许慕离re.
[root@localhost ~]# mysql -uroot -p'Root123!'
mysql: [Warning] Using a password on the command line i系统运维工作内容nterface can be insecure.
Welcome to the MySQL monitor. Commands end with ; omysql密码忘记了怎么办r \g.
Your MySQL connection id is 7
Server version: 5.7.37 M凶猛领主ySQL Community Server (GPL)

Copyright (c) 2000, 2022, Orac西门龙霆le and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademmysqlarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statemhtml是什么意思ent.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performahtml简单网页代码nce_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)


#### (1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄 
```html/xml
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)
mysql> 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为分组依据,显喜马拉雅听书免费版官方下载示每组的平均年龄

```html/xmysql怎么读ml
mysql> sel西门龙霆ect classimysql密码忘记了怎么办d,avg(age) from students where classid is no系统运维工资一般多少t null group by classid;
+----喜马拉雅山高度多少米-----+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |html文件怎么打开
| 7 | 19.6667 |
+---------+----------+
7 rows in set (0.00 sec)


#### (3) 显示第2题中平均年龄大于30的分组及平均年龄
```html/xml
mysql> select classid,avg(age) from students where classid is not null  group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|       2 |  36.0000 |
|       5 |  46.0000 |
+---------+----------+
2 rows in set (0.00 sec)

(4) 显示以L开头的名字的同学的信息

```htmmysql创建表l/xml
mysql> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+----------mysql怎么读-+
| StuID | Nalinux系统安装me | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+-------许慕离--+-----------+
| 8 | Lin Da许慕离iyu | 1linux系统安装7 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+---linux删除文件命令----+-------------+-----+--------+--------html个人网页完整代码-+-----------+
3 rows in set (0.00 sec)


### 2、数据库授权magedu用户,允许192.168.1.0/24网段可以连接mysql
```html/xml
mysql> grant all on *.* to magedu@'192.168.1.%' identified by 'Magedu0!';
Query OK, 0 rows affected, 1 warning (0.00 sec)