Introduction to oracle 05

2011-04-17  来源:本站原创  分类:Database  人气:92 

DML (Data Manipulation Language - data manipulation language) can be run under the following conditions:
• • insert data to the table to modify the existing data • Delete the existing data

1, the insert statement using INSERT statement to insert data to the table.
INSERT INTO table [(column [, column ...])]
VALUES (value [, value ...]);
You can only use this syntax to insert a data table
/ / Insert the users table to record a complete
SQL> insert into users (username, password, name, address, zip) values ​​('rrmy', '1234 ',' test ',' Beijing ', 100089);
Note: If a string is a number if you can add''plus''and may not add ===> generally not
SQL> commit;
Note: oracle database insert command in DOS, modification and other operations must be manually submitted. Or not (is true) was inserted successfully.
Insert the full record in users () in the table corresponds to a field can be omitted, but must correspond to the structure of the database table Caixing.
/ / Insert a table to users of non-complete records

SQL> insert into users (username, password) values ​​('mj', '123 ');
/ / Note: In some sub-section when inserted, the table must meet the constraints, otherwise it will appear as anomalies.
insert into users (username, password) values ​​('mj', '123 ')
RA-01400: can not insert NULL into ("RED". "USERS". "NAME")
SQL> insert into users (username, password, name) values ​​('mj', '123 ',' Majestic ');
1 row inserted
SQL> commit; / / must be manually submitted to
Commit complete
Above brief summary is as follows:
 Add a new column for each value.
 listed in order by the column's default value of each column.
 random lists in the INSERT clause column names and their values.
 character and date type data should be included in single quotation marks.

Additional knowledge points:
Implicit way: the watch list omitted the value of the column
SQL> insert into users (username, password, name) values ​​('mj', '123 ',' Majestic ');
Display: in the VALUES clause specifies null value.
SQL> insert into users values ​​('test', '123 ',' test ', null, null);

Common interview questions:
/ / Backup a table
SQL> create table usess as select * from users;
/ / Clear the table records
SQL> delete from usess;
/ / Use the insert statement to insert records into the users table in usess
SQL> insert into usess (username, password, name, address, zip) select username, password, name, address, zip from users;
SQL> commit
 do not have to write a VALUES clause.
 subquery in the list of values ​​should be the column name in INSERT clause of the corresponding

2, updating the data used to update data update syntax:
Syntax is as follows:
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];
Note: once you can update multiple records
/ / Update data: update users table to re-set passwrod condition is the only (primary keys are unique) conditions.
SQL> update users set password = 'sunlijuan' where username = 'sunlijuan';
SQL> commit
Note: When updating multiple fields, you can use a comma to distinguish for example as follows:
SQL> update users set password = 'sunlijuan', name = 'Lijuan' where username = 'rrmy';
SQL> commit
Note: If you omit the WHERE clause, then all data in the table will be updated
SQL> update users set password = 'sunlijuan', name = 'Lijuan';
6 rows updated
SQL> commit;
Commit complete
SQL> select * from users;
-------------------- -------------------- ---------- ---------- -------------------- -------
huxz sunlijuan Lijuan Beiyuan home 100 012
liucy sunlijuan Lijuan Tsinghua Park 100 084
sunlijuan sunlijuan Lijuan Beijing 100089
rrmy sunlijuan Lijuan Beijing 100089
mj sunlijuan Lijuan
test sunlijuan Lijuan
6 rows selected

Added: in the update statement using subqueries
/ / Update huxz zip code zip code consistent with the user liucy
SQL> update users set zip = (select zip from users where username = 'liucy') where username = 'huxz';
/ / Note: when updating the data can also be used where conditions are sub-queries.

3, delete data in the table is not necessary if some data can be deleted using the delete statement, and the release of the data storage space occupied by deleted the following syntax:
[WHERE condition];
Note: delete statement to delete data only from the table, not delete the table structure to delete the table structure using the drop statement.
Note: delete data in the table, we must consider the constraints of the table (otherwise there will be some unusual information)
/ / Clear the table delete from table name | | delete table
/ / Delete and update the data where the same conditions, must ensure that the unique (primary key is unique) conditions
SQL> delete from users where username = 'test';

  • Introduction to oracle 05 2011-04-17

    DML (Data Manipulation Language - data manipulation language) can be run under the following conditions: • • insert data to the table to modify the existing data • Delete the existing data 1, the insert statement using INSERT statement to insert data

  • Introduction to oracle 9i (primer 1 )----- 2011-03-30

    Introduction to oracle 9i (primer) 1 Introduction: the need to have some basis for the database, do not speak here of basic SQL, focus on Oracle 9i in some of the more special things, many of which are frequently used in practice. One, Oracle 9i Inst

  • Chapter 1 Introduction to Oracle Oracle 2011-04-29

    Oracle 9i installation and uninstallation Chapter 2 of Oracle query Oracle data types, functions Chapter 1 Introduction to Oracle Oracle Note: This article uses the version for Oracle 9i 1, the technical objectives Understand the various components o

  • Oracle Learning (1) - Introduction to Oracle for 15 days 2010-04-07

    * A few days ago I shared a bit rubbish after graduation learning java and Oracle article, could be determined to enter the IT graduates or people a little help. Many current students email asking how I should be learning Oracle, while the project is

  • Introduction to oracle 01 2011-04-17

    1, after a successful installation into operation during the following DOS interface, the need to start the oracle service. A, into the sql interface: Start - Run - cmd: prompt type sqlplus Enter the correct user name and password B, Start -> All Pro

  • Introduction to oracle 10 2011-04-17

    PL / SQL Programming PL / SQL is the oracle of the special language, its standard SQL language extension. SQL statements can be nested in a PL / SQL language, and combined treatment statement. PL / SQL program using the block structure of the structu

  • Chapter Introduction to Oracle 2011-05-24

    1, the technical objectives Understand the various components of Oracle architecture Mastered the basic user management in Oracle Understanding of Oracle's tools 2, Oracle Introduction Object-relational database management system (ORDBMS) In manageme

  • Oracle Learning (3) - Introduction to Oracle for 15 days 2010-04-07

    Oracle Getting Started (3) These common functions you do not know how many? Although I do not advocate the business logic in sql in treatment, but some simple business I Renshi or dealt with in the sql it makes me a lot of effort and Efficiency Jiesh

  • Oracle Learning (4) - Introduction to Oracle for 15 days 2010-04-18

    PL / SQL Programming (1) 1: pl / sql based 1: PL / SQL is a standard sql oracle in the process of language on the formation of the extended programming language. He not only can use Nested sql it allows the custom, but also a variety of variables, an

  • Oracle Learning (6) - Introduction to Oracle for 15 days 2010-04-18

    Exception Handling - Exception handling is divided into pre-defined exception handling, non-predefined exception handling, and custom exception handling. <1>. Predefined exception handling Oracle has a predetermined common abnormalities are: Excepti

  • introduction to oracle 2010-06-30

    The task of this chapter 1: Network Configuration - 1, in the server to start the two services OracleListener: listener OracleServiceDB: Database 2, configure the network connection through the net Manager, be sure to save the network service name to

  • Introduction to Oracle Coherence 2010-09-11

    Sequence: once because the project side of the wealthy, and extremely popular Oralce, fortunately able to access and use in the project Oracle Coherence. I have done during the first Oracle Coherence within the company's share in order to ensure the

  • Brief Introduction to ORACLE's analyze 2010-11-04

    ORACLE Database PL / SQL statement execution optimizer ORACLE - Performance Optimization

  • Introduction to oracle stored procedure 2010-10-11

    1, create a stored procedure create or replace procedure test (var_name_1 in type, var_name_2 out type) as - Declare a variable (variable type variable name) begin - Body of stored procedure execution end test; 2, variable assignment Variable name: =

  • Introduction and Introduction to ORACLE 2010-11-09

    Oracle logical components: Database table space (TableSpace) is the largest logical unit of database, an Oracle database contains at least one table space, is the system tablespace named SYSTEM. Each table space is composed of one or more files, a da

  • Introduction to oracle 9i (primer )----- 2 2011-03-30

    Oracle frequently used commands (basic level) 1 back into the Oracle command: Run sqlplusw, sqlplus (DOS mode), the program starts, etc. 2 shows the current user show user; 3 Connect command (switch user) conn [ect] username / password @ netServiceNa

  • Introduction to oracle 9i (primer 4 )----- 2011-03-30

    Oracle's transaction processing 1 When performing transactional operations (DML statement), Oracle will be on the operating table lock to prevent other users on the operation table (2) the transaction is committed using the commit statement after the

  • Introduction to oracle 9i (primer 5 )----- 2011-03-30

    Oracle functions 1. Lower (char) switch to lower case 2. Upper (char) transfer capital 3. Length (char) 4. Substr (str, i, len) in str i characters from the first len ​​characters beginning to take form a new string (subscripts starting at 1) 5 Math

  • Introduction to oracle 9i (primer )----- 6 2011-03-30

    Oracle database management 1. Oracle 9i in two of the most important sys and system users The following analysis of the difference between the two (1) the importance of data stored in different oracle sys user to store all the data dictionary base ta

  • Introduction to oracle 02 2011-04-17

    Oracle's query advanced query described above is also limited to a table in the database. However, in practice, we often need more than one table or query data need to classify data in the table, summary and so on. This requires more complex Advanced