SQL查询语句备忘录

有关于SQL查询的相关语句和语法的记录!备忘与复习用

1、SQL多表联合查询

select a.字段1,a.字段2,b,字段2 from 表1 a,表2 b where a.字段1 =b.字段1

2、 字段排序 先依字段1升序排,然后再按字段2降序排

SELECT 字段1, 字段2,字段3 FROM 表1 ORDER BY 字段1, 字段2 DESC

3、SQL查询数据库每个表的记录数

USE JYDB                   -- JYDB为你所在的数据库
GO
select b.[name] '表名',max(a.rowcnt) '记录数'  
from sysindexes a  
join sys.objects b on b.object_id=a.id  
where b.type='U'  
group by b.[name]
order by '记录数'

4、 重复记录记数排序
```sql
select  count(F_ComputerName) as '重复次数',F_ComputerName from Pc_UseRecord group by F_ComputerName  having count(*)>1 order BY '重复次数' desc

5、mysql重新分配序号

DROP PROCEDURE IF EXISTS u_head_and_low_pro;
DELIMITER $$
CREATE PROCEDURE u_head_and_low_pro()
BEGIN
    DECLARE n INT DEFAULT 1880;
    DECLARE m INT DEFAULT 0;
    WHILE m < n DO
        SET m = m + 1;
UPDATE  booksection AS e 
SET e.section_id =m,
e.section_addtime = '2020-06-04 00:00:00'
WHERE e.book_id =18 and e.section_addtime <'2020-06-04 00:00:00' 
order by section_id asc LIMIT 1;  
END WHILE;
END $$
DELIMITER ;
CALL u_head_and_low_pro();