MySQL增删改

1.插入语句

• 语法:
  • INSERT INTO [表名] (列名) VALUES (值);
  • INSERT INTO [表名] SET [列名]=[值],[列名]=[值]...;
• 注意事项:
  • 表中设有非空约束的字段必须赋值
  • 列名个数与顺序需要与值对应
  • 第一种方式可以同时插入多条值,第二种不可以
  • 省略列名时,默认所有字段都赋值
• 示例
  • INSERT INTO beauty(name,sex,id,phone) VALUES(‘小花‘,‘女‘,17,‘110‘);
  • INSERT INTO beauty VALUES(18,‘张飞‘,‘男‘,NULL,‘119‘,NULL,NULL);
  • INSERT INTO beauty SET id=19,NAME=‘刘柳‘,phone=‘999‘;
  • INSERT INTO beauty(age,name,sex,id,phone) VALUES(23,‘唐艺昕1‘,‘女‘,1,‘1898888888‘) ,(24,‘唐艺昕2‘,‘女‘,2,‘1898888888‘),(25,‘唐艺昕3‘,‘女‘,3,‘1898888888‘);

2.删除语句

• 语法: DELETE FROM [表名] WHERE [筛选条件];
• 示例
  • DELETE FROM beauty WHERE phone LIKE ‘%9‘;
  • DELETE b FROM beauty b INNER JOIN boys bo ON b.boyfriend_id= bo.idWHERE bo.boyName=‘张无忌‘;

3.修改语句

• 语法: UPDATE [表名] SET 列=新值,列=新值,... WHERE 筛选条件;

• 示例

  #修改姓唐的美女电话号为13899888899 
  UPDATE beauty SET phone = '13899888899' WHERE NAME LIKE '唐%';
  
  #修改张无忌的女朋友的phone和userCP
  UPDATE boys bo
  INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
  SET b.`phone`='119',bo.`userCP`=1000
  WHERE bo.`boyName`='张无忌';