mysql语法总结day1

day 1:
1、基础查询
1)、% ----> 表示取余操作
2)、select version(); ----->查询当前mysql版本
3)、as 或者空格 -----> 给当前字段取别名
select xxx as xx from …
4)、如果别名字段中要求有空格，则需要在别名字段前后加上“ ”
SELECT salary AS “out put” FROM employees;
5)、在查询结果中去重使用关键字distinct
select distinct departmenr_id from employee
6)、+号的作用
仅仅只有运算符的功能（没有字符串拼接功能）
select 100+90; ----> 190 两个操作数都为数值型的时候，就做加法运算
select ‘123’+90; ----> 213 其中一方不为数值型的时候将其转换为数值进行运算
select ‘Join’+90 -----> 90 如果一方无法转换为一个数值，则其专户那结果为0
select null+0 -----> null 如果其中一方为null，那么结果肯定为null
7)、IFNULL函数的使用
IFNULL(commission_pct,0) as xxx …
如果‘commissio_pct’字段为null,那么将它当做0处理，否则就是原来的数据
2、条件查询
select
查询列表 —step3
from
表名 —step1
where
筛选条件; —step2

1)、按条件表达式进行筛选
条件运算符：>、<、=、!=(相当于<>)、>=、<=
2)、按逻辑表达式筛选
作用:用于连接条件表达式
逻辑运算符： &&、||、！
and、or、 not
3)、模糊查询
a. like 一般和通配符搭配使用：%代表任意多个字符，包括零个字符
代表任意一个字符
\用来转义 用escape关键字来自定义转义符号
select xxx from xxx where xxx LIKE 'KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’;
这里的$便是转义符号
b. between… and…
可以提高代码的简洁性，包含两个临界值，两个临界值不能调换顺序
c. in
判断某字段的值是否属于in列表中的某一项
in列表中的值的类型必须统一，或者必须兼容，并且不支持使用模糊匹配中所使用的通配符
d. is null
=或者!=不能用来判断null的值
is null 和 is not null用来判断null的值
<=>安全等于可以用来判断当前值为null
but be careful! the <!=> is wrong!
二者区别
is null：仅仅可以判断null值，可读性较高
<=> :既可用于判断null值，又可以判断普通的数值，不过可读性较低
3、查询排序
select 查询列表 —>step3
from 表 —>step1
【where 筛选条件】 —>step2
order by 排序列表 [asc(默认) | desc] —>step4
1)、asc代表升序，desc代表降序，如果不写的话默认是升序；
2)、order by子句中可以支持单个字段，多个字段、表达式、函数、别名；
3)、order by子句一般是放在查询语句的最后面，但是limit子句除外，limit放在order by之后;
4)、limit (start,count)；其中start表示从结果的第几条记录开始，count表示一共查询多少条记录；
4、常见函数(函数间可以嵌套)
1)、单行函数
a. 字符函数
1. LENGTH 获取参数值的字节个数
select LENGTH(‘john’); ---->4
select LENGTH(‘张三’); ---->6(UTF-8编码)
查看当前使用的字符集
show variables like ‘%char%’;
2. CONCAT 拼接字符串
CONCAT(last_name,’_’, first_name) AS 姓名 …
3. upper,lower
SELECT UPPER(‘john’);
SELECT LOWER(‘Hello’);
4. substr、substring 注意，sql语言索引从1开始
5. instr 获取子串第一次出现的索引，如果找不到，返回-1
6. trim 默认是去掉两端空格，去掉指定字符使用 from
SELECT TRIM(‘aa’ FROM ‘aaaaaa张aaaaaaaa翠山aa’) AS out_put;
7. lpad 用指定字符实现左填充指定长度
SELECT LPAD(‘殷素素’,2,’-’) AS out_put; #殷素
SELECT LPAD(‘殷素素’,5,’-’) AS out_put; #–殷素素
8. rpad 用指定字符实现右填充指定长度
SELECT RPAD(‘殷素素’,2,’-’) AS out_put; #殷素
SELECT RPAD(‘殷素素’,5,’-’) AS out_put; #殷素素–
9. replace 字符串的替换
b. 数学函数
1. round 四舍五入
SELECT ROUND(1.4567,2); #1.46 保留小数点后两位
SELECT ROUND(-1.55); #-2
2. ceil 向上取整 返回>=该参数的最小整数
SELECT CEIL(-1.11); #-1
SELECT CEIL(12.56); #13
3. floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99); #-10
4. truncate 截断,返回保留小数点后n位
SELECT TRUNCATE(1.6999,2); #1.69
5. mod 取余,被除数为正则为正，被除数为负则为负
mod(a,b) : a-a/bb
mod(-10,-3) : -10- (-10)/(-3)(-3)
SELECT MOD(10,3); #1，相当于% ：a-a/bb(表示整除取商)
c. 日期函数
1. now() 返回当前系统日期+时间
select now();
2. curdate() 返回当前系统日期，不包含时间
select curdate();
3. curtime() 返回当前时间，不包含日期
select curtime();
4. 可以获取指定的部分，年，月，日，小时，分钟，秒
year(字符串参数)，month(字符串参数)
day(字符串参数)，dayname(字符串参数)—>获取的是星期几
hour(字符串参数)，minute(字符串参数)
second(字符串参数)
5. str_to_date 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE(‘22:13:20 1998/3/4’,’%H:%i:%s %Y/%m/%d’) AS ‘date’; #1998-03-04 22:13:20
6. date_format:将日期转换为字符
SELECT DATE_FORMAT(‘2018/6/6’,’%Y年%m月%d日’); #2018年06月06日
d. 流程控制函数
1. if函数
SELECT IF(10<5,‘大’,‘小’);
2. case函数的使用一：switch case的效果
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
…
else 要显示的值n或者语句n
end
#-------------------------example-----------------------
SELECT
salary AS 原始工资,
department_id,
CASE department_id
WHEN 30 THEN salary1.1
WHEN 40 THEN salary1.2
WHEN 50 THEN salary1.3
ELSE salary
END AS 新工资
FROM
employees
#------------------------------------------------------------
case函数的使用二：类似于 多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n;
#------------------example-----------------------------
SELECT
salary,
CASE
WHEN salary>20000 THEN ‘A’
WHEN salary>15000 THEN ‘B’
WHEN salary>10000 THEN ‘C’
ELSE ‘D’
END
AS 工资级别
FROM
employees;
#------------------------------------------------------------
2)、分组函数
功能：用作统计使用，又称为聚合函数或者统计函数或是组函数
分类：sum求和，avg平均值，max最大值，min最小值，count计算个数
特点：
1、sum,avg一般用于处理数值型数据；
max,min,count可以用于处理任何类型；
2、以分组函数都将忽略null值进行处理；
3、可以和distinct搭配使用实现去重运算；
4、count函数的单独使用一般使用count()统计行数
5、和分组函数一同查询的字段要求是group by后的字段，其他的都不行
datediff(date1,date2)；参数为两个日期型数据，查询结果为date1比date2多的天数；
5、分组查询
group by子句语法—>可以使用Group By子句将表中的数据分成若干组
select column,group_function(column)
from table
[where condition]
[group by group_by_expression] [having condition]
[order by column];
如果要对原来不存在表中的字段进行筛选的话使用having关键字+查询条件
总结：
1、分组查询的筛选条件可以分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果表 group by子句的后面 having
1）、分组函数做条件肯定是放在having子句中
2）、能用分组前筛选的优先使用分组前筛选
2、group by子句支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开没有顺序要求）
3、也可以添加排序（排序放在整个分组查询的最后）
6、连接查询
6.1、笛卡尔积（内连接）
笛卡尔乘积现象：加入表1 有m行，表2有n行，结果=mn行
发生原因：没有有效的匹配条件
如何避免：添加有效的连接条件
6.2、连接的分类
分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持所有的内连接+外连接（保护支持全外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
6.3、等值连接
1、多表等值连接的结果为多表的交集部分
2、n表连接，至少需要n-1个连接条件
3、多表的顺序没有要求
4、一般需要为表起别名
5、可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
#案例：查询员工名部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id
AND city LIKE ‘s%’
ORDER BY department_name ASC;
6.4、非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level=‘A’;

6.5、自连接
select employees_id,last_name,manager_id
from
employees;
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

#########################
day 1、总结
排序
一、语法
select 查询列表
from 表
where 筛选条件
order by 排序列表【asc|desc】
二、特点
1、asc:升序，如果䘏默认升序
desc:降序
2、排序列表 支持单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后（除limit语句之外）
常见函数：
一、概述
功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select 函数名（实参列表）；
二、单行函数
1、字符函数
concat : 连接
substr : 截取子串
upper : 变大写
lower：变小写
replace：替换
length：获取字符串长度
trim：去前后空格
lpad：左填充
rpad：右填充
instr：获取子串第一次出现的索引
2、数学函数
ceil：向上取整
round：四舍五入
mod：取模
floor：向下取整
truncate：截断
rand：获取随机数，默认返回的是[0,1)之间的一个小数
3、日期函数
now：返回当前日期+时间
year：返回年
month：返回月
day：返回日
date_format：将日期转换成字符
curdate：返回当前日期
str_to_date：将字符转化为日期
curtime：返回当前时间
hour：小时
minute：分钟
second：秒
datediff：返回两个日期相差的天数
monthname：以英文形式返回月
4、其他函数
version：当前数据库服务器的版本
datebase：当前打开的数据库
user：当前用户
password（“字符”）：返回该字符的密码形式
MD5（“字符”）：返回字符的MD5的加密形式结果
5、流程控制函数
1、if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
2、case
情况a、
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end
情况b、
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end
三、分组函数
1、分类
max：最大值
min：最小值
sum：和
avg：平均值
count：计算个数
2、特点
a.语法
select max(字段) from 表名；
b.支持的类型
sum和avg一般用于处理数值型数据
max、min、count可以处理任何数据类型
c.以上分组函数都忽略null
d.都可以搭配distinct使用，先去重的统计
select sum(distinct 字段) from 表；
e.count函数
count(字段)；统计该字段非空值的个数
count();统计结果集的行数
案例：查询每个部门的员工个数
select count()
from employees
group by department_id;
count(1)：统计结果集的行动
效率上：
MyISAM,count()最高
InnoDB存储引擎，count()和count(1)效率>count(字段)
f.和分组函数一同查询的字段，要求是group by后出现的字段
四、分组查询
1、语法
select 分组函数，分组后的字段··················step5
from 表··················································step1
[where 筛选条件]·····································step2
group by分组的字段·································step3
[having 分组后的筛选]······························step4
[order by 排序列表]··································step6
注意：
在mysql中除了order by、select中支持别名，在group by以及having中也支持别名（但是不推荐）
但是在Oracle、SQLServer中不支持在group by和having中使用别名
2、特点
使用关键字 筛选的表 位置
分组前筛选 where 原始表 group by前面
分组后筛选 having 分组后的表 group by后面
五、连接查询
1、含义
当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2…;
笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，到值多个表所有行实现完全连接
如何解决：添加有效的连接条件
2、分类
按年代分类：
sql92:
等值
非等值
自连接

       	         也支持一部分外连接(用于Oracle、sqlserver,mysql中不支持)
       	    sql99【推荐使用】：
       	         内连接
       	         		等值
       	         		非等值
       	         		自连接
       	         外连接
       	                左外
       	                右外
       	                全外（mysql不支持）
       	          交叉连接
  3、sql92语法
      1、等值连接
      语法：
          select 查询列表
          from 表1 别名，表2 别名
          where 表1.key=表2.key
          [and 筛选条件]
          [group by 分组字段]
          [having 分组后的筛选]
          [order by 排序字段]
        特点：
          a. 一般会为表起别名
          b. 多表的顺序可以调换
          c. n表至少需要n-1个连接条件
          d. 等值连接的结果是多表的交集部分
      2、非等值连接
      	语法：
        select 查询列表
          from 表1 别名，表2 别名
          where 非等值的连接条件
          [and 筛选条件]
          [group by 分组字段]
          [having 分组后的筛选]
          [order by 排序字段]
      3、自连接
      语法：
          select 查询列表
          from 表 别名1，表 别名2
          where 非等值的连接条件
          [and 筛选条件]
          [group by 分组字段]
          [having 分组后的筛选]
          [order by 排序字段]
          [limit 显示行数]