Day02 数据库MySql--存储引擎与表的操作

一、存储引擎

　　存储数据的方式。

　　一张表：①数据；②表的结构；③索引（查询的时候使用的一个目录结构）

1、Innodb存储引擎——mysql5.6之后默认的存储引擎

　　① 数据和索引存储在一起（2个文件）

　　数据索引、表结构

　　② 数据持久化

　　③ 支持事务：未来保证数据的完整性，将多个操作变成原子性操作。

　　④ 支持行级锁：修改的行少的时候使用

　　⑤ 支持表级锁：批量修改多行的时候使用

　　⑥ 支持外键：约束两张表中的关联字段不能随意的添加\删除

2、Myisam存储引擎——mysql5.5之前的默认存储引擎

　　① 数据和索引不存储在一起（3个文件）

　　数据、索引、表结构

　　② 只支持表级锁

3、Memory存储引擎

　　① 数据存储在内存中，也就是说数据断电消失（1个文件）

　　表结构

4、面试问题

（1）你了解mysql的存储引擎吗？

（2）你的项目中用来什么引擎，为什么？

　　用的Innodb存储引擎，原因有：

　　① 多个用户操作的过程中对同一张表的数据同时修改；

　　② Innodb支持行级锁，所以我们使用了这个存储引擎；

　　③ 为了适应程序未来的扩展性，扩展新功能的时候可能会用到。。。涉及到要维护数据的完整性；　　

　　④ 项目中有一两张xx xx表，之间的外键关系是什么，一张表的修改或者删除比较频繁，怕出错所以做了外键约束。

二、存储引擎测试

1、查看引擎

　　mysql> show variables like '%engine%';

2、在建表时指定引擎

　　mysql> create table ai(id bigint(12), name varchar(200)) ENGINE=InnoDB;

【tips】查看所建的表的详细信息：mysql> show create table engin1;

（1）创建的默认表（InnoDB存储引擎）

（2）创建Myisam存储引擎的表

三、表和数据的基础操作

1、创建表

create table 表名(
id int,
name char(18),
字段名3 类型[(宽度) 约束条件]
);
# 放在中括号里的内容可以不写

2、写入数据的方式（3种）

insert into 表 values (值1,值2,值3);
    # 这张表有多少的字段,就需要按照字段的顺序写入多少个值
insert into 表 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
    # 一次性写入多条数据
insert into 表 (字段1,字段3 ) values (值1,值3);
    # 指定字段名写入,可以任意的选择表中你需要写入的字段进行

3、查看表中的数据

select * from 表

4、查看表结构

desc 表名;
    # 能够查看到有多少个字段\类型\长度,看不到表编码,引擎,具体的约束信息只能看到一部分
show create table 表名;
    # 能查看字段\类型\长度\编码\引擎\约束

5、删除表

drop table 表名

四、MySQL中的数据类型

1、数字

# int 不约束长度,最多表示10位数
float(m,n)
    m 一共多少位,
    n 小数部分多少位

create table t1(
    id int,               # 默认是有符号的
    age tinyint unsigned  # 如果需要定义无符号的使用unsigned
);

create table t2(
  f1 float(5,2),   # 保留2位小数 并四舍五入
  f2 float,
  f3 double(5,2),
  f4 double
);

insert into t2(f2,f4) values(5.1783682169875975,5.1783682169875975179);

create table t3(
  f1 float,   # 保留2位小数 并四舍五入
  d1 double,
  d2 decimal(30,20),
  d3 decimal
);
insert into t3 values(5.1783682169875975179,5.1783682169875975179,
                      5.1783682169875975179,5.1783682169875975179);

2、字符串

　　主要是char和varchar两种。

（1）char和varchar的介绍和区别

① char(18)    最多只能表示255个字符
    定长存储,浪费空间,节省时间
    'alex'   'alex                 '
② varchar(18) 最多能表示65535个字符
    变长存储,节省空间,存取速度慢
    'alex'   'alex4'

适合使用char
    身份证号
    手机号码
    qq号
    username 12-18
    password 32
    银行卡号
适合使用varchar
    评论
    朋友圈
    微博

（2）操作格式

create table t6(c1 char(1),v1 varchar(1),c2 char(8),v2 varchar(8));
create table t6(c1 char,v1 varchar(1),c2 char(8),v2 varchar(8));

3、时间

（1）常用的时间

date  20190620
time  121953
datetime 20190620121900

（2）格式

mysql>create table t4(
    dt datetime,
    y year,
    d date,
    t time,
    ts timestamp
);

# 让datetime也可以向timestamp那样，自动实现插入当前时间
mysql> create table t5(
    -> id int,
    -> dt datetime NOT NULL                        # 不能为空
                  DEFAULT CURRENT_TIMESTAMP        # 默认是当前时间
                  ON UPDATE CURRENT_TIMESTAMP);

4、enum/set

　　enum　　单选行为

　　set　　多选行为

# enum
create table t8(
    id int,
    name char(18),
    gender enum('male','female')
);

# set
create table t9(
    id int,
    name char(18),
    hobby set('抽烟','喝酒','烫头','洗脚')
);

五、完整性约束

1、无符号的 int unsigned

# 无符号的
create table t10(
    id int unsigned
);

2、不能为空 not null

（1）严格模式设置：在my.ini里输入下面的内容（永久的话）

# not null的严格设置：(要是不为空的情况下不输入有效，则需要开启）
#（1）直接在myasql中生效（重启失效）
    mysql> sqi_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
#（2）配置文件添加（永久有效）
    sqi_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

（2）操作格式

create table t11(
    id int unsigned not null,
    name char(18) not null
);

3、默认值

# 设置默认是，假如你不输入，那么会自动输入默认值
create table t12(
    id int unsigned not null,
    name char(18) not null,
    sex enum('male','female') not null default 'male'
);

4、不能重复 unique

# 不能重复 (值不能重复，但是null可以)
create table t13(
    id1 int unique,
    id2 int
);

联合唯一：IP+端口

create table t14(
    id int,
    server_name char(12),
    ip char(15),
    port char(5),
    unique(ip,port)
);

5、自增

　　只对数字有效，自带非空约束；

　　至少是unique的约束之后才能使用。

# 自增
create table t16(
    id int primary key auto_increment, # id会自动增加
    name char(12)
);

6、主键

（1）第一个被定义为非空+唯一时那一列会成为这张表的primary key

　　可以指定主键（primary key）

（2）联合主键

# 联合主键
create table t15(
    id int,
    server_name char(12),
    ip char(15),
    port char(5),
    primary key(ip,port)
);

7、外键

# 外键
# 将学生表和班级表关联在一起(先建班级表，后建学生表)
# 班级表
create table my_class(
    cid int primary key auto_increment,
    cname char(12) not null,
    startd date
);
# 学生表
create table stu(
    id int primary key auto_increment,
    name char(12) not null,
    gender enum('male','female') default 'male',
    class_id int,
    foreign key(class_id) references my_class(cid)
);

级联更新级联删除：可以在关联下删除和修改。（权限比上面的高）——不建议用，危险

# 班级表
create table my_class(
    cid int primary key auto_increment,
    cname char(12) not null,
    startd date
);
# 学生表
create table stu(
    id int primary key auto_increment,
    name char(12) not null,
    gender enum('male','female') default 'male',
    class_id int,
    foreign key(class_id) references my_class(cid) on update cascade on delete cascade
);

【注】cascade 可以换成 set null（删除后，设置为null）、No action（不能进行删除和更新）、Restrict（不能进行删除和更新）、Set default

六、修改表

1、修改表名

　　alter table 表名 rename 新表名；

2、增加字段

　　alter table 表名 add 字段名数据类型 [完整性约束]；

3、删除字段

　　alter table 表名 drop 字段名；

4、修改字段

　　alter table 表名 modify 字段名数据类型 [完整性约束]；

　　alter table 表名 change 旧字段名新字段名旧数据类型 [完整性约束]；

　　alter table 表明 change 旧字段名新字段名新数据类型 [完整性约束]；

5、修改字段排列顺序/在增加的时候指定字段位置

　　alter table 表名 add 字段名数据类型 [完整性约束] first;

　　alter table 表名 add 字段名数据类型 [完整性约束] after 字段名；

　　alter table 表名 change 字段名旧字段名新字段名数据类型 [完整性约束] first;

　　alter table 表名 modify 字段名数据类型 [完整性约束] after 字段名；

6、添加和去掉null与unique约束

　　添加：alter table t modify name char(10) null;

　　去掉：alter table t modify name char(10) not null;

　　添加：alter table t modify id int unique;

　　去掉：alter table t drop index id;

七、多表结构的创建与分析

1、多对一

# 多对一
    # 学生    班级
    # 多个学生是一个班级的
    # 学生表有一个外键 关联班级表

    # 书籍    作者
    # 多本书可以都是一个作者写的
    # 书籍表有一个外键 关联作者表

    # 书籍    出版社
    # 多本书可以是一个出版社出版的
    # 书籍表有一个外键 观看出版社表

    # 商品    订单
    # 多个商品可以在一个订单里
    # 商品表有一个外键 订单表

create table press(
    id int primary key auto_increment,
    name varchar(20)
);

create table book(
    id int primary key auto_increment,
    name varchar(20),
    press_id int not null,
    foreign key(press_id) references press(id)
    on delete cascade
    on update cascade
);


insert into press(name) values
    ('北京工业出版社'),
    ('人民出版社'),
    ('知识产权出版社')
;

insert into book(name,press_id) values
    ('九阳神功',1),
    ('九阴真经',2),
    ('九阴白骨爪',2),
    ('独孤九剑',3),
    ('降龙十巴掌',2),
    ('葵花宝典',3)
;

SQL语句

2、多对多

# 多对多——出现第三张表（两个外键）
    # 学生    班级      多对一
        # 多个学生是一个班级的
    # 班级    学生      多对一
        # 多个班级对应一个学生

    # 一本书可以有多个作者
    # 一个作者可以写多本书

    # 一个商品可以有多个订单
    # 一个商品可以有多个订单

create table author(
    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)
);

#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功

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);

SQL语句

3、一对一

# 一对一
    #  客服     ——    学生
    # unique    foreign key unique

create table customer(
    id int primary key auto_increment,
    name varchar(20) not null,
    qq varchar(10) not null,
    phone char(16) not null
);

create table student(
    id int primary key auto_increment,
    class_name varchar(20) not null,
    customer_id int unique, #该字段一定要是唯一的
    foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
    on delete cascade
    on update cascade
);

#增加客户
insert into customer(name,qq,phone) values
    ('韩蕾','31811231',13811341220),
    ('杨澜','123123123',15213146809),
    ('翁惠天','283818181',1867141331),
    ('杨宗河','283818181',1851143312),
    ('袁承明','888818181',1861243314),
    ('袁清','112312312',18811431230);

#增加学生
insert into student(class_name,customer_id) values
    ('脱产1班',3),
    ('周末1期',4),
    ('周末1期',5);

SQL语句

【注】本次学习并借鉴的地址：https://www.cnblogs.com/Eva-J/articles/9677452.html#_labelTop

Day02 数据库MySql--存储引擎与表的操作

一、存储引擎

1、Innodb存储引擎——mysql5.6之后默认的存储引擎

2、Myisam存储引擎——mysql5.5之前的默认存储引擎

3、Memory存储引擎

4、面试问题

二、存储引擎测试

1、查看引擎

2、在建表时指定引擎

三、表和数据的基础操作

1、创建表

2、写入数据的方式（3种）

3、查看表中的数据

4、查看表结构

5、删除表

四、MySQL中的数据类型

1、数字

2、字符串

3、时间

4、enum/set

五、完整性约束

1、无符号的 int unsigned

2、不能为空 not null

3、默认值

4、不能重复 unique

5、自增

6、主键

7、外键

六、修改表

1、修改表名

2、增加字段

3、删除字段

4、修改字段

5、修改字段排列顺序/在增加的时候指定字段位置

6、添加和去掉null与unique约束

七、多表结构的创建与分析

1、多对一

2、多对多

3、一对一

推荐阅读