数据库基本原理==嵌套查询

/*嵌套查询（nested query）
select——from-where语句被称为语句块
一个语句块嵌入到另一个语句块的where或者having 语句短语条件的查询
称为嵌套查询
*/
select sname 
from student		--外层查询或者父查询
where sno in 
		(select sno
		from SC				--内层查询或者子查询
		where Cno = '2');
/*
注释：
子查询的select语句不能使用order by 语句，该字句只可以对最终结果进行查询
*/
--in
select sno,sname,sdept
from student			--父查询：查询内容及源头
where sdept in(
select sdept
from student			--子查询：查询条件刘晨所在系
where sname = '刘晨');
--子查询的条件不依赖于父查询--不相关查询
 --解法二自身连接
 select s1.sno,s1.sname,s1.sdept
 from student s1,student s2
 where s1.sdept = s2.sdept and s2.sname =  '刘晨';\
 --

--创建数据库yygl
create database YYGL
on
(
name='YYGL_data',
filename='D:\sql\data\YYGL.mdf',
size=10 mb,
maxsize=50 mb,
filegrowth=5%
)
log on
(
name='YYGL_1log',
filename='D:\sql\data\YYGL.ldf',
size=2 mb,
maxsize=5 mb,
filegrowth= 1 mb
);
--创建基本表并插入数据
USE YYGL;

create table Employees
(
EmployeeID char(6)  NOT NULL primary key,/*员工编号、主键*/
Name char(10) NOT NULL,/*姓名*/
Education char(4) NOT NULL,		/*学历*/
Birthday date NOT NULL,		/*出生日期*/
Sex bit NOT NULL  default(1),			/*性别，默认值为1*/
WorkYear tinyint  NULL,		/*工作时间*/
Address varchar (40)  NULL,	/*地址*/
PhoneNumber char(12)  NULL,	/*电话号码*/
DepartmentID char(3) not null	/*员工部门号，外键*/
);

create table Departments
(
DepatmentID char(3)  not null primary key, /*部门编号，主键*/
DepatmentName char(20) not null,/*部门名*/
Note varchar(100) null,/*备注*/
);
create table Salary
(
EmployeeID char(6)  not null primary key ,/*员工编号，主键*/
InCome float null,/*收入*/
OutCome float null,/*支出*/
);
--插入数据

insert into Employees
values
( '000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668','2'),
( '010008','李四','本科','1976-03-28',1,3,'北京东路100-2','83321321','1'),
( '020010','王蓉','硕士','1982-12-09',1,2,'四牌楼10-0-108','83792361','1'),
( '020018','李丽','大专','1960-07-30',0,6,'中山东路102-2','83413301','1'),
( '102201','刘明','本科','1972-10-18',1,3,'虎踞路100-2','83606608','5'),
( '102208','朱军','硕士','1965-09-28',1,2,'牌楼巷5-3-106','84708817','5'),
( '108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','83346722','3'),
( '111006','张石兵','本科','1974-10-01',1,1,'解放路34-1-203','84563418','5'),
( '210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','83467336','3'),
( '302566','李玉','本科','1968-09-20',1,3,'热和路209-3','58765991','4'),
( '308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','83308901','4'),
( '504209','陈琳','大专','1969-09-03',0,5,'汉中路120-4-12','84468158','4')
;
insert into Departments
values
( '1','财务部',null),
( '2','人力资源部',null),
( '3','经理办公室',null),
( '4','研发部',null),
( '5','市场部',null);
--------
insert into Departments
values
('000001',2100.8,123.09),
('010008',1582.62,88.03),
( '102201',2569.88,185.65),
( '111006',1987.01,79.58),
( '504209',2066.15,108.0),
( '302566',2980.7,210.2),
( '108991',3259.98,281.52),
( '020010',2860.0,198.0),
( '020018',2347.68,180.0),
( '308759',2531.98,199.08),
( '210678',2240.0,121.0),
( '102208',1980.0,100.0);