Java EE Development Shopping site experience-table _jsp programming

Go on continue to 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 condition > 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 Add data row to table
Delete Deletes data rows from the table
Update change data in table 3. Data Definition Language DDL completes 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 Creating tables
CREATE index
Create View creating views
ALTER TABLE adds table columns, redefining table columns, changing storage allocation
drop TABLE Delete Tables
Drop index deletes indexes 4. The database control Language DCL is used to grant or reclaim certain privileges to access the database, and to control the time and effect of database manipulation transactions, and to monitor the database. Such as:
Grant grants 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 commits. 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. Next, we in the sql*plus in the actual combat, for we will do below lay a good foundation.
After using system login to Sql*plus, we do the following (no screenshots, 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 Oracle data types
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>): fixed-length string with a maximum length of 2000 bytes, and a default of 1 bytes 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, which allows you to put all the symbols 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, 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 string 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 that are provided in Oracle:
Date: The default format is the LOB data type that DD-MON-YY (day-month-year) provides in Oracle:
BLOBs, CLOB, NCLOB: Three large objects (lobs) to hold large graphics files or formatted text files, such as Word documents, and audio, video, and other non text files, the maximum length 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, which can be used to save large graphics or formatted text files, as well as audio, video, and other non text files, which is an older type of data that will be replaced by LOB data types. Other data types:
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 site background database Now we go back to use the Java EE system to develop the theme of the shopping site, start the actual battle to build a shopping site background database.
In order to achieve the basic functions of the shopping site, we need to create four tables: Product list (products), Product 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 allows NULL primary key/foreign key comment
type_id INTEGER (AutoNumber) No primary key commodity category ID mark
Type VARCHAR2 (30) No commodity 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 the Customer 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 administrator password after the design of the table structure, we will begin to create.
Create a table I don't think it's a difficult thing to do, so we should be aware of the association between the three tables of product, item, orders, and automatic numbering. Here's the complete SQL statement, which I'll give you in a few words, which you can follow in the Sql*plus, or you can save it as a SQL script file and execute it in Sql*plus or sqlplus worksheet. Of course, you can also copy the code directly to the Sql*plus execution! Code Copy Box
REM///by Maxuan start///CREATE TABLE item (type_id Integer NOT NULL, type VARCHAR2 (), Constraint ITEM_PK key (type _id)); CREATE TABLE product (product_id integer NOT NULL, title VARCHAR2 (a) not NULL, type_id 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) References item (TYPE_ID)); CREATE TABLE orders (order_id integer NOT NULL, name VARCHAR2 is not NULL, address VARCHAR2 (), tel number (), email v Archar2 NOT NULL, btime date, product_id integer NOT NULL, Uword varchar2 (MB), Constraint ORDERS_PK key (order _id), Constraint ORDERS_FK foreign key (product_id) references product (product_id)); Create TABLE admin (admin_id integer NOT NULL, adminname VARCHAR2 is not NULL, password VARCHAR2 is 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 All select then copy] Description: Establish the relationship between the 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!!
In the next third article WebLogic, I will continue to put personal life experience unpaid, I hope we can have some harvest!! Thank you for your support!
PS: Write really tired, fast catch out of the book!!