MySQL 之 数据查询

数据查询

数据查询一直是我们使用数据库的重头戏，很多人其实大部分时候都用不上数据库的增删改，但是查一定是会用得到的，能够熟练的写出查询语句都会让自己的工作效率大幅度提升。

先来创建一个用户表，作为我们练习查询操作的对象，当然这个表和实际项目中的表还是有一定的区别的。

create table emp(
  	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
);

插入记录
三个部门：人事，教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('亚瑟','male',24,'20170301','HR',7300.33,401,1), 
('典韦','male',28,'20150302','teacher',100000.31,401,1),
('鲁班七号','female',1,'20130305','HR',8300,401,1),
('兰陵王','male',33,'20140701','teacher',3500,401,1),
('刘禅','male',28,'20121101','teacher',2100,401,1),
('张飞','male',18,'20110211','teacher',9000,401,1),
('雅典娜','female',18,'19000301','teacher',30000,401,1),
('荆轲','female',24,'20101111','teacher',10000,401,1),
('露娜','female',48,'20150311','sale',3000.13,402,2),
('李白','male',28,'20101101','sale',2000.35,402,2),
('花木兰','female',19,'20110312','sale',1000.37,402,2),
('张良','male',20,'20160513','sale',3000.29,402,2),
('安琪拉','female',28,'20170127','sale',4000.33,402,2),
('貂蝉','female',28,'20160311','operation',10000.13,403,3),
('程咬金','male',48,'19970312','operation',20000,403,3),
('武则天','male',38,'20130311','operation',19000,403,3),
('姜子牙','male',58,'20150411','operation',18000,403,3),
('后羿','male',28,'20140512','operation',17000,403,3)
;

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

单表查询

刚开始查询表，一定要按照最基本的步骤，先确定是哪张表，再确定查这张表也没有限制条件，再确定是否需要分类，最后再确定需要什么字段对应的信息

书写顺序

select distinct * from '表名' where '限制条件'  group by '分组依据' 
having '过滤条件' order by '排序依据' limit '限制条件'

执行顺序

from → where → group by → having → order by → limit → distinct → select

where约束条件

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

mysql> select *  from emp where id between 3 and 6;
+----+--------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name         | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+--------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  3 | 鲁班七号     | female |   1 | 2013-03-05 | HR      | NULL         | 8300.00 |    401 |         1 |
|  4 | 兰陵王       | male   |  33 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  5 | 刘禅         | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
|  6 | 张飞         | male   |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+--------------+--------+-----+------------+---------+--------------+---------+--------+-----------+

2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); 

mysql> select * from emp where salary in (20000,18000,17000);
+----+-----------+------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex  | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金    | male |  48 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
| 17 | 姜子牙    | male |  58 | 2015-04-11 | operation | NULL         | 18000.00 |    403 |         3 |
| 18 | 后羿      | male |  28 | 2014-05-12 | operation | NULL         | 17000.00