一千万的数据如何查询的?
where限定范围.聚合函数不能用在where子句.

with rollup放在结尾,用于对分组结果集再次做汇总计算


写在where子句比having子句好,过滤
sql执行顺序:
FROM-->WHERE-->GROUP BY-->HAVING-->SELECT-->DISTINCT-->UNION-->ORDER BY-->limit
limit 是随机抽取n条, 插入时会导致重复数据. 配合order by使用
CAST(createtime AS char) as createtime, 转换timestamp类型为 string
select CURRENT_TIMESTAMP, 获取当前时间 2021-05-13 20:39:52
load data infile ‘D:/Python workspace/user.txt’ into table user(username, salt, pwd)
mysql 默认wait_timeout=28800(8h)
update dps set es_status = status where tid = 202
1.sql
1. 有两张数据表A和B,AB都有字段ID,统计只在一张表里的ID列表
SELECT A.id,B.id FROM todos A LEFT JOIN userinfo B ON A.id = B.id WHERE B.id IS NULL UNION SELECT A.id,B.id FROM todos A RIGHT JOIN userinfo B ON A.id = B.id WHERE A.id IS NULL
2. where 1=1 与 count
查询是否存在记录,不关心有多少条记录
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1
Mysql中where 1=1 和count(0) 使用小技巧
mysql中使用 where 1=1和 1=0 的作用
where 1=0; 这个条件始终为false,结果不会返回任何数据,只有表结构,可用于快速建表
“SELECT * FROM strName WHERE 1 = 0”;
该select语句主要用于读取表的结构而不考虑表中的数据,这样节省了内存,因为可以不用保存结果集。
create table newtable as select * from oldtable where 1=0;
创建一个新表,而新表的结构与查询的表的结构是一样的。
count
count(*) count(0) count(1) 没有区别,都会统计包含null的值的项,但 count(field) 则会在查询字段时忽略字段值为null的项。

统计表长时,用count() 就可以了,但要统计字段时,需要注意这个问题。
count(*)&count(1)&count(列名)执行效率比较:
(1)如果列为主键,count(列名)效率优于count(1)
(2)如果列不为主键,count(1)效率优于count(列名)
(3)如果表中存在主键,count(主键列名)效率最优
(4)如果表中只有一列,则count(*)效率最优
(5)如果表有多列,且不存在主键,则count(1)效率优于count(*)
3. mysql实现rownum,使用mysql查询显示行号
Mysql查询结果带行号【带解析】
SELECT @rowno:=@rowno+1 as rowno,r.* from grade_table r ,(select @rowno:=0) t;
用@rowno这自定义变量显示行号,简洁而实用,
在这里插入图片描述
如果有按照某个字段排序,行号会不规则排列,换成先排序,外层加上行号会更加合适。
SELECT @ROWNO := @ROWNO + 1 AS ROWNO, T.*
FROM (SELECT T.ARTICLE_TITLE, T1. NAME, T.ARTICLE_CREATEDATE
FROM T_ARTICLE T
LEFT JOIN T_ARTICLE_TYPE T1
ON T.TYPEID = T1. NAME
WHERE T.ARTICLE_TITLE LIKE '%博士%'
ORDER BY ARTICLE_CREATEDATE DESC) T,
(SELECT @ROWNO := 0) T3
ORDER BY ROWNO
MySQL8支持窗口函数的

子查询求top N

2. MySQL3种连接
- 交叉连接 cross join
无条件时,返回笛卡尔积(任意组合)
(查询2张表的字段,不限定where条件时,也返回笛卡尔积)
使用where时,相当于内连接
SELECT * FROM userinfo CROSS JOIN todos WHERE userinfo.id=todos.id;
- 内连接
SELECT * FROM userinfo INNER JOIN todos ON userinfo.id=todos.id;
或
SELECT * FROM userinfo,todos WHERE userinfo.id=todos.id;
3.自然连接 natural join
连接字段相同时可用. 相当于简化写法,会过滤重复字段
NATURAL LEFT JOIN
NATURAL RIGHT JOIN
NATURAL JOIN
select * from userinfo NATURAL JOIN todos;

4. using
用来指定连接字段
也会对连接结果进行整理,整理方式同自然连接
SELECT * FROM userinfo INNER JOIN todos USING(id);
5.大小写
linux下,数据库名,表名,表的别名是区分大小写的
mysql默认不区分大小写,通过如下几种方法区分大小写:
- 使用binary关键字
SELECT * FROM table_name WHERE word binary LIKE ‘F%’;
建表时, name varchar(10) binary - 查询时指定字符集
SELECT * FROM table_name WHERE word COLLATE utf8_bin LIKE ‘F%’;
3.建表时用区分大小写字符集
*_bin: 表示的是binary case sensitive collation,也就是说是区分大小写的
*_cs: case sensitive collation,区分大小写
*_ci: case insensitive collation,不区分大小写
6. other
order by支持相对位置
like是谓词(predicate)
7.计算字段
- 拼接字段 CONCAT
SELECT CONCAT(username,password) FROM userinfo;

SELECT CONCAT(username,'(',password,')') FROM userinfo;

trim:去除数据中的空格. rtrim去除右边的空格, ltrim去除左边的空格
8.函数
trim:去除数据中的空格. rtrim去除右边的空格, ltrim去除左边的空格
upper():
- select列中出现的列名和表达式,也会出现在group by 子句中。
select列出现的聚合函数,但是group by子句中不能有任何的聚集函数。having中可以出现
where 过滤行,having过滤分组(where分组前过滤)
一般在使用group by子句时,应该也给出order by子句,保证正确排序
9. 查询b库c表所有字段插入到当前库c表
INSERT INTO c
SELECT * from b.c limit 1000,100000
10. 参数配置
POOL_RECYCLE: 多少秒后自动回收连接。这对 MySQL 是必要的, 它默认移除闲置多于 8 小时的连接. -1 表示永不回收
11. 获取过去一段时间的数据
- DATE_SUB函数
# 获取过去30分钟内有更新的数据(精确到秒)
SELECT * FROM data WHERE updatetime>=DATE_SUB(now(), INTERVAL 30 MINUTE)
- unix_timestamp
## 获取 当前小时数 与updatetime 相差1h 的记录
SELECT * FROM data WHERE (unix_timestamp(DATE_FORMAT(now(),'%Y-%m-%d %H')) - unix_timestamp(DATE_FORMAT(updatetime,'%Y-%m-%d %H')))=3600
select sum(docsize) from (select count(docid) as docsize from preprocess where docid in (select docid from data where is_valid = 1) and status = 17 group by docid having count(docid)> 1) tmp ;
12.复制表结构/数据
1. 复制表结构及其数据:
create table table_name_new as select * from table_name_old
2. 只复制表结构:
create table table_name_new as select * from table_name_old where 1=2;
或者:
create table table_name_new like table_name_old
3. 只复制表数据:
如果两个表结构一样:
insert into table_name_new select * from table_name_old
如果两个表结构不一样:
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
13.mysql的alter,change,modify用法
# 修改某一列,列名不改变,用modify
alter table a MODIFY `createtime` timestamp default CURRENT_TIMESTAMP;
ALTER table `order` MODIFY `order_fee` decimal(14,4) DEFAULT NULL;
# 修改某一列,列名要改变,用change
ALTER TABLE `apps`
CHANGE COLUMN `at_p1` `at_p2` decimal(8,4) NULL DEFAULT NULL COMMENT 'test111' AFTER `status_id`;
# 增加一列
ALTER TABLE `apps`
ADD COLUMN `op_date` datetime(0) NULL COMMENT '创建时间' AFTER `op_user`;
本文围绕SQL和MySQL展开,介绍了多种实用技巧。包括统计只在一张表中的ID列表、where 1=1与count的使用、mysql实现rownum显示行号等。还涉及MySQL的连接方式、大小写处理、计算字段、函数运用,以及数据插入、参数配置、重复记录查询和关联更新等内容。
2976

被折叠的 条评论
为什么被折叠?



