不等于: <>
比较两个表达式(比较运算符)。 当比较非空表达式时,如果左操作数不等于右操作数,则结果为 TRUE;否则结果为 FALSE。两个操作数都不可为空。
在结果中新增不存在的列
select *,cast('自定义的值' as 自定义的字段类型) as '列名' from 表名
select *, '自定义的值' as '列名' from 表名 (简写)
case when:判断语句(不是分支语句!)
构造新列example:
简单case when:
select
case gender
when 1 then 'man'
when 0 then 'woman'
else 'uk'
end as genderword
from student
复杂case when:
select
case
when (gender = 1) then 'man'
when (gender = 1) then 'woman'
else 'uk'
end as genderword
from student
case when 可以用在很多地方,比如:
UPDATE Salary
SET sex= case sex
WHEN 'm' THEN 'f'
WHEN 'f' THEN 'm'
END
取余运算:mod(var1,var2)
var1:被取余数,var2:除数
偶数:mod(var,2)=0
截取字符串:
left(str,length)
right(str,length)
substing(str,position,length):position为负则从后向前截取
substring_index(str,keyword,count):从str中截取到keyword第count次出现的位置,count为负则从后向前截取
逻辑运算:
与:and 或:or 非:not
不可以使用&&,||,!
二进制按位与:&
查找同一个table内重复的数据:
select * from person p1,person p2 where p1.a = p2.a
字符串拼接:concat(str1,str2) (mysql)
大写/小写函数:Upper(str)/Lower(str)
字符串长度函数:
length(str) 返回str的字节长度
char_length(str) 返回str的字符个数
CONCAT_WS(separator, expression1, expression2, expression3,...)将多个表达式(多列)连接为一个字段
GROUP_CONCAT(DISTINCT expression ORDER BY expression SEPARATOR separator);将多个表达式(多行)连接为一个字段
example:
SELECT sell_date,
group_concat(distinct product separator ',') as products
from Activities --#同一sell_date 对应的多个product,合为一行
二者结合:
SELECT GROUP_CONCAT(CONCAT_WS(', ', contactLastName, contactFirstName) SEPARATOR ';') FROM customers;
1、列转行
SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;
2、行转列
SELECT
product_id,
SUM(IF(store = 'store1', price, NULL)) 'store1',
SUM(IF(store = 'store2', price, NULL)) 'store2',
SUM(IF(store = 'store3', price, NULL)) 'store3'
FROM
Products1
GROUP BY product_id ;