Memcache
分布式缓存出于如下考虑，首先是缓存本身的水平线性扩展问题，其次是缓存大并发下的本身的性能问题，再次避免缓存的单点故障问题(多副本和副本一致性)。分布式缓存的核心技术包括首先是内存本身的管理问题，包括了内存的分配，管理和回收机制。其次是分布式管理和分布式算法，其次是缓存键值管理和路由。
原文：http://wenku.baidu.com/view/8686d46c7e21af45b307a8c3
什么是Memcached
许多Web 应用程序都将数据保存到RDBMS中，应用服务器从中读取数据并在浏览器中显示。但随着数据量的增大，访问的集中，就会出现REBMS的负担加重，数据库响应恶化，网站显示延迟等重大影响。Memcached是高性能的分布式内存缓存服务器。一般的使用目的是通过缓存数据库查询结果，减少数据库的访问次数，以提高动态Web 应用的速度、提高扩展性。如图：
Memcached作为高速运行的分布式缓存服务器具有以下特点。
- 协议简单：memcached的服务器客户端通信并不使用复杂的MXL等格式，而是使用简单的基于文本的协议。
- 基于libevent的事件处理：libevent是个程序库，他将Linux 的epoll、BSD类操作系统的kqueue等时间处理功能封装成统一的接口。memcached使用这个libevent库，因此能在Linux、BSD、Solaris等操作系统上发挥其高性能。
- 内置内存存储方式：为了提高性能，memcached中保存的数据都存储在memcached内置的内存存储空间中。由于数据仅存在于内存中，因此重启memcached，重启操作系统会导致全部数据消失。另外，内容容量达到指定的值之后memcached回自动删除不适用的缓存。
- Memcached不互通信的分布式：memcached尽管是“分布式”缓存服务器，但服务器端并没有分布式功能。各个memcached不会互相通信以共享信息。他的分布式主要是通过客户端实现的。
Memcached的内存管理
最近的memcached默认情况下采用了名为Slab Allocatoion的机制分配，管理内存。在改机制出现以前，内存的分配是通过对所有记录简单地进行malloc和free来进行的。但是这中方式会导致内存碎片，加重操作系统内存管理器的负担。
Slab Allocator的基本原理是按照预先规定的大小，将分配的内存分割成特定长度的块，已完全解决内存碎片问题。Slab Allocation
而且slab allocator 还有重复使用已分配内存的目的。也就是说，分配到的内存不会释放，而是重复利用。
Slab Allocation 的主要术语
-
Page :分配给Slab 的内存空间，默认是1MB。分配给Slab 之后根据slab 的大小切分成chunk. -
Chunk : 用于缓存记录的内存空间。 -
Slab Class:特定大小的chunk 的组。
在Slab 中缓存记录的原理
Memcached根据收到的数据的大小，选择最合适数据大小的Slab (图2) memcached中保存着slab内空闲chunk的列表，根据该列表选择chunk,然后将数据缓存于其中。
Memcached在数据删除方面有效里利用资源
Memcached删除数据时数据不会真正从memcached中消失。Memcached不会释放已分配的内存。记录超时后，客户端就无法再看见该记录（invisible 透明），其存储空间即可重复使用。
Lazy Expriationmemcached内部不会监视记录是否过期，而是在get时查看记录的时间戳，检查记录是否过期。这种技术称为lazy expiration.因此memcached不会再过期监视上耗费CPU时间。
对于缓存存储容量满的情况下的删除需要考虑多种机制，一方面是按队列机制，一方面应该对应缓存对象本身的优先级，根据缓存对象的优先级进行对象的删除。
LRU:从缓存中有效删除数据的原理
Memcached会优先使用已超时的记录空间，但即使如此，也会发生追加新纪录时空间不足的情况。此时就要使用名为Least Recently Used (LRU)机制来分配空间。这就是删除最少使用的记录的机制。因此当memcached的内存空间不足时（无法从slab class）获取到新空间时，就从最近未使用的记录中搜索，并将空间分配给新的记录。
Memcached分布式
Memcached虽然称为“分布式“缓存服务器，但服务器端并没有“分布式 ” 的功能。Memcached的分布式完全是有客户端实现的。现在我们就看一下memcached是怎么实现分布 式缓存的。
例如下面假设memcached服务器有node1～node3三台，应用程序要保存键名为“tokyo”“kanagawa”“chiba”“saitama”“gunma” 的数据。
首先向memcached中添加“tokyo”。将“tokyo”传给客户端程序库后，客户端实现的算法就会根据“键”来决定保存数据的memcached服务器。服务器选定后，即命令它保存“tokyo”及其值。
同样，“kanagawa”“chiba”“saitama”“gunma”都是先选择服务器再保存。
接下来获取保存的数据。获取时也要将要获取的键“tokyo”传递给函数库。函数库通过与数据保存时相同的算法，根据“键”选择服务器。使用的算法相同，就能选中与保存时相同的服务器，然后发送get命令。只要数据没有因为某些原因被删除，就能获得保存的值。
这样，将不同的键保存到不同的服务器上，就实现了memcached的分布式。 memcached服务器增多后，键就会分散，即使一台memcached服务器发生故障无法连接，也不会影响其他的缓存，系统依然能继续运行。
Memcached的缓存分布策略： http://blog.csdn.net/bintime/article/details/6259133
Consistent Hashing的简单说明
Consistent Hashing如下所示：首先求出memcached服务器（节点）的哈希值， 并将其配置到0～232的圆（continuum）上。 然后用同样的方法求出存储数据的键的哈希值，并映射到圆上。 然后从数据映射到的位置开始顺时针查找，将数据保存到找到的第一个服务器上。 如果超过232仍然找不到服务器，就会保存到第一台memcached服务器上。
从上图的状态中添加一台memcached服务器。余数分布式算法由于保存键的服务器会发生巨大变化 而影响缓存的命中率，但Consistent Hashing中，只有在continuum上增加服务器的地点逆时针方向的 第一台服务器上的键会受到影响。
因此，Consistent Hashing最大限度地抑制了键的重新分布。 而且，有的Consistent Hashing的实现方法还采用了虚拟节点的思想。 使用一般的hash函数的话，服务器的映射地点的分布非常不均匀。 因此，使用虚拟节点的思想，为每个物理节点（服务器） 在continuum上分配100～200个点。这样就能抑制分布不均匀， 最大限度地减小服务器增减时的缓存重新分布。
缓存多副本
缓存多副本主要是用于在缓存数据存放时存储缓存数据的多个副本，以防止缓存失效。缓存失效发生在以下几种情况：
- 1.
缓存超时被移除（正常失效） - 2.
缓存由于存储空间限制被移除（异常失效） - 3.
由于缓存节点变化而导致的缓存失效（异常失效）
在缓存多副本的情况下，需要重新考虑缓存的分布式分布策略。其次缓存的多个副本实际本身是可能的多个读的节点，可以做为分布式的并行读，这是另外一个可以考虑的问题。
缓存数据的一致性问题
缓存数据尽量只读，因此缓存本身是不适合大量写和更新操作的数据场景的。对于读的情况下，如果存在数据变化，一种是同时更新缓存和数据库。一种是直接对缓存数据进行失效处理。
http://raychase.iteye.com/blog/1545906
Ehcache 整合Spring 使用页面、对象缓存
cas服务下单点登录
http://blog.csdn.net/frinder/article/details/7969925
AS单点登录(SSO)完整教程(2012-02-01更新)
一、教程说明
前言
- 教程目的：从头到尾细细道来单点登录服务器及客户端应用的每个步骤
- 单点登录（SSO）：请看百科解释猛击这里打开
- 本教程使用的SSO服务器是Yelu大学研发的CAS(Central Authentication Server)，
官网：http://www.jasig.org/cas
本教程环境：
- Tomcat6.0.29
- JDK6
- CAS Server版本：cas-server-3.4.3.1、cas-server-3.4.10
- CAS Client版本：cas-client-3.1.12、cas-client-3.2.1
- 教程撰写日期：2010-11-05(第一版)、2011-11-05(一年后更新)、2012-02-01(异常处理)
- 教程作者：咖啡兔
二、创建证书
啰嗦几句：证书是单点登录认证系统中很重要的一把钥匙，客户端于服务器的交互安全靠的就是证书；本教程由于是演示所以就自己用JDK自带的keytool工具生成证书；如果以后真正在产品环境中使用肯定要去证书提供商去购买，证书认证一般都是由VeriSign认证，中文官方网站：http://www.verisign.com/cn/
用JDK自带的keytool工具生成证书：
keytool -genkey -alias wsria -keyalg RSA -keystore d:/keys/wsriakey
无图不给力，有图有真相：
具体的输入项图片中都有说明，有一点我要解释一下；在输入完密码后提示输入域名是我输入的是sso.wsria.com，其实这个域名是不存在的，但是我为了演示所以虚拟了这个域名，技巧在于修改
C:\Windows\System32\drivers\etc\hosts
添加内容如下：
127.0.0.1 sso.wsria.com
这样在访问sso.wsria.com的时候其实是访问的127.0.0.1也就是本机
严重提醒：提示输入域名的时候不能输入IP地址
三、导出证书
D:\keys>keytool -export -file d:/keys/wsria.crt -alias wsria -keystore d:/keys/wsriakey
特别提示：如果提示：
keytool error: java.io.IOException: Keystore was tampered with, or password was incorrect
那么请输入密码：changeit
来点颜色：
至此导出证书完成，可以分发给应用的JDK使用了，接下来讲解客户端的JVM怎么导入证书。
四、为客户端的JVM导入证书
keytool -import -keystore D:\tools\jdk\1.6\jdk1.6.0_20\jre\lib\security\cacerts -file D:/keys/wsria.crt -alias wsria
来点颜色瞧瞧：
特别说明
D:\tools\jdk\1.6\jdk1.6.0_20\jre\lib\security -- 是jre的目录；密码还是刚刚输入的密码。至此证书的创建、导出、导入到客户端JVM都已完成，下面开始使用证书到Web服务器中，本教程使用tomcat。
五、应用证书到Web服务器-Tomcat
说是应用起始做的事情就是启用Web服务器(Tomcat)的SSL，也就是HTTPS加密协议，为什么加密我就不用啰嗦了吧…… 准备好一个干净的tomcat，本教程使用的apache-tomcat-6.0.29 打开tomcat目录的conf/server.xml文件，开启83和87行的注释代码，并设置keystoreFile、keystorePass修改结果如下：
|
1
2
|
<
connector
port
=
"8443"
protocol
=
"HTTP/1.1"
sslenabled
=
"true"
maxthreads
=
"150"
scheme
=
"https"
secure
=
"true"
clientauth
=
"false"
sslprotocol
=
"TLS"
keystorefile
=
"D:/keys/wsriakey"
keystorepass
=
"wsria.com"
>
</
connector
>
|
参数说明：
- keystoreFile：在第一步创建的key存放位置
- keystorePass：创建证书时的密码
好了，到此Tomcat的SSL启用完成，现在你可以启动tomcat试一下了，例如本教程输入地址：https://sso.wsria.com:8443/ 打开的是：
好的，那么我们点击“继续浏览此网站(不推荐)。现在进入Tomcat目录了吧，如果是那么你又向成功迈进了一步。
OK，接下来要配置CAS服务器了。
六、CAS服务器初体验
-
CAS服务端下载：http://www.jasig.org/cas/download
-
下载完成后将cas-server-3.4.3.1.zip解压，解压cas-server-3.4.3/modules/cas-server-webapp-3.4.3.1.war，改名为cas，然后复制cas目录到你的tomcat/webapp目录下
-
现在可以访问CAS应用了，当然要使用HTTPS加密协议访问，例如本教程地址：https://sso.wsria.com:8443/cas/login ，现在打开了CAS服务器的页面输入admin/admin点击登录（CAS默认的验证规则只要用户名和密码相同就通过）所以如果你看到下面的这张图片你就成功了
你成功了吗？如果没有成功请再检查以上步骤！
2011-11-05更新说明
使用Maven构建：
使用cmd或者shell进入cas-server-3.4.10目录，运行：
|
1
|
mvn
package
-pl cas-server-webapp,cas-server-support-jdbc
|
意思是只需要构建cas-server-webapp和cas-server-support-jdbc，如果需要其他的请根据文件夹名称设置或者构建全部模块，打包全部模块命令：mvn package 即可。打包过程中会从网络下载需要的jar包，请耐心等待；如果在~/.m2/settings.xml中定义了mirror代理，那么请把随便修改一个字符，否则下载jar包会失败！
打包完成后就可以从cas-server-webapp/target/cas.war复制到你的tomcat/webapp中；或者直接复制cas-server-webapp/target/cas-server-webapp-3.4.10目录到tomcat/webapp目录下，其他步骤和上面一样。
七、CAS服务器深入配置
上面的初体验仅仅是简单的身份验证，实际应用中肯定是要读取数据库的数据，下面我们来进一步配置CAS服务器怎么读取数据库的信息进行身份验证。 首先打开
tomcat/webapp/cas/WEB-INF/deployerConfigContext.xml配置的地方如下：
找到第92行处，注释掉：SimpleTestUsernamePasswordAuthenticationHandler这个验证Handler，这个是比较简单的，只是判断用户名和密码相同即可通过，这个肯定不能在实际应用中使用，弃用！
注释掉92行后在下面添加下面的代码：
|
1
2
3
4
5
|
<
bean
class
=
"org.jasig.cas.adaptors.jdbc.QueryDatabaseAuthenticationHandler"
>
<
property
name
=
"dataSource"
ref
=
"dataSource"
>
<
property
name
=
"sql"
value
=
"select password from t_admin_user where login_name=?"
>
<
property
name
=
"passwordEncoder"
ref
=
"MD5PasswordEncoder"
>
</
property
></
property
></
property
></
bean
>
|
在文件的末尾之前加入如下代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
<
bean
id
=
"dataSource"
class
=
"org.springframework.jdbc.datasource.DriverManagerDataSource"
>
<
property
name
=
"driverClassName"
><
value
>com.mysql.jdbc.Driver</
value
></
property
>
<
property
name
=
"username"
><
value
>root</
value
></
property
>
<
property
name
=
"password"
><
value
>root</
value
></
property
>
</
bean
>
<
bean
id
=
"MD5PasswordEncoder"
class
=
"org.jasig.cas.authentication.handler.DefaultPasswordEncoder"
>
<
constructor-arg
index
=
"0"
>
<
value
>MD5</
value
>
</
constructor-arg
>
</
bean
>
|
复制cas-server-3.4.3.1\modules\cas-server-support-jdbc-3.4.3.1.jar和mysql驱动jar包到tomcat/webapp/cas/WEB-INF/lib目录
配置解释：
-
QueryDatabaseAuthenticationHandler，是cas-server-support-jdbc提供的查询接口其中一个，QueryDatabaseAuthenticationHandler是通过配置一个 SQL 语句查出密码，与所给密码匹配
-
dataSource,我就不用解释了吧，就是使用JDBC查询时的数据源
-
sql，语句就是查询哪一张表，本例根据t_admin_user表的login_name字段查询密码，CAS会匹配用户输入的密码，如果匹配则通过；下面是t_admin_user的表结构：
|
1
2
3
4
5
6
7
8
|
create
table
t_admin_user (
id
bigint
not
null
auto_increment,
email
varchar
(255),
login_name
varchar
(255)
not
null
unique
,
name
varchar
(255),
password
varchar
(255),
primary
key
(id)
) ENGINE=InnoDB;
|
- passwordEncoder，这个就算是自己加的盐巴了，意思很明显就是处理密码的加密，看你的应用中数据库保存的是明码还是加密过的，比如本例是使用MD5加密的，所以配置了MD5PasswordEncoder这个Handler，cas内置了MD5的功能所以只需要配置一下就可以了；如果在实际应用中使用的是公司自己的加密算法那么就需要自己写一个Handler来处理密码，实现方式也比较简单，创建一个类继承org.jasig.cas.authentication.handler.PasswordEncoder然后在encode方法中加密用户输入的密码然后返回即可
八、配置CAS客户端
添加cas-client的jar包，有两种方式：
传统型
下载cas-client，地址：http://www.ja-sig.org/downloads/cas-clients/，然后解压cas-client-3.1.12.zip，在modules文件夹中有需要的jar包，请根据自己的项目情况选择使用
2011-11-05更新：
用maven打包server的方式一样，在cas-client-3.2.1目录中运行命令：
|
1
|
mvn
package
-pl cas-client-core -DskipTests=
true
|
然后从target目录中复制cas-client-core-3.2.1.jar到应用的WEB-INF/lib目录中
Maven型
|
1
2
3
4
5
|
<
dependency
>
<
groupid
>org.jasig.cas.client</
groupid
>
<
artifactid
>cas-client-core</
artifactid
>
<
version
>3.1.12</
version
>
</
dependency
>
|
设置filter
编辑web.xml，然后粘贴下面的代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
<!-- 用于单点退出，该过滤器用于实现单点登出功能，可选配置-->
<
listener
>
<
listener-class
>org.jasig.cas.client.session.SingleSignOutHttpSessionListener</
listener-class
>
</
listener
>
<!-- 该过滤器用于实现单点登出功能，可选配置。 -->
<
filter
>
<
filter-name
>CAS Single Sign Out Filter</
filter-name
>
<
filter-class
>org.jasig.cas.client.session.SingleSignOutFilter</
filter-class
>
</
filter
>
<
filter-mapping
>
<
filter-name
>CAS Single Sign Out Filter</
filter-name
>
<
url-pattern
>/*</
url-pattern
>
</
filter-mapping
>
<!-- 该过滤器负责用户的认证工作，必须启用它 -->
<
filter
>
<
filter-name
>CASFilter</
filter-name
>
<
filter-class
>org.jasig.cas.client.authentication.AuthenticationFilter</
filter-class
>
<
init-param
>
<
param-name
>casServerLoginUrl</
param-name
>
<!--这里的server是服务端的IP-->
</
init-param
>
<
init-param
>
<
param-name
>serverName</
param-name
>
</
init-param
>
</
filter
>
<
filter-mapping
>
<
filter-name
>CASFilter</
filter-name
>
<
url-pattern
>/*</
url-pattern
>
</
filter-mapping
>
<!-- 该过滤器负责对Ticket的校验工作，必须启用它 -->
<
filter
>
<
filter-name
>CAS Validation Filter</
filter-name
>
<
filter-class
>
org.jasig.cas.client.validation.Cas20ProxyReceivingTicketValidationFilter</
filter-class
>
<
init-param
>
<
param-name
>casServerUrlPrefix</
param-name
>
</
init-param
>
<
init-param
>
<
param-name
>serverName</
param-name
>
</
init-param
>
</
filter
>
<
filter-mapping
>
<
filter-name
>CAS Validation Filter</
filter-name
>
<
url-pattern
>/*</
url-pattern
>
</
filter-mapping
>
<!--
该过滤器负责实现HttpServletRequest请求的包裹，
比如允许开发者通过HttpServletRequest的getRemoteUser()方法获得SSO登录用户的登录名，可选配置。
-->
<
filter
>
<
filter-name
>CAS HttpServletRequest Wrapper Filter</
filter-name
>
<
filter-class
>
org.jasig.cas.client.util.HttpServletRequestWrapperFilter</
filter-class
>
</
filter
>
<
filter-mapping
>
<
filter-name
>CAS HttpServletRequest Wrapper Filter</
filter-name
>
<
url-pattern
>/*</
url-pattern
>
</
filter-mapping
>
<!--
该过滤器使得开发者可以通过org.jasig.cas.client.util.AssertionHolder来获取用户的登录名。
比如AssertionHolder.getAssertion().getPrincipal().getName()。
-->
<
filter
>
<
filter-name
>CAS Assertion Thread Local Filter</
filter-name
>
<
filter-class
>org.jasig.cas.client.util.AssertionThreadLocalFilter</
filter-class
>
</
filter
>
<
filter-mapping
>
<
filter-name
>CAS Assertion Thread Local Filter</
filter-name
>
<
url-pattern
>/*</
url-pattern
>
</
filter-mapping
>
<!-- 自动根据单点登录的结果设置本系统的用户信息 -->
<
filter
>
<
display-name
>AutoSetUserAdapterFilter</
display-name
>
<
filter-name
>AutoSetUserAdapterFilter</
filter-name
>
<
filter-class
>com.wsria.demo.filter.AutoSetUserAdapterFilter</
filter-class
>
</
filter
>
<
filter-mapping
>
<
filter-name
>AutoSetUserAdapterFilter</
filter-name
>
<
url-pattern
>/*</
url-pattern
>
</
filter-mapping
>
<!-- ======================== 单点登录结束 ======================== -->
|
每个Filter的功能我就不多说了，都有注释的，关键要解释一下AutoSetUserAdapterFilter的作用和原理. 查看完整的web.xml请 猛击这里
利用AutoSetUserAdapterFilter自动根据CAS信息设置Session的用户信息
先看一下这个AutoSetUserAdapterFilter.java的源码
好的，如果你是老程序员应该很快就清楚Filter的目的，如果不太懂我再讲解一下； 主要是通过CAS的const_cas_assertion获取从CAS服务器登陆的用户名，然后再根据系统内部的用户工具（UserUtil.java）来判断是否已经登录过，如果没有登录根据登录名从数据库查询用户信息，最后使用设置把用户信息设置到当前session中。 这样就把用户信息保存到了Sessino中，我们就可以通过UserUtil工具来获取当前登录的用户了，我在实例项目中也加入了此功能演示，请看代码：main.jsp的第44行处
补充一下：
如果是为一个老项目添加单点登录功能，那么基本不需要其他的修改，设置好上面的filter即可；当然最好获取用户信息的地方都调用一个工具类，统一管理不容易出错。
九、单点退出
这个比较简单，把你的退出链接设置为：https://sso.wsria.com/cas/logout 即可。
十、美化CAS服务器界面
CAS服务端(cas-server)的界面只能在测试的时候用一下，真正系统上线肯定需要定制开发自己的页面，就像网易和CSDN的统一认证平台一样，所有子系统的认证都通过此平台来转接，大家可以根据他们的页面自己定制出适合所属应用或者公司的界面；简单介绍一下吧，复制 cas\WEB-INF\view\jsp\default\ui的一些JSP文件，每一个文件的用途文件名已经区分了，自己修改了替换一下就可以了。 例如：
- 登录界面：casLoginView.jsp
- 登录成功：casGenericSuccess.jsp
- 登出界面：casLogoutView.jsp
十一、结束语
花了一下午时间终于写完了，总共十项也算完美了。 现在看来起始利用CAS实现单点登录其实不难，不要畏惧，更不要排斥！ 本教程后面的代码部分均来自http://code.google.com/p/wsria的项目分支wsria-demo-sso
和本教程相关资料下载
到此本教程全部结束，希望看完后对你有帮助，如果有帮助还望继续推荐给其他人，有说明意见或者问题请回复或者IM联系我。
MySQL
mysql [database] -p -u user [-h host-name] [<create.sql]
pw:xxxx
mysqlshow [database] -p -u user
pw:xxxx
mysql>create user 'user'@'localhost' indentified by 'xxxx';
mysql>grant all on database.* to 'user'@'localhost';
mysql> insert into president values('w','f','q','cas','d','1992-02-03',null),
('22','444','444','see','11','1993-04-05',null); //多条
mysql> insert into president set last_name='we',first_name='ee',city='beij',stat
e='ee',birth='1992-03-03';//其他按默认值
mysql> source mysqlsql\insert-president.sql
mysql>load data infile 'member.txt' into table member;//加载数据（如果主机无权限，试试mysql --local-infile database启动）
mysqlimport --local absurd member.txt
NULL 无数据、未知数据
必须用IS NULL 或者IS NOT NULL
空值排序。升序将出现在查询结果开头，降序在末尾。如果要它一定出现在末尾，增加一个排序数据区分NULL和非NULL。
如降序要让null在前面
mysql> select * from president order by death desc;
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| dfs | gbs | NULL | beij | z | 1992-01-03 | 2013-11-23 |
| dfs | dddd | NULL | beij | z | 1992-01-03 | 2011-11-23 |
| dfs | gbs | NULL | beij | z | 1992-01-03 | 1996-11-23 |
| dfs | gbs | NULL | beij | z | 1992-01-03 | 1995-11-23 |
| w | f | q | cas | d | 1992-02-03 | NULL |
| 22 | 444 | 444 | see | 11 | 1993-04-05 | NULL |
| we | ee | NULL | beij | ee | 1992-03-03 | NULL |
| ddddd | dddd | NULL | beij | z | 1992-01-03 | NULL |
+-----------+------------+--------+------+-------+------------+------------+
8 rows in set (0.00 sec)
mysql> select * from president order by if(death is null,0,1) ,death desc;
+-----------+------------+--------+------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+------------+
| w | f | q | cas | d | 1992-02-03 | NULL |
| 22 | 444 | 444 | see | 11 | 1993-04-05 | NULL |
| we | ee | NULL | beij | ee | 1992-03-03 | NULL |
| ddddd | dddd | NULL | beij | z | 1992-01-03 | NULL |
| dfs | gbs | NULL | beij | z | 1992-01-03 | 2013-11-23 |
| dfs | dddd | NULL | beij | z | 1992-01-03 | 2011-11-23 |
| dfs | gbs | NULL | beij | z | 1992-01-03 | 1996-11-23 |
| dfs | gbs | NULL | beij | z | 1992-01-03 | 1995-11-23 |
+-----------+------------+--------+------+-------+------------+------------+
8 rows in set (0.00 sec)
IF对第一个参数求值，遇到空就为0非空就为1（第二个参数是第一个参数为真的结果）
mysql> select * from president order by if(death is null,0,1),death desc
-> limit 5;//行数限制
mysql> select * from president order by if(death is null,0,1),death desc
-> limit 2,5;//跳过的个数和返回的个数
mysql> select * from president order by if(death is null,0,1),death desc
-> ,rand() limit 1;//随机返回一条
mysql> select 17,format(sqrt(25+11),3);
+----+-----------------------+
| 17 | format(sqrt(25+11),3) |
+----+-----------------------+
| 17 | 6.000 |
+----+-----------------------+
1 row in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2015-08-19 |
+------------+
1 row in set (0.00 sec)
YEAR(),MONTH(),DAYOFMONTH()
天数差别
（to_days(time)）转化为天数
select last_name,first_name,birth,death,
timestampdiff(year,birth,death) as age
from president where death is not null
order by age desc;
mysql> source mysqlsql/diffage.sql
+-----------+------------+------------+------------+------+
| last_name | first_name | birth | death | age |
+-----------+------------+------------+------------+------+
| dfs | gbs | 1992-01-03 | 2013-11-23 | 21 |
| dfs | dddd | 1992-01-03 | 2011-11-23 | 19 |
| dfs | gbs | 1992-01-03 | 1996-11-23 | 4 |
| dfs | gbs | 1992-01-03 | 1995-11-23 | 3 |
+-----------+------------+------------+------------+------+
4 rows in set (0.00 sec)
mysql> select date_add('1999-01-01',interval 10 year);
+-----------------------------------------+
| date_add('1999-01-01',interval 10 year) |
+-----------------------------------------+
| 2009-01-01 |
+-----------------------------------------+
1 row in set (0.00 sec)
模式匹配
% _
mysql> select * from president
-> where first_name like 'e%'
-> ;
+-----------+------------+--------+------+-------+------------+-------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+-------+
| we | ee | NULL | beij | ee | 1992-03-03 | NULL |
+-----------+------------+--------+------+-------+------------+-------+
1 row in set (0.00 sec)
mysql> select * from president
-> where first_name like '_'
-> ;
+-----------+------------+--------+------+-------+------------+-------+
| last_name | first_name | suffix | city | state | birth | death |
+-----------+------------+--------+------+-------+------------+-------+
| w | f | q | cas | d | 1992-02-03 | NULL |
+-----------+------------+--------+------+-------+------------+-------+
1 row in set (0.00 sec)
变量
mysql> select @birth :=birth from president
-> where first_name like '_';
+----------------+
| @birth :=birth |
+----------------+
| 1992-02-03 |
+----------------+
1 row in set (0.00 sec)
mysql> set @today=curdate();
Query OK, 0 rows affected (0.00 sec)
mysql> select @today;
+------------+
| @today |
+------------+
| 2015-08-19 |
+------------+
1 row in set (0.00 sec)
mysql> select count(distinct state) from president;
+-----------------------+
| count(distinct state) |
+-----------------------+
| 4 |
+-----------------------+
1 row in set (0.00 sec)
unix下~/my.conf windows c://my.conf
[client]
host=
user=
password=
chmod 600 my.conf
show character set//charset服务器所支持的字符集的名称
show collation//该字符集排序方式的名字
mysql> show variables like 'character\_set\_%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+--------------------------+--------+
7 rows in set (0.00 sec)
mysql> show variables like 'collation\_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
当前数据库
查看定义
mysql> show create database absurd\G
*************************** 1. row ***************************
Database: absurd
Create Database: CREATE DATABASE `absurd` /*!40100 DEFAULT CHARACTER SET utf8 */
· MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。
· InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。InnoDB也支持FOREIGN KEY强制。
InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
· BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。
· Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。
· Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。
· Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。数据行插入后不可再修改
· Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。
· Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。
· Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应 用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。
创建表时最好用drop table if exist语句，再执行一条if not exist 短语的create table
临时数据表
create temporary table table_name ...
Query OK, 0 rows affected (0.12 sec)
mysql> desc new_diff_table
-> ;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| last_name | varchar(15) | NO | | NULL | |
| first_name | varchar(15) | NO | | NULL | |
| suffix | varchar(5) | YES | | NULL | |
| city | varchar(20) | NO | | NULL | |
| state | varchar(2) | NO | | NULL | |
| birth | date | NO | | NULL | |
| death | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
复制表结构
自连接：
select a.* from student a,student b where a.math>b.english and a.id=b.id
MySQL索引的类型
1. 普通索引
这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
01 | –直接创建索引 |
02 | CREATE INDEX index_name ON table(column(length)) |
03 | –修改表结构的方式添加索引 |
04 | ALTER TABLE table_name ADD INDEX index_name ON (column(length)) |
05 | –创建表的时候同时创建索引 |
06 | CREATE TABLE `table` ( |
07 | `id` int(11) NOT NULL AUTO_INCREMENT , |
08 | `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , |
09 | `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , |
10 | `time` int(10) NULL DEFAULT NULL , |
11 | PRIMARY KEY (`id`), |
12 | INDEX index_name (title(length)) |
13 | ) |
14 | –删除索引 |
15 | DROP INDEX index_name ON table |
2. 唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。
01 | –创建唯一索引 |
02 | CREATE UNIQUE INDEX indexName ON table(column(length)) |
03 | –修改表结构 |
04 | ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)) |
05 | –创建表的时候直接指定 |
06 | CREATE TABLE `table` ( |
07 | `id` int(11) NOT NULL AUTO_INCREMENT , |
08 | `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , |
09 | `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , |
10 | `time` int(10) NULL DEFAULT NULL , |
11 | PRIMARY KEY (`id`), |
12 | UNIQUE indexName (title(length)) |
13 | ); |
3. 全文索引（FULLTEXT）
MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
01 | –创建表的适合添加全文索引 |
02 | CREATE TABLE `table` ( |
03 | `id` int(11) NOT NULL AUTO_INCREMENT , |
04 | `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , |
05 | `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , |
06 | `time` int(10) NULL DEFAULT NULL , |
07 | PRIMARY KEY (`id`), |
08 | FULLTEXT (content) |
09 | ); |
10 | –修改表结构添加全文索引 |
11 | ALTER TABLE article ADD FULLTEXT index_content(content) |
12 | –直接创建索引 |
13 | CREATE FULLTEXT INDEX index_content ON article(content) |
4. 单列索引、多列索引
多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
5. 组合索引（最左前缀）
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：
1 | –使用到上面的索引 |
2 | SELECT * FROM article WHREE title='测试' AND time=1234567890; |
3 | SELECT * FROM article WHREE utitle='测试'; |
4 | –不使用上面的索引 |
5 | SELECT * FROM article WHREE time=1234567890; |
alter table table_name modify i int;
alter table table_name change i i int;
重命名表
alter table table_name rename to new_tab_name;
rename table old_name to new_name;
加数据库前缀。从一个库移动到另外一个库
获取元数据
show databases;
show create database db_name;
show tables [from db_name] ;
show columns from tb_name;
show index from tb_name;
show table status;
cross join/inner join/join
left join on...
子查询 in
关系比较操作符（只能一个）
exists 返回0或1
事务；原子性、隔离性、可靠性、稳定性
start transaction;
...
...
commit;
start transaction;
...
...
rollback;
Query OK, 0 rows affected (0.21 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.04 sec)
mysql> savepoint my_savepoint;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(2);
Query OK, 1 row affected (0.00 sec)
mysql> rollback to savepoint my_savepoint;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from t;
+------+
| i |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)
存储函数、存储过程
create function fun_name(p INT)
returns int
begin
return(.....)
end
create procedure pro_name(p INT)
begin
...
end
触发器
create trigger tri_name
{before|after}
{insert|update|delete}
on tab_name
for each row tri_stmt..;
事件
[mysqld]
event_scheduler=ON
create event event_name
on schedule every 4 hour
do
..........;
alter event event_name disable;//enable
Oracle
1、约束
约束用于确保数据库数据满足特定的商业规则。在Oracle中，约束包括：not null、unique、primary key， foreign key和check五种。
A、not null（非空）
如果在列上定义了not null，那么当插入数据时，必须为列提供数据。
B、unique（唯一）
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
C、primary key（主键）
用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且不能为NULL。一张表最多只能有一个主键，但是可以由多个unique约束。
D、foreign key（外键）
用于定义主表和从表之间的关系，外键约束要定义在从表上，主要则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为NULL。
E、check
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000～2000之间，如果不在1000～2000之间就会提示出错。
字符串的链接操作用: ||
1. 创建表空间
create tablespace schooltbs datafile ‘D:\oracle\datasource\schooltbs.dbf’ size 10M autoextend on;
2. 删除表空间
drop tablespace schooltbs[including contents and datafiles];
3. 查询表空间基本信息
select *||tablespace_name from DBA_TABLESPACES;
4. 创建用户
create user lihua
identified by lihua
default tablespace schooltbs
temporary tablespace temp;
5. 更改用户
alter user lihua
identified by 123
default tablespace users;
6. 锁定用户
alter user lihua account lock|unlock;
7. 删除用户
drop user lihua cascade;--删除用户模式
8. oracle数据库中的角色
connect,dba,select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database,resource
9. 授予连接服务器的角色
grant connect to lihua;
10.授予使用表空间的角色
grant resource to lihua with grant option;--该用户也有授权的权限
11.授予操作表的权限
grant select,insert on user_tbl to scott;--当前用户
grant delete,update on lihua.user_tbl to scott;--系统管理员
12.修改表的结构(alter)
Alter table 表名 add(列的名称,列的类型);
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
