mysql复习笔记之练习

一个小练习

包括了增删查改

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;