[SQL] MySQL基础 + Python交互

内容为日常学习笔记
转载请注明：陈熹 chenx6542@foxmail.com （简书号：半为花间酒）
若公众号内转载请联系公众号：早起Python

理论知识

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

InnoDB支持事务和原子性操作

连接MySQL

mysql -h$ip -P$port -u$user -p

net start mysql # 或 mysql.server start
mysql -u root -p 
mysql -u user -p db_name # 直接进入指定数据库

导入导出

导出现有数据库数据：

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构 

导入现有数据库数据：
mysqldump -uroot -p密码  数据库名称 <导入文件路径

数据库操作

SELECT version(), current_date, now(); # 查看mysql版本号和日期、时间
SELECT version(); select now(); select current_date; # 分开写则表格分开

SOURCE c://test.sql # 用txt文件保存命令该后缀名 可直接执行文件内的命令

\c # 撤销先前输入 
\G # 按行输出
SHOW DATABASES; # 查看所有数据库
SELECT DATABASE(); # 进入数据库后查看当前数据库


# 数据库名称组成除了三大项还可以含$，但不能是纯数字
CREATE DATABASE db_name; # 创建spiders数据库
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 
# 安装utf8规则排序
# utf8mb4支持墨迹表情

USE db_name； # 进入数据库

ALTER DATABASE db_name DEFAULT CHARSET SET utf8;  # 修改数据库

DROP DATABASE db_name； # 删除数据库

DROP VARIABLES LIKE 'datadir'; # 查看数据库所在位置

SHOW TABLES; # 查看所有表
DESCRIBE db_name; # 查看表的描述，也可以用 DESC table;
SELECT * FROM db_name; # 查看表中所有数据
SELECT host,user FROM db_name; # 大小写不敏感
INSERT INTO person(name,birth) VALUES('A',1994-01-01); # VALUE也可

表操作

- 创建表

CREATE TABLE students(
    nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  # 自增列必须是索引，最好是主键
    name VARCHAR(20),
    num INT NOT NULL DEFAULT 2)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 主键

一张表只能有一个主键，值是唯一的（或多列组合是唯一的），不能重复不能为空，一般情况下自增列设置为主键。

常用 nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY

一张表可以多个唯一列

CREATE TABLE tb1(
                nid INT NOT NULL,
                num INT NOT NULL,
                PRIMARY KEY(nid,num) # 两列组成一个主键
            )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

主键的作用：

  1. 约束
  2. 索引，加速查找
  • 外键
# 创建表时创建
CREATE TABLE color(
    INT NOT NULL PRIMARY KEY,
    name CHAR(16) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;

CREATE TABLE fruit(
    INT NOT NULL PRIMARY KEY,
    smt CHAR(32) NOT NULL ,
    color_id INT NOT NULL,
    CONSTRAINT fk_fruit_color FOREIGN KEY (color_id) REFERENCES color(nid)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;

# 表外创建
ALTER TABLE students ADD CONSTRAINT fk_tb2_tb1 FOREIGN KEY tb2(info) REFERENCES tb1(nid);

- 修改表属性

# 添加列：
ALTER TABLE 表名 ADD 列名 类型
# 删除列：
ALTER TABLE 表名 DROP COLUMN 列名

# 修改列：
ALTER TABLE 表名 MODIFY COLUMN 列名 类型;  一般只改类型
ALTER TABLE 表名 CHANGE 原列名 新列名 类型; 可改列名+类型
  
# 添加主键：
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
# 删除主键：
ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE 表名  MODIFY 列名 INT, DROP PRIMARY KEY;
  
# 添加外键：
ALTER TABLE 从表 ADD CONSTRAINT 外键名称（形如：FK_从表_主表）FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);
# 删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  
# 修改默认值：
ALTER TABLE tbl ALTER列名 SET DEFAULT 1000;
# 删除默认值：
ALTER TABLE tbl ALTER列名 DROP DEFAULT;

数据类型

  1. bit(M): 二进制位，M为1~64，默认M=1

  2. int:

    nid int unsigned 最大4GB (2**32 − 1)

    整数类型中的m仅用于显示，对存储范围无限制

    tinyint: 有符号-128127，无符号0255，默认是signed

    ​MySQL中无布尔值，使用tinyint(1)构造

    bigint

    smallint

  3. decimal: 精确的小数，能够存储精确值的原因在于其内部按照字符串存储

    num decimal(6,2) 6个有效数字(总长度)，2位小数，最大是65和30

    float，double: 不精确

  4. char: 唯一定长，其他都是变长，数据不到该长度其他空间会闲置，查找速度快，浪费空间

    varchar: 变长，相对于char效率低。查找下一列时不确定跳多少存储空间

    字符最大是255

    text：字符最大是65535 2**16-1

    mediumtext：2**24-1

    longtext：2**32-1

  5. 二进制数据：

    TinyBlob、Blob、MediumBlob、LongBlob

    上传文件，blob强制二进制方式。现在多用varchar保存路径，上传文件保存在硬盘

  6. 时间

    DATE
YYYY-MM-DD（1000-01-01/9999-12-31）

TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）

YEAR
YYYY（1901/2155）

DATETIME

YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

  7. 枚举 enum

    支持65535个元素枚举

    CREATE TABLE shirts (
 name VARCHAR(40),
 size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));
 
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

    单选

  8. 集合 set

    CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

    可以多选

表内容操作

# 写的顺序
SELECT... FROM...
WHERE...
GROUP BY... HAVING...
ORDER  BY... 
LIMIT...

# 执行顺序
FROM...
WHERE...
GROUP BY...
SELECT...
HAVING...
ORDER BY...
LIMIT...

- 增

INSERT INTO 表 (列名,列名...) VALUES(值,值,值...);
INSERT INTO  表 (列名,列名...) VALUES(值,值,值...),(值,值,值...); # 增加多条数据

INSERT INTO  表 (列名,列名...) SELECT 列名,列名 FROM 表;  # 后面选择列不需要空格
# 如果数据可以转则允许互转
insert into students(name,age) SELECT caption,nid FROM tb2 WHERE nid>2;

- 删

DELETE FROM 表;
DELETE FROM 表 WHERE id＝1 AND name＝'alex';
DELETE FROM 表 WHERE id > 1 OR name＝'alex';

# 删除和清空
DROP TABLE student; # 删除表
DELETE FROM student; # 清空表，有自增列则清空后自增记忆存在
TRUNCATE (TABLE) student；# 快速清空表，有自增列则清空后自增从1重新开始

- 改

UPDATE students SET name ＝ 'alex' WHERE id>1;

UPDATE salary SET sex = IF(sex = 'm', 'f', 'm')  # 条件判断更改
UPDATE salary SET sex = char(ascii('m') + ascii('f') - ascii(sex));
UPDATE salary 
SET 
   sex = CASE sex 
        WHEN "m" THEN "f" 
        ELSE "m" END;
        
# UPDATE和JOIN
UPDATE A JOIN B ON A.URL = B.URL
SET member_id = '00012138' 
WHERE LOGIN_time BETWEEN '2019' AND '2020' 
AND B.class_id = 'TNT'; # BETWEEN 后可以再跟AND

- 查

SELECT * FROM 表;
# 要全部列可以把列名写一遍，效率比*高
SELECT * FROM 表 WHERE id <> 1; # 即!=
SELECT * FROM 表 WHERE id BETWEEN 3 AND 5;  # 即 id>=3 AND id<=5
SELECT nid,name,gender FROM 表 WHERE id%2 = 1;

SELECT DISTINCE name FROM score WHERE num < 60；
# distinct 去重只保留各组一项 也可以使用group by

WHERE (class, name) NOT IN (SELECT class, name FROM B) # 多个字段限制

- 条件选择

SELECT * FROM 表 WHERE id BETWEEN 5 AND 16; # 也支持多个并列
SELECT * FROM 表 WHERE id IN(11,22,33)
SELECT * FROM 表 WHERE id NOT IN(11,22,33)
SELECT * FROM 表 WHERE id IN(select nid from 表)

- 通配符模糊匹配

SELECT * FROM 表 WHERE name LIKE 'ale%'  # - ale开头的所有（多个字符串） where ... like ''
SELECT * FROM 表 WHERE name LIKE 'ale_'  # - ale开头的所有（1个字符）
SELECT * FROM 表 WHERE name LIKE '_le%'

- 限制/分页

SELECT * FROM 表 LIMIT 5;            # 前5行
SELECT * FROM 表 LIMIT 4,5;          # 从第4行下一行开始的5行
SELECT * FROM 表 LIMIT 5 OFFSET 4    # 从第4行开始的5行，与上一条功能一样

- 排序

SELECT * FROM 表 ORDER BY 列 ASC             # 根据 “列” 从小到大排列
SELECT * FROM 表 ORDER BY 列 DESC            # 根据 “列” 从大到小排列
SELECT * FROM 表 ORDER BY 列1 DESC,列2 ASC   # 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序，否则一列相同时其他列默认升序排序

- 分组+聚合

# group by获取各组第一行作为标识，其余行丢弃
SELECT num FROM 表 GROUP BY num 
# 分组的原理默认是升序排序，也可以降序
SELECT num FROM 表 GROUP BY num DESC

SELECT count(1) FROM 表 GROUP BY num # 也可使用
SELECT count(name) FROM students GROUP BY classid
# count(1) 和 count(name)区别
# 如果name中有null则count(name)不记录，其余时候二者完全一样
SELECT count(distinct name) FROM students GROUP BY classid # 去重

# sum只能对int类型计算 否则是0

# 多列分组 多个均相同的分到一组
SELECT num,nid FROM 表 GROUP BY num,nid
SELECT num,nid FROM 表  WHERE nid > 10 GROUP BY num,nid ORDER BY nid DESC
SELECT num,nid,count(*) AS count,sum(score) AS sum,avg(score),max(score),min(score) FROM 表 GROUP BY num,nid  # as 是自定义命名
 
# 如果要对聚合函数进行筛选需引入having 顺序在group by后
SELECT num FROM 表 GROUP BY num HAVING max(id) > 10
# 聚集函数
count   sum   max   min   avg   group_concat(字符串拼接) # 都会去除null
# sum(1) 等于 count(1) 只能针对int类型

# group_concat
SELECT id,GROUP_CONCAT(name) FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(name SEPARETOR ';') FROM aa GROUP BY id;  
SELECT id,GROUP_CONCAT(DISTINCT name) FROM aa GROUP BY id;  
SELECT id,GROUP_CONCAT(name ORDER BY name DESC) FROM aa GROUP BY id;
# 语法如下
DISTINCT name ORDER BY id DESC SEPARETOR '-'

group by 必须在where之后，order by之前

- 组合

# 纵向组合，并以第一个表字段为准
# 组合，自动处理重合
SELECT nickname FROM A UNION SELECT name FROM B;
 
# 组合，不处理重合
SELECT nickname FROM A UNION ALL SELECT name FROM B;

- 连表

# 需要多行之间满足一个需求就需要join

# 笛卡尔积
SELECT * FROM students,disc_info;

# 根据对应关系连表，实际等同于inner join
SELECT * FROM students,disc_info WHERE students.discipline = disc_info.nid;
SELECT students.name,disc_info.discipline FROM students,disc_info WHERE students.discipline = disc_info.nid;

# join 左右连接如果无对应关系显示NULL，join效率高

# 取交集，inner join，也可以理解成过滤掉含NULL数据行的左右连接
SELECT A.num, A.name, B.name FROM 
A INNER JOIN B 
ON A.nid = B.nid；

# 右连接，right join
SELECT A.num, A.name, B.name FROM 
A RIGHT JOIN B 
ON A.nid = B.nid

# 左连接，right join
SELECT A.num, A.name, B.name FROM 
A LEFT JOIN B 
ON A.nid = B.nid

获得的结果是个表，可以作为子查询的临时表

- CASE WHEN 判断

CASE WHEN 表达式
    THEN 输出
WHEN 表达式 # 多少个WHEN都可以
    THEN 输出
ELSE
    输出
END

SELECT id,name,
(
    CASE WHEN classid = 1 THEN 2
    WHEN classid =2 THEN 1
    ELSE classid END
) AS clid
FROM stu;


# 把tidydata转为正常数据
SELECT name,
MAX(CASE WHEN project = '基础' THEN score ELSE NULL END) as '基础',
MAX(CASE WHEN project = '爬虫' THEN score ELSE NULL END) as '爬虫',
MAX(CASE WHEN project = 'SQL' THEN score ELSE NULL END) as 'SQL'
FROM score;


# 查询两门及以上不及格同学信息
SELECT st.Name, AVG(score) as Score_n
FROM SC JOIN student st ON SC.SId = st.SId
GROUP BY SId
HAVING COUNT(CASE WHEN Score < 80 THEN 1 ELSE NULL END) >=2；

- 半连接

SELECT
    id,NAME,classid
FROM
    student tf
WHERE
    id = (
        SELECT
            max(id)
        FROM
            student ts
        WHERE
            ts.classid = tf.classid
    );
    
# 也可以用常规方法
SELECT
    id,NAME,classid
FROM
(
        SELECT
            max(id)
        FROM
            student
        GROUP BY
 classid
    );

- 索引

# 基于B+树
# 数据分的越开的列则建索引效果越好
# OR情况不能用索引
# 如果是联合索引 前部过滤条件可以做为索引
# 在SQL语句前加EXPLAIN就可以明确是否走索引

# 创建索引
CREATE INDEX name_index ON student(name);

# 聚簇索引叶子节点跟着数据，非聚簇索引叶子节点跟着主键(聚簇索引)
# 非聚簇走完多数会再走聚簇，除非SELECT内容均被包含于索引(全覆盖索引)
# MySQL中主键是聚簇索引，其他均为非聚簇索引
# 如果没有主键，内部会虚拟一个AUTO_INCREMENT的主键

- 视图

CREATE VIEW V1 AS  # 反复利用某个临时表则可以创建视图 
SELECT * FROM stu WHERE id > 10;

# 视图是一个动态表，会从物理表动态读出来。但无法直接对虚拟表即视图修改

# 修改视图
ALTER VIEW V1 AS SELECT * FROM stu WHERE id > 20;

# 删除视图
DROP VIEW V1;

- 触发器

DELIMITER // # 修改终止符
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT INTO tb1 FOR EACH ROW
BEGIN
    INSERT INTO tb2(name) VALUES('chenx'); 
    INSERT INTO tb2(name) VALUES(NEW.sname);  # 指代新插入的一行，OLD可以用在DELETE和UPDATE
END //
DELIMITER ; # 修改回原终止符

# BEFORE可以换成AFTER，INSERT可以换成DELETE或者UPDATE

# 删除触发器
DROP TRIGGER tri_before_insert_tb1;

- 函数

# 内置函数
SELECT CURDATE(); # 执行函数的用法
SELECT DATE_FORMAT(ctime, "%Y-%m") FROM stu GROUP BY DATE_FORMAT(ctime, "%Y-%m") # 时间格式化

# 自定义函数
DELIMITER \\
CREATE FUNCTION f1(
    i1 INT,
    i2 INT)
RETURNS INT# 强类型语言
BEGIN
    DECLARE num INT DEFAULT 0; # 声明变量
    SET num = i1 + i2;
    RETURN(num);
END \\
DELIMITER ;

- 事务、存储过程、循环等见

https://www.cnblogs.com/wupeiqi/articles/5713323.html

pymysql

import pymysql

# 连接配置信息
config = {
     'host':'localhost',
     'port':3306,
     'user':'root',
     'password':'xxxx',
     'charset':'utf8'}
db = pymysql.connect(**config)


cursor = db.cursor() # 获得MySQL的操作游标，利用游标来执行SQL语句，后续可以用execute()方法
cursor.execute('''执行SQL语句''') # 该语句的返回值是受影响的函数
cursor.commit() # 确认执行语句，如果是查询则不需要commit()

cursor.close() # 关闭游标
db.close() # 关闭数据库

# 如果是动态参数必须用传递参数而不能用字符串拼接
imp = 'A'
SQL = 'INSERT INTO student(name) VALUES(%s)'
cursor.execute(SQL,imp)

# 如果一条记录需要传递tuple
cursor.execute('INSERT INTO student(name,age) VALUES(%s,%d)',('A',25))
# 故单条单个记录也可以写成
cursor.execute('INSERT INTO student(name) VALUES(%s)',('A',))

# 如果多条记录需要用executemany()
lst = [('A',25),('B',25)]
cursor.executemany('INSERT INTO student(name,age) VALUES(%s,%d)',lst)

# 如果要返回查询的结果
print(cursor.fetchall()) # 以元祖内嵌元祖返回
print(cursor.fetchmany(10))

print(cursor.fetchone()) # 取一次后指针下移，下次执行fetchone()返回下一个记录；以元祖返回
results = cursor.scroll(0, mode='absolute') # 绝对指针，回到总记录第0位置
results = cursor.scroll(-1, mode='relative') # 相对指针，回到当前记录上1位置

# 设置游标参数，将返回值从tuple改为dictionary
cursor = db.cursor(cursor = pymysql.cursors.DictCursor)

# 操作完成后返回最后一个自增id
nid = cursor.lastrowid
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,335评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,895评论 3赞 387
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,766评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,918评论 1赞 285
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,042评论 6赞 385
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,169评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,219评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,976评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,393评论 1赞 304
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,711评论 2赞 328
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,876评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,562评论 4赞 336
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,193评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,903评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,142评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,699评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,764评论 2赞 351

推荐阅读更多精彩内容

  • MYSQL:基础知识
    一、什么是数据库 ？数据库是按照数据结构来组织，存储和管理数据的仓库，数据库是存储数据的集合的单独的应用程序。每个...
    wujimozun阅读 194评论 0赞 0
  • MySQL基础
    MySql基础 1、初识Mysql 1.1 什么是数据库 数据库（DataBase）：长期存放在计算机内，有组织，...
    渣渣殇阅读 161评论 0赞 0
  • MySQL：数据库基础
    一、什么是MySQL？ MySQL 是一个小型关系型数据库管理系统，开发者为瑞典 MySQL AB 公司。由于其体...
    虞锦雯阅读 480评论 0赞 1
  • MySQL基础
    1、什么是数据库 ？ 答：数据的仓库，如：在ATM的示例中我们创建了一个 db 目录，称其为数据库 2、什么是 M...
    明_27bf阅读 735评论 0赞 0
  • 六、Mysql 数据库设计
    数据类型之整数类型 • MySQL不仅支持标准SQL中的integer和smallint类型，还支持一些自己的扩展...
    码农工号9527阅读 236评论 0赞 0