mysql常用sql语句汇总
清空表ID归0:
truncate table student;
重命名表:
ALTER TABLE student RENAME TO student_N;
复制表结构到新表:
CREATE TABLE student_N LIKE student; CREATE TABLE wenzhang_m SELECT * FROM wenzhang_w;
读取一个表里有多少行
SELECT COUNT(1) FROM `wenzhang_w`
复制表结构及数据到新表:
CREATE TABLE student_N SELECT * FROM student;
设置字段为唯一性索引:
ALTER TABLE `wenzhang_m` ADD unique(`AID`);
设置字段为普通索引:
ALTER TABLE `yanchu` ADD INDEX index_name ( `ssid` )
排序:
SELECT * FROM yanchu ORDER BY AID DESC , id asc
SELECT * FROM yanchu WHERE aid = 287493 and ssid like '%am%' ORDER BY AID DESC , id asc
更新语句:
UPDATE Content SET [已发]=1 WHERE [ID] <= 132715; UPDATE Content SET [已采]=0 WHERE [PC收录] is null; UPDATE Content SET [已采]=0 WHERE [PC收录] =0; UPDATE Content SET [已采]=0 WHERE [M收录] is null; UPDATE Content SET [已采]=0 WHERE [PC收录] =0 and [PC] LIKE '%yanchupiaowu1%' UPDATE Content SET [已采]=0 WHERE [M收录] =0 and [M] LIKE '%yanchupiaowu1%'
添加自增id:
alter table `wenzhang_m` add id int auto_increment primary key;
增加随机数量
update destoon_sell set hits=hits+FLOOR(10 +(RAND() * 50)) where adddate="2010-5-17"
字段大写变小写
UPDATE `icms_tag` SET `title` = LOWER(`title`); UPDATE `icms_tag` SET `name` = LOWER(`name`);
若数据库ID不连贯,可重置ID
-- 将表里的id列,取消自增,取消主键 ALTER TABLE 表名 MODIFY id INT(11) NOT NULL FIRST,DROP PRIMARY KEY; -- 新增id2列,自增,主键。名字可以自定义。 ALTER TABLE 表名 ADD id2 INT(11) NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY (id2); -- 删除id列 ALTER TABLE 表名 DROP id; -- 把id2改为id ALTER TABLE 表名 CHANGE id2 id INT(11) NOT NULL AUTO_INCREMENT FIRST;
删除:
DELETE FROM `destoon_article_data_25` WHERE `itemid` < 200000; DELETE FROM `destoon_article_25` WHERE `itemid` < 200000; DELETE from wenzhang_m where `发布日期` < '2022-07-31'; DELETE from wenzhang_m where `发布日期` > '2022-08-31' ;
插入新字段:
ALTER TABLE `pgyt_info_24` ADD `baidu` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '百度推送' AFTER `note`; ALTER TABLE `wenzhang_m` ADD `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';
设置唯一值:
ALTER TABLE wenzhang add unique(`url`)
替换字段:
Update `wenzhang_m` SET `MURL` = replace (`MURL`,'http://','https://'); Update `icms_article` SET `tags` = replace (`tags`,',无,',',') Update `wenzhang_m` SET `BM` = replace (`BM`,'wd= Update `destoon_category` SET `seo_title` = replace (`seo_title`,'2019','2022'); Update `destoon_category` SET `seo_keywords` = replace (`seo_keywords`,'2019','2022'); Update `destoon_category` SET `seo_description` = replace (`seo_description`,'2019','2022');
根据日期或时间等值查询
select * from t_date where year_col = 2020; select * from t_date where date_col = '2020-06-03'; select * from t_date where dt_col = '2020-06-03 16:04:04';
根据日期或时间范围查询
select * from t_date where date_col > '2018-01-01'; select * from t_date where dt_col >= '2020-05-01 00:00:00' and dt_col < '2020-05-31 23:59:59'; select * from t_date where dt_col between '2020-05-01 00:00:00' and '2020-05-31 23:59:59';
查询本月的数据
# 查询create_time在本月的数据 select * from t_date where DATE_FORMAT(create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' );