show databases;
-- 如果存在名为school的数据库就执行删除
drop database if exists school;
-- 创建数据库
create database school default charset utf8;
-- 切换到school数据库
use school;
-- 查看所有表
show tables;
-- 如果存在名为tb_student的表就删除它
drop table if exists tb_student;
-- 创建名为tb_student的表
create table tb_student(
stuid integer not null comment '学号',
stuname varchar(20) not null comment '姓名',
stusex boolean default 1 comment '性别',
stubirth date comment '生日',
primary key (stuid)
);
-- not null 非空约束(列的值不能为空)ro
-- default 默认值约束(如果没有给列赋值就使用默认值)
查看描述表
desc tb_student;
-- 修改表添加一个列
alter table tb_student add column stuaddr varchar(255);
-- 修改表删除一个列
alter table tb_student drop column stuaddr;
-- 修改表修改一个列
alter table tb_student modify column stuaddr varchar(100);
alter table tb_student change column addr varchar(100);
-- 向tb_student表插入数据
insert into tb_student values(1001,'小明',1,'1992-12-2','四川成都');
insert into tb_student(stuid,stuname) values(1003,'小华');
insert into tb_student(stuid,stuname,stusex) values(1004,'aa',1),(1005,'bb',0),(1006,'cc',1);
-- 删除数据
delete from tb_student where stuid=1002;
delete from tb_student where stuid in (1004,1006);
truncate table tb_student;
-- 更新修改数据
update tb_student set stuaddr='四川绵阳' where stuid=1003;
update tb_student set stuaddr='四川广元',stubirth='1997-6-12' where stuid=1005;
-- 查看tb_student表
select * from tb_student;
select * from tb_student\G;
-- 修改学生表,建立学生对学院的多对一关系
alter table tb_student add column collid integer;
-- 通过添加外键(foreign key)约束来建立学生表对学院表的参照关系(学生表中的学院编号要参照学院表)
alter table tb_student add constraint fk_student_collid
foreign key (collid) references tb_college (collid);
-- 创建老师表tb_teacher
create table tb_teacher(
teaid integer not null comment '工号',
teaname varchar(20) not null comment '老师姓名',
teatitle varchar(10) not null comment '老师职称',
collid integer not null comment '所属学院',
primary key (teaid),
foreign key (collid) references tb_college(collid)
);