插入语句
intsertinto表名values(值1值2值3…..)插入的数值的字段类型要与定义的时候一样
intsertinto表名values(值1,值2,null,值3………)有不想写的可以用null来标示
intsertinto表名(字段1.字段2,字段3………)values(值1值2值3……)类型对应
注意:
数值的类型个数顺序不能超过最大值
插入记录到表中INSERT的语法
INSERTINTO表名(字段名1,字段名2,......)VALUES(值1,值2......);
为MYEMP中增加一条记录
INSERTINTOmyemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7899,'张三','清洁工','7369','14-2月-1995',9000,300,40)
注:
给定的值要和指定的字段数一致
当为表中的所有列都插入数据数,可以采用如下简单写法
INSERTINTO表名VALUES(值1,值2......);
INSERTINTOmyempVALUES(7991,'李四','清洁工','7369','14-2月-
1995',9000,300,40)
注:
要求给定值的数目和表中列的数目一致,并且值的顺序和表中列的顺序一致。
新增加一条记录到MYEMP中,但是该员工没有奖金也没有上级领导
第一种写法:
INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno)
VALUES(7899,'张三','清洁工','14-2月-1995',9000,40)
第二种写法:
(用null来指定没有值的列)
INSERTINTOmyempVALUES(7898,'王五','清洁工',null,'14-2月-
1995',9000,null,40)
新增加一条记录到MYEMP中,但日期格式按'1988-09-09'的方式指定
�TO_DATE函数的使用
INSERTINTOmyemp(empno,ename,job,hiredate,sal,deptno)
VALUES(7896,'赵六','清洁工',TO_DATE('1984-09-09','yyyy-mm-dd'),9000,40);
修改记录
update表名set字段=值,字段=值,字段=值…….where条件;
注意:
条件必须写
删除记录
deletefrom表名where条件;
注意:
条件必须写
ROLLBACK(回滚)
在sqlplus中如果使用ROLLBACK的话那么将撤销操作直到上次执行commit的时候的状态
标量函数的使用
转化大小写
selectupper(‘sddfa’)fromdualselectlower(‘sddfa’)fromdual
将首字母大写其余小写
selectinitcap(ename)fromemp;
连接字符串:
selectcount(deptno)fromdept;
selectcontact(‘hello’,’woeld’)fromdual;
select‘abc’||‘jkh’||‘sdf’fromdual;
求子串:
selectsubstr(字符串,起始位,结束位)fromdual;起始位写0或者1都可以代表第一位
selectsubstr(字符串,起始位)fromdual;从起始位开始往后截取
求字符串长度:
selectlength(ename)fromemp;
字符串替换
selectreplace(原始字符串,想要被替换的字符串,替换字符串)fromdual;
输出字符串的后三位
selectename,substr(ename,length(ename)-2)fromdual;
selectenamesubstr(ename,-3)fromdual;
四舍五入
selectround(132.78454)fromdual;四舍五入取整
selectround(132.78345,2)fromdual;四舍五入并且指定保留小数点的位数
selectround(1323.7858416,-2)fromdual;小数不要并且整数位两位取整本代码得1300
截断小数位
selecttrunc(546541.151)fromdual不管四舍五入直接截断小数5465541
selecttrunc(789.536,2)fromdual截断小数指定小数的数位789.53
selecttrunc(789.536,-2)fromdual截断小数并且对整数进行截断700
取余
selectmod(10,3)fromdual;取余数1
运算规律:
日期+数字=日期
日期--数字=日期
日期—日期=数字
显示系统时间
selectsysdatefromdual;
selectto_char(sysdate,'yyyy-mm-dd,hh24:
mi:
ss')fromdual;
显示某本门员工进入公司星期数:
selectempno,ename,round((sysdate-hiredate)/7)fromempwheredeptno=10;
在指定的日期加上指定的月数后的日期:
selectadd_months(sysdate,2)fromdual;
求出给定日期范围的月数:
selectempnoenamemonth_between(sysdate,hiredate)fromemp;
下一个给定的星期是那个日期:
selectnext_day(sysdate,’星期一’)fromdual;
求出给定日期所在的月份的最后一天的日期
selectlast_day(sysdate)fromdual;
转换函数
转换成字符串(TO_CHAR)
查询雇员号,姓名,以及入职的年份
SELECTempno,ename,TO_CHAR(hiredate,'yyyy')FROMemp;
说明:
yyyy匹配年份,mm匹配月份,dd匹配日
使用TO_CHAR设置日期的显示格式
SELECTempno,ename,TO_CHAR(hiredate,'yyyy-mm-dd')FROMemp;
使用fm去掉前导0
SELECTempno,ename,TO_CHAR(hiredate,'fmyyyy-mm-dd')FROMemp;
通过TO_CHAR设置数值的格式
SELECTempno,ename,TO_CHAR(sal,'99,999')FROMemp;
说明:
9代表1位数字
显示金额
SELECTempno,ename,TO_CHAR(sal,'$99,999')FROMemp;
说明:
$代表美元,L代表本地
转换成数字(TO_NUMBER)
SELECTTO_NUMBER('123')+TO_NUMBER('123')FROMdual;
转换成日期(TO_DATE)
SELECTTO_DATE('2009-07-31','yyyy-mm-dd')FROMdual;
通用函数
查询每个员工的年收入
SELECTempno,ename,(sal+NVL(comm,0))*12incomeFROMemp;
说明:
(COMM,0)当comm的值为null时,用0替换
常用的列函数
SUM(expression)求和
MAX(expression)求最大值
MIN(expression)求最小值
COUNT(expression)统计记录数
COUNT(DISTINCTCOLNAME)统计去除重复行记录数
SQL语句的执行顺序
FROM
WHERE
GROUPBY
HAVING
SELECT
ORDERBY
创建表
createtable表名(字段1数值类型defultxx,字段2数值类型,……………);
defult是默认值可写可不写
createtable表名2as表名1复制表2到表1中
createtable表名as(子查询)复制子查询结果这张表,当子查询结果没有记录时这时只会复制表结构(数据类型)而不会增加数据(有时目的只是复制表结构那么就使用这种方法比如查询语句中使用select1=2from表名)
删除表
droptable表名;
注意:
delete是删除表里面的数据
修改表结构
增加一列:
altertable表名add(列名称列数据类型defult默认值)
注意:
defult和默认值可写可不写
删除列:
altertable表名dropcolumn列名称
注意:
不管有没有数据都会删除
修改列的数据类型:
altertable表名modify(列名称数据类型defult默认值)
注意:
修改的时候(和原来相同数据类型)长度要求不能小于数据中数据的最小长度,但是能增大,要想改变数据类型的话,必须将字段下的数据全部删除才可以更改数据类型
表名重命名
rename表名(旧)to表名(新);
截断表
truncate表名;
注意:
清空表中的所有数据立即释放资源,该操作不可回滚
ROWNUM
表示行号,是一个伪列,可以在每一张表中出现
应用:
查询表中记录的前五行
SELECT*
FROMEMP
WHEREROWNUMBETWEEN<=5;
现在要查询从6到10条记录,使用子查询
SELECT*FROM(SELECTROWNUMnum,empno,ename,salFROMemp)eeWHERErownumBETWEEN6AND10;
视图
创建视图
语法:
CREATEVIEW视图名称AS子查询
例如:
创建部门20员工的雇员信息,包含EMPNO,ENAME,SAL,DEPTNO
CREATEVIEWempv20AS
SELECTempno,ename,sal,deptnoFROMemp;
注:
当创建视图以后,可以像操作表一样的操作视图
注:
当创建视图时,视图的操作会影响到对表的操作,这是不安全的,我
们可以采用如下选项来创建视图
�WITHCHECKOPTION不能更新创建视图的条件
�WITHREADONLY创建只读视图
删除视图
语法:
DROPVIEW视图名称
DROPVIEWempv20;删除视图empv20;
注:
当删除视图所在的表时,则视图也不能再被使用
序列
创建序列
语法:
CREATESEQUENCE序列名称
[MAXVALUEnum|NOMAXVALUE]
[MINVALUEnum|NOMINVALUE]
[INCREMENTBYnumSTARTWITH10]
[CACHEnum|NOCACHE];
[CYCLE|NOCYCLE]
创建序列CREATESEQUENCEmyseq;
序列的操作
nextVal:
取得序列的下一个内容
currVal:
取得当前序列的内容
select序列名称.nextvalfromdual.
创建表验证序列的操作
�CREATETABLEtestseq(
nextNUMBER;
CURRNUMBER;
)
INSERTINTOtestseqVALUES(myseq.nextVal,myseq.currVal);
将以上插入语句,执行5次,观察效果,不难得出结论,nextVal每次都会加1,而
currVal都是取当前值
创建序列指定每次增长的增量
CREATESEQUENCEmyseqINCREMENTBY2;
创建序列指定开始的序列,默认的序列从1开始。
CREATESEQUENCEmyseqINCREMENTBY2STARTWITH10;
创建一个循环序列1,3,5,7,9。
CREATESEQUENCEmyseqMAXVALUE9INCREMENTBY2
STARTWITH1CACHE2CYCLE;
数据库的常用操作语言:
showuser;察看当前连接的用户
connectscott/passwored采用scott的用户名/密码连接数据库
desctable_name;察看tableName表结构
quit|exit;退出
disconnect;断开连接
clearscreen;清屏,相当于Windows下的cls命令
select*fromtab;列出当前用户下的所有表。
@path执行path指定的脚本文件
Oracle常用基本数据类型:
varchar2/varchar变长字符串
char定长字符串
Integer整型
number(m,n)数字型
smallint短整型
float浮点数
decimal十进制数字
date日期型
SQL分类
DML(DataManipulationLanguage,数据操作语言)
用于检索或修改数据
DDL(DataDefinitionLanguage,数据定义语言)
用于定义数据的结构,如创建修改或者删除数据库对象
DCL(DataControlLanguage,数据控制语言)
用于定义数据库用户的权限
约束
constraint约束名约束字段
主键约束(PRIMARYKEY)
CONSTRAINTpersion_pid_pkPRIMARYKEY(pid);
非空约束:
nameVARCHAR2(30)NOTNULL,
唯一约束:
nameVARCHAR2(30)UNIQUENOTNULL,在创建表时声明
CONSTRAINTperson_name_ukUNIQUE(pid)在表的结尾声明
检查约束:
(check)
ageNUMBER(3)NOTNULLCHECK(agebetween1and150),
主外键约束:
(如一本书要属于一个人)
CREATETABLEperson(
pidVARCHAR2(18),
nameVARCHAR2(30)NOTNULL,
ageNUMBER(3)NOTNULL,
birthdateDATE,
sexVARCHAR2
(2)DEFAULT'男'
CONSTRAINTperson_pid_pkPRIMARYKEY(pid),
CONSTRAINTperson_name_ukUNIQUE(name),
CONSTRAINTperson_age_ckCHECK(ageBETWEEN1AND150),
CONSTRAINTperson_sex_ckCHECK(sexIN('男','女','中'))
);
CREATETABLEbook(
bidNUMBERPRIMARYKEYNOTNULL,
bnameVARCHAR(30),
bpriceNUMBER(5,2),
pidVARCHAR2(18),
CONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESperson(pid)
);
增加约束
ALTERTABLE表名称ADDCONSTRAINT约束名称约束类型(约束字段)
例:
分别增加主键约束和外键约束给book表
ALTERTABLEbookADDCONSTRAINTbook_bik_pkPRIMARYKEY(bik);
ALTERTABLEbookADDCONSTRAINTperson_book_pid_fkFOREIGN
KEY(pid)REFERENCESperson(pid)ONDELETECASCADE;
约束的命名规范(建议)
PRIMARYKEY:
表名称_主键名称_pk;
UNIQUE:
表名称_字段名称_uk
CHECK:
表名称_字段名称