一个小练习
包括了增删查改
mysql -uroot -p123456
create database Tao8171;
use Tao8171;
create table state(
name varchar(20) primary key,
emotion varchar(20) not null,
grade int not null,
GPA int,
birthday date);
create table study(
name varchar(20),
grade int not null,
score int,
award varchar(20),
date_year year not null,
foreign key(name) references state(name));
alter table study add primary key(name,grade);
create table civil_servant(
result varchar(20),
name varchar(20) primary key,
grade int not null,
written_test int,
interview int,
temperament varchar(20),
uniform char(1),
date_interview date,
foreign key(name) references state(name));
create table lncd(
Hi char(1),hen char(1),gao char(1),xing char(1),
ren char(1),shi char(1),ni char(1),you char(1),
kong char(1),yi char(1),qi char(1),wan char(1));
insert into lncd values('H','e','l','l','o',',','w','o','r','l','d','!');
insert into state values('tl','nervous','4',null,'1998-11-10');
insert into study values('tl','1','95','third','2017');
insert into study values('tl','2','97','first','2018');
insert into study values('tl','3','99','second','2019');
insert into study values('tl','4','95','first','2020');
insert into civil_servant values(null,'tl','4','1',null,'elegant','Y','2021-06-05');
insert into state values('Tao','nervous','4',null,'1998-11-10');
update study set name= 'Tao' where name= 'tl';
update civil_servant set name= 'Tao' where name= 'tl';
delete from state where name='tl';
update state set GPA = (select avg(score) from study);
update civil_servant set result='pass';
update civil_servant set interview = 1;
update state set emotion='proud';
select * from state;
select * from study;
select * from civil_servant;
select * from lncd;