针对Oracle数据库表中的数据的常见操作

时间:2021-09-11 09:49:34

1、查询表中所有数据

select * from 表名;  例:select * from stu;

2、查询的同时修改表中数据

select * from 表名  for update;  例:select * from stu for update;

3、往表中添加数据

insert into 表名(列1,列2...) values(值1,值2...);

例:insert into stu(id,name,age) values(1,'zhangsan',23);

注意:字符串类型要用单引号括起

   对于列中要求非空的属性,添加时必须添加,可以为空的数据可以不写上.

   列名和值要一一对应。

4、删除表中数据(删除某行数据)

delete from 表名 where 列名=值;    例:delete from stu where id=1;//删除id是1的那行数据

注意:删除时尽量使用唯一键进行删除,或者使用主键进行删除。

5、更改表中某行数据

update 表名 set 列名=值,列名=值...where 列名=值;  例:update stu set id=2,name='lisi';age=4 where id=1;  //把id为1的那行数据进行更改,可只更改部分数据

6、查询(重点,最常用的对数据库进行的操作就是查询)

在sql中,可以使用简单的运算符(+-%/);

 (1)、查询表中所有数据

  select * from 表名;  例:select * from stu;

(2)、查询表中指定列的数据

  select 列名 from 表名;  例:select name,age from stu;

(3)、查询表中指定行列的数据

  select 列名 from 表名 where 列名=值;  例:select name,age from stu where id=2;(查询id为2的数据的姓名和年龄)

(4)、查询十年后张三的年龄

  select name,age+10 from stu where name='zhangsan';

(5)、给age字段起别名

    给字段起别名是为了更好的对字段进行描述,也就是说起一个别名并不能随意起,要适用于这个字段的意思

    select name age+10 as ages from stu where name='zhangsan';(使用关键字as,通常情况下,这里的as可以省略)

    select name age+10 ages from stu where name='zhangsan';

    也可以给表起别名:select name from stu s;(给表stu起别名s,这里不能加as);

    注意:如果别名中间有空格,需要给别名加个双引号

    例:select age+10 "zhangsan age" from stu where id=1;

(6)、表格自定义的格式表现

  例:select '姓名:'||name,'年龄:'||age from stu;(使用了单引号)

(7)、对数据进行排序

  这里需要使用关键字order by,默认是升序的---->asc升序,如果以升序进行排列通常情况下asc省略。desc降序,不能省略

  例:select * from stu order by age asc;(对学生表按照年龄升序排列)

    select * from stu order by age desc;(对学生表按照年龄降序排列)

    select * from stu order by age desc,name desc;(对学生表按照年龄降序排列,年龄相同时按名字降序排列)

(8)、去除重复项(关键字:distinct)

  例:select distinct age from stu;(去除stu表中年龄相同的项,只保留一项)

  注意:这里的去除只能按照某一属性去除,不能同时按照两个或以上属性去除。
(9)、计算1+1等于几?

  select 1+1 from dual;(1+1信息会显示在dual表中)

  dual其实也是一张表,这一表是虚拟的,没有列和数据,当查询的时候会进行赋予列和数据,用于方便用户测试使用.

(10)、查询当前Oracle登录用户

  select user from dual;(用户信息会显示在dual表中)

 7、利用where关键字进行查询(这里都以teacher表为例,该表有id,name,age,description hire_date等列,其中id列是主键)

  (1)、= 关键符号

    select * from teacher where id=2;  //查找id等于2的那行

  (2)、大于>、小于<、大于等于>=、小于等于<=和!=不等于;

    select * from teacher where id>2;  //查找id大于2的数据;

    select * from teacher where id<5;  //查找id小于5的数据;

    select * from teacher where id>=2;  //查找id大于等于2的数据;

    select * from teacher where id<=2;  //查找id小于等于2的数据;

    select * from teacher where id!=2;  //查找id不等于2的数据;

  (3)、between and是包含两边的极限数据(闭区间)

    select * from teacher where id between 2 and 10;  //查找id在2和10之间的数据(包括2和10);

  (4)、or关键字(或者)

    select * from teacher where id=2 or id=5;  //查找id等于2或者id等于5的。

  (5)、and关键字(和)

    select * from teacher where name='zhangsan' and age=32;  //查找名字等于张三并且年龄32的数据(人);

  (6)、in、not in关键字(主要的作用:批量操作)

    select * from teacher where id in(1,5,10);  //查找id是1、5、10的数据

    select * from teacher where id not in(1,5,10);  //查找除了id是1、5、10的数据 

  (7)、is null(过滤出来所有为null的数据)

    select * from teacher where description is null;  //查找所有description属性是null的数据

  (8)、 is not null (过滤出来所有不为null的数据)

    select * from teacher where description is not null;  //查找所有description属性不是null的数据

  (9)、模糊查询(使用关键字like和not like)not like 和like刚好相反

    like一般情况下要和%结合起来用,%其实一个占位符(代表多位),如果把%写在前面,匹配以a结尾的所有名字,反之匹配以a开头的所有名字
    如果所需要查询的字段前后都加上%,只要包含该查询字段就全部查找出来

    例:select * from teacher where like name like '%a';  //匹配所有以a字符结尾的name属性。

      select * from teacher where like name like 'a%';  //匹配所有以a字符开头的name属性。

      select * from teacher where like name like '%a%';  //匹配所有包含a字符的name属性。

      _下划线也是占位符,不过它只代表1位,就是代表了任意一个字符

      以_a%进行模糊查询的时候,会匹配以第二位为'a'的字符串

      select * from teacher where like name '_a%';

      另一个关键字escape, 

      匹配规则:使用escape的时候会,如果_写在需要查询字段的前面,oracle会自动把_转移为任意一个字符
      只有把下划线写在需要查询字段的后面,才能使用escape关键字去掉多余字段,只保留下划线。
      如果'%_x%',使用escape关键字时,会连同下划线一起去除。

      例:select * from teacher where name like '%x_d%' escape 'x';  //查询以_开头的数据

        select * from teacher where name like '%/_d% escape '/';  //查询以_开头的数据

      例:搜索以“QA_”开头的数据 :

      select * from teacher where name like 'QA_%'

      结果为:QA_OFFICER_1,QA_OFFICER_2,QA112

      不符合,必须把下划线转义

      select * from teacher where name like 'QA/_%' escape '/';

      结果为:QA_OFFICER_1,QA_OFFICER_2    

      补充:

      SQL中escape的用法

      使用 ESCAPE 关键字定义转义符。 在模式中,当转义符置于通配符之前时,该通配符就解释为普通字符。

      例如,要搜索在任意位置包含字符串 5% 的字符串: WHERE ColumnA LIKE '%5/%%' ESCAPE '/' 

      前后两个%作为通配符使用,中间的%经过ESCAPE 转义,作为普通字符使用

8、Oracle中的函数

  (1)、lower(转换为小写)

    select lower(name) from teacher;  //把name数据转换成小写

   (2)、upper(转换为大写)

    select upper(name) from teacher;  //把name数据转换成大写

   (3)、initcap(首字母开头大写)

    select initcap(name) from teacher;  //把name属性数据首字母转换成大写

    select initcap('oracle') from dual;  //把字符串oracle首字母转换成大写

   (4)、length:获取字符串的长度
    select length(name) from teacher where id =1;

   (5)、replace:有三个参数第一个参数:列名(字段);第二个参数:需要替换字段中的某一个字符;第三参数:替换的字符

    select replace(name,'z','Q') from teacher where id =1;

   (6)、substr:和Java的subString差不多

    substr有两个参数:第一个参数:列名,第二个参数:从哪一位开始截取(包含最后一位截取的数):name,要截取:subtr('name',2);--->得到的是ame

    select substr(name,3) from teacher where id=2;

    如果第二个参数为负数的话,就倒着从最后一位开始往前截取

    select substr('zhangsan',-3) from dual;  //运行结果为san;    

    subtr:有三个参数:第一个参数:列名,第二个参数:从哪一位开始截取(闭区间),第三个参数:截取到多少位,截多少长度(会从第二个参数的截取位置往后开始进行截取)
    oracle中,截取的位置会从0或者1起始(0和1的位置是一样的;   

    select substr('zhangsan',0,5) from teacher where id = 1;
    select substr('zhangsan',1,5) from teacher where id = 1;

   (7)、ceil(向上取整)

    select ceil(12345.1) from dual;

   (8)、floor(向下取整)

    select floor(12345.1) from dual;

   (9)、round(四舍五入)   

    select round('12345.1') from dual;
    select round('12345.7') from dual;
    select round('12845.6', -3) from dual;

    select round('12845.6', 3) from dual;

   (10)、trunc:截断:会取整数,第二个参数:保留的小数位

    如果没有小数,并且第二个参数为正数:就原样显示,不会加上.00

    select trunc(123456.235243) from dual;

    select trunc(123456.235243,2) from dual;

    如果没有小数,并且第二个参数为负数:会把整数位从后往前写为0

    select trunc('123456', -3) from dual;

   (11)、concat:拼接字符串----->可代替||

    select concat('zhang','san') from dual;

    select concat('姓名是:',name) from teacher;

9、Oracle中的时间函数

  (1)、获取当前的时间:sysdate

    select sysdate from dual;

  (2)、时间-时间,查询的是指定日期距离某个日期的天数

    select sysdate-hire_date from teacher;  //查询某天距离当前日期的天数

  (3)、add_months:对指定日期月份进行加减计算

    select add_months(sysdate,3) from dual;

     select add_months(sysdate,-3) from dual;

   (4)、months_between:指定日期距离当前日期的月份    

     select hire_date, sysdate, months_between(sysdate, hire_date) from teacher;

   (5)、next_day:查询出指定日期和指定星期几的日期:只能往后不能往前

      select next_day(sysdate, '星期一') from dual;

   (6)、last_day:查询出当月的最后一天,查询当月有多少天

      select last_day(sysdate) from dual;
      select t.hire_date, last_day(hire_date) from teacher t;

   (7)、日期字符串转换函数,可在在日期型,字符串型和number型之间互转

    to_date('2017-12-4 16:12:34','yyyy-MM-dd hh24:mi:ss');  可以把日期字符串转换成日期型 

    to_date()函数可以将字符串转换为日期类型

    to_date(char) //按缺省格式进行解析

    to_date(char,‘format_model’) //按模式串指定的格式进行解析

    to_char()函数可以将日期型数值转换为字符串形式

    to_char(date) //缺省转换为'dd-mon-yy'格式

    to_char(date,‘format_model’) //转换为模式串指定的格式

    to_char(date,'yyyy');  //取到某个日期的年份

    to_char(date,'MM');  //取到某个日期的月份

    to_number(日期字符串);  //可以把日期字符串转换成number型。

    注意:缺省的日期格式是DD-MON-YY