Linux相关知识的第十七回合

Linux相关知识的第十七回合

# 导入库略
14:30:07 (root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| emp               |
| scores            |
| students          |
| teachers          |
| toc               |
| user              |
| v_st_co_sc        |
+-------------------+
10 rows in set (0.00 sec)

14:32:49 (root@localhost) [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   | MUL | 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)

# 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄 
14:33:00 (root@localhost) [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)

# 以`ClassID`为分组依据，显示每组的平均年龄
14:37:13 (root@localhost) [hellodb]> select ClassID as 班级ID,avg(age) as 平均年龄 from students group by ClassID;
+----------+--------------+
| 班级ID   | 平均年龄     |
+----------+--------------+
|     NULL |      27.0000 |
|        1 |      20.5000 |
|        2 |      52.0000 |
|        3 |      20.2500 |
|        4 |      24.7500 |
|        5 |      46.0000 |
|        6 |      20.7500 |
|        7 |      19.6667 |
+----------+--------------+
8 rows in set (0.00 sec)

# 显示第2题中平均年龄大于30的分组及平均年龄
14:37:45 (root@localhost) [hellodb]> select ClassID as 班级ID,avg(age) as 平均年龄 from students group by ClassID having avg(age) > 30;
+----------+--------------+
| 班级ID   | 平均年龄     |
+----------+--------------+
|        2 |      52.0000 |
|        5 |      46.0000 |
+----------+--------------+
2 rows in set (0.00 sec)

# 显示以L开头的名字的同学的信息
14:38:44 (root@localhost) [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)

数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

14:25:58 (root@localhost) [hellodb]> select Host,User,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| Host      | User          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *3A4CF0C22DF4D75E05738349EB11077C46A6F8A6 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | hooper        | *E96DA9D73BA0C5C55271253F87F4AA11DEAD45D3 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)

14:26:08 (root@localhost) [hellodb]> select password('2Fkq7eTPNK#K');
+-------------------------------------------+
| password('2Fkq7eTPNK#K')                  |
+-------------------------------------------+
| *3A4CF0C22DF4D75E05738349EB11077C46A6F8A6 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

# 创建用户
14:28:13 (root@localhost) [hellodb]> create user 'magedu'@'192.168.168.%' identified by '2Fkq7eTPNK#K';
Query OK, 0 rows affected (0.03 sec)

14:29:51 (root@localhost) [hellodb]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

14:30:04 (root@localhost) [hellodb]> select Host,User,authentication_string from mysql.user;
+---------------+---------------+-------------------------------------------+
| Host          | User          | authentication_string                     |
+---------------+---------------+-------------------------------------------+
| localhost     | root          | *3A4CF0C22DF4D75E05738349EB11077C46A6F8A6 |
| localhost     | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost     | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost     | hooper        | *E96DA9D73BA0C5C55271253F87F4AA11DEAD45D3 |
| 192.168.168.% | magedu        | *3A4CF0C22DF4D75E05738349EB11077C46A6F8A6 |
+---------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)