MySQL表与表之间的三种关系、修改表、复制表、记录相关操作、单表查询

文章目录

  • ​​表与表之间的三种关系​​
  • ​​多对一(单向foreign key)​​
  • ​​多对多的关系(建立中间表,双向foreign key)​​
  • ​​多对一的关系(foreign key + unique)​​
  • ​​修改表 ALTER TABLE​​
  • ​​复制表​​
  • ​​记录相关操作​​
  • ​​单表查询​​
  • ​​小练习​​

表与表之间的三种关系

多对一(单向foreign key)

#逻辑层面的表与表层面的关联关系:本质是独立的,没有约束,会产生脏数据

foreign key(强关联)
1 多对一表关系 外键字段建在多的一方
2 在创建表的时候 一定要先建被关联表
3 在录入数据的时候 也必须先录入被关联表

# 案例
#建库
mysql> create database yang;

#切到库下
mysql> use yang;


# 被关联表
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(16),
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),
("linux12",503),
("linux13",603);

#查看
mysql> select * from class;
+----+-------------+------+
| id | name | room |
+----+-------------+------+
| 1 | python16期 | 403 |
| 2 | linux12 | 503 |
| 3 | linux13 | 603 |
+----+-------------+------+

#插入关联表数据
insert student(name,age,gender,class_id) values
("yang",18,"male",1),
("tom",19,"male",1),
("jack",20,"male",1),
("lili",19,"female",2),
("lxx",20,"female",2);

#插入失败 因为class表中没有4444班级
insert student(name,age,gender,class_id) values
("kk",21,"female",4444);
#插入成功
insert student(name,age,gender,class_id) values
("kk",21,"female",3);


#查看插入数据
mysql> select * from student;




2 真正做到数据之间有关系
更新就同步更新
删除就同步删除
级联更新 级联删除
# 级联更新 两张表一起变 防止脏数据产生
mysql> update class set id=232323 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from class;
+--------+-------------+------+
| id | name | room |
+--------+-------------+------+
| 1 | python16期 | 403 |
| 3 | linux13 | 603 |
| 232323 | linux12 | 503 |
+--------+-------------+------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+--------+----------+
| id | name | age | gender | class_id |
+----+------+------+--------+----------+
| 1 | yang | 18 | male | 1 |
| 2 | tom | 19 | male | 1 |
| 3 | jack | 20 | male | 1 |
| 4 | lili | 19 | female | 232323 |
| 5 | lxx | 20 | female | 232323 |
| 7 | kk | 21 | female | 3 |
+----+------+------+--------+----------+
6 rows in set (0.00 sec)

# 级联删除 删除一个表的id字段 两一个表也会删除
mysql> delete from class where id=232323;
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+----+-------------+------+
| id | name | room |
+----+-------------+------+
| 1 | python16期 | 403 |
| 3 | linux13 | 603 |
+----+-------------+------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+--------+----------+
| id | name | age | gender | class_id |
+----+------+------+--------+----------+
| 1 | yang | 18 | male | 1 |
| 2 | tom | 19 | male | 1 |
| 3 | jack | 20 | male | 1 |
| 7 | kk | 21 | female | 3 |
+----+------+------+--------+----------+
4 rows in set (0.00 sec)

多对多的关系(建立中间表,双向foreign key)

(
id int primary key auto_increment,
title varchar(32),
price int
);

create table author(
id int primary key auto_increment,
name varchar(32),
age int
);

create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);

desc book2author;


book表与author表之间没有直接的关系,book2author这张表作为一张媒介表使用。
book2author中的book_id与book中的id是一个一对多的关系,
book2author中的author_id与author中的id也是一个一对多的关系。

insert into book(title,price) values("九阴白骨爪",9999),("九阳真经",8888);
insert into author(name,age) values("yang",16),("lili",18);


select * from book;
select * from author;
select * from book2author;

delete from book where id=2;
select * from book2author; # 删除book表中的id为2的数据,那么book2author表中的跟book表中id为2关联的数据也被删除了

多对一的关系(foreign key + unique)

#左表唯一记录对应右表唯一记录

create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);

create table author1(
id int primary key auto_increment,
name varchar(32),
age int,
authordetail_id int unique,
foreign key(authordetail_id) references authordetail(id)
on update cascade
on delete cascade
);
desc author1;
desc authordetail;

MySQL表与表之间的三种关系、修改表、复制表、记录相关操作、单表查询

修改表 ALTER TABLE

语法:
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],

3. 删除字段
ALTER TABLE 表名
DROP 字段名;

4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
# 案例
#创建表
mysql> create table t1(id int);

mysql> desc t1;
#增加字段
mysql> alter table t1 add name varchar(5) not null,add age int not null;

#查看字段
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+

#增加字段(添加到最前面)
mysql> alter table t1 add x tinyint first;

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+


#插入字段(添加id字段之后)
mysql> alter table t1 add y varchar(3) after id;

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| y | varchar(3) | YES | | NULL | |
| name | varchar(5) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+

# 删除字段
mysql> alter table t1 drop y;

# 修改字段(修改字段modify 修改字段和名字change)
mysql> alter table t1 change name new_name varchar(10) not null;

#查看
mysql> desc t1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| new_name | varchar(10) | NO | | NULL | |
| age | int(11) | NO | | NULL | |

复制表

# select语句拼出来的虚拟表
mysql> select host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| db01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| db01 | | |
+-----------+------+-------------------------------------------+

# 复制表
mysql> create table t2 select host,user,password from mysql.user;

#查看表结果
mysql> desc t2;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| host | char(60) | NO | | | |
| user | char(16) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 查看表记录
mysql> select * from t2;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| db01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| db01 | | |
+-----------+------+-------------------------------------------+



# 只复制表结构 不复制表记录

# 没有符合条件的记录 1>10 永远为假
mysql> select host,user,password from mysql.user where 1>10;
Empty set (0.00 sec)

# 复制表结构
mysql> create table t3 select host,user,password from mysql.user where 1>10;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

# 没有内容
mysql> select * from t3;
Empty set (0.00 sec)

# 表结构已经复制成功
mysql> desc t3;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| host | char(60) | NO | | | |
| user | char(16) | NO | | | |
| password | char(41) | NO | | | |

记录相关操作

1.使用INSERT实现数据的插入
2.UPDATE实现数据的更新
3.使用DELETE实现数据的删除
4.使用SELECT查询数据


# 》插入数据INSERT《

# 案例
mysql> create table t4(id int,name varchar(10),age int);
Query OK, 0 rows affected (0.01 sec)

# 插入数据
mysql> insert t4 values(1,"yang",18);
Query OK, 1 row affected (0.00 sec)

mysql> insert t4(id,name,age) values(1,"yang",18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | yang | 18 |
| 1 | yang | 18 |
+------+------+------+
2 rows in set (0.00 sec)





# 修改表案例(小的数据迁移)
create table test1(
id int primary key auto_increment,
name varchar(10),
age int,
email varchar(30),
born_year year,
reg_time datetime
);

mysql> desc test1;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
| born_year | year(4) | YES | | NULL | |
| reg_time | datetime | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+


insert test1(name,age,email,born_year,reg_time) values
("yang1",18,"123123@qq.com",1997,now()),
("yang2",28,"123123@163.com",1998,now()),
("yang3",38,"123123@xx.com",1999,now()),
("yang4",48,"123123@aa.com",2000,now()),
("yang5",58,"123123@zz.com",2001,now());


mysql> select * from test1;
+----+-------+------+----------------+-----------+---------------------+
| id | name | age | email | born_year | reg_time |
+----+-------+------+----------------+-----------+---------------------+
| 1 | yang1 | 18 | 123123@qq.com | 1997 | 2021-07-01 21:31:37 |
| 2 | yang2 | 28 | 123123@163.com | 1998 | 2021-07-01 21:31:37 |
| 3 | yang3 | 38 | 123123@xx.com | 1999 | 2021-07-01 21:31:37 |
| 4 | yang4 | 48 | 123123@aa.com | 2000 | 2021-07-01 21:31:37 |
| 5 | yang5 | 58 | 123123@zz.com | 2001 | 2021-07-01 21:31:37 |
+----+-------+------+----------------+-----------+---------------------+


#先创建新表
create database db3;

use db3;

create table test2(
id int primary key auto_increment,
name varchar(15),
email varchar(30),
reg_time datetime
);

# 查看要导出的表的数据
mysql> select id,name,email,reg_time from yang.test1;
+----+-------+----------------+---------------------+
| id | name | email | reg_time |
+----+-------+----------------+---------------------+
| 1 | yang1 | 123123@qq.com | 2021-07-01 21:31:37 |
| 2 | yang2 | 123123@163.com | 2021-07-01 21:31:37 |
| 3 | yang3 | 123123@xx.com | 2021-07-01 21:31:37 |
| 4 | yang4 | 123123@aa.com | 2021-07-01 21:31:37 |
| 5 | yang5 | 123123@zz.com | 2021-07-01 21:31:37 |
+----+-------+----------------+---------------------+
5 rows in set (0.00 sec)


#导出数据(数据迁移完成)
mysql> insert db3.test2(id,name,email,reg_time) select id,name,email,reg_time from yang.test1;
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 1

#查看
mysql> select * from db3.test2;
+----+-------+----------------+---------------------+
| id | name | email | reg_time |
+----+-------+----------------+---------------------+
| 1 | yang1 | 123123@qq.com | 2021-07-01 21:31:37 |
| 2 | yang2 | 123123@163.com | 2021-07-01 21:31:37 |
| 3 | yang3 | 123123@xx.com | 2021-07-01 21:31:37 |
| 4 | yang4 | 123123@aa.com | 2021-07-01 21:31:37 |
| 5 | yang5 | 123123@zz.com | 2021-07-01 21:31:37 |
+----+-------+----------------+---------------------+


# 》更新数据UPDATE《
语法:
UPDATE 表名 SET
段1=值1,
段2=值2,
WHERE CONDITION;
# 案例:
UPDATE mysql.user SET password=password(‘123’)
where user=’root’ and host=’localhost’;



# 》删除数据DELETE《
语法:
DELETE FROM 表名
WHERE CONITION;
# 案例 :
DELETE FROM mysql.user
WHERE password=’’;

单表查询

# 语法
select distinct 字段1,字段2,... from 库.表
where 过滤条件(分组前过滤,针对表进行筛选)
group by 分组字段
having 过滤条件(分组后过滤,针对组进行筛选)
order by 排序字段
limit 条数



执行步骤:
1.找到表:from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重distinct
7.将结果按条件排序:order by
8.限制结果的显示条数 limit



# 案例


#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#插入记录
#三个部门:教学,销售,运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

# 查看
mysql> select * from employee;

# 删除多余内容
mysql> alter table employee drop post_comment ;

mysql> alter table employee drop office ;

mysql> alter table employee drop depart_id;


# 查看
mysql> select * from employee;


# 重复太多
mysql> select post from employee;
+-----------------------------------------+
| post |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使 |
| teacher |
| teacher |
| teacher |
| sale |
| sale |
| sale |
| operation |
| operation |
+-----------------------------------------+

# distinct 去重
mysql> select distinct post from employee;
+-----------------------------------------+
| post |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使 |
| teacher |
| sale |
| operation |
+-----------------------------------------+

# 查看年薪(加减乘除四则运算) as 别名annual_salary
mysql> select name,salary*12 as annual_salary from employee;
+------------+---------------+
| name | annual_salary |
+------------+---------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+---------------+

# 指定字段加内容(拼接)
mysql> select id,concat(name,"_vip"),sex from employee;
+----+---------------------+--------+
| id | concat(name,"_vip") | sex |
+----+---------------------+--------+
| 1 | egon_vip | male |
| 2 | alex_vip | male |
| 8 | 成龙_vip | male |
| 9 | 歪歪_vip | female |
| 10 | 丫丫_vip | female |
| 16 | 程咬银_vip | female |
| 17 | 程咬铜_vip | male |
| 18 | 程咬铁_vip | female |
+----+---------------------+--------+
mysql> select id,concat(name,"_vip")as new_name ,sex from employee; # as 加别名


# select结合case语句可以根据判断条件进行字符串拼接
SELECT
id,
(
CASE
WHEN name = 'egon' THEN
concat(name,"_vip")
WHEN name = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
name
END
) as new_name,
age
FROM
employee;
+----+------------+-----+
| id | new_name | age |
+----+------------+-----+
| 1 | egon_vip | 18 |
| 2 | alex_BIGSB | 78 |
| 8 | 成龙 | 48 |
| 9 | 歪歪 | 48 |
| 10 | 丫丫 | 38 |
| 16 | 程咬银 | 18 |
| 17 | 程咬铜 | 18 |
| 18 | 程咬铁 | 18 |

where筛选条件
作用:是对整体数据的一个筛选操作

1. 比较运算符:> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_,
%表示任意多字符
_表示一个字符
5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not




select * from employee where id=3;
select * from employee where id>3;
select * from employee where id>=3;
select * from employee where id!=3;

# and 和 or
select * from employee where id>3 and id<5;
select * from employee where id<3 or id>10;

# 查询id大于等于3小于等于5的数据
select * from employee where id>=3 and id<=5;
select * from employee where id between 3 and 5; # 两者等价

# 查询id等于 3,5,7 的数据
select * from employee where id=3 or id=5 or id=7;
select * from employee where id in (3,5,7);


# 模糊查询
like
% 匹配任意多个字符
_ 匹配任意单个字符

select * from employee where name like 'ji%';
select * from employee where name like 'ji_';
select * from employee where name like '__'; #任意两个字符
select * from employee where name regexp '^jin'; # 正则匹配
select * from employee where name regexp 'n$';

小练习

1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪


select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age > 30;
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post='teacher' and name like 'jin%';