常用SQL命令

Mysql常用sql

查询

• 基本查询

  select * from students;
  

• 条件查询

  select * from students where name="xianyuluo";
  

• 投影查询

  select name username from students where age=23;
  

  这里将name重命名为username

• 排序

  select * from students ORDER BY score;
  

  降序排列

• 分页查询

   select * from students LIMIT 3 OFFSET 6;
  

  每页展示3条数据，展示第3页

• 聚合查询
  Max()：最大值
  COUNT()：记录数
  AVG()：平均值
  SUM()：求和
  MIN()：最小值

• 多表查询

  select 
      s.id sid,
      s.name sname,
      c.name cname,
  from students s, classes c
  where s.gender='M' and c.id=1;
  

• 连接查询

  select * from students s
  INNER JOIN classes c
  on s.class_id = c.id;
  

  Iner Join：2张表中都存在的数据
  left Outer Join：第一张表中存在的记录
  Right Outer Join：第二张表中存在的记录
  Full Outer Join：两张表都存在的记录

修改数据

• 插入

  Insert Into students (class_id, name, gender, score) values
  (1, "张三", "M", 81)
  (1, "李四", "M", 99)
  

  Insert Into <表名> (字段1, 字段2, 字段3) values (值1, 值2, 值3)

• 更新

  update students set score = score + 10 where score < 70;
  

  update <表名> set <字段1=值1, 字段2=值2> where [];

• 删除

  delete from students where score = 101 or score > 100;
   ``
   > delete from <表名> where [];
  
  

Mysql事物

例子

BEGIN;
update accounts SET balance=balance-100 where id = 1;
update accounts SET balance=balance+100 where id = 2;
COMMIT/ROLLBACK

事物具有ACID特性，用来保证多条SQL语句全部执行或者全部不执行！