SQL语句之：DQL

DQL语句

SELECT

SELECT

查询操作

• 语法：

  SELECT
      [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
      select_expr [, select_expr ...]
      [FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position}
        [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name'
          [CHARACTER SET charset_name]
          export_options
        | INTO DUMPFILE 'file_name'
        | INTO var_name [, var_name]]
      [FOR UPDATE | LOCK IN SHARE MODE]]  
  

• 字段显示可以使用别名：
  col1 AS alias1，col2 AS alias2，...
• WHERE子句：指明过滤条件以实现“选择”的功能：
  过滤条件：布尔型表达式
  算术操作符：+，-，*，/，%
  比较操作符：=，!=，<>，<，<=，>，>=
BETWEEN min_num AND max_num
IN (element1,element2,...)
IS NULL
IS NOT NULL
• LIKE：模糊搜索查询
   %：任意长度的任意字符
   _：任意单个字符
• PLIKE：正则表达式，索引失效，不建议使用
• REGEXP：匹配字符串可用正则表达式书写模式，同上
• 逻辑操作符：
  NOT
AND
OR
XOR
• GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
  avg()，max()，min()，count()，sum()
HAVING：对分组聚合运算后的结果指定过滤条件
• ORDER BY：根据指定的字段对查询结果进行排序
  升序：ASC
  降序：DESC
• LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
• 对查询结果中的数据请求施加“锁”
  FOR UPDATE：写锁，独占或排它锁，只有一个读和写
  LOCK IN SHARE MODE：读锁，共享锁，同时多个读

示例：

• SELECT * FROM students;
• SELECT name,age,gender FROM students;
• SELECT name,age,gender FROM students WHERE id=2;
• SELECT name,age,gender FROM students WHERE id>=2 AND id <=5;
• SELECT name,age,gender FROM students WHERE id BETWEEN 2 AND 5;
• SELECT name,age,gender FROM students WHERE gender IN ('m','f');
• SELECT name,age,gender FROM students WHERE gender IS NULL;
• SELECT name,age,gender FROM students WHERE gender IS NOT NULL;
• SELECT gender,avg(score) FROM students GROUP BY gender;
• SELECT class,gender,avg(score) FROM students GROUP BY class,gender;

多表查询

交叉连接：

• 笛卡尔乘积：CROSS JOIN
  SELECT * FROM students CROSS JOIN teachers;

内连接：

等值连接（让表之间的字段以“等值”建立连接关系）；不等值连接；自然连接（去掉重复列的等值连接）；自连接

• 取交集：INNER JOIN
  SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s INNER JOIN teachers AS t ON s.teacherid = t.tid;

外连接：

• 左外连接：
  FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col
  SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s LEFT JOIN teachers AS t ON s.teacherid = t.tid;
• 右外连接：
  FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col
  SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s RIGHT JOIN teachers AS t ON s.teacherid = t.tid;
• 取A表和B表之间，仅存在于A表之内的数据
  SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s LEFT JOIN teachers AS t ON s.tid = t.id WHERE t.name IS NULL;
• 取A表和B表之间，仅存在于B表之内的数据
  SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s RIGHT JOIN teachers AS t ON s.tid = t.id WHERE s.name IS NULL;

联合查询：

• 完全的外连接：
  在MySQL中需要借助union来实现，左外连接 union 右外连接：
  SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s LEFT JOIN teachers AS t ON s.tid = t.id union SELECT s.name AS students_name,t.name AS teachers_name FROM students AS s RIGHT JOIN teachers AS t ON s.tid = t.id;

子查询：

在查询语句中嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询

用在WHERE子句中的子查询

• 用于比较表达式中的子查询；子查询仅能返回单个值
  SELECT Name,Age FROM students WHERE Age > (SELECT avg(Age) FROM students);
• 用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
  SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
• 用于EXISTS

用于FROM子句中的子查询

• 使用格式：
  SELECT tb_alias.col1,...FROM (SELECT clause) AS tb_alias WHERE Clause;
  示例：
  SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage > 30;