mysql读书笔记

1、初识MYSQL

什么是MySQL

MySQL是一个小型关系数据库管理系统，与其他大型数据库管理系统例如Oracle、DB2、SQL Server等相比，MySQL规模小、功能有限，但是它体积小、速度快、成本低，且它提供的功能对稍微复杂的应用来说已经够用，这些特性使得MySQL成为世界上最受欢迎的开放源代码数据库。

MySQL的优势

MySQL的主要优势如下：
（1）速度：运行速度快。
（2）价格：MySOL对多数个人用来说是免费的。
（3）容易使用：与其他大型数据库的设置和管理相比，其复杂程度较低，易于学习。
（4）可移植性：能够工作在众多不同的系统平台上，例如：Windows、Linux、Unix、MacOS等。
（5）丰富的接口：提供了用于C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tel等语言的API。
（6）支持查询语言：MySQL可以利用标准SQL语法和和支持ODBC（开放式数据库连接）的应用程序。
（7）安全性和连接性：十分灵活和安全的权限和密码系统，允许基于主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码安全。并且由于MySQL是网络化的，因此可以在因特网上的任何地方访问，提高数据共享的效率。

登录mysql的两种方式

show数据库：SHOW DATABASES；

MySQL安装完成之后，将会在其data目录下自动创建几个必需的数据库，可以使用SHOW DATABASES；语句来查看当前所有存在的数据库，输入语句如下。

创建数据库：CREATE DATABASE database_name;

删除数据库：DROP DATABASE database_name;

使用DROPDATABASE命令时要非常谨慎，在执行该命令时，MySQL不会给出任何提醒确认信息，DROP DATABASE声明删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复。

数据库存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySOL的核心就是存储引擎。

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL5.5支持的存储引擎有：InnoDB，MylSAM，Memory，Merge，Archive，Federated，CSV，BLACKHOLE等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型，结果如下。

InnoDB存储引擎

InnoDB事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL5.5.5之后，InnoDB作为默认存储引擎，lnnoDB主要特性有：
（1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表与其他MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
（2）InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
（3）lnnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。lnnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
（4）InnoDB支持外键完整性约束（FOREIGNKEY）。
存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，lnnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。
（5）InnoDB被用在众多需要高性能的大型数据库站点上。
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdatal的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfilel的5MB大小的日志文件。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。在MySQL5.5.5之前的版本中，MyISAM是默认存储引擎。MyISAM主要特性有：
（1）大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。
（2）当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。
（3）每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16个。
（4）最大的键长度是1000字节，这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的键将被用上。
（5）BLOB和TEXT列可以被索引。
（6）NULL值被允许在索引的列中。这个值占每个键的0-1个字节。
（7）所有数字键值以高字节优先被存储以允许一个更高地索引压缩。
（8）每表一个AUTOINCREMENT列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。
（9）可以把数据文件和索引文件放在不同目录。
（10）每个字符列可以有不同的字符集。
（11）有VARCHAR的表可以固定或动态记录长度。
（12）VARCHAR和CHAR列可以多达64KB。
使用MyISAM引擎创建数据库，将生产3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD（MYData），索引文件的扩展名是.MY1
（MYIndex）。

MEMORY 存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。
MEMORY主要特性有：
（1）MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度。
（2）MEMORY存储引擎执行HASH和.BTREE索引。
（3）可以在一个MEMORY表中有非唯一键。
（4）MEMORY表使用一个固定的记录长度格式。
（5）MEMORY不支持BLOB或TEXT列。
（6）MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。
（7）MEMORY表在所有客户端之间共享（就像其他任何非TEMPORARY表）。
（8）MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享。
（9）当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）。

存储引擎的选择

不同存储引擎都有各自的特点，以适应不同的需求，如表3.1所示。为了做出选择，首先需要考虑每一个存储引擎提供了哪些不同的功能。

如果要提供提交，回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是个很好的选择。
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。
如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据，如记录日志信息可以使用Archive引擎。
使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

准备操作zoo数据库：USE zoo；

查看默认存储引擎

在前面介绍了使用SHOW ENGINES语句查看系统中所有的存储引擎，其中包括默认的存储引擎，还可以使用一种直接的方法查看默认存储引擎，输入语句如下：
mysql> SHOW VARIABLES LIKE 'storage_engine‘；

执行结果直接显示了当前默认的存储引擎。MySQL不同版本中的默认存储引擎不同，MySQL允许修改默认存储引擎，方法是修改配置文件。
在Windows平台下，设置数据库默认存储引擎需要修改配置文件my.ini。例如，将MySQL5.5的默认存储引擎修改为MyISAM。首先打开my.ini，将[mysqld]字段下面的default-storage-engine参数后面的值，由“InnoDB”改为“MylSAM”，保存文件，重新启动MySQL即可。

创建完数据库之后，就是创建数据表了！

在数据库中，数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。数据表被定义为列的集合，数据在表中是按照行和列的格式来存储的。每一行代表一条唯一的记录，每一列代表记录中的一个域。
本章将详细介绍数据表的基本操作，主要内容包括：创建数据表、查看数据表结构、修改数据表、删除数据表。通过本章的学习，读者能够熟练掌握数据表的基本概念，理解约束、默认和规则的含义并且学会运用；能够在图形界面模式和命令行模式下熟练地完成有关数据表的常用操作。

数据表操作

使用主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够惟一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单字段主键和多字段联合主键。

使用外键约束

查看表数据结构：DECRIBE 表名；

查看表详细结构语句：SHOW CREATE TABLE；

修改数据表：ALTER TABLE tb dept3 RENAME tb_deptment3;

修改字段的数据类型：ALTER TABLE tb_deptl MODIFY name VARCHAR(30);

修改字段名；ALTER TABLE tb_deptl CHANGE location loc VARCHAR(50);

添加字段：ALTER TABLE tb deptl ADD managerld INT(10);（无约束）。ALTER TABLE tb_deptl ADD columnl VARCHAR(12)not null;（有约束）

第一列添加一个字段：ALTER TABLE tb_dept 1ADD column2 INT(11)FIRST;

指定列添加字段：ALTER TABLE tb deptl ADD column3 INT(11)AFTER name;（在name之后）

删除字段：ALTER TABLE tb deptl DROP column2;

修改字段为表的第一个字段：ALTER TABLE tb deptI MODIFY columnI VARCHAR（12）FIRST；

修改字段到表的指定列之后: ALTER TABLE tb_deptl MODIFY columnl VARCHAR（12）AFTER location；

查看表的存储引擎：SHOW CREATE TABLE tb_deptment3\G;

修改表的存储引擎：ALTER TABLE tb deptment3 ENGINE=MyISAM;

删除外键约束：ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

删除数据表：DROP TABLE IF EXISTS tb_dep2;（无外键约束）

删除有外键约束的数据表：ALTER TABLE tb_tmp DROP FOREIGN KEY fk_emp_dept; + DROP TABLE tb_dept2;

mysql 数据类型

MySQL函数

绝对值函数ABS（x）和返回圆周率的函数PI（）

平方根函数SQRT（x）和求余函数MOD（x，y）

获取整数的函数CEIL（x）、CEILING（x）和FLOOR（x）

获取随机数的函数RAND（）和RAND（x）

四舍五入函数ROUND（x）、ROUND（X，y）和TRUNCATE（x，y）

符号函数SIGN（x）

计算字符串字符数的函数和字符串长度的函数: CHAR_LENGTH(str)

合并字符串函数CONCAT（s1，s2……）、CONCAT_WS（x，s1，s2……）

字符串str中的字母字符全部转换成小写字母:LOWER（str）或LCASE（str）

获取指定长度的字符串的函数LEFT（S，n）和RIGHT（s，n）

获取指定长度的字符串的函数LEFT（S，n）和RIGHT（s，n）

填充字符串的函数LPAD（s1，len，s2）和RPAD（s1，len，s2）

删除空格的函数LTRIM（s）、RTRIM（s）和TRIM（s）

删除指定字符串的函数 TRIM（s1 FROMs）

重复生成字符串的函数REPEAT（s，n）

空格函数SPACE（n）和替换函数REPLACE（s，s1，s2）

比较字符串大小的函数STRCMP（s1，s2）

获取子串的函数SUBSTRING（s，n，len）和MID（s，n，len）

匹配子串开始位置的函数 LOCATE（Strl，str）、POSITION（str1 IN str）和INSTR（str，strl）

字符串逆序的函数REVERSE（s）

返回指定位置的字符串的函数ELT（N，字符串1，字符串2，字符串3…，字符串N）

返回指定字符串位置的函数FIELD（s，s1，s2…）

返回子串位置的函数FIND_IN_SET（s1，s2）

条件判断函数

查询数据