show databases;
-- 如果存在名為school的數(shù)據(jù)庫(kù)就執(zhí)行刪除
drop database if exists school;
-- 創(chuàng)建數(shù)據(jù)庫(kù)
create database school default charset utf8;
-- 切換到school數(shù)據(jù)庫(kù)
use school;
-- 查看所有表
show tables;
-- 如果存在名為tb_student的表就刪除它
drop table if exists tb_student;
-- 創(chuàng)建名為tb_student的表
create table tb_student(
stuid integer not null comment '學(xué)號(hào)',
stuname varchar(20) not null comment '姓名',
stusex boolean default 1 comment '性別',
stubirth date comment '生日',
primary key (stuid)
);
-- not null 非空約束（列的值不能為空）ro
-- default 默認(rèn)值約束（如果沒有給列賦值就使用默認(rèn)值）
查看描述表
desc tb_student;
-- 修改表添加一個(gè)列
alter table tb_student add column stuaddr varchar(255);
-- 修改表刪除一個(gè)列
alter table tb_student drop column stuaddr;
-- 修改表修改一個(gè)列
alter table tb_student modify column stuaddr varchar(100);
alter table tb_student change column addr varchar(100);
-- 向tb_student表插入數(shù)據(jù)
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);
-- 刪除數(shù)據(jù)
delete from tb_student where stuid=1002;
delete from tb_student where stuid in (1004,1006);
truncate table tb_student;
-- 更新修改數(shù)據(jù)
update tb_student set stuaddr='四川綿陽(yáng)' 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;
-- 修改學(xué)生表，建立學(xué)生對(duì)學(xué)院的多對(duì)一關(guān)系
alter table tb_student add column collid integer;
-- 通過添加外鍵(foreign key)約束來建立學(xué)生表對(duì)學(xué)院表的參照關(guān)系（學(xué)生表中的學(xué)院編號(hào)要參照學(xué)院表）
alter table tb_student add constraint fk_student_collid
foreign key (collid) references tb_college (collid);
-- 創(chuàng)建老師表tb_teacher
create table tb_teacher(
teaid integer not null comment '工號(hào)',
teaname varchar(20) not null comment '老師姓名',
teatitle varchar(10) not null comment '老師職稱',
collid integer not null comment '所屬學(xué)院',
primary key (teaid),
foreign key (collid) references tb_college(collid)
);