＜MySQL入门到精通＞阅读笔记之数据表的基本操作

数据库存储引擎

InnoDB存储引擎

事务性引擎 支持事务安全表 ACID 支持行锁和外键 MYSQL5.5.5之后 INNODB 作为默认存储引擎

MylSAM存储引擎

MylSAM拥有较高的插入、查询速度，但不支持事务。MySQL5.5.5之前的版本中，MylSAM是默认存储引擎

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

存储引擎的选择

如果要提供提交、回滚和崩溃恢复能力的事务安全ACID兼容）能力，并要求实现并发控制，InnoDB是个很好的选择。如果数据表主要用来插入和查询记录，则MylSAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的
中间结果。如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引擎支持高并发的插入操作 但是本身并不是事务安全的 Archive非常适合存储归档数据 如记录日志信息可以使用Archive存储引擎

数据库的创建和删除

CREATE DATABASE zoo / / 创建数据库

SHOW DATABASES / / 查询数据库 

USE zoo / / 使用数据库 

DROP DATABASE zoo / / 删除数据库 

SHOW ENGINES  // 查看默认存储引擎

表的基本操作

创建表

CREATE TABLE tb_empl(
     id INT(11),
     `name` VARCHAR(25),
     deptId INT(11),
     salary FLOAT
);

主键约束

主键要求主键列的数据唯一 并且不允许为空 主键能够唯一标识表中的一条记录 可以结合外键来定义不同数据表之间的关系 可以加快数据库的查询速度

单字段主键

// 方式一

CREATE TABLE tb_emp2(
     id INT(11) PRIMARY KEY,
     `name` VARCHAR(25),
     deptId INT(11),
     salarty FLOAT
);

// 方式二

CREATE TABLE tb_emp3(
     id INT(11),
     `name` VARCHAR(25),
     deptId INT(11),
     salarty FLOAT,
     PRIMARY KEY (id)
 );

多字段主键

 CREATE TABLE tb_emp4(
      id INT(11),
      `name` VARCHAR(25),
      deptId INT(11),
      salary FLOAT,
      PRIMARY KEY (id,`name`)
  )

外键约束

外键用来在两个表之间建立链接 可以是一列或多列 一个表可以有一个或多个外键 它可以不是本表的主键 但对应另外一个表的主键 且关联字段的数据类型必须匹配

CREATE TABLE tb_dept1(
     id INT(11) PRIMARY KEY,
    `name` VARCHAR(22) NOT NULL,
    location VARCHAR(50)
);


CREATE TABLE tb_emp5(
     id INT(11) PRIMARY KEY,
     `name` VARCHAR(25),
     deptId INT(11),
     salart FLOAT,
     CONSTRAINT fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

非空约束

指定字段不能为空 对于使用非空约束的字段 如果添加数据没有指定值 会报错

CREATE TABLE tb_emp6(
      id INT(11) PRIMARY KEY,
      `name` VARCHAR(25) NOT NULL,
      deptId INT(11),
      salary FLOAT
)

唯一性约束

允许为空 但只能出现一个空值 唯一约束可以确保一列或几列不出现重复值

// 方式一
CREATE TABLE tb_dept2(
  id INT(11) PRIMARY KEY,
  `name` VARCHAR(25) UNIQUE,
  location VARCHAR(50)
);

// 方式二

CREATE TABLE tb_dept3(
  id INT(11) PRIMARY KEY,
  `name` VARCHAR(25) ,
  location VARCHAR(50)
  constraint sth UNIQUE(`name`)
);

UNIQUE和PRIMART KEY的区别： 一个表可以有多个UNIQUE 但只能有一个PRIMART KEY 声明PRIMART KEY的列不允许为空 UNIQUE 的列可以允许NULL值的存在

默认约束

指某列得默认值 如男性同学较多 性别就可以默认为男

CREATE TABLE tb_emp7(
     id INT(11) PRIMARY KEY ,
     `name` VARCHAR(25) NOT NULL,
     deptId INT(11) DEFAULT 111,
     salary FLOAT
)

设置表得属性值自动增加

每次插入新记录时 系统自动生成字段的主键值 可以通过AUTO_INCREMENT约束 在MYSQL中每新增一条记录字段值 +1 一个表只能有一个字段使用AUTO_INCREMENT约束 且该字段必须为主键

CREATE TABLE tb_emp8(
      id INT(11) PRIMARY KEY AUTO_INCREMENT,
      `naem` VARCHAR(25) NOT NULL,
      deptId INT(11) DEFAULT 111,
      salarty FLOAT
)

查询数据表结构

DESC tb_emp8

NULL 表示该列是否可以存储NULL值
KEY 表示该列是否编制索引 PRI 表示该列是主键的一部分 UNI表示该列是UNIQUE索引的一部分 MUL表示在列中某个给定值允许出现多少次
DEFUALT 表示该列是否有默认值
EXTRA 表示可以获取给定列得附加信息 如 AUTO_INCREMENT等

修改数据表

修改表名

ALTER TABLE <表名 >RENAME <新表名>
ALTER TABLE tb_dept2 RENAME tb_dept9

修改字段的数据类型

ALTER TABLE <表名> MODIFY <需要修改的字段> <字段类型>
ALTER TABLE tb_emp8 MODIFY salarty VARCHAR(33)

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名>  <新数据类型>
ALTER TABLE tb_emp8 CHANGE salarty sal  VARCHAR(255)

添加字段

ALTER TABLE <表名> ADD <字段名> <数据类型>
ALTER TABLE tb_emp8 ADD demo VARCHAR(255)

添加约束条件

ALTER TABLE <表名> ADD <字段名> <数据类型> <添加约束条件>
ALTER TABLE tb_emp8 ADD test VARCHAR(255) NOT NULL

在表的第一列添加一个字段

 ALTER TABLE <表名> ADD <字段名> <数据类型> FIRST 
 ALTER TABLE tb_emp8 ADD hehe INT(1) FIRST 

在指定的列后面添加一个字段

ALTER TABLE <表名> <字段名> <数据类型>  AFTER <字段名>
ALTER TABLE tb_emp8  ADD zeze INT(1) AFTER hehe

删除字段

ALTER TABLE <表名> DROP <字段名> 
ALTER TABLE tb_emp8 DROP hehe

修改字段的排列位置

ALTER TABLE <表名> MODIFY <字段名> <数据类型>  AFTER | FIRST <字段名>
ALTER TABLE tb_emp8 MODIFY zeze INT(1) AFTER | FIRST id 

更改表的存储引擎

查看表信息

SHOW CREATE TABLE <表名>
SHOW CREATE TABLE tb_emp8

修改表的存储引擎

ALTER TABLE <表名> ENGINE = <存储引擎>
ALTER TABLE tb_emp8 ENGINE = MYISAM

删除数据表

删除没有被关联的表

DROP TABLE IF EXISTS <表名>
DROP TABLE IF EXISTS tb_emp2

删除被其它表关联的主表

数据表存在主外关联的情况下 直接删除父表 会显示失败 可以先删除与它关联的子表 再删除父表 或者解除键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键名>
ALTER TABLE tb_dept222 DROP FOREIGN KEY fk