-- 时间 -- 年月日 date -- 时分秒 time -- 默认null 最大 9999-12-31 datetime -- 默认当前系统时间, 最大2038-01-19 timestamp
-- 导入 *.sql文件 source d:/tables.sql;
-- 练习 selectname,wages from emp where up isnull; selectname,mgr from emp where mgr isnotnull;
-- 别名 selectnameas'姓名'from emp; selectname'姓名'from emp; selectname 姓名 from emp;
-- 比较运算符 > < >= <= != <> selectname,mgr from emp where mgr<2000; selectname,mgr,job from emp where job='manager'; selectname,job,mgr from emp where mgr<1600; selectname,nm from emp where nm != 10; selectname,nm from emp where nm <> 10; select * from t_item where price=23; select title from t_item where price <> 8443;
-- 去重 distinct selectdistinct job from emp; selectdistinct deptno from emp;
-- and or select * from emp where deptno=10and sal < 2000; select * from emp where deptno=30or sal > 3000;
-- 模糊查询 like -- % 0或多个未知字符 -- _单个未知字符 select * from emp where ename like'j%'; select ename,sal from emp where ename like'_l%'; select title from t_item where title like'%记事本%'; select ename,sal,job from emp where job like'%an%'and sal > 1500; select * from t_item where sell_point like'%赠%'and title like'%dell%'; select title,price from t_item where title like'%笔记本%'and price<100; select * from t_item where image isnotnulland title like'%得力%'; select ename from emp where ename notlike'%a%';
-- between select ename,sal from emp where sal<=3000and sal>=2000; select ename,sal from emp where sal between2000and3000; select title,price from t_item where price between50and100; select ename,sal from emp where sal notbetween1000and2000;
-- in select * from emp where sal=800or sal=1300or sal=1500; select * from emp where sal in(200, 1300, 1500); select title,price from t_item where price in(56,58,89); select ename,sal from emp where sal notin(3000,5000,1500);
-- 练习 select * from t_item where categoryId in(238,917); select title,price from t_item where title like'%得力%'and price between50and200; select ename,sal,deptno from emp where deptno isnotnulland sal<2000; select ename,jj,mgr from emp where mgr isnotnulland jj>0; selectdistinct job from emp where ename like'%a%'and sal<3000;
-- 排序 order by select ename,sal from emp where sal<3000orderby sal desc; select ename,sal from emp where deptno=10orderby sal asc; select ename,sal,comm from emp where comm>0orderby comm desc; select price,tile from t_item where price<100orderby price;
-- 多字段排序 select ename,saldeptno from emp whereorderby deptno desc,sal asc;
-- 分页查询 limit 跳过的条数,请求的条数 select ename,sal from emp orderby sal desclimit6,3; select * from emp orderby sal desc linit 0,3; select title,price from t_item orderby price asclimit10,5; select * from emp where deptno=30orderby sal desclimit0,1;
-- 数值计算 + - * / % select ename,sal,sal*5 年终奖 from emp; select title,price,num,price*num 总价值 from t_item; select ename,sal+5 加薪后工资 from emp;
-- ifnull(x, y) null ? y : x update emp set comm=ifnull(comm, 0);
-- 聚合函数 对查询的多条数据进行统计查询:平均值 最大值 最小值 求和 计数 -- 平均值 avg selectavg(sal) from emp; -- 最大值 max selectmax(sal) from emp where deptno=20; -- 最小值min selectmin(sal) from emp where ename like'%a%'; -- 求和 sum selectsum(sal) from emp where deptno=10; -- 计数 count() selectcount(*) from emp where sal>2000;
selectavg(sal),max(sal),min(sal),sum(sal),count(*) from emp where deptno=20;
-- 练习 selectnum,ename,sal from emp where deptno isnull; select ename,job,sal,comm from emp where comm isnullor comm=0; selectnum,ename,job,comm from emp where comm>0; select ename,sal,deptno from emp where deptno isnotnull; select ename from emp where ename like's%'; select ename from emp where ename like'%e_'; select ename from emp where ename like'%n__'; select ename from emp where ename like'%a%'; select * from emp where ename notlike'k%'; select * from emp where ename notlike'%a%'; selectcount(*) from emp where job like'clerk'; selectmax(sal) from emp where job='销售'; select create_time from emp orderby create_time limit0,1; select create_time from emp orderby create_time desclimit0,1) selectsum(num) from t_item where category_id=163; select * from t_item where category_id=163; select title from t_item where price<=100; select ename,job,sal,create_time,deptno from emp where sal>3000or deptno=30; select * from emp where deptno<>30; select * from emp where comm isnotnull; selectnum,ename,job from emp orderbynumdesc; select ename,job,sal from emp wherenum=10ornum=30orderby sal asc; selectnum,ename,sal,deptno where sal>1000orderbynumdesc,sal asc; selectsum(sal) from emp; selectmin(hiredate),max(hiredate) from emp;
-- 分组查询 select gender,avg(age) from emp groupby gender; select deptno,avg(sal) from emp groupby deptno; select job,max(sal) from emp groupby job; select deptno,count(*) from emp groupby deptno; select deptno,count(*) from emp where sal>1500groupby deptno; select mgr,count(*) from emp where mgr isnotnullgroupby mgr; select deptno,job,avg(sal) from emp groupby deptno,job; select deptno,count(*) c,sum(sal) s from emp groupby deptno orderby c,s desc; selectavg(sal) a,min(sal),max(sal) from emp where sal between1000 adn 3000groupby deptno orderby a; selectcount(*) c,sum(sal),avg(sal) a from emp where mgr isnotnullgroupby job orderby c desc,a;
-- 聚合查询条件 select deptno,avg(sal) a from emp groupby deptno having a>2000; select category_id,avg(price) a from t_item groupby category_id having a<100; select deptno,count(*),avg(sal) a from emp groupby deptno having a>2000orderby a desc; select category_id,avg(price) from t_item where category_id in(238,917) groupby category_id; select deptno,sum(sal),avg(sal) a from emp where sal between1000and3000groupby deptno having a>=2000orderby a; selectextract(year, dcredate) e, count(*) from emp groupby e; select deptno,avg(sal) a from emp groupby deptno orderby a desclimit0,1;
-- 子查询 嵌套查询 select * from emp where sal>(selectavg(sal) from emp); select * from emp where sal>(selectmax(sal) from emp); select * from emp where sal>(selectmax(avg) from emp where deptno=20); select * from emp where job=(select job from emp where ename='jones') and ename<>'joens'; select * from emp where deptno=(select deptno from emp where sal=(selectmin(sal) from emp)) and sal<>(select (min(sal)) from emp) select * from emp where hiredate=(selectmax(hiredate) from emp); select * from dept where deptno=(select deptno from emp where ename='king'); select * from dept where deptno in (selectdistinct deptno from emp) and deptno <> 40; select * from dept where deptno in(select deptno from emp groupby deptno havingavg(sal)=(selectavg(sal) a from emp groupby deptno orderby a desclimit0,1))
-- 子查询可以放的位置 -- 1. 写在where having 后面 -- 2. 创建表的时候,吧查询结果保存到新表 createtable emp_2 as (select * from emp where deptno=2); -- 3. 写在from后面 一定要写别名 select ename from (select * from emp where deptno=10) t;
-- 关联查询 必须写关联关系 不写会得到两张表的成绩 -- 等值连接 查两张表交集数据 select e.ename, d.dname from emp e,dept d where e.deptno=d.deptno; -- 内连接 查两张表交集数据 select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno where e.sal>2000; select e.name,e.sal from emp e join dept d on e.deptno=d.deptno where a.dept='new york'; select e.ename,d.dname,d.loc from emp e join dept d on e.deptno=d.deptno where e.ename='james'; -- 外连接 查一张表全集,另一张表的交集 select e.ename,d.dname from emp e rightjoin dept d on e.deptno=d.deptno;
-- 练习 select deptno,count(*) c from emp groupby deptno orderby c desc; select deptno,mgr,count(*) from emp where mgr isnotnullgroupby deptno,mgr; select job avg(sal) from emp groupby job; selectextract(year, dictdate) y,count(*) from emp groupby y; select * from emp where sal=(selectmin(sal) from emp); select * from dept where deptno in(select deptno c from emp groupby deptno havingcount(*)<=3); select d.* from emp e rightjoin dept d groupby deptno havingcount(e.ename)<=3; select * from emp where ename=(select ename from emp groupby mgr havingcount(*)=1); select * from dept where deptno in(select deptno from emp groupby deptno havingsum(sal)=(selectsum(sal) s from emp groupby deptno groupby s limit0,1)); select * from emp where ename in(select ename from emp groupby mgr havingcount(*)=(selectcount(*) c from emp groupby mgr orderby c desclimit0,1)); select * from emp where ename in(select ename from emp groupby deptno having deptno=(select deptno from emp orderby dictdate desclimit0,1)); select * from dept deptno in(select deptno from emp groupby deptno havingavg(sal)>(selectavg(sal) from emp where deptno=20groupby deptno)); select e.*,d.dname from emp e leftjoin dept d on e.deptno=d.deptno; select e.*,d.dname,d.loc from emp e leftjoin dept d on e.deptno=d.deptno; select * from emp where deptno in(select deptno from dept where loc='Dallas'); selectcount(*) from emp e rightjoin dept d on e.deptno=d.deptno groupby d.loc; select e.*,m.ename from emp e join emp m on e.mgr=m.empno;