常用的mysql查询语句--DQL操作

• 概念：

  – 数据库管理系统一个重要的功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。

  – mysql提供了功能强大、灵活的语句来实现这些操作

  – Mysql使用select语句来进行查询操作

• 语法格式：

  select
  	[all | distinct]
  	<目标列的表达式1> [别名],
  	<目标列的表达式1> [别名],
  	...
  from <表名或视图名> [别名], <表名或视图名> [别名]...
  [where<条件表达式>]
  [group by <列名>]
  [having <条件表达式>]
  [order by <列名> [asc | desc]]
  [limit <数字或列表>];
  
  select *|column from tablename where condition;
  

1. select:字段表达式

• select既可以查询，也可以输出，示例：

  select rand(), --随机数
  select unix_timestamp(), --显示unix时间戳
  select id, name form <表名>; --在<表名>中查询id，name并输出
  

2. from 子句

• select 字段 from 表名;

• from后面是数据源，数据源可以有多个，一般也是用表名做数据源，也可以是其他查询的结果

  select student.name, score.math from student, score; --从名为学生、分数的两个表中查询姓名、数学分数
  
  select * from student; --从student表中查询所有信息
  

• 别名查询

  –表别名：

  select * from product as p; --将product表取别名p，查询其所有信息，as可以省略
  

  – 列别名：

  select column1 as '1' column2 as '2' from tablename;
  

3. where 子句：按指定条件过滤

• select 字段 from 表名 where 条件;

• where 是做条件查询，只返回结果为True的数据

  select name from staff where city = '上海';
  

• 空值判断：is null | is not null

  select `name` from `staff` where `description` is null;
  select `name` from `staff` where `description` is not null;
  

• 范围判断:

  – between … and …;

  – not between … and …;

  select id, math from score where math between 60 and 70;
  select id, math from score where math not between 60 and 70;
  select * from score where math>=80 and english<=60
  

4. having

• having和where功能类似，都可以做条件查询，通常情况下可以互换使用，甚至是混用

  select `name`, `birthday` from `staff` where `birthday` > '1995-1-1';
  select `name`, `birthday` from `staff` having `birthday` > '1995-1-1';
  select * from staff where id>=3 and city='北京';
  select * from staff having id>=3 and city='北京';
  select * from staff where id>=3 having city='北京';
  

• 只能用where不能用having的情况

  select `name`, `birthday` from `staff` where id>2;
  
  select `name`, `birthday` from `staff` having id>2; 
  --报错，having的查询，只能是在前面的搜索结果中，即name，birthday中
  

• 只能使用having不能使用where的情况

  select name as n, birthday as b, id as i from staff having i>2;
  
  select name as n, birthday as b, id as i from staff where i>2; 
  --报错，where只能识别表中添加的字段，不能识别别名
  
  select city, group_concat(birthday) from staff group by city having min(year(birthday))>1995;
  --筛选出每个城市中满足最小出生年份大于1995的
  

5. group by：分组查询

• 按照某一字段进行分组，会将该字段中值相同的归为一组，再将查询结果分类显示，便于统计

• 如果语句中包含where，则group by需要放在where后面

• 如果要进行分组的话，则select子句之后，只能出现分组的字段和统计函数，其他字段不能出现

• select , from group by 分组字段 having 分组条件;

• 分组之后的条件筛选–having

  – 分组之后对统计结果进行筛选的话必须使用having,不能使用where

  – where子句用来筛选from子句中指定的操作所产生的行

  – group by子句用来分组where子句的输出

  – having子句用来从分组的结果中筛选

  select gender, count(id) from staff group by gender;
  
  select gender, name from staff group by gender; --报错，要进行分组的话，则select子句之后，只能出现分组的字段和统计函数，其他字段name不能出现
  
  select gender, group_concat(name) from staff group by gender; --用group将需要的结果通过聚合函数group_concat拼接
  
  select category_id, count(*) from product group by category_id having count(*) > 4; --统计各类商品的个数，但只显示个数大于4的信息
  

6. order by：按字段排序

• order by的主要作用是排序

• 需要放在group by 和having后

• select from order by <排序column> asc|desc;

• asc按升序排列，desc按降序排列，默认按asc

  select * from staff order by age; --从staff表中按年龄升序排序查询并显示所有信息
  
  select * from staff order by age desc;  --从staff表中按年龄降序排序查询并显示所有信息
  
  select city, avg(salary), group_concat(name), sum(salary) from staff group by city having sum(salary)>3000 order by sum(solary); 
  --可以分行写成以下形式：
  select
  	city, avg(salary), group_concat(name), sum(salary)
  from
  	staff
  group by
  	city
  having
  	sum(salary)>3000
  order by
  	sum(salary);
  -- sql语句执行顺序：from -> group by -> avg(salary),group_concat(name),sum(salary) -> select -> having ->order by
  

7. limit：限制取出的数量

   select <column> from <tablename> limit m; -- 从第1行到第m行
   select <column> from <tablename> limit m, n; -- 从第m+1行开始，显示n行
   select <column> from <tablename> limit m offset n; -- 跳过前n行，取后面的M行
   

• 譬如用在分页显示中

  per_page = 5 --每页显示5行
  current_page = 1 --当前页码为
  select * from staff limit per_page offset (current_page-1)*perpage; 
  select * from staff limit (current_page-1)*perpage, perpage;
  

8. distinct：去重

   select distinct city from staff;
   

9. dual表：是一个内置的虚拟的表，仅为了保证select … from …语句的完整性

   select now() from dual;
   

10. 子查询

• 子查询（也称为内嵌查询或嵌套查询）是嵌套在另一个查询中的 SQL 查询。子查询可以出现在 SELECT、INSERT、UPDATE、DELETE 等语句中，通常用于返回一个或多个值，这些值可以作为条件或数据源用于外部查询。

• 子查询可以是标量子查询，也可以是表子查询。标量子查询返回单个值，而表子查询返回一个结果集（一行或多行）。

• 标量子查询

  标量子查询通常用在 SELECT 列表、WHERE 子句或 HAVING 子句中，返回单个值。

  示例：

  SELECT (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count
  FROM customers;
  

  –这个查询返回每个客户的订单数量

• 表子查询

  表子查询返回一个结果集，它可以出现在 FROM 子句中，或者在 WHERE 子句中使用 IN、ANY、ALL 等操作符。

  示例：

  SELECT *
  FROM (SELECT id, name FROM customers WHERE country = 'USA') AS us_customers
  WHERE id IN (SELECT customer_id FROM orders);
  

  –这个查询返回所有在美国且至少有一个订单的客户。

• 子查询的限制

  –标量子查询必须只返回一个值，否则会导致错误。

  –表子查询必须只返回一个列的结果集，如果返回多个列，也会导致错误。

  –子查询通常不能包含 ORDER BY 子句，除非它是一个 SELECT 语句的一部分，并且该 SELECT 语句被用于 FROM 子句中。

• 子查询的使用场景

  –作为条件：在where子句中使用子查询作为条件

  –作为数据源：在from子句中使用子查询作为数据源

  –在select列表中：在select列表中使用子查询来计算或返回特定的值

  –在having子句中：使用子查询来对分组后的数据进行条件筛选

11. insert into select语句

    • 作用

      将一张表的数据导入到另一张表

    • 语法

      insert into table2(column1,lolumn2,...) select value1,value2,... from table1;
      或者:
      insert into table2 select * from table1;
      

12. 查询案例：

    –创建staff表

    create table staff(
         sno int unique comment '员工编号',
         sname varchar(16) comment '员工姓名',
         job varchar(16) comment '岗位名称',
         mgr int comment '上级编号',
         hiredate date comment '入职日期',
         salary int comment '员工薪资',select depno, job, max(salary) from staff group by depno, job;
         bonus int comment '员工年终奖',
         depno int comment '部门编号'
         )charset=utf8;
    

    – 查询staff表中，姓名第二个字母不是’a’且薪资>1000的员工信息，按年薪（salary*12+bonus）降序排列

    select * from staff where sname not like '_a%' and salary > 1000 order by (salary*12 + ifnull(bonus,0)) desc;
    

    – 查询每个部门的平均薪水、最高薪水

     select depno, avg(salary),max(salary) from staff group by depno;
    

    – 查询每部门中每个岗位的最高薪水

    select depno, job, max(salary) from staff group by depno, job;