MySQL 基础之 单表、多表联查

目录

• 使用和不使用not null 的区别：
  • 不使用： 查询时用‘name is null’ 作为条件
  • 使用：查询时用‘name=’‘ ’作为查询条件
• 单表操作：
  • 单表查询的语法：
    • 分组：group by
      • 分组指的是：
      • 用法：select 聚合函数，字段名 from 表名 group by 分组的字段；
      • 例子：
        • • 1、select count(id), gender from emp group by gender;
          • 2、select depart_id,name, max(age) from emp group by depart_id;
          • 3、min : 求最小的;
          • 4、sum ： 求和; select depart_id, sum(age) from emp group by depart_id;
          • 5、count ： 计数; select depart_id,count(depart_id) from emp group by depart_id;
          • 6、avg ： 平均数; select depart_id, avg(age) from emp group by depart_id;
    • having:
    • order by: order by 字段名 asc（升序）/desc(降序)
    • limit 分页： limit offset, size
• 多表操作
  • 外键： 占用空间少，方便修改数据
    • 一对多：
      • 语法: constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
    • 多对多:
      • 创建男生表
      • 创建女生表
      • 创建关联表
      • 用到 left jion ：
    • 一对一：
      • 创建员工信息表
      • 创建员工工资表
    • 多表联查：
      • left join ...on... :
      • right join ...on...
      • inner join

使用和不使用not null 的区别：

不使用： 查询时用‘name is null’ 作为条件

mysql>create table t8(-> id int auto_increment primary key,-> name varchar(32),-> email varchar(32)-> )charset=utf8;mysql>insert into t8(email) values ('allen');mysql> select * from t8;+----+------+-------+| id | name | email |+----+------+-------+|  1 | NULL | allen |+----+------+-------+1 row in set (0.01 sec)mysql> select * from t8 where name is null;+----+------+-------+| id | name | email |+----+------+-------+|  1 | NULL | allen |+----+------+-------+1 row in set (0.00 sec)

使用：查询时用‘name=’‘ ’作为查询条件

mysql> create table t9(-> id int auto_increment primary key,-> name varchar(32) not null default '',-> email varchar(32) not null default ''-> )charset=utf8;mysql> insert into t9 (email) values ('allen');mysql> select * from t9;+----+------+-------+| id | name | email |+----+------+-------+|  1 |  | allen |+----+------+-------+1 row in set (0.00 sec)mysql> select * from t9 where name='';+----+------+-------+| id | name | email |+----+------+-------+|  1 |  | allen |+----+------+-------+1 row in set (0.01 sec)

单表操作：

单表查询的语法：

select 字段1，字段2 from 表名  						where 条件group by fieldhaving 筛选order by fieldlimit 限制条数

分组：group by

分组指的是：

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

用法：select 聚合函数，字段名 from 表名 group by 分组的字段；

group by 是分组的关键词， 必须和聚合函数 一起出现

where 条件语句和groupby分组语句的先后顺序：
where > group by > having(*********)

例子：

创建表：

create table emp(id int not null unique auto_increment,name varchar(20) not null,gender 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,gender,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 count(id), gender from emp group by gender;

以性别为例， 进行分组， 统计一下男生和女生的人数是多少个：

+-----------+--------+| count(id) | gender |+-----------+--------+|10 | male|| 8 | female |+-----------+--------+2 rows in set (0.01 sec)

2、select depart_id,name, max(age) from emp group by depart_id;

对部门进行分组， 求出每个部门年龄最大的那个人

mysql> select depart_id,name, max(age) from emp group by depart_id;+-----------+--------+----------+| depart_id | name| max(age) |+-----------+--------+----------+| 1 | egon|81 || 2 | 歪歪|48 || 3 | 张野|28 |+-----------+--------+----------+3 rows in set (0.01 sec)

3、min : 求最小的;

4、sum ： 求和; select depart_id, sum(age) from emp group by depart_id;

mysql> select depart_id, sum(age) from emp group by depart_id;+-----------+----------+| depart_id | sum(age) |+-----------+----------+| 1 |  362 || 2 |  150 || 3 |  100 |+-----------+----------+3 rows in set (0.01 sec)

5、count ： 计数; select depart_id,count(depart_id) from emp group by depart_id;

mysql> select depart_id,count(depart_id) from emp group by depart_id;+-----------+------------------+| depart_id | count(depart_id) |+-----------+------------------+| 1 |8 || 2 |5 || 3 |5 |+-----------+------------------+3 rows in set (0.00 sec)

6、avg ： 平均数; select depart_id, avg(age) from emp group by depart_id;

mysql> select depart_id, avg(age) from emp group by depart_id;+-----------+----------+| depart_id | avg(age) |+-----------+----------+| 1 |  45.2500 || 2 |  30.0000 || 3 |  20.0000 |+-----------+----------+3 rows in set (0.00 sec)

having:

having用于对group by之后的数据进行进一步的筛选

mysql> select depart_id, avg(age) from emp group by depart_id having avg(age)>35;+-----------+----------+| depart_id | avg(age) |+-----------+----------+| 1 |  45.2500 |+-----------+----------+1 row in set (0.01 sec)

order by: order by 字段名 asc（升序）/desc(降序)

对多个字段进行排序：

age asc, depart_id desc; 表示先对age进行降序，再把age相等的行按部门号进行升序排列

mysql> select * from emp order by age asc, depart_id desc;+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| gender | age | hire_date  | post| post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| 18 | 程咬铁 | female |  18 | 2014-05-12 | operation| NULL |17000.00 |403 | 3 || 17 | 程咬铜 | male|  18 | 2015-04-11 | operation| NULL |18000.00 |403 | 3 || 16 | 程咬银 | female |  18 | 2013-03-11 | operation| NULL |19000.00 |403 | 3 || 15 | 程咬金 | male|  18 | 1997-03-12 | operation| NULL |20000.00 |403 | 3 || 12 | 星星| female |  18 | 2016-05-13 | sale| NULL |3000.29 |402 | 2 || 11 | 丁丁| female |  18 | 2011-03-12 | sale| NULL |1000.37 |402 | 2 ||  1 | egon| male|  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使  | NULL |7300.33 |401 | 1 ||  7 | jinxin | male|  18 | 1900-03-01 | teacher | NULL |30000.00 |401 | 1 ||  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL |9000.00 |401 | 1 || 14 | 张野| male|  28 | 2016-03-11 | operation| NULL |10000.13 |403 | 3 || 13 | 格格| female |  28 | 2017-01-27 | sale| NULL |4000.33 |402 | 2 ||  5 | liwenzhou  | male|  28 | 2012-11-01 | teacher | NULL |2100.00 |401 | 1 || 10 | 丫丫| female |  38 | 2010-11-01 | sale| NULL |2000.35 |402 | 2 ||  9 | 歪歪| female |  48 | 2015-03-11 | sale| NULL |3000.13 |402 | 2 ||  8 | 成龙| male|  48 | 2010-11-11 | teacher | NULL |10000.00 |401 | 1 ||  4 | yuanhao| male|  73 | 2014-07-01 | teacher | NULL |3500.00 |401 | 1 ||  2 | alex| male|  78 | 2015-03-02 | teacher | NULL | 1000000.31 |401 | 1 ||  3 | wupeiqi| male|  81 | 2013-03-05 | teacher | NULL |8300.00 |401 | 1 |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+18 rows in set (0.01 sec)

select * from emp order by depart_id asc, age desc;

mysql> select * from emp order by depart_id asc, age desc;+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| gender | age | hire_date  | post| post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+|  3 | wupeiqi| male|  81 | 2013-03-05 | teacher | NULL |8300.00 |401 | 1 ||  2 | alex| male|  78 | 2015-03-02 | teacher | NULL | 1000000.31 |401 | 1 ||  4 | yuanhao| male|  73 | 2014-07-01 | teacher | NULL |3500.00 |401 | 1 ||  8 | 成龙| male|  48 | 2010-11-11 | teacher | NULL |10000.00 |401 | 1 ||  5 | liwenzhou  | male|  28 | 2012-11-01 | teacher | NULL |2100.00 |401 | 1 ||  1 | egon| male|  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使  | NULL |7300.33 |401 | 1 ||  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL |9000.00 |401 | 1 ||  7 | jinxin | male|  18 | 1900-03-01 | teacher | NULL |30000.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 || 13 | 格格| female |  28 | 2017-01-27 | sale| NULL |4000.33 |402 | 2 || 11 | 丁丁| female |  18 | 2011-03-12 | sale| NULL |1000.37 |402 | 2 || 12 | 星星| female |  18 | 2016-05-13 | sale| NULL |3000.29 |402 | 2 || 14 | 张野| male|  28 | 2016-03-11 | operation| NULL |10000.13 |403 | 3 || 15 | 程咬金 | male|  18 | 1997-03-12 | operation| NULL |20000.00 |403 | 3 || 16 | 程咬银 | female |  18 | 2013-03-11 | operation| NULL |19000.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 |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+18 rows in set (0.00 sec)

limit 分页： limit offset, size

offset 表示 行数据索引； size 表示取多少条数据

从第offset行开始，取size行数据。

mysql> select * from emp limit 0,10;+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+| id | name| gender | age | hire_date  | post| post_comment | salary | office | depart_id |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+|  1 | egon| male|  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使  | NULL |7300.33 |401 | 1 ||  2 | alex| male|  78 | 2015-03-02 | teacher | NULL | 1000000.31 |401 | 1 ||  3 | wupeiqi| male|  81 | 2013-03-05 | teacher | NULL |8300.00 |401 | 1 ||  4 | yuanhao| male|  73 | 2014-07-01 | teacher | NULL |3500.00 |401 | 1 ||  5 | liwenzhou  | male|  28 | 2012-11-01 | teacher | NULL |2100.00 |401 | 1 ||  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL |9000.00 |401 | 1 ||  7 | jinxin | male|  18 | 1900-03-01 | teacher | NULL |30000.00 |401 | 1 ||  8 | 成龙| 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 |+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+10 rows in set (0.00 sec)

从第6行开始取10行：

mysql> select * from emp limit 6,10;+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+| id | name  | gender | age | hire_date  | post  | post_comment | salary| office | depart_id |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+|  7 | jinxin| male|  18 | 1900-03-01 | teacher| NULL | 30000.00 |401 | 1 ||  8 | 成龙  | 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 || 11 | 丁丁  | female |  18 | 2011-03-12 | sale  | NULL |  1000.37 |402 | 2 || 12 | 星星  | female |  18 | 2016-05-13 | sale  | NULL |  3000.29 |402 | 2 || 13 | 格格  | female |  28 | 2017-01-27 | sale  | NULL |  4000.33 |402 | 2 || 14 | 张野  | male|  28 | 2016-03-11 | operation | NULL | 10000.13 |403 | 3 || 15 | 程咬金| male|  18 | 1997-03-12 | operation | NULL | 20000.00 |403 | 3 || 16 | 程咬银| female |  18 | 2013-03-11 | operation | NULL | 19000.00 |403 | 3 |+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+10 rows in set (0.00 sec)

多表操作

外键： 占用空间少，方便修改数据

一对多：

语法: constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

mysql> create table dep(-> id int auto_increment primary key,-> name varchar(32) not null default ''-> )charset=utf8;mysql> insert into dep (name) values ('研发部'),('运维部'),('前台部'),('小卖部');mysql> create table userinfo (-> id int auto_increment primary key,-> name varchar(32) not null default '',-> depart_id int not null default 1,->-> constraint fk_user_depart foreign key (depart_id) references dep(id)-> )charset utf8;mysql> insert into userinfo (name, depart_id) values ('allen a',1);mysql> insert into userinfo (name, depart_id) values ('allen b',2);mysql> insert into userinfo (name, depart_id) values ('allen c',3);mysql> insert into userinfo (name, depart_id) values ('allen d',4);mysql> insert into userinfo (name, depart_id) values ('allen e',1);mysql> insert into userinfo (name, depart_id) values ('allen f',2);mysql> insert into userinfo (name, depart_id) values ('allen g',3);以上7行符合外键要求，所以能插入不报错，但下边一行插入时会报错mysql> insert into userinfo (name, depart_id) values ('allen h',5);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> insert into userinfo (name, depart_id) values ('allen h',5)' at line 1

多对多:

创建男生表

mysql> create table boy(-> id int auto_increment primary key,-> bname varchar(32) not null default ''-> )charset=utf8;insert into boy (bname) values ('xiaoming'),('xiaogang'),('xiaoqiang');

创建女生表

mysql> create table girl(-> id int auto_increment primary key,-> gname varchar(32) not null default ''-> )charset=utf8;mysql> insert into girl (gname) values ('xiaohong'),('xiaoli'),('xiaojiao');

创建关联表

mysql> create table b2g(-> id int auto_increment primary key,-> bid int not null default 1,-> gid int not null default 0,->-> constraint fk_b2g_boy foreign key (bid) references boy(id),-> constraint fk_b2g_girl foreign key (gid) references girl(id)-> )charset utf8;mysql> insert into b2g (bid, gid) values (1,1),(1,2),(2,3),(3,3),(2,2);

用到 left jion ：

mysql> select * from boy left join b2g on boy.id=b2g.bid left join girl on girl.id=b2g.gid;+----+-----------+------+------+------+------+----------+| id | bname | id| bid  | gid  | id| gname|+----+-----------+------+------+------+------+----------+|  1 | xiaoming  |1 |1 |1 |1 | xiaohong ||  1 | xiaoming  |2 |1 |2 |2 | xiaoli||  2 | xiaogang  |5 |2 |2 |2 | xiaoli||  2 | xiaogang  |3 |2 |3 |3 | xiaojiao ||  3 | xiaoqiang |4 |3 |3 |3 | xiaojiao |+----+-----------+------+------+------+------+----------+5 rows in set (0.01 sec)

mysql> select bname, gname from boy left join b2g on boy.id=b2g.bid left join girl on girl.id=b2g.gid;+-----------+----------+| bname | gname|+-----------+----------+| xiaoming  | xiaohong || xiaoming  | xiaoli|| xiaogang  | xiaoli|| xiaogang  | xiaojiao || xiaoqiang | xiaojiao |+-----------+----------+5 rows in set (0.00 sec)

一对一：

创建员工信息表

mysql> create table user(-> id int auto_increment primary key,-> name varchar(32) not null default ''-> )charset=utf8;mysql> insert into user (name) values ('xiaoming'),('xiaogang'),('xiaoqiang');mysql> select * from user;+----+-----------+| id | name  |+----+-----------+|  1 | xiaoming  ||  2 | xiaogang  ||  3 | xiaoqiang |+----+-----------+3 rows in set (0.00 sec)

创建员工工资表

mysql> create table priv(-> id int auto_increment primary key,-> salary int not null default 0,-> uid int not null default 1,->-> constraint fk_priv_user foreign key (uid) references user(id),-> unique(uid)-> )charset=utf8;mysql> insert into priv (salary, uid) values (2000, 1),(2500,2),(3000,3);mysql> select * from priv;+----+--------+-----+| id | salary | uid |+----+--------+-----+|  1 |2000 |1 ||  2 |2500 |2 ||  3 |3000 |3 |+----+--------+-----+3 rows in set (0.00 sec)

多表联查：

left join ...on... :

mysql> select userinfo.name as uname, dep.name as dname from userinfo left join dep on depart_id=dep.id;+---------+-----------+| uname| dname |+---------+-----------+| allen a | 研发部|| allen e | 研发部|| allen b | 运维部|| allen f | 运维部|| allen c | 前台部|| allen g | 前台部|| allen d | 小卖部|+---------+-----------+7 rows in set (0.01 sec)mysql> select userinfo.name as uname, dep.name as dname from userinfo left join dep on depart_id=dep.id;+---------+-----------+| uname| dname |+---------+-----------+| allen a | 研发部|| allen e | 研发部|| allen b | 运维部|| allen f | 运维部|| allen c | 前台部|| allen g | 前台部|| allen d | 小卖部|+---------+-----------+7 rows in set (0.01 sec)

right join ...on...

inner join