MySQL数据库的查询操作

MySQL单表查询

字段解析	字段名	字段类型
雇员编号	id	int
雇员姓名	name	varchar(30)
雇员性别	sex	enum
雇用时期	hire_date	date
雇员职位	post	varchar(50)
职位描述	job_description	varchar(100)
雇员薪水	salary	double(15,2)
办公室	office	int
部门编号	dep_id	int

#创表
CREATE TABLE company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);
#插入数据
insert into company.employee5 (name,sex,hire_date,post,job_description,salary,office,dep_id) values 
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);

#简单查询：
SELECT   字段名称,字段名称2    from  表名   条件
SELECT   column_name,column_2  from  table  WHERE   ...

SELECT name, salary, dep_id from employee5;

#避免重复DISTINCT	#去重
SELECT post FROM employee5;
SELECT DISTINCT post  FROM employee5;

# 拼接两个字段
select name,post from employee5;
select concat(name,'_is_',post) from company.employee5;

#通过四则运算查询
SELECT 437.4384/5;
SELECT 5>3;
SELECT name, salary, salary*14 FROM employee5;
SELECT name, salary, salary*14 AS Annual_salary FROM employee5;#查询并改名
SELECT name, salary, salary*14 Annual_salary FROM employee5;

#定义显示格式
CONCAT() #函数用于连接字符串
SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM employee5;

#单条件查询
SELECT name from employee5 WHERE salary=5000;
> < >=  <= !=
#多条件查询
SELECT name from employee5 WHERE salary>5000 and salary<6000;

#关键字BETWEEN AND
SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

#关键字IS NULL
SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
SELECT name,job_description FROM employee5 WHERE job_description='';

#NULL说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小，所以NULL值总是排在最前。

#关键字IN集合查询
SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000;
SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;

#排序查询 DESC降序 ASC升序（默认）
SELECT name from employee5 order by name;
SELECT name from employee5 order by name desc;
SELECT name from employee5 order by name desc limit 3;
#限制次数limit 分页显示（行号，行数）
SELECT name from employee5 order by name desc limit 1,3;
SELECT name from employee5 order by name desc limit 2,3;

#按多列排序:
入职时间相同的人薪水不同
SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
有差别于
SELECT * from employee5 ORDER BY hire_date DESC;   

#先按入职时间，再按薪水排序
SELECT * from employee5 ORDER BY hire_date DESC,salary DESC;

#先按职位，再按薪水排序
SELECT * from employee5 ORDER BY post,salary DESC;

#分页查询
SELECT * from employee5 limit 0,2;	#两行一页
#限制查询的记录数
SELECT * FROM employee5 ORDER BY salary DESC LIMIT 100;	#默认初始位置为0 
SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,200;	#从第4条开始，共显示5条

#分组查询GROUP BY和GROUP_CONCAT()函数一起使用
SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5   GROUP BY dep_id;

#模糊查询(通配符)   
%  所有字符
SELECT * from employee5 WHERE salary like '%20%';
SELECT * FROM employee5 WHERE hire_date like "2017%"; -- 模糊查询
SELECT * FROM employee5 WHERE LEFT(hire_date,4)=2017; -- right 
SELECT * FROM employee5 WHERE hire_date >"2016-12-31" and hire_date < "2018-01-01";
SELECT * FROM employee5 WHERE hire_date BETWEEN "2016-12-31" and "2018-01-01";
SELECT * FROM employee5 WHERE hire_date REGEXP "2017" -- 正则表达式

#正则查询
SELECT * FROM employee5 WHERE salary REGEXP '72+'; 722222 7222222222
SELECT * FROM employee5 WHERE name REGEXP '^ali';
SELECT * FROM employee5 WHERE name REGEXP 'yun$';
SELECT * FROM employee5 WHERE name REGEXP 'm{1,8}';#M最多出现9次

#函数
count()	#统计数量
max()	#最大值
min()	#最小值
avg()	#平均值
database()	#
user()	#
now()	#
sum()	#总和
password()	#
SELECT COUNT(*) FROM employee5;
SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
SELECT MAX(salary) FROM employee5;
SELECT MIN(salary) FROM employee5;
SELECT AVG(salary) FROM employee5;
SELECT SUM(salary) FROM employee5;
SELECT SUM(salary) FROM employee5 WHERE dep_id=101;