Mysql日常操作总结

 

Mysql日常操作总结

1. insert ......select

结构:INSERT INTO `table` (`filed1`,......,`filedN`) SELECT `filed1`,......,`filedN` FROM `table` [WHERE]

关于使用此种写法需要注意的问题,举例

INSERT INTO `user_address` (`id`,`userid`,`firstname`,`lastname`)

SELECT

`id`,`id`,`firstname` , `lastname`

FROM `user`";

此操作主要注意不在需要写values关键字,直接跟select查询语句

 

2. insert  into table on duplicate key update

结构:INSERT INTO `table` (`filed1`,......,`filedN`) ON DUPLICATE KEY UPDATE filed1=value1,…… filedN=valueN

此操作为在执行插入语句时，如果发生与表里的记录突然时，执行更新语句。执行此语句必须建立主键或主索引

3. insert ...... select case filed1 when 'a' then 1 when 'b' then 2 end

结构:INSERT INTO `table` (`filed1`,......,`filedN`) SELECT `filed1`,(CASE filed2 WHEN 'a' THEN 1 WHEN 'b' THEN 2 END),……`filedN` FROM `table`

某个字段不同值的不同替换举例

INSERT INTO `ticket_reply` (`reply_id`,`reply_content`,`reply_grade`)

SELECT

message_id,

message_msg,

(CASE ticket_grade WHEN '5' THEN 1 WHEN '10' THEN 2 WHEN '15' THEN 3 WHEN '20' THEN 4 END),

FROM message;

此操作需要注意的问题是 case when then 最后有一个end表示结束

4． Update table set filed1=replace (filed1,’aaaa’,’bbbb’)

结构:UPDATE table SET FILED1 = REPLACE (FILED1, 'VALUE1', 'VALUE2');

批量替换某个字段的值，把value1替换为value2

5. select * from table DATE(‘datetime’)=’date’

对于处理时间类型的数据采用mysql内置函数搜索更高效

举例:

Select * from table datetime between '2010-05-01 00:00:00' and '2010-05-31 23:59:59'

用

Select * from table DATE(datetime) ='2010-05-01’

6. mysql 字段串函数应用 left(),right(),if(),concat()

统计时对时间的处理如数据库存201003，取出年用LEFT(filed,4),取月用RIGHT(filed,2)

搜索第一季度举例

SELECT * FROM table WHERE RIGHT(`statmonth`,2) BETWEEN '01' AND '03'

IF(a>0,yes,no)

 

举例搜索付款时间为空的的记录更新成当前时间

SELECT* FROM table  WHERE pay_date`=if(pay_date='0000-00-00','$get_pay_date',pay_date)

 

Concat字符串连接，

Concat(‘a’,’b’,’c’) 结果abc

Concat(‘a’,null,’c’) 如果有一个值为null 返回null

Ifnull(a,b)如果a为真返回a,否返回b

应用与当前表中的备注连接

UPDATE table SET remark`=CONCAT('$remark',' ',ifnull(remark,''))

7. 搜索某个字段出现重复的记录

SELECT b.* FROM (SELECT email_address FROM `user` GROUP BY email_address HAVING COUNT(*) >1 ) AS p INNER JOIN `user` AS b ON b.email_address = p.email_address

8 .查询一个数据表是否存在
SHOW TABLES LIKE 'tablename'  如果存在返 Tables_in_database (tablename)
得到一个数据表的创建语句
SHOW CREATE TABLE 'tablename'
查询库中所有的数据表
show tables
查询表结构
desc tablename

9. 关于设置mysql权限

mysql不允许主机ip访问
mysql -u root -p

mysql>use mysql;

mysql>update user set host = '%'  where user ='root';

mysql>flush privileges;

mysql>select 'host','user' from user where user='root';

10. 从服务器上导出指定的某个表
mysqldump -h192.168.1.206 -uadmin -padmin gets produce --default-character-set=latin1 > /backup/test.sql
还原数据表
mysql -uroot -padmin  gets --default-character-set=utf8 <backup/test.sql

11.找出user中firstname最长的的信息

select * from user where LENGTH(`firstname`)=(select max(LENGTH(`firstname`)) from user limit 1)

12更新同一个数据库中，不同表的相同ID的某个字段

UPDATE produce,produce_bak SET produce.quality=produce_bak.quality where produce.id=produce_bak.id

13.mysql备份数据时对数据特殊字符的处理
mysqldump -u root -p   --default-character-set=utf8 fbeadscn |sed "s/\'/''/g" >  aa.sql
14.mysql 复制一个数据库的备份数据到指定的数据库
mysql -e "source /path-to-backup/backup-file.sql" db_name
15.增加索引
ALTER TABLE `produce_cn`  ADD INDEX (`ssort`) ;
16.给数据增加用户，并修改相关权限
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"
grant select on gets.*to sdb@"%" identified by "aaaaaa";
给gets库增加用户名为sdb,主机名不限，密码为aaaaa的账号，只有读的权限
17 修改时间戳是否自动理新
1.ALTER TABLE `orders_cn` CHANGE `updatetime` `updatetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(不自动更新)
2.ALTER TABLE `orders_cn` CHANGE `updatetime` `updatetime` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP (自动更新)
 
 18删除o_p_c表重复的记录，只保留一条
1.把重复记录生成的临时表
create table tmp_table SELECT tmp.* From orders_products_cn tmp group by orders_products_id having count(orders_products_id)>1;
2.删除原表的这些重复记录
DELETE from orders_products_cn where orders_products_id in (select a.orders_products_id from (SELECT tmp.`orders_products_id` From orders_products_cn tmp group by orders_products_id having count(orders_products_id)>1) as a);
3.从临时表导原表
insert into orders_products_cn select * from tmp_table;
4.删除临时表
DROP table tmp_table