mysql使用

落花踏尽游何处，笑入胡姬酒肆中。这篇文章主要讲述mysql使用相关的知识，希望能为你提供帮助。
1、导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

mysql> 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)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为分组依据，显示每组的平均年龄

mysql> SELECT classid,AVG(age) FROM students WHERE classid IS NOT NULL GROUP BY classid; +---------+----------+ | classid | AVG(age) | +---------+----------+ |2 |36.0000 | |1 |20.5000 | |4 |24.7500 | |3 |20.2500 | |5 |46.0000 | |7 |19.6667 | |6 |20.7500 | +---------+----------+ 7 rows in set (0.00 sec)

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

mysql> select classid,AVG(age) as avgage from students where classid is not null group by classid having avgage > 30; +---------+---------+ | classid | avgage| +---------+---------+ |2 | 36.0000 | |5 | 46.0000 | +---------+---------+ 2 rows in set (0.01 sec)

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

mysql> 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)

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

mysql> create user \'magedu\'@\'192.168.1.%\' identified by \'123456\'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on `hellodb`.* to \'magedu\'@\'192.168.1.%\' identified by \'123456\'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)