数据库SQL语言以及常见操作

发表于 | 分类于 | 热度:
字数统计: 3.7k 字 | 阅读时长 ≈ 15 分钟

SQL语言

1.sql简介

SQL：Structured Query Language
Oracle ： C/S 客户端发起请求，通过网络传递给服务器，服务器对请求进行响应

2.sql组成

DDL（数据定义语言）：

数据定义语言 DDL( Data Definition Language ) , 是 SQL 语言集中负责数据结构定义及数据库对象定义的语言 , 主要有 create、alter、drop 和 truncate 四种常用语句。对数据结构起作用。
例如 create alter drop 主要针对数据库对象的操作
注意：DDL语言使用的时候不需要提交，系统会自动提交

  • create 数据库对象的创建
  • alter 修改数据库对象
  • drop 删除数据库对象
  • truncate 清空表数据

DML（数据操纵语言）

数据操纵语言 DML( Data Manipulation Language ) , 用户通过它可以实现对数据表的基本操作 ,即对表中数据的增、删、改。DML对数据起作用。

  • insert 插入操作
  • update 更新操作
  • delete 删除操作
  • select 查询操作

    DCL（数据控制语言）：

控制存取权限 grant revoke
数据控制语言DCL（Data Control Language），用来控制存取许可、存取权限等。

  • grant 权限分配
  • revoke 权限回收
    TCL（事务控制语言）：

commit rollback savepoint 主要针对的是DML操作的
事务控制语言TCL（Transaction Control Language）是用来对 DML操作进行确认的。

  • commit 提交数据
  • rollback 回滚

下面针对每一种语言进行讲解：

3.DML语言

1）insert 插入
第一种插入方式

1
2
3
4
5
6
7
8
9
10
11
12
13
**第一种插入方式**

--创建一张emp01表，复制emp表的结构。

create table emp01 as select * from emp where 1=2;

--往emp01中插入记录

--给全部字段插入值

insert into emp01 values

(1001,'shelly','MANAGER',7839,date'1989-12-08',3000,NULL,10);

第二种插入方式

1
2
3
给其中几个字段插入值，没有显示的字段默认null
insert into emp01(empno,ename,sal,job,deptno)values
(1002,'ellen',4000,'salseman',20);

第三种插入方式

1
2
3
4
5
6
7
8
9
insert into emp01(empno,ename,job,sal,deptno)

select 3001,'LILY','ANALYST',3000,30 from dual;  注：select  和 from 成对出现，如果from后面没有内容就用虚表dual

--等价于：

insert into emp01(empno,ename,job,sal,deptno)values

(3001,'LILY','ANALYST',3000,30);
1
2
3
4
5
6
7
8
--问题：从emp表中复制20部门员工信息，放入到emp01表中

insert into emp01 select * from emp where deptno=20;

--问题：往emp01表中插入一条ellen的记录，其余内容跟emp01表一致，empno在原来的基础上加1000
insert into emp01(empno,ename,job,sal,deptno)

select empno+1000,ename,job,5000,deptno from emp01 where ename='ellen';

–一条insert语句插入多条记录

–问题：一条insert语句插入多条记录

1
2
3
4
5
6
7
insert into emp01(empno,ename,sal,job)

select 9999,'LILEI',3500,'MANAGER' from dual union

select 9998,'Lucy',4500,'MANAGER' from dual union

select 9997,'hanmeimei',5500,'MANAGER' from dual

–union：Oracle的集合运算并运算，求并集,使用的时候会去重
–union all:求并集运算，不去重

1
2
3
4
5
6
7
8
9
10
11
select 9999,'LILEI',3500,'MANAGER' from dual union

select 9999,'LILEI',3500,'MANAGER' from dual union

select 9999,'LILEI',3500,'MANAGER' from dual

select 9999,'LILEI',3500,'MANAGER' from dual union all

select 9999,'LILEI',3500,'MANAGER' from dual union all

select 9999,'LILEI',3500,'MANAGER' from dual

需要注意的是：

注意事项：
1.插入记录时，字段的数据类型，长度 小数的精度 都要符合表的结构要求
2.插入数据的个数应该与字段个数一致
3.插入数据时，注意日期的处理格式
第一种 处理格式 date声明字符串是一个日期
第二种 使用默认日期格式
第三种 to_date函数将字符串转换为日期
insert into emp01 values( , , , , , ,) 如果插入的自定义日期 前面要用date修饰一下 说明此处是一个日期 

1
2
3
4
5
insert into emp01 values(1001,'shelly','MANAGER',7839,date'1989-12-08',3000,NULL,10);
--使用默认日期格式
insert into emp01 values(1001,'shelly','MANAGER',7839,'08-12月-1989',3000,NULL,10);
--使用to_date将字符串转换为日期
insert into emp01 values(1001,'shelly','MANAGER',7839,to_date('1989-12-08','yyyy-mm-dd'),3000,NULL,10);

（2）delete删除

–删除表中所有数据

1
delete from emp01；  但是表还是存在的  删除表是drop 语句

–删除表中符合条件的记录
–问题：删除emp01表中部门编号为10的记录
–问题：删除emp01表中部门编号为10的员工的信息

1
delete from emp01 where deptno=10;
注意事项：
1、如果不使用where子句，将删除表中所有的数据。(特别注意)
2、delete语句不能删除某一列的值(可使用update)
3、使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。
4、同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题（主外键关联关系），在修改数据库数据时，头脑中应始终不要忘记这个潜在的问题。

删除的几种方法比较：
| delete from student where xh=’A001’; 删除一条记录 |
| ———————————————————— |
| delete from 表名; 删除所有记录，表结构还在，写日志，可以恢复的，速度慢 |

truncate table 表名; 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
drop table 表名; 删除表的结构和数据
—-

（3）update更新

–问题：更新emp01表中员工工资，如果工资小于1500，那么给员工涨1000；

update emp01 set sal=sal+1000 where sal<1500;
语法：

1
2
3
4
5
update tableName

set 字段1=值1，字段2=值2，…字段n=值n

where 条件;
注意事项：
1、update语法可以用新值更新原有表行中的各列；
2、set子句指示要修改哪些列；
3、where子句指定应更新哪些行。如没有where子句，则更新所有的行。

示例：

--对students中的数据进行修改
--将张三的性别改成女
SQL>update students set sex='女' where name='张三';
--把张三的年级改为2
SQL>update students set gradeid=2 where name='张三';
--把所有人的成绩都提高10%
SQL>update students set score = score *1.1;

（4）select

简单查询

查询所有列

select * from tableName; 

1
2
查询所有员工信息
select * from emp;

查询指定列

1
2
3
4
5
select col1,col2,… from tableName;

查询所有员工的员工号，姓名和工资

select empno,ename,sal from emp;

去重查询（使用关键字distinct）

1
2
3
查询所有员工所在的部门号。

select distinct deptno from emp;

使用算术表达式

算术运算符用于执行数值计算，可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成。
算术操作符包括加(+)、减(-)、乘()、除(/)。
查询员工的年薪
select empno,ename,sal12 from emp;

** 使用列的别名**

1
2
3
select ename "姓名",sal*13+nvl(comm,0)*13 "年收入" from emp;          
select ename 姓名,sal*13+nvl(comm,0)*13 年收入 from emp;
select ename as "姓名",sal*13+nvl(comm,0)*13 as "年收入" from emp;

PS：oracle在使用别名时，可以用双引号、不使用、使用as来表明别名。但不能使用单引号。

使用nvl函数来处理null
nvl是oracle提供的函数，是用于处理null值使用的。
语法：nvl(值1,值2)
解释：nvl值1为null时则取值2，值1不为null时则取值1原值。
select empno,ename,(sal+nvl(comm,0))*13 from emp;

使用连接运算符

连接运算符（||），用于将多个字符串或数据值合并成一个字符串
select ename||’年收入’||(sal13+nvl(comm,0)13) “雇员的年收入” from emp;
select ename,’部门编号是’||deptno 部门编号 from emp;

使用where子句
如果希望只查询一部分行，那么可以通过WHERE子句指定条件。WHERE子句的作用是通过指定条件，使SELECT语句仅仅查询符合条件的行。

在更多情况下，都需要根据指定的条件对数据进行查询。

WHERE子句指定的条件是一个关系表达式，如果关系表达式的结果为真，则条件成立，否则条件不成立。

关系表达式用于比较两个表达式的大小，或者进行模糊匹配，或者将一个表达式的值与一个集合中的元素进行匹配。

比较运算符

比较运算符用于比较两个表达式的值，操作符包括 =、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和IS NULL等

1
2
3
4
5
6
7
8
（1）查询工资高于3000的员工信息。
select * from emp where sal>3000;

（2）查询部门号是10的员工信息。
 select * from emp where deptno=10;

（3）查询工资在2000至3000之间的员工
 select * from emp where sal between 2000 and 3000;

说明：between是指定区间内取值，如：between 2000 and 2500，取2000至2500内的值，同时包含2000和2500

like模糊查询
在模糊查询中需要使用通配符。
在这里插入图片描述

查询员工姓名以S开头的员工信息
select * from emp where ename like ‘S%’;

查询员工姓名第三个字符为大写O的所有员工的姓名和工资
select ename,sal from emp where ename like ‘__O%’;

在where条件中使用in

in运算符用来与一个集合中的元素进行比较。
查询部门10,20的员工姓名及工资。
select ename,sal from emp where deptno in (10,20);

在where条件中使用is null

在Oracle中使用IS NULL和IS NOT NULL来判断是否为空了。
查询没有上级的员工信息
select * from emp where mgr is null;

使用order by排序查询

Oracle中使用order by子句，对查询结果按照某一列进行排序查询，排序结果有升序（asc）和降序（desc）之分。默认排序结果是升序，即从小到大排序。

单一列排序

按照工资的从低到高的顺序显示员工信息

select * from emp order by sal asc;

PS：asc写或不写都是升序排序即从小到大排序，desc则是降序排序从大到小排序。

多列排序

按照部门号升序而雇员的入职时间降序排列
select * from emp order by deptno,hiredate desc;

使用列的别名排序

select ename,sal*12 年薪 from emp order by 年薪 asc;

TCL语言

commit     
savepoint 
rollback

事务是对数据库操作的逻辑单位，在一个事务中可以包含一条或多条DML （数据操纵语言）、DDL （数据定义语言）和DCL （数据控制语言）语句，这些语句组成一个逻辑整体。

事务具有四个属性，这四个属性的英文单词首字母合在一起就是ACID 。这四个属性是：

  • 原子性（ Atomicity ）：事务要么全部执行，要么全部不执行，不允许部分执行。
  • 一致性（ Consistency ）：事务把数据库从一个一致状态带入另一个一致状态。
  • 独立性（ Isolation ）：一个事务的执行不受其他事务的影响。
  • 持续性（ Durability ）：一旦事务提交，就永久有效，不受关机等情况的影响。

一个事务中可以包含多条DML语句，或者包含一条DDL语句，或者包含一条DCL语句。

用于事务控制的语句有：
COMMIT - 提交并结束事务处理
ROLLBACK -  撤销事务中已完成的工作
SAVEPOINT – 标记事务中可以回滚的点

创建一个保存点：

savepoint  mark1
rollback  to mark1；   -----回滚到mark1保存点
commit  ---提交  提交后不能再回滚。

commit操作

示例：银行转账，小明给小红转账500元。

update custom set balance=balance-500 where name=’小明’;
update custom set balance=balance+500 where name=’小红’;
commit;

rollback操作

回滚事务有两种方式：完全回滚，即回滚到事务的开始；部分回滚事务，即可以将事务有选择地回滚到中间的某个点。部分回滚是通过设置保存点（ SAVEPOINT ）来实现的。

--部分回滚
…
savepoint 保存点名称;//设置保存点
…
rollback to [savepoint] 保存点名称;//回滚到保存点

特别注意：设置保存点及回滚操作是配合delete语句使用，用来找回使用delete删除的数据。而通过truncate删除的表数据是无法通过此方法找回的。

建议：

在使用delete删除表数据前使用savepoint设置保存点，防止数据误删除。

例子：

1
2
3
4
5
6
7
8
9
--创建保存点：

savepoint mark1 

update emp01 set sal=sal+500 where comm is null;

rollback to mark1;--回滚到mark1保存点

commit; --提交
1
2
3
4
5
6
7

SQL>  UPDATE students SET score = score + 5 WHERE score <= 30;
SQL>  SAVEPOINT mark1;
SQL>  DELETE FROM students WHERE id= ‘100001’;
SQL>  SAVEPOINT mark2;
SQL>  ROLLBACK TO mark1;
SQL>  COMMIT;

PS：
（1）在一个事务中可以保存多个保存点。
（2）一旦回退后，该保存点就消失了，不能再次回退。
（3）设置保存点是有资源开销的。
（4）一旦提交了事务，则不能回退到任何保存点。

oracle的运算符

算术运算符：+ - * /

update emp01 set sal=sal+500 where comm is null;

连接运算符:||

1
2
3
--问题：查询emp01中员工姓名，在员工姓名前面加上dear

select 'Dear '||ename from emp01;

–问题：查询每个员工的工资，显示为：ellen的工资是：800

select ename||’的工资是：’||sal from emp01;

比较运算符: 

 > >= < <= <>(!=) = is null  between...and...  inlike

--问题：查询工资大于3000的员工的信息

select * from emp01 where sal>3000;

--问题：查询工资小于2000的员工的信息

select * from emp01 where sal<2000;

--问题：查询部门编号不为20的员工的信息

select * from emp where deptno<>20;

select * from emp where deptno!=20;

is null用来做空值的比较

1
2
3
4
5
6
7
--问题：查询奖金为空的员工的信息

select * from emp where comm is null;

--问题：查询奖金不为空的员工的信息

select * from emp where comm is not null;

–注意：0和null不是一回事

between…and…:包含边界

--问题：查询工资在2000-5000之间的员工的信息

select * from emp where sal between 2000 and 5000;

--等价于：

select * from emp where sal>=2000 and sal<=5000;

in：后面加的是一个集合

--问题：查询部门10或部门20中的员工的信息

select * from emp where deptno in(10,20);

select * from emp where deptno=10 or deptno=20;

like：用来进行模糊查询

通配符：

_:表示匹配任意一个字符，必须有一个字符
%：表示匹配0个或者多个字符
[]：表示匹配【】中的任意一个字符
1
2
3
4
5
6
7
8
9
10
11
--问题：查询姓名中带有LLEN的员工信息

select * from emp where ename like '%LLEN%';

--问题：查询姓名以A开头，以N结束的员工的信息；

select * from emp where ename like 'A%N';

--查询员工姓名长度为5的员工的信息

select * from emp where ename like '_ _ _ _ _';

逻辑运算符:not and or

1
2
3
4
5
6
7
8
9
--查询emp表中部门10中的MANAGER或者部门20中的CLERK，显示其详细信息

select * from emp where deptno=10 and job='MANAGER'or deptno=20 and job='CLERK';

--查询部门10或者20中MANAGER

select * from emp where (deptno=10 or deptno=20) and job='MANAGER';

select * from emp where deptno in(10,20) and job='MANAGER';

8.运算符的优先级:

算术运算符>连接运算符>比较运算符>not>and>or

++++++++转载请注明出处++++++++++

本文标题:数据库SQL语言以及常见操作

文章作者:雷凯博

发布时间:2018年02月16日 - 13:02

最后更新:2021年05月16日 - 19:05

原始链接:http://yoursite.com/2018/02/16/2018-02-16-%E6%95%B0%E6%8D%AE%E5%BA%93SQL%E8%AF%AD%E8%A8%80%E4%BB%A5%E5%8F%8A%E5%B8%B8%E8%A7%81%E6%93%8D%E4%BD%9C/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

坚持原创技术分享，您的支持将鼓励我继续创作！
-------------本文结束感谢您的阅读-------------
0%