四、oracle基本sql语句和函数详解

论坛 期权论坛     
选择匿名的用户   2021-5-30 02:49   145   0
<div class="blogpost-body" id="cnblogs_post_body">
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">一、oracle常用数据类型</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;"><img alt="" src="https://beijingoptbbs.oss-cn-beijing.aliyuncs.com/cs/5606289-1d74c729c787ef97f01b7df616c8a09f.png"></span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;"> </span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;"><img alt="" src="https://beijingoptbbs.oss-cn-beijing.aliyuncs.com/cs/5606289-88d48760dc80f7812c71191cd60f5f3c.png"></span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">  </span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"><strong>一、  数据定义语言(<span style="color:#000000;">ddl</span>)</strong></span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">数据定义语言ddl(data definition language)用于改变数据库结构,包括创建、更改和删除数据库对象。</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">用于操纵表结构的数据定义语言命令有:</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">create table</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">alter table</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">truncate table</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">drop table</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">eg、</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"><em>--</em><em>创建tb_stu表数据结构</em></span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">create table tb_stu(</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">   id number,</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">   name varchar2(20)</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">);</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"><em> </em></span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;color:#ff0000;font-size:14px;"><em>--</em><em>修改tb_stu表数据结构,新增一列</em></span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">alter table tb_stu add pwd varchar2(6);</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;color:#ff0000;font-size:14px;"><em>--</em><em>修改字段</em></span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">alter table tb_stu modify pwd varchar2(20);</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;color:#ff0000;font-size:14px;">--重命名表: rename table_name1 to table_name2;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">RENAME student TO tb_student;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;color:#ff0000;font-size:14px;">--重命名列:alter table table_name rename column col_oldname to colnewname;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">ALTER TABLE student RENAME COLUMN pwd TO pwd1;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;color:#ff0000;font-size:14px;"><em>--</em><em>删除字段</em></span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">alter table tb_stu drop column pwd;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"> </span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"> </span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"> </span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"><em>--</em><em>删除tb_stu表数据</em></span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;">truncate table tb_stu;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"> </span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"><em>--</em><em>删除tb_stu表</em></span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">drop table tb_stu;</span></p>
<p align="left"><span style="font-family:&#39;宋体&#39;;font-size:14px;"><strong>二、  数据操作语言(dml)</strong></span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">数据操纵语言dml(data manipulation language)用于检索、插入和修改数据,dml利用insert、select、update 及 delete 等语句来操作数据库对象所包含的数据。</span></p>
<p><span style="font-family:&#39;宋体&#39;;color:#ff0000;font-size:14px;">(1)、利用现有的表创建表</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">语法:create table &lt;new_table_name&gt; as</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">         select column_names from &lt;old_table_name&gt;;</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">eg、</span></p>
<p><span style="font-family:&#39;宋体&#39;;font-size:14px;">1)、create tab
分享到 :
0 人收藏
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

积分:3875789
帖子:775174
精华:0
期权论坛 期权论坛
发布
内容

下载期权论坛手机APP