欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL表操作

程序员文章站 2022-04-29 18:39:13
MySQL表操作 存储引擎:存储数据的方式 Innodb存储引擎: Myisam存储: Memory存储引擎: 查看当前的默认存储引擎: 表介绍 表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段 还记得我们之前写过的‘员工信息表作业’么?存储这 ......

mysql表操作

存储引擎:存储数据的方式

innodb存储引擎(数据持久化): mysql5.6之后的默认的存储引擎
myisam存储(数据持久化):mysql5.5之前的默认存储引擎
memory存储引擎(断点数据消失):数据存储在内存中,也就是说数据断点消失
innodb存储引擎:
# 数据和索引存储在一起 2个文件
#  数据索引一个文件
#  表结构一个文件
# 原子性操作:不能再拆分

# innodb存储引擎:支持事务,支持行级锁,表级锁,支持外键
# 支持事务:为了保证数据的完整性,将多个操作变成原子性操作,保证数据安全
# 支持行级锁:修改的行少的时候,修改数据频繁的操作
# 表级锁:批量修改多行的时候用,对于大量数据的同时修改
# 支持外键:约束两张表中的关联字段不能随意的添加和删除,降低数据增删改的出错率
myisam存储:
# 数据和索引不存储在一起 3个文件
#  数据
#  索引
#  表结构
#myisam存储只支持表锁
memory存储引擎:
# memory存储引擎(断点数据消失):数据存储在内存中,也就是说数据断点消失
#  表结构
# 查看当前的默认存储引擎:
# show variables like %engin%;
# 面试题:
# 了解mysql的存储引擎吗?
# innodb myisam  memory
# 项目用了什么存储引擎,为什么?
   # innodb
   # innodb 支持事务 行锁,表锁,外键
   # 多个用户操作的过程中对同一张表的数据同时做修改,
   # innodb支持行级锁,所以用了这个存储引擎
   # 为了适应程序未来的扩展,扩展新功能的时候可能会用到,涉及要维护的数据的完整性
   # 项目中有两张表,之间的外键关系是什么···,一张表的修改或删除比较频繁,怕出错所以做了外键约束

表介绍

表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段

还记得我们之前写过的‘员工信息表作业’么?存储这员工信息的文件是这样的:

id,name,age,sex,phone,job
1,anwen,23,female,13651054608,it
2,yage,22,male,13304320533,tearcher
3,wudi,25,male,13332353222,it
4,juses,40,male,13332353333,it

如果把上面这个文件改成一张表,应该是下面这个样子:

id,name,age,sex,phone,job称为字段,其余的,一行内容称为一条记录

id name age sex phone job
1 anwen 23 male 13651054608 it
2 yage 20 female 13304320533 it
3 wudi 25 male 13804310533 it
4 juses 22 male 13802320533 it

创建表

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

#注意:
1. 在同一张表中,字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
建表:
mysql> create database staff;
query ok, 1 row affected (0.00 sec)

mysql> use staff;
database changed
mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));
query ok, 0 rows affected (0.02 sec)


mysql> show tables;
+-----------------+
| tables_in_staff |
+-----------------+
| staff_info      |
+-----------------+
1 row in set (0.00 sec)

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| field | type                  | null | key | default | extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | yes  |     | null    |       |
| name  | varchar(50)           | yes  |     | null    |       |
| age   | int(3)                | yes  |     | null    |       |
| sex   | enum('male','female') | yes  |     | null    |       |
| phone | bigint(11)            | yes  |     | null    |       |
| job   | varchar(11)           | yes  |     | null    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select id,name,sex from staff_info;
empty set (0.00 sec)

mysql> select * from staff_info;
empty set (0.00 sec)
插入数据:
mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'alex',83,'female',13651054608,'it');
query ok, 1 row affected (0.00 sec)

mysql> insert into staff_info values (2,'egon',26,'male',13304320533,'teacher');
query ok, 1 row affected (0.00 sec)

mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'it'),(4,'boss_jin',40,'male',13332353333,'it');
query ok, 2 rows affected (0.00 sec)
records: 2  duplicates: 0  warnings: 0

mysql> select * from staff_info;
+------+----------+------+--------+-------------+---------+
| id   | name     | age  | sex    | phone       | job     |
+------+----------+------+--------+-------------+---------+
|    1 | alex     |   83 | female | 13651054608 | it      |
|    2 | egon     |   26 | male   | 13304320533 | teacher |
|    3 | nezha    |   25 | male   | 13332353222 | it      |
|    4 | boss_jin |   40 | male   | 13332353333 | it      |
+------+----------+------+--------+-------------+---------+
4 rows in set (0.00 sec)

查看表结构

mysql> describe staff_info;
mysql> desc staff_info;
show create table staff_info\g;

修改表结构

语法:
1. 修改表名
      alter table 表名 
                      rename 新表名;

2. 增加字段
      alter table 表名
                      add 字段名  数据类型 [完整性约束条件…],
                      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 字段名;
alter操作非空和唯一:
create table t(id int unique,name char(10) not null);

#去掉null约束
alter table t modify name char(10) null;
# 添加null约束
alter table t modify name char(10) not null;


# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;

alter处理null和unique约束
alter操作主键:
1、首先创建一个数据表table_test:
create table table_test(
`id` varchar(100) not null,
`name` varchar(100) not null,
primary key (`name`)
); 
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了,仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。
为表添加外键:
创建press表
create table `press` (
  `id` int(11) not null,
  `name` char(10) default null,
  primary key (`id`)
) ;

创建book表
create table `book` (
  `id` int(11) default null,
  `bk_name` char(12) default null,
  `press_id` int(11) not null,
  key `press_id` (`press_id`)
) ;

为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键
alter table book drop foreign key fk_id;

删除表

drop table 表名;

多表结构的创建与分析

如何找出两张表之间的关系

分析步骤:
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)

#3、总结:
#多对一:
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表

#多对多
如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

建立表之间的关系

#一对多或称为多对一
三张表:出版社,作者信息,书

一对多(或多对一):一个出版社可以出版多本书

关联方式:foreign key
sql示例:
===================== 多对一 =====================
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)
;
#多对多
三张表:出版社,作者信息,书

多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多
  
关联方式:foreign key+一张新的表
多对多:
=====================多对多=====================
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)
;
#一对一
两张表:学生表和客户表

一对一:一个学生是一个客户

关联方式: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
    -> );

#增加客户
mysql> insert into customer(name,qq,phone) values
    -> ('安文','31811231',13811341220),
    -> ('ll','123123123',15213146809),
    -> ('xx','283818181',1867141331),
    -> ('yy','283818181',1851143312),
    -> ('zz','888818181',1861243314),
    -> ('qq','112312312',18811431230)

mysql> #增加学生
mysql> insert into student(class_name,customer_id) values
    -> ('1班',3),
    -> ('2班',4),
    -> ('3班',5)
    -> ;