创建数据表和操作数据库 · 常用笔记

# 创建数据表和操作数据库 # 原生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; ``` ```