Mysql数据库思维导图

Mysql数据库思维导图
Mysql数据库流程图:
mysql
数据类型:
字符串

varchar
CHAR
TINYBLOB
TINYTEXT
BLOB
TEXT
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT

日期

DATE
TIME
YEAR
DATETIME
TIMESTAMP

数值

TINYINT
SMALLINT
MEDIUMINT
INT或INTEGER
FLOAT
DOUBLE
DECIMAL

建库建表
#建库
CREATEDATABASE IF NOT EXISTS
yuesu DEFAULT CHARSET utf8
#建表
CREATE TABLE IF NOT EXISTS
student( id INT PRIMARY KEY);
增删改查
增
INSERT INTO emp VALUES(‘‘zhangsan’’);
删
DELETE FROM emp WHERE NAME=‘‘lisi’’;
改
UPDATE cs_user SET age=14
查
基本
SELECT * FROM cs_user
where
SELECT * FROM cs_user WHERE age <= 14
排序
SELECT * FROM cs_user ORDER BY gender
分组
SELECT * FROM cs_user GROUP BY gender HAVING AVG(age)>14
去重
SELECT DISTINCT gender FROM cs_user
表关联查询
内连接
SELECT w.,a. FROM websites w INNER JOIN access_log a ON w.id=a.site_id
左连接
SELECT w.,a. FROM websites w LEFT JOIN access_log a ON w.id=a.site_id
右连接
SELECT w.,a. FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id
union联合查询
SELECT * FROM access_log UNION ALL SELECT * FROM websites
全连接
SELECT w.,a. FROM websites w LEFT JOIN access_log a ON w.id=a.site_id
UNION ALL
SELECT w.,a. FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id
三表关联
SELECT st.,sc. FROM stu st
LEFT JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERE sc.scroe>60
子查询
单值
SELECT * FROM emp WHERE
salary>= (SELECT salary FROM emp WHERE
emp_name=‘王强’);
多值
SELECT * FROM emp WHERE dept_id IN
(SELECT dept_id FROM emp WHERE emp_name LIKE ‘孙%’)
多值作中间结果
F3A
视图
直接创建
CREATE VIEW v_author(编号,姓名)
AS
SELECT * FROM author WITH CHECK OPTION
创建表创建
CREATE OR REPLACE VIEW v_blog(编号,标题,内容,作者编号)
AS SELECT * FROM blog
WITH CHECK OPTION
索引
普通索引
CREATE INDEX index_emp_name ON emp(emp_name);
复合索引
CREATE INDEX index_title_content ON article(title,content);
事务
开启
BEGIN
提交
COMMIT
回滚
ROLLBACK
触发器
插入
DELIMITER $
CREATE TRIGGER abc
AFTER INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO cj(stu_id,stu_name)VALUES(new.stuid,new.username); END $ DELIMITER ;
触发
INSERT INTO student(username,PASSWORD,birtday)
VALUES(‘王三’,‘111111’,‘2016-08-23’);
SELECT * FROM cj
存储过程
创建
DELIMITER $
CREATE PROCEDURE tesa()
BEGIN
SELECT * FROM student;
SELECT * FROM cj;
END $
DELIMITER ;
调用
CALL tesa()
查询优化
强制使用索引
SELECT * FROM T1 FORCE INDEX(IX_ProcessID)
WHERE nextprocess =1 AND processid IN(8,32,45)
消除顺序读取，使用索引
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008;
模糊查询where like 首字母’1%'会使用索引
SELECT * FROM t2 WHERE NAME LIKE ‘L%’
COUNT(）
SELECT (SELECT COUNT() FROM cnt) - COUNT() FROM
cnt WHERE id<=5;
数据不兼容会拉低效率
INSERT INTO cnt(id)VALUES(11)
索引列进行运算会让索引失效
SELECT * FROM cnt WHERE age>36
避免的情况 !=,<>,is null,is not null, in,not in
SELECT COUNT() FROM cnt WHERE EXISTS
(SELECT age FROM cnt WHERE id >100);
合理使用exists，not，exists
#后使用where >0
SELECT SUM(t1,c1) FROM t1 WHERE
(SELECT COUNT() FROM t2 WHERE t2.c2=t1.c2>0);
#优先使用EXISTS
SELECT SUM(t1,c1) FROM t1 WHERE EXISTS
(SELECT COUNT(*) FROM t2 WHERE t2.c2=t1.c2);
#优先between , 在使用in
SELECT * FROM cnt WHERE age BETWEEN 100 AND 200;
SELECT * FROM cnt WHERE age IN (20,10,22);
#优先distinct,在使用group by 先分组在排序
SELECT DISTINCT NAME FROM cnt;
SELECT NAME FROM cnt GROUP BY NAME;