MYSQL-基础查询

 基础查询

         条件查询

                逻辑运算

                        and         与
                        or            或
                        not          非

SELECT * FROM footballer WHERE place='前锋';

-- and 与
SELECT * FROM footballer WHERE place='前锋'AND height>=200;

-- or或
SELECT * FROM footballer WHERE place='前锋'OR height>=200;

-- not 非
SELECT * FROM footballer WHERE NOT place='前锋'

-- 比较
-- =, != 或<>, >, <, >=, <=

SELECT * FROM footballer WHERE place<>'前锋'

-- 比较
-- =, != 或<>, >, <, >=, <=

SELECT * FROM footballer WHERE place<>'前锋'

        模糊查询

LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值
或数值型.
通配符： % 任意多个字符
between and 两者之间,包含临界值；
in 判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）

-- 模糊查询
-- SELECT * FROM 表名 WHERE 字段名 LIKE 对应值（子串）
SELECT * FROM footballer WHERE birthday LIKE '198%'
SELECT * FROM footballer WHERE place IS NOT NULL 

        排序

-- 查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
-- asc代表的是升序，desc代表的是降序，如果不写，默认是升序
-- order by子句中可以支持单个字段、多个字段

SELECT * FROM footballer WHERE weight>80  ORDER BY height ASC,weight  DESC

                数量限制

SELECT * FROM footballer LIMIT 0,4;
SELECT * FROM footballer LIMIT 4,4;
SELECT * FROM footballer LIMIT 8,4;

        分组查询

                语法：

                        select 分组函数，列（要求出现在group by的后面）
                        from 表
                        [where 筛选条件]
                        group by 分组的列表
                        [having 分组后的筛选]
                        [order by 子句] 

                注意：

                        查询列表比较特殊，要求是分组函数和group by后出现的字段
                        分组查询中的筛选条件分为两类:
                                                数据源                         源位置                                 关键字
                        分组前筛选      原始表                         group by子句的前面             where
                        分组后筛选      分组后的结果集           group by的后面                    having

SELECT COUNT(*),place FROM footballer 
WHERE place IS NOT NULL 
GROUP BY place