How to use SQL plus to operate cmdel (Windows platform)

Platform: Windows Server 2003 enterprise Editon, Oracle 10g (10.02.00.01)

1. Run "Database Configuration assistant" in "start-> Program/Oracle-oradb10g_home1/configuration and transplantation tool" to create a database.
2. After successful creation, use one of the following to enable sqlplus or sqlplusw.
   
   Method A): Run "start-> Program/Oracle-oradb10g_home1/" SQL plus "in application development ". username: SYS password: 123456 as sysdba (the password must be followed by a space as space sysdba) host string: if it is a local machine, do not fill in.
   Note: You can also send the shortcut to the desktop, and then right-click the "properties-> shortcut" tab, add the space sys/123456 @ host name (not added to the host) as sysdba after the EXE in the project column. Then, click this shortcut to log on.
   
   Method B): Run sqlplusw sys/123456 as sysdba at the command prompt.
   
   Method C): Run sqlplus/nolog from the command prompt and then run

   Conn username/password as sysdba "connected" is successful.

3. SQL * plusw Editing Command
   • LIST commands used to list the buffer (it seems that by default, only the last SQL command is saved, unless you can use input to insert it into the command buffer)
   • /Execute the previous command
   • C/The string to be modified/the modified string/you can modify the previous command (and the command in the buffer)
   • Ed can call the external editor to edit commands in the buffer.
   • N --Switch activity line(NNumber of rows)
   • A --Add after activity line(Append)
   • I --Insert a new row after the activity row to add(Imput)
   • Del n --Delete rowN　
   • SQL> alter session set nls_language = 'American '; change the session language display.
   • SQL> alter session set nls_language = 'simplified Chinese ';
4. SQL * plusw formatted output
   By default, the query output results are chaotic.
   Set linesize 1000 and then modify
   Options --> enviroment --> screen buffer can make the output more neat.

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)
----------- If the login is successful, you do not need to do this step 1-3, directly do step 1 ----------------

1. SQL> create user maxuan identified by Max; # create a maxuan user with the max password
2. SQL> grant connect, resource to maxuan; # authorize maxuan
3. SQL> conn maxuan/max; # connect with your maxuan
4. 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.
5. SQL> insert into test values (1); # insert a record
6. SQL> select * from test; # query record. The first row of column A is 1.
7. SQL> Update Test Set A = 2; # change the record. At this time, the first row of column A has been changed to 2.
8. SQL> commit; # commit. When you insert, delete, and modify a database, only when the transaction is committed to the database is complete.
9. SQL> Delete from test; # delete all records in the test table. At this time, there are no records in the test table.
10. 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 (): a fixed-length string with a maximum length of 2000 bytes. If the length is not specified, the default length is 1 byte.
Varchar2 (): 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 (): a fixed-length String Based on the character set. It consists of a maximum of 2000 bytes.
Nvarchar2 (): 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 (,): A Variable Length Value column, which can be 0, positive, or negative. 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 (): variable-length binary data. The maximum length must be specified 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
　　
Additional knowledge:
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.

OneShopping website background databaseInstance:
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 copy the Code directly to SQL * Plus for execution!
　　
Code:
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)
);