sql语句

本文围绕SQL和MySQL展开,介绍了多种实用技巧。包括统计只在一张表中的ID列表、where 1=1与count的使用、mysql实现rownum显示行号等。还涉及MySQL的连接方式、大小写处理、计算字段、函数运用,以及数据插入、参数配置、重复记录查询和关联更新等内容。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一千万的数据如何查询的?
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默认不区分大小写,通过如下几种方法区分大小写:

  1. 使用binary关键字
    SELECT * FROM table_name WHERE word binary LIKE ‘F%’;
    建表时, name varchar(10) binary
  2. 查询时指定字符集
    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.计算字段

  1. 拼接字段 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. 获取过去一段时间的数据

  1. DATE_SUB函数
# 获取过去30分钟内有更新的数据(精确到秒)
SELECT *  FROM data WHERE updatetime>=DATE_SUB(now(), INTERVAL 30 MINUTE)
  1. 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`;

14. GROUP_CONCAT 和 FIND_IN_SET

MySQL 一个字段,用’,'隔开,存储多个id,关联查询

15. SQL查询重复记录 sql 查重

16.mysql之关联更新(update join,用b表更新a表记录)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值