MySQL创建数据库及表和约束语句

-- 创建数据库
-- 创建数据库
CREATE DATABASE IF NOT EXISTS shopping DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

-- 创建表，不加任何约束
create table aaa(
  u_id int,
  u_name varchar(20),
  u_sex varchar(20),
  u_date date
)

create table aaaa(
  a_id int,
  u_id int,
  a_name varchar(20)
)

create table tab_b(
  b_id int,
  b_name int,
  a_sex varchar(20)
)
alter table tab_b add primary key(b_id);
alter table tab_b modify b_id int auto_increment 

insert into tab_b(b_name,b_date) values('王青山','男'),('顾坤伦','男')

insert into aaa(u_name,u_sex,u_date) values('zhao','男','1997-03-14');

insert into aaaa values(3,10,'z');

select *from aaa

drop table aaa
-- 添加主键
alter table aaa add primary key(u_id);
alter table aaaa add primary key(a_id);

-- 删除主键
alter table aaa drop primary key;

-- 主键自增
alter table aaa modify u_id int auto_increment 

-- 删除主键自增
alter table aaa modify u_id int

-- 添加字段
alter table student add column s_no int not null;

ALTER TABLE student ADD age int DEFAULT 20 COMMENT '年龄';

-- 删除字段
alter table student drop column age;

-- 添加非空约束
alter table aaa modify u_name varchar(20) not null;

-- 删除非空约束
alter table aaa modify u_name varchar(20) null;

-- 添加唯一约束
alter table aaa add unique u_name(u_name);

-- 删除唯一约束
alter table aaa drop key u_name

-- 添加默认值 
alter table aaa alter u_sex set default '男';

-- 删除默认值 
alter table aaa alter u_sex drop default

-- 添加外键约束
alter table aaaa add constraint fk_uaid foreign key(u_id) references aaa(u_id);

-- 删除外键约束
alter table aaaa drop foreign key fk_uaid;
alter table aaaa drop index fk_uaid;

-- 创建时加约束
create table students (
  s_id int auto_increment primary key,
  s_name varchar(20), 
  s_sex varchar(20) default '男' check(s_sex in ('男','女')),
  s_date date,
  u_id int,
  constraint pk_test_id foreign key (u_id) references student(u_id)
)