MySQL-rand()函数
MySQL-round()函数
MySQL-locate()函数
MySQL-group_concat_max_len
1.获取对应年份函数
在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
2.通配符
1.%通配符
LIKE “robot%” 找到以robot开头的字符串
LIKE "%robot"找到以robot结尾的字符串
LIKE "%robot%"找到包含robot的字符串
LIKE "r%t"找到r开头t结尾的字符串
注意:
- 在保留字robot后有空格,LIKE "%robot“会无法正确匹配到,可以用函数去掉首尾空格
- LIKE "%"匹配不到NULL值
- MySQL中保留字区分大小写,robot 和Robot不同
2._通配符
“_”与%一样,但只能匹配单个字符。
SELECT prod_id, prod_name
FROM producs
WHERE prod_name LIKE '_ ton anvil';
使用通配符的注意事项:
- 通配符开销很大,如能使用其他操作符,不要用通配符
- 如果一定要用,晚点用
3.找到一个列中的极值
ORDER BY必须位于FROM子句后
LIMIT必须位于ORDER BY后
ORDER BY coluomns DESC
LIMIT 1
4.Limit用法
- 从第3行到第13行
SELECT * FROM table LIMIT 2,10;
// 等价于
SELECT * FROM table LIMIT 2 OFFSET 10;
- 从某一个偏移量起到结束所有行,指定第二个参数为 -1:
SELECT * FROM table LIMIT 5,-1; // 6行-结束
- 如果只给定一个参数n, 获得钱n行数据
SELECT * FROM table LIMIT 5; //前5行
// 等价于
SELECT * FROM table LIMIT 0, 5;
5.WHERE子句
1.操作符
2.空值检查 IS NULL
该语句返回prod_name为空的字段
SELECT prod_name
FROM products
WHERE prod_price IS NULL
3.逻辑运算 AND & OR
AND在sql中优先级更高,如果和OR同时使用应添加圆括号获得希望结果。当它们同时出现都应该用括号区分,以消除歧义
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
4.IN操作符
IN的合法取值应当在圆括号内由逗号分隔。
IN完成了和OR相同的功能
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
5.NOT操作符
NOT对在它后面的条件取反
NOT IN
NOT BETWEEN
NOT EXISTS
6.正则表达式
不区分大小写,如果需要区分,加上BINARY
SELECT prod_name
FROM products
WHERE prod_name REGEXP BINARY '[123] Tony'
ORDER BY prod_name
常见正则表达式 or 用“|”,eg。 REGEXP ‘1000|2000’
作用 | 例子 |
---|---|
OR | REGEXP ‘1000|2000’ |
匹配范围 | 数字[0-9] ,字母[a-z] |
匹配特殊字符 | 查找. :’\\.’ ,查找-:’\\-’ ,查找空白元字符 |
字符类:预定义好的字符集
重复元字符
定位元字符
6.计算字段
运行时在SELECT语句内创建。
1.拼接:将两个列拼接起来
Concat()拼接,各个串之间用逗号分隔
SELECT Concat(name, '(', country, ')') AS title
FROM vendors
ORDER BY name;
2.算数计算
7.函数
1.去除空格
RTrim() 去除右侧空格
LTrim() 去除左空格
Trim() 去除两侧空格
SELECT RTrim(name)
FROM vendors
2.将文本转换为大写
Upper()
SELECT name, Upper(name) as name_upcase
FROM vendors
常用文本处理函数
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。
常用时间处理函数
SELECT id, num
FROM orders
WHERE Date(order_data) BETWEEN '2016-05-05' AND '2016-06-06';
常用数值处理函数
8.汇总与分组
1.聚集函数
SELECT COUNT(*) AS num_items,
MIN(price) AS price_min,
AVG(price) AS price_avg
FROM products;
2.分组
SELECT id, COUNT(*) AS num_prods
FROM products
GROUP BY id;
会对每个组而不是整个结果聚集
3.过滤分组 HAVING
所有的WHERE子句都可以用HAVING代替。差别在于WHERE过滤行,HAVING过滤分组。
WHERE在分组前过滤,HAVING在分组后过滤
SELECT id, COUNT(*) AS num_orders
FROM orders
WHERE price >= 10
GROUP BY id
HAVING COUNT(*)>= 2;
Rank()
组合索引:根据查询字段的位置不同来决定,如查询a, a,b a,b,c a,c 都可以走索引的,其他条件的查询不能走索引。组合索引 有“最左前缀”原则。就是只从最左面的开始组合,并不是所有只要含有这三列存在的字段的查询都会用到该组合索引。
sql性能优化:sql需要避免在索引字段上使用函数
避免在WHERE子句中使用in,not in , 可以使用exist和not exist代替
将对于同一个表格的多个字段的操作写到同一个sql中, 而不是分开成两个sql语句实现
避免建立索引的列中使用空值
索引:
索引的特点:
创建索引的好处
(1)通过创建索引,可以在查询的过程中,提高系统的性能
(2)通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
(3)在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
创建索引的坏处
(1)创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
(2)索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
(3)在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护