|
SELECT DISTINCT purchased_by FROM purchases;
select * from purchases for update;
select * from products;
select * from customers for update;
insert into customers(customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST) values (
20,'pan','weiwei',null, null,null);
Select * From customers Where customer_id =20;
Insert Into customers Values (21,'testing','my',Null,Null,Null);
Insert Into customers_bak Values (Select customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST From CUSTOMERS Where customer_id = 19);
Select * From CUSTOMERS_BAK For Update;
Truncate Table customers;
Delete From CUSTOMERS;
Insert Into CUSTOMERS_bak(customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST) Select customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST From CUSTOMERS Where customer_id = 19;
select * from CUSTOMERS_BAK Where customer_id =19;
Drop Table CUSTOMERS_BAK;
Create Table customers_bak As Select * From CUSTOMERS;
Select * From customers_bak For Update;
drop table CUSTOMERSBAK;
create table CUSTOMERSBAK as select * from customers;
select * from CUSTOMERSBAK ;
SELECT customer_id,first_name,last_name, NVL(phone,'Unknown Phone Number')
AS PHONE_NUMBER FROM customers;
SELECT * FROM customers WHERE customer_id = 1;
select * from departments for update;
insert into departments(DEPARTMENT_ID,DEPARTMENT_NAME,DEPARTMENT_INFO)
values(6,'testing006','infotesting06');
SELECT * FROM customers WHERE customer_id <> 1;
SELECT * FROM customers WHERE customer_id > ANY (2,3,4);
SELECT * FROM customers WHERE customer_id > ALL (2,3,4);
select min(department_id) from DEPARTMENTS;
SELECT * FROM customers WHERE first_name like '_a%';
SELECT * FROM customers WHERE customer_id IN (2,3,5);
select * from customers where exists (select customer_id from customers where customer_id = 3);
SELECT * FROM customers WHERE customer_id BETWEEN 2 AND 3;
SELECT * FROM customers WHERE customer_id >= 1 And customer_id <= 3;
SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 3;
ALTER SESSION SET NLS_DATE_FORMAT='MONTH-DD-YYYY';
SELECT * FROM customers WHERE dob > to_date('20-8-2007 23:46:50','DD-MM-YYYY hh24:mi:ss')
OR customer_id < 2 AND phone like '%1211';
select sysdate from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select to_char(c.DOB,'DD-MM-YYYY hh24:mi:ss') from CUSTOMERS c where c.DOB is not null;
select c.DOB from CUSTOMERS c where c.DOB is not null;
SELECT product_id, INITCAP(description) FROM products where product_id=4;
select * from products;
select * from customers for update ;
SELECT UPPER('first_name'),LOWER(last_name),initcap(last_name) FROM customers;
SELECT name,LENGTH('Name') FROM products;
select * from products where product_id =1;
SELECT customer_id, NVL(phone, 'Unknown phone Number'),NVL2(phone,'号码不为空','号码为空')
FROM customers;
select * from customers;
SELECT customer_id, NVL2(phone, '已知','Unkown') As "PHONE" FROM customers order by 1 ,"PHONE" Desc;
select c.LAST_NAME,c.FIRST_NAME from customers c Order By 1 ,2 Desc;
select * from products WHERE product_id =1;
select upper(c.first_name),upper('first_name'),initcap(last_name),lower(first_name)
from customers c;
select * from products where product_id =1;
SELECT REPLACE(name,'Scidnce','Physicsb') FROM products WHERE product_id =1;
---当被转换的字符比转换的短时,用空来替代
SELECT translate(name,'Science','Phys') FROM products WHERE product_id =1;
select Name from products where product_id =1;
update products set name='Science' where name like '%Physics%';
select * from products where name like '%Physics%';
Select c.* From USER_ALL_TABLES c Where c.table_name Like '%CUSTOMER_';
select p.name from products p;
SELECT SUBSTR(name,2,LENGTH('name')) FROM products
WHERE product_id =1 order by product_id;
select length(substr(name,2,length(name))),length(name) from products;
select name from products;
SELECT RPAD(name,30,'.'), LPAD(price,8,' ') FROM products WHERE product_id =1;
SELECT LTRIM(' Hello,Welcome To 51testing!'),
length(LTRIM(' Hello,Welcome To 51testing!')),
length(' Hello,Welcome To 51testing!'),
RTRIM('Hi,Welcome To Beijing!abcabzc', 'eabtfcd')
FROM dual;
SELECT FLOOR(5.8),FLOOR(-5.8) FROM dual;
--select FLOOR(5.75),FLOOR(-5.86, 1) from dual;
select FLOOR(5.8) from dual;
SELECT MOD(8,3), MOD(8,4) FROM dual;
SELECT ROUND(5.73), ROUND(5.73,-2),ROUND(-5.73),ROUND(56.73,-1),ROUND(5.759993,4)
FROM dual;
SELECT TRUNC(5.73,0),TRUNC(5.785,2),TRUNC(5.73,-1) FROM dual;
SELECT DECODE(x,1,'x is 1', 2, 'x is 2', 'others') FROM dual ;
select * from CUSTOMERS for update;
select c.first_name, DECODE(c.first_name,
'Gail','全名为Gail Black',
'Xu','全名为xulinlin',
'其他情况'
) from customers c;
select c.first_name,
case c.first_name
when 'Gail' then '全名为Gail Black'
when 'Xu' then '全名为xulinlin'
else '其他情况'
end
from customers c;
select c.*, decode(customer_id, 5,'id is 5', 3, 'first_name is wang', 'others') from customers c;
select * from customers c order by c.CUSTOMER_ID desc;
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
SELECT TO_CHAR(12345.67,’99,999.99’) FROM dual;
SELECT TO_DATE(’08-JUL-2007’),TO_DATE(’08-JUL-06’) FROM dual;
alter session set nls_date_format='YYYY-MM-DD';
SELECT to_date('08-AUG-2007','MM.DD.YY') FROM dual;
select sysdate from dual;
SELECT TO_NUMBER('913.13') FROM dual;
SELECT TRANSLATE('testing','ts','arf') FROM dual;
SELECT product_id, DECODE (warehouse_id, 1, 'Southlake',
2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non-domestic')
"Location of inventory" FROM inventories WHERE product_id < 1775;
SELECT c.* FROM customers c ORDER BY 2;
SELECT * FROM customers ORDER BY first_name ASC, last_name DESC;
SELECT customer_id, first_name, last_name FROM customers ORDER BY 1;
SELECT customer_id, first_name, last_name LASTNAME FROM customers ORDER BY LASTNAME;
SELECT product_type_id FROM products GROUP BY product_type_id ;
SELECT product_id, customer_id FROM purchases GROUP BY product_id,customer_id ;
SELECT product_type_id,AVG(price) FROM products GROUP BY product_type_id ;
SELECT product_type_id,AVG(price) FROM products WHERE price <15
GROUP BY product_type_id
HAVING AVG(price)>13
ORDER BY AVG(price);
select * from products;
--正确写法
select product_type_id,
product_id,
--name,
AVG(price) from products
where product_type_id >2
--having AVG(price) > 3
group by product_type_id,product_id
--,product_id
--having AVG(price) > 3
order by product_id
select * from products order by name;
select p.product_type_id from products p order by p.product_id;
select * from products;
select * from product_types;
select * from product_price_audit;
update products set price = price * 0.7;
select product_type_id,
--product_id,
AVG(price)from products
where product_type_id > 1
--having product_id > 1
group by product_type_id
--order by product_id
--where product_type_id > 1
having product_id > 1
--where product_type_id > 1
order by product_id;
--写一SQL查询产品类型为DVD的所有价格有调整的所有产品信息
select a.* from products a,product_types b, product_price_audit c
where b.name = 'DVD'
and c.old_price <> c.new_price
and a.product_id = c.product_id
and a.product_type_id = b.product_type_id;
--写一SQL语句查询老价格低于25且产品类型为DVD的价格有调整的所有产品信息并对
--其进行分组统计
select a.product_id,a.product_type_id,a.name,a.description,a.price
from products a,product_types b, product_price_audit c
where c.old_price < 20
and b.name = 'DVD'
and c.old_price <> c.new_price
and a.product_id = c.product_id
and a.product_type_id = b.product_type_id
group by a.product_type_id,a.product_id,a.name,a.description,a.price
select * from product_types;
select * from products;
select * from product_price_audit;
--查询所有价格小于10的所有产品类型名称
select p.name from product_types p where p.product_type_id in
(select c.product_type_id from products c where c.price < 10 );
select distinct(p.name) from product_types p, products c
where c.product_type_id = p.product_type_id
and c.price < 10;
--查询所有价格有变动的所有产品信息
select p.* from products p, product_price_audit c
where c.old_price <> c.new_price
and p.product_id = c.product_id
--查询所有DVD产品价格的总计
select sum(price) from products p, product_types f
where f.name = 'DVD'
and p.product_type_id = f.product_type_id;
--查询老价格高于新价格而且类型中含有D字符的所有产品信息
select c.* from product_price_audit p, products c, product_types f
where p.old_price > p.new_price
and f.product_type_id = c.product_type_id
and c.product_id = p.product_id
and f.name like '%D%';
select c.* from product_types p, products c
where p.product_type_id = c.product_type_id
and c.name like '%W%'
and c.product_id in
(select f.product_id from product_price_audit f where f.old_price > f.new_price )
--查询产品价格小于DVD产品平均价格的所有产品信息,并对其进行分组和统计
select c.product_id,c.product_type_id,c.name,
c.description,c.price
from products c where price <
(select avg(price) from products a, product_types c
where a.product_type_id = c.product_type_id
and c.name = 'DVD'
)
group by cube(c.product_id,c.product_type_id,c.name,c.description,c.price);
select * from (select * from customers); |
|