数据库--三

表查询

单表查询

select语句

  		select...from tbname    （指定表中检索）
  	例1：*代表表中所有列    
  		select *from xsqk;  
  	例2：检索表中学生的学号和姓名    
  		select学号,姓名from xsqk; 
  	例3：定义列别名  将姓名，学号改为sno，sname（只改变显示时的，不动原表）  
  		select 学号 as sno,姓名 as sname from xsqk;     
  	例4：查询经过计算的列(将表中的出生年月查询时显示成年龄)   
  		select学号,姓名,year(now())-year(出生年月) as 年龄 from xsqk;
  	将表中的年龄显示时改成出生年份
  		select sname,year(now())-sage as 出生年份 from student;  
  	例5：select distinct 学号 from xscj;  (distinct 消除重复行)
  	例6：select *from xsqk limit 2,2; 

select …from where

  	1.比较运算符  <>=不等于可以是<> ,也可以是!= 
  		xsqk表中检索年龄大于37的学生的学号、姓名和年龄信息
  		select 学号,姓名,year(now())-year(出生年月) as 年龄 from xsqk where (year(now())-year(出生年月))>37;
  	2. 运算范围符 [not ] between...and:判断值是否在指定的范围内   
  		从xsqk表中检索年龄在37至38范围的学生的学号、姓名和年龄信息。
  		select 学号,姓名,Year(now())-Year(出生年月) as 年龄 from xsqk where Year(now())-Year(出生年月) between 37 and 38;
  	3. 模糊查找
  		例一： 从xsqk表中检索姓王且名字为两个字的学生的学号，姓名信息。  
  			select 学号,姓名 from xsqk where 姓名 like '王_';  
  		例二：从xsqk表中检索姓王的学生的学号，姓名信息。  
  			select 学号,姓名 from xsqk where 姓名 like '王%';    
  		例三： 从xsqk表中检索姓张且第二个字为—的学生的学号，姓名信息。
  			select 学号,姓名from xsqk where 姓名 like '张\_%';  
  			select学号,姓名from xsqk where 姓名 like '张|_%' escape '|'; 
  			从xsqk表中查询学号以1或2结尾的学生的学号,姓名,专业信息。
  			select 学号,姓名,专业 from xsqk where 学号 regexp '[12]$';     
  			从xsqk表中检索姓王的学生的学号，姓名信息。        
  			select 学号,姓名 from xsqk where 姓名 regexp '^王';    
  	4、列表运算符
  		从xsqk表中检索计算机应用与维护和信息管理专业学生的学号，姓名，专业信息
  		select 学号,姓名,专业 from xsqk where 专业 in ('计算机应用与维护','信息管理'); 
  	5、空值判断符
  		从xsqk表中检索总学分是空的学生的学号,姓名,总学分信息
  		select 学号,姓名,总学分 from xsqk where 总学分 is null;  
  	6、逻辑运算符
  		从xsqk表中检索信息管理专业男生的学号,姓名,性别,专业信息。
  		select 学号,姓名,性别,专业 from xsqk where 专业='信息管理' and 性别='男';
  		从xsqk表中检索“计算机应用与维护”或“信息管理”专业学生的学号,姓名,专业信息。
  		select 学号,姓名,专业 from xsqk where 专业='计算机应用与维护' or 专业='信息管理';
  		从xsqk表中检索年龄不大于38岁的学生的学号，姓名，年龄信息。
  		select 学号,姓名,Year(now())-Year(出生年月) as '年龄' from xsqk where not ((Year(now())-Year(出生年月))>38);

select …from group by （进行分组）

  	1.sum:求和
  		从xscj表中检索被选修的各门课程的总成绩    
  		select课程号,sum(成绩) as总成绩 from xscj group by课程号;    
  	2、avg:求平均
  		从xscj表中检索被选修的各门课程的平均成绩。      
  		select课程号,avg(成绩) as平均成绩from xscj group by课程号;  
  	3、max:求最大值   min:求最小值
  		从xscj表中检索101这门课程的最高成绩，最低成绩     
  		select max(成绩) as 最高成绩,min(成绩) as 最低成绩 from xscj where 课程号='101';  
  	4、count:统计
  		统计xsqk表中管信学生人数   
  		select count(*) from xsqk where 院系='管信';    
  		统计xsqk表中各专业学生人数
  		select 专业,c ount(学号) as '人数' from xsqk group by 专业;   
  		统计xscj表中每门课程被选修的学生人数
  		select 课程号,count(学号) as '人数' from xscj group by课程号;   

Where ----group by----having

  	在xscj表中查找平均成绩在80分以上的学生的学号和平均成绩
  		select 学号,avg(成绩) as ‘平均成绩’ from xscj group by 学号  having avg(成绩)>80;

order by----排序

  	查询xsqk表中学生的数据，并按出生年月降序排序
  		select * from xsqk order by 出生年月 desc;  (desc表示降序)
  	查询xsqk表中年龄最大的两名学生的学号，姓名，年龄信息。
  		select 学号,姓名 year(now())-year(出生年月) as '年龄' from xsqk  order by year(now())-year(出生年月) desc limit 2;

case when

  Case 表示搜索 ，when 当什么时候 ，then 返回 ,end 结尾，这种方法效率高
  	select sum(case sex when '男' then 1 else 0 end ) 男生人数, 
  	sum(case sex when '女' then 1 else 0 end ) 女生人数 from emp ;

多表查询

一：连接查询

内连接

  	例1：从stuscore数据库中检索选修了课程的学生的院系，学号，姓名，课程号，成绩信息。
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk inner join xscj on xsqk.学号=xscj.学号;
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk,xscj where  xsqk.学号=xscj.学号;
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk inner join xscj using(学号);
  	例2：从stuscore数据库中检索选修了课程的学生的院系，学号，姓名，课程号，课程名，成绩信息。
  		select 院系,xsqk.学号,姓名,xskc.课程号,课程名,成绩 from xsqk inner join xscj on xsqk.学号=xscj.学号 join xskc on xscj.课程号=xskc.课程号;
  		select 院系,xsqk.学号,姓名,xskc.课程号,课程名,成绩 from xsqk ,xscj,xskc where xsqk.学号=xscj.学号 and xscj.课程号=xskc.课程号;
  		select 院系,学号,姓名,xskc.课程号,课程名,成绩 from xsqk inner join xscj using(学号) join xskc using(课程号);
  	例3：从stuscore数据库中检索选修了课程的女生的院系，学号，姓名，课程号，成绩信息。
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk join xscj on xsqk.学号=xscj.学号 where 性别='女';
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk,xscj where xsqk.学号=xscj.学号 and 性别='女';
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk join xscj  using(学号) where 性别='女';

自连接

  	  例1：从xscj表中检索每个学生每两门课程的成绩。
  		select a.学号,a.课程号,a.成绩,b.课程号,b.成绩 from xscj a join xscj b on a.学号=b.学号 where a.课程号<b.课程号
  		注意：自连接要给同一个表不同的别名

外连接

  	  例1：左外连接
  		select 院系，xsqk.学号，姓名，课程号，成绩from xsqk left outer join xscj on xsqk.学号=xscj.学号；
  	  例2：右外连接
  		select 院系,xsqk.学号,姓名,课程号,成绩 from xsqk right outer join xscj on xsqk.学号=xscj.学号;

二、子查询

1、FROM 子句中的子查询

  	  例1：查询信息管理专业中年龄大于等于38岁的学生的信息。
  		select * from (select 专业, 学号,姓名,Year(now())-Year(出生年月) as 年龄 from xsqk) as student where 专业='信息管理' and 年龄>=38;

2、WHERE 子句中的子查询

  	 （1）IN|NOT IN
  		例1：查询选修了“101”课程的学生的学号和姓名信息。
  		  select 学号,姓名 from xsqk where 学号 in(select 学号 from xscj where 课程号='101');
  		例2：查询选修了“数据结构”课程的学生的学号和姓名信息。
  		  select 学号,姓名 from xsqk where 学号 in (select 学号 from xscj where 课程号 in (select 课程号 from xskc where 课程名='数据结构'));
     （2）比较运算子查询
  		例1：查询除了”信息管理0201“班的其它班中年龄不大于”信息管理0201“班中最小年龄学生的学号，姓名，班级，年龄信息。
  		  select 学号,姓名,班级,(Year(now())-Year(出生年月)) as 年龄 from xsqk  where (Year(now())-Year(出生年月))<=(select min(Year(now())-Year(出生年月)) from xsqk where 班级='信息管理0201') and 班级!='信息管理0201';
  		  select 学号,姓名,班级,(Year(now())-Year(出生年月)) as 年龄 from xsqk  where (Year(now())-Year(出生年月))<=all (select Year(now())-Year(出生年月) from xsqk where 班级='信息管理0201') and 班级!='信息管理0201';
  		例2：从stuscore数据库中检索选修了101课程且成绩大于或等于85的学生的学号和姓名信息。
  		  select 学号,姓名 from xsqk where 学号=any(select 学号 from xscj where 课程号=‘101’ and 成绩>=85)；

（3）EXISTS子查询

  		例1：查询选修了101课程的学生的学号和姓名信息。
  		  select 学号,姓名 from xsqk where exists(select 学号 from xscj where 学号=xsqk.学号 and 课程号='101');

三、合并查询结果

  	使用UNION关键字,将多条查询语句的结果合并在一起显示
  	一种是查询结果不重复（过滤掉重复的记录），
  	另一种是保留所有查询结果。
  	检索结果的列名称问题：第一个select语句的列名而定。

  	例一：select a,b from t1 union select b,a from t2;
  	例二：select a,b from t1 union all select b,a from t2;

  小贴士
  	where用于制定筛选条件select...from tbname where    <condition>   （从表里取出的数据用where后边加条件过滤）

  	group by将查询结果按指定列进行分组  select...from tbname  group by  having （从表里取出的数据用group给分组） 
  	（having为可选参数，用于对分组后的结果进行筛选）

  	select...from tbname order by   （从表里取出的数据order by进行分组）

  	limit子句：用于查询结果集的行数，参数offset为偏移量，当offset值为0时，表示从查询结果的第一条记录开始，
  	如果offset为1时，表示查询结果从第二条记录开始（例如数据库如果有1万条记录，那就用limit限制到10行或者）

  	limit [offset_start,]row_count;   限制查询结果数量  (offset_start)表示一个数，row_count同左   offset _start:表示起始位置，
  	用于指定查询从哪一行开始，如果不指定，默认为0，表示从表的第一行记录开始查询   row_count:表示查询出来的记录条数  
  	 (只看前三行) select *from xsqk limit 3;

  	like| not like; 字符通配符:% _; %：任意长度的字符串    eg:a%1;  _:任意单个字符eg:a_1; [escape‘转义字符’]:定义转义符，
  	在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。

  	^:表示以什么开始，$：表示以什么结束，.：表示换行符以外的任意一个字符，[ ab]:中括号表示这个位置可以是a和b的任意一个

  	[ ^ ab] : 表示除了ab以外的任意一个字符，|：表示或的意思，*：表示匹配一个或多个在它前边的字符

  	+：表示匹配前边的字符一次或多次，{n}：表示匹配前边的符号恰好出现n次，{n，m}：表示他前边的符号最少出现n次，最多出现m次

  	in|not in :判断值是否为列表中指定的项。

  	is [ not]  null:判断值是否为空

  	and|or|not：用于多个条件的逻辑连接

  	count:用于统计满足条件的记录的行数，用于统计字段中选取的项目数

  	having语句：having语句用在分组或使用聚合函数以后对行再次进行筛选，

  	haing语句通常在grou p by子句中使用

  	Where ----group by----having

  	Where:用于筛选由from子句中指定操作产生的行。

  	Group by:用于将where子句的输出结果分组

  	Having:用于筛选分组汇总后的行

  	order by：根据by后面的字段对查询的结果进行排序。

  	连接：inner join，cross join，outer join（right join，left join，full join（mysql不支持全连接)）

  	格式：
  	From tbl_1 [inner] join tbl_2 on 连接条件         他在连接时，按连接条件进行过滤
  	From tbl1,tb2 where 连接条件          先交叉在进行过滤
  	From tbl1 [inner] join tbl2 on  using(连接条件)       只有有公共字段才能连接

  	没有条件时：
  	左外连接：左表全显示，右表不满足条件的则为空，left outer join（outer可省）
  	右外连接：右表全显示，左表不满足条件的则为空，right outer join（outer可省）