单表查询-白红宇

单表查询

阅读量：5329 次

发布时间：2019-06-14

本文共 11545 字，大约阅读时间需要 38 分钟。

先创建表

#创建表create table employee(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#查看表结构mysql> desc employee;+--------------+-----------------------+------+-----+---------+----------------+| Field        | Type                  | Null | Key | Default | Extra          |+--------------+-----------------------+------+-----+---------+----------------+| id           | int(11)               | NO   | PRI | NULL    | auto_increment || name         | varchar(20)           | NO   |     | NULL    |                || sex          | enum('male','female') | NO   |     | male    |                || age          | int(3) unsigned       | NO   |     | 28      |                || hire_date    | date                  | NO   |     | NULL    |                || post         | varchar(50)           | YES  |     | NULL    |                || post_comment | varchar(100)          | YES  |     | NULL    |                || salary       | double(15,2)          | YES  |     | NULL    |                || office       | int(11)               | YES  |     | NULL    |                || depart_id    | int(11)               | YES  |     | NULL    |                |+--------------+-----------------------+------+-----+---------+----------------+#插入记录#三个部门：教学，销售，运营insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);

1 1.注意：                                                                                        2     select * from t1 where 条件 group by 分组字段                                                  3     1.分组只能查询分组字段，要想查看其余的利用聚合函数                                                               4     2.聚合函数的分类：count,min,max,avg,group_concat,sum等。                                           5     3.模糊匹配：用like关键字。                                                                         6       select * from t1 where name like '%eg%'; #%表示任意字符                                      7       select * from t1 where name like 'd__l'; #一个下划线表示一个字符，两个下划线就表示两个字符                     8     4.拷贝表 ：create table t2 select * from t1;                                                 9                create table t2 select * from t1 where 1=2 ;

一点小知识复习

一、查询语法

SELECT 字段1,字段2... FROM 表名                  WHERE 条件                  GROUP BY field                  HAVING 筛选                  ORDER BY field                  LIMIT 限制条数

二、简单查询

#简单查询    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id     FROM employee;    SELECT * FROM employee;    SELECT name,salary FROM employee;#避免重复DISTINCT    SELECT DISTINCT post FROM employee;    #通过四则运算查询    SELECT name, salary*12 FROM employee;    SELECT name, salary*12 AS Annual_salary FROM employee;    SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式   CONCAT() 函数用于连接字符串   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary    FROM employee;      CONCAT_WS() 第一个参数为分隔符   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary    FROM employee;

小练习：

1 查出所有员工的名字，薪资,格式为    
      <名字:egon>    
       <薪资:3000>
        select concat('
        <名字:',name,'>
          '  ,'
         <薪资:',salary,'>
          ' ) from employee;2 查出所有的岗位（去掉重复）select distinct depart_id from employee;3 查出所有员工名字，以及他们的年薪,年薪的字段名为年薪select name,salary*12 年薪 from employee;

三、where约束

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=

2. between 80 and 100 值在80到100之间

3. in(80,90,100) 值是80或90或100

4. like 'eg%'

可以是%或_，

%表示任意多字符

_表示一个字符

　like 'e__n' :

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

#1:单条件查询    SELECT name FROM employee        WHERE post='sale';        #2:多条件查询    SELECT name,salary FROM employee        WHERE post='teacher' AND salary>10000;#3:关键字BETWEEN AND    SELECT name,salary FROM employee         WHERE salary BETWEEN 10000 AND 20000;    SELECT name,salary FROM employee         WHERE salary NOT BETWEEN 10000 AND 20000;    #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)    SELECT name,post_comment FROM employee         WHERE post_comment IS NULL;    SELECT name,post_comment FROM employee         WHERE post_comment IS NOT NULL;            SELECT name,post_comment FROM employee         WHERE post_comment=''; 注意''是空字符串，不是null    ps：        执行        update employee set post_comment='' where id=2;        再用上条查看，就会有结果了#5:关键字IN集合查询    SELECT name,salary FROM employee         WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;        SELECT name,salary FROM employee         WHERE salary IN (3000,3500,4000,9000) ;    SELECT name,salary FROM employee         WHERE salary NOT IN (3000,3500,4000,9000) ;#6:关键字LIKE模糊查询    通配符’%’    SELECT * FROM employee             WHERE name LIKE 'eg%';    通配符’_’    SELECT * FROM employee             WHERE name LIKE 'al__';

四、having过滤

having和where语法上是一样的。

select * from employee where id>15;    select * from employee having id>15;

但是having和where不一样的地方在于以下几点！！！

#！！！执行优先级从高到低：where > group by > 聚合函数 > having >order by

1.where和having的区别                                                                                     1. Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的                                             （先找到表，按照where的约束条件，从表(文件)中取出数据），Where中不能使用聚合函数                                                   2.Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作                                                          （先找到表，按照where的约束条件，从表(文件)中取出数据，然后group by分组，                                                      如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤），                                           在Having中可以使用聚合函数。                                                                               3.where的优先级比having的优先级高                                                                          4.having可以放到group by之后，而where只能放到group by 之前。

验证不同之处：

1.查看员工的id>15的有多少个select count(id) from employee where id>15;#正确，分析：where先执行，后执行聚合count(id),                                            然后select出结果select count(id) from employee having id>15; #报错，分析：先执行聚合count(id)，后执行having过滤，                                            #无法对id进行id>15的过滤#以上两条sql的顺序是1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果（此时只有count(id)字段了）进行id>15的过滤，很明显，根本无法获取到id字段

1 ------having-----------2 select depart_id,count(id) from employee group by depart_id;3 select depart_id,count(id) from employee group by depart_id having depart_id = 3;4 select depart_id,count(id) from employee group by depart_id having count(id)>7;5 select max(salary) 最大工资 from employee where id>2 group by depart_id having count(id)>3;6 select * from employee where id>7; #查看所有id>7的员工信息

having 举例

小练习：

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数select post,group_concat(name) 员工姓名,count(id) 个数 from employee group by post having count(id)<2;2. 查询各岗位平均薪资大于10000的岗位名、平均工资select post,avg(salary) from employee group by post having avg(salary)>10000;3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资select post,avg(salary)  from employee group by post having  avg(salary) between 10000 and 20000;

五、分组查询 group by

大前提：可以按照任意字段分组,但分完组后，只能查看分组的那个字段，要想取的组内的其他字段信息，需要借助函数

单独使用GROUP BY关键字分组    select post from employee group by post;    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数GROUP BY关键字和group_concat()函数一起使用      select post,group_concat(name) from  employee group by post;#按照岗位分组，并查看组内成员名      select  post,group_concat(name) as emp_members FROM employee group by post;GROUP BY与聚合函数一起使用    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

强调：

分组：一般相同的多的话就可以分成一组（一定是有重复的字段） 小练习：

1. 查询岗位名以及岗位包含的所有员工名字select post,group_concat(name) from employee group by post; 2. 查询岗位名以及各岗位内包含的员工个数select post,count(id) from employee group by post; 3. 查询公司内男员工和女员工的个数select sex,count(id) from employee group by sex; 4. 查询岗位名以及各岗位的平均薪资select post,max(salary) from employee group by post; 5. 查询岗位名以及各岗位的最高薪资select post,max(salary) from employee group by post; 6. 查询岗位名以及各岗位的最低薪资select post,min(salary) from employee group by post; 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex;

六、关键字的执行优先级(重点)

重点中的重点：关键字的执行优先级fromwheregroup byhavingselectdistinctorder bylimit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.如果有聚合函数，则将组进行聚合

5.将4的结果过滤：having

6.查出结果：select

7.去重

8.将6的结果按条件排序：order by

9.将7的结果限制显示条数

七、查询排序order by

按单列排序    SELECT * FROM employee ORDER BY salary;    SELECT * FROM employee ORDER BY salary ASC;    SELECT * FROM employee ORDER BY salary DESC;按多列排序:先按照age排序，如果年纪相同，则按照薪资排序    SELECT * from employee        ORDER BY age,        salary DESC;

===========order by==========1.select * from employee order by salary;#如果不指定，默认就是升序2.select * from employee order by salary asc;3.select * from employee order by salary desc;#先按照年龄升序，当年龄相同的太多，分不清大小时，在按照工资降序4.select * from employee order by age asc, salary desc;

小例子：

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序select * form employee order by age,hire_date desc;2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列select post ,avg(salary) from employee group by post having avg(salary)>10000;3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列select post ,avg(salary) from employee group by post having avg(salary)>10000 desc;

八、使用聚合函数查询

先from找到表

再用where的条件约束去表中取出记录

然后进行分组group by，没有分组则默认一组

然后进行聚合

最后select出结果

示例：    select count(*) from employee;    select count(*) from employee where depart_id=1;    select max(salary) from employee;    select min(salary) from employee;    select avg(salary) from employee;    select sum(salary) from  employee;    select sum(salary) form employee WHERE depart_id=3;

九、where的补充（使用正则表达式查询）

1.select * from employee where name regexp '^ale';  #匹配以ale开头的员工信息2.select * from employee where name regexp 'on$'; #匹配以on结尾的员工信息3.select * from employee where name regexp 'n{1,2}'; #匹配name里面包含1到2个n的员工信息小结：对字符串匹配的方式where name = 'egon';where name like 'yua%';where name regexp 'on$';

小练习：

查看所有员工中名字是jin开头，n或者g结果的员工信息select * from employee where name regexp '^jin.*[ng]$';

十、限制查询的记录数：LIMIT

=========limit：限制打印几条=========1.select * from employee limit 3;#打印前三条2.像这样表示的：指的是从哪开始，往后取几条 (这样的操作一般用来分页)select * from employee limit 0,3;select * from employee limit 3,4;select * from employee limit 6,3;select * from employee limit 9,3;3.select * from employee order by id desc limit 3; #查看后三条

小练习

1. 分页显示，每页5条

select * from employee limit 0,5;select * from employee limit 5,5;select * from employee limit 10,5;

转载于:https://www.cnblogs.com/lianxuebin/p/8620082.html

你可能感兴趣的文章

Winform 菜单和工具栏控件

查看>>

CDH版本大数据集群下搭建的Hue详细启动步骤（图文详解）

查看>>

巧用Win+R

查看>>

浅析原生js模仿addclass和removeclass

查看>>

Python中的greenlet包实现并发编程的入门教程

App Store最新审核指南（2015年3月更新版）

查看>>

织梦MIP文章内容页图片适配百度MIP规范

查看>>

[Kali_BT]通过低版本SerialPort蓝牙渗透功能手机