mysql查询操作之单表查询、多表查询、子查询

一、单表查询

单表查询的完整语法：               

1、完整语法(语法级别关键字的排列顺序如下)
select distinct 字段1,字段2,字段3,... from 库名.表名
                    where 约束条件
                    group by 分组依据
                    having 过滤条件
                    order by 排序的字段
                    limit 限制显示的条数
                    ;

必须要有的关键字如下：
select * from t1;     分析之前先将其进行占位，需要什么在进行添加

关键字执行的优先级：
from
where
group by
having
distinct
order by
limit

上面的关键字的执行的优先级可以用伪代码的形式写出来：
运行级别：

def from(dir,file):
    open('%s\%s' %(dir,file),'r')
    return f

def where(f,pattern):
    for line in f:
        if pattern：
            yield line

def group():
    pass

def having():
    pass

def distinct():
    pass

def order():
    pass

def limit():
    pass

def select():
    f=from()
    res1=where(f)
    res2=group(res1)
    res3=having(res2)
    res4=distinct(res3)
    res5=order(res4)
    limit(res5)

事先创建好表和插入记录

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
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','老男孩驻沙河办事处外交大使',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)
;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

1、简单查询

select * from t1;                                   #查询t1表中所有字段的记录内容
select id,name,sex from t1;                         #查询指定字段的记录内容
select distinct post from emp;                      #查询emp表中post字段，并为其去重

通过四则运算进行查询：

select name,salary*12 as annual_salary from emp;    #查询表中的指定字段做四则运算，并通过as为字段起别

避免重复DISTINCT

SELECT DISTINCT post FROM employee;              #为某一个字段去重

定义显示格式：

CONCAT() 函数用于连接字符串
select concat('名字: ',name) as new_name,concat("年龄: ",age) as new_age from emp;   #concat在指定的字段记录内容前拼接上我们要的格式，并重新为指定字段起别名
select concat(name,":",age) from emp;               #将两个字段的记录内容通过分隔符拼接成一个字段记录内容
select concat(name,":",age,":",sex) from emp;       #也可以将多个字段记录通过多个分隔符拼接成一个字段记录内容

CONCAT_WS() 第一个参数为分隔符--------------会在每一个字段主键加上冒号
select concat_ws(":",name,age,sex) as info from emp;#每个字段记录内容之间都要用分隔符是不是觉得很麻烦，那么让我们用concat_ws通过一个分隔符就可以搞定上面相同的效果了

case:可以跟多个条件，当然这种筛选完全可以在程序中实现
 SELECT
       (
           CASE
           WHEN NAME = 'egon' THEN
               NAME                    #名字是egon的直接打印出他打的名字
           WHEN NAME = 'alex' THEN     #名字是alex的，做一个拼接操作
               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%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
从硬盘读到内存，没将优化机制就要每一条记录都要进行读取到内存，这样随着记录条数的增加，会拖慢查询的速度

select * from emp where id >= 10 and id <=15; # 等同于select * from emp where id between 10 and 15;   #10<=id<=15
select * from emp where id = 6 or id = 9 or id = 12; # 等同于select * from emp where id in (6,9,12);

#_代表任意单个字符
#%代表任意无穷个字符
select * from emp where name like "__";          #模糊匹配筛选出只包含两个任意字符的名字
select * from emp where name like "jin%";        #模糊匹配筛选出名字是以jin开头的所有名字（jin后面可以跟任意多个字符）
select * from emp where id not in (6,9,12);      #筛选出id不是in括号内的所有记录内容
select * from emp where id not between 10 and 15;#筛选出id不是10<=id<=15的所有字段的记录内容

#要求：查询员工姓名中包含i字母的员工姓名与其薪资
select name,salary from db39.emp where name like '%i%'

#要求：查询员工姓名是由四个字符组成的的员工姓名与其薪资
select name,salary from db39.emp where name like '____';           #_任意单个字符，%任意无穷个字符
select name,salary from db39.emp where char_length(name) = 4;


select *  from db39.emp where id not between 3 and 6;
select * from emp where salary not in (20000,18000,17000);

#要求：查询岗位描述为空的员工名与岗位名
select name,post from db39.emp where post_comment is NULL;
select name,post from db39.emp where post_comment is not NULL;

三、group by分组

1、什么分组：按照所有记录相同的部分进行归类，一定区分度低的字段
2、为何要分组：当我们要以组为单位进行统计时就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义

3、设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据
    mysql> set global sql_mode="strict_trans_tables,only_full_group_by";
    分组之后只能查到分组的字段，以及组内多条记录聚合的成果，要*也不会报错，只会给出每一组的第一条记录
set global sql_mode="strict_trans_tables,only_full_group_by";         #将mysql设置为严格模式
 注意:分组之后，只能查到分组的字段以及组内多条记录聚合的成果---------------分组之前where不能直接使用聚合函数

select * from emp group by post;            #对post进行分组，然后查看所有字段的记录，但是这种单纯的查看是没有意义的
                                             因为分组后查询的结果只是分组后每组的第一条记录

# 聚合函数------------------------结合分组进行使用
max
min
avg
sum
count


select post,count(id) from emp group by post;        #按post字段进行分组，并统计每个分组的记录个数，一般是id作为统计每个分组记录的个数，当然你想用其他的字段也是可以的
select post,max(salary) from emp group by post;      #按post字段进行分组，并统计每组最高薪水的记录，当然为了好看，你还可以给字段起别名
select post,avg(salary) from emp group by post;      #按post字段进行分组，并统计每组最底薪水的记录
select sex,count(sex) from emp group by sex;         #按post字段进行分组，count可以写表中存在的任意字段，并不影响最终查询的结果

# 统计出每个部门年龄30以上的员工的平均薪资
select post,avg(salary) from emp where age >= 30 group by post;  #按照post字段进行分组，筛选出年龄大于30的平均薪资，当然你还可以给平均薪资起别名

# 注意：分组是在where之后发生的
mysql> select * from emp where max(salary) > 3000;   #分组之前where不能直接使用聚合函数
ERROR 1111 (HY000): Invalid use of group function

select max(salary) from emp where salary > 3000;     #通过where筛选条件在使用聚合函数是没有问题的

# group_concat
select post,group_concat(name,':',age) from emp group by post;   #将分组后的多个字段多一个拼接操作，当然我们依然可以为新字段起别名

四、having 过滤条件----having在分组之后，可以直接使用聚合函数

where是在分组之前的过滤，即在分组之前做了一次整体性的筛选
having是在分组之后的过滤，即在分组之后专门针对聚合的结果进行进一步的筛选
新增字段均可以为其起别名

select post,avg(salary) from emp group by post having avg(salary) > 10000; #按post进行分组，并按having后的筛选条进行筛选，然后求出平均值
select post,avg(salary) from emp group by post ;           #只是单纯的求每个部门的平均薪资，并没有为其添加筛选条件

五、order by排序

select * from emp order by age asc; # 默认asc升序-》从小到大---------ascend上升
select * from emp order by age desc;# desc降序-》从大到小------------descend下降

select * from emp order by age asc,salary desc; # 先按照age升序排列，如果age相同则按照salary降序排

select post,avg(salary) from emp group by post order by avg(salary);    #没有写是按照升序还是降序的顺序进行排序，默认的是按照升序进行排序的

六、limit 限制显示的条件

select * from emp limit 3;          #查看表中的记录条数，可以设置我们想要的显示条数，我们还可以指点显示条数的起点，以及显示几条

#薪资最高那个人的详细信息
select * from emp order by salary desc limit 1; #按照薪资降序得到一张新表，新表的第一条记录即我们想要的结果

# 分页显示
select * from emp limit 0,5; # 从0开始往后取5条
select * from emp limit 5,5; #从5开始往后取5条

正则表达式
select * from emp where name regexp "^jin.*(g|n)$";   #模糊匹配太过有限，这时我们强大的正则就派上用场了

七、多表查询

事先创建好表和插入记录

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

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;


#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

1、内连接：把两张表有对应关系的记录连接成一张虚拟表，在笛卡尔积基础上做了筛选---inner join

1、内连接：把两张表有对应关系的记录连接成一张虚拟表-----在笛卡尔积基础上做了一个筛选
          会将左右两张表没有对应关系的也筛选掉
select * from emp inner join dep on emp.dep_id = dep.id;      #on条件筛选出我们需要符合条件的记录，我们可以添加where进一步根据需求进行筛选
              左表           右表    连表的条件

#应用：
    笛卡尔积
    将左表中的每一条记录与右表中的所有条记录进行匹配
    select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不要用where做连表的活

    select * from emp inner join dep on emp.dep_id = dep.id     #on做的是将两张表连接成一张表的筛选条件
        where dep.name = "技术";             #where做连接成一张表后的筛选条件

2、左连接：在内连接的基础上，保留左边没有对应关系的记录-----left join

select * from emp left join dep on emp.dep_id = dep.id;

3、右连接：在内连接的基础上，保留右边没有对应关系的记录-----right join

select * from emp right join dep on emp.dep_id = dep.id;

4、全连接：在内连接的基础上，保留左、右边没有对应关系的记录---左连接 union 右连接

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;

多表查询思路：先定位到和那些表有关，合成一张大表，在基于一张大表进行查询（单表查询）

应用：

#补充：多表连接可以不断地与虚拟表连接
#查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2        #拿到每个部门最高的工资
on t1.post = t2.post
where t1.salary = t2.ms
;

八、子查询

子查询：就是一个查询语句的查询结果用括号括起来当做另外一个查询语句的条件取用

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

1、带IN关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名(子查询得到的是有人的部门id)select name from department where id not in (select distinct dep_id from employee);

2、 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;

3、 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)