select:查询表中的记录,是sql语句中最复杂的;
基本查询:
select uid,uname from emp;
查询所有列:
select * from emp;
为列取别名:
select uid as i from emp;
只显示列上的不通知:
select distinct uid from emp;
查询过程中执行运算:
select salary*12 +comm from emp;
条件查询:实现根据特定条件对结果进行筛选
进行相等或不相等:
select * from emp where uid=23;
select * from emp where salary >=10000;
select * from emp where age between 10 and 50;
select * from emp where uid in(10,20,30);
多条件并列:
select * from emp where age>40 and uid<50;
select * from emp where salary>8000 or salary<4000;
模糊查询:
select * from emp where uname like ‘%e%’;//uname中含有关键字e的列;
查询排序:
–升序排序:select * from emp order by salary;
–降序排序:select * from emp order by salary desc;
–多列排序:select * from emp order by uid ,age desc;
分页查询:
select * from emp [where..][order by..] limit start ,count;
假设没页20条:
select * from emp limit 0,20; 第一页;
select * from emp limit 20,20; 第二页;
select * from emp limit 20*(n-1),20; 第 n 页;
聚合分组查询:MySql提供五个聚合函数,可以对查询结果集进行特定运算
select MAX(salary) from emp; #查询工资最大值
select MIN(salary)from emp; #查询工资最小值
select SUM(salary) from emp; #查询工资总和
select COUNT(salary) from emp; #查询工资数量
select AVG(salary) from emp; #查询工资的平均值
分组查询:指将指定列上的值相同的记录划分在一组中,在组内进行聚合运算
select MAX (salary) from emp group by uid;
子查询:在一个查询语句中的某个或多个子句中包含其他查询语句,是一种符合查询
select * from emp where uid=(
select did from dept where uame=’hanzx’);
多表查询:跨表查询,指一次查询的结果集中出现来自多个表中的列。
–内链接(inner join)
select uname,dname from emp inner join dept on emp.uid=dept.did;//查询dept和emp中uid和did相等的uname和dname
结果集合并:union操作符用于将两个查询结果合并为一个结果集
–合并结果集,重复数据仅显示一边
select uname from emp_cn union uname from emp_us;
–合并结果集,允许出现重复数据
select uname from emp_cn union all select uname from emp_us;
function(){
}