条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
=、!=、<>、<、<=、>、>=;
BETWEEN…AND;IN(set);IS NULL; AND;OR; NOT;
查询性别为女,并且年龄50的记录
SELECT * FROM stu WHERE gender='female' AND age<50;
查询学号为S_1001,或者姓名为liSi的记录
SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';
查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');
查询学号不是S_1001,S_1002,S_1003的记录
SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');
查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL;
查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>=20 AND age<=40;
查询性别非男的学生记录
SELECT * FROM stu WHERE gender!='male';
或者:或者
SELECT * FROM stu WHERE gender<>'male';
查询姓名不为null的学生记录
SELECT * FROM stu WHERE NOT sname IS NULL;
或者:SELECT * FROM stu WHERE sname IS NOT NULL;
模糊查询需要使用关键字LIKE。
通配符:_ 任意一个字母;%:任意0~n个字母
查询姓名由5个字母构成,并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';
查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';其中“%”匹配0~n个任何字母。
查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';
查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';
去重复:SELECT DISTINCT sal FROM emp;表:emp,重复记录:sal
查看雇员的月薪与佣金之和:SELECT *,sal+comm FROM emp;comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL:
SELECT *,sal+IFNULL(comm,0) FROM emp;
给列名添加别名
在上面查询中出现列名为sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total:
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时,是可以省略AS关键字的:
SELECT *,sal+IFNULL(comm,0) total FROM emp;
排序
查询所有学生记录,按年龄升序排序
SELECT * FROM stu ORDER BY sage ASC;
或者: SELECT * FROM stu ORDER BY sage;
查询所有学生记录,按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;
查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno ASC;
聚合查询
聚合函数是用来做纵向运算的函数:
1.COUNT():统计指定列不为NULL的记录行数;
查询emp表中记录数:
SELECT COUNT(*) AS cnt FROM emp;
查询emp表中有佣金的人数:
SELECT COUNT(comm) cnt FROM emp;
注意,因为count()函数中给出的是comm列,那么只统计comm列非NULL的行数。
统计月薪与佣金之和大于2500元的人数:
SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
查询有佣金的人数,以及有领导的人数:
SELECT COUNT(comm), COUNT(mgr) FROM emp;
2.MAX():计算指定列的最大值;
3.MIN():计算指定列的最小值;
查询最高工资和最低工资:
SELECT MAX(sal), MIN(sal) FROM emp;
4.SUM():计算指定列的数值和;
查询所有雇员月薪和:
SELECT SUM(sal) FROM emp;
查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal), SUM(comm) FROM emp;
查询所有雇员月薪+佣金和:
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
5.AVG():计算指定列的平均值;
统计所有员工平均工资:
SELECT AVG(sal) FROM emp;
分组查询
查询每个部门的部门编号和每个部门的工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门的人数: SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
l 查询每个部门的部门编号以及每个部门工资大于1500的人数:
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
HAVING子句:
having与where的区别:WHERE是对分组前记录的条件,如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;而HAVING是对分组后数据的约束。
查询工资总和大于9000的部门编号以及工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;
LIMIT:LIMIT用来限定查询结果的起始行,以及总行数。
查询5行记录,起始行从0开始:SELECT * FROM emp LIMIT 0, 5;
分页查询:
如果一页记录为10条,希望查看第3页记录应该怎么查呢?
l 第一页记录起始行为0,一共查询10行;
l 第二页记录起始行为10,一共查询10行;
l 第三页记录起始行为20,一共查询10行;
查询语句书写顺序:select – from- where- group by- having- order by-limit
查询语句执行顺序:from - where -group by - having - select - order by-limit
多表查询有如下几种:
合并结果集;UNION 、 UNION ALL
连接查询:
1.内连接:[INNER] JOIN ON;
2.外连接OUTER JOIN ON
左外连接 LEFT [OUTER] JOIN
右外连接 RIGHT [OUTER] JOIN
全外连接(MySQL不支持)FULL JOIN
3.自然连接 NATURAL JOIN
4.子查询
合并结果集
1. 作用:合并结果集就是把两个select语句的查询结果合并到一起!
2. 合并结果集有两种方式:
UNION:去除重复记录,例如:SELECT * FROM t1 UNION SELECT * FROM t2;
UNION ALL:不去除重复记录,例如:SELECT * FROM t1 UNION ALL SELECT * FROM t2。
3.要求:被合并的两个结果:列数、列类型必须相同
连接查询
使用主外键关系做为条件来去除无用信息
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
上面查询结果会把两张表的所有列都查询出来,也许你不需要那么多列,这时就可以指定要查询的列了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname FROM emp,dept
WHERE emp.deptno=dept.deptno;
还可以为表指定别名,然后在引用列时使用别名即可。
SELECT e.ename,e.sal,e.comm,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno=d.deptno;
内连接
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
外连接
1.左连接:SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
2.右连接:SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
自然连接
SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;
子查询
子查询出现的位置:
1.where后,作为条为被查询的一条件的一部分;2.from后,作表;
当子查询出现在where后作为条件时,还可以使用如下关键字:any,all
工资高于JONES的员工
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')
查询工作和工资与MARTIN(马丁)完全相同的员工信息
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN')
有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);
查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=7788
自连接:自己连接自己,起别名
求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno , e1.ename,e2.mgr,e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno = 7369;
导出数据
mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径
恢复数据
SOURCE C:\mydb1.sql
或
mysql –u用户名 –p密码 数据库<要执行脚本文件路径
网友评论