Mysql数据库练习

无危险

已于 2023-03-21 13:10:38 修改

阅读量526

点赞数

分类专栏： Mysql数据库文章标签：数据库 mysql sql Powered by 金山文档

于 2023-03-14 11:26:54 首次发布

本文链接：https://blog.csdn.net/qq_64005599/article/details/129520957

版权

Mysql数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

实验步骤：

练习1：

创建数据库test02_library（test修改为自己的姓名全拼）

CREATE DATABASE wuweixian02_company CHARACTER SET utf8mb4

USE wuweixian02_company;

2、创建表 books，表结构如下：

CREATE TABLE offices(

officCode INT PRIMARY KEY UNIQUE,

city VARCHAR(50) NOT NULL,

address VARCHAR(50) NOT NULL,

country VARCHAR(50) NOT NULL,

postalCode VARCHAR(15) UNIQUE

);

3.使用ALTER语句给books按如下要求增加相应的约束

（1）给id增加主键约束

（2）给id字段增加自增约束

（3）针对于非id的其他所有字段增加非空约束

ALTER TABLE books MODIFY name VARCHAR(50) NOT NULL;

ALTER TABLE books MODIFY authors VARCHAR(100) NOT NULL;

ALTER TABLE books MODIFY price FLOAT NOT NULL;

ALTER TABLE books MODIFY pubdate YEAR NOT NULL;

ALTER TABLE books MODIFY note VARCHAR(100) NOT NULL;

ALTER TABLE books MODIFY num INT NOT NULL;

练习2：

1.创建数据库test02_company（test修改为自己的姓名全拼）

2.按照下表给出的表结构在test02_company数据库中创建两个数据表offices和employees（test修改为自己的姓名全拼）

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

ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode ;

将表employees的birth字段改名为employee_birth

ALTER TABLE employees RENAME COLUMN birth TO employee_birth;

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

ALTER TABLE employees MODIFY sex char(1);

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

删除字段note

ALTER TABLE employees DROP note;

增加字段名favoriate_activity，数据类型为VARCHAR(100)

ALTER TABLE employees ADD favoriate_activity VARCHAR(100) FIRST;

将表employees名称修改为employees_info

SHOW TABLES;

ALTER TABLE employees RENAME TO employees_info;

源码：

CREATE DATABASE wuweixian02_library CHARACTER SET utf8mb4
USE wuwexian02_library
CREATE TABLE books(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR( 50)NOt NULL UNIQUE,
authors VARCHAR(100)NOT NULL,
price FLOAT NOT NULL,
pubdate YEAR NOT NULL,
note VARCHAR(100) NOT NULL,
num INT NOT NULL
);
DESC books;
ALTER TABLE books ADD PRIMARY KEY(id);

CREATE DATABASE wuweixian02_company CHARACTER SET utf8mb4
USE wuweixian02_company;
CREATE TABLE offices(
officCode INT PRIMARY KEY UNIQUE,
city VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE
);
ALTER TABLE offices ADD UNIQUE(postalCode);
ALTER TABLE offices ADD UNIQUE(officCode);
ALTER TABLE offices DROP index postalCode
DESC offices;
CREATE TABLE employees (
employeeNumber INT(11) PRIMARY KEY UNIQUE,
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)
);
DROP TABLE employees;
ALTER TABLE employees MODIFY lastName VARCHAR(50) NOT NULL;
DESC employees;
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode ;
ALTER TABLE employees RENAME COLUMN birth TO employee_birth;
ALTER TABLE employees MODIFY sex char(1);
ALTER TABLE employees MODIFY  sex char(1) NOT NULL;
ALTER TABLE employees DROP note;
ALTER TABLE employees ADD favoriate_activity VARCHAR(100) FIRST;
SHOW TABLEs;
ALTER TABLE employees RENAME TO employees_info;