初始数据库

程序媛—QY

已于 2023-08-24 21:05:37 修改

阅读量82

点赞数

分类专栏：数据库文章标签：数据库

于 2023-08-21 18:28:21 首次发布

本文链接：https://blog.csdn.net/QYcgln/article/details/132414256

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql

一，数据库简介(DB)

数据库(Database):根据数据结构来组织，修改的仓库

SQL(Structured Query Language),结构化查询语言

关系型数据库：有多张相互联系的二维表组成的数据库，有行和列组成的。

Mysql
Oracle

特点

使用表存储数据，格式统一，便于维护
使用SQL语句操作，标准统一，方便使用
- SQL(Structured Query Language),结构化查询语言

非关系型数据库

欲知后事如何，明天再说

二，MySQL概述

MySQL是一种使用广泛的开源，简单易用，高性能，可靠性的关系型数据管理系统(RDBMS)

数据库操作

1连接数据库

打开navicate
点击连接，选择Mysql
填写数据库连接信息
- 连接名，可以中文
- 主机：localhost(本机)或127.0.01
- 端口：3306(MySQL的端口号)
- 用户名/密码：安装时自己设置的

2创建数据库

双击打开链接
右键新建数据库
填写数据库名字符集排序规则

字符集：utf8mb4兼容性好

排序规则：不选则默认为utf8mb4_0900_ai_ci。

3创建表

第一条数据为id 并设为主键(唯一标识,主键唯一不为空) 一般为自动递增
数据类型
- 数字
  
  类型大小描述
  tinyint 1 byte 小整数值
  int/integer 4 byte 整数型
  float 4 byte 单精度浮点数值
  decimal 防止小数精度丢失(sql)
  BigDecimal 防止小数精度丢失(java)
- 字符串
  
  类型大小描述
  char 0-255 bytes 定长字符串
  varchar 0-65535 bytes 变长字符串
  text 0-65535 bytes 长文本数据
- 日期类型
  
  类型格式描述
  date YYYY-MM-DD 日期值
  time HH:MM:SS 时间值/持续时间
  year YYYY 年份值
  datetime YYYY-MM-DD 混合日期和时间值
  timesetamp YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳

类型	大小	描述
tinyint	1 byte	小整数值
int/integer	4 byte	整数型
float	4 byte	单精度浮点数值
decimal		防止小数精度丢失(sql)
BigDecimal		防止小数精度丢失(java)

类型	大小	描述
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
text	0-65535 bytes	长文本数据

类型	格式	描述
date	YYYY-MM-DD	日期值
time	HH:MM:SS	时间值/持续时间
year	YYYY	年份值
datetime	YYYY-MM-DD	混合日期和时间值
timesetamp	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

char和varchar的区别：

char不论你实际存储了多少的字符，都会占用设置的n个字符空间，比如手机号码，性别，身份证号等
varchar它存储的长度并不固定，实际使用多少就占用多少

datetime和timesetamp区别：

timesetamp： 1970-01-01 00:00:01.000000’到 2038-01-19 03:14:07.999999。

datetime： 1000-01-01 00:00:00.000000到 9999-12-31 23:59:59.999999。

对于跨时区的业务，timesetamp更为合适。

SQL语句

1 添加数据(insert)

# 给指定字段添加数据
	insert into 表名 (字段1,字段2...) values (值1,值2...);
# 给全部字段添加数据
	insert into 表名 values (值1,值2...);
# 批量添加数据
	insert into 表名 (字段1,字段2...) values (值1,值2...),(值1,值2...),(值1,值2...);
	insert into 表名  values (值1,值2...),(值1,值2...),(值1,值2...);

2 修改数据(update)

update 表名 set 字段1=值1， 字段2=值2,... where 条件

3 删除数据(delete)

delete from 表名 where 条件

4 查询数据

# 查询表内所有数据
# 查询表内所有数据 可以用as给字段起别名，as也可以省略
# * 代表所有字段
	select 字段名 from 表名  
	

# where 条件查询
# 模糊查询 % 表示0或多个字符 _ 表示一个字符
	select 字段名 from 表名 where name like '乔%' 
	
# 分页查询  关键字limit
# 分页查询第m页，每页展示n条数据 limit (m-1)*n, n 
	select 字段名 from 表名 limit 起始行数 展现数

# 排序 关键字 order by 字段（ASC 升序 ，DESC 降序）
	SELECT 字段名 from 表名 ORDER BY 字段 ASC;

# 聚合函数 count() sum() avg() max() min()   CAST()
	select count(0) from 表名   查询有多少条记录
    select sum(字段) from 表名 	 求和
    select avg(字段) from 表名 	 求平均值
    select min(字段) from 表名 	 求最小
    select max(字段) from 表名 	 求最大
    SELECT CAST(avg(age) as decimal(10,0)) as avg from person 四舍五入

# 分组 关键字 group by 字段
	select 字段名 from 表名 group by 字段 
# having条件查询  where分组前，having分组后的条件
	SELECT 字段名 from 表名 where 条件 GROUP BY 字段 having 聚合函数;
	
# 子查询 把查询到的结果当作另一个查询的条件
	SELECT 字段名 from 表名 where id in (SELECT pid from dept where name='鼓励部')
	
# 内连接 	
# 笛卡尔积 查询出来的结果没有意义
	select * from 表1,表2

# 显示内连接 inner可以省略
	select * from 表1 INNER JOIN 表2 on 表1.字段=表2.字段
	select * from 表1 JOIN 表2 on 表1.字段=表2.字段
	select 表1.字段,.....,表2.字段 from 表1 p  JOIN 表2 d on p.id=d.pid

# 隐式内连接
	select * from 表1,表2 where 表1.字段=表2.字段
	
# 外连接
# 左外连接  left [outer] join  查询的是左边表的所有部分和右边表的交集 outer可以省略l
	select 表1.字段,.....,表2.字段 from 表1 p left outer join 表2 d on 表1.字段=表2.字段
# 右外连接  right [outer] join  查询的是右边表的所有部分和左边表的交集
	select 表1.字段,.....,表2.字段 from 表1 p right outer join 表2 d on 表1.字段=表2.字段

5 条件(where)

sql语句里，where后面跟条件

# 运算符
	=、！=、<>、<、<=、>、>=、
# 关键字
	BETWEEN…AND、IN(set)、IS NULL、AND、OR、NOT

索引

1 概括

索引就是给一条数据增加一个定位

索引就是数据结构(b树) 作用：定位数据，提高查询效率

2 优缺点

优点

创建唯一性索引，保证数据库表中每一行数据的唯一性
大大加快数据的检索速度，这也是创建索引的最主要的原因
减少磁盘IO(向字典一样可以直接定位)

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占用额外的物理空间
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

当数据少的时候索引就显得多此一举了

3 索引类型

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。
聚合索引：由多个字段共同组成的索引。

4 索引优化

1.尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全局扫描

select * from user where name like '%子%'
-- 优化：在字段后面使用模糊查询
select * from user where name like '子%'

2. 尽量避免使用in 和not in，会导致引擎走全表扫描

SELECT * FROM user WHERE id IN (2,3)

-- 优化方式：如果是连续数值，可以用between代替
SELECT * FROM user WHERE id BETWEEN 2 AND 3

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

select * from user WHERE id = 1 or id = 3

-- 优化方式：可以用union（且）代替or
select * from user WHERE id = 1
UNION
select * from user WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

select * from user WHERE age is null 

-- 优化方式：可以给字段添加默认值0，对0值进行判断
select * from user WHERE age = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

可以将表达式、函数操作移动到等号右侧 ( 不建议这样写会造成**运行效率降低 ** )

-- 全表扫描
SELECT * FROM user WHERE age/10 = 9
-- 走索引
SELECT * FROM user WHERE age = 10*9

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

SELECT name, age, pwd FROM user and 1=1

-- 优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and
SELECT name, age, pwd FROM user WHERE 1 and 1

7. 查询条件不能用 < > 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。要去java进行判断，不能对数据库进行操作

8. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含a1，b2，c3三列，但SQL语句没有包含索引前置列"a1"，按照MySQL联合索引的最左匹配原则，不会走联合索引

-- 创建联合索引
create index user_index on user (name,age,pwd)
-- 这种方式是不会走索引的
select name,age,pwd from user where age=123 and pwd='312'

9. 隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select name from table where name = 123;

事务

离离原上草，一烧烧好久，

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

事务的四大特征：ACID

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间。相互独立。
一致性：事务操作前后，数据总量不变

函数

1.字符串

函数	功能
cocat(s1,s2,…sn)	字符串拼接，将 s1，s2 ，sn 拼接成一个字符串
lower(str)	将字符串全部转为小写
upper(str)	将字符串全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对的左边进行填充，达到个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对的左边进行填充，达到个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串 str从start位置起的len个长度

2.数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0-1内的随机数
round(x,y)	求参数的四舍五入的值，保留y位小数

3.日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定 date的年份
month(date)	获取指定 date的月份
day(date)	获取指定 date的日期
date_add(date,interval expr type)	返回一个日期时间值加上一个时间间隔
datediff(date1,date2)	返回时间date1和时间date2之间的天数

8.json

JSON是一种轻量级的数据交换格式，全称为JavaScript Object Notation（JavaScript对象表示法）。它以易于阅读和编写的文本格式来表示结构化数据，常用于Web应用程序之间的数据传输。

对象(Object): 用{}表示，有多个键值对组成
- ```
{"key1":"value1","key2":"value2","key3":"value3"}
```
数组(Array):用[]表示 ,多个只逗号隔开
- ```
["value1","value2","value3"]
```

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UQ5JG6wE-1692882327763)(D:\A\book\img\mysql.png)]

1.插入数据

数组：json_array(值1,值2,…)
对象：json_object(键1,值1,键2,值2,…)

insert into 表名 values(字段1,json_array(值1,值2,...),json_object(键1,值1,键2,值2,...))

2.查询

json_extract(字段名,“$.key”)
-> 字段名->“$.key”
json_keys(字段) 查询key

select 字段,json_extract(字段,"$.key") from 表名

select 字段,字段名->"$.key" from 表名

select json_keys(字段) from 表名

3.修改

json_set(“字段”,“$.key”,值) 字段有此属性则修改，否则增加数据
json_remove(字段,key)

update 表名 set 字段 = json_set(字段,"$.key",值) where 条件

update 表名 set  字段 = json_remove(字段,"$.key") where 条件

4.函数搜索

json_contains(字段，值)
json_contains(字段，值，‘$.key’)

select * from 表名 where json_contains(字段，'值')
select * from 表名 where json_contains(字段，值，'$.key')

只要另加一个 ‘’ 号

9.视图

1 介绍

视图概念：
- 视图可以由数据库的一张或者多张表中的数据组成的，可以对视图中的数据进行增删改查操作，视图可以理解成一张虚拟表，当数据库表中的数据发生变化的时候，视图中的数据也跟着发生变化
视图优点
- 我们可以把经常用的数据放到视图中，无需关心视图对应的数据库表的结构，表之间的关联关系，也不需要关心表之间的业务逻辑，简化开发人员的操作
- 增加数据的安全性，根据权限对用户进行数据访问的限制，将用户的访问之间限制在视图上，不用访问真实的数据表

2 使用

1 创建视图

格式：

create view  视图名称  as  sql语句(可以是多表查询)
CREATE VIEW pv as select id,username,hobby,friend from person
CREATE VIEW pv1(uname,gf) as select username,friend from person
CREATE VIEW pv as select id,username,hobby,friend from person WHERE JSON_CONTAINS(hobby, '"游戏"');

2 查看视图

可以在navicat中直接查看视图

3 修改视图

create or replace view 视图名称 as sql语句

如果存在视图名称则修改视图，如果视图名称不存在，则修改视图

create or replace  view pv1 (username,girlfriend,hb) as select username,friend ,hobby from person
select * from pv1

alter view 视图名称 as sql语句

alter view pv1 (username) as select username from person

4 插入数据

和普通表插入的格式一样,但是视图改变也会将影响原来的表

insert into 视图名（） vlues ()

insert into pv1 (username,girlfriend,hb) VALUES ('侯妈',json_object("name","a"),JSON_ARRAY("听歌","下厨房"))

5 删除数据

和普通表删除数据做法一样

delete from 视图名 where 条件

delete from pv1 where username='侯妈'

6 删除视图

drop view if exists 视图名称;

注意：当我们改变原表的时候，视图表中的内容也会发生改变。

存储过程

1 介绍

存储过程是一组预先编译的SQL语句，被命名并存储在数据库中。它们通常由数据库管理员或开发人员编写，并可以包含控制结构（例如条件、循环等）和输入/输出参数。存储过程可以像函数一样被调用，但它可能不返回值，也可以返回多个结果集。

2 使用

1 创建存储过程

格式：

DELIMITER //
CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name data_type, ...)
BEGIN
    -- 存储过程的SQL语句
END //
DELIMITER ;

procedure_name是你给存储过程起的名字；
parameter_name是存储过程参数的名字；
data_type是参数的数据类型。
DELIMITER: DELIMITER是一个特殊的命令，用于改变语句分隔符的默认值。SQL语句的默认分隔符是分号(;)，这意味着在执行多个语句时，每个语句都必须以分号结束。然而，在创建存储过程语句时，可能包含了多个分号，这会导致MySQL在遇到第一个分号时就认为语句结束，从而产生错误。
IN：表示参数是输入参数，用于接收调用存储过程时传递的值。
OUT：表示参数是输出参数，用于将存储过程的计算结果返回给调用者。
INOUT：表示参数既是输入参数，又是输出参数，用于接收值并返回结果

DELIMITER //
CREATE PROCEDURE GetStudentNameById(IN student_id INT, OUT student_name VARCHAR(255))
BEGIN
    SELECT name INTO student_name FROM student WHERE id = student_id;
END //
DELIMITER ;

-- 调用存储过程，并将结果保存到@name变量中
CALL GetStudentNameById(10, @name);
-- 查看存储过程返回的结果
SELECT @name;

2 存储过程的调用

可以使用CALL语句来调用存储过程。调用存储过程时，需要提供传递给存储过程的参数。如果存储过程有输出参数，需要在调用存储过程之前声明这些参数。

格式： CALL procedure_name([parameter_value, …]);

procedure_name是要调用的存储过程的名称；
parameter_value是传递给存储过程的参数值。

-- 调用存储过程，并将结果保存到@name变量中
CALL GetStudentNameById(10, @name);
-- 查看存储过程返回的结果
SELECT @name;

3 查看存储过程

SHOW PROCEDURE STATUS WHERE db = ‘数据库名称’;

SHOW CREATE PROCEDURE 存储过程的名称;

SHOW PROCEDURE STATUS WHERE db = 'xinzhi666';
SHOW CREATE PROCEDURE GetStudentNameById

程序媛—QY

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
1
评论
初始数据库

数据库(Database):根据数据结构来组织，修改的仓库SQL(Structured Query Language),结构化查询语言关系型数据库：有多张相互联系的二维表组成的数据库，有行和列组成的。MysqlOracle特点使用表存储数据，格式统一，便于维护使用SQL语句操作，标准统一，方便使用SQL(Structured Query Language),结构化查询语言非关系型数据库欲知后事如何，明天再说。
复制链接

扫一扫