Mysql 创建库和表的处理

创建数据库ishop
create database ishop default charset=utf8;



创建表 tbl_commoditytype
create table tb1_commoditytype(

    -> id int primary key,

    -> name varchar(50) not null);



创建commodity
create table tbl_commodity(

id int(11) primary key not null,

name varchar(50) not null,

madein varchar(50) not null,

type int(11) not null,

inprice int(11) not null,

outpeiece int(11) default null,

num int(11)not null default'100',

constraint ftct foreign key(type) references tb1_commoditytype(id));




创建客户数据表tbl customer
create table tbl_customer(

id int(11) not null,

name varchar(50) not null,

phone varchar(50) not null,

gender int(11) not null default'1');




添加主键约束
alter table  tbl_customer add constraint primary key(id);





添加地址字段
alter table tbl_customer add address varchar(100) not null;



创建订单数据表
create table tbl_order(
id int(11) primary key  auto_increment not null,
cuid int(11) not null,
cid int(11) not null,
num int(11) not null);




添加外键约束
alter table tbl_order
add constraint ftotc foreign key(cuid)references tbl_customer(id);
alter table tbl_order
add constraint ftotcy foreign key(cuid)references tb1_commoditytype(id);





创建学生数据表
create table tbl_studen(
sid varchar(50)primary key,
sname varchar(50),
sage int(11),
sex int(11));



创建老师数据表
create table tbl_teachaer(
tid varchar(50) primary key,
tname varchar(50));



添加默认约束
alter table tbl_studen alter sage set default 11;



创建科目数据表
create table tbl_course(
cid varchar(50),
cname varchar(50),
tid varchar(50),
constraint ftctt foreign key(tid)references tbl_teacher(tid));



添加主键
alter table tbl_course add primary key(cid);



创建数据表tbl_sc
create table tbl_sc(
scid int(11) primary key auto_increment,
sid varchar(50),
cid varchar(50),
score int(11),
constraint ktstcu foreign key(cid) references tbl_course(cid),
constraint ktscts foreign key(sid) references tbl_studen(sid));



添加检查约束
alter table tbl_sc add constraint check(score>0);



查看检查约束
select*from information_schema.table_constraints;
where table_name=tbl_sc;

2.MDL搞一搞

INSERT INTO tb1_commoditytype VALUES(1,'玩具');
INSERT INTO tb1_commoditytype VALUES(2,'文具');
INSERT INTO tb1_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,120,245,15);
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);



查看结果






修改数据
update tbl_commodity set `num`=20 WHERE id=52;



修改数据
UPDATE tbl_commodity SET outpeiece=59 WHERE id=5;
UPDATE tbl_commodity SET outpeiece=258 WHERE id=6;



删除数据
DELETE FROM tbl_commodity WHERE id=4;



新增数据
INSERT INTO tbl_customer VALUES(1,'刘德华',123,1,'中国');



删除数据
DELETE FROM tbl_customer WHERE id=1;



加密函数手机号
INSERT INTO tbl_customer VALUES(1,'刘德华',MD5(18918001900),1,'中国');



修改数据
UPDATE tbl_customer SET address='北京' WHERE id=1;



修改加密数据
UPDATE tbl_customer SET  phone=MD5(1801999888888) WHERE id=1;



新增数据
INSERT INTO tbl_order(cuid,cid,num)VALUES(1,1,2);



修改数据
update tbl_order SET num=3;WHERE id=1;




新增数据
INSERT INTO tbl_course(cid,cname,tid)VALUES(1,'数学',1),(2,'语文',1),(3,'地理',1);




新增数据
INSERT INTO tbl_teacher (tid,tname)VALUES(1,'Andy');




修改数据
UPDATE	tbl_course SET cname='体育'WHERE cid=1;






删除数据
DELETE FROM tbl_course WHERE cid=3;




新增数据
INSERT INTO	tbl_studen(sid,sname,sage,sex)
VALUES('s001','Jack',21,1),('s002','Rose',20,1),('s003','Tom',21,1);




修改数据
UPDATE tbl_studen SET sage=sage+1;




删除数据
DELETE FROM tbl_studen WHERE sid=003;




新增数据
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;




删除数据
DELETE FROM tbl_sc WHERE scid=1;