SQL笔记

从电子书里面摘抄的笔记

数据库
1、 数据库database保存有组织的数据的容器（一个或一组文件）。
2、 数据库管理系统DBMS创建和操纵数据库。
3、 表table某种特定类型数据的结构化清单。
4、 模式schema关于数据库和表的布局及特性信息。
5、 列colomn表中的一个字段。
6、 数据类型datatype，每个列有相同的数据类型。类型帮助分类数据、优化存储。数据类型及名称不同是SQL不兼容的一个主要原因。
7、 行row为表中的一个记录record。
8、 主键primary key为表中一列或一组列，能唯一标识一行。应该总是定义主键。主键值不可重用，删除后不可赋给新行。主键值不可修改。主键值非空。
9、 SQL结构化查询语句structured query language，读写数据的简单有效的方法。
10、 ANSI SQL以及各厂商的扩展，oracle的PL SQL，微软和sybase的Transact SQL
11、 检索数据select。select col_x from table_x; select col_x1,col_x2 from table_x;select * from table_x;
12、 Select返回的列顺序不一定是定义顺序。尽量不用返回。
13、 单条SQl语句后面可以加;号，多条时必须加，一般建议有分号。
14、 SQL不区分大小写，但可以考虑关键字都大写，其它都小写，方便识别。
15、 排序检索数据：select * from table_x order by col_x1,col_col_x2;
16、 子句clause。Order by为子句。可以用select清单中的序号，省略列名，不推荐。
17、 Order by x DESC 降序排序，放在列名后。省略的为ASC升序。多个列名分别指定。
18、 字典排序时，A a视为相同。默认都是字典排序。如果要改，需要更改数据库设置。
19、 过滤数据，where子句。指定搜索条件、过滤条件。Where col_x=x; PostgreSQL7.3之前的版本中，需要指定类型，例如 where col_x=decimal’3.49’
20、 Order by放在where后面。
21、 Where条件：=等于<>不等于!=不等于!<不小于BETWEEN AND两个值之间(含边界值)、IS NULL空值。每种数据库支持的不一样。
22、 IS NULL并不是空字符串，一般是指未赋值。
23、 组合where子句，用AND、OR、NOT。AND优先级高于OR。建议用()控制优先级。MySQL中不支持部分NOT，支持NOT EXISTS
24、 IN操作符。Where col_x in (…) 少量数据时，IN于OR实际一样。
25、 IN一般比OR更快，估计用了SET集合检索。IN WHERE子句。NOT IN子句。
26、 LIKE操作符，通配符wildcard（只用于文本字符串）、搜索模式search pattern。%匹配任意字符任意次。Access数据库用替代%
27、 是否区分大小写，需要改配置。有的数据库会在字符串后面补齐空格，匹配时需要考虑。
28、 下划线_通配符，匹配单个字符。Access数据库用?替代_通配符。
29、 方括号[]通配符，微软系的数据库一般支持。LIKE ‘[JM]%’ 以J或M开头的字符串。
30、 脱字号^{用于否定一个集合，[}JM]% 不以JM开头的。NOT col_x LIKE [JM]% 作用一样。Access中用!替代^
31、 通配符比较慢，尽量不用。尽量放在后面搜索，前面的条件全部判断完，再判断最费时的。
32、 计算字段field。一般速度比客户机上计算要快。
33、 拼接字段，concatenate，用+或||拼接。微软的一般为+，建议用||。具体见DBMS文档。Select name1+’ (’+name2+’)’ from … 。MySQL不支持+和||，用CONCAT(name1,’ (’, name2,’)’)替代。MySQL中||等价于OR，&&等价于AND
34、 列可以执行算术运算±/以及()
35、 列的别名alias（也叫导出列），用as
36、 数据处理函数，例如：去掉空格的三个函数：RTRIM、LTRIM、TRIM
37、 不同数据库实现的函数不同，函数名大多也不一致。
38、 可移植portable。考虑SQL脚本解析和转换，配置文件记录不同环境不同DB的函数代价。自动测试不同数据库的代价，生成配置文件。
39、 函数类型：文本处理、数值运算、日期运算、系统信息、空间运算
40、 LEFT、RIGHT、LENGTH、LOWER、UPPER
41、 SOUNDEX字符串的发音，用于检索发音类似的字符串
42、 汇总数据，聚集函数，AVG()列平均值、COUNT行数、MAX、MIN、SUM。NULL值会被忽略。都可用于多列的算术运算。
43、 聚集不同的值AVG(DISTINCT col_x)as avg_col_x，只统计不同的值，默认为ALL
44、 其他聚集参数：TOP、TOP PERCENT
45、 分组数据，GROUP BY、HAVING。Select col_x,count()as col_x_count from table_x group by col_x; GROUP BY可以包含多列。细节用时再查。Where之后，order之前
46、 Having过滤分组。Where过滤行。Having可以替代where。Having支持所有where操作符。Select col_x,count()as col_x_count from table_x group by col_x having count()>2;
47、 先用where过滤，后用having过滤，Select col_x,count()as col_x_count from table_x where col_y>10 group by col_x having count()>2;
48、 分组和排序。Select col_x,count()as col_x_count from table_x where col_y>10 group by col_x having count()>2 order by col_x
49、 Select子句顺序：select、from、where、group by、having、order by
50、 子查询：select x from t where y IN (select y form t where z=’…’)，where嵌套。从内向外处理。只能是单列，多列报错。子查询嵌套太多太复杂会很慢。
51、 联结表，join，用where a.id=b.a_id来联结。Select a,b,c from t1,t2 where t1,id=t2.id，如果字段不存在歧义，可以不标识表名
52、 没有联结条件的表之间联结，返回结果是笛卡尔积，t1的条目数t2的条目数。效率较低，递归多个更是低效率。如果有联结条件，外键，效率较高。
53、 叉联结cross join、等值联结equijoin（内部联结），Select a,b,c from t1 inner join t2 on t1,id=t2.id，与前面的等价。但首选inner join语法
54、 联结的表数量越多，速度越慢。
55、 可以为table指定别名，并用于全句中。
56、 多做实验，性能相关因素：类型、DBMS、数据量、索引、键、存储方式…
57、 高级联结。自联结、自然联结、外部联结。前面是内部联结inner join。
58、 自联结，联结的两个表为同一个表。可用子查询替代，但部分情况下会比子查询快。
59、 自然联结，排除多次重复出现的列，每个列只出现一次。一般内部联结都是自然联结。
60、 外部联结，LEFT OUTER JOIN，可以筛选出没有联结的行。LEFT从左边表中选择所有行，RIGHT从右边。*= =也可标识左右。Oracle中用(+)标识替代
61、 全外部联结 FULL OUTER JOIN，部分数据库支持
62、 联结之后可以使用聚集group by等
63、 组合查询，UNION、复合查询compound query，sleect … union select …。可以通过测试确定union效率问题。
64、 Union all不取消重复的行，union默认删除重复的行
65、 UNION后面可以加order by，对所有select的结果集进行order
66、 有些DBMS还支持EXCEPT(MINUS 差集，A-B)、INTERSECT(交集)
67、 插入数据，INSERT，需要相应权限。
68、 插入完整行insert into t values(…)依赖于定义顺序，不安全
69、 插入部分列insert into t(…) values(…)省略的列允许NULL、或者有默认值
70、 Insert select组合，insert into t1(…) select … from t2 这个是多行插入，前面两种都是单行插入
71、 从表1复制到表2，select into（DB2不支持），表2可以不存在（自动新建），表2可以存在并被覆盖。Select * into t2 from t1。有的语法不同，create table t2 as select * from t1
72、 更新update，update t set c=’…’ where c2=’…’ 没有where将会更新所有行
73、 删除delete，delete from t where c=’…’ 没有where将会删除所有行
74、 删除表中所有行，用TRUNCATE TABLE更快。但不记录数据变动。
75、 SQL没有撤销undo按钮
76、 创建表，create table，不同DBMS语法有差别。
77、 NULL、NOT NULL最好明确指出。有些数据库不支持默认值。
78、 DEFAULT指定默认值。推荐使用DEFAULT，不使用NULL。
79、 更新表，ALTER TABLE，有数据时不建议更新表。许多不允许删除或更改列。可以考虑创建新表，用insert select拷贝数据，删除旧表。
80、 删除表，drop table t;
81、 重命名表 rename，部分数据库支持。
82、 视图，虚拟的表。简化查询，复用SQL。视图每次吃使用时都会运行，效率较低。
83、 Create view v as select …、drop view v
84、 存储过程，类似函数、中断函数、编译过效率较高。
85、 执行存储过程 EXECUTE pFunc(…)
86、 创建存储过程create procedure p(…) is begin … end; 不同DBMS，语法不同
87、 事务处理，transaction processing，维护数据的完整性，保证批量sql操作完全执行或完全不执行。失败时自动回退(撤销)。Begin transaction … commit transaction
88、 Insert、update、delete可被回退。Select回退无意义。Create、drop不可回退。
89、 Savepoint del1; rollback to del1；设置保留点、撤销到保留点。不同DBMS语法不同。判断@@ERROR的值<>0来控制是否要回退。保留点越多越好。
90、 游标cursor，结果集result set，类似C中的数组下标。定义游标Declare c cursor from select …、使用游标open cursor c…close c、fetch c into t、loop … end loop
91、 约束constraint，外键约束references，主键约束primary key（一个表只有一个）。外键可以预防意外删除。唯一约束unique，一个表中可以有多个，可修改更新，可以为NULL，可重复使用，不可用于外键。检查约束，check。Check (c like ‘[MF]’)
92、 索引create index I on t©
93、 触发器，特殊的存储过程，create trigger tri on t for insert,update as update t set c=’’ where t.c=inserted.c
94、 约束比触发器快，尽量用约束
95、 MySQL5.7、MySQL8、MySQL企业版