数据库基础

数据库概论

数据库做什么

1. 存储大量数据，方便检索和访问；
2. 保持数据信息的一致，完整；
3. 共享和安全；
4. 组合分析，产生有用的信息；

基本概念

1. 数据（Date）:

2. 数据库（Datebase）:

3. 数据库管理系统（DBMS）:

4. 数据库系统（DBS）:

5. 数据库管理员（DBA）：

6. 关系数据库管理系统（RDBMS）:

7. SQL语言（Structured Query Language）:

层次数据库

网状数据库

---

关系数据库

基本概念

1. 实体(entity)：

2. 属性(attribute)：

3. 码（key）：

4. 域（）：

   域里面存放的数据是独立的，且不可划分；

5. 联系：

   1. 1 : 1 的联系：一个人有一个身份证；
   2. 1 ： n 联系：一个班级里有N多个学生；
   3. m : n 联系：学生和课程（老师）的关系；

关系数据库的数据结构

     数据的逻辑是一张二维表，有行和列；

1. 主键：
2. 外键：

基本查询操作：

1. 简单的数据查询语句：

   语法： SELECT *列名1，[列名2，列名3，….] FROM 表名

2. 限定查询（条件查询）

3. 范围查询

   Eg2.查询81年入职的所有员工信息。

   SELECT * FROM emp WHERE hiredate BETWEEN ‘1-1月-81’ AND ‘31-12月-81’
   Oracle中表示日期格式时需要使用单引号括起来。

4. 判断是否为空 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”虚拟表供测试。

1. 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;