数据库练习命令

数据库练习命令

show databases;

drop database test;
status;
create database test default character set gbk;
show databases;
status;

use test
show databases;
status;

show tables;
create table xs(学号 char(6) not null primary key,姓名 char(8) not null,专业 char(10) null,性别 tinyint(1) not null default 1,出生日期 date not null,总学分 tinyint(1) null,照片 blob null,
备注 text null)engine=innodb;
show tables;
describe xs;

alter table xs add 奖学金 int null  after 照片;
describe xs;
alter table xs drop  奖学金;
describe xs;

select * from xs;
insert into xs values('0001','王林','Android',default,'1994-02-10',50,null,null);
select * from xs;
insert into xs(照片,专业,性别,学号,出生日期,姓名) values(null,'java',0,'0002','1994-05-12','张玉');
select * from xs;
insert into xs set 姓名='李燕',出生日期='1994-09-10',学号='0003',专业='Android',性别=default;
select * from xs;
select 姓名,专业 from xs;
alter table xs add 密码 int null after 照片;
describe xs;
select * from xs;

update xs set 总学分=总学分+10 where 专业='Android';
select * from xs;
update xs set 总学分=0 where 专业='Android' and 总学分=null;
select * from xs;
update xs set 总学分=总学分+10 where 专业='Android';
select * from xs;
update xs set 备注='辅修JAVA' where 学号='0003';
select * from xs;
create table xs_1 select * from xs;
describe xs_1;
select * from xs_1;
update xs set 密码=111111;
select * from xs;
update xs,xs_1 set xs.密码=222222,xs_1.密码=222222 where xs.专业=xs_1.专业 and xs.专业='Android';
select * from xs;
select * from xs_1;
delete xs,xs_1 from xs_1,xs where xs.专业='Android' and xs.专业=xs_1.专业
select * from xs;
select * from xs_1;

delete from xs;
select * from xs;
delete from xs_1 where 学号='0002';
select * from xs_1;
drop table xs;
drop table xs_2;
show tables;
drop database test;
show databases;
exit|quit