Oracle Learning Notes (i)

1.oracle sign-in and logout

Go to cmd command line:

Login to normal user--

1) sqlplus scott/169500 Note: Scott is the user name (this is the Oracle comes with the user) 169500 is the password

2) Sqlplus drawdown

Enter user name: Scott

Enter Password: 169500

3) Sqlplus Scott

Enter Password: 169500

Login administrator (with all permissions)

Go to cmd command line:

Sqlplus AS/SYSDBA Drawdown

Exit: Exit or press CTRL + C

2. Change the user's password

Login Administrator Mode--

Unlock users:

Alter user username account unlock;

Lock User:

Alter user username account lock;

Change Password:

Alter user username identified by new password;

To modify the administrator password:

Alter user sysidentified by new password;

3. View all tables for the current user

SELECT * from tab;

4. Querying data in an EMP table

SELECT * from EMP;

The column name specified by select from EMP;

5. Change Linesize and PageSize

The structure of the SELECT * from emp;--> table is not obvious so we can change it to make it easier to see:

Linesize: Width in the command line

PageSize: Sets the number of bars to display per page

Show Linesize;show pagesize;--> can view the system default settings

Modified-->set linesize 100; Set pagesize 20; Modify width to 100 change the number of bars displayed per page to 20

However, this change is only valid when the current session scope is active when you close or exit the query again becomes the system default, so you can set a permanent modification for convenience

Locate the installation directory for Oracle: E:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\slogin.sql added

Set Linesize 100; Set pagesize 20;

6. Controlling null and handling of NULL

Select Comm from emp;--> can see that there are no bonuses on the line default is null we can modify it to 0

Select NVL (comm,0 ) from EMP;-->NVL (column name, value) if the column name is null, modify it to the corresponding value

7. Change the alias displayed

Select Ename Name Form emp;--> Display column named name

Select Ename as "Li Wei Kang" from Emp;--> if the alias has special characters must use double quotes Note: The AS keyword can be omitted

8. Using Connectors | |

Select Ename | | ' The salary is ' | | Sal from EMP;

The query results are:

9. Remove Duplicate rows

--Acting on a column
SELECT distinct job from EMP;
--Acts on multiple columns, and the values of all the columns are repeated together to count the duplicate records
Sql> SELECT DISTINCT job, deptno from EMP;

10. Virtual Table Dual

If you query only one expression and no data is used for any table, you must also write from ...

Select 3+2 from dual;

Select ' Hello ' | | "World" from dual; You can also use the functions provided by Oracle Concat (",") Select Concat (' Hello ', ' world ') from dual;

11.like between in

1) The LIKE keyword fuzzy query condition: When you use like, you can use% and _ to represent any number of any character or any character, respectively.

SELECT * from emp where ename like '%th '

To express% or _ itself, you need to use an escape character, for example: SELECT * from emp where ename like ' ki\%% ' escape ' \ ';

2) between

Between
Consists of two boundaries.
Must be a small value to write to the front, the large value is written to the back, otherwise there is no result.

3) in keyword

If in (...) the parentheses can be null

Example: Query all employees who are managers
SELECT * from EMP where empno in (select Mgr from EMP);

If no in (...) parentheses if there is a null query result with no result

Example: Query all employees who are not managers
SELECT * from EMP where empno not in (the Select Mgr from EMP where Mgr are NOT NULL); here must write where Mgr is not null--> otherwise no result record!!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Learning Notes (i)