SQL第1篇 DQL基础查询语句

PS:绝不眼高手低，从基础做起。咱也不知道sql语句写在哪里，就直接用代码块呈现了，这样看起来方便一些。
（1）先来讲一些注意事项:
选中Fn+F12格式化。选中Fn+F9执行。";"分割语句。双击字段快捷输入，这样输入时字段是着重号引起来。着重号一般用来区分是字段还是关键字，是关键字的时候引起来，不是的时候可不用（手打时）。
（2）表中字段含义：

 

表中字段含义.png

进阶一 查询

语法：select 查询列表 from 表名;
特点：
1、可以查：字段、常量值、表达式、函数
2、查询的结果是一个虚拟表格

 

#选用操作的库
USE `myemployees`;
#显示表的结构
DESC `departments`;

#1.查询字段
#连接字符串用concat
SELECT 
  CONCAT(`last_name`, '-',`first_name`) AS 姓名 
FROM
  `employees` ;
#去重 在一次执行中只能去一个字段的重
SELECT DISTINCT `department_id` FROM `employees`;

#有null值返回指定值，不是null值显示原值
SELECT `last_name`  姓,IFNULL(`commission_pct`,0) AS 奖金率
FROM `employees` 

#2.查询常量值

SELECT '粉红狐狸'; #字符型和日期型要用单引号引起来，数值型不需要
SELECT '100';
SELECT 100;

#3.查询表达式

SELECT 100%98 AS 取模;  
SELECT 100%98 余数;#取别名可以用空格
SELECT 100%98 AS "out put";#别名为关键字建议双引号引起来，单引号也可以
#4.查询函数

SELECT VERSION();

#5.+在Mysql中只有一个功能：运算符

SELECT 99+1; #两个都是数值
SELECT '98'+1; #能转换成数字的字符，试图转换成数字，再运算
SELECT '粉红狐狸'+1;#不能转换成数字的字符，转换成0，再运算
SELECT  NULL+1;#只要有null结果就是null

进阶二 条件查询

语法：select 查询列表 from 表 where 筛选条件
执行过程：先查表，再看条件，再查询
条件分类：
1、条件表达式：< > != <> (又大于又小于就是不等于,推荐写这个）<= >=
2、逻辑运算符（用于连接条件表达式）：&& || ！，推荐写 and or not
3、模糊查询：
like ：与通配符搭配查询字符型或者数值型都可以，%任意多个字符，说不清是什么字符，包含0个字符 _只匹配一个字符 。 (如果字段里有null值，like+通配符是无法匹配的)
between and ：取值区间[<= ,>=]
in|not in : 判断字段是否在in的列表里，不支持通配符查询，因为in等价于==
is null | is not null ：只能判断null值

 

#案例1：查询工资在1万到2万之间的员工姓名，工资，奖金
SELECT `last_name` ,`salary` ,`salary`*12*IFNULL(`commission_pct`,0) AS 奖金
FROM `employees`
WHERE `salary` >=10000 AND `salary`<=20000;

SELECT `last_name` ,`salary` ,`salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM `employees`
WHERE `salary` BETWEEN 10000 AND 20000; #between and 包含端点值，临界值顺序不能颠倒

#案例2：查询员工姓名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';

#案例3：查询员工姓名中第二个字符为"_"的员工信息
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';#escape 表示后面的字符是转义字符，$可任意

#案例4：查询工种编号为AD_PRES，AD_VP,IT_PROG的员工姓名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id  IN ('AD_PRES','AD_VP','IT_PROG');

#查询工种编号不为‘IT’的员工信息
SELECT * FROM employees WHERE job_id <> 'IT' ;#不能用is not 

#案例5：查询有奖金的员工姓名和奖金率
SELECT last_name ,`commission_pct` FROM employees WHERE `commission_pct`IS NOT NULL;#= 或者<> 不能去判断null值

#案例6：查询没有奖金的员工姓名和奖金率 安全等于<=>  等价于==
SELECT last_name ,`commission_pct` FROM employees WHERE `commission_pct` <=> NULL;
SELECT last_name ,salary FROM employees WHERE salary <=> 12000;
/* 
is null:只能判断是否等于null值，可读性高
   <=> :既能判断是否是null值，也能判断普通值，但可读性差，建议用is null     
*/

进阶三 排序 order by 放在最后面

语法：select 查询列表 from 表 where 条件 order by 排序列表 【asc|desc】
执行顺序：从哪个表筛选什么条件，再去查找，最后排序
特点：默认升序asc

 

#案例1.查询部门编号>90,按照入职时间先后顺序进行排序
SELECT * FROM employees WHERE `department_id`>90 ORDER BY `hiredate`;#时间早的值较小

#案例2.查询员工信息，按照入职时间先后顺序、月薪和姓名长度降序、进行排序【怎么只能有两个排序字段？第三个不管用了呢】
SELECT `last_name`,`hiredate`,`salary`,LENGTH(last_name) 姓名长度 
FROM employees  
ORDER BY `hiredate` ASC ,`salary` DESC,姓名长度  DESC;

进阶四 函数

函数分为单行函数，聚合函数（统计函数）
1、单行函数：给一个值返回一个值
2、统计函数：给多个值返回一个值（下一篇更新）

一、单行函数

1.1字符型函数

 

#length ：获取参数值的字节个数（不是字符个数）在utf8中汉字占3个字符，在jbk中汉子占两个字符；英文字母均占一个字符
SHOW VARIABLES LIKE '%char%' #看字符集
SELECT LENGTH('粉红狐狸');

#concat ： 拼接字符串
SELECT CONCAT('粉红狐狸','_','最好看');

#upper、lower ： 全部变成大写、小写
SELECT  CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;

#substr | substring : 截取字符串 索引从1开始
#一个数值参数时，返回从开始索引的位置到最后
SELECT SUBSTR('乘风破浪的小姐姐',6);#返回“小姐姐”
#两个数值参数时，返回从开始索引的位置，指定字符长度的子串
SELECT SUBSTR('乘风破浪的小姐姐',1,4);#返回“乘风破浪”
#案例：首字母大写，其余小写，下划线拼接
SELECT  CONCAT(UPPER(SUBSTR(last_name,1,1)),
           LOWER(SUBSTR(last_name,2)),
           '_',
           LOWER(first_name)
           ) FROM employees;

#instr ：返回在字符串中第一次出现的索引，找不到返回0
SELECT INSTR('红色高跟鞋','高跟鞋') #返回3

#trim去掉前后空格,或者去掉前后的指定字符
SELECT LENGTH(TRIM('   杨幂  ')); #2*3=6
SELECT TRIM('a' FROM 'aaaaaaaa杨aaaa幂aaaa')#若为aa 则以aa为单位进行匹配

#lpad left_pad | rpad right_pad : 参数代表总长度,原有数值的个数超过指定长度从右边截断
SELECT LPAD('杨幂',10,'*') AS result

#replace : 凡是能够匹配上的都替换掉
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') result
 

1.2 数学函数

 

#round ： 四舍五入
SELECT ROUND(-1.55); #先计算正数的四舍五入值，再加符号
SELECT ROUND(-1.567,2); #四舍五入，小数点保留2位

#ceil ：向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.01);

#floor : 返回<=该参数的最大整数
SELECT FLOOR(-1.01);

#truncate :截断，第二个参数表示小数点后保留的位数
SELECT TRUNCATE(1.79999,1)

#mod : 取余 符号看被除数 10%3 看10
/* 
a-a/b*b java中除法左右两边都是整数取整
mod(-10,-3) : -10 - (-10)/(-3)*(-3)
mod(10,-3) : 10 - (10)/(-3)*(-3)
*/
SELECT MOD(-10,-3)
SELECT MOD(10,-3)
SELECT -10/-3

1.3 日期函数

日期的符号.png

 

SELECT NOW(); #日期+时间
SELECT CURDATE(); #日期
SELECT CURTIME(); #时间
#可以获取年、月、日、时、分、秒
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW()); #英文的月份

#将字符串转换成日期 
SELECT STR_TO_DATE('1996-5-15','%Y-%c-%d')
#在一定的业务场景下使用解析用户返回给的日期字符串
SELECT * FROM `employees` WHERE `hiredate`=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年-%m月-%d日') result;

1.4其他函数

 

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

1.5 流程控制函数

 

#if函数
SELECT 
  last_name,
  commission_pct,
  IF(
    commission_pct,
    '有奖金 嘻嘻',
    '没奖金 呵呵'
  ) AS 备注 
FROM
  employees 
  
#case使用一  case后面加字段，功能相当于等于的判断，when后面加的是常量
  /*
case 需要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n（默认）
end

搭配select时 case-end充当的是表达式，then后面显示的是值
*/

/*案例：查询员工的工资，要求：
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT 
  last_name,
  department_id,
  salary,
  CASE department_id
    WHEN  30 THEN salary * 1.1 
    WHEN  40 THEN salary * 1.2 
    WHEN  50  THEN salary * 1.3 
    ELSE salary 
   END AS 工资 
 FROM
   employees 
    
#case使用二  case后面不加东西，判断的条件在when后面写，相当于是区间的判断
/*
case 
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n（默认）
end

搭配select时 case-end充当的是表达式，then后面显示的是值
*/

/*案例：查询员工的工资，要求：
工资>20000,显示级别A
工资>15000,显示级别B
工资>10000,显示级别C
否则，显示级别D
*/

SELECT 
  last_name,
  department_id,
  salary 原工资,
  CASE 
    WHEN  salary>20000 THEN 'A' 
    WHEN  salary>15000 THEN 'B'
    WHEN  salary>10000 THEN 'C' 
    ELSE 'D'
   END AS 工资级别 
 FROM
   employees 
   

总结 单行函数

字符函数

length , concat , substr , instr , trim ,
upper , lower , lpad , rpad , replace

数学函数

round , ceil , floor, truncate, mod

日期函数

now , curdate, curtime ,
year , month, monthname, day, hour, minute, second
str_to_date , date_format

其他函数

version , database, user

控制函数

if , case

二、分组函数 :用来统计

分类：sum avg min max count
特点：
1.sum avg ：只处理数值型 min max count：可以处理任何类型
2.以上函数都忽略null值。
3.可以和dinstinct搭配 去重
4.count 的详细用法
count（*），count(1)表的统计行数，一般使用count(*)
5.和分组函数一同查询的字段，要求是出现在group by 后的字段
因为分组函数只返回一个值，加上其他字段，返回的数据表不规范
select sum(salary),last_name from 表 这样是不可以的，sum只返回一个值，但last_name有多个值

 

SELECT SUM(DISTINCT salary), SUM(salary) FROM employees
SELECT DATEDIFF(NOW(),'1996-06-30') 地球居住天数;