数据库之单表查询

阅读目录

一、单表查询的语法

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

二、关键字的执行优先级

#重点中的重点:关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

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

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

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序:order by

8.限制结果的显示条数

三、简单查询

数据库之单表查询
#创建表
create table emp(
  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
);

#插入记录
#三个部门:教学,销售,运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
(jason,male,18,20170301,张江第一帅形象代言,7300.33,401,1), #以下是教学部
(tom,male,78,20150302,teacher,1000000.31,401,1),
(kevin,male,81,20130305,teacher,8300,401,1),
(tony,male,73,20140701,teacher,3500,401,1),
(owen,male,28,20121101,teacher,2100,401,1),
(jack,female,18,20110211,teacher,9000,401,1),
(jenny,male,18,19000301,teacher,30000,401,1),
(sank,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);

# 当表字段特别多 展示的时候错乱 可以使用\G分行展示
select * from emp\G;

#查看表结构
mysql> desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| 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    |                |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)
准备表和记录
#ps:如果在windows系统中,插入中文字符,select的结果为空白,可以将所有字符编码统一设置成gbk
#简单查询
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM emp;

    SELECT * FROM emp;

    SELECT name,salary FROM emp;

#避免重复DISTINCT(去重)
    SELECT DISTINCT post FROM emp;    

#通过四则运算查询(只能是数值字段)
    SELECT name, salary*12 FROM emp;
    SELECT name, salary*12 AS Annual_salary FROM emp;
    SELECT name, salary*12 Annual_salary FROM emp;

#定义显示格式(as 临时给字段取别名)
   CONCAT() 函数用于连接字符串
   SELECT CONCAT(姓名: ,name,  年薪: , salary*12)  AS Annual_salary 
   FROM emp;
   
#CONCAT_WS() 第一个参数为分隔符,指定分隔符
   SELECT CONCAT_WS(:,name,salary*12)  AS Annual_salary 
   FROM emp;

#结合CASE语句:
   SELECT
       (
           CASE
           WHEN NAME = jason THEN
               NAME
           WHEN NAME = tom THEN
               CONCAT(name,_BIGSB)
           ELSE
               concat(NAME, SB)
           END
       ) as new_name
   FROM
       emp;

四、where筛选条件

# 作用:是对整体数据的一个筛选操作

#where字句中可以使用:

1. 比较运算符:> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. 模糊查询like %egon%%表示任意多字符
    _表示一个字符
5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
    SELECT name FROM emp
        WHERE post=sale;
        
#2:多条件查询
    # 查询id大于等于3小于等于6的数据
       select id,name,age from emp where id>=3 and id<=6;
       select id,name from emp where id between 3 and 6;  两者等价

#3:关键字BETWEEN AND
    SELECT name,salary FROM emp 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM emp 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS)
    SELECT name,post_comment FROM emp
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM emp
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM emp
        WHERE post_comment=‘‘; 注意‘‘是空字符串,不是null
    ps:
        执行
        update emp set post_comment=‘‘ where id=2;
        再用上条查看,就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM emp 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM emp 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM emp 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%#找出name中带有"o"字母的name、salary
    select name,salary from emp where name like %o%;

    通配符’_’
    #查询员工姓名是由四个字符组成的 姓名和薪资  可以使用char_length() 或模糊查找的通配符_
    select name,salary from emp where name like ____;
    select name,salary from emp where char_length(name) = 4;

五、分组查询:group by

什么是分组?为什么要分组?

#1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的

#2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等

#3、为何要分组呢?
    取每个部门的最高工资
    取每个部门的员工数
    取男人数和女人数

小窍门:‘每’这个字后面的字段,就是我们分组的依据


#4、大前提:
    可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数

ONLY_FULL_GROUP_BY

#查看MySQL 5.7默认的sql_mode如下:
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#!!!注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。


#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):
mysql> set global sql_mode=STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY;
#没设置ONLY_FULL_GROUP_BY之前
mysql> select @@global.sql_mode;
+---------------------+
| @@global.sql_mode   |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from emp group by post;
+----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name   | sex    | age | hire_date  | post                        | post_comment | salary     | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 14 | 僧龙   | male   |  28 | 2016-03-11 | operation                   | NULL         |   10000.13 |    403 |         3 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale                        | NULL         |    3000.13 |    402 |         2 |
|  2 | tom    | male   |  78 | 2015-03-02 | teacher                     |              | 1000000.31 |    401 |         1 |
|  1 | jason  | male   |  18 | 2017-03-01 | 张江第一帅形象代言          | NULL         |    7300.33 |    401 |         1 |
+----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)

mysql>
#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果,默认都是组内的第一条记录,但其实这是没有意义的
#设置ONLY_FULL_GROUP_BY
mysql> set global sql_mode=ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)
mysql> quit #设置成功后,一定要退出,然后重新登录方可生效
Bye

mysql>  use school;    #进入库
Database changed
mysql> select @@global.sql_mode;
+----------------------------------------+
| @@global.sql_mode                      |
+----------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from emp group by post;
ERROR 1055 (42000): school.emp.id isnt in GROUP BY
mysql> 

#设置严格模式之后  分组默认只能拿到分组的依据
mysql> select post from emp group by post;
+-----------------------------+
| post                        |
+-----------------------------+
| operation                   |
| sale                        |
| teacher                     |
| 张江第一帅形象代言          |
+-----------------------------+
4 rows in set (0.00 sec)

mysql>
#按照什么分组就只能拿到分组,其他字段不能直接获取,如需获取其他字段需要借助于一些方法(聚合函数)

group by

#单独使用GROUP BY关键字分组
    SELECT post FROM emp GROUP BY post;
    注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助聚合函数

#GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM emp GROUP BY post;#按照岗位分组,并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM emp GROUP BY post;

#GROUP BY与聚合函数一起使用
    select post,count(id) as count from emp group by post;#按照岗位分组,并查看每个组有多少人

#ps:语句中是给select查找的字段取别名

强调:

如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
多条记录之间的某个字段值相同,该字段通常用来作为分组的依据

聚合函数

#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组

示例:
    SELECT COUNT(*) FROM emp;
    SELECT COUNT(*) FROM emp WHERE depart_id=1;
    SELECT MAX(salary) FROM emp;
    SELECT MIN(salary) FROM emp;
    SELECT AVG(salary) FROM emp;
    SELECT SUM(salary) FROM emp;
    SELECT SUM(salary) FROM emp WHERE depart_id=3;

注意事项:

# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000;

select max(salary) from emp;  # 不分组 默认整体就是一组

# 统计各部门年龄在30岁以上的员工平均薪资
    1 先求所有年龄大于30岁的员工
        select * from emp where age>30;
    2 再对结果进行分组        
    select post,avg(salary) from emp where age>30 group by post;

练习:

# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as 部门,max(salary) as 最高薪资 from emp group by post;
select post 部门,max(salary) 最高薪资 from emp group by post;
# as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱

# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post;  # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post;  null不行

# 6.查询分组之后的部门名称和每个部门下所有的员工姓名 
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,_DSB) from emp group by post;
select post,group_concat(name,:,salary) from emp group by post;
# concat不分组的时候用 
select concat(NAME:,name),concat(SAL:,salary) from emp;

# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;  
select emp.id,emp.name from emp as t1;   报错
select t1.id,t1.name from emp as t1;

# 查询每个人的年薪  12薪
select name,salary*12 from emp;

having分组之后的筛选条件

HAVING与WHERE不一样的地方在于!!!!!!

#!!!执行优先级从高到低:where > group by > having 
#1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp 
        where age>30 
        group by post
        having avg(salary) > 10000
        ;

distinct去重

"""
一定要注意 必须是完全一样的数据才可以去重!!!
一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的
[
{‘id‘:1,‘name‘:‘jason‘,‘age‘:18},
{‘id‘:2,‘name‘:‘jason‘,‘age‘:18},
{‘id‘:3,‘name‘:‘egon‘,‘age‘:18}
]
"""
#select distinct id,age from emp;因为id字段不同,所以无法去重
mysql> select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
|  1 |  18 |
|  2 |  78 |
|  3 |  81 |
|  4 |  73 |
|  5 |  28 |
|  6 |  18 |
|  7 |  18 |
|  8 |  48 |
|  9 |  48 |
| 10 |  38 |
| 11 |  18 |
| 12 |  18 |
| 13 |  28 |
| 14 |  28 |
| 15 |  18 |
| 16 |  18 |
| 17 |  18 |
| 18 |  18 |
+----+-----+
18 rows in set (0.00 sec)
#select distinct age from emp; #去掉id字段后
mysql> select distinct age from emp;
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  28 |
|  48 |
|  38 |
+-----+
7 rows in set (0.00 sec)

#总结:
去重的时候最后去掉id字段(因为id字段一般为主键,自增,并不相同)

order by排序

select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
"""
order by默认是升序  asc 因此该asc可以省略不写
也可以修改为降序     desc
"""
select * from emp order by age desc,salary asc;
# 先按照age降序排  如果碰到age相同 则再按照salary升序排

# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
    select post,avg(salary) from emp 
        where age>10 
        group by post
        having avg(salary) > 1000
        order by avg(salary) desc
        ;

limit限制展示条数

select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3;  # 只展示三条数据

select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置
第二个参数是展示条数

mysql> select * from emp limit 0,5;
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                        | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
|  1 | jason | male |  18 | 2017-03-01 | 张江第一帅形象代言          | NULL         |    7300.33 |    401 |         1 |
|  2 | tom   | male |  78 | 2015-03-02 | teacher                     |              | 1000000.31 |    401 |         1 |
|  3 | kevin | male |  81 | 2013-03-05 | teacher                     | NULL         |    8300.00 |    401 |         1 |
|  4 | tony  | male |  73 | 2014-07-01 | teacher                     | NULL         |    3500.00 |    401 |         1 |
|  5 | owen  | male |  28 | 2012-11-01 | teacher                     | NULL         |    2100.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)

mysql>select * from emp limit 5,5;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jack   | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jenny  | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
|  8 | sank   | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
|  9 | 哈哈   | female |  48 | 2015-03-11 | sale    | NULL         |  3000.13 |    402 |         2 |
| 10 | 呵呵   | female |  38 | 2010-11-01 | sale    | NULL         |  2000.35 |    402 |         2 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)

mysql> 

使用正则查询

SELECT * FROM employee WHERE name REGEXP ^ale;

SELECT * FROM employee WHERE name REGEXP on$;

SELECT * FROM employee WHERE name REGEXP m{2};


小结:对字符串匹配的方式
WHERE name = egon;
WHERE name LIKE yua%;
WHERE name REGEXP on$;

 

数据库之单表查询

上一篇:DVWA-7.2 SQL Injection(SQL注入)-Medium-绕过引号转义


下一篇:[netty] 原理入门