Practice using the underlying SQL statement

Most of the SQL statements in this statement come from "Introduction to Database System" (fourth edition) Wang Shan & Shaman Xuan Munsu, is our class with the teaching materials, feel very good, summed up the third chapter of the SQL statement, more practical, I hope to help everyone. Summarized below, can be used to learn the underlying SQL statements.

Set up a database

CREATE DATABASE db_student

Create a table

CREATE TABLE Student

(Sno CHAR (9) PRIMARY KEY,--main code

Sname CHAR,--Unique value

Ssex CHAR (2),

Sage SMALLINT,

Sdept CHAR (20)

);

CREATE TABLE Course

(Cno CHAR (4) PRIMARY KEY,

Cname Char (40),

Cpno CHAR (4),

Ccredit SMALLINT,

FOREIGN KEY (cpno) REFERENCES Course (Cno)

);

CREATE TABLE SC

(Sno CHAR (9),

Cno CHAR (4),

Grade SMALLINT,

PRIMARY KEY (SNO,CNO),

FOREIGN key (Sno) REFERENCES Student (Sno),--External code

FOREIGN KEY (Cno) REFERENCES Course (Cno)

);

Data

Table Operations

ALTER TABLE Student add s_entrance date--Add column

ALTER TABLE student ALTER COLUMN Sage int--modify field type

ALTER TABLE course add unique (Cname)--Add uniqueness Constraint

drop table student--Delete base table

DROP table Student cascade--Delete base table and related dependent objects

Create an index

Drop INDEX Stusname

Querying data

Select Sno,sname from Student

Select Sname,sno,sdept from Student

Select Sname,2004-sage from Student

Select Sname, ' Year of Birth: ', 2004-sage, Lower (sdept) from student--query result the second column is an arithmetic expression

Select Sname name, ' Year of Birth: ' Birth,2004-sage birthday,lower (sdept) department from Student--lower () lowercase letters

Select Sno from SC

Select DISTINCT SNO from sc--eliminate duplicate rows

Select Sno from SC

Select All Sno from SC

Select sname from student where sqept= ' CS '

--=, >, <, >=, <=,! =, <>,!>,!< operators compared

Select Sname,sage from student where sage<20

SELECT DISTINCT Sno from SC where sage<20

Select Sname,sdept,sage from student where Sage between and 23

Select Sname,sdept,sage from student where sage not between and 23

Select Sname,ssex from student where sdept in (' CS ', ' MA ', ' is ')

Select Sname,sage from student where sdept isn't in (' CS ', ' MA ', ' is ')

SELECT * FROM student where Sno like ' 200215121 '

SELECT * FROM student where sno= ' 200215121 '

--Character matching

--% arbitrary length string, _ any single character, escape escape character

Select Sname,sno,ssex from student where sname like ' Liu '

Select sname from student where sname like ' Ouyang __ '

Select Sname,sno from student where sname like ' __ positive% '

Select Sname,sno,ssex from student where sname don't like ' Liu% '

Select Cno,ccredit from course where cname like ' db\_design ' escape ' \ '

SELECT * FROM course where cname like ' db\_%i__ ' escape ' \ '

Select Sno,cno from SC where grade is null--null null value

Select Sno,cno from SC where grade are NOT NULL

Select sname from student where sdept= ' CS ' and sage<20

Select Sname,sage from studnet where sdept= ' CS ' or sdept= ' MA ' or sdept= ' is '

Select Sno,grade from SC where cno= ' 3 ' ORDER BY grade DESC--ORDER by sort

SELECT * FROM student ORDER BY sdept,sage DESC--Null value max

--Aggregation function

Select COUNT (*) from student-count () rows

Select COUNT (Distinct sno) from SC

Select AVG (grade) from SC where cno= ' 1 '--avg () average

Select Max (grade) from SC where cno= ' 1 '--MAX () max

Select SUM (ccredit) from Sc,course where sno= ' 200215012 ' and Sc.cno=course.cno-sum () total

--Grouping

Select Cno,count (SNO) from SC GROUP by CNO

Select Sno from SC GROUP by SNO has Count (*) >3--having gives criteria for selecting groups

--Connection Query

Select Student. *,sc.* from STUDENT,SC where Student.sno=sc.sno

Select Student.sno,sname,ssex,sage,sdept,cno,grade from STUDENT,SC where Student.sno =sc.sno

Select First.cno,second.cpno from Course first,course second Fwhere first.cpno=second.cno--Self connection

Select Student.sno,sname,ssex,sage,sdept,cno,grade from student left off join SC in (STUDENT.SNO=SC.SNO)--Outer JOIN

--from student left out join SC using (SNO)

Select Student.sno,sname from STUDENT,SC where Student.sno=sc.sno and sc.cno= ' 2 ' and sc.grade>90

Select Student.sno,sname,cname,grade from Student,sc,course where Student.sno=sc.sno and Sc.cno=course.cno

Select Sname from student where Sno on (select Sno from SC shere con= ' 2 ')

Select Sdept from student where Sname= ' Liu Chen '

Select Sno.sname,sdept from student where sdept= ' CS '

--Nested query

Select Sno,sname,sdept from student where sdept in (select Sdept from studnet where Sname= ' Liu Chen ')

Select Sno,sname,sdept from student where sdept in (' CS ')

Select s1.sno,s1.sname,s1.sdept from student s1,student S2 where s1.sdept =s2.sdept and S2.sname= ' Liu Chen '

Select Sno,sname from student where Sno in (select Sno from SC where CNO in (select CNO from course where cname= ' Information system '))

Select Student.sno,sname from student, Sc,course where Student.sno=sc.sno and Sc.cno =course.cno and course.cname= ' information systems '

--the result of the inner query is a value, so you can use = instead of In

Select Sno,sname,sdept from student where sdpet= (Se3lect sdept from studnet where Sname= ' Liu Chen ')

Select sno,sname,sdept from student where (select sdept from student where Sname= ' Liu Chen ') = sdept

Select Sno,cno from SC x where grade >= (select AVG (grade) from SC y where Y.sno=x.sno)

Select AVG (grade) from SC y where y.sno= ' 200215121 '

Select Sno,cno from SC x where grade>=88

Select Sname,sage from student where Sage <any (SELECT sage from student where sdept= ' CS ') and sdept <> ' CS '

Select Sname,sage from student where sage< (select Max (sage) from student where sdept= ' CS ') and sdept <> ' CS '

Select Sname,sage from student where Sage < All (select Sage from student where sdept = ' CS ')

Select Sname,sage from student where sage< (select min (sage) from student where sdept= ' CS ') and sdept <> ' CS '

Select Sname from student where exists (SELECT * from SC where sno=student.sno and cno= ' 1 ')

Select sname from student where isn't exists (SELECT * from SC where sno=student.sno and cno= ' 1 ')

Select sno.sname,sdept from student S1 where exists (select * from STUDETN S2 where s2.sdept=s1.sdept and S2.sname= ' Liu Chen ')

Select sname from student where isn't exists (SELECT * from course where NOT EXISTS (SELECT * from SC where Sno=student.sno a nd cno=course.cno))

SELECT DISTINCT Sno Frome SC Scx where NOT EXISTS (SELECT * from SC scy where scy.sno= ' 200215122 ' and NOT EXISTS (SELECT * From SC Scz where Scz.sno=scx.sno and Scz.cno=scy.cno))

Http://zxlovenet.cnblogs.com

Collection Query

SELECT * FROM student where sdept = ' CS ' UNION SELECT * from student where sage<=19--union and operates

Select Sno from SC where cno= ' 1 ' union select Sno from SC where SC where cno= ' 2 '

SELECT * FROM student where sdept= ' CS ' intersect select * from student where sage<=19--intersect intersection operation

SELECT * FROM student where sdept= ' CS ' and sage<=19

Select Sno from SC where cno= ' 1 ' intersect select Sno from SC where cno= ' 2 '

Select Sno from SC where cno= ' 1 ' and sno in (select So from SC where cno= ' 2 ')

SELECT * FROM student where sdept= ' cs ' except select * from student where sage<= '--except difference operation

SELECT * FROM student where sdept = ' cs ' and sage>19

Inserting data

INSERT into student (Sno,sname,ssex,sdept,sage) VALUES (' 200215128 ', ' Chen Dong ', ' male ', ' is ', ' 18 ')

INSERT into student values (' 200215126 ', ' Zhang Chengmin ', ' male ', ' + ', ' CS ')

Insert INTO SC (SNO,CNO) VALUES (' 200215128 ', ' 1 ')

INSERT into SC values (' 200215128 ', ' 1 ', null)

Update data

CREATE TABLE Dept_age (sdept char (avg_agea) smallint)

Insert into Dept_age (sdept,avg_age) Select Sdept,avg (SAGE) from student group by sdept

Update student set sage=22 where sno= ' 200215121 '

Update student Set Sage=sage+1

Update SC set grade=0 where ' cs ' = (select sdept from student where Student.sno=sc.sno)

Update is_student set sname= ' Liu Chen ' where sno= ' 200215122 '

Update student set sname= ' Liu Chen ' where sno= ' 200212122 ' and sdept= ' is '

Delete from student where sno= ' 200215128 '

Delete from is_student where sno= ' 200215129 '

Delete from student where sno= ' 200215129 ' and sdept= ' is '

Delete from SC

Delete fro SC where ' cs ' = (select sdept from student where Student.sno=sc.sno)

Delete operation

Delete from Student where sno= ' 200215128 '

Delete from SC

Delete from SC where ' cs ' = (select Sdept from Student where STUDENT.SNO=SC. Sno)

Create a View

CREATE VIEW Is_student

As

Select Sno,sname,sage from student where sdpet= ' is '

CREATE VIEW Is_student

As

Select Sno,sname,sage from student where sdept= ' are ' with CHECK option

Create View is_s1 (Sno,sname,grade)

As

Select Student.sno,sname,grade from STUDENT,SC where STUDENT,SC where sdept== ' is ' and Student.sno=sc.sno and sc.cno= ' 1 '

Http://zxlovenet.cnblogs.com

CREATE VIEW Is_s2

As

Select Sno,sname,grade from IS_S1 where grade>=90

Create View bt_s (Sno,sname,sbirth)

As

Select Sno,sname,2004-sage from Student

Create View S_g (SNO,GAVG)

As

Select Sno,avg (grade) from SC Group by Sno

Create View f_student (f_sno,name,sex,age,dept)

As

SELECT * FROM student where ssex= ' woman '

Delete a view

Drop View IS_S1 Cascade

Select Sno,sage from Is_student where sage<20

Select Sno,sage from student where sdept= ' is ' and sage<20

Select Is_sutdent.sno,sname from IS_STUDENT,SC where Is_student.sno=sc.sno and sc.cno= ' 1 '

SELECT * FROM S_g where gavg>=90

Group

Select Sno,avg (grade) from SC Group by Sno

Select Sno,avg (grade) from SC where AVG (grade) >=90 GROUP by Sno

Select Sno,avg (grade) from SC GROUP by SNO have avg (grade) >=90

First line
Q Q:121866673
QQ Group:313686804 (verified: Blog Park)
Source:http://zxlovenet.cnblogs.com
Statement:This article was originally published in the blog Park, the author for the first line of this article welcome reprint, but without the author's consent must retain this paragraph statement, and in the article page obvious location to the original link, otherwise considered infringement.

Stored Procedures

CREATE PROCEDURE Insert_pass

@pass NVARCHAR (50)

As

BEGIN

DECLARE @count INT

Select @count = (select count (*)

From list

WHERE pass = @pass)

IF @count = 0

BEGIN

INSERT into List

(pass)

VALUES (@pass)

END

END

Executing stored procedures

Call Procedure Insert_pass (2011)

To delete a stored procedure

drop procedure Insert_pass ()

Trigger

CREATE TRIGGER Insert_table1

On table1

For INSERT, DELETE

As

BEGIN

UPDATE table2

SET count = (SELECT count (*)

From table1)

WHERE id = 1;

END;

Http://zxlovenet.cnblogs.com

Cursor: A cursor is a data buffer opened by the system for the user, which holds the results of the execution of the SQL statement, and each cursor area has a name. The user can retrieve the record through the cursor and assign it to the main variable, which is further processed by the master language.

PS: Above source "Database system Introduction" (fourth Edition) Wang Shan & Shaman Xuan Munsu