【SQL基本语法 三】数据查询

##select语句概述
查询语句常用关键字select，有三点常用功能：单表查询，多表连接查询，子查询
接下来的所有介绍都是对此表做操作

		create  table tml  //主表，两个字段，id(主键自增)和name（非空）
		(
			tml_id int auto_increament,
			tml_name varchar(255) not null,
			 primary key(tml_id)
		);
		creat table student //从表，3个字段，id(主键自增)和name以及java_tml(参考主表name的外键)
		(
			stu_id int auto_increment primary key,
			stu_name varchar(255),
			#指定java_tml 参照到tml_id列
			java_tml int ,
			constraint   student_tml_fk   foreign   key   (java_tml)   references   tml_table  (tml_id) //为外键约束指定名称

);  对此表进行操作

##*单表查询语句
###单表查询基本

   #执行单表查询
	select student_name     //选择列，如果全部选择，可以用*来代替
	from  student_table     //选择的数据表
	where  java_tml>3       //对行设置条件
	
	#可以在select语句中使用算数运算符（某种意义上说数据列也是一种变量）
	select  *
	from  student_table
	where  student_id*3>4;
	
	#使用concat函数进行字符串连接运算
	select  concat(tml_name,'xyz')
	from tml_table;
	
	#对于mysql而言，无论是算数表达式中用null，还是连接运算中用null都会导致结果为null
	select concat(tml_name,null)
	from tml_table;                  //返回结果为null
	
	#为数据列或者表达式起个别名
	select  tml_id+5 as  MY_ID       
    from tml_table；
	*****************************
	select  tml_id+5  "MY'ID"
	from tml_table;                                                    //选出新的列（6，7，8….）并且为其取一个别名MY_ID
	#为多列以及表起别名
	select  tml_id+5  MY_ID,tml_name  老师名      //为多列起别名
	from tml_table  t;                                                 //为表起别名
	
	#用distinct除去多列组合的重复值
	select  distinct  tml_id,tml_name
	from  tml_table;                                 //只有其组合的全部值都相同时（1，tianmaolin）,(1,tianmaolin )，这里是且的关系

###查询用到的SQL中的运算符
####赋值运算符
不是等号而是冒号等号（：=）
####比较运算符
#####关键字between

				select * from student_table
				where student_id between 2 and 4; // (两个值不能相等，并且两个值都是大等于和小等于的关系)
			    **************也可以是两列之间**********
				select * from student_table
				where 2 between student_id and java_tml; //取出student_id>=2和java_tml<=2的所有记录

#####关键字in

	select  *  from  student_table
	where  2  in(student_id,java_tml);               //选出括号里这两列值为2的所有记录，这里是或的关系

#####关键字like
like用于模糊查询我认为很重要这一点,mysql中有两个常用通配符：下划线（_）和百分号（%），下划线代表一个字符，通配符代表多个字符，通常有以下语法的应用

#查询所有姓孙的同学
select *from student_table
where student_name  like '孙%'；
#查询所有名字不是两个字符的所有学生
select * from student_table
where   not  student_name like '__';
#要查询带有下划线和百分号通配符的数据通常使用escape关键字
select  *  from student_table
where  student_name like ‘\_%’ escape  '\';
#使用like关键字和and（且）or（或）的组合，来指定多个条件的查找
select *  from  student_table
where student_name like '__' and student_id>3 ; //选出student_name 是两个字符并且                                student_id>3的所有记录

#####关键字is null
is null关键字判断是否为空

select *from student_table
where student_name  is  null;      //将选择出表中所有为空的记录

####逻辑运算符
not 》 and 》 or按照优先级排序，所有的比较运算符都比逻辑运算符优先
####对查询结果进行排序
关键字：order by , desc(降序), asc(升序) 默认按照升序排列

select *from student_table
order by  java_teacher desc , student_name;    //强制依据java_teacher按照降序排列，当java_teacher相同时，依据student_name按照升序排序

##数据库函数（单行函数）
函数用来对数据进行复杂的操作，分为单行函数（对每行输入值单独计算，一行输入对一个输出），多行函数（多行同时计算，多行输入对一个输出，也称为聚集函数或分组函数）。
###单行函数特征
1，单行函数的参数可以是变量，常量或者数据列，每行可以接受多个参数，但只返回一个值。
2，使用单行函数可以改变参数的数据类型。
3，单行函数支持嵌套使用，即内层函数的返回值是外层函数的参数。
###单行函数分类（对应于mysql数据类型）
1，日期时间函数，数值函数，字符函数。
2，转换函数，主要用于完成数据类型的转化。
3，位函数，流程控制函数，加密解密函数，信息函数
###单行函数实例

选出tml_table表中tml_name列的字符长度
select  char_length  (tml_name)
from  tml_table;
#计算tml_name列的字符长度的sin值
select sin  (char_length  (tml_name))
from  tml_table;
#计算1.57的sin值
select sin  (1.57);

select  curtime();      获取当前时间
select  curdate();    获取当前日期
select  now();          获取当前日期和时间
select  date_add ( now(), interval 1  day/hour/minute/second/mirsecond/week/month/quarter/year)  //为当前的时间添加时间

select MD5（‘testing’）     //MD5是加密函数

处理null的函数
#如果student_name（expr1）这一列为null，则返回“没有名字”（expr2）
select   ifnull（student_name,‘没有名字‘）
from   student_table;
#如果expr1=expr2,返回null否则返回expr1
select  nullif（student_name,‘张三‘）
from   student_table;  //如果student_name等于张三，则返回null否则返回expr1
#如果student_name列为null，则返回没有名字，否则返回有名字
select   if (isnull(student_name),     '没有名字'，‘有名字’)
from   student_table;      //isnull(expr1)如果expr1为null则为true，如果为true则返回没有名字

流程控制函数
select    student_name, case  java_teacher  //按照老师的id编号返回对应的学生列表
case    value
when   compare_value1   then   result1
when   compare_value2   then   result2
…….
else   result
end
from   student_table
如果value的值等于之后的value1则返回result1 的结果，之后的同理。
select     student_name, case
when  condition1   then   result1
when  student_id<=3   then  '初级班'
…….
else   result
end
from   table

##分组和组函数
###组函数就是多行函数

#计算student_table表中的记录条数
select   count(*)                 //*表示该表中记录的行数
from   student_table;    //注意*和distinct（不计算重复条数）不能同时使用
#计算java_teacher列中有多少个值
select   count(distinct  java_teacher)
from   student_table; 
#统计所有student_id的总和
select   sum(student_id)
from   student_table; 
#选出表中student_id的最大值
select   max（student_id)
from   student_table; 
#选出表中student_id的最小值
select   min（student_id)
from   student_table; 
#avg计算时为了避免空值计算使用以下步骤
select   avg（ifnull(java_teacher,0))
from   student_table; 

###分组：关键字：group by

select *                                                   
from  student_table                           //选中所有数据
group  by  java_teacher                     //按照java_teacher值相同的进行分组
havaing   count(*)>2;                          //选出记录条数大于2的分组

##*多表连接查询
###sql92规范（等值连接，非等值连接，外连接，广义笛卡尔积）

********等值连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s,teacher_table  t      //指明来源表并且给表起别名
where  s.java_teacher=t.teacher_id  and student_name is not null                                   //指明连接条件
*****非等值连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s,teacher_table  t       //指明来源表并且给表起别名
where  s.java_teacher>t.teacher_id               //指明连接条件
			
***广义笛卡尔积*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s,teacher_table  t   //指明来源表并且给表起别名
         
******外连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s,teacher_table  t   //指明来源表并且给表起别名
where  s.java_teacher=t.teacher_id  （*）     //右外链接   ，都返回满足条件的值，右外连接还返回右表中不符合连接条件查询的数据行，如果左边没有对应的则返回null值

###*sql99规范（交叉连接，自然链接，using子句链接，on子句连接，全外链接或者左右外链接）

****交叉链接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s                  //from后只跟一个表名
cross join  teacher_table t             //相当于广义笛卡尔积
*****自然连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s                  //from后只跟一个表名
nature  join  teacher_table  t          //将会把两个表中所有的同名列都选出来     
			
***using子句连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s                  //from后只跟一个表名
join  teacher_table  t          //这里也可以使用nature join代替join将会把两个表中所有的同名列都选出来     
using （两个表中的同名列）        //指明条件  
******on子句链接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s                  //from后只跟一个表名
join  teacher_table  t          //这里也可以使用nature join代替join将会把两个表中所有的同名列都选出来     
on s.java_teacher=t.teacher_id      //on子句链接完全可以代替等值和非等值连接，条件任意

*****右外连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s                  //from后只跟一个表名
right   join  teacher_table  t         //右外链接   ，都返回满足条件的值，右外连接还返回右表中不符合连接条件查询的数据行，如果左边没有对应的则返回null值
on s.java_teacher=t.teacher_id              
******全外连接*********************************************************
select s.*，teacher_name              //查询所有学生的资料和对应老师的姓名
from  student_table   s                  //from后只跟一个表名
full  join  teacher_table  t         //全外连接将会把两个表中所有不符合条件的记录全部列出
on s.java_teacher=t.teacher_id              

##*子查询语句
###把子查询当成数据表（临时视图）

select *
from (select * from  student_table)  t
where  t.java_teacher>1

###把子查询当成过滤条件

select *
from student_table
where   java_teacher>(select  teacher_id   from  teacher_table  where  teacher_name='yeeku') ;//返回的子查询值被当作标量使用
*******************************
select *
from student_table
where  studetn_id  in  (select  teacher_id   from  teacher_table) ;//将要返回一组值，然后与studetn_id比较，一样的被选出来
*******************************
select *
from student_table
where  studetn_id  >
any  (select  teacher_id   from  teacher_table) ;//要求大于值列表中的最小值
*******************************
select *
from student_table
where  studetn_id  >all (select  teacher_id   from  teacher_table) ;//要求大于值列表中的最大值
			

##集合运算
实际上就是把多个select进行组合,两个结果集所包含的数据列数量和数据类型必须一致
###union运算（并）

select  *  from  teacher_table
union
select   student_id,student_name  from  student_table;

###minus运算（差）（用子查询代替）

select  student_id  ,student_name   from student_table
where ( student_id  ,student_name)
not  in 
(select   student_id,student_name  from  student_table);     //查找出所有学生表中减去与老师表中id，姓名相同的记录。in表示的是标量值相等，using表示同名字的列  

intersect运算（交）（用多表查询代替）

select  student_id  ,student_name   from student_table
join
teacher_table
on(student_id=teacher_id  and  student_name=teacher_name)
(select   student_id,student_name  from  student_table);    //查找出所有学生表中和老师表中记录相同的项
where  teacher_name  like '李%' and  student_id<4;     //两个表各自的细分条件 on代表的是不同表之间的限定条件，where表示的是每个表各自的限定条件