创建一个数据库和两张表

数据库：company

数据表：offices和employees

  1. create schema company;
  3. use company;
  5. create table offices (
  6. officeCode int(10) primary key not null unique,
  7. city varchar(50) not null,
  8. address varchar(50),
  9. county varchar(50) not null,
  10. pastalCode varchar(15) unique
  11. );
  13. create table employees (
  14. employeeNumber int(11) primary key not null unique auto_increment,
  15. lastName varchar(50) not null,
  16. fisrtName varchar(50) not null,
  17. mobile varchar(25) unique,
  18. officeCode int(10) not null,
  19. jobTitle varchar(50) not null,
  20. birth datetime not null,
  21. note varchar(255),
  22. sex varchar(5),
  23. constraint fk_off_emp foreign key(officeCode) references offices(officeCode)
  24. );
  26. alter table employees modify mobile varchar(25) after officeCode;
  28. alter table employees change birth employee_birth datetime;
  30. alter table employees change sex sex varchar(1) not null;
  31. alter table employees modify sex char(1) not null;
  33. alter table employees drop note;
  35. alter table employees add favoriate_activity varchar(100);
  37. drop table offices;
  38. alter table employees drop foreign key fk_off_emp;
  39. drop table offices;
  41. alter table employees engine=myisam;
  43. show create table employees\G;
  45. alter table employees rename employees_info;