linux下mysql使用整理

目录

【mysql安装】

【常用SQL语句】

【SQL模糊匹配】

【SQL中常用函数】

【mysql安装】

【mysql中的键】

【mysql的sql文本文件】

【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 <密码> <database名> [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 rxsg2@'%' identified by "rxsg2" 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')

【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。

【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

【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

【参考资料】

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

shell 字符串操作（长度，查找，替换）详解 

http://apps.hi.baidu.com/share/detail/23262717

本文转载出处：点击打开链接