Oracle Basic Command Letter

(1) connection command :
1 "Conn[ect]: Usage, conn username/password @ NETWORK SERVICE name [as Sysdba/sysoper], when connected as a privileged user, must bring as SYSDBA or as Sysoper
Sql> Conn Sys/lipeng @orcl as SYSDBA;
2 "Disc[onnect]: This command is used to disconnect from the current database
3 "Passw[ord]: This command is used to modify the user's password, if you want to modify the other user's password, you need to log in with Sys/system
4 "Show User: Display the current username
5 exit: The command disconnects from the database and exits
(2) file Operation command
1 "START and @: Run SQL script; For example: sql>@ d:\a.sql or Sql>start d:\a.sql
2 "EDIT: This command can edit the specified SQL script; For example: Sql>edit d:\a.sql
3 "Spool: This command can output the contents of the Sql*plus screen to the specified file. Example: Sql>spool d:\b.sql and input sql>spool off

(3) display and set environment variables : can be used to control the output of various formats, set show if you want to permanently save the relevant settings, you can modify the Glogin.sql script
1 linesize: Sets the width of the display line, which is 80 characters by default:
Sql>show linesize
Sql>set Linesize 90
2 "pagesize: Set the number of lines displayed per page, default is 14, use the same as Linsize

(4) granting and recycling of permissions

Hope xxxxx users can go to query the EMP table
Hopefully xxxxx users can query Scott's EMP table: Grant SELECT on EMP to XXXXXX
Hopefully xxxxx users can modify Scott's EMP table: Grant update on EMP to XXXXXX
Hopefully xxxxx users can go to modify/delete/query/Add Scott's EMP table: Grant all on EMP to xxxxxx
Scott wants to reclaim XXXXXX's query permissions on the EMP table: Revoke select on EMP from xxxxxxx

Maintenance of permissions: I hope xxxxx users can query Scott's EMP table/also hope that XXXXX can continue to give this permission to others (if object permissions, join with GRANT OPTION): Grant Select the EMP to XXXX with G rant option; (indicates that XXXXX has the right to continue giving this permission to others)
(If system permissions) grant connect to XXXX with admin option

？？？？ If Scott xxxxx the query permission on the EMP table, XXXXX will continue to yyyy, so what happens to yyyy?
A: Then yyyy's privileges will be recycled.

(5) use profile to manage user passwords :

Profile is a collection of command limits, resource constraints, and when a database is established, Oracle automatically creates a profiles called default, and Oracle assigns the default to the user when the setup user does not specify the Profiles option.
1 "Account lockout: Specify the number of times a password can be entered when the account (user) logs in, or you can designate the time that the user is locked out (days) to execute the command as a DBA, for example:
Make tea this user can only try to log in at most 3 times, the lockout time is 2 days, the implementation code is as follows:
Sql>create Profile Lock_account (Lock_account represents the name of the creation specification and can be defined arbitrarily) limit failed_login_attempts 3 Password_lock_time 2 ; (Create profile creates a rule)
Sql>alter User Tea Profile Lock_account (indicates that this specification (Lock_account) is assigned to tea)
2 "Unlock the account (user)
Sql>alter user tea account unlock;
3. Stop password: In order for the user to periodically change the password can use the command to terminate the password to complete, the same command also requires DBA identity to operate
For example: To create a profile for the user tea, require the user every 10 days to modify their login password grace period of 2 days, the implementation code is as follows:
Sql>create profile Myprofile limit password_life_time password_grace_time_2;
Sql>alter User Tea Profile Myprofile

(6) password history :

If you want users to be able to change passwords without using a password that has been used before, you can use the password history so that Oracle will store the password modification information in the data dictionary so that when the user modifies the password, Oracle will compare the old and new passwords, and when the old and new passwords are found, The user is prompted to reenter the password. For example:
1) Establish profile:
Sql>create profile password_history (password_history file name, free to change) limit Password_life_time Password_grace_time 2 Password_resuse_time 10
password_reuse_time//specifies that passwords can be reused for 10 days after they are reused
2) Delete profile: You can delete the file when you don't need a profile
Sql>drop profile password_history [Cascade]

(7) modification of the table
1 Add a field:
Sql>alter Tables Student Add (ClassID number (2));
2. Modify the length of the field:
Sql>alter Table Student Modify (XM VARCHAR2 (30));
3. Modify the type/name of the field (cannot have data):
Sql>alter Table Student Modify (XM char (30));
4 Delete a field:
Sql>alter table student Drop column Sal;
5. Name of the modified table:
Sql>rename student to Stu;
6 Delete Table:
Sql>drop table student;

(8) View
1 "Table structure:
Sql>desc student;
2 "All columns:
Sql>select * from table name;
3 "Development of the column:
Sql>select Ename,sal,job,deptno from EMP;
4 "Cancel duplicate lines;
Sql>select distinct deptno,job from EMP;

(9)Add Data
1 "All characters Fu insert
Sql>insert into student values (' A001 ', ' Zhang San ', ' Male ', ' January-May-01 ', 10);
Friendly tip: The default date format in Oracle is ' DD-MON-YY ' for example: ' September-June-99 '
Default format for Modified Date: Alter session set nls_date_format= ' YYYY-MM-DD ';
After modification, you can add the date type in our familiar format, namely:
Sql>insert into student values (' A002 ', ' MIKE ', ' Male ', ' 1905-02-30 ', 10);
2 Insert part of field
Sql>insert into student (xh,xm,sex) VALUES (' A003 ', ' JOHN ', ' female ');
3 "Insert null value
INSERT into student (Xh,xm,sex,birthday) VALUES (' A004 ', ' MARTIN ', ' male ', null);
Friendly reminder: When you need to query for a value on a column in a table is empty, the code is as follows:
Sql>select * FROM student where birthday is null;
4 Modify a field
Sql>update student set sex= ' female ' where xh= ' A001 ';
5 "Modify multiple fields
Sql>update student set sex= ' Male ', birthday= ' 1980-04-01 ' where xh= ' A001 ';
6. Modify data that contains null values
7 "Delete data
Sql>delete from student; Delete all records, table structure is still in, write log, can recover, slow
Friendship tip: To restore, you must delete all the data before you save the node (savepoint), that is: savepoint xxx;
After deletion, you only need to roll back to the saved node: rollback to XXX;
Sql>drop table student; Delete the structure and data of a table
Sql>delete from student where xh= ' A001 '; Delete a record
Sql>truncate table student; Delete all the records in the table, the table structure is still in, do not write the log, can not retrieve deleted records, fast

(10)simple query for table
1 "To alias the column:
Sql>select sal*12 "annual salary", ename from EMP;
2 How to handle null values: Use the NVL function to handle
Sql>select SAL*12+NVL (comm,0) "annual salary", ename from EMP;
where NVL (comm,0) means that if the Comm is null then use the zero instead, if Comm is not NULL, then use COMM instead
3 "How to connect a string
Sql>select ename | | ' is a ' | | Job from EMP;
4 using the WHERE clause
Sql>select * from EMP where sal>2000 and sal<2500;
5 "How to use the LIKE operator
%: Represents any 0 to more characters, for example: Sql>select ename from emp where ename like ' s% '; (used to query the name of the employee whose first character is S)
_: Represents any single character, for example: Sql>select ename from emp where ename like ' __o% '; (used to query the employee's name for a third-letter O in uppercase)
6 "in the Where condition use in:
Sql> SELECT * from emp where empno in (7369,7521,7934); Represents the employee information in a table that queries Empno is 7369,7521,7934
7 "Using is NULL operator:
Sql> SELECT * from emp where Mgr is null; Indicates a situation where there are no superior employees
8 using the logical operation of the operational symbols:
Sql> SELECT * from emp where (sal>500 or job= ' MANAGER ') and ename like ' j% ';
Indicates that the query salary is higher than 500 or the position is manager and the employee hand-written character is J
9 Use the ORDER BY clause:
Sql> SELECT * from emp order by Sal; Indicates that the employee's salary will be sorted from low to high
Sql> SELECT * from emp order BY Sal Desc; Indicates that it will be sorted by employee's salary from high to low
Sql> SELECT * from emp order by deptno, Sal Desc; Indicates that it will sort by department from low to high and that employee wages are sorted from highest to lowest within the department
10: Sort by using the alias of the column.
Sql>select ename,sal*12 "annual salary" from the EMP Order by "annual salary";

Complex query for table (one)
1 "Data grouping----max,min,avg,sum,count function
Sql> select Max (sal), Min (sal) from EMP; Indicates how much the maximum wage and minimum wage are queried
Sql> SELECT * from emp where sal= (select Max (SAL) from EMP); Information that represents the employee who queried the highest wage (this applies to subqueries)
2 "GROUP BY" and "having" clause
Group BY is used to group statistics on the results of a query, and having clauses are used to restrict grouped display results
Warm tip: Having is the group by the good groups again to filter
Sql> Select AVG (SAL), Max (SAL) from the EMP group by DEPTNO; Indicates the average wage and maximum wage for each department
Sql> Select AVG (sal), Min (sal), deptno,job from EMP Group by Deptno,job;
Indicates the average wage and minimum wage for each position in each department
Sql> Select AVG (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) >2000;
Indicates that the average wage of each department to be queried will be screened for departments with average wages above 2000

a summary of the data grouping :
1. The grouping function can only appear in the select list, having, ORDER BY clause;
2. If both group by, have, and order by are included in the SELECT statement, then their order is group By,having,order by
3. In the select list, if there are columns, expressions, grouping functions, then the columns and expressions must have one in the GROUP BY clause, otherwise an error will occur

() Multi-table Query
1 "multi-table
Sql> Select Table1.ename,table1.sal,table2.dname from emp table1,dept table2 where Table1.deptno=table2.deptno; Indicates the name, salary, and company name of the employee (where the name and salary are in the EMP table, the company name is in the Dept table)
2 "Self-connection: refers to the connection query on the same table
Sql> Select Work.ename,boss.ename from emp work,emp boss where Work.mgr=boss.empno;
Indicates the name of the employee and the name of the immediate boss

(13)Sub-query
1 subquery: A subquery is a SELECT statement embedded in another SQL statement, also called a nested query
2 "single-line subquery: Refers to a subquery that returns only one row of data
Sql> SELECT * from emp where deptno= (select Deptno from emp where ename = ' SMITH ');
Represents a query for information about all employees in the same department as Smith
3 multi-row subquery: Refers to a subquery that returns multiple rows of data
Sql> SELECT * from emp where job in (select distinct job from EMP where deptno=10);
Indicates the same employee information as the department Number 10 is queried
Use the all operator in multiline subqueries:
Sql> Select Ename,sal,deptno from emp where sal> (select Max (SAL) from EMP where deptno=30);
Sql> Select Ename,sal,deptno from emp where Sal>all (select Sal from EMP where deptno=30);
The name, salary, and department number of the employee who queried for a salary higher than the salary of all employees in the Department 30
Use the any operator in multiline subqueries:
Sql> Select Ename,sal,deptno from emp where sal> (select min (sal) from EMP where deptno=30);
Sql> Select Ename,sal,deptno from emp where Sal>any (select Sal from EMP where deptno=30);
The name, salary, and department number of the employee who queried for a salary higher than the salary of any employee in the Department 30
4 "Multi-column sub-query:
A single-row subquery refers to a subquery that returns only single-column, single-row data;
Multi-row subquery refers to the return of multiple rows of data in a single column;
A multicolumn subquery is a subquery that returns multiple columns of data
Sql> SELECT * from emp where (deptno,job) = (select Deptno,job from emp where ename = ' SMITH ');
Represents all employee information that is exactly the same as Smith's department and position
5 "Using subqueries in the FROM clause
Find out about employees who are above their average salary:
Step 1: Check the average salary and department number for each department:
Sql>select Deptno,avg (SAL) mysal from the EMP Group by DEPTNO;
Step 2: Consider the query result of step 1 as a sub-table;
Step 3: List the results:
Sql>select * from the EMP table1, (select Deptno,avg (SAL) mysal from the EMP Group by Deptno) Table2 where Table1.deptno=table2. Deptno and table1.sal>table2.mysal;
Tip: When using a query in the FROM clause, the subquery is treated as a view, so it is also called an inline view, and you must specify an alias for the subquery when you use the subquery in the FROM clause

Paging Query (there are three ways for Oracle paging query)
1 "rownum pagination
The first step: first to query out a child table, for example:
Sql>select * from EMP;
Step Two: Add the row ID to the child table:
Sql>select table1.*,rownum rn from (SELECT * from emp) table1;
Warm tip: RN indicates row ID
Step three: Intercept data from line sixth to line tenth
SELECT * FROM (select Table1.*,rownum rown from (SELECT * from emp) table1 where rownum<=10) table2 where rown>5;
Warm tip: Several changes to the query:
1. All changes (show only partial information, sort, etc.) only need to change from the innermost Word table, for example:
Sql> SELECT * FROM (select Table1.*,rownum rown from (select Ename,job,sal from emp) table1 where rownum<=10) table2 where rown>5;
2 "rowID pagination
Sql>select * from T_xiaoxi where rowID in (the Select RID from
(select RowNum rn,rid from (select rowID rid,cid from T_xiaoxi ORDER BY cid DESC)
where rownum<10000) where rn>9980) Order by CID Desc;

Create a new table with query results
Sql>create table MyTable (id,name,sal,job,deptno) as select Empno,ename,sal,job,deptno from EMP;

(+) Consolidated query
1 Union: This operator is used to get the unions of two result sets, and when the operator is used, the duplicate rows in the result set are automatically removed, for example:
Sql> Select Ename,sal,job from EMP where sal>2500 Union select ename,sal,job from emp where job= ' MANAGER ';
2 UNION All: Merge all and do not cancel duplicate rows
3 "intersect: Getting the intersection
4 "Minus: Get the difference set of two results, only get the data that exists in the first set without the second set

working with Java database
1 "How to use the Jdbc_odbc Bridge connection method
public class test{
public static void Main (string[] arge) {
try{
1. Load Driver
Class.froname ("Sun.jdbc.odbc.JdbcOdbcDriver");
2. Get Connected
Connection ct=drivermanager.getconnection ("jdbc:odbc:testsp//Configuration Data Source", "Scott", "Lipeng");

Statement sm=ct.createsstatement ();

Query Total Pages
int pagecount=0; Number of pages
int rowcount=0; There are a few records
int pagesize=0; Show a few lines of records per page

ResultSet rs=sm.executequery ("SELECT * from emp");

while (Rs.next ()) {
System.out.println ("User name:" +rs.getstring (2)); 2 indicates ename in the second column of the table (default starting from 1)

}

Close various open resources
Rs.close ();
Ct.close ();
Sm.close ();

}catch (Exception e) {
E.printstacktrace ();
}
}
}

2 using JDBC to connect to Oracle
public class test{
public static void Main (string[] arge) {
try{
1. Load Driver
Class.froname ("Oracle.jdbc.driver.OracleDriver");
2. Get Connected
Connection ct=drivermanager.getconnection ("jdbc:oracle:thin:@127.0.0.1 (127.0.0.1 is the IP address of the Oracle to be connected) : 1521 (port number for 1521-bit Oracle database): Oracle (Oracle for database instance to connect to)//database URL "," Scott "," Lipeng ");

Statement sm=ct.createsstatement ();

ResultSet rs=sm.executequery ("SELECT * from emp");

while (Rs.next ()) {
System.out.println ("User name:" +rs.getstring (2)); 2 indicates ename in the second column of the table (default starting from 1)

}

Close various open resources
Rs.close ();
Ct.close ();
Sm.close ();

}catch (Exception e) {
E.printstacktrace ();
}
}
}

operating data in Oracle

1. Inserting date values in a specific format
Use the To_date function:
To_date (' 1992/09/01 ', ' yyyy/mm/dd ')

2 Inserting data using subqueries
When you use the VALUES clause, you can insert only one row of data at a time, and an INSERT statement can insert a large amount of data when you use a subquery to insert data. When you process row migrations or load data from external tables to the database, you can use subqueries to insert data. For example:
Sql>insert to XXX (id,name,sal,job,deptno) as select Empno,ename,sal,job,deptno from EMP;

3 using subqueries to update data
When updating data using the UPDATE statement, you can either modify the data directly using an expression or a numeric value, or you can modify the data using a subquery.
sql> update emp Set (job,sal) = (select Job,sal from emp where ename= ' SMITH ') where ename= ' SCOTT ';
Hopefully, Scott's position, salary, and benefits are the same as the Smith staff.

()SQL functions
1 "character function
Lower (char): convert string to lowercase format
Sql> Select lower (Job) from EMP; Represents converting work to lowercase
Upper (char): convert string to uppercase format
Length (char): Returns the lengths of the strings
Sql> SELECT * from emp where length (ename) = 5; Employee information that indicates that only five framed are queried for a name
SUBSTR (Char,m,n): Takes a substring of a string//n means intercept n characters
Sql> Select substr (ename,1,3) from EMP; Represents the first three characters of a truncated name

Sql> Select Concat (substr (ename,1,1), Lower (substr (ename,2,length))) from EMP;
Indicates that all employees ' names are displayed in uppercase letters
Sql> Select Concat (Lower (substr (ename,1,1)), substr (Ename,2,length (ename))) from EMP;
Indicates that all employees ' names are displayed in lowercase letters

Replace (char1,search_string,replace_string) is replaced with a replace_string string in the CHAR1 string
Sql> Select Replace (ename, ' a ', ' a ') from EMP;
Indicates the name of all employees and replaces all "a" with "a"
InStr (Char1,char2,[,n[,m]]) takes a substring in the position of a string

2 "Mathematical functions
Round (n,[m]) This function is used to perform rounding, if M is omitted, rounded to an integer, or if M is a positive number, rounded to the M-bit of the decimal point. If M is a negative number, it is rounded to the M-bit of the decimal point
Trunc (N,[m]) This function is used to intercept numbers. If M is omitted, the fractional part is truncated and if M is a positive number, it is truncated to the M bit of the decimal point, and if M is negative, the first m bit of the decimal point is truncated.
MoD (m,n) m to n modulo (i.e. redundancy)
Floor (n) returns the largest integer less than or equal to n (that is, rounding down)
Ceil (n) returns the smallest integer greater than or equal to n (that is rounding up) ABS (n) returns the absolute value of the number n Select ABS ( -13) from dual;
ACOs (N): Returns the inverse spin value of a number ASIN (N): Returns the inverse value of a number
Atan (N): Returns the inverse tangent cos (n) of the number
EXP (n): Returns the n power of E
Log (M,n) returns the value of the
Power (M,n): Returns the n-th power of M

Special note: Use the dual table when doing Oracle testing

3 Date function
(1) Sysdate: The function returns the system time
Sql> select sysdate from dual; (2) add_months (d,n) This function refers to n months from the time of D and
SQL > select * from emp where sysdate>add_months (hiredate,8);
Find employees who have been in employment for more than 8 months
Sql> select Ename,hiredate from emp where sysdate>add_months (hiredate,120);
Displays the name and date of employment of the employee who has been in service for 10 years. Sql> Select Trunc (sysdate-hiredate) "Number of days in the job", ename from EMP;
for each employee, show the number of days that they joined the company. (3) Last_day (d): Returns the last day of the month in which the specified date sql> select * from emp where (Last_day (hiredate)-hiredate) = 2;
Find all employees who are employed on the 3rd day of the month.

4 "Conversion function
Conversion functions are used to convert data types from one type to another. In some cases, the data type of the Oracle server allow value is different from the actual, when Oracle server implicitly converts the type of conversion data, such as: CREATE TABLE T1 (ID number); INSERT into T1 values (' Ten ') and Oracle will automatically '-->10create table t2 (id varchar2); INSERT into T2 values (1); -So Oracle will automatically 1---> ' 1 '; what we're saying is that while Oracle can do the implicit conversion of the data type, it doesn't fit all, and to improve the reliability of the program, we should use the conversion function to convert To_char:
YY: Two-digit year 2004-->04
YYYY: Four-digit year 2004 mm: two-digit month August-->08dd:2 Digit 30th number of days-->30
Hh24:8 Point--"20hh12:8 Point--" 08
MI, SS--and show minutes \ sec

9: Displays the number and ignores the previous 0
0: Displays the number, if the number of digits is insufficient, then uses 0 to be padded
.: Displays the decimal point at the specified location
,: Displays a comma at the specified location
$: Before the number plus USD
L: Add a local currency symbol before the number
C: Add the International currency symbol before the number
G: Displays the group separator at the specified position,
D: Displays the decimal symbol (.) at the specified position.
Select Ename,to_char (Sal, ' l99g999d99 ') from EMP;

Sql> SELECT * from emp where TO_CHAR (hiredate, ' yyyy ') = 1980;
Show all employees who are in employment in 1980
The To_date function to_date is used to convert a string into a date type of data.

5 "system function
Sys_context:
1) Terminal: identifier of the terminal that the current session client corresponds to 2) Lanuage: Language
3) db_name: Current database name
4) Nls_date_format: the date format that the current session customer corresponds to
5) Session_user: The database user name for the current session client
6) Current_schema: The default scheme name corresponding to the current session customer?
7) Host: Returns the name of the host on which the database is located 7 all sys_context parameters
With this function, you can query some important information, such as which database you are using? Select Sys_context (' Userenv ', ' db_name ') from dual;
Warm tip: Userenv is not to be changed

() data dictionary (that is, static tables and dynamic views of sys users )
1) User_tables: Used to display all tables owned by the current user, it only returns all tables of the user's corresponding scheme
Sql> Select table_name from User_tables;
2) All_tables: Used to display all tables that the current user can access, he will not only return all tables that the current user is disgusted with, but also return tables for other scenarios that the current user can access
Sql> Select table_name from All_tables;
3) Dba_tables: It shows all the database tables owned by the schema, but querying the database dictionary view requires that the user must be a DBA role or have select any table system permissions, for example: when querying the data dictionary view dba_tables with the system user , it will return System,sys,scott ... database tables corresponding to the scenario
Sql> Select table_name from Dba_tables;

4) User name, permissions, role
When a user is established, Oracle stores the user's information in a data dictionary, and Oracle stores the permissions and role information in the data dictionary when the user is granted permissions or roles.
Query Dba_users can display the details of all database users;
By querying the data dictionary view Dba_sys_privs, you can display the system permissions that the user has;
By querying the data dictionary view Dba_tab_privs can display the object permissions that the user has;
Query the data dictionary Dba_col_privs can display the user has the column permission;
By querying the database dictionary view dba_role_privs you can display the roles that the user has
How many kinds of roles does Oracle have?
Sql>select * from Dba_roles;
Querying all system permissions in Oracle, typically DBA
Sql>select * from System_privilege_map order by name;
Querying all object permissions in Oracle, typically DBA
Sql>select distinct privilege from dba_tab_privs;
Querying the table space of a database
Sql>select tablespace_name from Dba_tablespaces;
Query what role the user has
Sql>select * from Dba_role_privs where grantee= ' username '
To see which system permissions a role includes
Sql>select * from Dba_sys_privs where grantee= ' DBA ' (DBA represents a role)
or a
Sql>select * from Role_sys_privs where role= ' DBA ';
To view the object permissions that a role includes:
Sql>select * from Dba_tab_privs where grantee= ' role name '
Displays all data dictionary views that the current user can access:
Sql>select * from Dict where comments like '%grant% ';
Displays the full name of the current database:
Sql>select * from Global_name;

(+) build table
1 "PRIMARY key: Create Talbe customer (customerId char (8) Primary key,--primary key
2 "FOREIGN key: Create TABLE Purchase (CustomerId char (8) References customer (CUSTOMERID),--foreign key
3 "Other constraints are written directly behind the defined variables
4. Set default options and several options: Sex char (2) Default ' male ' Check (sex in (' Male ', ' female '))

Oracle Basic Command Letter