insert语句防止重复插入:
INSERT INTO t_demo ( name )
SELECT '3' FROM ( SELECT count( 1 ) AS number FROM t_demo WHERE name = '3' ) a
WHERE a.number =0
字符串替换:
update user set address=replace(address,'上海','北京') WHERE id>'1';
升级已有数据库:
mysql_upgrade -uroot -p
用户操作
use mysql;
create user 'alex'@'%' identified by '123';
drop user '用户名'@'IP地址';
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
set password for '用户名'@'IP地址'=Password('新密码');
show grants for '用户'@'IP地址';
grant all privileges on db1.* to "alex"@'%';
revoke all privileges on db1.* from 'alex'@'%';
备份
mysqdump -u root db1 > db1.sql -p
mysqdump -u root -d db1 > db1.sql -p
use mysql;
source /root/db1.sql;
基本操作:
CREATE DATABASE IF NOT EXISTS 库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE TABLE `数据库名`.`表名` (
`id` int(0) NOT NULL,
`name` varchar(255),
PRIMARY KEY (`id`)
);
ALTER TABLE t_order_info ADD remark varchar(255) COMMENT '备注';
ALTER TABLE `test`.`t_demo`
MODIFY COLUMN `name` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称' AFTER `id`;
ALTER TABLE `test`.`t_demo` DROP COLUMN `is_delete`;
TRUNCATE TABLE test
DROP TABLE 表名