# SQL

# Flag

其他数据库

图数据库 (opens new window)

# MySQL

MySQL优化工具

MySql 5.0 以上字符串数据类型可以存的汉字个数

注意谨慎选择较大的存储数据类型

  • UTF8MB4编码:一个汉字 = 4 个字节,英文是一个字节(bytes)
  • UTF8编码:一个汉字 = 3 个字节,英文是一个字节(bytes)
  • GBK编码: 一个汉字 = 2 个字节,英文是一个字节(bytes)
  • 在UTF8状态下 longtext : 4294967295/3=1431655765 个汉字,约14亿,存储空间占用:4294967295/1024/1024/1024=4G 的数据
  • 在UTF8状态下 mediumtext : 16777215/3=5592405 个汉字,约560万,存储空间占用:16777215/1024/1024=16M 的数据
  • 在UTF8状态下 text : 65535/3=21845个汉字,约20000,存储空间占用:65535/1024=64K 的数据
  • 在UTF8状态下 tinytext : 256/3=85个汉字,存储空间占用:256 bytes
  • 在UTF8MB4状态下 varchar : (65535 - 2) / 4 = 16383 个汉字,英文也为 16383 个字符串,存储空间占用:64k
  • 在UTF8状态下 varchar : (65535 - 2) / 3 = 21844 个汉字,英文也为 21844 个字符串,存储空间占用:64k
  • 在GBK状态下 varchar : (65535 - 2) / 2 = 32766 个汉字,英文也为 32766 个字符串,存储空间占用:64k

varchar 超过255个字节会有2字节的额外占用空间开销,所以减2,如果是255以下,则减1

通过分组聚合GROUP_CONCAT实现分析函数(开窗函数)

SELECT
  product_id,
  branch,
  GROUP_CONCAT(t.stock ORDER BY t.stock DESC ) stocks
FROM (SELECT *
      FROM product_stock) t
GROUP BY product_id,branch

# 索引

索引是加速搜索引擎检索数据的一种特殊表查询。简单地说,索引是一个指向表中数据的指针。 索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。

一个数据库中的索引与一本书的索引目录是非常相似的。 拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。

索引有助于加快 SELECT 查询和 WHERE 子句,但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除,但不会影响数据。 使用 CREATE INDEX 语句创建索引,它允许命名索引,指定表及要索引的一列或多列,并指示索引是升序排列还是降序排列。 索引也可以是唯一的,与 UNIQUE 约束类似,在列上或列组合上防止重复条目。

-- 强制使用指定索引
FORCE INDEX (索引名)

从数据结构角度

  1. BTREE
  2. HASH

从物理存储角度

  1. 聚集索引(clustered index)
  2. 非聚集索引(non-clustered index)

从逻辑角度

  • Normal(普通索引)
CREATE INDEX 索引名 ON 表名(`字段名`(length));
ALTER TABLE 表名 ADD INDEX IndexName(`字段名`(length));
  • Unique(唯一索引)
CREATE UNIQUE INDEX 索引名 ON 表名(`字段名`(length));
ALTER TABLE 表名 ADD UNIQUE (column_list);
  • 组合索引
CREATE INDEX 索引名 On 表名(`字段名`(length),`字段名`(length),...);
ALTER TABLE 表名 ADD INDEX 索引名 (id,name);
  • SPATIAL(空间索引)

  • Full Text(全文索引)

# 最小字符长度,默认是4,必须要匹配大于4的才会有返回结果
ft_min_word_len=2
# 存储在InnoDB的FULLTEXT索引中的最小词长
innodb_ft_min_token_size=2
# 中文检索分词插件,设置分词大小,取值范围是1到10,默认值是2,分词的SIZE越大,索引的体积就越大
# 注意位置必须放在全文索引的配置后面
ngram_token_size=1
-- 查看所有全文索引相关参数
SHOW GLOBAL VARIABLES LIKE '%ft%';
SHOW GLOBAL VARIABLES LIKE 'ngram_token_size';
ALTER TABLE tablename ADD FULLTEXT(column1, column2);
-- 
SELECT * FROM 表名 WHERE MATCH(column1, column2) AGAINST('aa','bb','cc'...);
-- 使用IN BOOLEAN MODE匹配不完整单词,默认IN NATURAL LANGUAGE MODE(自然语言模式)
SELECT * FROM  表名 WHERE MATCH(字段) AGAINST('关键词' IN BOOLEAN MODE);

重新构建索引文件

-- 对于InnoDB存储引擎的表无效
REPAIR TABLE 表名 QUICK;
-- InnoDB对表进行索引的重新构建
ALTER TABLE 表名 ENGINE=INNODB;
-- 使用优化指令也可以起到同样的作用,同时这个指令会完成更多的优化作用。OPTIMIZE TABLE运行过程中,MySQL会锁定表
OPTIMIZE TABLE 表名;
-- 执行之后会返回如下信息,但实际上是执行成功的
-- Table does not support optimize, doing recreate + analyze instead

# ORACLE

系统表

  • USER_TABLES 当前用户拥有的表:TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED
  • DBA_TABLES 包括系统表:多了OWER
  • ALL_TABLES 所有用户的表:多了OWER
  • ALL_OBJECTS 当前用户有访问权限的所有对象:OWER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS
  • USER_TAB_COLUMNS 当前用户拥有的表字段
  • ALL_TAB_COLUMNS
  • DBA_TAB_COLUMNS
  • USER_TAB_COMMENTS 当前用户拥有的表注释 :TABLE_NAME,TABLE_TYPE,COMMENTS
  • DBA_TAB_COMMENTS :多了OWER
  • ALL_TAB_COMMENTS :多了OWER
  • USER_COL_COMMENTS 当前用户拥有的表字段注释 : TABLE_NAME,COLUMN_NAME,COMMENTS
  • DBA_COL_COMMENTS :多了OWER
  • ALL_COL_COMMENTS :多了OWER

SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS LIKE '%摘要%'

分组获取最新一条数据(查询各组最新的一条记录)

  • over partition by 分析函数(开窗函数)
SELECT * FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY 分组字段名 ORDER BY 排序字段名 DESC) rn,t.* FROM test1 t
    ) WHERE rn = 1;

SELECT * FROM (
    select eb_vipcode,eb_time,MAX(eb_time) over(partition by eb_vipcode) as "atime" from eb_daskexpdateinfo
    ) x where eb_time = "atime";

SELECT * FROM (
    select ID_,COMPANY_NAME,USAGE_RATE,CREATE_TIME
    ,MAX(CREATE_TIME) over(partition by COMPANY_NAME) as "atime" from SPEC_RATE_ORIGIN
    ) x where CREATE_TIME = "atime";
  • group by
SELECT eb_vipcode,MAX(eb_time) AS "atime" FROM eb_daskexpdateinfo group by eb_vipcode
  • inner join
SELECT A.* FROM SPEC_RATE_ORIGIN A INNER JOIN (
    SELECT COMPANY_NAME,MAX(CREATE_TIME) AS "atime" FROM SPEC_RATE_ORIGIN group by COMPANY_NAME
    ) B ON A.COMPANY_NAME = B.COMPANY_NAME AND A.CREATE_TIME = B."atime";

一次插入多条数据

INSERT ALL
 INTO a表(字段) VALUES(各个值1)
 INTO a表(字段) VALUES(其它值2)
 INTO a表(字段) VALUES(其它值3)
SELECT 1 FROM DUAL;

插入或更新 upsert

MERGE INTO table_name alias1   
USING (table|view|sub_query) alias2  
ON (join condition)   
WHEN MATCHED THEN   
    UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN   
    INSERT (column_list) VALUES (column_values);

# Postgre

-- 插入或更新 upsert
INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

# SQLite3

连接符

连接符 说明
- 算术减法
!= 关系不等于
% 算术模量
& 逻辑与
* 算术乘法
/ 算术除法
| 逻辑或
|| 字符串串联
+ 算术加法
< 关系小于
<< 按位右移
<= 关系式小于或等于
<> 关系不等于
= 关系等于
== 关系等于
> 关系大于
>= 关系大于或等于
>> 按位左移
AND 逻辑与
GLOB 关系文件名匹配
IN 逻辑输入
LIKE 关系字符串匹配
OR 逻辑或

# Redis

# Memcached

# MongoDB

# 免费数据库

MySQL

SQLServer

PostgresqlSQL

Redis

# 标准SQL

在标准 SQL 中,字符串使用的是单引号。

如果字符串本身也包括单引号,则使用两个单引号(注意,不是双引号,字符串中的双引号不需要另外转义)。

有些SQL中使用双引号字符串,是其它的数据库对 SQL 的扩展,比如在MySQL中允许使用单引号和双引号两种。

保留字不能用于表名,比如desc,此时需要加入反引号来区别,但使用表名时可忽略反引号。

保留字不能用于字段名,比如desc,此时也需要加入反引号,并且insert等使用时也要加上反引号

# SQL1992

sql分类

  1. 笛卡尔积 (表乘表)
  2. 等值连接 表的连接条件使用 =
  3. 非等值连接 表的连接条件使用 >、>=、 <、<=、!=、any
  4. 自连接 自己连接自己
  5. 外连接
  6. 左外连接,+ 在等号右边
  7. 右外连接,+ 在等号左边
  8. + 在哪一边的列,该表就补充null

# SQL1999

sql分类

  1. cross join 交叉连接 (笛卡尔积) ,不需要on关键字
  2. natural join 自然连接 (找两个表中相同的列,进行等值匹配),不需要on关键字
  3. inner join 内连接
    • 必须有on关键字,on表示连接条件
    • inner关键字可以省略
    • outer join 外连接,outer关键字可以省略
    • left outer join
    • right outer join
    • full outer join

sql99语法:通过join关键字实现连接

  • 含义:1999年推出的sql语法
  • 支持: 等值连接、非等值连接 (内连接)、外连接
select 字段,...
from1inner|left outer|right outerjoin2 on  连接条件
【inner|left outer|right outerjoin3 on  连接条件
【where 分组前筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 最后执行的,排序的字段或表达式】

好处:语句上,连接条件和筛选条件实现了分离,简洁明了!

# 自连接

  • cross join:自然连接。主要用于产生笛卡尔积。
    • select * from emp cross join dept;
  • natural join:自然连接。这种情况下,数据库会自动找到一个字段来消除笛卡尔积。一般来说,数据库会找那些通过外键约束关联的字段。因此,有较大的局限性。
    • select * from emp natural join dept;

案例:查询员工名和直接上级的名称

  • sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
  • sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

# 子查询

含义:一条查询语句中又嵌套了另一条完整的select语句,其中被嵌套的select语句,称为子查询或内查询在外面的查询语句,称为主查询或外查询

特点:

  1. 子查询都放在小括号内
  2. 子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
  3. 子查询优先于主查询执行,主查询使用了子查询的执行结果
  4. 子查询根据查询结果的行数不同分为以下两类:
    • 单行子查询,结果集只有一行,一般搭配单行操作符使用:> < = <> >= <=,非法使用子查询的情况:
      • 子查询的结果为一组值
      • 子查询的结果为空
    • 多行子查询,结果集有多行,一般搭配多行操作符使用:any、all、in、not in
      • in: 属于子查询结果中的任意一个就行
      • any和all往往可以用其他查询代替

# 分页查询

应用场景:实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

select 字段|表达式,...
from 表
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段】
limit 【起始的记录索引,】 每页的记录数;

特点:

  1. 起始条目索引从0开始
  2. limit子句放在查询语句的最后
  3. 公式:select * from  表 limit (page-1)*sizePerPage, sizePerPage
    • 假如:每页显示条目数sizePerPage,要显示的页数 page

Oracle分页

select * from (
    select rownum as rn, first_name from (select first_name from some_table order by first_name)
  ) where rn > 100  and rn <= 200

select * from (select ncallernm, count(*) tol from tmp_86 group by ncallernm order by tol desc) where rownum<20

select * from OB_CALL_DATA_LOG rownum<101  minus  select * from OB_CALL_DATA_LOG rownum>9

# 连接查询

  • 连接查询的分类:
    • 按年代分为sql192标准仅仅支持内连接,sql199标准支持内连接,左外连接,右外连接,交叉连接    - 按功能分为内连接,外连接,交叉连接

sql92标准:内连接包括

  1. 等值连接:select name,boyname form boys,beauty where beauty.boyfriend_id = boys.id;
    • 多表等值连接的结果为多表的交集部分;n表连接至少需要n-1个连接条件;多表的顺序没有要求;一般需要为表起别名
  2. 非等值连接:select salary,grade_level form employees e,job_grades g where salary between g.'lowest_sal' and 'highest_sal';
  3. 自连接:select e.employee_id,e.last_name,m.employee_id,m.last_name from employees e,employees m where e.'manager_id' = m.'employee_id';

sql99语法:包括内连接(inner),外连接(left outer ,right outer,full outer),交叉连接(cross join)

语法:select 查询列表 form 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】

特点:添加排序,分组,筛选,inner可以省略,筛选条件放在where后面,连接条件放在on后面,提高分离性

  1. 内连接:select 查询列表 form 表1 别名 inner join 表2 别名 on 连接条件

    • 等值连接:select last_name,department_name form employee e inner join department d on e.'department_id' = d.'department_id';
    • 非等值连接:select  salary,grade_level from employee e join job_grades g on e.'salary' between g.'lowest_sal' and g.'highest_sal' group by grade_level;
    • 自连接: select e.last_name,m.last_name from employees e join employees m on e.'manager_id' = m.'employee_id' where e.'last_name' like '%k%';
  2. 外连接:用于查询一个表中有,一个表中没有的,外连接的查询结果是主表中的所有记录 如果从表中有和它匹配的值,则显示出来,没有显示null

    • 左外连接,left join左边的是主表: select b.name,bo.* form beauty b left outer join boys bo on b.'boyfriend_id' = bo.'id';
    • 右外连接,right join右边的是主表: select b.name,bo.* form boys bo right outer join  beauty b on b.'boyfriend_id' = bo.'id';
    • 全外连接,等于内连接的结果,加上表1有但表2没有的,加上表2有表一没有的 use girls; select b.*,bo.* from beauty b full outer join boys bo on b.'boyfriend_id' = 'bo.id';
    • 交叉连接:select b.*,bo.* form beauty b cross join boys bo;

# CASE

同其他编程语言中的switch...caseif...else语句,可以直接在order by 后面使用自定义排序

Case函数在满足了某个符合条件后,剩下的条件将会被自动忽略,因此,即使满足多个条件,执行过程中也只认第一个条件。 在使用 CASE WHEN时,可以把它当作一个没有字段名的字段,字段值根据条件确认,在需要使用字段名时可以是用 as 来定义别名。

  • 简单Case函数

简单Case函数胜在简洁,但是它只适用于这种单字段的单值比较

CASE sex
WHEN '0' THEN '男'
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
  • Case搜索函数

Case搜索函数的优点在于适用于所有比较的情况

CASE WHEN sex = '1' and sex = '0' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

# SQL2003

开窗函数简介:与聚合函数一样,开窗函数也是对行集组进行聚合计算,但是它不像普通聚合函数那样每组只返回一个值 ,开窗函数可以为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗口