[大数据]PHP中执行sql语句

一：增

1. 按照字段全部插入

• INSERT INTO student VALUES(1025, ‘郭翔’, 18, ‘男’, ‘GP20’, 100)
• 在插入的时候, 主键可以不写, 写一个 null

2. 选取字段插入

• INSERT INTO student (字段1, 字段2, …) VALUES(数据1, 数据2, …)
• 注意: 其他字段需要允许为空

  // 1. 按照字段全部插入
 $sql = 'INSERT INTO `student` VALUES(NULL, "郭翔", 18, "男", "2020", 100)';

  // 2. 选取字段插入
  $sql = 'INSERT INTO `student` (`name`, `score`) VALUES("郭翔", 59)';

二：删

1. 删除的语句

• DELETE FROM student WHERE 条件

  $sql = 'DELETE FROM `student` WHERE `id`>1025';

三：改

1. 修改一条数据

• UPDATE student SET 要修改的字段=要修改的值 WHERE 条件

  // 1. 修改一条数据
  $sql = 'UPDATE `student` SET `gender`="女" WHERE `id`>1000';

四：查

1. 查询所有

• SELECT * FROM 表
• 查询这个表里面的所有数据, 每一个数据的每一个字段都要

  // 1. 查询所有
   $sql = 'SELECT * FROM `student`';

2. 查询所有数据, 但是只要几个字段

• SELECT 字段1, 字段2, … FROM 表

  // 2. 查询所有数据, 但是只要几个字段
  $sql = 'SELECT `name`, `age` FROM `student`';

3. WHERE 关键字

• 按照条件查询
• SELECT * FROM student WHERE age>28

  // 3. WHERE 条件查询
$sql = 'SELECT * FROM `student` WHERE `age`>28';

4. AND 关键字

• 并列条件查询
• SELECT * FROM student WHERE 条件1 AND 条件2

  // 4. AND 关键字
 $sql = 'SELECT `score`, `age` FROM `student` WHERE `score`>90 AND `age`>25';

5. OR 关键字

• 或者条件查询
• SELECT * FROM student WHERE 条件1 OR 条件2

  // 5. OR 关键字
$sql = 'SELECT `age` FROM `student` WHERE `age`>28 OR `age`<20';

6. LIMIT 分页查询

• SELECT * FROM student LIMIT 开始索引, 多少个

  // 6. LIMIT
$sql = 'SELECT `name` FROM `student` LIMIT 10, 10';

7. ORDER BY 排序

• SELECT * FROM student ORDER BY 字段 DESC-----从大到小
• SELECT * FROM student ORDER BY 字段 ASC-------从小到大

  // 7. ORDER BY
$sql = 'SELECT * FROM `student` ORDER BY `score` DESC';//从大到小
$sql = 'SELECT * FROM `student` ORDER BY `score` ASC';//从小到大

8. LIKE % 模糊查询

• SELECT * FROM student WHERE name LIKE ‘三%’ 必须是 三xxx
• SELECT * FROM student WHERE name LIKE ‘%三’ 必须是 xxx三
• SELECT * FROM student WHERE name LIKE ‘%三%’ 包含三就可以

  // 8. LIKE   %   模糊查询
$sql = 'SELECT `name` FROM `student` WHERE `name` LIKE "郑%"';
$sql = 'SELECT `name` FROM `student` WHERE `name` LIKE "%三"';
$sql = 'SELECT `name` FROM `student` WHERE `name` LIKE "%三%"';

9. LIKE _ 模糊查询

• 一个 _ 只能表示一位
• SELECT * FROM student WHERE name LIKE ‘三’ 必须是 x三x(三位)
• SELECT * FROM student WHERE name LIKE ‘_三’ 必须是 x三(两位)
• SELECT * FROM student WHERE name LIKE ‘__三’ 必须是 xx三(三位)

  // 9. LIKE   _   模糊查询
$sql = 'SELECT `name` FROM `student` WHERE `name` LIKE "_三_"';

10. 聚合函数

• 在查询的时候直接帮我们计算一些内容
  10-1. count() 统计数字
  => SELECT count(*) 别名 FROM student;
  10-2. avg() 求平均值
  => SELECT avg(哪一个字段) 别名 FROM student;
  10-3. max() 最大值
  => SELECT max(字段) 别名 FROM student;
  10-4. min() 最小值
  => SELECT min(字段) 别名 FROM student;

  // 10-1. count聚合
  $sql = 'SELECT count(*) c FROM `student`';

  // 10-2. avg聚合
  $sql = 'SELECT avg(`score`) a FROM `student`';

  // 10-3. max聚合
  $sql = 'SELECT max(`score`) max FROM `student`';

  // 10-4. min聚合
  $sql = 'SELECT count(*) c, min(`score`) min, avg(`score`) a, max(`score`) max FROM `student`';