mysq集合差操作_mysql操作集合 常用篇

增删改查

//where 1 =1;

//这个1=1常用于应用程序根据用户选择项的不同拼凑where条件时用的。

$sql = 'delete from MyClass where id=1';

#mysql 字段追加字符串

$sql = "update `news` set `content`=CONCAT(content,'今天又看了') where `id`=$_GET[id]";

#mysql 替换字段//删除字段

REPLACE(string,search_str,relpace_str)

$sql = "UPDATE `tbl_develop_app_tools` SET `AppID` = REPLACE ( `AppID`, '2', '') where `ToolsID` =1";

$sql = "update person set number=null,name=null";

//type中以 1,3,4的格式存储.

$this->db->where("FIND_IN_SET($apptype,TypeID) !=", 0); FIND_IN_SET({$v},{$k})

//select * from treenodes where FIND_IN_SET(id,'1,2,3,4,5'); 与where in 相反

//按照既定的顺序排序 指定顺序

$sql = 'select * from tb order by decode(blogid,3,1,2), blogid'; //312 456789

//中文排序

$sql = "SELECT * FROM tbl_lib_gametype WHERE `IsH5`='1' and State='0' order by convert(Title USING gbk) COLLATE gbk_chinese_ci";

//重复的值也显示一次

$sql = 'SELECT DISTINCT Company FROM Orders';

#order by 、group by 、having的用法区别 having(并且)

//group by 分组

//having 分组过滤

$sql = 'select count(pid) , pid from products group by uid having count(pid)> 5'; //having count(pid) between 3 and 5

查找重复的数据

$this->DB::whereRaw("SELECT COUNT( * ) AS c, book_name FROM `table_keywords_ebook` GROUP BY keyword HAVING c >1")->get();

迁移数据表

RENAME TABLE old_table TO new_table

rename table a.table to b.table

如果存在则更新，不存在则插入新数据

$sql = "INSERT INTO {$ecs->table(‘cat_lang')} (cat_id,lang_id,cat_name,keywords,cat_desc)

VALUES({$cat_id},{$k},'{$val['cat_name']}','{$val['keywords']}','{$val['cat_desc']}')

ON DUPLICATE KEY UPDATE cat_name='{$val['cat_name']}',cat_desc='{$val['cat_desc']}',keywords='{$val['cat_desc']}'";

批量更新一个表

UPDATE categories

SET display_order = CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END,

title = CASE id

WHEN 1 THEN 'New Title 1'

WHEN 2 THEN 'New Title 2'

WHEN 3 THEN 'New Title 3'

END

WHERE id IN (1,2,3)

//这句sql的意思是，更新display_order 字段，如果id=1 则display_order 的值为3，如果id=2 则 display_order 的值为4，如果id=3 则 display_order 的值为5。

foreach ($array['PriceDiscount'] as $FeeTypeID => $val) {

$sql = "UPDATE $table SET PriceDiscount = CASE Level ";

foreach ($val as $Level => $vals) {

$Levels = implode (",", array_keys($val));

$sql .= sprintf("WHEN %d THEN %d ", $Level, $vals);

}

$sql .= "END WHERE Level IN ($Levels) AND FeeTypeID = $FeeTypeID";

echo $sql.'
';

}

//表数据迁移

update tbl_novel_version_part a ,tbl_novel_version_part_page b SET a.txt_url=b.url , a.url=NULL

WHERE a.novel_id=b.novel_id AND a.part_id=b.part_id and a.txt_url IS NULL AND a.`status` != 2 AND a.novel_id = 3736;

库

//创建一个库

CREATE DATABASE IF NOT EXISTS $yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

//创建一个表

mysql>use test;

mysql>CREATE table employees(id int(6),name varchar(30),hired date null,job_code int(6), store_id int(6)) engine=MyISAM default charset=utf8 ;

//复制表结构

mysql>CREATE table t3 like t1;

//复制表数据

mysql>INSERT into t3 select * from t1 ;

INSERT INTO tbl_company_organ (SELECT * FROM tbl_company_organ_test WHERE OrganSN NOT IN (SELECT tbl_company_organ.OrganSN FROM tbl_company_organ ) );

// 新增/修改字段

mysql>ALTER TABLE `tbl_lib_feetype` ADD `IsH5` TINYINT( 4 ) NOT NULL DEFAULT '0' COMMENT '是否奇优用户' AFTER `Title`

mysql>ALTER TABLE `tbl_lib_feetype` DROP `IsH5`;

//插入

mysql>INSERT INTO table_name(column_list,column_list) VALUES(val1,val2),(val1,val2),(val1,val2),(val1,val2);

//创建一个用户

mysql>CREATE USER 'username'@'host' IDENTIFIED BY 'password';

//授权一个用户

mysql>grant all on *.* to 'username'@'192.168.1.170' identified by '456';

//修改索引

//alter table (修改)来创建普通索引、unique索引或primary key索引

mysql>ALTER TABLE table_name ADD INDEX index_name(column_list); //普通索引

mysql>ALTER TABLE table_name ADD UNIQUE(column_list); //唯一索引 作为唯一索引的列不能有重复的值

mysql>ALTER TABLE table_name ADD PRIMARY KEY(column_list); //主键索引

//创建索引

mysql>CREATE TABLE table_name(id int unsigned auto_increment primary key,name varchar(30));

mysql>CREATE INDEX index_name ON table_name(column_list);

mysql>CREATE UNIQUE INDEX index_name ON table_name(column_list); //唯一索引

//删除索引

mysql>DROP INDEX index_name ON table_name;

mysql>ALTER TABLE index_name DROP INDEX index_name;

mysql>ALTER TABLE index_name DROP PRIMARY KEY;

//删除重复的值 //只保留一条记录

DELETE FROM tbl_book_info WHERE (book_name, book_author) IN (

SELECT

t.book_name,

t.book_author

FROM (SELECT book_name,book_author FROM tbl_book_info GROUP BY book_name,book_author HAVING count(1) > 1) t

);

DELETE FROM company WHERE (id) IN (

SELECT

t.id

FROM (SELECT id FROM company GROUP BY id HAVING count(1) > 1) t

)

//添加自增

mysql>ALTER TABLE table_name MODIFY id int UNSIGNED NOT NULL AUTO_INCREMENT;

//删除自增

mysql>ALTER TABLE table_name MODIFY id int UNSIGNED NOT NULL AUTO_INCREMENT;

//创建一个视图 更像一个中介,缓存

mysql>CREATE VIEW v_name as select * from t1 where id > 4 and id <8;

mysql>drop table 数据表名; //删除视图

//删除一个库

mysql>drop database 数据库名;

//删除一个表

mysql>drop table 数据表名;

//清空一张表中的内容

mysql>truncate TABLE XXX;

--------------------------------自动增量-----------------------------------------

//修改自增量 AUTO_INCREMENT = 1

ALTER TABLE table_name AUTO_INCREMENT = 1;