#数据库的创建
create database crm_info_test;

#数据库的删除
drop database crm_info_test;

#创建中介表
create table zj_zjxx(
    ID varchar(64) primary key ,
    zj_name varchar(10) unique
);

#创建客户表
create table kh_khxx(
    ID varchar(64) primary key,
    zj_id varchar(64) not null,
    kh_name varchar(20),
    constraint fk_zhkh foreign key(zj_id) references zj_zjxx(ID)
);

#创建合同表
create table ht_htxx(
    ID varchar(64) primary key,
    kh_id varchar(64) not null,
    ht_date datetime,
    ht_name varchar(20),
    ht_price float
) ;

#修改表
#新增一列
alter table kh_khxx
    add kh_no1 varchar(10),
    add kh_temp int;

#修改列名
alter table kh_khxx
    change kh_no1 kh_no varchar(10);

#删除列
alter table kh_khxx 
    drop column kh_temp;

#新增约束 
alter table ht_htxx
    add constraint htkh foreign key(kh_id) references kh_khxx(ID),
    add constraint price_5 check (ht_price>5);

#删除约束
alter table ht_htxx
    drop check price_5;

#删除表
drop table test;

#表中插入数据 

insert into zj_zjxx values (replace(uuid(),"-",""),‘广州你最好中介机构‘);

#指定列名插入数据 
insert into kh_khxx(ID,zj_id) values (replace(uuid(),"-",""),‘22ffadefb2d611eaba23005056c00001‘);

#修改某个值
update kh_khxx
set kh_name=‘上海‘ where kh_no = 1;

SET SQL_SAFE_UPDATES = 0

#建立索引 
create index kh_index on kh_khxx(kh_no);