SQL常用
创建数据库
drop DATABASE if EXISTS `shihou_www`;
CREATE DATABASE shihou_www;
use shihou_www;
时间操作
当前日期增加10个小时
SELECT DATE_ADD(NOW(), INTERVAL 600 MINUTE);
时间戳格式化
FROM_UNIXTIME(create_time/1000, ‘%Y-%m-%d %H:%i:%s’)
select unix_timestamp(now()) from dual;//查询到当前日期时间的时间戳
创建时间字段默认为自动添加当前时间戳:
在默认一栏填写 CURRENT_TIMESTAMP;
并将【根据当前时间戳更新勾】选上
按字符搜索和删除
select count(*) from article_info;
select * from article_info where locate(‘娱乐城利来国际’, content) > 0;
select * from article_info where locate(‘赌博’, content) > 0;
select * from article_info where locate(‘色情’, content) > 0;
select * from article_info where locate(‘病毒’, content) > 0;
DELETE from article_info where locate(‘娱乐城利来国际’, content) > 0;
DELETE from article_info where locate(‘赌博’, content) > 0;
DELETE from article_info where locate(‘色情’, content) > 0;
DELETE from article_info where locate(‘病毒’, content) > 0;
DELETE from article_info where locate(‘vpn翻墙’, content) > 0;
修改字段
ALTER TABLE user_info ADD COLUMN real_name varchar(64) DEFAULT NULL COMMENT ‘真实姓名’;
ALTER TABLE user_info ADD COLUMN id_card bigint(18) DEFAULT NULL COMMENT ‘身份证号’;
复制表结构同时复制主键等选项
CREATE TABLE IF NOT EXISTS newTableName LIKE originalTableName;
查询该表是否存在
SHOW TABLES LIKE tableName;
mysql 查询该字段属于哪个表
use information_schema;
select * from columns where column_name=’字段名’ ;
按照一定规则查询表名,并拼接删除语句
SELECT CONCAT(‘drop table ‘, table_name, ‘;’) FROM information_schema. TABLES WHERE table_name LIKE ‘user_step_info_u%’;
排序并获得名次
主要是使用rownum,
详情点击:http://blog.csdn.net/assassinsshadow/article/details/76612979
MySQL常用命令
用户
用户授权
grant all privileges on test.* to 'user1'@'%';
flush privileges;
select user();//显示当前用户
数据库
show database; #当前用户下所有数据库的
select database(); #当前数据库
show tables; #当前数据库下的所有表
打赏地址:http://blog.csdn.net/assassinsshadow/article/details/76086401