interbase 查询中文乱码_MySQL之表的查询及连表操作

不知名菜鸟 嗨！来了就点个关注呗！ 关注

表的查询

select、where、group by、having、distinct、order by、limit、regexp、like

# 员工表create table emp(    id int not null unique auto_increment,    name varchar(20) not null,    sex enum('male','femal') 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('json','male',18,'20150302','teacher',7000.35,401,1),('jack','male',21,'20150306','teacher',6000.15,401,1),('mary','female',19,'20150402','teacher',6666.35,401,1),('maria','female',23,'20160402','sale',5600.88,402,2),('emily','female',20,'20100102','sale',7777.98,402,2),('tony','male',31,'20130502','operation',8888.35,403,3),('stuck','male',26,'20170702','operation',5555.35,403,3);

当窗口显示不全时，可以使用/G来进行分行展示           select * from emp\G; 若显示中文的时候出现乱码情况，将字符编码统一设置成gbk格式

"""几个重要关键字的执行顺序"""# 书写顺序select id name from emp where id > 3;# 执行顺序from whereselect"""按照书写顺序的方式写sql语句：    sekect * from xxxxxx 先用*占位    之后补全后面的sql语句    最后将*号替换成你想要的具体字段"""

• where筛选条件

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

# 1、查询id大于等于3小于等于6的数据select id,name,age from emp where id>=3 and id<=6;select id,name,age from emp where id between 3 and 6;

# 2、查询薪资是6000.15或者8888.35的数据select name from emp where salary=6000.15 or salary=8888.35;select name from emp where salary in(6000.15,8888.35);  # 成员运算

# 3、查询员工姓名中字母包含o的员工的姓名和薪资"""模糊查询    like        % 匹配任意多个字符        _匹配任意单个字符"""select name,salary from emp where name like '%o%';   # %o%：o的前面和后面都可能出现多个字符

# 4、查询员工姓名是由四个字符组成的，拿到姓名和薪资select name,salary from emp where name like '____';select name,salary from emp where char_length(name) = 4;

# 5、查询id<3或者id>6的数据select * from emp where id not between 3 and 6;

# 6、查询薪资不在20000，18000，16000的数据select * from emp where salary not in (20000,18000,16000);

# 7、查询岗位描述为空的岗位名称select name,post from emp where post_comment is null;

• group by分组操作

分组实际应用场景：男女比例、部门平均薪资、国家之间的数据统计等，应用场景非常广泛。

# 按照部门分组select * from emp group by post;  # 拿到的是每组的第一条数据"""上述命令在未设置严格模式的时候，返回的是每组的第一条数据，但是这不符合分组的规范(分组之后没办法直接获取组内的单个数据)若设置了严格模式，上述命令直接报错！"""set global sql_mode = 'strict_trans_tables,only_full_group_by';# 设置严格模式之后，分组默认只能拿到分组的依据select post from emp group by post;"""mysql> select post from emp group by post;+-----------+| post      |+-----------+| operation || sale      || teacher   |+-----------+3 rows in set (0.00 sec)"""# 什么时候需要分组？"""关键字：每个 平均 最高 最低""""""聚合函数：max min sum avg count"""=====================================================================# 1、获取每个部门的最高薪资select post,max(salary) from emp group by post;"""mysql> select post,max(salary) from emp group by post;+-----------+-------------+| post      | max(salary) |+-----------+-------------+| operation |     8888.35 || sale      |     7777.98 || teacher   |     7000.35 |+-----------+-------------+3 rows in set (0.00 sec)"""select post as '部门',max(salary) as '最高薪资' from emp group by post;**************************************************************************    as可以给字段起别名，也可以直接省略不写(不推荐，语义不明确，容易错乱)**************************************************************************select post '部门',max(salary) '最高薪资' from emp group by post;"""mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post;+-----------+--------------+| 部门      | 最高薪资     |+-----------+--------------+| operation |      8888.35 || sale      |      7777.98 || teacher   |      7000.35 |+-----------+--------------+3 rows in set (0.00 sec)"""# 最低(min)、和(sum)、平均(avg)、数量(count)select post as '部门',count(id) as '部门总人数' from emp group by post;# count()括号中不能放内容为null的字段=====================================================================# 2、查询分组之后的部门名称和每个部门下的所有的员工姓名**************************************************************************    group_concat:不单可获取分组之后的字段值，还支持字段拼接**************************************************************************select post,group_concat(name) from emp group by post;"""mysql> select post,group_concat(name) from emp group by post;+-----------+--------------------+| post      | group_concat(name) |+-----------+--------------------+| operation | tony,stuck         || sale      | maria,emily        || teacher   | json,jack,mary     |+-----------+--------------------+3 rows in set (0.00 sec)"""select post as '部门名称',group_concat(name,'_不知名菜鸟',':',salary) as '部门员工信息' from emp group by post;"""mysql> select post as '部门名称',group_concat(name,'_不知名菜鸟',':',salary) as '部门员工信息' from emp group by post;+--------------+----------------------------------------------------------------------------------------+| 部门名称     | 部门员工信息                                                                           |+--------------+----------------------------------------------------------------------------------------+| operation    | tony_不知名菜鸟:8888.35,stuck_不知名菜鸟:5555.35                                       || sale         | maria_不知名菜鸟:5600.88,emily_不知名菜鸟:7777.98                                      || teacher      | json_不知名菜鸟:7000.35,jack_不知名菜鸟:6000.15,mary_不知名菜鸟:6666.35                |+--------------+----------------------------------------------------------------------------------------+3 rows in set (0.00 sec)"""    concat不分组的时候使用select concat('姓名:',name),concat('工资:',salary) from emp;# 补充:as语法可以给表进行临时起别名select t1.id,t1.name from emp as t1;"""mysql> select t1.id,t1.name from emp as t1;+----+-------+| id | name  |+----+-------+|  1 | json  ||  2 | jack  ||  3 | mary  ||  4 | maria ||  5 | emily ||  6 | tony  ||  7 | stuck |+----+-------+7 rows in set (0.00 sec)"""=====================================================================# 3、查询每个人的年薪select name,salary*12 from emp;"""mysql> select name,salary*12 from emp;+-------+-----------+| name  | salary*12 |+-------+-----------+| json  |  84004.20 || jack  |  72001.80 || mary  |  79996.20 || maria |  67210.56 || emily |  93335.76 || tony  | 106660.20 || stuck |  66664.20 |+-------+-----------+7 rows in set (0.00 sec)"""

注意事项： 1、关键字where和group by 同时出现的时候，group by必须在where的后面(where先对整体数据进行过滤，再进行分组(group_by)操作)。 2、where筛选条件不能使用聚合函数，聚合函数只能在分组之后使用，不分组则默认整体为一组。

• having分组之后的操作

having的语法同where是一致的，只不过having是在分组之后的过滤操作，即having可以使用聚合函数。

统计各部门年龄20岁以上的员工平均工资并且保留平均工资大于6000的部门select post,avg(salary) from emp where age>30 group by post having avg(salary)>6000;# 统计年龄大于30的各部门人员，然后计算平均工资，然后保留大于6000的"""mysql> select post,avg(salary) from emp where age>30 group by post having avg(salary)>6000;+-----------+-------------+| post      | avg(salary) |+-----------+-------------+| operation | 8888.350000 |+-----------+-------------+1 row in set (0.00 sec)""" 

• distinct去重

注意：一定是完全一样的数据才可以去重   有主键存在的情况下不可能去重

# 注意：一定是完全一样的数据才可以去重   有主键存在的情况下不可能去重# ORM 对象关系映射 不懂SQL语句也可以进行数据库的操作    # 表----->类    数据----->对象   字段的值----->对象的属性insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values('json','male',18,'20150302','teacher',7000.35,401,1);select distinct id,age from emp;   # 带主键id不能去重"""mysql> select distinct id,age from emp;+----+-----+| id | age |+----+-----+|  1 |  18 ||  2 |  21 ||  3 |  19 ||  4 |  23 ||  5 |  20 ||  6 |  31 ||  7 |  26 ||  8 |  18 |+----+-----+8 rows in set (0.00 sec)"""select distinct age from emp;   # 可以去重"""mysql> select distinct age from emp;+-----+| age |+-----+|  18 ||  21 ||  19 ||  23 ||  20 ||  31 ||  26 |+-----+7 rows in set (0.00 sec)"""

• order by排序

默认升序排列   asc升序    desc降序

# 默认升序排列 asc升序    desc降序select * from emp order by salary;   # 按照薪资排序select * from emp order by salary asc;select * from emp order by age desc, salary asc;  # 先按照age降序排列，若相同则再按照薪资升序排列"""mysql> select * from emp order by age desc, salary asc;+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+| id | name  | sex  | age | hire_date  | post      | post_comment | salary  | office | depart_id |+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+|  6 | tony  | male |  31 | 2013-05-02 | operation | NULL         | 8888.35 |    403 |         3 ||  7 | stuck | male |  26 | 2017-07-02 | operation | NULL         | 5555.35 |    403 |         3 ||  4 | maria |      |  23 | 2016-04-02 | sale      | NULL         | 5600.88 |    402 |         2 ||  2 | jack  | male |  21 | 2015-03-06 | teacher   | NULL         | 6000.15 |    401 |         1 ||  5 | emily |      |  20 | 2010-01-02 | sale      | NULL         | 7777.98 |    402 |         2 ||  3 | mary  |      |  19 | 2015-04-02 | teacher   | NULL         | 6666.35 |    401 |         1 ||  1 | json  | male |  18 | 2015-03-02 | teacher   | NULL         | 7000.35 |    401 |         1 ||  8 | json  | male |  18 | 2015-03-02 | teacher   | NULL         | 7000.35 |    401 |         1 |+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+8 rows in set (0.00 sec)"""select post,avg(salary) from emp where age>22 group by post having avg(salary)>5000 order by avg(salary) desc;# 统计各部门年龄大于22的员工的平均工资，并且保留平均工资大于5000的部门，然后对平均工资进行降序排列"""mysql> select post,avg(salary) from emp where age>22 group by post having avg(salary)>5000 order by avg(salary) desc;+-----------+-------------+| post      | avg(salary) |+-----------+-------------+| operation | 7221.850000 || sale      | 5600.880000 |+-----------+-------------+2 rows in set (0.00 sec)"""

• limit限制展示条数

针对数据过多的情况，通常作分页处理。

select * from emp limit 3;   # 从emp表中获取3条数据"""mysql> select * from emp limit 3;+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+| id | name | sex  | age | hire_date  | post    | post_comment | salary  | office | depart_id |+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+|  1 | json | male |  18 | 2015-03-02 | teacher | NULL         | 7000.35 |    401 |         1 ||  2 | jack | male |  21 | 2015-03-06 | teacher | NULL         | 6000.15 |    401 |         1 ||  3 | mary |      |  19 | 2015-04-02 | teacher | NULL         | 6666.35 |    401 |         1 |+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+3 rows in set (0.00 sec)"""select * from emp limit 0,5;  # 从0开始，取5条数据select * from emp limit 5,5;  # 从5开始，取5条数据# 第一个参数：起始位置# 第二个参数：展示条数"""mysql> select * from emp limit 5,5;+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+| id | name  | sex  | age | hire_date  | post      | post_comment | salary  | office | depart_id |+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+|  6 | tony  | male |  31 | 2013-05-02 | operation | NULL         | 8888.35 |    403 |         3 ||  7 | stuck | male |  26 | 2017-07-02 | operation | NULL         | 5555.35 |    403 |         3 ||  8 | json  | male |  18 | 2015-03-02 | teacher   | NULL         | 7000.35 |    401 |         1 |+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+3 rows in set (0.00 sec)"""

• 正则

想要处理某个数据的正则表达式，可以去网上直接查找，拿来使用即可。

select * from emp where name regexp '^j.*(n|y)$';  # 以j开头，以n或y结尾的数据"""mysql> select * from emp where name regexp '^j.*(n|y)$';+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+| id | name | sex  | age | hire_date  | post    | post_comment | salary  | office | depart_id |+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+|  1 | json | male |  18 | 2015-03-02 | teacher | NULL         | 7000.35 |    401 |         1 ||  8 | json | male |  18 | 2015-03-02 | teacher | NULL         | 7000.35 |    401 |         1 |+----+------+------+-----+------------+---------+--------------+---------+--------+-----------+2 rows in set (0.00 sec)"""

多表操作

create table dep(    id int,    name varchar(20));create table emps(    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 emps(name,sex,age,dep_id) values('json','male',18,200),('mary','female',48,201),('helen','male',18,201),('jack','male',28,202),('tom','male',18,203),('jerry','female',18,204);

• 拼表查询

select * from dep,emps;   # 结果 笛卡尔积  一个对应多个select * from emps,dep where emps.dep_id = dep.id;  # 表的拼接操作"""mysql> select * from emps,dep where emps.dep_id = dep.id;+----+-------+--------+------+--------+------+--------------+| id | name  | sex    | age  | dep_id | id   | name         |+----+-------+--------+------+--------+------+--------------+|  1 | json  | male   |   18 |    200 |  200 | 技术         ||  2 | mary  | female |   48 |    201 |  201 | 人力资源     ||  3 | helen | male   |   18 |    201 |  201 | 人力资源     ||  4 | jack  | male   |   28 |    202 |  202 | 销售         ||  5 | tom   | male   |   18 |    203 |  203 | 运营         |+----+-------+--------+------+--------+------+--------------+5 rows in set (0.00 sec)"""

inner join 内连接  只拼接共有的数据部分 left join 左连接 左表作为主表，展示所有的数据，右表没有对应的项目则显示NULL right join 右连接 右表作为主表，展示所有的数据，左表没有对应的项目则显示NULL union 全连接 左右量表所有的数据都展

select * from emps inner join dep on emps.dep_id = dep.id;# 只拼接两张表中共有的数据部分，内连接   要习惯性的加上表的前缀"""mysql> select * from emps inner join dep on emps.dep_id = dep.id;+----+-------+--------+------+--------+------+--------------+| id | name  | sex    | age  | dep_id | id   | name         |+----+-------+--------+------+--------+------+--------------+|  1 | json  | male   |   18 |    200 |  200 | 技术         ||  2 | mary  | female |   48 |    201 |  201 | 人力资源     ||  3 | helen | male   |   18 |    201 |  201 | 人力资源     ||  4 | jack  | male   |   28 |    202 |  202 | 销售         ||  5 | tom   | male   |   18 |    203 |  203 | 运营         |+----+-------+--------+------+--------+------+--------------+5 rows in set (0.00 sec)"""select * from emps left join dep on emps.dep_id = dep.id;# 左表作为主表，展示所有的数据，没有对应的项目则显示NULL"""mysql> select * from emps left join dep on emps.dep_id = dep.id;+----+-------+--------+------+--------+------+--------------+| id | name  | sex    | age  | dep_id | id   | name         |+----+-------+--------+------+--------+------+--------------+|  1 | json  | male   |   18 |    200 |  200 | 技术         ||  2 | mary  | female |   48 |    201 |  201 | 人力资源     ||  3 | helen | male   |   18 |    201 |  201 | 人力资源     ||  4 | jack  | male   |   28 |    202 |  202 | 销售         ||  5 | tom   | male   |   18 |    203 |  203 | 运营         ||  6 | jerry | female |   18 |    204 | NULL | NULL         |+----+-------+--------+------+--------+------+--------------+6 rows in set (0.00 sec)"""select * from emps left join dep on emps.dep_id = dep.id union select * from emps right join dep on emps.dep_id = dep.id;"""mysql> select * from emps left join dep on emps.dep_id = dep.id union select * from emps right join dep on emps.dep_id = dep.id;+------+-------+--------+------+--------+------+--------------+| id   | name  | sex    | age  | dep_id | id   | name         |+------+-------+--------+------+--------+------+--------------+|    1 | json  | male   |   18 |    200 |  200 | 技术         ||    2 | mary  | female |   48 |    201 |  201 | 人力资源     ||    3 | helen | male   |   18 |    201 |  201 | 人力资源     ||    4 | jack  | male   |   28 |    202 |  202 | 销售         ||    5 | tom   | male   |   18 |    203 |  203 | 运营         ||    6 | jerry | female |   18 |    204 | NULL | NULL         |+------+-------+--------+------+--------+------+--------------+6 rows in set (0.00 sec)"""

• 子查询

基本思路：        第一步...第二步...第三步...

表的查询结果可以作为其他表的查询条件， 也可以通过起别名的形式作为一个虚拟表与其他表进行关联，只要涉及到数据查询的相关语法，都要一步一步完成。

# 查询部门是技术部还是人力资源部的员工信息1-先获取部门的ID号2-再去员工表里面筛选出对应的员工信息select id from dep where name='技术' or name='人力资源';  # 获取部门IDselect name from emps where dep_id in (200,201);   # 获取员工信息select name from emps where dep_id in (select id from dep where name='技术' or name='人力资源'); """mysql> select name from emps where dep_id in (select id from dep where name='技术' or name='人力资源');+-------+| name  |+-------+| json  || mary  || helen |+-------+3 rows in set (0.00 sec)"""

总结

主要关键字

select distinct 字段1,字段2,... from 表名    where 分组之前的筛选操作    group by 分组条件    having 分组之后的筛选操作    order by 排序字段1 asc,排序字段2 desc    limit 起始位置，展示条数    

where

where id>=3 and id<=6;where id between 3 and 6;where salary=1000 or salary=2000;where salary in (1000,2000);# 模糊匹配 """% 任意多个字符_ 任意单个字符"""where name like '%mode%';where name like '____';where char_length(name) = 4;# 针对null数据， 判断的时候用is  不要用=where post_name is null;where salary*100;  # 可以直接和数字进行运算

group by

# 分组场景    每个    平均    最大    最小    ...    """分组之后只能直接获取到分组的数据，其他字段无法获取"""set global sql_mode = 'strict_trans_tables,only_full_group_by';# 设置严格模式之后，分组默认只能拿到分组的依据#group_concat    可以帮助我们获取到分组之外的字段信息并且可以拼接多个字段select post as '部门名称',group_concat(name,'_不知名菜鸟',':',salary) as '部门员工信息' from emp group by post;# concat：    分组之前帮助我们获取字段信息并且可以拼接多个字段select concat(name,'??') from emp;"""mysql> select concat(name,'??') from emp;+-------------------+| concat(name,'??') |+-------------------+| json??            || jack??            || mary??            || maria??           || emily??           || tony??            || stuck??           || json??            |+-------------------+8 rows in set (0.01 sec)"""# concat_ws:    如果多个字段之间的连接符号是相同的情况下，可以直接使用concat_ws来完成select concat_ws(':',name,age,sex) from emp;   # 将字段之间都用冒号隔开"""mysql> select concat_ws(':',name,age,sex) from emp;+-----------------------------+| concat_ws(':',name,age,sex) |+-----------------------------+| json:18:male                || jack:21:male                || mary:19:                    || maria:23:                   || emily:20:                   || tony:31:male                || stuck:26:male               || json:18:male                |+-----------------------------+8 rows in set (0.00 sec)"""as用法    1-可以直接给字段起别名    2-可以给表起别名聚合函数    max    min    sum    count    avg    必须再分组之后使用

having

# 用法同where一样，作用于分组之后的再次筛选select post,avg(salary) from emp where age>30 group by post having avg(salary)>6000;

distinct

# 数据必须是一模一样的情况下才能去重，不能带主键去重select distinct post from emp;

order by

# 排序  默认升序select post,avg(salary) from emp where age>30 group by post having avg(salary)>6000;

limit

# 主要用于限制数据展示的条数  分页效果select * from emp limit 0,5;  # 从0开始，取5条数据select * from emp limit 5,5;  # 从5开始，取5条数据# 第一个参数：起始位置# 第二个参数：展示条数

regexp

select * from emp where name regexp '^j.*(n|y)$';  # 以j开头，以n或y结尾的数据"""python的re模块常用方法"""findall：分组优先展示(不会展示所有正则表达式匹配到的内容，优先展示括号内的正则表达式匹配到的内容)match：从头匹配search：从整体进行匹配贪婪与非贪婪匹配：    正则表达式默认贪婪匹配，    非贪婪(在正则表达式后面加?)    .*  贪婪    .*? 非贪婪

完