Sql必知必会笔记_sql必知必会表单从哪里获取-CSDN博客

本文链接：https://blog.csdn.net/believesoul/article/details/86631479

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相同的功能，它的优点如下：
IN操作符的优点

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;
MySQL不支持拼接

使用列别名（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。之前一直对事务有误区。

详情参见对mysql事务提交、回滚的错误理解

事务实例：

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数据库中完成的，部分内容在其他类型数据库中可能并不通用，也有可能会有部分理解错误。在数据库的发展过程中，各类数据库都不断进步，不断的更具科学性，打破了越来越多的不合理限制，在这本书中都没有涉及，更多的是非常基础性的数据库操作。
最后部分约束、索引等感觉在书中实在是没有很好的讲出它们的用处和使用方式，因此没有进行记录，后面看到更多的相关内容时会再做系统整理。
如果有问题，可以提出进行讨论验证。