SQL语言和常用函数

最新推荐文章于 2024-09-10 19:37:16 发布

最蓝的海

最新推荐文章于 2024-09-10 19:37:16 发布

阅读量682

点赞数 5

分类专栏： Oracle

本文链接：https://blog.csdn.net/u011740475/article/details/38472255

版权

Oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

此次文章用到的表为：学生表和教师表，学生表中包括学生姓名，性别，年龄，入学时间等字段，类型分别为varchar2(10)、varchar2(4)、number、date。教师表中包括教师，姓名，年龄两个字段，分别为varchar2(10)、number类型。

SQL语言是一种高级的非过程化的查询语言，用户使用它进行数据库的操作。因此可以吧它看做客户端与服务器端沟通的一个工具，用来存取、查询、更新以及管理数据库。

SQL语言分类：1、DDL（数据定义语言，作用是定义和管理数据库中的各种对象）。2、DML（数据操作语言，作用是操作数据库对象所包含的数据，包括查询、修改、删除等操作）。3、DCL（数据控制语言，它是对数据对象操作权限的控制以及事物的控制，例如，用户或角色授权，事务的提交回滚等）。

INSERT语句，在数据表中插入一行数据。

语法：INSERT INTO table[(column[,column……])] table要插入数据的表的表名，column指的是表中插入数据列名。

VALUES (value [,value……]); value是指对应列中要插入的具体的数据值。

例如：

insert into teacher (name, age) values ('李四', 28);

或者简写为：

insert into teacher values ('李四', 28);

假如插入值当中有时间我们需要用到SYSDATE函数和TO_DATE函数

例如：1、插入数据包含时间，并且时间是当前系统时间。

insert into student (name,gender,age,startdate) values ('王五','男',12,sysdate);

2、插入数据包含时间，并且时间是某个具体时间

insert into student (name,gender,age,startdate) values ('钱二','女',14,to_date('2013-3-2','yyyy-mm-dd'));

数据备份时往往需要多行插入数据，例如备份用户数据：

insert into teacher_bak
  select * from teacher;

当插入的字符串数据中包含单引号时，需要把一个单引号变成两个单引号。

例如：

<span style="font-size:14px;">insert into teacher values('mary''s son',20);</span>

UPDATE语句，更新或修改一行或多行的值。

UPDATE的语法结构：

UPDATEtable SET column=value [,column=value，……] table对应的要更新的数据表的表名，column代表你要更新哪一列，value对应的你要更新以后的列的具体的数值。

[WHEREcondition]; condition指的是更新的条件，用来确定哪一行需要进行更新操作。

例如初始化所有学生的年龄为12岁：

<span style="font-size:14px;">update student set age = 12;</span>

例如：更新学生姓名为钱二的学生性别为男，年龄为13岁

update student set gender = '男', age = 13 where name = '钱二';

注意：在更新操作时，WHERE条件一般选择类似主键这样有唯一约束的字段，除非情况特殊，否则的话可能引起我们想不到的操作，比如更新用户表的时候使用name做条件是非常危险的，因为名字可以重复。

SELECT查询数据表中的数据

示例1：使用select查询学生表中所有数据。

select *或者select all columns

<span style="font-size:14px;">select * from student或 select name,gender,age,startdate from student;</span>

示例2：使用select查询学生表中所有年龄为12岁的学生

<span style="font-size:14px;">select * from student where age = 12;</span>

示例3：使用select查询学生表中年龄为13岁，并且入学时间是2013年3月2号的学生

select * from student where age = 13 and startdate = to_date('2013-3-2','yyyy-mm-dd');

示例4：使用select查询学生表中姓名为李四和钱二的学生

select * from student where name = '李四' or name = '钱二';

示例5：使用select查询学生表年龄字段为空的学生数据。

select * from student where age is null;

示例6：查询学生中所有姓李的学生

select * from student where name like '李%';

这里like表示模糊查询的意思，%表示任意匹配0个或多个字符。_表示匹配单个字符

示例7：查询学生表中年龄在11到12之间的学生

select * from student where age between 11 and 12;

示例8：查询学生表中张三、李四、王五的信息

select * from student where name in ('张三','李四','王五');

对表指定别名的方法：select alias.column from tablealias alias表示对表起的别名

对列指定别名的方法：select column alias from table alias表示对列起的别名

例如：

select s.name as "学生姓名",s.age as "年龄" from student s;

注意：为列起别名的AS可以省略。

排序的语法结构：ORDER BY column [ASC|DESC]; column代表排序的依据字段，也就是说根据那一列排序。ASC表示升序排序、DESC表示降序排序。默认是升序排序所以ASC在通常情况下可以省略。

示例9：查询学生表，按入学时间进行升序排序

select * from student order by startdate asc

示例10：查询教师表商品教师信息（不包含重复信息）

select distinct * from teacher;

distinct过滤重复的数据，只保留不同的记录。

DLETE语句，用来删除数据表中一行或多行的数据。

DELETE的语法结构：DELETE [FROM] table [where condition];table是指要删除数据表的表名condition删除的条件

示例1：使用DELETE语句删除学生表年龄为空的数据

delete from student where age is null;
--提交事务
commit;

示例2：使用DELETE语句删除教师备份表中所有数据。

delete from teacher_bak;
commit;

在实际工作中，由于删除数据就是在数据库中永久的删除数据，因此对于一些重要的数据库表，建议使用假删除，假删除就是增加一个状态（一般为status）字段，0表示此行记录删除，1表示未删除，在使用此表时查询条件添加status=1。

oracle中提供了一些函数，方便进行一些操作。

基本函数分为：字符函数、数字函数、日期函数、转换函数、聚合函数（组函数）

字符函数，专门用于字符处理的函数,一般接受字符作为参数，并且返回字符或数字

CONCAT函数，主要用于字符串连接。语法结构：CONCAT(c1,c2)

接受两个参数，将第二个参数连接到第一个参数末尾。假如第二个参数为NULL，则返回第一个参数，如果第一个参数为NULL，则函数返回第二个参数，假如两个参数都是NULL则返回NULL。

示例1:为学生年龄追加“岁”单位。

select name,concat(age,'岁') from student

或者：

select name,age||'岁' from student

注意：Oracle的CONCAT函数只允许两个参数，因此涉及多个字符串连接时只能使用CONCAT嵌套来实现，或者使用“||”

NVL函数语法：NVL(expr1,expr2);接受两个参数，假如第一个参数不为NULL时，函数返回第一个参数，假如第一个参数为NULL时，函数返回第二个参数.

示例2：查询学生表相关信息，对没有年龄的学生默认显示“-1”

<span style="font-size:14px;">select name,NVL(age,-1) from student</span>

数字函数,对数字数据进行一些算数运算的函数。

TRUNC函数语法：TRUNC(column|expression,n)这个函数是截取函数，用于对指定的数值进行截取操作。第一个参数是要截取的数值，可以是数据库表中的某个字段，有或者是某个表达式。第二个参数n是要截取的位数。

注意：

1、此函数可以只有一个参数，那么此时第二个参数=将使用缺省值0。

2、n可以是正整数，也可以是负整数，正整数表示截取小数点后面的位数，负整数表示截取小数点前面的位数。

dual表：dual表是一个虚拟表，在Oracle中dual里面只包含一条记录，并且任何用户都可以读取，此表被我们通常用来构成我们的语法结构。

示例1:

SELECTTRUNC(45.926) FROM dual; 结果：45

SELECTTRUNC(45.926，2) FROM dual; 结果：45.92

SELECTTRUNC(45.926，-1) FROM dual; 结果：40

ROUND函数语法：ROUND (column|expression,n)是四舍五入函数。用于对指定的数值进行四舍五入操作。第一个参数是要四舍五入的数值。第二个参数n是要四舍五入的位数。

注意：

1、此函数可以只有一个参数，那么此时第二个参数将使用缺省值0.

2、n额可以是正整数，也可以是负整数，正整数时表示四舍五入取小数点后面的位数，为负整数时表示四舍五入小数点前面的位数。

示例2：

SELECTROUND(45.926) FROM dual; 结果：46

SELECTROUND(45.926，2) FROM dual; 结果：45.93

SELECTROUND(45.926，-1) FROM dual; 结果：50

示例3：检索商品表时，在检索结果中对商品价格进行四舍五入处理，（精确到小数点后两位）

SELECTROUND(p.price,2) AS ”四舍五入后的价格”,p.name AS “商品名称” FROM es_product p;

日期函数，主要操作DATE类型数据。

它的输入参数大部分为DATE类型，并且输出参数也为DATE类型。

EXTRACT用于返回日期中的部分日期值。

例如：1、获取日期中的年

select extract(year from sysdate) from dual

2、获取日期中的月

select extract(month from sysdate) from dual

2、获取日期中的日

select extract(day from sysdate) from dual

例：检索学生表时，对检索结果中的入学时间使用年-月-日格式显示

select extract(year from startdate) || '-' || extract(month from startdate) || '-' ||
       extract(day from startdate)
  from student

转换函数：用于操作多重数据类型，它的主要作用是把数据从一种数据类型转换成另一种数据类型

TO_CHAR函数用于将日期数字等转换为字符串

日期转换为字符串的语法：TO_CHAR(date)或TO_CHAR(date,’format_model’);

示例：检索学生表时，对检索结果中的入学时间使用年-月-日格式显示

select to_char(startdate,'yyyy-mm-dd') from student

TO_DATE语法：TO_DATE(date,’format_model’)

第一个参数是要转换的日期字符串，第二个参数是日期字符串的时间格式。

注意：日期格式要和日期字符串对应否则会出现转换错误。

示例：对学生入学日期进行比较检索，检索2014年8月份上学的学生

select *
  from student
 where to_date('2014-7-31', 'yyyy-mm-dd') < startdate
   and startdate < to_date('2014-9-1', 'yyyy-mm-dd');

聚合函数：他的数据源于多行数据，而函数的返回值一般是单一的结果。

以下示例使用商品表，

分组的语法：GROUP BY column; column是分组依据字段

示例：查询商品表，根据商品类别分别求商品数量

select SUM(p.stockcount),p.sort_id from es_product p GROUP BY p.sort_id;

SUM函数语法：SUM(column)，接受一个参数，表示对指定列进行求和操作。

示例1：获得商品表中所有商品的总数量

<span style="font-size:14px;">select sum(p.stockcount) from es_product p;
</span>

示例2：获得商品表中每类商品的总数量

select sum(p.stockcount),p.sort_id from es_product p group by p.sort_id;

COUNT函数的语法:count(column);表示求指定表中列的总行数

示例1：获得商品表中总的记录数。

select count(p.stockcount) fron es_product p;

示例2：获得每类商品在商品表中的总记录数

select count(p.stockcount),p.sort_id from es_product p group by s.sort_id;

MAX函数语法MAX(column),表示求指定列中的最大值

示例1：查询商品表中最贵商品价格

select  MAX(p.price) from ex_product p;

示例2：查询每类商品中最贵的商品价格

select MAX(p.price),p.sort_id from es_product p group by p.sort_id;