刀刀的知识积累

SQL入门和实战

更新时间: 2024-03-28 14:32:08

# 为什么要学习SQL

# 无处不在的SQL

不管是何种开发语言，亦或是何种开发方向，SQL都是开发人员无法绕开的话题，除了一门趁手的编程语言外，SQL语言也是开发人员人人必备的开发技能。

# 后续学习的铺垫

  • PySpark
  • PyFlink
  • BI可视化

# 学习的程度

简单增删改查，做个SQL入门，详细的以后感兴趣再学

# 数据库介绍

如何实现数据库形式的数据管理呢？
我们需要借助：数据库管理系统，也就是常说的数据库软件。

# 数据库和SQL的关系

数据库是用来存储数据的，在这个过程中，会涉及到：

  • 数据的新增
  • 数据的删除
  • 数据的修改
  • 数据的查询
  • 数据库、数据表的管理
    等等

而SQL语言，就是一种对数据库、数据进行操作、管理、查询的工具。

使用数据库软件去获得库 -> 表 -> 数据，这种数据组织、存储的能力，并借助SQL语言，完成对数据的增删改查等操作

# MySQL的安装

首先去MySQL官网 (opens new window) ,去download页面，找到 MySQL installer for Windows

安装完成后配置一下

  1. 打开 C:\Program Files\MySQL\MySQL Server 8.0\bin，复制路径
  2. 我的电脑右键 -> 属性 -> 高级系统设置 -> 环境变量 -> 系统变量
  3. 找到path,双击编辑，点击新建，将 C:\Program Files\MySQL\MySQL Server 8.0\bin 粘贴进去

验证一下有没有安装成功： cmd中输入 mysql -uroot -p 回车输入密码。如果成功表示安装成功。

# MySQL的入门使用

# 在命令提示符内使用MySQL

MySQL安装好后，就可以简单的尝试使用它。

打开：命令提示符程序，输入mysql -uroot -p,然后回车输入密码，即可进入命令行环境。

在MySQL的命令行环境下，可以通过：

  • show databases; (分号一定要有) 查看有哪些数据库
  • use 数据库名 使用某个数据库
  • show tables; 查看数据库内有哪些表
  • exit 退出MySQL的命令行环境
    等基础命令

# 使用图形化工具操作MySQL

命令提示符进行MySQL的操作，不是太方便，一般开发者都会使用第三方的图形化工具进行使用。
可用于MySQL的图形化工具非常多，可成使用跨平台、开源、免费的图形化工具：DBeaver (opens new window)

打开网站找到download页面，下载DBeaver Community的windows(installer)

安装完成以后打开，连接到MySQL,只要输入密码测试连接通过就可以啦

# SQL基础与DDL

# SQL语言的分类

由于数据库管理系统（数据库软件）功能非常多，不仅仅是存储数据，还要包含：数据的管理、表的管理、库的管理、账户管理、权限管理等等。

所以，操作数据库的SQL语言，也基于功能，可以划分为4类：

  • 数据库定义： DDL（Data Definition Language）

    • 库的创建删除、表的创建删除等

  • 数据操纵： DML（Data Manipulation Language）

    • 新增数据、删除数据、修改数据等

  • 数据控制： DCL（Data Control Language）

    • 新增用户、删除用户、密码修改、权限管理等

  • 数据查询：DQL（Data Query Language）

    • 基于需求查询和计算数据

# SQL的语法特征

  • SQL语言，大小写不敏感
  • SQL可以单行或多行书写，最后以;号结束
  • SQL支持注释：
    • 单行注释： -- 注释内容（--后面一定要有一个空格）
    • 单行注释： # 注释内容 （# 后面可以不加空格，推荐加上）
    • 多行注释： /* 注释内容 */

# DDL - 库管理

  • 查看数据库
    SHOW DATABASES;

  • 使用数据库
    USE 数据库名称;

  • 创建数据库
    CREATE DATABASE 数据库名称 [CHARSET UTF-8];

  • 删除数据库
    DROP DATABASE 数据库名称;

  • 查看当前使用的数据库
    SELECT DATABASE();

# DDL - 表管理

  • 查看有哪些表
    SHOW TABLES; 注意：需要先旋转数据库哦

  • 创建表
    CREATE TABLE 表名称( 列名称 列类型, 列名称 列类型, ...... )

列类型 含义
int 整数
float 浮点数
varchar(长度) 文本，长度为数字，做最大长度限制
date 日期类型
timestamp 时间戳类型
  • 删除表
    DROP TABLE 表名称;
    DROP TABLE IF EXISTS 表名称;

# SQL - DML

DML是指数据操作语言，用来对数据库中标的数据记录进行更新
关键字：

  • 插入INSERT
  • 删除DELETE
  • 更新UPDATE

# 数据插入 INSERT

基础语法：
INSERT INFO 表[(列1，列2，。。。，列N)] values(值1，值2，。。。，值N)[,(值1，值2，。。。，值N),...(值1，值2，。。。，值N)]

create table student(
  id int,
  name varchar(20),
  age int
)

insert into student(id) values(1),(2),(3);

insert into student(id, name, age) values(4, '周杰伦', 31),(5, '林俊杰', 33);

-- 如果不写插入的列名就按建表顺序插入
insert into student values(6, '周伦', 11),(7, '俊杰', 3);
1
2
3
4
5
6
7
8
9
10
11
12

警告

SQL中只支持单引号

# 数据删除 DELETE

基础语法：
DELETE FROM 表名称 [WHERE 条件判断]

条件判断： 列 操作符 值
操作符： = < > <= >= != 等 如：

  • id = 5
  • id < 3
  • id >= 6
  • id != 5
delete from student where id = 1;
delete from student where id < 4;
delete from student where age = 33;
delete from student； -- 整张表的数据一条也不留
1
2
3
4

# 数据更新 UPDATE

基础语法：
UPDATE 表名 SET 列=值 [WHERE 条件判断]

update student set name = '张学友' where id = 1;
update student set name = '王力宏'; -- 整张表的名字都改成王力宏
1
2

# SQL - DQL

# 基础数据查询

在SQL中，通过SELECT关键字开头的SQL语句，来进行数据的查询 基础语法：
SELECT 字段列表|* FROM 表
含义： 从（FROM）表中，选择（SELECT）某些列进行展示

select id,name from student;
select id,name,age,gender from student;
select * from student; -- 查询全部
1
2
3

# 基础查询数据 - 过滤

查询条件也可以带有指定条件，语法如下：
SELECT 字段列表|* FROM 表 WHERE 条件判断

select * from student where age > 33;
select * from student where gender = '男';
1
2

# 分组聚合

分组聚合应用场景非常多，如：统计班级中，男生和女生的人数。
这种需求就需要：

  • 按性别分组
  • 统计每个组的人数
    这就称之为：分组聚合。

基础语法：
SELECT 字段|聚合函数 FROM 表 [WHERE 条件] GROUP BY 列
聚合函数有：

  • SUM(列) 求和
  • AVG(列) 求平均值
  • MIN(列) 求最小值
  • MAX(列) 求最大值
  • COUNT(列|*) 求数量
select gender,avg(age) from student group by gender;
-- 一个SQL中是可以写多个聚合的
select gender,avg(age),sum(age),min(age),max(age),count(*) from student group by gender;
1
2
3

注意

GROUP BY中出现了哪个列，哪个列才能出现在SELECT中的非聚合中

# 结果排序

可以对查询的结果，使用ORDER BY关键字，指定某个列进行排序，语法：
SELECT 列|聚合函数|* FROM 表 WHERE ... GROUP BY ... ORDER BY ... [ASC|DESC]

select * from student where age > 30 order by age desc;
1

# 结果分页限制

同样，可以使用LIMIT关键字，对查询结果进行数量限制或分页显示，语法：
SELECT 列|聚合函数|* FROM 表 WHERE ... GROUP BY ... ORDER BY ... [ASC|DESC] LIMIT n[,m]
含义：从第n条开始取m条

select * from student limit 2,3;
1

警告

  • WHERE、GROUP BY、ORDER BY、LIMIT均可按需求省略
  • SELECT 和 FROM 是必须写的
  • 执行顺序： FROM -> WHERE -> GROUP BY 和聚合函数 -> SELECT -> ORDER BY -> LIMIT

# Python & MySQL

# pymysql

除了使用图形化工具以外，我们也可以使用编程语言来执行SQL从而操作数据库。
在Python中，使用第三方库：pymysql来完成对MySQL数据库的操作。

安装：pip install pymysql

# 创建到MySQL的数据库链接

代码如下：

from pymysql import Connection

# 获取到MySQL数据库的链接对象
conn = Connection(
  host='localhost', # 主机名（或IP地址）
  port=3306, # 端口，默认3306
  user='root', # 账户名
  passord='123456' # 密码
)
# 打印MySQL数据库软件信息
print(conn.get_server_info())

# 关闭到数据库的链接
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 执行SQL语句

from pymysql import Connection
# 获取到MySQL数据库的链接对象
conn = Connection(
  host='localhost', # 主机名（或IP地址）
  port=3306, # 端口，默认3306
  user='root', # 账户名
  passord='123456' # 密码
)

# 获取游标对象
cursor = conn.cursor()
conn.select_db("test") # 先选择数据库
# 使用游标对象，执行sql语句,这里可以不写分号
cursor.execute("CREATE TABLE test_pymysql(id INT, info VARCHAR(255))")

# 关闭数据库的链接
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  • 执行查询性质的SQL语句
cursor.execute("SELECT * FROM student")
# 获取查询结果
results: tuple = cursor.fetchall()
for r in results:
  print(r)
1
2
3
4
5

# commit提交

pymysql在执行数据插入或其他产生数据更改的SQL语句时，默认是需要提交更改的，即，需要通过代码“确认”这种更改行为。

通过链接对象.commit()即可确认此行为。

cursor.execute("insert into student values(10007, '周姐抡', 19, '男')")

# 通过commit确认
conn.commit()
1
2
3
4

# 自动commit

如果不想手动commit确认，可以在构建链接对象的时候，设置自动commit的属性。

conn = Connection(
  host='localhost', # 主机名（或IP地址）
  port=3306, # 端口，默认3306
  user='root', # 账户名
  passord='123456',# 密码
  autocommit=True # 设置自动提交
)
1
2
3
4
5
6
7

← PySpark综合案例 →

MIT Licensed | Copyright © 2021-present 刀刀