增删改查

//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.'<br>';
	} 

//表数据迁移
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;