sql精妙语句整理

最新推荐文章于 2025-02-20 18:42:29 发布

用程序改变世界

最新推荐文章于 2025-02-20 18:42:29 发布

阅读量2.2k

点赞数 3

分类专栏：参考

本文链接：https://blog.youkuaiyun.com/b69489733/article/details/62041434

版权

参考专栏收录该内容

4 篇文章

订阅专栏

一、SQL分类：

DDL—数据定义语言(Create，Alter，Drop，DECLARE)

创建数据库、表格，修改表格，删除数据库表格、创建、修改、删除查询指令，删除数据表内容

DML—数据操纵语言(Select，Delete，Update，Insert)

DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

二、DDL—数据定义语言

1、说明：创建数据库

Create DATABASE database-name

2、说明：删除数据库

drop database dbname

3、说明：备份oracle

数据库模式：
EXP SYSTEM/PASSWD@ORACLE FULL = YFILE = 'DB081222.DMP' LOG = 'DB081222.LOG'
用户模式：
EXP USER/PASSWD@ORACLE FILE ='USER081222.DMP' LOG = 'USER081222.LOG'
表模式：
EXP USER/PASSWD@ORACLE FILE ='USER081222.DMP' LOG = 'USER081222.LOG' TABLES = ('TABLE_1','TABLE_2')

4、说明：创建新表

create tabletabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as selectcol1,col2… from tab_old definition only

5、说明：删除新表drop table tabname

6、说明：增加一个列

Alter table tabnameadd column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname addprimary key(col)

说明：删除主键： Altertable tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxnameon tabname(col….)

删除索引：dropindex idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as selectstatement

删除视图：drop viewviewname

D三、DML—数据操作语言

10、说明：几个简单的基本的sql语句

选择：select* from table1 where 范围

插入：insertinto table1(field1,field2) values(value1,value2)

删除：deletefrom table1 where 范围

更新：updatetable1 set field1=value1 where 范围

查找：select* from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

排序：select* from table1 order by field1,field2 [desc]

总数：selectcount * as totalcount from table1

求和：selectsum(field1) as sumvalue from table1

平均：selectavg(field1) as avgvalue from table1

最大：selectmax(field1) as maxvalue from table1

最小：selectmin(field1) as minvalue from table1

四、DML---关键字

1、链接查询

说明：几个高级查询运算词

A、left join：左连接

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

select column_name(s)

from table_name1

left join table_name2

ontable_name1.column_name=table_name2.column_name

B：right join:

RIGHT JOIN关键字会从右表(table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

select column_name(s)

from table_name1

right join table_name2

ontable_name1.column_name=table_name2.column_name

C：full join：

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

select column_name(s)

from table_name1

full join table_name2

on table_name1.column_name=table_name2.column_name

2、如何使用sql把两张字段不相同的表合成字段相同的一张表

select A as a,B as b from table1
union all
select d as a,c as b from table2

结果：a b

3、返回表中某列的唯一值--- DISTINCT 关键词

select distinct company from orders

4、where字句

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

5、order by语句用于根据指定的列对结果集进行排序。

Asc默认升序，desc降序

6、通配符

例子：

查询居住在以"Ne" 开始的城市里的人：select * from persons where city like 'ne%'

查询居住在包含 "lond" 的城市里的人：select * from persons where city like '%lond%'

查询名字的第一个字符之后是"eorge" 的人：select * from persons where firstname like '_eorge'

查询的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"：select * from persons where lastname like 'c_r_er'

查询居住的城市以 "A" 或 "L" 或 "N" 开头的人：select * from persons where city like '[aln]%'

居住的城市不以 "A" 或 "L" 或 "N" 开头的人：select * from persons where city like '[!aln]%'

7、使用EXISTS句代替in语句

操作符允许我们在 WHERE 子句中规定多个值。

select column_name(s)

from table_name

where column_name exists (value1,value2,...)

8、操作符 between ... and 会选取介于两个值之间的数据范围。

这些值可以是数值、文本或者日期。

9、GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。分组返回一条记录

group by 一列

select customer,sum(orderprice) from orders group by customer

group by 多列，按多个字段分组，只要有一个字段值不相同都需要把两条记录都返回。如果分组的字段值都相同则返回一条记录

select customer,orderdate,sum(orderprice) from orders

group by customer,orderdate

group by原则

就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

和having区别

查询table表查询每一个班级中年龄大于20，性别为男的人数

select COUNT(*)as '>20岁人数',classid from Table1 where sex='男' group by classid,age having age>20

--需要注意说明：当同时含有where子句、group by 子句、having子句及聚集函数时，执行顺序如下：

--执行where子句查找符合条件的数据；

--使用groupby 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。

--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.

--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。

--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。

10、Except 比较两个查询的结果，返回非重复值。

(select a from tableA ) except (select a fromtableB) except (select a from tableC)

  返回A  B  C表中不重复的值

11、oracle case when then else end 一旦满足了某一个WHEN ，则这一条数据就会退出CASE WHEN，而不再考虑其他CASE

CASE WHEN 表达式有两种形式

--简单Case函数

CASEsex

WHEN'1' THEN '男'

WHEN'2' THEN '女'

ELSE'其他'END

--Case搜索函数

CASE

WHENsex = '1' THEN '男'

WHENsex = '2' THEN '女'

ELSE'其他'END

使用场合例子1

使用场合例子2

使用场合3

12、select into 语句从一个表中选取数据，然后把数据插入另一个表中。

说明：复制表(只复制结构,源表名：a 新表名：b)

select * into b froma where 1<>1

说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

insert into b(a, b, c) select d,e,f from b;

说明：跨数据库之间表的拷贝(具体数据使用绝对路径)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in'"&Server.MapPath(".")&"\data.mdb"&"' where..

五、oracle数据类型

六、DML---函数

1、聚合函数

max():返回一列中的最大值。NULL 值不包括在计算中。

select max(column_name) from table_name

min(): 函数返回一列中的最小值。NULL 值不包括在计算中。

select min(column_name) from table_name

sum()：函数返回数值列的总数（总额）。

select sum(column_name) from table_name

last() ：函数返回指定的字段中最后一个记录的值。

select last(column_name) from table_name

first() ：函数返回指定的字段中第一个记录的值。

select first(column_name) from table_name

count() ：函数返回匹配指定条件的行数。

函数返回指定列的值的数目（null 不计入）：

select count(column_name) from table_name

count(*) 函数返回表中的记录数：

select count(*) from table_name

avg()：函数返回数值列的平均值。null 值不包括在计算中。

select avg(column_name) from table_name

2、字符函数

lower():字符串变成小写select lower('abcde') from dual ;

upper():将字符串变成大写 select upper('abcd') from dual ;

initcap():将每个字符串首字母大写select initcap(lower(ename)) from emp; 函数可以嵌套

concat()函数：(可以连接两个字符串)select concat('hello',' world!!!') from dual ;

实际上使用“||”也可以完成两个字符串的连接操作。

substr()函数：(字符串截取)

截取的时候需要注意：要从那里开始截取，之后取多长的长度。
select substr('hello',1,2) from dual ;

同时也可以取负数，表示从尾倒着取。
select substr('hello',-3,2)from dual 倒数第三个开始取两位

注意：substr函数的开始点是从1或0开始的，oracle非常智能。

length()：取出字符串的长度，取出每一个雇员的姓名的长度：
select ename||' 姓名的长度为：'||length(ename)from emp ;

instr()函数：查找在一个字符串中是否有指定的字符串，如果有，则返回其位置

selectinstr('hello','x') from dual ;
如果有此字符串，则返回位置，如果没有，则返回0。

replace()函数：替换，可以将一个字符串中的指定字符串替换为其他内容：
selectreplace('hello','l','x') from dual ;

trim()函数：去掉左右空格的函数
selecttrim(' hello ')from dual ;

查询：
（1）、检索姓名最后一个字母为n的雇员
• select * from emp wheresubstr(ename,-1,1)='n';
（2）、检索职务为“sale”的全部员工信息
• select * from emp wheresubstr(job,1,4)='sale';

3、数值函数

round()函数：(表示四舍五入)

•select round(34.56) from dual; 结果:35
•select round(34.56,-1) from dual ; 结果:30

•select round(35.56,-1) from dual ; 结果:40

•select round(34.56,1) from dual ; 结果:35.6
trunc()函数：（表示截取函数,不会保留任何小数且不会执行四舍五入的操作）
• select trunc(34.56) from dual; 结果:34

• select trunc(34.56,1) from dual ; 结果:34.5
• select trunc(34.56,-1) from dual ; 结果:30

mod()函数：(取余数,即取模）
•select mod(10,3) from dual; 结果:1

4、日期函数

sysdate取得当前的日期：
select sysdate fromdual ;

日期-数字=日期；日期+数字=日期；日期-日期=数字(多少天)

   例如：计算10部门中的员工进入公司的周数：
      肯定使用当前日期-雇佣日期（hiredate） = 天数，天数/7为日期数。
      select ename,round((sysdate-hiredate)/7) from emp ;
   months_between()函数:给出指定日期范围的月数

求出所有员工的受雇月数：months_between，使用当前日期与雇佣日期比较。

select ename,round(months_between(sysdate,hiredate))from emp ;

add_months()函数：在指定的日期上加上指定的月数，求出之后的日期

求出，三个月之后的日期是那一天：
select add_months(sysdate,3) from dual ;

next_day()：表示下一个今天（星期几）
select next_day(sysdate,'星期一')from dual ; 表示下一个星期一是多少号

last_day：求出当前日期所在月的最后一天：
select last_day(sysdate) from dual; 表示本月的最后一天的日期

6、通用函数

nvl(ex0,ex1)函数：将一个指空值变为一个指定的内容

select nvl(null,’123’) from dual; 如果为空则按123显示

nvl2(ex0,ex1,ex2)函数

select nvl2(null,’123’,’11111’)from dual;

如果不为空返回123，为空返回11111

nullif(ex1,ex2)

比较两个表达式，如果值相等返回空，否则返回ex1表达式

decode函数：类似于if else if else 语句

select decode(1,1,'内容为1',2,'内容为2',3,'内容为3') from dual;

如果给出的值为1，那么则显示内容为1 ，为2则显示内容为2……

再例如如果工作为销售则显示销售员、若为业务员则显示业务员、若为总裁则显示总裁

select decode(job,'salseman','销售员','clerk','业务员','president','总裁') from emp！

start with  connect by 语法结构
如上面说看到的例子，其语法结构为  start with condition  connect by  condition （含 prior 关键字)
start with conditon 给出的seed 数据的范围, connect by  后面给出了递归查询的条件,prior 关键字表示父数据，prior 条件表示子数据需要满足父数据的什么条件。