数据库基础知识_2

六、DQL操作

1、数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端，查询返回的结果集是一张虚拟表

2、语法：select 列名 from 表名 where group by having order by

  SELECT selection_list /*要查询的列名称*/

  FROM table_list /*要查询的表名称*/

  WHERE condition /*行条件*/

  GROUP BY grouping_columns /*对结果分组*/

  HAVING condition /*分组后的行条件*/

  ORDER BY sorting_columns /*对结果分组*/

  LIMIT offset_start, row_count /*结果限定*/

3、查询所有列：select * from stu

4、查询指定列：select sid,sname,age from stu;

5、条件查询：

• = != <> < <= > >= 
• between and
• in (set)
• is null ; is not 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='s_1001' or sid='s_1002' or sid='s_1003';
  • select * from stu where sid in ('s_1001' ,'s_1002' ,'s_1003');
  • 查询学号不是s_1001,s_1002,s_1003的记录
  • select * from stu where sid not in ('s_1001' ,'s_1002' ,'s_1003');
  • 查询年龄为Null的记录
  • select * from stu where age is null;
  • select * from stu where age is not null;
  • 查询年龄在20到40之间的学生记录
  • select * from stu where age>=20 and age<=40;
  • select * from stu where age between 20 and 40;
  • 查询性别非男的学生记录
  • select * from stu where gender != 'male';
  • select * from stu where gender <> 'male';
  • select * from stu where not gender = 'male';
  • select * from stu where gender = 'female';

6、模糊查询

• 当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询使用关键字like
• 通配符 _任意一个字符 %任意0-n个字符
• 查询姓名由5个字母构成的学生记录
• select * fron stu where sname like '_____';
• 查询姓名由5个字母构成，并且第五个字母为'i'的学生记录
• select * from stu where sname like '____i';
• 查询姓名以'z'开头的学生记录
• select * from stu where sname like 'z%';
• 查询姓名中第二个字母为'i'的学生记录
• select 8 from stu where sname like '_i%';
• 查询姓名中包含'a'字母的学生记录
• select * from stu where sname like '%a%';

7、字段控制查询

• 去掉重复记录
• select distinct sal from emp;
• select distinct sal,depto from emp;//sal 和 depto 完全相同才去掉
• 查看雇员的月薪与佣金之和
• select *,sal+comm from emp;//sal 和 comm两列的类型都是数值类型，所以可以做加运算，如果sal或comm中有一个字段不是数值类型，那么会出错
• select 8,sal+ifnull(comm,0) from emp//comm列有很多记录的值为Null,因为任何东西与null相加结果还是null，所以结算结果会出现Null,使用把null转换成数值0的函数ifnull
• 给列名添加别名
• select *，sal+ifnull(comm,0) as total from emp;
• select *，sal+ifnull(comm,0) total from emp;

8、排序 order by 列名 asc(默认) desc

• select * from stu order by sage asc;
• 查询所有学生记录，按年龄降序排序
• select * from stu order by sage desc;
• 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
• select * from emp order by sal desc,empno,asc;

8、聚合函数

• sum avg min max count
• 聚合函数是用来纵向运算的函数
• count() 统计指定列不为null的记录行数
• select count(*) from stu;
• max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
• min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序预算
• sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
• avg() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
• 查询emp表中记录数
• select count(*) from emp;
• 查询emp表中有佣金的人数
• select count(comm) from emp;//只统计comm列非null的行数
• 统计月薪与佣金之和大于2500元的人数
• select count(*) from emp where sal+ifnull(comm,0) > 2500
• 查询有佣金的人数，有领导的人数
• select count(comm),count(mgr) from emp;
• 查询所有雇员月薪和
• select sum(sal) from emp;
• 查询所有雇员佣金和
• select sum(comm) from emp; //滤空 null值并没有计算
• 查询所有雇员月薪+佣金和
• select sum(sal+ifnull(comm,0)) from emp;
• 统计所有员工平均工资
• select avg(sal) from emp;
• 查询最高工资和最低工资
• select max(sal),min(sal) from emp;

9、分组查询

• 当需要分组查询时需要使用groupby子句，例如查询每个部门的工资和，这说明要使用部分来分组
• 凡和聚合函数同时出现的列名，一定要写在groupby之后
• select deptno,count(*) from emp group by deptno;
• 查询每个部门的部门编号和每个部门的工资和
• select deptno,sum(sal) from emp group by deptno;
• 查询每个部门的部门编号以及每个部门的人数
• select deptno,count(*) from emp group by deptno;
• 查询每个部门的部门编号以及每个部门工资大于1500的人数
• select deptno,count(*) from emp where sal>1500 group by deptno;

10、having 子句

• 查询工资总和大于9000的部门编号以及工资和
• seletc deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
• having 与 where 的区别：
• • having是在分组后对数据进行过滤
  • where是在分组前对数据进行过滤
  • having后面可以使用聚合函数（统计函数）
  • where后面不可以使用聚合函数
  • where是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组，而having是对分组后的数据的约束
  •  

11、查询代码的书写顺序和执行顺序

• 查询语句书写顺序：select from where group by having order by limit
• 查询语句执行顺序：from where group by having select order by limit

12、limit

• limit用来限定查询结果的起始行以及总行数
• 查询5行记录，起始行从0开始
• select * from emp limit 0,5;
• 查询10行记录，起始行从3开始
• select * from emp limit 3,10
• 分页查询
• int currentpage = 3; int pagesize = 4;
• select * from emp limit (currentpage-1)*pagesize,pagesize;