4.SELECT语句

SELECT

SELECT
[ALL | DISTINCT | DISTiNCTROW ] [SQL_CACHE | SQL_NO_CACHE ] select_expr 
[,select_expr ...] [FROM table_references [WHERE where_condition] 
[GROUP BY{col_name | expr | position } [ ASIC | DESC ],... [WITH ROLLUP]] 
[HAVING where_condition] [ORDER BY { col_name | expr | position } [ASC | DESC],...] 
[ LIMIT {[ offset, ] row_count | row_count OFFSET offset }] FOR UPDATE | LOCK IN SHARE MODE]

字段显示可以使用别名：
  col1 AS alias1, col2 AS alias2, ...

WHERE子句：指明过滤条件以实现“选择”的功能：
  过滤条件：布尔型表达式
  算术操作符：+，-，*，/，%
  比较操作符：=，<=>（相等或都为空），<>，!=（非标准SQL）,>，>=，<，<= 
  BETWEEN min_num AND max_num
  IN (element1,element2,...)
  IS NULL
  IS NOT NULL
DISTINCT去重复列
  SELECT DISTINCT gender FROM students;
LIKE:
  %    任意长度的任意字符
  _    任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
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：读锁，共享锁，同时多个读  

eg

DESC students
INSERT INTO students VALUES (1,'tom','m'),(1,'alice','f')
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id <3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4;
SELECT * FROM students WHERE BETWEEN 2 AND 4;
SELECT * FROM students WHERE name LIKE 't%';
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students;

多表查询

交叉连接：笛卡尔乘积
内连接：
  等值连接：让表之间的字段以“等值”建立连接关系；
  不等值连接
  自然连接：去掉重复列的等值连接
  自连接
外连接：
  左外连接：
    FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
  右外连接：
    FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2=col
子查询：
  在查询语句嵌套着查询语句，性能较差
  基于某语句的查询结果再次进行的查询
用在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;
联合查询：UNION
  SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;

流程图

select语句.jpg