MySql基础知识

数据库的基本操作

查看数据库：SHOW DATABASES;

创建数据库：CREATE DATABASE database_name;

删除数据库：DROP DATABASE database_name;

查看mysql支持的存储引擎：SHOW ENGINES;

InnoDB 存储引擎：

1、支持事务，支持行锁

2、大数据处理性能好，CPU效率比别的存储引擎高

3、在主内存中缓存数据和索引维持缓冲池

4、支持外检约束

MyISAM 存储引擎：

1、支持大文件

2、执行dml时产生更少的碎片

3、BLOB和TEXT列可以使用索引

4、每个字符列可以有不同的字符集

数据表的基本操作

主键可以加快数据表查询的速度，分为单字段和多字段联合主键

1、定义列时指定主键

CREATE TABLE emp(

 id INT(11) PRIMARY KEY,

 name VARCHAR(25)

);

2、定义完所有列时指定主键

CREATE TABLE emp(

 id INT(11) ,

 name VARCHAR(25)，

 PRIMARY KEY(id,name)

);

3、定义外键

CREATE TABLE emp(

 id INT(11) PRIMARY KEY,

 deptId INT(11),

 name VARCHAR(25)，

 CONSTRAINT fk_emp_dept FOREIGN KEY(deptId)  REFERENCES dept(id)

);

4、非空约束

CREATE TABLE emp(

 id INT(11) PRIMARY KEY,

 deptId INT(11),

 name VARCHAR(25) NOT NULL

);

5、唯一性约束

CREATE TABLE emp(

 id INT(11) PRIMARY KEY,

 deptId INT(11),

 name VARCHAR(25) UNIQUE

);

6、默认约束

CREATE TABLE emp(

 id INT(11) PRIMARY KEY,

 deptId INT(11) DEFAULT 111,

 name VARCHAR(25) 

);

7、查看数据表结构：DESCRIBE 表名 或者 DESC 表名

8、查看建表语句：SHOW CREATE TABLE tb_name

9、修改表名：ALTER TABLE 旧表名 RENAME [TO] 新表名

10、修改字段的数据类型：ALTER TABLE 表名 MODIFY 字段名 数据类型

11、修改字段名：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型

12、添加字段：ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST | AFTER 已存在字段名]

13、删除字段：ALTER TABLE 表名 DROP 字段名

14、修改字段的排列位置：ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST | AFTER 字段2

15、更改表的存储引擎：ALTER TABLE 表名 ENGINE=更改后的存储引擎名

16、删除表的外键约束：ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

17、删除没有被关联的表：DROP TABLE [IF EXISTS] 表1, 表2, ...

18、删除被其他表关联的主表：先解除关联关系，在删

19、并不是每一个表一定要有一个主键

20、并不是每个表都可以任意选择存储引擎，外键是不能跨存储引擎的

数据类型和运算符

1、整数类型：该类型字段可以添加 AUTO_INCREMENT 自增约束条件

TINYINT  1个字节

SMALLINT  2个字节

MEDIUMINT 3个字节

INT(INTEGER) 4个字节

BIGINT 8个字节

注意：如 year INT(4), 这表示year一半显示4位数字的宽度，和取值范围是无关的，只要取值不超过该类型最大范围就可以插入

2、浮点数类型和定点数类型

FLOAT 4个字节

DOUBLE 8个字节

DECIMAL(M,D) M+2个字节

3、日期与时间类型

YEAR   YYYY                                1个字节

TIME    HH:MM:SS                        3个字节

DATE   YYYY-MM-DD                    3个字节

DATETIME  YYYY-MM-DD HH:MM:SS  8个字节

TIMESTAMP   YYYY-MM-DD HH:MM:SS  4个字节

4、文本字符串类型

CHAR(M)： 固定长度字符串

VARCHAR(M)：长度可变字符串，实际占用空间为字符串实际长度加1

CHAR(4) 和 VARCHAR(4) 对比

插入值              CHAR(4)                  存储需求                    VARCHAR(4)               存储需求

   ''                        '    '                        4个字节                            ''                              1个字节

 'ab'                      'ab  '                      4个字节                            'ab'                           3个字节

'abcdef'                'abcd'                     4个字节                          'abcd'                         5个字节

TEXT : 保存非二进制字符串（TINYINT,TEXT,MEDIUMTEXT,LONGTEXT）

BLOB : 二进制字符串，主要存储图片，音频信息等

5、算术运算符

= 用来判断数字、字符串和表达式是否相等，与类型无关

expr BETWEET min AND max  expr 大于或等于min 且小于或等于max

LEAST(值1，值2，...值n),返回最小的一个

GREATEST(值1,值2,...值n),返回最大的一个

6、IF(expr,v1,v2),如果表达式是TRUE（expr <> 0 and exp <> null）,则返回v1，否则返回v2

查询数据

1、HAVING 在数据分组之后进行过滤来选择分组，而WHERE 在分组之前用来选择记录

2、使用limit限制查询结果的数量

3、内连接

      非标准内连接：SELECT  *  FROM  emp e,dept d WHERE e.dept_id = d.id

      标准规范内连接：SELECT  *  FROM  emp e INNER JOIN dept d ON e.dept_id = d.id    (推荐的方式)

4、外连接

      左连接（左外连接）：LEFT JOIN(LEFT OUTER JOIN),返回左表中的所有记录和右表中符合条件的记录

      右连接（右外连接）：RIGHT JOIN(RIGHT OUTER JOIN),返回右表中的所有记录和左表中符合条件的记录

5、合并查询结果：两个表对应的列数和数据类型必须相同

     UNION：会过滤重复的记录

     UNIIN ALL：不会过滤重复记录

索引

1、索引优点：

     1）、唯一索引可以保证数据的唯一性

     2）、可以大大加快数据的查询速度

     3）、加速表和表之间的链接

     4）、使用分组和排序时，可以减少查询中分组和排序时间

2、索引的缺点：

     1）、创建和维护索引会耗费时间，并且随着数据量的增加所耗费的时间也会增加

     2）、每一个索引要占据一定的物理空间，如果有大量索引，索引文件会吃掉很多磁盘空间

     3）、对表进行dml操作时，索引也会动态维护，这样会降低dml操作的执行速度

3、索引的分类：

    1）、普通索引和唯一索引

    2）、单列索引和组合索引

    3）、全文索引（只有MyISAM存储引擎支持）

    4）、空间索引（只有MyISAM存储引擎支持）

4、索引的设计原则：

    1）、索引并非越多越好，大量索引会占据磁盘空间，影响DML操作的性能

    2）、避免对经常更新的表进行过多的索引，索引列尽可能少，对经常做查询条件的字段添加索引

    3）、数据量小的表最好不要建索引，查询的时间可能比遍历索引的时间少

    4）、在不同值较多的列上建立索引，在不同值较少的列上不要建立索引（比如性别、婚否等）

    5）、当唯一性是某种数据本身的特征时，指定唯一索引

    6）、在频繁进行排序或分组的列上建立索引，如果有多个可以建立组合索引

5、创建索引

    1）、创建表时创建索引：INDEX index_name(col_name1,col_name2...)、UIQUE INDEX index_name(col_name1,col_name2...)

    2）、在已经存在表中创建索引：ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX  index_name (col_name[length],...)  [ASC | DESC]

                                                        CREATE [UNIQUE | FULLTEXT | SPATIAL]  INDEX  index_name  ON table_name (col[length],...) [ASC | DESC]

6、删除索引

   1）、ALTER TABLE table_name DROP INDEX index_name;

   2）、DROP INDEX index_name ON table_name;

7、尽量使用短索引

8、使用like关键字时，如果匹配字符第一个为"%"，索引则不会生效，只有"%"不在第一个位置时才生效

9、对于多列索引，只有查询条件中使用了这些字段中的第一个时，索引才会生效

10、使用or关键字时，只有or前后的字段列都是索引时，查询时才使用索引

11、优化子查询时尽量使用join

12、优化数据结构应该考虑数据的冗余、查询和更新的速度、字段数据类型是否合理等

13、优化插入速度，禁用索引、禁用唯一性检查

#####索引失效场景

1.WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引

2.类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引

3.在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用

4.如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条件是LIKE 'abc%',MYSQL将使用索引；如果条件是LIKE '%abc'，MYSQL将不使用索引。

5.在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。

6.如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。

7.索引有用的情况下就太多了。基本只要建立了索引，除了上面提到的索引不会使用的情况下之外，其他情况只要是使用在WHERE条件里，ORDER BY 字段，联表字段，一般都是有效的。 建立索引要的就是有效果。 不然还用它干吗？ 如果不能确定在某个字段上建立的索引是否有效果，只要实际进行测试下比较下执行时间就知道。

8.如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

9.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

10.如果mysql估计使用全表扫描要比使用索引快,则不使用索引