SQL使用
目录
- DBeaver怎么看存储在本地的密码
- 表结构管理语句使用
- 如何创建用户并授权
- 根据条件分组统计并筛选和排序
- 如何查询datetime类型数据
- 如何查询时间数据
- 如何统计数据库的表数量
- 如何统计数据库的字段数量
- 如何查询当前数据库的表名称、表注释等信息
- 如何查询数据库中的所有表、字段、字段类型以及注释等信息
- 如何统计某一张表的字段数量
- 如何导出数据
- 如何取近似值
- 如何根据不同状态赋不同值
- 将id组合成用单引号封装，并用逗号拼接的字符串应该怎么做
- 空条件要如何写
- 导出身份证号表示为科学计数法失真怎么处理
- 根据一张表更新另一张表数据要怎么做？
- 根据一张表数据插入另一张表怎么处理
- 怎么处理json数据
- 怎么初始化uuid
- 怎么使用正则查询
- mysql忘记root密码怎么办
- 如何去掉字段前后逗号
- JSON查询与替换
DBeaver怎么看存储在本地的密码
- 找到workspace的位置：窗口 > 首选项 > 常规 > 工作空间 > show full workspace path
- 找到密码文件的位置：
\General.dbeaver\credentials-config.json - 使用openssl对credentials-config.json文件解码
openssl aes-128-cbc -d \
-K babb4a9f774ab853c96c2d653dfe544a \
-iv 00000000000000000000000000000000 \
-in credentials-config.json | \
dd bs=1 skip=16 2>/dev/null
表结构管理语句使用
alter table `main`.`t_user` add column `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户ID';
alter table `main`.`t_user` drop column `user_id`;
如何创建用户并授权
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL ON `test_%`.* TO 'test'@'%';
FLUSH PRIVILEGES;
根据条件分组统计并筛选和排序
SELECT SUM(price * num) FROM goods_detail
WHERE state = 1
GROUP BY goods_id
HAVING SUM(price * num) > 10000
ORDER BY sum(price * num) DESC;
如何查询datetime类型数据
SELECT * FROM user WHERE create_time between '2020-12-17 10:31:00' and '2020-12-18 10:32:00';
-- 避免使用以下语句，因为在where中使用函数会造成不走索引，造成性能下降。必须用的话要放在“=”右边
-- SELECT * FROM user WHERE date( create_time )= '2020-12-17';
-- SELECT * FROM user WHERE time( create_time ) between '20:45:47' and '20:46:08';
-- SELECT * FROM user WHERE minute( create_time )=46;
-- SELECT * FROM user WHERE second( create_time ) between 2 and 12;
-- SELECT * FROM `order` WHERE TIMESTAMPDIFF(MINUTE, create_time, now()) < 10; -- 查询10分钟以内创建的订单
索引案例
表结构
CREATE TABLE `t_bank_statement` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`account_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '账户Id',
`money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '交易金额，支出负，收入正',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_account_ids` (`account_id`),
KEY `idx_create_at` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='银行流水表'
走索引
不走索引
走指定索引
如何查询时间数据
-- 查询当月
SELECT * FROM user WHERE DATE_FORMAT( birth, '%Y-%m' ) = DATE_FORMAT( CURDATE( ), '%Y-%m' );
-- 查询前一月
PERIOD_DIFF( DATE_FORMAT( now( ) , '%Y%m' ) , DATE_FORMAT( `create_time` , '%Y%m' ) ) = 1
-- 查询指定月
SELECT * FROM user WHERE DATE_FORMAT( birth, '%Y-%m' ) = '2021-06';
-- 查询前一天
DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 day)
如何统计数据库的表数量
SELECT
TABLE_SCHEMA AS '数据库名称',
count( TABLE_NAME ) AS '表数量'
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA IN ( '${database_name}' )
GROUP BY
TABLE_SCHEMA;
如何统计数据库的字段数量
SELECT
TABLE_SCHEMA AS '数据库名称',
count( COLUMN_NAME ) AS '字段数量'
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA IN ( '${database_name}' )
GROUP BY
TABLE_SCHEMA;
如何查询当前数据库的表名称、表注释等信息
SELECT
TABLE_NAME,
TABLE_COMMENT
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA =(SELECT DATABASE())
ORDER BY
TABLE_NAME;
如何查询数据库中的所有表、字段、字段类型以及注释等信息
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_COMMENT
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA IN ( '${database_name}' );
如何统计某一张表的字段数量
SELECT
count(*)
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = '${database_name}'
AND TABLE_NAME = '${table_name}'
如何导出数据
SELECT * FROM user WHERE name like '张%' INTO OUTFILE 'D:/user.cvs';
如何取近似值
-- CEIL向上取整
-- FLOOR向下取整
-- ROUND四舍五入
SELECT
w.sid,
s.name AS name,
IFNULL( COUNT( a.id ), 0 ) AS times,
IFNULL( SUM( a.stars ), 0 ) AS stars,
IFNULL( COUNT( DISTINCT ( a.wid )), 0 ) AS wts,
ROUND( IFNULL( SUM( a.stars ), 0 ) * 20 / IFNULL( COUNT( DISTINCT ( a.wid )), 0 ), 2 ) AS avgscore
FROM
asst a
JOIN work w ON a.wid = w.id
JOIN staff s ON w.sid = s.id
WHERE
DATE_FORMAT( w.clzdate, '%Y-%m' )= '2021-05'
GROUP BY
w.sid
HAVING
COUNT(DISTINCT ( a.wid ))>= 5
ORDER BY
avgscore DESC,
wts DESC
如何根据不同状态赋不同值
SELECT
DATE_FORMAT( create_time, '%Y-%m-%d' ),
SUM( CASE WHEN STATUS = 1 THEN 1 ELSE 0 END ) AS '1',
SUM( CASE WHEN STATUS = 2 THEN 1 ELSE 0 END ) AS '2'
FROM
t_order
GROUP BY
DATE_FORMAT( create_time, '%Y-%m-%d' )
ORDER BY
create_time DESC
LIMIT 1,10
将id组合成用单引号封装，并用逗号拼接的字符串应该怎么做
SELECT GROUP_CONCAT("'",id,"'") FROM `user`;
空条件要如何写
is null 与 is not null
='' 与 <>''
ifnull(col, '') = '' 与 ifnull(col, '') <> ''
导出身份证号表示为科学计数法失真怎么处理
- 使用concat函数
SELECT
id as '用户ID',
concat('\t', id_card_num) as '身份证号'
FROM
t_user
对csv有效，但它改变了身份证号码的长度，严格要求格式的情境不适用。
- excel导入设置
数据 - 导入数据 - 选“直接打开数据文件”后选择数据源 - 选择编码（默认简体中文GBK）- 文本类型选分割符号 - 分割符号选“Tab键” & 逗号 - 左右切换数据列后选择数据类型为：文本 - 完成
根据一张表更新另一张表数据要怎么做？
UPDATE t_user u
JOIN t_clz c ON u.user_id = c.user_id
set c.user_name = u.user_name;
根据一张表数据插入另一张表怎么处理
INSERT INTO t_user_bak(`id`, `user_name`, `addr`)
select u.`id`, u.`user_name`,
(select province_name + city_name from t_address where user_id = u.id)
from t_user u;
怎么处理json数据
-- 初始生成
select CONCAT('{"total":', CAST(r.score as decimal(5, 2)),
', "name":"课程为', r.course_name,
'", "desc":"', case
when r.score>=85 then '优秀'
when r.score>=60 and r.score < 85 then '及格'
ELSE '不及格'
end,
'"}')
from t_result r
-- 更改
UPDATE t_result ro SET ro.ext_info =
(
SELECT mid.ext_info FROM (
SELECT ri.id, REPLACE(
ri.ext_info,
'}',
CONCAT(',"extDesc":' , '"风险小"', RIGHT(ri.ext_info,1))
) ext_info
FROM t_result ri
) mid WHERE mid.id = ro.id
) where ro.id=1
怎么初始化uuid
update user
set valid_code = (select UUID())
where valid_code is null or valid_code = '';
update user
set valid_code = REPLACE(valid_code, '-', '');
怎么使用正则查询
-- 查询已数字结尾的字符串
select * from t_web where url rlike('[0-9]+$');
mysql忘记root密码怎么办
环境1：windows10 + mysql8.0以上
1.使用管理员权限打开命令提示符，输入：net stop mysql
2.待mysql服务停止后，输入：mysqld --shared-memory --skip-grant-tables，此时命令提示符窗口处于锁定状态。
3.重新以管理员权限打开新的命令提示符窗口，输入： mysql -uroot -p 后回车，提示输入密码时直接按回车进入。
4.输入： use mysql;
5.输入： update user set authentication_string=’’ where user=‘root’; ，将authentication_string置空。
注：在mysql8.0以上版本，
update mysql.user set password=‘newpassword’ where user=‘root’;
和
update mysql.user set password=PASSWORD(‘newpassword’) where User=‘root’; 这两条命令已经不起作用了。
6. quit; 退出此次连接
7. mysql -uroot -p 后回车，提示输入密码时直接按回车进入。
8.输入： alter user ‘root’@‘localhost’ identified by ‘newpassword’; newpassword是要设的新密码。
9.如果提示设置成功，则输入： flush privileges; 。完成后Ctrl+Z退出mysql，使用新密码重新登录即可。
注：此步骤切不可省略！！！
10.如果提示不成功，则关闭两个命令提示符，重新打开一个管理员权限的命令提示符窗口，输入： mysql -uroot -p 后回车，输入密码时直接按回车进入（因为此时密码已被置空）。
11.重复步骤4，步骤8，步骤9。
12.如果使用Navicat等可视化界面连接数据库时连接不成功的情况，如错误信息：2059。此时需要更改验证方式，使用命令提示符登录数据库，输入： ALTER USER’root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘yourpassword’; yourpassword是你想要设置的密码。
13.提示成功后，重复步骤7，再次打开Navicat连接即可。
如何去掉字段前后逗号
select s.id, s.category_ids,
REPLACE(LTRIM(REPLACE(s.category_ids,',',' ')),' ',',') as '去掉字符串前面逗号',
REPLACE(RTRIM(REPLACE(s.category_ids,',',' ')),' ',',') as '去掉字符串后面逗号',
REPLACE(TRIM(REPLACE(s.category_ids,',',' ')),' ',',') as '去掉字符串前后逗号'
from scale s;
-- 子查询中要用FIND_IN_SET函数
select
s.id,
s.name,
(select count(*) from question where sid = s.id) as 数量,
(CASE WHEN s.type = 1 THEN '类型1'
WHEN s.type = 2 THEN '类型2'
ELSE '未知' END) as 类型,
(select GROUP_CONCAT(val) from dict where FIND_IN_SET(id, (REPLACE(TRIM(REPLACE(s.category_ids,',',' ')),' ',',')))) as 分类
from scale s;
JSON查询与替换
-- 统计
SELECT count(*)
FROM your_table
WHERE JSON_VALID(param) AND JSON_EXTRACT(param, '$.username') = 'zhangsan';
-- 修改value
UPDATE your_table
SET param = JSON_REPLACE(param, '$.password', '123456')
WHERE JSON_VALID(param) AND JSON_EXTRACT(param, '$.username') = 'zhangsan';
-- 增加key
UPDATE your_table
SET param = JSON_SET(param, '$.age', 18)
WHERE id = 1;
-- 查询json list中某字段=某值的记录
SELECT *
FROM your_table
WHERE JSON_CONTAINS(json_column, '{"id": 1}');
浙公网安备 33010602011771号