Mysql基础使用

创建数据库并指定编码集

create database 数据库名 
default character set utf8 
collate utf8_general_ci;

修改编码集

# 库
alter database workspace collate utf8mb4_bin ;
# 表
alter table administrator collate utf8mb4_bin ;
# 字段
alter table administrator modify username varchar(64) collate utf8mb4_bin;

查看创建的数据库

show create database 库名;

删除数据库

drop database 数据库名;

选择数据库

use 数据库名;

建表

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除数据表

drop table 表名;

插入数据

insert into 表名 （字段名1,字段名2，字段名3）values (值1,值2,值3)

更新数据

update 表名 set 字段名1=值1,字段名2=值2 where

删除数据

delete from 表名 where

LIKE 子句

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

在 where like 的条件查询中，SQL 提供了四种匹配方式。

%：表示任意 0 个或多个字符。
可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
_：表示任意单个字符。
匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。
指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
[^] ：表示不在括号所列之内的单个字符。
其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

UNION 操作符

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

MySQL 连接的使用(内关联|左关联|右关联)

	CREATE TABLE `a_table` (
	  `a_id` int(11) DEFAULT NULL,
	  `a_name` varchar(10) DEFAULT NULL,
	  `a_part` varchar(10) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	
	CREATE TABLE `b_table` (
		`b_id` int(11) DEFAULT NULL,
		`b_name` varchar(10) DEFAULT NULL,
		`b_part` varchar(10) DEFAULT NULL
	)ENGINE = INNODB DEFAULT CHARSET = utf8;

表测试数据：

1. 内连接
   关键字：inner join

   	select * from a_table a inner join b_table bon a.a_id = b.b_id;
   

   执行结果：

   
   说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
   

2. 左连接（左外连接）
   关键字：left join

   	select * from a_table a left join b_table b  on a.a_id = b.b_id;
   

   执行结果：
   
   说明：left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
   左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
   

3. 右连接（右外连接）
   关键字：right join

   select * from a_table a right join b_table b on a.a_id = b.b_id;
   

   执行结果：
   
   说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
   与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

MySQL 正则表达式

REGEXP 操作符来进行正则表达式匹配
菜鸟教程查看详细

创建数据表的时候创建索引

基本的语法格式

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
);

UNIQUE：可选。表示索引为唯一性索引。
FULLTEXT；可选。表示索引为全文索引。
SPATIAL：可选。表示索引为空间索引。
INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名：可选。给创建的索引取一个新名称。
字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度：可选。指索引的长度，必须是字符串类型才可以使用。
ASC：可选。表示升序排列。
DESC：可选。表示降序排列。

1. 创建普通索引

   CREATE TABLE index1(id INT,
                       name VARCHAR(20),
                       sex BOOLEAN,
                       INDEX(id)
   );
   

2. 创建唯一性索引

   CREATE TABLE index2(id INT UNIQUE,
                       name VARCHAR(20),
                       UNIQUE INDEX index2_id(id ASC)
   );
   

3. 创建全文索引

   CREATE TABLE index3(id INT,
                       info VARCHAR(20),
                       FULLTEXT INDEX index3_info(info)
   )ENGINE=MyISAM;
   

4. 创建单列索引

   CREATE TABLE index4(id INT,
                       subject VARCHAR(30),
                       INDEX index4_st(subject(10))
   );
   

   注意：subject字段长度为30，而index4_st设置的索引长度只有10，这样做是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，而只查询它前面的若干字符信息。

5. 创建多列索引

   CREATE TABLE index5(id INT,
                   name VARCHAR(20),
                   sex CHAR(4),
                   INDEX index5_ns(name,sex)
   );
   

   那么，如果在name，sex这两个列上分别创建单列索引，效果是否和创建一个name，sex的多列索引一样呢？

   答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有两个单列的索引，MySQL会试图选择一个限制最严格的索引。 但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于name，sex这两个列上的多列索引。
   多列索引中最左前缀（Leftmost Prefixing）
   只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

给已有表添加/删除外键和唯一键

1. 添加外键的语句：

   ALTER TABLE 表名
   
   ADD CONSTRAINT 外键名
   
   FOREIGN KEY (需要设为外键的字段)
   
   REFERENCES 参考表名(参考字段);
   

2. 删除外键的语句：

   ALTER TABLE 表名
   
   DROP FOREIGN KEY 外键名;
   

3. 添加唯一键的语句：

   ALTER TABLE 表名
   
   ADD UNIQUE 唯一键名 (字段1, 字段2, 字段3);
   

4. 删除唯一键的语句：

   ALTER TABLE 表名
   
   DROP INDEX 唯一键名;
   

   注：如果需要删除唯一键的列也有外键约束的话，需要首先删除外键约束。

排查MySQL的问题的方法

show processlist

show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果：

select * from information_schema.processlist

mysql: show processlist 详解