MySQL数据库及表的创建与管理
创建数据库
创建库
create database ishop default charset=utf8;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-55Ne2JIT-1598921779984)(C:\Users\三点豪\AppData\Roaming\Typora\typora-user-images\image-20200826184120013.png)]
运行库
use ishop;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vg1TL9tw-1598921779987)(C:\Users\三点豪\AppData\Roaming\Typora\typora-user-images\image-20200826184211067.png)]
创建商品类型表
创建表
mysql> create table tbl_commoditytype(
-> id int(11) primary key,
-> name varchar(50) not null);
查看表结构desc tbl_commoditytype;
创建商品数据表
创建表
mysql> create table tbl_commodity(
-> id int(11) not null,
-> name varchar(50) not null,
-> madein varchar(50) not null,
-> type int(11) not null,
-> inperice int(11) not null,
-> outprce int(11) default null,
-> num int(11) not null default '100',
-> primary key(id),
-> constraint fk_tbl_commodity_tbl_commoditytype foreign key(type) references tbl_commoditytype(id));
查看表结构
desc tbl_commodity;
添加约束值
mysql> alter table tbl_commodity add constraint check(num>0);
查看约束值
select*from information_schema.table_constraints
where table_name='tbl_commodity';
创建客户表
创建表
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 primapy key (id);
添加地址字段
alter table tbl_customer add address varchar(100) not null;
查看客户数据表结构
desc tbl_customer;
创建订单数据表
创建表
create table tbl_order(
id int(11) not null auto_increment,
cuid int(11) not null,
cid int(11) not null,
num int(11) not null,
primary key (id));
添加外键约束
alter table tbl_order
add constraint fk_tbl_order_tbl_customer foreign key (cuid)
references tbl_customer (id);
alter table tbl_order
add constraint fk_tbl_order_tbl_commodity foreign key (cid)
references tbl_commodity (id);
查看表结构
desc tbl_order;
某中学在线考试系统数据库及表创建
创建数据库
创建库
create database scts default charset=utf8;
use scts;
创建学生数据表
创建表
create table tbl_student(
sid varchar(50) primary key,
sname varchar(50),
sage int(11),
sex int(11)
);
查看表结构
desc tbl_student;
创建老师数据表
创建表
create table tbl_teacher(
tid varchar(50),
tname varchar(50),
primary key (tid));
添加默认约束
alter table tbl_student alter sage set default 11;
查看表结构
desc tbl_teacher;
创建科目表
创建表
create table tbl_coures(
cid varchar(50),
cname varchar(50),
tid varchar(50),
constraint fk_tbl_course_tbl_teacher foreign key (tid) references tbl_teacher (tid));
添加主键
alter table tbl_coures add primary key (cid);
查看表结构
desc tbl_coures;
创建成绩数据表
创建表
create table tbl_sc(
scid int(11) primary key auto_increment,
sid varchar(50),
cid varchar(50),
score int(11),
constraint fk_tbl_sc_tbl_coures foreign key (cid) references tbl_coures(cid),
constraint fk_tbl_sc_tbl_student foreign key (sid) references tbl_student(sid));
查看表结构
desc tbl_sc;
添加检查约束
alter table tbl_sc add constraint check(score>0);
select*from information_schema.table_constraints
where table_name='tbl_sc';