Chapter 5th inserting updates and deleting data
Connect to SCHOOLDB using the SQL Manager management tool. Because the primary key is set for three tables, the primary key cannot be weighed in the following exercise.
Inserting data
1. Exercise: Insert data for all fields of a table
Insert data for all fields in a table without specifying a column
where into can save
INSERT into tstudent values (' 00008 ', ' Baek ampoule ', ' Male ', ' 132302197604044565 ', ' 19760404 ',
' [email protected] ', ' JAVA ', ' 20120803 ')
Insert Tstudent values (' 00009 ', ' Bai Aming ', ' Male ', ' 132302197604044565 ', ' 19760404 ',
' [email protected] ', ' JAVA ', ' 20120803 ')
SELECT * from ' tstudent '
650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:300px;padding-top:0 px; "title=" clip_image001 "border=" 0 "alt=" clip_image001 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515945qbuq.png "width=" 650 "height="/>
2. Exercise: Insert a field for the specified column of a table
The order of the columns can be different from the order of the columns in the table
Insert Tstudent (Sname,studentid,sex) VALUES (' Liu Qingming ', ' 00010 ', ' Male ')
SELECT * from ' tstudent '
650) this.width=650; "Style=" background-image:none;padding-left:0px;padding-right:0px;height:78px;padding-top:0 px; "title=" clip_image002 "border=" 0 "alt=" clip_image002 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515951qneg.png "width=" 650 "height=" "/>"
3. Practice: Inserting multiple records at the same time
Insert Tstudent (Sname,studentid,sex) VALUES (' Kim Jong-un ', ' 00011 ', ' Male '), (' Kim Jong-il ', ' 00012 ', ' Male ')
4. Exercise: Inserting query results into a new table
Create a new table
CREATE TABLE SP
(
StudentID varchar (15),
Sname varchar (10),
Sex char (1)
)
The following command inserts a tstudent student from the table into a new table
Insert SP (studentid,sname,sex) Select Studentid,sname,sex from ' tstudent ' where sname like ' Gold% '
SELECT * FROM SP
650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image003 "border=" 0 "alt=" clip_image003 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515961dnnc.png "height=" 165 "/>
Update data
5. Exercise: Update data
Change the name and gender of a student who learns to be 00010
Update ' tstudent ' Set sname= ' Liu Minghui ', sex= ' female ' where studentid= ' 00010 '
View changes after a change
SELECT * from ' tstudent ' where studentid= ' 00010 '
Add 10 points to the computer network score named Han Ligang
View Han Ligang's computer network now scores
Update ' Tscore ' set mark=mark+10 where ' Tscore '. ' StudentID ' =
(select StudentID from ' tstudent ' where sname= ' Han Li just ') and ' tscore '. ' Subjectid ' =
(select Subjectid from ' Tsubject ' where subjectname= ' computer network ')
650) this.width=650; "Style=" background-image:none;margin:0px;padding-left:0px;padding-right:0px;padding-top:0px ; "title=" clip_image004 "border=" 0 "alt=" clip_image004 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 13615159716czd.png "height=" 139 "/>
Update ' Tscore ' set mark=mark+10 where ' Tscore '. ' StudentID ' =
(select StudentID from ' tstudent ' where sname= ' Han Li just ') and ' tscore '. ' Subjectid ' =
(select Subjectid from ' Tsubject ' where subjectname= ' computer network ')
650) this.width=650; "style=" background-image:none;padding-left:0px;padding-right:0px;padding-top:0px; "title=" clip_image005 "border=" 0 "alt=" clip_image005 "src=" http://img1.51cto.com/attachment/201302/22/400469_ 1361515974znqw.png "height=" 141 "/>
Deleting records
6. Exercise: Delete a record
Delete student name is Liu Minghui student record
Delete from ' tstudent ' where sname= ' Liu Minghui '
Delete Han Ligang's scores
Delete from ' Tscore ' where ' tscore '. ' StudentID ' = (select StudentID from ' Tstudent ' where
Sname= ' Han Li just ')
This article is from "Ghost" blog, please make sure to keep this source http://caizi.blog.51cto.com/5234706/1543134