Real-j2ee-Development shopping site (iv)

Java EE go on continue advanced!! (This post is personally thought to be helpful to beginners, please look carefully.) Due to time haste, please correct me if there is any mistake.

sql*plus Foundation
In the previous post, we mastered some basic Oracle operations, such as creating, authorizing users, creating databases, and so on. In the Visual window Environment of OEM (Oracle Enterprise Manager), although we can do these things conveniently, in fact, writing in SQL language is more efficient in development!! Oracle provides Sql*plus is a good tool, if you like the development of the environment of the window, with Sqlplus worksheet also do! Here are some basic CC!

sql (Structure query Language) language is a structured query language, is the core language of the database, and is a descriptive and non procedural language oriented to the collection.
sql language is divided into four categories: Data Query Language DQL, Data manipulation language DML, data definition language DDL, Database control Language DCL.

1. The basic structure of a data query language DQL is a query block consisting of a SELECT clause, a FROM clause, a WHERE clause:
select < field name table > from < table or view name > where < query conditions >

2. Data manipulation language DML completes the task of determining, modifying, adding, and deleting a data value in the database (the following are some commonly used DML statements):
insert increase data row to table
delete Delete rows of data from a table
update change data in a table

3. Data definition Language DDL finishes defining the structure of the database, including database elements such as the database itself, data tables, directories, views, and so on (the following are some common DDL statements)
create table to create tables
create Index Creation
create View Create views
alter table column, redefine table column, change storage allocation
drop Table Delete Tables
drop Index Delete Indexes

4. The database control Language DCL is used to grant or reclaim certain privileges for accessing the database, and to control the time and effect of database manipulation transactions, and to monitor the database. Such as:
grant grant a permission or role to a user or other role
revoke Reclaim User Rights
roll rollback is the protection action that Oracle takes when a conversation changes the data in the database, and for some reason the user does not want to commit the change. This is a status that restores the information to the final submission before the user makes the update, insert, delete.
commit submitted. In the completion of the database insert, delete and modify operations, only when the transaction submitted to the database is completed, there is only the operation of the database before the I can see, others only in the final submission to be completed before they can see.

The next, we in the Sql*plus in combat, for we will do below lay a good foundation.
With system login to Sql*plus, we do the following operations (this time no screenshots, there are detailed instructions)
sql>create user Maxuan identified by Max; #创建口令为max的用户maxuan
sql>grant Connect,resource to Maxuan; #为用户maxuan授权
sql>conn Maxuan/max; #以用户maxuan进行连接
l>create table Test (a number); #建立一个名为test的表, only a column with a field name of a, with a data type of numeric
sql>insert into test values (1); #插入一条记录
sql>select * from Test; #查询记录, at this time the first act of column a 1
sql>update test set a=2; #更改记录, at this point the first row of column A is changed to 2
sql>commit; #提交
sql>delete from Test; #删除test表中所有的记录, there are no records in the test table at this time
sql>roll; #回滚到提交前, the test table is queried again, and the first row of column A returns to 2

Data type of oracle

When creating a data table in a database, we need to define the types of all the fields in the table, and the data types are roughly divided into: Character,numberic,date,lob and raw, which are the most basic data types. Of course, the custom data type is also allowed in Oracle!

Character data types provided in Oracle:
char (<size>): A fixed-length string with a maximum length of 2000 bytes, and a default of 1 bytes long If you do not specify a long charge.
varchar2 (<size>): A variable-length string with a maximum length of 4000 bytes, specifying the maximum length when specified, this type can be placed in numbers, letters, and ASCII character sets (or EBCDIC, such as database systems accepted) All the symbols in. If the data length does not reach the maximum, Oracle automatically adjusts the length of the field based on the data size. is the longest used data type.
nchar (<size>): A fixed-length string based on the character set, with a maximum length of 2000 bytes.
nvarchar2 (<size>): A variable-length string based on the character set, with a maximum length of 4000 bytes.
long: Variable long character column with a maximum length limit of 2GB for long strings of data that do not need to be searched for strings. This type is a legacy data type that will not be supported in the future and is gradually replaced by large data types such as Blob,clob,nclob.
The numberic data type is used to store negative and positive integers, fractions and floating-point data, and the Numberic data types provided in Oracle:
number (<m>,<n>): A variable-length numeric column that allows 0, positive, and negative values, M is the number of digits for all valid digits, and n is the number of digits after the decimal point.
Date data types provided in Oracle:
date: Default format is Dd-mon-yy (day-month-year)
LOB data types provided in Oracle:
blob, CLOB, NCLOB: Three large Objects (lobs), used to save large graphics files or formatted text files, such as Word documents, and audio, video, and other non-text files, the maximum length charge is 4GB. Halo Some data is stored inside the database.
bfile: Large binary object files stored outside the database with a maximum length of 4GB, an external LOB type that records changes through the database, but the data is stored outside the database.
Raw data types provided in Oracle:
raw (<size>): variable-length binary data that specifies the maximum length when defining a field, which is used to hold smaller graphics files or formatted text files, and is an older type of data that will be replaced by LOB data types.
long RAW: variable-length binary data with a maximum length of 2GB, can be used to save large graphics or formatted text files, as well as audio, video and other non-text files, which is also an older type of data, will be replaced by LOB data types.
Other types of data:
rowid: This is a pseudo example in an Oracle datasheet, which is the unique identifier within each row of data in a data table
Integer: Integral type

Create a shopping website background database

Now we go back to use the Java EE system to develop the theme of shopping site, the real site to build the background database of shopping websites.
In order to achieve the basic functions of the shopping site, we need to create four tables: Product list (products), commodity type table (item), order list (orders) and Administrator list (admin). The table structure looks like this:

Item table structure (commodity type table)
Field name     data type         allow NULL     primary key/foreign key      Notes    
type_id    INTEGER (AutoNumber)     no     primary key     product Category ID Mark
type    varchar2     no          Product Category name

Product table structure (product list)
Field name data type allows NULL primary key/foreign key comment
product_id INTEGER (AutoNumber) No primary key commodity ID mark
Title VARCHAR2 (30) No product name
type_id INTEGER no foreign key commodity category mark
Info VARCHAR2 (80) is a product introduction
Price Number (16,2) No commodity prices

Orders table structure (order list)
Field name data type allows NULL primary key/foreign key comment
order_id INTEGER (AutoNumber) No primary key order ID mark
Name VARCHAR2 (20) No customer name
Address VARCHAR2 (100) is the shipping addresses
Tel Number (16) is the contact telephone
Email VARCHAR2 (30) No contact email
Btime date is the order dates
product_id INTEGER no foreign key commodity mark
Uword VARCHAR2 (100) is a guest message

Admin table structure (Administrator list)
Field name data type allows NULL primary key/foreign key comment
admin_id INTEGER (AutoNumber) No primary key administrator ID tag
AdminName VARCHAR2 (20) No administrator name
Password VARCHAR2 (20) No admin password

After the design of the table structure, we will begin to create.
Create a table I think it is not a difficult thing, then we have to pay attention to product, item, orders, the relationship between the three tables, as well as automatic numbering.

The following is a complete SQL statement, which I will give you in detail later, you can follow the input in Sql*plus, or you can save it as a SQL script file in Sql*plus or sqlplus worksheet. Of course, you can also copy the code directly to the Sql*plus execution!

REM///by Maxuan start///create table item (type_id Integer not Null,type varchar2 (), Constraint ITEM_PK key (primary) The CREATE TABLE product (product_id integer not Null,title varchar2 () is not NULL, type_id the integer not null,info varchar2 (80) , price number (16,2) is not null,constraint PRODUCT_PK primary key (product_id), Constraint PRODUCT_FK foreign key (type_id) re Ferences item (TYPE_ID)), CREATE table orders (order_id integer not null,name varchar2 () not null,address varchar2 (100), Tel number, email varchar2 not null,btime date,product_id integer not Null,uword varchar2 (MB), 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 (a) not Null,password varchar2 (a) not null,constraint admin_pk Primar Y 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 One: Establish the association between tables
Product, item, orders three tables are associated with a common domain, often called a key field, and there are two types of keys: Primary key (Primary key) and foreign key (Foreign key). The primary key makes the data rows in the table unique, in table product, PRODUCT_ID is the primary key, and the table orders also contains product_id, at which point the product_id is the foreign key. A table's foreign keys get information from other tables. Look at the above SQL statement, it should be!

Note Two: about automatic numbering
There are automatically numbered data types in Access, and both MSSQL and MySQL have automatically growing data types, and when you insert records without manipulating this field, you automatically get data values, and Oracle does not automatically grow the data type, we need to create an automatic growth sequence number, When you insert a record, you assign the next value of the serial number to this field, and predictably, with this feature, we can migrate the data from Access, MSSQL, or MySQL to Oracle!
Create sequence type_id increment by 1-start with 1;
In this sentence, the type_id is the name of the serial number, each growth is 1 and the starting number is 1.

Well, our database has been built, and also learned some basic relevant knowledge, about my use of Java-EE development shopping site of the second Oracle chapter to this end, if you have any questions please leave a message!!