OpenHarmony 与 HarmonyOS的关系图

转载

墨香四溢 2024-07-14 08:08:40

文章标签 数据库 mysql database 字段 SQL 文章分类 HarmonyOS 后端开发

1.条件查询

distinct去重

where 过滤数据

like模糊查询

null空元素

between... and...包含在...和...之间

limit分页查询，限制数据的条数

order by排序

2.统计案例

2.1入职统计

2.2年薪统计

3.聚合 aggregation

3.1count统计个数/行数/总记录数

3.2max最大值，min最小值，sum求和，avg平均数

4.group分组

4.1 group by

4.2 having

5. 事务 transaction

5.1 概念

5.2 事务的4个特征ACID--面试题

5.3 隔离级别

5.4 查询mysql的隔离级别

5.5 事务处理

5.6 提交 commit

5.7 回滚 rollback

6.表强化：6约束 constraints

6.1 非空约束not null

6.2 唯一约束unique

6.3 主键约束：primary key

6.4****外键约束：foreign key...reference...

6.5默认约束：default

6.6检查约束：check

7. 表关联 association

8.索引 index

8.1 定义

8.2 分类

8.3 创建索引

8.4索引扫描类型

8.5 最左特性

8.6 为何索引快？

8.7 索引总结

9.视图

9.1 视图定义：

9.2 优缺点

9.3 视图的使用

1.条件查询

distinct去重

//查询部门地址
SELECT loc FROM dept #包含重复结果
SELECT DISTINCT loc FROM dept #去重--DISTINCT

union 和union all的区别

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

区别1：取结果的交集

1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

区别2：获取结果后的操作

1、union: 会对获取的结果进行排序操作

2、union all: 不会对获取的结果进行排序操作

where 过滤数据

注意：SQL的执行顺序from->where->select

and--并且，or--或者，注意:where中不能使用列别名!!

//where 用来过滤数据---AND高效，OR低效
 //查询部门编号=1的记录
 SELECT *FROM dept #查全表--低效
 SELECT * FROM dept WHERE deptno=1#只查一条--高效

 #SQL的执行顺序from->where->select
 
 #查询在二区的部门
 SELECT * from dept WHERE loc="二区"
 
 查询在二区的research部门
 SELECT * from dept WHERE loc='二区' AND dname='research'

 #查询在二区的research的部门编号
 SELECT deptno from dept WHERE loc='二区' AND dname='research'

 #查询在一区的编号是1的部门信息
 SELECT * FROM dept WHERE loc = '一区' AND deptno = 1

 #查询在一区的编号是1的部门名称
 SELECT dname FROM dept WHERE loc = '一区'
 
 #查询在一区的部门信息或者编号是3的部门信息
 SELECT * FROM dept WHERE loc='一区' OR deptno = 3 
 
//in--取在指定区域里的内容,not in-取不在指定区域里的内容)
select sal from emp where sal in(3000,3500,10000);
select sal from emp where sal not in(3000,3500,10000);

like模糊查询

注意：在%前面，是开头,在%后面，是结尾,%%包裹代表包含

//查询名称包含o的部门
 SELECT * FROM dept WHERE dname ='o' #明确的条件，查询名称=o的部门

#LIKE模糊的条件  %占位符 匹配0~n个字符
 SELECT * FROM dept WHERE dname LIKE '%o%' #模糊的条件，查询名称里包含o的部门

#以a开头的部门
SELECT * FROM dept WHERE dname LIKE 'a%' 
#以ch结尾的部门
SELECT * FROM dept WHERE dname LIKE '%ch' 

#查询在一区的部门或者包含ting的部门
SELECT * FROM dept WHERE dname ='一区'  OR dname LIKE '%ting%'

#查询名字包含o的员工的入职年份
SELECT *,YEAR(hiredate),YEAR(now())-YEAR(hiredate) FROM emp WHERE ename LIKE'%o%'

not like '%xx%'统计某字段不包含特定字符串

#如有一张employee表，其中有name字段，查询name中不包含li字符的所有行。

SELECT * FROM `employee` WHERE name NOT LIKE '%li%'

null空元素

注意：null如果参与运算需要转成0--ifnull(xxx,0)

SELECT * FROM emp WHERE comm is null --查询comm是空的员工信息
SELECT * FROM emp WHERE comm is not null --查询comm不为空的员工信息
SELECT *,sal+IFNULL(comm,0) FROM emp --查询每个员工的月薪，如果为null替换成0计算
SELECT *,(sal+IFNULL(comm,0))*12 FROM emp--查询员工年薪
SELECT *,(sal+IFNULL(comm,0))*12,sal*12+IFNULL(comm,0)*12 FROM emp--查询员工年薪

between... and...包含在...和...之间

SELECT * FROM emp WHERE sal>=5000 AND sal<=10000 --查询工资[5000,10000]的员工信息
SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000 --查询工资[5000,10000]的员工信息

limit分页查询，限制数据的条数

注意：从哪开始(初始为0)，取几条

SELECT * FROM emp LIMIT 2 -- 取前两条数据
SELECT * FROM emp LIMIT 2,2 -- #从哪开始(初始为0)，取几条

SELECT * FROM emp WHERE sal>5000 --工资大于5000的记录
SELECT * FROM emp WHERE sal>5000 LIMIT 2 --查询工资大于5000的前两条记录
SELECT * FROM emp WHERE sal>5000 LIMIT 1,2 --查询工资大于5000的记录从1开始取两条

order by排序

注意：默认升序，加desc变为降序

ORDER BY排序--默认升序
SELECT * FROM emp ORDER BY sal  --默认升序
SELECT * FROM emp ORDER BY sal asc --默认升序
SELECT * FROM emp ORDER BY sal DESC --加desc变为降序

#查询工资大于5000的两个最高薪的员工信息
SELECT * FROM emp WHERE sal>5000  ORDER BY sal DESC LIMIT 2 

#查询名字里包含o的老员工
SELECT * FROM emp WHERE ename LIKE '%o%' ORDER BY hiredate LIMIT 1

2.统计案例

2.1入职统计

SELECT * FROM emp WHERE YEAR(hiredate)<2015  #2015年以前入职的老员工
SELECT * FROM emp WHERE DATE_FORMAT(hiredate,'%Y-%m-%d')<'2015-01-01';
SELECT * FROM emp WHERE hiredate)<'2015-1-1'

#计算每个员工入职几年
SELECT *, YEAR(now()) - YEAR(hiredate) 入职几年 FROM emp 

#查询2015~2019年入职的员工
SELECT * FROM emp WHERE YEAR(hiredate) >=2015 AND YEAR(hiredate) <=2019

#日期进行格式转换后方便比较
SELECT * FROM emp WHERE YEAR(DATE_FORMAT(hiredate,'%Y-%m-%d'))<=2019;

2.2年薪统计

注意：列名设置别名，空格+汉字

一年13薪，算年薪
SELECT *,(sal+IFNULL(comm,0))*13 FROM emp 
SELECT *,sal*13+IFNULL(comm,0)*13 as 年薪 FROM emp --给列/字段设置别名，在列名后 空格as加汉字
SELECT *,sal*13+IFNULL(comm,0)*13  年薪 FROM emp --给列/字段设置别名，在列名后空格加汉字，as可省略

3.聚合 aggregation

3.1count统计个数/行数/总记录数

SELECT count(*) FROM emp--*低效
SELECT count(1) FROM emp--1高效
SELECT count(ename) FROM emp--可以用字段名统计个数
#格式：
SELECT count(字段名) FROM emp
SELECT count(comm) FROM emp--如果按照字段名取，但要保证字段值不能为null，是null就不会被统计

3.2max最大值，min最小值，sum求和，avg平均数

SELECT MAX(sal),min(sal) FROM emp --求字段的最大值最小值，可以混在一起
select max(sal),max(comm) from emp ---可以一起求不同字段名下的最大最小值
SELECT sum(sal) 总和,AVG(sal) 平均数 FROM emp--求指定字段的总和与平均值，并给字段设置别名
SELECT ename,MAX(sal) FROM emp group by ename --分组


#统计名字里包含a的员工人数
SELECT count(1) FROM emp WHERE ename LIKE '%a%'

#统计普通员工的平均工资
SELECT AVG(sal+comm) FROM emp WHERE job = '员工'

#统计2019年入职员工的总人数
SELECT count(1) FROM emp WHERE YEAR(hiredate) = 2019

#统计2号部门每年的工资总开销
SELECT sum(sal+IFNULL(comm,0))*12 2号部门每年开销 FROM emp WHERE deptno = 2

4.group分组

4.1 group by

注意：如果查询时，出现了聚合列和非聚合列，通常要按照非聚合列分组

//查询 每个部门里的最高薪和人名
SELECT deptno,ename,max(sal) FROM emp group by deptno
**如果查询时，出现了聚合列和非聚合列，通常要按照非聚合列分组

#查询 每种岗位 的平均工资和岗位名称
SELECT job,AVG(sal+IFNULL(comm,0)) 岗位平均工资 FROM emp GROUP BY job --按非聚合列分组
#查询每个部门的平均工资
SELECT deptno,AVG(sal+IFNULL(comm,0))部门平均工资 FROM emp GROUP BY deptno --分析需求：按照部门分组

#统计每个部门出现的次数
SELECT count(1) FROM emp GROUP BY deptno

4.2 having

having 在分组后的结果中，继续添加过滤条件

注意：

分组前过滤，使用where过滤--高效，分组后过滤，使用having过滤--低效，按需求来过滤
where只能过滤非聚合函数(字段)，having可以过滤非聚合函数也可以过滤聚合函数
执行顺序：FROM--》GROUP BY--》HAVING--》SELECT
having 的语法要求，having后面的关键字必须是在同一个SQL语句中之前查过的。

//查询 每种岗位 的平均工资 和岗位名称 --进一步查员工的平均工资
SELECT job,avg(sal) FROM emp  WHERE job = '员工' GROUP BY job--分组前过滤，使用where高效
SELECT job,avg(sal) FROM emp  GROUP BY job HAVING job = '员工'--分组后过滤，使用having低效


//统计 部门 出现的次数--再过滤出现次数大于1的
SELECT count(1) FROM emp GROUP BY deptno HAVING count(1)>1


//查询 每个部门 的平均工资 --再过滤平均工资大于10000的信息
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>10000
//where和having的区别：where只能过滤非聚合函数,having都可以
SELECT deptno,sal,AVG(sal) FROM emp GROUP BY deptno HAVING sal>10000
SELECT deptno,sal,AVG(sal) FROM emp WHERE sal>10000 GROUP BY deptno

5. 事务 transaction

5.1 概念

事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

5.2 事务的4个特征ACID--面试题

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：保证数据在不同电脑里是一致的。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发访问，保证事务间是隔离的，互不影响
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

5.3 隔离级别

读未提交：性能最好，数据的安全性最差
读提交：Oracle的默认隔离级别--性能较好，安全性较差
可重复读：Mysql的默认隔离级别--性能较差，安全性较好
串行化：安全性最高，但是表级锁，效率低

5.4 查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。

如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start transaction；
SQL语句....
结束事务：commit（提交事务）或rollback（回滚事务）。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！需要单选执行

5.5 事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
MySQL默认数据库的事务是开启的，执行SQL后自动提交。
MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

5.6 提交 commit

#多条语句时，批量执行，事务提交

#有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

#mysql的事务默认就是开启的 -- 多条语句一起操作时,要么一起成功要么一起失败

BEGIN; //关闭事务的自动提交

INSERT INTO user (id) VALUES(25);//成功

INSERT INTO user (id) VALUES(5);//已经存在5了，会失败

COMMIT; #手动提交事务

5.7 回滚 rollback

Begin...insert...后有一个看不见的地带，要么commit要么rollback；
#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;

INSERT INTO user (id) VALUES(15);

INSERT INTO user (id) VALUES(35);#存在了

ROLLBACK;#事务回滚，就不会再提交了

6.表强化：6约束 constraints

6.1 非空约束not null

6.2 唯一约束unique

Name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

DROP TABLE IF EXISTS tb_user; // 如果表存在则删除，慎用会丢失数据

CREATE TABLE tb_user(

id INT,

NAME VARCHAR(30) UNIQUE NOT NULL,

phone VARCHAR(20) UNIQUE NOT NULL,

email VARCHAR(30) UNIQUE NOT NULL,

PRIMARY KEY (id)

);

DESC tb_user;

INSERT INTO tb_user (id,NAME) VALUES(1,'tony');

INSERT INTO tb_user (id,NAME) VALUES(2,'tony');

6.3 主键约束：primary key

CREATE TABLE h(
id INT PRIMARY KEY auto_increment,#primary key 设置主键约束
sex char (3) DEFAULT '女' #DEFAULT设置默认值 
)

6.4****外键约束：foreign key...reference...

把两张表之间的关系，通过两个表的主键表示

格式：在当前表的最后添加：foreign key +(子表的字段名) reference+主表名(字段名)

//外键约束
create table tb_user(
	id int primary key auto_increment,
	name varchar(20),
	age int
)
create table tb_user_addr(
	user_id int primary key auto_increment,
	addr varchar(200) ,--只有最后一行SQL语句可以不用逗号，以上都要用逗号
	#描述了两张表之间通过哪个字段关联着--外键约束

    #外键          (子表的字段)     (参考)       主表名(主表字段)
	foreign key    (user_id)     references    tb_user(id)
)

6.5默认约束：default

给指定的字段设置默认值

可以给不经常用的值或者null值设置它的默认值，以便使用运算等等操作

CREATE TABLE h(
id INT PRIMARY KEY auto_increment,
sex char (3) DEFAULT '女' #DEFAULT设置默认值 
)

6.6检查约束：check

给字段增加检查条件

create table i(
id int PRIMARY KEY auto_increment,
age int,
CHECK (age>0 AND age<=150)#检查约束
)

7. 表关联 association

表table代表了生活中一个主体，如部门表dept，员工表emp。表关联则代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。

同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

下面我们讨论表的关系分为四种：

一对一 one to one QQ和QQ邮箱，员工和员工编号
一对多 one to many 最常见，部门和员工，用户和订单
多对一 many to one 一对多反过来，员工和部门，订单和用户
多对多 many to many 老师和学生，老师和课程

8.索引 index

8.1 定义

排好序的快速查找的数据结构，帮助数据库高效数据检索。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。

目前大多数索引都采用BTree树方式构建。

好处：给加完索引的列，提高查询效率

坏处：索引本质上就是一张表，如果表的体积太大，比较占内存

主键本身就有索引

8.2 分类

单值索引：一个索引只包括一个列，一个表可以有多个列
唯一索引：一个索引只能包含一列，但是索引列的值必须唯一，允许有空值；主键会自动创建唯一索引
复合索引：一个索引同时包括多列

8.3 创建索引

1）查看索引，主键会自动创建索引

show index from 表名
show index from dept

2）创建索引

create index 索引名字 on 表名(字段名);
经常被查询的字段,直接加索引
drop index 部门 on dept(dname)
create index dname_index on dept(dname)

3）修改表结构，添加普通索引

alter table 表名 add index 索引名(字段名)

alter table dept add index loc_index(loc)

4）创建唯一索引

格式：alter table 表名 add unique(字段名)

alter table dept add unique(loc) --loc的值已经重复了，不能使用唯一索引
alter table dept add unique(dname) --索引列的值必须唯一

5)创建复合索引

给多个字段加一个索引

alter table dept add index fuhe_index(dname,loc)

6）创建复合唯一索引

复合索引在执行查询dname列，查询dname，loc双列，查询loc，dname双列都生效，但是只查询loc列会失效。

alter table dept add unique fuhe_index(dname,loc)

7）删除索引

格式：alter table 表名 drop index 索引名

alter table dept drop index 部门

8.4索引扫描类型

type：

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果

8.5 最左特性

复合索引查询时必须包含最左边的索引
like模糊查询和or条件查询没有效果
经常被where查询的非聚合列通常设置为索引，
当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

alter table emp add index 复合_index(job,ename)#添加复合索引

EXPLAIN 
select * from emp where job ='员工' #生效

EXPLAIN
select * from emp where job='员工' and ename = 'jack' #生效

EXPLAIN
select *from emp where ename = 'jack' and job = '员工' #生效

EXPLAIN 
select * from emp where ename = 'jack' #不匹配最左特性，失效

EXPLAIN
select * from emp where job like '%员工%' # 模糊查询效率低，失效

EXPLAIN 
select * from emp where job = '员工' or ename = 'jack' #or条件查询，失效

8.6 为何索引快？

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

排序，tree结构，类似二分查找
索引表小

8.7 索引总结

优点:

索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率

缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

9.视图

9.1 视图定义：

把一个查询语句的结果缓存起来存入视图中

视图本质就是一个查询，和我们自己查询的区别是，它执行完会有缓存，下次查询就直接使用。但其也因为事先缓存，无法做优化，大型项目中禁止使用。

注意:视图只需创建一次，后面就可以类似表来使用，只是用来查询不能更新和删除

9.2 优缺点

好处：提高查询效率

坏处：占有内存，无法进行SQL优化，当更新了数据时视图也需要时间更新

9.3 视图的使用

1.创建视图： create view  视图名 as SQL语句
create view empView as 
select * from emp where ename like '%a%'
#2.使用视图，就当作一张表用
select * from empView

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：虚拟机如何启动Xmodem接收软件

下一篇：JAVA 车辆VIM验证规则

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯