SQL class notes-data manipulation and functions

2017.11.17

Seven. Inserting, updating and deleting data:
1. Insert data into the table:
Inserting using the INSERT statement
The traditional INSERT statement basic syntax format:
Insert into table name values (value 1, value 2,...)

You can also specify a column that says you want to insert data:
INSERT INTO table_name (column 1, column 2,...) VALUES (value 1, value 2,...)
(parameter into is an optional keyword)

Example 1: Inserting data into a table:
(1): Add a row to all columns
(2): Add a row to a section column
(3): What if I insert multiple rows of data?
1.insert into table name values (value 1, value 2,...) Repeat writing
New methods are available in 2.SQL server2008-----Row Value Constructors
INSERT into student (Sno,sname,ssex,sage,sdept,class) values
(' 95011 ', ' Ouyang One ', ' female ', 18, ' software ', ' 3 '),
(' 95012 ', ' Ouyang II ', ' female ', 18, ' software ', ' 3 '),
(' 95013 ', ' Ouyang Three ', ' female ', 18, ' software ', ' 3 ')
3. Insert using the SELECT clause:
Insert into table[(field 1, Field 2,...)]
Select Value1,value2,... [From table1]
Or:
INSERT INTO table2 select * FROM table1
Cases:
INSERT into student select ' 95011 ', ' Ouyang One ', ' female ', 18, ' software ', ' 3 '
INSERT into student select ' 95012 ', ' Ouyang II ', ' female ', 18, ' software ', ' 3 '
or use Union/union all
INSERT into student select ' 95011 ', ' Ouyang One ', ' female ', 18, ' software ', ' 3 '
Union/union All
' 95012 ', ' Ouyang II ', ' female ', 18, ' software ', ' 3 ')

2. Update the data in the table:
UPDATE statement Updates
Update table name set column name

Example 2:
Update student1 set sdept= ' anime ' where sno= ' 95012 '
3. Delete data:
Delete table name [WHERE condition]

Example 3: Delete a record of Sno in the Student1 table for 95015 students
Delete Student1 where sno=95012

Item Two: SQL syntax

1. Constants: string, Binary, shaping, date, real, currency
2. Variables:
1. Global variables: Provided by the system, with two "@" symbols in front of the name distinguished from local variables
2. Local variables: With a "@"

Practice:
1. Query the version information for SQL Server:
SELECT @ @version
2. Query the local server name:
SELECT @ @servername
3. Query the currently used language name:
SELECT @ @language

3. Use of variables:
1. Declare the variable with the Declare statement:
DECLARE {@ local variable name data type}[,... n]
Declares two variables @v1, @v2, with a data type of char (8), int
DECLARE @ v1 char (8), @v2 int
2.set Statement Assignment:
SET @ local variable = expression
Give two variables @v1, @v2 the output after assignment:
DECLARE @v1 char (8), @v2 int set @v1 = ' Welcome ' Set @v2 =100 select @v1, Output @v2 tabular form
DECLARE @v1 char (8), @v2 int set @v1 = ' Welcome ' Set @v2 =100 print ' v1 to: ' [email protected] message output
Print @v2
4. Comment statements
Line Comment
Block annotations

Two. variables, operators, and expressions

Three. Batch processing and Process control:
1. Sequential Statement Begin...end statement
BEGIN {SQL Statement | SQL statement block} end

2.if...else ... Statement:
In the student performance management system to view Zhang Li scores, if the score of the lowest score of 60 or more, showing the results, otherwise the text ' results are not ideal ':
DECLARE @m int
Select @m=min (Grade)
From STUDENT,SC
where Sc.sno = Student.sno and sname= ' Zhang Li '
if (@m>=60)
Select Student.sno,sname,cno Grade
From STUDENT,SC
where Student.sno=sc.sno and Sname= ' Zhang Li '
Else
print ' performance is not ideal '
3.while statements
while< Boolean Expressions >
{SQL Statement | SQL statement block}
[Break]
{SQL Statement | SQL statement block}
[Continue]
Continue: The program skips the statement after continue back to the first line of the Loop command
Break: Jump out of the loop

Example: Calculating the sum of odd numbers between 1 and 10
Declare @i tinyint, @sum int
Set @sum =0
Set @i=0
While @i>=10
Begin
Select ' Sum ' [email protected]
Break
End
Else
Begin
Set @[email protected]+1
if (@i%2) =0
Contiue
Else
Set @[email Protected][email protected]
End
End
4.1.case statement:
Case expression
When expression then-expression
When expression then-expression
When expression then-expression [... n]
[Else expression]
End

Example: Judging student grade, 90-100 points for ' good ', 80-90 for ' good ', 70-80 for ' Medium ', 60-70 for ' Pass ', others for ' fail '
Select Sno,cno,grade,
' Rank ' =case GRADE/10
When ten then ' excellent '
When 9 then ' excellent '
When 8 then ' good '
When 7 Then ' Medium '
When 6 Then ' pass '
Else ' fail '
End
From SC

2. Search for Case statements:
Select Sno,cno,grade,
' Rank ' =case
When grade>=90 and grade<=100 then ' excellent '
When grade>=80 and grade<90 then ' good '
When grade>=70 and grade<80 then ' Medium '
When grade>=60 and grade<70 then ' pass '
Else ' fail '
End
From SC

Practice:

Use students
Go
--one, insert into in student table ... Values add record information as: (95009, ' Ouyangjingqiu ', ' female ', 18, ' software ', 3)
INSERT into student values (95009, ' Ouyangjingqiu ', ' female ', 18, ' software ', 3)

-Two, add a student1 table with the student table structure in the provided students database.
--Three, insert the following data in the Student1 table with the row value constructor:
Insert into Student1 (Sno,sname,ssex,sage,sdept,class) values
(95011, ' Ouyang One ', ' female ', 18, ' software ', 3),
(95012, ' Ouyang II ', ' female ', 18, ' software ', 3),
(95013, ' Ouyang three ', ' female ', 18, ' software ', 3),
(95014, ' Ouyang IV ', ' female ', 18, ' software ', 3),
(95015, ' Ouyang Five ', ' female ', 18, ' software ', 3)
--Iv. write the SQL code in the provided students database for query:
--1, write the SQL code to create the table class, the structure of the table is as follows:
Use students
Go
CREATE TABLE Class
(
ClassID int PRIMARY KEY,
ClassName Char (20),
Specialityid Char (10),
Specialityname Char (20),
Entranceyear int,
Monitor int
)
--2, using an INSERT SQL statement with a SELECT clause, adds a record to the class table, recording information as: (1, ' 16 computer 01 shifts ', ' a01 ', ' photography ', 2019,95030), (2, ' 16 computer 01 classes ', ' a02 ', ' Development Program ') , 2019,95032)
INSERT into class values (1, ' 16 computer 01 classes ', ' A01 ', ' photography ', 2019,95030)
INSERT into class values (2, ' 16 computer 01 classes ', ' a02 ', ' Development Program ', 2019,95032)

--3, using an INSERT SQL statement with a SELECT clause, adds a record to some fields in the Class table: (2008001, ' 08 computer control ', ' A01 ', 2008,0733001)
INSERT into Class (Classid,classname,specialityid,entranceyear,monitor) values
(2008001, ' 08 computer control ', ' A01 ', 2008,0733001)

--Five, write the SQL code in the provided students database to query:
Use students
Go
--1, increase the credit for all courses by 1 points.
Update course set ccredit=ccredit+1

--2 5% of all students majoring in computer science.
Update SC set grade=grade*0.95 where Sno in (select Sno from student where sdept= ' computer ')

--3, delete the record of elective course score below 60 minutes.
Delete SC where grade<60

--4, use the union operator to merge the student table with the result set of the Student1 table.
--select Statement 1 Union [ALL] SELECT statement 2
Select Sno,sname,ssex,sage,sdept,class from student union ALL select Sno,sname,ssex,sage,sdept,class from Student1

--Six, write and run SQL scripts using SSMs.
--1, querying version information for SQL Server.
SELECT @ @version
--2, queries the name of the local server.
SELECT @ @servername
--3, queries the name of the currently used language.
SELECT @ @language
--4, in the student performance management system to see Wang Ming's results. If Wang Ming's average score is more than 70, display text "ideal", otherwise the display text "unsatisfactory results."
DECLARE @m int
Select @m=avg (Grade)
From STUDENT,SC
where Sc.sno = Student.sno and Sname= ' Wang Ming '
if (@m>=70)
print ' Results ideal '
Else
print ' performance is not ideal '

SQL class notes-data manipulation and functions