一、DBMS和DB
1、DBMS是什么？
数据库管理系统 是管理数据库的软件
2、DB是什么？
真正存储数据的文件
3、RDBMS和RDB
关系型数据库管理系统和关系型数据库
基于二维表的数据库就是关系型数据库。
4、二维表中的基本概念
表头
行
列
字段名
字段值
5、关系型数据库的代表
oracle
SQL
SYBASE
DB2
二、操作数据库的语言SQL（结构化查询语言）
1、查询(2天)
select * from tablename;
2、数据定义的语句 DDL
create，alter，drop
3、数据操作语句（增删改查） DML
insert，delete，update
4、事务控制语句
commit，rollback,savepoint
三、如何使用服务器上的数据库
1、远程登录服务器
telnet
2、用户名和密码
3、输入指令sqlplus
4、输入用户名和密码
5、自动进入sql编程环境
SQL>
清屏!clear
退出exit
四、使用sql的编程环境，查看数据库中的表结构
SQL>desc 表名; --查看表结构
desc s_emp; --查看表结构
（1）查看表结构能够得到的信息？
表头中每个字段的名字
（2）字段是否必须有值 如果必须有值，则显示成not null
（3）可以得到字段名的类型
number 数字类型
varchar2 字符串类型
date 日期类型
表结构名字对应
ID 员工编号
LAST_NAME 姓
FIRST_NAME 名
USERID 用户编号
START_DATE 入职时间
COMMENTS 备注
MANAGER_ID 领导的员工编号
TITLE 职位
SALARY 月薪
COMMISSION_PCT 提成
查询每个人的月薪
select salary from s_emp;
--------------------------------------------------------------------
查询语句 select语句
一、from 子句
1、 如何从表中查询一个字段的值
1.1 语法
select 字段名 from 表名;
1.2 举例:查询s_emp表中的所有的id。
select id from s_emp;
2、 如何从表中查询多个字段的值
2.1 语法
select 字段名1,字段名2 from 表名;
2.2 举例：把s_emp表中的id first_name salary显示出来
select id,first_name,salary from s_emp;
3、 如何查询所有的字段
3.1 语法
select 所有的字段名 from 表名;
*号可以代表所有的字段名
select * from 表名;//需要查询所有的资源，效率稍微要降低
4、 sql中的数学运算 + - * /
4.1 把每个员工的工资和工资加100之后显示出来
select id,salary,salary+100 from s_emp;
4.2 把每个员工的月薪和年薪显示出来
select id,salary,salary*13 as annualsalary from s_emp;
5、 字段或者表达式的别名
5.1 语法就是在字段 或者表达式后面另外起一个名字
5.2 显示月薪和年薪的别名
select id,salary monsal,salary*13 as annualsalary from s_emp;
5.3 别名默认处理成大写
别名中有空格字符或者原样显示
select salary monsal,salary*13 year sal from s_emp;//error
5.4 使用双引号 可以把多个对象看成一个整体可以使别名原样显示
select salary "monsal",salary*13 "Year sal" from s_emp;
6、sql中如何表达字符串
6.1 语法
使用单引号 引起一串字符
6.2 举例
'' ' ' 'a' 'hello world'
数据库中无字符概念，以上都是字符串
6.3 oracle中如何拼接字符串
6.3.1 使用字符串拼接符号 ||
6.3.2 举例
select first_name||last_name from s_emp;
6.3.3 在first_name和last_name之间拼接一个下划线字符_
select first_name || '_' || last_name from s_emp;
--select first_name||last_name as first_name_last_name from s_emp;
6.3.4 思考如何在first_name和last_name之间拼接一个单引号
select first_name||''''||last_name from s_emp;
6.3.5 在first_name和last_name之间拼接两个单引号
select first_name ||''''''|| last_name from s_emp;
select first_name||''''||''''||last_name from s_emp;
7. NULL值的处理
7.1 查询s_emp表中的所有的id，salary,以及对应的提成commission_pct
select id,salary,commission_pct from s_emp;
显示的结果中commission_pct有好多NULL值
7.2 按照这种方式计算年薪 salary*12+salary*12*commission_pct/100
select salary*12+(salary*12*commission_pct/100) from s_emp;
任何值和NULL值做运算的结果都是NULL.
7.3 ORACLE中如何处理NULL值。
nvl(par1,par2) par1，par2类型必须一致。 NULL空值处理函数
par1如果为NULL，则返回par2的值;如果par1则返回par1本身
NULL要 尽早做处理。
select salary*12+nvl((salary*12*commission_pct/100),0) from s_emp;
7.4 要求显示s_emp表中的id first_name manager_id
如果manager_id为NULL,则显示成-1
select id,first_name,NVL(manager_id,-1) from s_emp;
8. 数据的排重显示
8.1 distinct关键字
select salary from s_emp;
select distinct salary from s_emp;
8.2 联合排重
id salary
1 800
2 800
2 800
显示id salary 如果id，salary的值都相同就只显示一次
select distinct id,salary from s_emp;
select title,salary from s_emp;
select distinct titile,salary from s_emp;
二、where子句（条件语句）
1. 作用：
限制表中的行数据的返回
符合where条件的数据就返回，不符合where条件的就被过滤掉
2. 举例：两个极端的条件
select salary from s_emp;
select salary from s_emp where 1=1;
select salary from s_emp where 0=1;
select salary from s_emp where 1!=1;
3. number类型数据的条件判断
3.1 把s_emp表中的id salary显示出来,要求显示salary大于1200
select id,salary from s_emp where salary>1200;
select id,salary from s_emp where salary=1200;
4. 字符串类型的条件判断
4.1 找出first_name是Mark的id first_name salary显示出来的
select id,first_name,salary from s_emp where first_name = 'Mark';
sql语句不区分大小写，而字符串的值是区分大小写的
字符串的值要加单引号
select id,first_name,salary from s_emp where first_name like 'mark';
5. 找出工资在[1200，1400]范围内的员工的id first_name salary
select id,first_name,salary from s_emp where sarlary between 1200 and 1400;
闭区间[] 开区间()
where 字段名 between a and b
6. sql提供的其他运算符
6.1 where 字段名 between a and b 表达一个闭区间
6.2 where 字段名 in (列表)
列表 值1，值2，值3...
where id in (1,3,7);
in查找的过程：
id = 1的话，执行一次就可以
id = 3的话，执行先判断3!=1,然后判断id=3;
id = 7的话，执行3次
id = 9的话，执行3此后悲催了
查询s_emp表中部门编号在31或者32或者50部门的员工的id first_name以及部门编号
select id,first_name,dept_id from s_emp where dept_id in (31,32,50);
注意：数据出现的概率高的放到前面，这样的话效率高
6.3 模糊查询 （通配符）
6.3.1找出所有姓王的人
王石
王安石
王宝强
找出所有数据中的带龙的
李小龙
龙丫头
小龙女
6.3.2通配符
% 代表0-n个任意字符
- 代表一个任意字符
'王%' '%龙%' 通配串
6.3.3 模糊查询关键字
where 字段 like '通配串';
找出s_emp表中first_name 所有带a的
select first_name from s_emp where first_name like '%a%';
找出s_emp中first_name所有倒数第二个字符是a的。
select first_name from s_emp where first_name like '%a_';
6.3.4 特殊的模糊查询
字符串中带%或者_的模糊查询
s_emp
s_dept
找出所有的S开头的表名
desc user_tables;
select table_name from user_tables where table_name like 'S%';
找出所有的S_开头的表名
select table_name from user_tables where table_name like 'S\_%' escape '\';
6.4 NULL值的判断
6.4.1 使用where字段 is null
找出提成等于10的人的id,first_name和commission_pct;
select id,first_name,commission_pct from s_emp where commission_pct =10;
找出提成不等于10的人的id,first_name,commission_pct
select id,first_name,commission_pct from s_emp where commission_pct!=10;
6.4.2 判断NULL使用is null
select id,frist_name,commission_pct from s_emp where commission_pct is null;
6.4.3 找出manager_id是NULL的员工，列出id salary manager_id
select id,salary,manager_id from s_emp where manager_id is null;
7. 逻辑条件链接符号
and链接的条件都为真 则条件为真呢
or 只要有一个条件为真，则条件为真
not
7.1 求工资在[800,1200]的员工，列出id first_name salary
select id,first_name,salary from s_emp where salary>=800 and salary <=1200;
7.2 求部门号在31或者32或者50中的员工的id first_name dept_id
select id,first_name,salary from s_emp where deptid = 31 or deptid =32 or deptid = 50;
7.3 not
= != <> ^=
< >=
> <=
between a and b not between a and b;
in not in (注意NULL值)
like not like
is null is not null;
找出提成不是NULL的员工的id first_name commission_pct
select id,first_name,commission_pct from s_emp where commission_pct is not null;
三、数据排序
1. 概念
把数据 按照升序或者降序 排序显示
2. order by 排序标准 排序方式
2.1 排序方式
升序 默认的顺序 asc 自然顺序 字典顺序
降序 desc
2.2 出现的位置
永远出现在SQL语句的最后
3. 举例
3.1查询s_emp表中的id,first_name,salary
按照工资排序显示
select id,first_name,salary from s_emp order by salary;
3.2查询s_emp表中的id,first_name,salary
按照工资降序显示
select id,first_name,salary from s_emp order by salary desc;
4. NULL值在排序中的处理
按照manager_id排序显示 id,first_name,manager_id
select id,first_name,manager_id from s_emp order by manager_id;
结论：NULL在排序中做最大值处理。
5. 多字段排序
5.1概念
当第一个排序的值相同时，可以启用第二排序字段来对数据进行排序
5.2举例
按照工资排序 如果工资相同 则按照id降序排列
select id,first_name,salary from s_emp order by salary;
select id,first_name,salary from s_emp order by salary,id desc;
注意：只能有一个order by
6. 总结
order by 排序标准 排序方式，第二排序标准 排序方式;
四、单行函数
1. 单行函数 和 组函数的概念
单行函数 ： 对SQL语句的每一行数据都返回一个处理结果，sql语句影响多少行就
返回多少个结果。
组函数 ：对SQL语句影响的所有数据统一处理返回一个结果，无论sql语句影响多少行
都返回一个结果。
2. 举例
2.1 单行函数 upper
select first_name,upper(first_name) from s_emp;
2.2 组 函数 count
select count(first_name) from s_emp;
3. 单行函数的测试
dual 表 是一张单行单列的表。
select upper("hello world") from dual;
4. 常见的处理字符串的函数 -----重点内容
4.1 upper(par1) 变大写
4.2 lower(par1) 变小写
select lower("Hello world") from dual;
4.3 initcap(par1) 把每个单词的首字母变大写
select initcap("hello world") from dual;
4.4 length(par1) 求字符串的长度
select length('hello') from dual;
4.5 concat(par1,par2)连接字符串
用的很少，因为有||
select concat(concat('hello','world'),'hello') from dual;
select concat('hello','world') from dual;
4.6 substr(par1,par2,par3)
4.6.1参数解释
par1要处理的字符串
par2从什么位置开始截取，默认从1开始编号
也可以是负数-1代表最后一个字符的位置
par3 截取多少个字符
4.6.2举例
把s_emp表中的first_name和first_name前三个字符截取下来
select first_name,substr(first_name,1,3) from s_emp;
把s_emp表中的first_name和first_name后三个字符截取下来
select first_name,substr(first_name,-3,3) from s_emp;
4.7 nvl(par1,par2) NULL值处理函数
par1和par2类型要一致
5. 数字处理函数
5.1 round(par1,par2) 四舍五入
5.1.1 参数
par1要处理的数据
par2指定小数保留的位数，par2可以省略，代表取整。
5.1.2 举例
select round(12.88) from dual; --13
select round(12.88,1) from dual; --12.9
select round(12.874,2) from dual; --12.87
/*对小数点前 第几位进行四舍五入*/
select round(12.874,-1) from dual; --10
select round(12.874,-2) from dual; --0
5.2 trunc(par1,par2)截取
5.2.1 参数
par1要处理的数据
par2指定小数保留的位数，par2可以省略，代表取整。
5.2.2 举例
select trunc(12.88) from dual; --12
select trunc(12.88,1) from dual; --12.8
select trunc(12.874,2) from dual; --12.87
/*对小数点前 第几位进行四舍五入*/
select trunc(12.874,-1) from dual; --10
select round(12.874,-2) from dual; --0
6. 数字格式显示函数
6.1 to_char(par1,par2)
par1是要处理的数据
par2是格式 可以省略 代表把一个数据类型
变成字符串类型
格式以 fm开头
9 小数点前代表0-9的任意数字
小数点后代表1-9的任意数字
999.99 999.00
123 123. 123.00
0 小数点代表 强制显示前导零
12345.67 012,345.67
小数点后代表0-9的任意数字
$ 美元符号
L 本地货币符号
6.2 举例
6.2.1以fm$099,999.99显示s_emp中的数据
select salary,to_char(salary,'fm$099,999.99') from s_emp;
6.2.2以fmL099,999.99显示s_emp中的数据
select salary,to_char(salary,'fmL099,999.99') from s_emp;
6.3 如何修改本地语言
1 进入服务器的shell
bash
2修改配置文件
vi .bash_profile
3写入环境变量
export NLS_LANG = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
变成英文：
'AMERICAN_AMER.ZHS16GBK'
4保存退出 让配置生效
source .bash_profile
5重新进入sqlplus
7. 函数嵌套
7.1 概念
把一个函数的返回值作为另外一个函数的参数
7.2 例子
把s_emp 表中 first_name 后三个字符列出来
select first_name,substr(first_name,-3,3) from s_emp;
length 结合substr完成
select first_name,substr(first_name,length(first_name)-2,3) from s_emp;
显示s_emp 表中 id,first_name,manager_id(manager_id 是NULL的话显示成BOSS)
select id,first_name,nvl(to_char(manager_id),'BOSS') from s_emp;
8. 表连接：
natural joins:
natural join
using
on
Outer joins:
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS joins
------
select table1.column,table2.column
from table1
[NATURAL JOIN table2] |
[JOIN table2 USING column_name] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];
使用oracle synatx的连接表
select table1.column,table2.column
from table1,table2
where table1.column1 = table2.column2;
有相同列名时，在前面加上表名
如果表名很长的话，可以用 别名 AS
表别名给一个比较短的表名，可以保持SQL代码比较小，使用较少的内存。
使用列别名可以区分相同的列名。
select employee_id,last_name,e.department_id,departement_name
from employee e,departments d
where e.department_id = d.department_id;
创建NATURAL JOIN
（1）NATURAL JOIN 2个表有相同的名字和类型
（2）从两个表中选择有匹配的相等值的列的行
（3）如果没有相同的名字和类型，则报错
desc departments
DEPARTMENT_ID
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
desc locations
LOCATION_IDSTREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID
标准写法：
select department_id,department_name,location_id,city
from departments
NATURAL JOIN locations.
Oracle用法：
select d.department_id,d.department_name,d.location_id,l.city
from departments d,location l
where d.location_id = l.location_id
如果有几个相同的名字，但数据类型不匹配，natural join可以使用using条件来指定要使用的列。
使用using来匹配列。
desc employees
manager_id
department_id
employee
employee_id
location_id
desc departments
manager_id
department_id
标准写法：
select employee_id,last_name,location_id,department_id
from employees JOIN departments
USING (department_id);
oracle写法：
select d.department_id,d.department_name,d.location_id,l.city
from departments d,locations l
where d.location_id = l.location_id;
查询三个表
标准写法：
select employee_id,city,department_name
from employee e
join departments d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;
oracle写法：
select employee_id,city,department_name
from employee e,departments d, locations l
where e.department_id = d.department_id and d.location_id = l.location_id;
自连接
select worker.last_name emp,manager.last_name mgr
from employees worker join employees manager
on (worker.manager_id = manager.employee_id);
非等值连接
select e.last_name,e.salary,j.grade_level
from employee e join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;
以上的例子基本上都是返回的例子都是匹配的结果。
返回的记录也包含不直接匹配的记录（用外连接）
连接的两个表只返回匹配的行叫内连接inner join
连接的两个表返回内连接的结果以及左边表的不匹配的行的记录或者右边表的不匹配的行的记录（外连接：左连接、右连接）
连接的两个表返回内连接的结果以及左右表的所有不匹配行的记录（完全外连接）
inner join ----- 106
select e.last_name,e.department_id,d.department_name
from employees e inner join departments d
on (e.department_id = d.department_id);
select count(*) from employees; --107
left outer join ----107
select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id = d.department_id);
right outer join 122
select e.last_name,e.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id = d.department_id);
full outer join 123
select e.last_name,e.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id = d.department_id);
oracle写法：
哪边加（+）就把对立面的不匹配的记录显示出来。
select table1.column,table2.column
from table1,table2
where table1.column(+) = table2.column
select table1.column,table2.column
from table1,table2
where table1.column = table2.column(+)
两边都加这个就不行了X
select table1.column,table2.column
from table1,table2
where table1.column(+) = table2.column(+)
笛卡尔乘积cartesian products.
表1 m条记录 表2 n条记录 --》m*n条记录
什么时候会形成笛卡尔乘积？
（1）连接条件不正确
（2）连接条件被省略
（3）表1的所有行连接到表2的所有行
如何避免笛卡尔乘积？ 总是包含正确join条件
什么场景下应用笛卡尔乘积？可以产生大量的垃圾测试数据的时候。比如测试场景
select last_name,department_name
from employees
cross join departments;
8.1列出每个员工的 id first_name dept_id
select id,first_name,dept_id from s_emp;
8.2查询的数据来自于两张表
s_dept (部门表)
SQL> desc s_dept;
Name
-------------------
ID 部门编号
NAME 部门名
REGION_ID 地区编号
s_emp (员工表)
id 员工编号
first_name 名
dept_id 部门编号
select s_emp.id,first_name,dept_id,name from s_emp,s_dept; (笛卡尔积)
25*12=300
select s_emp.id,first_name,dept_id,name
from s_emp,s_dept
where dept_id=s_dept.id;
3. SQL> desc s_dept;
Name
-------------------
ID 部门编号
NAME 部门名
REGION_ID 地区编号
SQL> desc s_region
ID 地区编号
NAME 地区名
把每个部门的名字 和 对应的地区名列出来
select s_dept.id,s_dept.name,s_region.name
from s_dept,s_region
where s_dept.region_id=s_region.id;
对叫name的这一列 一行最多显示15个字符
col name for a15
4.表的别名 一旦给表起了别名 在这条sql中表原来的名字失效
select d.id,d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id;
5.
连接两张表的条件的符号 都是等号 所以这种连接 称之为等值连接。
连接两张表的条件的符号不是 等号 则这种连接 称之为非等值连接。
salgrade 工资级别表
SQL> desc salgrade;
Name
-------------------
GRADE 工资级别
LOSAL 这个级别对应的低工资
HISAL 这个级别对应的最高工资
s_emp 表中first_name salary每个员工的first_name salary 以及工资对应的工资级别?
select first_name,salary,grade
from s_emp e,salgrade s
where e.salary between losal and hisal;
select first_name,salary,grade
from s_emp e,salgrade s
where salary>=losal and salary<=hisal;
6.自连接
领导是员工
不是领导的员工 也是员工
找出所有的领导?
id manager_id
101 101
102 101
103 101
一个员工的manager_id 等于你的id 你就是领导
select id,first_name,manager_id
from s_emp
where id=manager_id;
------------------------------------------------
7.内连接
连接:
等值连接
非等值连接
自连接
这三种连接都有一个共同的特点：
符合连接条件就选中数据，不符合条件就过滤掉数据,内连接。
找出所有的普通员工?
select distinct m.id,m.first_name
from s_emp e,s_emp m
where m.id!=e.manager_id;
8.外连接:外连接的结果集 等于 内连接的结果集 加上
匹配不上的记录。
一个也不能少
(+) (+)字段对面表的数据全部被匹配出来
select distinct m.id,m.first_name
from s_emp e,s_emp m
where m.id=e.manager_id(+);
/*在上面结果集基础上 过滤得到需要的数据*/
select distinct m.id,m.first_name
from s_emp e,s_emp m
where m.id=e.manager_id(+)
and e.manager_id is null;
/*找出所有的领导*/
select distinct m.id,m.first_name
from s_emp e,s_emp m
where m.id=e.manager_id(+)
and e.manager_id is not null;
9.列出所有的部门名 和 部门名对应的地区名
s_dept 部门表
id
name
region_id
s_region 地区表
id
name
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id;
新增加了一个没有地区的部门
insert into s_dept values(111,
'testdept',NULL);
commit;
/* 显示所有的部门名 和对应的地区名 */
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id(+);
/* 把没有地区编号的部门显示出来 */
select d.name,r.name
from s_dept d,s_region r
where d.region_id=r.id(+)
and r.id is null;
10.列出每个员工的first_name salary 和 工资
对应的工资级别
salgrade
grade
losal
hisal
s_emp
first_name
salary
select id,first_name,salary,grade
from s_emp,salgrade
where salary between losal and hisal
order by id;
给老板涨工资
update s_emp set salary=12500 where id=1;
commit;
select id,first_name,salary,grade
from s_emp,salgrade
where salary between
losal(+) and hisal(+)
order by id;
-------
内连接：
在表连接时，符合连接条件的数据，就被选中，不符合连接条件的就被过滤掉。
等值连接：
非等值连接：
自连接：
外连接：
外连接的结果集等于内连接的结果集+匹配不上的记录。
(+) (+)字段对面表的数据全部被匹配出来。
等值连接：
非等值连接：
自连接：
一、sql99红的内外连接
1. sql 99中的内连接
from a表 join b表 on 连接条件;
from a表 inner join b表 on 连接条件;
2. 把每个部门名和对应的地区名显示出来
s_dept
s_region
select d.name,r.name
from s_dept d,s_region r
where d.region_id = r.id;
select d.name,r.name
from s_dept d join s_region r
on d.region_id =r.id;
3. sql 99中的外连接
3.1 把每个部门名和对应的地区名显示出来
即使没有对应的地区名 也要显示出这个部门。
select d.name,r.name
from s_dept d,s_region r
where d.region_id = r.id(+);
3.2 语法
from a 表 left outer join b 表 on连接条件。
from b 表 right outer join b 表 on连接条件。
要把哪张表的数据全部匹配出来 就让哪张表发起连接。
select d.name,r.name
from s_dept d left outer join s_region r
on d.region_id = r.region_id;
select d.name,r.name
from s_region r right outer join s_dept d
on d.region_id = r.id;
3.3 谁是普通员工？
(+)实现之后 再使用left outer join实现。
select distinct m.id,m.first_name
from s_emp e,s_emp m
where e.manager_id(+)=m.id ---连接条件
and e.manager_id is null; ----过滤条件
select distinct m.id,m.first_name
from s_emp m left outer join s_emp e
on e.manager_id=m.id ---连接条件
where e.manager_id is null; ----过滤条件
3.4全外连接
全外连接的结果集=左外连接的结果+右外连接的结果集-减去重复的记录。
a 表 full outer join b表 on 连接条件 ；
oracle中不是通过两端都加(+)
union 合并两个结果集 排重
union all 合并两个结果集
select id from s_emp union
select id from s_emp;
select id from s_emp union all
select id from s_emp;
二、组函数
1. 特点就是对一组数据处理之后，得到一个结果
2. 常见的组函数有哪些：
count(*) 统计数据个数
max 统计最大值
min 统计最小值
avg 统计平均值
sum 统计和
3. 统计s_emp表中的人数 统计工资的最大值 和工资的最小值
select count(id),max(salary),min(salary) from s_emp;
4. 统计s_emp中的工资的和是多少？
select avg(salary),sum(salary),sum(distinct salary) from s_emp;
组函数中可以使用distinct
5. 组函数对NULL值的处理是忽略的
统计s_emp表中的提成的个数 和 提成commision_pct的和
select count(commission_pct),sum(commission_pct) from s_emp;
三、分组
1. 概念
按照一定的标准把数据分成若干组
2. group by 分组标准（用在where后）
select dept_id
from s_emp
group by dept_id;
3. 按照部门号分组，统计每个部门的人数，能不能把部门名显示出来?
select dept_id,count(s_emp.id),max(name)
from s_emp,s_dept
where dept_id =s_dept.id
group by dept_id;
在分组语句中，select后的字段，要么是分组标准，要么是经过合适的组函数处理过的。
4. 对组数据的过滤
按照部门号分组，统计每个部门的平均工资
要求显示工资大于1500
select dept_id,avg(s_emp.salary)
from s_emp
group by dept_id
having avg(salary)>1500;
执行顺序：from》where》group by》having》select》order by
组数据的过滤用having而不是where来过滤。
5. 按照部门号分组，求每个部门的人数
要求显示人数大于1的部门
要求按照部门号排序
要求显示部门名
select dept_id,count(s_emp.id),max(d.name)
from s_emp e,s_dept d
where e.dept_id = d.id;
group by dept_id
having count(e.id)>1
order by dept_id;
四、子查询
4.1 概念
把一个查询的结果 作为另一个查询的基础
4.2 举例
/*找出所有的领导的id*/
select distinct manager_id from s_emp;
/*让员工的id出现在领导的id中*/
select id,first_name from s_emp
where id in (select distinct manager_id from s_emp);
/*使用子查询找出所有的普通员工*/注意NULL值 （空值的运算特点:is null,而非用=来判断;）
select id,first_name from s_emp
where id not in (select distinct manager_id from s_emp where manager_id is not null);
注意：一旦使用not in就要注意NULL值。
4.3 子查询还可以用在having之后
求平均工资大于41部门的平均工资的
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from e_emp where dept_id = 41);
4.4 子查询用在from后面
任何一条sql语句都对应内存中的一张表
select id,first_name name,salary from s_emp;
相当于查询一张内存表
select id,name,from(select id,first_name name,salary from s_emp)
where id>10;
求平均工资大于41部门的平均工资的
select * from (select dept_id,avg(salary) asal from s_emp group by dept_id)
where asal>(select avg(salary) from s_emp where dept_id=41);
五、表的操作
5.1如何建立表
create table 表名 (
字段名 类型,
字段名 类型，
字段名 类型
);
5.2 建立一张员工表 myemp
id number
name varchar2(30)
salary number
create table myemp(
id number,
name varchar2(30),
salary number
);
5.3 删除表
drop table 表名;
drop table myemp;
5.4 建立一张订单表 myorder123
id number
name varchar2(50)
money number
odate date
create table myorder123(
id number,
name varchar2(50),
money number,
odate date
);
六、对表中的数据的操作
6.1. 增加数据 insert
6.1.1 insert into 表名 values(字段值1，字段值2，字段值3);
insert into myorder123 values(1,"test1",1888.88,);
select * from myorder123;
6.1.2 insert into 表名 (字段名1，字段名3) values(字段值1，字段值3);
选择部分字段插入数据，必须包含所有的非空
没有选择的字段当做NULL值插入数据。
insert into myorder123(id) values (2);
6.1.3 向s_dept表放入数据
insert into s_dept values(100,'test',NULL);
6.2. 删除数据
delete from 表名 where 条件;
delete from myorder123 where id=1001;---千万不要忘了where条件
6.3 修改数据
update 表名 set 字段名=值,字段名2=值2 where 条件;
update myorder123 set id = 9527,name ='zhouxingxing' where id =1;
commit;----提交
七、数据库中的事务操作
原子性：一个事务中的sql语句，是不可分割的整体。要么一起成功，要么一起失败。
转账对应的操作：
A卡减
B卡加
update account set money = money - 20000 where ano='A';
//把上句的执行状态保存为a
update account set money = money + 20000 where ano='B';
//把上句的执行状态保存为b
if (a&&b)
{
commit;--确认本次操作
}else{
rollback; --撤销本次操作
}
隔离性：一个事务中的操作 在没有提交以前 数据的变化 不能被另一个事务发现数据的变化。
update s_emp set salary = salary+2000 where id = 1;
只对增删改insert delete update 有限制,是有事务操作的。
select没有事务可言
create drop 事务自动提交。
持久性：
一致性：平衡性
事务的补充：---事务也叫交易
事务中的语句一起成功 一起失败太严格
部分成功 部分失败
update account,set money=money-0.1 where ano='158100****';
savepoint a;
insert into logtable values();
发送短信
savepoint b;
update logtables
savepoint c;
if (a&& b&& c){
commit;
}else{
rollback to a;
commit;
}
八、日期类型的操作
8.1 日期的默认格式
'dd-MON-yy'
dd代表天
MON代表英文的三位的缩写
'26-DEC-10'
8.2 四位年
按照入职日期排序 显示 id first_name
start_date;
select id,first_name,start_date from s_emp;
to_char(日期数据,‘日期格式’)
yyyy 四位年
mm 两位月
dd 两位天
hh 12小时制
hh24 24小时制
mi 分钟
ss 秒
MON 月的英文缩写
month 月的英文全写
pm 下午
am 上午
day 星期几
select id,first_name,to_char(start_date,'yyyy-mm-dd') from s_emp order by start_date;
8.3 如何插入日期
建立一张订单表
create table myorder(
id varchar2(50),
name varchar2(50),
money number,
odate date
);
insert into myorder values('jn001','jnyyh',20000,'08-DEC-10');
select to_char(odate,'yyyy-mm-dd hh24:mi:ss') from myorder;
默认无法放入时分秒信息 默认是:00:00:00
8.4 to_date(par1,par2)
par1是要处理的日期字符串，par2是日期字符串的格式
'2008-08-08 20:08:08' par1
'yyyy-mm-dd hh24:mi:ss' par2
par1和par2要匹配。
insert into myorder values('bj001','bjolp0002',290000,to_date('2008-08-08 20:08:08','yyyy-mm-dd hh24:mi:ss'));
8.5 代表系统时间的函数
sysdate
select to_char(sysdate,'yyyy-md-dd hh24:mi:ss') from dual;
insert into myorder values('bj001','bjtest',18888,sysdate);
select to_char(odate,'yyyy-md-dd hh24:mi:ss') from myorder;
8.6 日期的调整
8.6.1 按照天进行调整
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')，to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') from dual; --- +1代表1天
8.6.2 按照小时为单位进行调整
select to_char(sysdate,'yyyy-md-dd hh24:mi:ss'),to_char(sysdate+1/24,'yyyy-mm-dd hh24:mi:ss') from dual;
8.6.3 按照分钟为单位进行调整
select to_char(sysdate,'yyyy-md-dd hh24:mi:ss'),to_char(sysdate+31/1440,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-md-dd hh24:mi:ss'),to_char(sysdate+1/(1440*60),'yyyy-mm-dd hh24:mi:ss') from dual;
8.6.4 按照月为单位进行调整
add_months(par1,par2)
par1 要调整的日期
par2 要调整几个月，正数向后调，负数向前调
select add_months(sysdate,3) from dual;
8.6.5 得到一个日期对应月的最后一天
last_day(par1)
select to_char(last_day(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;
8.6.6 下一个星期几(最近的)
next_day(par1,par2)
par1要处理的日期
par2星期几
select next_day(sysdate,'monday') from dual;
select next_day(sysdate,'friday') from dual;
select next_day(next_day(sysdate,'friday'),'friday') from dual;
8.6.7 日期的其他处理
8.6.7.1 round (par1,par2)
par1要进行四舍五入的日期
par2按照什么单位进行四舍五入 默认是dd
select to_char(round(sysdate),'yyyy-mm-dd') from dual;
select to_char(round(sysdate,'mm'),'yyyy-mm-dd hh24:mi:ss') from dual;
8.6.7.2 trunc(par1,par2)
par1要进行截取的日期
par2按照什么单位进行截取 默认是dd
select to_char(trunc(sysdate),'yyyy-mm-dd') from dual;
select to_char(trunc(sysdate,'mm'),'yyyy-mm-dd hh24:mi:ss') from dual;
------------------------------------------------------------------------
给定一个日期 得到这个日期对应月的最后一天的最后一秒 使用to_char做验证
select to_char(trunc(last_day(sysdate))+(1440*60-1)/(1440*60),'yyyy-mm-dd hh24:mi:ss') from dual;
------------------------------------------------------------------------
r610 p3e meirenwanying
九、数据库中的约束
9.1 概念
对表中的字段 加的一些限制。
9.2 约束的具体种类
9.2.1 主键约束 primary key
字段的值必须非空，并且不能重复
一个表只能有一个主键
9.2.2 唯一键约束 unique
代表值不能重复
9.2.3 非空约束 not null
字段的值不能是NULL 值
9.2.4 检查约束 check
字段的值必须符合检查条件 否则报错。
9.2.5 外键约束 references
foreign key
9.3 约束的实现方式
9.3.1 列级约束
在定义表的某一列时，直接在这一列的后面加约束限制。
9.3.1.1 主键的列级约束
create table testcolumncons(
id number primary key,
name varchar2(50)
);
insert into testcolumncons values(1,'test');
重复提交两次之后：
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0011161) violated
如果不给约束起名字，则系统会自动提供一个约束名。
9.3.1.2给约束起名字
drop table testcolumncons;
create table testcolumncons(
id number constraint testcolumncons_id_pk primary key,
name varchar2(50)
);
insert into testcolumncons values(1,'test');
9.3.1.3约束的练习
使用列级约束完成一张表，要求表有两个字段：
id number要求设置成主键
name varchar2(30)要求设置成唯一键
sname varchar2(30) 要求设置成非空
create table test(
id number constraint test_id_pk primary key,
name varchar2(30) constraint test_name_uk unique,
sname varchar2(30) constraint test_sname_nn not null
);
9.3.1.4 使用列级约束 建立一张表有两个字段id number设置成主键
salary number要求符合检查条件工资大于3500
create table testemp(
id number constraint testemp_id_pk primary key,
salary number constraint testemp_salary_ck check(salary>3500)
);
insert into testemp values(1,3500);
9.3.2 表级约束
在定义完表的所有列之后，再选择某些列加约束限制。
表级约束可以完成联合约束
create table testemp1008(
id number,
name varchar2(30),
salary number,
constraint testemp1008_id_pk primary key(id),
constraint testemp1008_name_uk unique(name),
constraint testemp1008_salary_ck check(salary>3500)
);
在数据库层面上，没有联合非空的需求
9.4 外键约束
9.4.1 概念
涉及两张表
一个表的一个字段的值要参考或者引用另一张表的字段的值。
子表中的外键字段值要么取空值要么是父表字段对应的值。
主表（父表） 从表（子表）
字段唯一性 字段的值引用主表的
9.4.2 实现
create table parent(
id number constraint parent_id_pk primary key,
name varchar2(30)
);
create table child(
id number constraint child_id_pk primary key,
name varchar2(30),
fid number constraint child_fid_fk references parent(id)
);
9.4.3 外键中的一些规则
a.建立表 一般先建立父表 后建立子表。先不考虑主外键就可以先建立子表了。
b.插入数据 一般先插入父表数据后插入子表数据，除非子表的外键值是空值。
insert into child values(1,'test',1);
ORA-02291:integrity constraint错误 。
c.删除数据
先删除子表中关联的数据，再删除父表中的数据。除非使用了级联。
d.删除表？
(1)先删除子表 后删父表
(2)drop table parent cascade constraint;
9.4.4 用外键实现一个关系(谁定义了外键，谁就是子表)
mydept
id number
name varchar2(30)
myemp
id number
name varchar2(30)
dept_id number
一个部门可以有多个员工，一个员工只能属于一个部门。
（一对多关系）
drop table mydept cascade constraint;
drop table myemp cascade constraint;
create table mydept(
id number constraint mydept_id_pk primary key,
name varchar2(30)
);
insert into mydept values(1,'test');
insert into mydept values(2,'look');
commit;
create table myemp(
id number constraint myemp_id_pk primary key,
name varchar2(30),
dept_id number constraint myemp_dept_id_fk references mydept(id)
);
insert into myemp values(1,'x',1);
insert into myemp values(2,'y',1);
insert into myemp values(3,'z',1);
insert into myemp values(4,'m',2);
insert into myemp values(5,'n',3);
commit;
外键是为了让关系的限制更加严格。
9.5 级联
删除一个父表数据的时候，把关联的子表一起删除
on delete cascade 级联删除
on delete set null 级联置空
drop table mydept cascade constraint;
drop table myemp cascade constraint;
create table mydept(
id number constraint mydept_id_pk primary key,
name varchar2(30)
);
insert into mydept values(1,'test');
insert into mydept values(2,'look');
commit;
create table myemp(
id number constraint myemp_id_pk primary key,
name varchar2(30),
dept_id number constraint myemp_dept_id_fk references mydept(id) on delete cascade
);
insert into myemp values(1,'x',1);
insert into myemp values(2,'y',1);
insert into myemp values(3,'z',1);
insert into myemp values(4,'m',2);
insert into myemp values(5,'n',2);
commit;
9.6 把上面的例子改成表级约束的外键约束。
drop table mydept cascade constraint;
drop table myemp cascade constraint;
create table mydept(
id number constraint mydept_id_pk primary key,
name varchar2(30)
);
insert into mydept values(1,'test');
insert into mydept values(2,'look');
commit;
create table myemp(
id number constraint myemp_id_pk primary key,
name varchar2(30),
dept_id number constraint myemp_dept_id_fk foreign key(dept_id) references mydept(id) on delete set null
);
insert into myemp values(1,'x',1);
insert into myemp values(2,'y',1);
insert into myemp values(3,'z',1);
insert into myemp values(4,'m',2);
insert into myemp values(5,'n',2);
commit;
---------------------------------------------------------------
约束的总结：
具体的约束种类：
主键primary key
外键foreign key ，references
唯一键unique
非空not null
检查check
on delete cascade
on delete set null
列级约束：在定义表的某一列时，直接在这一列的后面加约束限制。
表级约束：在定义完表的所有列之后，再选择某些列加约束限制。
外键约束：
1.建立表 先建立父表后建立子表
除非使用修改表结构alter table add constraint
2.插入数据先插入父表数据
除非子表的外键值是空值
3.删除数据
先删除子表中关联的数据，再删除父表数据
除非使用了级联(on delete cascade,on delete set null)
4.删除表
先删除子表，再删除父表
除非使用drop table cascade constraint
----------------------------------------------------------------
十、数据库中的其他对象
10.1 序列
10.1.1作用：
用来产生主键的值
10.1.2如何创建序列
create sequence 序列名;
create sequence myemp1001_id;
10.1.3使用序列
create table myemp1001(
id number constraint myemp1001_id_pk primary key,
name varchar2(30)
);
insert into myemp1001 values(myemp1001_id.nextval,'test');
myemp1001_id.currval 当前值
10.1.4删除序列
drop sequence 序列名;
drop sequence myemp1001_id;
JESSE备注：
10.2 索引
10.2.1 作用：加速查询，但会使插入速度降低
10.2.2 如何实现
底层通过树状结构组织数据，配合消耗空间和消耗时间来达到加速查询的目的。
挂接的时候消耗时间，挂接完成后
普通3亿条数据 大约需要8分钟
加索引 消耗的空间几十个G，建立索引用了40分钟，实际查询只用了0.01秒。
10.2.3 如何创建索引（一般情况下DBA负责）
数据库管理系统会自动为唯一性字段建立索引，我们称之为唯一性索引。
人手工创建的非唯一性索引：
create index 索引名 on 表名(字段);
set timing on //打开计时器
//复制一个表
create table copyemp as select * from emp;
create index copyemp_salary_ind on copyemp(salary);
10.2.4 删除索引
drop index copyemp_salary_ind;
10.3 视图
10.3.1 视图的本质
就是一条sql语句
10.3.2 如何创建一个视图
create or replace view 视图名 as select语句;
create or replace view myview as
select id,first_name from s_emp;
作用可以简化查询
可以对同一份物理数据做不同的表现。
10.3.3 删除视图
drop view myview
10.4 分页技术（oracle中的分页技术）---正常情况下需要3个select。
oracle中的分页技术 rownum
mysql中的分页技术 limit m,n
sqlserver top n
select id,first_name,salary from s_emp;
select rownum,first_name,salary from s_emp;
一页显示10条 要第一页数据
select rownum,first_name,salary from s_emp where rownum<11;
一页显示10条，取得第二页数据
select * from (select rownum r,first_name,salary from s_emp where rownum<21) where r>10;
按照工资排序 每页显示10条 显示第二页的数据
先排序还是先编号？先排序后编号
select * from (select rownum r,fisrt_name,salary from (select first_name,salary from s_emp order by salary)
where rownum < 21) where r>10;
最内层select 完成数据的排序
第二层select 完成数据的编号
第三层select 完成rownum特性的去除
按照工资排序，每页显示10条，显示第三页数据
select * from (select rownum r,fisrt_name,salary from (select first_name,salary from s_emp order by salary)
where rownum < 3*10+1) where r>(3-1)*10;
复习：列出sql中和NULL相关的知识点？
(1)NVL(par1,par2) NULL值处理函数
所有的单行函数：
处理字符串：upper lower initcap length substr concat replace
处理日期：to_char to_date round trunc sysdate last_day next_day add_months
处理数字：round trunc
格式显示的：to_char
(2)如何判断NULL
is null
is not null
= != > < >= <=
between a and b
in
like % _ \ escape
and
or
not
(3)排序
order by 排序标准 排序方式
(4)表连接
外连接
内连接
(+) 对面表的数据全部匹配出来
SQL99
(5)组函数
忽略空值
常见的组函数:count,max,min,avg,sum
(6)分组
group by 分组标准
having分组数据过滤
select后的字段，要么是分组标准，要么是经过合适的组函数处理过的。
(7)子查询
(8)DDL
create table
drop table
alter table
(9)DML
insert
update
delete
TCL :
commit
savepoint
rollback
(10)事务的四大特性：
原子性：一个事务中的sql语句，是不可分割的整体。要么一起成功，要么一起失败。
隔离性：一个事务中的操作
永久性：
(11)约束
五种具体的约束 两种实现方式：
主键、外键、唯一键、非空、检查
(12)view,sequence,index
(13)分页技术（oracle中的分页技术）---正常情况下需要3个select。
258
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
