Go on continues to the advanced level !! (I personally think this post is very helpful for beginners. Please read it carefully. Due to time rush, please correct me if there is any error)
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 to Add rows to the table
Delete Delete A data row from a table
Update changes 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 index create 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 permissions or roles to users or other roles
Revoke revokes User Permissions
Roll rollback is a protection operation performed 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. 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
L> 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. The maximum length is specified in the definition, this type can contain all characters in numbers, letters, and ASCII character sets (or character set standards accepted by database systems such as ebcdic. If the data length does not reach the maximum value, Oracle automatically adjusts the Field Length Based on the Data size. Yes
The longest data type.
Nchar (<size>): a fixed-length String Based on the character set. It consists of a maximum of 2000 bytes.
Nvarchar2 (<size>): a variable-length String Based on the character set. It consists of a maximum of 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 Value column. values 0, positive, and negative are allowed. 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: A large binary object file stored outside the database. The maximum length is 4 GB. This type of LOB is used to record changes in the database, however, data is stored outside the database.
Raw data types provided in Oracle:
Raw (<size>): variable-length binary data. You must specify the maximum length when defining a field. This format is used to save small image files or formatted text files, it is also an old data type that will be replaced by the lob data type.
Long raw: variable-length binary data with a maximum length of 2 GB. It can be used to save large graphics or text files with formats, 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-sample 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 implement the basic functions of a shopping website, we need to 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 allow null primary key/foreign key remarks
Type_id INTEGER (automatic number) No primary key item category ID tag
Type varchar2 (30) No product category name
Product table structure (product list)
Field Name Data Type allow null primary key/foreign key remarks
Product_id INTEGER (automatic number) No primary key item ID mark
Title varchar2 (30) No Product Name
Type_id integer no foreign key product category tag
Info varchar2 (80) is product introduction
Price number () No product price
Orders table structure (Order List)
Field Name Data Type allow null 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) is the shipping address
Tel number (16) is the contact number
Email varchar2 (30) No contact email
Btime date is the order date.
Product_id integer no foreign key product tag
Uword varchar2 (100) is the customer's message
Admin table structure (Administrator List)
Field Name Data Type allow null primary key/foreign key remarks
Admin_id INTEGER (automatic number) No primary key administrator ID mark
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 Code Directly copy to SQL * Plus for execution!
Code copy box
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 //
[Ctrl + A select all and then copy]
description 1: create association between tables
three tables, product, item, and orders, are joined through a public domain, usually called a key field. There are two types of keys: the 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!
description 2: automatic numbering
data types with automatic numbering in access, 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 a data type that increases automatically. Therefore, you need to create a serial number that increases automatically, when inserting a record, you need to assign the next value of the serial number to this field. It is foreseeable that with this function, 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.
Well, our database has been built and some basic related knowledge has been learned from it. This is the end of my Oracle article on the J2EE-based shopping website, if you have any questions, please leave a message !!
in the next article WebLogic, I will continue to share my painstaking efforts and hope you will get some benefits !! Thank you for your support!
PS: I'm so tired of writing that I'm about to catch up with my book !!
