#登录数据库mysql -hlocalhost -uroot -p;#修改密码mysqladmin -uroot -pold password new;#显示数据库show databases;#显示数据表show tables;#选择数据库use examples;#创建数据库并设置编码utf-8 多语言create database `examples` default character set utf8 collate utf8_general_ci;#删除数据库drop database examples;#创建表create table test( id int(10) unsigned zerofill not null auto_increment, email varchar(40) not null, ip varchar(15) not null, state int(10) not null default '-1', primary key (id))engine=InnoDB;#显示表结构describe #删除表drop table test；#重命名表alter table test_old rename test_new;#添加列alter table test add cn int(4) not null;#修改列alter table test change id id1 varchar(10) not null;#删除列 alter table test drop cn;#创建索引alter table test add index (cn,id);#删除索引alter table test drop index cn#插入数据insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0');#删除数据 delete from test where id = 1;#修改数据update test set id='1',email='q@qq.com' where id=1;#查数据select * from test; #取所有数据select * from test limit 0,2; #取前两条数据 select * from test email like '%qq%' #查含有qq字符 _表示一个 %表示多个select * from test order by id asc;#降序descselect * from test id not in('2','3');#id不含2,3或者去掉not表示含有select * from test timer between 1 and 10;#数据在1,10之间#---------------------------表连接知识------------------------------#等值连接又叫内链接 inner join 只返回两个表中连接字段相等的行select * from A inner join B on A.id = B.id; #写法1select * from A,B where A.id = B.id; #写法2select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#写法3 表的临时名称select a.id as ID,a.title as 标题 from A inner join B on A.id=B.id;#添加as字句#左连接又叫外连接 left join 返回左表中所有记录和右表中连接字段相等的记录select * from A left join B on A.id = B.id;select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#复杂连接#右连接又叫外连接 right join 返回右表中所有记录和左表中连接字段相等的记录select * from A right join B on A.id = B.id;#完整外部链接 full join 返回左右表中所有数据select * from A full join B on A.id = B.id;#交叉连接 没有where字句 返回卡迪尔积select * from A cross join B;-------------------------表连接结束-----------------------------------------------------------------------------索引创建------------------------------------------------show index from A #查看索引alter table A add primary key(id) #主键索引alter table A add unique(name) #唯一索引alter table A add index name(name) #普通索引alter table A add fulltext(name) #全文索引alter table A add index name(id,name) #多列索引#常用函数abs(-1)#绝对值pi()#pi值sqrt(2)#平方根mod(-5,3)#取余-2ceil(10.6)#进位+1 结果11 ceil(10.0)结果10floor(10.6)#取整 10round(2.5)#四舍五入到整数 结果3round(2.5,2)#保留两位小数 结果2.50truncate(2.5234,3)#取小数后3位不四舍五入 2.523sign(-2);#符号函数 返回-1 0还是0 正数返回1pow(2,3),exp(2);#2的3次幂 或e的2次幂log(2),log10(2);#求对数radians(180),degrees(0.618);#角度弧度转换sin(0.5),asin(0.5)#正弦和反正弦 类似cos acos tan atanlength('hi')#计算字符长度concat('1',1,'hi')#合并字符串insert('12345',1,0,'7890');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入ucase('a'),lcase('A')#转成大写和小写left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符ltrim(' 0 '),rtrim(' 0 '),trim(' 0 ')#删除空格replace('1234567890','345678','0');#替换输出12090substring('12345',1,2)#取字符 输出12 1是位置 2是长度instr('1234','234');#取得234位置是2reverse('1234');#反序输出4321current()#返回日期curtime()#返回时间now()#返回日期时间month(now())#当前月份 monthname 英文月份dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二week(now())#本年第多少周dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒time_to_sec(now()),sec_to_time(3600*8);#转换时间为秒和还原version()#mysql版本database()#当前连接的数据库 没有为nulluser()#获取用户名md5('a')#加密字符串ascii('a')#ascii值97bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制conv(10001,2,8);#各种进制相互转换rand()#生成0到1之间随机数sleep(0.02)#暂停秒数---------------------------------

1. MySQL 为日期增加一个时间间隔：date_add()

set @dt = now();

select date_add(@dt, interval 1 day);   - 加1天

select date_add(@dt, interval 1 hour);   -加1小时

select date_add(@dt, interval 1 minute);    - 加1分钟

select date_add(@dt, interval 1 second); -加1秒

select date_add(@dt, interval 1 microsecond);-加1毫秒

select date_add(@dt, interval 1 week);-加1周

select date_add(@dt, interval 1 month);-加1月

select date_add(@dt, interval 1 quarter);-加1季

select date_add(@dt, interval 1 year);-加1年

mysql adddate(), addtime()函数，可以用date_add() 来替代。下面是date_add() 实现addtime() 功能示例：

mysql> set @dt = '2009-09-09 12:12:33';

mysql>

mysql> select date_add(@dt, interval '01:15:30' hour_second);-加上1小时15分30秒

 date_add(@dt, interval '01:15:30' hour_second)

 

结果:2009-09-09 13:28:03

 

mysql> select date_add(@dt, interval '1 01:15:30' day_second);-加1天1小时15分30秒

 date_add(@dt, interval '1 01:15:30' day_second)

 2008-08-10 13:28:03

date_add() 函数，分别为@dt 增加了“1小时15分30秒” 和 “1天1小时15分30秒”

2. MySQL 为日期减去一个时间间隔：date_sub()

mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

 date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second)

 www.2cto.com

MySQL date_sub() 日期时间函数 和date_add() 用法一致，不再赘述。另外，MySQL 中还有两个函数subdate(), subtime()，建议，用date_sub() 来替代。

3. MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)

函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去N month（月）。

MySQL period_add(P,N)：日期加/减去N月。

mysql> select period_add(200808,2), period_add(20080808,-2)

| period_add(200808,2) | period_add(20080808,-2) |

结果|               200810 |                20080806 |

MySQL period_diff(P1,P2)：日期P1-P2，返回N 个月。

mysql> select period_diff(200808, 200801);

 period_diff(200808, 200801)

  结果:7

 MySQL 中，这两个日期函数，一般情况下很少用到。

4. MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

MySQL datediff(date1,date2)：两个日期相减date1 date2，返回天数。网站制作学习网整理

select datediff('2008-08-08', '2008-08-01'); - 7

select datediff('2008-08-01', '2008-08-08'); -7

MySQL timediff(time1,time2)：两个日期相减time1 time2，返回time 差值。

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00');- 08:08:08

select timediff('08:08:08', '00:00:00');                      - 08:08:08

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

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

MySQL DATE_FORMAT() 函数

MySQL Date 函数

定义和用法

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法

DATE_FORMAT(date,format)

 参数是合法的日期。 规定日期/时间的输出格式。

可以使用的格式有：

格式 描述 %a缩写星期名%b缩写月名%c月，数值%D带有英文前缀的月中的天%d月的天，数值(00-31)%e月的天，数值(0-31)%f微秒%H小时 (00-23)%h小时 (01-12)%I小时 (01-12)%i分钟，数值(00-59)%j年的天 (001-366)%k小时 (0-23)%l小时 (1-12)%M月名%m月，数值(00-12)%pAM 或 PM%r时间，12-小时（hh:mm:ss AM 或 PM）%S秒(00-59)%s秒(00-59)%T时间, 24-小时 (hh:mm:ss)%U周 (00-53) 星期日是一周的第一天%u周 (00-53) 星期一是一周的第一天%V周 (01-53) 星期日是一周的第一天，与 %X 使用%v周 (01-53) 星期一是一周的第一天，与 %x 使用%W星期名%w周的天 （0=星期日, 6=星期六）%X年，其中的星期日是周的第一天，4 位，与 %V 使用%x年，其中的星期一是周的第一天，4 位，与 %v 使用%Y年，4 位%y年，2 位

实例

下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') DATE_FORMAT(NOW(),'%m-%d-%Y') DATE_FORMAT(NOW(),'%d %b %y') DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

结果类似：

Dec 29 2008 11:45 PM 12-29-2008 29 Dec 08 29 Dec 2008 16:25:46.635 -------------------- 数据库优化1.开启缓存，尽量使用php函数而不是mysql2. explain select 语句可以知道性能3.一行数据使用 limit 1；4.为搜索字段重建索引 比如关键字 标签5.表连接join保证字段类型相同并且有其索引6.随机查询使用php $r = mysql_query("SELECT count(*) FROM user"); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d[0] - 1); $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");7.避免使用select * 应该使用具体字段8.每张表都是用id主键，并且是unsigned int9.对于取值有限而固定使用enum类型，如性别 国家 名族 部门 状态10.尽可能使用not null ip存储使用int(4),使用ip 转化函数ip2long()相互long2ip()11.delete和insert语句会锁表，所以可以采用分拆语句操作 while(1){操作语句;usleep(2000);}12.选择正确的存储引擎；MyISAM适合大量查询 写操作多用InnoDB支持事务#存储过程#存储程序delimiter #定义存储程序create procedure getversion(out params varchar(20)) #params是传出参数 in传进 out传出 inout传回beginselect version() into params; #版本信息赋值paramsendcall getversion(@a); #调用存储过程select @a;delimiter #定义存储函数create function display(w varchar(20)) returns varchar(20)beginreturn concat('hello',w);endselect display('world');drop procedure if exists spName; #删除一个存储过程alter function spName [];#修改一个存储过程show create procedure spName;#显示存储过程信息declare varName type default value;#声明局部变量#if语句if 条件 then 语句elseif 条件 then 语句else 语句end if#case语句case 条件when 条件 then 语句when 条件 then 语句else 语句end case#loop语句fn:loop语句end loop fn;leave fn #退出循环#while语句fn：while 条件 do语句end while fn#mysql使用帮助资料? contents; #列出帮助类型? data types;#列出数据类型？ int;#列出具体类型? show;#show语句? create table;##常见表的比较 Myisam BDB Memory InnoDB Archive存储限制 no no yes 64T no事物安全 支持 支持 锁机制 表锁 页锁 表锁 行锁 行锁全文索引 支持外键支持 支持myisam frm存储表定义 MYD存储数据 MYI存储索引InnoDB 用于事务处理char 和 varchar保存和索引都不相同浮点数float(10,2) 定点数decimal(10,2)长度一定下，浮点数表示更大数据范围，缺点是引起精度丢失，货币等使用定点数存储 索引适合于where字句或者连接字句列 对于唯一值使用唯一索引添加新用户 grant select,insert,update,delete on *.* to Yoby@localhost identified by 'mysql'; # *.* 数据库名.表名，限制登录某一个数据库 test.* localhost是本地主机 网络可以使用 '%'代替所有主机 'mysql'是密码 Yoby是用户名 所有权限可以用 all代替查看用户权限 show grants for 'root'@'localhost';移除权限 revoke all on *.* from root@localhost;group by id 分组having 限制字句select1 union select2 联合查询有重复去掉保留一行select2 union all select2 所有行合并到结果集中去 -------------------------------------------------------------------- 修改MYSQL参数 show variables like '%Func%'; set global log_bin_trust_function_creators=1; show variables like '%Func%'; 实用的insert select语句： INSERT INTO KM_MEMBER_RELA_FULLCUT ( SELECT a.MB_ID, b.MPFC_ID, 1 FROM KM_MEMBER a, KM_MEMBER_PREF_FULLCUT b WHERE b.MPFC_STATUS = 0 AND NOT EXISTS ( SELECT 1 FROM KM_MEMBER_RELA_FULLCUT WHERE MB_ID = a.MB_ID AND MPFC_ID = b.MPFC_ID ) )