数据库的查询

数据库查询

为了直观的反映查看效果，在这里先创建一个表
创建要求
测试表：company.employee5
雇员编号 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
创建：

				mysql> 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
			     );

插入表数据

				mysql> 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),
				('','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 表名 条件
mysql> select * from employee5;

select name, salary, dep_id from employee5 where id <=5; //根据指定的字段进行查询

  

• 避免重复DISTINCT
  SELECT post FROM employee5;

SELECT distinct post FROM employee5;
  
  注：不能部分使用DISTINCT，通常仅用于某一字段。

• 通过四则运算查询
  SELECT name, salary, salary*14 FROM employee5;

SELECT name, salary, salary*14 AS Annual_salary FROM employee5; //as annual_salary 创建的新的别名
  

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

• 单条件查询
  SELECT name,post FROM employee5 WHERE post='hr';
  

• 多条件查询
  select * from employee5 where salary>5000 and salary<10000 or dep_id=102;
  

• 关键字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;
  
  NULL说明：
  1、等价于没有任何值、是未知数。
  2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
  3、对空值做加、减、乘、除等运算操作，结果仍为空。
  4、比较时使用关键字用“is null”和“is not null”。
  5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。

• 关键字IN集合查询
  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) ;
  

• 关键字LIKE模糊查询
  SELECT * FROM employee5 WHERE name LIKE 'al%';
  

• 排序查询
  mysql> select name,salary from employee5 order by salary desc ; //按照工资有高到低进行打印输出。
  asc //升序
  desc //降序
  

• 使用集合函数查询
  select max(salary) from employee5; //部门薪资最高

select * from employee5 where salary = (select max(salary) from employee5); //打印薪水最高的这个人的详细信息：
  

• 分组查询：GROUP BY和GROUP_CONCAT()函数一起使用
  SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; //部门ID相同，就把名字拼到一起：