Oracle Common Commands ____oracle

(1) Initialization of password
SYS Change_on_intall
System Manager
Scott Tiger
(2) Copy a table
CREATE TABLE new_table as (SELECT * from old_table)
(3) Modify the primary key of a table
ALTER TABLE tablename DROP constraint a_pk;//Delete original PRIMARY KEY constraint
ALTER TABLE tablename ADD constraint A_PK primary key (ID)//Add a new constraint
(4) View table structure
DESC TableName
(5) Quickly clear the data of a table
TRUNCATE TABLE new_table
(6) Importing the results of a SELECT into a file
Spool C:/abc.text;
SELECT * from new_table;
Spool off;
(7) Modify table name
ALTER TABLE old_table Rename to new_table;
(8) Retrieve the first N records
SELECT * from new_table where rownum<n the order by ID;
(9) Statistics of the total number of records in both tables
Select (select count (ID) from-a) + (select COUNT (id) from B)) from dual;
(10) date plus two years
Select Add_months (t.curdate,24) from new_table t.id=1;
(11) Date plus two days
Select T.curdate-2 from New_table t.id=1;//t.curdate is a date type
(12) Return to the last day of the month
Select Last_day (t.hiredate) from EMP t where t.empno=7369;
(13) Set addition and subtraction
SELECT * FROM new_table UNION ALL SELECT * from old_table;
SELECT * from New_table minus select * from old_table;
(14) Create a sequence
Create sequence Seq_name start 1 increment by 1;
INSERT INTO TableName () values (Seq_name.nextval,)
(15) Create an index
CREATE INDEX index_name on tablename (name);
(16) To modify the SYS password
Alter user "SYS" identified by "New_password";
(17) Delete table space
Drop tablespace including contents and datafiles;
(18) Create a table space
Create Tablespace Tpname
DataFile ' Tpname '

Size 32m
Autoextend on Next 200M
MaxSize Unlimited;
(19) Delete user
Drop user Pork1 CASCADE;

(20) Create user and password and specify table space
Create user UserName identified by values "passwd"
Default Tablespace tpname;

(21) Give Superuser access to user session
Grant Connect,resource to UserName;

(22) Granting DBA authority to user username
Grant DBA to UserName;
(23) Pour out the data command

Export the specified table:
Exp userid=trswcm/trs owner=trswcm buffer=204800000 tables= (wcmchannel,wcmwebsite) file=/trs/mydata.dmp grants=y
To import the specified data:
Imp userid=trswcmtest/trs fromuser=trswcm touser=trswcmtest ignore=y buffer=204800000 file=/trs/MyData.dmp
204800000 bytes Commit once, ignore guarantees that the second input can succeed even if the object exists

(24) The difference between having and werhe
Having and WHERE clauses can control the rows in the source table used to generate the result set
(1) The Action object is different. Where is acting on a table or view, having is acting on a group
(2) Where the row is always selected before the aggregation calculation (Count,max) or grouping, and having always selects rows after grouping or after
So there cannot be a clustered function in the WHERE clause, and the having must include a clustered function

Conclusion:
The 1.WHERE clause is used to filter the rows generated by the actions specified in the FROM clause.
The 2.GROUP by clause is used to group the output of the WHERE clause.
The 3.HAVING clause is used to filter rows from the grouped results.

The difference between group by and where
The Where condition is filtered based on a field, and group by is an operation on a group

(26) escape character escape
SELECT * FROM bonus t where t.ename like ' s/_% ' escape '/'
Indicates/is an escape character, cannot blur a query on some keywords, and escapes _ (keyword)

(27) Function classification
1. Single-line function
Lower, upper Initcap (initial capital) concat substr length NVL
If the original value is NULL, the specified number is substituted
Select NVL (Count (Gard), 0) from T;
2. Numerical function
Round rounding
Select Round (45.935,2) from dual; Result 45.94 The second parameter indicates that two digits remain after the decimal point
Trunc Intercept
Select Trunc (45.935,2) from dual; Result 45.90 The second parameter represents intercepting two digits after the decimal point
3, Date function
Add_mounths (sysdate,-5) Delayed May
Add_mounths (SYSDATE,-5*12) delayed five years
Sysdate-5 Delay five days
To_char (sysdate, ' yyyy-mm-dd '), to_date (' 2008-01-12 ', ' yyyy-mm-dd '), To_number () date format conversion
4. Group function
GROUP BY group clauses, after which the record filter is filtered by the having and the Where
AVG (), COUNT (), Max (), Min (), SUM () AVG, and sum can only be used for numeric types
COUNT (*) is the number of records
Deep understanding of this query statement
Select Id,count (*) from T GROUP by ID
The first is to group the IDs, and then the statistics for each group, the final result is the number and average number of each group (AVG ())
You can also filter conditions on groups
Having Max (), Min (), count (), and so on, takes out the eligible group for statistics.
The HAVING clause controls the row groups determined by the GROUP BY clause, and the HAVING clause condition allows only a constant, a clustered function, or a column in a GROUP BY clause.

(28), SQL subquery
Run order of subqueries: Run the subquery first and run the main query, the subquery generally appears to the right of the operator, the subquery result is usually a row, a row of columns,
More than one row, multiple rows, and columns
A primary query should have a single value operator (one row, row, multiple columns) and a multivalued operator (a column of more than one row, multiple rows and columns), if a single operator is used but the subquery returns multiple rows
Will complain
Eg:select name from emp where title = (select title from emp where Emp.f_name= ' Chen ') there may be more than one f_name is Chen's
Employees, so title is also multiple results. It is an error to use a single operator.
Correction: select name from EMP where title in (select title from emp where Emp.f_name= ' Chen ')

(29), pagination display
RowNum is either equal to 1 or less than a value and cannot be equal to or greater than a value
Because RowNum is a pseudo column that adds to the result set, that is, a column that is followed by a result set and then added. RowNum is the serial number that matches the result of the condition. It's always been lined up from 1.

。 So the result you choose cannot be No 1. Between......and these conditions, because the rownum of the first record obtained from the buffer or data file is 1, then deleted and then fetched

The next, but its rownum or 1, and was deleted, and so on, then there is no data. Why between 1 and 10 or between 0 and 10 can find results and use

Between 2 and 10 do not get the same result as ibid., because RowNum always starts with 1.
But if you want to use the terms rownum > 10, use a nested statement to make Mr. RowNum, and then query him.
  SELECT *
      from [Selet rownum as rn,t1.* from a where ...
          where RN >10
The result set is paginated in general code. That is, Mr. RowNum after the column filter,
  SELECT * FROM (select A.*,rownum R to emp a) where r between 5