The Eleventh Weeks
The Eleventh Weeks(Lucklyzheng)
1、 导入hellodb.sql生成数据库
(1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
MariaDB [(none)]> source hellodb_innodb.sql MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | dbzpp2 | | dbzpp3 | | hellodb | | information_schema | | mysql | | performance_schema | +--------------------+ 6 rows in set (0.001 sec) MariaDB [hellodb]> use hellodb; Database changed MariaDB [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 | | NULL | | | Gender | enum('F','M') | NO | | NULL | | | ClassID | tinyint(3) unsigned | YES | | NULL | | | TeacherID | int(10) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.044 sec) MariaDB [hellodb]> select Name,age from students where Age > 25; +--------------+-----+ | Name | age | +--------------+-----+ | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Tian Boguang | 33 | | Xu Xian | 27 | | Sun Dasheng | 100 | +--------------+-----+
(2) 以ClassID为分组依据,显示每组的平均年龄
MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID; +---------+---------+ | ClassID | avg_age | +---------+---------+ | 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 in set (0.001 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID having avg_age > 30; +---------+---------+ | ClassID | avg_age | +---------+---------+ | NULL | 63.5000 | | 2 | 36.0000 | | 5 | 46.0000 | +---------+---------+ 3 rows in set (0.029 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 | | 26 | Lione | 40 | M | 5 | 3 | | 27 | Litwo | 20 | M | 3 | 3 | +-------+-------------+-----+--------+---------+-----------+ 5 rows in set (0.035 sec) MariaDB [hellodb]> select * from students where Name rlike '^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 | | 26 | Lione | 40 | M | 5 | 3 | | 27 | Litwo | 20 | M | 3 | 3 | +-------+-------------+-----+--------+---------+-----------+ 5 rows in set (0.096 sec)
2、数据库授权zheng用户,允许192.168.1.0/24网段可以连接mysql
MariaDB [hellodb]> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> create user 'zheng'@'192.168.33.%'; Query OK, 0 rows affected (0.048 sec) MariaDB [mysql]> select authentication_string,password,user,host from user; -+-------------+--------------+ | authentication_string | Password | User | Host | +-------------------------------------------+-------------------------------------------+-------------+--------------+ | | | mariadb.sys | localhost | | *1DA8F4346484CD9DEC3995995493D26581B7F5F8 | *1DA8F4346484CD9DEC3995995493D26581B7F5F8 | root | localhost | | invalid | invalid | mysql | localhost | | | | zheng | 192.168.33.% | +-------------------------------- MariaDB [mysql]> set password for 'zheng'@'192.168.33.%' = password('xxxxxxx'); MariaDB [mysql]> grant all privileges on *.* to 'zheng'@'192.168.33.%' identified by 'xxxxxxx'; MariaDB [mysql]> flush privileges;
[20:43:00 root@zpp-master1 ~]#\mysql -uzheng -p'zheng.0830' -h192.168.33.129
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dbzpp2 |
| dbzpp3 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· DeepSeek本地性能调优
· 一文掌握DeepSeek本地部署+Page Assist浏览器插件+C#接口调用+局域网访问!全攻略