MySQL 實驗三 建立資料庫、管理表結構和表資料

一.實驗目的

• 1.掌握資料庫的建立；
• 2.掌握表結構的建立、修改、檢視、删除操作；

二.實驗環境

• win10
• MySQL Server8.0
• MySQL Workbench

三.實驗内容和要求

1．建立資料庫

• (1) 使用 workbench 或者 MySQL 指令行建立一個名字為 db_test_學号（每個學生的完整學号）的資料庫；

-- 建立資料庫
create database db_test_學号;
-- 使用資料庫
use db_test_學号;

           

• (2) 檢視上面資料庫的建立資訊，如果資料庫的字元集不是 gbk，把資料庫字元集改成 gbk，字元序使用預設方式即可；

-- 修改資料庫預設字元集
alter database db_test_學号 character set gbk;
           

• (3) 把 db_test_學号設定為目前資料庫，下面的表都在該資料庫下建立；

2．建立表結構

• (1) 使用 workbench 或者 MySQL 指令行建立下列各表，表結構如附錄員工醫

  療保險系統表 1-表 7 所示。

CREATE TABLE IF NOT EXISTS `bussiness`(
   `bno` char(10) primary key COMMENT '企業編号',
   `bname` CHAR(50) NOT NULL COMMENT '企業名稱',
   `btype` enum ('企業','事業','私有') COMMENT '企業類型',
   `baddress` char(20) COMMENT '企業位址',
   `btel` char(13) COMMENT '聯系電話'
);

CREATE TABLE IF NOT EXISTS `hospital`(
   `hno` char(5) primary key COMMENT '醫院編号',
   `hname` CHAR(40) NOT NULL COMMENT '醫院名稱',
   `haddress` char(60) COMMENT '醫院名稱'
);

CREATE TABLE IF NOT EXISTS `card`(
   `cno` char(15) primary key COMMENT '醫保卡号',
   `ctype` enum ('企業','事業','靈活就業') COMMENT '醫保類型',
   `haddress` decimal(7,2) not null COMMENT '醫保餘額'
);

CREATE TABLE IF NOT EXISTS `staff` (
    `sno` CHAR(5) PRIMARY KEY COMMENT '員工編号',
    `sname` CHAR(20) not null COMMENT '員工姓名',
	`ssex` enum ('男','女') COMMENT '員工性别',
    `sbirthday` date COMMENT '出生日期',
    `sadress` CHAR(20) COMMENT '員工住址',
    `stel` CHAR(15) unique COMMENT '聯系電話',
    `cno` CHAR(15) COMMENT '醫保卡号',
    `bno` CHAR(10) COMMENT '企業編号',
    FOREIGN KEY (cno) REFERENCES card(cno),
    FOREIGN KEY (bno) REFERENCES bussiness(bno)
);

CREATE TABLE IF NOT EXISTS `see`(
   `sno` char(5) COMMENT '員工編号',
   `hno` char(5) COMMENT '醫院編号',
   `sdate`date COMMENT '就診日期',
	PRIMARY KEY (`sno`,`hno`,`sdate`),
    FOREIGN KEY (hno) REFERENCES hospital(hno),
    FOREIGN KEY (sno) REFERENCES staff(sno) on delete cascade
);

CREATE TABLE IF NOT EXISTS `consume`(
   `cno` char(15) COMMENT '醫保卡号',
   `hno` char(5) COMMENT '醫院編号',
   `csdate` date not null COMMENT '消費日期',
	`mname` char(20) COMMENT '藥品名稱',
    `mnum` int not null COMMENT '藥品數量',
    `csmoney` decimal(7,2) COMMENT '消費金額',
    PRIMARY KEY (`cno`,`hno`,`csdate`),
    FOREIGN KEY (cno) REFERENCES card(cno) on delete cascade
);

CREATE TABLE IF NOT EXISTS `insurance`(
   `idate` date COMMENT '存款日期',
   `cno` char(15) COMMENT '醫保卡号',
   `imone` decimal(5,2) not null COMMENT '存款金額',
   `bno` char(10) COMMENT '企業編号',
	PRIMARY KEY (`idate`,`cno`),
    FOREIGN KEY (cno) REFERENCES card(cno),
    FOREIGN KEY (bno) REFERENCES bussiness(bno)
);

           

3．檢視表結構

• (1) 使用 workbench 或者 MySQL 指令行檢視員工醫療保險系統所有表的字段

  資訊和限制資訊。

4．修改表結構

• (1) 使用 workbench 或者 MySQL 指令行根據表“staff”重新生成一個新表

  “staff_sql”，表結構和表“staff”完全一緻。

• (2) 使用 workbench 或者 MySQL 指令行為“staff_sql”表添加“age INT”字段，

  利用 DESC 指令檢視“staff_sql”表的字段資訊。

  (3) 使用 workbench 或者 MySQL 指令行為“staff_sql”表添加“salary

  decimal(5,2)”、“salary_add decimal (3,1)”兩個字段，利用DESC指令檢視“staff_sql”

  MySQL 資料庫教學資料

  北京理工大學珠海學院張申勇 2

  表的字段資訊。

• (4) 使用 workbench 或者 MySQL 指令行向“staff_sql”表添加“sname”字段惟一

  性限制，從資料字典 information_schema.table_constraints 檢視“staff_sql”表的限制

  資訊。

• (5) 使用 workbench 或者 MySQL 指令行删除“staff_sql”表上“sname”字段惟一

  性限制，從資料字典 information_schema.table_constraints 檢視“staff_sql”表的限制

  資訊。

• (6) 使用 workbench 或者 MySQL 指令行從“staff_sql”表删除“age”字段，利用

  DESC 指令檢視“staff_sql”表的字段資訊。

• (7) 使用 workbench 或者 MySQL 指令行從“staff_sql”表删除“salary”、

  “salary_add”兩個字段，利用 DESC 指令檢視“staff_sql”表的字段資訊。

• (8) 使用 workbench 或者 MySQL 指令行将“staff_sql”表“sname”字段長度修改

  為 30，利用 DESC 指令檢視“staff_sql”表的字段資訊。

5．删除表結構

• (1) 使用 workbench 或者 MySQL 指令行删除員工表，看能否成功。從原理上

  解釋原因，同時記錄外鍵限制表删除順序的影響

-- 查詢表結構和限制
select * from information_schema.table_constraints
where table_name='staff';

-- 複制表
CREATE TABLE staff_sql LIKE staff;

-- 添加字段
alter table staff_sql add age int;

-- 利用DESC指令檢視staff_sql表的字段資訊
select * from information_schema.columns
where table_schema = 'db_test_學号'  #表所在資料庫
and table_name = 'staff_sql' ; #你要查的表

-- 添加字段
alter table staff_sql add salary decimal(5,2);
alter table staff_sql add salary_add decimal(3,1);

-- 4
-- 添加限制
ALTER TABLE staff_sql ADD UNIQUE(sname);
-- 查詢限制
select * from information_schema.table_constraints
where table_name='staff_sql';

-- 5
-- 删除限制
ALTER TABLE staff_sql drop index sname;
-- 查詢限制
select * from information_schema.table_constraints
where table_name='staff_sql';

-- 6
-- 删除字段
ALTER TABLE staff_sql DROP age;
-- 利用DESC指令檢視staff_sql表的字段資訊
select * from information_schema.columns
where table_schema = 'db_test_學号'  #表所在資料庫
and table_name = 'staff_sql' ; #你要查的表

-- 7
-- 删除字段
ALTER TABLE staff_sql DROP salary;
ALTER TABLE staff_sql DROP salary_add;
-- 利用DESC指令檢視staff_sql表的字段資訊
select * from information_schema.columns
where table_schema = 'db_test_學号'  #表所在資料庫
and table_name = 'staff_sql' ; #你要查的表

-- 8
-- 修改字段長度
alter table staff_sql modify column sname char(30);
-- 利用DESC指令檢視staff_sql表的字段資訊
select * from information_schema.columns
where table_schema = 'db_test_學号'  #表所在資料庫
and table_name = 'staff_sql' ; #你要查的表


-- 删除資料表
DROP TABLE staff;