mysql 个人笔记

mysql修改最后一条记录&删除第一条记录 收藏
//修改最后一条记录
UPDATE userinfo set userid='55' WHERE 1 ORDER BY userid DESC LIMIT 1
//删除第一条记录
delete from userinfo where 1 order by userid limit 1

mysql sql 修改表名 建立外键 修改列名 删除列 --重命名表
rename table t_softwareport to software_port; 选择去重记录 SELECT distinct 列名称 FROM 表名称

--建立外键
alter table software_port add constraint fk_software_port_softwareprocessid foreign key (softwareprocessid)
references software_process (id) on delete restrict on update restrict;

--删除列
alter table software_type
drop column upid,
drop column orderid;
drop table tablename;

清空或删除所有表：
mysql -h127.0.0.1 -uroot -p123456 -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='city_gamedb'" |mysql -f -h127.0.0.1 -uroot -p123456 gamedb
echo "show tables;" | ./0sql.sh | awk '{printf("DROP TABLE IF EXISTS `%s`;\n", $1)}'| ./0sql.sh

清空存储过程和function：
delete from mysql.proc where db='gamedb' and modified < now();--增加列 alter table build add grid tinyint ; 

alter table family add (founder varchar(11) default '', resume varchar(20) default '')

--修改列名
alter table software_process change software_id softwareid int(11) not null;

多列排序&子列limit
SELECT sn,id,host,pid FROM item WHERE host=1 and pid in (select t.pid from (select pid from player order by equipPoint desc,pid asc limit 30) as t)

--表结构
describe tablename;

--建立表
create table faction(pid int(11),name varchar(16),gid int(11),rank tinyint(4),valid tinyint(4) default 0,primary key(pid)); 
drop table family;
create table family (pid int(11),name varchar(4),founder varchar(11),resume varchar(20),deputy varchar(11),primary key(pid));
create table familymem (pid int(11),gid int(11),valid tinyint(4),primary key(pid,gid));

--显示表
show tables；

--向表中添加数据
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
INSERT INTO table(col_1, col_2,col_3) VALUES('1','11','111'), ('2','22','222'), ('3','33','333'); 

--当记录不存在时插入
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

如何在mysql里设置字段的默认值:
建表时：create table tablename (columnname datatype default defaultvalue);
已建表修改：alter table tablename alter column columnname set default defaultvalue;
alter table family modify name varchar(4) not null default '',modify num int(11) not null default 0,modify founder varchar(11) not null default '',modify resume varchar(40) not null default '';


数据库
打开MySQL服务器 bin>mysqld --console
关闭服务器 bin>mysqladmin –u root shutdown
登陆MySQL服务器 bin>mysql –u root -p

创建数据库 create database menagerie;       指定字符集：CREATE DATABASE IF NOT EXISTS gamedb2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
显示所有数据库 show databases;
选择某个数据库 use test;
备份数据库 mysqldump –u root –p dbname >filename //回车，然后输入密码
还原数据库 mysql –u root –p dbname>filename

mysql -h192.168.100.20 -ulz -plzonline lz;

create table changanpk (sn int(11),value varchar(3000), primary key(sn)) ENGINE = INNODB;

ALTER TABLE family ENGINE = InnoDB;

bash命令行上直接运行mysql文件：mysql -h192.168.100.20 -ulz -plzonline lz < testdb.sql

alter table hisRankList auto_increment=1;
Alter table tb add primary key(id);
Alter table tb change id id int(10) not null auto_increment=1;
4 删除自增长的主键id
先删除自增长在删除主键
Alter table tb change id id int(10);//删除自增长
Alter table tb drop primary key;//删除主建

INSERT INTO account (aid,login) VALUE (%d, now()) ON DUPLICATE KEY UPDATE login = NOW()

1.MySQL复制表结构及数据到新表
CREATE TABLE 新表
SELECT * FROM 旧表

2.只复制表结构到新表

CREATE TABLE 新表
SELECT * FROM 旧表 WHERE 1=2
即:让WHERE条件不成立.
方法二:(低版本的mysql不支持，mysql4.0.25 不支持，mysql5已经支持了)
CREATE TABLE 新表 LIKE 旧表

3.复制旧表的数据到新表(假设两个表结构一样)

INSERT INTO 新表 SELECT * FROM 旧表

4.复制旧表的数据到新表(假设两个表结构不一样)

INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 旧表

线下做数据库数据处理：
echo "select * from hisRankList where type = 3" | mysql -uroot -ppwd zs -h192.168.100.31 | awk '{printf("INSERT INTO hisRankList SET pid=%s,type=%s,rank=%s,value=%s,time=%s;\n", $2, $3, $4, $5, $6)}' > addRank2.sql

cat sname.sql | mysql -uroot -ppwd dx -h192.168.100.31>tmp.dat

直接将MySQL数据库压缩备份
mysqldump -hhostname -uusername -ppassword databasename | gzip > db.sql.gz

备份MySQL数据库某个(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > db.sql

同时备份多个MySQL数据库
mysqldump --opt -R -E -i -h127.0.0.1 -uroot -p123456 gamedb > gamedb.sql 有些没有默认启动opt，R是存储过程和function， E是events、 -i是注释貌似没用
mysqldump -h127.0.0.1 -uroot -p123456 databasename1 > bk.sql
mysqldump -hhostname -uusername -ppassword –-databases databasename1 databasename2 databasename3 > db.sql

仅仅备份数据库结构 
mysqldump -hhostname -uusername -ppassword –-opt -d -R -E --databases databasename1 databasename2 databasename3 > db.sql

备份服务器上所有数据库
mysqldump –all-databases > db.sql

还原MySQL数据库的命令
mysql -hhostname -uusername -ppassword databasename < db.sql

还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器
mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

批量删除规则列：

mysql -h 192.168.100.20 -ulz -plzonline lz -s -e "show columns from player like 'ft%';" >tlist.txt
awk '{print $1}' tlist.txt>sqlcol.txt
rm tlist.txt
for vname in `cat sqlcol.txt`
do
echo $vname;
mysql -h 192.168.100.20 -ulz -plzonline lz -s -e "alter table player drop $vname;"
done

-----------一个根据log插入mysql数据的脚本：

#grep _OLAP_ /data/logs/sx-48/`date -d yesterday '+%Y-%m-%d_*'` |
#awk '{printf("insert into olap(event,pid,level,race,country,data1,data2,data3,data4,time) values(%s%s%s%s%s%s%s%s%s,\"%s %s\");\n",
#$6,$8,$10,$12,$14,$16,$18,$20,$22,$1,$2)}' | mysql -uroot -ppwd zs -h 192.168.100.31

getDate(){
if [ $# -eq 3 ]
then
echo "$1-$2-$3_*"
else
echo `date -d yesterday '+%Y-%m-%d_*'`
fi
}

sdate=`getDate $1 $2 $3`
for gid in 50 51 52 48
do
grep _OLAP_ `printf "/data/logs/sx-%d/%s" $gid $sdate` -h |
awk '{printf("insert into olap(event,pid,level,race,country,data1,data2,data3,data4,time) values(%s%s%s%s%s%s%s%s%s,\"%s %s\");\n",
$6,$8,$10,$12,$14,$16,$18,$20,$22,$1,$2)}' | mysql -uroot -ppwd zs -h 192.168.100.31
done

用户权限相关：

这仅仅是访问权限不够
ERROR 1045 (28000): Access denied for user 'xcj'@'localhost' (using password: YES)

#新加用户服务xcj,新加的用户不能马上生效 #同样解决在使用mysql视图是出现问题： The user specified as a definer ('root'@'%') does not exist。一般是由于root用户对全局host无访问权限。因此只要给root用户添加一个访问权限即可。

mysql> grant all on *.* to xcj@'%' identified by "xcj_passwd";
Query OK, 0 rows affected (0.04 sec)

#生效新加用户xcj权限
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

数据库路径获取：select @@datadir;

max函数： 

对数据库中数字类型的字段取最大值可以直接用：
SELECT MAX(field-name) FROM table-name WHERE conditions
而对于其它类型的字段要使用以下语句：
SELECT MAX(CAST(field-name AS UNSIGNED)) FROM table-name WHERE conditions

字符串替换
update skill_event set scriptFunc = replace(scriptFunc, substring(scriptFunc, 1,locate('_',scriptFunc)),"Skill_");

group by 可按照在 GROUP BY 子句中定义的组对行进行分组
1 列出每个部门编号的最高薪水的结果：
SELECT DEPT, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT
分组查询可以在形成组和计算列函数之前具有消除非限定行的标准WHERE子句。必须在GROUP BY子句之前指定WHERE子句
SELECT WORKDEPT,EDLEVEL,MAX(SALARY) AS MAXIMUM FROM EMPLOYEE WHERE HIREDATE > '1979-01-01' GROUP BY WORKDEPT, EDLEVEL ORDER
在 GROUP BY 子句之后使用 HAVING 子句可应用限定条件进行分组:
查询寻找雇员数超过 4 的部门的最高和最低薪水：
SELECT WORKDEPT, MAX(SALARY) AS MAXIMUM, MIN(SALARY) AS MINIMUM FROM EMPLOYEE GROUP BY WORKDEPT HAVING COUNT(*) > 4

SELECT count(1) as cnum, owner_idx from character_city GROUP BY owner_idx HAVING cnum > 1; 查询所有重复记录

charset问题
在[client ] 下面加入　default-character-set=utf8
在[mysqld ] 下面加　character_set_server=utf8　　　init_connect='SET NAMES utf8'        collation-server=utf8_unicode_ci
在[ mysql ] 下面加入 default-character-set=utf8

配置binlog:在/etc/mysql/my.cnf的mysqld配置里增加
log_bin = mysql-bin
server-id = 1
sudo /etc/init.d/mysql restart后datadir目录/var/lib/mysql(mysqld配置里不必cd进去)会自动生成binlog文件。
查看日志列表：show master logs;或show binary logs; 查看日志之前先flush logs;截断当前log文件输出。
查看日志：mysqlbinlog xxx-bin.000001|mysql -h127.0.0.1 -uroot –ppassword dataname或者用sudo mysqlbinlog mysql-bin.000001可能要sudo -i目录权限
show binlog events\G #查看所有的二进制信息
show binlog events in 'mysql-bin.000001'; #查看指定日志的二进制信息
show binlog events in 'mysql-bin.000001' from 123 limit 3; #从指定的事件位置开始
PURGE BINARY LOGS TO 'mysql-bin.000007'; #TO 'log_name' 把这个文件前的文件都删除
PURGE BINARY LOGS BEFORE '13-10-19 10:26:36'; #使用时间来删除二进制日志
reset master; #删除所有的二进制日志,编号从000001开始,不建议在生产环境下使用