MySQL 基本概念了解及入门

       发现自己对 MySQL 的了解确实少得可怜，于是到菜鸟网站菜鸟教程进行最基础的学习入门，一个个参考例子进行学习与实践，个人觉得上面的例子很简单，对于初学者来讲还是可以的，当然顶多入个门吧。我在这想记录下，方便以后温习与查看。

       先下载mysql并安装完成后就可以通过MySql命令行敲命令并查看结果。

      

先了解下mysql基本一些概念：

Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多的行和列组成一张表单

5.若干的表单组成database

RDBMS 术语数据库: 

    数据库是一些关联表的集合。

    数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

    列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。

    行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

    冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。

    主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

    外键：外键用于关联两个表。

    复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

    索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

     参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

注意mysql命令都是以分号";"结束。

一开始往数据库插入中文时发现乱码了，即使在建表时设置了字符集，百度后就找到mysql安装目录下的my.ini配置文件查看，发现里面默认是 utf8编码，但还是乱码了，于是将其改成gbk试试发现居然可以解决乱码问题了。。如下图所示：

以下进入各个知识点的学习：

1、MySQL 创建数据表：需要表名、表字段名和定义每个表字段的信息。

通用语法： CREATE TABLE table_name ( column_name column_type );

在命令窗口敲入选择数据库后再敲入以下实例代码建表：

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;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

创建成功后可通过命令：desc `runoob_tbl`;或者show columns from `runoob_tbl`;查看表的结构。

2、MySQL删除数据表

语法：DROP TABLE table_name;

3、MySQL插入数据（类型要对应匹配）

语法：INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
如果需要插入全部字段则可以省略为：INSERT INTO table_name VALUES ( value1, value2,...valueN );

4、MySQL查询数据

语法：SELECT column_name,column_name FROM table_name [WHERE Clause] [OFFSET M ][LIMIT N]
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
你可以使用 LIMIT 属性来设定返回的记录数。

5、MySQL WHERE子句

语法：SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

6、MySQL UPDATE查询

语法：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

7、MySQL DELETE语句

语法：DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

8、MySQL LIKE子句

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

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

9、MySQL UNION操作符

   MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

  语法：

  SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]
  UNION [ALL | DISTINCT]
  SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

  参数：

  expression1, expression2, ... expression_n: 要检索的列。
  tables: 要检索的数据表。
  WHERE conditions: 可选， 检索条件。
  DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
  ALL: 可选，返回所有结果集，包含重复数据。

10、MySQL 排序

   语法：SELECT field1, field2,...fieldN from table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

   你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
   你可以设定多个字段来排序。
   你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
   你可以添加 WHERE...LIKE 子句来设置条件。

11、MySQL 分组

    GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

    GROUP BY 语法：

      SELECT column_name, function(column_name)
      FROM table_name
      WHERE column_name operator value
      GROUP BY column_name;

   示例及结果如下：

   

  使用 WITH ROLLUP

  WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

  

  其中记录 NULL 表示所有人的登录次数。
  我们可以使用 coalesce 来设置一个可以取代 NULL 的名称，coalesce 语法：

  select coalesce(a,b,c);
  参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

  以下实例中如果名字为空我们使用总数代替：

  

12、MySQL 连接的使用

   JOIN 按照功能大致分为如下三类：
     INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
     LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
     RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。 

    特点如下：

    

  示例如下：

  

  

  

   MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

   

   MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

   

  恰好上述例子比较特殊，假如左表不存在相应数据也会显示为 NULL。

13、MySQL NULL 值处理

  IS NULL: 当列的值是 NULL,此运算符返回 true。
  IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
  <=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
  关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
  在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。
  MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

  

14、MySQL 正则表达式

  MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
  如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。
  下表中的正则模式可应用于 REGEXP 操作符中。

  

  

  以下例子尝试匹配手机号码（首位非0，共11位）：

  

15、MySQL 事务

  MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本     资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ：有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事物控制语句：
BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

16、MySQL ALTER命令

   当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

   删除，添加或修改表字段

  如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

  ALTER TABLE testalter_tbl  DROP i;

  如果数据表中只剩余一个字段则无法使用DROP来删除字段。
  MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:ALTER TABLE   testalter_tbl ADD i INT;
  执行以上命令后，i 字段会自动添加到数据表字段的末尾。

  如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

  FIRST 和 AFTER 关键字只占用于 ADD 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

 修改字段类型及名称
   如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
   例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
   ALTER TABLE testalter_tbl MODIFY c CHAR(10);
   使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
   ALTER TABLE testalter_tbl CHANGE i j BIGINT;
   ALTER TABLE testalter_tbl CHANGE j j INT;

 修改字段默认值

   ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
  也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：
   ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
 修改表名
   ALTER TABLE testalter_tbl RENAME TO alter_tbl;
 修改存储引擎：修改为myisam
   alter table tableName engine=myisam;
 删除外键约束：keyName是外键别名
   alter table tableName drop foreign key keyName;

17、MySQL 索引
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

  

  

  

18、MySQL 临时表

  MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

  

  重启之后就没了临时表的数据了。

    手动删除MySQL 临时表：

  

19、MySQL 复制表

  如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，    是无法实现的。
  本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下：
  使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
  复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
  如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

  步骤一：
    获取数据表的完整结构。
    SHOW CREATE TABLE `tableName` \G;（格式化查看建表`tableName`时的创建语句）
  步骤二：
    修改SQL语句的数据表名为新表名，并执行SQL语句。
  步骤三：
    执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO `新表名` (字段名...) SELECT 字段名,.,.,. from `tableName`; 语句来实现。
  执行以上步骤后，你将完整的复制表，包括表结构及表数据。