数据库总结汇总

Author:水木黄昏

DataBases

MYSQL

load: mysql -u root -p

1.创建数据库

CERATE DATABASE name;

2. 删除 数据库

DROP DATABASE name;

3. 选择数据库

USE name;

4.数据类型

数值类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

5.创建数据表

CREATE TABLE IF NOT EXISTS `tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

6.删除数据表

DROP TABLE talbe_name；

7.插入数据

INSERT INTO table_name(field1,field2,...) VALUES (value1,value2,...);

8.查询数据

SELECT name FROM table_name WHERE field1=value1,或者 LIKE '%char'  ORDER BY field1 ASC升序/DESC降序 LIMIT N; 

9.更新数据

UPDATE table_name SET field1=new_values1 WHERE fieldx=valuex;

10.删除数据

DELETE FROM table_name WHERE field1=values1;

11. 联合查询

SELECT expression1, expression2, ... expression_n
FROM table_name
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM table_name
[WHERE conditions];

12.分组语句

SELECT name FROM table_name WHERE field1=value1 GROUP BY name;

13.导出数据

SELECT * FROM table_name INTO OUTFILE '/home/data/data.txt';

14.导入数据

source /home/data/abc.sql

15.外键

外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)

REFERENCES table_name (index_col_name, …)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

ON DELETE、ON UPDATE表示事件触发限制，可设参数：

① RESTRICT（限制外表中的外键改动，默认值）

② CASCADE（跟随外键改动）

③ SET NULL（设空值）

④ SET DEFAULT（设默认值）

⑤ NO ACTION（无动作，默认的）

16.变更命令

ALTER TABLE testalter_tbl  DROP i;#删除
ALTER TABLE testalter_tbl ADD i INT;#更新
ALTER TABLE testalter_tbl MODIFY c CHAR(10);#修改
ALTER TABLE testalter_tbl CHANGE j j INT;#修改
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;#修改
 ALTER TABLE testalter_tbl RENAME TO alter_tbl;#修改表名

SQL（高级重点）

1.查询

SELECT DISTINCT country FROM Websites; #选取唯一不同的值，也就是去掉 "country" 列重复值
SELECT TOP 50 PERCENT * FROM Websites;#前面百分之 50数据

2.别名

 SELECT column_name(s) FROM table_name AS alias_name;#表别名
 SELECT column_name AS alias_name FROM table_name;#列别名

MySQL&SQL

1. MySQL支持enum和set类型，SQL Server不支持；
2. MySQL不支持nchar、nvarchar、ntext类型；
3. MySQL数据库的递增语句是AUTO_INCREMENT，而MS SQL是identity(1,1)；
4. MS SQL默认到处表创建语句的默认值表示是((0))，而在MySQL里面是不允许带两括号的；
5. MySQL需要为表指定存储类型；
6. MS SQL识别符是[],[type]表示他区别于关键字，但是MySQL却是 `，也就是按键1左边的那个符号；
7. MS SQL支持getdate()方法获取当前时间日期，但是MySQL里面可以分日期类型和时间类型，获取当前日期是cur_date()，当前完整时间是 now()函数；
8. MS SQL不支持replace into语句，但是在最新的SQL Server 2008里面，也支持merge语法；
9. MySQL数据库支持insert into table1 set t1 = ‘’, t2 = ‘’ ，但是MS SQL不支持这样写；
10. MySQL支持insert into tabl1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1,1), (1,1)
11. MS SQL不支持limit语句，是非常遗憾的，只能用top 取代limt 0,N，row_number() over()函数取代limit N,M；
12. MySQL在创建表时要为每个表指定一个存储引擎类型，而MS SQL只支持一种存储引擎；
13. MySQL不支持默认值为当前时间的datetime类型(MS SQL很容易做到)，在MySQL里面是用timestamp类型；
14. MS SQL里面检查是否有这个表再删除，需要这样：

if exists (select * from dbo.sysobjects   where id = object_id(N'uc_newpm') and OBJECTPROPERTY(id,N'IsUserTable')=1) ;

但是在MySQL里面只需要 DROP TABLE IF EXISTS cdb_forums;

15. MySQL支持无符号型的整数，那么比不支持无符号型的MS SQL就能多出一倍的最大数存储；

16. MySQL不支持在MS SQL里面使用非常方便的varchar(max)类型，这个类型在MS SQL里面既可做一般数据存储，也可以做blob数据存储；

17. MySQL数据库创建非聚集索引只需要在创建表的时候指定为key就行， 比如：KEY displayorder (fid,displayorder)
    在MS SQL里面必须要：
    create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers(username asc,appid asc)

18. MySQL text字段类型不允许有默认值；

19. MySQL的一个表的总共字段长度不超过65XXX。

20. 一个很表面的区别就是MySQL的安装特别简单，而且文件大小才110M(非安装版)，相比微软这个庞然大物，安装进度来说简直就是…

21. MySQL的管理工具有几个比较好的，MySQL_front和官方那个套件，不过都没有SSMS的使用方便，这是MySQL很大的一个缺点。

22. MySQL的存储过程只是出现在最新的版本中，稳定性和性能可能不如MS SQL。

23. 同样的负载压力，MySQL要消耗更少的CPU和内存，MS SQL的确是很耗资源；

24. php连接MySQL数据库和MS SQL的方式都差不多，只需要将函数的MySQL替换成MS SQL即可。

25. MySQL支持 date,time,year类型，MS SQL到2008才支持date和time。

Redis

Redis支持五种数据类型：string（字符串），hash（哈希），list（列表），set（集合）及zset(sorted set：有序集合)

1.配置

 CONFIG SET con_name value #设置配置
 CONFIG GET	con_name #获取配置

参数：

序号	配置项	说明
1	daemonize no	Redis 默认不是以守护进程的方式运行，可以通过该配置项修改，使用 yes 启用守护进程（Windows 不支持守护线程的配置为 no ）
2	pidfile /var/run/redis.pid	当 Redis 以守护进程方式运行时，Redis 默认会把 pid 写入 /var/run/redis.pid 文件，可以通过 pidfile 指定
3	port 6379	指定 Redis 监听端口，默认端口为 6379，作者在自己的一篇博文中解释了为什么选用 6379 作为默认端口，因为 6379 在手机按键上 MERZ 对应的号码，而 MERZ 取自意大利歌女 Alessia Merz 的名字
4	bind 127.0.0.1	绑定的主机地址
5	timeout 300	当客户端闲置多长秒后关闭连接，如果指定为 0 ，表示关闭该功能
6	loglevel notice	指定日志记录级别，Redis 总共支持四个级别：debug、verbose、notice、warning，默认为 notice
7	logfile stdout	日志记录方式，默认为标准输出，如果配置 Redis 为守护进程方式运行，而这里又配置为日志记录方式为标准输出，则日志将会发送给 /dev/null
8	databases 16	设置数据库的数量，默认数据库为0，可以使用SELECT 命令在连接上指定数据库id
9	save <seconds> <changes>Redis 默认配置文件中提供了三个条件：save 900 1save 300 10save 60 10000分别表示 900 秒（15 分钟）内有 1 个更改，300 秒（5 分钟）内有 10 个更改以及 60 秒内有 10000 个更改。	指定在多长时间内，有多少次更新操作，就将数据同步到数据文件，可以多个条件配合
10	rdbcompression yes	指定存储至本地数据库时是否压缩数据，默认为 yes，Redis 采用 LZF 压缩，如果为了节省 CPU 时间，可以关闭该选项，但会导致数据库文件变的巨大
11	dbfilename dump.rdb	指定本地数据库文件名，默认值为 dump.rdb
12	dir ./	指定本地数据库存放目录
13	slaveof <masterip> <masterport>	设置当本机为 slave 服务时，设置 master 服务的 IP 地址及端口，在 Redis 启动时，它会自动从 master 进行数据同步
14	masterauth <master-password>	当 master 服务设置了密码保护时，slav 服务连接 master 的密码
15	requirepass foobared	设置 Redis 连接密码，如果配置了连接密码，客户端在连接 Redis 时需要通过 AUTH 命令提供密码，默认关闭
16	maxclients 128	设置同一时间最大客户端连接数，默认无限制，Redis 可以同时打开的客户端连接数为 Redis 进程可以打开的最大文件描述符数，如果设置 maxclients 0，表示不作限制。当客户端连接数到达限制时，Redis 会关闭新的连接并向客户端返回 max number of clients reached 错误信息
17	maxmemory <bytes>	指定 Redis 最大内存限制，Redis 在启动时会把数据加载到内存中，达到最大内存后，Redis 会先尝试清除已到期或即将到期的 Key，当此方法处理 后，仍然到达最大内存设置，将无法再进行写入操作，但仍然可以进行读取操作。Redis 新的 vm 机制，会把 Key 存放内存，Value 会存放在 swap 区
18	appendonly no	指定是否在每次更新操作后进行日志记录，Redis 在默认情况下是异步的把数据写入磁盘，如果不开启，可能会在断电时导致一段时间内的数据丢失。因为 redis 本身同步数据文件是按上面 save 条件来同步的，所以有的数据会在一段时间内只存在于内存中。默认为 no
19	appendfilename appendonly.aof	指定更新日志文件名，默认为 appendonly.aof
20	appendfsync everysec	指定更新日志条件，共有 3 个可选值：no：表示等操作系统进行数据缓存同步到磁盘（快）always：表示每次更新操作后手动调用 fsync() 将数据写到磁盘（慢，安全）everysec：表示每秒同步一次（折中，默认值）
21	vm-enabled no	指定是否启用虚拟内存机制，默认值为 no，简单的介绍一下，VM 机制将数据分页存放，由 Redis 将访问量较少的页即冷数据 swap 到磁盘上，访问多的页面由磁盘自动换出到内存中（在后面的文章我会仔细分析 Redis 的 VM 机制）
22	vm-swap-file /tmp/redis.swap	虚拟内存文件路径，默认值为 /tmp/redis.swap，不可多个 Redis 实例共享
23	vm-max-memory 0	将所有大于 vm-max-memory 的数据存入虚拟内存，无论 vm-max-memory 设置多小，所有索引数据都是内存存储的(Redis 的索引数据 就是 keys)，也就是说，当 vm-max-memory 设置为 0 的时候，其实是所有 value 都存在于磁盘。默认值为 0
24	vm-page-size 32	Redis swap 文件分成了很多的 page，一个对象可以保存在多个 page 上面，但一个 page 上不能被多个对象共享，vm-page-size 是要根据存储的 数据大小来设定的，作者建议如果存储很多小对象，page 大小最好设置为 32 或者 64bytes；如果存储很大大对象，则可以使用更大的 page，如果不确定，就使用默认值
25	vm-pages 134217728	设置 swap 文件中的 page 数量，由于页表（一种表示页面空闲或使用的 bitmap）是在放在内存中的，，在磁盘上每 8 个 pages 将消耗 1byte 的内存。
26	vm-max-threads 4	设置访问swap文件的线程数,最好不要超过机器的核数,如果设置为0,那么所有对swap文件的操作都是串行的，可能会造成比较长时间的延迟。默认值为4
27	glueoutputbuf yes	设置在向客户端应答时，是否把较小的包合并为一个包发送，默认为开启
28	hash-max-zipmap-entries 64 hash-max-zipmap-value 512	指定在超过一定的数量或者最大的元素超过某一临界值时，采用一种特殊的哈希算法
29	activerehashing yes	指定是否激活重置哈希，默认为开启（后面在介绍 Redis 的哈希算法时具体介绍）
30	include /path/to/local.conf	指定包含其它的配置文件，可以在同一主机上多个Redis实例之间使用同一份配置文件，而同时各个实例又拥有自己的特定配置文件

2.字符串String

SET key value #设置
GET key #获取
Getrange key start end #获取存储在指定 key 中字符串的子字符串
GETSET KEY_NAME VALUE #设置指定 key 的值，并返回 key 的旧值
APPEND KEY_NAME NEW_VALUE #将 value 追加到 key 原来的值的末尾
STRLEN KEY_NAME #获取指定 key 所储存的字符串值的长度，×××当 key 储存的不是字符串值时，返回一个错误

序号	命令及描述
1	SET key value 设置指定 key 的值
2	GET key 获取指定 key 的值。
3	GETRANGE key start end 返回 key 中字符串值的子字符
4	GETSET key value 将给定 key 的值设为 value ，并返回 key 的旧值(old value)。
5	GETBIT key offset 对 key 所储存的字符串值，获取指定偏移量上的位(bit)。
6	[MGET key1 key2…] 获取所有(一个或多个)给定 key 的值。
7	SETBIT key offset value 对 key 所储存的字符串值，设置或清除指定偏移量上的位(bit)。
8	SETEX key seconds value 将值 value 关联到 key ，并将 key 的过期时间设为 seconds (以秒为单位)。
9	SETNX key value 只有在 key 不存在时设置 key 的值。
10	SETRANGE key offset value 用 value 参数覆写给定 key 所储存的字符串值，从偏移量 offset 开始。
11	STRLEN key 返回 key 所储存的字符串值的长度。
12	[MSET key value key value …] 同时设置一个或多个 key-value 对。
13	[MSETNX key value key value …] 同时设置一个或多个 key-value 对，当且仅当所有给定 key 都不存在。
14	PSETEX key milliseconds value 这个命令和 SETEX 命令相似，但它以毫秒为单位设置 key 的生存时间，而不是像 SETEX 命令那样，以秒为单位。
15	INCR key 将 key 中储存的数字值增一。
16	INCRBY key increment 将 key 所储存的值加上给定的增量值（increment） 。
17	INCRBYFLOAT key increment 将 key 所储存的值加上给定的浮点增量值（increment） 。
18	DECR key 将 key 中储存的数字值减一。
19	DECRBY key decrement key 所储存的值减去给定的减量值（decrement） 。
20	APPEND key value 如果 key 已经存在并且是一个字符串， APPEND 命令将指定的 value 追加到该 key 原来值（value）的末尾。

3.哈希Hash

HMSET key value
HGETALL key
HDEL KEY_NAME FIELD1.. FIELDN 

序号	命令及描述
1	[HDEL key field1 field2] 删除一个或多个哈希表字段
2	HEXISTS key field 查看哈希表 key 中，指定的字段是否存在。
3	HGET key field 获取存储在哈希表中指定字段的值。
4	HGETALL key 获取在哈希表中指定 key 的所有字段和值
5	HINCRBY key field increment 为哈希表 key 中的指定字段的整数值加上增量 increment 。
6	HINCRBYFLOAT key field increment 为哈希表 key 中的指定字段的浮点数值加上增量 increment 。
7	HKEYS key 获取所有哈希表中的字段
8	HLEN key 获取哈希表中字段的数量
9	[HMGET key field1 field2] 获取所有给定字段的值
10	[HMSET key field1 value1 field2 value2 ] 同时将多个 field-value (域-值)对设置到哈希表 key 中。
11	HSET key field value 将哈希表 key 中的字段 field 的值设为 value 。
12	HSETNX key field value 只有在字段 field 不存在时，设置哈希表字段的值。
13	HVALS key 获取哈希表中所有值。
14	[HSCAN key cursor MATCH pattern] [COUNT count] 迭代哈希表中的键值对。

4.列表 List

LPUSH RPUSH
LPOP  RPOP
LRANGE

序号	命令及描述
1	[BLPOP key1 key2 ] timeout 移出并获取列表的第一个元素， 如果列表没有元素会阻塞列表直到等待超时或发现可弹出元素为止。
2	[BRPOP key1 key2 ] timeout 移出并获取列表的最后一个元素， 如果列表没有元素会阻塞列表直到等待超时或发现可弹出元素为止。
3	BRPOPLPUSH source destination timeout 从列表中弹出一个值，将弹出的元素插入到另外一个列表中并返回它； 如果列表没有元素会阻塞列表直到等待超时或发现可弹出元素为止。
4	LINDEX key index 通过索引获取列表中的元素
5	LINSERT key BEFORE|AFTER pivot value 在列表的元素前或者后插入元素
6	LLEN key 获取列表长度
7	LPOP key 移出并获取列表的第一个元素
8	[LPUSH key value1 value2] 将一个或多个值插入到列表头部
9	LPUSHX key value 将一个值插入到已存在的列表头部
10	LRANGE key start stop 获取列表指定范围内的元素
11	LREM key count value 移除列表元素
12	LSET key index value 通过索引设置列表元素的值
13	LTRIM key start stop 对一个列表进行修剪(trim)，就是说，让列表只保留指定区间内的元素，不在指定区间之内的元素都将被删除。
14	RPOP key 移除列表的最后一个元素，返回值为移除的元素。
15	RPOPLPUSH source destination 移除列表的最后一个元素，并将该元素添加到另一个列表并返回
16	[RPUSH key value1 value2] 在列表中添加一个或多个值
17	RPUSHX key value 为已存在的列表添加值

5.集合SET

SADD

序号	命令及描述
1	[SADD key member1 member2] 向集合添加一个或多个成员
2	SCARD key 获取集合的成员数
3	[SDIFF key1 key2] 返回第一个集合与其他集合之间的差异。
4	[SDIFFSTORE destination key1 key2] 返回给定所有集合的差集并存储在 destination 中
5	[SINTER key1 key2] 返回给定所有集合的交集
6	[SINTERSTORE destination key1 key2] 返回给定所有集合的交集并存储在 destination 中
7	SISMEMBER key member 判断 member 元素是否是集合 key 的成员
8	SMEMBERS key 返回集合中的所有成员
9	SMOVE source destination member 将 member 元素从 source 集合移动到 destination 集合
10	SPOP key 移除并返回集合中的一个随机元素
11	[SRANDMEMBER key count] 返回集合中一个或多个随机数
12	[SREM key member1 member2] 移除集合中一个或多个成员
13	[SUNION key1 key2] 返回所有给定集合的并集
14	[SUNIONSTORE destination key1 key2] 所有给定集合的并集存储在 destination 集合中
15	[SSCAN key cursor MATCH pattern] [COUNT count] 迭代集合中的元素

6.有序集合

ZADD
ZRANGE

序号	命令及描述
1	[ZADD key score1 member1 score2 member2] 向有序集合添加一个或多个成员，或者更新已存在成员的分数
2	ZCARD key 获取有序集合的成员数
3	ZCOUNT key min max 计算在有序集合中指定区间分数的成员数
4	ZINCRBY key increment member 有序集合中对指定成员的分数加上增量 increment
5	[ZINTERSTORE destination numkeys key key …] 计算给定的一个或多个有序集的交集并将结果集存储在新的有序集合 key 中
6	ZLEXCOUNT key min max 在有序集合中计算指定字典区间内成员数量
7	[ZRANGE key start stop WITHSCORES] 通过索引区间返回有序集合指定区间内的成员
8	[ZRANGEBYLEX key min max LIMIT offset count] 通过字典区间返回有序集合的成员
9	[ZRANGEBYSCORE key min max WITHSCORES] [LIMIT] 通过分数返回有序集合指定区间内的成员
10	ZRANK key member 返回有序集合中指定成员的索引
11	[ZREM key member member …] 移除有序集合中的一个或多个成员
12	ZREMRANGEBYLEX key min max 移除有序集合中给定的字典区间的所有成员
13	ZREMRANGEBYRANK key start stop 移除有序集合中给定的排名区间的所有成员
14	ZREMRANGEBYSCORE key min max 移除有序集合中给定的分数区间的所有成员
15	[ZREVRANGE key start stop WITHSCORES] 返回有序集中指定区间内的成员，通过索引，分数从高到低
16	[ZREVRANGEBYSCORE key max min WITHSCORES] 返回有序集中指定分数区间内的成员，分数从高到低排序
17	ZREVRANK key member 返回有序集合中指定成员的排名，有序集成员按分数值递减(从大到小)排序
18	ZSCORE key member 返回有序集中，成员的分数值
19	[ZUNIONSTORE destination numkeys key key …] 计算给定的一个或多个有序集的并集，并存储在新的 key 中
20	[ZSCAN key cursor MATCH pattern] [COUNT count] 迭代有序集合中的元素（包括元素成员和元素分值）

7.发布订阅

SUBSCRIBE redisChat 订阅
PUBLISH redisChat "Redis is a great caching technique" 发布

序号	命令及描述
1	[PSUBSCRIBE pattern pattern …] 订阅一个或多个符合给定模式的频道。
2	[PUBSUB subcommand argument [argument …]] 查看订阅与发布系统状态。
3	PUBLISH channel message 将信息发送到指定的频道。
4	[PUNSUBSCRIBE pattern [pattern …]] 退订所有给定模式的频道。
5	[SUBSCRIBE channel channel …] 订阅给定的一个或多个频道的信息。
6	[UNSUBSCRIBE channel [channel …]] 指退订给定的频道。