Mysql的查询语句运用（简单查询语句以及条件语句）

Mysql的查询语句运用

先给大家创建一个表以及提供表数据给大家：

# 这个是创建的表
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
    );

# 查看表结构的代码：desc employee;

# 这个是表内的数据：
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)

简单查询

1.经典语句：select (distinct 去重) 字段1，字段2 from 库，表

select id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;
select * from  employee;
select name,salary from employee;

2.避免重复DISTINCT：select distinct 字段1，字段2 from 库，表

select distinct post from employee;

查询结果（类似于python的set）：

3.通过四则运算查询： select 字段 要取的表头名称 from 库，表

SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee; # 让salary*12输出的显示为Annual_salary
SELECT name, salary*12 Annual_salary FROM employee;  # 让salary*12输出的显示为Annual_salary

查询结果分别为（这里只给大家显示第一句和第二局的）：


4.定义显示格式： select concat(‘xxx:’,字段) 要取的表头名称 from 库，表
CONCAT() 函数用于连接字符串，类似python中的
print(f"姓名：{name}")的效果

select concat ('姓名:',name) AAA,concat('年龄：',age) BBB from employee;

查询结果为：

CONCAT_WS() 第一个参数为分隔符
类似python中的
print(f"{name}and{age}")的效果

select concat_ws(':',name,salary) Annual_salary from employee

查询结果为：

---

条件语句

1.where 条件：select 字段1，字段2 from 库，表 where 判断条件

select id,name from employee where id >7;  # 判断id>7
select id,name,salary from employee where salary >8000;  # 判断工资大于8000
select name,salary from employee where salary between 20000 and 30000;  # 判断工资在20000到30000之间的（含）
select name,salary from employee where salary not between 20000 and 30000;    # 判断工资不在20000到30000之间的
select name,salary from employee where salary >= 20000 and salary <= 30000;  # 判断工资在20000到30000之间的

select * from employee where age=73 or age=81 or age=28  # 判断年龄为73或者81或者28
select * from employee where age in (81,73,28)  # 这两种写法运行效果一样

select * from employee where post_comment is Null;    # 判断post_comment为空
select * from employee where post_comment is not Null;   # 判断post_comment不为空

select * from employee where name like "jin%";  # like模糊匹配，会将name为jin开头的都匹配出来
select * from employee where name like "jin___";   # 这里有三个下划线，匹配出来的是jin后边跟上三个字符，如果是四个下划线，就后边跟上四个，以此推类。

2.group by 分组条件：select 字段1，字段2… from 库，表 .group by 字段

select post,count(id) as emp_count from employee group by post;  # 分组后每个职位有多少个员工
select post,max(salary) as emp_count from employee group by post;  # 分组后每个职位的最大薪水
select post,min(salary) as emp_count from employee group by post;  # 分组后每个职位的最小薪水
select post,avg(salary) as emp_count from employee group by post;  # 分组后每个职位的平均薪水
select post,sum(salary) as emp_count from employee group by post;  # 分组后每个职位的薪水总和

select post,group_concat(id,':',name) as emp_count from employee group by post;  # 可以将分组后的组员id+姓名显示出来

3.having 过滤:select 字段1，字段2… from 库，表 group by 字段 having 判断条件

select post,group_concat(name),count(id) from employee group by post;  # 可以将分组后的组员姓名和数目显示出来
select post,group_concat(name),count(id) from employee group by post having COUNT(id) > 2;  # 可以将分组后组员大于2的组员姓名和数目显示出来
select post,avg(salary) from employee group by post having avg(salary) > 10000;  # 可以将分组后组员平均薪水大于10000的平均薪水显示出来
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;   # 可以将分组后组员平均薪水在10000到20000之间的平均薪水显示出来

4.order by 排序字段：select 字段1，字段2… from 库，表 order by 字段

select * from employee order by age asc;   # 按年龄升序排序
select * from employee order by age desc;   # 按年龄降序排序

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

5.limit n 限制条数（n为所限制的条数）：select 字段1，字段2… from 库，表 limit n

select * from employee limit 3  # 不管查询到的语句有几十几百条，在这里限制了它，让它只显示3条

select * from employee order by salary desc limit 3   # 将薪水降序排序，然后不管查询到的语句有多少条，这里让它只显示3条

select * from employee limit 1,5  # 第一个数字表明从1开始，先查询出第一条（id为从2而不是1开始，想从1开始的话就是 limit 0,5），然后包含这一条在内往后查5条（也就是id为2、3、4、5、6）

6.正则表达式：select 字段1，字段2… from 库，表 where 字段 regexp ‘^筛选条件’

select * from employee where name like 'jin%'  # 这是非正则的模糊查询名字为jin开头的，这样虽然方便，但是如果我们要查询的是电话号码，已知电话以136开头，以8000结尾，是不是就不好查询了？

select * fromemployee where name regexp '^jin'  # 正则表达式查询名字为jin开头的
select * from employee where name regexp '^jin.*(g|n)'  # 正则表达式查询名字为jin开头以g或者n结尾的

总结：
    语法顺序：
        select (distinct 去重) 字段1，字段2 from 库，表
            where 条件
            group by 分组条件
            having 过滤
            order by 排序字段
            limit n 限制条数
     执行顺序： 先执行from后边的表 然后后边的判断条件如果为1、2、3则先执行，然后再执行select和from之间的，如果后边有4，再执行4

希望本篇文章可以给你带来帮助~觉得好的话可以给作者一点鼓励！您的鼓励就是我前进路上的不懈动力！