mysql数据库之表查询

最新推荐文章于 2024-04-17 21:47:34 发布

weixin_34407348

最新推荐文章于 2024-04-17 21:47:34 发布

阅读量91

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/wangtenghui/p/10940877.html

版权

一、单表查询

1.语法执行顺序
2.where约束条件
3.group by
4.having
5.distinct
6.order by
7.limit
8.正则

二、多表查询

1.表查询
2.子查询

表（下面用的都是该表）：
    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), #以下是教学部 ('egon','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tank','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jerry','female',18,'20110211','teacher',9000,401,1), ('nick','male',18,'19000301','teacher',30000,401,1), ('sean','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) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

一、单表查询

1.语法执行顺序

 # 语法顺序
    select
    from
    where
    group by # 执行顺序 from where group by select # 初期，推荐按照一定查询优先级的顺序来写SQL语句，容易写出来，并且不容易出错 先是查哪张表：from emp 再是根据条件去查：where id > 3 and id < 10 最后对查询出的数据进行筛选再展示：select name,salary

2.where约束条件

(1)查询id大于等于3，小于等于6的数据
    select id,name from emp where id >= 3 and id <= 6; select * from emp where id between 3 and 6; """ +----+-------+ | id | name | +----+-------+ | 3 | kevin | | 4 | tank | | 5 | owen | | 6 | jerry | +----+-------+ 4 rows in set (0.09 sec) +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | tank | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 4 rows in set (0.00 sec) """ (2)查询薪资是20000或者18000或者17000的数据 select * from emp where salary = 20000 or salary = 18000 or salary = 17000; select * from emp where salary in (20000,18000,17000); # 简写 """ +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ """ (3)查询员工名字中包含o字母的员工姓名和薪资 select name,salary from emp where name like '%o%'; """ +-------+------------+ | name | salary | +-------+------------+ | jason | 7300.33 | | egon | 1000000.31 | | owen | 2100.00 | +-------+------------+ 3 rows in set (0.04 sec) """ (4)查询员工姓名是由四个字符组成的员工姓名与其薪资 '%'和'_'：都可以匹配任意字符，% 为贪婪匹配，_ 为非贪婪匹配 select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4; """ +------+------------+ | name | salary | +------+------------+ | egon | 1000000.31 | | tank | 3500.00 | | owen | 2100.00 | | nick | 30000.00 | | sean | 10000.00 | +------+------------+ 5 rows in set (0.00 sec) """ (5) 查询id小于3或者大于6的数据 select * from emp where id not between 3 and 6; (6)查询薪资不在20000，18000,17000范围的数据 select * from emp where salary not in (20000,18000,17000); """ 查询的数据太多，不粘了 """ (7)查询岗位描述为空的员工名与岗位名，针对null不能用等号，只能用is select name,post from emp where post_comment = NULL; # 查询为空！ select name,post from emp where post_comment is NULL; select name,post from emp where post_comment is not NULL; """ Empty set (0.00 sec) 第二个SQL语句查询结果是整张表，所以不粘了 第三个SQL语句查询的结果为：Empty set (0.00 sec) -->由于插入记录的时候post_comment字段都没有添加 """

3.group by

数据分组应用场景：每个部门的平均薪资，男女比例等

(1)show variables like 'sql_mode';  # 查看严格模式
    set global sql_mode="strict_trans_tables,only_full_group_by"; # 加上以后的东西 设置sql_mode为only_full_group_by,意味着以后但凡分组，只能去到分组的依据 不应该再去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取 select * from emp group by post; # 报错 select id,name,sex from emp group by post; # 报错 下面是报错信息 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day40.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by select post from emp group by post; # 获取部门信息 ''' +-----------------------------+ | post | +-----------------------------+ | operation | | sale | | teacher | | 张江第一帅形象代言 | +-----------------------------+ 4 rows in set (0.00 sec) ''' (2)获取每个部门的最高工资 # 以组为单位统计组内数据-->聚合查询 select post,max(salary) from emp group by post; """ +-----------------------------+-------------+ | post | max(salary) | +-----------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+-------------+ 4 rows in set (0.07 sec) """ # 每个部门的平均工资 select post,avg(salary) from emp group by post; """ +-----------------------------+---------------+ | post | avg(salary) | +-----------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 张江第一帅形象代言 | 7300.330000 | +-----------------------------+---------------+ 4 rows in set (0.04 sec) """ # 每个部门的工资总和 select post,sum(salary) from emp group by post; """ +-----------------------------+-------------+ | post | sum(salary) | +-----------------------------+-------------+ | operation | 84000.13 | | sale | 13001.47 | | teacher | 1062900.31 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+-------------+ 4 rows in set (0.05 sec) """ # 每个部门的人数 select post,count(id) from emp group by post; """ +-----------------------------+-----------+ | post | count(id) | +-----------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 张江第一帅形象代言 | 1 | +-----------------------------+-----------+ 4 rows in set (0.00 sec) """ (3)查询分组之后的部门名称和每个部门下所有的学生姓名 # group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用 select post,group_concat(name) from emp group by post; """ +-----------------------------+------------------------------------------------+ | post | group_concat(name) | +-----------------------------+------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | egon,kevin,tank,owen,jerry,nick,sean | | 张江第一帅形象代言 | jason | +-----------------------------+------------------------------------------------+ 4 rows in set (0.00 sec) """ select post,group_concat(name,"_SB") from emp group by post; """ +-----------------------------+---------------------------------------------------------------+ | post | group_concat(name,"_SB") | +-----------------------------+---------------------------------------------------------------+ | operation | 张野_SB,程咬金_SB,程咬银_SB,程咬铜_SB,程咬铁_SB | | sale | 歪歪_SB,丫丫_SB,丁丁_SB,星星_SB,格格_SB | | teacher | egon_SB,kevin_SB,tank_SB,owen_SB,jerry_SB,nick_SB,sean_SB | | 张江第一帅形象代言 | jason_SB | +-----------------------------+---------------------------------------------------------------+ 4 rows in set (0.00 sec) """ select post,group_concat(name,": ",salary) from emp group by post; """ +-----------------------------+----------------------------------------------------------------------------------------------------------+ | post | group_concat(name,": ",salary) | +-----------------------------+----------------------------------------------------------------------------------------------------------+ | operation | 张野: 10000.13,程咬金: 20000.00,程咬银: 19000.00,程咬铜: 18000.00,程咬铁: 17000.00 | | sale | 歪歪: 3000.13,丫丫: 2000.35,丁丁: 1000.37,星星: 3000.29,格格: 4000.33 | | teacher | egon: 1000000.31,kevin: 8300.00,tank: 3500.00,owen: 2100.00,jerry: 9000.00,nick: 30000.00,sean: 10000.00 | | 张江第一帅形象代言 | jason: 7300.33 | +-----------------------------+----------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) """ select post,group_concat(salary) from emp group by post; """ +-----------------------------+--------------------------------------------------------------+ | post | group_concat(salary) | +-----------------------------+--------------------------------------------------------------+ | operation | 10000.13,20000.00,19000.00,18000.00,17000.00 | | sale | 3000.13,2000.35,1000.37,3000.29,4000.33 | | teacher | 1000000.31,8300.00,3500.00,2100.00,9000.00,30000.00,10000.00 | | 张江第一帅形象代言 | 7300.33 | +-----------------------------+--------------------------------------------------------------+ 4 rows in set (0.00 sec) """ (4) 补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用 select name as 姓名,salary as 薪资 from emp; """ 内容较多，粘了一部分 +-----------+------------+ | 姓名 | 薪资 | +-----------+------------+ | jason | 7300.33 | | egon | 1000000.31 | | kevin | 8300.00 | """ select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp; """ 内容较多，粘了一部分 +-----------------+-----------------+ | 姓名 | 薪资 | +-----------------+-----------------+ | NAME: jason | SAL: 7300.33 | | NAME: egon | SAL: 1000000.31 | | NAME: kevin | SAL: 8300.00 | | NAME: tank | SAL: 3500.00 | """ (5) 补充as语法 即可以给字段起别名也可以给表起 select emp.id,emp.name from emp as t1; # 报错 因为表名已经被你改成了t1 """ ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list' """ select t1.id,t1.name from emp as t1; """ 内容较多，粘了一部分 +----+-----------+ | id | name | +----+-----------+ | 1 | jason | | 2 | egon | | 3 | kevin | """

4.having

having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

(1) 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
    select post,avg(salary) from emp
            where age >= 30 group by post having avg(salary) > 10000; """ +---------+---------------+ | post | avg(salary) | +---------+---------------+ | teacher | 255450.077500 | +---------+---------------+ 1 row in set (0.00 sec) """ #强调：having必须在group by后面使用 select * from emp having avg(salary) > 10000; # 报错

5.distinct

# 对有重复的展示数据进行去重操作
    select distinct post from emp;
    
    """
    +-----------------------------+
    | post                        |
    +-----------------------------+
    | 张江第一帅形象代言          |
    | teacher                     |
    | sale                        |
    | operation                   |
    +-----------------------------+
    4 rows in set (0.04 sec)
    """

6.order by

select * from emp order by salary asc; #默认升序排

    select * from emp order by salary desc; #降序排

    select * from emp order by age desc; #降序排

    #先按照age降序排，在年轻相同的情况下再按照薪资升序排
    select * from emp order by age desc,salary asc;

    # 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
    select post,avg(salary) from emp
        where age > 10
        group by post
        having avg(salary) > 1000
        order by avg(salary)
        ;
    """ +-----------------------------+---------------+ | post | avg(salary) | +-----------------------------+---------------+ | sale | 2600.294000 | | 张江第一帅形象代言 | 7300.330000 | | operation | 16800.026000 | | teacher | 151842.901429 | +-----------------------------+---------------+ 4 rows in set (0.00 sec) """

7.limit

# 限制展示条数
select * from emp limit 3;

# 查询工资最高的人的详细信息 select * from emp order by salary desc limit 1; # 分页显示 select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置 select * from emp limit 5,5;

8.正则

select * from emp where name regexp '^j.*(n|y)$';
# 以 j开头，中间匹配任意字符，以 n 或 y 结尾

二、多表查询

#建表
    create table dep(
    id int,
    name varchar(20)
    );

    create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',38,201), ('nick','female',28,202), ('owen','male',18,200), ('jerry','female',18,204) ;

1.表查询

(1)select * from emp,dep;  # 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积

(2) 查询员工及所在部门的信息
select * from emp,dep where emp.dep_id = dep.id;
    """
    +----+-------+--------+------+--------+------+--------------+
    | id | name  | sex    | age  | dep_id | id   | name         |
    +----+-------+--------+------+--------+------+--------------+
    |  1 | jason | male   |   18 |    200 |  200 | 技术         |
    |  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
    |  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
    |  4 | nick  | female |   28 |    202 |  202 | 销售         |
    |  5 | owen  | male   |   18 |    200 |  200 | 技术         |
    +----+-------+--------+------+--------+------+--------------+
    5 rows in set (0.00 sec)
    """

(3) 查询部门为技术部的员工及部门信息
select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术'; """ +----+-------+------+------+--------+------+--------+ | id | name | sex | age | dep_id | id | name | +----+-------+------+------+--------+------+--------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 5 | owen | male | 18 | 200 | 200 | 技术 | +----+-------+------+------+--------+------+--------+ 2 rows in set (0.00 sec) """

(3) 将两张表关联到一起的操作，有专门对应的方法
    # 1、内连接：只取两张表有对应关系的记录
    select * from emp inner join dep on emp.dep_id = dep.id;
    select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术"; """ +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 200 | 200 | 技术 | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) """ # 2、左连接: 在内连接的基础上保留左表没有对应关系的记录 select * from emp left join dep on emp.dep_id = dep.id; """ +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 5 | owen | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 6 | jerry | female | 18 | 204 | NULL | NULL | +----+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) """ # 3、右连接: 在内连接的基础上保留右表没有对应关系的记录 select * from emp right join dep on emp.dep_id = dep.id; """ +------+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) """ # 4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id; """ +------+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 5 | owen | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 6 | jerry | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+-------+--------+------+--------+------+--------------+ 7 rows in set (0.04 sec) """

2.子查询

# 就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

(1)查询部门是技术或者人力资源的员工信息
"""
先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
"""
    select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源"); """ +----+-------+--------+------+--------+ | id | name | sex | age | dep_id | +----+-------+--------+------+--------+ | 1 | jason | male | 18 | 200 | | 2 | egon | female | 48 | 201 | | 3 | kevin | male | 38 | 201 | | 5 | owen | male | 18 | 200 | +----+-------+--------+------+--------+ 4 rows in set (0.00 sec) """ (2)每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询 select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1 inner join (select post,max(hire_date) as max_date from emp group by post) as t2 on t1.post = t2.post where t1.hire_date = t2.max_date ; """ +----+--------+------------+-----------------------------+-----------------------------+------------+ | id | name | hire_date | post | post | max_date | +----+--------+------------+-----------------------------+-----------------------------+------------+ | 1 | jason | 2017-03-01 | 张江第一帅形象代言 | 张江第一帅形象代言 | 2017-03-01 | | 2 | egon | 2015-03-02 | teacher | teacher | 2015-03-02 | | 13 | 格格 | 2017-01-27 | sale | sale | 2017-01-27 | | 14 | 张野 | 2016-03-11 | operation | operation | 2016-03-11 | +----+--------+------------+-----------------------------+-----------------------------+------------+ 4 rows in set (0.35 sec) """ """ 记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询 """ select * from emp inner join dep on emp.dep_id = dep.id; """ +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 38 | 201 | 201 | 人力资源 | | 4 | nick | female | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 200 | 200 | 技术 | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) """