DML statement Learning (1)

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'

Rollback;

-- 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;

Commit;

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

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

Rollback;

-- 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.
Select
From
Where
Group
Having
Order