Oracle simple example

Predefined roles:

1. Connect role: Typical and basic rights granted to end users
Alter session -- modify a session
Create Cluster -- create a cluster
Create database link -- create a database link
Create sequence -- create a sequence
Create session -- create a session
Create synonym -- create a synonym
Create View -- create View

2. Resource role: Is granted to developers
Create Cluster -- create a cluster
Create procedure -- creation process
Create sequence -- create a sequence
Create Table -- create a table
Create trigger -- create a trigger
Create type -- create type

3. DBA role: all system-level permissions of the system

4. imp_full_database and exp_full_database:
Backup any table -- back up any table
Execute any procedure -- execute any operation
Select any table -- Query any table

5. delete_catalog_role:
Grant this role to the user to delete records from the SYS. AUD $ table,
The sys. AUD $ table records the audited records. This role can be used to simplify audit trace management.

6. select_catalog_role and execute_catalog_role:
The select_catalog_role role has the right to query data dictionaries,
The execute_catalog_role role has the right to execute some processes and functions from the data dictionary.

SQL * Plus Basics
In the previous post, we learned some basic Oracle operations, such as creating, authorizing users, and creating databases. In the Visual Window environment of the OEM (Oracle Enterprise Manager), although we can do these things very conveniently, in fact, it is more efficient to write in the SQL language in development !! SQL * Plus provided by Oracle is a good tool. If you like the development environment of Windows, use sqlplus worksheet! Let's talk about the basic West!

The Structure Query Language (SQL) is a structured query language and the core language of a database. It is a set-oriented descriptive non-procedural language.
There are four types of SQL languages: dql, DML, DDL, and DCL.

1. The basic structure of the Data Query Language dql is a query block consisting of the select clause, from clause, and where clause:
Select <field name table> from <Table or view Name> where <query condition>

2. Data manipulation language (DML ):
Insert Add row to table
Delete Delete data rows from a table
Update Change table data

3. Data Definition Language DDL defines the structure of the database, including the database itself, data tables, directories, views, and other database elements (below are some common DDL statements)
Create Table Create a table
Create Index Create an index
Create View Create View
ALTER TABLE Add a table column, redefine the table column, and change the storage allocation
Drop table Delete table
Drop Index Delete Index

4. The Database Control Language DCL is used to grant or revoke certain privileges for accessing the database, control the time and Effect of database operations, and monitor the database. For example:
Grant Grant permissions or roles to users or other roles
Revoke Revoke User Permissions
Roll Rollback is the protection action taken by Oracle when a dialog changes the data in the database and the user does not want to submit the change for some reason. This is a state that restores information to the last submitted state before update, insert, and delete.
Commit Submit. When you insert, delete, and modify a database, it is only after the transaction is committed to the database. Only the database owner can see the transaction before it is committed, only after the final submission is complete can others see it.

Next, let's take a look at SQL * Plus to lay a solid foundation for what we will do below.
After logging on to SQL * Plus using system, we will perform the following operations (this time, no details are provided)
SQL> create user maxuan identified by Max; # create a maxuan user with the max password
SQL> grant connect, resource to maxuan; # authorize maxuan
SQL> conn maxuan/max; # connect with your maxuan
SQL> Create Table Test (a number); # create a table named test. Only one column with the field name a is created, and the data type is Numeric.
SQL> insert into test values (1); # insert a record
SQL> select * from test; # query record. The first row of column A is 1.
SQL> Update Test Set A = 2; # change the record. At this time, the first row of column A has been changed to 2.
SQL> commit; # submit
SQL> Delete from test; # delete all records in the test table. At this time, there are no records in the test table.
SQL> Roll; # query the test table before rolling back to submit. The value of the first row of column A is returned to 2.

Oracle Data Type
When creating a data table in a database, we need to define the types of all fields in the table. The data types include character, numberic, date, lob, and raw, these are the most basic data types. Of course, you can also customize Data Types in Oracle!

Character data types provided in Oracle:
Char (<size>:A fixed-length string with a maximum length of 2000 bytes. If the length is not specified, the default length is 1 byte.
Varchar2 (<size>:A variable-length string with a maximum length of 4000 bytes. It indicates the maximum length. This type can contain numbers, letters, and ASCII character sets (or character sets accepted by database systems such as ebcdic) all symbols in. If the data length does not reach the maximum value, Oracle automatically adjusts the Field Length Based on the Data size. Is the longest data type.
Nchar (<size>:A character set-Based String of a fixed length up to 2000 bytes.
Nvarchar2 (<size>:A variable-length string based on character sets. The maximum length is 4000 bytes.
Long:Variable long character column. The maximum length is 2 GB. It is used for long string data that does not need to be searched by string. This type is a legacy data type that will not be supported in the future. It is gradually replaced by blob, clob, nclob, and other big data types.

The numberic data type is used to store negative and positive integers, scores, and floating point data. The numberic data type provided in Oracle:
Number (<m>, <n>:A Variable Length numeric column allows 0, positive, and negative values. m is the number of digits of all valid numbers, and N is the number of digits after the decimal point.

Date data types provided in Oracle:
Date:The default format is dd-mon-yy (day-month-year)

Lob data types provided in Oracle:
Blob, clob, and nclob:Three large objects (LOB) are used to save large image files or formatted text files, such as Word documents and non-text files such as audio and video files. The maximum length is 4 GB. Dizzy data is stored in the database.
Bfile:The maximum length of a large binary object file stored outside the database is 4 GB. This type of LOB is used to record changes in the database, but the data is stored outside the database.

Raw data types provided in Oracle:
Raw (<size>:Variable-length binary data. The maximum length must be specified when a field is defined. This format is used to save a small image file or a text file with a format. It is also an old data type, will be replaced by the lob data type.
Long raw:Variable-length binary data, with a maximum length of 2 GB, can be used to save large graphics or formatted text files, as well as non-text files such as audio and video files, this is also an old data type, which will be replaced by the lob data type.

Other data types:
Rowid:This is a pseudo-example in the Oracle data table. It is the unique identifier of each row of data in the data table.
Integer:Integer type

Create a shopping website background database

Now, let's go back to the theme of developing a shopping website using the J2EE system and start to build the background database of the shopping website.
To implementBasicTo create four tables: product list, item, orders, and Admin ). The table structure is as follows:

Item table structure (item type table)
Field name Data Type Null allowed Primary Key/foreign key Remarks
Type_id INTEGER (automatic number) No Primary Key Product Category ID tag
Type Varchar2 (30) No Product category name

Product table structure (product list)
Field name Data Type Null allowed Primary Key/foreign key Remarks
Product_id INTEGER (automatic number) No Primary Key Product ID mark
Title Varchar2 (30) No Product Name
Type_id Integer No Foreign key Product Category mark
Info Varchar2 (80) Yes Product Introduction
Price Number (16, 2) No Product Price

Orders table structure (Order List)
Field name Data Type Null allowed Primary Key/foreign key Remarks
Order_id INTEGER (automatic number) No Primary Key Order ID mark
Name Varchar2 (20) No CUSTOMER NAME
Address Varchar2 (100) Yes Shipping address
Tel Number (16) Yes Contact number
Email Varchar2 (30) No Contact email
Btime Date Yes Order Date
Product_id Integer No Foreign key Product tag
Uword Varchar2 (100) Yes Customer message

Admin table structure (Administrator List)
Field name Data Type Null allowed Primary Key/foreign key Remarks
Admin_id INTEGER (automatic number) No Primary Key Administrator ID tag
Adminname Varchar2 (20) No Administrator name
Password Varchar2 (20) No Administrator Password

After designing the table structure, we will start to create it.
It is no longer difficult to create a table. Note the association between the three tables "product", "item", and "orders", as well as the automatic number.

The following is a complete SQL statement. I will give a detailed description later. You can compare it with the input in SQL * Plus or save it as an SQL script file, run the command in SQL * Plus or sqlplus worksheet. Of course, you can also copy the Code directly to SQL * Plus for execution!

Rem // By maxuan start ///

Create Table item (
Type_id integer not null,
Type varchar2 (30 ),
Constraint item_pk primary key (type_id)
);

Create Table product (
Product_id integer not null,
Title varchar2 (30) not null,
Type_id integer not null,
Info varchar2 (80 ),
Price number (16,2) not null,
Constraint product_pk primary key (product_id ),
Constraint product_fk foreign key (type_id) References item (type_id)
);

Create Table orders (
Order_id integer not null,
Name varchar2 (20) not null,
Address varcharacter 2 (100 ),
Tel number (16 ),
Email varchar2 (30) not null,
Btime date,
Product_id integer not null,
Uword varchar2 (100 ),
Constraint orders_pk primary key (order_id ),
Constraint orders_fk foreign key (product_id) References product (product_id)
);

Create Table admin (
Admin_id integer not null,
Adminname varchar2 (20) not null,
Password varchar2 (20) not null,
Constraint admin_pk primary key (admin_id)
);

Create sequence type_id increment by 1 start with 1;
Create sequence product_id increment by 1 start with 1;
Create sequence order_id increment by 1 start with 1;
Create sequence admin_id increment by 1 start with 1;

Rem // By maxuan end ///

Description 1: create association between tables
The product, item, and orders tables are associated through the public domain, usually called the key field. There are two types of keys: primary key) and the external key (foreign key ). The primary key keeps the data rows in the table unique. In the product table, product_id is the primary key, and the table orders also contains product_id. In this case, product_id is the external key. The external key of a table obtains information from other tables. Check the preceding SQL statement! The SQL statement for creating a primary or foreign key is described as follows:
Constraint primary key name primary key (field name)
Constraint external key name foreign key (field name) References corresponding table (corresponding field)
If the primary key name or external key name is not specified, the system uses the default name!
If you want to delete a primary key or external key, use the following syntax:
Alter table Table Name drop constraint primary key name or external key name
If the primary key/unique keyword is referenced by the external key, you must delete the external key before deleting the primary key !!

NOTE 2: automatic numbering
There are automatically numbered Data Types in access, and both MSSQL and MySQL have auto-increasing data types. When you insert a record, you do not need to operate on this field, and the data value is automatically obtained, oracle does not have an auto-increasing data type. We need to create an auto-increasing serial number. When inserting a record, we need to assign the next value of the serial number to this field. It is foreseeable that this function is available, we can migrate data from access, MSSQL, or MySQL to Oracle!
Create sequence type_id increment by 1 start with 1;
In this sentence, type_id is the name of the serial number. Each time it increases to 1, the Starting sequence number is 1.
If you want to delete a sequence, use the drop sequence name !!