sql经典语句（SQL classic sentences）
I. Basic operation
1) DESC, the describe role is to display the structure of the data table using the form: desc data table name
2) distinct eliminates duplicate data usage: select, distinct, field name, from data table
3) order by field 1 ASC, field 2 desc
4) nested queries select, pno, ame, emp.job, emp.sal
p
Where sal>= (select, Sal, from, p, where, ename
='WARD');
5) nested queries select, pno, ame, emp.job, in, emp.sal
p
Where, Sal, in (select, Sal, from, p, where, ename ='WARD');
6) nested queries select, pno, ame, emp.job, any, emp.sal
p
Where Sal > any (select, Sal, from, p, where, job
='MANAGER');
Equivalent to (1) select, Sal, from, p, where, job ='MANAGER'
(2) select, pno, ame, emp.job, emp.sal
p
Data found in where Sal > (1) data a, or, Sal > (1), data found in B, or, Sal > (1), C
Eg:
Select, Sal, from, p, where, job ='MANAGER'results; 12,10,13
Equivalent to sal=12,10,13 or SAL> (12, OR, 10, OR, 13)
7) the intersection operation is the intersection concept in the set. The sum of the elements that belong to the set A and belongs to the set B is the intersection. In the command edit area, execute the following statement.
Eg:
(select, djbh, from, ck_rwd_hz) intersect (select, djbh, from, ck_rwd_mx) documents numbered the same
Select * from ck_rwd_mx a,
((select, djbh, from, ck_rwd_hz) intersect (select, djbh, from, ck_rwd_mx)) B
Where a.djbh =b.djbh
Two function
1) ceil takes the minimum integer ceil (N) greater than or equal to the value N; select, Mgr, mgr/100, ceil (mgr/100), from, p;
2) floor takes the maximum integer floor (N) less than or equal to the value N; select, Mgr, mgr/100, floor (mgr/100), from, p;
3) the remainder of mod m divisible by mod (m, n) n
4) power, m, N, square, mod (m, n)
5) round m four, five, keep the n bit mod (m, n)
Select round (8.655,2) from dual; 8.66
truncatedelete和drop的区别
Select round (8.653,2) from dual; 8.65
6) sign n>0, take 1; n=0, take 0; n<0, take -1;
7) AVG averages AVG (field name)
8) count statistics total count (field name) select (*) p; select count (distinct job) p;
9) min calculates numeric fields minimum, select, min (SAL), minimum salary p;
10) max calculates numeric field maximum, select, max (SAL), highest salary p;
11) sum calculates the sum of numeric fields, select, sum (SAL), the sum of salaries, from, p;
Three input data
1) single data entry
Insert into data tables (fields 1, fields 2,...) valuse (field name 1 values, field name 2 values,...)
Numeric fields can write values directly; character fields add single quotation marks; date fields add single quotes; at the same time pay attention to the order of days and months
2) multi line data entry
Insert into data table (field name 1, field name 2,...)
(select (field name 1 or operation, field name 2 or operation,...) from data table where condition)
3) data replication between tables
Create st
As
(
Select, distinct, empno, ename, hiredate, from, p, where, empno>=7000
);
Create table spkpk_liu as select * from spkfk; creates tables and copies data, but creates incomplete table information
This is all the way to full table backups.
Usually after the table is built, you need to see if you want to build the index and primary key again.
And "create, table, spkpk_liu, as, select * from, spkfk."
"
After this table is built, many of the parameter values of the table are the default minimum values, such as the initial value of the original table 10M, and the new table is probably only 256K.
Formal environment used in the table, generally do not recommend such a table built.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。