Introduction and use of Oracle Database DML statements

DML (Data Manipulation Language) is a Data Manipulation Language, one of the types of SQL, in addition to the Data Definition Language (DDL) Data Definition Language and Data Control Language (DCL) Data Control Language. DML includes INSERT, UPDATE, and DELETE. Note that the select statement belongs to DQL (Data Query Language ).

1. DML (Data Manipulation Language-Data operation Language) can be executed under the following conditions:
2. • Insert data into a table
3. • Modify existing data
4. • Delete existing data
5. 1Insert statement
6. Use the INSERT statement to INSERT data to the table.
7. Insert into table [(column [, column...])]
8. VALUES (value [, value...]);
9. With this syntax, only one data entry can be inserted into the table at a time.
10. // Insert a complete record to the users table
11. SQL> insert into users (username, password, name, address, zip) values ('Rrmy','123','Honghong','Beijing',100089);
12. NOTE: If it is a string, add ''. If it is a number, add'' or not ==> generally, do not add
13. SQL> commit;
14. Note: The Oracle database must be submitted manually when performing insert or modify operations in the DOS command. Otherwise, the data is not inserted successfully.
15. When inserting a complete record, the field corresponding to the table in users () can be omitted without writing, but it must correspond to the structure of the table in the database.
16. // Insert an incomplete record to the users table
18. SQL> insert into users (username, password) values ('Mj','123');
19. // Note: The table constraints must be met when inserting a part of the segment. Otherwise, the following exception occurs.
20. Insert into users (username, password) values ('Mj','123')
21. RA-01400: NULL cannot be inserted ("RED"."USERS"."NAME")
22. SQL> insert into users (username, password, name) values ('Mj','123','Marj');
23. 1Row inserted
24. SQL> commit;// Manual submission required
25. Commit complete
26. The above summary is as follows:
27. Add a new value to each column.
28. The values of each column are listed by default.
29. The column names and their values are randomly listed in the INSERT clause.
30. Character and date data should be included in single quotes.
32. Additional knowledge points:
33. Implicit mode: omit the value of this column in the column name table.
34. SQL> insert into users (username, password, name) values ('Mj','123','Marj');
35. Display Mode: specify a null value in the VALUES clause.
36. SQL> insert into users values ('Test','123','Test',Null,Null);
38. Common interview questions:
39. // Back up a table
40. SQL> create table usess as select * from users;
41. // Clear records in the table
42. SQL> delete from usess;
43. // Insert the records in the users table to usess using the insert statement
44. SQL> insert into usess (username, password, name, address, zip) select username, password, name, address, zip from users;
45. SQL> commit
46. Note:
47. You do not have to write the VALUES clause.
48. The Value List in the subquery should correspond to the column name in the INSERT clause.
51. 2, Update data
52. Update data using the update Syntax:
53. Syntax:
54. UPDATE table
55. SET column = value [, column = value,...]
56. [WHERE condition];
57. Note: Multiple records can be updated at a time.
58. // Update data: update the users table and reset the passwrod condition to be unique (the primary key is unique.
59. SQL> update users set password ='Redarmy'Where username ='Redarmy';
60. SQL> commit
61. Note: When updating multiple fields, you can use commas to differentiate them. For example:
62. SQL> update users set password ='Redarmy', Name ='Chen hongjun'Where username ='Rrmy';
63. SQL> commit
64. Note: If the WHERE clause is omitted, all data in the table will be updated.
65. SQL> update users set password ='Redarmy', Name ='Chen hongjun';
66. 6Rows updated
67. SQL> commit;
68. Commit complete
69. SQL> select * from users;
70. USERNAME PASSWORD NAME ADDRESS ZIP
71. ---------------------------------------------------------------------------------------
72. Huxz redarmy Chen hongjun's home in Beibei100012
73. Liucy redarmy Chen hongjun Tsinghua Park100084
74. Redarmy Chen hongjun Beijing100089
75. Rrmy redarmy Chen hongjun Beijing100089
76. Mj redarmy Chen hongjun
77. Test redarmy Chen hongjun
78. 6Rows selected
80. Supplement: Use the subquery in the update statement
81. // Update huxz's zip code to be consistent with the user's liucy zip code.
82. SQL> update users set zip = (select zip from users where username ='Liucy') Where username ='Huxz';
83. // Note: subqueries can also be used in the where condition when updating data.
84. 3, Delete data
85. If some data in the table is not needed, you can use the delete statement to delete the data and release the storage space occupied by the data. The delete syntax is as follows:
86. DELETE [FROM] table
87. [WHERE condition];
88. Note: The delete statement only deletes data from the table and does not delete the table structure. The drop statement is used to delete the table structure.
89. Note: When deleting data in a table, you must consider the constraints of the table (otherwise, some exceptions may occur)
90. // Clear the delete from table name | delete table name
91. // The where condition for data deletion is consistent with the update condition. The where condition must be unique (the primary key is unique ).
92. SQL> delete from users where username ='Test';