oracle-01-Database Classification/oracle sql*plus common commands

I. Classification of databases

I. Classification of databases
1. Small database: Access, Foxbase
2. Medium database: Informix, SQL Server, MySQL
3. Large database: Sybase, DB2, Oracle

Second, how to reasonably use the database in the project, can be based on the following three aspects to start
1, the size of the project
A, how much load, that is, the number of users
B, Cost
C, security

eg
Small database
1, the load is small, the user about 100 people within
For example: Message board, information management system
2, the cost within thousand yuan
3, the security requirements are not high

Medium Database
such as load daily traffic 5000~10000
Cost is within million yuan
Like business websites.

Large database
Massive loads to handle massive amounts of data (SYBASE<ORACLE<DB2 (mass data processing capability))
High security, relatively expensive

Ii. Oracle SQL plus common commands

One, sys users and system users
The Oracle installation automatically generates SYS users and system users
(1), sys user is superuser, has the highest privileges, has the SYSDBA role, has the rights to create database, the user's default password is Change_on_install
(2), the system user is the management operator, the authority is also very large. With the Sysoper role, without the CREATE DATABASE permission, the default password is the manager
(3), generally speaking, the database maintenance, using the system user login can pull
Note: The biggest difference between SYS and system two users is that there is no permission to create database.

Ii. Introduction of Sql*plus Tools
Sql*plus is Oracle's own tool software, primarily used to execute SQL statements, Pl\sql blocks.
The operation is as follows:
(1), in the D:\dev\oracle\product\10.2.0\db_1\bin\ directory of the Sqlplusw.exe. (D:\dev\oracle\ for Oracle installation directory)
(2), enter "SQLPLUSW" in the Run bar to

Three, Sqlplus DOS tool introduction
(1), overview: Sqlplus is the tool that operates Oracle under DOS, its functions and sql*plus are similar.
(2), operate as follows: Enter "Sqlplus" in the Run bar to

Four, Plsql developer tools, need to install themselves, recommend everyone to use

V. Sql*plus COMMON Commands
1), connect command
1.conn[ect]
USAGE: Conn User name/password @ NETWORK SERVICE name [as Sysdba/sysoper]
Note: When connecting with a privileged user, you must bring as SYSDBA or as Sysoper
eg
Sql> Show User
USER is "SCOTT"
Sql> Conn system/[email protected]
is connected.
Sql> Show User
USER is "SYSTEM"
Sql>
The above command achieves the same effect as switching users
2.disc/disconn/disconnect
Description: This command is used to disconnect from the current database
3.pssw[ord]
Note: This command is used to modify the user's password, and if you want to modify another user's password, you need to log in with Sys/system.
eg
Sql> Conn Scott/oracle
is connected.
Sql> PASSW
Change SCOTT's password
Old Password:
New Password:
Retype the new password:
Password has changed
Sql>
4.show User
Description: Displays the current user name
5.exit
Description: The command disconnects from the database and exits the Sql*plus
5.clear screen
Clear Screen

2), File Operation command
1.start and @
Description: Run SQL script
Case: sql>@ d:\a.sql or Sql>start d:\a.sql
2.edit
Description: The command can edit the specified SQL script
Case: Sql>edit D:\a.sql, this will open the D:\a.sql file.
3.spool
Note: This command can output the contents of the Sql*plus screen to the specified file.
Case: Sql>spool d:\b.sql and input sql>spool off
eg
Sql>spool D:\b.sql;
Sql>select * from EMP;
Sql>spool off;

3), interactive commands
1.&
Note: You can override a variable, which needs to be entered by the user when it executes.
SELECT * from emp where job= ' &job ';

4), display and set environment variables
Overview: Can be used to control the output of various formats, set show if you want to permanently save the relevant set
You can modify the Glogin.sql script.
1.linesize
Description: Sets the width of the display line, which is 80 characters by default
Show Linesize
Set Linesize 90
2.pagesize Description: Sets the number of rows displayed per page, default is 14
Usage is the same as linesize.
The use of other environmental parameters is similar

oracle-01-Database Classification/oracle sql*plus common commands