#（1）創(chuàng)建數(shù)據(jù)庫(kù)stuexpm

create database stuexpm;

#（2）創(chuàng)建studentinfo表，顯示studentinfo表的基本結(jié)構(gòu)

#表4.9

use stuexpm;

create table StudentInfo

(

StudentID varchar(6) not null primary key comment'學(xué)號(hào)',

Name varchar(8) not null comment'姓名',

Sex varchar(2) not null default '男' comment'性別',

Birthdar date not null comment'出生日期',

Speciality varchar(12) null comment'專業(yè)',

Address varchar(50) null comment'家庭地址'

)comment '學(xué)生表';

#（3）由studentinfo表使用復(fù)制方式創(chuàng)建studentinfo1表

create table StudentInfo1 like StudentInfo;

#（4）在studentinfo表中增加一列stuno,添加到表的第1列，不為空，取值唯一并自動(dòng)增加，顯示studentinfo表的基本結(jié)構(gòu)

alter table StudentInfo add column StuNo int not null unique auto_increment first;

#（5）將studentinfo1表的address 列修改 為city ,將數(shù)據(jù)類型改為char,可為空，默認(rèn)值為"北京"，顯示studentinfo1表的基本結(jié)構(gòu)

alter table StudentInfo1 change column Address City char(10) default '北京';

desc StudentInfo1;

#（6）將studentinfo1表的speciality 列修改為school,將數(shù)據(jù)類型改為char,可為空，默認(rèn)值為"計(jì)算機(jī)學(xué)院"

alter table StudentInfo1 change column Speciality School char(12) default '計(jì)算機(jī)學(xué)院';

desc StudentInfo1;

#（7）將studentinfo1表的city列的默認(rèn)值修改為"上海"

alter table StudentInfo1 alter column City drop default;

alter table StudentInfo1 alter column City set default'上海';

desc StudentInfo1;

#（8）將studentinfo1表的city列的類型修改為varchar(20),并移到列Name之后

alter table StudentInfo1 modify column City varchar(20) after Name;

desc StudentInfo1;

#（9）在studentinfo1表中刪除stuno列

alter table studentinfoStudentInfo drop StuNo;

desc StudentInfo;

#（10）將studentinfo1表更名為studentinfo2表

alter table StudentInfo1 rename to StudentInfo2;

show tables;

#（11）刪除studentinfo2表

#添加數(shù)據(jù)內(nèi)容/記錄

#P81-例5.4

use stuexpm;

insert into StudentInfo

values

('181001','成志強(qiáng)','男','1988-08-17','計(jì)算機(jī)','北京市海淀區(qū)'),

('181002','孫紅梅','女','1997-11-23','計(jì)算機(jī)','成都市錦江區(qū)'),

('181003','朱麗','女','1998-02-19','計(jì)算機(jī)','北京市海淀區(qū)'),

('184001','王志勇','男','1997-12-05','電子信息工程','null'),

('184002','周潞潞','女','1998-02-24','電子信息工程','上海市浦東區(qū)'),

('184004','鄭永波','男','1997-09-19','電子信息工程','上海市浦東區(qū)');

#查看數(shù)據(jù)表內(nèi)容記錄

select * from studentinfo;

create table courseinfo

(

courseid varchar(4) not null primary key comment '課程號(hào)',

coursename varchar(16) not null comment '課程名',

credit tinyint null comment '學(xué)分'

)comment '學(xué)生表';

create table scoreinfo

(

studentid varchar(6) not null comment'學(xué)號(hào)',

courseid varchar(4) not null comment'課程號(hào)',

grade tinyint null comment'成績(jī)',

primary key(studentid,courseid)

);

create table teacherinfo

(

teacherid varchar(6) not null primary key comment'教師編號(hào)',

teachername varchar(8) not null comment'姓名',

teachersex varchar(2) not null default '男' comment'性別',

teacherbirthday date not null comment'出生日期',

school varchar(12) null comment'學(xué)院',

address varchar(20) null comment'地址'

);

insert into courseinfo

values

('1004','數(shù)據(jù)庫(kù)系統(tǒng)','4'),

('1025','物聯(lián)網(wǎng)技術(shù)','3'),

('4002','數(shù)字電路','3'),

('801','高等數(shù)學(xué)','4'),

('1201','英語(yǔ)','4');

select * from courseinfo;

insert into scoreinfo

values

('181001','1004','95'),

('181002','1004','85'),

('181003','1004','91'),

('184001','4002','93'),

('184002','4002','76'),

('184004','4002','88'),

('181001','8001','94'),

('181002','8001','89'),

('181003','8001','86'),

('184001','8001','85'),

('184002','8001',null),

('184004','8001','94'),

('181001','1201','92'),

('181002','1201','78'),

('181003','1201','94'),

('184001','1201','85'),

('184002','1201','79'),

('184004','1201','94');

select * from scoreinfo;

insert into teacherinfo

values

('100005','李慧強(qiáng)','男','1968-09-25','計(jì)算機(jī)學(xué)院','北京市海淀區(qū)'),

('100024','劉松','男','1976-02-17','計(jì)算機(jī)學(xué)院','北京市海淀區(qū)'),

('400021','陳霞飛','女','1975-12-07','通信學(xué)院','上海市黃浦區(qū)'),

('800004','柳泉明','男','1978-08-16','數(shù)學(xué)學(xué)院','廣州市越秀區(qū)'),

('120007','張莉','女','1980-03-21','外國(guó)語(yǔ)學(xué)院','成都市錦江區(qū)');

select * from teacherinfo;文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-754121.html