Sql必知必会笔记
Sql必知必会笔记
文章目录
常规CRUD
增加(Create) INSERT INTO 表名(列名) VALUES ();
查询(Retrieve) SELECT 列名 FROM 表名;
更新(Update) UPDATE 表名 SET 列名="" WHERE 条件;
删除(Delete) DELETE FORM 表名 WHERE 条件;
子句
排序子句 ORDER BY
在指定一条ORDER BY子句时,应保证它是SELET语句中的最后一条子句,该子句次序不对会报错
OREDER BY 子句的特性
- 可以按非检索列排序 SELECT a,b FROM table ORDER BY c ;
- 可以按多个列排序 SELECT a,b,c FROM table ORDER BY a,b;
- 可以按列位置排序 SELECT a,b FROM table ORDER BY 2,3;
- 可以升序(A-Z)或降序(Z-A),默认是升序,降序加DESC。PS:哪个是降序就在那个列后面加上DESC,升序是ASC,但不用加 SELECT a,b FROM table ORDER BY c DESC
条件子句 WHERE子句
普通条件子句:SELECT 列名 FROM 表名 WHERE 列名 = “”;
WHERE子句操作符
PS:sql语句中的单引号用来限定字符串,如果将值与串类型的列进行比较,则需要限定引号,用来与数值列作比较的值不用引号
使用BETWEEN操作符的WHERE子句: SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 1 AND 10;
空值(null)检查: SELECT 列名 FROM 表名 WHERE 列名 IS NULL;
高级搜索子句 组合WHERE子句
AND和OR操作符
通过AND或OR连接多个查询条件
SELECT a,b FROM table WHERE a = 1 AND b = 1;
SELECT a,b FROM table WHERE a = 1 OR b = 1;
PS:sql语句在处理OR操作符前,优先处理AND操作符,此时应当使用()来明确的分组相应的操作符
SELECT a,b,c FROM table WHERE (a = 1 OR b = 2) AND c = 5 ;
IN操作符
IN操作符用来指定条件范围,范围中每个条件都可以进行匹配
SELECT a,b FROM table WHERE a IN(1,2,3);
IN操作符完成与OR相同的功能,它的优点如下:
NOT操作符
WHERE子句中的NOT操作符有且只有一个功能,那就是否定它之后所跟的任何条件,因为NOT从不自己使用
SELECT a,b FROM table WHERE NOT a = 1 ORDER BY b;
PS:MySQL中不支持这种NOT的格式,在MySQL中,NOT只用来否定EXISTS(如NOT EXISTS)
通配符
LIKE操作符
通配符本身实际是SQL的WHERE子句中有特殊含义的字符,在搜索子句中使用通配符,必须使用LIKE操作符。LIKE指示DBMS,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
通配符搜索只能用于文本字段(串),非文本数据类型字段不能使用通配符搜索。
%通配符
在搜索串中,%代表任何字符出现任意次数
SELECT a,b FROM table WHERE a LIKE ‘董%’;
SELECT a,b FROM table WHERE a LIKE ‘%迎%’;
SELECT a,b FROM table WHERE a LIKE ‘%港’;
PS: 除了一个或多个字符外,%还能匹配0个字符。
_通配符
下划线只匹配单个字符而不是多个字符
SELECT a,b FROM table WHERE a LIKE ‘董__’;
[]通配符
方括号([])通配符用来指定一个字符集,他必须匹配指定位置的一个字符。
SELECT a,b FROM table WHERE a LIKE ‘[董王]%’;
此通配符可以用前缀^(脱字号)来否定
SELECT a,b FROM table WHERE a LIKE ‘[^董王]%’;
并不是所有的DBMS都支持用来创建集合的[]。
通配符使用技巧
创建计算字段
拼接字段
SELECT CONCAT(id,name) AS ‘id+name’ FROM t_demo ORDER BY id;
使用列别名(alias)
SELECT id,name AS realName FROM t_demo ORDER BY id ;
执行算术计算
SELECT id,name,price,quantity,CONCAT(price*quantity) AS totalprice FROM t_demo ORDER BY id;
使用函数
文本处理函数
函数名 | 功能 | 例句 |
---|---|---|
UPPER(str) | 大写 | SELECT name,UPPER(name) FROM t_demo; |
LOWER(str) | 小写 | SELECT name,LOWER(name) FROM t_demo; |
LEFT(str,len) | 返回字符串左边的字符 | SELECT name,LEFT(name,2) FROM t_demo; |
LENGTH(str) | 返回字符串长度 | SELECT name,LENGTH(name) FROM t_demo ; |
TRIM(str) | 去除空格 | SELECT name,LENGTH(name) FROM t_demo ; |
SOUNDEX(str) | 返回SOUNDEX值 | SELECT name,SOUNDEX(name) FROM t_demo ; |
其中SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节,使得对字符串进行发音比较而不是字母比较。虽然,soundex不是SQL概念,但是多数DBMS都提供对soundex的支持。
示例:SELECT name,SOUNDEX(name) FROM t_demo WHERE SOUNDEX(name) = SOUNDEX(‘nama’);
日期处理函数
以MySQL为例,常用日期处理函数如下:
函数名 | 作用 | 例句 |
---|---|---|
NOW() | 获取当前时间 | SELECT NOW(); |
SYSDATE() | 获得当前时间 | SELECT NOW(),SLEEP(3),SYSDATE(); |
CURRENT_TIMESTAMP() | 获取当前时间戳 | SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(); |
DATE_FORMAT(str,format) | 日期格式化 | SELECT DATE_FORMAT(‘2008-08-08 22:23:01’, ‘%Y/%m/%d %H:%i:%s’); |
TIME_FORMAT(str,format) | 时间格式化 | SELECT TIME_FORMAT(‘22:23:01’, ‘%H:%i:%s’); |
STR_TO_DATE(str,format) | 字符串转日期 | select str_to_date(‘08.09.2008 08:09:30’, ‘%m.%d.%Y %h:%i:%s’); |
TO_DAYS(DATA) | (日期、天数)转换 | SELECT TO_DAYS(‘2018-01-07’); |
TIME_TO_SEC(time) | (时间、秒)转换 | select time_to_sec(‘01:00:05’); – 3605 |
SEC_TO_TIME(sec) | (时间、秒)转换 | select sec_to_time(3605); – ‘01:00:05’ |
MAKEDATE(year,dayofyear) | 拼凑日期 | select makedate(2001,31); – '2001-01-31’select makedate(2001,32); – ‘2001-02-01’ |
MAKETIME(hour,minute,second) | 拼凑时间 | select maketime(12,15,30); – ‘12:15:30’ |
UNIX_TIMESTAMP() | Unix时间戳 | SELECT unix_timestamp(); |
UNIX_TIMESTAMP(data) | 指定日期的时间戳 | SELECT unix_timestamp(‘2019-01-17’); – ‘1547654400’ |
FORM_UNIXTIME(unix_timestamp) | 根据时间戳返回日期 | SELECT FROM_UNIXTIME(1547654400); – ‘2019-01-17 00:00:00’ |
FORM_UNIXTIME(unix_timestamp,formate) | 根据时间戳和格式返回日期 | SELECT FROM_UNIXTIME(1547654400,’%Y/%m/%d’); – ‘2019/01/17’ |
数值处理函数
汇总数据
聚集函数
指运行在行组上,计算和返回单个值的函数。
函数名 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
PS:COUNT(*)计算空值,COUNT(column)不计算NULL值
###聚焦不同值
上述5个聚集函数都可以如下使用:
- 对所有的行执行计算,指定ALL参数或不给参数(因为ALL是默认行为)
- 只包含不同的值,指定DISTINCT参数
例:SELECT AVG(DISTINCT price) FROM t_demo;
组合聚集函数
SELECT COUNT(*) AS items,
AVG(price) AS avg_price,
MAX(price) AS max_price,
MIN(price) AS min_price
FROM t_demo
分组数据
涉及到两个新的SELECT语句子句,分别是GROUP BY子句和HAVING子句。
数据分组 GROUP BY
分组求和 SELECT name, COUNT(*) AS items,SUM(quantity) AS total_quantity FROM t_demo GROUP BY name
GROUP BY可以有多个条件: SELECT name, COUNT(*) AS items,SUM(quantity) AS total_quantity FROM t_demo GROUP BY name,price
过滤分组 HAVING
过滤:SELECT name, COUNT(*) AS items,SUM(quantity) AS total_quantity FROM t_demo GROUP BY name,price HAVING items > 1
HAVING和WHERE的区别
SELECT子句顺序
使用子查询
利用子查询进行过滤
SELECT id,test FROM t_test WHERE id IN (SELECT id FROM t_demo WHERE name = ‘Name1’)
在SELECT语句中,子查询总是由内向外处理。
据查,子查询嵌套在7层之内是可行的,大于7之后会出现问题。
作为子查询的SELECT语句只能查询单个列,企图检索多个列将返回错误。
使用子查询并不总是执行数据检索最有效的方法。
作为计算字段使用子查询
可以将子查询结果变为最终查询结果的一个列值。
联结表
简单来说,联结是一种机制,用来在一条SELECT语句中关联表,因此称之为联结。
连接的创建
最简单的联结:
SELECT name,price,quantity,test FROM t_demo,t_test WHERE t_demo.id = t_test.id;
完全限定列名:在引用的列可能出现二义性时,必须使用完全限定列名。
SELECT name,price,quantity,test FROM t_demo,t_test;
笛卡尔积:由没有联结条件的表关系返回的结果为笛卡尔积,检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
内部联结
目前为止所用的联结称为等值联结(equijoin),它基于两个表之间的相等测试,这种联结也称为内部联结。这种联结可以使用稍微不同的语法来明确指定联结的类型。
SELECT name,price,quantity,test FROM t_demo INNER JOIN t_test ON t_demo.id = t_test.id;
上述SELECT和之前用WHERE子句联结得到的效果一样。
联结多个表
SQL对一条SELECT语句中可以联结的表的数目没有限制。
性能:DBMS在运行时关联指定的每个表以处理联结,这种处理可能是非常耗费资源的,因此不要联结不必要的表。联结的表越多,性能下降越厉害。
创建高级联结
使用表别名
SELECT name,price,quantity,test FROM t_demo as d INNER JOIN t_test AS t ON d.id = t.id;
表别名不只能用于WHERE子句,也可以用于SELECT列表、ORDER BY子句等。
Oracle中没有AS,Oracle不支持AS关键字,应该是t_demo d,t_test t。
使用不同类型的联结
自联结
使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。
例:想发一封信给张三所在部门的所有员工。(部门未知且应当唯一)
SELECT t1.name,t1.price,t1.quantity FROM t_demo AS t1,t_demo AS t2 WHERE t1.name =t2.name AND t2.quantity = 4;
上述语句在quantity=4唯一时,等价于 SELECT name,price,quantity FROM t_demo WHERE name = (SELECT name FROM t_demo WHERE quantity =4);
自联结与子查询的效率问题:
SELECT permission_name FROM t_permission WHERE parent_id in (
SELECT permission_id FROM t_permission WHERE parent_id in(
SELECT permission_id FROM t_permission WHERE parent_id = 0));
Time: 0.104 0.085 0.088 0.412 0.384
SELECT t1.permission_name FROM t_permission AS t1,t_permission AS t2 WHERE
t1.parent_id = t2.parent_id
AND t2.parent_id IN(
SELECT t3.permission_id FROM t_permission AS t3 WHERE parent_id = 0);
Time: 0.089ms 0.090ms 0.090ms 0.092ms 0.090ms
SELECT t1.permission_name FROM t_permission AS t1,t_permission AS t2,t_permission AS t3 WHERE
t1.parent_id = t2.parent_id
AND t2.parent_id = t3.parent_id
AND t3.parent_id =0;
Time: 0.281ms 0.715ms 2.751ms 0.547ms 0.378ms
实际应用中应当以实测为准。
外部联结
许多联结将一个表中行与另一个表中的行相关联,但有时候会需要包含没有关联行的那些行,这就是外部联结。例如:列出所有产品以及订购数量,包括没有人订购的产品。
在使用外部联结时,必须使用RIGHT或LEFT关键字指定包括其所有行的表
左联结:SELECT d.id,d.name,t.test FROM t_demo d LEFT JOIN t_test t ON d.id = t.id;
右联结:SELECT d.id,d.name,t.test FROM t_demo d RIGHT JOIN t_test t ON d.id = t.id;
左联结是包括左表的所有记录,右联结则包括右表的所有记录。
PS:笛卡尔积(交叉联结CROSS JOIN)属于内连接
组合查询
SQL允许执行多个查询并将结果作为单个查询结果集返回,这些组合查询通常称为并(union)或复合查询(compound query)。
使用UNION
在两个SELECT语句之间加上UNION就能够达成多个查询返回一个结果集的效果。和OR、AND比较类似?
UNION在使用时会自动将重复行给取消,这是UNION的默认行为,如果愿意,可以使用UNION ALL。
PS:在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后。
数据插入
插入操作主要有一下三种方式:
- 插入完整的行
- 插入行的一部分
- 插入某些查询的结果
插入完整的行
INSERT INTO t_test VALUES(7,'id为7');
这种方式并不安全,高度依赖表中列的定义次序。
INSERT INTO t_test(id,test) VALUES(7,'id为7');
上面这条语句和之前的INSERT语句完成了相同的工作,但是在表名后加上了列名。
插入部分行
INSERT INTO t_demo(id,name) VALUES(7,'Name7');
被省略的列必须满足以下某个条件:
- 该列定义为允许NULL值(无值或空值)
- 在表定义中给出默认值,这表示如果不给出值,将使用默认值
如果对表中不允许NULL值且没有默认值的列不给出值,则DBMS将产生一条错误信息,并且相应的行插入不成功。
插入检索出的数据
INSERT INTO t_demoNew(id,name) SELECT id,name FROM t_demo;
SELECT语句从t_demo中检索出要插入的值,而不是列出它们。
在例句中,INSEERT和SELECT语句中是用了相同的列名。实际上,DBMS不关心SELECT返回的列名,它使用的是列的位置。
INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。
从一个表复制到另一个表
新表未创建:CREATE TABLE t_testCopy (SELECT * FROM t_test);
更新和删除数据
更新数据
UPDATE语句通常包括三部分:
- 要更新的表
- 列名和它们的新值
- 确定要更新哪些行的过滤条件
更新单个列:UPDATE t_test SET test = 'id为8' WHERE id=7;
更新多个列:UPDATE t_demo SET name = 'name7',price=5,quantity=6 WHERE id=7;
删除数据
DELETE语句一定不要省略WHERE子句,否则会删除整个表。
DELETE FROM t_testCopy WHERE id=7;
DELETE不需要列名或通配符,DELETE删除整行而不是删除列,为了删除指定的列,请使用UPDATE语句。
删除表中所有行:TRUNCATE TABLE t_testcopy;
TRUNCATE TABLE 语句速度更快,因为它不记录数据变动。
创建和操纵表
创建表
CREATE TABLE t_demo(
id INT(10) DEFAULT NULL,
name VARCHAR(100) NOT NULL
);
创建新表时,指定表名必须不存在,否则将出错。
DROP TABLE IF EXISTS `t_createDemo`;
在建表语句前加上DROP TABLE语句能够将原有的重名表删去。
使用NULL值
建表时,NULL为默认设置(即允许空值,如果需要列不为空,需要用NOT NULL 标识)。
主键和NULL值
理解NULL值
指定默认值
SQL允许指定默认值,在插入行时如果不给出值,DBMS将自动采用默认值。
DROP TABLE IF EXISTS `t_createDemo`;
CREATE TABLE t_createDemo(
id int(10) DEFAULT 1,
name varchar(100) NOT NULL DEFAULT "董",
caeateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP()
);
在MYSQL中对于日期相关类只能给timestamp类的字段设置默认值,默认值为now()或current_timestamp()。、
参考资料:MYSQL日期类型及默认设置
更新表 ALTER
加字段:ALTER TABLE t_createdemo ADD test char(20);
删字段:ALTER TABLE t_createdemo DROP COLUMN test;
在MySQL中,即使表中字段中有值,删除语句也能够成功删除,因此需要小心使用。
删除表
删除整个表而不是内容: DROP TABLE t_createdemo;
删除表没有确认也没有撤销,执行就会永久删除该表。慎用
重命名表
在MySQL中有两种方式
ALTER TABLE t_createdemo RENAME TO t_rename;
RENAME TABLE t_rename TO t_createdemo;
使用视图
视图是虚拟表,不存数据,数据都是从表中检索出的。
为何使用视图?
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分而不是整个表
- 保护数据
- 更改数据格式和显示
但是,视图由于本身不包含数据,所以效率低于在表中查询,谨慎使用
创建视图
CREATE VIEW AS 相应的SQL语句
CREATE VIEW test AS
SELECT d.id,d.name,t.test FROM t_demo d LEFT JOIN t_test t ON d.id = t.id;
SELECT * FROM test;
通常创建视图的所用的SQL语句涉及到联结。
用视图重新格式化检索出的数据
设置列别名或进行列的形式变化,拼接等,并创建视图,然后检索。
管理事务处理
事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的SQL操作要么完全执行,要么完全不执行。
事务中的常用关键字
- 事务(transaction)指一组SQL语句
- 回退(rollback)指撤销指定SQL语句的过程
- 提交(commit)指将未存储的SQL语句结果写入数据库表
- 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),你可以对它发布回退
可以回退哪些语句?用来管理INSERT、UPDATE和DELETE语句,不能回退SELECT语句(回退无意义)也不能回退CREATE和DROP操作,事务处理中可以使用这些语句,但进行回退时,它们不被撤销。
控制事务处理
管理事务处理的关键在于将SQL语句分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。
事务控制不是以BEGIN开始,COMMIT结束就能够实现自动提交或回滚的,而是应当有明确的条件选择进行COMMIT或者ROLLBACK。之前一直对事务有误区。
事务实例:
BEGIN;
INSERT INTO t_demo(id,name) VALUES(8,'name8');
INSERT INTO t_demo(id,name) VALUES(7,'name7');
COMMIT;
如果上述实例中第二条数据插入失败,name就会出现当新执行一个COMMIT或BEGIN或START TRANSACTION(新开一个事务会将该链接中的其他未提交的事务提交,相当于commit!)你会发现已经将刚才第一条写进了数据库。
回滚实例:
INSERT INTO t_demo(id,name) VALUES(8,'name8');
ROLLBACK;
SELECT * FROM t_demo;
运行上述SQL语句会发现INSERT语句执行成功后被直接回滚,也就是如果单独使用ROLLBACK的话,无论行为是否成功,都会执行回滚操作。
在MySQL中通过创建存储过程实现事务,实例如下:
DROP PROCEDURE IF EXISTS t_procedure;
CREATE PROCEDURE t_procedure()
BEGIN
DECLARE t_error INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION;
INSERT INTO t_demo(id,name) VALUES(8,'name8');
-- INSERT INTO t_demo(id,name) VALUES(7,'name7');
INSERT INTO t_demo(id,name) VALUES(9,'name9');
IF t_error =1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END;
CALL t_procedure();
SELECT * FROM t_demo;
约束
主键
主键是一种特殊约束,表中任意列满足以下条件,就可以用于主键。
- 任意两行的主键都不相同
- 每行都具有一个主键值(即列中不允许NULL值)
- 包含主键值的列不修改或更新
- 主键值不能重用。如果从表中删除某一行,其主键值不分配给新行。
创建主键只需要在表创建声明列时加上一个PRIMARY KEY即可。
外键
外键是表中的一个列,其值必须在另一表的主键中列出。外键是保证引用完整性的一个极重要的成分。
总结
这篇笔记是在看《Sql必知必会》过程中记录的,其中所有的数据库操作都是在MySQL数据库中完成的,部分内容在其他类型数据库中可能并不通用,也有可能会有部分理解错误。在数据库的发展过程中,各类数据库都不断进步,不断的更具科学性,打破了越来越多的不合理限制,在这本书中都没有涉及,更多的是非常基础性的数据库操作。
最后部分约束、索引等感觉在书中实在是没有很好的讲出它们的用处和使用方式,因此没有进行记录,后面看到更多的相关内容时会再做系统整理。
如果有问题,可以提出进行讨论验证。