sql笔记——mysql

最新推荐文章于 2024-10-17 18:58:27 发布

♪飙♪

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量1.6k

点赞数 57

分类专栏： sql Need To Modify 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_66327871/article/details/140399327

版权

Need To Modify 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

sql

1 篇文章 0 订阅

订阅专栏

一、数据库

1.数据库分类

系列文章【1】：史上最全SQL基础知识总结(理论+举例)-CSDN博客

系列文章【2】：”数据库分类及主流数据库对比_主流列式数据库对比-CSDN博客

1.1 关系型数据库

即二维表格模型数据库。它具有数据行、数据列、数据表、数据库的核心元素。

常见有：MySQL、SqlServer、Oracle、PostgreSQL、SQLite等。

1.2 非关系型数据库

有key-value存储数据库、list存储数据库等非关系型数据库，详细可参考 系列文章【2】。

常见有：Redis（key-value存储）、HBase（列存储）等。

2.数据库特点

系列文章【3】：

【数据库】第一章数据库系统概述笔记_数据库概述笔记-CSDN博客

系列文章【4】：

数据库系统的概念及其四大特点_数据由dbms统一管理和控制,是指什么?-CSDN博客

数据结构化
数据的共享性高，冗余度低，易扩充
数据独立性高
数据由DBMS统一管理和控制

数据库管理系统模式：

3. SQL

SQL （Structured Query Language）是结构化查询语言。

SQL语言分：

DOL:数据查询，对数据进行查询，如select
DML:数据操作，对数据进行增删改，如add、delete、insert
TPL:事务处理，对事务进行处理，
DCL:数据控制，对数据进行授权与权限回收，
DOL:数据定义，对数据进行定义，如create、drop

4.数据库安装

MySQL官网下载：MySQL

安装：MySQL安装教程（详细版）_mysql8.0.36安装教程-CSDN博客

5.MySQL的使用

5.1 基本语法

-- 创建数据库
create database fitbit_now;  
-- 将修改数据库更改到创建的数据库
use fitbit_now;
-- 清空数据表格
drop table if exists product, client, sales;

-- 创建表
create table biaoname(
-- 字段名 数据类型 可选约束条件,
id int unsigned primary key auto_increment not null,
-- ...
);

代码	说明
create database a1;	创建database:a1
drop database a1;	删除database:a1
use a1;	指针指向database:a1
create table b1( id int primary key auto_increment not null);	创建table:b1( id 数字类型主键 auto 不能null)
select * from a1.b1; or select a1.b1;	查询database:a1中table:b1所有数据
select now();	查询当前时间
desc a1.b1;	查询database:a1中table:b1中结构
...	...

例：

create database if not exists ceshi;
use ceshi;
drop table chanpin_chandi;
create table if not exists chanpin_chandi(
id int unsigned primary key auto_increment not null,
name1 char(4) not null,
type1 enum("易碎品","重要物品","其他"),
go_id int not null,
over_id int not null
);
-- 查看表中类型数据
desc ceshi.chanpin_chandi;
-- 查看表中数据
select * from ceshi.chanpin_chandi;
-- 查看创表/库
show create table chanpin_chandi;
show create database ceshi;
-- 添加类型
alter table chanpin_chandi add is_chaoshi enum("1","0") not null;
alter table chanpin_chandi add del_1 int;
-- 修改类型 modify 只能修改字段类型or约束，不能修改字段类型
alter table chanpin_chandi modify name1 varchar(16) not null;
-- 修改类型 change 即可重命名又可修改字段类型和修改约束
alter table chanpin_chandi change go_id get_datetime datetime not null,change del_1 del_2 varchar(15) not null; 
-- 删除表中类型 
alter table chanpin_chandi drop del_2;

select * from chanpin_chandi;
select id,name1 as "姓名",type1 "类型",over_id "到达id",is_chaoshi as "是否超时" from chanpin_chandi;
select id,name1,type1,over_id,is_chaoshi from chanpin_chandi where id<5;

-- 查看表结构
desc chanpin_chandi;

-- 添加数据 注：default:该字段的默认值; now():现在时间;
insert into chanpin_chandi values (1,"毛笔","其他",now(),00110,"1");
insert into chanpin_chandi(name1,type1,get_datetime,over_id,is_chaoshi) values ("玻璃杯","易碎品",now(),00111,"0"),
									("文件","重要物品",now(),00112,"0"),("纸",default,now(),113,"1");
-- 修改数据
update chanpin_chandi set is_chaoshi="1" where 2<id and id<5;
-- 删除数据
delete from chanpin_chandi where id>5 and over_id<>111 and over_id != 112;

-- 集合查询
select distinct type1, ifnull(is_chaoshi,0) is_chaoshi from chanpin_chandi where type1 is not null;
-- between...and...查询:连续范围内查询
select * from chanpin_chandi where id between 1 and 5;
-- in查询：
select * from chanpin_chandi where type1 in ("其他","重要物品");
-- 模糊查询  %:任意多个字符;  _:任意一个字符;
select * from chanpin_chandi where get_datetime like "202_-07-17 22%";
-- 分页查询    limit 开始索引 上限次数
select * from chanpin_chandi limit 0,10;
-- 分组查询  group by group_concat(a):指定字段信息集合 
-- with rollup: 添加一行显示组函数统计与计算结果，一般在跟在group by后
-- having: 过滤分组数据，只能用于group by
select id,type1 from chanpin_chandi group by id,type1;
select type1,group_concat(id) from chanpin_chandi group by type1;

-- 排序  asc升/desc降
select * from chanpin_chandi order by over_id desc;
select * from chanpin_chandi order by id asc, over_id desc; -- 优先级排序：左>右

create table if not exists chanpin_xiaoshou (
id int primary key auto_increment not null,
name1 varchar(16) not null,
base varchar(16) not null,
pice float null
);
drop table chanpin_xiaoshou;

insert into chanpin_xiaoshou values (1,"毛笔","A市",9.2);
insert into chanpin_xiaoshou(name1,base,pice) values ("玻璃杯","B市",10.3),("文件","A市",4.3),("毛笔","B市",9.0),("文件","B市",4.8),("玻璃杯","B市",9.9);

select * from chanpin_xiaoshou;
select count(id) from chanpin_xiaoshou;
select * from chanpin_xiaoshou where id > (select avg(id) from chanpin_xiaoshou)/3;
-- sub查询 即交集查询
select * from chanpin_xiaoshou x inner join chanpin_chandi c on x.id = c.over_id;
-- 左右连接查询  即不包含查询，分左右,如果另一边数据查询后有值则填数据,如果另一边数据查询后无值则填充null
select * from chanpin_xiaoshou x left join chanpin_chandi c on x.id = c.over_id;
select * from chanpin_xiaoshou x right join chanpin_chandi c on x.id = c.over_id;

use ceshi;
-- 添加外键约束
alter table chanpin_chandi add foreign key (over_id) references chanpin_xiaoshou(id);
select * from chanpin_xiaoshou where id in (select over_id from chanpin_chandi);
/* 或者 在表内 添加
a(id int primary key,x_id int,foreign key(x_id) references chanpin_chandi(id))
*/

-- 删除外键约束
alter table chanpin_chandi drop foreign key chanpin_chandi_ibfk_1; -- 外键名
show create table chanpin_chandi;

use ceshi;
create table chanpin_chandi_next1(
id int primary key auto_increment,
name1 varchar(16) not null,
type1 enum("易碎品","重要物品","其他")
);
-- drop table chanpin_chandi_next1;

show create table chanpin_chandi;
-- 表插入
insert into chanpin_chandi_next1(name1,type1) select name1,type1 from chanpin_chandi;
select * from chanpin_chandi_next1;

-- 一张表添加另一张表中的数据
use ceshi;
create table chanpin_chandi_next1(
id int primary key auto_increment,
name1 varchar(16) not null,
type1 enum("易碎品","重要物品","其他"),
get_datetime datetime
);
drop table chanpin_chandi_next1;

show create table chanpin_chandi;
insert into chanpin_chandi_next1(name1,get_datetime) select name1,get_datetime from chanpin_chandi;
select * from chanpin_chandi_next1;

select * from chanpin_chandi c,chanpin_chandi_next1 n where c.name1 = n.name1 and c.get_datetime = n.get_datetime;
select * from chanpin_chandi c inner join chanpin_chandi_next1 n on c.name1 = n.name1 and c.get_datetime = n.get_datetime;
alter table chanpin_chandi_next1 add super_id int;
update  chanpin_chandi_next1 n inner join chanpin_chandi c on (n.name1 = c.name1 and n.get_datetime = c.get_datetime and n.id > 0) set n.super_id = c.id;

-- 创建并读写已有的表
use ceshi;
create table chanpin_chandi_next2(
id int unsigned not null primary key auto_increment,
name2 varchar(16) not null) 
select name1 name2 from chanpin_chandi;

select * from chanpin_chandi_next2;

5.2 组函数

组函数又叫做聚集函数（aggregation function），它在一个行的集合（一组行）上进行操作，对每个组给一个结果。每个组函数接受一个参数。参数expr通常是列或表达式。默认情况下，组函数忽略列值为null的行，不把它们拿来参与计算。

常用组函数：

函数	说明
count()	总个数
min()	最小值
abs()	绝对值
sum()	总和
avg()	平均数
round(a,b)	数值a保留b位小数
...	...

5.3 远程登录(need to modify)

在MySQL里先开启服务端，后在另一台使用mysql使用host:port 以及hostuser、password进行连接。

ip地址查询介绍：python笔记——网络编程-CSDN博客

系列文章：远程连接MySQL数据库_mysql远程连接-CSDN博客

系列文章：如何给MySQL设置远程访问？（官方校正版）_设置 mysql 远程访问知乎-CSDN博客

5.4 数据库三范式

系列文章：数据库设计的三范式超详细详解_数据库三范式-CSDN博客

系列文章：数据库的三大范式及其重要性，详细易懂-阿里云开发者社区 (aliyun.com)

系列文章：数据库系统原理--【E-R模型】-CSDN博客

系列文章：数据库模型详解_数据库模型怎么看-CSDN博客

第一范式是数据库设计中的基本要求，它要求每个数据表中的每个字段都是原子的，即不能再分解为更小的数据单元。这样可以避免数据冗余和数据更新异常的问题。

第二范式要求数据表中的每个非主键字段完全依赖于主键，而不是依赖于其他非主键字段。这样可以避免数据冗余和更新异常的问题。

第三范式要求数据表中的每个非主键字段都不依赖于其他非主键字段，而是直接依赖于主键。这样可以进一步减少数据冗余和更新异常的问题。

5.5 python与mysql交互

支持第三方包：pymysql

执行过程：

图片详细：如何将 Python 与 SQL 数据库连接？- 极客为极客 (geeksforgeeks.org)

常用库函数：

函数	说明
conn = connect(host,prot,user,password,database,charset)	创建连接对象
conn.close()	关闭连接
conn.commit()	提交数据
conn.rollback()	撤销数据
a = conn.cursor()	获取游标对象，即执行sql语句
a.execute(sql)	执行并提交sql语句
a.fetchall()	取出结果中的所有数据
conn.commit()
...	...

例：

import pandas as pd
import pymysql as pm
conn = pm.connect(host='localhost',user= 'root',password='password',db='ceshi',charset='utf8')
a = conn.cursor()
sql = 'select * from chanpin_chandi' #查询


try:
    a.execute(sql)
except:
    a.rollback()
# 取出sql执行后返回的第一行数据
aa = a.fetchone()
print(aa)
# 取出sql执行后返回的未取出的、后面的所有数据
data = a.fetchall()
print(data)
# 表格数据
df = pd.read_sql(sql, conn)
print(df)
conn.close()

#运行结果
'''
(1, '毛笔', '其他', datetime.datetime(2024, 7, 17, 22, 29, 46), 110, '0')
((2, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 22, 37, 4), 111, '0'), (3, '文件', '重要物品', datetime.datetime(2024, 7, 17, 22, 37, 4), 112, '1'), (18, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 22, 41, 21), 111, '0'), (20, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 22, 41, 21), 111, '0'), (22, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 22, 41, 22), 111, '0'), (27, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 22, 47, 45), 111, '0'), (28, '文件', '重要物品', datetime.datetime(2024, 7, 17, 22, 47, 45), 112, '0'), (30, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 23, 29, 32), 111, '0'), (31, '文件', '重要物品', datetime.datetime(2024, 7, 17, 23, 29, 32), 112, '0'), (32, '纸', None, datetime.datetime(2024, 7, 17, 23, 29, 32), 113, '1'), (33, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 23, 29, 32), 111, '0'), (34, '文件', '重要物品', datetime.datetime(2024, 7, 17, 23, 29, 32), 112, '0'), (35, '纸', None, datetime.datetime(2024, 7, 17, 23, 29, 32), 113, '1'), (36, '玻璃杯', '易碎品', datetime.datetime(2024, 7, 17, 23, 29, 33), 111, '0'), (37, '文件', '重要物品', datetime.datetime(2024, 7, 17, 23, 29, 33), 112, '0'), (38, '纸', None, datetime.datetime(2024, 7, 17, 23, 29, 33), 113, '1'))1.50')))

    id name1 type1        get_datetime  over_id is_chaoshi
0    1    毛笔    其他 2024-07-17 22:29:46      110          0
1    2   玻璃杯   易碎品 2024-07-17 22:37:04      111          0
2    3    文件  重要物品 2024-07-17 22:37:04      112          1
3   18   玻璃杯   易碎品 2024-07-17 22:41:21      111          0
4   20   玻璃杯   易碎品 2024-07-17 22:41:21      111          0
5   22   玻璃杯   易碎品 2024-07-17 22:41:22      111          0
6   27   玻璃杯   易碎品 2024-07-17 22:47:45      111          0
7   28    文件  重要物品 2024-07-17 22:47:45      112          0
8   30   玻璃杯   易碎品 2024-07-17 23:29:32      111          0
9   31    文件  重要物品 2024-07-17 23:29:32      112          0
10  32     纸  None 2024-07-17 23:29:32      113          1
11  33   玻璃杯   易碎品 2024-07-17 23:29:32      111          0
12  34    文件  重要物品 2024-07-17 23:29:32      112          0
13  35     纸  None 2024-07-17 23:29:32      113          1
14  36   玻璃杯   易碎品 2024-07-17 23:29:33      111          0
15  37    文件  重要物品 2024-07-17 23:29:33      112          0
16  38     纸  None 2024-07-17 23:29:33      113          1

#更改数据
import pymysql as pm
conn = pm.connect(host='localhost',user= 'root',password='password',db='ceshi',charset='utf8')
a = conn.cursor()
sql = 'update chanpin_chandi set is_chaoshi = "1" where id = 1;'
# sql = "insert into chanpin_chandi(name1,type1,get_datetime,over_id,is_chaoshi) values('毛笔', '其他', now(), 110, '1')"
try:
    a.execute(sql)
    #将修改操作提交到数据库
    conn.commit()
except:
    a.rollback()

import pymysql as pm
conn = pm.connect(host='localhost',user= 'root',password='password',db='ceshi',charset='utf8')
a = conn.cursor()
sql = 'select * from chanpin_chandi where id < %s or is_chaoshi = %s'
try:
    #可传参元组列表字典
    a.execute(sql,(3,1))
except:
    a.rollback()
data = a.fetchall()
print(data)
conn.close()

5.6 事务

介绍：

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

系列文章：

【数据库的三大特性】——数据库中事务，事务的隔离性，隔离级别，事务的实现_数据库特性,隔离级别,事务是怎么实现的-CSDN博客

MySQL——事务(Transaction)详解_mysql事务-CSDN博客

事务四大特性：

原子性

指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。要么全部提交，要么全部回滚。

一致性

事务中操作的数据及状态是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致。

隔离性

一个事务所操作的数据在提交之前，对其他事务的可见性设定（一般设定为不可见）

MySQL中的隔离级别有：读未提交(Read Uncommitted)、读已提交(Read committed)、可重复读(Repeatable Read)、串行化(Serializable)。

持久性

事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

事务默认储存引擎：Inno DB 类型

-- 查看储存引擎
show engines;
-- 修改储存引擎
alert table chanpin_chandi engines="MyISAM";
-- 开启事务
begin;
-- or
start transaction;

-- 临时取消自动提交事务模式,以后需要手动提交
set autocommit = 0;
-- 手动提交
commit;
-- 回滚事务,即撤销
rollback;

5.7 索引

系列文章：

一文搞懂MySQL索引（清晰明了）-CSDN博客

MySQL 索引 | 菜鸟教程 (runoob.com)

索引是一种数据结构，用于加快数据库查询的速度和性能。

分类：

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引，即一个索引包含多个列。

单列索引：

例：

-- 查看语句执行所需要的时间
show profiles;

use ceshi;

-- 主键列、外键约束会自动创建索引,索引名不指定会默认使用字段名
-- 创建索引
alter table chanpin_chandi add index suoyin(name1);
create table a(id primary key auto_auto_increment,name1 char(2),index id_name1(name1));
-- 删除索引
alter table chanpin_chandi drop index suoyin;

show create table chanpin_chandi;
show create table a;

组合索引（复合索引、联合索引）：

相比较单列索引，这个较节约空间资源。

且满足最左原则，即最左边的字段一定要执行查询条件在where语句里面。

use ceshi;

-- 主键列、外键约束会自动创建索引,索引名不指定会默认使用字段名
-- 创建索引
alter table chanpin_chandi add index suoyin(name1,type1);
create table a(id int primary key auto_increment,name1 char(2),age int, index id_name1(name1,age));
-- 删除索引
alter table chanpin_chandi drop index suoyin;

drop table a;

show create table chanpin_chandi;
show create table a;

索引优缺点：

优：

加快查询

缺：

耗时间、占空间