郑州网站优化公司哪家好,海口专注海南网站建设,银河星宇 网站建设,通过邮箱查注册网站1、sql按照指定数组顺序查询数据 2、数据库SQL 某字段按首字母排序
基础语句
1、SELECT#xff1a;选择数据表中的数据
SELECT column_name1, column_name2 FROM table_name;2、WHERE#xff1a;筛选符合条件的数据
SELECT column_name FROM table_name WHERE column_nam…1、sql按照指定数组顺序查询数据 2、数据库SQL 某字段按首字母排序
基础语句
1、SELECT选择数据表中的数据
SELECT column_name1, column_name2 FROM table_name;2、WHERE筛选符合条件的数据
SELECT column_name FROM table_name WHERE column_name value;3、AND用于连接 WHERE 子句中的多个条件
SELECT column_name FROM table_name WHERE column_name1 value1 AND column_name2 value2;4、OR用于连接 WHERE 子句中的多个条件其中至少一个条件必须成立
SELECT column_name FROM table_name WHERE column_name1 value1 OR column_name2 value2;5、IN筛选符合指定值中任意一个的数据
SELECT column_name FROM table_name WHERE column_name IN (value1, value2, value3);6、NOT IN筛选不符合指定值中任意一个的数据
SELECT column_name FROM table_name WHERE column_name NOT IN (value1, value2, value3);7、LIKE筛选符合指定模式的数据
SELECT column_name FROM table_name WHERE column_name LIKE pattern;SELECT column_name FROM table_name WHERE column_name LIKE %value%8、NOT LIKE筛选不符合指定模式的数据
SELECT column_name FROM table_name WHERE column_name NOT LIKE pattern;SELECT column_name FROM table_name WHERE column_name NOT LIKE %value%9、BETWEEN筛选在指定范围内的数据
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;9、NOT BETWEEN筛选不在指定范围内的数据
SELECT column_name FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;10、ORDER BY按指定列进行排序
SELECT column_name FROM table_name ORDER BY column_name ;//默认升序
SELECT column_name FROM table_name ORDER BY column_name DESC;//降序11、GROUP BY按指定列进行分组
SELECT column_name1,column_name2 FROM table_name GROUP BY column_name1;备注 从 MySQL 5.7.5 开始默认 SQL 模式包括 ONLY_FULL_GROUP_BY。 在 5.7.5 之前MySQL 不检测函数依赖并且默认不启用 ONLY_FULL_GROUP_BY。所以select的内容如果不在group by 中将会报错可以参考这里的解决方法
DISTINCT 基于指定列的唯一值去重
SELECT DISTINCT(column_name1) FROM table_name;区别distinct和group by具体区别点击查看
12、HAVING筛选分组后符合指定条件的数据
SELECT column_name1, SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) 100;进阶一下
13、JOIN连接多个数据表
SELECT table1.column_name1, table2.column_name2 FROM table1 JOIN table2 ON table1.column_name1 table2.column_name1;14、LEFT JOIN连接左侧数据表并包括右侧数据表中与左侧数据表中没有匹配项的行
SELECT table1.column_name1, table2.column_name2 FROM table1 LEFT JOIN table2 ON table1.column_name1 table2.column_name1;//右连接
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name table2.column_name;15、UNION合并多个查询结果
SELECT column_name FROM table1 UNION SELECT column_name FROM table2;16、EXISTS检查子查询结果是否存在
SELECT column_name FROM table1 WHERE EXISTS (SELECT column_name FROM table2 WHERE column_name value);//NOT EXISTS检查子查询结果是否不存在
SELECT column_name FROM table1 WHERE NOT EXISTS (SELECT column_name FROM table2 WHERE column_name value);17、AVG计算平均值
SELECT AVG(column_name) FROM table_name;18、COUNT计算数据行数
SELECT COUNT(*) FROM table_name;19、MAX计算最大值
SELECT MAX(column_name) FROM table_name;20、MIN计算最小值
SELECT MIN(column_name) FROM table_name;21、SUM计算总和
SELECT SUM(column_name) FROM table_name;22、CASE根据条件返回不同的结果
SELECT column_name, CASE WHEN column_name value1 THEN result1 WHEN column_name value2 THEN result2 ELSE result3 END AS new_column_name
FROM table_name;23、ROW_NUMBER按照指定列进行分组并排序(就是对column_name字段进行分组并在组内对column_name2字段降序排)
SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name2 DESC) FROM table_name;示例如下 24、DENSE_RANK按照指定列进行分组并排序相同值的行具有相同的排名不跳过下一个排名RANK跳过下一个排名
SELECT column_name, DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name2 DESC) FROM table_name;示例如下 25、FIRST_VALUE返回分组中第一个行的列值
SELECT column_name, FIRST_VALUE(column_name2) OVER (PARTITION BY column_name ORDER BY column_name2) FROM table_name;//返回分组中最后一个行的列值
SELECT column_name, LAST_VALUE(column_name2) OVER (PARTITION BY column_name ORDER BY column_name2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM table_name;26、CONCAT连接多个字符串值为一个字符串
SELECT CONCAT(column_name1, , column_name2) FROM table_name;示例如下
附加使用的sql
1、连表更新将一张表的某个字段更新为另一张表的某个字段值
#将1表的reward更新为b表的reward值
UPDATE project_submits a
INNER JOIN projects b ON a.project_id b.id
SET a.reward b.reward2、DATE_FORMAT查询指定日期的数据 select * from order_exam_logs where DATE_FORMAT(created_at, %Y-%m-%d)2023-06-05因为数据表中created_at存到了秒所以要使用DATE_FORMAT函数
