函数和表达式
SQL函数包括大量的可用函数，包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数可以简化用户的操作，使用时可以查询相关资料和手册。函数便于用户编写sql处理格式化、过滤和数据。
同时注意，虽然每种数据库管理系统都提供各种函数，但是具体实现差异都比较大，不可移植。我们学习函数，第一有可能很多查询不用函数无法实现，第二虽然很多函数不可移植或者通用，但是每种数据库系统都提供类似功能的函数。
字符串函数
常用的字符串处理函数
-- CONCAT：连接字符串
SELECT CONCAT(name, '-', department) AS full_name
FROM student;
-- UPPER/LOWER：转换大小写
SELECT UPPER(name) AS upper_name,
LOWER(name) AS lower_name
FROM student;
-- LENGTH：获取字符串长度
SELECT name, LENGTH(name) AS name_length
FROM student;
-- TRIM：删除首部、尾部空格字符，相应的LTRIM和RTRIM处理首部和尾部
SELECT LTRIM(' student '),TRIM(' student '),RTRIM('student ') ;
-- REPLACE：字符串替换
SELECT REPLACE(department,'系','学院'),department FROM student;
-- SUBSTRING：截取子字符串
SELECT SUBSTRING(name, 1, 2) AS short_name
FROM student;
数值函数
数学计算相关函数Abs：返回绝对值 Exp：值e的n次方，自然对数的底 Sqrt：返回非负数n的平方根 Pow：返回值x的y次幂 Mod或者%：返回N被M除的余数 Ceiling：不小于X的最小整数值 Round：四舍五入的一个整数
-- ROUND：四舍五入
SELECT ROUND(score, 2) AS rounded_score
FROM stuscore;
-- CEIL/FLOOR：向上/向下取整
SELECT CEIL(score) AS ceil_score,
FLOOR(score) AS floor_score
FROM stuscore;
-- ABS：绝对值
SELECT ABS(score) AS abs_balance, ABS(-5) as test
FROM stuscore;
-- MOD：取余数
SELECT MOD(10, 3) AS remainder;
日期函数
一般数据库中的日期时间类型有date、time、datetime和timestamp，相关函数的主要功能就是处理相关格式转化以及和字符串的转化。
-- NOW：当前日期时间
SELECT NOW() AS current_datetime;
-- CURDATE：当前日期
SELECT CURDATE() AS currentdate;
SELECT CURTIME(),CURDATE(),NOW();
-- YEAR/MONTH/DAY：提取年月日
SELECT YEAR('2005-12-24') AS birth_year,
MONTH('2005-12-24') AS birth_month,
DAY('2005-12-24') AS birth_day
FROM student;
-- HOUR/MINUTE/SECOND：提取时分秒
SELECT NOW(),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
-- 提取日月季度
SELECT DAYNAME('2019-05-03'),MONTHNAME('2019-05-03'),QUARTER('2019-05-03');
-- DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)
SELECT NOW(),DAYOFWEEK(NOW()),DAYOFMONTH(NOW()),DAYOFYEAR(NOW());
-- DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒 DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒
SELECT DATE_SUB(CURDATE(), INTERVAL 31 DAY), CURDATE(),DATE_ADD(CURDATE(),INTERVAL 2 DAY);
-- DATEDIFF：计算日期差
SELECT DATEDIFF(NOW(), '2005-12-24') AS days_old
FROM student;
-- timestamp：时间戳timestamp也是sql常用的一种时间类型,unix时间戳相互转化
select from_unixtime(1488160428,'%Y-%m-%d %T'),unix_timestamp(now());
-- timestampdiff(interval, datetime1,datetime2)，返回时间差，结果单位由interval参数给出，interval有second，minute，hour，day，month，year，week，quarter等
SELECT TIMESTAMPDIFF(HOUR,'2019-10-01','2020-01-13'),DATEDIFF('2019-10-01','2020-01-13');
-- DATE_FORMAT：格式化日期
SELECT DATE_FORMAT('2005-12-24', '%Y年%m月%d日') AS formatted_date;
str_to_date('2019-10-16 18:30:24','%Y-%m-%d %H:%i:%s')函数类似和dateformat效果。 DATE_FORMAT(date,format)函数用于把日期转化各种格式，下表是相关格式说明。
SQL日期时间格式符
MySQL等数据库常用的日期时间格式化标识
| 标识 | 描述 | 标识 | 描述 |
|---|---|---|---|
| %a | 缩写星期名 | %m | 月，数值(00-12) |
| %b | 缩写月名 | %p | AM 或 PM |
| %c | 月，数值 | %r | 时间，12小时制（hh:mm:ss AM 或 PM） |
| %d | 带有英文前缀的月中的天 | %s | 秒(00-59) |
| %e | 月的天，数值(00-31) | %S | 秒(00-59) |
| %f | 月的天，数值(01-31) | %T | 时间，24小时制（hh:mm:ss） |
| %F | 微秒 | %U | 周(00-53) 星期一是一周的第一天 |
| %H | 小时(00-23) | %u | 周(00-53) 星期一是一周的第一天 |
| %h | 小时(01-12) | %V | 周(01-53) 星期一是一周的第一天，与 %X 使用 |
| %I | 小时(01-12) | %v | 周(01-53) 星期一是一周的第一天，与 %x 使用 |
| %i | 分钟，数值(00-59) | %W | 星期名 |
| %j | 年的天(001-366) | %w | 周的天（0=星期日, 6=星期六） |
| %k | 小时(0-23) | %X | 年，其中的星期一是周的第一天，4位，与 %V 使用 |
| %l | 小时(1-12) | %x | 年，其中的星期一是周的第一天，4位，与 %v 使用 |
| %M | 月名 | %Y | 年，4位 |
条件函数
根据条件返回不同的值
-- IF：简单条件判断
SELECT name,
score,
IF(score >= 60, '及格', '不及格') AS result
FROM stuscore;
-- CASE WHEN：多条件判断
SELECT name,
score,
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS grade
FROM stuscore;
-- IFNULL：处理NULL值
SELECT name,
IFNULL(email, '未填写') AS email
FROM student;
算术运算符
基本的数学运算
| 运算符 | 说明 | 示例 |
|---|---|---|
| + | 加法 | price + tax |
| - | 减法 | total - discount |
| * | 乘法 | quantity * price |
| / | 除法 | total / count |
| % | 取余 | number % 2 |
-- 计算总价
SELECT product_id, total_amount, quantity, total_amount * quantity AS total_price FROM orders;
多表关联查询
数据库是存储数据集合的容器，一般数据库中都存在多个表，处理多个表的查询涉及到关联和嵌套，一个表中的数据是同一种类型的数据集合，数据库中的每个表都有唯一名字来标识。描述表的元信息就是所谓的模式schema，模式用来描述数据库中特定的表，包括表的字段名，数据类型，约束关系等。
多表查询
从多个表查询数据，一次查询多个表的数据，查询的结果也是一个二维表，这种多表查询又称笛卡尔查询，假如两个表查询，表一有M行记录，表二有N行记录，则多表查询返回记录数为M*N行。SELECT * FROM <表1> <表2>
SELECT * FROM student,course;
由于返回的列名有相同的，我们可以使用以下别名策略： SELECT s.no sno, s.name sname, s.age,s.sex,s.department,c.no cno,c.name coursename, c.hours hours FROM student s,course c;
Union组合查询
利用 UNION可对多条SELECT 语句，将它们的结果组合成一个结果集。UNION 必须由两条或两条以上的 SELECT 语句组成; UNION每个查询必须包含相同的列、表达式或聚集函数; 列数据类型必须兼容，类型不必完全相同，但必须是数据库可以自动转换的类型; UNION会从查询结果集中自动去除重复的行。如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION。
SELECT * FROM student WHERE sex='男';
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE sex='男' UNION SELECT * FROM student WHERE age > 20;
关联查询
关系数据库是创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据，现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系模型本质上就是若干个二维表，类似Excel的多个sheet。 主键是指能够唯一标识表中每一行的一列或者一组列，主键不允许为NULL。对于关系表，有个很重要的约束，就是任意两条记录不能重复，最好不使用任何业务相关的字段作为主键。关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。主键的作用是保证唯一性。 外键：外键并不是通过列名实现的，而是通过定义外键约束实现的，可以把数据与另一张表关联起来，这种列称为外键。存在一对多，多对多，一对一的关系。表的外键是另一表的主键, 外键可以有重复的, 可以是空值。外键的作用是用于和其他表进行关联。 关联查询主要基于关系模型，依赖于主键和外键处理。SQL 最强大的功能之一就是能在数据查询的执行中关联查询join，Excel中我们都知道vlookup，创建关联查询需要指定要关联的所有表以及关联它们的方式。关联方式有内关联(inner join)，左外连接( left outer join)，右外连接(right outer join)，全外连接(full outer join)。
-- 获取所有选择C01数据库课程学生信息以及课程成绩
SELECT s.*,c.cno,c.grade FROM student s INNER JOIN coursegrade c ON s.no = c.sno WHERE c.cno='c01';
-- 内连接查询
SELECT s.*,c.cno,c.grade FROM student s INNER JOIN coursegrade c ON s.no = c.sno;
-- 默认连接查询和上面查询效果一样，平时我们用的更多的是这个用法。
SELECT s.*,c.cno,c.grade FROM student s, coursegrade c WHERE c.sno=s.no;
-- 左外连接，发现比内连接多了两条数据，cno为空的数据。
SELECT s.*,c.cno,c.grade FROM student s LEFT OUTER JOIN coursegrade c ON s.no = c.sno;
-- 三表关联获取所有所有学生选课的所有成绩
SELECT s.*,c.*,sc.grade FROM student s,course c, coursegrade sc WHERE s.no=sc.sno AND c.no=sc.cno;
子查询和嵌套
sql允许创建子查询，即嵌套查询，一个查询语句(select-from-where)在另外的查询语句中使用，子查询的select查询总是使用圆括号括起来。子查询可以返回一个值或者一组值，用于前面查询计算或者过滤。
where in 子查询
in关键字用于where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足条件的记录。查询所有数据库课程大于80分的学生基本信息； 查询数据库课程的ID 获取数据库课程ID大于80分的学生ID 根据学生ID获取相关信息
SELECT NO,NAME FROM course WHERE NAME='数据库';
SELECT sno,grade FROM coursegrade WHERE cno='C01' ANDgrade>80;
SELECT * FROM student WHERE NO IN(9512101,9512102);
SELECT * FROM student WHERE NO IN(SELECT sno FROM coursegrade WHERE cno='C01' AND grade>80 );
作为计算字段的子查询
作为计算或者结果的一部分使用子查询
SELECT NAME,department, (SELECT SUM(grade) FROM coursegrade WHERE coursegrade.sno=student.no) AS course FROM student;
where EXISTS子查询
EXISTS是sql中的逻辑运算符号，如果子查询有结果集返回，那么就为True，EXISTS代表“存在”的意义，它只查找满足条件的那些记录，一旦找到第一个匹配的记录后，就马上停止查找。查询如果存在不及格学生则返回所有学生信息；
SELECT * FROM student WHERE EXISTS (SELECT * FROM coursegrade WHERE grade<60);
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
where any all子查询
all是sql中的逻辑运算符好，如果一系列的比较都为true，那么结果才能为true。返回的结果集为多个的子查询，为多行子查询，多行子比较符有 IN(等于列中任意一个)、ANY(和子查询返回的某个值比较)，ALL(和子查询返回的所有值比较)。查询其他课程中比数据库课程的所有分数低的学生的学号、姓名、课程名和分数。
-- 获取数据库课程的所有分数：
SELECT grade FROM coursegrade WHERE coursegrade.cno = 'C01';
-- 获取比所有数据库课程分数低的学生的信息：
SELECT NO, NAME, department, coursegrade.grade FROM student, coursegrade WHERE (student.no=coursegrade.sno) AND coursegrade.grade < ALL(SELECT grade FROM coursegrade WHERE coursegrade.cno= 'C01' );
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。
where运算符子查询
子查询作为where运算中表达式的一部分，比如查询平均成绩比‘吴斌’大的学生id，姓名，平均成绩等信息
SELECT AVG(grade) FROM student s JOIN coursegrade g ON s.no= g.sno WHERE s.name = '吴斌';
SELECT NO, NAME, AVG(grade) FROM student s, coursegrade c WHERE s.no = c.sno GROUP BY NO HAVING AVG(grade) > (SELECT AVG(grade) FROM student s JOIN coursegrade g ON s.no = g.sno WHERE s.name = '吴斌');
多个子查询联合使用
查询数据库成绩比数学成绩高的学生的信息和相关分数：
先找两门课程成绩
关联查询，数据库成绩比数学成绩高
根据Id获取相关信息和分数
SELECT * FROM student RIGHT JOIN(SELECT t1.sno,class1,class2 FROM (SELECT sno,grade AS class1 FROM coursegrade WHERE cno = 'c01') AS t1, (SELECT sno,grade AS class2 FROM coursegrade WHERE cno = 'c02') AS t2 WHERE t1.sno = t2.sno AND t1.class1 > t2.class2) re ON student.no =re.sno;
使用子查询的好处：具体数据改变不需要改语句，容易维护。坏处效率会低，而且随着语句的增长可读性不强，不建议经常使用。