库、表、记录的详细操作
cuckooyang
|
|
MySQL
|
230

2685 字
|
23 分钟
本文最后更新于 50 天前，如有失效请评论区留言。

多实例部署

基于二进制包部署下的单机多实例部署（实际上没有公司会这么用，本处主要体会进程的思想）

实例1：/service/mysql --> /data/3306/data/my.cnf --> /data/3306/data
实例2：/service/mysql --> /data/3307/data/my.cnf --> /data/3307/data
实例3：/service/mysql --> /data/3308/data/my.cnf --> /data/3308/data

两种连接方式

  • 本机指定套接字连接 -S
  • 远程指定ip+port连接 -P -h

库详细操作

类型 描述
information_schema 虚拟库存储启动后参数
performance_schema 存储数据库性能参数
mysql 授权库存储权限信息
自定义库 存储自己的数据

创建数据库

规则

  • 字母数字下划线，@#\$都可以，不能单独用数字
  • 不区分大小写
  • 唯一不重复
  • 不用数据库关键字

数据库操作

create/drop/alter/show

表详细操作

存储引擎

概念：不同类型的表用不同存储引擎
网络链路

image-20260107071031311

mysql> show engines;

几种存储引擎的特点

存储引擎 描述
InnoDB
t1.frm 表元数据
t1.ibd 表数据		 transactions ： 事务，多个操作的完整性
row-level locking：行级锁，并发时数据修改机制
foreign keys：外键，建立表间的关联
MEMORY 数据存在内存
MyISAM 不支持事务、表锁；支持全文索引
  • innodb_file_per_table参数：独立idb数据文件

存储引擎存数据的方式不同

create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;

[root@centos /data/mysql/data/db1]# ll
total 128
-rw-r----- 1 mysql mysql 114688 Jan  7 22:34 t1.ibd
-rw-r----- 1 mysql mysql   1625 Jan  7 22:34 t2_377.sdi
-rw-r----- 1 mysql mysql      0 Jan  7 22:34 t2.MYD
-rw-r----- 1 mysql mysql   1024 Jan  7 22:34 t2.MYI
-rw-r----- 1 mysql mysql   1625 Jan  7 22:34 t3_378.sdi
-rw-r----- 1 mysql mysql   1628 Jan  7 22:34 t4_379.sdi

创建表

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

#注意：
1. 一张表中，字段名唯一
2. 宽度和约束条件可选
3. 字段名、类型必须

查看表

# 查看结构
desc t1; 
# 查看建表语句
show create table t1\G
# 查看内容
select * from t1;

select distinct 字段1,字段2,... from 库.表
                    where 过滤条件
                    group by 分组字段
                    having 过滤条件
                    order by 排序字段
                    limit 条数

简单查询：
select distinct 字段1,字段2,... from 库.表;

删除表

drop table t1;

修改表

数据库没有改名这么一说，只有表才会被修改。

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

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 旧字段名 新字段名 新数据类型 [完整性约束条件…];
      modify ：字段名 数据类型 必须写，不能改字段名；主键只能设置一个字段。

# 5. 主键
增加主键：alter table t1 modify in int primary key;
         alter table t1 add primary key(id);
删除主键：alter table t1 drop primary key;
自增主键：alter table t1 modify in int primary key auto_increment;

image-20260109065953545

复制表

image-20260109213910618

方案1：select 复制表（不带key）

  • 复制表结构＋记录 （key不会复制: 主键、外键和索引）

  • select 的查询结果是一张虚拟表，默认打印到终端

    也可以将其传给一个命令，如可以输入一个新表，就不再打印到终端。

    如果虚拟表没有数据，输入新表的只有表结构

mysql> create table new_service select * from service;

方案2：只复制表结构(记录为空，拿到的就是表结构)

方法1：条件为假，查不到任何记录
mysql> create table new1_service select * from service where 1=2;  

方法2：like 复制表结构（带key）
mysql> create table t4 like employees;

image-20260109070747091

数据类型

记住这6大类型

类型
int 整形括号里指定打印宽度，其他类型是存储宽度
float/double/decimal 浮点型（精度不一样）
char/varchar 字符型
datetime/time/date 时间日期（1001-9999，8字节）
TIMESTAMP 时间日期（1970-2038，4字节）
set/enum 集合/枚举
bit 位类型

关键点：数据库省空间的目的：不是节省硬盘，而是为了提升速度。

重点讲解：

char/varchar：

  • char 定长：不够补空格

  • varchar 变长：由 header（1byte）+ 数据组成

    header用于记录数据的长度。

  • 如果存空字符：char长度0bytes，varchar长度1bytes（因为header）

  • 如果存满字符：char长度n bytes，varchar 长度 n+1 bytes。

  • 谁更占空间：难说，分情况讨论。

集合：多选多
枚举：多选一

优化思路：越精简越好

  • 建表时，数据类型能占空间越小越好

    目的是为了提高速度，而不是省磁盘空间。

  • 没有负数的情况，设置无符号int unsigned能存更多东西

  • timestamp 通常更省空间，能支持到2038年也很了不起了

  • char/varchar 的选择要看具体存储的数据，并不是选择varchar就能省空间

#InnoDB存储引擎：建议使用VARCHAR类型
单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。

但对于InnoDB数据表，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

#其他字符串系列（效率：char>varchar>text）
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB 
BINARY系列 BINARY VARBINARY

text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

字段的约束条件

字段约束
not null/null 非空/空
default 默认值
primary key 主键（非空且唯一）
foreign key 外键
unique key 唯一键
auto_increment 自增（只能设置在主键上）

primary key

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
innodb存储引擎一定会有一个primary key，如果没有指定，会自己生成一个隐藏字段作为主键（问题来了，此时即占空间，又没办法调用，也就是用不上innodb最快的效率）。
查询条件能用主键就要用主键，它最快，超级无敌快；所以说能把一张表中的关键条件设置成主键最好了。
主键两大效果：不为空且唯一；加速查询

auto_increment

auto_incremnet：主键的铁搭子，只有主键字段才能设置为自增。

补充：

  • delete 清空表不会重置自增字段 。

  • truncate 才能还原最初的表环境。

index

索引就是key，也是index。primary key，unique key， foreign key 都属于索引。

foreign key

经验；能多对多就不要多对一，能多对一就不要一对一。

1、表与表之间的关系

多对一：表1多条记录 --对应--> 表2一条记录(比如学生表和班级表)

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

insert student(name,age,gender,class_id) values
("egon",18,"male",1),
("tom",19,"male",1),
("jack",20,"male",1),
("lili",19,"female",2),
("lxx",20,"female",2);

insert student(name,age,gender,class_id) values
("kk",21,"female",4444);

insert student(name,age,gender,class_id) values
("kk",21,"female",3);

这种情况下，class表必须存在，否则student表不能创建成功（约束的作用）

多对多：表1的多条记录对应表2的多条记录（如学生表和课程表），建立中间表关联。

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

一对一：

create table customer(
id int primary key auto_increment,
name varchar(20),
tel int
);
create table student(
id int primary key auto_increment,
class varchar(10),
customer_id int unique,
foreign key(customer_id) references customer(id) on update cascade on delete cascade
);

记录详细操作

读：select 写：insert update delete (通常 读写占比，可以达到10:1 )

insert [into]

# 默认全字段插值
# 可指定字段插值（顺序可调）
# 可一次性插入多个记录
# 插入一个 select 虚拟表

insert [into] t1 values(1,"cuckooyang",18);
insert t1(age,id) values(18,1);
insert t1(age,id) values(18,1)(..)(..);
insert t1(age,id) select age,id from db1.t2;

update set

update t1 set name="cuckooyang" where name="tom";

delete from

delete from t1 where name="tom";

# 清空表
delete清表：不能还原初始环境，如自增数字不会复位为0
truncate：清空并重建一个新表，完全还原的初始环境

表查询—单表查询

语法

SELECT 字段1,字段2... FROM 库名.表名
                  WHERE    分组前过滤条件
                  GROUP BY 分组字段
                  HAVING   分组后的筛选条件
                  ORDER BY 排序字段
                  LIMIT    限制条数;

关键字执行优先级

# 重点中的重点：关键字的执行优先级如下
from
where
group by
having
select
distinct
order by
limit

# 理解：
1、from，where，group by，having 都是在提取子表
2、select 拿到子表数据
3、distinct,order by,limit 处理子表数据

简单查询

select name from employee;
select distinct name from employee;

数值运算

四则运算查询

select name,salary*12 from employee;
# 指定虚拟表字段别名
select name as "姓名",salary*12 "年薪" from employee;

tips：这个虚拟表的概念，突然好多东西理顺了。

字符串拼接

定义显示格式

非常重要的内容
concat() 用于字符串拼接

select id,concat(name,"_vip") from employee;

多条件判断拼接

case 又来了！！！

select () from employee; ()里面写什么呢？
select (
  case
    when name = "cuc" then
      concat(name,"_vip")
    when name = "cuckoo" then
      concat(name,"_svip")
    else
      ...
  end
) as vip_name 
from employee

image-20260109074018815

where 单条件

where条件分类

  1. 比较运算符：> < >= <= <> !=
  2. 范围判断：
    • 范围内：between and
    • 空/非空判断：is null / is not null
  3. 多值判断：in(x,y,z)
  4. 模糊匹配：like 'cuckoo%'
    • % ：表示任意多字符
    • ：表示单个字符，可以多个 表示指定数量。
  5. 正则匹配：正则表达式基本都通用，where name regexp 'koo$'

where 条件关联：

  1. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

group by 分组

分组：发生在where之后，基于where的结果。
作用：某个表达属性的字段，可以用于分组（如部门，班级，性别）
注意：分组之后就只能以组为最小单位操作数据，即操作分组后聚合的结果（分组字段+分组后的聚合（任意字段））

分组搭配的聚合函数：

  • 平均值：avg
  • 最大值：max
  • 最小值：min
  • 求和：sum
  • 数量：count

having 聚合条件

也是过滤，可以使用聚合函数作为过滤条件。
having 跟 where

#！！！执行优先级从高到低：where > group by > having 
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

小知识点：

  • 没有写where，group by，having，但select运行在having之后，此时实际上已经将全部数据分为一个大组，所以是可以在select中使用聚合函数的。
select max(age) from employee；

order by 排序

作用：排序（按某字段）

  • 正序：asc
  • 倒序：desc

单列排序：order by age asc
多列排序：order by age asc, id desc;

limit 分页

限制显示条数：limit 5（前5个）
分页: limit 5,6 （5是起始位置，6是显示数量）

表查询—多表关联查询

注意：join连表过多查询效率会很低

多表连接查询

  • inner|left|right join on

笛卡尔积

数列的乘积，3x3 生成9个元素。

select * from employee,department;

很明显笛卡尔积得到了太多我们不需要的结果。我们希望表间是有关联的，而非全量配对。
可以通过where关键字建立表间关系，消除笛卡尔积。
但我们还有专门的方式：内连接

内连接

找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

select * from department inner join employee on department.id=employee.dep_id;

外连接

左连接：在内连接的基础上优先保留左表上的记录

右连接：在内连接的基础上优先保留右表上的记录

select * from department left join employee on department.id=employee.dep_id;
select * from department right join employee on department.id=employee.dep_id;

全外连接：两张表全部保留（左右连接的并集）（内连接是左右连接的交集）

  • union 并集，去重
  • union all 并集，不去重
select * from department left join employee on department.id=employee.dep_id
union
select * from department right join employee on department.id=employee.dep_id;

自连接

原理：表自己连接自己。
场景：一张表中的两个字段有关联关系的场景。where只能用在同一行，自连接能跨行。

select m.name, n.name, m.dep_id from employee m  inner join employee n on m.dep_id = n.dep_id and m.name
e != n.name;

多张表关联查询

延申一个多表内连接

inter join t1 on xxx inter join t2 on xxx inter join t3 on xxx

子查询

虚拟表连接真实表

虚拟表：（select....）
虚拟表连接真实表

虚拟表：select id,name from employee;
物理表：department
连接：select * from department where id in (select dep_id from employee);

子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中()。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

大白话：先从一张表中查出结果，然后以该结果作为条件去查下一张表，知道查出想要的结果。

1、where in/not in
2、any / all
3、exist / not exists

在使用EXISTS关键字时：
子查询状态为真：执行查询
子查询状态为假：不执行查询

select * from employee where exists (select id from employee where id=1);

作业

单表查询的作业

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

1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
3. 查询各岗位平均薪资大于10000的岗位名、平均工资
4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

1、查看所有员工中名字是jin开头，n或者g结果的员工信息
版权声明：除特殊说明，博客文章均为cuckooyang原创，依据CC BY-SA 4.0许可证进行授权，转载请附上出处链接及本声明。 | 博客订阅：RSS | 广告招租：留言板 | 博客VPS |
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇