mysql使用记录

mysql使用记录

一、日期处理函数
DATE_FORMAT(date,format)  
根据format字符串格式化date值。下列修饰符可以被用在format字符串中： %M 月名字(January……December)  
%W 星期名字(Sunday……Saturday)  
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）  
%Y 年, 数字, 4 位  
%y 年, 数字, 2 位  
%a 缩写的星期名字(Sun……Sat)  
%d 月份中的天数, 数字(00……31)  
%e 月份中的天数, 数字(0……31)  
%m 月, 数字(01……12)  
%c 月, 数字(1……12)  
%b 缩写的月份名字(Jan……Dec)  
%j 一年中的天数(001……366)  
%H 小时(00……23)  
%k 小时(0……23)  
%h 小时(01……12)  
%I 小时(01……12)  
%l 小时(1……12)  
%i 分钟, 数字(00……59)  
%r 时间,12 小时(hh:mm:ss [AP]M)  
%T 时间,24 小时(hh:mm:ss)  
%S 秒(00……59)  
%s 秒(00……59)  
%p AM或PM  
%w 一个星期中的天数(0=Sunday ……6=Saturday ）  
%U 星期(0……52), 这里星期天是星期的第一天  
%u 星期(0……52), 这里星期一是星期的第一天  
%% 一个文字“%”。  
所有的其他字符不做解释被复制到结果中。  
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');  
-> 'Saturday October 1997'  
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');  
-> '22:23:00'  
mysql> select DATE_FORMAT('1997-10-04 22:23:00',  
'%D %y %a %d %m %b %j');  
-> '4th 97 Sat 04 10 Oct 277'  
mysql> select DATE_FORMAT('1997-10-04 22:23:00',  
'%H %k %I %r %T %S %w');  
-> '22 22 10 10:23:00 PM 22:23:00 00 6'  
MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。  
TIME_FORMAT(time,format)  
这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。
其他修饰符产生一个NULL值或0。  
CURDATE()  
　  
CURRENT_DATE  
以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。  
mysql> select CURDATE();  
-> '1997-12-15'  

二、时间比较
sendtime > '2010-11-01 00:00:00'  and  sendtime < '2010-11-05 00:00:00'

三、字符截取
select *  from sendrecord where substring(mobile,1,3) = '158'

四、正则表达式匹配字串查询
select count(*) from sendrecord t1 where (SELECT mobile REGEXP '^1((3[4-9])|(5[012789])|(8[78]))([0-9]{8})$') = 1
正则表达式正确为1.匹配失败为0

五、mysql中的也有case when语法
select a.carrier,count(a.carrier) from (
select mobile,case
when (SELECT mobile REGEXP '^1((3[4-9])|(5[012789])|(8[78]))([0-9]{8})$') = 1 then 'Mobile'
when (SELECT mobile REGEXP '^1((3[0-2])|(5[56])|(8[56]))([0-9]{8})$') = 1 then 'Unicom'
when (SELECT mobile REGEXP '^(1((3[3])|(5[3])|(8[09]))([0-9]{8}))|(0[0-9]{9,11})$') = 1 then 'Telecom'
else 'oher' end as carrier
from sendrecord201009 ) as a group by a.carrier

六、linix下安装mysql文件下载地址
http://dev.mysql.com/downloads/mirror.php?id=397238

七、linix下启动mysql出现ended。说明启动未能成功，解决办法如下
vi /etc/my.conf
看看[mysqld]下有没有
port  = 3306
添加上就可以了
touch /tmp/mysql.sock
killall -9 mysqld
/usr/local/mysql/bin/mysqld_safe -user=mysql &
成功启动

八、查找mysql目录所在
whereis mysql

九、在linix下运行不了mysql是因为环境变量未设置造成的，解决办法如下：
在/etc/profile中添加：
export PATH=$PATH:/usr/local/MySQL/bin就OK
其他的变量都类似了 关键是明白各个配置文件在系统中的原理
设置完成后，别忘了source一下。使环境变量生效

最后设置变量到用户下面的.bash_profile来作修改，修改完后source下使环境变量生效

十、用shell脚本执行连接mysql时
mysql -u root -p yyy  这样写的话，会老是叫你输入密码的
其实在运行此命令时可以这样写
mysql -uroot -pyyy 这样机器也是可以识别的

十一.以下提供一段shell脚本连接mysql
#!/bin/bash
beginTime="`date -d \"-1 month\" \"+%Y-%m\"`"'-01 00:00:00'
echo 'beginTime:     '$beginTime

endTime=`date '+%Y-%m'`'-01 00:00:00'
echo 'endTime:  '$endTime

sufTable="`date -d \"-1 month\" \"+%Y%m\"`"
echo 'sufTable:   '$sufTable

mysql -uxuanwu -p123456 <use xuanwu;

create table sendrecord$sufTable as select * from sendrecord where sendtime > "\"$beginTime\"" and sendtime < "\"$endTime\"";
#echo "create table sendrecord$sufTable as select * from sendrecord where sendtime > \"$beginTime\" and sendtime < \"$endTime\"";

#echo 'delete from sendrecord where sendtime > $beginTime and sendtime < $endTime;'

commit;

十二、shell脚本调用mysql脚本
#!/bin/bash
mysql -uxuanwu -p123456 <use xuanwu;
source ./create.sql

十三、mysql脚本执行多个sql语句
#建表转移数据
SET @TableName=CONCAT('sendrecord',DATE_FORMAT(DATE_SUB(CURDATE() ,INTERVAL 1 month),'%Y%m'));
SET @BeginTime=DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 day),INTERVAL 1 month);
SET @EndTime=DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 day);
SET @CreateSqlStr=CONCAT("CREATE TABLE `",@TableName,"` AS SELECT * FROM sendrecord WHERE sendtime > '",cast(@BeginTime as char)," 00:00:00' and sendtime < '",cast(@EndTime as char)," 00:00:00'");
SELECT @CreateSqlStr;
PREPARE CR FROM @CreateSqlStr;
EXECUTE CR;

#删除数据
SET @DeleteSqlStr=CONCAT("DELETE FROM sendrecord WHERE sendtime > '",cast(@BeginTime as char)," 00:00:00' and sendtime < '",cast(@EndTime as char)," 00:00:00'");
SELECT @DeleteSqlStr;
PREPARE DE FROM @DeleteSqlStr;
EXECUTE DE;

#提交数据
SET @CommitSqlStr=CONCAT("COMMIT");
SELECT @CommitSqlStr;
PREPARE CO FROM @CommitSqlStr;
EXECUTE CO;

十四、mysql安装步骤记录

# tar -zvxf mysql-max-5.1.30-linux-i686-glibc23.tar.gz
# mv -r mysql-max-5.1.30-linux-i686-glibc23 /usr/local/mysql
# cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

添加mysql用户及用户组
# groupadd mysql
# useradd -g mysql mysql
修改mysql目录权限
# chown -R root /usr/local/mysql
# chgrp -R mysql /usr/local/mysql
# chown -R mysql /usr/local/mysql/data

以下如果mysql不在本机可以不操作。
生成mysql系统数据库
# ./scripts/mysql_install_db --user=mysql&
启动mysql服务
# /usr/local/mysql/bin/mysqld_safe --user=mysql&
如出现 Starting mysqld daemon with databases from /usr/local/mysql/data
代表正常启动mysql服务了, 按Ctrl + C 跳出
修改 mysql 的 root 密码
# /usr/local/mysql/bin/mysqladmin -u root password 123456

十五、ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL server
解决方法：
1。 改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"

mysql -u root -pvmwaremysql>use mysql;mysql>update user set host = '%' where user = 'root';mysql>select host, user from user;

2. 授权法。例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

本人觉得授权法比较好用.

十六、ERROR 1045 (28000): Access denied for user (using password: YES)
# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

# /etc/init.d/mysqld restart
# mysql -uroot -p
Enter password:

mysql>

十七、mysql从本机登录,不用用户名密码可以直接登陆?如何禁止?

解决办法：我刚才查看了一下配置文件，发现/etc/my.cnf里面的配置
# The following options will be passed to all MySQL clients
[client]
#password        = your_password
port            = 3306
socket          = /tmp/mysql.sock

我把password前面的那个注释去掉，重启，OK，成功。。。。

十八.yum 安装 mysql

1、[root@sample ~]# yum -y install mysql-server　 ← 安装MySQL
.....略
Installed: mysql-server.i386 0:4.1.20-1.RHEL4.1
Dependency Installed: mysql.i386 0:4.1.20-1.RHEL4.1 perl-DBD-MySQL.i386 0:2.9004-3.1 perl-DBI.i386 0:1.40-8
Complete!

2、配置
[root@sample ~]# vi /etc/my.cnf　 ← 编辑MySQL的配置文件

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1　 ← 找到这一行，在这一行的下面添加新的规则，让MySQL的默认编码为UTF-8
default-character-set = gbk　 ← 添加这一行
然后在配置文件的文尾填加如下语句：

[mysql]
default-character-set = gbk

3、启动MySQL服务

[root@sample ~]# chkconfig mysqld on　 ← 设置MySQL服务随系统启动自启动

[root@sample ~]# chkconfig --list mysqld　 ← 确认MySQL自启动
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off　 ← 如果2--5为on的状态就OK

[root@sample ~]# /etc/rc.d/init.d/mysqld start　 ← 启动MySQL服务

启动mysql                                              [确定]

备注：如果这一部执行失败有可能是/var/lib/mysql 没有数据库文件，会报【 Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist】，这是需要执行mysql_install_db命令就OK了。

4、MySQL的root用户设置密码
MySQL在刚刚被安装的时候，它的root用户是没有被设置密码的。首先来设置MySQL的root密码。

[root@sample ~]# mysql -u root　 ← 用root用户登录MySQL服务器
mysql> select user,host,password from mysql.user;　 ← 查看用户信息
mysql>set password for root@localhost=password('在这里填入root密码');　 ← 设置root密码
mysql>set password for root@域名=password('在这里填入root密码');
mysql> delete from mysql.user where user='';　 ← 删除匿名用户
mysql> exit　 ← 退出MySQL服务器【测试设置的密码是否正确】