MySQL 学习笔记--源自技术

简介

    MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，MySQL AB 被sun收购，sun被Oracle收购。MySQL分为社区版和商业版。在MySQL被甲骨文收购后， MySQL的创始人Michael Widenius主导开发了 MYSQL 的替代品 MariaDB。

基础CRUD

-- 数据库操作

-- 启动数据库

[plain]  view plain  copy

1. net start mysql;  

-- 连接mysql数据库

[plain]  view plain  copy

1. mysql -h'地址' -u'用户' -p  

-- 显示数据库列表

[plain]  view plain  copy

1. SHOW DATABASES;  

-- 打开数据库

[plain]  view plain  copy

1. use myusql;  

-- 显示表列表

[plain]  view plain  copy

1. show tables;  

-- 显示表结果

[plain]  view plain  copy

1. describe 表名;  

-- 显示最后一条执行的语句所产生的错误、警告和通知

[plain]  view plain  copy

1. show warnings;  

-- 显示最后一个执行语句所产生的错误

[plain]  view plain  copy

1. show errors;  

-- 数据库状态

[plain]  view plain  copy

1. SHOW STATUS;  

-- 查看正在运行的线程

[plain]  view plain  copy

1. SHOW FULL PROCESSLIST;  

-- 查看打开的表

[plain]  view plain  copy

1. SHOW OPEN TABLES;  

-- 查看innodb引擎运行是信息

[plain]  view plain  copy

1. SHOW ENGINE INNODB STATUS;  

-- 查看服务器配置参数

[plain]  view plain  copy

1. SHOW VARIABLES LIKE '%timeout%';  

-- 创建数据库

[plain]  view plain  copy

1. create database '表名' character set utf8 collate utf8_general_ci;  

-- 创建表

[plain]  view plain  copy

1. CREATE TABLE 表名  
2. (      
3.  `id` BIGINT NOT NULL AUTO_INCREMENT ,       
4.  `name` VARCHAR(20) NOT NULL ,       
5.  `grade` VARCHAR(20) NOT NULL ,  
6.  PRIMARY KEY (`id`),  
7.  KEY (`grade`)  
8.  )   
9. ENGINE=INNODB COMMENT='' ROW_FORMAT=DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;  

-- 插入表数据

[plain]  view plain  copy

1. INSERT INTO 表名 () VALUES();  
2. INSERT INTO 表名 (列-1,列-2) VALUE(列-1-值,列-2-值);  
3. INSERT INTO 表名 (列-1,列-2) VALUES(列-1-值,列-2-值),(列-1-值,列-2-值);  
4. INSERT INTO 表名 (列-1,列-2) VALUES(列-1-值,列-2-值) ON DUPLICATE KEY UPDATE 列-2-值=更新值;  
5. INSERT INTO 表名 SELECT (列-1,列-2) FROM 表名;  
6. INSERT INTO 表名 (列-1,列-2) SELECT (列-1,列-2) FROM 表名;  
7. INSERT DELAYED INTO 表名 (列-1,列-2) VALUE(列-1-值,列-2-值);  

-- 查询表数据

[plain]  view plain  copy

1. SELECT * FROM 表名 WHERE 列=条件;  
2. SELECT * FROM 表名 ORDER BY 列名;  
3. SELECT * FROM 表名 GROUP BY 列名;  
4. SELECT * FROM 表名 LIMIT 0,10;  
5. SELECT 列-1,列-2 FROM 表名 UNION (SELECT 列-1,列-2 FROM 表名);  
6. SELECT 列-1,列-2 FROM 表名 UNION ALL(SELECT 列-1,列-2 FROM 表名);  
7. SELECT * FROM 表名 WHERE EXISTS (SELECT * FROM 表名);  
8. SELECT * FROM 表名 WHERE NOT EXISTS (SELECT * FROM 表名);  
9. SELECT * FROM 表名 WHERE 列名 IN(SELECT 列名 FROM 表名);  
10. SELECT * FROM 表名 WHERE 列名 NOT IN(SELECT 列名 FROM 表名);  
11. SELECT * FROM 表名 WHERE 列名 关系运算符 ANY(SELECT 列名 FROM 表名);  
12. SELECT * FROM 表名 WHERE 列名 关系运算符 SOME(SELECT 列名 FROM 表名);  
13. SELECT * FROM 表名 WHERE 列名 关系运算符 ALL(SELECT 列名 FROM 表名);  
14. SELECT * FROM 表名,表名;  
15. SELECT * FROM 表名 LEFT JOIN 表名 ON 表名.列名=表名.列名;  

-- 修改数据

[plain]  view plain  copy

1. UPDATE 表名 SET 列=值 WHERE 列=条件;  
2. UPDATE 表名 SET 列名 = 更新值 WHERE 列名 = 条件 ORDER BY NAME LIMIT 3;  
3. UPDATE 表名,表名,表名 SET 列名 = 更新值 WHERE 列名 = 条件;  
4. UPDATE 表名 LEFT JOIN 表名 ON 表名.列名 = 表名.列名 SET 表名.列名=更新 WHERE 条件表达式;   

-- 删除数据

[plain]  view plain  copy

1. DELETE FROM 表名 WHERE 列=条件;  
2. TRUNCATE FROM 表名;  

主键/索引/约束

-- 约束

1.NOT NULL非空约束。
2.UNIQUE 唯一约束，可以是单列也可以是多列。
3.外键约束。
4.主键约束。

-- 添加非空约束

[plain]  view plain  copy

1. ALTER TABLE 表名 CHANGE 字段名 字段名 类型 NOT NULL;  

-- 删除非空约束

[plain]  view plain  copy

1. ALTER TABLE 表名 CHANGE 字段名 字段名 类型 NULL;  

-- 添加唯一约束

[plain]  view plain  copy

1. ALTER TABLE 表名 ADD UNIQUE 约束名(列);  

-- 删除唯一约束

[plain]  view plain  copy

1. ALTER TABLE 表名 DROP KEY 约束名;  

-- 添加主键

[plain]  view plain  copy

1. ALTER TABLE 表名 ADD PRIMARY KEY (列名);  

-- 删除主键

[plain]  view plain  copy

1. ALTER TABLE 表名 DROP PRIMARY KEY;  

-- 添加外键

[plain]  view plain  copy

1. ALTER TABLE 外键表 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主键表 (主键字段) ON DELETE NO ACTION  ON UPDATE NO ACTION ;  

-- 删除外键

[plain]  view plain  copy

1. ALTER TABLE 表名 DROP FOREIGN KEY  外键名;  

-- 索引

1.普通索引。
2.唯一索引。
3.主键索引。
4.组合索引。
5.全文索引。

-- 创建普通索引，创建表时使用"KEY"或者下面的SQL

[plain]  view plain  copy

1. ALTER TABLE 表名 ADD INDEX 索引名(列);  

-- 创建全文索引

[plain]  view plain  copy

1. ALTER TABLE 表名 ADD FULLTEXT 索引名 (列);  

-- 创建唯一索引
同唯一约束
-- 删除唯一索引
同唯一约束
-- 创建主键索引
同主键约束
-- 删除主键索引
同主键约束

-- 主键和约束与索引的关系

主键是是一个特殊的索引，它是非空约束和唯一约束的结合，一个表可以有多个唯一约束但是只能有一个主键。

-- 那些操作索引会失效

1.LIKE查询已%开头。
2.多列索引，不是使用的第一部分，索引失效。
2.对列使用算数运算符进行运算或者使用函数。
3.WHERE子句中有的列没有索引而使用or那么索引失效。
4.使用NOT IN 和 <>操作符。
5.隐式转换到时索引失效，列是varchar(20)类型，查询时传number类型条件。
6.没有查询条件，或者查询条件没有索引，WHERE子句中应该让索引列在前。

-- 查看索引的使用情况

show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效

数据库锁

-- 锁分类

-- 按级别

行级锁定（row-level）
表级锁定（table-level）
页级锁定（page-level）

-- 按类型

共享锁(Shared lock)。
更新锁(Update lock)
排他锁（独占锁，Exclusive Locks)
意向锁(Intent Locks)
计划锁(Schema Locks)
批量更新锁(Bulk Update Locks)

-- -------------资料-- -------------

MariaDB 百科

MySQL 百科

数据库锁机制