今天给大家分享一些简单好用的数据库优化方式！1、选择最合适的字段属性Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。例如：在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。2、尽量把字段设置为NOT NULL在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。3、使用连接(JOIN)来代替子查询(Sub-Queries)MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如：我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询，如下图所示：如果使用连接(JOIN)来完成这个工作，速度将会快很多，尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。另外，如果你的应用程序有很多JOIN查询，你应该确认两个表中JOIN的字段是被建立过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。而且这些被用来JOIN的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和一个INT字段JOIN在一起，MySQL就无法使用他们的索引。对于那些STRING类型，还需要有相同的字符集才行。(两个表的字符集可能不相同)。inner join内连接也叫做等值连接，left/right join是外链接。    SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;    SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;    SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;经过多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。例如我们有两张表：Orders表通过外键Id_P和Persons表进行关联。inner join（内连接），在两张表进行连接查询时，只保留两张表中完全匹配的结果集。我们使用inner join对两张表进行连接查询，sql如下：    SELECT p.LastName, p.FirstName, o.OrderNo    FROM Persons p    INNER JOIN Orders o    ON p.Id_P=o.Id_P and 1=1  --用and连接多个条件    ORDER BY p.LastName查询结果集：此种连接方式Orders表中Id_P字段在Persons表中找不到匹配的，则不会列出来。注意：单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了，此时就等价于：select * from a inner join b on a.id = b.id。 -- 即就是内连接。但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。内连接查询 (select * from a join b on a.id = b.id) 与 关联查询 (select * from a , b where a.id = b.id)的区别left join,在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。我们使用left join对两张表进行连接查询，sql如下：    SELECT p.LastName, p.FirstName, o.OrderNo    FROM Persons p    LEFT JOIN Orders o    ON p.Id_P=o.Id_P    ORDER BY p.LastName查询结果如下：可以看到，左表（Persons表）中LastName为Bush的行的Id_P字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。right join,在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。我们使用right join对两张表进行连接查询，sql如下：    SELECT p.LastName, p.FirstName, o.OrderNo    FROM Persons p    RIGHT JOIN Orders o    ON p.Id_P=o.Id_P    ORDER BY p.LastName查询结果如下：Orders表中最后一条记录Id_P字段值为65，在左表中没有记录与之匹配，但依然保留。full join,在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。我们使用full join对两张表进行连接查询，sql如下：    SELECT p.LastName, p.FirstName, o.OrderNo    FROM Persons p    FULL JOIN Orders o    ON p.Id_P=o.Id_P    ORDER BY p.LastName查询结果如下：查询结果是left join和right join的并集。4、使用联合(UNION)来代替手动创建的临时表MySQL从4.0版本开始支持union查询，他可以把需要使用临时表的两条或更多的select查询合在一个查询中。在客户端查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用union作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。下面一个例子就演示了一个使用union额查询。当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候尽量使用union all而不是union，因为union和union all的差异主要是前者需要将两个或者多个结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，增大资源消耗及延迟。5、事务尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。    BEGIN;    INSERTINTOsalesinfoSETCustomerID=14;    UPDATEinventorySETQuantity=11WHEREitem='book';    COMMIT;事务的另一个作用是当多个用户同时使用相同的数据源时，他可以使用锁定数据库的方式来为用户提供一种安全的访问机制，这样可以保证用户的操作不被其它的用户所干扰。一般来说，事务必须满足四个条件(ACID)：原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability).    原子性：一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执行过一样。    一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。    隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).    持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。事务的并发问题：1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据2、不可重复读：事务A多次读取同一事物，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。3、幻读：系统管理员A将数据库中的所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表MySQL事务隔离级别：事务隔离级别    脏读    不可重复读    幻读读未提交（read-uncommitted）    是    是    是不可重复读（read-committed）    否    是    是可重复读（repeatable-read）    否    否    是串行化（serializable）    否    否    否事务控制语句：    BEGIN或START TRANSACTION：显式的开启一个事物。    COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的。    Rollback：也可以使用Rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。    SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有很多个SAVEPOINT；    RELEASE SAVEPOINT identifier：删除一个事物的保存点,当没有指定的保存点时，执行该语句会抛出一个异常。    ROLLBACK TO inditifier：把事务回滚到标记点。    SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。6、使用外键锁定表的方法可以维护数据的完整性，但是他却不能保证数据的关联性。这个时候我们可以使用外键。例如：外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.    CREATE TABLE customerinfo(customerid int primary key) engine = innodb;    CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;注意例子中的参数“on delete cascade”.该参数保证当customerinfo表中的一条客户记录也会被自动删除。如果要在mysql中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是mysql表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=innoDB。7、锁定表尽管事务是维护数据库完整性的一个非常好的方法，但却因为他的独占性，有时会影响数据库的性能，尤其是很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方式来获得更好的性能。下面的例子就是锁定表的方法来完成前面一个例子中事务的功能。这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其他访问来对inventory进行插入、更新或者删除的操作。8、使用索引索引是提高数据库性能的常用方法，他可以令数据库服务器比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对那些字段进行索引呢？一般来说，索引应该建立在那些将用于join，where判断和orderby排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引，对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。9、优化de的查询语句    不使用子查询    例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);    子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。    在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为    SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;    但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询    避免函数索引    例：    SELECT * FROM t WHERE YEAR(d) >= 2016;    由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。    应改为—–>    SELECT * FROM t WHERE d >= '2016-01-01';    用IN来替换OR    低效查询    SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;    —–> 高效查询    SELECT * FROM t WHERE LOC_IN IN (10,20,30);    LIKE双百分号无法使用到索引    SELECT * FROM t WHERE name LIKE ‘Þ%’;    —–>    SELECT * FROM t WHERE name LIKE ‘de%’;    目前只有MySQL5.7支持全文索引（支持中文）    读取适当的记录LIMIT M,N    SELECT * FROM t WHERE 1;    —–>    SELECT * FROM t WHERE 1 LIMIT 10;    避免数据类型不一致    SELECT * FROM t WHERE id = ’19’;    —–>    SELECT * FROM t WHERE id = 19;    分组统计可以禁止排序    SELECT goods_id,count(*) FROM t GROUP BY goods_id;    默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。    —–>    SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;    避免随机取记录    SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;    MySQL不支持函数索引，会导致全表扫描 —–>    SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;    禁止不必要的ORDER BY排序    SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;    —–>    SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;    批量INSERT插入        INSERT INTO t (id, name) VALUES(1,’Bea’);        INSERT INTO t (id, name) VALUES(2,’Belle’);        INSERT INTO t (id, name) VALUES(3,’Bernice’);    —–>    INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);————————————————版权声明：本文为CSDN博主「肥肥技术宅」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/m0_71777195/article/details/127360558

EXPLAIN分析pgsql的性能前言EXPLAIN命令EXPLAIN -- 显示一个语句的执行计划命令详解EXPLAIN输出结果展示analyzebuffers全表扫描索引扫描位图扫描条件过滤Nestloop joinHash joinMerge JoinNested Loop，Hash JOin，Merge Join对比参考EXPLAIN分析pgsql的性能前言对于pgsql中查询性能的分析，好像不想mysql中那么简单。当然pgsql中也是通过EXPLAIN进行分析，那么就来认真中结下pgsql中explain的使用。EXPLAIN命令EXPLAIN -- 显示一个语句的执行计划EXPLAIN [ ( option [, ...] ) ] statementEXPLAIN [ ANALYZE ] [ VERBOSE ] statement这里 option可以是： ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] BUFFERS [ boolean ] TIMING [ boolean ] FORMAT { TEXT | XML | JSON | YAML }ANALYZE选项通过实际执行的sql来获取相应的计划。这个是真正执行的，多以可以真是的看到执行计划花费了多少的时间，还有它返回的行数。当然对于分析插入更新的语句，我们我们是可以把ANALYZE放到事物里面的，分析后之后回滚。BEGIN;EXPLAIN ANALYZE ...;ROLLBACK;命令详解VERBOSE选项用于显示计划的附加信息。这些附加的信息有：计划中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。该选项默认为FALSE。COSTS选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。该选项默认是TRUE。BUFFERS选项显示关于缓存区的信息。该选项只能与ANALYZE参数一起使用。显示的缓存区信息包括共享快块，本地块和临时读和写的块数。共享块、本地块和临时块分别包含表和索引、临时快和临时索引、以及在排序和物化计划中使用的磁盘块。上层节点显示出来的数据块包含其所有子节点使用的块数。该选项默认为FALSE。EXPLAIN输出结果展示explain select * from test1QUERY PLAN-----------------------------------------------------------------------------------------------------------------------Seq Scan on test1 (cost=0.00..146666.56 rows=7999956 width=33)Seq Scan 表示的是全表扫描，就是从头扫尾扫描一遍表里面的数据。(cost=0.00..146666.56 rows=7999956 width=33)的内容可以分成三部分cost=0.00..146666.56 cost后面有两个数字,中间使用..分割，第一个数字0.00表示启动的成本，也就是返回第一行需要多少cost值；第二行表示返回所有数据的成本。rows=7999956:表示会返回7999956行width=33:表示每行的数据宽度为33字节其中cost描述的是一个sql执行的代价。analyze通过analyze可以看到更加精确的执行计划。explain analyze select * from test1QUERY PLAN-----------------------------------------------------------------------------------------------------------------------Seq Scan on test1 (cost=0.00..146666.56 rows=7999956 width=33) (actual time=0.012..1153.317 rows=8000001 loops=1)Planning time: 0.049 msExecution time: 1637.480 ms加了analyze可以看到实际的启动时间，(actual time=0.012..1153.317 rows=8000001 loops=1)其中:actual time=0.012..1153.317:0.012表示的是启动的时间,..后面的时间表示返回所有行需要的时间rows=8000001:表示返回的行数buffers通过使用buffers来查看缓存区命中的情况explain (analyze,buffers) select * from test1QUERY PLAN-----------------------------------------------------------------------------------------------------------------------Seq Scan on test1 (cost=0.00..146666.56 rows=7999956 width=33) (actual time=0.013..1166.464 rows=8000001 loops=1) Buffers: shared hit=777 read=65890Planning time: 0.049 msExecution time: 1747.163 ms其中会多出一行Buffers: shared hit=777 read=65890。shared hit=777：表示的在共享内存里面直接读到777个块；read=65890：表示从磁盘中读了65890块written: 表示写磁盘共xx块通过上面数字进行分析，我们可以知道那些部分是对I/O最敏感的。全表扫描全表扫描在pgsql中叫做顺序扫描(seq scan)，全表扫描就是把表的的所有的数据从头到尾读取一遍，然后从数据块中找到符合条件的数据块。索引扫描索引是为了加快数据查询的速度索引而增加的(Index Scan)。索引扫描也就是我们的查询条件使用到了我们创建的索引，当然什么是索引，自行查阅资料吧。位图扫描位图扫描也是走索引的一种方式。方法是扫描索引，那满足条件的行或者块在内存中建立一个位图，扫描完索引后，再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引进行’and‘或‘or’计算，合并到一个位图，再到表的数据文件中把数据读出来。条件过滤就是在where后面加上过滤条件，扫描数据行，会找出满足条件过滤的行。条件过滤执行计划中显示为‘Filter’。Nestloop join对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。Nested Loop是连表查询最朴素的一种连接方式。在嵌套循环的时候，内表被外表驱动，外表中返回的每一行，都要在内表中检索找寻和它匹配的行。整个查询返回的结果集不能太大（>10000不适合），要把返回子集比较小的表作为外表，同时内表中连接查询的字段，最好能命中索引，不然会有性能问题。执行的过程：确定一个驱动表（outer table），另一个表为inner table，驱动表中的每一行数据会去inner表中，查找检索数据。注意，驱动表的每一行都去inner表中检索，索引驱动表的数据不能太大。对于inner表中的数据就没有限制了，只要创建的索引合适，inner表中数据的大小对查询的性能影响不大。测试下：创建数据表create table test1( id bigserial not null constraint test1_pk primary key, name text not null, category_id bigint not null);create index test1_category_id_index on test1 (category_id); create table test2( id bigserial not null constraint test2_pk primary key, name text not null, category_id bigint not null);create index test2_category_id_index on test2 (category_id);插入数据,test1插入8000000条，test2插入6000000条do $$declarev_idx integer := 1;begin while v_idx < 8000000 loop v_idx = v_idx+1; insert into test1 (name, category_id) values ( random()*(20000000000000000-10)+10,random()*(8000000-10)+10); end loop;end $$;do $$declarev_idx integer := 1;begin while v_idx < 6000000 loop v_idx = v_idx+1; insert into test2 (name, category_id) values ( random()*(20000000000000000-10)+10,random()*(6000000-10)+10); end loop;end $$;验证下查询explain select a.id,b.id from test1 a,test2 b where a.category_id=b.category_id and a.id<10000QUERY PLAN-----------------------------------------------------------------------------------------------------------------------Gather (cost=1170.58..67037.87 rows=14666 width=16) Workers Planned: 2 -> Nested Loop (cost=170.58..64571.27 rows=6111 width=16) -> Parallel Bitmap Heap Scan on test1 a (cost=170.15..24939.15 rows=3748 width=16) Recheck Cond: (id < 10000) -> Bitmap Index Scan on test1_pk (cost=0.00..167.90 rows=8996 width=0) Index Cond: (id < 10000) -> Index Scan using test2_category_id_index on test2 b (cost=0.43..10.55 rows=2 width=16) Index Cond: (category_id = a.category_id)可以看到当有高选择性索引或进行限制性，查询优化器会自动选择Nested LoopHash join优化器使用两个表较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的列。这种方式适用于较小的表可以完全放入到内存中，这样总成本就是访问两个表的成本之和。但是如果表都很大，不能放入到内存中，优化器会将它分割成若干个不同的分区，把不能放入到内存的部分写入到临时段。此时要求有较大的临时段从而尽量提高I/O 的性能。它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。优化器会自动选择较小的表，建立散列表，然后扫描另个较大的表。Hash Join只能应用于等值连接(如WHERE A.COL3 = B.COL4)，这是由Hash的特点决定的。测试下drop index test1_category_id_index;drop index test2_category_id_index;删除关联查询的字段的索引explain select a.id,b.id from test1 a,test2 b where a.category_id=b.category_id and a.id<10000QUERY PLAN-----------------------------------------------------------------------------------------------------------------------Hash Join (cost=214297.39..248684.97 rows=14666 width=16) Hash Cond: (a.category_id = b.category_id) -> Index Scan using test1_pk on test1 a (cost=0.43..335.86 rows=8996 width=16) Index Cond: (id < 10000) -> Hash (cost=109999.98..109999.98 rows=5999998 width=16) -> Seq Scan on test2 b (cost=0.00..109999.98 rows=5999998 width=16)因为没有索引了，并且查询的条数可以完全放入到内存里面，所以查询优化器就选择使用Hash join了，对于选择那个表建立散列表，要看查询的条件。如上面的限制条件a.id<10000,限制了a表查询的数据条数，那么a表条数较少，然后就在a表建立散列表，然后扫描b表。Merge Join通常情况下散列连接的效果比合并连接的效果好，如果源数据上有索引，或者结果已经排过序，在执行顺序合并连接时就不需要排序了，这时合并连接的性能会优于散列连接。Merge join的操作步骤：1' 对连接的每个表做table access full;2' 对table access full的结果进行排序;3' 进行merge join对排序结果进行合并。Merge Join可适于于非等值Join（>，<，>=，<=，但是不包含!=，也即<>）explain select a.id,b.id from test1 a,test2 b where a.category_id=b.category_id QUERY PLAN-----------------------------------------------------------------------------------------------------------------------Merge Join (cost=2342944.84..2575285.64 rows=13041968 width=16) Merge Cond: (b.category_id = a.category_id) -> Sort (cost=1005574.67..1020574.66 rows=5999998 width=16) Sort Key: b.category_id -> Seq Scan on test2 b (cost=0.00..124999.98 rows=5999998 width=16) Filter: (id < 100000000) -> Materialize (cost=1337364.94..1377364.72 rows=7999956 width=16) -> Sort (cost=1337364.94..1357364.83 rows=7999956 width=16) Sort Key: a.category_id -> Seq Scan on test1 a (cost=0.00..146666.56 rows=7999956 width=16)category_id上面是没有索引的，这时候查询选择了Merge Join，上面的Sort Key: a.category_id，就是对a表的category_id字段排序。Nested Loop，Hash JOin，Merge Join对比类别Nested LoopHash JoinMerge Join使用条件任何条件等值连接（=）等值或非等值连接(>，<，=，>=，<=)，‘<>’除外相关资源CPU、磁盘I/O内存、临时空间内存、临时空间特点当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。当缺乏索引或者索引条件模糊时，Hash Join比Nested Loop有效。通常比Merge Join快,如果有索引，或者结果已经被排序了，这时候Merge Join的查询更快。在数据仓库环境下，如果表的纪录数多，效率高。当缺乏索引或者索引条件模糊时，Merge Join比Nested Loop有效。非等值连接时，Merge Join比Hash Join更有效缺点当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。为建立哈希表，需要大量内存。第一次的结果返回较慢。所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。参考【EXPLAIN】cid:link_17

1：数据库性能常用的度量指标（1）运行时间 run time  （2）QPS：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。（3）TPS：是TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。2：数据库性能常见的依赖因素（1）环境a: 机器配置：       磁盘、存储文件系统规划；       CPU主频、核数;            linux OS系统内核配置：/etc/sysctl.conf; (配置生效时: sysctl -p) ；blog/20170412_04.md at master · digoal/blog · GitHub        网络带宽时延、网络带宽、防火墙；        资源限制：/etc/security/limits.conf b: HA\集群架构（同步、异步复制策略） c: 数据库参数（postgresql.conf 刷盘策略等, 下面会介绍一些常用的参数设置技巧）（2）业务场景表定义数据量SQL事务隔离级别并发度3：数据库运行时间消耗(1) 客户端：建连\断连， SQL发送、 结果集接受、处理(2) 数据库服务端: 通信层(采用连接池、用户态网络、逻辑连接)， SQL优化（优化）\执行层（线程池），写日志（并发）、数据刷盘（刷页线程、shared）、结果输出(3) 网络延迟4： SQL执行时间分析SQL--->查询解析（词法分析token keyword、语法分析parsetree、语义分析querytree）---> 查询优化 （查询重写）---> 优化器 （代价估算、生成执行计划 plantree） -------> 绑参 ------> 执行器 ----->存储引擎5： 逻辑上常见的SQL执行顺序【】【7】select 【8】distinct 【11】<select_list>【1】from <left_table>【3】<join_type> join <right_table>【2】on <join_condition>【4】where <condition>【5】group by <group_list>【6】having <having_condition>【9】order by <order_list>【10】limit <limit_offset>6： 不同执行计划的选择 （explain的使用：PgSQL · 最佳实践 · EXPLAIN 使用浅析）（1）如何扫描表：        Seq Scan ：全表顺序扫描， 一般查询没有索引的表需要全表扫描。        Index Scan： 索引扫描， 主要用来再where条件中存在索引列是的扫描。（建表的时候可以创建索引列）。一般由于Seq Scan。        Bitmap index Scan：一种索引扫描， 节点每次执行返回的是一个位图而不是一个元组，其中位图中每位代表了一个扫描到的数据块。这样做最大的好处就是把Index Scan 的随机读转换成了按照数据块的物理顺序读取，在数据量比较大的时候，这会大大提升扫描的性能。         Bitmap heap Scan： 作为BitmapIndex Scan 的父节点， 将BitmapIndex Scan 返回的位图转换为对应的元组。        如何选择：        [1]:  大多数情况下，Index Scan 要比 Seq Scan 快。但是如果获取的结果集占所有数据的比重很大时，这时Index Scan 因为要先扫描索引再读表数据反而不如直接全表扫描来的快。        [2]:  如果获取的结果集的占比比较小，但是元组数很多时，可能Bitmap Index Scan 的性能要比Index Scan 好。        代价估计信息：        cost 就是该执行节点的代价估计。它的格式是xxx..xxx，在.. 之前的是预估的启动代价，即找到符合该节点条件的第一个结果预估所需要的代价，在..之后的是预估的总代价。而父节点的启动代价包含子节点的总代价。        rows 代表预估的行数， 根据表的统计信息预估来；        width 代表预估的结果宽度，单位字节。根据表的统计信息预估来。        真实执行信息：        当EXPLAIN 命令中ANALYZE 选项为on时，会在代价估计信息之后输出真实执行信息，包括：    actual time 执行时间，格式为xxx..xxx，在.. 之前的是该节点实际的启动时间，即找到符合该节点条件的第一个结果实际需要的时间，在..之后的是该节点实际的执行时间    rows 指的是该节点实际的返回行数    loops 指的是该节点实际的重启次数。如果一个计划节点在运行过程中，它的相关参数值（如绑定变量）发生了变化，就需要重新运行这个计划节点。（2）如何join        Nested Loop        Hash        Merge  (3) 如何排序（4）如何处理子查询（5）其他等价的执行计划7 SQL优化器如何选择执行计划（1）RBO 基于规则的优化器、动态适应性差（2）CBO 基于代价的优化器、目前关系数据库的主流技术8: 如何估算行数：eg： explain select * from test_t;显示的执行计划中rows=200001这个行数估算来自pg_class系统表。reltuples表示表中行的数量，该值只是被优化器使用的一个估计值。估算值：select  reltuples from pg_class where relname='test_t';返回reltuples = 200001实际值：select count(*) from test_t;返回行数为了201001。实际值和估算值不超过5%eg: explain select * from test_t where col_val = XXX; (等值where条件场景的行数估算)显示的执行计划中rows=300112， Filter:(cl_val = 1)这个函数估算来自pg_class系统表和pg_stats系统表. pg_stats记录的是每个表每个字段的统计信息。用于优化器做执行计划选择提供参考。tablebname (pg_class.relname)表示表的名字；attname(pg_attribute.attname)表示字段的名字。where 的估算值： select * from pg_stats where tablename = 'test_t' and 他他那么= 'col_val';9: 选择合适的索引：PG支持非常丰富的索引方法，例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend)，用户可以根据不同的数据类型，以及查询的场景，选择不同的索引。(1) b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。索引与递归查询结合，还能实现快速的稀疏检索。(2) hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。更多索引相关参看链接PostgreSQL 9种索引的原理和应用场景-阿里云开发者社区10: SQL优化tips：(1) 优先使用预编译语句执行DML，可提升安全性和性能(2) 尽量避免长事务或大型事务，会导致垃圾膨胀或并发事务锁等待。(3) 避免使用 select * ,采用具体的字段。不要使用count(*)判断是否有数据、建议用limit 1(4) 不要再索引列进行类型转换、数学运算等(5) 合理选择union 、 union all11: 数据库访问优化漏斗法则（从业务的全局优化）： (1) 减少数据访问（减少磁盘访问）        索引，减少全表扫描(2) 返回更少数据（减少网络传输或磁盘访问）        根据实际业务需求返回所需数据(3) 减少交互次数（减少网络传输）         缓存        存储过程        批量查询(4) 减少服务器CPU开销（减少CPU几内存开销）        批量处理        在客户端处理大量负责运算(5) 利用更多资源（增加资源）        硬件资源12: PG的锁等待问题：pg_locks  + pg_thread_wait_status + pg_stat_activityPostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁-阿里云开发者社区13: linux 常用的数据库性能分析、问题诊断工具（1）性能分析： top、htop、iotop、vmstat、sar 、netstat、perf、iostat、perftopLinux系统调优常用的命令_写的不是代码的博客-CSDN博客_linux调优命令  (2)  问题诊断：gdb、gstack、gcore、lsof、tcpdump14: PG外部GUI管理工具(1) pgadmin4(2) DBeaver15：性能视图 pg_stat*postgres=# \d+ pg_stat*postgres=# select relname from pg_class where relname like 'pg_stat%';16： sql语句调优(1) 查询字段优化不要使用 SELECT * 用具体的字段列表替换 * ，不要返回用不到的字段。        返回了不必有的数据，就会浪费内存，加重网络的负担降低性能 。如果表大，在表扫描的期间将表锁住，禁止其他的链接访问表，后果严重！！(2) where 子句 like调优：        like的关键键词前面用了“%”，会导致该Sql走全表查询，除非必要，否则不要在关键词前加%。查询耗时和字段值总长度成正比。SELECT id FROM A WHERE name LIKE 'abc%'；（correct）SELECT id FROM A WHERE name LIKE '«c%'；(error)(3) where子句 避免对null做判断。        该判断将导致引擎放弃使用索引而进行全表扫描，建议针对null字段设置默认值0。(4) where 子句避免使用!=        该判断将导致引擎放弃使用索引而进行全表扫描，建议将不等于 拆成 大于或者小于(5)  where子句 避免使用 or        使用or的子句可以分解成多个查询，并且通过union链接多个查询。它们的速度只同是否使用索引有关，如果查询使用到联合索引，用unionAll执行的效率更高，多个or字段的字句没有用到索引，改写成union的形式，再视图与索引匹配。        select id from tb1 where a > 22 or  a < 22; (error)        select id form tb1 where a > 22 union all  select id from tb1 where a < 22; (correct)(6)  避免使用distinct 和 order by;        它会使查询变慢，这些动作可以改在客户端执行也可以(7) group by 和 having的优化        如果能在group by的having子句之前就尽量剔除多余的行，不要用他们来做剔除行的动作。        a: select 的where语句选择所有合适的行。b: group用来分组统计。c: having用于剔除多余的分组。        这样group by和having的开销小，查询快。对于大的数据进行分组和having十分消耗资源。如果group by的目的不包括计算，只是分组。Distinct更快 (8) union all 比 union 快        UNION在进行表链接后会筛选掉重复的记录，UNION ALL不会去除重复记录。        UNION将会按照字段的顺序进行排序，UNION ALL只是简单的将两个结果合并后就返回（9）一次更新多条记录比分多次更新，每次更新一条快。        批量处理更有效率。        insert into tb1(id, a) values(1, 10);    (error)        insert into tb1(id, a) values(2, 16);    (error)        insert into tb1(id, a) values(1, 10), (2, 16);     (correct)   (10) where 子句避免在条件左侧使用算法           在where子句中的“=”左边进行函数、算数运算或其他表达式运算，系统可能无法正确的使用索引。        select * from tb1 where a/2 = 100; (error)        select * from tb1 where substring(a, 1, 4) = '666';   (error)                select * from tb1 where a = 100 * 2;   (correct)        select * from tb1 where a like '666%';   (correct)17： postgres内核参数配置（postgesql.conf）PostgreSQL配置优化_Kyle__Shaw的博客-CSDN博客_postgresql 优化18：PG的查询优化app(sql语句) --> 解析器 （生成查询树） --> 重写器 （重写后的查询树） ---> 优化器 （执行计划）--> 执行器。查询优化包括：a: 物理优化：单表优化、两表优化、大于两表的优化b: 逻辑优化：  子查询优化、等价谓词\条件表达式优化、外链接优化。————————————————版权声明：本文为CSDN博主「写的不是代码」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/Melo0705/article/details/124340834

如题，报错信息如下：none of the server's SASL authentication mechanisms are supported

mysql数据库部署在红帽7.6操作系统上，跑cms和sum数据库脚本时，检测数据库操作系统为非麒麟或suse，报错，无法继续进行。需重新部署suse操作系统版本mysql

请问SQL server迁移GaussDB有什么工具可以实现对象的迁移？

这篇文章主要介绍了PostgreSQL 字符串处理与日期处理操作 ， 具有很好的参考价值 ， 希望对大家有所帮助 。一起跟随小编过来看看吧 。字符串长度、大小写1SELECT CHAR_LENGTH(‘test’) — 字符串长度SELECT LENGTH(‘test’)LENGTH(string,encoding name)SELECT LENGTH(‘测试’,’UTF-8′);LOWER(string) 或者 UPPER(string) — 大小写ASCII(string)SELECT ASCII(‘abc’) — 结果是’a’的ascii码字符串格式化1FORMAT(formatstr text [,formatarg “any” [, …] ]) — 类似于printf字符串拼接SELECT ‘number’ || 123 –字符串连接CONCAT(str “any” [, str “any” [, …] ])CONCAT_WS(sep text, str “any” [,str “any” [, …] ])SELECT * FROM CONCAT_WS(‘#’,’hello’,’world’)字符串剪切与截取LPAD(string text, length int [,fill text])RPAD(string text, length int [,fill text])SELECT LPAD(‘12345′, 10,’0’) — 结果 “0000012345”TRIM([leading | trailing | both] [characters] from string)SELECT TRIM(both ‘ ‘ from ‘ hello world’) — 结果是’hello world’BTRIM(string text [, characters text])RTRIM(string text [, characterstext])LTRIM(string text [, characterstext])SELECT BTRIM(‘yyhello worldyyyy’,’y’) — 结果是’hello world’LEFT(str text, n int) — 返回字符串前n个字符 ， n为负数时返回除最后|n|个字符以外的所有字符RIGHT(str text, n int)SUBSTRING(string from int [for int])SELECT SUBSTRING(‘hello world’ from 7 for 5) — 结果是’world’字符串加引号QUOTE_IDENT(string text)QUOTE_LITERAL(STRING TEXT)QUOTE_LITERAL(value anyelement)SELECT ‘l”host”‘ — 结果是’l’host”‘SELECT QUOTE_LITERAL(‘l”host”‘) — 结果是’l”host”‘字符串分割SPLIT_PART(string text,delimiter text, field int)REGEXP_SPLIT_TO_ARRAY(stringtext, pattern text [, flags text])REGEXP_SPLIT_TO_TABLE(stringtext, pattern text [, flagstext])SELECT SPLIT_PART(‘hello#world’,’#’,2) — 结果是’world’SELECT REGEXP_SPLIT_TO_ARRAY(‘hello#world’,’#’) — 结果是{hello,world}SELECT REGEXP_SPLIT_TO_TABLE(‘hello#world’,’#’) as split_res — 结果是两行,第一行hello,第二行world字符串查找、反转与替换POSITION(substring in string) — 查找SELECT POSITION(‘h’ in ‘hello world’) — 结果是1 ， 这里从1开始计数REVERSE(str)REPEAT(string text, number int)REPLACE(string,string,string)SELECT REPLACE(‘hello world’,’ ‘,’#’)REGEXP_MATCHES(string text,pattern text [, flags text])REGEXP_REPLACE(string text,pattern text,replacement text[, flags text])SELECT REGEXP_MATCHES(‘hello world’,’.o.’,’g’) — 返回两行 ， 第一行是’lo ‘,第二行是’wor’SELECT REGEXP_MATCHES(‘hello world’,’.o.’) — 返回第一个匹配 ， ’lo ‘时间处理SELECT TO_CHAR(TO_TIMESTAMP(CREATE_TIME),’YYYY-MM-DD HH24:MI:SS’)SELECT EXTRACT(YEAR FROM NOW());postgresql处理时间函数 截取hh:mm/yyyy-mm-dd转自 http://www.henanlong.com/c/051A20M12022.html

    数据是非常重要的资产，然而任何数据库在日常运行过程中都存在一定的安全隐患，特别像银行、证券等金融单位，在日常运行过程中，为防止出现系统故障、病毒或者用户操作不当而导致数据丢失，必须要有良好的预备方案，以恢复数据。数据库备份和恢复是预防灾难的一个非常有效的手段，数据库备份与恢复是数据库管理员的重要管理职责，而数据库备份是否成功对数据恢复至关重要。一、备份与恢复概述    为了保证计算机系统的备份和高可用性，很多高性能服务器经常采用多种备份策略，例如RAID技术、双机热备、集群技术等，这些备份策略是从硬件的角度来考虑的，这些策略能够部分解决数据库备份问题。如磁盘介质损坏，可以快速地在镜像上做简单的恢复。然而，这种硬件的备份并不能满足现实的需要，比如数据表被误删除。    数据库备份与恢复是一对相反操作。备份是保存数据库中数据的副本，实际上就是把数据库复制到转储设备（磁盘或者磁带）的过程；恢复是指当发生各种故障（硬件故障、软件故障、网路故障、系统故障等）造成数据库瘫痪或者错误，利用备份将数据库恢复到故障时刻的状态，重构完整的数据库。数据库恢复可分为数据库修复以及在数据库修复基础上的数据库恢复。    1、备份的类型    根据数据备份方式的不同，备份可以分为物理备份和逻辑备份两种。物理备份是将组成数据库的数据文件、重做日志文件、控制文件、初始化参数文件等系统文件进行复制，将形成的副本保存到与当前系统独立的磁盘或者磁带上。逻辑备份是指利用oracle提供的导出工具将数据库中的数据抽取出来，存放到一个二进制文件中。    2、恢复的类型    根据数据库恢复时使用的备份不同，恢复可以分为物理恢复和逻辑恢复两种。物理恢复是指利用物理备份来恢复数据库，即利用物理备份文件恢复损毁文件，是在操作系统级别上进行。而逻辑恢复是利用逻辑备份的二进制文件，使用oracle提供的导入工具将部分或者全部信息导入数据库，恢复丢失的数据。根据数据库恢复的程度可分为完全恢复和不完全恢复。数据库出现故障后，如果能够利用备份使数据库恢复到出现故障时的状态，称为完全恢复，否则称为不完全恢复。二、数据的导出与导入    导出时将数据库中选定的对象或者数据字典的逻辑副本以二进制文件的形式存储到操作系统中，dmp文件格式存储；恢复的时候，从dmp格式文件恢复到数据库中。    1、数据的导出    oracle11g数据库使用exp工具实现数据的逻辑备份，该工具有3种工作方式。    命令行方式：在命令行中直接指定参数设置。    参数文件方式：将参数的设置信息存放到一个参数文件中，在命令行中用parfile参数指定参数文件。    交互方式：通过交互命令进行导出作业的管理。    Exp工具提供了4中导出模式。    全库导出模式：导出整个数据库。    模式导出模式：导出指定模式中的所有对象。    表导出模式：导出指定模式中指定的所有表、分区及其依赖的对象和数据。    表空间导出模式：导出指定表空间中所有表及其依赖对象。    这4种模式主要通过选择参数来进行相应的设置。    下面主要介绍命令行方式和交互方式。    命令行方式：    导出scott用户下的emp表和dept表，存储文件名称为output.dmp，日志文件为output.log    首先进入指定存储文件的目录，假设目录名称为C盘的backup，然后执行相应的命令。    cd backup    exp scott/tiger dumpfile=output.dmp logfile=output.log tables=scott.emp,scott.dept    导出scott用户下所有对象    cd backup    exp scott/tiger dumpfile=user.dmp    交互方式    首先需要准备一个进行数据备份的目录，假设现在将D:\backup目录作为备份路径。    需要进入到backup目录中（以命令行的方式进行操作）。    cd backup;    输入exp指令，导出数据，输入用户名和口令（例如scott/tiger）；    2、数据的导入    oracle11g数据库使用imp工具实现备份数据的导入，该工具和导出工具exp一样也有3中工作方式：命令行方式、交互方式和参数文件方式。    命令行方式：    scott用户下的emp和dept表数据丢失，利用前面导出的备份文件恢复。    首先进入指定存储文件的目录，假设目录名称为C盘的backup，然后执行相应命令。    cd backup    imp scott/tiger dumpfile=output.dmp tables=scott.emp,scott.dept;    假设scott用户下的对象丢失，利用前面所备份的文件恢复scott用户下所有对象。    cd backup    imp scott/tiger dumpfile=user.dmp三、数据库的冷备份及恢复    数据库的冷备份严格来说称为归档备份，指的是数据库要关闭服务，所有的事务都需要提交了。从实际来说，如果要进行备份则需要备份以下内容。    控制文件：控制着整个oracle的实例信息，可以使用v c o n t r o l f i l e 数 据 字 典 找 到 。 重 做 日 志 文 件 ： 通 过 v controlfile数据字典找到。 重做日志文件：通过v controlfile数据字典找到。重做日志文件：通过vlogfile数据字典找到    数据文件：通过v d a t a f i l e 数 据 字 典 找 到 。 核 心 配 置 文 件 （ p f i l e ） ： 使 用 “ s h o w p a r a m e t e r p f i l e ” 找 到 。 冷 备 份 操 作 数 据 库 的 备 份 操 作 由 管 理 员 进 行 ， 因 此 首 先 以 管 理 员 身 份 登 录 。 1 、 使 用 s y s 登 录 c o n n s y s / c h a n g e o n i n s t a l l a s s y s d b a ; 2 、 查 找 控 制 文 件 的 信 息 s e l e c t ∗ f r o m v datafile数据字典找到。 核心配置文件（pfile）：使用“show parameter pfile”找到。 冷备份操作 数据库的备份操作由管理员进行，因此首先以管理员身份登录。 1、使用sys登录 conn sys/change_on_install as sysdba; 2、查找控制文件的信息 select * from v datafile数据字典找到。核心配置文件（pfile）：使用“showparameterpfile”找到。冷备份操作数据库的备份操作由管理员进行，因此首先以管理员身份登录。1、使用sys登录connsys/changeo​ni​nstallassysdba;2、查找控制文件的信息select∗fromvcontrolfile;    3、查找重做日志文件信息。    select * from v l o g f i l e ; 4 、 找 到 所 有 数 据 文 件 信 息 s e l e c t ∗ f r o m v logfile; 4、找到所有数据文件信息 select * from v logfile;4、找到所有数据文件信息select∗fromvdatafile;    5、找到PFILE文件    show parameter pfile;    6、记录好2、3、4、5命令执行后所显示的文件路径。    7、关闭oracle服务。    shutdown immediate    8、复制出所有的备份文件。    9、重新启动服务；    startup    这种备份是允许关闭计算机的备份。    冷恢复    当数据库系统出现错误的时候，可以使用冷备份的文件进行恢复。    步骤如下    1、关闭oracle服务。    shutdown immediate    2、将备份时复制出的所有备份文件复制到原来的目录下。    3、重新启动服务。    startup————————————————版权声明：本文为CSDN博主「旷世奇才李先生」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/weixin_44096133/article/details/125649055

1.定义不同 数据库审计:以安全事件为中心,以全面审计和精确审计为基础,实时记录网络上的数据库活动,对数据库操作进行细粒度审计的；2.审计重点不一样 数据库审计是数据库安全技术之一,它主要是通过对用户访问数据库行为的记录、分析和汇报,来帮助用户事后生成合规报告；3.审计对象不一样 日志审计主要是对企业企业安全设备进行审计,而数据库审计主要对象是数据库。漏扫也可以很便携铱迅漏洞扫描系统自发布以来，深受广大用户喜爱，除了传统的机架式，还推出了便携式漏扫，适用于有线及无线等各种网络环境，无需在机房中部署;又异于常见桌面式小型设备，更加轻巧便携。既可以满足移动办公的需要，还能够满足特殊机构单位的移动办公和垂直监查需求。铱迅漏洞扫描系统支持主机漏洞扫描、Web漏洞扫描、弱密码扫描等，帮助您快速掌握主机中存在的脆弱点，能够降低与缓解主机中的漏洞造成的威胁与损失。

Oracle的闪回技术提供了一组功能，可以访问过去某一时间的数据并从人为错误中恢复。闪回技术是Oracle 数据库独有的，支持任何级别的恢复，包括行、事务、表和数据库范围。使用闪回特性，您可以查询以前的数据版本，还可以执行更改分析和自助式修复，以便在保持数据库联机的同时从逻辑损坏中恢复。闪回技术包括以下特性：   (1) 闪回查询允许用户查询过去某个时间点的数据，以重新构建由于意外删除或更改而丢失的数据。   (2) 闪回版本查询提供了一种查看行级数据库随时间变化的方法。   (3) 闪回事务查询提供了一种查看事务级数据库变化的方法。   (4) 闪回数据库是进行时间点恢复的新策略。它能够快速将Oracle 数据库恢复到以前的时间，正确更正由于逻辑数据损坏或用户错误而引起的任何问题。   (5) 闪回表功能使 DBA 可以非常快速、轻松地将一个表或一组表恢复至过去特定的某一时间点。   (6) 闪回删除在删除对象时提供了一个安全网，您可以非常快速、轻松地取消对一个表及其相关对象对象的删除。　Flashback技术是以Undo Segment中的内容为基础的， 因此受限于UNDO_RETENTON参数。要使用flashback 的特性，必须启用自动撤销管理表空间。闪回参数如下：　　Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0　　Connected as system@LOCAL_DB　　SQL> show parameter undo_　　NAME TYPE VALUE　　------------------------------------ ----------- ------------------------------　　undo_management string AUTO　　undo_retention integer 900 --默认是1440 即24*60,只保留一天的闪回数据，具体值在安装数据库实例时可以设置　　undo_tablespace string UNDOTBS1接下来讲讲oracle数据库闪回类型主要有三种：　　(1) 闪回数据库（flashback database）　　　　闪回数据库就是当出现逻辑错误时，能够将整个数据库回退到出错前的那个状态。　　(2) 闪回删除（flashback drop）　　　　Oracle10g之前，一旦删除了一个表，那么该表就会从数据字典里面删除。要恢复该表，需要进行不完全恢复。　　　　Oracle10g以后，当我们删除表时，默认Oracle只是在数据库字典里面对被删的表的进行了重命名，并没有真正的把表删除。　　(3) 闪回表(flashback table)　　  所谓闪回表，就是将表里的数据会退到历史上的某个时间点，例如回退到用户误删除数据之前的时间点，从而将误删除的数据恢复回来，在这个过程中，数据库仍然可用，而且不需要类似于闪回日志一样的额外空间。闪回表利用的是undo表空间里记录的数据被改变前的值，如果闪回表所需要的undo数据，由于保留的时间超过了初始化参数undo_retention所指定的值，从而导致该undo数据块被其他事务覆盖，就不能恢复到指定的时间点了。一、闪回数据库详解　　Flashback Database整个架构包括一个进程Recover Writer(RVWR)后台进程、Flashback Database Log日志和Flash Recovery Area。闪回数据库的实现机理是闪回日志，只要配置了闪回数据库，就会自动创建闪回日志。只要数据库里的数据发生变化，oracle会将数据修改前的旧值保存在闪回日志中。　　数据库的Flashback Database功能缺省是关闭的，要想启用这个功能，就需要做如下配置。（1）数据库配置为归档模式　　SQL> select log_mode from v$database;　　　　LOG_MODE　　　　------------　　　　NOARCHIVELOG　　SQL> archive log list;　　　　数据库日志模式 非存档模式　　　　自动存档 禁用　　　　存档终点 USE_DB_RECOVERY_FILE_DEST　　　　最早的联机日志序列 320　　　　当前日志序列 3221、启动归档为归档模式（其与将非归档模式转为归档模式步骤相似）：　　(1)保存一致性,先关闭数据库　　　　shutdown immediate ;　　(2)启动到mount阶段　　　　startup mount;　　(3)启动闪回功能　　　　alter database flashback on; --alter database archivelog;将数据库改为规定模式　　(4)切换到open阶段　　　　alter database open;　　(5)对数据做一个完整备份　　　　full backup --可以使用数据泵(expdp)或者使用RMAN　启动闪回功能完善。　2、配置闪回恢复区（flash recovery area FRA）　SQL> show parameter db_recovery_file　　NAME TYPE VALUE　　------------------------------------ ----------- ------------------------------　　db_recovery_file_dest string D:\app\flash_recovery_area　　db_recovery_file_dest_size big integer 2G一般在安装数据库实例时，会让你选择是否开启oracle数据闪回功能，默认是开启的，安装过程中可以配置闪回路径以及闪回空间的大小。3、配置闪回保留时间　　SQL> show parameter db_flashback_retention_target　　　　NAME TYPE VALUE　　　　------------------------------------ ----------- ------------------------------　　　　db_flashback_retention_target integer 1440　　　　db_flashback_retention_target参数用来控制flashback log 数据保留的时间，默认值是1440，单位是minute，即24小时。　4、启动数据库实例即可　5、获取当前时间点和SCN　　　SQL> select dbms_flashback.get_system_change_number from dual;　　　GET_SYSTEM_CHANGE_NUMBER　　　　------------------------　　　　 13081009　　　SQL> select to_char(systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt from dual;　　　SYSDT　　　　-------------------　　　　2013-08-25 21:12:46　　　SQL> truncate table dept;　　　　Table truncated　Flashback Database 实际是对数据库的一个不完全恢复操作，因为需要关闭数据库重启到mount状态。　　SQL> shutdown immediate;　　 数据库已经关闭。　　 已经卸载数据库。　　 ORACLE 例程已经关闭。　 SQL> startup mount;　　ORACLE 例程已经启动。　　Total System Global Area 1071333376 bytes　　Fixed Size 1348508 bytes　　Variable Size 385879140 bytes　　Database Buffers 679477248　　bytes Redo Buffers 4628480 bytes　　数据库装载完毕。　 SQL> flashback database to timestamp to_timestamp('2013-08-25 21:12:46','yyyy-mm-dd HH24:MI:SS');闪回完成。　或者使用 SQL> flashback database to scn 13081009;　在执行完flashback database 命令之后，oracle 提供了两种方式让你修复数据库：　　1) 直接alter database open resetlogs打开数据库，当然，指定scn或者timestamp时间点之后产生的数据统统丢失。　　2) 先执行alter database open read only命令以read-only模式打开数据库，然后立刻通过逻辑导出的方式将误操作涉及表的数据导出，再执行recover database命令以重新应用数据库产生的redo，将数据库修复到flashback database 操作前的状态，然后再通过逻辑导入的方式，将之前误操作的表重新导入，这样的话对现有数据的影响最小，不会有数据丢失。　 SQL>alter database open read only;查询被truncate的表，是否已经闪回数据。　　SQL> select deptno from dept;　　deptno　　----------------------------------------　　0105206000001　　0105108040001　　0105304000001　　0105304010001　　010560001　　0105600010001　　0105601000001　　0105602000001　　证明闪回成功。二、闪回删除　　Oracle10g以后，当我们删除表时，默认Oracle只是在数据库字典里面对被删的表的进行了重命名，并没有真正的把表删除。　　回收站(recyclebin)：用来维护表被删除前的名字与删除后系统生成的名字之间的对应关系的数据字典，表上的相关对象（索引、触发器等）也会一并进入回收站。　　SQL> show parameter recycle　　NAME                                 TYPE        VALUE　　------------------------------------ ----------- ------------------------------　　buffer_pool_recycle                  string     　　db_recycle_cache_size                big integer 0　　recyclebin                                                  on实例一：闪回sys用户下的表SQL> select * from user_recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------SQL> drop table dept;Table droppedSQL> select * from user_recyclebin;OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------SQL>flashback table dept to before drop; --或者使用(flashback table dept to before drop rename to dept_bak;)报如下错误：ORA-38305: 对象不在回收站中原因：Flashback不支持sys用户与system表空间下的对象，也不能从回收站里拿到。故使用SYS或者SYSTEM用户登陆时,show recyclebin为空。实例二：闪回非sys、system用户下的表先删除表：SQL> drop table DEPT_BAK;Table droppedSQL> select * from user_recyclebin a where a.original_name = 'DEPT_BAK';OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------BIN$BnpnsFC8SfSYdJ7Qio0k2g==$0 DEPT_BAK DROP TABLE TESTSPACE 2013-04-21:15:38:04 2013-08-25:22:11:45 13087074 YES YES 745783 745783 745783 8SQL> flashback table DEPT_BAK to before drop;Done备注：1、 禁用与启用recyclebin该功能：　　SQL> alter system set recyclebin=off;　　SQL> alter system set recyclebin=on;禁用后删除的对象将直接删除，不会写到Recyclebin中，当然在删除时，指定purge参数,表也将直接删除，不会写到recyclebin中。　　SQL> drop table name purge;2、关于purge命令　　表空间的RecycleBin区域只是一个逻辑区域，而不是从表空间上物理的划出一块区域固定用于回收站，因此RecycleBin是和普通对象共用表空间的存储区域，或者说是RecycleBin的对象要和普通对象抢夺存储空间。当发生空间不够时，Oracle会按照先入先出的顺序覆盖RecycleBin中的对象。也可以手动的删除Recycle Bin占用的空间。　　1) Purge tablespace tablespace_name;  用于清空表空间的RecycleBin　　2) Purge tablespace tablespace_name user user_name;  清空指定表空间的Recycle Bin中指定用户的对象　　3) Purge recyclebin;  删除当前用户的Recycle Bin中的对象　　4) Purge dba_recyclebin  删除所有用户的Recycle Bin中的对象，该命令要sysdba权限　　5) Drop table table_name purge;  删除对象并且不放在Recycle Bin中，即永久的删除，不能用Flashback恢复。　　6) Purge index recycle_bin_object_name;  当想释放Recycle bin的空间，又想能恢复表时，可以通过释放该对象的index所占用的空间来缓解空间压力。因为索引是可以重建的。三、闪回表　　查询过去某个时刻表的数据的情况，一旦确认某个时刻的数据满足我们的需求以后，可以根据这个时间执行闪回表。　　ORA-08189: 因为未启用行移动功能, 不能闪回表，需要先执行一个命令：　　ALTER TABLE dept ENABLE ROW MOVEMENT。语法一：　　select * from dept　　VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY AND SYSTIMESTAMP -INTERVAL '1' HOUR;语法二：　　select * from dept as of timestamp to_timestamp('2013-08-25 21:12:46','yyyy-mm-dd HH24:MI:SS');语法三：　　select * from dept as of scn 13081009;语法四：　　select * from flashback_transaction_query a where a.table_name='DEPT';附注：interval用法Oracle语法:INTERVAL '{ integer | integer time_expr | time_expr }' { { DAY | HOUR | MINUTE } [ ( leading_precision ) ] | SECOND [ ( leading_precision[, fractional_seconds_precision ] ) ] } [ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND表示: 20天 - 240小时 = 10天0秒INTERVAL '30.12345' SECOND(2,4)表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入.查询tableA中10分钟前的数据（闪回查询，前提：undo没有被覆盖..如果undo被覆盖,查询会失败）Select * From table dept As Of Timestamp (systimestamp - Interval '10' minute);-------------------THE END----------------------------参考：cid:link_0

Oracle数据库的存储过程：存储过程是一段为了完成特定功能的sql语句集，是一段sql代码片段。经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它，因为他是一段sql语句代码并已经存放在数据库中，因此它的执行效率非常高。1.存储过程的创建create [or replace] procedure +过程名 [(参数名in|out 数据类型)]　as|is　　　　变量的声明　　begin　　　 plsql的子程序体;　　end;//如果是is，end 后面要加上过程名2.存储过程的调用　第一种：call 存储过程名（参数）；　　第二种：begin　　　　　　 　　存储过程名（参数）；　　　　　　end；3.存储过程的创建和调用的实例例如1：给指定的员工涨工资100，并打印出涨前和涨后的工资创建存储过程：　　　　create or replace procedure addsal(eno in number) is　　　　　　pemp emp%rowtype;　　　　begin　　　　　　select * into pemp from emp where empno = eno;　　　　　　update emp set sal = sal + 100 where empno = eno;　　　　　　dbms_output.put_line('ename:' || pemp.ename || 'after' || (pemp.sal+100));　　　　end addsal;调用存储过程：　　　　第一种调用：call addsal(7369);　　　　第二种调用：　　　　　　begin　　　　　　　　addsal(eno=>7369);　　　　　　　　commit;//因为oracle数据库是手动提交的，所有涉及到的增删改都必须commit；　　　　　　end;例如2：输出所有员工的姓名和工资创建存储过程：　　　　create or replace procedure infoemp as　　　　　　cursor allemp is　　　　　　　　select * from emp;//创建一个游标　　　　　　enemp emp%rowtype;　　　　begin　　　　　　open allemp;　　　　　　loop　　　　　　　　fetch allemp　　　　　　　　　　into enemp;//利用循环从游标中取数据　　　　　　　　dbms_output.put_line(enemp.ename || ' '||enemp.sal);　　　　　　exit when allemp%notfound;//退出循环条件　　　　　　end loop;　　　　　　close allemp;　　　　end;调用存储过程：第一种：　call infoemp();第二种： begin　　　　　　infoemp();　　　　end;以上就是oracle数据库的存储过程的基本知识和用法。转自 https://zhuanlan.zhihu.com/p/257816485

Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。 oracle 数据库中主要使用两种类型的函数：1.  单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回 x 除以 y 的余数（x 和 y 可以是两个整数，也可以是表中的整数列）。常用的单行函数有：Ø  字符函数：对字符串操作。Ø  数字函数：对数字进行计算，返回一个数字。Ø  转换函数：可以将一种数据类型转换为另外一种数据类型。Ø  日期函数：对日期和时间进行处理。2.  聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x)返回结果集中 x 列的总合。一、字符函数字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。常用的字符函数：函数说明ASCII(X)返回字符X的ASCII码CONCAT(X,Y)连接字符串X和YINSTR(X,STR[,START][,N)从X中查找str，可以指定从start开始，也可以指定从n开始LENGTH(X)返回X的长度LOWER(X)X转换成小写UPPER(X)X转换成大写LTRIM(X[,TRIM_STR])把X的左边截去trim_str字符串，缺省截去空格RTRIM(X[,TRIM_STR])把X的右边截去trim_str字符串，缺省截去空格TRIM([TRIM_STR  FROM]X)把X的两边截去trim_str字符串，缺省截去空格REPLACE(X,old,new)在X中查找old，并替换成newSUBSTR(X,start[,length])返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾上面各函数的例子：示例示例结果SELECT ASCII('a') FROM dual;97SELECT CONCAT('Hello','world') FROM dual;HelloworldSELECT INSTR('Hello world','or') FROM dual;8SELECT LENGTH('Hello') FROM dual;5SELECT LOWER('Hello') FROM dual;helloSELECT UPPER('hello') FROM dual;HELLOSELECT LTRIM('=Hello=','=') FROM dual;Hello=SELECT RTRIM('=Hello=','=') FROM dual;=HelloSELECT TRIM('='FROM'=Hello=') FROM dual;HelloSELECT REPLACE('ABCDE','CD','AAA')FROM dual;ABAAAESELECT SUBSTR('ABCDE',2,3) FROM dual;BCD二、数字函数数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。函数说明示例ABS(X)X的绝对值ABS(-3)=3ACOS(X)X的反余弦ACOS(1)=0COS(X)余弦COS(1)=0.54030230586814CEIL(X)大于或等于X的最小值CEIL(5.4)=6FLOOR(X)小于或等于X的最大值FLOOR(5.8)=5LOG(X,Y)X为底Y的对数LOG(2，4)=2MOD(X,Y)X除以Y的余数MOD(8，3)=2POWER(X,Y)X的Y次幂POWER(2，3)=8ROUND(X[,Y])X在第Y位四舍五入ROUND(3.456，2)=3.46SQRT(X)X的平方根SQRT(4)=2TRUNC(X[,Y])X在第Y位截断TRUNC(3.456，2)=3.45说明：1.  ROUND(X[,Y])，四舍五入。在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。2.  TRUNC(x[,y])，直接截取，不四舍五入。在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。三、日期函数日期函数对日期进行运算。常用的日期函数有：1、ADD_MONTHS(d,n)，在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期，n 表示要加的月数。例：SELECT SYSDATE,add_months(SYSDATE,5) FROM dual; 2、LAST_DAY(d)，返回指定日期当月的最后一天。例：SELECT SYSDATE,last_day(SYSDATE) FROM dual; 3、ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式模型。默认 fmt 为 DDD，即月中的某一天。Ø  ① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。Ø  ② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。Ø  ③ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。Ø  ④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。例：SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual; 与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。4、EXTRACT(fmt FROM d)，提取日期中的特定部分。fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。例：SELECT SYSDATE "date",       EXTRACT(YEAR FROM SYSDATE)"year",       EXTRACT(MONTH FROM SYSDATE)"month",       EXTRACT(DAY FROM SYSDATE)"day",       EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",       EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",       EXTRACT(SECOND FROM SYSTIMESTAMP)"second"FROM dual;四、转换函数转换函数将值从一种数据类型转换为另外一种数据类型。常见的转换函数有：1、TO_CHAR(d|n[,fmt])把日期和数字转换为制定格式的字符串。Fmt是格式化字符串代码演示：TO_CHAR对日期的处理SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual; 代码解析：在格式化字符串中，使用双引号对非格式化字符进行引用针对数字的格式化，格式化字符有：参数示例说明9999指定位置处显示数字.9.9指定位置返回小数点,99,99指定位置返回一个逗号$$999数字开头返回一个美元符号EEEE9.99EEEE科学计数法表示LL999数字前加一个本地货币符号PR999PR如果数字式负数则用尖括号进行表示代码演示：TO_CHAR对数字的处理SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual; 2、TO_DATE(X,[,fmt])把一个字符串以fmt格式转换成一个日期类型3、TO_NUMBER(X,[,fmt])把一个字符串以fmt格式转换为一个数字代码演示：TO_NUM函数SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual; 五、其它单行函数1、NVL(X,VALUE)如果X为空，返回value，否则返回X例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元代码演示：NVL函数SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;-------------------------------------------------------------------ENAME    JOB    SAL    NVL(COMM,100) SMITH    CLERK    800    100 ALLEN    SALESMAN    1600    300 WARD    SALESMAN    1250    500 MARTIN    SALESMAN    1250    1400 TURNER    SALESMAN    1500    50 ADAMS    CLERK    1100    100 JAMES    CLERK    950    100 -------------------------------------------------------------------7 rows selected2、NVL2(x,value1,value2)如果x非空，返回value1，否则返回value2例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元代码演示：NVL2函数SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm"2   FROM EMP WHERE SAL<2000;-------------------------------------------------------------------ENAME    JOB    SAL    comm SMITH    CLERK    800    200 ALLEN    SALESMAN    1600    400 WARD    SALESMAN    1250    600 MARTIN    SALESMAN    1250    1500 TURNER    SALESMAN    1500    150 ADAMS    CLERK    1100    200 JAMES    CLERK    950    200 MILLER    CLERK    1300    200-------------------------------------------------------------------------------------------------------8 rows selected六、聚合函数聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。名称作用语法AVG平均值AVG（表达式）SUM求和SUM(表达式)MIN、MAX最小值、最大值MIN(表达式)、MAX(表达式)COUNT数据统计COUNT（表达式）例：求本月所有员工的基本工资总和代码演示：sum函数SQL> SELECT SUM(sal) FROM emp;-------------------------------------------------------------------SUM(SAL)29025例：求不同部门的平均工资代码演示：AVG函数下的分组查询SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;  -------------------------------------------------------------------DEPTNO    AVG(SAL)---------    ----------转自 https://www.cnblogs.com/lxl57610/p/7442130.html

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。范式简介编辑 播报 范式来自英文Normal form，简称NF。要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。各类范式编辑 播报 1、第一范式（1NF）：所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。2、第二范式（2NF）在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。（该主键的添加是在ER设计时添加，不是建库时随意添加）第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。3、第三范式（3NF）在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）数据库范式第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。4、巴斯-科德范式（BCNF）Boyce-Codd Normal Form（巴斯-科德范式）在3NF基础上，任何主属性不能对主键子集依赖（在3NF基础上消除主属性对主码子集的依赖）巴斯-科德范式（BCNF）是第三范式（3NF）的一个子集，即满足巴斯-科德范式（BCNF）必须满足第三范式（3NF）。通常情况下，巴斯-科德范式被认为没有新的设计规范加入，只是对第二范式与第三范式中设计规范要求更强，因而被认为是修正第三范式，也就是说，它事实上是对第三范式的修正，使数据库冗余度更小。这也是BCNF不被称为第四范式的原因。某些书上，根据范式要求的递增性将其称之为第四范式是不规范，也是更让人不容易理解的地方。而真正的第四范式，则是在设计规范中添加了对多值及依赖的要求。 [1] 目的原则编辑 播报 规范化目的是使结构更合理，消除存储异常，使数据冗余尽量小。便于插入、删除和更新。遵从概念单一化“一事一地”原则，即一个关系模式描述一个实体或实体间的一种联系。规范的实质就是概念的单一化。一个关系模式接着分解可以得到不同关系模式集合，也就是说分解方法不是惟一的。最小冗余的要求必须以分解后的数据库能够表达原来数据库所有信息为前提来实现。其根本目标是节省存储空问，避免数据不一致性，提高对关系的操作效率，同时满足应用需求。实际上，并不一定要求全部模式都达到BCNF不可。有时故意保留部分冗余可能更方便数据查询。尤其对于那些更新频度不高，查询频度极高的数据库系统更是如此。反范式编辑 播报 规范化的优点是明显的，它避免了大量的数据冗余，节省了存储空间，保持了数据的一致性。当一个库里的数据经常发生变化时，达到3NF的库可以使用户不必在超过两个以上的地方更改同一个值。那么是不是只要把所有的表都规范为3NF后，数据库的设计就是最优的呢?这可不一定。范式越高意味着表的划分更细，一个数据库中需要的表也就越多，用户不得不将原本相关联的数据分摊到多个表中。当用户同时需要这些数据时只能采用连接表的形式将数据重新合并在一起。同时把多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。 [2] 转自 https://baike.baidu.com/item/数据库范式/7309898

关键字：split_part、unnest 、string_to_array（1）获取数据with 排名表 as(select 'A' as name , 67 语文,47 数学        union all                                       select 'B' as name ,58 语文,96 数学                 )  select * from 排名表 （2）列转行1、先拼接转换成字符串的值，然后使用string_to_array，将指定符号分割开的内容转数组。2、然后将转换后的数组使用unnest进行行转置。3、最后根据split_part进行字符串切割，使用索引取指定列的值。with tmp as (select name,'语文' || '~' || 语文 || ',' || '数学' || '~' || 数学 as concat_co from 排名表)select * from tmpselect name,split_part(unnest(string_to_array(concat_co,',')),'~',1) as 科目,split_part(unnest(string_to_array(concat_co,',')),'~',2) as 成绩 from tmp    1    2在这里插入图片描述四、Pgsql行专列关键字：sum … filter(where…)（1）获取数据with 排名表 as(select 'A' as name ,'语文' 科目, 67 成绩        union all                                        select 'A' as name ,'数学' 科目, 47 成绩        union all                                       select 'B' as name ,'语文' 科目, 58 成绩        union all                                        select 'B' as name ,'数学' 科目, 96 成绩)select * from 排名表在这里插入图片描述（2）使用filter行转列 select name,            sum(成绩) filter(where 科目='语文') 语文,sum(成绩) filter(where 科目='数学') 数学    from 排名表    group by name————————————————版权声明：本文为CSDN博主「手心儿有糖」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/Lelouch_Vi/article/details/125593598

一、Oracle列转行 关键字：unpivotdemno1:（1）先获取数据with 排名表 as (select a.* ,dense_rank() over(order by chinese desc) chinese_rank ,dense_rank() over(order by math desc) math_rank from (select 'A' as name ,36 chinese, 67 math FROM dual union all select 'B' as name ,56 chinese, 47 math FROM dual union all select 'C' as name ,78 chinese, 58 math FROM dual union all select 'D' as name ,53 chinese, 96 math FROM dual union all select 'E' as name ,87 chinese, 63 math FROM dual ) a order by name ) SELECT * FROM 排名表（2）列转行需求分析：1.添加学科列，字段名为“学科”，取分别为“语文”，“数学”2.将语文成绩和数学成绩两列转成一列，并给新字段名为“成绩”3.将语文排名和数学排名两列转成一列，并给新字段名为“排名”select name,学科,成绩,排名 from 排名表 unpivot-- 列转行 ( (成绩,排名) for 学科 in ((chinese,chinese_rank) as '语文', (math ,math_rank ) as '数学') ) order by nameunpivot用法总结：select 原字段1，新字段名1，新字段名2，新字段名3 别名from 表名 unpivot （ （新字段名2，新字段名3） for ​ 新字段名1 in （（原字段2，原字段3） as 新字段名1值1, ​ (原字段4，原字段5) as 新字段名1值2， ​ …） ）二、Oracle行转列 关键字：pivotdemno1:(1)获取数据with 排名表 as (select 'A' as name ,'语文' sub, 67 sco FROM dual union all select 'A' as name ,'数学' sub, 47 sco FROM dual union all select 'B' as name ,'语文' sub, 58 sco FROM dual union all select 'B' as name ,'数学' sub, 96 sco FROM dual ) SELECT * FROM 排名表（2）pivot行转列需求分析：1.NAME字段一列转变多列2.将科目一列转成两列，分别为“语文”、“数学”。值为对应的分数。SELECT * FROM 排名表 pivot ( sum(sco) FOR sub IN ('语文' 语文,'数学' 数学) ) ORDER BY name;pivot用法总结：select * from 表名 pivot （ sum(做为值的字段名) for ​ 拆分的原字段名 in （值1 新列名1，值2 新列名2 ，…） ）ORDER BY 未变动的列名———————————————— 版权声明：本文为CSDN博主「手心儿有糖」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：cid:link_0