20141104 Database Join,union

Use new

Go

--------Common Method-----Check the sales department of people older than 35 full information-------

Select *from haha where age >35 and bumen = (select code from bumen where bumen = ' Sales Department ')

--------Folk prescription exists (know her, will use her good) hahaha-------------

Select *from haha where age >35 and exists (SELECT * from Bumen where Bumen.code=haha. Bumen and Bum En. bumen = ' Sales Department ')

--Name bumen in the Haha table as ' Department name ' and display the information in the Bumen (followed by the CEO message)-select name, sex, age, (select Bumen from Bumen where Bumen.code=haha. Bumen) as Department name, (select CEO from Bumen where Bumen.code=haha.bumen) as CEO from haha

--You can select the query, and then name the new column as the Add column

Select haha. Name, sex,age,Bumen.bumen,ceo from Haha,bumen

where Haha.bumen=bumen.code

--Some columns of information in the table can be added directly

Select Haha.name,sex,age,bumen.bumen, CEO from haha

Join Bumen on Haha.bumen =bumen. Code

--join Adding columns (notice how to express)

-------------above are three ways to pay attention to the expression format! ---------

---Join joins the table on the connection relationship of two tables---

INSERT into haha values (16, ' FA Shun Fung ', ' Male ', 41,5)

INSERT into bumen values (6, ' Security Department ', ' housekeeping ', null,null)

--Insert two lines of information in order to conveniently observe jion XXX on XXX role!

Select haha. Name, sex,age,bumen. Bumen, Ceo,zhineng from haha

Join bumen on haha. Bumen =bumen. Code

--

Select haha. Name, sex,age,bumen. Bumen, Ceo,zhineng from haha

Full join bumen on haha. Bumen =bumen. Code

- - Full put two copies of the table to extract all the information displayed , regardless of the information in two tables is not related !

Select haha. Name, sex,age,bumen. Bumen, Ceo,zhineng from haha

Left join bumen on haha. Bumen =bumen. Code

- left will display all the information on the table haha to be extracted and arrange it in the original order, regardless of whether the information is related in two tables!

Select haha. Name, sex,age,bumen. Bumen, Ceo,zhineng from haha

Right join bumen on haha. Bumen =bumen. Code

- Right will display all the information on the table bumen to be extracted , and in the original order, regardless of the information in the two tables is not related!

add ' rows '-------------Union query (add table information vertically)----------

Select *from haha where age >40

Union-requires two table ' column ' data types to correspond !

Select *from haha where age <30

--(Display table column name, display the first table column name !) Two tables of the same information, after the connection automatically go heavy ! )

Select Name,bumen from haha

Union

Select Ceo,code from Bumen

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

--Create Student Information table: School number, name, class, gender, language teacher number, math teacher number, English teacher number

CREATE TABLE Xuesheng

(

code int,

Name varchar (20),

Banji varchar (20),

Sex varchar (20),

Yuhao int,

Shuhao int,

Yinghao int,

)

Go

INSERT into Xuesheng values (1, ' Monday ', ' 2 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (2, ' Zheng er ', ' 1 classes ', ' female ', 1001,1007,1009)

INSERT into Xuesheng values (3, ' Zhang San ', ' 3 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (4, ' John Doe ', ' 2 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (5, ' Harry ', ' 3 classes ', ' female ', 1001,1007,1009)

INSERT into Xuesheng values (6, ' Zhao Liu ', ' 1 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (7, ' Tianqi ', ' 1 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (8, ' Sun Eight ', ' 3 classes ', ' female ', 1001,1007,1009)

INSERT into Xuesheng values (9, ' money Nine ', ' 2 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (10, ' Prince ', ' 3 classes ', ' Male ', 1001,1007,1009)

INSERT into Xuesheng values (11, ' Goujian ', ' 2 classes ', ' female ', 1001,1007,1009)

INSERT into Xuesheng values (12, ' Shi Xi ', ' 1 classes ', ' Male ', 1001,1007,1009)

Select *from Xuesheng

--Create a teacher's table: Teacher number, name, course, gender, date of birth

CREATE TABLE Jiaoshi

(

code int,

Name varchar (20),

Kecheng varchar (20),

Sex varchar (20),

Shengri varchar (20),

)

Go

INSERT into Jiaoshi values (1001, ' Wen Zhao ', ' language ', ' Male ', ' 1989-09-16 ')

INSERT into Jiaoshi values (1007, ' Li number ', ' math ', ' female ', ' 1993-09-1 ')

INSERT into Jiaoshi values (1009, ' out of the picture ', ' English ', ' Male ', ' 1988-09-9 ')

Select *from Jiaoshi

--update Jiaoshi Set Kecheng = ' English ' where code =1009

--Modified method of use update haha set bumen=1 where bumen = ' sales Department '

--drop Table Jiaoshi--Deleting tables

--delete Jiaoshi where code =1001--deletes a column in a table

--Create a score table: Chinese score, math score, English score, student study number

CREATE TABLE Fenshu

(

code int,

Yufen Decimal (18,2),

Shufen Decimal (18,2),

Yingfen Decimal (18,2),

)

Go

INSERT into Fenshu values (1,98,97,99)

INSERT into Fenshu values (2,87,76,88)

INSERT into Fenshu values (3,59,68,99)

INSERT into Fenshu values (4,88,99,77)

INSERT into Fenshu values (5,88,70,97)

INSERT into Fenshu values (6,88,47,69)

INSERT into Fenshu values (7,74,87,77)

INSERT into Fenshu values (8,98,97,99)

INSERT into Fenshu values (9,90,46,99)

INSERT into Fenshu values (10,98,100,99)

INSERT into Fenshu values (11,90,91,79)

INSERT into Fenshu values (12,98,57,99)

Select *from Fenshu

--Insert virtual data, then query operation:

--1. Check the information of the students with the highest language scores in this exam

Select top 1 * from Fenshu ORDER by Yufen desc--find information on the highest score in the language

Select *from Xuesheng WHERE code =

(select top 1 code from Fenshu ORDER BY Yufen Desc)

--2. Find information on the instructor of the students who have the lowest math scores in this exam

Select top 1 *from fenshu ORDER by Shufen

Select *from Xuesheng WHERE code = (select top 1 code from Fenshu Order by Shufen)

Select *from Jiaoshi WHERE code = (select Shuhao from Xuesheng where code = (select top 1 code from Fenshu ORDER by Shufen ))

--3. query rollup becomes a table: each course score, student name, class, Instructor's name

Select Xuesheng. Name, Banji,fenshu.yufen,shufen,yingfen, (select name from Jiaoshi where code = (SELECT DISTINCT Yuhao from Xuesheng) as Language teacher, (select name from Jiaoshi where code = (SELECT DISTINCT Shuhao from Xuesheng)) as Math teacher (select name from Jiaoshi WHERE code = (SELECT DISTINCT Yinghao from Xuesheng)) as English teacher from Xuesheng

Join Fenshu on Xuesheng.code=fenshu.code

Join Jiaoshi on Xuesheng.yuhao=jiaoshi.code

-In the new virtual table, you can use the (select ... As xxx any insert information column! For example

Select name, Sex, age, (select Bumen from Bumen where bumen.code=haha.bumen) as department name (select CEO from Bumen where Bumen.cod E=haha.bumen) as CEO from haha

--4. Query the highest math score in each class

--select Xuesheng.name,banji,fenshu.shufen from Xuesheng

Join Fenshu on Xuesheng.code =fenshu.code

GROUP BY Banji-this time xuesheng.name, Fenshu.shufen are not valid because they are not aggregate functions or group by is not included.

Select Banji,zuigaoshu from

(Select Banji,name, (select MAX (Shufen) from Fenshu) as Zuigaoshu from Xuesheng) GROUP by Banji

--The new virtual Table statement Plus () represents a new table! can be manipulated.

Select Banji, Max (Shufen) as the highest math score from Xuesheng per class

Join Fenshu on Xuesheng. Code=fenshu. Code

GROUP BY Banji-this shows the highest math score grouped by class

--Group on the last! Aggregate functions can only function on the columns of a table, the virtual SELECT statement (SELECT ...) In the query!

$$$$$$$$ Teacher Solution $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Select Class,max (math) from student

Join score on student.code = Score.stucode

GROUP BY class

Select Max (math), class from score--writes the table to query according to the table in which the first column is located

Join student on student.code = Score.stucode

GROUP BY class

Select Max (math), class from

(Select Math, (select class from student where Student.code = Score.stucode) as class from score) as NewTable

GROUP BY Class--under (), query the SELECT statement

--5. Information on the teachers of the class with the highest average of the language courses

Select *from Xuesheng

Select *from Jiaoshi

Select *from Fenshu

Select *from Jiaoshi WHERE code = (

Select distinct Yuhao from Xuesheng where banji= (select top 1 Banji from Fenshu

Join Xuesheng on Fenshu.code=xuesheng.code

Group BY Banji Order by AVG (Yufen) desc)

)

$$$$$$$$$ Teacher Solution $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Select *from Teacher where code= (

Select top 1 chteacher from student where class= (

Select top 1 class from student

Join score on score.stucode = Student.code

Group BY class ORDER by AVG (Chiese) desc)

)

20141104 Database Join,union