03 表操作

mysql多实例部署

通过开启多个不同的端口，将mysql部署多个进程

# 指定不同的datadir ，初始化
# 将配置文件的端口改成3307，3308，3309
# 使用mysqld --defaults-file=//// --user=mysql
# 和你二进制安装的数据库一样的操作，只是配置文件指定的端口，tmp目录，datadir不同而已

不要问我为什么只写这么多，没有步骤，因为我觉得没有哪个公司会这么搞！

存储引擎

存储引擎就是 如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

查看存储引擎

mysql> show engines;

存储引擎介绍

InnoDB 存储引擎

支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。
InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

MyISAM 存储引擎

不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。

NDB 存储引擎

2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。

Memory 存储引擎

正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。

Infobright 存储引擎

第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。

NTSE 存储引擎

网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。

BLACKHOLE

黑洞存储引擎，可以应用于主备复制中的分发主库。MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

使用存储引擎

# 方法一：建表时指定
mysql> create table t3 (id int,name varchar(16))engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t2;

# 方法二：配置文件中指定
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1

表介绍

创建表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

案例

# 创建库
create database db1 charset utf8mb4;

# 创建表
use db1
create table t1( id int, name varchar(50), sex enum('male','female'), age int(3) );

# 插入数据
insert into t1 values (1,"egon","male",18), (2,"baim0","male",18), (3,"jack","female",18);

# 查询
select * from t1;

数据类型

整数类型

浮点型

类型	解释
float(255,30)	精确到小数点后面6位，后面6位后不准
double(255,30)	双倍精确，精确到12位
decimal(66,31)	完全精准到31位

（d,m）d代表的前面的数字个数，m代表的小数位数

日期类型

类型	解释
YEAR	1901/2155
DATE	YYYY-MM-DD（1000-01-01/9999-12-31）
TIME	HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME	YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
TIMESTAMP	YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时

！！！注意：针对datetime或者timestamp如果是用作注册时间，那么指定not null default now()自动填充时间即可，如果是用作更新时间那么需要额外指定on update now()，该配置timestamp自带。

字符串类型

类型	解释	特点
char	字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）	定长，简单粗暴，浪费空间，存取速度快
varchar	变长，精准，节省空间，存取速度慢	变长，精准，节省空间，存取速度慢

枚举类型与集合类型

类型	解释
enum	单选
set	多选

约束条件

类型	解释
PRIMARY KEY	标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY	标识该字段为该表的外键
NOT NULL	标识该字段不能为空
UNIQUE KEY	标识该字段的值是唯一的
AUTO_INCREMENT	标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT	为该字段设置默认值
UNSIGNED	无符号
ZEROFILL	使用0填充

notnull与default

mysql> create table t5(id int not null);	#增加约束条件不可为空
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t5 values();
ERROR 1364 (HY000): Field 'id' doesn't have a default value	#插入报错
mysql> insert into t5 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> create table t6(id int default 1);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> alter table t6 modify id int not null default 1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

unique

# 设置唯一约束，只能存在一个这样的
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

# not null + unique = primary key
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

#联合唯一
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

primary key

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键

一个表中可以：

单列做主键
多列做主键（复合主键）

#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);
mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

# 多列主键
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

auto_increment

#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)

foreign key的三种对应关系

多对一

指定foreign key

# 一个课程对应多个学生
create table class(
id int primary key auto_increment,
name varchar(20),
room int
);

create table student(
id int primary key auto_increment,
name varchar(20),
age int,
gender enum("male","female"),
class_id int,
foreign key(class_id) references class(id)	#外键关联
on update cascade	# 级联更新
on delete cascade	# 级联删除
);

insert class(name,room) values
("python16期",403),
("Linux 12期",503),
("Linux 13期",603);

insert student(name,age,gender,class_id) values
("baim0",18,"male",1),
("tom",19,"male",1),
("alex",22,"male",1),
("jack",18,"female",1),
("lili",20,"female",2),
("sb",22,"male",3);

insert student(name,age,gender,class_id) values
("egon",18,"male",222);	#插入报错

insert student(name,age,gender,class_id) values
("egon",18,"male",2);

多对多

用第三张表去建立多对多的关系。

# 三张表：出版社、作者信息、书
# 创建作者表
create table author(
id int primary key auto_increment,
name varchar(20)
);

# 创建book表
create table book(
id int primary key auto_increment,
name varchar(20)
);

#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

# 插入四个作者
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

# 插入6本书
insert into book(name) values('九阳神功'),('九阴真经'),('九阴白骨爪'),('独孤九剑'),('降龙十八掌'),('葵花宝典');

# 作者如下
1 egon: 
      1 九阳神功
      2 九阴真经
      3 九阴白骨爪
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典


2 alex: 
      1 九阳神功
      6 葵花宝典

3 yuanhao:
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典

4 wpq:
      1 九阳神功


insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

一对一

foreign key + unique

# 一个用户只有一个博客
用户表：
id  name
1    egon
2    alex
3    wupeiqi


博客表   
fk+unique
id url name_id
1  xxxx   1
2  yyyy   3
3  zzz    2

create table user (
id int primary key auto_increment,
name varchar(10)
);

create table blog (
id int primary key auto_increment,
blog_id int unique, 
blog_name varchar(10),
foreign key(blog_id) references user(id)
on delete cascade
on update cascade
);

insert user(name) values
("baim0"),
("alex"),
("chenyang"),
("egon");

insert blog(blog_id,blog_name) values
("1","www.cnblog.com/baim0"),
("2","www.csdn.com/alex"),
("3","www.yuque.com/chenyang"),
("4","www.jianshu.com/egon");

# 删除一个id=2的用户，博客地址也消失
mysql> select * from blog;
+----+---------+------------------------+
| id | blog_id | blog_name              |
+----+---------+------------------------+
|  1 |       1 | www.cnblog.com/baim0   |
|  2 |       2 | www.csdn.com/alex      |
|  3 |       3 | www.yuque.com/chenyang |
|  4 |       4 | www.jianshu.com/egon   |
+----+---------+------------------------+
4 rows in set (0.00 sec)

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | baim0    |
|  2 | alex     |
|  3 | chenyang |
|  4 | egon     |
+----+----------+
4 rows in set (0.00 sec)

mysql> delete from user where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from blog;
+----+---------+------------------------+
| id | blog_id | blog_name              |
+----+---------+------------------------+
|  1 |       1 | www.cnblog.com/baim0   |
|  3 |       3 | www.yuque.com/chenyang |
|  4 |       4 | www.jianshu.com/egon   |
+----+---------+------------------------+
3 rows in set (0.00 sec)

修改表

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;# 这种也是复制表结构，方便一点

作业练习

# 班级表，直接创建
create table class(
cid int primary key auto_increment,
caption varchar(10)
);

# 老师表，直接创建
create table teacher(
tid int primary key auto_increment,
tname varchar(16)
);


# 学生表，sid唯一，gender（枚举类型enum），class_id(多个学生对应一个班)
create table student(
sid int primary key auto_increment,
sname varchar(16),
gender enum("男","女"),
class_id int,
foreign key(class_id) references class(cid)
on delete cascade
on update cascade
);



# 课程表，cid唯一，cname(字符串)，tearch_id(一个老师对应一节课)
create table course(
cid int primary key auto_increment,
cname varchar(16),
tearch_id int,
foreign key(tearch_id) references teacher(tid)
on delete cascade
on update cascade
);


# 成绩表，sid唯一，student_id(int),course_id(int),number(int)
create table score(
sid int primary key auto_increment,
student_id int,
foreign key(student_id) references student(sid),
corse_id int,
foreign key(corse_id) references course(cid),
number int
);