IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码

MariaDB表查询以及用户管理实践

IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码

1、 导入hellodb.sql生成数据库

[root@test-centos7-node1 ~]# rz
rz waiting to receive.
 zmodem trl+C ȡ

  100%       7 KB    7 KB/s 00:00:01       0 Errors
Transferring hellodb_innodb.sql...

[root@test-centos7-node1 ~]# ls
hellodb_innodb.sql
[root@test-centos7-node1 ~]# mysql < hellodb_innodb.sql 
[root@test-centos7-node1 ~]# mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

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]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> 

  说明:导入数据库,可以用输入重定向到方式,如上所示,也可以在数据库里用source sql脚本路径的方式导入,两种的方式核心思想就是把sql脚本交给mysql数据库引擎执行一遍,生成数据。

(1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄

MariaDB [hellodb]> show  tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

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.00 sec)

MariaDB [hellodb]> 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)

MariaDB [hellodb]> 

(2) 以ClassID为分组依据,显示每组的平均年龄

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.00 sec)

MariaDB [hellodb]> select classid , 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.00 sec)

MariaDB [hellodb]> 

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

ariaDB [hellodb]> select classid , 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.00 sec)

MariaDB [hellodb]> select classid , 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.00 sec)

MariaDB [hellodb]> 

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

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.00 sec)

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.00 sec)

MariaDB [hellodb]> 

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

MariaDB [hellodb]> select user,host,password from mysql.user;
+------+--------------------+----------+
| user | host               | password |
+------+--------------------+----------+
| root | localhost          |          |
| root | test-centos7-node1 |          |
| root | 127.0.0.1          |          |
| root | ::1                |          |
|      | localhost          |          |
|      | test-centos7-node1 |          |
+------+--------------------+----------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> grant all on *.* to magedu@'192.168.1.%' identified by 'admin';
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select user,host,password from mysql.user;                     
+--------+--------------------+-------------------------------------------+
| user   | host               | password                                  |
+--------+--------------------+-------------------------------------------+
| root   | localhost          |                                           |
| root   | test-centos7-node1 |                                           |
| root   | 127.0.0.1          |                                           |
| root   | ::1                |                                           |
|        | localhost          |                                           |
|        | test-centos7-node1 |                                           |
| magedu | 192.168.1.%        | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+--------+--------------------+-------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [hellodb]> 

  说明:此方式是创建用户+授权一起做,当然也可以用先创建账号,然后在授权,有关mysql的账号创建和授权可以参考https://tech.souyunku.com/qiuhom-1874/p/9741166.html

3、总结mysql常见的存储引擎以及特点。

  MyISAM引擎特点:

    1)不支持事务

    2)支持表级别锁定

    3)读写相互阻塞,写入不能读,读时不能写

    4)只缓存索引

    5)不支持外键约束

    6)不支持聚簇索引

    7)读取数据较快,占用资源较少

    8)不支持MVCC(多版本并发控制机制)高并发

    9)崩溃恢复性较差

    10)mysql5.5前默认的数据库引擎

  MyISAM存储引擎适用于只读(或者写较少)、表较小(可以接受长时间进行修复操作)的场景

  MyISAM引擎文件有三个.frm(表格式定义文件) .MYD(数据文件) .MYI(索引文件)

  InnoDB引擎特点:

    1)支持事务,适合处理大量短期事务

    2)支持行级别锁

    3)读写阻塞与事务隔离级别相关

    4)可缓存数据和索引

    5)支持聚簇索引

    6)崩溃恢复性更好

    7)支持MVCC高并发

    8)从mysql5.5后支持全文索引,mysql5.5开始默认的数据库引擎,InnoDB数据库文件所有InnoDB表达数据和索引放置于同一个表空间中,表空间文件是由主配置文件中的datadir来指定存放的位置,数据文件:ibddata1,idbdata2……,若每个表单独使用一个表空间存储表的数据和索引,可在主配置文件中用innodb_file_per_table=ON来启用,两类文件放在数据库独立目录中,其中数据文件(存储数据和索引)是以标名加.ibd结尾的文件,表格式文件是表名加.frm结尾的文件

文章永久链接:https://tech.souyunku.com/?p=36962


Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(64) 打赏



未经允许不得转载:搜云库技术团队 » MariaDB表查询以及用户管理实践

IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码
IDEA2023.1.3破解,IDEA破解,IDEA 2023.1破解,最新IDEA激活码

评论 抢沙发

大前端WP主题 更专业 更方便

联系我们联系我们

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

微信扫一扫打赏


Fatal error: Uncaught Exception: Cache directory not writable. Comet Cache needs this directory please: `/data/wangzhan/tech.souyunku.com.wp/wp-content/cache/comet-cache/cache/https/tech-souyunku-com/index.q`. Set permissions to `755` or higher; `777` might be needed in some cases. in /data/wangzhan/tech.souyunku.com.wp/wp-content/plugins/comet-cache/src/includes/traits/Ac/ObUtils.php:367 Stack trace: #0 [internal function]: WebSharks\CometCache\Classes\AdvancedCache->outputBufferCallbackHandler() #1 /data/wangzhan/tech.souyunku.com.wp/wp-includes/functions.php(5109): ob_end_flush() #2 /data/wangzhan/tech.souyunku.com.wp/wp-includes/class-wp-hook.php(303): wp_ob_end_flush_all() #3 /data/wangzhan/tech.souyunku.com.wp/wp-includes/class-wp-hook.php(327): WP_Hook->apply_filters() #4 /data/wangzhan/tech.souyunku.com.wp/wp-includes/plugin.php(470): WP_Hook->do_action() #5 /data/wangzhan/tech.souyunku.com.wp/wp-includes/load.php(1097): do_action() #6 [internal function]: shutdown_action_hook() #7 {main} thrown in /data/wangzhan/tech.souyunku.com.wp/wp-content/plugins/comet-cache/src/includes/traits/Ac/ObUtils.php on line 367