MySql中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, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]

先来看看一些简单的搭配查询：

//简单的全显示查询，AS取别名
SELECT sno AS 编号, sname AS 姓名, age AS 年龄, saddress AS 地址 FROM sstud;
SELECT sname AS 姓名, age AS 年龄, saddress AS 地址 FROM sstud;


//SELECT 复杂查询
//WHERE子句 --BETWEEN子句 和 IN子句
SELECT * FROM sstud WHERE age>=25;
SELECT * FROM sstud WHERE age>=23 AND age<=25 ;
SELECT * FROM sstud WHERE age BETWEEN 23 AND 25; //[23,25]
SELECT * FROM sstud WHERE age=23 OR age=25;
SELECT * FROM sstud WHERE age IN(23,25,32);//age=23/25/32
SELECT * FROM sstud WHERE age NOT IN(23,25,32);//age!=23/25/32

//模糊查询LIKE '%'匹配所有 '_'匹配单字符 ---必须和LIKE共同使用
SELECT * FROM sstud WHERE sname LIKE '张%';
SELECT * FROM sstud WHERE sname LIKE '张_';
SELECT * FROM sstud WHERE sname LIKE '张__';
SELECT * FROM sstud WHERE sname LIKE '%a%';
SELECT * FROM sstud WHERE sname LIKE '%a%' AND age>25;

//判断值时，NULL是不能用“=”号判断，而应该用IS
UPDATE sstud SET age=20 WHERE age=NULL;
UPDATE sstud SET age=20 WHERE age IS NULL;
SELECT * FROM sstud WHERE saddress=''; //空字符串，不是NULL


知识点：
SELECT 字段 FROM 表名 WHERE 条件　AND 条件　OR 条件　


//创建视图 CREATE VIEW 视图名 AS SELECT子句
CREATE VIEW aview AS SELECT * FROM sstud WHERE age>20;
SELECT sname,age FROM aview WHERE saddress='湖南益阳';

//聚合函数
SELECT COUNT(*) FROM sstud; //一般要专门给一个别名
SELECT COUNT(*) AS sumNum FROM sstud;//统计总行数
SELECT COUNT(1) AS sumNum FROM sstud;
SELECT COUNT(saddress) AS sumNum FROM sstud;//统计saddress这一列中，内容为非NULL的行数

SELECT AVG(age) AS averageAge FROM sstud;//取平均值
SELECT ROUND(AVG(age)) AS averageAge2 FROM sstud;//四舍五入
SELECT SUM(age) AS sumAge FROM sstud;//和
SELECT MAX(age) AS maxAge FROM sstud;//最大值

//选择年龄最小的那个人的名字和年龄
SELECT sname,age FROM sstud WHERE age = ( SELECT MIN(age) FROM sstud ) ;
SELECT sname,age FROM sstud WHERE age IN ( SELECT MIN(age) FROM sstud ) ;

//DISTINCT --数据相同时只显示第一条(去除重复数据)
SELECT DISTINCT sname,age FROM sstud WHERE age = ( SELECT MIN(age) FROM sstud ) ;
SELECT DISTINCT saddress FROM sstud;

//ORDER BY 字段名 ASC/DESC,...
SELECT * FROM sstud ORDER BY age ASC;//按年龄升序排
SELECT age,sname FROM sstud ORDER BY age DESC;//降序

//EXISTS函数
//一条有bug的SQL语句,EXISTS为true，则全部查询，为false,则全部不查询
SELECT * FROM sstud WHERE EXISTS( SELECT * FROM sstud WHERE age=38 );

//分组 GROUP BY
SELECT saddress,AVG(age)AS 平均年龄 FROM sstud GROUP BY saddress;
SELECT saddress, SUM(age)AS 年龄总和 FROM sstud GROUP BY saddress;
//固定搭配1: SELECT ... FROM ... WHERE ... ORDER BY ... ----ORDER BY子句要放在最后
//固定搭配2: SELECT ... FROM ... GROUP BY ... HAVING .... ----GROUP BY子句中的条件用的是HAVING (不能用WHERE)
SELECT saddress,AVG(age)AS 平均年龄 FROM sstud GROUP BY saddress HAVING AVG(age)>22;
SELECT saddress,AVG(age) AS a FROM sstud GROUP BY saddress HAVING a>22; //留意一下别名的用法

//字符串处理函数
SELECT * FROM sstud WHERE TRIM(sname)='abc'; //TRIM(str) LTRIM(str) RTRIM(str) 去除空格
SELECT * FROM sstud WHERE LEFT(saddress,2)='湖南'; //LEFT(str,n) RIGHT(str,n) SUBSTRING(str,BEGIN,END)//[BEGIN,END]子串
SELECT REVERSE(sname) AS 反序 , saddress FROM sstud;

/* Lower(str) Upper(str) */
SELECT UPPER(sname) AS 姓名 FROM sstud;
/*concat(str1,str2,...) INSTR(str,s) */
SELECT CONCAT(sno,sname,saddress) AS conca FROM sstud;
SELECT sname,INSTR(sname,'a') AS ins FROM sstud;

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, ...

查询表范围：

[FROM table_references

查询条件：

1：where查询：

    [WHERE where_definition]

2：分组查询：

[GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

排序约束:

[ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

在字段名的使用，可以用函数进一步处理