这篇博客详细介绍了如何在Oracle数据库中进行表格的创建、修改和删除，包括如何添加、删除列以及更新数据。此外，还讲解了使用WHERE、NVL、NVL2、ASC、DESC等条件进行查询，以及聚合函数MAX、MIN、SUM、AVG和COUNT的用法。通过示例展示了如何查询特定条件的数据、计算年薪、分组统计等操作。
## –创建表格
CREATE TABLE Account_szl1(
id NUMBER(9),
recommender_id NUMBER(9),
login_name VARCHAR2(30),
login_passwd VARCHAR2(8),
status CHAR(1),
create_date DATE,
pause_date date,
close_date date,
real_name VARCHAR2(20),
idcard_no char(18),
birthdate date,
gender CHAR(1),
occupation varchar2(50),
telephone VARCHAR2(15),
email VARCHAR2(50),
mailaddress VARCHAR2(50),
zipcode CHAR(6),
qq VARCHAR2(15),
last_login_time date,
last_login_ip VARCHAR2(15)
);
## --修改表名
RENAME account_szl to t_account_szl;
## --增加一列bak，其数据类型为varchar2，长度为50；
ALTER TABLE account_szl add(
bak VARCHAR2(50)
);
–bak列的长度为40，并增加默认值的设置，默认值为“login”；
ALTER table account_szl MODIFY(
bak VARCHAR2(40) DEFAULT’login’
);
–删除t_Account_XXX表中的bak列。
ALTER TABLE account_szl drop(bak);
–向t_Account_XXX表中插入一条记录
INSERT INTO account_szl(id,login_name,login_passwd,create_date,real_name,idcard_no,telephone) VALUES
(1,‘chenhh’,256528,SYSDATE,‘chenhehe’,410381194302256523,13669351234);
update account_szl set create_date =(to_date(‘2008-01-28’,‘yyyy-MM-dd’));
where id = 1;
–将账务账户表ID为1的账务账户的密码更改为801206。
UPDATE account_szl set login_passwd = 801206
where id = 1 ;
–删除账务账户表中ID为1的账务账户信息。
DELETE from account_szl
WHERE id = 1;
创建表格
CREATE TABLE emp_szl(
empno NUMBER(4,0),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4,0),
hiredate date,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0)
);
CREATE table dept_szl(
deptno NUMBER(2,0),
dname VARCHAR2(14 BYTE),
loc VARCHAR2(13 BYTE)
);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7369,‘SMITH’,‘CLERK’,7902,to_date(‘1980/12/17’,‘yyyy/MM/dd’),800,NULL,20);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7499,‘ALLEN’,‘SALESMAN’,7698,to_date(‘1981/2/20’,‘yyyy/MM/dd’),1600,300,30);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7521,‘WAED’,‘SALESMAN’,7698,to_date(‘1981/2/22’,‘yyyy/mm/dd’),1250,500,30);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7566,‘JONES’,‘MANAGER’,7839,to_date(‘1981/4/2’,‘yyyy/mm/dd’),2975,null,20);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7654,‘MARTIN’,‘SALESMAN’,7698,to_date(‘1981/9/28’,‘yyyy/mm/dd’),1250,1400,30);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7698,‘BLAKE’,‘MANAGER’,7839,to_date(‘1981/5/1’,‘yyyy/mm/dd’),2850,null,30);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7782,‘CLARK’,‘MANAGER’,7839,to_date(‘1981/6/9’,‘yyyy/mm/dd’),2450,null,10);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7788,‘SCOTT’,‘ANALYST’,7566,to_date(‘1987/4/19’,‘yyyy/mm/dd’),3000,null,20);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7839,‘KING’,‘PRESIDENT’,null,TO_DATE(‘1981/11/17’,‘yyyy/mm/dd’),5000,null,10);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7844,‘TURNER’,‘SALESMAN’,7698,TO_DATE(‘1981/9/8’,‘yyyy/mm/dd’),1500,0.00,30);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7876,‘ADAMS’,‘CLERK’,7788,to_date(‘1987/5/23’,‘yyyy/mm/dd’),1100,null,20);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7900,‘JAMES’,‘CLERK’,7698,to_date(‘1981/12/3’,‘yyyy/mm/dd’),950,null,30);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7902,‘FORD’,‘ANALYST’,7566,to_date(‘1981/12/3’,‘yyyy/mm/dd’),3000,null,20);
INSERT INTO emp_szl(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES
(7934,‘MILLER’,‘CLERK’,7782,to_date(‘1982/1/23’,‘yyyy/mm/dd’),1300,null,10);
INSERT INTO dept_szl(DEPTNO,DNAME,LOC)VALUES
（10,‘ACCOUNTING’,‘NEW YORK’）;
INSERT INTO dept_szl(DEPTNO,DNAME,LOC)VALUES
(20,‘RESEARCH’,‘DALLAS’);
INSERT INTO dept_szl(DEPTNO,DNAME,LOC)VALUES
(30,‘SALES’,‘CHICAGO’);
INSERT INTO dept_szl(DEPTNO,DNAME,LOC)VALUES
(40,‘OPERATIONS’,‘BOSTON’);
SELECT JOB,LENGTH(JOB) FROM EMP ORDER BY JOB; --打印工作的长度
SELECT ENAME||‘earns$’||sal||‘monthly but wants $’||sal3 as “Dream Salary” from EMP; --打印字符串拼接
SELECT EMPNO,ENAME from emp where UPPER(job) = ‘CLERK’; --打印编号和姓名 -job为CLERK的
SELECT SUBSTR(‘Doctor Who’, 1 , 6)from dual; --切割
SELECT TRIM(‘半’from’半月二更半’) AS “上联” from dual; --将两边的‘半’字去除
SELECT TRIM(leading’半’from’半月二更半’)AS"上联" from dual; --将左边的‘半’字去除
SELECT TRIM(trailing’半’from’半月二更半’)AS"上联" from dual; --将右边的‘半’字去除
SELECT LPAD(‘TARDIS’, 10, '’)from dual; --左拼接
SELECT LPAD(‘TARDIS’, 3, '’)from dual; --右拼接
SELECT * FROM EMP WHERE LENGTH(ENAME)=5; --答应出名字长度为5的用户信息
SELECT ENAME,SAL,ROUND(sal1.08)from emp; --打印出名字，工资，在打印出工资的1.08倍 四舍五入
SELECT MOD(11,4)from dual; --除法取余数
SELECT TRUNC(123.123, -1)from dual; – 从小数点前面几位四舍五入
SELECT FLOOR(SYSDATE-HIREDATE) as “入职天数”,ENAME FROM EMP; --根据系统时间-入职时间计算入职天数
SELECT * FROM EMP where EXTRACT(year from hiredate) = ‘1987’; --截取年份，打印出1987年出生的用户信息
SELECT ENAME, LAST_DAY(HIREDATE)from emp; – 打印姓名和入职月份的最后一天
SELECT NEXT_DAY(SYSDATE, 7)from dual; --打印出明天的时间
SELECT ENAME,ADD_MONTHS(HIREDATE, 3)from EMP; --打印出入职时间加3天
SELECT ENAME,MONTHS_BETWEEN(SYSDATE, HIREDATE)from EMP; --打印当前时间-入职时间的天数
SELECT GREATEST(TO_DATE(‘20070101’, ‘yyyymmdd’), HIREDATE)from EMP; --将所有的hiredate 转换成20070101输出
–7月18日
–where
SELECT * FROM emp_szl
–where
–在部门20的人
–DEPTNO =20;
--工资大于3000的人 > >=
--sal >= 3000;
--工资小于3000的人 < <=
--sal <= 3000;
--工资不等于3000的人 <>
--sal<>3000;
--大于3000或者小于3000 or and (and的优先级比较高)
-- (sal>3000 or sal<3000)
-- and DEPTNO =20;
--job 是salesman的
-- LOWER(job) = 'SALESMAN';
-- 名字长度超过5位的人
--LENGTH(ENAME)> 5;
--名字中以 s 开头的人 通配符 %：0个或者多个 _：表示一个,模糊查询
--ename like '%S%';
--查找没有领导的人
--null 值很特殊，不能判断、也不能够计算，代表无穷大，或者无穷小。
--mgr is null;
--mgr is not null;
--工资大于 3000，2000，1200中的任意一个
--sal > any(3000,2000,1200);
--工资大于每一个
--sal > all(3000,2000,1200);
--工资等于 3000 1500 1200 中的任何一个
--sal in(3000,1500,1200)
**
nvl nvl2 asc desc
**
–查找所有人的年薪=sal12 +comm
–SELECT 后可以跟字段、表达式、函数
–nvl(comm,0):如果comm为null，则取0
SELECT ENAME,SAL * 12,comm,
sal12 + NVL(comm, 0) as salary – 起名 ，as 可以省略
from emp_szl;
--nvl2(comm,0,1000) 如果comm为null，取1000 否则取0
--查询出来的员工，按照入职时间排序 - 默认升序 asc 降序 desc
SELECT * from emp order by hiredate asc ;
聚合函数max min sum avg count
分组函数、聚合函数 max min sum avg count
SELECT COUNT(EMPNO) from emp_szl;
– 分组函数 忽略null值
SELECT COUNT(comm) from emp_szl;
–可以不受空值影响
SELECT COUNT(1) from emp_szl;
–每个部门最高工资
SELECT DEPTNO,MAX(SAL) FROM emp_szl
GROUP BY DEPTNO;
--------注意，错误！ename14条，max(sal) 1 条
--------结论： select子句中出现的字段，必须同时出现在group 里面。
--------错误例子：SELECT ename ,MAX(sal) from emp_szl;
–通过emp表查询员工部门都有哪些
select DEPTNO from emp;
select DISTINCT deptno FROM emp_szl;–去重复
select DISTINCT DEPTNO, MGR from emp_szl;------去重复
--------课堂练习 day02 练习
–1
SELECT * from emp_szl
where UPPER(ENAME)=‘SMITH’ or LOWER(ENAME)=‘smith’;
–2
SELECT * from emp_szl
WHERE LENGTH(ENAME) = 5;
–3
SELECT ENAME,LPAD(SAL, 15, ‘$’)salary
from emp_szl;
–4
SELECT ENAME,HIREDATE,FLOOR(SYSDATE-HIREDATE) worktime
from emp_szl;
–5
SELECT TO_CHAR(SYSTIMESTAMP, ‘yyyy-mm-dd hh24-mi-ss’)
from dual;
–6
SELECT TO_CHAR(HIREDATE,‘DD" of "MON yyyy’)
from emp_szl;
–7
ALTER TABLE emp_szl add(
review DATE
);
SELECT ENAME,TO_CHAR(hiredate,‘yyyy-mm-dd’) hiredate,TO_CHAR(HIREDATE,‘yyyy"年 "mm"月 "dd"日 "’)review
FROM emp_szl;
–8
CREATE TABLE customer_szl(
cust_id NUMBER(4),
cname VARCHAR2(25),
birthday DATE,
account NUMBER
);
INSERT into customer_szl VALUES
(1001,‘张三’,TO_DATE(‘1980-1-23’, ‘yyyy-mm-dd’),9800);
INSERT into customer_szl VALUES
(1002,‘李四’,TO_DATE(‘1981-2-23’, ‘yyyy-mm-dd’),12000);
INSERT into customer_szl VALUES
(1003,‘王五’,TO_DATE(‘1982-3-23’, ‘yyyy-mm-dd’),15000);
INSERT into customer_szl VALUES
(1004,‘赵六’,null,9800);
INSERT into customer_szl VALUES
(1005,‘啊七’,TO_DATE(‘1987-3-23’, ‘yyyy-mm-dd’),19000);
INSERT into customer_szl VALUES
(1006,‘小八’,TO_DATE(‘1987-3-23’, ‘yyyy-mm-dd’),null);
SELECT
NVL2(BIRTHDAY, TO_CHAR(BIRTHDAY), ‘not available’),
NVL(TO_CHAR(account), ‘no account’)
FROM customer_szl;
–9
SELECT * from customer_szl
where EXTRACT(year from BIRTHDAY) = ‘1987’;
–10
SELECT EMPNO,ENAME,sal+NVL(comm, 0) from emp;
–day03练习
—1⃣️
–1
SELECT *
from emp_szl
order by hiredate asc;
–2
SELECT ENAME,SAL
from emp_szl
where sal>1600;
–3
SELECT ENAME,DEPTNO
from emp_szl
WHERE EMPNO = 7369;
–4
– BETWEEN n and m ----- >=n <=m
SELECT ENAME,SAL
from emp_szl
where sal<4000 or sal>5000;
–where sal not BETWEEN 4000 and 5000;
–5
SELECT ENAME
FROM emp_szl
where DEPTNO is null;
–6
SELECT ENAME, sal + NVL(comm*0.8, 0) as money
from emp_szl;
ORDER BY sal asc;
/**
– 分组函数、聚合函数 max min sum avg count
SELECT COUNT(EMPNO) from emp_szl;
– 分组函数 忽略null值
SELECT COUNT(comm) from emp_szl;
–可以不受空值影响
SELECT COUNT(1) from emp_szl;
–每个部门最高工资
SELECT DEPTNO,MAX(SAL) FROM emp_szl
GROUP BY DEPTNO;
--------注意，错误！ename14条，max(sal) 1 条
--------结论： select子句中出现的字段，必须同时出现在group 里面。
--------错误例子：SELECT ename ,MAX(sal) from emp_szl;
–通过emp表查询员工部门都有哪些
select DEPTNO from emp;
select DISTINCT deptno FROM emp_szl;–去重复
select DISTINCT DEPTNO, MGR from emp_szl;------去重复
–加上组函数的条件 having 效率非常低
**/
----day03分组查询练习
–1
SELECT JOB,MAX(SAL),MIN(SAL),AVG(SAL),SUM(SAL)
FROM emp_szl
GROUP BY JOB ;
–2
SELECT JOB,COUNT(1)
FROM emp_szl
GROUP BY JOB;
–3
SELECT (MAX(sal)-MIN(sal))DIFFERENCE
FROM emp_szl;
–4
SELECT
MGR,MIN(SAL),AVG(sal)
FROM emp_szl
WHERE MGR is NOT null and sal >=900
GROUP BY MGR;
having avg(sal) >=1300
–from - where - group - having - order by
–5
SELECT
deptno,count(*)
from emp_szl
where sal>=1500
GROUP BY deptno;
–6
SELECT
DEPTNO,AVG(nvl(COMM,0)) avg_comm
from emp_szl
GROUP BY DEPTNO;
扫一扫
868
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
