1 创建了这些表,这些表结构
create table co_cust(
cust_id varchar(30) not null,
cust_name varchar(100),
cust_tel varchar(30),
cust_addr varchar(100),
primary key(cust_id)
);
#create a table like this;
cretae table co_itme(
itme_id varchar(30) not null,
item_name varcahr(100),
item_price decimal(10,2), #最大存十位数字,小数位数是两位
itme_type varchar(100),
primary key(itme_id)
);
#订单表
create table co_order(
co_num varchar(30) not null,
cust_id varchar(30) ,
co_date varchar(30),
qty_amount decimal(18,2),
primary key(co_num)
);
#订单明细表
create table co_order_detail(
co_num varchar(30) not null,
line_num varcahr(30) not null,
item_id varchar(30),
co_data varchar(30),
qty_ord decimal(18,2),
qty_amount decimal(18,2),
primary key(co_num,line_num)
);
insert插入数据
1
insert into co_cust(cust_id,cust_name,cust_tel,cust_addr) values('C001','zhangsan','18601019988','chaoyangshequ');
2
insert into co_cust(,,,) values(,,,);
3
4
insert into co_order_detail(co_num,line_num,item_id,qty_ord,qty_amount) values();
select 查询语句
1 查询当前有哪些客户
select cust_id,cust_name,cust_tel,cust_addr from co_cust;
2 查询当前有哪些商品?
select item_id,item_name,item_price,item_type from co_item;
3 查询20160729这一天产生了哪些订单?
select co_num,cust_id,co_date,qty_amount from co_order where co_date='20160729';
4 查询金额为20160729 且金额大于60的订单
select co_num,cust_id,co_date,qty_amount from co_order where co_date='20160729' and qty_amount>60;
5 查询现有订单,按照订单金额降序排列
select co_num,co_num,cust_id,co_date,qty_amount from co_order order by qty_amount desc;
6 查询已有订单的客户的id; distinct 关键字,去重复
select distinct cust_id from co_order;
update 更新语句
***更新之前应该备份数据库
***如果没有where条件,将全部更新
1 修改客户张三的电话号码
select cust_tel from co_cust where cust_name='张三'
update co_cust set cust_tel ='31231' where cust_id='c001'; #这个就是张三
select cust_tel from co_cust where cust_name='张三';
delete 语句
***删除之前要备份,没有where会全部删除
1.delete from co_cust where cust_id='c001';