from Student,score ,subject
where StudentDB.id=score.id and score .id=subject.id and Student.name='张三' and subject.sname='C#'
------------------------------------------------------------------------------
-----------------------------------------------------------------子查询语句 -------------------------------------------------------------------------------
--------------------不知道李斯文的年龄,查询年龄大于李斯文年龄的人的信息 select stuname,stuno,stuage,stuseat,stuaddress from Student
where age>(select age from Student where stuname='李斯文')
---------------------在sql server 数据库中,在products 表中最贵的产品名称和价格
---------------------方法一
select ProductName,UnitPrice from Products
where UnitPrice=(select Max(UnitPrice) from Products) ---------------------方法二
select top 1 UnitPrice , ProductName from Products order by UnitPrice desc
---------------------在Products表中查询,查询所有价格高于平均价格的产品 ---------------------名称和价格,正确的SQL语句为(1条SQL) select ProductName,UnitPrice from Products
where UnitPrice>(select Avg(UnitPrice) from Products)
------------如果子查询充当查询条件\子查询返回的结果只能有一行 ------------如果有多行，可以使用in ，exists 来实现
------------------------------------------------------------------子查询充当表达式
select orderid,customerid,orderdate, (
select companyname from customers
where customers.CustomerID =orders.CustomerID )
from orders
--------------------案例:姓名,地址,_学生表的,,,成绩,科目_成绩表编号 select student.name ,student.address, from student
where Student.id in (
select id from score
)
----------------------子查询,不及格的学生姓名 ----------------------用in语句完成子查询 select student.name ,student.address from student
where Student.id in (
select id from score where score<60 )
------------------------没有参加考试的同学的名字,地址 select snumb, sname ,address from Info where Info.snumb not in (
select snumb from score )
-----------------------参加考试的同学的名字,地址,学号 select snumb, sname ,address from Info where Info.snumb in (
select snumb from score )
----------------------------------exists子查询
----------------------------------返回的是true 或者 false值
-----------------------查询info和score相等的数据(参加考试的学生) select sname from Info where not exists (
select * from score where Info.snumb=score.snumb )
---------------------------------查找系统中的数据库信息 use master go
----------mater库中系统视图 sysdatabases存储了服务器中所有的数据库的信息 select * from sysdatabases
---------------------------------如果数据库已经存在,那么就删了重新创建,如果不存在就新建
-----判断系统中是否有要创建的数据库 if exists (
select * from sysdatabases where name ='northwind1' )
------如果系统中存在该数据库则删除 drop database northwind1 -----创建数据库
create database northwind1
----------------------------------系统中判断库里是否已经存在表sysobjects use LibraryDB go
--------判断是否存在你要建的数据表 if exists (
select * from sysobjects where name ='student' )
------如果系统中存在该数据表则删除 drop table student -----创建数据表
create table student (
name varchar (20) primary key )
---------------------------------查询10-20之间的数据
select top 10 * from orders --------从11条开始的前10条数据 where orderid not in (
select top (10) orderid from orders ---------orderid 排除前10条数据 )
order by orderid asc
----------------------------查找20-30之间的数据
select top 10 * from orders --------要显示几行数据 where orderid not in (
select top (10*(3-1)) orderid from orders ---------排除前20行 )
order by orderid asc
-----------------------------10-15之间的数据
select top 5 * from orders -------要显示几行数据 where orderid not in (
select top (5*(3-1)) orderid from orders ---------排除前10行数据 )
order by orderid asc
--------------------------------取出25-30之间的数据
select top 5 orderid,customerid,orderdate,shipname from orders --------要显示几行数据
where orderid not in (
select top (5*(6-1)) orderid from orders ---------排除前行数据 not in )
order by orderid asc
-------------分页子查询语法：eg：查询10-15行的数据 --select top 每页行数 * from 表名 --where pk not in
--(select top (每页行数 * （页数-1）) pk from 表名) --order by pk asc ---排序
------------随机从表中抽取10条记录 select top 10 * from orders
order by NEWID() -----------------按guid
-----------T_sql编程 ---声明变量
declare @seatnum int----------声明变量
set @seatnum =8 -------------给变量赋值，方法一 select @seatnum =5------------给变量赋值，方法二
print '变量为：'+convert (varchar ,@seatnum )---------------输出变量,类型转换一
--print '变量为：'+cast (@seatnum as varchar)----------------输出变量，类型转换二
-----数据类型的转换
-- Cast （表达式 as 目标数据类型） -- Convert（目标数据类型 ， 表达式 ）
-----------给变量赋值，赋的是查出来的结果
---------语法：select @变量=值 from 表 where 条件 declare @num int
select @num = COUNT (snumb) from info
print '学生人数是：'+cast(@num as varchar)
--查询，已知学号3，获取学生姓名，赋给变量 declare @name varchar(8) ---声明变量
select @name =sname from INFO where snumb=3 ---给变量赋值
print '学生姓名是：'+@name -----输出
create table student (
stuname varchar(10), stuno varchar(10), stusex varchar(2), stuage int , stuseat int ,
stuaddress varchar(50) )
insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('张秋丽','s25301','男',18,1,'北京海淀')
insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('李文才','s25302','男',28,2,'地址不详')
insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('李斯文','s25303','女',22,3,'河南洛阳')
insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('欧阳俊雄','s25304','男',28,4,'新疆')
insert into student(stuname,stuno,stusex,stuage,stuseat,stuaddress) values ('梅超风','s25318','女',23,5,'地址不详')
-----查找李文才的左右同桌 ----先查找?李文才?的座位号 declare @seatnum int
select @seatnum =stuseat from student where stuname ='李文才' print '李文才de同桌'
select * from student where stuseat=@seatnum -1 or stuseat=@seatnum +1
------if else
-----案例：计算平均年龄，如果大于30，输出平均年龄偏大
---------输出年龄最大的两个学生，如果小于30，输出平均年龄正常 ---------输出平均年龄最小的两个同学 ---------1、声明变量
declare @avgage decimal(18,2) ---------2、赋值
select @avgage =AVG (stuage) from student ---------3、判断 if (@avgage >30) begin
print '平均年龄偏大' +cast(@avgage as varchar)
select top 2 * from student order by stuage desc-----年龄最大的两个人 end
else if (@avgage <30)