The management of data in data table

Source: Internet
Author: User
Tags date format insert interface sql query sqlplus
The following describes how to manipulate data in a datasheet.
Inserting data into a datasheet
1. Insert data into the Instructor information table
(1) Select the Table Data Editor option in the shortcut menu shown in Figure 7.19, and the interface shown in Figure 7.21 appears. You can enter data in a two-dimensional form.
(2) Click the button to see the corresponding SQL code for the data you entered.
(3) After the data is entered, click the button to complete the insertion of the data.

(4) The corresponding SQL code for the above process is as follows.
―――――――――――――――――――――――――――――――――――――
INSERT into "SCOTT". " DIRECTOR "(" director_id "," NAME "," Zhicheng "," Zhiwu ")
VALUES (200201, ' Zhang San Fung ', ' Bo Guide ', ' Dean ');
INSERT into "SCOTT". " DIRECTOR "(" director_id "," NAME "," Zhicheng "," Zhiwu ")
VALUES (200202, ' Zhang Trixan ', ' Shuo ', ' room director ');
INSERT into "SCOTT". " DIRECTOR "(" director_id "," NAME "," Zhicheng "," Zhiwu ")
VALUES (200203, ' Zhang Mowgli ', ' Shuo ', ' director ');
INSERT into "SCOTT". " DIRECTOR "(" director_id "," NAME "," Zhicheng "," Zhiwu ")
VALUES (200204, ' song Far Bridge ', ' Bo Guide ', ' Department deputy director ');
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 7th Chapter \insertdirector.sql.
(5) The reader can also insert data into the datasheet scott.director directly by executing the Insertdirector.sql file in "SQL Plus Worksheet".
2. Inserting data into the Postgraduate information table
(1) The same data insert operation is performed on the Postgraduate information table. As shown in Figure 7.22.

(2) The field "Birthday" is a date-type field and the reader may not know how to insert date-type data. Click the button and the interface appears as shown in Figure 7.23.
You can observe such a date-type field data insertion format.
―――――――――――――――――――――――――――――――――――――
To_date (', ' dd-mon-yyyy HH:MI:SS AM ')
―――――――――――――――――――――――――――――――――――――
This indicates that the date field is inserted according to "date-month-year hour minutes".

(3) The analysis of the format does not necessarily be able to correctly input date data. This tells the reader a simple way to find out the author. There is a data table EMP under the Scott user, and one of the HireDate fields is a date type.

(4) Finally completes the complete Scott.student data table data inserts, as shown in Figure 7.25.

"See CD-ROM File": \ 7th Chapter \insertstudent.sql.
(5) The corresponding SQL code for the above process is as follows. The reader can also perform the insertion of the Insertstudent.sql file in the "Sqlplus Worksheet" directly to complete the data.
―――――――――――――――――――――――――――――――――――――
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20020101, ' Zhou Zhijo ', ' Software engineering ', To_date (' 20月-November -1976 ', ' dd-mon-yyyy HH:MI:SS AM '), 200201);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20020102, ' Zhao ', ' computer security ', To_date (' July-October -1980 ', ' dd-mon-yyyy HH:MI:SS AM '), 200202);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20020103, ' small Zhao ', ' graphic images ', To_date (' 2 February-October -1973 ', ' dd-mon-yyyy HH:MI:SS AM '), 200203);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20020104, ' spider ', ' e-commerce ', to_date (' November-January -1971 ', ' dd-mon-yyyy HH:MI:SS AM '), 200204);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20030101, ' Golden Flower mother-in-law ', ' database ', to_date (' June-August -1945 ', ' dd-mon-yyyy HH:MI:SS AM '), 200201);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20030102, ' Hu Qing cattle ', ' cyber security ', To_date (' February-May -1923 ', ' dd-mon-yyyy HH:MI:SS AM '), 200203);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20030103, ' Ding Minjun ', ' web technology ', to_date (' December-April -1967 ', ' dd-mon-yyyy HH:MI:SS AM '), 200201);
INSERT into "SCOTT". " STUDENT "
("student_id", "NAME", "PROFESSIONAL", "Birthday", "director_id")
VALUES (20030104, ' Yinsusu ', ' web security ', To_date (' 1 April-July -1971 ', ' dd-mon-yyyy HH:MI:SS AM '), 200202);
Commit;
―――――――――――――――――――――――――――――――――――――
Querying data in a datasheet
1. Query Tutor Information table data
(1) Execute the following statement using "Sqlplus Worksheet".
―――――――――――――――――――――――――――――――――――――
SELECT * from Scott.director;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 7th Chapter \selectdirector.sql.
(2) The results of the query are shown in Figure 7.26.
2. Inquiry data of Postgraduate information table
(1) Execute the following statement using "Sqlplus Worksheet".
―――――――――――――――――――――――――――――――――――――
SELECT * from Scott.student;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 7th Chapter \selectstudent.sql.
(2) The results of the query are shown in Figure 7.27.

Updating data in a datasheet
1. Update instructor Information table data
(1) Figure 7.28 shows the Datasheet editor interface. In the table directly to "director_id" as "200204" tutor's "Zhiwu" by "deputy director" change to "Dean".

(2) Click the button and the SQL statement for the updated data appears as follows.

(3) The reader may wonder, what does rowid = ' Aaah2kaaiaaaaayaad ' mean?
(4) Oracle 9i when creating a data table, the default is to create an implied field for each datasheet called ROWID. When you insert a record into a datasheet, the system automatically assigns a unique ROWID number to each record, using this ROWID number to quickly navigate to the record.
(5) However, SQL statements that use ROWID to update data apply only to the same computer. Different computer environments may assign different ROWID numbers to the data, so we need to give a universally applicable syntax for updating data table data.
(6) The following statements are executed in the "Sqlplus Worksheet", and the function is the same. This is through the main code director_id to locate the record. Such an UPDATE statement is suitable for use on different computer environments.
The results of the execution are shown in Figure 7.29.


2. Update postgraduate Information table data
The data for graduate data sheets can be updated in the same way.
(1) In the Table Data editor, the Tutor Number field director for postgraduate number student_id "20030103" is changed to 200204, as shown in Figure 7.30.

(2) The corresponding SQL code is as follows.
―――――――――――――――――――――――――――――――――――――
UPDATE "SCOTT". STUDENT "
SET director_id = 200204
WHERE student_id = 20030103;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 7th Chapter \updatestudent.sql.

Delete data from a datasheet

(1) Perform the following actions in the Table Data editor interface shown in Figure 7.31.

(2) The SQL statement that deletes the data is as follows.
―――――――――――――――――――――――――――――――――――――
DELETE from "SCOTT". " STUDENT "
WHERE student_id = 20030101;
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 7th Chapter \deletestudent.sql.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.