Oracle multi-Table query, as its name implies, queries the data you want from multiple tables. Here we will test the emp table and dept table under the scott user. The structure of the two tables is as follows:SQL> select * from dept;DEPTNO DNAME LOC-------------------------------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON
SQL
Many of my friends use mysqllimit for paging to oracle and do not know how to write paging. In fact, oracle paging is similar to mssql paging. Next I will introduce oracle paging statements.
Many of my friends who use mysql limit for paging to oracle do not know how to write paging. In fact,
Query all the elements of a distance from a certain point (that is, the surrounding query) SQL:
Select T. objectid, T. comp_type, T. datatype, T. comp_name, T. health_lic, T. shape. sdo_point.x as x, t. shape. sdo_point.y as Y, T. reg_addr, T. bus_addr from t_publicplaces t where 1 = 1 and substr (T. comp_type,) = '01' and sdo_within_distance (T. shape, sdo_geom
">
2. Merge into statement:
For the top statement, you can use the merge into statement
--Merge into implementation method
merge into EMP
using (SELECT * from
emp_new1
) E on
(e.empno = emp.empno)
when matched then
update set emp.empdesc = E.empdesc;The execution results are the same as the results in 1, assuming inference. It is recommended to use the merge into method.
Because merge into only visits once emp_new1.
Iii. Deleting delete op
Oracle Query result set, random sort
Copy Code code as follows:
SELECT * FROM table1 ORDER by Dbms_random.value ();
MySQL randomly query out a record:
Copy Code code as follows:
--The following query statement is efficient, do not use SELECT * FROM table1 to rand () LIMIT to
One of the Oracle-SQL skills (query continuous records) Requirement Description: www.2cto.com needs to query that a customer has been continuously performing business for those days of a year. The SQL implementation is as follows: Create a table: create table test_num (tyear
SQL connection query for Oracle databasesPrefaceIn reality, there is such a requirement that sometimes the data we need to query is in multiple tables, so how to query data from multiple tablesWhat about it? In this case, you need to use table connections to perform queries.
☆Retrieve all table names and column names of the databaseSelect name from sysobjects where xtype = 'U'Select name from syscolumns where id = (select max (id) from sysobjects where xtype = 'U' and name = 'table name ')☆Recursive Data QueryRecursive query of SqlServer2005 and Oracle in SQL statementsI used Oracle before
Different databases: oracle mysql SQL Server DB2 infomix sybase paging query statement, infomixsybase
Paging query statements used in different databases:
Current page: currentpagePage size: pagesize
1. Oracle Database
Select * from (select A. *, rownum rn from (QUERY_
My SQL is very poor, half a day response to, was a colleague joke. Oh, ah ah. Must surpass him later!!Table ShopPk_shop PRIMARY KeyName Commodity
Shop
Pk_shop
Name
001
Down
002
Coca - cola
Bpm_ll_test2
PK_BPM_LL_TEST2 PRIMARY Key
Sname Product Name
01
001
Select (select name from shop where shop s s.pk_shp
Copy codeThe Code is as follows:-- Oracle reads SQL statements from last Monday to weekend-- The day of the week starts on Sunday.Select to_char (to_date ('201312', 'yyyymmdd'), 'D') from dual;-- Result: Note 6: the sixth day of the week is Friday.Select to_char (sysdate + (2-to_char (sysdate, 'D')-7, 'yyyymmdd') from dual; --- last MondaySelect to_char (sysdate + (2-to_char (sysdate, 'D')-1, 'yyyymmdd') fr
,'YYYYMMDD') Rangebetween thePreceding Currentrow)End asf70115_70011, Case
whenA.c_date>To_char (sysdate-3,'YYYYMMDD')
Then
sum(VALUEF1) Over(Partition byA.code,a. Year Order byTo_date (A.c_date,'YYYYMMDD') Rangebetween thePreceding Currentrow)End asf70104_70011, Case
whenA.c_date>To_char (sysdate-3,'YYYYMMDD')
Then
sum(VALUEF6) Over(Partition byA.code,a. Year Order byTo_date (A.c_date,'YYYYMMDD') Rangebetween thePreceding Currentrow)End asf70126_70011, Case
whenA.c
BackgroundWhen using Oracle or other databases, using the LIKE keyword for fuzzy query is commonly used by people, in the use of pure Chinese environment is very easy to use, there are some wildcards can be used, but in a pure English environment, the size of the need to accurately match the problem, the main reason is the problem of string
FL like '%{0}% ' and
Here like after is a string, so
ORACLE basic SQL statements-query, oraclesql statements
1. Common Query
/* Query table data */Select * from STU
/* Retrieve the first three rows of Data */Select * from stu where ROWNUM
/* Fuzzy query */Select * from stu where s
This article provides a detailed analysis of the implementation of Oracle SQL statements for querying and deleting jobs, for more information about how to query and delete duplicate records, see SQL statement 1. For more information about duplicate records in a table, see select * frompeoplewherepeopleIdin (selectpeopl
SQL statement for querying and deleting duplicate records1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).Select * from peopleWhere peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest
Oracle journey (ii) SQL statement query settings such as t_chmo_order_opera p, business table t_chmo_teruser u, user table t_chmo_province, Province table t_chmo_opera_price r price list ----- value-added business details select p. oo_source_num as phone number, r. opera_price as amount, cp_province_name as region, p. oo_create_time as date, u. ct_category as ope
SQL statements for Oracle management and query
Tablespace and capacity:
Select tablespace_name, sum (bytes)/1024/1024/1024 from dba_data_files group by tablespace_name;
Free space of the tablespace:
Select tablespace_name, sum (bytes) from dba_free_space group by tablespace_name;
System tablespace:
Select tablespace_name from dba_tablespaces;
System data
Page query SQL statement for the Oracle database tutorialOracleSELECT *From (select RowNum r,t1.* from table name T1 where rownum where T2.R >= m
For example, from the table sys_option (primary key is sys_id) from 10 records or retrieve 20 records, the statement is as follows:
SELECT *From (select RowNum r,t1.* from sys_option where RowNum where T2.R >= 10
MyS
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.