SELECT * FROM OD WHERE ROWNUM <= 100;
DROP TABLE OD purge;/*删除表并释放空间*/
ALTER TABLE OD DROP COLUMN c; /*删除某个表内的一列数据*/
SELECT ori_taz_no, time ,SUM(num) FROM (
SELECT * FROM C##WLMQ.YUWANG2025 WHERE ori_taz_no = 122 ) a GROUP BY ori_taz_no, time ORDER BY time ASC; /*1点到22点,从某一网格出发到其他网格的客流量分时刻总和*/
WITH temp_table AS (
SELECT ori_taz_no,to_char(flow, 'fm9999990.00') as aa from (
SELECT ori_taz_no,SUM(num) as flow from C##WLMQ.YUWANG2025 GROUP BY ori_taz_no ORDER BY flow DESC ) a )
SELECT * FROM temp_table where ori_taz_no = 2198; /*创建临时表temp_table ,并查询表*;ORDER BY是排序,默认从低到高ASC顺序,DESC倒序/
SELECT NAME FROM V$DATABASE; /*检查当前 Oracle 数据库的名称*/
select ori_taz_no || ',' || dest_taz_no AS full_name from C##WLMQ.YUWANG2025 where ROWNUM <= 100; /*将a列的数据与b列的数据通过连接符合并为一列数据,如果a、b类数据类型相同则不需要写明数据类型*/
SELECT TO_CHAR(ori_taz_no) || ',' || TO_CHAR(UPDATE_time, 'YYYY/MM/DD HH24:MI:SS') AS full_name FROM C##WLMQ.YUWANG2025 where ROWNUM <= 100; /*将a列的数据与b列的数据通过连接符合并为一列数据,并保持原有数据的数据格式*/
ALTER TABLE your_table ADD (c NUMBER);
UPDATE your_table SET c = ROUND(DBMS_RANDOM.VALUE(1, 100)); /*原有方法:需要分2步,第一步在表中增加一个字段(c)数据类型是(NUMBER),第二步是赋值*/
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE OD ADD (c NUMBER)';
EXECUTE IMMEDIATE 'UPDATE OD SET c = ROUND(DBMS_RANDOM.VALUE(1, 100))';
COMMIT;
END; /*二步合并为一步,第一步在表中增加一个字段(c)数据类型是(NUMBER),第二步是赋值*/
SELECT * FROM Employee
-- 创建员工表
CREATE TABLE Employee (
employee_id INT,
employee_name VARCHAR2(50),
department VARCHAR2(50)
);
-- 插入员工数据
INSERT INTO Employee VALUES (1, 'Alice', 'HR');
INSERT INTO Employee VALUES (2, 'Bob', 'IT');
INSERT INTO Employee VALUES (3, 'Cathy', 'HR');
INSERT INTO Employee VALUES (4, 'David', 'IT');
INSERT INTO Employee VALUES (5, 'Eva', 'Sales');
-- 使用LISTAGG函数聚合员工姓名
SELECT department, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS employees
FROM Employee
GROUP BY department; -- listagg(合并字段, 连接符) within group(order by 合并字段排序)
SELECT a.ORI_TAZ_NO,a.DEST_TAZ_NO,a.FLOW, b.NO FROM C##WLMQ.YUWANGDAQU_1 a
INNER JOIN C##WLMQ.YUWANGDAQU b ON a.DEST_TAZ_NO = b.ID; -- 左连接(left join)和内连接(inner join)的区别在于左连接会保留左侧表中所有记录,而内连接只返回满足连接条件的记录。
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from DUAL;
select to_char(sysdate,'yyyy') from DUAL;--保留需要的数据格式
SELECT sysdate FROM DUAL; -- SYSDATE是系统系统时间,dual 是一张伪表(没有实际意义)