数据库总结汇总

Author:水木黄昏

DataBases

MYSQL

load: mysql -u root -p

1.创建数据库

CERATE DATABASE name;

2. 删除 数据库

DROP DATABASE name;

3. 选择数据库

USE name;

4.数据类型

数值类型大小范围(有符号)范围(无符号)用途
TINYINT1 byte(-128,127)(0,255)小整数值
SMALLINT2 bytes(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 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)单精度 浮点数值
DOUBLE8 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)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳
字符串类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-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;
  1. MySQL支持无符号型的整数,那么比不支持无符号型的MS SQL就能多出一倍的最大数存储;

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

  3. 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)

  4. MySQL text字段类型不允许有默认值;

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

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

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

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

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

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

  11. 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 #获取配置

参数:

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

3.哈希Hash

HMSET key value
HGETALL key
HDEL KEY_NAME FIELD1.. FIELDN 
序号命令及描述
1[HDEL key field1 field2] 删除一个或多个哈希表字段
2HEXISTS key field 查看哈希表 key 中,指定的字段是否存在。
3HGET key field 获取存储在哈希表中指定字段的值。
4HGETALL key 获取在哈希表中指定 key 的所有字段和值
5HINCRBY key field increment 为哈希表 key 中的指定字段的整数值加上增量 increment 。
6HINCRBYFLOAT key field increment 为哈希表 key 中的指定字段的浮点数值加上增量 increment 。
7HKEYS key 获取所有哈希表中的字段
8HLEN key 获取哈希表中字段的数量
9[HMGET key field1 field2] 获取所有给定字段的值
10[HMSET key field1 value1 field2 value2 ] 同时将多个 field-value (域-值)对设置到哈希表 key 中。
11HSET key field value 将哈希表 key 中的字段 field 的值设为 value 。
12HSETNX key field value 只有在字段 field 不存在时,设置哈希表字段的值。
13HVALS 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 移出并获取列表的最后一个元素, 如果列表没有元素会阻塞列表直到等待超时或发现可弹出元素为止。
3BRPOPLPUSH source destination timeout 从列表中弹出一个值,将弹出的元素插入到另外一个列表中并返回它; 如果列表没有元素会阻塞列表直到等待超时或发现可弹出元素为止。
4LINDEX key index 通过索引获取列表中的元素
5LINSERT key BEFORE|AFTER pivot value 在列表的元素前或者后插入元素
6LLEN key 获取列表长度
7LPOP key 移出并获取列表的第一个元素
8[LPUSH key value1 value2] 将一个或多个值插入到列表头部
9LPUSHX key value 将一个值插入到已存在的列表头部
10LRANGE key start stop 获取列表指定范围内的元素
11LREM key count value 移除列表元素
12LSET key index value 通过索引设置列表元素的值
13LTRIM key start stop 对一个列表进行修剪(trim),就是说,让列表只保留指定区间内的元素,不在指定区间之内的元素都将被删除。
14RPOP key 移除列表的最后一个元素,返回值为移除的元素。
15RPOPLPUSH source destination 移除列表的最后一个元素,并将该元素添加到另一个列表并返回
16[RPUSH key value1 value2] 在列表中添加一个或多个值
17RPUSHX key value 为已存在的列表添加值

5.集合SET

SADD
序号命令及描述
1[SADD key member1 member2] 向集合添加一个或多个成员
2SCARD key 获取集合的成员数
3[SDIFF key1 key2] 返回第一个集合与其他集合之间的差异。
4[SDIFFSTORE destination key1 key2] 返回给定所有集合的差集并存储在 destination 中
5[SINTER key1 key2] 返回给定所有集合的交集
6[SINTERSTORE destination key1 key2] 返回给定所有集合的交集并存储在 destination 中
7SISMEMBER key member 判断 member 元素是否是集合 key 的成员
8SMEMBERS key 返回集合中的所有成员
9SMOVE source destination member 将 member 元素从 source 集合移动到 destination 集合
10SPOP 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] 向有序集合添加一个或多个成员,或者更新已存在成员的分数
2ZCARD key 获取有序集合的成员数
3ZCOUNT key min max 计算在有序集合中指定区间分数的成员数
4ZINCRBY key increment member 有序集合中对指定成员的分数加上增量 increment
5[ZINTERSTORE destination numkeys key key …] 计算给定的一个或多个有序集的交集并将结果集存储在新的有序集合 key 中
6ZLEXCOUNT key min max 在有序集合中计算指定字典区间内成员数量
7[ZRANGE key start stop WITHSCORES] 通过索引区间返回有序集合指定区间内的成员
8[ZRANGEBYLEX key min max LIMIT offset count] 通过字典区间返回有序集合的成员
9[ZRANGEBYSCORE key min max WITHSCORES] [LIMIT] 通过分数返回有序集合指定区间内的成员
10ZRANK key member 返回有序集合中指定成员的索引
11[ZREM key member member …] 移除有序集合中的一个或多个成员
12ZREMRANGEBYLEX key min max 移除有序集合中给定的字典区间的所有成员
13ZREMRANGEBYRANK key start stop 移除有序集合中给定的排名区间的所有成员
14ZREMRANGEBYSCORE key min max 移除有序集合中给定的分数区间的所有成员
15[ZREVRANGE key start stop WITHSCORES] 返回有序集中指定区间内的成员,通过索引,分数从高到低
16[ZREVRANGEBYSCORE key max min WITHSCORES] 返回有序集中指定分数区间内的成员,分数从高到低排序
17ZREVRANK key member 返回有序集合中指定成员的排名,有序集成员按分数值递减(从大到小)排序
18ZSCORE 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 …]] 查看订阅与发布系统状态。
3PUBLISH channel message 将信息发送到指定的频道。
4[PUNSUBSCRIBE pattern [pattern …]] 退订所有给定模式的频道。
5[SUBSCRIBE channel channel …] 订阅给定的一个或多个频道的信息。
6[UNSUBSCRIBE channel [channel …]] 指退订给定的频道。

更多相关内容:请点击查看