东软培训-Day3

大熙哥 2021年09月08日 140次浏览

常用函数

  • 数学函数
  • 字符串函数
  • 日期和时间函数
  • 流程控制函数
  • 其他函数

数学函数

  • ABS(x):返回x的绝对值;
  • SQRT(x):返回非负数x的平方根;
  • PI():返回圆周率;
  • MOD(x,y)或%:返回x被y除的余数;
  • CEIL(x)、CEILING(x):返回大于或者等于x的最小整数值;
  • FLOOR(x):返回小于或者等于x的最大整数值;
  • ROUND(x,y):返回保留小数点后面y位,四舍五入的整数;
  • TRUNCATE(x,y):返回被舍弃的小数点后y位的数字x;
  • RAND():每次产生不同的随机数;
  • SIGN(x):返回参数的符号;
  • POW(x,y)和POWER(x,y): 返回x的y次乘方的结果值;
  • EXP(x):返回以e为底的x乘方后的值;
  • LOG(x):返回x的自然对数,x相对于基数e的对数;
  • LOG10(x):返回x的基数为10的对数;
  • RADIANS(x):将参数x由角度转化为弧度;
  • DEGREES(x):将参数x由弧度转化为度。
  • SIN(x):返回x正弦,其中x为弧度值;
  • ASIN(x)返回x的反正弦,即正弦为x的值;
  • COS(x):返回x的余弦;
  • ACOS(x):返回x反余弦
  • TAN(x):返回x的正切;
  • ATAN(x)返回x的反正切;

练习

-- 1.写一个查询,分别计算100.456 四舍五入到小数点后第2位,第1位,整数位的值。
SELECT  ROUND(100.456, 2)
SELECT  ROUND(100.456, 1)
SELECT  ROUND(100.456)

-- 2.写一个查询,分别计算100.456 从小数点后第2位,第1位,整数位截断的值。
SELECT  TRUNCATE(100.456, 2)
SELECT  TRUNCATE(100.456, 1)
SELECT  TRUNCATE(100.456)

字符串函数

  • CHAR_LENGTH(str):返回字符串str的所包含字符个数;
  • LENGTH(str):返回字符串str的长度;
  • CONCAT(s1,s2,...): 字符串连接;
  • CONCAT_WS(x,s1,s2,…):字符串连接, x是其它参数的分隔符;
  • INSERT(s1,x,len,s2) :返回字符串s1,s1中插入字符串s2;
  • LOWER (str)|LCASE (str):将字符串全部转换成小写字母;
  • UPPER(str)|UCASE(str):将字符串全部转换成大写字母;
  • LEFT(s,n):返回最左边指定长度的字符;
  • RIGHT(s,n):返回最右边指定长度的字符;
  • LPAD(s1,len,s2)| RPAD(s1,len,s2) :填充字符串函数;
  • TRIM(s1 FROM s)|LTRIM(s)|RTRIM(s):删除空格函数;
  • REPEAT(s,n):重复生成字符串函数;
  • SPACE(n):返回一个由n个空格组成的字符串;
  • REPLACE(s,s1,s2):字符串替换函数;
  • STRCMP(s1,s2):比较字符串大小函数;
  • SUBSTRING(s,n,len):获取子串函数;
  • LOCATE(str1,str)|POSITION(str1 IN str)|INSTR(str, str1):匹配子串开始位置函数;
  • REVERSE(s):将字符串s反转;
  • ELT(N,字符串1,字符串2,字符串3,…):返回指定位置函数;

CHAR_LENGTH()和LENGTH()的区别区别

无论是LENGTH()还是CHAR_LENGTH()都是为了统计字符串的长度。只不过,LENGTH()是按照字节来统计的,CHAR_LENGTH()是按照字符来统计的。

练习

-- 1.显示所有员工姓名的前三个字符
SELECT LEFT(ename,3) FROM emp;
SELECT SUBSTR(ename,1,3) FROM emp;
-- 2.显示正好为5个字符的员工的姓名,工资,部门号
SELECT ename,sal,deptno
FROM emp
WHERE CHAR_LENGTH(ename) = 5;

-- 1.写一个查询,用首字母大写,其它字母小写显示雇员的 ename,显示名字的长度,并给每列一个适当的标签,条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员,并对查询结果按雇员的ename升序排序。(提示:使用length、substr)
SELECT CONCAT(UPPER(LEFT(ename,1)),LOWER(SUBSTR(ename,2,CHAR_LENGTH(ename)))) AS '员工姓名' FROM emp;
WHERE UPPER(LEFT(ename,1)) IN ('J','A','M') ORDER BY ename;
-- 2.查询员工姓名中中包含大写或小写字母A的员工姓名。
SELECT ename FROM emp WHERE ename LIKE '%a%' OR '%A%' ;
SELECT ename FROM emp WHERE POSITION('a' IN ename) > 0 OR POSITION('A' IN ename) > 0 ;
-- 3.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ename,'a','A')FROM emp ;
-- 4.查询部门编号为10或20,入职日期在81年5月1日之后,并且姓名中包含大写字母A的员工姓名,员工姓名长度
SELECT CHAR_LENGTH(ename) AS '员工姓名长度',ename,deptno FROM emp
WHERE deptno IN (10,20) AND hiredate > '1981-05-01' AND POSITION('A' IN ename) > 0;
SELECT ename ,CHAR_LENGTH(ename)FROM emp
WHERE (deptno=10 OR deptno=20) AND hiredate>'81-05-01' AND ename LIKE '%A%';
-- 5.查询每个职工的编号,姓名,工资
-- 要求将查询到的数据按照一定的格式合并成一个字符串.
-- 前10位:编号,不足部分用*填充,左对齐
-- 中间10位:姓名,不足部分用*填充,左对齐
-- 后10位:工资,不足部分用*填充,右对齐
SELECT CONCAT( RPAD(empno,10, '*'),RPAD(ename,10, '*'),LPAD(sal,10, '*')) AS '编号,姓名,工资' FROM emp

日期和时间函数

CURDATE()和CURRENT_DATE() :获取当前日期函数;
NOW():返回服务器的当前日期和时间;
CURTIME():返回当前时间,只包含时分秒;
UTC_DATE():返回世界标准时间日期函数;
UTC_TIME():返回世界标准时间函数;
TIMEDIFF(expr1, expr2):返回两个日期相减相差的时间数;
DATEDIFF(expr1, expr2):返回两个日期相减相差的天数;
DATE_ADD(date,INTERVAL expr type):日期加上一个时间间隔值;
DATE_SUB(date,INTERVAL expr type):日期减去一个时间间隔值;
DATE(date)、TIME(date)、YEAR(date):选取日期时间的各个部分:
EXTRACT(unit  FROM  date):从日期中抽取出某个单独的部分或组合;
DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天
DAYNAME、MONTHNAME:返回日期的星期和月份名称;
DATE_FORMAT(date,format):格式化日期;
TIME_FORMATE(time,formate):格式化时间;

选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒(常用)

  • SELECT now(),date(now()); -- 日期
  • SELECT now(),time(now()); -- 时间
  • SELECT now(),year(now()); -- 年
  • SELECT now(),quarter(now()); -- 季度
  • SELECT now(),month(now()); -- 月
  • SELECT now(),week(now()); -- 周
  • SELECT now(),day(now()); -- 日
  • SELECT now(),hour(now()); -- 小时
  • SELECT now(),minute(now()); -- 分钟
  • SELECT now(),second(now()); -- 秒
  • SELECT now(),microsecond(now()); -- 微秒
练习
1.查询服务器当前时间
2.查询部门10,20的员工截止到2000年1月1日,工作了多少个月,入职的月份。
3.如果员工试用期6个月,查询职位不是MANAGER的员工姓名,入职日期,转正日期,入职日期是第多少月,第多少周

流程控制函数

常见的控制流程函数如下:

  • CASE 
  • IF
  • IFNULL
  • NULLIF

case 选择

SELECT CASE 11 WHEN 1 THEN 'one'
	WHEN 2 THEN 'two' ELSE 'more' END;
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

if 选择

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。
IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。

SELECT IF(1>2,2,3);
SELECT IF(1<2,'yes ','no');

IFNULL(expr1,expr2)|NULLIF(expr1,expr2)

假如expr1 不为NULL ,则IFNULL() 的返回值为expr1 ; 否则其返回值为expr2 。
IFNULL() 的返回值是数字或是字符串,具体情况取决于其所使用的语境

课后作业

-- 课后作业
-- 1.计算2000年1月1日到现在有多少月,多少周(四舍五入)。
SELECT 
TIMESTAMPDIFF(MONTH,'2000-01-01',NOW()) AS '现在有多少月',
TIMESTAMPDIFF(WEEK,'2000-01-01',NOW()) AS '现在有多少周' ;

-- 查询第三个字母是A的
SELECT ename FROM emp WHERE POSITION('A' IN ename) = 3;
SELECT ename,MID(ename,3,1) FROM emp WHERE MID(ename,3,1) = 'A';

-- 使用trim函数将字符串‘hello’、‘  Hello ’、‘bllb’、‘ hello    ’分别处理得到下列字符串ello、Hello、ll、hello。

SELECT TRIM( 'h' FROM'hello'),TRIM('   Hello  '),TRIM( 'b' FROM 'bllb'),TRIM('   hello  ')

-- 4.将员工工资按如下格式显示:123,234.00 RMB 。

SELECT CONCAT(FORMAT(sal,2),' RMB')  from emp;


-- 5.查询员工的姓名及其经理编号,要求对于没有经理的显示“No Manager”字符串。
SELECT ename,IFNULL(mgr,'No Manager')  from emp;

-- 6.将员工的参加工作日期按如下格式显示:月份/年份。 
SELECT   DATE_FORMAT(hiredate,'%m/%Y')  from emp;

-- 7.在员工表中查询出员工的工资,并计算应交税款:
-- 如果工资小于1000,税率为0
-- 如果工资大于等于1000并小于2000税率为10%
-- 如果工资大于等于2000并小于3000税率为15%
-- 如果工资大于等于3000,税率为20%。

SELECT CASE 
WHEN sal < 1000 
THEN 0
WHEN  sal < 2000
THEN sal * 0.1
WHEN  sal < 3000
THEN sal * 0.15
WHEN  sal >= 3000
THEN sal * 0.2
END AS '应交税款',sal AS '工资' FROM emp;



-- 8.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度,用 $ 左填充,列标签 SALARY。
SELECT ename, LPAD(sal,15,'$') AS 'SALARY' FROM emp;

多表连接

image.png

连接类型

image.png

笛卡尔积

  • 第一个表中的所有行和第二个表中的所有行都发生连接。
  • 笛卡尔积在下列情况产生:
  • 连接条件被省略
  • 连接条件是无效的
  • 为了避免笛卡尔积的产生,通常需要在WHERE子句中包含一个有效的连接条件。

多于两个表连接

image.png

image.png

非等值连接

image.png

image.png

自身连接

image.png

image.png

写法

  1. 分析要查询的列都来自于哪些表,构成FROM子句;
  2. 分析这些表之间的关联关系,如果表之间没有直接关联关系,而是通过另一个中间表关联,则也要在FROM子句中补充中间关联表;
  3. 接下来在WHERE子句中补充表之间的关联关系,通常N个表,至少要有N-1个关联关系;
  4. 分析是否还有其它限制条件,补充到WHERE子句的表关联关系之后,作为限制条件;
  5. 根据用户想要显示的信息,补充SELECT子句。
  6. 分析是否有排序要求,如果排序要求中还涉及到其它表,则也要进行第2步补充排序字段所在的表,并且添加表之间的关联关系;

mysql where执行顺序

自左往右
排除越多的应该放到最前

ORACLE where执行顺序

自下而上
排除越多的应该放到最后