mysql增改查的sql语句学习笔记

mysql增改查的sql语句学习笔记


DATE: 2017-11-02 10:09:37

  • 创建数据表
CREATE TABLE IF NOT EXISTS `posts`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `date` DATE,
   `title` TINYTEXT,
   `content` TEXT,
   PRIMARY KEY ( `id` )
)ENGINE=yixzm DEFAULT CHARSET=utf8;
  • 插入数据格式
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

实际命令

INSERT INTO posts ( id, date,title, content)
                       VALUES
                       ( 1, "2017-11-1","title","正文1" );
INSERT INTO posts ( id, date,title, content)
                       VALUES
                       ( 2, "2017-11-2","title","正文2" );
INSERT INTO posts ( id, date,title, content)
                       VALUES
                       ( 3, "2017-11-3","title","正文3" );
  • 查询表的字段信息

desc 表名称;
实际效果如下:
这里写图片描述

  • 添加表字段,如增加摘要

alter table posts add excerpt varchar(100) not null;

  • 增加字段值

INSERT INTO 表名 SET 字段名1=值1[,字段名2=值2,……]
比如增加摘要
insert into posts where id=2 set excerpt="摘要1" ;

  • 修改字段值

update posts set date="2017-11-04" where id=4;

  • 修改字段类型

alter table posts modify column id bigint;

最终效果如下:
这里写图片描述

版权声明

弈心博客


本文首发site_name,转载请附上博文链接!