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;
3109
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
