编程练习生的第21天 （数据库表设计与一些常用语句）

首先接昨天的 语法 99 语法 顾名思义 99 语法就是99年出产的sql语法
–99语法 cross join

select * from dept cross join emp;

–查询员工的信息以及所在部门信息
–自然连接 natural join 自动匹配两张表中主外键关系的字段,或同名字段,做等值连接
select dname,deptno,empno,ename from dept inner natural join emp;

–注意:自然连接同名字段不能出现限定词
join…using(等值连接的字段) 指定使用哪一个字段做等值连接
–表1 join 表2 on 连接条件–>等值|非等值

select * from emp join salgrade on sal between losal and hisal;
select emp.deptno,ename from emp inner join dept on emp.deptno=dept.deptno;

接下来是视图和索引
–试图是建立在 表和结果集之间的一个虚拟表,操作试图不会修改原数据库表中的内容的
–适当的创建试图可以提高执行效率和简化sql
建立方式

--create or replace view 视图名 as select语句 [with read only];
create or replace view vw_emp as select empno,ename,sal from emp where deptno=30 order by sal desc;

删除视图和删除表一样

 --drop view 视图名
 drop view vw_emp;

–索引
–数据库的对象之一,对象本身是需要维护的
–索引本身是透明的,是否创建对于数据库表的本身,和使用上没有任何影响
–对大量数据,常用于查询,会提高执行效率,执行速度
–对添加索引的字段大量的做增删改,需要更新维护索引对象,降低效率
–相当于字典的目录
–oracle会自动为主键添加索引
索引建立方式

--create index 索引名 on表名 (字段列表...)
--drop index 索引名
create index index_emp_sal on emp(sal);

接下来是表设计
首先设计表要满足三范式要求，然后表内的字段，字段的类型，长度，约束条件，表和表之间的练习。

– 创建表 表名 字段名 字段的类型 约束
–create table 表名(字段名 字段类型,字段名 字段类型…)

添加约束的方式:
–1.创建表的同时字段的后面直接+约束(没有名字,简单,但是提示不直观)
–2.创建表的同时字段的后面直接+约束(指定约束的名字)
–3.创建表结构的最后,非字段的后面,添加约束

–主从表,主外键关系
–创建表:先创建主表,再创建从表
–插入数据,从表中插入数据的时候,外键字段值必须在主表中已存在
–删除表:默认先删除从表,再删除主表

       --drop table 表名 cascade constraints; 删除主表,级联删除约束

–删除数据:1)默认先删除从表中引用了这个主表的这一条数据的那些从表数据,再删除主表的这一条数据
–2)删除主表中这条数据的同时给从表中那些引用了这一条数据的从表数据的外键字段设置为null
–3)删除主表中这条数据的同时给从表中那些引用了这一条数据的从表数据一起删掉
接下来实际设计三个表

create table t_type(
       --非空 唯一 -->主键约束
       tid number(5) constraints pk_sid primary key,
       tname varchar2 (20)
       );
       select * from t_type;
       drop table  p_product;
       

这里就建立了一个商品类型表 ，id = 商品类型编号 tname 是商品编号类型的名称

create table p_product (
       pid number(5) constraints pid primary key, --pid作为主键
       pname varchar2 (20),
       prodate date ,
       efdate varchar2 (20) ,
       price number(5)  ,
       punit varchar2 (20),
       tid number(5) references  t_type(tid) on delete cascade--   tid 商品类型编号 ->关联类型表的id 
       );

商品信息表 p_product pid 商品编号 主键 npame 商品名称 prodate 生产日期
effdate 有效期 price 商品单价 punit 商品单位 tid 商品类型编号
->关联类型表的id

如果类型建立表的时候错误了 该怎么办呢 不要着急 这里有方法

   alter table 表名 modify 字段名 类型（长度）;
   alter table p_product modify punit varchar2 (20);

还有一个表

 create table o_orders(
 oid number(5) constraints oid primary key,
 dealtime date,
 p_num number(5)  ,
 c_name varchar2 (20),
 o_oper varchar2 (20),
 tatol  number(10)  ,
 pid number(5) references  p_product(pid) on delete cascade--   pid 商品类型编号 ->关联类型表的id 
 );

3.订单表 o_orders oid 订单编号 主键 dealtime 成交时间 p_id 商品编号 ->关联商品表的id p_num 商品数量 c_name 客户名称 o_oper 业务员名称 tatol 订单总额

表建立好了 接下来是不是要插入一些数据进去呢

– 一、类型表数据 1 食品类 2 生活用品 3 电器 4 厨房用品

方法 inster into 表名（字段1，字段2…）values(数据1，数据2)
这里要注意，插入的数据类型必须和字段的数据类型保持一致
方法 inster into 表名values(数据1，数据2。。。)
这里要注意，如果表名后面没有跟字段，那么要按照顺序插入表中和字段属性一致的数据

insert into t_type(tid,tname)values (1,'食品类'); 
insert into t_type  values (2,'生活用品');
insert into t_type (tid)values (3);
如果插入的时候忘了插入数值怎么办呢  ，这里用到修改方法
update 表名 set 字段 =‘数值’ where 条件 字段=‘数据’;
update t_type set tname='电器' where tid=3;
insert into t_type values (4,'厨房用品');

这里要注意下 时间类型的数据插入方式要在前面加上 to_date（‘数据’，‘格式’);

insert into o_orders(dealtime) values(to_date(‘20160610’,‘yyyymmdd’));

insert into o_orders(oid,dealtime,p_num,c_name,o_oper,tatol,pid) values (001,to_date('20160610','yyyymmdd'),200,'傻小子工厂','张三',400,1);
insert into o_orders(oid,dealtime,p_num,c_name,o_oper,tatol,pid) values (003,to_date('20160820','yyyymmdd'),60,'海飞丝生产线','王语嫣',840,2);
insert into o_orders(oid,dealtime,p_num,c_name,o_oper,tatol,pid) values (004,to_date('20161201','yyyymmdd'),100,'辣条生产商','段誉',100,3);
insert into o_orders(oid,dealtime,p_num,c_name,o_oper,tatol,pid) values (005,to_date('20160606','yyyymmdd'),100,'苏泊尔总经销商','乔峰',3500,4);
insert into o_orders(oid,dealtime,p_num,c_name,o_oper,tatol,pid) values (006,to_date('20160411','yyyymmdd'),200,'威猛总代理','阿朱',5000,5);

第二个表插入数据

insert into p_product(pid,pname,prodate,efdate,price,punit,tid) values (1,'傻小子',null,'60天',2.5,'包',1);
insert into p_product(pid,pname,prodate,efdate,price,punit,tid) values (2,'海飞丝',null,'730天数',18.8,'瓶',2);
insert into p_product(pid,pname,prodate,efdate,price,punit,tid) values (3,'辣条',null,'30天',1.5,'包',1);
insert into p_product(pid,pname,prodate,efdate,price,punit,tid) values (4,'苏泊尔电饭煲',null,null,389,'台',3);
insert into p_product(pid,pname,prodate,efdate,price,punit,tid) values (5,'威猛先生',null,'730天',36.8,'瓶',4);
     这里会遇到苏泊尔电饭锅插入不进去，因为刚开始设置字段长度是20，但是要插入的数据长度是21，要怎么办呢，这个时候我们只需修改
     一下数据类型的长度就好了
      alter table o_orders modify c_name varchar2 (25);
 update p_product set prodate=to_date('20160215','yyyymmdd') where  pid=1;
 update p_product set prodate=to_date('20160614','yyyymmdd') where  pid=2;
 update p_product set prodate=to_date('20161112','yyyymmdd') where  pid=3;
 update p_product set prodate=to_date('20150803','yyyymmdd') where  pid=4;
 update p_product set prodate=to_date('20160621','yyyymmdd') where  pid=5;

接下来是一些简单的查询语句练习
–1.查询商品名称和商品价格，商品类型。
select pname,price,tid from p_product; – 这个很简单就没什么好说的了
–2. 查询由业务员王语嫣签订的订单，显示订单的编号，订单价格，订单中商品名称，商品数量。
select oid,tatol,pname,p_num from o_orders ,p_product where o_orders.pid=p_product.pid and o_oper=‘王语嫣’; --使用92语法 连接两个表的相同字段 pid
– 3. 查询订单总额在1000元以上的所有订单，显示订单编号，订单商品名称，最后根据价钱排序。
select oid ,pname from o_orders natural join p_product where tatol>1000 order by price ; --使用了99语法的自然连接 natural join 内部自动查找同名字段|主外键关系 自动实现等值连 最后用orderbye分组
–4. 根据商品类型分类，查询出每个类型的商品各下个多次订单。 看不懂 没思路
– 5. 找出订单中金额最高的订单，显示此订单当中的商品名称，单价，订单总额。
select max(tatol) from o_orders; – 先用max函数求出最大的订单金额
select pname,price,tatol from o_orders natural join p_product where tatol =(select max(tatol) from o_orders); – 然后用99语法连接 条件是金额最大的商品
–6. 查询类型是食品类的所有订单信息，显示订单编号，订单成交的时间。
select pid from t_type natural join p_product where tname=‘食品类’;–99语法查询食品类pid
select pid from p_product where tid =(select tid from t_type where tname=‘食品类’)order by pid;–92语法查询食品类pid
select * from o_orders where pid = (select pid from t_type natural join p_product where tname=‘食品类’) order by pid;–这里不能用等于 因为会返回两个pid
select * from o_orders where pid in (select pid from t_type natural join p_product where tname=‘食品类’) --这里返回两个pid 所以用in;
select oid ,dealtime from o_orders where pid in(select pid from t_type natural join p_product where tname=‘食品类’);
–7. 查询张三 的所有订单，但是订单的编号 ，订单成交的时间，显示订单中商品的名称
select oid,dealtime,pname from o_orders natural join p_product where o_oper=‘张三’;

  总之 ，数据库需要多练习，然后就会融汇贯通，犹如练功一样，练的时间越长，越熟练，那么你的武功自然也就比别人高了！

														2019.07.23