MySQL查询语句

本文参考: Mysql菜鸟教程 C语言中文网教程 李玉婷老师视频教程

1.基本查询语句

• 语法: SELECT [字段名] FROM [表名];
• 示例
  • SELECT Host,User FROM user;
  • SELECT * FROM user; (查询user表中所有字段)
  • SELECT id AS "序号" FROM user;(在查询结果中将id重命名为"序号")

2.条件查询语句

• 语法: SELECT [字段名] FROM [表名] WHERE [筛选条件];
• 含义:通过设置筛选条件,查询出字段中符合条件的值; 筛选条件主要分为两类, 条件表达式和模糊匹配条件;通过逻辑运算符可将几条简单筛选条件复合为新的筛选条件
  • 条件表达式的关键词有: > < = != <> >= <=
  • 模糊匹配的关键词有: like between and in is null
  • 常用的逻辑运算符: and或&&, or或||, not或!;
• 示例
  • SELECT id,name FROM user WHERE id>3;
  • SELECT * FROM user WHERE id>3 AND name != "lena";
  • SELECT name FROM user WHERE id BETWEEN 1 AND 10;
  • SELECT * FROM user WHERE name IN('xiaoming','John','Tom');
  • SELECT * FROM user WHERE phone_num IS NOT NULL OR email IS NOT NULL;
  • SELECT id FROM user WHERE name LIKE _e%;(在user表中查询name字段第二个字母为'e'的id值)

3.分组查询

• 语法: SELECT [查询字段] FROM [表名] WHERE [筛选条件] GROUP BY[作为分组依据的字段];
• 含义:将具有某种相同性质的数据作为一个整体查询, 可查看其平均值, 总和等整体性质
  • 分组可以按单个字段也可以按多个字段
  • 分组条件最好放在WHERE筛选条件后
• 示例
  • SELECT COUNT(*) FROM employees WHERE department_id=90;(无分组条件)
  • SELECT AVG(salary),job_id FROM employees GROUP BY job_id;
  • SELECT COUNT(*),department_id FROM employees GROUP BY department_id;
  • SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>5;(对上述查询结果进行二次筛选)

4.排序查询

• 语法:SELECT [查询字段] FROM [表名] ORDER BY [排序字段][排序方式];
• 含义:将查询结构按指定方式排序
  • DESC (降序) ASC (升序,默认排序)
• 示例
  • SELECT * FROM employees ORDER BY salary; (默认升序)
  • SELECT * FROM employees ORDER BY salary DESC;
  • SELECT *,salary*12*(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;(按表达式排序)

5.连接查询

• 语法: `SELECT [查询字段] FROM [表名(多个)] WHERE [筛选条件];
• 含义: 连接查询一般是多表查询, 多表连接时只是简单的取笛卡尔积, 故需要设置条件对查询结果进行筛选
  • 由于涉及到多个表, 故连接查询中需要指出字段所在的表,以避免名字冲突
• 示例
  • SELECT name,boyName FROM boys,beauty WHERE beauty.boyfriend_id= boys.id;
  • SELECT name,boyName FROM boys a,beauty b WHERE a.boyfriend_id= b.id; (取别名)

6.子查询

• 含义: 出现在其他语句中的select语句，称为子查询或内查询

• 示例

  #1.查询工资比Abel高的员工信息
  SELECT *
  FROM employees
  WHERE salary>(
      SELECT salary
      FROM employees
      WHERE last_name = 'Abel'
  );
  
  #2.查询查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
  SELECT last_name,job_id,salary
  FROM employees
  WHERE job_id = (
      SELECT job_id
      FROM employees
      WHERE employee_id = 141 #①
  ) AND salary>(
      SELECT salary
      FROM employees
      WHERE employee_id = 143 #②
  );
  
  #3.查询员工最低工资低于结果①的部门id,以及这些部门的员工最低工资
  SELECT MIN(salary),department_id
  FROM employees
  GROUP BY department_id
  HAVING MIN(salary)>(
      SELECT  MIN(salary) #①查询部门id为50的员工最低工资
      FROM employees
      WHERE department_id = 50
  );
  

7.分页查询 (不常用)

• 语法: SELECT [查询字段] FROM [表名] limit [offset,]size;
  • offset为起始值,默认为0, size为展示结果条数
• 含义:截取查询结果中的一部分展示
• 示例
  • SELECT * FROM employees LIMIT 0,5; #展示查询结果中前5条
  • SELECT * FROM employees LIMIT 5; #同上
  • SELECT * FROM employees LIMIT 10,15; #展示查询结果中11-25条

8.联合查询 (不常用)

• 语法: 查询语句1 union 查询语句2 union ...

• 含义: 将几个查询语句的结果整合到一起

• 示例

  SELECT * FROM employees  WHERE email LIKE '%a%'
  UNION
  SELECT * FROM employees  WHERE department_id>90;
  
  #等价于 ???
  
  SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;