Mysql 知识点总结(持续更新)

MySQL 创建数据库

使用 mysqladmin 创建数据库

使用root用户登录，root用户拥有最高权限，可以使用 mysql mysqladmin 命令来创建数据库。

实例
以下命令简单的演示了创建数据库的过程，数据名为 Student

[root@host]# mysqladmin -u root -p create Student
Enter password:******

以上命令执行成功后会创建 MySQL 数据库 Student

MySQL 删除数据库

使用 mysqladmin 删除数据库

以下实例删除数据库Student (该数据库在前一章节已创建)：

[root@host]# mysqladmin -u root -p drop Student
Enter password:******

执行以上删除数据库命令后，会出现一个提示框，来确认是否真的删除数据库：

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the ‘W3CSCHOOL’ database [y/N] y
Database “Student” dropped

MySQL 选择数据库

有时候可能有多个数据库,那么该如何选取其中一个呢?
从命令提示窗口中选择MySQL数据库,可以使用SQL命令来选择指定的数据库。
实例
以下实例选取了数据库Student

[root@host]# mysql -u root -p
Enter password:******
mysql> use Student;
Database changed
mysql>

注意:所有的数据库名，表名，表字段都是区分大小写的。所以使用SQL命令时需要输入正确的名称。

MySQL 数据类型

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

数值类型

日期和时间类型

字符串类型

MySQL where 子句

我们知道从MySQL表中使用 SELECT 语句来读取数据。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法
以下是SQL SELECT 语句使用 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 表中的字段值来读取指定的数据。

MySQL UPDATE 查询

如果我们需要修改或更新MySQL中的数据，可以使用 SQL UPDATE 命令来操作。.

语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用SQL语法：

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

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

通过命令提示符更新数据
以下我们将在 SQL UPDATE 命令使用 WHERE子句来更新w3cschool_tbl表中指定的数据：

实例
以下实例将更新数据表中 softWare_id 为 3 的 softWare_title 字段值：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use Student;
Database changed
mysql> UPDATE softWare_tbl
    -> SET softWare_title='Learn' 
    -> WHERE softWare_id=3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

MySQL DELETE 语句

可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

DELETE FROM table_name [WHERE Clause]

1.如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
2.可以在 WHERE 子句中指定任何条件
3.可以在单个表中一次性删除记录。
以下实例将删除 student_tbl 表中 student_id 为3 的记录：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> DELETE FROM student_tbl WHERE student_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

MySQL LIKE 子句

我们可以在MySQL中使用 SQL SELECT 命令来读取数据， 同时可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 (=) 来设定获取数据的条件，如 “student_teacher = ‘San’”。

但是有时候我们需要获取 student_teacher 字段含有 “S” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。

如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'S'

以下是获取 student_teacher 字段含有 “S” 字符为``结尾的所有记录的：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> SELECT * from student_tbl 
    -> WHERE student_teacher LIKE '%S';

1 rows in set (0.01 sec)

mysql>

MySQL 排序

如果我们需要对读取的数据进行排序，就可以使用MySQL的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

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

MySQL 分组

GROUP BY 语句根据一个或多个列对结果集进行分组。

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

MySQL 连接的使用

可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

1.INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
2.LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
3.RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

MySQL 正则表达式

MySQL 支持正则表达式的匹配

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，删除一个人员，即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
事务用来管理 insert , update , delete 语句。
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性或不可分割性）、Consistency（一致性）、Isolation（隔离性或独立性）、Durability（持久性）

1、原子性：一组事务，要么成功；要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。
2、一致性 ： 一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。
3、隔离性：数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离可分为：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。
4、持久性：事务在处理结束后对数据做出的修改是永久的，无法丢失。
事务控制语句
1，显式的开始一个事务：

start transaction

begin

2, 做保存点，一个事务中可以有多个保存点：

savepoint 保存点名称

3, 提交事务，并使数据库中进行的所有修改成为永久性的：

commit
commit work

4，回滚结束用户的事务，并撤销正在进行的所有未提交的修改：

rollback
rollback work

5.删除一个事务的保存点，若没有指定保存点，执行该语句操作会抛错。

release savepoint 保存点名称

6.将事务滚回标记点：

rollback to 标记点

7.设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

set transaction

事务处理方法

1.用 begin ， rollback ， commit 来实现事务处理。

2.用 set 来改变 MySQL 的自动提交模式。

set autocommit = 0 （禁止自动提交）。
set autocommit = 1 （开启自动提交）。
MySQL ALTER命令
当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

开始本章教程前让我们先创建一张表，表名为：testalter_tbl。

root@host# mysql -u root -p password;
Enter password:
mysql> use W3CSCHOOL;
Database changed
mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

删除，添加或修改表字段

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

mysql> ALTER TABLE testalter_tbl  DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

MySQL 中使用 ADD 子句来想数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

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

尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

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

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

如果你现在想把字段 j 从 BIGINT 修改为 INT，SQL语句如下：

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含只或者是否设置默认值。

以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值
你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)
你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：

cpp
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)
Changing a Table Type:

修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：

注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;