MySQL SQL基础DML

 我们先附上表创建的代码

-- 查询数据 --------------------------
-- 数据准备
create table user(
    id          int                 comment '编号',
    workno      varchar(10)         comment '工号',
    name        varchar(10)         comment '姓名',
    gender      char                comment '性别',
    age         tinyint unsigned    comment '年龄',
    idcard      varchar(18)         comment '身份证号',
    workaddress varchar(50)         comment '工作地址',
    entrydate   date                comment '入职时间'
)comment '员工表';

 举个例子，随便插入一点数据

insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1,'1','柳岩','女',20,'123456789012345678','北京','2000-01-01'),
       (2,'2','张无忌','男',18,'123456789012345678','北京','2005-09-01'),
       (3,'3','韦一笑','男',38,'123456789012345678','上海','2005-08-01'),
       (4,'4','赵薇','女',18,'123456789012345678','北京','2009-12-01');

插入

插入指定字段

insert into user(id, work_no, name, gender, age, idc_ard, entrydate) values(1,'1','Itcast','男',10,'1234567890123456789','2020-01-01');
insert into user(id, work_no, name, gender, age, idc_ard, entrydate) values(2,'2','Itcast2','男',1,'1234567890123456789','2020-01-01');

插入全部字段，不设置字段，会自动按照顺序插入

insert  into user values(2,'2','张无忌','男',18,'1234567890123456789','2020-01-01');

批量添加，以逗号隔开

insert  into user values(3,'2','韦一笑','男',18,'1234567890123456789','2020-01-01'),(4,'2','赵敏','男',18,'1234567890123456789','2020-01-01')

显示整个表，user为表名

select * from  user;

更新

-- 修改id为1的数据中name修改为itheima
update user set name='itheima' where id=1;

-- 修改为id为1的数据，将name修改为小昭，gender修改为女
update user set name='小昭',gender='女' where id=1;

-- 将所有员工入职日期修改为 2008-01-01
update user set entrydate='2008-01-01';

不带 'where' 的 'Update' 语句会立刻更新所有表行

删除

-- 删除 gender为女的员工
delete from user where gender='女';

-- 删除所有员工
delete from user;

 很简单，用用就明白了的说