厚瑜网站建设,宁波网站建设科技有限公司,活动汪策划网站,不属于网络营销的特点单表查询、条件查询、查询并排序、限制结果查询、查询并排名、分组聚合查询、-- DQL操作#xff0c;数据基本查询语言使用----------------------------------------------------------------------------------------------- -- 创建数据表-- 注释#xff1a;员工编号#… 单表查询、条件查询、查询并排序、限制结果查询、查询并排名、分组聚合查询、······-- DQL操作数据基本查询语言使用----------------------------------------------------------------------------------------------- -- 创建数据表-- 注释员工编号员工姓名领导姓名领导编号入职时间工资奖金部门编号CREATE TABLE employee ( empid int(11) NOT NULL, ename varchar(30) DEFAULT NULL, job varchar(30) DEFAULT NULL, leaderid int(11) DEFAULT NULL, hiredate datetime DEFAULT NULL, wage decimal(10,2) DEFAULT NULL, prize decimal(10,2) DEFAULT NULL, deptid int(11) DEFAULT NULL, PRIMARY KEY (empid)) ENGINEInnoDB DEFAULT CHARSETutf8; -- 添加数据INSERT INTO employee VALUES (2069, JALEN, CLERK, 7902, 2009-12-17 00:00:00, 18000.00, null, 20),(3099, WANRE, SALESMAN, 7698, 2010-02-20 00:00:00, 18000.00, 300.00, 30),(3021, FIKEN, SALESMAN, 7698, 2010-02-22 00:00:00, 16500.00, 500.00, 30),(3066, JONES, MANAGER, 7839, 2011-04-02 00:00:00, 16000.00, null, 20),(3054, RANKEE, SALESMAN, 7698, 2012-09-28 00:00:00, 16500.00, 1400.00, 30),(3098, BLAKE, MANAGER, 7839, 2013-05-01 00:00:00, 16000.00, null, 30),(1082, CALAN, MANAGER, 7839, 2014-06-09 00:00:00, 16000.00, null, 10),(2088, SCOTT, ANALYST, 7566, 2015-04-19 00:00:00, 16000.00, null, 20),(3039, DIVE, PRESIDENT, null, 2016-11-17 00:00:00, 15000.00, null, 10),(3044, TURNER, SALESMAN, 7698, 2016-09-08 00:00:00, 15000.00, 0.00, 30),(2076, JULI, CLERK, 7788, 2017-05-23 00:00:00, 11000.00, null, 20),(3000, JAMES, CLERK, 7698, 2017-12-03 00:00:00, 9500.00, null, 30),(2002, FAXI, ANALYST, 7566, 2017-12-03 00:00:00, 9000.00, null, 20),(1034, MOKA, CLERK, 7782, 2018-01-23 00:00:00, 8800.00, null, 10); -- 查询语句语法 select cols #查询并展示的数据(字段,表达式等) from tablename #查询的数据来源(表,结果集,视图等) where condition #条件语句 group by #分组 having #分组之后的条件判断 order by #排序(asc升序 desc降序) limit #限制结果查询(仅限于mysql) -- 1、查询所有数据select * from employee; -- 2、查询部分字段select ename,job from employee; -- 3、单一条件查询select * from employee where ename FAXI;select * from employee where wage 10000; -- 4、组合条件查询select * from employee where wage 6000 and wage 10000;select * from employee where wage 10000 or hiredate 2016-01-01 00:00:00; -- 5、范围查询 between ... and ... | andselect * from employee where hiredate between 2015-01-01 00:00:00 and 2019-01-01 00:00:00;select * from employee where hiredate 2015-01-01 00:00:00 and hiredate 2019-01-01 00:00:00; -- 6、集合查询 in | orselect * from employee where deptid in (10,20);select * from employee where deptid 10 or deptid 20; -- 7、别名 [as]可以省略select e.ename,e.job,e.wage from employee as e;select e.ename,e.job,e.wage from employee e;select e.ename as 姓名,e.job as 岗位,e.wage 薪资 from employee as e; -- 8、去重distinctselect distinct e.deptid from employee as e;select distinct deptid from employee; -- 9 、模糊查找select * from employee where ename like B%; #以B开头的,%表示通配符select * from employee where ename like %A%; #包含A的select * from employee where ename like A%; #以A结尾的select * from employee where ename like _A%; #第2个字母是A的_表示占位字符select * from employee where ename like __A%; #第3个字母是A的_表示占位字符 -- 10、排序order by ... desc | [asc]select * from employee order by deptid;select * from employee order by deptid asc;select * from employee order by deptid desc;select * from employee order by deptid desc ,hiredate asc ;select * from employee order by hiredate,wage; -- 11、限制结果查询(limit:mysql专用)select * from employee limit 5; #查看前5行select * from employee limit 0,5; #查看前5行select * from employee limit 2,5; #查看从第3行开始往后数5行即(2,25] -- 12、查询并排名select a,b; #输出申明的a变量和b变量默认值都为nullselect a : 1,b; #select命令赋值用 : 给申明的a变量赋值注意 : 中间不能有空格set b 2; #set命令给申明的变量赋值用 select a,b; #此时输出的结果应该为a 1 ,b 2, 两个变量都已经被赋值select a : a ,b : 12.35; #声明的变量可以赋予任意值(整数小数字符串...) #案例 set rank 0; #申明一个rank变量并赋予初始值0 -- 注释查询表emp,并按照sal字段降序,同时输出rank : rank 1字段(表示申明的变量rank每一次都等于上一次的结果加1) select *, rank : rank 1 from emp order by sal desc; -- 13、分组查询 # 分组函数 # group by 分组字段 (先分组再聚合,每组一个结果) # having子句: 和where类似,分组之后过滤 # 分组之后,select能够出现聚合函数,分组的字段 select deptno,avg(sal) avg from emp group by deptno; # 查询平均工资2000的部门的编号和平均工资 # 1.每个部门的平均工资 # 2.平均工资中再筛选2000的 select deptno,avg(sal) avg from emp group by deptno having avg 2000;
