MySQL 笔记


索引

索引使用原则

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

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

查看表索引

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弧度转角度函数

日期函数

函数功能
NOWyyyy-MM-dd hh:mm:ss
CURDATEyyyy-MM-dd
CURTIMEhh:mm:ss
DATE_FORMAT%Y
DATE_ADDDATE_ADD(日期, INTERVAL 偏移量 时间单位)
DATEDIFFDATEDIFF(日期,日期)
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;

文章作者: 套陆
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 套陆 !
评论
  目录