MySQL基础练习

练习1

1.1创建数据库以及表
create database company；

创建表offices

CREATE TABLE offices(
officeCode  INT(10) PRIMARY KEY UNIQUE,
city VARCHAR(50) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) UNIQUE
)；

创建表employees

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),
FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
)；

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

SHOW TABLES
DESC offices
DESC employees

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

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

1.4将表employees的birth字段改名为employees_birth

ALTER TABLE employees CHANGE birth  employee_birth DATETIME;
DESC employees

1.5修改sex字段，数据类型为char（1），非空约束

alter table  employees modify sex char(1) not null;
desc employees

1.6删除字段note

ALTER TABLE employees DROP note;
DESC employees

1.7增加字段favoriate_activity，数据类型为varchar（100）

ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
DESC employees

1.8删除表offices

ALTER TABLE employees DROP FOREIGN KEY employees_ibfk_1;
DROP TABLE offices

1.9将表employees名字修改为employees_info

ALTER TABLE employees RENAME TO employees_info

练习2

2.1创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束
创建数据库

create database Market;

创建表

create table customers(
	c_num int(11) primary key auto_increment,
	c_name varchar(50),
	c_contact varchar(50),
	c_city varchar(50),
	c_birth datetime not null
);

2.2 将c_contact字段插入c_birth字段后面。

alter table customers modify c_contact varchar(50) after c_birth;

2.3将c_name字段数据类型改为VARCHAR(70)

alter table customers modify c_name varchar(70);

**
2.4将c_contact字段改名为c_phone。

alter table customers change c_contact c_phone varchar(50);

2.5 增加c_gender字段，数据类型为CHAR(1)

alter table customers add c_gender char(1);

2.6 将表名修改为customers_info。

alter table customers rename to customers_info;

2.7删除字段c_city。

alter table customers_info drop c_city;

2.8修改数据表的存储引擎为MyISAM。

alter table customers_info engine=MyISAM;