Oracle BASICS (2) --- operation commands, oracle basics --- commands

Oracle BASICS (2) --- operation commands, oracle basics --- commands

Next, I will introduce the basic overview of Oracle. The following describes the database operation commands.

Common SQL * plus commands

Connection command

1. conn [ect]

Usage conn username/password @ network service name [as sysdba/sysoper] When connecting with a privileged user identity, it must contain as sysdba or as sysoper

File Operation commands

1. start and @

Run SQL script case SQL> @ d: \ a. SQL or SQL> start d: \ a. SQL

2. edit

You can edit the SQL script SQL> edit d: \ a. SQL.

3. spool

This command can output the content on the SQL * plus screen to the specified file. Case: slq> spool d: \ B. SQL and enter SQL> spool off

Display and set Environment Variables

Set show can be used to control various input formats. If you want to permanently Save the settings, you can modify the glogin. SQL script.

1. linesize: Set the display line width. The default value is 80 characters.

SQL> show linesize

SQL> set linesize 90

Pagesize: Set the number of rows displayed on each page. The default value is 14 sets pagesize 8.

2. Create a user

Create user can be used only when they have dba permissions.

SQL> create user xiaoming identified bym123 name starts with a letter

3. Change password self-password SQL> password username System can be modified

4. You cannot delete a user by yourself. drop user. If you have created a table for a user to delete a table, you must include the cascade parameter.

5. User Management

Add logon permissions for new users: connect to SQL> conn User Name

Grant permissions SQL> grant connect to xiaoming

Permissions: system permissions, user-related permissions on the database, logon, password modification, and so on; object permissions: User-access permissions on Data Objects of other users.

Role: Custom role; predefined role; Dba role

Resourse any table creation in the tablespace

Create table SQL> create table test (userID varchar2 (30)

Object permission types: Select, insert, update, delete. alll, createindex

Authorization: Grant select on emp to xiaoming

SQL> conn xiaoming/m1234

SQL> select * from scott. emp;

Grant all on emp to xiaoming

Revoke permission: Revoke select on emp from xiaoming

Maintenance of permissions and transfer of Permissions

If it is an object permission, add with grand option, for example: Grant select on emp to xiaoming with grantoption

Create user

SQL> create user xiaohong identified bym123

SQL> grant connect on xiaohong

SQL> conn xiaoming/m123

SQL> grant select on scott. emp toxiaohong

For system Permissions

Grant connet to xiaoming with admin option

Xiaoming can assign permissions to another user.

Use profile to manage user passwords (a set of commands for password restrictions and resource restrictions)

1. account locking

Limits on the number of logins

SQL> create profile lock_account () Rule name limistfailed_login_attempts 3 password_lock_time 2 (days );

SQL> alter user teaprofile lock_account;

2. Unlock the user

SQL> alter user tea account unlock;

3. Termination Password

Dba identity SQL> create profile myprofile limitpassword_life_time10 password_grace_time2;

Change the password every 10 days. 2-day delay

SQL> alter user tea profile myprofile.

Password history: the previous password cannot be used:

Create profile: SQL> createprofile password_historylimitpassword_life_time 10password_grace _ time2password_reuse_time 10 note: Password_reuse_time can be repeated in 10 days

4. delete a profile

SQL> drop profilepassword_history [cascade] (cascade)

Oracle table management

1. Data Type

Character type: the Char length cannot exceed 2000 characters

Varchar2 (20) variable length, up to 4000 characters

Clob (character large object) large object Type up to 4 GB

Numeric type

Number ranges from-0 to-38 to the power of 10.

Number (5, 2) indicates that a decimal place has a valid Number of five digits. The range of two decimal places is-999.99-999.99.

Number (5) indicates a five-digit integer-99999-99999

Date type

Date includes year, month, day, hour, minute, second Timestamp Extension

Image: Blob binary data, which can store pictures and sounds for 4 GB

2. Create a table:

SQL> create table users (usernamechar (200 ),

SQL> create table classes (classId

SQL> dec

3. Add Fields

SQL> alter table student add (classidnumber (2 ));

4. Modify

SQL> alter table student modify (xmvarchar (30 ));

5. Delete

SQL> alter table student drop column sal;

6. Change the name

SQL> rename student to stu;

7. delete a table

SQL> drop table student;

8. add data

Insert into student values ('a001', 'zhang san'); default time format: dd-mon-yy 09-6-99

Alter session setnls_date_fomart = 'yyyy-mm-dd'

Add data after modification

Insert into student values ('a002 ', 'Mike', 'mal', '2017-1905 ');

9. insert some fields

Insert into student (xh, xm, sex) values ('a003 ', 'john', 'female ");

10. Insert null values

Insert into student (xh, xm, sex, birthday) values ('a004 ', 'Mat', 'mal', null)

11. Change a field

Update student set sex = 'female"

12. storage point: SQL> savapoint aa; SQL> rollback to aa; rollback

13. delete data

Delete from student

Delete all records, the table structure is still in progress, logs are written, and can be recovered. The speed is slow.

Drop table student Delete table structure and data

Delete from student where xh = 'a001'; Delete a piece of data

Truncate table student;

Delete all records in the table. The table structure is still in progress. If you do not acknowledge the log, it cannot be retrieved, and the speed is fast.

14. view the table structure: SQL> desc dept

Oracle Query

1. How to cancel duplicate rows

Select distinct deptno, job from emp;

2. Copy

SQL> nstert into users (userid, username, userpss) select * from users

3. Use arithmetic expressions

4. Use the column alias: Select ename "name", en fromemp; alias

SQL> select name from emp

5. How to Handle null

Nvl (comm, 0) is used. If comm is null, 0 is used.

6. How to connect a string (|)

Select ename | 'is a' | job form emp;

7. Use where,

SQL> select ename, sal from emp wheresal> 3000; sal> = 2000 and sal | <= 2500

8. Use like

Select ename, sal from emp where emp whereename like '_ o %'

9. Use the in clause in the where clause

Select * from emp where empno in (11,234,456 );

10. Use the is null operator

Select * from emp where mgr is null;

11. Use logical operators: Use order by and sort by column aliases

12. Paging Query

Group by is used to Group statistics on query results.

The Having clause is used to limit the display results of a group.

13. Multi-Table query

Sort by department

SQL> select ?,?,?, From empa1, dept a2 where a1.deptno = a2.deptno order by a. deptno;

The group by field must be included in the queried field.

14. Self-join: queries links in the same table

Select worker. name. boss. name from emp worker, emp boss where worker. mgr = boss. empno workere. name = 'frode ';

15. Single Row subquery

Select * from emp where deptno = (select ...); Data is executed from left to right during SQL Execution.

16. multi-row subquery

Select ename, sal, dept from emp wheresal> all (select sal form emp where deptno = 30 );

Any: Select ename, sal, dept from emp wheresal> any (select sal form emp where deptno = 30 );

17. Multi-column subquery

Select * from emp where (deptno, job) = (selectdeptno, job from emp where name = '');

18. Paging Query

Right subquery

Select a1. *, rownum rn from (select * from emp) al;

There are three methods

1. rownum Paging

Select * from emp

Select * from (Select a1. *, rownum rn from (select * from emp) alwhere rownum <= 10) where rn> 6;

2. Display rownum

Select al. *, rownum rn form emp

19. Create a table and import other table data to the new table

Create table mytable (id, name, sal, job, deptno) as select empno, ename, sal, job, deptbo from emp;

20. Merge Query

1 union gets the union of two result sets and removes duplicate rows automatically.

Select ename, sal, job from emp wheresal> 2500 union select ename, sal, job from emp where job = 'manager ';

2 union all is the same as Union, but duplicate rows are not canceled and not sorted.

Select ename, sal, job from emp wheresal> 2500 union all select ename, sal, job from emp where job = 'manager ';

3 minus merge Query

Get the difference set of the two result sets. Only the first set exists, but no data in the second set exists.

Select ename, sal, job from emp wheresal> 2500 minus select ename, sal, job from emp where job = 'manager ';

The above are basic operation commands. At first I thought a lot of them, but later I thought they were the same as SQL statements of SQL Server. So there is much less to learn. However, there are a lot of such things, so I'm so lazy to remember, when can I come back and look at it? It's just a coincidence that these commands, especially database queries, can be written, this will affect the data query performance.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.