12.基本命令:
SQL>Show user;显示当前登录的用户
SQL>clear screen;清空屏幕
SQL>host cls;清空屏幕
SQL> spool c:\some.sql – 将以下执行的sql 语句和执行结果保存到some.sql文件中。
SQL> spool off;关闭,将数据写出到some.sql中。
13.
--创建表的DDL语句
--创建一个用户表 CONSTRAINT 创建表约束
create table users(
userid integer CONSTRAINT users_pk PRIMARY KEY,
username nvarchar2(50),
userpass nvarchar2(20)
);
--查看表结构的命令 desc 表名
desc users
--查询users表
select * from users;
--删除表 users
drop table users;
--更改表名
rename users to myusers;
--插入记录
insert into myusers values(5,'张无忌','123456','1992-12-05',5000);
--查询记录
select * from myusers;
--给myusers新增加一列 出生日期
--sysdate 是系统的当前时间 default 默认值 not null 不能为空
alter table myusers
add birthday date default sysdate not null;
--给myusers表添加性别(sex)字段
alter table myusers
add sex nvarchar2(6);
--查看表的(sex)字符长度和(username)的字节长度
select length(sex),lengthb(username) from myusers;
select length(birthday),lengthb(birthday) from myusers;
--修改字段(修改性别字段的默认值是男)
alter table myusers
modify sex default '男';
select * from myusers;
--添加新的用户
insert into myusers values(1,'张三丰','678988','1982-05-23');
insert into myusers values(2,'刘备','123456',sysdate);
insert into myusers values(4,'曹操','abcd12','1982-04-14');
insert into myusers (userid,username,userpass,birthday) values (3,'刘亦菲','345678','1990-12-24');
insert into myusers values(6,'午马','345671','1972-08-12',2000,'男');
--不允许对虚拟列插入值
insert into myusers (userid,username,userpass,birthday,salary,sex) values(8,'李四','123678','1992-09-12',2000,'女');
--给表添加注释
comment on table myusers is '用户信息表';
--从视图中查看备注信息
select * from SYS.user_tab_comments;
--复制一张新表
create table users as select * from myusers;
--删除表users 被删除的表会在回收站中
drop table users;
--删除表users 不经过回收站直接删除
drop table users purge;
--给myusers表再增加一个工资字段
alter table myusers
add salary number(10,2);
--修改字段(修改工资字段的默认值是1000)
alter table myusers
modify salary number(10,2) default 1000;
--修改(更新)字段值
update myusers set salary=1000;
--新增一个年薪虚拟列
alter table myusers add(year_salary as (salary*12));
--查看表结构
desc myusers;
describe myusers;
--给列重命名
alter table myusers
rename column year_salary to ysalary;
select * from myusers;
--删除虚拟列
alter table myusers
drop column ysalary;
alter table myusers
drop column year_salary;
--在表中复制一行
insert into myusers (userid,username,userpass) select 7,username,userpass from myusers where userid=3;
14.Oracle数据库中dual表:
dual是一个虚拟表,用来构成select的语法规则,oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情。
1、查看当前用户,可以在 SQL Plus中执行下面语句 select user from dual;
2、用来调用系统函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间
select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名
select SYS_CONTEXT('USERENV','language') from dual;--获得当前 locale
select dbms_random.random from dual;--获得一个随机数
3、得到序列的下一个值或当前值,用下面语句
select your_sequence.nextval from dual;--获得序列your_sequence的下一个值
select your_sequence.currval from dual;--获得序列your_sequence的当前值
4、可以用做计算器 select 7*9 from dual;
15.SELECT语句的基本语法
SELECT 子句
FROM 子句
[WHERE 子句]
说明
SELECT 子句:指定查询结果集的列的组成,列表中的列可以来自一个或多个表或视图
FROM 子句:指定要查询的一个或多个表或视图
WHERE 子句:指定查询的条件
关键字
是指单个的SQL元素。
例如,SELECT和FROM都是关键字
子句
是SQL语句的一部分。
例如,SELECT employee_id,last_name,…就是一个子句
语句
是两个或更多个子句的组合。
例如,SELECT * FROM employees就是一个SQL语句
选择所有的列:在SELECT关键字后加星号(*),也可以列出所有的列
投影:在SELECT语句中指定列名,使用逗号隔开,这样可以指定表显示某些列,也可以调整列的先后显示顺序
如果查询语句中有表达式时,表达式的名称将会显示在列位值,可以为其提供别名;别名可以使用双引号,也可以在别名前加AS关键字
例如: SELECT sal AS "员工工资" FROM emp;
SQL语句是不区分大小写的
SQL语句可以是一行,也可以是多行
关键字不能在两行之间一分为二或缩写
子句通常放在单独的行中,这样可以增强可读性并且易于编辑
使用缩进是为了增强可读性
关键字通常使用大写字母输入;所有其它文字都是使用小写输入的
使用算术运算符,可以创建数字和日期数据表达式。
算术表达式可以包含列名、常数值和算术运算符,+,-,*,/
乘和除的运算优先级高于加和减
优先级相同的运算符是从左到右进行运算的
可以使用小括号来强制语句做出优先运算,并使语句运算顺序更为清晰
例如:SELECT empno,ename, sal,sal/12 + 100 FROM emp;
使用括号
例如:SELECT empno,ename, sal,(sal+10000)/12 FROM emp;
连接运算符具有以下特定和功能
将列或字符串连接到其他列
由两条竖线表示(||) 或 concat(arg1,arg2)
创建一个合成列,它是字符串表达式
例如:SELECT empno||ename AS "Employees" FROM emp;
或者:SELECT concat(empno,ename) AS "Employees" FROM emp;
文字字符串:
文字是指包含在SELECT列表中的字符、数字或日期
日期和字符文字值必须包含在单引号中
在输出结果中,每个返回的行都会将每个字符串显示一次
WHERE子句在FORM后面,将查询对象限制在满足一定条件的行
condition(s):由列名、表达式、常数和比较运算符组成
WHERE子句可以比较列、文字值、算术表达式或函数的值。它包含三个元素:
列名
比较条件
列名、常数或值列表
Where子句中的and\or\in\not in\between\like关键字的使用
字符串和日期包含在单引号中
WHERE子句中的字符串和日期必须包含在单引号中。但是数字常数不能包含在单引号中
字符值是区分大小写的
默认日期格式为DD-MON-YY
Oracle数据库以内部数字格式存储日期,包括世纪、年、月、日、小时、分钟和秒
dual表常用在没有查询目标的SELECT语句块中,它只包含一行数据的系统表。
可以使用DESCRIBE查看dual表结构
DESCRIBE dual
查询dual表的数据
SELECT * FROM dual;
查询当前日期
SELECT sysdate FROM dual;
可以在日期上使用加法或减法运算符,加减操作的数字表示天数
例如,在日期上加3天:SELECT TO_DATE('14-5月-2012')+3 FROM dual;
在某日期上减3天:SELECT TO_DATE('14-5月-2012')-3 FROM dual;
比较运算符:
运算符 说明
= 等于
<>或!= 不等于
< 小于
大于
<= 小于等于
= 大于等于
ANY 比较值列表中的任何一个值
ALL 比较列表值中的所有值
<>运算符
查询empno不等于7369的客户信息:SELECT FROM emp WHERE empno <> 7369;
或者:SELECT FROM emp WHERE empno != 7369;
运算符
查询工资大于1000的员工:SELECT * FROM EMP WHERE SAL>1000;
ANY运算符
在WHERE子句中使用ANY运算符与列表中的任意值进行比较
必须在使用ANY运算符之前使用= , <> , < , > , <= ,或 >=运算符
例如:查询emp表,empno值只要大于列表(7369, 7521, 7499)中的任意一个值即满足条件
SELECT FROM emp
WHERE empno > ANY (7369, 7521, 7499);
ALL运算符
在WHERE子句中使用ALL运算符与值列表中的所有值进行比较
例如,查询emp表
SELECT FROM emp
WHERE empno > ALL(7369, 7521, 7499);
SQL运算符可以进行模式匹配,像字符串、列表值、NULL值。
LIKE:匹配字符串
IN:匹配列表值
BETWEEN:匹配范围值
IS NULL:匹配空值
IS NAN:匹配非数字值
可以在WHERE子句中使用LIKE运算符进行字符串的匹配
可以使用普通字符及通配符进行组合,通配符如下:
下划线(_):表示匹配某个位置的字符
百分号(%):表示在某个位置的任意个字符
例如,匹配’_o%’,表示第一个字母任意,第二个是o,%表示最后可以是任意个字母
SELECT *FROM emp WHERE ename LIKE '_M%';
转义字符:
如果要查询实际的下划线或百分号就需要使用ESCAPE选项区分通配符
ESCAPE选项告知数据库如何区分通配符和要匹配的字符,可以使用反斜杠(),例如,
SELECT ename FROM emp
WHERE ename LIKE '%\%%' ESCAPE '\';
IN运算符:
在WHERE子句中可以使用IN筛选某列的值是否在列表中
例如:查询empno在(7369, 7521, 7499)中的员工
SELECT FROM EMP
WHERE EMPNO IN (7369, 7521, 7499);
查询empno不在(7369, 7521, 7499)中的员工
SELECT FROM EMP
WHERE EMPNO NOT IN (7369, 7521, 7499);
Exists运算符
select from emp where exists (select from emp);
BETWEEN运算符:
在WHERE子句中可以使用BETWEEN检查某列的值是否在一个指定的范围内。包含区间两端的值。
例如,查询工资在800到1300之间的员工
SELECT * FROM emp
WHERE sal BETWEEN 800 AND 1300;
逻辑运算符:
逻辑运算符允许根据逻辑条件限制行
x AND y:x和y都必须是真时,返回真
x OR y:x或y为真时,返回真
NOT x:x为假时,返回真
AND运算符
例如:查询emp表中雇佣日期列大于’01-1月-1982’年,并且工资大于900的员工信息
SELECT FROM EMP
WHERE HIREDATE > '01-1月-1982'
AND SAL > 900;
OR运算符
例如:查询emp表中雇佣日期列大于’01-1月-1982’年,或者工资大于900的员工信息
SELECTFROM EMP
WHERE HIREDATE > '01-1月-1982'
OR SAL > 900;
ORDER BY字句 进行排序
可以使用ORDER BY对查询的结果进行排序,可以对一列或多列进行排序,ORDER BY必须在FROM子句后或WHERE子句后(如果有WHERE子句)
例如,查询EMP表,并对ename列进行排序
SELECT FROM EMP ORDER BY ename;
默认情况下,ORDER BY是按照升序(ASC)排序,也可以使用DESC指定为降序排序
例如,查询EMP表,先按empno升序排列,再按ename降序排列
SELECTFROM EMP
ORDER BY empno ASC, ename DESC;
同样可以是列的位置编号,指定哪列需要进行排序
例如,查询emp表,要对查询的第2列ename列进行降序排列
SELECT *FROM emp ORDER BY 2 DESC;
NULL值:
NULL值表示未知的值。它是一个特殊的值,但并不是空字符串,NULL值表示该列是未知的。
当某些查询语句在输出结果列上看不到值的时候,可能就是NULL值
SELECT * FROM EMP WHERE COMM IS NULL;
处理NULL值
NULL值被查询出来的时候没有显示信息,如何告知用户这是空字符串还是NULL,这可以通过NVL()函数来进行处理
SELECT empno,ename ,NVL(comm, 0) FROM EMP;
在SELECT语句中,可以使用DISTINCT关键字删除重复的行
例如,查询EMP表中,删除重复的job值
SELECT DISTINCT job FROM EMP;
SELECT中使用参数:
语法:使用&+参数名
例如:查询员工编号为某数值的员工
SELECT * FROM EMP WHERE EMPNO=&arg;