数据库(2):sql语句例子

--------------------------------------------------------------------------------------------------------------

1． 数据库登陆

--------------------------------------------------------------------------------------------------------------

1.登陆数据库

1.本地方式： mysql -u root

2.远程方式： mysql -h xxxx -P 20003 -u root -p 回车然后输入密码

例: mysql -hxxxx -P4007 -uxxxx -pxxxxapplications --default-character-set=utf8

 

--------------------------------------------------------------------------------------------------------------

2, 创建,显示表结构,删除表

------------------------------------------------------------------------------------------------------------

1.创建表: CREATE TABLE tablename

CREATE TABLE IF NOT EXISTS act_shendian_taotai_zuanshi (

ch_id int(11) unsigned NOT NULL COMMENT '公会id',

PRIMARY KEY(ch_id)

) ENGINE = InnoDB DEFAULT CHARSET = utf8;

 

2.显示表结构的方法:

方法1：desc tablename;

方法2:show create table tablename;

方法3:use information_schema;select * from columns where table_name='tablename'

 

2. 删除表

DROP TABLE table_name;

 

--------------------------------------------------------------------------------------------------------------

3, EXISTS 的使用

------------------------------------------------------------------------------------------------------------

1. 判断是不是存在，用在创建表和删除表中:

A,创建表: CREATE TABLE IF NOT EXISTS act_shendian_taotai_zuanshi ch_id int(11) unsigned NOT NULL COMMENT '公会id',PRIMARY KEY(ch_id)) ENGINE = InnoDB DEFAULT CHARSET = utf8;

B,删除表:DROP TABLE IF EXISTS `toutiao_duanwei`;

2.用在查询语句用:

SELECT c.CustomerId, CompanyName

FROM Customers c

WHERE EXISTS(

SELECT OrderID FROM Orders o

WHERE o.CustomerID = cu.CustomerID)

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False.

--------------------------------------------------------------------------------------------------------------

4, index 创建，修改表索引

------------------------------------------------------------------------------------------------------------

1.查询索引：

show index from tablename;

或者 show keys from tablename;

或者 SELECT INDEX_NAME,INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME=’temp_index’;

索引类型

BTREE 适合连续读取数据

RTREE 适合根据一条数据找附近的数据

HASH 适合随机读取数据

FULLTEXT

SPATIAL

 

2.创建索引

允许使用重复的值

CREATE INDEX index_name ON table_name (column_name)

注释："column_name" 规定需要索引的列。当用 create index 创建索引时,必须指定索引的名字，否则mysql会报错；

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值

CREATE UNIQUE INDEX index_name ON table_name (column_name)

如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC

CREATE INDEX PersonIndex ON Person (LastName DESC)

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开

CREATE INDEX PersonIndex ON Person (LastName, FirstName)

 

或者: ALTER TABLE actor ADD INDEX idx_actor_first_name (first_name);

用 ALTER TABLE 创建索引时，可以不指定索引名字，若不指定mysql会自动生成索引名字

 

3. 删除索引: DROP INDEX idx_actor_fname ON actor;

或 ALTER TABLE actor DROP INDEX idx_actor_fname;

 

--------------------------------------------------------------------------------------------------------------

5, 修改表ALTER

------------------------------------------------------------------------------------------------------------

1. 如需在表中添加列

ALTER TABLE table_name ADD column_name datatype;

如:ALTER TABLE `broadcast_text_cfg` ADD pumping_num int(11) NOT NULL DEFAULT '1' COMMENT 'n连抽';

2. 要删除表中的列

ALTER TABLE table_name DROP COLUMN column_name

3.要改变表中列的数据类型

ALTER TABLE table_name ALTER COLUMN column_name datatype

 

--------------------------------------------------------------------------------------------------------------

6．使用命令将数据导入到数据库

--------------------------------------------------------------------------------------------------------------

3. 使用命令直接将备份的.dmp数据导入到数据库(将.sql文件导入到数据库也是这样的)

1.将数据导入到指定数据库 (导入.dmp文件或者.sql文件)

方法一:使用mysql 导入

~#:mysql -u root card_game < ./xxx.dmp 或者(有密码) mysql -u root -p card_game < ./xxx.dmp

有时候可能需要指定表 ~#:mysal -u root card_game chess <./xxx.sql

例:mysql -hxxxx -P4001 -uxxxx -p123qwe activity --default-character-set=utf8 <./sheng.sql

 

方法二：使用mysql语句导入(使用source命令)

a.进入mysql : mysql -uroot -p 回车 输入密码

b. > source /home/user/data/fimeName.sql (需要指定路径)

 

--------------------------------------------------------------------------------------------------------------

7．使用命令获取数据库数据

------------------------------------------------------------------------------------------------------------

1.. 将mysql中的数据导出到.sql文件中 （注意是使用mysqldump）

方法一：使用mysqldump 命令 （注意一下操作可能需要权限，否则 会报“to database 'lottery' when doing LOCK TABLES” 异常）

a.将整个数据库导入到sql文件

~#:mysqldump -u root -p xxx card_game chess > ~/XXX.sql

b.将数据库中的某个表导入到sql

~#: mysqldump -u userName -p dabaseName tableName > fileName.sql

或者带上ip: mysqldump -hxxxx -P6306 -uyymusic_rw -prevenue_lottery lottery broadcast_text_cfg > ~/broad_cast.sql

c.导出数据库中的所有的表结构 （注意加了 -d）

~#: mysqldump -u userName -p -d dabaseName > fileName.sql

d.导出整个数据库中的某个表的表结构 (注意加了 -d)

~#: mysqldump -u userName -p -d dabaseName tableName > fileName.sql

报"to database 'lottery' when doing LOCK TABLES" 类似的异常的时候是因为该用户没有lock tables 权限,处理办法:

1. 给该用户赋予lock tables 权限，建议是删除该用户，重新用mysql命令建

2. 加上 --skip-lock-tables 即可: mysqldump -uroot -p dabaseName --skip-lock-tables > fileName.sql

 

 

方法二 : 使用mysql命令

将指定表中的数据通过select查找到导出 (注意加了 -N -e)

~#: mysql -hxxxx -P6306 -uxxxx -prevenue_lottery lottery --default-character-set=utf8 -N -e "select * from broadcast_text_cfg" > broad_cast.sql

~#:echo "select * from broadcast_text_cfg" | mysql -h119.147.177.208 -P6306 -uyymusic_rw -prevenue_lottery lottery --default-character-set=utf8 >broad_cast.sql

 

--------------------------------------------------------------------------------------------------------------

8, 设置权限

------------------------------------------------------------------------------------------------------------

4.权限设置 （设置文件的属主改为mysql:nysql ,权限改为660）

chown mysq:mysql /var/lib/mysql/mydb/*

shmod 660 /var/lib/mysql/mydb/*

 

--------------------------------------------------------------------------------------------------------------

9, 查询,插入,修改,删除

------------------------------------------------------------------------------------------------------------

1. 查询: select * from table_name where uid = 1;

Order by xxx asc ; 升序 order by xxx desc;降序 group by 按照什么分组

如:SELECT phase, type, ch_id, sum(ticket), sum(jifen), sum(ticket) as total_ticket, sum(rank) as total_rank FROM act_shendian_jifen_20160922 GROUP BY phase, type, ch_id ORDER BY sum(jifen) desc,total_ticket desc, total_rank asc;

A.查询个数的时候

通常做法用count(*) 或者count(1) 这两个的效率是一样的，如 select count(*) from `mytable`;

如果有主键可以使用count(key) 效率会稍微高一些，但是总体来说还是比较低，因为count是全表遍历检索

可以使用系统表查询：SELECT TABLE_ROWS FROM information_game.`TABLES` WHERE TABLE_SCHEMA ="数据库名" and TABLE_NAME ="表名";

 

A. 自连接：

公交线路表： route(num, company, pos, stop)

SELECT * FROM route R1, route R2 WHERE R1.num=R2.num AND R1.company=R2.company

我们route表用字段(num, company)来进行自连接．你可以知道每条公交线路的任意两个可联通的车站。

B. 左连接:

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

C. 右连接

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

D. 全连接:

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

E.合并表查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2

select * from ((select * from tabe1) union all (select * from table) as mytable where uid = 1

例:将几个表合并输出:

select `id`, `time1` FROM ((select * FROM`game_test_1`) UNION all (select * FROM`game_test_2`) UNION ALL (select * FROM`game_test_1`)) game_test where `id` =1 or 2 ORDER BY `id`;

 

2. 修改表数据 :update `SingerInfo_20160621` t set t.contractId=34631725,t.type = 5 where t.uid = 1103718327;

 

3. 插入数据：insert into act_shendian_jinji_20160922( ch_id, phase,type,rank,sort) values (31113, 1, 4, 4,2000);

 

4. 删除数据: 可以使用delete/truncate/drop

1. delete from table_name where uid = 1;

2.truncate table 表名;

不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。

效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

 

5. 查询插入:SELECT INTO

SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing'

--------------------------------------------------------------------------------------------------------------

10, 插入数据时的冲突处理ignore/replace/on duplicate key update)

------------------------------------------------------------------------------------------------------------

1. mysql 忽略字段冲突的几种方式：(ignore/replace/on duplicate key update)

第一：使用ignore：

应用1：如果主键(或者是组合主键)重复直接忽略插入，执行后返回0，不报错

insert ignore into table_name(uid) values (1);

应用2：复制表，避免重复

insert ignore into table(name) select name from table2;

第二：使用replace：

入新的数据，当主键(或者唯一关键字)重复，就删除旧的数据，插入新的数据

replace into table(uid,...) values(1,..);

replace into table(uid,...) select ...;

replace into table set uid=1,...;

第三：使用on duplicate key update

插入数据时，如果数据已经存在就更新指定字段；

注意使用下面第一种方法：注意必须将所有字段写出来，否则会覆盖掉没有列出的字段，或者出现一条新的无意义的数据

insert into chess (uid, credit) values (6429182, 14110) on duplicate key update credit=14110;

insert into table select * from table2 on duplicate key update table.uid =table2.uid

如下：如果key不存在就插入，如果存在就将star的值加10

INSERT INTO crx2016_xunzhang_star(uid,star) VALUES (1111,10) ON DUPLICATE KEY UPDATE star=star+10;

--------------------------------------------------------------------------------------------------------------

11, 条件语句

------------------------------------------------------------------------------------------------------------

1. 判断值的合理性:

如果key存在就修改变化的值，没有key就插入; 且使用IF语句计算hot的值(IF(公式1，公式2，公式3)，如果公式1成立就返回公式2否则返回公式3)

INSERT INTO `act_hot`(`id`, `hot`, `hit_time`, `state`, `last_send`)
VALUES(:s0, :1, :2, :3, :4) ON DUPLICATE KEY UPDATE
`hot`=IF((CURRENT_TIMESTAMP -`last_send`)*1 div 15>=`hot`, 100,

`hot`-(CURRENT_TIMESTAMP-`last_send`)*:1 div 15)+10

 

--------------------------------------------------------------------------------------------------------------

12．查看mysql性能，除了看语句执行时间，还可以使用explain 进行检查

------------------------------------------------------------------------------------------------------------

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

例:explain select * from statuses_status where id=11;

explain select * from SingerInfo_20160922 where uid = 1350621583\G // \G是竖行显示

 

explain列的解释

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

key： 实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：mysql认为必须检查的用来返回请求数据的行数

extra：关于mysql如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是using temporary和using filesort，意思mysql根本不能使用索引，结果是检索会很慢

 

extra列返回的描述的意义

distinct:一旦mysql找到了与行相联合匹配的行，就不再搜索了

not exists: mysql优化了left join，一旦它找到了匹配left join标准的行，就不再搜索了

range checked for each record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

using filesort: 看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

using temporary 看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上

where used 使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型all或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为mysql先读这个值然后把它当做常数来对待

eq_ref:在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比all更好，因为索引一般小于表数据）

all:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

 

 

--------------------------------------------------------------------------------------------------------------

13． Show用法

------------------------------------------------------------------------------------------------------------

1. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。

2. show databases; -- 显示mysql中所有数据库的名称。

3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。

4. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。

5. show index from table_name; -- 显示表的索引。

6. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。

7. show variables; -- 显示系统变量的名称和值。

8. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

9. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。

10. show privileges; -- 显示服务器所支持的不同权限。

11. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。

12. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 或desc tablename;

13. show engines; -- 显示安装以后可用的存储引擎和默认引擎。

14. show innodb status; -- 显示innoDB存储引擎的状态。

15. show logs; -- 显示BDB存储引擎的日志。

16. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。

17. show errors; -- 只显示最后一个执行语句所产生的错误。

18. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

 

 

示例：

1 显示该数据库连接使用的字符集的情况

show variables like '%char%';

2 查看MySQL服务器运行的各种状态值

show global status;

3 连接数

show variables like 'max_connections'; // 查询最大连接数

show global status like 'max_used_connections'; // 查询用户响应连接数

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

4 key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb

mysql> show variables like 'key_buffer_size';

| key_buffer_size | 67108864 |

mysql> show global status like 'key_read%';

| Key_read_requests | 25629497 |

| Key_reads | 66071 |

一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27%

需要适当加大key_buffer_size

mysql> show global status like 'key_blocks_u%';

| Key_blocks_unused | 10285 |

| Key_blocks_used | 47705 |

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

5 open table 的情况

mysql> show global status like 'open%tables%';

| Open_tables | 1024 |

| Opened_tables | 1465 |

Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_cache值

mysql> mysql> show variables like 'table_cache';

| table_cache | 1024 |

Open_tables / Opened_tables * 100% =69% 理想值 （>= 85%）

Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)

6 进程使用情况

mysql> show global status like 'Thread%';

| Threads_cached | 31 |

| Threads_connected | 239 |

| Threads_created | 2914 |

| Threads_running | 4 |

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应 下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的 话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：

mysql> show variables like 'thread_cache_size';

| thread_cache_size | 32 |

7, 查询缓存(query cache)

mysql> show global status like 'qcache%';

| Qcache_free_blocks | 2226 |

| Qcache_free_memory | 10794944 |

| Qcache_hits | 5385458 |

| Qcache_inserts | 1806301 |

| Qcache_lowmem_prunes | 433101 |

| Qcache_not_cached | 4429464 |

| Qcache_queries_in_cache | 7168 |

| Qcache_total_blocks | 16820 |

Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

Qcache_free_memory：缓存中的空闲内存。

Qcache_hits：每次查询在缓存中命中时就增大

Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这 个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks和free_memory可以告诉您属于哪种情况）

Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。

Qcache_total_blocks：缓存中块的数量。

我们再查询一下服务器关于query_cache的配置：

mysql> show variables like 'query_cache%';

| query_cache_limit | 33554432 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 33554432 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

各字段的解释：

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小

query_cache_size：查询缓存大小

query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服务器 查询缓存碎片率＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

 

8 文件打开数(open_files)

mysql> show global status like 'open_files';

| Open_files | 821 |

mysql> show variables like 'open_files_limit';

| open_files_limit | 65535 |

比较合适的设置：Open_files / open_files_limit * 100% <= 75％

正常

 

9 表锁情况

mysql> show global status like 'table_locks%';

| Table_locks_immediate | 4257944 |

| Table_locks_waited | 25182 |

Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些.

 

10 表扫描情况

mysql> show global status like 'handler_read%';

| Handler_read_first | 108763 |

| Handler_read_key | 92813521 |

| Handler_read_next | 486650793 |

| Handler_read_prev | 688726 |

| Handler_read_rnd | 9321362 |

| Handler_read_rnd_next | 153086384 |

各字段解释参见http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html，调出服务器完成的查询请求次数：

mysql> show global status like 'com_select';

| Com_select | 2693147 |

计算表扫描率：

表扫描率 ＝ Handler_read_rnd_next / Com_select

如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

 

--------------------------------------------------------------------------------------------------------------

14．时间处理(mysql)

------------------------------------------------------------------------------------------------------------

下面的表格列出了 MySQL 中最重要的内建日期函数：

函数 描述

NOW() 返回当前的日期和时间

CURDATE() 返回当前的日期

CURTIME() 返回当前的时间

DATE() 提取日期或日期/时间表达式的日期部分

EXTRACT() 返回日期/时间按的单独部分

DATE_ADD() 给日期添加指定的时间间隔

DATE_SUB() 从日期减去指定的时间间隔

DATEDIFF() 返回两个日期之间的天数

DATE_FORMAT() 用不同的格式显示日期/时间

to_days(`begin_time`)-to_days(`end_time`), 表示获取时间差

extract(year_month from `modify_time`) 表示取一个时间里面的年份和月份

 

1. 将查询的数据以日期表示

SELECT *, FROM_UNIXTIME(createtime) FROM `chess` order by createtime desc limit 1;

2. 将日志转换为数值

SELECT ch_id, round, ticket, jifen, rank, type, UNIX_TIMESTAMP(`updatetime`) from teble1；

 

3.将查询的日期数据"yy-mm-dd hh:mm:ss"转换为64位长整形字符串 (使用unix_timestamp()函数)

"select `create_time`, `begin_date`, `disables_date`,to_days(`disables_date`)-to_days(`begin_date`),

`status`,`agent_id`, unix_timestamp(create_time), unix_timestamp(begin_date) from `guardian_info_detail` where `uid`=:0 and extract(year_month from `create_time`) = :s1 order by `create_time` desc limit :2, :3";

 

4.（时间减法,获取天数差, 日期的年月）将搜到的数据进行减法处理并产生一个新的字段名（注意要使用括号）（使用函数to_days() 获取日期天数，year_month from 获取日期天数 , extract将新字段名别名）

SELECT * from(select `modify_time`, `begin_time`, `end_time`, to_days(`end_time`)-to_days(`begin_time`),

`noble_type`, extract(year_month from `modify_time`) as `pay_date` from `noble_history`
where `uid`="1057478647" order by `modify_time` desc limit 1, 100) as mytable WHERE `pay_date` = "201410"

 

-------------------------------------------------------------------------------------------------------------

15．函数集合(不同的平台可能支持的函数不同)

------------------------------------------------------------------------------------------------------------

一、数学函数

ABS(x) 返回x的绝对值

BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）

OCT(N) 返回八进制

HEX(N) 返回16进制

CEILING(x) 返回大于x的最小整数值

EXP(x) 返回值e（自然对数的底）的x次方

FLOOR(x) 返回小于x的最大整数值

GREATEST(x1,x2,...,xn) 返回集合中最大的值

LEAST(x1,x2,...,xn) 返回集合中最小的值

LN(x) 返回x的自然对数

LOG(x,y) 返回x的以y为底的对数

MOD(x,y) 返回x/y的模（余数）

PI() 返回pi的值（圆周率）

RAND() 返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

ROUND(x,y) 返回参数x的四舍五入的有y位小数的值

SIGN(x) 返回代表数字x的符号的值

SQRT(x) 返回一个数的平方根

TRUNCATE(x,y) 返回数字x截短为y位小数的结果

 

二、聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col) 返回指定列的平均值

COUNT(col) 返回指定列中非NULL值的个数

MIN(col) 返回指定列的最小值

MAX(col) 返回指定列的最大值

SUM(col) 返回指定列的所有值之和

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

 

三、字符串函数

ASCII(char) 返回字符的ASCII码值

BIT_LENGTH(str) 返回字符串的比特长度

CONCAT(s1,s2...,sn) 将s1,s2...,sn连接成字符串

CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔

INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

FIND_IN_SET(str,list) 分析逗号分隔的list列表，如果发现str，返回str在list中的位置

LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果

LEFT(str,x) 返回字符串str中最左边的x个字符

LENGTH(s) 返回字符串str中的字符数 OCTET_LENGTH(str) ， CHAR_LENGTH(str) , CHARACTER_LENGTH(str)

LTRIM(str) 从字符串str中切掉开头的空格

POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置

QUOTE(str) 用反斜杠转义str中的单引号

REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果

REVERSE(str) 返回颠倒字符串str的结果

RIGHT(str,x) 返回字符串str中最右边的x个字符

RTRIM(str) 返回字符串str尾部的空格

STRCMP(s1,s2) 比较字符串s1和s2

TRIM(str) 去除字符串首部和尾部的所有空格

UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

 

四、日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期

CURTIME()或CURRENT_TIME() 返回当前的时间

DATE_ADD(date,INTERVAL int keyword) 返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);

DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值

DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);

DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date) 返回date是一个月的第几天(1~31)

DAYOFYEAR(date) 返回date是一年的第几天(1~366)

DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);

FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts

HOUR(time) 返回time的小时值(0~23)

MINUTE(time) 返回time的分钟值(0~59)

MONTH(date) 返回date的月份值(1~12)

MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);

NOW() 返回当前的日期和时间

QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

WEEK(date) 返回日期date为一年中第几周(0~53)

YEAR(date) 返回日期date的年份(1000~9999)

一些示例：

获取当前系统时间：SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());

SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);

SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);

SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);

返回两个日期值之间的差值(月数)：SELECT PERIOD_DIFF(200302,199802);

在Mysql中计算年龄：

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;

这样，如果Brithday是未来的年月日的话，计算结果为0。

下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d')

 

五、加密函数

AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储

AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果

DECODE(str,key) 使用key作为密钥解密加密字符串str

ENCRYPT(str,salt)

使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str

ENCODE(str,key)

使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储

MD5() 计算字符串str的MD5校验和

PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

SHA() 计算字符串str的安全散列算法(SHA)校验和

示例：

SELECT ENCRYPT('root','salt');

SELECT ENCODE('xufeng','key');

SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起

SELECT AES_ENCRYPT('root','key');

SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');

SELECT MD5('123456');

SELECT SHA('123456');

 

六、控制流函数

MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。

MySQL控制流函数：

CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default

CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default

IF(test,t,f) 如果test是真，返回t；否则返回f

IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2

NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1

这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。

如：SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');

NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。

如：SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);

和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。

如：SELECTIF(1<10,2,3),IF(56>100,'true','false');

IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。

CASE函数的格式有些复杂，通常如下所示：

CASE [expression to be evaluated]

WHEN [val 1] THEN [result 1]

WHEN [val 2] THEN [result 2]

WHEN [val 3] THEN [result 3]

......

WHEN [val n] THEN [result n]

ELSE [default result]

END

这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。

CASE函数还有另外一种句法，有时使用起来非常方便，如下：

CASE

WHEN [conditional test 1] THEN [result 1]

WHEN [conditional test 2] THEN [result 2]

ELSE [default result]

END

这种条件下，返回的结果取决于相应的条件测试是否为真。

示例：

mysql>SELECT CASE 'green'

WHEN 'red' THEN 'stop'

WHEN 'green' THEN 'go' END;

SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;

SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS;

SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo;

SELECT fname,lname,(math+sci+lit) AS total,

CASE WHEN (math+sci+lit) < 50 THEN 'D'

WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'

WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'

ELSE 'A' END

AS grade FROM marks;

SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一个登陆验证

 

七、格式化函数

DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值

FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数

INET_ATON(ip) 返回IP地址的数字表示

INET_NTOA(num) 返回数字所代表的IP地址

TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值

其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。

示例：

SELECT FORMAT(34234.34323432,3);

SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

SELECT DATE_FORMAT(19990330,'%Y-%m-%d');

SELECT DATE_FORMAT(NOW(),'%h:%i %p');

SELECT INET_ATON('10.122.89.47');

SELECT INET_NTOA(175790383);

 

八、类型转化函数

为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

示例：

SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;

SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

 

九、系统信息函数

DATABASE() 返回当前数据库名

BENCHMARK(count,expr) 将表达式expr重复运行count次

CONNECTION_ID() 返回当前客户的连接ID

FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数

USER()或SYSTEM_USER() 返回当前登陆用户名

VERSION() 返回MySQL服务器的版本

示例：

SELECT DATABASE(),VERSION(),USER();

SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。

 

 

 

 

十,连接相同的表union关键字

在数据库中，UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。

MySQL中的UNION

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys union select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

MySQL中的UNION ALL

而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL，如下：

select * from gc_dfys union all select * from ls_jg_dfys

使用Union，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了DISTINCT

使用Union all，则不会排重，返回所有的行

如果您想使用ORDER BY或LIMIT子句来对全部UNION结果进行分类或限制，则应对单个地SELECT语句加圆括号，并把ORDER BY或LIMIT放到最后一个的后面：

(SELECT a FROM tbl_name WHERE a=10 AND B=1)

UNION

(SELECT a FROM tbl_name WHERE a=11 AND B=2)

ORDER BY a LIMIT 10;

麻烦一点也可以这么干：

select userid from (

select userid from testa union all select userid from testb) t

order by userid limit 0,1;

 

如果你还想group by，而且还有条件，那么：

select userid from (select userid from testa union all select userid from testb) t group by userid having count(userid) = 2;

注意：在union的括号后面必须有个别名，否则会报错

当然了，如果当union的几个表的数据量很大时，建议还是采用先导出文本，然后用脚本来执行

因为纯粹用sql，效率会比较低，而且它会写临时文件，如果你的磁盘空间不够大，就有可能会出错