MySQL数据库学习

☞风雨中☜回荡着的气息

已于 2023-12-04 15:24:03 修改

阅读量1.4k

点赞数

文章标签： mysql

于 2020-08-16 12:01:31 首次发布

本文链接：https://blog.csdn.net/weixin_43261942/article/details/107965380

版权

Mysql 简单了解

mysql是关系型数据库管理系统
1.Mysql连接
mysql -u root -p
Enter password: **********
2.创建数据库：
create database scoot default charset 'utf8';
显示所有数据库
show databases;
删除数据库：
drop database scoot;
使用某个指定数据库：
use scoot;
3.数据类型
TINYINT 1 byte 小整数值
SMALLINT 2 bytes 大整数值
MEDIUMINT 3 bytes 大整数值
int或integer 4 bytes 大整数值
BIGINT 8 bytes 极大整数值
float 4 bytes 单精度浮点数值
DOUBLE 8 bytes 双精度浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值依赖于M和D的值小数值
char 0-255 bytes 定长字符串
varchar 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
blod 0-65 535 bytes 二进制形式的长文本数据
text 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038
4.表的操作
增：insert into 表名 (字段名1，字段名2……) values（值1，值2……）
删：delete from 表名 where 条件
改：update 表名 set 字段名=值 where 条件1 and 条件2……

create table student(            ##创建表
	sid int(10) not null auto_increment primary key,   ##主键约束 auto_increment自增
	sname varchar(100) not null,   ##非空约束
	sage int(3),                ##mysql中无检查约束
	ssex char(4),
    sbirth date,
	sphone int(10) unique,       ##唯一约束
	cid int(10) references clazz(cid) on delete cascade
)
##注意：主键自增在创建表的字段后使用 auto_increment primary key
alter table modify sname varchar(100) not null;  ##非空约束
select * from student
drop table student

//给表中增加数据 insert into 表名 字段名1，字段名2…… value（值1，值2……）
//全字段赋值则可以 如下写法 
insert into student values(default,'张三',13,'男','2000-04-19',7382794,1);   
insert into student values(default,'李四',13,'男','2000-04-19',7382892,2);
insert into student values(default,'王五',13,'男','2000-04-19',7387822,1);

create table clazz(
	cid int(10) not null auto_increment primary key,
	cname varchar(100) not NULL
)
select * from clazz
insert into clazz values(default,'java');
insert into clazz values(default,'c++');

delete from student where cid=1

查：select * from 表名 *指查询所有字段信息
select 字段名1，字段名2…… from 表名
distinct 进行查重显示不重复的信息
select distinct 字段1，字段2…… from 表名
5.约束
主键约束:sid int(10) auto_increment primary key其中auto_increment 是使其自增
非空约束：sname varchar(100) not null 值不可为空
唯一约束：sphone int(10) unique 值不可重复
外键约束：foreign key (cid) reference 表名(cid) 或 cid int(10) references clazz(cid) on delete cascade
on delete cascade 联级删除
6.排序
select * from t_user order by age asc 默认为升序排序(asc可不写)
select * from t_user order by age desc为降序排序

7.limit关键字 用途：分页查询
select * from 表名 limit 3 跟一个数据为从头开始查询指定个数(3)个数据
select * from 表名 limit 3,4从指定位置(3)开始查询指定个数(4)个数据

##每页n条数据，第m页
SELECT * FROM 表名 limit n*m-n，n

in()select * from 表名 where 字段 in(值1，值2……) 指定多个值查询数据
is null 查询指定字段为空的值
is not null 查询指定字段不为空的值

8.聚合函数
select count(*) from 表名 记录行数
select max(列名) from 表名 列中最大值
select min(列名) from 表名 列中最小值
select avg(列名) from 表名 列的平均值
select sum(列名) from 表名 列的总和

9.分组查询&筛选

--分组查询&筛选学习 
    --关键字：group by 字段名,字段名2....
      --注意：分组之后select后只允许跟分组字段和多行函数
         --where 后不能跟多行函数
  --分组筛选：
     --关键字：having
     --作用：针对分组进行筛选，允许使用多行函数
     --注意：having必须和分组结合使用，不可单独使用
  --where和having比较
      --where子句中不允许出现多行函数，having允许出现多行函数
        --where和having都可以用于对普通字段进行筛选 where执行效率高于having
          --where执行顺序：from where group select order
          --having执行顺序：from group having select order
----------------------------------------------------------     
    --查询不同部门最高工资
    select deptno,max(sal) from emp group by deptno
    --查询不停工作岗位员工数
    select job,count(*) from emp group by job
    --查询不同部门不同工作岗位人数
    select deptno,job,count(*) from emp group by deptno,job order by deptno
    --查询不同部门不同工作岗位且人数大于一的信息
    select deptno,job,count(*) from emp group by deptno,job having count(*)>1 order by deptno
    --查询部门号大于10的不同部门不同工作岗位人数
    select deptno,job,count(*) from emp group by deptno,job having deptno>10 order by deptno
    select deptno,job,count(*) from emp where deptno>10 group by deptno,job order by deptno

10.多表联合查询

--多表联合查询
         --SQL92方式
         --SQL99方式
-------------------------------------
--SQL92方式
      --笛卡尔积：将多个表的数据进行一一对应，所得结果为多表的笛卡尔积
                --结果的数量为所有表的数量的乘积
      select * from emp,dept
      --等值连接筛选：
          --概念：先做表的笛卡尔积，然后筛选，筛选条件为等值筛选
          select * from emp,dept where emp.deptno=dept.deptno
             --可以直接在select子句中使用字段直接获取数据，但效率较低。建议字段前加上表明
             --注意：如果是公共字段，则必须声明表名     
             select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno  --等值连接筛选
             select emp.ename,emp.job,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno
             select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno --给表使用别名
      --不等值连接
             select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.sal>=d.lowsal and d.hisal>=e.sal
      --自连接
             select * from emp e1,emp e2 where e1.mgr=e2.empno
      --外连接
          --左外连接
             select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno(+)
          --右外连接
             select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno(+)=d.deptno
------------------------------------------------------------------             
--SQL99多表查询
    --注意1：依然可以给表添加别名
    --注意2：如果使用on或者usering关键对结果进行筛选，必须使用inner join作用表与表的连接，其中inner可以省略
    --注意3:外连接的 outer关键字可以省略不写
    --注意4：依然可以继续使用分组，having ，排序等
    --笛卡尔积:使用cross join 关键字
            ---select 内容 from 表名 cross join 
            select * from emp cross join dept;
    --筛选
         --查询员工姓名，工作，薪资，部门名称
         --自然连接：使用关键字 natural join
            --使用：select 内容 from 表名 natural join 表名
            --特点1：底层先笛卡尔积，然后按照所有的同名同值字段自动进行等值筛选。
            --问题1：如果只想按照部分字段结果筛选怎么办？
            --问题2：如果想按照字段名不同，但是值相同进行等值筛选怎么办？
            select * from emp natural join dept;
             --解决1：使用using关键字
                  --作用1：指明使用指定的字段对联合查询的结果进行等值筛选
                  --注意：指明的字段必须是两表的同名同值字段
                  --使用:select 内容 from 表名 inner join 表名 using(字段名，字段名,....)
                  select * from emp e inner join dept d using(deptno);
            --解决2：使用on关键字进行自定义连接条件筛选(等值筛选，不等值筛选)
                  --注意：普通筛选条件使用where进行筛选，不要使用on进行。好处：SQL语句的阅读性变强。
                  --使用：select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
                  select * from emp e inner join dept d on e.deptno=d.deptno where e.deptno>10;
        --外连接：
             --左外连接:select 内容 from 表名 left outer join 表名 on 连接条件 
                 --查询员工姓名，工作，薪资，部门名称及没有部门的员工信息
                 update emp set deptno=null where eid=1;
                 select * from emp e left outer join dept d on e.deptno=d.deptno; 
             --右外连接:select 内容 from 表名 right outer join 表名 on 连接条件 
                  --查询员工姓名，工作，薪资，部门名称及没有员工的部门信息
                  select * from dept;
                  select * from emp 
                  update dept set deptno=10 where dname='西邮'; 
                  select * from emp e right outer join dept d on e.deptno=d.deptno; 
             --全外连接：select 内容 from 表名 full outer join 表名 on 连接条件 
                  select * from emp e full outer join dept d on e.deptno=d.deptno order by e.eid;
       --自连接:
             --查询员工及其上级领导姓名
             select e1.ename,e1.leader,e2.ename,e2.deptno from emp e1 inner join emp e2 on e1.leader=e2.deptno;

子查询：
--多表联合查询
     --当查询的数据分布于多张表中时，考虑多表联合查询
--子查询
  --使用时机：当查询的筛选条件不明确时，考虑使用子查询
   --单行子查询
   --多行子查询
--单行子查询
   --使用时机：筛选条件不明确需要执行一次查询，并且查询结果只有一个数据
   --注意：where子句中允许出现查询语句，该查询语句称为子查询
   --使用 select 内容 from 表名 where 字段名 比较运算符 子查询语句
   select * from emp
   update emp set deptno=10 where eid=1
   select * from emp where deptno>(select deptno from emp where eid=1);
--多行子查询
   --使用： 
      --关键字1：any 任意
      select * from emp where sal>any (select sal from where job='CLERK')
      --关键字2：all 全部
      select * from emp where sal>all (select sal from where job='CLERK')
      --关键字3：in 任意存在 相当于any
               --not in
      select * from emp where job=any (select job from emp deptno=10) and deptno=20
      select * from emp where job in (select job from emp deptno=10) and deptno=20
      select * from emp where job not in (select job from emp deptno=10) and deptno=20

流程控制函数：

IF(test,t,f)  --test为真返回t；否则返回f
IFNULL(expr1,expr2)  --expr1不为空返回expr1，否则返回expr2
NULLIF(expr1,expr2)  --expr1=expr2 返回null;否则返回expr1
case t
  when test1 then result1          --t与test相同则执行相应result 否则执行result3
  when test2 then result2
  else result3
END

#示例
select unit_name,IF(unit_name='银川','银川','其他') ordiess from pc_device_site_summary;

select unit_name,CASE
	WHEN unit_name='银川' THEN
		'银川'
	ELSE
		'其他'
END ordiess from pc_device_site_summary

MD5()加密
11.数据结构
数组链表二分查找树平衡树红黑树 b树 b+树
12.MySQL索引
1.索引可以大大提高MySQL的检索速度。
2.索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列
3.创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
4.索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
create index 索引名 on 表名(字段名(length)) length一般可以省略
drop index 索引名 on 表名 删除索引

13.视图：
create view 视图名(字段1，字段2……) as select语句创建视图
show create view 视图名 展示视图
drop view 视图名 删除视图

14.MySQL 事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

事务所要满足的四个条件：

原子性：

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

持久性

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
事务操作教程

15.数据库设计三范式

一.有主键，并且要求每个字段原子性不可再分
二.要求所有非主键字段全部依赖主键，不能产生部分依赖
三.所有非主键字段和主键字段不能产生传递依赖

16.数据字典
17.拉链表

Sql分析

https://blog.csdn.net/wang5701071/article/details/123920789?spm=1001.2014.3001.5502

1:sql的执行次数分析:
sql: show global status like 'com_______'
查看当前数据库下的增删改查的使用次数
2、慢查询日志记录

show variables like '%slow_query_log%';

记录执行时间大于所设置的时间的sql

开启慢查询日志sql: set global slow_query_log=1;

设置慢查询的超时时间: set global long_query_time=2; 以秒为单位

3、profile分析
1、查看profile是否开启
SELECT @@profiling;
2: 开启profile:
SET profiling = 1;
3: 查看当前会话下的所有sql执行时间:
show profiles;
4:查看具体sql的每个步骤消耗时间
show profiles for query xx; 这个xx是query_id
5:查看具体sql的cpu消耗时间
show profiles cpu for query xx; 比上个步骤多了一个cpu字段

存储过程

https://blog.csdn.net/scj0725/article/details/114625180

存储过程简单来说，就是为以后的使用而保存的一条或多条 MySQL 语句的集合。可将其视为批处理文件。虽然他们的作用不仅限于批处理。
就是数据库 SQL 语言层面的代码封装与重用

示例1：

-- 创建存储过程
create procedure mypro(in a int,in b int,out sum int)
begin
set sum = a+b;
end;

--执行上述存储过程语句
call mypro(1,2,@abc);
select @abc

create procedure 创建过程
mypro 定义过程名称
mypro(in a int,in b int,out sum int) 表示入参和返回参数
call 用来调用过程，
@s 是用来接收过程输出参数的变量

存储过程的参数

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）;

示例2：

create procedure mypro1(in tphone varchar(20))
begin
declare tname varchar(20) default 'dddd';
set tname='bianyuan';
select * from pc_entity_user where name=tname and phone=tphone;
end

--调用过程
call mypro1(15686675514)

declare 用于声明变量；
tname 表示变量名称；
varchar(20) 为 MySQL 的数据类型；
default 用于声明默认值;
例如：declare name varchar(20) default ‘jack’

流程控制语句

if

示例：

create procedure mypro2(in num int)
begin
if num<0 then -- 条件开始
select '负数';
elseif num=0 then
select '不是正数也不是负数';
else
select '正数';
end if;-- 条件结束
end;

call mypro2(10)

case

-- 创建过程
create procedure mypro3(in num int)
begin
case -- 条件开始
	when num<0 
		then select '负数';
	when num=0 
		then select '不是正数也不是负数';
	else 
		select '正数';
end case; -- 条件结束
end;

-- 调用过程
call mypro3(1);

while 循环语句

create procedure mypro5(out sum int)
begin
	declare num int default 0;
	set sum = 0;
	
	while num<10 do -- 循环开始
		set num = num+1;
		set sum = sum+num;
	end while; -- 循环结束
	
end;
-- 调用过程
call mypro5(@sum);
-- 查询变量值
select @sum;

repeat 循环语句

和 java 中的 do…while 语句类似，都是先执行循环操作，再判断条件
repeat 表达式值为 false 时才执行循环操作，直到表达式值为 true 停止。

-- 创建过程
create procedure mypro6(out sum int)
begin
	declare num int default 0;
	set sum = 0;
	repeat-- 循环开始
		set num = num+1;
		set sum = sum+num;
		until num>=10
	end repeat; -- 循环结束
end;
-- 调用过程
call mypro6(@sum);
-- 查询变量值
select @sum;

loop 循环语句

循环语句，用来重复执行某些语句。执行过程中可使用 leave 语句或 iterate 跳出循环

-- 创建过程
create procedure mypro7(out sum int)
begin
	declare num int default 0;
	set sum = 0;
	loop_sum:loop-- 循环开始
		set num = num+1;
		set sum = sum+num;
		if num>=10 then
			leave loop_sum;
		end if;
	end loop loop_sum; -- 循环结束
end;
-- 调用过程
call mypro7(@sum);
-- 查询变量值
select @sum;

存储过程的管理

-- 显示存储过程
SHOW PROCEDURE STATUS;
-- 显示指定数据库的存储过程，代码如下所示
SHOW PROCEDURE status where db = 'databaseName';
-- ：显示特定模式的存储过程，要求显示名称中包含“my”的存储过程，代码如下所示
SHOW PROCEDURE status where name like '%my%';
-- 显示存储过程源码
SHOW CREATE PROCEDURE mypro1;
-- 删除存储过程
drop PROCEDURE mypro1;

表分区操作

注：要么表没有主键否则分区字段必须是主键字段

--1 创建表 同时创建分区
create table employees (
    id int not null primary key,
    first_name varchar(30),
    last_name varchar(30)
)ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic 
partition by range(id)(
    partition p0 values less than (11),
    partition p1 values less than (21),
    partition p2 values less than (31),
    partition p3 values less than (41)
);

--2、手动进行分区修改
ALTER table employees partition by range(unix_timestamp(create_time))(
    partition employees202302 values less than (unix_timestamp('2023-02-28 23:59:59'))
) 

-- 添加分区sql
alter table employees add partition (partition employees202303 values less than (unix_timestamp('2023-03-31 23:59:59')));

mysql8 JSON

# mysql JSON
#     ->:提取json中的对象   ->>:提取json中的文本      JSON_EXTRACT: 文本
select feature -> '$.code',feature ->> '$.code',JSON_EXTRACT(feature,'$.code') from oms_finance_budget_task where id = 5;
select feature -> '$.name',feature ->> '$.name',JSON_EXTRACT(feature,'$.name') from oms_finance_budget_task where id = 5;

# null 数据 使用 ->>  与字符串'null'进行比较  下边只有第二条有结果
select * from oms_finance_budget_task where id = 5 and feature -> '$.name' = 'null';
select * from oms_finance_budget_task where id = 5 and feature ->> '$.name' = 'null';
select * from oms_finance_budget_task where id = 5 and JSON_EXTRACT(feature,'$.name') = 'null';

# json 函数
select json_array('a', 'b', 'c', 1) as arr;
select json_object('code', '111', 'name','zhangsna') as obj;     # 键值对
select json_quote('{"code": "111", "name": "zhangsna"}') a;
select feature, json_arrayagg(feature) from oms_finance_budget_task group by feature;  #  通过指定列或者表达式的值聚合成一个 JSON 数组
select json_objectagg(task_type) from oms_finance_budget_task where feature is not null group by task_type;

select json_contains(feature,'["FAIL", 2, {"try8": 3}]', '$.ysState') from oms_finance_budget_task where feature is not null

☞风雨中☜回荡着的气息

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
MySQL数据库学习

Mysql 简单了解mysql是关系型数据库管理系统1.Mysql连接mysql -u root -pEnter password: **********2.创建数据库：create database scoot default charset 'utf8';显示所有数据库show databases;删除数据库：drop database scoot;使用某个指定数据库：use scoot;3.数据类型TINYINT 1 byte 小整数值SMALLINT 2 byte
复制链接

扫一扫