First section, Oracle Installation and database and table creation, knowledge collation

1.1oracle installation omitted

2.1sql*plus Connection Database

Enter "C:\sqlplus/nolog" into the DOS interface

Sql>conn System/[email protected]

Sql>select * from Scott.emp;

Connect to specified database: Sqlplus username/[email protected]:p Ort/sid

After entering Sql*plus, you can use Conn to connect to other users, such as: Conn Sys/admin as Sysdba

3.1 Creating a Database

There are two ways to create a database, a graphical interface, and a command environment that uses the Create Databases command.

3.2 Creating a table space

The tablespace is unique to Oracle, which is physically composed of one or more data files, and logically the table space is the largest logical unit in the database, with at least one table space for an Oracle database.

Classification of table spaces

(1) Permanent table space: Data of tables, views, processes, indexes, etc. are generally maintained. The SYSTEM, Sysaux, USERS, and example table spaces are installed by default.

(2) Temporary tablespace: data that is used to hold short-term activities in the system, such as sorting data.

(3) Undo tablespace: Used to help return transactional data for submission

first, the database creation:1, using the Database Configuration Assistant (DBCA,DatabaseConfiguration Assistant) graphical Way of2, after the creation is complete, locate the D:\oracle\product\11.2.0\dbhome_1\network\admin Tnsnames.ora Listening configuration file open Configure the database name and SID for the new database3, and then restart Pl\sql login to create a temporary tablespace: (Use the default temporary tablespace if not created)Create TemporaryTablespace TEMP_HJB Tempfile'temp_file.dbf'size 10m; Modify table space name:AlterTablespace TEMP_HJB Rename tohjb_temp; Create a permanent tablespace: (Use the default persistent tablespace if not created)CreateTablespace hjb_data datafile'hjb_data.dbf'size 10m; Modify table space (online or offline) status:AlterTablespace Hjb_data Online|offline; View the file name of the tablespace:Select file_name  fromDba_data_fileswhereTablespace_name= 'Hjb_data'; Add a delete tablespace data file (to capitalize the tablespace name):AlterTablespace Hjb_dataAdd/DropData_file'hjb_data.dbf'; iv. Create User: (also specify both its temporary tablespace and the Permanent table space)Create UserWBS identified bywbs123 Account Unlockdefaulttablespace HJBTemporarytablespace hjb_temp; Delete User:Drop UserHjb_wbs; Grant to the newly created userGrantConnect,resource toWbs-Grant Connect,resource permissions to new usersGrantDba toWbs-To delegate DBA authority to a new user note:GrantXxx to User, Grant is an authorization, where XXX can be a role or DBA: With full privileges, the system has the highest privileges, only the DBA can create the database structure. RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure. Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure. For normal users: Grant Connect, resource permissions. For DBA administration users: Grant Connect,resource, dba authority. 

3.3 Creating Table details Knowledge

Creating tables: Create TABLE tablename (Stuname varchar2 () not NULL, Stuno number (5,0) is not null)

Alert Table command: Alert table tableName Modify (column definition ...);

Add new column: Alert table tableName Add (column definition ...);

Delete Column: Alert table tableName drop column columnName;

TRUNCATE TABLE command: Removes only all row data from the database, does not delete the table structure, and frees the storage space used by this table. TRUNCATE TABLE tableName;

Desc tableName: View table structure

3.4 Data integrity

Data stored in a database requires that each row of data reflect a different entity, with indexes, unique constraints, and primary keys that enable data integrity of the datasheet

Create PRIMARY key: minimum, stability. The minimum one column is faster than multi-column manipulation of the primary key, but the combination of two integer types is faster than a large column of character types. Stability requires that primary key data not be updated frequently

Add Constraint: Alert table indicates the constraint type specific to the ADD constraint constraint name constraint type

Alert table Stuinfo Add constraint Pk_examno primary key (EXAMNO);--Add PRIMARY KEY constraint

Alert table stuinfo Add constraint Uq_examno unique (EXAMNO);--Add UNIQUE constraint

Alert table stuinfo Add constraint Fk_examno foreign key (Examno) references Stuinfo (EXAMNO);--Add FOREIGN KEY constraint

Alert table stuinfo Add constraint ck_stuage check (stuage between 40);--Add Check Constraint

Delete constraint: Alert table tableName drop constraint Pk_examno;

View constraints: SELECT * FROM user_constraint WHERE table_name = ' stuinfo ';

3.5 Naming conventions

Tablespace: Tbs_xxx table: tbl_xxxx primary key: Pk_ XXXXX foreign key: fk_xxxxx

First section, Oracle Installation and database and table creation, knowledge collation