该系列文章系个人读书笔记及总结性内容,任何组织和个人不得转载进行商业活动!
至此,SQL的入门即将结束,后续会阅读《高性能MySQL》,以期进一步的了解和掌握数据库的知识;
以下是一些值得关注的点,要么是内容单独成篇,要么是需要熟练掌握,记录在此,方便查阅;
1.保留字和特殊字符
SQL语言由许多保留字(关键字)组成,起名的时候最好避开;
特殊字符:
命名是最好不要使用特殊字符,下划线除外;
*:从SELECT语句中返回表的所有列;
():用于集合一组表达式、指定执行数学运算的顺序以及调用函数;也用于围起子查询;
;:表示SQL语句的结束;
,:分隔各个列表项;出现在INSERT语句与IN子句中;
.:用于引用表名以及表示浮点数;
_:在LIKE子句中代表一个字符的通配符;
%:在LIKE子句中代表多个字符的通配符;
!:感叹号等于NOT,用于WHERE子句的比较条件中;
‘:一对单引号,告诉SQL其中的内容是字符串;
“:一对双引号也有单引号的功能,不过最好使用单引号;
\:可于表内存储作为文字用的单引号字符;
+:除了表示加法,加号也能用于连接或串联两个字符串;
2.ANY、ALL和SOME
这三个关键字在子查询中都非常好用;
他们可以与比较运算符和结果集一起使用;
我们新建一个实例数据库表:
mysql> create database hq_aas;
mysql> use hq_aas;
mysql>
mysql> CREATE TABLE stu_ranking
-> (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> rank_num INT NOT NULL
-> );
mysql> INSERT stu_ranking
-> (name,rank_num)
-> VALUES
-> ('A',4),
-> ('B',7),
-> ('C',1),
-> ('D',3),
-> ('E',5),
-> ('F',2),
-> ('G',6);
mysql> SELECT * FROM stu_ranking;
+----+------+----------+
| id | name | rank_num |
+----+------+----------+
| 1 | A | 4 |
| 2 | B | 7 |
| 3 | C | 1 |
| 4 | D | 3 |
| 5 | E | 5 |
| 6 | F | 2 |
| 7 | G | 6 |
+----+------+----------+
mysql> SELECT name, rank_num
-> FROM stu_ranking
-> WHERE rank_num IN
-> (SELECT rank_num
-> FROM stu_ranking
-> WHERE rank_num > 3 AND rank_num < 6);
查询结果:
+------+----------+
| name | rank_num |
+------+----------+
| A | 4 |
| E | 5 |
+------+----------+
使用ALL:
任何大于集合中最大值的的值都将匹配;
mysql> SELECT name, rank_num
-> FROM stu_ranking
-> WHERE rank_num > ALL
-> (SELECT rank_num
-> FROM stu_ranking
-> WHERE rank_num > 3 AND rank_num < 6);
+------+----------+
| name | rank_num |
+------+----------+
| B | 7 |
| G | 6 |
+------+----------+
任何大于集合或者等于集合中最高结果集的值都将匹配;
mysql> SELECT name, rank_num
-> FROM stu_ranking
-> WHERE rank_num >= ALL
-> (SELECT rank_num
-> FROM stu_ranking
-> WHERE rank_num > 3 AND rank_num < 6);
+------+----------+
| name | rank_num |
+------+----------+
| B | 7 |
| E | 5 |
| G | 6 |
+------+----------+
ALL如果结合小于号使用,对应比较的就是集合中的最小值;
简单理解为:ALL表示对集合中的所有值都满足的匹配条件;
使用ANY:
任何大于集合中最小值的值都将匹配;
排名高于4、5中的任何一个均可;
mysql> SELECT name, rank_num
-> FROM stu_ranking
-> WHERE rank_num > ANY
-> (SELECT rank_num
-> FROM stu_ranking
-> WHERE rank_num > 3 AND rank_num < 6);
+------+----------+
| name | rank_num |
+------+----------+
| B | 7 |
| E | 5 |
| G | 6 |
+------+----------+
ANY结合小于号使用,对应的就是小于集合中的最大值;
简单理解为:ANY表示集合中任意一个值满足的匹配条件;
使用SOME:
标准SQL语法中,SOME和ANY一样;
3.再谈数据类型
BOOLEAN:
只能存储’true' ‘false' 或 NULL;
RDBMS实际上是用1存储’true’,0存储’false’;
INT:
是指无符号整数;
INT(SIGNED)表示有符号整数;
DATE与TIME类型:
MySQL存储日期与实践数据类型的格式:
DATE:YYYY-MM-DD
DATETIME:YYYY-MM-DD HH:MM:SS
TIMESTAMP:YYYYMMDDHHMMSS
TIME:HH:MM:SS
选取时间类型的数据时,其实可以调整RDBMS返回的格式:
格式的字符串需要加上引号;
可以查询具体的格式字符串的含义;
Syntax:
DATE_FORMAT(date,format)
Examples:
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
4.临时表
我们已经创建的表,RDBMS都会存储表的结构以及存储在表中的数据;
在退出SQL session时,表和数据仍然会存在,除非被删除或卸载;
需要临时表的理由:
1)可用于保存中间结果;如某数学运算的结果暂时不会用到,而是会在后续的session中用到;
2)捕获某个时刻的表内容;
3)帮助重新整理数据的结构;
4)在SQL结合其他编程语言时,可以在收集数据时创建临时表,然后把最终结果存储在永久性表中;
创建临时表:
和创建表类似,只不过加上了关键字TEMPORARY;
mysql> show tables;
+------------------+
| Tables_in_hq_aas |
+------------------+
| stu_ranking |
+------------------+
mysql> CREATE TEMPORARY TABLE tep_my_table AS
-> SELECT name, rank_num
-> FROM stu_ranking
-> WHERE rank_num > SOME
-> (SELECT rank_num
-> FROM stu_ranking
-> WHERE rank_num > 3 AND rank_num < 6);
mysql> show tables;
+------------------+
| Tables_in_hq_aas |
+------------------+
| stu_ranking |
+------------------+
mysql> SELECT * FROM tep_my_table;
+------+----------+
| name | rank_num |
+------+----------+
| B | 7 |
| E | 5 |
| G | 6 |
+------+----------+
5.转换数据类型
如果希望查询是另外一种数据类型,SQL的CAST()函数可以转换数据类型:
CAST(expr AS type)
type可以是:CHAR()、DATE、DATETIME、DECIMAL、SIGNED、TIME、UNSIGNED;
示例:
mysql> SELECT CAST('2018-10-10' AS DATE);
+----------------------------+
| CAST('2018-10-10' AS DATE) |
+----------------------------+
| 2018-10-10 |
+----------------------------+
mysql> SELECT CAST(2 AS DECIMAL);
+--------------------+
| CAST(2 AS DECIMAL) |
+--------------------+
| 2 |
+--------------------+
在INSERT语句和SELECT选取列的列表中均可使用;
6.你是谁?现在几点?
确定当前使用的账号:
mysql> SELECT CURRENT_USER;
+----------------+
| CURRENT_USER |
+----------------+
| root@localhost |
+----------------+
使用的是本地的RDBMS,用户时根用户;
获取当前的时间与日期:
mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2018-10-11 |
+--------------+
mysql> SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 17:00:42 |
+--------------+
7.有用的数字函数
ABS(x):取绝对值;
ACOS(x):返回x的反余弦值;
ASIN:反正弦;
ATAN(x,y):返回x与y的反正切值;
CEIL(x):返回大于等于x的最小整数,返回值为BIGINT;
SIN(x):正弦值;
TAN(x):正切值;
COS(x):x的余弦值;
COT(x):x的余切;
EXP(x):返回e的x次方;
FLOOR(x):返回小于等于x的最大整数;
FORMAT(x,y):转换x为文本字符串并四舍五入至y指定的位数,数字3点1逗处理;
LN(x):返回x的自然对数;
LOG(x)与LOG(x,y):返回x的自然对数,若有两个参数,则以x为基数,返回y的对数;
MOD(x,y):返回x除以y的余数;
PI():返回PI;
POWER(x,y):返回x的y次方值;
RADIANS(x):返回x从角度转换成弧度的值;
RAND():返回随机浮点数;
ROUND(x):返回x四舍五入后最近的整数;
ROUND(x,y):以y指定的小数位数对x四舍五入;
SIGN(x):正数时返回1 负数-1 0返回0;
SQRT(x):x的平方根;
TRUNCATE(x,y):返回x截断至y指定的小数位数后的值;
8.索引能加快速度
简单的索引理论:
我们已经知道主键与外键索引,这些索引很适合连接多张表及强化数据的完整性;
此外,我们还可以对列创建索引来加快查询速度;
对于没有索引的列,RDBMS需要从列的开始入手,逐一读取数据;
创建索引之后,RDBMS会持有额外信息,以加快搜索速度;
额外信息存储在幕后的表中,具有特殊顺序;
搜索会比较快,代价是会占用额外的空间;
所以,创建索引的对象必须在常用的列上;
为列添加索引:
mysql> ALTER TABLE stu_ranking
-> ADD INDEX(name);