----------------更新日志------------------
2013/01/04 补充【常用SQL语句】两个表之间数据复制；补充【SQL中常用函数】类型转换
2012/11/18 补充【SQL中常用函数】获取指定时间
2012/09/26 补充【常用SQL语句】创建索引
2012/09/04 增加【异常处理】
2012/08/02 补充【SQL中常用函数】输出到CSV文件
2012/07/03 补充【SQL中常用函数】字符串查找,where in查询结果按照in中的排序
2012/06/15 增加【SQL模糊匹配】
2012/06/01 补充【常用SQL语句】
2012/04/19 补充【常用SQL语句】
2012/04/06 补充【常用SQL语句】left join重复问题
2012/03/30 补充【常用SQL语句】
2012/03/28 补充【SQL中常用函数】
2012/02/23 增加【mysql的日志清除】
2012/01/30 增加 常用SQL语句
2012/01/29 初版
------------------------------------------
目录
【mysql安装】
【常用SQL语句】
【SQL模糊匹配】
【SQL中常用函数】
【mysql安装】
【mysql中的键】
【mysql的sql文本文件】
【mysql的日志清除】
【异常处理】
【navicat for mysql】
【查询大小写区分】
【参考资料】
【SQL模糊匹配】
------------------------------------------
【mysql安装】
安装选项
--with-plugins=myisam,innodb_plugin,innobase
数据库目录
/var/lib/mysql/
配置文件目录
/etc/my.cnf
log目录
/var/lib/mysql/[hostname名].err
初始化数据库：
mysql_install_db --user=mysql
启动：
mysqld start 或者 service mysql start
停止:
mysqld stop
是否支持innodb查询：
show variables like 'have_innodb';
【常用SQL语句】
1)连接数据库
连接远程mysql数据库
mysql -uremoteuser -pmh_db_server -h65.255.42.38
-Djoyhero_user
mysql常用参数：“
--skip-column-names 查询结果不显示表头标题,例如:
mysql --skip-column-names -e "update table set field=xx limit
10;select row_count();"
1)查看表结构信息
show create table mem_tip_info;
2)查看前几条数据
select uid from sys_user limit 2;
SELECT * FROM table LIMIT [offset,] rows |
rows OFFSET offset
SELECT * FROM mytable LIMIT 5,10; // 检索记录行
6-15
3)插入一条数据，若存在则更新某几个字段的值
ON DUPLICATE KEY UPDATE
例如：insert into test2 select * from test1 on duplicate key update
a = 'REMOVE-ME';
http://blog.csdn.net/cheungjustin/article/details/5992445
4)导入表定义sql文件
命令行输入: mysql -h localhost -u root -p mydb2 <
e:\mysql\mydb2.sql
mysql -h localhost -u star -p star < \
5)插入多条数据
insert into sys_mail_system (uid, content_id) values(1, 'a'),
(2, 'b');
替换形式插入：
replace into sys_mail_system (uid, content_id) values(1, 'a'),
(2, 'b');
6)清空表中所有数据
truncate table log_gm_operate; 立即执行
delete from log_gm_operate;
7)修改表数据
update 表名 set 字段=新值,… where 条件
mysql> update MyClass set name='Mary' where
id=1;
8)当前数据库包含的表信息：
mysql> show tables; (注意：最后有个s)
9)显示所有的数据库
命令：show databases (注意：最后有个s)
mysql> show databases;
10)数据库导出导入操作
mysqldump导出database或table：
mysqldump -u -p
[table1 table2]
> dump.sql
例如：
mysqldump -u wcnc -p smgp_apps_wcnc >
wcnc.sql
导出表部分数据
mysqldump -uroot -p'njmysqlurp543' joyhero_index user_index
--where="uid <=200" >
user_index_100.sql
恢复数据库或表
mysql -u username -p password dbname <
filename.sql
其它参数说明：
--databases：
mysqldump后跟的所有都是database，而不是table。这个在需要备份多个数据库的时候有效。
如果使用了--databases参数，则在导入的时候可以不指定数据库，并且数据库可不存在。
例如：
-t or --no-create-info
这个选项使的mysqldump命令不创建CREATE
TABLE语句，这个选项在您只需要数据而不需要DDL(数据库定义语句)时很方便。
-d or --no-data 这个选项使的mysqldump命令不创建INSERT语句。
在您只需要DDL语句时，可以使用这个选项。
11)两个数据库之间拷贝
$mysqldump database [table] | mysql [-h other.host.com]
database1
12)左关联
left join on
where子句
select A.ID as AID, B.ID as BID
from A left join B on A.ID = B.ID where
B.ID<3
关联产生多条数据解决方案：
http://www.forasp.cn/html/2291.html
select product.*,foraspcn.image from product left join(select
imagename,pid from image group by pid) foraspcn on product.pid =
foraspcn.pid;
第二张表现group by滤掉重复数据，GROUP BY 是分组查询, 一般 GROUP BY 是和 聚合函数配合使用,
13)修复表
REPAIR TABLE [表名称];
http://blog.chinaunix.net/space.php?uid=20447986&do=blog&id=1945633
14)用户权限查看和修改
mysql>use
mysql> grant all on *.* TO user12@'127.0.0.1'
identified by "user12" with grant option;
grant all on *.* TO user12@localhost identified by "user12" with
grant option;
mysql>GRANT ALL ON *.* TO star@localhost
IDENTIFIED BY "star" WITH GRANT OPTION;
mysql>GRANT ALL ON *.* TO star@127.0.0.1
IDENTIFIED BY "star" WITH GRANT OPTION;
grant all on *.* to 'remoteuser'@"%" identified by
"mh_db_server"
注意密码必须要用双引号
show grants for star@localhost
show grants for star@'%'
http://hi.baidu.com/drinfgu/blog/item/20ed39dbc902b2163af3cf18.html
15)表结构增加一列
alter table mytable add column single char(1) not null;
16)创建数据库
create database
17)位运算
& 就是与运算
SELECT * FROM
TABLE WHERE
(ID &
0x01);
这样就把ID为奇数的记录选出来了
18)获取当前时间
select now();
select curdate();
select curtime();
19 where in查询结果按照in中的排序
使用find_in_set
例如：Select * FROM table1 Where (ID IN (3,5,1,4,2))
ORDER BY FIND_IN_SET(ID, '3,5,1,4,2')
20)查看当前时区
show variables like '%time_zone%';
如果mysql时间和系统时间不一致，重启服务器
21)查询一周三个月半年之内的数据
如果数据行中有一个保存日期/时间的数据my_date
下面是查询一周内的数据：
SELECT *
FROM table_name
WHERE (DATE_SUB(CURDATE()
- INTERVAL 7 DAY) <=
my_date);
其他的类似 三个月 INTERVAL 3 MONTH ......
半年
INTERVAL 6 MONTH ......
22)
iconv -f utf-8 -t gb2312 /server_test/reports/software_.txt
> /server_test/reports/software_asserts.txt
23)分隔字符串，获取部分字符串
SQL： update `edw_video` set company =
substring(keyword,1,length(SUBSTRING_INDEX(keyword,",",1)))
说明：str 要split的目标字符串 (输入格式为‘11，2，3，4，5，6’； 输出为‘2，3，4，5，6’)
delimiter
分隔符 (‘，’)
split1
输出第一项(‘11’)
--截取第二个 '.' (倒数)之后的所有字符。
mysql> select
substring_index('www.sqlstudy.com.cn', '.', -2);
字符串操作：
http://www.cnblogs.com/tillere007/archive/2010/06/09/1754985.html
24)输出到csv文件
mysql user12_s251 -e"select id, name from mem_user into outfile
'/tmp/data.csv'";
25) 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX
index_name
[index_type]
ON tbl_name (index_col_name,...)
-- 创建无索引的表格
create table testNoPK (
id int not null,
name varchar(10)
);
-- 创建普通索引
create index IDX_testNoPK_Name on testNoPK
(name);
26)两个表之间数据复制
经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，
这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。
-- INSERT INTO SELECT语句
语句形式为：Insert into Table2(field1,field2,...) select
value1,value2,... from Table1
要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。
-- SELECT INTO FROM语句
语句形式为：SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
替代方法: create table new_table_name (Select * from
old_table_name);
【SQL模糊匹配】
我们都知道SQL查询过程中，单引号“'”是特殊字符，所以在查询的时候要转换成双单引号“''”。
但这只是特殊字符的一个，在实际项目中，发现对于like操作还有以下特殊字符：下划线“_”，百分号“%”，方括号“[]”以及尖号“^”。
其用途如下：
下划线：用于代替一个任意字符(相当于正则表达式中的 ? )
百分号：用于代替任意数目的任意字符(相当于正则表达式中的 * )
方括号：用于转义(事实上只有左方括号用于转义，右方括号使用最近优先原则匹配最近的左方括号)
尖号：用于排除一些字符进行匹配(这个与正则表达式中的一样)
以下是一些匹配的举例，需要说明的是，只有like操作才有这些特殊字符，=操作是没有的。
a_b...
a[_]b%
a%b...
a[%]b%
a[b...
a[[]b%
a]b...
a]b%
a[]b...
a[[]]b%
a[^]b...
a[[][^]]b%
a[^^]b...
a[[][^][^]]b%
对于like操作，需要进行以下替换(注意顺序也很重要)
[ -> [[] (这个必须是第一个替换的!!)
% -> [%] (这里%是指希望匹配的字符本身包括的%而不是专门用于匹配的通配符)
_ -> [_]
^ -> [^]
【SQL中常用函数】
1)获取unix系统时间
时间戳
获取当前时间戳：unix_timestamp();
unix_timestamp(date) 如果调用时没有参数，以无符号的整数形式返回一个 Unix 时间戳(从
'1970-01-01 00:00:00' GMT 开始的秒数)。如果以一个参数 date 调用
UNIX_TIMESTAMP()，它将返回该参数值从 '1970-01-01 00:00:00' GMT 开始经过的秒数值。date
可以是一个 DATE 字符串，一个 DATETIME 字符串，一个 TIMESTAMP，或者以一个 YYMMDD 或 YYYYMMDD
显示的本地时间
例如：
字符串形式：
sysdate()
例如：select sysdate(); 返回2008-08-08 14:47:11
2)将时间戳直接转换成日期时间
FROM_UNIXTIME
例如：
select
uid,userid,username,email,FROM_UNIXTIME(addtime,'%Y年%m月%d日') from
members
3)把结果集按照规则连接成字符串
group_concat()
该函数返回带有来自一个组的连接的非NULL值的字符串结果。
group_concat(字段名 SEPARATOR '分隔符')
group_concat长度有默认限制，修改方法是在同一个session中执行如下语句
SET group_concat_max_len = 100000
4)返回当前连接的最后一次插入的自增字段值
例如： select last_insert_id()
5)把结果集按照规则连接成字符串
group_concat()
默认按逗号分割，例如：
select group_concat(cid) from sys_city where uid= $uid
可以指定分隔符，例如：
select group_concat(cid，SEPARATOR '|||') from sys_city where
uid= $uid
6) 字符串连接函数
mysql字符串连接 concat函数
使用方法：
concat(str1,str2,…)
mysql向表中某字段后追加一段字符串:
update table_name set field=concat(field,'str')
mysql 向表中某字段前加字符串
update table_name set field=concat('str',field)
返回结果为连接参数产生的字符串。如有任何一个参数为null ，则返回值为 null。
8)字符串查找 (查询某字段中包含以逗号分隔的字符串的记录)
SELECT * FROM test WHERE find_in_set('3',pnum) OR
find_in_set('9',pnum);
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
+----+-------+---------+
9)字符串截取
substr：字符串截取函数。substr($str,$start,$end)
MySQL中的substr函数中如要从第一个字符开始截取，则第一个参数应该为1；
10)获取指定时间
昨天凌晨3点钟
unix_timestamp(concat(DATE_SUB(CURDATE(), INTERVAL 1 DAY),'
03:00:00'))
今天凌晨3点钟
unix_timestamp(concat(CURDATE(),' 03:00:00'))
前天的日期(字符串形式)
date_format(curdate()-1,'%Y%m%d')
11)类型转换
Cast(value as type)或者 Convert(value ，type)；
可以转换的type如下：
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
例如：
update table_name set next_check_time=
unix_timestamp(concat('2012-12-27 ',cast(send_hour_minute as
char),':00')) where
next_check_time<>0;
【mysql中的键】
unique是当你定义数据库时候对某个字段限制唯一值(就是这个值不能重复)
primary key定义这个表的主键(比如id，不能重复，不能为空)
foreign
key定义这个表的外键(与其他表关联的键,比如Students表的id字段与Course表的id字段相对应)
check是定义这个字段只能输入满足条件的，比如sex字段，只能输入男和女)
Create Students(
id varchar(10) not null,
name varchar(20) not null unique,
sex varchar(2) not null check(sex ='男' or sex = '女'),
address varchar(30) null,
primary key (id),
id int foreign key references Course(id)
)；
00001 name1 男 address1(可以输入)
00001 name3 男 address2(不可以输入，id主键不能重复)
00002 name1 男 address3(不可以输入，name字段必须唯一)
00004 name4 她 address4(不可以输入，sex字段必须是男或女)
00005 name5 女 address5(可以输入)
【mysql的sql文本文件】
1. 添加注释
mysql 服务器支持 # 到该行结束、-- 到该行结束 以及 的注释方格：
mysql> SELECT 1+1; # 这个注释直到该行结束
mysql> SELECT 1+1; -- 这个注释直到该行结束
mysql> SELECT 1 + 1;
【mysql的日志清除】
1)空链接到数据库 mysql -uroot -p
2)show master logs; 查看目前的log列表
3)删除log信息
purge master logs to‘mysql-bin.000067';
表示mysql-bin.000067之前的bin日志将被删除
PURGE MASTER LOGS BEFORE‘2008-12-19 21:00:00';
4)进行查看确认 show master logs;
/var/lib/mysql
【异常处理】
1. 查看执行比较慢的SQL语句
vim /var/lib/mysql/mysql-slow.log
./mysqladmin processlist | grep -i Locked
2. 杀死锁死的进程
for i in `./mysqladmin processlist | grep -i Locked
| awk '{print $2}'`;do ./mysqladmin kill $i ;
done
【navicat for mysql】
1. 两个数据库表结构比较与差分脚本生成
菜单 -> Tools -> Structure
Synchronization
2. 两个数据库表数据同步
菜单 -> Tools -> Data
Transfer...
3. 远程机连接
http://blog.sina.com.cn/s/blog_48f9c0840100tp5h.html
【查询大小写区分】
1.方法一：设置字段的collate属性
http://www.codesky.net/article/201112/121977.html
【存储过程】
mysql中怎么拆分以逗号隔开的字符串
http://bbs.csdn.net/topics/380168180
mysql中split函数
http://zy116494718.iteye.com/blog/1156863
【参考资料】
Linux下安装mysql
http://wenda.tianya.cn/wenda/thread?tid=79f41c7a7e0a7551
InnoDB和MyISAM的区别
http://www.cnblogs.com/villion/archive/2009/07/09/1893762.html
navicat使用
http://wenku.baidu.com/view/1bf8584469eae009581becfd.html
mysql常用命令
http://www.cnblogs.com/hateislove214/archive/2010/11/05/1869889.html
MySql安装及数据库导入导出
http://wenku.baidu.com/view/f53930294b73f242336c5f40.html
让MySQL支持InnoDB ：
http://www.geekso.com/post/124/
MySQL数据库中SQL语句中 关于日期、时间\时间戳的函数
http://ggmmchou.blog.163.com/blog/static/59333149201112625426846/
select last_insert_id()语句 和 mysql_insert_id函数 的区别
http://hi.baidu.com/traindiy/blog/item/81f56163f081696b0d33fac8.html
MySQL内置函数获取几天前的日期
http://blog.csdn.net/amber_room/article/details/7024896
启动多个实例
http://xukaizijian.blog.163.com/blog/static/17043311920110309562210/
本文原创，转载请注明出处 http://blog.sina.com.cn/faithfish
扫一扫
207
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
