mysql

Mysql on duplicate key update用法及优缺点

Mysql模糊查询之LIKE CONCAT(‘%’,#{name},‘%’)_keli_Jun的博客-CSDN博客_like concat(’%

外键约束

添加外键关联 方式一

create table 表名(

字段名 数据类型,

...

constraint 外键名称 foreign key (外键字段) references 主表名(主表字段));

方式二

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段);

删除外键

alter table 表名 drop foreign key 外键名称;

关联查询

条件顺序

select {columns} from {table|view|other select}
[where 查询条件] W

[group by 分组条件] G

[having 分组后再限定] H

[order by 排序] O

group_concat() 函数用法

用法: mysql group concat 去重_MySQL group_concat() 函数用法-CSDN博客

自连接

-- 1. 查询员工 及其 所属领导的名字 (可以把这表看着两个表 做关联)

-- 表结构: emp

数据如下

select a.name , b.name from emp a , emp b where a.managerid = b.id;

查询结果 

列  子查询

查询比 财务部 所有人 工资都低的员工信息

SELECT *, FROM emp WHERE emp.salary < (SELECT MIN(emp.salary) FROM emp WHERE emp.dept_id=(

SELECT dept.id FROM dept WHERE dept.`name`='财务部'));

查询员工信息和员工所在的部门

SELECT emp.*,dept.`name` as '部门' FROM emp LEFT JOIN dept ON emp.dept_id= dept.id;

查询比 财务部 所有人 工资都低的员工信息 (包含部门信息)

SELECT emp.*,dept.`name` as '部门' FROM emp LEFT JOIN dept ON emp.dept_id= dept.id

WHERE emp.salary < (SELECT MIN(emp.salary) FROM emp WHERE emp.dept_id=(

SELECT dept.id FROM dept WHERE dept.`name`='财务部'));

行  子查询

-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

-- a. 查询 "张无忌" 的薪资及直属领导

select salary, managerid from emp where name = '张无忌';

-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表  子查询

表子查询练习一

-- 1. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

-- a. 查询 "鹿杖客" , "宋远桥" 的职位和薪资

select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

-- b. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

表子查询练习二

-- 2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

-- a. 入职日期是 "2006-01-01" 之后的员工信息

select * from emp where entrydate > '2006-01-01';

-- b. 查询这部分员工, 对应的部门信息;

方式一

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

方式二

SELECT emp.* ,dept.`name` as '部门' FROM emp LEFT JOIN dept ON emp.dept_id=dept.id

WHERE emp.entrydate > '2006-01-01';

  多表关联查询

-- 6. 查询 "研发部" 所有员工的信息及 工资等级

-- 表: emp , salgrade , dept

-- 连接条件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id

-- 查询条件 : dept.name = '研发部'

select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部';

三个表关联查询例子2
SELECT * FROM index_type_goods_banner AS itgb
LEFT JOIN goods_type  AS gt ON itgb.goods_type_id = gt.id
LEFT JOIN goods_s_k_u AS gsku  ON itgb.goods_s_k_u_id =gsku.id  WHERE itgb.display_type=1 AND gt.name ="新鲜水果" Order BY itgb.index

关联查询例子3

求出每一列的最大值，并且根据某一个字段进行分组--分topn求法
SELECT article, MAX(price) AS price

FROM shop

GROUP BY article;
分组 查询每组多少个
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+

| name | COUNT(*) |

+--------+----------+

| 小丽 | 1 |

| 小明 | 3 |

| 小王 | 2 |

+--------+----------+

 事务

• 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
• 隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

• 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

  

racle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。

默认系统事务隔离级别是READ COMMITTED,也就是读已提交.

下图是mysql的四种隔离级别

mysql进阶

索引语法

创建索引

create unique/fulltext index 索引名字 on 表名(字段名);

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

SQL性能分析

慢查询日志

explain执行计划

EXPLAIN 执行计划各字段含义:

➢ Id select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。

➢ select_type

表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、

UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等

➢ type

表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。

➢ possible_key

显示可能应用在这张表上的索引，一个或多个。

索引使用,索引失效

1.最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。 如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

2.范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

EXPLAIN SELECT * FROM tb_user WHERE  profession ='软件工程' and age>30 AND STATUS ='0';

3.索引列运算

不要在索引列上进行运算操作， 索引将失效

EXPLAIN SELECT * FROM tb_user WHERE  substring(phone,10,2) ='15';

4.字符串不加引号

字符串类型字段使用时，不加引号， 索引将失效

5.模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

EXPLAIN SELECT * FROM tb_user WHERE  profession like '软件%'
--下面这个失效
EXPLAIN SELECT * FROM tb_user WHERE  profession like '%工程'

6 or连接的条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

--profession有索引,email没有索引 ,这个执行会索引失效
EXPLAIN SELECT * FROM tb_user WHERE profession='软件工程' or  email='19980729@sina.com';

7. 数据分布影响

如果MySQL自己评估使用索引比全表更慢，则不使用索引。

8.SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示代码指定来达到优化操作的目的。

9.覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少 select * 知识小贴士:
using index condition :查找使用了索引，但是需要回表查询数据
using where; using index :查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

10.前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。

2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪 个索引最有效地用于查询。

书写高质量SQL的30条建议

后端程序员必备：书写高质量SQL的30条建议 - Jay_huaxiao - 博客园

存取过程,触发器