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;

--建立外键
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;

--增加列
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 tinyin
t(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;
显示所有数据库 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 -u lz -plzonline zs -h 192.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 -u lz -plzonline dx -h 192.168.100.31>tmp.dat

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql
还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.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 -u lz -plzonline 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 -u lz -plzonline zs -h 192.168.100.31
done

用户权限相关：

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

#新加用户服务xcj,新加的用户不能马上生效
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;

mysql 个人笔记,古老的榕树,5-wow.com