新建表数据
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
-> ('laura', 'male', 19, 200),
-> ('wendy', 'male', 20, 201),
-> ('iris', 'male', 38, 201),
-> ('linda', 'female', 28, 202),
-> ('john', 'female', 39, 200),
-> ('andy', 'male', 26, 204)
-> ;
查看表结构和数据
desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
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 | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
mysql> select * from employee;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | laura | male | 19 | 200 |
| 2 | wendy | male | 20 | 201 |
| 3 | iris | male | 38 | 201 |
| 4 | linda | female | 28 | 202 |
| 5 | john | female | 39 | 200 |
| 6 | andy | male | 26 | 204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)
要想在两个表查询信息,必须先把两个表联系起来,如我现在想查每个人对应的部门,
交叉连接
也叫直接查询,表与表之间产生了一个暴力全集-----生成笛卡尔积
笛卡尔积:如果有两个集合[a, b, c], [1, 2, 3],会得到9个结果:a1, b1, c1, b1, b2, b3, c1, c2, c3.
所以笛卡尔积对于查询两个表之间的数据并没有什么意义。
内外连接
内连接
所谓内连接,相当于拼接出来一张新表
关键字:inner join 表示表1和表2内连接了 on 以什么条件为连接,一般以表1的外键字段 和表2某个字段拼接。
如:
表1 字段: id , name , sex , age , dep_id
表2 字段: id, name
目标:以表1为基准,和表2做拼接。表1的dep_id和id对上才行。
语法:select * from 表1 inner join 表2 on 表1.外键字段 = 表2.字段
只有两个表中互相匹配的项才会被显示在新的表中
还可以从内连接当中筛选要显示的字段,相当于从新表中筛选出想要的内容
外链接
左外链接
以左边的表为标准,尽管左边某一项没有任何部门,右边拼个NULL就行了。左表元素一定出现在新表里。
右外链接
左表可以不全,右表中所有数据必须出现。
全外链接
左边和右边的全在新表中,没有连上的都显示空。mysql没有专门语法,只有左外链接和右外连接拼在一起,用关键字union关联起来。
连接查询
符合条件的连接查询:在拼好的表中做筛选。 形成了新表后,查询方式又回到了单表查询
-
以内连接的方式查询employee和department表,并且employee表中的age字段值大于25
-
以内连接的方式查询employee和department表,并且以age字段的升序方式显示。
子查询
在一个查询里面有两个以上的select,先select一个结果后,另外一条select在这个结果上查询。在一条查询里套着一条子查询
带in关键字查询
- 查询平均年龄在25岁以上的部门名:
- 首先查询平均年龄在25岁以上的id,因为这件可以在一张表里面搞定。
- 取到部门id后再找部门名
- 把第一步和第二步拼在一起
- 查看技术部门员工姓名
-
先找出技术部门id
-
再找出dep_id = 200 的人
- 查看不足1人的部门名
- 先把有人的部门查出来
- 不在以上部门就是要找的部门
带比较运算符的子查询
比较运算符: =, != , > , >= , < , <= , <>
当查出来的值是某个值时,就可以做比较
- 查询大于所有平均年龄的员工名与年龄。
- 先找到所有员工的平均年龄
- 找到employee里面大于28.3333的值
带exists关键字的子查询
exists表示关键字是否存在,select语句是否执行完全取决于exists的True or False
例
综合练习
mysql> create table employee2(
-> 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
-> );
Query OK, 0 rows affected (0.66 sec)
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 | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
插入数据
insert into employee2(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
-> ('laura','male',18,'20170301','teacher',7300.33,401,1),
-> ('wendy','male',78,'20150302','teacher',1000000.31,401,1),
-> ('iris','male',81,'20130305','teacher',8300,401,1),
-> ('david','male',73,'20140701','teacher',3500,401,1),
-> ('sinala','male',28,'20121101','teacher',2100,401,1),
-> ('john','female',18,'20110211','teacher',9000,401,1),
-> ('shaer','male',18,'19000301','teacher',30000,401,1),
-> ('guojin','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)
-> ;
mysql> select * from employee2;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | laura | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 |
| 2 | wendy | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | iris | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | david | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | sinala | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | john | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | shaer | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | guojin | 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 |
| 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.34 sec)
- 查询每个部门最新入职的员工
- 第一种方法
-
根据部门进行分组
-
查询每个部门入职最晚的时间
不能直接取人名,如果直接用人名,谁的名字在第一个就显示谁。所以只能先查日期。如
-
将时间和人对上,也就是利用时间找人,从employee表中找到表2中时间一样的人。特点:表1中post和表2 post一样,表1中hire_date 和表2的max(hire_date) 一样。
- 第二种方法