数据库作业（一）

作业一

--创建数据库

create DATABASE test1;

use test1;

--创建数据表

CREATE TABLE offices(

officeCode INT(10) PRIMARY KEY,

city VARCHAR(50) NOT NULL,

address VARCHAR(50),

country VARCHAR(50) not NULL,

postalCode VARCHAR(15) UNIQUE

);

CREATE TABLE employees(

employeeNumber INT(11) PRIMARY KEY auto_increment,

lastName VARCHAR(50) NOT NULL,

firstName VARCHAR(50) NOT NULL,

mobile VARCHAR(25) UNIQUE,

officeCode INT(10) NOT NULL,

jobTitle VARCHAR(50) NOT NULL,

birth datetime NOT NULL,

note VARCHAR(255),

sex VARCHAR(5),

CONSTRAINT fk_offices FOREIGN KEY(officeCode) REFERENCES offices(officeCode)

);

drop TABLE employees_info;

--查看该库下几张表以及查看两张表结构

SHOW TABLES;

DESCRIBE employees;

DESCRIBE offices;

--将表employees的mobile字段修改到officeCode字段后面

alter table employees drop mobile;

alter table employees add mobile varchar(25) after officeCode;

--将表employees中的birth字段改名为employee_birth

alter table employees CHANGE birth employee_birth datetime;

--修改sex字段，数据类型为char(1),非空约束

ALTER TABLE employees modify sex char(1) NOT NULL;

--删除字段note

ALTER TABLE employees drop note;

--增加字段名favourite_activity,数据类型为varchar(100)

ALTER TABLE employees add favourite_activity VARCHAR(100);

--删除表offices

SHOW TABLES;

----先要删除外键约束，否则删除不成功

ALTER TABLE employees_info DROP FOREIGN KEY fk_offices;

DROP TABLE offices;

--将表employees名称改为employees_info

ALTER TABLE employees RENAME to employees_info;

作业二

--创建数据库Market

CREATE DATABASE Market;

USE Market;

--创建数据表

CREATE TABLE customers(

c_num INT(11) PRIMARY KEY auto_increment,

c_name VARCHAR(50),

c_contant VARCHAR(50),

c_city VARCHAR(50),

c_birth datetime NOT NULL

);

--将c_contant字段插入c_birth字段后面

ALTER TABLE customers DROP c_contant;

ALTER TABLE customers ADD c_contant VARCHAR(50) after c_birth;

--将c_name字段数据类型改为varchar(70)

ALTER TABLE customers MODIFY c_name VARCHAR(70);

--将c_contant字段改名为c_phone

ALTER TABLE customers CHANGE c_contant c_phone VARCHAR(50);

--增加c_gender字段，数据类型为char(1)

ALTER TABLE customers add c_gender char(1);

--将表名修改为customers_info

ALTER TABLE customers RENAME customers_info;

--删除字段c_city

ALTER TABLE customers_info DROP c_city;

--修改数据库表的存储引擎为MYISAM

ALTER TABLE customers_info ENGINE = MyISAm;

ALTER TABLE customers_info ENGINE = INNODB;

--在Market中创建数据表orders

CREATE TABLE orders(

o_name INT(11),

o_date DATE,

c_id INT(11)

);

DROP TABLE orders;

--创建数据表orders，在o_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联customers表中的c_num

alter table orders modify o_name int auto_increment primary key;

alter table orders add constraint fk_orders foreign key(c_id) references customers_info(c_num);

--删除orders表的外键约束，然后删除表customers

ALTER TABLE orders DROP foreign key fk_orders;

DROP TABLE customers_info;

作业三

--创建数据库MySchool

CREATE DATABASE MySchool;

USE MySchool;

--创建数据表

--年级表

CREATE TABLE Grade(

GradeId INT PRIMARY KEY,

GradeName VARCHAR(50) NOT NULL

);

CREATE TABLE Student(

StudentNo VARCHAR(50) PRIMARY KEY,

LoginPwd VARCHAR(50),

StudentName varchar(50),

Sex char(2),

GradeId INT,

Phone varchar(255),

Address VARCHAR(255),

BornDate DATETIME,

Email varchar(50),

CONSTRAINT fk_Student FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)

);

CREATE table Subjects(

SubjectId INT PRIMARY KEY,

SubjectName VARCHAR(20),

ClassHour INT,

GradeId INT,

CONSTRAINT fk_subjects FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)

);

CREATE TABLE Result(

Id INT,

StudentNo VARCHAR(50),

SubjectId INT,

StudentResult INT,

ExamDate datetime

);

--result表添加外键

ALTER TABLE Result ADD CONSTRAINT fk_result FOREIGN KEY(StudentNo) REFERENCES Student(StudentNo);

ALTER TABLE Result ADD CONSTRAINT fk_result1 FOREIGN KEY(SubjectId) REFERENCES Subjects(SubjectId);