1、 导入hellodb.sql生成数据库
(1)安装mysql,启动服务 [17:18:49 root@CentOS8 ~]\ [#yum -y install mysql-server [17:21:03 root@CentOS8 ~]\ [#systemctl start mysqld (2)导入hellodb.dql生成数据库 [17:26:30 root@CentOS8 ~]\ [#mysql mysql> source hellodb_innodb.sql
(1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
mysql> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use hellodb; Database changed mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec) 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 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 | | NULL | 63.5000 | +---------+----------+ 8 rows in set (0.00 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
mysql> select classid,avg(age) from students group by classid having avg(age) >30; +---------+----------+ | classid | avg(age) | +---------+----------+ | 2 | 36.0000 | | 5 | 46.0000 | | NULL | 63.5000 | +---------+----------+ 3 rows in set (0.00 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)
2、数据库授权magedu用户,允许192.168.1.0/24网段可以连接mysql
mysql> create user 'magedu'@'192.168.1.%'; Query OK, 0 rows affected (0.00 sec) mysql> grant all privileges on *.* to 'magedu'@'192.168.1.%'; Query OK, 0 rows affected (0.01 sec)