MySQL和Oracle语法_informix和oracle语法-程序员宅基地

技术标签: 数据库  DataBase  

一、DDL(Data Definition Language 数据定义语言)

用于操作对象和对象的属性,这种对象包括数据库本身,以及数据库对象,像:表、视图等等,DDL 对这些对象和属性的管理和定义具体表现在 create、drop 和 alter 上。特别注意:DDL 操作的“对象”的概念,”对象“包括对象及对象的属性,而且对象最小也比记录大个层次。以表举例:create 创建数据表,alter 可以更改该表的字段,drop 可以删除这个表。由此得知,DDL 不涉及具体的数据。所有的 DDL 均将被隐式提交,无法 ROLLBACK。

1️⃣DDL的主要操作

  1. 【create】
    可以创建数据库和数据库的一些对象。

  2. 【drop】

  • drop 是 DDL,会隐式提交,所以不能回滚,不会触发触发器。
  • drop 删除表结构及所有数据,并将表所占用的空间全部释放。
  • drop 将删除表的结构,所依赖的约束,触发器,索引以及数据表的权限等,依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
  1. 【truncate】
    truncate table tabname一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
  • truncate 是 DDL,会隐式提交,所以不能回滚,不会触发触发器。
  • truncate 会删除表中所有记录,并且将重新设置高水线和所有的索引,缺省情况下将空间释放到 minextents 个 extent,除非使用 reuse storage。不会记录日志,所以执行速度很快,但不能通过 rollback 撤消操作(如果一不小心把一个表 truncate 掉,也是可以恢复的,只是不能通过 rollback 来恢复)。
  • 对于外键(foreign key)约束引用的表,不能使用 truncate table,而应使用不带 where 子句的 delete 语句。
  • truncate table不能用于参与了索引视图的表。
  1. 【alter】
    修改数据表定义及属性。

2️⃣DDL的操作对象(表)

  1. 表的概念
    表的创建就是用来存放数据用的,由于存放的数据的不同,所以需要定义些数据类型,以方便管理。

  2. 表的属性

【主键属性】
主键就是主键约束,主键的起名偏向于虚的(就是描述这件事),主键约束起名偏向于实得(就是描述操作的实施),描述的都是同一件事,主键约束就是表中的一个属性。在一个表中最多可以有一个主键,一个主键可以定义在一个或多个字段。主键使一个或多个字段的值必须唯一且不为空,这样做可以通过该字段或该组字段中的值唯一的代表一条记录。

【唯一属性】
一个表中只能有一个主键属性,为了方表用户,提出唯一约束。唯一约束可以定义在一个或多个字段上。唯一约束使该字段或该组字段中的值唯一,可以为空,但是不能重复。

【外键属性】
又叫外键、外键约束,跟主键和主键约束的关系是一样的。外键约束针对的两个表,如果表A的主关键字是表B中的字段,则该字段称为表B的外键,表A称为主表,表B称为从表。但要注意,必须要系统知道是这种关系。

【核查、Null 和缺省属性】
核查属性又叫核查约束,Null 属性又叫 Null 约束,缺省属性又叫缺省约束。这些名称是描述一件事,描述一种情况,这件事或这张情况。当然可以人为的那样特意做(输入数据时注意就行),但本意是实现自动化,也就是让计算机做这件事。

二、DML(Data Manipulation Language 数据操控语言)

用于操作数据库对象中包含的数据,也就是说操作的单位是记录。

1️⃣DML的主要语句操作

  1. 【insert】
    向数据表张插入一条记录。
  2. 【delete】
    删除数据表中的一条或多条记录,也可以删除数据表中的所有记录。但是,它的操作对象仍是记录。
  • delete 是DML,执行时,每次从表中删除一行,并且同时将该行的删除操作记录在 redo 和 undo 表空间中以便进行回滚(rollback)和重做操作,但要注意表空间要足够大,需要手动提交(commit)操作才能生效,可以通过 rollback 撤消操作。

  • delete 可根据条件删除表中满足条件的数据,如果不指定 where 子句,那么删除表中所有记录。

  • delete 不影响表所占用的 extent,高水线(high watermark)保持原位置不变。

  1. 【update】
    用于修改已存在表中的记录的内容。

2️⃣DML的操作对象——记录

三、DCL(Data Control Language 数据控制语句)

DCL的操作是数据库对象的权限,这些操作的确定使数据更加的安全。

1️⃣DCL的主要语句操作

  1. 【grant】
    允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限。
  2. 【revoke】
    可以废除某用户或某组或所有用户访问权限

2️⃣DCL的操作对象(用户):
此时的用户指的是数据库用户。

四、MySQL 相关基本语句

1️⃣插入记录

insert into tab (field1,field2) values (value1,value2);

2️⃣删除记录

delete from tab where conditions;

3️⃣更新记录

update tab set field1=value1,field2=value2 where conditions;

MySQL 给一个字段递增赋值:
①首先设置一个变量,初始值为0:set @r:=0;
②然后更新表中对应的ID列:update tab set id=(@r:=@r+1)
③如果是插入,那就找一个记录多的表 t1:
set @r:=0;
insert into t select @r:=@r+1 from t1 limit 0, 2000

4️⃣查询数据

select * from tab where conditions order by field desc;(精准查询)
asc升序【默认】 / desc降序
select * from tab where field like '%value%'(模糊查询)

order by id desc,time desc-----id 降序排列优先;id 一样的话,再按 time 降序排列(前提是满足 id 降序排列)。后面再加第三列的话,同理。
中文排序异常解决(第十点)

5️⃣提交数据/回滚数据

commit;//提交数据
rollback;//回滚数据

6️⃣总数

select count(*) from tab;

7️⃣求和/求平均值

select sum(field) as sumvalue from tab;//求和
select avg(field) as avgvalue from tab;//求平均值

8️⃣最大/最小

select max(field) as maxvalue from tab;//最大
select min(field) as minvalue from tab;//最小

9️⃣分组

grounp by:一张表,一旦分组完成后,查询只能得到组相关的信息。

between限制查询数据范围时包括了边界值。

select * from tab where time between time1 and time2;
select a,b,c from tab where a not between num1 and num2;

1️⃣1️⃣in的用法

select * from tab where a [not] in ('值1','值2','值3','值4')

1️⃣2️⃣两张关联表,删除主表中已经在副表中没有的信息

delete from tab1 where not exists 
(select * from tab2 where tab1.field1=tab2.field2)

1️⃣3️⃣四表联查

select * from a 
left inner join b on a.a=b.b 
right inner join c on a.a=c.c 
inner join d on a.a=d.d 
where conditions

1️⃣4️⃣查询相同条目并记录重复次数(大于2)

select a,count(a) from tab group by a having count(*) > 2

1️⃣5️⃣创建数据库/删除数据库

create database database_name;//创建数据库
drop database database_name;//删除数据库

1️⃣6️⃣创建新表/删除表

## 创建新表
create table dept(
    id  int(11)  not null  auto_increment,
    name  varchar(255)  default null,
    primary  key(dept_id)
 );
## 删除表
drop table tab;

1️⃣7️⃣根据已有的表创建新表

create table ntab like otab;(使用旧表B创建新表A)  (MySQL)

备注:此种方式在将表B复制到A时候,会将表B完整的字段结构和索引复制到表A中来。但不会复制数据。

create table ntab as select col1,col2... from otab definition only;		 

备注:此种方式只会将B表的字段结构复制到表A中来,但不会复制表B中的索引到表A中来。这种方式比较灵活可以在复制原来表结构的同时指定要复制哪些字段,并且自身复制表也可以根据需要增加字段结构。

1️⃣8️⃣新增表的别名

comment on tab col is '测试';

1️⃣9️⃣更改表名

alter table tab_oldName rename to tab_newName;

2️⃣0️⃣增加一个列

alter table tab add  column_name type;
alter table 表名 ADD 字段名称 字段类型(字段长短-选填)  
NOT NULL[是否为null] default 0 comment '字段备注';

2️⃣1️⃣添加主键/删除主键

alter table tab add primary key(col);//添加主键
alter table tab drop primary key(col);//删除主键

一个数据表只可以有一个主键,所以不存在删除某一列的主键。

2️⃣2️⃣创建索引/删除索引索引不可以更改,想更改必须删除重新建。

//创建索引
create [unique] index idxname on tab(col...);
//删除索引
drop index idxname;
drop index idxname on table;

2️⃣3️⃣查看某个表的索引

show index from table_name

2️⃣4️⃣创建视图/删除视图

//创建视图
create view viewname as select statement;
//删除视图
drop view viewname;

五、pro

select * from dba_blockers;查询锁
select * from dba_waiters;查询被阻塞的会话
select column_name from tab_old intersect select column_name from tab_new;显示两表的相同数据
select @@version; 或 select version();查看数据库版本
select database();查看当前数据库
select user();查看当前用户
show tables;查看所有表
show columns from table;查看表中的列的基本信息
desc table; 或 describe table; 表名后加字段名,查看该字段基本信息
select CHARACTER_LENGTH(col) from table;查询该字段值的长度

六、Oracle相关基本语句

1️⃣ Oracle查询表名

select table_name,tablespace_name,temporary 
from user_tables [where table_name=upper('表名')];
  1. table_name:表名(varchar2(30))
  2. tablespace_name:存储表名的表空间(varchar2(30))
  3. temporary:是否为临时表(varchar2(1))

2️⃣ Oracle查询表列名

select column_name,data_type,data_length,data_precision,data_scale 
from user_tab_columns [where table_name=upper('表名')];

select table_name,column_name
from user_tab_columns
where column_name like '%xxx%' and table_name like '%xxx%';
  1. column_name:列名(varchar2(30))
  2. data_type:列的数据类型(varchar2(106))
  3. data_length:列的长度(number)

另外,也可以通过 all_tab_columns来获取相关表的数据。

select * from all_tab_columns [where table_name='表名'];

3️⃣根据表名,查询表的索引

select * from user_indexes [where table_name=upper('表名')];
select* from all_indexes where table_name=upper('表名');
select * from user_ind_columns where table_name=upper('表名');

4️⃣根据索引名,查询表的索引字段

select * from user_ind_columns [where index_name=('索引名')];

5️⃣根据索引名,查询创建索引的语句

select dbms_metadata.get_ddl('INDEX','索引名', ['用户名']) from dual ;
-- ['用户名']可省,默认为登录用户。

6️⃣ Oracle查询数据库版本

select * from v$version;

select * from product_component_version  
where SUBSTR(PRODUCT,1,6)='Oracle';

select version from v$instance;


7️⃣ Oracle锁表查询和解锁方法

select sess.sid,sess.serial#, lo.oracle_username,
	   lo.os_user_name, ao.object_name, lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id=lo.object_id and lo.session_id=sess.sid;

-- 杀掉锁表进程
alter system kill session '66,88';-- 分别为SID和SERIAL#号

七、drop、truncate 和 delete 的区别

1️⃣truncate 与不带 where 的 delete:只删除数据,而不删除表的结构(定义)及其列、约束(constrain)、索引(index)等。drop 将删除整个表(结构和数据)、被依赖的约束、触发器(trigger)、索引,依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

2️⃣️truncate 只能对 table。delete 可以是 table 和 view。truncate 后,新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,需用 delete。

3️⃣一般速度上,drop > truncate > delete。delete 操作不会减少表或索引所占用的空间,truncate 会将该表和索引所占用的空间会恢复到初始大小,drop 将表所占用的空间全释放掉。

4️⃣delete 每次从表中删除一行,同时将该删除操作作为事务记录在日志中保存以便进行回滚操作。该操作是 DML 会被放到 rollback segment 中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

truncate 一次性删除表所有的数据并不记录日志,删除行不可恢复。并且在删除的过程中不会激活与表有关的删除触发器,执行速度快。truncate、drop 是 DDL 操作立即生效,原数据不放到 rollback segment 中,不能回滚。

5️⃣truncate table tabname速度快,效率高。truncate 在功能上与不带 WHERE 子句的 delete 语句相同:二者均删除表中的全部行。但 truncate 比 delete 速度快,且使用的系统和事务日志资源少。

6️⃣对于由 foreign key 约束引用的表,不能使用 truncate,而应使用不带 where 子句的 delete 语句。由于 truncate 不记录在日志中,所以它不能激活触发器。

7️⃣总结

  1. 在速度上,一般来说,drop> truncate > delete。

  2. 在使用 drop 和 truncate 时一定要注意,虽然可以恢复,但为了减少麻烦,还是要慎重。

  3. 如果想删除部分数据用 delete,注意带上 where 子句,回滚段要足够大;如果想删除表,当然用 drop;如果想保留表而将所有数据删除,如果和事务无关,用 truncate 即可;如果和事务有关,或者想触发 trigger,还是用 delete;如果是整理表内部的碎片,可以用 truncate 跟上 reuse stroage,再重新导入/插入数据。

八、MySQL 和 Oracle 的区别

MySQL 和 Oracle 都是流行的关系型数据库管理系统(RDBMS)。大多数数据库以类似的方式工作。在数据库管理部分,Oracle DBA 比 MySQL DBA 更有收益。与 MySQL 相比,Oracle DBA 有很多可用的范围。二者区别如下:

1️⃣本质的区别

  1. Oracle 数据库是一个对象关系数据库管理系统(ORDBMS)。它通常被成为 OracleRDBMS 或简称为 Oracle,是一个收费的数据库。
  2. MySQL 是一个开源的关系数据库管理系统(RDBMS)。它是世界上使用最多的 RDBMS(Relational Database Management System,关系数据库管理系统),作为服务器运行,提供多个数据库的多用户访问。它是一个免费的数据库。

2️⃣数据库安全性

  1. MySQL 使用三个参数来验证用户,即用户名、密码和位置。
  2. Oracle 使用了许多安全功能,如用户名,密码,配置文件,本地身份验证,外部身份验证,高级安全增强功能等。

3️⃣SQL语法的区别

Oracle 的 SQL 语法与 MySQL 有很大不同。Oracle 为称为 PL/SQL 的编程语言提供了更大的灵活性。Oracle 的 SQL*Plus 工具提供了比 MySQL 更多的命令,用于生成报表输出和变量定义。

4️⃣存储上的区别

与 Oracle 相比,MySQL 没有表空间、角色管理、快照、同义词和包以及自动存储管理。

5️⃣对象名称的区别

虽然某些模式对象名称在 Oracle 和 MySQL 中都不区分大小写,例如列、存储过程和索引等。但在某些情况下,两个数据库之间的区别大小写是不同的:

  1. Oracle 对所有对象名称都不区分大小写。
  2. 某些 MySQL 对象名称(如数据库和表)区分大小写(取决于底层操作系统)。

6️⃣运行程序和外部程序支持

  • Oracle 数据库支持从数据库内部编写,编译和执行的几种编程语言。此外,为了传输数据,Oracle 数据库使用 XML。
  • MySQL 不支持在系统内执行其他语言,也不支持 XML。

7️⃣MySQL和Oracle的字符数据类型比较

两个数据库中支持的字符类型存在一些差异。

  1. 对于字符类型,MySQL 具有 CHAR 和 VARCHAR,最大长度允许为65535字节(CHAR 最多可以为255字节,VARCHAR 为65535字节)。
  2. 而 Oracle 支持四种字符类型,即 CHAR、NCHAR、VARCHAR2和NVARCHAR2。所有四种字符类型都需要至少1个字节长;CHAR 和 NCAHR 最大可以是2000个字节,VARCHAR2 和 NVARCHAR2 的最大限制是4000个字节。可能会在最新版本中进行扩展。

8️⃣MySQL 和 Oracle 的额外功能比较

  1. MySQL 数据库不支持其服务器上的任何功能,如 Audit Vault。
  2. Oracle支持其数据库服务器上的几个扩展和程序,例如 Active Data Guard,Audit Vault,Partitioning 和 Data Mining 等。

9️⃣临时表的区别:Oracle 和 MySQL 以不同方式处理临时表。

  1. 在 MySQL 中,临时表是仅对当前用户会话可见的数据库对象,并且一旦会话结束,这些表将自动删除。
  2. Oracle 中临时表的定义与 MySQL 略有不同,因为临时表一旦创建就会存在,直到它们被显式删除,并且对具有适当权限的所有会话都可见。但是,临时表中的数据仅对数据插入表中的用户会话可见,并且数据可能在事务或用户会话期间持续存在。

MySQL 和 Oracle 中的备份类型

  1. Oracle 提供不同类型的备份工具,如冷备份,热备份,导出,导入,数据泵。Oracle 提供了最流行的称为 RecoveryManager(RMAN)的备份实用程序,可以使用极少的命令或存储脚本自动化备份调度和恢复数据库。
  2. MySQL 有 mysqldump 和 mysqlhotcopy 备份工具。在 MySQL 中没有像 RMAN 这样的实用程序。
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ChineseSoftware/article/details/118800276

智能推荐

springboot工程搭建_搭建好的springboot工程-程序员宅基地

文章浏览阅读82次。springboot工程搭建先搭建spring boot工程点next 配置jdk版本,打包方式等选择需要的组件会在创建工程时加入相关jar包最后一步点击完成等待加载jar包完成一个最简单的springboot项目就搭建好了下面我们引入web组件写一个简单的hello wordimport org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.Spring_搭建好的springboot工程

win10家庭版安装docker(借助dockertoolbox)_104.18.121.25:443-程序员宅基地

文章浏览阅读384次。windows利用虚拟机安装docker始终不是同一台机器,然而docker又必须运行在linux环境下,那么windows用户的我们怎么安装docker呢?_104.18.121.25:443

基于比较的七种常见排序算法_基于比较的排序-程序员宅基地

文章浏览阅读8.3k次,点赞30次,收藏73次。本文主要介绍基于比较的七种常见排序算法,分别为:选择排序法,插入排序法,希尔排序法,冒泡排序法,堆排序法,归并排序法,快速排序法。基于比较的排序算法是指对于元素的排序必须是建立在元素之间是可以比较的。体现在 javajavajava​​​ 语言中为待排序的元素类型是实现了 ComparableComparableComparable​​​​​ 接口的类型。本文所涉及的复杂度分析都是基于现有结论加上自己的简单的理解,所以可能非常不严谨,大家看看就好,不过最终的结论都是对的。在分析复杂度的同时也对排序算法_基于比较的排序

JSX的基本使用_jsx 在什么环境中运行-程序员宅基地

文章浏览阅读577次。简介JSX是JavaScript XML的简写,表示在JavaScript代码中写XML格式的代码优点:声明式语法更加直观、与HTML结构相同,降低了学习成本,提升开发效率。为什么脚手架中可以使用JSX语法?1.JSX不是标准的ECMAScript语法,它是ECMASeript的语法拓展。2.需要使用babel编译处理后,才能在浏览器环境中使用3.create-react-app 脚手架中已经默认有该配置使用步骤1.使用JSX语法创建react元素const title_jsx 在什么环境中运行

Spring中Bean的生命周期详解_spring bean加载过程(简述bean的生命周期)-程序员宅基地

文章浏览阅读135次。Spring中Bean的生命周期详解Spring最重要的功能就是帮助程序员创建对象(也就是IOC),而启动Spring就是为创建Bean对象做准备,所以我们先明白Spring到底是怎么去创建Bean的,也就是先弄明白Bean的生命周期。Bean的生命周期就是指:在Spring中,一个Bean是如何生成的,如何销毁的?..._spring bean加载过程(简述bean的生命周期)

ps新手秒变大师必备的Ps插件全在这!(mac版本)_delicious retouch 5 mac-程序员宅基地

文章浏览阅读3k次。作为设计师Photoshop可谓神器,不论你是何种设计师多多少少都会用到它。随着时代的发展,对效率越来越注重,ps虽功能强大,但不是大神级别的炉火纯青,用起来还是比较浪费时间的,这是设计公司永远不会说的秘密。 今天专为使用Mac的同学们带来的是10款必备常用ps扩展插件合集!美工修图必备!1.Delicious Retouch 5.0 (DR5)白金版Delicious Retouch5 白金版 for mac(PS磨皮插件DR5)立即下载Delicious Retouch5 白金版 for mac_delicious retouch 5 mac

随便推点

Windows中断处理_c++ 如何禁止中断-程序员宅基地

文章浏览阅读3k次。在现代操作系统当中,驱动并不是固定与中断相对应的,而是作为一种资源由程序员自己在驱动当中申请,这样在驱动加载之后,如果有中断产生的时候,就会调用在与中断相关的函数。_c++ 如何禁止中断

轻轻松松制作与众不同的黑白照片_黑白照片自动生成-程序员宅基地

文章浏览阅读279次。无论摄影技术如何发展,黑白照片都能保持它独特的吸引力,但如果你亲手试过把彩色照片直接调成黑白照片,那你想必会发现,转换后的效果好像就是差了一点。不用担心,今天小编给大家推荐三款好用的黑白照片处理软件,操作简单,功能实用,让你轻轻松松就制作与众不同的黑白照片,快跟着小编一起来看看吧~1.PhotosRevive for mac(黑白照着色工具)PhotosRevive for mac(黑白照着色工具)立即下载PhotosRevive for mac(黑白照着色工具)Mac哪款黑白照着色工具好用呢?_黑白照片自动生成

LeetCode刷题Python Day1 Q35:搜索插入位置-程序员宅基地

文章浏览阅读96次。题目如下:35. 搜索插入位置难度简单1029收藏分享切换为英文接收动态反馈给定一个排序数组和一个目标值,在数组中找到目标值,并返回其索引。如果目标值不存在于数组中,返回它将会被按顺序插入的位置。请必须使用时间复杂度为O(log n)的算法。示例 1:输入: nums = [1,3,5,6], target = 5输出: 2示例2:输入: nums = [1,3,5,6], target = 2输出: 1示例 3:输入: nums = [1,3...

oracle 存储过程 带游标作为OUT参数输出_oracle存储过程 行变量入参-程序员宅基地

文章浏览阅读9.2k次。包中带过程 要自己定义一个type [cur_name] is ref cursor游标,返回的时候就直接 procedure AAA(变量名 out [cur_name])如此申明OUT变量 存储过程 用系统默认的 sys_refcursor 游标类型 定义变量就OK了 Sql代码 --PL/SQL Code (包中带过程) 过程带游标的OUT参数,返回游标(ref curs_oracle存储过程 行变量入参

AES-NI指令集-程序员宅基地

文章浏览阅读1.6k次。对于intel的AES-NI新指令集,需要i5处理器及以上的相关硬件支持.在编译时,可能会出现/usr/lib/gcc/x86_64-linux-gnu/4.8/include/wmmintrin.h:34:3: error: #error "AES/PCLMUL instructions not enabled"很显然,可以通过查其头文件,定位到#if !defined (__AES..._aes-ni指令集的下载与使用

Bug基础-程序员宅基地

文章浏览阅读131次。1.缺陷报告的定义软件不正常的错误或者是代码行错误等均衡成为缺陷2.缺陷的种类:功能不正常软件在使用上感觉不方便软件的结构未做良好规划提供的功能不充分与软件操作者的互动不良使用性能不佳未做好错误处理边界错误计算错误使用一段时间所产生的错误控制流程的错误在大数据量压力下所产生的错误在不同硬件环境下产生的错误版本控制不良导致的错误软件文档的错误3.将缺陷按照严重程度划分系统崩溃,严重,一般,次要,建议4.解决bug的优先级高,中,低5.按照测试种类分:逻辑功能类性_bug基础

推荐文章

热门文章

相关标签