Mysql数据库思维导图

本文详细介绍了MySQL数据库的数据类型、建库建表、增删改查操作,以及索引、事务、触发器、存储过程和查询优化等核心概念。通过实例展示了如何有效地使用和优化MySQL,包括内连接、子查询、视图和事务管理,帮助读者深入理解并提升数据库管理能力。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

varchar
CHAR
TINYBLOB
TINYTEXT
BLOB
TEXT
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT

日期

DATE
TIME
YEAR
DATETIME
TIMESTAMP

数值

TINYINT
SMALLINT
MEDIUMINT
INTINTEGER
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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值