数据库mysql语句 -> pymysql,sqlite3,MySQLdb,mysql.connector 第三方库常用sql语句

---

登录 Mysql

mysql -u 用户名 -p
密码

显示数据库
show databases;

使用数据库
use 数据库名;

显示可用表
show tables;

 

查询其他表
show tables from 其他表名;

 

查看数据库
select database();

创建表

create table 表名(
    字段名 类型,
    字段名 类型(长度)       
);

 

---

查看表结构
desc 表名;

 

---

插入数据
insert into 表名(字段名,...,字段名) values (值1,...,值n);

 

---

修改表的数据
update 表名 set 字段名=值 where 字段名=值;

 

---

删除表中数据
delete from 表名 where 字段名=值;

 

查看版本信息
select version();

查询常量

select 数字;

查询字符串常量
select '字符串';

查询表达式
select 运算式;

别名
select 字段/表达式 as '别名';

select 字段 空格 '别名';

查询时去掉重复的值
select distinct 字段 from 表名;

连接两列的值
select concat(列名1,...,列名n) from 表名;

---

查看是否有字段的值为空
select ifnull(列名,如果为空应该修改为什么值) from 表名;

查询某一字段的值在某一区间内
select distinct concat(列名1,...,列名n) from 表名 where 条件;

模糊查询
select 字段 from 表 where 字段 like '值%';
% 可以匹配0或多个, _ 可以匹配一个字符

将字符转义,如查询带有_的值
select 字段 from 表名 where 字段 like '_\_';
使用 \ 进行转义

转义时,使用自定义符号实现
select 字段 from 表名 where 字段 like '_自定义符号_' escape '自定义符号';

将自定义符号后面的进行转义

---

查询在某一区间内的值
select 字段 from 表名 where 字段 between 小的值 and 大的值;
取值包括小的值和大的值

查询某一个值是否在该字段中
select 字段 from 表名 where 值 in (字段);

查询某一个值是否为 null
select 字段 from 表名 where 字段 is null;
select 字段 from 表名 where 字段 is not null;

判断字段的指定值
select 字段 from 表名 where 字段 <=> 值;
<=> 安全等于
select 字段 from 表名 where 字段 = 值;

对查询的数据进行排序
默认升序 asc 

可以添加 where 语句进行判断

查询查看字段的长度
select length(字段) 别名 from 表名 order by 别名;

按照多个字段进行排序
select * from 表名 order by 字段1 排序方式,字段2 排序方式;

字符函数
select length(实参);
查看字符长度

字符变为大写
select upper(值);

字符变为小写
select lower(值);

字符拼接
select concat(值1,...,值n);

---

查询字符的某一小段值
select substr(值,起始位置,长度);

select substr(值,起始位置);
索引从 1 开始

查询某个值在字符中的第一个索引位置
select instr(字符,值);

没有值,则返回 0

查询去掉两边的空格或者其他符号
select trim(值);
默认去掉空格

select trim('符号' from 字符);
去掉两边的符号

在左侧填充符号
select lpad(字符,总长度,符号);

在右侧填充符号
select rpad(字符,总长度,符号);

将字符替换成新的字符
select replace(字符,需要替换的字符,新的字符);

数学函数
四舍五入
select round(值);
四舍五入到整数

select round(值,位);
四舍五入到小数点后几位

向上取整
select ceil(值);

---

向下取整
select floor(值);

截断数字,不进行修改数值
select truncate(值,位);

数值取模,取余
select mod(值1,值2);

---

日期函数
查询现在时间
select now();

查询当前日期
select curdate();

查询当前时间,不带有日期
select curtime();

查询当前年
select year(now());

参数还可以是指定的日期格式
select year('年-月-日');

查询当前月份
select month(now());

显示月份名字
select monthname(now());

字符转换为日期
select str_to_date(字符,'时间格式');
select str_to_date('年-月-日','%Y-%m-%d');

将日期转换为字符
select date_format(日期,时间格式);

---

其他函数
查看当前数据库
select database();

查看当前用户
select user();

---

判断语句
select if (条件,条件成立的值,条件不成立的值);

case 多分支条件选择

select 字段,字段,
case 字段
when 值 then 表达式
...
when 值 then 表达式
else 表达式
end 
from 表名
where 条件;

case 后不指定字段
select 字段,
case
when 条件 then 值
...
when 条件 then 值
else 值
end as 别名
from 表名;

分组函数
求和
select sum(字段) from 表名;

求平均值
select avg(字段) from 表名;

求最大值
select max(字段) from 表名;

求最小值
select min(字段) from 表名;

求个数
select count(字段) from 表名;

---

将 distinct 和 分组函数一起使用
select avg(distinct 字段) from 表名;

统计行数,只要有一个字段不为null,就加1
select count(*) from 表名;

---

查看两个日期之间相差的天数
select datediff(字符1,字符2);

分组查询
select 字段 from 表名
where 条件
group by 字段
order by 字段;

查看字段不为 null 的,进行分组
select max(字段),字段 from 表名 where 字段 is not null group by 字段;

在使用 group by 之后,对分组进行筛选
select  分组函数(字段),字段 
from 表名 
where 条件
group by 字段 
having 分组函数(字段) 条件;

group by 和 having 后支持别名

创建好的两个表如下所示

两个表连接查询
select 表1字段,表2字段 from 表1,表2 where 表1.字段 比较符号 表2.字段;

可以对表起别名
from 表名 as 别名

对两个表使用模糊查询
在 where 条件下 加入 like

对两个表使用 分组查询并排序
group by 进行分组
order by 进行排序 默认升序

自连接
设置别名来进行区分,将一张表当做两张表

生成随机数
select rand();

MD5 加密
select md5(字符);

两个表的连接方式

inner 内连接

left 左外连接

right 右外连接

cross 交叉连接

进行分组筛选

查看连接时存在的空值

select 字段1,...,字段2 from 表名1 别名 连接方式 join 表名2 on 条件
where 字段 is null;

查询时应用左外连接，判断是否为空

子查询

any 表示任意一个

 

all 全部

not in 不在子查询中

=any 效果

子查询中存放两个字段

子查询在 select 后

select 查询后放 select 查询

子查询放在 from 中

子查询放到 exists 后面 ， 相关子查询
查看是否具有数据

分页查询 
select 字段
from 表名 别名
连接方式 join 表 别名
on 连接条件
where 条件
group by 分组字段
having 分组筛选
order by 排序的字段
limit offset,size;

offset 起始索引,从 0 开始
size 个数

省略 offset 

语句顺序
select 查询字段 / 别名.字段
from 表名 as 别名
连接类型[inner , left , right] join 表名 别名
on 连接条件
where 条件
group by 字段
having 条件
order by 条件
limit 起始索引,条数;

联合查询 union
select 语句
union 
select 语句

使用 union all 不去掉查询的结果中的重复项

插入语句 
insert into 表名(字段) values (值);

空值 null

插入时，不指定字段名称
注：顺序要和表中一致

第二种插入方式
insert into 表名
set 字段=值,字段=值;

修改单表记录
update 表名 set 字段=值 where 条件;

修改多个字段值

修改多表

update 表名 别名
join 表名 别名
on 连接条件
set 字段 = 值
where 条件;

删除语句
delete from 表名 where 条件;

删除语句

delete 表1别名,表2别名
from 表1 别名
连接方式 join 表2 别名
on 连接条件
where 筛选条件;

删除全部信息,不可以加where
truncate table 表名;

更改数据库的字符集
alter database 数据库名 character set 字符集;

删除数据库
drop database if exists 数据库名;

创建表

create table 表名(
    字段 字段类型,
    字段 字段类型(长度),
    字段 字段类型(长度) 约束,
)

显示表结构
desc 数据表;

---

修改表

修改列名
alter table 表名 change column 旧列名 新列名  字段类型 ;

修改列的类型
alter table 表名 modify column 列名 新类型;

添加列
alter table 数据表 add column 字段名 字段类型;

删除列
alter table 表名 drop column 列名;

修改表名
alter table 表名 rename to 新名称;

删除表
drop table 表名;

复制表的结构
create table 表名 like 表名;

复制表的结构和数据
create table 表名 select * from 表名;

创建带有约束的表
primary key 主键
not null 非空
check 检查输入的值
unique 唯一
default 设置默认值
外键: 字段 类型 references 表名(字段)

添加表级约束

create table 表名(
    字段 字段类型,
    字段 字段类型(最大长度),
    constraint 名称 约束(字段),
    constraint 名称 foreign key(字段) references 表名(字段)
);

添加非空约束
alter table 表名 modify column 字段 字段类型 约束 ;

查看 mysql 支持的存储引擎
show engines;

事务的ACID 
A 原子性 , C 一致性 , I 隔离性 , D 持久性

事务

开启事务
set autocommit = 0;
start transaction;

编写一组事务语句
update account set 字段=值 where 条件;
... select,insert,update,delete ...
update account set 字段=值 where 条件;

结束事务

提交
commit;
回滚
rollback;

查看当前默认的隔离级别,mysql 8 版本
select @@transaction_isolation;

设置最低的隔离级别
set session transaction isolation level read uncommitted;

设置隔离级别为 读提交
set session transaction isolation level read committed;

设置为可重复读
set session transaction isolation level repeatable read;

---

2021-01-07