小记2--MySQL数据库DML操作

MySQL数据库DML操作

商品管理

新增商品信息

insert into tbl_commodity values ('1','变形金刚-擎天柱','中国','1','20','50','60');

新增商品类型数据

insert into tbl_commoditytype values ('1','玩具');
insert into tbl_commoditytype values ('2','文具');
insert into tbl_commoditytype values ('3','书籍');

商品表新增数据

insert into tbl_commodity values ('1','变形金刚-擎天柱','中国','1','20','50','60');
insert into tbl_commodity values ('2','变形金刚-霸天虎','中国','1','20','45','50');
insert into tbl_commodity values ('3','变形金刚-威震虎','美国','1','120','245','15');

insert into tbl_commodity values ('4','魔仙玩偶','中国','1','6','12', default);
insert into tbl_commodity values ('5','超人玩偶','中国','1','29', null, '100');
insert into tbl_commodity values ('6','小霸王游戏机','中国','1','50', null ,default);

insert into tbl_commodity values ('23','中华铅笔HB','中国','2','1', '2','100'),('24','中华铅笔2B','中国','2','1', '2','109'),('25','中华铅笔2H','中国','2','1', '2','100');
insert into tbl_commodity values ('50','格林童话','中国','3','23', '29','12'),('51','上下五千年','中国','3','200', '400','3'),('52','电脑报15年合集','中国','3','24', '52','2');

查看数据

select*from tbl_commoditytype;
select*from tbl_commodity;

修改数据

update tbl_commodity set num=20 where id=52;

查看数据

select*from tbl_commodity;

修改数据

update tbl_commodity set outprce=59 where id=5;
update tbl_commodity set outprce=259 where id=6;

查看数据

select*from tbl_commodity;

delete from tbl_commodity where id=4;

查看数据

select*from tbl_commodity;

客户管理

新增数据

insert into tbl_customer values ('1','刘德华','123','1','中国');

查看数据

select*from tbl_customer;

删除数据

delete from tbl_customer where id=1;

新增数据

insert into tbl_customer values ('1','刘德华', MD5('18918001900'),'1','中国');

查看数据

select*from tbl_customer;

修改数据表

update tbl_customer set address='北京' where id=1;

修改数据

update tbl_customer set phone=MD5('18918001900') where id=1;

查看数据

select*from tbl_customer;

订单管理

新增数据

insert into tbl_order (cuid, cid, num) values (1,1,2);

查看数据

select*from tbl_order;

]

修改数据

update tbl_order set num=3 where id=1;

查看数据

select*from tbl_order;

教师管理

新增数据

insert into tbl_teacher (tid, tname) values (1,'Andy');

查询数据

select*from tbl_teacher;

修改数据

update tbl_teacher set tname='刘德华' where tid=1;

查询数据

select*from tbl_teacher;

科目管理

新增数据

insert into tbl_coures (cid, cname, tid) values (1,'数学',1), (2,'语文',1), (3,'地理',1);

查看数据

select*from tbl_coures;

修改数据

update tbl_coures set cname='体育' where cid=1;

删除数据

delete from tbl_coures where cid=3;

查看数据

select*from tbl_coures;

学员管理功能实现

新增数据

insert into tbl_student (sid, sname, sage ,sex) values ('s001','jack', 21,1),('s002', 'rose', 20, 0),('s003', 'tom', 21,1);

修改数据

update tbl_student set sage=sage +1;

查看数据

select*from tbl_student;

删除数据

delete from tbl_student where sid='003';

查看数据

select*from tbl_student;

成绩管理

新增数据

insert into tbl_sc (scid, sid, cid, score) values (1, 's001', 1, 89),( 2, 's001', 2, 91);

修改数据

update tbl_sc set score=86 where scid=1;

查看数据

select*from tbl_sc;

删除数据

delete from tbl_sc where scid=1;

查看数据

select*from tbl_sc;

修改数据

update tbl_sc set score=86 where scid=1;

查看数据

select*from tbl_sc;

[外链图片转存中…(img-y1TGdIMc-1599005561972)]

删除数据

delete from tbl_sc where scid=1;

[外链图片转存中…(img-QHYDK7Bi-1599005561974)]

查看数据

select*from tbl_sc;