MySQL 基础

目录

1、基本操作：

2、分组聚合函数

3、通配符

4、事务控制

5、MySQL锁

6、什么时候使用表锁

7、explain

---

https://mp.weixin.qq.com/s/lc7E1QZsNHBGk3QYVSf-yw

1、基本操作：

 显示所有数据库
show databases;

 创建名字为 db_book 的数据库
create databases db_book; 

 删除名字为 db_book 的数据库
drop databases db_book; 

 创建表
CREATE TABLE t_bookType(
	id int primary key auto_increment,
	bookTypeName varchar(20),
	bookTypeDesc varchar(200)
);

 外键
CREATE TABLE t_bookType(
	id int primary key auto_increment,
	bookTypeName varchar(20),
	bookTypeDesc varchar(200)
);
CREATE TABLE t_book(
	id int primary key auto_increment,
	bookName varchar(20),
	author varchar(10),
	price decimal(6,2),
	bookTypeId int,
	constraint `fk` foreign key (`bookTypeId`) references `t_bookType`(`id`)
);

 查看表
desc t_bookType;

 表详细信息
show create table t_bookType;

 修改表名
alter table 旧表名 rename 新表名;

 修改字段
alter table 表名 change 旧属性名 新属性名 新数据类型;

 增加字段
alter table 表名 add 属性名1 数据类型  [ 完整性约束条件 ]  [ first | alter 属性名2 ];

 删除字段
alter table 表名 drop 属性名;

 删除表
drop table 表名;

 插入
INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('1','张三','23','男','一年级');
 如果有主键或者唯一键冲突则不插入
insert ignore into
 如果有主键或者唯一键冲突则更新，注意这个会影响自增的增量
INSERT INTO room_remarks(room_id,room_remarks)VALUE(1,"sdf") ON DUPLICATE KEY UPDATE room_remarks = "234"
 如果有就用新的替代，values 如果不包含自增列，自增列的值会变化
REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf")

 查询
SELECT * FROM t_student WHERE id=1;
SELECT * FROM t_student WHERE age>22;
SELECT * FROM t_student WHERE age IN (21,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);
SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE stuName LIKE '张三';
SELECT * FROM t_student WHERE stuName LIKE '张三_';  下划线任意字符
SELECT * FROM t_student WHERE stuName LIKE '%张三%';  模糊查询
SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;
SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
SELECT DISTINCT gradeName FROM t_student;  去重
SELECT * FROM t_student ORDER BY age ASC;  升序
SELECT * FROM t_student ORDER BY age DESC;  降序
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;  分组查询
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;  聚合函数查询
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;  计数大于3的
SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;  统计
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT * FROM t_student LIMIT 0,5;  分页查询
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;

 删除
DELETE FROM table_name [WHERE Clause]
 连表删除
DELETE user FROM user,black WHERE user.id=black.id

 更新
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
 连表更新
UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id

 临时关系
WITH test2 (W1, W2, W3, W4) AS (SELECT F1, F2, F3, F5 FROM test1)SELECT * FROM test2; 

 创建索引
alter table table_name add index index_name (column_list);
alter table table_name add unique (column_list);
alter table table_name add primary key (column_list);

create index index_name on table_name (column_list);
create unique index index_name on table_name (column_list);

 全文索引
CREATE TABLE article (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content) --在title和content列上创建全文索引
);
 使用
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

 删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

 查看索引
SHOW INDEX FROM [tableName];

 全文索引
 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);

select * from fulltext_test 
    where match(content,tag) against('xxx xxx');

 备份表
CREATE TABLE user_info SELECT * FROM user_info

 复制表结构
CREATE TABLE user_v2 LIKE user

 从查询语句中导入
INSERT INTO user_v2 SELECT * FROM user 或者 INSERT INTO user_v2(id,num) SELECT id,num FROM user

2、分组聚合函数

AVG([distinct] expr)  求平均值
COUNT({*|[distinct] } expr)  统计行的数量
MAX([distinct] expr)  求最大值
MIN([distinct] expr)  求最小值
SUM([distinct] expr)  求累加和

3、通配符

%：任意长度

_：单个字符

4、事务控制

mysql数据库默认是自动提交

begin
your dml statement
select statement
commit or rollback

begin会开启一个事务，然后你执行你的dml语句，并通过select语句验证，如果验证通过，就执行commit，否则执行rollback

5、MySQL锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

--读锁：允许其他线程上读锁，但是不允许上写锁。
select * from user where user_name='wzzf' lock in share mode

--写锁：不允许其他线程上任何锁。
select * from user where user_name='wzzf' for update

6、什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

•  第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
•  第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。

7、explain

使用EXPLAIN解析SQL执行计划时，如果有下面几种情况，就需要特别关注下了：

首先看下 type 这列的结果，如果有类型是 ALL 时，表示预计会进行全表扫描（full table scan）。通常全表扫描的代价是比较大的，建议创建适当的索引，通过索引检索避免全表扫描。

再来看下 Extra 列的结果，如果有出现 Using temporary 或者 Using filesort 则要多加关注：

• Using temporary，表示需要创建临时表以满足需求，通常是因为GROUP BY的列没有索引，或者GROUP BY和ORDER BY的列不一样，也需要创建临时表，建议添加适当的索引。
• Using filesort，表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引。
• Using where，通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或 index），又加上了WHERE条件，建议添加适当的索引。

列名	类型	说明
id		查询序号，为sql语句执行顺序
select_type		提供了对表的查询类型
	simple	简单查询（没有union和子查询），对于不包含子查询和其他复杂语法的简单查询
	primary	最外层查询（在存在子查询的语句中，最外面的select查询就是primary）这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和 UNION 类型混合使用时见到
	derived	子查询（在FROM列表中包含的子查询）当一个表不是一个物理表时，那么这个就被叫做DERIVED
	subquery	映射为子查询（在SELECT或WHERE列表中包含了子查询）这个select-type 的值是为使用子查询而定义的
	union	联合
	union result	使用联合的结果
table		table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表的标识符，如派生表、子查询或集合
type		代表表示查询计划的连接类型，有多个参数，先从最佳类型到最差类型介绍，重要且困难 性能： null > system/const > eq_ref > ref > ref_or_null（前优后劣）>index_merge >  range > index >  all
	NULL	在优化过程中就已得到结果，不用再访问表或索引
	const	常量，在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行；表最多有一个匹配行，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以一定是用到primary key 或者unique 情况下才会是const,看下面这条语句。 所以说可以理解为const是最优化的。
	eq_ref	使用有唯一性索引查找（主键或唯一性索引）对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列
	ref	非唯一性索引访问，这是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行，然而，它可能会找到多个符合条件的行。因此，它是查找和扫描的混合体，此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值
	ref_or_null	该联接类型如同ref类似,结果包含空行
	range	索引范围扫描，常见于　<,<=,>,>=,between,in等操作符
	index	该联接类型与ALL相同都是扫描表，但index只对索引树进行扫描，而ALL是是对数据表文件的扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）主要优点是避免了排序，因为索引是排好序的
	ALL	对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出
possible_keys		该 possible_keys列表示MySQL可以从中选择查找表中的行的索引。如果此列是NULL，则没有相关的索引。在这种情况下，您可以通过检查WHERE 子句来检查是否引用某些适合索引的列，从而提高查询的性能。如果是这样，请创建一个适当的索引并使用 EXPLAIN再次检查查询 。 另外如果这个列出现大量可能被使用的索引(例如多于3 个), 那么这 意味着备选索引数量太多了，同时也可能提示存在无效的索引
key		该key 列指出mysql优化器决定选择使用哪个索引来优化对该表的访问。一般来说SQL查询中的每个表都只会使用一个索引。但是也存在索引合并的少数例外情况，如给定表上用到了两个或者更多索引。查询过程中由优化器来决定实际使用的索引。如果possible_keys索引列表中没有适合查找行的索引，那么这个key可能会命名一个不存在于该possible_keys值中的索引 。简单且重要
key_len		该key_len 列定义了mysql在索引里使用的字节数。如果mysql正在使用的只是索引里的某些列，那么就可以用这个值来算出具体是哪些列。
ref		ref 列显示使用哪个列或常数与key一起从表中选择数据行。指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时 。如果值是func，则使用的值是某个函数的结果
row		这一列是mysql评估，为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数目，也就是说它不是mysql认为它最终要从表里读取出来的行数，而是mysql为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。 rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。执行计划很容易描述这个很困难的统计量。查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。这是一种嵌套行算法。 简单且重要，数值越大越不好，说明没有用好索引
Extra		该列包含 MySQL 查询的详细信息
	Not exists	不存在信息
	range checked for each record	没有找到合适的索引
	Using index condition	出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错
	using temporary	mysql对查询结果进行排序的时候使用了一张临时表
	using filesort	mysql对数据不是按照表内的索引顺序进行读取,而是使用了其他字段重新排序
	using where	表示 MySQL 服务器从存储引擎收到查询数据,再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”