锐客网

  1. 首页 > 睿知 > it技术 > >

第十一周学习作业

IT知识作业学习作业

【第十一周学习作业】眼前多少难甘事，自古男儿当自强。这篇文章主要讲述第十一周学习作业相关的知识，希望能为你提供帮助。
1、导入hellodb.sql生成数据库

[root@centos84 ~]# mysql < hellodb_innodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database|
+--------------------+
| #mysql50#.cache|
| hellodb|
| information_schema |
| mysql|
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]>
##或者
MariaDB [(none)]> help

General information about MariaDB can be found at
http://mariadb.org

List of all client commands:
Note that all text commands must be first on line and end with ;
?(\\?) Synonym for `help.
clear(\\c) Clear the current input statement.
connect(\\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\\d) Set statement delimiter.
edit(\\e) Edit command with $EDITOR.
ego(\\G) Send command to MariaDB server, display result vertically.
exit(\\q) Exit mysql. Same as quit.
go(\\g) Send command to MariaDB server.
help(\\h) Display this help.
nopager(\\n) Disable pager, print to stdout.
notee(\\t) Dont write into outfile.
pager(\\P) Set PAGER [to_pager]. Print the query results via PAGER.
print(\\p) Print current command.
prompt(\\R) Change your mysql prompt.
quit(\\q) Quit mysql.
rehash(\\#) Rebuild completion hash.
source(\\.) Execute an SQL script file. Takes a file name as an argument.
status(\\s) Get status information from the server.
system(\\!) Execute a system shell command.
tee(\\T) Set outfile [to_outfile]. Append everything into given outfile.
use(\\u) Use another database. Takes database name as argument.
charset(\\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings(\\W) Show warnings after every statement.
nowarning (\\w) Dont show warnings after every statement.

For server side help, type help contents
MariaDB [(none)]> source /root/hellodb_innodb.sql;

MariaDB [(none)]> use hellodb;
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 [hellodb]> 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.001 sec)

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [hellodb]> select name 姓名,age 年龄 from students where age> 25 and gender=M;
+--------------+--------+
| 姓名| 年龄|
+--------------+--------+
| 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.001 sec)

(2) 以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select classid 班级ID,avg(age) 平均年龄 from students group by classid ;
+----------+--------------+
| 班级ID| 平均年龄|
+----------+--------------+
|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 班级ID,avg(age) 平均年龄 from students group by classid having avg(age)> 30 ;
+----------+--------------+
| 班级ID| 平均年龄|
+----------+--------------+
|NULL |63.5000 |
|2 |36.0000 |
|5 |46.0000 |
+----------+--------------+
3 rows in set (0.001 sec)

(4) 显示以L开头的名字的同学的信息
MariaDB [hellodb]> select * from students where name like l%;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name| Age | Gender | ClassID | TeacherID |
+-------+------

    推荐阅读


    上一篇：#yyds干货盘点# 04 MySQL创建表

    下一篇：ZabbixZabbix微信告警配置演示