MySql基础1 --- 基础查询、条件查询、排序查询、常见函数

该文章所用的数据库文件可私发。

进阶1：基础查询

语法

select 查询列表 from 表名；

特点

1、查询列表可以是字段、常量、表达式、函数，也可以是多个
2、查询结果是一个虚拟表

常见用法

查询单个字段 

SELECT 字段名 FROM 表名；
如：SELECT last_name FROM employees;

 查询多个字段 

SELECT 字段名,字段名,... FROM 表名；
如：SELECT last_name,salary,email FROM employees;

查询所有字段 

SELECT * FROM 表名；

#使用*的话，则查询结果中的字段顺序与数据库的一样。
SELECT * FROM employees;

查询常量值 

 SELECT 常量值;   

#注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
SELECT 100;
SELECT 'john';

查询表达式

SELECT 100%98;

查询函数   

SELECT 函数名(实参列表);  

#mysql中的函数调用方法必须用select，且必须有返回值
如：SELECT VERSION();

起别名

以SELECT 100%98; 为例，查询结果的字段名为100%98，不直观。

特点：
1、便于理解
2、如果要查询的字段有重名的情况，使用别名可以区分开来（后面会用到）

#方式一：使用as（提高可读性）
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二：使用空格（书写简单）
SELECT last_name 姓,first_name 名 FROM employees;

#案例：查询salary,显示结果为 out put。如果别名中有特殊符号，比如空格 、 #，建议加上双引号或单引号
SELECT salary AS "out put" FROM employees;

去重 

#案例：查询员工表中涉及到的所有的部门编号 (在字段名前加上DISTINCT即可)
SELECT  DISTINCT department_id FROM employees;

+ (加号)的作用

java中的 + 号：
1、运算符，两个操作数都为数值型
2、连接符，只要有一个操作数为字符串

mysql中的 + 号：仅仅只有一个功能：运算符（做加法运算）
select 100+90;       两个操作数都为数值型，则做加法运算
select '123'+90;     其中一方为字符型，试图将字符型数值转换成数值型
                     如果转换成功，则继续做加法运算
select 'john'+90;    如果转换失败，则将字符型数值转换成0

select null+10;      只要其中一方为null，则结果肯定为null

concat函数  

功能：拼接字符 
select concat(字符1,字符2,字符3,...)

#在mysql里做拼接，不能用+，而是用concat()函数（后面会详细介绍常见函数）
错误写法：SELECT last_name+first_name AS 姓名 FROM employees;   
正确写法：SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

ifnull函数 

功能：判断某字段或表达式是否为null。如果为null，返回指定的值，否则返回原本的值

#案例：显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT;如果commission_pct值为NULL，返回指定值0
#注意：在mysql中null和任何字段拼接都为null

SELECT IFNULL(commission_pct,0) AS 奖金率,commission_pct FROM employees;

SELECT CONCAT(first_name,',',last_name,',',job_id,',',IFNULL(commission_pct,0)) AS 'OUT_PUT' FROM employees;

isnull函数

功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

SELECT isnull(commission_pct),commission_pct FROM employees;

进阶2 ：条件查询

语法

select 查询列表 from 表名 where 筛选条件；
执行顺序：1、from 表名，先看当前库中有没有这张表，如果有就定位到这张表了
         2、where 筛选条件，看哪行数据满足要求
         3、select 查询列表

根据筛选条件的写法不同，可以分为以下几类:

一、按条件表达式筛选：
(简单)条件运算符：> < = != <> >= <=   <=>

二、按逻辑表达式筛选
作用：（当条件比较多时）用于连接条件表达式
逻辑运算符： &&    ||    ！
            and   or    not(推荐使用)
 && 和 and： 两个条件都为true,结果为true,反之为false
 || 和 or ： 只要有一个条件为true,结果为true，反之为false
 !  和 not： 如果连接的条件本身为false,结果为true,反之为false
 
三、模糊查询（不是精确匹配）（复杂条件运算符）
like
between and
in
is null /is not null

按条件表达式筛选

#案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;

#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id!=90;
SELECT last_name,department_id FROM employees WHERE department_id<>90;(推荐使用)

按逻辑表达式筛选

#案例1：查询工资在10000到20000之间的员工名，工资以及奖金
SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;

#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees WHERE department_id<90 OR department_id>110 OR salary>15000;
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

模糊查询

注意：字符型的值必须用单引号引起来。

1、like
特点：一般和通配符搭配使用（通配符：% 任意多个字符，包含0个字符；_ 任意单个字符）

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

#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';

#案例3：查询员工名中第二个字符为_的员工名（需使用\对_进行转义）
SELECT last_name,salary FROM employees WHERE last_name LIKE '_\_%';

SELECT last_name,salary FROM employees WHERE last_name LIKE '_a_%' escape 'a';  #此处a的作用相当于转移符号\，推荐使用


2、between and（not between and）
特点：
-- 使用 between and 可以提高语句的简洁度
-- 包含临界值（两个临界值不要调换顺序）

#案例1：查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id>=100 AND department_id<=120;
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;    #推荐使用


3、in
含义：判断某字段的值是否属于in列表中的某一项
特点：
-- 使用in提高语句简洁度
-- in列表的值类型必须一致或者兼容（比如 '123' 可以转换为123）

#案例1：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
等价于
SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');

错误写法：SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROG','AD_%');  （in 中不能使用通配符）


4、is null
= 或者 <> 不能用于判断null值
is null 或者 is not null 可以判断null值

#案例1、查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
错误写法：SELECT last_name,commission_pct FROM employees WHERE commission_pct=null;（因为 = 运算符不能判断null值）


 <=> （安全等于） 不仅能判断null值，也能判断普通的数值
#案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> null;
#案例2：查询工资为12000的员工信息
SELECT * FROM employees WHERE salary <=> 12000;

is null PK  <=> 

is null：仅仅可以判断null值，可读性较高，建议使用
<=>：不仅能判断null值，也能判断普通的数值，可读性较低

测试：查询员工号为176的员工的姓名和部门号和年薪

SELECT last_name,department_id,12*salary*(1+ifnull(commission_pct,0)) AS 年薪 FROM employees;

经典面试题

试问：1、SELECT * FROM employees;  
和
SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';
结果一样吗？并说明原因
不一样。如果判断的字段存在null值，则不一样，无null值则一样

2、SELECT * FROM employees;  
和
SELECT * FROM employees WHERE commission_pct LIKE '%%' OR last_name LIKE '%%';
结果一样吗？
一样。commission_pct 和 last_name 字段不可能同时为NULL

进阶3：排序查询(默认升序)

语法

SELECT 查询列表 FROM 表 【WHERE 筛选条件】 ORDER BY 排序列表 【ASC|DESC】;

特点

1、ASC代表的是升序，DESC代表的是降序。如果不写，默认是升序
2、ORDER BY子句中可以支持单个字段，多个字段，表达式，函数，别名
3、ORDER BY子句一般是放在查询语句的最后面，limit子句除外

案例

#案例1：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
select * FROM employees ORDER BY salary ASC;
select * FROM employees ORDER BY salary;

#案例2：查询部门编号>=90的员工信息，按入职时间的先后进行排序
SELECT * FROM employees WHERE department_id >= 90 ORDER BY  hiredate;

#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0))  desc;

#案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪  desc;

#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT last_name,length(last_name) 字节长度,salary FROM employees ORDER BY length(last_name) desc;

#案例6：查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

进阶4：常见函数

概念

类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处

1、隐藏了实现细节
2、提高代码的重用性

调用

select 函数名(实参列表) 【from 表】;

特点

1、叫什么（函数名）
2、干什么（函数功能）

分类

1、单行函数
   字符函数：length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
   数学函数：round、ceil、floor、truncate、mod、rand（获取随机数，返回0-1之间的小数）
   日期函数：now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format
   其他函数：version、database、user、password('字符')：返回该字符的密码形式、md5('字符’)：返回该字符的md5加密形式
   控制函数：if、case
    
2、分组函数
   功能：做统计使用，又称为统计函数、聚合函数、组函数
   分类： sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
   语法： SELECT max(字段) FROM 表名；
            
   特点：
       1、sum、avg一般用于处理数值型，max、min、count可以处理任何类型
       2、以上分组函数都忽略null值
       3、可以和distinct搭配实现去重的运算
       4、count函数的单独介绍
          count(字段)：统计该字段非空值的个数
          count(*)：统计结果集的行数
                    一般使用count(*)用做统计
                    
       5、和分组函数一同查询的字段要求是GROUP BY后出现的字段

注意

utf8   1个字母占1个字节，1个汉字占3个字节   
gbk    1个字母占1个字节，1个汉字占2个字节
show variables like '%char%';   #查询数据库编码

一、字符函数

1、length 获取参数值的字节个数
SELECT length('john');
SELECT length('张三丰hahaha');

2、concat 拼接字符串
SELECT CONCAT(last_name,'',first_name) 姓名 FROM employees;

3、upper、lower
SELECT UPPER('john');
SELECT LOWER('jOhn');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

4、substr、substring
#注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

#案例：姓名中首字符大写，其他字符小写然后用 _ 拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),'_',LOWER(last_name)) out_put FROM employees;

5、instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;

6、trim
SELECT LENGTH(TRIM('   张翠山     ')) AS out_put;
SELECT TRIM('a' from 'aaaaaaa张aaa翠山aaaaaaaa') AS out_put;
SELECT TRIM('aa' from 'aaaaaaa张aaa翠山aaaaaaaa') AS out_put;

7、lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',12,'*') AS out_put;

8、rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'*') AS out_put;

9、replace替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

二、数学函数

1、round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

2、ceil 向上取整，返回>=该参数的最小整数
SELECT CEIL(-1.02);

3、floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);

4、truncate 截断
SELECT TRUNCATE(1.655555,1)  #小数点后保留1位

5、mod 取余
SELECT MOD(10,-3);
SELECT MOD(-10,3);
SELECT 10%3;

三、日期函数

1、now 返回当前系统日期+时间
select now();

2、curdate 返回当前系统日期，不包含时间
select curdate();

3、curtime() 返回当前时间，不包含日期
select curtime();

4、可以获取指定的部分，比如年、月、日、小时、分钟、秒
select year(now()) 年;
select year('1998-1-1') 年;
select distinct year(hiredate) 年 from employees;

select month(now()) 月;
select monthname(now()) 月;

5、用户输入的日期，在后端接收到的是字符串
#str_to_date 将字符通过指定的格式转换成日期
select str_to_date('1998-3-2','%Y-%c-%d') as output;

6、查询入职日期为1992-4-3的员工信息
select * from employees where hiredate = '1992-4-3';
select * from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y');

7、date_format 将日期转换成字符
select DATE_FORMAT(now(),'%y年%m月%d日') as output;

8、查询有奖金的员工名和入职日期（xx月/xx日 xx年）
select last_name,date_format(hiredate,'%m月/%d日 %y年') 入职日期
from employees where commission_pct is not null;

四、其他函数

select VERSION();
select DATABASE();
select user();

五、流程控制函数

1、if函数

select if(10<5,'大','小');
select last_name,commission_pct,if(commission_pct is null,'没奖金，呵呵','有奖金，嘻嘻') 备注 from employees;

2、case函数的使用一：switch case 的效果（适合等值判断）

java中
switch(变量或表达式）{
        case 常量1:语句1;break;
        ...
        default:语句n;break;
}


mysql中（值不用加;，语句要加;)
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n或语句n;
end
案例：查询员工的工资，要求部门号=30，显示的工资为1.1倍
                        部门号=40，显示的工资为1.2倍
                        部门号=50，显示的工资为1.3倍
                        其他部门，显示的工资为原工资
select salary 原始工资,department_id,
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;

3、case函数的使用二：类似于多重if  （范围判断）

java中：
if(条件1){
        语句1;
}else if(条件2){
        语句2;
}
...
else{
        语句n;
}


mysql中：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
*/
#案例：查询员工的工资的情况
-- 如果工资>20000，显示A级别
-- 如果工资>15000，显示B级别
-- 如果工资>10000，显示C级别
-- 否则，显示D级别
select salary,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;

例题：组一个查询，产生下面的结果

样式：<last_name> earns <salary> monthly but wants <salary*3>  
例句：King earns 24000 monthly but wants 72000

sql语句：select concat(last_name,' earns ',salary,' monthly but wants ',salary*3) 'Dream salary' from employees;

六、分组函数

1、简单使用
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;


2、参数支持那些类型
select max(last_name),min(last_name) from employees;
select max(hiredate),min(hiredate) from employees;
select count(commission_pct) from employees;

错误写法：select sum(last_name),avg(last_name) from employees;    #mysql语法不报错不代表正确，无意义
    

3、是否忽略null值
select sum(commission_pct),avg(commission_pct),sum(commission_pct)/35,sum(commission_pct)/107 from employees;

select max(commission_pct),min(commission_pct) from employees;


4、和distinct搭配
select sum(distinct(salary)),sum(salary) from employees;
select count(distinct(salary)),count(salary) from employees;


5、count函数的详细介绍
select count(salary) from employees;
select count(*) from employees;     #当count统计某行数据时，只要有一个字段不为null，就统计上了。
select count(1)  from employees;    #相当于在表中加了一列1

效率：
myisam存储引擎下，count(*)的效率高
innodb存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些。


6、和分组函数一同查询的字段有限制
select avg(salary),employee_id from employees;    #错误查询

例题：查询员工表中的最大入职时间和最小入职时间的相差天数

select datediff(max(hiredate),min(hiredate)) difference from employees;