数据库概论
数据库做什么
- 存储大量数据,方便检索和访问;
- 保持数据信息的一致,完整;
- 共享和安全;
- 组合分析,产生有用的信息;
基本概念
数据(Date):
数据库(Datebase):
数据库管理系统(DBMS):
数据库系统(DBS):
数据库管理员(DBA):
关系数据库管理系统(RDBMS):
SQL语言(Structured Query Language):
层次数据库
网状数据库
关系数据库
基本概念
实体(entity):
属性(attribute):
码(key):
域():
域里面存放的数据是独立的,且不可划分;
联系:
- 1 : 1 的联系:一个人有一个身份证;
- 1 : n 联系:一个班级里有N多个学生;
- m : n 联系:学生和课程(老师)的关系;
关系数据库的数据结构
数据的逻辑是一张二维表,有行和列;
- 主键:
- 外键:
基本查询操作:
简单的数据查询语句:
语法: SELECT *列名1,[列名2,列名3,….] FROM 表名
限定查询(条件查询)
范围查询
Eg2.查询81年入职的所有员工信息。
SELECT * FROM emp WHERE hiredate BETWEEN ‘1-1月-81’ AND ‘31-12月-81’
Oracle中表示日期格式时需要使用单引号括起来。判断是否为空 IS (NOT)NULL
-
基本语句练习:
简单的数据查询语句:
-- 1. 查询所有员工的全部信息
select * from emp;
-- 2.查询员工的姓名与工作以中文的形式显示;
select ename, job FROM emp;
-- 3.要求姓名与工作以中文的形式显示。
SELECT ename "姓名", job "工作职位" FROM emp;
SELECT ename 姓名, job 工作职位 FROM emp;
-- 4.查询每个员工的职位(去掉重复的行记录);
SELECT job FROM emp;
SELECT DISTINCT job FROM emp;
--(排重操作使用关键字DISTINCT(distinct))
--5. 查询所有员工的基本信息,并且显示效果是:编号是7369,姓名是Smith,工作职位是cleck
SELECT '编号是'||empno||'姓名是'||ename||'工作职位是'||job FROM emp;
--6.查询所有员工的姓名与年薪。
SELECT ename,sal*12 income FROM emp;
SELECT ename,(sal+300)*12+sal income FROM emp;
--(查询 每月300交通费+一个月工资 )
限定查询(即条件查询)
--1. 要求查询职工编号为7788的员工信息。
SELECT * FROM emp WHERE empno = 7788;
--2.查询名字叫simth 的员工信息
SELECT * FROM emp WHERE ename = 'SMITH';
--3.查询工资高于1500的员工信息
SELECT * FROM emp WHERE sal > 1500;
--4. 查询职位是办事员或者销售人员的全部信息,并且要求这些员工的工资大于1200.
SELECT * FROM emp WHERE (job ='CLERK' OR job = 'SALESMAN') AND sal > 1200;
--5.查询员工编号不是7788的员工信息;
SELECT * FROM emp WHERE empno != 7788;
SELECT * FROM emp WHERE empno =7788;
--6.查询姓名中包括A的员工信息
--(模糊查询,关键字like)
SELECT * FROM emp WHERE ename LIKE 'A%';
--(所有ename以A开头的)
SELECT * FROM emp WHERE ename LIKE2 '_A%';
--(ename A是作为第二位的名字)
SELECT * FROM emp WHERE ename LIKE2 '%A%';
--(ename 字母A在名字中间的)
SELECT * FROM emp WHERE ename LIKE2 '%%';
--(查询所有的ename)
SELECT * FROM emp WHERE ename NOT LIKE2 '%A%';
--(查询所有ename中没有A字母的)
范围查询
--1. 查询工资在1500到3000之间的所有员工信息
SELECT * FROM emp WHERE sal > 1500 AND sal < 3000;
SELECT * FROM emp WHERE sal between 1500 AND 3000;
--(在Oracle中范围查询使用BETWEEN …AND…关键字。)
--2. 查询81年入职的所有员工信息。
SELECT * FROM emp where hiredate between '1-1月-81' AND '31-12月-81';
--(注意日期格式 --'1-1月-81' -- '日-月-年'且月份的数字后面一定要带 '月' 字)
--3. 查询职工编号是:7788,7499,7521的员工信息。
SELECT * FROM emp WHERE empno = 7788 or empno = 7499 or empno = 7521;
SELECT * FROM emp WHERE empno in (7788,7499,7521);
--(范围查询可以使用IN 关键字; NOT IN表示不在某些范围)
--4. 查询SMITH,FORD ,KING的信息.
SELECT * from emp where ename IN ('SMITH','FORM','KING');
--5. 查询职工编号不是:7788,7499,7521的员工信息.
SELECT * FROM emp WHERE empno NOT IN (7788,7499,7521);
--(NOT IN使用注意:如果使用了IN操作符,查询的范围之中有null,不影响查询)
SELECT * from emp where empno IN (7369,7566, NULL);
SELECT * from emp where empno NOT IN (7369,7566, NULL);
--(如果使用NOT IN操作符,查询范围之中有null,不会又任何结果返回)
判断是否为空IS(NOT)NULL
--1. 查询出所有有奖金的员工姓名和工作职位以及入职日期
SELECT ename,job FROM emp WHERE comm is NOT NULL;
--2. 查询出没有资金的员工姓名和工作职位以及入职日期。
SELECT ename,job,hiredate FROM emp WHERE comm IS NULL;
--3. 查询工资大于1500,并且有奖金的员工。
SELECT * FROM emp WHERE sal >1500 AND comm is NOT NULL;
--4. 查询工资大于2000,或者没有奖金的员工。
SELECT * FROM emp where sal >2000 or comm is not null;
--5. 查询工资大于1500 并且有奖金以外的员工。
select * from emp where not(sal > 1500 and comm is not null);
排序查询结果
--1. 查询所有员工姓名,入职日期,工资。结果按入职升序显示。
select ename,hiredate,sal FROM emp order BY hiredate ASC;
--(如果是升序则关键字ASC可以省略。)
--(在排序查询中,如果对多个字段排序,则先按第一个排,再按第二个排。)
--2. 查询所有员工姓名,入职日期,工资。结果按入职升序显示,工资降序。
SELECT ename ,hiredate,sal FROM emp order BY hiredate ASC, sal DESC;
常用单行函数
函数是各种数据库系统的最大区别。
字符函数
Oracle中为了验证函数功能,必须编写完整的SQL语句;所以Oracle为了查询方便,专门提供了一个”dual”虚拟表供测试。
- UPPER(String|column)将字符转成大写
Oracle 常用单行函数练习
字符函数
–1. upper(string|columu)可以将字符转成大写;
select upper(‘helloworld’) from dual;
select upper (ename) FROM emp;
--2.lower(String|column)将制定的字符串转换成小写;
select lower('HELLOWORLD') FROM dual;
SELECT lower(ENAME) FROM emp;
--3. INITCAP(String|column)将单词的第一个字母大写;
SELECT initcap('hello world') FROM dual;
SELECT initcap(ENAME) FROM emp;
--4. LENGTH(String|column)计算字符串的长度;
SELECT ENAME,LENGTH(ENAME) FROM emp;
SELECT ENAME,LENGTH(ENAME) FROM EMP WHERE LENGTH(ename)=5;
--5. REPLACE(String|column)替换字符串;
SELECT REPLACE(ENAME,'A','_') FROM emp;
--6. CONCAT(String1,String2)拼接字符串||;
SELECT concat('HELLO', 'WORLD') FROM dual;
--7. SUBSTR(String,start,length)截取字符串;
SELECT ENAME, substr(ENAME, 0, 3) FROM emp;
SELECT ENAME, substr(ENAME, 1, 3) FROM emp;
--截取每个员工姓名的最后三个字母;
SELECT ENAME, substr(ENAME,-3) FROM emp;
数值函数
-- 1. ROUND()对数字进行四舍五入。
--ROUND(number,[index])按指定位置对数字进行四舍五入运算
--index为正:表示小数点右边的位置 index为负:表示小数点左
SELECT round(1024, 633), round(-1024, 633) FROM dual;
--SELECT round(903, 633,-1),round(1024, 633,2) from dual;
--2. TRUNC()对数字进行截取(与ROUND作用差不多,唯一区别是不进行四舍五入操作);
SELECT trunc(1024, 633), trunc(-1024, 633) from dual;
--3. MOD(number1,number2)求模运算
SELECT MOD(10,3) FROM dual;
日期函数
--1. 获得当前时间用sysdate;
SELECT sysdate FROM dual;
--(1) 日期加减。
SELECT sysdate + 3, sysdate + 300 FROM dual; --当前日期之后
SELECT sysdate - 3, sysdate - 300 from dual; --当前日期之前
-- 日期 - 数字 = 日期 ; 日期 + 数字 = 日期 ; 日期 - 日期 = 数字(天数);
--(2)查询所有员工入职的星期数;
SELECT ename, sysdate - hiredate days FROM emp ;
SELECT ename,(sysdate - hiredate) / 7 weeks FROM emp;
--3.LAST_DAY()计算指定日期所在月的最后一天。
SELECT last_day(sysdate) FROM dual;
--4.NEXT_DAY()求下一个指定星期X的日期
-- 计算下一个星期三是几号;
SELECT next_day(sysdate,'星期三') FROM dual;
--5.ADD_MONTHS()在指定日期上加上相应的月数;
SELECT add_months(sysdate , 4) FROM dual ;
--ADD_MONTHS(date,number)是在日期date上加上相应number个月
-- date:表示日期 ; number:表示需要加上月数;
--6.MONTHS_BETWEEN()求出指定日期范围的月数。
-- 查询所有员工的入职月数;
select ename, trunc(months_between(sysdate, hiredate)) mons FROM emp;
转换函数
--7.TO_CHAR() 日期或数字转换成字符串
SELECT to_char (sysdate, 'yyyy-mm-dd') FROM dual;
SELECT TO_CHAR(sysdate,'yyyy-mm-dd') FROM dual;
--SELECT TO_CHAR(sysdate,'yyyy-mm-dd'), TO_CHAR(sysdate,'yyyy'), TO_CHAR(sysdate,'mm') TO_CHAR(sysdate,'dd') from dual;
SELECT TO_CHAR(sysdate,'fmyyyy-mm-dd') FROM dual; -- 去掉0
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') day FROM dual;
SELECT TO_CHAR(88888888888,'999,999,999,999,999') FROM dual;
SELECT TO_CHAR(88888888888,'L999,999,999,999,999') FROM dual;
--YYYY年份 Mm月份 DD日 HH(HH24|HH12)小时 MM(Mi)分钟 SS秒 Dy星期几
--最常用用格式:‘yyyy-mm-dd HH24:Mi:SS dy’
--可以使用fm将前置的0去年如月份前的0使用”fmYYYY-MM-DD”如:2009-01-01会显示成1009-1-1
--当用在数字上时可以对数字进行格式化显示。 9:表示普通数字 $:表示美元符号 L:表示本地Local
--8.TO_NUMBER() 将数字形式的字符串转换成数值
SELECT '123' + 456 FROM dual;
--9.TO_DATE() 将字符串转换成日期
select to_date ('1989-10-12', 'yyyy-mm-dd') FROM dual;
通用函数
--1.NVL函数
--NVL(value1,value2):表示的意思就是如果valu1为值null,则计算时用value2替换参与运算。
SELECT ename,job,hiredate,(sal + nvl(comm,0)) * 12 income FROM emp ;
--2.DECODE函数 CASE 多条件多数值判断
--DECODE(表达式,匹配条件1,value1,匹配条件2,value2…….default)
--等同
--if(匹配条件1) value1 else if(匹配条件2) value2 …….esle
--查询全部雇员的职位:要求显示CLERK : 业务员
SELECT ename,decode(job,'clerk','业务员','saleman','销售人员','manager','经理','analyst','分析员','president','总裁') from emp ;
分组统计
Oracle中的组函数与分组统计练习
组函数
--COUNT():统计记录的条数 如果没有记录,返回 0;
--(1)统计EMP表一共有多少条记录
SELECT count(EMPNO) FROM emp;
SELECT COUNT(*) FROM emp;
--(2)统计EMP中一共有多少种工作
SELECT COUNT(DISTINCT JOB) FROM emp;
--2.MAX()计算最大值
--查出EMP表中最高工资。
SELECT MAX(SAL) FROM emp;
--3.MIN()计算最小值;
-- 查出EMP表中的最低工资
select MIN(SAL), MAX(SAL) FROM emp;
--4. SUM()计算总和
--.查询EMP表中的员工工资总和。
SELECT SUM(SAL) FROM emp;
--5. AVG()计算平均值
--查询员工平均工资。
SELECT AVG(SAL) FROM emp;
SELECT AVG(nvl(COMM, 0)) FROM emp;
--按照设呢字段分组就只能查询什么字段,组函数不可以和其他字段混合使用;
--一般都是按照一个字段分组,如果按照多个字段分组那么必须多个字段都一一样才算一个组;
分组统计
--1.查询出各部门的员工数目。
SELECT DEPTNO, COUNT(*) FROM emp GROUP BY deptno;
--2.出各部门平均工资。
SELECT DEPTNO,AVG(SAL) FROM emp GROUP BY deptno;
--3.按照职位分组,求出每个职位的最高和最低工资。
SELECT JOB,MAX(SAL), MIN(SAL) FROM emp GROUP BY job;
--4.按部门名称分组,显示部门名称,以及每个部门的员工数。
--5.查询出每个部门的名称,部门的人数,平均工资。
SELECT DEPTNO,COUNT(EMPNO),AVG(sal) FROM emp group by deptno;
--6.统计平均工资最高的工资。
SELECT MAX(AVG(SAL)) FROM emp GROUP BY job;
--7.查出平均工资大于2000的部门编号与平均工资。
SELECT deptno, AVG(sal) FROM emp group by deptno having AVG(sal) >2000;
--8.显示非销售人员工作名称以及从事同一工作的雇员的月工资总和,并且满足从事同一工作的员工的月工资合计大于5000,输出的结果按月工资总和排序。
SELECT job, sum(sal) FROM emp WHERE job<>'salesman' group by job having SUM(sal)>5000 order by sum(sal) DESC;
--9. 查询出平均工资最高的部门。
SELECT MAX(AVG(sal)) FROM emp group by deptno;
链接查询:
--查询员工的姓名和员工部门名称,部门编号
select * FROM emp;
SELECT * FROM dept;
--如果查询的结果在在多各表中,那么就要使用表连接查询;
--如果出现了多张表,就要表明字段所属的表;
--笛卡尔积 自然组合,没有表关系, 两个表的记录----组合;
SELECT EMP.ENAME, EMP.DEPTNO,DEPT.DNAME FROM emp, dept;
--表连接必须要表明表之间的关系;(外键-- 表中的字段来自于其他表中的字段)
--内连接,又叫等值连接,
SELECT emp.*,dept.dname from emp , dept where emp.deptno = dept.deptno;
--再加一个条件,deptno = 10;
SELECT emp.*,dept.dname from emp , dept where emp.deptno = dept.deptno and dept.deptno = 10;