索引

索引使用原则

  • 数据量过大，而且经常被查询的字段可以设置索引
  • 索引只添加在经常被用作检索条件的字段
  • 不要在大字段上创建索引

索引是在表的某个字段建立二叉树进行排序，利用二叉树的二分查找加快查询速度

查看表索引

SHOW INDEX FROM 表名;

删除表索引

DROP INDEX 索引名称 ON 表名;

CREATE 语句创建表索引

CREATE INDEX 索引名称 ON 表名(字段);

ALTER 语句创建表索引

ALTER TABLE 表名 ADD INDEX 索引名称(字段);

查询

执行顺序

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

去重

DISTINCT 只能对一个字段去重，多个字段会失效

SELECT 字句中 DISTINCT 只能出现一次，且必须出现在第一个字段的前面

SELECT DISTINCT 字段名 FROM 表名;

条件查询

如果字段值为空，则返回0

IFNULL(字段名, 0)

查询字段与现在时间相差多少年

DATEDIFF(NOW(), 字段名)/365

查询范围在 300 到 400 之间

SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN 300 AND 400;

正则表达式

REGEXP "^[a-zA-Z0-9]{2,4}"

分组查询

SELECT 子句中只可以出现分组的字段和聚合函数

// ROUND 为将值四舍五入
// WITH ROLLUP 为将查询结果再次进行汇总计算
SELECT depno,job,COUNT(*),ROUND(AVG(sal)) FROM t_emp GROUP BY depno,job WITH ROLLUP ORDER BY depno DESC;
SELECT depno,GROUP_CONCAT(ename),COUNT(*) FROM t_emp WHERE sal>=2000 GROUP BY depno;

HAVING

SELECT depno FROM t_emp GROUP BY depno HAVING AVG(sal)>=2000;

外连接

内连接除了符合条件的记录外，结果集不会保留不符合条件的记录，外连接则会保留；左外连接为保留左表所有记录去和右表连接，右表相反。

SELECT e.empno,e.ename,d.dname,e.depno FROM t_emp e LEFT JOIN t_dep d ON e.depno=d.depno;

UNION

UNION 关键字用于将多个查询语句的结果集进行合并

(SELECT d.dname,COUNT(e.depno) FROM t_dep d LEFT JOIN t_emp e ON e.depno=d.depno GROUP BY d.depno) UNION (SELECT d.dname,COUNT(*) FROM t_dep d RIGHT JOIN t_emp e ON e.depno=d.depno GROUP BY d.depno);

子查询

SELECT ename FROM t_emp WHERE depno IN (SELECT depno FROM t_emp WHERE ename IN ("FORD","MARTIN")) AND ename NOT IN ("FORD","MARTIN"));

SELECT ename FROM t_emp WHERE sal>=ALL(SELECT sal FROM t_emp WHERE ename IN ("FORD","MARTIN")) AND ename NOT IN ("FORD","MARTIN");

SELECT empno,ename,sal FROM e_emp WHERE EXISTS (SELECT grade FROM t_salgrade WHERE sal BETWEEN losal AND hisal AND grade IN (3,4));

聚合函数

聚合函数不可出现在 WHERE 字句中

AVG

SELECT AVG(sal + IFNULL(comm,0)) AS avg FROM t_emp;

SUM

SELECT SUM(sal) FROM t_emp WHERE depno IN (10,20);

MAX MIN

SELECT MAX(comm) FROM t_emp;
SELECT MAX(LENGTH(ename)) FROM t_emp;

COUNT

SELECT COUNT(*) FROM t_temp
SELECT COUNT(comm) FROM t_temp

修改

基本函数

数字函数

函数 功能
ABS 绝对值
ROUND 四舍五入
FLOOR 强制舍位到最近的整数
CEIL 强制进位到最近的整数
POWER 幂函数
LOG 对数函数
LN 对数函数
SORT 开平方
PI 圆周率
SIN 三角函数
COS 三角函数
TAN 三角函数
COT 三角函数
RADIANS 角度转弧度函数
DEGREES 弧度转角度函数

日期函数

函数 功能
NOW yyyy-MM-dd hh:mm:ss
CURDATE yyyy-MM-dd
CURTIME hh:mm:ss
DATE_FORMAT %Y
DATE_ADD DATE_ADD(日期, INTERVAL 偏移量 时间单位)
DATEDIFF DATEDIFF(日期,日期) 
SELECT ename,DATE_FORMAT(hiredate,"%Y") FROM t_emp;
SELECT DATE_ADD(NOW(), INTERVAL 15 DAY);
SELECT DATE_ADD(NOW(), INTERVAL -300 MINUTE);
SELECT DATE_ADD(DATE_ADD(NOW(), INTERVAL -300 MINUTE), INTERVAL -20 DAY);

字符函数

函数 功能
LOWER 小写
UPPER 大写
LENGTH 字符数量
CONCAT 连接字符串
INSTR 字符出现的位置
INSERT 插入/替换字符
REPLACE 替换字符 
SELECT LENGTH("hello");
SELECT INSTR("hello", "l");
SELECT INSERT("hello", 2, 0, "world");
SELECT CONCAT(sal, "$");
SELECT REPLACE("Hello World", "World", "world!");

条件函数

清空表

MySQL

TRUNCATE table table_name

PostgreSQL

TRUNCATE table table_name restart identity

数据库中途重置从库重新同步

从库清空从状态

STOP SLAVE;
RESET SLAVE ALL;
SHOW SLAVE STATUS\G;

从库清空所有表或直接删除所有库，保留安装时自动创建的库

主库锁表，禁止写入，查看当前状态，记下 File 和 Position

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G;

主库导出当前状态下的所有库

mysqldump -uroot  -ppassword --all-databases > all.sql
scp all.sql user@slaveip:/root/

主库恢复表写入

UNLOCK TABLES;

从库导入主库导出的文件

从库设置主从

MASTER_HOST 为主库 IP，MASTER_USER 为主库创建的已经设置好同步权限的用于同步数据的账号，MASTER_PASSWORD 为主库创建的已经设置好同步权限的用于同步数据的密码，MASTER_PORT 为主库端口，MASTER_LOG_FILE 为主库锁表后记录的 File，MASTER_LOG_POS 为主库锁表后记录的 Position

CHANGE MASTER TO MASTER_HOST='192.168.21.13', MASTER_USER='ha', MASTER_PASSWORD='123456', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000067', MASTER_LOG_POS=75851887;
START SLAVE;
SHOW SLAVE STATUS\G;