mysql数据库基本操作

mysql> create database company; //创建数据库company
Query OK, 1 row affected (0.01 sec)
mysql> use company; //使用数据库company
Database changed
mysql> create table offices(officescode int(10) not null unique,
/*创建数据表offices,设置officescode列的数据类型为int，宽度为10，该列非空not null，且数值唯一unique,*/
    -> city varchar(50) not null,
//设置city列的数据类型为varchar，长度为50，该列非空not null
    -> address varchar(50) not null,
    -> country varchar(50) not null,
    -> postalcode varchar(15) not null,
    -> primary key (officescode));//设置officescode列为表offices的主键，数据具有唯一性。
Query OK, 0 rows affected (0.06 sec)
mysql> desc offices;//查看offices表结构
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| officescode | int(10)     | NO   | PRI | NULL    |       |
| city        | varchar(50) | NO   |     | NULL    |       |
| address     | varchar(50) | NO   |     | NULL    |       |
| country     | varchar(50) | NO   |     | NULL    |       |
| postalcode  | varchar(15) | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)




mysql> show tables;//查看company数据库中的表
+-------------------+
| Tables_in_company |
+-------------------+
| offices           |
+-------------------+
1 row in set (0.00 sec)


mysql> create table employees(
    -> employeenumber int(11) not null primary key
    -> auto_increment,//设置employeenumber列为自增长列
    -> lastname varchar(50) not null,
    -> firstname varchar(50) not null,
    -> mobile varchar(25) not null,
    -> officecode int(10) not null,
    -> jobtitle varchar(50) not null,
    -> birth datetime,//设置birth列的数据类型为datetime。
    -> note varchar(255),
    -> sex varchar(5),
    -> constraint office_fk foreign key(officecode)
    -> references offices(officescode));
//设置officecode列为外键约束与表offices的officescode列链接，并设置该外键名称为office_fk。
Query OK, 0 rows affected (0.07 sec)
mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeenumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname       | varchar(50)  | NO   |     | NULL    |                |
| firstname      | varchar(50)  | NO   |     | NULL    |                |
| mobile         | varchar(25)  | NO   |     | NULL    |                |
| officecode     | int(10)      | NO   | MUL | NULL    |                |
| jobtitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)


mysql> alter table employees modify mobile varchar(25) after officecode;
//将表employees中的列mobile的顺序移到列officecode顺序之后。
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employees
    -> ;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeenumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname       | varchar(50)  | NO   |     | NULL    |                |
| firstname      | varchar(50)  | NO   |     | NULL    |                |
| officecode     | int(10)      | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  |     | NULL    |                |
| jobtitle       | varchar(50)  | NO   |     | NULL    |                |
| birth          | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)


mysql> alter table employees change birth employee_birth datetime;
//将表employees中的列birth改名为employee_birth。
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeenumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname       | varchar(50)  | NO   |     | NULL    |                |
| firstname      | varchar(50)  | NO   |     | NULL    |                |
| officecode     | int(10)      | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  |     | NULL    |                |
| jobtitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(5)   | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


mysql> alter table employees modify sex varchar(4) not null;
//将employees表中的列sex的数据类型varchar的长度5改为4，并加以非空约束。
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employeenumber | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname       | varchar(50)  | NO   |     | NULL    |                |
| firstname      | varchar(50)  | NO   |     | NULL    |                |
| officecode     | int(10)      | NO   | MUL | NULL    |                |
| mobile         | varchar(25)  | YES  |     | NULL    |                |
| jobtitle       | varchar(50)  | NO   |     | NULL    |                |
| employee_birth | datetime     | YES  |     | NULL    |                |
| note           | varchar(255) | YES  |     | NULL    |                |
| sex            | varchar(4)   | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.02 sec)


mysql> alter table employees drop column  note ;
//将employees表中的note列删除。
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employees;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| employeenumber | int(11)     | NO   | PRI | NULL    | auto_increment |
| lastname       | varchar(50) | NO   |     | NULL    |                |
| firstname      | varchar(50) | NO   |     | NULL    |                |
| officecode     | int(10)     | NO   | MUL | NULL    |                |
| mobile         | varchar(25) | YES  |     | NULL    |                |
| jobtitle       | varchar(50) | NO   |     | NULL    |                |
| employee_birth | datetime    | YES  |     | NULL    |                |
| sex            | varchar(4)  | NO   |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)


mysql> alter table employees add favoriate_activity varchar(100);
//在表employees中增加列favoriate_activity，数据类型为varchar,长度为100.
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employees;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| employeenumber     | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname           | varchar(50)  | NO   |     | NULL    |                |
| firstname          | varchar(50)  | NO   |     | NULL    |                |
| officecode         | int(10)      | NO   | MUL | NULL    |                |
| mobile             | varchar(25)  | YES  |     | NULL    |                |
| jobtitle           | varchar(50)  | NO   |     | NULL    |                |
| employee_birth     | datetime     | YES  |     | NULL    |                |
| sex                | varchar(4)   | NO   |     | NULL    |                |
| favoriate_activity | varchar(100) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)


mysql> alter table employees drop foreign key office_fk;
//删除表empolyees中的外键约束office_fk。
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc employees;
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| employeenumber     | int(11)      | NO   | PRI | NULL    | auto_increment |
| lastname           | varchar(50)  | NO   |     | NULL    |                |
| firstname          | varchar(50)  | NO   |     | NULL    |                |
| officecode         | int(10)      | NO   | MUL | NULL    |                |
| mobile             | varchar(25)  | YES  |     | NULL    |                |
| jobtitle           | varchar(50)  | NO   |     | NULL    |                |
| employee_birth     | datetime     | YES  |     | NULL    |                |
| sex                | varchar(4)   | NO   |     | NULL    |                |
| favoriate_activity | varchar(100) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)




mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| offices           |
+-------------------+
2 rows in set (0.01 sec)


mysql> drop table offices;
//删除表offices
Query OK, 0 rows affected (0.01 sec)


mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)


mysql> rename table employees to employees_info;
//将表employees的名称改为employees_info。
Query OK, 0 rows affected (0.05 sec)


mysql> show tables
    -> ;
+-------------------+
| Tables_in_company |
+-------------------+
| employees_info    |
+-------------------+
1 row in set (0.00 sec)