python之SQL语言类型

2022-12-30 12:35:55

python之SQL

1. 前言

1.1 概念

  • SQL全称结构化查询语言(Structured Query Language， SQL)
  • 一种特殊目的的编程语言
  • 一种数据库查询和程序设计语言，可用于存取数据以及查询、更新和管理关系数据库系统

1.2 介绍

在这里插入图片描述
SQL标准使得SQL在MySQL、Oracle等关系型数据库通用。

1.3 作用

  • 数据库数据的增删改查
  • 数据库对象的创建、修改和删除
  • 用户权限/角色的授予和取消
  • 事务控制

2. SQL语言的分类

2.1 DDL(数据定义语言)

  • 关键字：create, alter, drop
    使用help查看命令。

2.1.1 数据库定义相关

  • 创建数据库
create database 数据库名;
  • 删除数据库
drop database 数据库名;
  • 切换数据库
use 数据库名;
  • 查看当前选择的数据库
    很少使用了。
select database();

2.1.2 表操作

  • 查看当前数据库的所有表
show tables;
  • 创建表
create table 表名(列及类型);

例如：

create table stu(
id int auto_increment primary key,  
name vachar(10) not null
);

上述基于命令行进行，可以使用可视化工具进行创建，如：
在这里插入图片描述

  • 删除表
drop table 表名;
  • 查看表的信息
desc 表名;
  • 修改表名
rename table 原表名 现表名;
  • 修改表
alter table 表名 add|change|drop|modify 列名 类型;

add: 新增字段
drop: 删除字段
change: 修改字段的名字或者数据类型，修改名和数据类型时都需要将原来的列名加上。
modify: 修改字段的数据类型，不用加原列名。
change和modify的区别可以使用help查看。
例如：

-- add
alter table t_stu add sex int(1) DEFAULT 0;

-- change
-- 将sname改成name
alter table t_stu change sanme name varchar(300) not null;

-- modify
alter table t_stu change name varchar(300) not null;
  • 查看表的创建语句
show create table ‘表名’;
  • 向某个数据库中导入存在sql脚本文件，即.sql脚本。
source sql文件的路径;

2.1.3 表的约束

见本文后续文章表的约束。

2.1.4 表的索引

见后续文章表的索引。

2.2 DML(数据操作语言)

  • 关键字：insert, delete, update, select (增删改查)

2.2.1 增加insert

  • 全列插入
insert into 表名 values(...);
  • 缺省插入
insert into 表名 (列1, 列2...) values(值1, 值2...);
  • 同时插入多条数据
-- way1: 
insert into 表名 values(...), (...) ...;

-- way2:
insert into 表名(列1,...) values(值1,...);
  • 实例：
    在这里插入图片描述
  • 自增实例：

自增的字段可以不用管，可以随便给值，会根据上一个字段自动增加。
如：
在这里插入图片描述

2.2.2 删除delete

-- 删除整张表的数据
delete from 表名;

-- 删除特定的一条数据
delete from 表名 where 条件;

例如：

在这里插入图片描述

2.2.3 修改update

update 表名 set 列1=值1 where 条件;

上述中的set表示集合的意思，即上述代码意思为：在[条件]下，将表为[表名]的表中的集合列1的值修改为值1。例如：
在这里插入图片描述

2.3 DQL(数据查询语言)

  • 关键字：select
  • 非常重要

2.3.1 最简单的查询方式： select * from

-- 查看表中所有信息数据；
select * from 表名;

-- 查看自定字段信息
select 字段1, 字段2... from 表名;

”*“代表表中的所有字段。

2.3.2 字段中运用算术表达式

对可计算的字段进行算术操作。如：

在这里插入图片描述

2.3.3 使用字段别名替换较长的字段名

为了增强SQL语句可读性，可以给字段给别名；
别名不可以为关键字。
如果含有空格或其它特殊字符或者大小写敏感，需要用双引号引起来。
利用as给字段给别名，as可以省略；如：

在这里插入图片描述

2.3.4 连接运算符(MySQL不支持，Oracle支持)

利用||可以将列和字符、或其它表达式连接在一起，得到一个新的字符串，起到合成的功能。
这个功能MySQL中不支持；Oracle中支持。
在这里插入图片描述

2.3.5 去重数据

使用DISTINCT关键字可从结果中清除重复行。

select distinct 字段名 from 表名;

问题
在这里插入图片描述

2.3.6 order by 对查询结果进行排序

排序包括两种，升序(asc， 缺省)和降序(desc)；默认按照升序排序。

-- 升序
select 字段名 from 表名 order by 字段名;

-- 降序
select 字段名 from 表名 order by 字段名 desc; 

-- 按多字段排序
select 字段名1, 字段名2... from 表名 order by 字段1, 字段2;

-- 使用字段别名排序
select 字段名1, 字段名2 (as) 字段名2别名... from 表名 order by 字段名2别名;

2.3.7 条件查询 where

-- 与
select * from 表名 where 字段1条件 and 字段2条件;

-- 或
select * from 表名 where 字段1条件 or 字段2条件;

-- 集合
select * from 表名 where 字段 in 范围;

问题：
上述and和or存在优化问题；结论为使用and时将检索结果较少的放在后面；使用or时将检索结果较多的条件放在后面。需要后续验证。
例如：
在这里插入图片描述

2.3.8 使用运算符的优化级

一种四种优先级: 算术>连接>比较>逻辑
如下：

在这里插入图片描述
可以使用小括号()改变运算顺序；
如：
在这里插入图片描述

2.3.9 分页查询

当查询出来的结果会放进内存中，当返回的结果过多时，可能会造成内存溢出，数据库崩溃；因此，在不知道返回数据大小时，应该采用分页查询，即选择从那一条数据开始查询，显示多少条数据。
语法：

select * from 表名 limit start, count;

上述sql语句意思为：从start开始获取count条数据。

补： SQL语句的执行顺序

比如：

select name, id from stu where id > 10 order by name;

上述SQL执行顺序：依次从繁到简，从多到少
from stu(取表) -> wher id > 10(筛选) -> select name , id(选择查询的字段) -> oder by name(排序)

2.3.10 使用函数(聚合函数和内置函数)查询

  • 函数包括聚合函数(SQL通用的函数)和内置函数(不同数据库有着不同的内置函数，这里选择MySQL学习)；
  • 函数的目的是为了快速得到结果，比如使用5个常见的聚合函数可以快速得到统计结果，如下：
-- 1. 获取表中的总行数  ---- count()
select count(*) from 表名;  -- 不建议使用
select count(常量) from 表名;
select count(字段名) from 表名; -- 推荐使用 


-- 2. 获取表中某一个字段的最大值 ---- max(字段名)
select max(字段名) from 表名;

-- 3. 获取表中某一个字段的最小值 ---- min(字段名)
select min(字段名) from 表名;


-- 4. 获取表中某一个字段的总和 ---- sum(字段名)
select sum(字段名) from 表名;

-- 5. 获取表中某一个字段的平均值  ---- avg(字段名)
select avg(字段名) from 表名;
  • mysql中内置函数：
    只供MySQL使用，不使用其它数据库。不需要全部记住，会查怎么用就行了。
    字符串相关内置函数 
-------- 字符串函数
-- 查看字符的Acii码值  --- ascii(str)，当str为空时，返回0
select ascii(str)；
select ascii('a');

-- 查看ascii码对用的字符 --- char(数值)
select char(数值);
select char(97);

-- 拼接两个字符串         ---concat(str1, str2)
select concat(str1, str2);
select concat(12,34,'ab');

-- 获取字符串中字符个数  --- length(str)
select length(str);
select length('abc');

-- 截取字符串  
select left(str, len);    --- 左截取 left(str, len) 
select right(str, len);   --- 右截取 right(str, len) 
substring(str, pos, len);---随机截取 substring(start, post, len)

-- 去除空格
select ltrim(str);   ---去除str左边的空格
select rtrim(str);   ---去除str右边的空格
select trim(方向 remstr from str);  -- 从指定方向删除remstr后的字符串str，指定方向包括both, leading, trailing，代表两侧，左，右

-- 返回n个空格字符组成的字符串  --- space(n)
select space(n);

-- 替换字符串   --- replace(str, from_str, to_str)
select replace(str, from_str, to_str);
select replace('abc1243', '123', 'abc');

-- 大小写转化
select lower(str);  --- 全部转为小写
select upper(str);  --- 全部转为大写

常见的数学相关函数 – 现在用的很少

-------- 数学相关函数
-- 求绝对值   ---- abs(n)
select abs(值);

-- 求m除以n的余数  --- mod(m, n) 或者m%n
select mod(m, n);
select m%n;

-- 向上取整   ---- floor(n) 不大于n的最大整数
select floor(n);


-- 向下取整  ---- ceiling(n)  不小于n的最大整数
select ceiling(n);

-- 四舍五入值    ---- round(n, d) n表示原数，d表示小数位置，默认为0
select round(1.6); -- 返回2


-- 求幂次  ----pow(x, y)  表示x^y
select pow(x, y)

-- 获取圆周率 PI()
select PI();

-- 产生0-1之间随机数  ---- rand()
select rand()

日期时间相关函数 — date, time和datetime类型相关
编程时间的起点： 1970.1.1日期的0:0:0:0为起始值开始计算。

----------- 日期时间相关函数
-- 获取子值  date为字符串，如"2016-12-31"  
select year(date);  --范围在1000-9999
select mouth(date);
select day(date);
 -- time为字符串，如
select hour(time);
select minute(time);
select second(time);

-- 日期计算 使用 +- 运算符， 数字后面接关键字year, day, hour, minute, decond
select '2016-7-6' = interval 1 day;

------ 日期格式和字符串之间
-- 字符串存入MySQL中：
into into 表名(时间字段) values('2021-07-31');
into into 表名(时间字段) values('20210731');

-- 读取表中的时间，并显示特定格式，利用date_format(时间字符串，格式)
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

-- 当前的日期
select current_date();

-- 当前得时间
select current_time();

-- 当前日期时间
select now(); 

-- 获取数据插入的时间
select hireddate;

时间中的格式：
可以使用的格式有：

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

2.3.11 分组查询 group by

  • 前言
    将表中的数据分成若干小组；
    按照某一个字段进行分组。

  • 语法格式

select 字段名
from 表名
where 条件
group by 分组
order by 排序;

注意：
group要在where后面，不能写道where前面；
oder by 不能写在where 和group前面。

  • 案例：
    在这里插入图片描述
  • 注意事项
    查询出来的数据是以分组为颗粒进行显示的，但是在select中可以适用聚合函数。
    案例1
    在这里插入图片描述
    案例2
    在这里插入图片描述
    上述SQL执行顺序： from -> where-> group by -> select；当执行到where时，并没有分组。

2.3.12 过滤分组数据having

  • 前言
    为了对group by 分组后的结果再进行过滤，则需要考虑having；因此having必须和group by 一起用 。
    where是对字段行进行过滤，having 是为了过滤分组后行结果；
    having 支持所有where操作符。
  • 语法：
    必须在group by后面，order by前面。
select 字段
from 表名
where 条件
group by 分组
having 分组过滤
order by 排序;

上述执行顺序: from -> where -> group by -> having -> select -> order by。

  • 案例
    在这里插入图片描述

2.4 DCL(数据控制语言)

  • 关键字：grant, revoke
    了解就行；用的时候会查就行

2.5 TCL(事务控制语言)

  • 关键字：SAVEPOINT, ROLLBACK, SET TRANSACTION, COMMIT
    了解就行；用的时候会查就行
  • 作者：Raymond。
  • 原文链接：https://blog.csdn.net/weixin_42384444/article/details/119200925
    更新时间：2022-12-30 12:35:55