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;