三、单表查询

一、基本查询

1. 基本

-- mysql中，不区分大小写，为了方便调试，一般把关键字使用大写，库名，表名，字段名使用小写
SELECT * FROM cnip;
SELECT name,age FROM cnip;
SELECT 100;  SELECT 'mick';   // 常量    
SELECT (2+2); // 表达式
SELECT VERSION(); // 函数
-- 全限定表名，列名
SELECT * FROM cnip_db.cnip WHERE cnip.name = '李明';

2. 别名

# 优点一： 可改变字段显示名称
# 优点二： 多表联查，重复字段进行区分
SELECT VERSION() AS version;  
SELECT VERSION() version;  
SELECT VERSION() 'my version';  // 别名中存在空格，用‘’

3. 拼接符

# 拼接时候不能用 + ， sql中的+ 只能当作运算符
SELECT 100 + 99;
SELECT '100' + 99;  //尝试将字符转换为数字并做运算，如果转换不成功则将字符视为0
SELECT 'dfa' + 99;
SELECT null + 10;   // 只要其中一个为null，则结果为null

SELECT CONCAT('姓名:',first_name,'---',last_name) AS name FROM president;

4. 去重

SELECT DISTINCT name FROM phone;   // 获取不重复的某个字段的value
-- 去重：DISTINCT关键字应用于后面所有列，即多行记录中只有name和age都重复了才会进行去重
SELECT DISTINCT name,age FROM cnip;

5. 分页查询

• 语法最后，执行顺序最后

-- 查前五条数据
SELECT * FROM cnip LIMIT 5;
-- 跳过前面5个，检索2个
SELECT * FROM cnip LIMIT 5,2;
-- 跳过前面5个，检索2个
SELECT * FROM cnip LIMIT 2 OFFSET 5;

二、条件查询

1. 条件/逻辑

# 后面可以跟多个条件检索
# 1. OR     AND      NOT
# 2. =   !=  >  <    >=    >=
SELECT * FROM cnip WHERE name = 'lucy' OR age = 20;
SELECT * FROM phone WHERE NOT age >= 9;

2. 模糊查询

# 不能匹配null值
# % 代表0-n个字符，  英文匹配时候不区分大小写；  -只匹配一个字符；
# 匹配时： 字符的一定要用‘’,     数值类型，可用可不用''
SELECT * FROM cnip WHERE content LIKE '%zte%';
SELECT * FROM cnip WHERE content LIKE 'z%e';
SELECT * FROM cnip WHERE content LIKE '_te';
SELECT * FROM cnip WHERE content LIKE '__te';
SELECT * FROM phone WHERE  name like '%\_%';   // 转义符号   \
SELECT * FROM phone WHERE name like '%^_%' ESCAPE '^';   // 自定义转义符号为 ^

3. 范围查询

# between and             not     between       and 
SELECT * FROM phone WHERE age BETWEEN 20 and 100; //  包含临界值  有前后顺序
SELECT * FROM phone WHERE age NOT BETWEEN 32 AND 100;

# in :  命中一个即可
SELECT * FROM phone WHERE name in ('tony','jack','lucy');

4. null值比较

// IS NULL         IS NOT NULL
SELECT * FROM phone WHERE name IS NULL;
SELECT * FROM phone WHERE name IS NOT NULL;


//  <=>: mysql提供的特殊的，用于对null值比比较的
SELECT * FROM phone WHERE name <=> NULL; 
SELECT * FROM phone WHERE NOT name <=> NULL;

5. 排序查询

# 默认升序，即ASC ，DESC为降序排列
# 多个字段排序： 先按第一个字段排序，第一个字段相同再按第二个排序
# order by 后也支持别名
SELECT * FROM president ORDER BY first_name DESC, last_name DESC;

# 区分null值的排序： 先按照null和非null排序，再按照字段排序
# IF(ccondtion,0,1): true则为0，false 则为1, 三元表达式
SELECT * FROM president ORDER BY IF(address IS NULL,0,1) DESC, address DESC;

三 简单函数

1. 字符函数

SELECT LENGTH(name) from phone;                 // 字符长度
SELECT CONCAT(name,'====',brand) from phone;    // 字符拼接
SELECT UPPER(name) from phone;                  // 大写转换
SELECT LOWER(name) from phone;                  // 小写转换
SELECT CONCAT( LOWER(name), '===',UPPER(brand)) from phone; // 函数嵌套

SELECT TRIM('   LUCY     ') as result;   // TRIM()   LTRIM().  RTRIM()

2. 数学函数

SELECT ROUND(1.5) AS result;       // 四舍五入
SELECT CEIL(1.5) AS result;       // 向上取整。 >= 该参数的最小整数
SELECT FLOOR(1.5) AS result;       // 向下取整
SELECT TRUNCATE(1.345,2);         // 取小数点后几位

3. 流程控制函数

// 条件中可以用 = <= >=  is null等判断
SELECT IF( 1=1,'positive','negative' ) as result;    //  三元运算符

// 1. case 函数 ： 类似java的switch， 适用于等值判断
// 是用在from之前的，对于某些字段进行计算
SELECT *,
       CASE brand
           WHEN 'Apple' THEN price * 0.9
           WHEN 'Huawei' THEN price * 0.8
           WHEN 'XiaoMi' THEN price * 0.7
           WHEN 'Oppo' THEN price * 0.6
           ELSE price
           END AS discount_price
FROM phone;

// 2. case 函数 ： 类似java的多重if，  适用于区间比较
// case 后没有字段 ， 
SELECT *,
       CASE
           WHEN price >= 5000 THEN 'LEVEL-A'
           WHEN price >= 4000 THEN 'LEVEL-B'
           WHEN price >= 3000 THEN 'LEVEL-C'
           WHEN price >= 2000 THEN 'LEVEL-D'
           WHEN price >= 1000 THEN 'LEVEL-E'
           ELSE 'LEVEL-LOW'
           END AS product_level
FROM phone;

四、聚合函数

1. 分组函数

COUNT(*)    COUNT(field)    COUNT(1)  的区别

1. COUNT(*)， COUNT(1)： 基本没区别，统计不为null的行数目， 即只要一行中一个字段不为null，
                         总数就会计算该行
2. COUNT(field):          如果某个字段不为null，就统计

性能： 
INNODB:  COUNT(*) 和 COUNT(1)的效率差不多，  比COUNT(filed)效率高
MYISAM:  COUNT(*) 性能最高

//  统计函数都会忽略null值：      字段为NULL时候，统计时候忽略该行（分子和分母都忽略该行）
//  SUM()  AVG():             参数必须是数值类型                
//  MAX()  MIN()，COUNT():     参数为任何类型
SELECT MAX(age) from phone;// 获取某个字段的最大值
MIN()  MAX()  AVG()   SUM()     COUNT()

// 和distinct搭配
SELECT SUM(DISTINCT(brand)) FROM phone;

// 和统计函数一同查询的字段，必须是 group by后的字段
SELECT COUNT(brand)，brand FROM phone GROUP BY brand;

2. 分组查询

2.1 分组前筛选

// 1. GROUP BY 将表中数据分为若干组
SELECT COUNT(brand) as brand_num FROM phone GROUP BY brand;

// 2. 查询列表字段必须是分组函数 或者 group by后面的字段
SELECT MAX(price) as brand_max_price , brand FROM phone GROUP BY brand;

// 3.  COUNT()时候，最后一行可以通过这种方式来进行汇总
SELECT  count(*)  FROM phone GROUP BY brand WITH ROLLUP;

2.2 分组后筛选

// group by后的结果集再筛选, 一般跟在 group by字段之后
// 其实就是分组后筛选， 放在group by之后
SELECT MAX(price) AS max_price , brand FROM phone GROUP BY brand
HAVING max_price >2000;

// 分组前筛选: where, 放在group by 之前
SELECT MAX(price) as brand_max_price , brand FROM phone WHERE id < 10 
GROUP BY brand;

// 能用分组前筛选的，优先用分组前where

2.3 多条件分组

// 按照多个条件分组，只有后面所有的条件都相同时候，才会归为一组
SELECT COUNT(*) AS nums, country,brand FROM phone GROUP BY country, brand
HAVING nums>1;

五、子查询

• 子查询/内查询： 出现在其他语句内部的select查询
• 先执行子查询，再执行父查询

# 子查询的位置
select 后面         
from 后面      
where后面        
exists后面

# 结果集
标量子查询：    单行单列
列子查询：      一列多行
行子查询：      一行多列
表子查询：      多行多列

# 1. where 后面的： 标量子查询     后面可以跟多个子查询
SELECT *
FROM phone
where price > (SELECT price FROM phone where brand = 'Oppo') ; 

# 2. where 后面的： 列子查询.       一般可以通过其他方式替代
SELECT *
FROM phone
WHERE brand in (SELECT brand FROM phone where country = 'us');

# 3. where 后面的： 行子查询

# 4. 表子查询
SELECT a.name FROM (SELECT * FROM phone) a;

# 5. EXISTS： 表中存在数据结果为1， 不存在为0
SELECT EXISTS(SELECT * FROM phone WHERE id >10);