sql语句第一天 Oracle

create table es_user(
 id number primary key description ,
  username varchar2(20) not null,
 password varchar2(20) not null ,
 realname varchar2(20),
 tel varchar2(20),
 address varchar2(100),
 zip varchar(6),
 email varchar2(50),
 usertype number(5,0) check (usertype in (0,1,2))
)
create table es_sort(
id number primary key,
sortname varchar2(40)
)
create table es_product(
id number primary key,
sort_id number  references es_sort not null,
name varchar2(500) not null,
price number not null,
description varchar2(2000) not null,
contents varchar2(2000),
saledate date ,
stockcount number 
)
create table es_order(
id number primary key,
user_id number  references es_user not null,
realname varchar2(100) not null,
address varchar(100),
zip varchar2(6),
tel varchar2(50),
email varchar2(20),
createtime date not null,
totalamount number(10,2),
status number not null check (status in (1,2,3,4))
)






update es_user set password = '111111' ;
select * from es_user;
update  es_product set name ='name1', price = 0 where id = 1; /*修改某一个商品的名字和价格 where 一般只通过主键修改*/
select * from es_product;
update es_product set price  = 100 ;--设置所有的价格都为100
select * from es_product;

update es_user set usertype = 2 where usertype = 1;
insert into es_user values(2,'李刚','111','xukebao','63678657','dizhi','325401','735980892@qq.com',1);
insert into es_user values(3,'李妹子','111','xukebao','63678657','dizhi','325401','735980892@qq.com',2);
insert into es_user values(4,'哈哈','111','xukebao','63678657','dizhi','325401',null,2);
delete es_user where id = 3; /*删除es_user 上的某一行数据*/
select * from es_user where usertype = 2 and(username = 'kenybao' or username = 'kenybbb');
/*查询为空的时候 是用is null 的 */
select * from es_user where email is null;
 /*所有姓李的会员*/
select *from es_user where username like '李%'; 
  /*所有两个名字的姓李的会员*/
select * from es_user where username like '李_'; 
/*模糊查询 in两个中或选择*/
select * from es_user where username in('李刚','哈哈');  
select * from es_user where username ='李刚'or username = '哈哈';
select u.username as "用户名" from es_user u where u.id = 2;/*起别名 为列取别名 as “别名字”*/
/*?????第二个名字为刚的用户信息？？？？*/
select * from es_user  where username like '_刚';
/*查询 用户id 为1 - 2的用户信息*/
select * from es_user where id between 1 and 2 ;
/* 取别名 表的别名在后面取的 可以省略as*/
select username as "用户账户名称" from es_user u ;
--设置产品分类编号和名字
insert into es_sort values(1001,'product1');
insert into es_sort values(1002,'product2');
insert into es_sort values(1003,'product3');
insert into es_sort values(1004,'product4');
--插入产品信息
--插入第一种方法
insert into es_product values (1,1001,'dasd',10.2,'hahaha',null,sysdate,10000);
insert into es_product values (2,1002,'dasd',20.11,'hahaha',null,sysdate,10000);
insert into es_product values (3,1003,'dasd',90.77,'hahaha',null,sysdate,10000);
insert into es_product values (4,1004,'dasd',70.55,'hahaha',null,sysdate,10000);
insert into es_product values (5,1001,'dasd',15.44,'hahaha',null,sysdate,10000);
insert into es_product values (6,1002,'dasd',60.33,'hahaha',null,sysdate,10000);
insert into es_product values (7,1003,'dasd',80,'hahaha',null,sysdate,10000);

--也可以这样插
insert into es_product (id , sort_id ,name,price ,description,saledate,stockcount)
values(8,1004,'dasd',70,'hahaha',sysdate,10000);
select * from es_sort;

select id ,name,price,to_char(saledate,'yyyy-dd-mm') from es_product; /*导出 规定输出date 用to_char方法*/
--可以这样排序asc表示升序排序，desc表示表示降序排序
--按产品价格降序排序
select * from es_product u order by u.price desc ;
select * from es_product u order by saledate desc ;
select price from es_product ;
--查看商品的价格分布 同样的价格需要被过滤 同时按价格升序排序
select distinct price from es_product order by price desc

--查询所有商品的类别 按 类别降序排列
 select distinct sort_id from es_product order by sort_id desc;
--查询 价格在20-60 之间的所有产品 价格降序排列
select * from es_product u where u.price between 20 and 70 order by price desc;
--删除id为1 的商品
delete  from es_product u where u.id = 1;
--
rollback --不加也删了？？？？ 
delete from es_user ;
commit --不加也删了？？？？ 
-- 在价格price 后面加上‘元’  并降序排序 caoncat（参数1 ，参数2 )将参数1 和参数2 连接起来
select id "ID" ,name "名字",price||'元' "价格" from es_product order by price desc;
--连接两个属性的信息 并在一列中显示 方法同上
select concat(name , '的id是'||id) "姓名 和id " ,price "价格"from es_product;

--检索商品表 发现商品描述不为空的 用"暂无描述“代替
select id "ID" ,name "名字",price||'元' "价格",nvl(u.contents ,'暂无描述') "描述" from es_product u
--trunc(value,n)截取只有一个参数的时候 那么n将使用默认值0，n可以是整数也可以是负整数，
--正整数时表示截取小数点后面的位数，负整数时表示截取小数点前的位数。
select price from es_product;
select trunc(price ) from es_product;
select trunc(price ,1) from es_product;
select trunc(price ,-1) from es_product;

--round 四舍五入 其他和trunc用法一样
select price "原来的"from es_product;
select round(price ) "整数部分取整四舍五入"  from es_product;
select round(price ,1) "小数点保留一位" from es_product;
select round(price ,-1) "小数点左边保留" from es_product;
--extract (year from sysdate)日期函数 取出 日期中的部分数值
select extract(year from saledate) "生产年份",extract (month from saledate) "生产月份" ,extract(day from saledate) "生产日" from es_product ;

insert into es_product values(5,1002,'dasd',80,'hahaha',null,sysdate,10000);
--用extract (year from sysdate)日期函数 打印列入 2013--8--1 的日期格式
--法1：用字符连接符 ||  或者用
select extract(year from saledate)||'--'|| extract(month from saledate)||'--' ||extract(day from saledate) "日期" from es_product where id = 5;
--法2： to_char函数： 将date类型的字段转换为 固定格式的字符串输出
select to_char(saledate,'yyyy--mm--dd') "to_char日期" from es_product;

--to_date 方法  将固定字符串转为date 类型的数据 一般在日期字符串比较  或者录入日期类型数据时用
-- 1. 录入日期（ 修改日期）
update es_product set saledate = to_date('2013--09--01','yyyy--mm--dd') where sort_id = 1002;
--2  查询规定字符串所表示的日期中的数据
select * from es_product where saledate between to_date('2013-08-1','yyyy-mm--dd') and to_date('2013-08-2','yyyy-mm--dd');


--  group by  聚合函数一般用于计算 什么平均工资啊之类的  但是必须分类 如每个员工 都属于一个部分 
-- 那么 在这里我们计算每个部门的平均薪资 这时候我们就需要用 group by 来实现  
--那么在这里 我们这里有四个 sort_id  也就是四个商品类别 计算每个类别的平均价格 
--group by 类别 就是你要分类的类别 sort_id  商品类别 或者是 上面所说的 部门

select sort_id "商品类别" , avg(price)  "平均价格" from es_product group by sort_id;


select * from es_user;
select * from es_sort;
delete es_sort;
delete es_product;
select * from es_product;
desc es_product
select * from es_order;