02-MySQL表操作

02-MySQL表操作

---

创建表结构并指定单字段主键

create table tb_emp2
(
    id int(11) PRIMARY KEY,
    name varchar(25),
    deptId int (11),
    salary float
);

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

创建表结构并指定多个字段主键

create table tb_emp3
(
    name varchar(25),
    deptId int (11),
    salary float,
    PRIMARY KEY(name,deptId)
);

mysql> desc tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(25) | NO   | PRI | NULL    |       |
| deptId | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

外键约束

1. 外键用来在两个表的数据之间建立链接，它可以是一列或者多列。
2. 外键对应的是参照完整性，一个表的外键可以为空，若不为空，则每一个外键值都必须等于另一个表中外键的某个值。
3. 外键主要作用是保证数据的完整性，一致性。

创建一个部门表tb_dept1

字段名称	数据类型	备注
id	int(11)	部门编号
name	varchar(22)	名称
location	varchar(50)	部门位置

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

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

创建一个tb_emp5表

1. 主表为tb_dept1,主键列为id
2. 从表为tb_emp4,外键为deptId

mysql> create table tb_emp4
    -> (
    -> 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.04 sec)

mysql> desc tb_emp4;
+--------+-------------+------+-----+---------+-------+
| 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.01 sec)

使用唯一性约束

第一种方法：定义完列之后直接指定唯一约束

create table tb_dept2
(
    id int(11) PRIMARY key,
    name varchar(22) UNIQUE,
    location varchar(50)
);

第二种方法：定义完所有列之后指定唯一约束：

create table tb_dept2
(
    id int(11) PRIMARY key,
    name varchar(22),
    location varchar(50),
    CONSTRAINT STH UNIQUE(name)
);

使用默认约束、自增

create table tb_emp3
(
    id int(11) PRIMARY KEY AUTO_INCREMENT,
    name varchar(25),
    deptId int (11) DEFAULT 1111,
    salary float
);

修改数据表

修改表名

alter table tb_dept_old RENAME tb_dept_new;

修改字段的数据类型

alter table tb_dept_new MODIFY name varchar(20);

修改字段名

alter table tb_dept_new CHANGE name name_new VARCHAR(11);

添加字段

alter table tb_dept_new add age int(3);

添加字段（完整性约束）

alter table tb_dept_new add column2 varchar(12) not null;

添加字段（指定位置）

alter table tb_dept_new add cloumn3 varchar(12) FIRST; -- 第一行
alter table tb_dept_new add cloumn4 int(11) AFTER cloumn3; -- 添加到column3后面

删除字段

alter table tb_dept_new drop cloumn3;

修改字段排列位置

alter table tb_dept_new MODIFY cloumn4 varchar (12) FIRST  -- 放到第一个
alter table tb_dept_new MODIFY cloumn4 varchar (11) AFTER age -- 放到指定位置上

查看数据库存储引擎

 show create table tb_emp3;

修改数据库存储殷勤

alter table tb_dept_new ENGINE=myISAM

删除表的外键约束

alter table tb_emp4 drop FOREIGN KEY fk_emp_dept1

删除数据表

drop table if EXISTS tb_emp4