創建數據表和操作數據庫 · 常用筆記

# 創建數據表和操作數據庫 # 原生sql語句： ``` <pre class="calibre13">``` CREATE TABLE IF NOT EXISTS student( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '姓名', `age` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年齡', `sex` TINYINT UNSIGNED NOT NULL DEFAULT 10 COMMENT '性別', `create_at` INT NOT NULL DEFAULT 0 COMMENT '新增時間', `update_at` INT NOT NULL DEFAULT 0 COMMENT '修改時間' )ENGINE=INNODB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1001 COMMENT='學生表'; ``` ``` # 增刪改查 ``` <pre class="calibre13">``` public function sqltest1() { //查詢 // $sqldata = DB::select('select * from student'); //新增 //$sqldata = DB::insert('insert into student(name,age) values (?,?)',['張三1',28]); //返回true //dump($sqldata); //更新 // $num = DB::update('update student set age =? where name = ?',[20,'張三1']); // dump($num); //返回1 //查詢 // $sqldata = DB::select('select * from student where id > ?',[1002]); // dd ($sqldata); //刪除 $num = DB::delete('delete from student where id > ?',[1002]); dd($num); //返回1 } ``` ``` ``` <pre class="calibre13">``` public function query1() { // 查詢構造器 //$num = DB::table('student')->insert(['name'=>'abc','age'=>35]); //$num = DB::table('student')->insertGetId(['name'=>'we','age'=>12]); $num = DB::table('student')->insert([ ['name'=>'abc','age'=>35], ['name'=>'test','age'=>23] ]); return $num; } ``` ``` ``` <pre class="calibre13">``` // 更新數據 // $num = DB::table('student') // ->where('id',1003) // ->update(['name'=>'ceshi1003']); 返回1 // 自增 //$num = DB::table('student')->where('id',1004)->increment('age',3); // 自減 // $num = DB::table('student')->where('id',1004)->decrement('age',5); // 不帶條件自減 // $num = DB::table('student')->decrement('age',5); //返回影響的所有行數數量 //自增的同時更新其他字段 // $num = DB::table('student')->where('id',1004)->increment('age',5,['name'=>'gengxin']); ``` ``` ``` <pre class="calibre14">``` // 刪除數據 //DB::table('student')->delete(); //刪除整個表 //$num = DB::table('student')->where('id',1004)->delete(); //返回1 // $num = DB::table('student')->where('id','>=',1004)->delete();//返回影響的行數 //$num = DB::table('student')->truncate(); //不會任何東西刪除整個表 return $num; ``` ```