DML statement Learning (1)

Source: Internet
Author: User

DML statements
(Insert, update, delete, select)

-- Insert statement
Select * from student;
-- Insert a statement separately
Insert into student (SNO, sname, sex, address, cardid)
Values (2, 'zhang san', 'male', 'changsha ', '123 ')

Update student set sex = 'femal'


-- Notes when inserting
(1) The number of columns to be inserted must match the number of values
(2) When constraints exist, consider constraints (check, default, foreign key, unique)
(3) data type (considering integrity constraints (entity integrity, domain integrity, reference integrity, custom integrity ))
(4) in SQL Server, when an ID column exists, we cannot insert a value to this ID column.
-- Consider the default constraints below
Insert into student (SNO, sname, sex, cardid)
Values (2, 'zhang san', 'male', '123 ')

Insert into student
Values (3, 'lily', 'female ', default, '23 ')

-- Insert multiple rows of data at a time
-- In SQL Server
-- Self-input value
Insert into student
Select 110, 'zhang san', 'male', getdate (), 95031 Union
Select 111, 'zhang 2', 'male', getdate (), 95031 Union
Select 112, 'zhang 3', 'male', getdate (), 95031 Union
Select 113, 'zhang 4', 'male', getdate (), 95031

-- Insert data in another table when creating a table (if the table does not exist)
Select * into newstudent from student; -- it can be executed only once at the same time
Select * From newstudent;
Truncate table newstudent;
Drop table newstudent;

-- If the table already exists
Insert into newstudent select * from student;

-- Only copy the table structure. Do not copy data.
Select * into newstudent from student where 1 = 0;

-- When creating a table, we add a field and add an ID column to the field.
Select Identity (INT, 1, 1) as SnO, sname, ssex into newstudent from student;
Select * From newstudent;
Select * from student;
-- Insert multiple rows of records at a time in Oracle
Insert into student
Select 110, 'zhang san', 'male', 'changsha ', '123' from dual Union
Select 111, 'zhang 2', 'male', 'changsha ', '000000' from dual Union
Select 112, 'zhang 3', 'male', 'changsha ', '123' from dual Union
Select 113, 'zhang 4', 'male', 'changsha ', '123' from dual

Select * into newstudent from student; -- error in Oracle

Create Table newstudent
Sno int primary key,
Sname varchar2 (20)
Select * From newstudent;
-- Insert data in another table into an existing table
Insert into newstudent select SnO, sname from student;


Select * from student;
-- Update statement
Update table name set field name = value where Condition

Update student set sname = 'wang wu' where SnO = 111;


-- Delete statement
Delete from student where SnO = 111;

Delete cardid from student; -- the value of the specified Column cannot be deleted.

-- Delete all records in the table
Truncate table student;
-- Equivalent
Delete from student;

Truncate table TT1;

Select * from student;

Insert into student
Values (1, 'zhang san', 'male', default, '123 ')

Create Table TT
(SNO int primary key,
Sname varchar2 (20 ))

Insert into TT select SnO, sname from student;

Select * from TT;

Truncate table tt;

-- Query select
Select 12 + 12 from dual;
-- The select execution sequence cannot be changed at will.

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: 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.