2018-09-05 工作日志

Java Oracle 数据库 MySQL 工作日志

查询数据

SELECT * FROM `customers`
-- 查询记录个数
SELECT count(*) cust_num FROM customers;
-- 查询c_email为空的记录的c_id,c_name,c_email值
-- 注意查询某列为空值时，使用IS NULL
SELECT c_id,c_name,c_email FROM customers WHERE c_email IS NULL;
-- 查询c_email不为空的记录的c_id,c_name,c_email值
SELECT c_id,c_name,c_email FROM customers WHERE c_email IS NOT NULL;
-- 水果表中s_id=101并且f_price    >=5的名称和价格
SELECT f_name,f_price FROM fruits WHERE s_id='101' AND f_price>=5;
-- 水果表中s_id=101或102，并且f_price   >=的名称和价格
SELECT f_name,f_price FROM fruits WHERE s_id IN ('101','102') AND f_price> 5 AND f_name='apple';
-- s_id=101或s_id=102
SELECT s_id,f_name,f_price FROM fruits WHERE s_id in(101,102);
SELECT s_id,f_name,f_price FROM fruits WHERE s_id=101 OR s_id=102;
-- 去掉重复的某一列（相对于查询结果）
SELECT DISTINCT s_id FROM fruits;

多列排序

与DESC相反的是ASC(升序排序)
使用ROWNUM时，只支持<,<=和!=符号，不支持>,>=,=和between...and符号

-- 排序：默认ase，降序DESC
SELECT f_name FROM fruits ORDER BY f_name;
SELECT f_name FROM fruits ORDER BY f_name DESC;
-- 查询执行顺序：1.FROM table 2.WHERE 3.GROUP BY 4.HAVING 5.SELECT 6.ORDER BY
SELECT f_name,f_price FROM fruits ORDER BY f_price DESC;
-- 先按f_pirce降序，再按f_name升序排列
SELECT f_name,f_price FROM fruits ORDER BY f_price DESC ,f_name;
-- 分组查询,使用GROUP BY时，出现在SELECT列表中的字段，如果没有在聚合函数中，那么必须出现在GROUP BY字句中
SELECT s_id,f_name,count(*) AS zhonglei FROM fruits GROUP BY s_id ORDER BY s_id;
SELECT * FROM fruits GROUP BY s_id,f_name;

聚合函数

AVG():返回某列的平均值。
COUNT():返回某列的行数。
MIN():返回某列的最小值。
SUM():返回某列值的和

SELECT * FROM orderitems ORDER BY o_num;
-- 按o_num分组，查询总订单价格大于100的单号和总价格
SELECT
    o_num,
    SUM( quantity * item_price ) AS orderTotal 
FROM
    orderitems 
GROUP BY
    o_num 
HAVING
    SUM( quantity * item_price ) > 100;
-- count()统计查询结果的行数
SELECT * FROM customers;
SELECT COUNT(*) AS 行 FROM customers;
SELECT COUNT(c_email) AS NotNullEmailNum FROM customers;
SELECT * FROM orderitems;
SELECT o_num,COUNT(f_id) AS 行数 FROM orderitems GROUP BY o_num;
SELECT o_num,SUM(quantity) AS 总质量 FROM orderitems GROUP BY o_num;
SELECT o_num,SUM(quantity) AS 总质量 FROM orderitems WHERE o_num=30005;
-- s_id=103的平均价
SELECT AVG(f_price) AS avg_price FROM fruits WHERE s_id=103;
-- 分组平均值
SELECT s_id,AVG(f_price) AS avg_price FROM fruits GROUP BY s_id;
-- 查找价格最高的水果(子查询)
SELECT f_name,f_price FROM fruits WHERE f_price=(SELECT MAX(f_price) FROM fruits);
-- 分组查询最高价格
SELECT * FROM fruits ORDER BY s_id;
SELECT s_id,MAX(f_price) FROM fruits GROUP BY s_id;
-- 分组查询最高价格的水果(ANY可替换为SOME)
SELECT
    s_id,
    f_name,
    f_price 
FROM
    fruits 
WHERE
    f_price = ANY( SELECT MAX( f_price ) FROM fruits GROUP BY s_id );

系统信息函数：

USER:返回当前会话的登录名
NVL函数：由于聚合函数是忽略空值的，例如sum(),avg()等，所以经常使用NVL函数进行null的转换。
例如:nvl(列值,0),当列值为null时，取值为0,当列值为非null时，取值为列值本身。

select ename,NVL(comm, 0) from emp;

查询执行顺序：

1. FROM table
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

在使用GROUP BY时，出现在SELECT列表中的字段，如果没有在聚合函数中，那么必须出现在GROUP BY字句中。

下午

关于Oracle序列

-- 经常用于生成主键
create sequence emp_seq start with 100 increment by 10;
select emp_seq.nextval from dual;
select emp_seq.currval from dual;
drop sequence emp_seq;
create sequence ss_seq start with 1000 increment by 100；-- 默认1，步长1
select ss_seq.nextval from dual;-- 获取序列下一个值
select ss_seq.currval from dual;-- 获取序列当前值
drop sequence ss_seq;-- 删除序列

Oracle分页

伪列，每次必须从0开始

-- 分页(仅Oracle)
SELECT*FROM fruits;
SELECT f_name,ROWNUM FROM fruits;
-- 分页(仅Oracle)
-- SELECT * FROM fruits WHERE 5<ROWNUM<10;不正确，每次必须从0开始
SELECT * FROM (SELECT f_name,ROWNUM r FROM fruits) WHERE r> 5 AND r< 11;
SELECT * FROM (SELECT f.*,ROWNUM r FROM fruits f) WHERE r> 5 AND r< 11;
SELECT*FROM (SELECT f.*,ROWNUM r FROM fruits f) WHERE r> (2-1)*5 AND r< (2*5+1);

连接查询

-- 连接查询fruits和suppliers
SELECT
    suppliers.s_id,
    s_name,
    f_name,
    f_price,
    s_city
FROM fruits,suppliers 
WHERE fruits.s_id = suppliers.s_id;

内连接查询

-- 内连接查询fruits和suppliers
SELECT suppliers.s_id,s_name,f_price FROM fruits INNER JOIN suppliers ON fruits.s_id=suppliers.s_id;
SELECT orders.c_id,orders.o_num FROM orders INNER JOIN customers ON orders.c_id=customers.c_id AND orders.c_id=10001;

外连接查询

SELECT c_id,c_name FROM customers;
SELECT o_num,c_id FROM orders;
-- 查询所有客户的订单（左外连接）
SELECT customers.c_id,c_name,o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id=orders.c_id ORDER BY c_id;-- OUTER关键字可忽略
-- 查询所有客户的订单(右外连接)
SELECT customers.c_id,c_name,o_num FROM customers RIGHT OUTER JOIN orders ON customers.c_id=orders.c_id ORDER BY o_num;-- OUTER关键字可忽略