mysql数据库表的基本操作

1.外键关联：

mysql> create table tb_dept
    -> (id int(11) primary key, name varchar(22) not null, location varchar(50));
Query OK, 0 rows affected (0.11 sec)


mysql> create table tb_emp5
    -> (id int(11) primary key, name varchar(25), deptId int(11), salary float, constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id));
Query OK, 0 rows affected (0.01 sec)


mysql> describe tb_emp5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

以上的例子，再表tb_emp5上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，其依赖于表tb_dept1的主键id。

2.定义唯一性约束

两种方法;

第一：

mysql> create table tb_dept2
    -> (id int(11) primary key, name varchar(22) unique, location varchar(50));
Query OK, 0 rows affected (0.02 sec)

第二：

mysql> create table tb_dept3 (id int(11) primary key, name varchar(22), location varchar(50), constraint sth unique(name));
Query OK, 0 rows affected (0.01 sec)


mysql> describe tb_dept3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.设置表的属性值自动增加

mysql> create table tb_emp8 (id int(11) primary key auto_increment, name varchar(25) not null,  deptId int(11), salary float);
Query OK, 0 rows affected (0.01 sec)


mysql> insert into tb_emp8(name, salary)
    -> values('Lucy', 1000), ('Lura', 1200), ('Kevin', 1500);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from tb_emp8;
+----+-------+--------+--------+
| id | name  | deptId | salary |
+----+-------+--------+--------+
|  1 | Lucy  |   NULL |   1000 |
|  2 | Lura  |   NULL |   1200 |
|  3 | Kevin |   NULL |   1500 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)

从上面的例子看出，增加三条记录，并没有输入id的值，但是系统自动添加该值。

4.查看表详细结构语句show create table

mysql> show create table tb_emp8;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_emp8 | CREATE TABLE `tb_emp8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

加上\G后，可使显示的結果更加直观

mysql> show create table tb_emp8 \G;
*************************** 1. row ***************************
       Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


ERROR: 
No query specified

5.修改表名

mysql> alter table tb_dept3 rename tb_deptment3;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept           |
| tb_dept2          |
| tb_deptment3      |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp8           |
| tb_empl           |
+-------------------+
9 rows in set (0.00 sec)

6.修改字段数据类型

mysql> desc tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_dept2 modify name varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

7.修改字段名

mysql> desc tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_dept2 change location loc varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  | UNI | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)