SQL的一些常用知识

最新推荐文章于 2024-01-10 18:35:20 发布

PH = 7

最新推荐文章于 2024-01-10 18:35:20 发布

阅读量246

点赞数

分类专栏： sql语言文章标签： sql

本文链接：https://blog.csdn.net/qq_23095607/article/details/85037493

版权

sql语言专栏收录该内容

7 篇文章 2 订阅

订阅专栏

数据库的默认排序，当没有筛选条件或者当所有的筛选条件一致时，从数据库提取的顺序就是数据库的存储顺序。
1.别名以及自定义行
例如：
select allot_id line_id from wplaces where line_id is null
此处就是给allot_id 起别名为line_id
自定义
在这里插入图片描述


SELECT a.area_name,'好吃的' AS '特色' FROM am_area a WHERE a.area_id = '110102'

SELECT a.area_name,'goodFood' AS 'special' FROM am_area a WHERE a.area_id = '110102'

在这里插入图片描述

自定义的行只接受bool或者数字
2.相同的表即使再次筛选，也是新的表
如：

SELECT
	a.*
FROM
	am_area a,
	am_area b
WHERE
	a.area_code = b.area_code
AND b.area_level = 3

等价于

SELECT
	a.*
FROM
	am_area a
WHERE
	a.area_code = (
		SELECT
			b.area_code
		FROM
			am_area b
		WHERE
			b.area_level = 3
	)

比较复杂的应用为：
am_area
在这里插入图片描述
am_org

am_user

am_user_org

查询员工11同一个市的员工

SELECT
	c2.user_name
FROM
	am_area a1,
	am_area a2,
	am_area a3,
	am_org b1,
	am_org b2,
	am_user c1,
	am_user c2,
	am_user_org d1,
	am_user_org d2
WHERE
	c1.user_name = '员工11'
AND c1.user_id = d1.user_id
AND d1.org_id = b1.org_id
AND b1.area_id = a1.area_id
AND a1.area_code LIKE CONCAT(a2.area_code, '%')
AND a2.area_level = 3
AND a3.area_code LIKE CONCAT(a2.area_code, '%')
AND b2.area_id = a3.area_id
AND b2.org_id = d2.org_id
AND d2.user_id = c2.user_id

结果
在这里插入图片描述

3.GROUP BY的正确使用
在这里插入图片描述

如：选取相同的OnLine，最近的登录时间的 QQID

SELECT a.QQID , MAX(a.lastLogTime)  FROM qquser  a GROUP BY a.`OnLine`

在这里插入图片描述
这样得到的QQID是不对的
应该先选出OnLine和他的最大的时间，然后去根据这些时间去选出QQID

SELECT q.QQID FROM (
SELECT a.`OnLine` AS line,MAX(a.lastLogTime) AS logtime 
FROM qquser a GROUP BY a.`OnLine`
)AS x INNER JOIN qquser AS q ON x.line = q.OnLine and x.logtime = q.lastLogTime

在这里插入图片描述

4.重复数据只取一条
SELECT DISTINCT * FROM TABLE
sqlserver/mysql 取前10条数据： select top 10 * from table
oracle取前10条数据：select * from table where rownum < 11(注意rownum是oracle默认排序的行号，是rownum不显示的列，如果有其他排序条件，就会出问题，如：select * from table where rownum < 11 and user = ‘张三’ 这样得到的就是前十条中姓名为张三的人。)
5.关于union 和union all的区别
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
union all比union快很多
例子：
在这里插入图片描述

SELECT *  FROM student WHERE age > 22 UNION SELECT * FROM student WHERE age < 30

SELECT *  FROM student WHERE age > 22 UNION ALL SELECT * FROM student WHERE age < 30

在这里插入图片描述

6.关于多列筛选的问题***
1.首先明确group by 分组函数，group by 什么，相同的列就会自动合并，并且会响应聚合函数
2.多列筛选其实就是将多列按照一列来处理，将相同的合并，并且响应聚合函数
3.那么可以写个例子：
在这里插入图片描述
7.统计数据库中各表的列数
select count(*) from syscolumns s
where s.id = object_id(‘表名’);
–查询某个表的列数

select name ,id from sysobjects a where xtype=‘u’
–查询所有的表名

select name ,id ,(select count(*) from syscolumns s
where s.id = a.id)from sysobjects a where xtype=‘u’
order by name desc
–数据库中各表的列数
8.left jion , right join ,inner jon的用法

inner join 等同于 join

根据关联条件，取两个表中的公共部分,例如：
grade表：

student表：

SELECT * FROM grade a INNER JOIN student b ON a.id = b.Grade_id

等价于

SELECT * FROM grade a ,student b WHERE a.id = b.Grade_id

结果为：
在这里插入图片描述
常见例子：

MySQL如何查询每个分组里第一条、最后一条、最大一条的数据
查询每种水果最便宜额价格
select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

等价于：
select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x , fruits as f where f.type = x.type and f.price = x.minprice;
+--------+----------+-------+
| type   | variety  | price |
+--------+----------+-------+
| apple  | fuji     |  0.24 | 
| cherry | bing     |  2.55 | 
| orange | valencia |  3.59 | 
| pear   | bartlett |  2.14 | 
+--------+----------+-------+

left join

保留left join 前的全部数据，left join后的数据没有的用null补齐，所以这个很有可能导致右边的数据不全

SELECT * FROM grade a LEFT JOIN student b ON a.id = b.Grade_id

在这里插入图片描述

right join

保留right join 后边的全部数据，前边的数据用null补齐，所以这个很有可能导致右边的数据不全
例如：

SELECT * FROM grade a RIGHT JOIN student b ON a.id = b.Grade_id

在这里插入图片描述
参考链接

关于join系列的数据顺序问题

left join	左边的数据不能少，且结果是以左边的顺序为准
right join	右边的数据不能少，且结果是以右边的顺序为准
inner join	查询交集，且顺序不定，或按照其中一边，或是随机的

下面进行例子的相关说明：
正常的查询SQL如下：

SELECT
	b1.QQID,
	b1.NickName,
	b1.Age
FROM
	(
		SELECT
			*
		FROM
			baseinfo
		ORDER BY
			Age DESC
	) b1
RIGHT JOIN (
	SELECT
		*
	FROM
		baseinfo
	ORDER BY
		Age ASC
) b2 ON b1.QQID = b2.QQID;

在这里插入图片描述
当进行左插入时，是降序

当右插入时，是升序

inner join是乱序，

另外说一下插入操作，ON和WHERE的区别，

ON是对附表插入的限制
where是对参与的所有表（合并后的表）的限制

以left join说明

SELECT b.Age FROM qquser a LEFT JOIN baseinfo b ON a.QQID = b.QQID AND b.Age = 32;

SELECT b.Age FROM qquser a LEFT JOIN baseinfo b ON a.QQID = b.QQID WHERE b.Age = 32;

在这里插入图片描述
所以结果为：

关于update的返回值

update返回值指的是受影响的行数，也可以认为是符合条件查询的条数，因为即使相同的数据你更新很多遍，返回的数据还是一样的，下面是本人亲测的数据

=================================================
UPDATE qquser SET OnLine = '3'  where LEVEL = 20
8
***************************************************
=================================================
UPDATE qquser SET OnLine = '3'  where LEVEL = 20
8
***************************************************
=================================================
UPDATE qquser SET OnLine = '3'  where LEVEL = 20
8
***************************************************
=================================================
UPDATE qquser SET OnLine = '3'  where LEVEL = 20
8
***************************************************
=================================================
UPDATE qquser SET OnLine = '3'  where LEVEL = 20
8
***************************************************

=================================================
UPDATE qquser SET OnLine = '3'  where QQID = 286207
1
***************************************************

=================================================
UPDATE qquser SET OnLine = '3'  where QQI = 286207
0
***************************************************
=================================================
UPDATE qquser SET OnLine = '3'  where QQID = 286208
0
***************************************************

可以看到，相同的数据在更新了很多遍后依旧会返回8，因为数据库中LEVEL = 20的有8个，同理QQID = 286207有一个，where QQI = 286207没有，会报错然后会返回0
QQID = 286208不会报错，但是数据库里没有这个值，所以返回0

结论：

insert：插入n条记录，返回影响行数n。（n>=1，n为0时实际为插入失败）

update：更新n条记录，返回影响行数n。（n>=0）

delete：删除n条记录，返回影响行数n。（n>=0）

可能navicat或者sqlyog给出的相应结果不同，但是代码返回值肯定是上面的情况，请放心食用

多条件排序

SELECT  * from b ORDER BY a DESC , b DESC

排序前
在这里插入图片描述
排序后

**

要习惯用exists (not exists) 代替 in (not in) ，因为前者比后者执行速度快

**
相关解释1，相关解释2

相关用法：
在这里插入图片描述

SELECT a.*  FROM am_area a WHERE  a.area_code 
in 
(SELECT b.area_code FROM am_area b WHERE b.area_code LIKE CONCAT('000100010001','%'))

等价于

SELECT a.*  FROM am_area a WHERE EXISTS (
SELECT * from am_area b 
WHERE a.area_code = b.area_code AND b.area_code LIKE CONCAT('000100010001','%')
)

结果
在这里插入图片描述

SELECT *  FROM am_area a WHERE  a.area_code 
NOT in 
(SELECT b.area_code FROM am_area b WHERE b.area_code LIKE CONCAT('000100010001','%'))

等价于

SELECT *  FROM am_area a WHERE NOT EXISTS (
SELECT b.area_code FROM am_area b WHERE a.area_code = b.area_code AND b.area_code LIKE CONCAT('000100010001','%')
)

结果
在这里插入图片描述

union all ,union 和 or （sql优化）

对于索引列来最好使用union all，因复杂的查询【包含运算等】将使or放弃索引而全表扫描，除非你能确定or会使用索引

对于只有非索引字段来说你就老老实实的用or ，因为 非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数

对于既有索引字段【索引字段有效】又包含非索引字段来时，按理你也使用or 或者union all 都可以但是推荐使用or。

相关的使用等价例子如下：
在这里插入图片描述

SELECT *  FROM am_area a WHERE a.area_id LIKE CONCAT('11010','%')
UNION 
SELECT *  FROM am_area a WHERE a.area_code LIKE CONCAT('000100010001000','%')
UNION 
SELECT *  FROM am_area a WHERE a.area_leaf = 0

等价于

SELECT *  FROM am_area a WHERE (
a.area_id LIKE CONCAT('11010','%') OR 
a.area_code LIKE CONCAT('000100010001000','%') OR
a.area_leaf = 0
)

一些实用的数据库函数

逗号分隔的查询---------------FIND_IN_SET

要找到画线的这条数据包含的所有实体
错误的使用方法

select a.*  FROM  am_area  a WHERE a.area_code FIND_IN_SET(a.area_code,(
SELECT b.area_code  FROM am_area b WHERE b.area_id = '110230'
))

正确的使用方法

SELECT a.*  FROM am_area a,am_area b 
WHERE FIND_IN_SET(a.area_code,b.area_code) AND b.area_id = '110230'

或者

SELECT a.*  FROM am_area a JOIN am_area b ON FIND_IN_SET(a.area_code,b.area_code) AND b.area_id = '110230'

结果
在这里插入图片描述

对取出的按条件筛选重新赋值------------case when then else end

选出地区及地区等级

SELECT a.area_name  '地区名', 
CASE  a.area_level  
WHEN 1 THEN '国' 
WHEN 2 THEN '省'
WHEN 3 THEN '市'
WHEN 4 THEN '区'
ELSE '其他称谓' END AS '等级'
FROM am_area a

结果：
在这里插入图片描述

向表中加一列

alter table park add column 'trees' varchar(20) not null;

注意：tableName 和列明不要加引号不然会报错

PH = 7

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL的一些常用知识

1.别名例如：select allot_id line_id from wplaces where line_id is null此处就是给allot_id 起别名为line_id2.关于相同的字符串只取一个例如：我们密码相同的只取一条数据，那么数据库语言应该是3.重复数据只取一条SELECT DISTINCT * FROM TABLE4.关于union 和union all的...
复制链接

扫一扫