SQL基础语句合集

最新推荐文章于 2024-09-29 22:30:48 发布

pljnb

最新推荐文章于 2024-09-29 22:30:48 发布

阅读量71

点赞数 1

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

本文链接：https://blog.csdn.net/pljnb/article/details/128657927

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

本文详细介绍了如何使用Python连接和操作MySQL数据库，包括数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）语句。内容涵盖创建、删除数据库，查询与修改表结构，以及数据的增删改查操作，同时涉及用户管理和权限控制。示例代码详实，适合初学者参考。

摘要由CSDN通过智能技术生成

用python执行MySQL语句

文章目录

用python执行MySQL语句

导入python库

import json
import pymysql
from pymysql.converters import escape_string
import pandas as pd

连接数据库

#连接数据库
conn=pymysql.connect(
    host="localhost",
    port=3306,#端口号
    user="root",#数据库用户
    password="123456",#数据库密码
    #database="world"#要连接的数据库名称
)
cur=conn.cursor()

DDL语句-数据定义语言

DDL语句是数据定义语言，用来定义数据库对象

数据库操作

查询所有数据库：

SHOW DATABASES;

sql = """SHOW DATABASES;"""
cur.execute(sql)
cur.fetchall()

查询当前正在操作的数据库：

SELECT DATABASE();

sql = """SELECT DATABASE();"""
cur.execute(sql)
cur.fetchall()

创建数据库：

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

sql = """CREATE DATABASE IF NOT EXISTS PLJNB"""
cur.execute(sql)
cur.fetchall()

删除数据库：

DROP DATABASE [IF EXISTS] 数据库名;

sql = """DROP DATABASE IF EXISTS PLJNB"""
cur.execute(sql)
cur.fetchall()

使用数据库，进入指定数据库

USE 数据库名;

sql = """USE PLJNB"""
cur.execute(sql)
cur.fetchall()

表操作

查询当前数据库所有表

SHOW TABLES;

sql = """SHOW TABLES"""
cur.execute(sql)
cur.fetchall()

查询表结构

DESC 表名;

sql = """DESC PLJ_USER;"""
cur.execute(sql)
cur.fetchall()

查询指定表的建表语句

SHOW CREATE TABLE 表名;

sql = """SHOW CREATE TABLE PLJ_USER;"""
cur.execute(sql)
cur.fetchall()

创建表：

CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释]
)[COMMENT 表注释];

sql = """
	CREATE TABLE plj_user(
            id     INT COMMENT '编号',
            name   VARCHAR(50) COMMENT '姓名',
            age    INT COMMENT '年龄',
            gender VARCHAR(1) COMMENT '性别'
	)COMMENT '用户表'
"""
cur.execute(sql)
cur.fetchall()

数据类型

数值类型：

数值类型	长度
TINYINT [UNSIGNED 无符号数]	1B
SMALLINT	2B
MEDIUMINT	3B
INT	4B
BIGINT	8B
FLOAT	4B
DOUBLE(最大总长度,小数位数)	8B
DECIMAL

字符串类型：

字符串类型	长度	含义
CHAR(最大长度)	0~255B	定长字符串，空位会占空格字符，性能高
VARCHAR(最大长度)	0~65535B	变长字符串，空位不占空间，性能差
TINYBLOB	0~255B	二进制数据
TINYTEXT	0~255B	短文本字符串
BLOB	0~65535B	二进制形式长文本数据
TEXT	0~65536B	长文本数据
MEDIUMBLOB	0~16777215B	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215B	中等长度文本数据
LONGBLOB	0~4294967295B	二进制形式的极大文本数据
LONGTEXT	0~4294967295B	极大文本数据

日期类型：

日期类型	格式	含义
DATE	YYYY-MM-DD	日期值
TIME	HH:MM:SS	时间值或持续时间
YEAR	YYYY	年份值
DATETIME	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳（最多到2038年）

修改表-添加字段

ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT 注释][约束];

sql = """ALTER TABLE emp ADD nickname varchar(30) comment '昵称';"""
cur.execute(sql)
cur.fetchall()

修改表-修改数据类型

ALTER TABLE 表名 MODIFY 字段名新数据类型(长度)

sql = """ALTER TABLE emp MODIFY username varchar(20) comment '用户名';"""
cur.execute(sql)
cur.fetchall()

修改表-修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名新字段名类型(长度) [COMMENT 注释][约束];

sql = """ALTER TABLE emp CHANGE nickname username varchar(30) comment '用户名';"""
cur.execute(sql)
cur.fetchall()

修改表-删除字段

ALTER TABLE 表名 DROP 字段名;

sql = """ALTER TABLE emp DROP username;"""
cur.execute(sql)
cur.fetchall()

修改表-修改表名

ALTER TABLE 表名 RENAME TO 新表名；

sql = """ALTER TABLE emp RENAME TO employee;"""
cur.execute(sql)
cur.fetchall()

修改表-删除表(表数据和表结构都删除)

DROP TABLE[IF EXISTS] 表名;

sql = """DROP TABLE IF EXISTS plj_user;"""
cur.execute(sql)
cur.fetchall()

修改表-删除表(保留表结构，删除表数据)

TRUNCATE TABLE 表名;

sql = """TRUNCATE TABLE employee;"""
cur.execute(sql)
cur.fetchall()

DML语句-数据操作语言

数据操作语言，用来对数据库表中的数据进行增删改

添加数据(INSERT)

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在’引号’中。
插入的数据大小，应在字段规定范围内。

指定字段添加数据：

INSERT INTO 表名 (字段名1，字段名2，…) VALUES (值1，值2，…);

sql = """INSERT INTO employee (id,workno,name,gender,age,idcard,entrydate) 
            VALUES (1,'1','plj','男',21,'300080204106204004','2023-01-01');"""
cur.execute(sql)
cur.fetchall()

全部字段添加数据：

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

sql = """INSERT INTO employee
		    (id,workno,name,gender,age,idcard,entrydate) 
            VALUES
            (3,'1','plj','男',21,'440982200101204734','2023-01-01'),
            (4,'1','plj','男',21,'440982200101204734','2023-01-01'),
            (5,'1','plj','男',21,'440982200101204734','2023-01-01');"""
cur.execute(sql)
conn.commit()# 提交事务
cur.fetchall()

修改数据(UPDATE)

UPDATE 表名 SET 字段名1 = 值1，字段名2 = 值2，… [WHERE 条件];

sql = """UPDATE employee SET name = 'zxy',gender = '女' WHERE id = 3;"""
cur.execute(sql)
conn.commit()# 提交事务
cur.fetchall()

删除数据(DELETE)

DELET FROM 表名 [WHERE 条件];

sql = """DELETE FROM employee WHERE id BETWEEN 3 AND 5;"""
cur.execute(sql)
conn.commit()# 提交事务
cur.fetchall()

DQL语句-数据查询语言

数据查询语言，用来查询数据库中表的记录

查询数据(SELECT)：

编写顺序：

     SELECT 字段列表 \ 
     FROM 表名列表 \
     WHERE 条件列表 \
     GROUP BY 分组字段列表 \
     HAVING 分组后条件列表 \
     ORDER BY 排序字段列表 \
     LIMIT 分页参数 \

执行顺序：

     FROM 
     WHERE
     GROUP BY
     HAVING
     SELECT
     ORDER BY 
     LIMIT

简单查询

查询多个字段：
SELECT 字段1，字段2，字段3…FROM 表名；
或
SELECT * FROM 表名;
设置别名：
SELECT 字段1[AS 别名1]，字段2[AS 别名2]…FROM 表名;
去除重复记录：
SELECT DISTINCT 字段列表 FROM 表名;

# 尽量不要用*代替字段列表
sql = """SELECT name,workno,age FROM employee;"""
cur.execute(sql)
cur.fetchall()

# 去除重复记录＋设置别名(as可省略)
sql = """SELECT DISTINCT name as '姓名' FROM employee;"""
cur.execute(sql)
cur.fetchall()

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

sql = """SELECT * FROM employee WHERE (id >= 1) AND (id <= 6) AND (gender != '男');"""
cur.execute(sql)
cur.fetchall()

条件：

 >                     大于
 >=                    小于等于
 <                     小于
 <=                    小于等于
 =                     等于
 <>或!=                不等于
 BETWEEN ... AND ...   [最小值，最大值]
 IN(...)               在in列表中的值，多选一（相当于多个OR）
 LIKE                  占位符 模糊匹配（_匹配单个字符,%匹配任意个符）
 IS NULL               是NULL
 IS NOT NULL           不是NULL
 AND 或 &&             且
 OR 或 ||              或
 NOT 或 !              非

LIKE模糊查询：

sql = """SELECT * FROM employee WHERE name LIKE '_j_';"""
cur.execute(sql)
cur.fetchall()

sql = """SELECT * FROM employee WHERE name LIKE '%j%';"""
cur.execute(sql)
cur.fetchall()

分组查询

SELECT 字段/聚合函数列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

sql = """SELECT name,COUNT(*) as '人数',AVG(age) as '平均年龄' 
		 FROM employee 
         WHERE age <= 40 
         GROUP BY name
         HAVING AVG(age) >= 22 LIMIT 1;"""
cur.execute(sql)
cur.fetchall()

聚合函数

SELECT 聚合函数(字段名) FROM 表名;

聚合函数名含义
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
where与having的区别：

聚合函数名	含义
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和


执行时机不同	where是在分组之前过滤，having是对分组之后的结果进行过滤
判断条件不同	where不能对聚合函数进行判
执行顺序	where > 聚合函数 > having

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;

关键字	含义
ASC	升序（默认）
DESC	降序

sql = """SELECT * FROM employee ORDER BY age DESC, gender DESC;"""
cur.execute(sql)
cur.fetchall()

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

起始索引从0开始，起始索引 = （查询页面-1）*每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

sql = """SELECT * FROM employee LIMIT 0,10;"""
cur.execute(sql)
cur.fetchall()

DCL 语句-数据控制语言

数据控制语言，用来创建数据库用户、控制数据库的访问权限

管理用户

查询用户

USE mysql;
SELECT * FROM user;

sql = """USE mysql;"""
cur.execute(sql)

sql = """SELECT * FROM user;"""
cur.execute(sql)
cur.fetchall()

创建用户

CREATE USER ‘用户名’@‘允许访问的主机名’ IDENTIFIED BY ‘密码’;

若要任意主机访问，主机名为%

# 创建一个用户pljroot，只能在当前主机localhost访问，密码123456；
sql = """CREATE USER 'pljroot'@'%' IDENTIFIED BY '123456';"""
cur.execute(sql)
cur.fetchall()

修改用户密码

ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;

删除用户

DROP USER ‘用户名’@‘主机名’;

权限控制（参考官方文档）

查询权限

SHOW GRANTS FOR ‘用户名’@‘主机名’;

sql = """SHOW GRANTS FOR 'pljroot'@'%';"""
cur.execute(sql)
cur.fetchall()

授予权限

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;

sql = """GRANT ALL ON *.* TO 'pljroot'@'localhost';"""
cur.execute(sql)
cur.fetchall()

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;

sql = """REVOKE ALL ON *.* FROM 'pljroot'@'%';"""
cur.execute(sql)
cur.fetchall()

pljnb

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录