MySql 有关数据库、表定义及约束的基本语法

create  database demo ;/*创建数据库*/
show DATABASES;/*显示数据库*/
drop DATABASE if exists Demo;/*删除数据库*/
use sqls;/*使用数据库*/
show tables;/*显示所有的表*/

/*创建数据库*/
create table students(
stid int ,
stname varchar(20),
stbirth date
);

DESC students;/*显示表结构*/
show create table students;/*查看定义语句*/
alter table students rename student;/*修改表名称*/
alter table student add stuscore float(5,1);/*追加列在末尾*/
alter table student DROP stuscore;/*删除指定列*/
/*在指定列后面追加列*/
alter table student add stuscore float (5,1) after stid;
/*在第一列添加列*/
alter table student add stuscore float (5,1) FIRST;
alter table student modify stuscore int;/*修改列类型*/
/*修改列名称和类型*/
alter table student change stuscore score float(5,1);
create table students select * from student;/*快速复制表*/
create table students select * from student where 1=1;/*快速复制表*/
/*只复制表结构*/
create table students select * from student where 2=1;
create table students like student;/*只复制表结构*/

/*---------------实体完整性--------------------*/

/*primary key --- 主键
		1.不能为null，不能重复
		2.表可以不使用主键约束，如果使用只能添加一次
		3.添加一次主键可以是一个列，或者 同时多个列
*/
drop table if exists student;
create table student (
stid int primary key,/* 主键*/
stname varchar(10),
stscore float(5,1)
);

create table student (
stid int ,
stname varchar(10),
stscore float(5,1),
primary key (stid,stname)/* 声明两个属性共同组成主键*/
);
		
	/*auto_increment --- 自增长
		1.只能作用于数值列
		2.默认从1开始，每次自增1
	unique key --- 唯一约束 
		1.可以为null ，但不能重复
*/
create table student(
stid int primary key auto_increment,/* 自增*/
stname varchar(10), 
stscore float(5,1),
stphone char(11) unique key/* 唯一键，值不可重复*/
)auto_increment=1000;/* 从1000开始自增，增量为1*/

drop table if exists student;
create table student(
stid int,
stname varchar(20),
stphone char(11)
);
alter table student add constraint primary key(stid);/* 追加主键*/
alter table student drop primary key ;/* 删除主键*/
/* 追加唯一主键*/
alter table student add constraint UQ_phone unique key(stphone);
alter table student drop index UQ_phone;/* 删除唯一键*/

/*--------------域完整性（Domain）--------------------*/
/*  null  --- 可以为空，默认值
		not null --- 该列数据不允许为null
		default --- 默认值
*/
drop table if exists students;
create table students (
stid int not null,
stname varchar(20),
staddress varchar(200) default'地址不详'
);
alter table students alter staddress drop default;/*删除默认*/
/*添加默认*/
alter table students alter staddress set default '地址不详';

/*--------------------参照完整性	---------------*/
drop table if exists students;
drop table if exists classes;
/*写法一*/
create table students(
stid int primary key,
stname varchar(10)
)engine = innodb;

create table classes(
cid int primary key,
stid int,
foreign key(stid) references students(stid)
)engine = innodb;

insert into students values (111,'张三');
insert into classes values (222,111);

/*写法二*/
create table students (
stid int primary key,
stname varchar(10)
)engine = innodb;

create table classes(
cid int primary key,
stid int
)engine = innodb;
/*建立外键约束*/
alter table classes add constraint FK_stid
foreign key (stid) references students (stid);

insert into students values (333,'张三');
insert into classes values (444,333);
/*删除外键约束*/
alter table classes drop foreign key FK_stid;

/*---------------------级联删除------------------*/
drop table if exists students;
drop table if exists classes;

create table students(
stid int primary key,
stname varchar(10) 
)engine = innodb;

create table classes(
cid int primary key,
stid int,
foreign key (stid) references students(stid) on delete cascade
)engine = innodb;

insert into students values(1111,'张三');
insert into classes values(1,1111);

delete from students where stid=1111;

/*------------------级联更新-----------------*/
drop table if exists classes;
drop table if exists students;

create table students(
stid int primary key,
stnaem varchar(10)
)engine = innodb;

create table classes(
cid int primary key,
stid int,
foreign key(stid) references students (stid) on update cascade
)engine = innodb;
insert into students values(1111,'张三');
insert into classes values(1,1111)
update students set stid=2222;