sql学习
一、数据库相关概念
二、MYSQL背景与优点
三、sql基础语句
1.查看表结构
DESC tab_users;
2.拼串
CONCAT(str1,str2,....)表示拼接字符串，IFNULL(字段,字段为空返回结果)，判断字段为空返回值。
如果拼串的小串是数值型，那将会先转换为字符型，然后进行拼串
SELECT CONCAT(loginname,',',IFNULL(`password`,0)) from tab_users
3.逻辑运算符不等于<>,!=
更推荐用<>
SELECT * from tab_users where loginname <> '测试' SELECT * from tab_users where loginname != '测试'
4.查询在一个区间内的数据between and,and
在一个区间内的数据：
SELECT * from tab_users where username>=400 and username<=1000 SELECT * from tab_users where username BETWEEN 400 AND 900
不在一个区间内的数据：
SELECT * from tab_users where username NOT BETWEEN 400 AND 900
5.查询不在一个区间内的数据
SELECT * from tab_users where username<400 or username>1000 SELECT * from tab_users where NOT(username>=400 and username<=1000)
6.使用通配符进行模糊查询 LIKE
注意通配符%不能匹配null。
%匹配任意个字符，_匹配单个字符。
通配查询字符型的：
6.1查询第二个字为明的 SELECT * FROM tab_users where loginname LIKE '_明' 6.2查询最后一个字为明的 SELECT * FROM tab_users where loginname LIKE '%明' 6.3查询第二个字为_，最后一个字为试的，用了转义字符 SELECT * FROM tab_users where loginname LIKE '_\_试' SELECT * FROM tab_users where loginname LIKE '_$_试' ESCAPE '$'
通配查询数值型的：
6.4查询数值型的 SELECT salary from tab_users WHERE salary LIKE '2%'
8.判断字段值是否满足某些条件IN,OR
SELECT * from tab_users where loginname IN ('测试','小明') SELECT * from tab_users where loginname <>'测试' OR loginname='小明'
9.多表查询之左连接右链接
关键字LEFT JOIN 前面紧跟的是为主体的那个表，后面紧跟的是另一个表，关键字ON后面紧跟的是建立外键联系的逻辑语句。WHERE后面紧跟的是查询条件。
主体表中有几条数据，查询结果中就有几条，符合外连接条件的就在符合条件的数据后面展示另一个表对应的信息，不符合条件的数据的外连接信息显示为空。
SELECT t.loginname, r.rolename, r.content FROM tab_users t LEFT JOIN role r ON t.roleid = r.id WHERE t.loginname = "测试"
10.判断null相等,null不相等
IS NULL：仅仅可以判断null值，可读性较高，建议用。
<=> ：又叫“安全等与”，判断是否等于，如果等于返回true。既可以判断null值又可以判断普通的值，可读性较低
SELECT loginname,roleid from tab_users where roleid <=> NULL SELECT loginname,roleid from tab_users where roleid IS NULL
IS NOT NULL：仅仅可以判断null值。
SELECT loginname,roleid from tab_users where roleid IS NOT NULL
11.数值计算
SELECT loginname,salary*IFNULL(`month`,0) "all" from tab_users
12.去重DISTINCT
注意，distinct 后面只能接一个字段名。假设对两个字段去重，每个字段去重之后条数都不一样，让数据库怎么显示？
SELECT DISTINCT loginname from tab_users
13.经典面试题
结果是第一行结果不一样。因为如果数据中含有null则查不出当前条数数据。
第二行结果一样 。总有一个是不为null，当前数据可以被查出。
14.+
select 数值+数值；直接进行数值运算
select 字符+数值；先把字符转为数值，转换成功，进行数值间运算，转换失败，把字符转为0，然后做数值运算。
select null+值；结果都为null
15.ISNULL()
函数isnull（字段），如果为空，返回1，否则返回0。在mysql里，可以把1认为是true，0认为是false。
SELECT roleid,ISNULL(roleid) from tab_users
结果：
16.order by排序
desc 降序排列
asc升序排列
16.1按字段排序。字段用字符串表示的数字，排序后按照从前到后的顺序依次排序。
SELECT loginname,salary from tab_users ORDER BY salary ASC
结果：
16.2按字段排序如果想按实际大小排布字符型数字，可以＋0后转为数值型，然后再排序。
SELECT loginname,salary from tab_users ORDER BY salary+0 ASC
结果：
16.3按表达式排序
SELECT loginname,(salary+0)*12 工资 from tab_users ORDER BY (salary+0)*12 ASC
16.4按别名排序
SELECT loginname,(salary+0)*12 工资 from tab_users ORDER BY 工资 ASC
16.5按函数排序
SELECT loginname,LENGTH(salary),salary 工资 from tab_users ORDER BY LENGTH(salary) ASC
16.6按多个字段排序
这里先根据工资升序排序，再在相同的工资条目上，根据id降序排序。
SELECT id,loginname,salary 工资 from tab_users ORDER BY salary+0 ASC,id DESC
结果：
17.sql语句执行顺序
先执行 from...
再执行 where...
再执行select...
再执行order by...
四、常见函数
概念：类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴漏方法名
好处：隐藏了实现细节，提高代码的复用性
调用：select 函数名（实参列表） 【from 表】
分类：
单行函数：
concat,length,ifnull等
分组函数：
做统计使用，又称为统计函数，聚合函数，组函数
1.length 获取参数值的字节数
SELECT LENGTH("陆双123")
这条返回结果是9，因为一个英文占一个字节，一个中文占三个字节（在utf8编码下），一个汉字占2个字节（在gbk编码下）
查看我的编码方式：
show VARIABLES LIKE "%char%"
这条返回结果：
2.concat 拼接字符串
SELECT CONCAT(id,'_',loginname) as pinjie from tab_users
3.upper,lower变大写，变小写
SELECT UPPER("lucy") SELECT LOWER("Zoom") SELECT CONCAT(UPPER("lucy_"),LOWER("Age_"),'18') SELECT CONCAT(UPPER(loginname),LOWER(username),'18') from tab_users
4.substr，substring截取字串
注意sql语言中的索引从1开始
#截取从指定索引之后的所有字符。#返回结果为陆双 SELECT SUBSTR("我叫陆双",3) #截取从指定字符之后的指定长度的字符。#返回结果为陆 SELECT SUBSTR("我叫陆双",3,1)
5.instr，查索引
返回第二个字符串在第一个字符串中第一次出现的索引数。如果找不到返回0。
SELECT INSTR("今天天气很好","很好")
这里结果是5
6.trim去空格，去指定字符
去除左右两端的，内部不去除。
SELECT TRIM(" 拦路雨 偏似雪花 ") as output
这里结果是“拦路雨 偏似雪花”
SELECT TRIM( "拦" from "拦拦路雨 偏似雪花拦") as output
这里结果也是"路雨 偏似雪花"
SELECT TRIM( "拦拦路" from "拦拦路雨 偏似雪花拦") as output
这里结果是"雨 偏似雪花拦"
7.lpad,rpad 左填充，右填充，指定字符填充至固定长度
向第一个参数中，添加第三个参数，至总长度为指定的第二个参数大小。若第二个参数比第一个参数的长度还小，则不填充
SELECT LPAD("我是小学生",10,"*") SELECT LPAD("我是小学生",10,"ab") SELECT RPAD("我是小学生",10,"*") SELECT RPAD("我是小学生",10,"ab")
8.replace 替换子串中指定字符
SELECT REPLACE("小狗吃骨头","骨头","狗粮")
五、数学函数
1.round四舍五入
先取绝对值，进行四舍五入，再加上正负号
SELECT ROUND(3.4)#3 SELECT ROUND(3.7)#4 SELECT ROUND(-3.4)#-3 SELECT ROUND(-3.7)#-4
两个参数的round函数，保留固定小数位数的四舍五入结果
SELECT ROUND(3.456,2)#3.46
2.ceil向上取整
返回>=该参数的最小整数
SELECT CEIL(1.2)#2 SELECT CEIL(-1.2)#-1
3.floor向下取整
返回<=该参数的最大整数
SELECT FLOOR(1.2)#1 SELECT FLOOR(-1.2)#-2
4.truncate截断
固定位数之后的小数位全都不要了
SELECT TRUNCATE(3.456,2)#3.45
5.mod除
简单的看第一个参数正负即可。
实际上运算结构是：mod(a,b)=a-a/b*b
比如，mod(-10,-3)=-10-(-10/-3)*-3=-1
SELECT MOD(10,3)#1 SELECT MOD(10,-3)#1 SELECT MOD(-10,3)#-1
三、日期函数
1.now返回当前系统日期＋时间
SELECT NOW()
2.curdate返回当前系统日期，不包括时间
SELECT CURDATE()
3.curtime返回当前时间，不包括系统日期
SELECT CURTIME()
4.可以获取指定的部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW())#2022 SELECT YEAR('2001-06-19')#2001 SELECT MONTH(NOW())#3 SELECT MONTH('2001-06-19')#6 SELECT MONTHNAME(NOW())#MARCH SELECT MONTHNAME('2001-06-19')#JUNE SELECT DAY(NOW())#5
5.str_to date字符串转日期
SELECT STR_TO_DATE("2022:3_5","%Y:%c_%d")
6.date_formate日期转字符串
SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日')
四、其他函数
1.version查mysql版本
SELECT VERSION()
2.database查数据库名字
SELECT DATABASE()
3.user查用户名字
SELECT USER()
五、流程控制函数
1.if函数，有if-else效果
三个参数中，第一个参数是判断语句，第二个参数是判断为真执行语句，第三个参数为判断为假执行语句
SELECT loginname,IF(roleid IS NULL,"没有角色名","有角色名") from tab_users SELECT if(10>3,'yes','no')
2.case
相当于Java中的switch...case...
语法为：
【
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1；（这里then后如果是值，不加分号，如果是语句函数就加分号）
when 常量2 then 要显示的值2或者语句2；
else 要显示的值n或者语句n；
end
】
SELECT salary 原工资, CASE salary WHEN 1000 THEN salary*2 WHEN 100 THEN salary*2 ELSE salary END AS 新工资 from tab_users
3.case使用2
类似于Java中的多重查询if
语法：
【
case
when 语句1 then 要显示的值1或者语句1;
when 语句2 then 要显示的值2或者语句2;
else 要显示的值n或者语句n;
end
】
SELECT salary 原工资, CASE WHEN salary>500 THEN 'A' WHEN salary>200 THEN 'B' WHEN salary>100 THEN 'C' ELSE 'D' END AS 工资级别 from tab_users
二、分组函数
功能：用作统计使用，又称为聚合函数，或者统计函数，或者组函数
分类：sum求和，avg平均值，max最大值，min最小值，count计算个数
特点：1.sum.avg一般用于处理数值型
2.max.min.count可以处理任何类型
3.以上分组均可以忽略null
1.简单的使用
SELECT SUM(salary),ROUND(AVG( salary),2),MIN( salary),MAX( salary),COUNT( salary) FROM tab_users
