练习1
create database if NOT EXISTS test01_office CHARACTER SET 'utf8';
USE test01_office;
CREATE TABLE if NOT EXISTS dept01(
id int(7),
NAME VARCHAR(25)
);
DESC dept01;
create table dept02
AS
SELECT *
FROM atguigudb.departments;
CREATE TABLE IF NOT EXISTS emp01(
id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);
DESC emp01;
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);
DESC emp01;
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.employees;
SELECT *
FROM emp02;
show TABLES;
DROP TABLE IF EXISTS emp01;
show TABLES;
RENAME TABLE emp02 TO emp01;
ALTER TABLE dept02 ADD test_column VARCHAR(10);
DESC dept02;
DESC emp01;
ALTER TABLE emp01 ADD test_column VARCHAR(10);
DESC emp01;
ALTER TABLE emp01
DROP department_id;
DESC emp01;
练习2
CREATE database if not exists test02_market character set 'utf8';
USE test02_market;
CREATE TABLE IF NOT EXISTS customers(
c_num INT,
c_name varchar(50),
c_contact VARCHAR(50),
c_city varchar(50),
c_birth DATE
);
DESC customers;
ALTER TABLE customers
MODIFY c_contact varchar(50) AFTER c_birth;
DESC customers;
ALTER TABLE customers
MODIFY c_name VARCHAR(70);
DESC customers;
ALTER TABLE customers
CHANGE c_contact c_phone varchar(50);
DESC customers;
ALTER TABLE customers
ADD c_gender char(1) AFTER c_name;
RENAME TABLE customers
TO customers_info;
DESC customers_info;
ALTER TABLE customers_info
DROP COLUMN c_city;
DESC customers_info;
练习3
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';
USE test03_company;
CREATE TABLE IF NOT EXISTS offices(
officeCode int,
city varchar(30),
address varchar(50),
country varchar(50),
postalCode varchar(25)
);
DESC offices
CREATE TABLE IF NOT EXISTS employees(
empNum int,
lastName varchar(50),
firstName varchar(50),
mobile varchar(25),
`code` int,
jobTitle varchar(50),
birth DATE,
note varchar(255),
sex varchar(5)
);
DESC employees;
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER `code`;
ALTER TABLE employees
CHANGE birth birthday DATE;
ALTER TABLE employees
MODIFY sex char(1);
DESC employees;
ALTER TABLE employees
DROP note;
ALTER TABLE employees
ADD favoriate_activity varchar(100);
RENAME TABLE employees
TO employees_info;
DESC employees_info;