常用的SQL例句数据库开发所需知识__select_cl_id_from_where_studio_

当前位置: 数据库>mysql

常用的SQL例句数据库开发所需知识

来源: 互联网发布时间：2014-10-04

本文导语: --查看学生表的全部数据 select * from studio --插入一个新的学生信息 insert into studio(st_name,st_sex,st_age,st_add,st_tel) values("黄兰淇",0,36,'南充','13943943334') --查看class全部数据 select * from class --向class表增加两条条数据 insert into class(cl_clas...

--查看学生表的全部数据
select * from studio
--插入一个新的学生信息
insert into studio(st_name,st_sex,st_age,st_add,st_tel) values("黄兰淇",0,36,'南充','13943943334')
--查看class全部数据
select * from class
--向class表增加两条条数据
insert into class(cl_class,cl_coding,cl_o_time,cl_remark) values('新电实训班','GXA-ncs-001','2008-03-11','都是很优秀的朋友')
insert into class(cl_class,cl_coding,cl_o_time) values('阿坝师专实训班','GXA-ABSZ-001','2008-03-11')
--更新一条的数据条件的重要性
update class set cl_remark='真的是不错' where cl_id=5
--删除一条数据条件的重要性
delete from class where cl_id=7
--修改列标题
select cl_id as '班级主键',cl_class as '班级名称' from class
select 名字=st_name from studio
--使用文字串
select '名字是：',st_name from studio
--=============条件稍微复杂点的查增删改==============
--主要涉及到 or and not between in like > < = !> !< != () = is null is not null
--查询cl_id 大于 1 的所有信息
select * from class where cl_id>1
--使用 or
select * from class where cl_id10 or cl_class='百杰一班'
--使用and
select * from class where cl_id10 and cl_class='百杰一班'
--使用like 和 %
select * from class where cl_class like '百杰%'
select * from class where cl_remark like '%上午%'
--使用 between
select * from class where cl_id between 3 and 5
--使用 between 配合上 not
select * from class where cl_id not between 3 and 5
--使用 is not null
select * from class where cl_remark is not null
--使用 in
select * from class where cl_class in('千星一班','百杰二班')
--=================使用数学运算符======================
--主要涉及到 + = *
--查询Java相关课程分别要上多少周按照每周5天，每天6节课来计算
select '结果'=co_num/5/6 from course where co_name in ('Java基础','Java项目入门')
--==================使用汇总函数 ========================
--涉及到COUNT SUM AVG MAX MIN
--查询课时数小于50的课程一共有多少门
select count(*) from course where co_num35 and ho_id>2
--===========联合查询=======学云网-天轰穿-[url]www.ixueyun.com[/url]======
--使用union子句的查询称为联合查询，功能：将两个以上的查询结果集组合为一个单个结果集，该集中包括所有集中的全部行数据
--下面我们尝试将多个查询联合起来
select * from studio where cl_id=1
union
select * from studio where ho_id=1
union
select * from studio where st_age>=30
--下面我们继续利用上面的例题，增加上 All 看下效果
select * from studio where cl_id=1
union all
select * from studio where ho_id=1
union all
select * from studio where st_age>=30
--再继续利用，给他加上排序
select * from studio where cl_id=1
union all
select * from studio where ho_id=1
union all
select * from studio where st_age>=30
order by st_id
--===========连接查询==================
--连接查询，功能 - 将多个表中的数据查询出来放在一起
--内连接：使用比较运算符=>2
--再给他个排序
--order by st.st_id
--外连接：
--与内连接不同的是，内连接至少要有一个同属于两个表的行符合连接条件时才会返回行，外连接会返回符合任意条件的行
--他的表有主从之分，他用主表中的每行去匹配从表中的，与内连不同的是，他不会丢弃没有匹配的行，而是填充null给从结果集
--左外连接
select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'
from studio as st left outer join class as cl
on st.cl_id=cl.cl_id
where cl.cl_id>2
--多表
select tka.te_co_id as '课程安排编号'
,cl.cl_id as '班级编号',cl.cl_class as '班级名称'
,co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数'
,te.te_name as '老师姓名'
from te_kc_ap as tka left outer join class as cl
on tka.cl_id=cl.cl_id
left outer join
course as co
on tka.co_id=co.co_id
left outer join
teacher as te
on tka.te_id=te.te_id
--====================右外连结 =======================
select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'
from studio as st right outer join class as cl
on st.cl_id=cl.cl_id
where cl.cl_id>2
--多表
select tka.te_co_id as '课程安排编号'
,cl.cl_id as '班级编号',cl.cl_class as '班级名称'
,co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数'
,te.te_name as '老师姓名'
from te_kc_ap as tka
right outer join class as cl
on
tka.cl_id=cl.cl_id
right outer join teacher te
on
tka.te_id=te.te_id
right outer join course co
on
tka.co_id=co.co_id
--========完全连接==============
select st.st_id as '学生编号', st.st_name as '学生姓名',cl.cl_id as '班级编号',cl_class as '班级名称'
from studio as st full outer join class as cl
on st.cl_id=cl.cl_id
order by st.st_id
--多表
select tka.te_co_id as '课程安排编号'
,cl.cl_id as '班级编号',cl.cl_class as '班级名称'
,co.co_id as '课程ID',co.co_name as '课程名称',co.co_num as '课时数'
,te.te_name as '老师姓名'
from te_kc_ap as tka
full outer join class as cl
on
tka.cl_id=cl.cl_id
full outer join teacher te
on
tka.te_id=te.te_id
full outer join course co
on
tka.co_id=co.co_id
--==========交叉连接================
--该方式在不带where子句时，返回的是两个表中所有数据行的笛卡尔积（第一个表中的行乘以第二个表中的行）
--用学生和班级表做交叉查询
select st_name,cl_class from studio cross join class
select st_name,cl_class from studio,class
select st_name,cl_class from studio cross join class
--=========自连接===
-----------------先临时创建一个表-------------
create table zone(
id int primary key identity(1,1) not null,
z_zone varchar(30),
z_id int references zone(id))
--大家试下，这里是否可以给个默认值
select * from zone
insert into zone(z_zone) values('北京')
insert into zone(z_zone,z_id) values('北京',4)
insert into zone(z_zone) values('四川')
insert into zone(z_zone,z_id) values('成都',6)
insert into zone(z_zone,z_id) values('绵阳',6)
insert into zone(z_zone) values('江苏')
insert into zone(z_zone,z_id) values('南京',10)
insert into zone(z_zone,z_id) values('苏州',10)
insert into zone(z_zone,z_id) values('无锡',10)
insert into zone(z_zone,z_id) values('常州',10)
----------------------------------------------
--看下自连接的一般用处
select a.z_zone,b.z_zone from zone as a inner join zone as b on a.z_id=b.id
--扩展应用下
select b.z_zone,count(a.z_zone) as '辖区数' from zone as a inner join zone as b on a.z_id=b.id group by b.z_zone
--简单说就是自己连接自己，换言之对同一个表进行连接操作
select a.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as b on a.st_add=b.st_add
--我们发现有人等于自己，那么增加一个条件
select a.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as b on a.st_add=b.st_add and a.st_name!=b.st_name
--====学云网-天轰穿-[url]www.ixueyun.com[/url]==子查询============
--在一个SQL语句中镶入另一个SQL语句教镶套查询，而被镶入的这个SQL语句就被江湖人称子查询。是处理多表操作的附加方法
--子查询也称内部查询，而包含子查询的Select语句被诚为外部查询，子查询自身可以包括一个或者多个子查询，也可以镶套任意数量的子查询
--使用in的子查询
select * from studio where cl_id in (select cl_id from class where cl_id>2)
--使用 not in
select * from studio where cl_id not in (select cl_id from class where cl_id>2)
--使用比较运算符的子查询 -- any 表示子查询中任意的值 all 表示子查询中的每个值
--使用any
select * from class where cl_id>any(select cl_id from studio where st_age>30)
--使用all
select * from class where cl_id>all(select cl_id from studio where st_age>30)
--============一个分页的SQL语句========
select top 3 * from studio
where st_id>all(select top 3 st_id from studio order by st_id)
order by st_id

--使用 exists ,该关键字引入一个子查询的时候基本上是对数据进行一次是否存在的测试
--我们查询那些人所在的班级是编号为 1 的
select * from studio where exists(select cl_id from class where studio.cl_id=class.cl_id and class.cl_id=1)
--使用 not exists
select * from studio where not exists(select * from class where studio.cl_id=class.cl_id and class.cl_id=1) order by st_id
--基于查询生成新的表
select st_name into class_3 from studio where cl_id=3
--将数据批量插入一个表中
insert into class_3 select st_name from studio where cl_id=4
-----------------------sql 编程--------------
declare @max int;
--申明一个变量@max
set @max=1;
--为变量@max赋值
while @max