SQL 学习笔记

SQL 语句使用 UNION A#L从 "Websites" 和 "apps"

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
表中选取所有的country（也有重复的值）：

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

• 如果没有all 会去掉重复，只筛选不同的部分 *

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SQL SELECT INTO 语句

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

SQL SELECT INTO 语法

• 我们可以复制所有的列插入到新表中：

SELECT *
INTO newtable [IN externaldb]
FROM table1;

或者只复制希望的列插入到新表中：

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

SQL SELECT INTO 实例

• 创建 Websites 的备份复件：

SELECT *
INTO WebsitesBackup2016
FROM Websites;

• 只复制一些列插入到新表中：

SELECT name, url
INTO WebsitesBackup2016
FROM Websites;

• 只复制中国的网站插入到新表中：

SELECT *
INTO WebsitesBackup2016
FROM Websites
WHERE country='CN';

• 复制多个表中的数据插入到新表中：

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

提示：SELECT INTO

• 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可：

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

SQL 通配符 的使用

• MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

• 下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

[ 实例 ]
SQL 语句选取 name 不以 A 到 H 字母开头的网站：

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

使用 SQL % 通配符

下面的 SQL 语句选取 url 以字母 "https" 开始的所有网站：

SELECT * FROM Websites
WHERE url LIKE 'https%';

SQL 正则表达式

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

• 如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。*

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

不同类型的SQL联接

• 内连接（INNER JOIN）：当两个表中都存在匹配时，才返回行。
• 左连接（LEFT JOIN）：返回左表中的所有行，即使右表中没有匹配的行。
• 右连接（RIGHT JOIN）：返回右表中的所有行，即使左表中没有匹配的行。
• 全连接（FULL JOIN）：只要某一个表存在匹配，就返回行。
• 笛卡尔连接（CARTESIAN JOIN）：返回两个或者更多的表中记录集的笛卡尔积。

通配符的使用

WHERE SALARY LIKE '200%' 找出任何以 200 开头的值。
WHERE SALARY LIKE '%200%' 找出任何存在 200 的值。
WHERE SALARY LIKE '00%' 找出任何第二个位置和第三个位置为 0 的值。
WHERE SALARY LIKE '2%_%' 找出任何以 2 开始，并且长度至少为 3 的值。
WHERE SALARY LIKE '%2' 找出任何以 2 结尾的值。
WHERE SALARY LIKE '_2%3' 找出任何第二个位置为 2，并且以 3 结束的值。
WHERE SALARY LIKE '2___3' 找出任何以 2 开始，以 3 结束的五位数。

函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

有用的 Aggregate 函数：

• AVG() - 返回平均值
• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

有用的 Scalar 函数：

• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入
• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

SQL 日期函数

名称 描述

• ADDDATE() 增加日期
• ADDTIME() 增加时间
• CONVERT_TZ() 将当前时区更改为另一时区
• CURDATE() 返回当前日期
• CURRENT_DATE(), CURRENT_DATE CURDATE() 的别名
• CURRENT_TIME(), CURRENT_TIME CURTIME() 的别名
• CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP NOW() 的别名
• CURTIME() 返回当前时间
• DATE_ADD() 将两个日期相加
• DATE_FORMAT() 按照指定格式格式化日期
• DATE_SUB() 将两个日期相减
• DATE() 从 date 或者 datetime 表达式中提取出日期部分
• DATEDIFF() 将两个日期相减
• DAY() DAYOFMONTH() 的别名
• DAYNAME() 返回某天在用星期中的名称
• DAYOFMONTH() 返回某天是当月的第几天 （1-31）
• DAYOFWEEK() 返回某天是该星期的第几天
• DAYOFYEAR() 返回某天是一年中的第几天（1-366）
• EXTRACT 提取日期中的某一部分
• FROM_DAYS() 将天数转换为日期
• FROM_UNIXTIME() 将某个日期格式化为 UNIX 时间戳
• HOUR() 提取小时
• LAST_DAY 返回参数日期所在月份的最后一天
• LOCALTIME(), LOCALTIME NOW() 的别名
• LOCALTIMESTAMP, LOCALTIMESTAMP() NOW() 的别名
• MAKEDATE() 利用年份和某天在该年所处的天数来创建日期
• MAKETIME MAKETIME()
• MICROSECOND() 由参数返回微秒
• MINUTE() 由参数返回分钟
• MONTH() 返回日期参数的月份
• MONTHNAME() 返回月份的名字
• NOW() 返回当前日期和时间
• PERIOD_ADD() 向年月格式的日期数据之间添加一段时间
• PERIOD_DIFF() 返回两个年月格式的日期数据之间的月份数
• QUARTER() 返回日期参数所在的季度
• SEC_TO_TIME() 将秒数转换为 'HH:MM:SS' 格式
• SECOND() 返回参数中的秒数 (0-59)
• STR_TO_DATE() 将字符串转换为日期数据
• SUBDATE() 以三个参数调用的时候是 DATE_SUB() 的同义词
• SUBTIME() 减去时间
• SYSDATE() 返回函数执行的时的时刻
• TIME_FORMAT() 格式化时间
• TIME_TO_SEC() 将时间参数转换为秒数
• TIME() 返回参数表达式中的时间部分
• TIMEDIFF() 将两个时间相减
• TIMESTAMP() 只有一个参数时，该函数返回 date 或者 datetime 表达式。当有两个参数时，将两个参数相加。
• TIMESTAMPADD() 在 datetime 表达式上加上一段时间
• TIMESTAMPDIFF() 在 datetime 表达式上减去一段时间
• TO_DAYS() 将日期参数转换为天数
• UNIX_TIMESTAMP() 返回 UNIX 时间戳
• UTC_DATE() 返回当前 UTC 日期
• UTC_TIME() 返回当前 UTC 时间
• UTC_TIMESTAMP() 返回当前 UTC 日期和时间
• WEEK() 返回参数的星期数
• WEEKDAY() 返回日期参数时一个星期中的第几天
• WEEKOFYEAR() 返回日期参数是日历上的第几周 (1-53)
• YEAR() 返回日期参数中的年份
• YEARWEEK() 返回年份和星期