mysql练习一

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

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

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

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

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

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

MariaDB [hellodb]> select classid,avg(age)  avg from students group by classid having avg > 30;
+---------+---------+
| classid | avg     |
+---------+---------+
|    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.003 sec)

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

MariaDB [(none)]> create user 'magedu'@'192.168.1.%' identified by '123456';
MariaDB [(none)]> select host,user,password from mysql.user;
+---------------+--------+-------------------------------------------+
| host          | user   | password                                  |
+---------------+--------+-------------------------------------------+
| localhost     | root   |                                           |
| centos8-node1 | root   |                                           |
| 127.0.0.1     | root   |                                           |
| ::1           | root   |                                           |
| 192.168.1.%   | magedu | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+--------+-------------------------------------------+
5 rows in set (0.001 sec)

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

功能 MyISAM MEMORY InnoDB
存储限制 256T RAM 64T
支持事务 NO NO YES
支持B树索引 YES YES YES
支持哈希索引 NO YES NO
支持集群索引 NO NO YES
支持数据索引 NO YES YES
支持全文索引 YES NO NO
支持数据压缩 YES NO NO
空间使用率 LOW N/A HIGH
支持外键 NO NO YES

MyISAM

  • 特点
不支持事务 
表级锁定 
读写相互阻塞,写入不能读,读时不能写 
只缓存索引
不支持外键约束 
不支持聚簇索引 
读取数据较快,占用资源较少 ,不支持MVCC(多版本并发控制机制)高并发
崩溃恢复性较差 
MySQL5.5.5前默认的数据库引擎 ,查询速度快、存储空间小,原因是在磁盘上分成三个文件存储:.frm(存储表定义),.MYD(MYData,存储数据),.MYI(MYIndex,存储索引),SELECT COUNT(*) FROM TABLE时,避免了全表扫描。
  • MyISAM存储引擎适用场景
只读(或者写较少)、表较小(可以接受长时间进行修复操作),适合查询多、写入少的业务场景
  • MyISAM引擎文件
tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件

InnoDB

  • 特点
InnoDB,最大的特点就是支持事务和行锁,是现在Mysql的默认存储引擎。
对事务和写并发的支持使InnDB成为互联网公司最常使用的存储引擎。
行级锁 ,支持行级锁及外键约束,所以在可以支持写并发。
支持事务,适合处理大量短期事务 ,支持ACID的事务,支持事务的四种隔离级别。
读写阻塞与事务隔离级别相关 
可缓存数据和索引 
支持聚簇索引 
崩溃恢复性更好 
支持MVCC高并发 
从MySQL5.5后支持全文索引 ,SELECT COUNT(*) FROM TABLE时会进行全表扫描,效率低于MyISAM。
从MySQL5.5.5开始为默认的数据库引擎 
  • InnoDB数据库文件
所有InnoDB表的数据和索引放置于同一个表空间中
  表空间文件:datadir定义的目录下  
  数据文件:ibddata1, ibddata2, ... 
每个表单独使用一个表空间存储表的数据和索引
启用:innodb_file_per_table=ON  
  两类文件放在数据库独立目录中   
  数据文件(存储数据和索引):tb_name.ibd   
  表格式定义:tb_name.frm 
  • 优缺点
优势在于提供了良好的事务管理、奔溃修复能力和并发控制。
缺点是读写效率稍差,占用的数据空间相对比较大。
  • 适用场合
更新密集的表:InnoDB存储引擎特别适合处理多重并发的更新请求。
事务:InnoDB存储引擎是唯一支持事务的标准MySQL存储引擎,这是管理敏感数据(如金融信息和用户注册信息)的必须软件
自动灾难恢复:与其他存储引擎不一样,InnoDB表能够自动从灾难中恢复。虽然MyISAM表也能在灾难后修复,但其过程要长的多

MEMORY
MEMORY是一种特殊的存储引擎,特点是使用存储在内存中的内容来创建表,并且数据全部放在内存中。

  • 特点
每个基于MEMORY存储引擎的表实际对应一个磁盘文件。
该文件的文件名与表名相同,类型为frm类型。
该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。
需要注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。
如果不需要了,可以释放内存,甚至删除不需要的表。
将所有数据存储在RAM中,以便在需要快速查找参考和其他类似 数据的环境中进行快速访问。适用存放临时数据。
引擎以前被称为HEAP引擎 
MEMORY默认使用哈希索引,速度比使用B树索引快。
当然如果你想用B型树索引,可以在创建索引时指定。
注意,MEMORY用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。
如果重启或者关机,所有数据都会消失。因此,基于MEMORY的表的生命周期很短,一般是一次性的。
所有的数据都在内存中,处理速度快,但是安全性不高。对表的大小有要求,依赖内存,不能建立太大的表。
  • MEMORY存储引擎的优缺点
MEMORY表的大小是受到限制的。表的大小主要取决于两个参数,分别是max_rows和max_heap_table_size。其中,max_rows可以在创建表时指定:max_heap_table_size的大小默认为16MB,可以按需要进行扩大。因其存在内存中的特性,这类表的处理速度非常快。但是其数据容易丢失,生命周期短。(注意:mysqld守护进程奔溃时,所有的MEMORY数据都会丢失)
MEMORY表不支持VARCHAR、BLOB、TEXT数据类型,因为这种表类型按固定长度的记录格式存储。此外,如果使用版本4.1.0之前的MySQL,这不支持自动增长列。
  • 适用场景
暂时:目标数据只是临时需要,在其生命周期中必须立即可用。
相对无关:存储在MEMORY表中的数据如果突然丢失,不会对应用服务产生实质的负面影响,而且不会对数据完整性有长期影响。
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 205,236评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,867评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,715评论 0 340
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,899评论 1 278
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,895评论 5 368
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,733评论 1 283
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,085评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,722评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 43,025评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,696评论 2 323
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,816评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,447评论 4 322
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,057评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,009评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,254评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,204评论 2 352
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,561评论 2 343

推荐阅读更多精彩内容

  • MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎,就必须重新编译MYSQL。在...
    时待吾阅读 1,542评论 0 4
  • 索引 数据库中的查询操作非常普遍,索引就是提升查找速度的一种手段 索引的类型 从数据结构角度分 1.B+索引:传统...
    一凡呀阅读 2,859评论 0 8
  • 第一章 MySQL 体系架构和存储引擎 mysql是数据库也是数据库实例 mysql 是一个单进程多线程架构的数据...
    snail_knight阅读 3,492评论 0 6
  • MySQL不权威总结 欢迎阅读 本文并非事无巨细的mysql学习资料,而是选择其中重要、困难、易错的部分进行系统地...
    liufxlucky365阅读 2,572评论 0 26
  • 这一周是不寻常的一周,也就是很忙的一周,因为这周主持人汇演,还有街舞考级培训,但是我还是没忘记读书,每天读书的时间...
    23班张昌奇阅读 314评论 1 1