MySQL数据查询之单表查询

- 创建表
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 创建数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');
-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

准备表和数据

#查询语法: 
select [distinct]*(所有)|字段名,...字段名 from 表名;
 
#查询所有字段信息
select * from person;
 
#查询指定字段信息
select id,name,age,sex,salary from person;
 
#别名查询，使用的as关键字，as可以省略的
select name,age as'年龄',salary '工资' from person;
 
#直接对列进行运算,查询出所有人工资,并每人增加100块.
select (5/2);
select name, salary+100 from person;
 
#剔除重复查询
select distinct age from person;

#查询格式:
select [distinct]*(所有)|字段名,...字段名 from 表名 [where 条件过滤]
 
#比较运算符: > < >= <= = <>(!=)    is null 是否为null
select * from person where age = 23;
select * from person where age <> 23;
select * from person where age is null;
select * from person where age is not null;
 
#逻辑运算符: 与 and 或 or
select * from person where age = 23 and salary =29000;
select * from person where age = 23 or salary =29000;

# 使用  between...and  进行区间 查询
select * from person where salary between 4000 and 8000;
ps: between...and 前后包含所指定的值
等价于 select * from person where salary >= 4000 and salary <= 8000;

#使用 in 集合(多个字段)查询
select * from person where age in(23,32,18);
等价于: select * from person where  age =23 or age = 32 or age =18;
 
#使用 in 集合 排除指定值查询
select * from person where age not in(23,32,18);

#模糊查询  like %:任意多个字符,  _:单个字符
 
#查询姓名以"张"字开头的
select * from person where name like '张%';
#查询姓名以"张"字结尾的
select * from person where name like '%张';
#查询姓名中含有"张"字的
select * from person where name like '%张%';
 
#查询 name 名称 是四个字符的人
select * from person where name like '____';
#查询 name 名称 的第二个字符是 'l'的人
select * from person where name like '_l%';
 
#排除名字带 a的学生
select * from student where name not like 'a%'

#排序查询格式:
select 字段|* from 表名 [where 条件过滤] [order by 字段[ASC][DESC]]
 
升序:ASC 默认为升序
降序:DESC
PS:排序order by 要写在select语句末尾
 
#按人员工资正序排列,注意:此处可以省略 ASC关键字
select * from person order by salary ASC;
select * from person order by salary;
 
#工资大于5000的人,按工资倒序排列
select * from person where salary >5000 order by salary DESC;
 
#按中文排序
select * from person order by name;
 
#强制中文排序
select * from person order by CONVERT(name USING gbk);
ps:UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序
 

#格式:
select 聚合函数(字段) from 表名;
 
#统计人员中最大年龄、最小年龄，平均年龄分别是多少
select max(age),min(age),avg(age) from person;

#分组查询格式:
select 被分组的字段 from 表名 group by 分组字段 [having 条件字段]
ps: 分组查询可以与 聚合函数 组合使用.

#查询每个部门的平均薪资
select avg(salary),dept from person  GROUP BY dept;

#查询每个部门的平均薪资 并且看看这个部门的员工都有谁?
select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept;
#GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来

#查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?  
select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept; having avg(salary)>10000;

#查询前5条数据
select * from person limit 5;
 
#查询第5条到第10条数据
select * from person limit 5,5;
 
#查询第10条到第15条数据
select * from person limit 10,5;
 
ps: limit (起始条数),(查询多少条数);

# ^  匹配 name 名称 以 "e" 开头的数据
select * from person where name REGEXP '^e';
 
# $  匹配 name 名称 以 "n" 结尾的数据
select * from person where name REGEXP 'n$';
 
# . 匹配 name 名称 第二位后包含"x"的人员 "."表示任意字符
select * from person where name REGEXP '.x';
 
# [abci] 匹配 name 名称中含有指定集合内容的人员
select * from person where name REGEXP '[abci]';
 
# [^alex] 匹配 不符合集合中条件的内容 , ^表示取反
select * from person where name REGEXP '[^alex]';
#注意1：^只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
#注意2 : 简单理解 name  REGEXP '[^alex]' 等价于 name != 'alex'
 
# 'a|x' 匹配 条件中的任意值
select * from person where name REGEXP 'a|x';　　
 
#查询以w开头以i结尾的数据
select * from person where name regexp '^w.*i$';
#注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾

select name, max(salary)
   
from person  
   
where name is not null  
   
group by name  
   
having max(salary) > 5000
   
order by max(salary)
 
limit 0,5