平时一些mysql小技巧及常识

》navicat premium 快捷键
1.ctrl+q 打开查询窗口
2.ctrl+/ 注释sql语句
3.ctrl+shift +/ 解除注释
4.ctrl+r 运行查询窗口的sql语句
5.ctrl+shift+r 只运行选中的sql语句
6.F6 打开一个mysql命令行窗口
7.ctrl+l 删除一行
8.ctrl+n 打开一个新的查询窗口
9.ctrl+w 关闭一个查询窗口

》SELECT子句顺序：

SELECT子句顺序
SELECT要返回的列或表达式
FROM从中检索数据的表
WHERE行级过滤
GROUNP BY分组说明
HAVING组级过滤
ORDER BY输出排序顺序
LIMIT要检索的行数

》数据类型

整数类型：int、integer、tinyint、smallint。
布尔类型：bit、boolean。
字符型：varchar、tinytext、longtext、text、longvarchar、char、nchar、nvarchar、clob、nclob。
数值类型：decimal、numeric、real、float、double。
时间日期类型：datetime、date、timestamp、time。
二进制类型：blob、tinyblob、longblob、binary、varbinary、longvarbinary、image。

》mysql 执行状态分析 show processlist
show processlist；
kill id；
show index from xxx;查看表的索引

》删除记录之前-切记要检查是不是存在该记录
删除一条记录时，一定要先检查该记录是不是存在，如果为空值，可能会删掉整个表
原因是第一次删掉后，第二次数据库中已经不存在该值，结果就把整个表删了。。。。。。

》INTERVAL使用

INTERVAL使用	描述
INTERVAL'1'YEAR	1年的时间间隔。
INTERVAL'1'MONTH	1个月的时间间隔。
INTERVAL'14'MONTH	14个月（1年2个月）的时间间隔。
INTERVAL'1-2'YEAR TO MONTH	1年3个月的间隔。
INTERVAL'0-1'YEAR TO MONTH	0年2个月的相隔。
INTERVAL'999'YEAR（3）TO MONTH	的999年间隔为3位精度。
INTERVAL“-1-2'YEAR TO MONTH	1年9个月的负区间。

》mysql 数据相除并保留2为小数点

decimal(a,b)
a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。
SELECT CAST(CAST(112500 AS DECIMAL(18,2)) / CAST(46408 AS DECIMAL(18,2)) AS DECIMAL(18,2))

》多表查询：

SELECT
A.ID,A.NUMBER,A.PRICE,A.ORDER_TIME,
B.USER_ID,B.STARTIME,B.STOPTIME,
C.CHANNEL_PAY,D.COMPANY
FROM
D表 D
LEFT JOIN A表 A ON A.COMPANY_ID = D.UID
LEFT JOIN B表 B ON B.COMPANY_ID = D.UID
LEFT JOIN C表 C ON C.COMPANY_ID = D.UID

有条件的再加行：
WHERE ..........
排序(如A表的ID由大到小排):
ORDER BY A.ID DESC

》case when 来实现批量update
（单个）
UPDATE categories
SET display_order =
　CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
（多个）
UPDATE categories
SET display_order =
　CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title =
　CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)

》子查询总结

- 子查询需用括号包裹。
-- from型
from后要求是一个表，必须给子查询结果取个别名。
- 简化每个查询内的条件。
- from型需将结果生成一个临时表格，可用以原表的锁定的释放。
- 子查询返回一个表，表型子查询。
select * from (select * from tb where id>0) as subfrom where id>1;
-- where型
- 子查询返回一个值，标量子查询。
- 不需要给子查询取别名。
- where子查询内的表，不能直接用以更新。
select * from tb where money = (select max(money) from tb);
-- 列子查询
如果子查询结果返回的是一列。
使用 in 或 not in 完成查询
exists 和 not exists 条件
如果子查询返回数据，则返回1或0。常用于判断条件。
select column1 from t1 where exists (select * from t2);
-- 行子查询
查询条件是一个行。
select * from t1 where (id, gender) in (select id, gender from t2);
行构造符：(col1, col2, ...) 或 ROW(col1, col2, ...)
行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。

1.用于过滤
>SELECT cust_id
FROM orders
WHERE order_num

IN (SELECT order_num FROM orderitems)
2.作为字段
>SELECT cust_name,
cust_state , (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name

》mysql 连接表一般大表放在前面

mysql 连接表一般大表放在前面
例：在t_team表中生成1000条数据，在t_people表中生成100000条数据。
方法一：select * from t_people p left join t_team t on p.team_id=t.id order by p.pname limit 10; [语句①]
方法二： select * from t_people p left join t_team t on p.team_id=t.id limit10; [语句②]
方法三：select * from t_people p order by p.pname limit 10; [语句③]
方法四：select * from (select * from t_people p order by p.pname limit 10) p left join t_team t on p.team_id=t.id limit 10; [语句④]
[语句④] 效率最高

》group by
1.group by 可以包含任意数目的列
2.group by 中每个列都必须是检索列或有效的表达式（但不能使聚集函数）
3.除聚集函数外，select语句中的每个列都必须在group by子句中出现
4.如果分组列有Null值，Null将作为一个分组返回
5.group by 子句必须出现在where子句之后, order by 之前

》where和having区别

where在分组前过滤，having在分组后过滤
having 字段必须是查询出来的，where 字段必须是数据表存在的。
where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
where 不可以使用合计函数。一般需用聚合函数才会用 having

》优化GROUP BY语句

如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序
有些情况下，可以使用连接来替代子查询。

》left join on 后跟and和where区别
通过多表联查查询数据时，数据库会产生一个中间临时表，然后将临时表返回给用户。
1、ON条件是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回左边表中的记录；
2、WHERE条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有LEFT JOIN的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉；
3、AND 是过滤之后再连接，WHERE是连接之后再过滤。
4、而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

》UNION自动去除重复行；UNION ALL 重复行保留

》mysql注释

# 到该行结束、-- 到该行结束以及 /* 行中间或多个行 */ 的注释方格：
mysql> SELECT 1+1; # 这个注释直到该行结束
mysql> SELECT 1+1; -- 这个注释直到该行结束
mysql> SELECT 1 /* 这是一个在行中间的注释 */ + 1;
注意 -- (双长划) 注释风格要求在两个长划后至少有一个空格！

》

-- 数值函数
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数

-- 时间日期函数

adddate() 增加一个日期-天或周
addtime() 增加一个时间
curdate() 返回当前日期
curtime() 返回当前时间
date() 返回日期时间的日期部分
datediff() 计算两个日期差
date_add() 高度灵活的日期运算函数
day() 返回一个日期的天数部分
dayofweek() 对于一个日期，返回对应的星期几

now(), current_timestamp(); -- 当前日期时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间

-- 常用字符串函数

length(str) 字符串长度
locate(substr,str) 返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0
LOCATE(substr,str,pos) 返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0
left(str, length) 返回字符串左边字符
right(str, length) 返回字符串右边字符
substring(str, pos); substring(str, pos, len)
SUBSTRING_INDEX(str,delim,count)
mid(), substr() 等价于substring() 函数
ltrim(str) 去掉字符串左边空格
rtrim(str) 去掉字符串右边空格
trim(str) 去掉字符串两边空格
lower(str) 转为小写
upper(str) 转为大写
SELECT LOCATE(’bar’, ‘foobarbar’);#4
SELECT LOCATE(’bar’, ‘foobarbar’,5);#7
select substring(‘sqlhahaha.com‘, 4); #从字符串的第 4 个字符位置开始取，直到结束。
select substring(‘sqlhahaha.com‘, 4, 2);#从字符串的第 4 个字符位置开始取，只取 2 个字符。
select substring(‘sqlhahaha.com‘, -4);#从字符串的第 4 个字符位置（倒数）开始取，直到结束。
select substring(‘sqlhahaha.com‘, -4, 2);#从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。
select substring_index(‘www.baidu.com.cn‘, ‘.‘, 2); #www.baidu#截取第二个 ‘.‘ 之前的所有字符。
select substring_index(‘www.baidu.com.cn‘, ‘.‘, -2); #com.cn #截取第二个 ‘.‘ （倒数）之后的所有字符。
select substring_index(‘www.baidu.com.cn‘, ‘.coc‘, 1);#www.baidu.com.cn #如果在字符串中找不到 delim 参数指定的值，就返回整个字符串

》MySQL中NULL和空值的区别：
判断NULL用is null 或者 is not null。 sql语句里可以用ifnull()函数来处理
判断空字符串‘’，要用 ='' 或者 <>''。sql语句里可以用if(col,col,0)处理，即：当col为true时(非null，及非'')显示，否则打印0

mysql使用中一些小总结：

1、仅列出需要查询的字段，这对速度不会明显的影响，主要是考虑节省应用程序服务器的内存。
原来语句： select * from admin
优化为： select admin_id,admin_name,admin_password from admin

2、尽量避免在列上做运算，这样导致索引失效。
原语句： select * from admin where year(admin_time)>2014
优化为： select * from admin where admin_time> '2014-01-01′

3、使用JOIN 时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小如果有条件应该放到左边先处理，right join 同理反向），同事尽量把牵涉到多表联合的查询拆分多个query（多个连表查询效率低，容易到之后锁表和阻塞）。
原来语句 select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10
优化为： select * from (select * from admin where admin_id>10) T1 lef join log on T1.admin_id = log.admin_id

4、注意LIKE 模糊查询的使用，避免使用 %% ,可以使用后面带% ，双%是不走索引的。
原来语句： select * from admin where admin_name like ‘%de%'
优化为： select * from admin where admin_name >='de' and admin_nam <'df' （注意不是等效的这里试试提供优化的思路）

5、使用批量插入节省交互（当如如果使用存储过程来处理批量的sql 各种逻辑是更好的选择）。
原来语句：insert into admin(admin_name,admin_password) values (‘test1′,'pass1′);
insert into admin(admin_name,admin_password) values (‘test2′,'pass2′);
insert into admin(admin_name,admin_password) values (‘test3′,'pass3′)
优化为： insert into admin(admin_name,admin_password) values(‘test1′,'pass1′),(‘test2′,'pass2′),(‘test3′,'pass3′)

6、limit 的基数比较大时使用between。
原来语句：select * from admin order by admin_id limit 100000,10
优化为： select * from admin where admin_id between 100000 admin 100010 order by admin_id

7、不要使用rand函数获取多条随机记录。
原来语句： select * from admin order by rand() limit 20
优化为： select * from admin as t1 Join(select round(rand()*((select max(admin_id) from admin)-(select min(id) from admin))+(select min(id) from admin)) as id) as t2 where t1.id>=t2.id order by t1.id limit

8、避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

9. 不要使用 count(id) 建议使用 count(*) or count(1)。

10、不要做无谓的排序操作，而应该使用索引完成排序。

11.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0

12.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)

posted @ 2016-11-30 16:38 Little_Rookie 阅读(5662) 评论(0) 编辑收藏举报

刷新页面返回顶部