select语句的功能是查询数据。下面对常用点进行了总结。
单表查询
(1)数据列可当做变量来使用
//查询出teacher_id+5的结果
select teacher_id+5
from teacher_table;
//无敌的null。不管是算术表达式还是字符串连接运算中,只要出现null,则结果一定是null
select concat(teacher_name,null)
from teacher_table;
(2)字符串连接和去除重复行经常用到
//字符串连接-concat函数。查询出teacher_name和“老师”字符串连接后的结果
select concat(teacher_name,'老师')
from teacher_table;
//去除重复行。distinct是对查询出来的整体结果进行去重,而不是对某一个字段。
select distinct student_name,java_teacher
from student_table;
(3)给列或表起别名,可以使用as,也可以将as省略。
//为列和表起别名
select concat(teacher_name,'老师') 老师名
from teacher_table t;
//当别名中含特殊字符或需要强制大小写敏感时,可添加双引号
select concat(teacher_name,'老师') as "tea-name"
from teacher_table t;
(4)sql中的比较运算符可以比较数值、字符串、日期的大小。
//查询出 2≤student_id≤4 的所有记录
select *
from student_table
where student_id between 2 and 4;
//指定列必须与in括号里任意一个值相等
select *
from student_table
where sudent_id in(2,4);
//模糊查询中使用like运算符,其中_代表一个任意字符,%代表任意多个字符。
select * from student_table
where student_name like '孙%';
//判断是否为空时,使用is null。不能使用=null,因为sql中如果null=null,返回null
select * from student_table
where student_name is not null;
(5)执行查询后的结果默认按插入顺序排列,如果想按某列值的大小进行排序,可以使用order by
//按java_teacher列排序,默认按升序排列,asc可省略不写
select *
from student_table
order by java_teacher;
//可使用desc进行降序排列
select *
from student_table
order by java_teacher desc,student_name;
分组和组函数
在前面中,每条记录返回一个结果,这次是将一组记录作为整体计算,每组记录返回一个结果,就像将一组数求和,最后返回“和”这个结果。这也是组函数的由来。
(1)常用的组函数有:avg,count,max,min,sum。还有两个必须是组函数存在时才能使用的用法:group by,having
//统计所有student_id的总和
select sum(student_id)
from sudent_table;
//选出列中的最大值用max。最小值可用min
select max(student_id)
from student_table;
//使用distinct则不计算重复值
select count(distinct student_name)
from student_table;
//使用count统计行数,null不会被计算在内
select count(student_name)
from student_table;
//对于可能出现null的列,可以使用ifnull函数对其进行处理
select avg(ifnull(java_teacher,0))
from student_table;
(2)group by是将查询结果根据一列或多列进行分组。
//对每组只得到一个结果
select count(*)
from student_table
gruop by java_teacher;
(3)having用于条件筛选。having和where的区别是,where用于过滤行,having用于过滤组。不能在where中使用组函数,只能在having中使用。
//查询java_teacher
select *
from student_table
group by java_teacher
having count(*)>2;
多表连接查询
(1)如果要查询的数据来自多个表,则需要用到多表连接查询。
//过程:先遍历teacher_table表中的每条记录,然后遍历student_table表中的记录,
//最后将满足连接条件的结果输出
select s.*,teacher_name
from student_table s,teacher_table t
where s.java_teacher=t.teacher_id;
//自连接
select a.emp_id,a.emp_name 员工名,b.emp_name 经理名
from emp_table a,emp_table b
where a.manager_id=b.emp_id;
(2)连接查询
- 交叉连接
交叉连接就是对数据进行笛卡尔积。返回结果的行数为两个表行数的乘积。
select s.*,teacher_name
from student_table s
cross join teacher_table t;
- 自然连接
自然连接以两个表中的同名列为连接条件,如果两个表中没有同名列,则与连接查询说的就是一回事了。
select s.*,teacher_name
from student_table s
natural join teacher_table t
- using子句连接
using子句是比自然连接更进一步的,它可以显示指定两个表中的同名列作为连接条件。
select s.* ,teacher_name
from student_table s
join teacher_table t
using(teacher_name);
- on子句连接
on子句连接是最常用的连接方式。将连接条件放在on子句后指定。
select s.* ,teacher_name
from student_table s
join teacher_table t
on s.java_teacher=t.teacher_id;
(3)外连接
外连接分为内连接,左外连接,右外连接,全外连接(笛卡尔积)。
下面以右外连接为例,来说明一下外连接的用法。
select s.*,teacher_name
from student_table s
right join teacehr_table t
on s.java_teacher>t.teacher_id
子查询
(1)子查询就是指在查询语句中嵌套另一个查询。可以在from,where后面加入子查询。
//在from语句后加入子查询,这种用法也叫行内视图。需要给子查询起别名。
select *
from (select * from student_table) t
where t.java_teacher>1;
//在where子句后加入子查询
select *
from student_table
where java_teacher>
(select teacher_id
from teacher_table
where teacher_name='小明')
(2)如果子查询返回多个值,需要结合in、any、all等关键字进行使用。
//in表示只要和括号里的任意一个值相等,则选出这条记录
select *
from student_table
where student_id in
(select teacher_id
from teacher_table);
//=any 和in的作用相同
select *
from student_table
where student_id =
any(select teacher_id
from teacher_table);
//<all 表示需要小于括号里的所有值
select *
from student_table
where student_id <
all(select teacher_id
from teacher_table);
集合运算
可以对select语句查询的结果进行交(intersect)、并(union)、差(minus)运算。也可以对查询得到的结果集进行此运算,但是需要满足此条件:两个结果集所含的数据列的数量必须相等,数据类型也必须一一对应。
//并
select 语句 union select 语句
//差-mysql不支持此运算符,可以借助子查询实现此功能。
//交-mysql不支持此运算符,可以借助多表连接实现此功能。
小结
和java语法不同,sql语句中用单引号‘’表示字符串