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) Don't 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) Don't 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 |
+-------+-------------+-----+--------+---------+-----------+
| 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.001 sec)
2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql
MariaDB [(none)]> create user magedu@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.008 sec)
MariaDB [(none)]> select user, host from mysql.user;
+-------------+-------------+
| User | Host |
+-------------+-------------+
| magedu | 192.168.1.% |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-------------+
4 rows in set (0.001 sec)
