玩转SQL-持续更新中

爱折腾的波波
204 阅读2分钟

收集整理日常开发中常用的SQL操作，提升日常开发效率

sql操作大全

1. mysql命令行参数

mysql --help 能查看完整参数，下面列举常用的参数

1.1 常用参数

mysql参数含义
-h服务部署机器的hostname
-P服务部署机器的port,默认端口3306
-u账户名
-p账户密码(含特殊字符时需双引号包裹防止解析异常，如-p"&!@#")
-D指定数据库
-N等同于--skip-column-name, 命令执行结果里面不输出列名
(纯结果，不带表头)
--default-character-set命令行输出的字符集设置，--default-character-set=utf8
-e执行SQL命令, shell命令中 -e 可传入变量，动态执行SQL

mysqldump --help 能查看完整参数，下面列举常用的参数

mysqldump参数含义
-d只导出建表sql,没有数据
--skip-lock-tables不锁表，正在insert, update数据不会被备份。
mysqldump的默认锁定所有表。

1.2 示例

## 1. 常见登入
mysql -hbaidu.com -P3306 -uadmin -p"p&wd" -Ddbname

## 2. 执行指定sql文件(外部sql文件、字符串形式传入sql，变量形式传入sql)
mysql -hbaidu.com -uadmin -p"p&wd" -Ddbname < ./test.sql
mysql -hbaidu.com -uadmin -p"p&wd" -Ddbname -e 'select * from xxx'
mysql -hbaidu.com -uadmin -p"p&wd" -Ddbname -e "$sql_str"

## 3. -N的作用 是否输出列名
输出列名
mysql -hbaidu.com -uadmin -p"p&wd" -Ddbname -Ddb -e 'select event_day, id from test_db limit 2;'
+-----------+----+
| event_day | id |
+-----------+----+
| 20220122  |  1 |
| 20220122  |  2 |
+-----------+----+
mysql -hbaidu.com -uadmin -p"p&wd" -Ddbname -Ne 'select event_day, id from test_db limit 2;'
不输出列名
+----------+---+
| 20220122 | 1 |
| 20220122 | 2 |
+----------+---+

## 4. mysql导出操作(Usage: mysqldump [OPTIONS] database [tables])
mysqldump -hbaidu.com  -p"p&wd" databaseName tableName --skip-lock-tables > tableName.sql
mysqldump -hbaidu.com  -p"p&wd" -d databaseName tableName --skip-lock-tables > tableName.sql

2.常用函数和关键字

2.1 日期类

函数名用法
date_format
date类型字符串=>格式化日期		用法：date_format(createtime, "%Y-%m-%d")
示例：date_format(now(), "%Y") = '2022'
from_unixtime
数字时间戳=>格式化日期		用法：from_unixtime(时间戳, 'yyyy-MM-dd HH:mm:ss')
示例： from_unixtime(unix_timestamp(now()), '%Y-%m-%d')
unix_timestamp
string => timestamp秒		unix_timestamp() , unix_timestamp('20191212'),
now
返回当前日期，格式为YYYY-MM-DD HH-MM-SS		now() => ' 2022-09-04 14:41:05'
curdate
返回当前日期		curdate() = '2022-09-04'
curtime
返回当前时间		curtime() = '14:43:02'

2.2 字符串处理类

函数名用法
regexp'%xx'匹配xx结尾 'xx%'匹配xx开头 'xx_'匹配类似于xx*
like'%xx'匹配xx结尾 'xx%'匹配xx开头 'xx_'匹配类似于xx*
rlike支持正则的like关键字， rlike '^[0-9]+$'，匹配整数
unbase64 base64解密unbase64('加密字符串')
concat拼接字符串concat('a', 'b') = 'ab'
concat('a', 'b', 'c') = 'abc'
get_json_object json字符串解析get_json_object('{"search_id": 123}', '$.search_id') = '123'
如果json层级比较深可能需要get_json_object嵌套解析
parse_url
hive专用的url解析		parse_url(url, 'HOST')获取域名
parse_url(url, 'QUERY','id')获取query中的id字段
reverse 字符串倒置reverse('abc') = 'cba'
substring_indexsubstring_index(str,delim,count)
substring_index('12.19.22.12', '.', 2) = '12.19'
split 字符串分隔(返回结果数组)split('1,2,3', ',')[0] = '1'
translate 字符串全部替换translate('1,2,3', ',', '@') => "1@2@3"
trim字段处理首尾空白trim(' a ') = 'a'

2.3 数学类

函数名用法
rand随机生成0~1之间的随机数
大数据量查询的时,性能影响比较大,不推荐
max,minmax支持string大小判断，若要判断数字大小，建议类型转换
percentilepercentile(x, 0.8)获取80分位数据
percentile_approxpercentile_approx(ratio, array(0.2, 0.4, 0.8)) 获取20 40 80分位数据
ifnull(xx, yy)ifnull(xx, yy), xx=null,返回yy;xx!=null,返回xx
isnullisnull(NULL) = true,isnull(1) = false
if(x, "xx", "yy")同x ? 'xx' : 'yy'
isis null, is not null
inin (1, 2 ,3), not in (1,2,3)

3. 关键字

列举了常用关键字，便于日常查询

3.1 DDL关键字-数据定义语言

DDL这类SQL命令，大多用于创建、修改和删除数据库/表结构，但不直接作用于数据。 www.geeksforgeeks.org/sql-ddl-dql…

名称用法
create创建databases、table的关键字
drop删除数据库表结构 + 数据，不可恢复
truncate删除表数据，表结构还保存
alter修改数据库、表的结构、类型和备注等信息
comment修改备注信息
rename数据库、表重命名
-- drop truncate 高危操作，记录备份好再进行操作
rename table test_db to test_db2;


-- create comment用法
CREATE TABLE `test_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `event_day` date NOT NULL COMMENT '日期',
  `hostname` varchar(256) NOT NULL COMMENT '落地页hostname',
  `pv` bigint(20) NOT NULL COMMENT '页面pv',
  `show_time_avg` int(11) NOT NULL COMMENT '平均上屏时间',
  `os_type` int(11) DEFAULT NULL COMMENT '操作系统类型',
  PRIMARY KEY (`id`)
)

-- alter用法
--  1. 改字段类型
alter table test_tb modify column preload_pv bigint(20)

-- 2. 改字段备注
alter table test_tb modify preload_pv int(11) COMMENT '预加载pv';

-- 3. 改默认值-采用时间戳设置字段默认值，每次创建采取当前的时间戳(yyyy-mm-dd hh:mm:ss格式)
alter table test_tb timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- 4. 加字段alter table xx add 加字段
alter TABLE test_tb ADD field_name field_type DEFAULT 'xxx'

-- 5. 修改字段名称
alter table test_tb change os os_type int(11);

-- 6. 建立索引
    alter table `test_tb` add index(`event_day`)

3.2 DML关键字-数据操作

DML这类SQL命令，大多用于插入、更新和删除数据，直接作用于数据。 | 名称 | 用法| | --- | --- | | insert| 插入记录| | load | 外部插入记录| | update| 更新记录| | delete| 产出记录|

-- insert
insert INTO dishes (`name`, `desc`, `ingredients`, `createtime`) VALUES ('ssss', 'desc', '12,2,1', '2019-12-09')

-- update 1.单条记录修改
update dishes SET `name` = '${name}', `desc` = '${desc}', `ingredients` = '${ingredients}' WHERE `id` = ${id}

-- update 2. 字段之间拷贝
update test_tb th SET timestamp = DATE_FORMAT(th.stdate, "%Y-%m-%d %T")
批量修改表字段 从stdate拷贝到 timestamp

-- delte操作
DELETE FROM dishes WHERE id = 10

-- load操作-设置默认字段
load data local infile './test_load' into table xsb_test_table fields terminated by',' (id,name) set gender='male'

-- load操作-设置字符集编码
load data local infile './iOS.data' into table test_tb CHARACTER SET UTF8 fields terminated by',' (os, name, model, manufacturer);

3.3 DCL关键字-数据设置

-- 创建账户
CREATE USER 'user1' IDENTIFIED BY 'nadCvtRead';
-- 删除用户
DROP USER 'test@host';
-- 添加表权限
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 数据库名称.* TO 'user1';

3.4 DQL关键字-数据查询

DQL这类SQL命令，用于查询数据、计算、排序等操作. 这块内容较多 暂时以sql示例显示

-- 1. 简单case表达式： case when then end  相当于条件判断
case
    when a = 'xx' then 'xx'
    when a = 'yy' then 'yy
end as xx

-- 1.2 搜索case表达式, 相当于简化写法
case xxx
    when 'xx' then 'xx'
    when 'yy' then 'yy
end as xx

-- 2. having 如果不使用having，需要子查询外部，通过where进行过滤
-- 使用having：可以过滤 group by的结果\
-- 比如 having  count(1) > 10

-- 3. 单行转多行 explode(split(str, ';')) as item
select item,count(*) as pv
from (
    SELECT
        explode(split(str, ';')) as item
    FROM test t1
    WHERE t1.event_day in (20220310)
) tmp
group by item

4.基础类型

待更新

5.注意事项

问题原因
count不生效count是有条件的计数(不为null字段才会被记录)
原始数据和load数据库的数据不一致mysql load数据时，字符串会被超长阶段；较大的数会被提升到对应字段的最大值

参考资料

avatar
文章
阅读
粉丝