MySQL相关知识

字符串拼接

select * from tablename where mydata like CONCAT(CURDATE(), '%') limit 3

 

这里concat是字符串拼接, concat('mys', 'q', 'l')    ->  mysql

显示日期不带时间的函数, 如 2015-05-14

CURDATE()是日期不算时间 2015-05-13

正则匹配

查询是数字开头的方法
select * from a where b REGEXP '^[0-9]' 

查询出非数字开头的，也可以用正则，并且使用NOT
select * from a where b not REGEXP '^[0-9]'   

统计查询条数

select count(1) from 
(select id from tablename group by need_point) t

查看Mysql进程和锁表状态

SHOW PROCESSLIST
show OPEN TABLES where In_use > 0

　　

加外键

ALTER TABLE `supplier_supplierinfo`
  ADD KEY `supplier_supplierinfo_sid_foreign` (`si_id`);

ALTER TABLE `supplier_supplierinfo`
  ADD CONSTRAINT `supplier_supplierinfo_sid_foreign` FOREIGN KEY (`si_id`) REFERENCES `supplier` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE;

　　

改密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234');

　　

重命名数据库

mysqldump -u root -p originl_database > original_database.sql
mysql -u root -p -e "create database my_new_database"
mysql -u root -p my_new_database < original_database.sql
mysql -u root -p -e drop database originl_database

 

命令行查询, 导出查询结果到文件

mysql -uroot -p12345678 -Ne "use glitzhome; select * from user; " > /file.sql;