✍LIKE 子句

说明：

使用 SELECT 来查询数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “字段(text_title) = ‘值()'”。

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

LIKE 子句中使用百分号 %字符来表示任意字符。

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

1、语法

SELECT field1, field2,...fieldN FROM text_tableWHERE field1 LIKE condition1 [AND [OR]] filed2 = 'value'

• 可以在 WHERE 子句中指定任何条件。
• 可以在 WHERE 子句中使用LIKE子句。
• 可以使用LIKE子句代替等号=。
• LIKE 通常与%一同使用，类似于一个元字符的搜索。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

2、演示

从 text_table 表中获取 text_title 字段中，包含 cs的所有记录。

SELECT * from text_tableWHERE text_title LIKE '%cs%';

✍UNION

1、建表

• 为了演示UNION 在建立一张表为(text_table2)语句如下

CREATE TABLE IF NOT EXISTS `text_table2`( `text_id` INT UNSIGNED AUTO_INCREMENT, `text_title` VARCHAR(100) NOT NULL, `text_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `text_id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 为 text_table2 表添加数据

INSERT INTO text_table2(text_title,text_author, submission_date )VALUES('安徒生童话', '臭弟弟', NOW()),( '小天鹅与丑小鸭', '你才是臭弟弟', NOW()),( '西游记2', '你才是臭弟弟', NOW()),( '西游记2', '你才是臭弟弟', NOW()),( '西游记','我不是臭弟弟', NOW(),( '西游记','我不是臭弟弟', NOW());

• 查看 “text_table”和 ” text_table2 “两表的数据

2、UNION

说明：

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

2.1、语法

SELECT expression1, expression2, ... expression_nFROM text_table[WHERE conditions]UNION [ALL | DISTINCT]SELECT expression1, expression2, ... expression_nFROM text_table[WHERE conditions];

• expression1, expression2, … expression_n: 要检索的列。

• tables:要检索的数据表。

• WHERE conditions:可选， 检索条件。

• DISTINCT:可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

• ALL:可选，返回所有结果集，包含重复数据。

2.2、演示

• 正常情况从 “text_table” 和 “text_table2 ” 表中选取所有不同的text_title（只有不同的值）

SELECT text_title FROM text_tableUNIONSELECT text_title FROM text_table2ORDER BY text_title;

注意：UNION 不能用于列出两个表中所有的text_title。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

3、UNION ALL

3.1、演示

• 使用 UNION ALL 从 “text_table” 和 “text_table2” 表中选取所有的text_title（也有重复的值）

SELECT text_title FROM text_tableUNION ALLSELECT text_title FROM text_table2ORDER BY text_title;

4、带WHERE 条件 UNION ALL

4.1、演示

• 使用 UNION ALL 从 “text_table” 和 “text_table2” 表中选取所有的作者(你才是臭弟弟)的数据（也有重复的值）

SELECT text_title,text_author FROM text_tableWHERE text_author='你才是臭弟弟'UNION ALLSELECT text_title,text_author FROM text_table2WHERE text_author='你才是臭弟弟'ORDER BY text_author;

✍排序

1、语法

说明：

使用 ORDER BY 子句将查询数据按某个字段排序后再返回数据

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

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

2、演示

• 将提交时间(submission_date)字段，按升序及降序排列。

SELECT * from text_table ORDER BY submission_date ASC

✍分组

说明：

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

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

1、语法

SELECT column_name, function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;

2、建表

• 新建一个员工日志表

DROP TABLE IF EXISTS `emp_log`;CREATE TABLE `emp_log` (`id` int(11) NOT NULL,`name` char(10) NOT NULL DEFAULT '',`date` datetime NOT NULL,`signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 给日志表 emp_log 添加数据

INSERT INTO `emp_log`VALUES( '1', '张三', '2023-06-22 15:25:33', '1' ),( '2', '张三', '2023-06-20 15:25:47', '3' ),( '3', '王五', '2023-06-19 15:26:02', '2' ),( '4', '弟弟', '2023-06-07 15:26:14', '4' ),( '5', '二狗', '2023-06-11 15:26:40', '4' ),( '6', '二狗', '2023-06-04 15:26:54', '2' );

3、演示

• 使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录

SELECT name, COUNT(*) FROM emp_log GROUP BY name;

3.1、使用 WITH ROLLUP

• WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT 等）。
• 例如将以上的数据表按名字进行分组，再统计每个人登录的次数

SELECT name, SUM(signin) as signin_count FROMemp_log GROUP BY name WITH ROLLUP;

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法

select coalesce(a,b,c,d);

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

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

SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROMemp_log GROUP BY name WITH ROLLUP;

✍连接的使用

说明：

使用 MySQL 的 JOIN 在两个或多个表中查询数据。

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

JOIN 大致分为三类如下：

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

1、查看数据

2、演示

2.1、INNER JOIN（内连接,或等值连接）

使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取text_table表中所有text_title字段在tcount_tbl表对应的runoob_count字段值

SELECT a.text_id, a.text_author,a.text_title FROM text_table a INNER JOIN text_table2 b ON a.text_title = b.text_title;

以上SQL表a和表b之间进行内连接，并选择a.text_id、a.text_author和a.text_title列。连接条件是a.text_title等于b.text_title。就是当a表text_title 等于 b表text_title 返回结果

• 以上 SQL 语句等价于以下SQL 语句

SELECT a.text_id, a.text_author,a.text_title FROM text_table a, text_table2 b WHERE a.text_title = b.text_title;

2.2、LEFT JOIN（左连接)

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

SELECT a.text_id, a.text_author,b.text_title FROM text_table a LEFT JOIN text_table2 b ON a.text_title = b.text_title;

以上SQL以text_table表为左表，text_table2表为右表

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 text_table的所有选取的字段数据，即使在右侧表 text_table2中 没有对应的 text_title 字段值。

2.3、RIGHT JOIN（右连接）

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

SELECT a.text_id, a.text_author,b.text_title FROM text_table a RIGHT JOIN text_table2 b ON a.text_title = b.text_title;

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 text_table2 的所有选取的字段数据，即使在左侧表 text_table中没有对应的 text_title字段值。

✍NULL 值处理

说明：

NULL是什么，查询条件某字段时某一行这个字段或其他行这个字段没有值所以查询出来为 NULL 。

为了处理这种情况，MySQL提供了三大运算符如下:

• IS NULL:当列的值是 NULL,此运算符返回 true。
• IS NOT NULL:当列的值不为 NULL, 运算符返回 true。
• :比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

1、建表

DROP TABLE IF EXISTS `text_null`; CREATE TABLE `text_null` (`id` int(11) NOT NULL,`text_title` char(10) COMMENT '书名',`text_author`char(10) COMMENT '作者',`price`double COMMENT '价格',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO text_null (id,text_title,text_author,price) values (1,'百科全书解决空值','火爆的后槽牙儿',6.66);INSERT INTO text_null (id,text_title,text_author,price) values (2,'这是一本书','小王',NULL);INSERT INTO text_null (id,text_title,text_author,price) values (3,'西游记','作者',NULL);

2、演示

2.1、查看新建的表数据

 SELECT * from text_null;

2.2、演示= 和 != 运算符

 SELECT * from text_nullwhere price = null; SELECT * from text_null where price != NULL;

可以看到 = 和 != 运算符是不起作用的

2.3、使用IS NULL和IS NOT NULL

SELECT * from text_null where price IS NULL;SELECT * from text_null where price IS NOT NULL;

✍正则表达式

说明：

前面了解了 MySQL可以通过LIKE …%来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

1、操作符

2、演示

• 查找text_title字段中以 ‘ja’ 为开头的所有数据

SELECT text_title from text_table2 where text_title REGEXP '^ja';

• 查找text_title字段中以’西’为开头的所有数据：

SELECT text_title FROM text_table2 WHERE text_title REGEXP '^西';

• 查找text_title字段中以’d’为结尾的所有数据：

SELECT text_title FROM text_table2 WHERE text_title REGEXP 'd$';

• 查找text_title字段中包含’JAVA’字符串的所有数据：

SELECT text_title FROM text_table2 WHERE text_title REGEXP 'JAVA';

• 查找text_title字段中以元音字符开头或以’d’字符串结尾的所有数据：

SELECT text_title FROM text_table2 WHERE text_title REGEXP '^[aeiou]|d$';

✍事务

1、事务说明

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

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 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开启自动提交

2、演示

mysql> use product; #选择数据库Database changedmysql> CREATE TABLE test_transaction( id int(5)) engine=innodb; # 创建数据表Query OK, 0 rows affected, 1 warning (0.27 sec)mysql> select * from test_transaction;Empty set (0.00 sec)mysql> begin; # 开始事务Query OK, 0 rows affected (0.03 sec)mysql> insert into test_transaction value(5);Query OK, 1 row affected (0.00 sec)mysql> insert into test_transaction value(6);Query OK, 1 row affected (0.00 sec)mysql> commit; # 提交事务Query OK, 0 rows affected (0.14 sec)mysql> select * from test_transaction;+------+| id |+------+|5 ||6 |+------+2 rows in set (0.00 sec)mysql> begin;# 开始事务Query OK, 0 rows affected (0.00 sec)mysql> insert into test_transaction values(7);Query OK, 1 row affected (0.00 sec)mysql> rollback;# 回滚Query OK, 0 rows affected (0.11 sec)mysql> select * from test_transaction; # 为什么没有ID为7的数据，因为执行了rollback回滚所以数据没有插入+------+| id |+------+|5 ||6 |+------+2 rows in set (0.00 sec)

需要注意的是，及时使用Navicat工具，指定数据新增或修改的时候，Navicat也是自动就帮我们进行事务的提交了，不像plsql那样，需要我们手动提交事务和手动回滚；

✍ALTER命令

说明：

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

1、建表

DROP TABLE IF EXISTS `text_ALTER`; CREATE TABLE `text_ALTER` (`id` int ,`price`INT) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、演示

2.1、删除表字段

• 使用 ALTER 命令及 DROP 子句来删除以上创建表好的 price 字段

ALTER TABLE text_alter DROP price;

2.2、添加表字段

• 如果数据表中只剩余一个字段则无法使用DROP来删除字段。
• MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 text_alter 中添加 price 字段，并定义数据类型

ALTER TABLE text_alter ADD price INT;

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

• 如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
• 尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化

ALTER TABLE text_alter DROP price;ALTER TABLE text_alter ADD price INT FIRST;ALTER TABLE text_alter DROP price;ALTER TABLE text_alter ADD price INT AFTER id;

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

2.3、修改表字段

• 如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
• 例如，把字段 price的类型从 INT改为 CHAR(10)，可以执行以下命令

 ALTER TABLE text_alter MODIFY price CHAR(10);

2.4、修改字段类型及名称

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

ALTER TABLE text_alter CHANGE id ids INT;ALTER TABLE text_alter CHANGE price prices INT;

• 显示text_alter 表所有列

mysql>SHOW COLUMNS FROM text_ALTER; #显示所有列+--------+------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+--------+------+------+-----+---------+-------+| ids| int| YES| | NULL| || prices | int| YES| | NULL| |+--------+------+------+-----+---------+-------+2 rows in set (0.00 sec)

2.5、ALTER TABLE 对 Null 值和默认值的影响

• 当你修改字段时，你可以指定是否包含值或者是否设置默认值。
• 以下实例，指定字段 price 为 NOT NULL 且默认值为100 。

ALTER TABLE text_alter MODIFY prices INT NOT NULL DEFAULT 100;

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

修改字段默认值

• 你可以使用 ALTER 来修改字段的默认值，实例如下

ALTER TABLE text_alter ALTER prices SET DEFAULT 1000;

• 查看列

SHOW COLUMNS FROM text_alter;

删除字段默认值

• 也可以使用 ALTER 命令及 DROP子句来删除字段的默认值

ALTER TABLE text_alter ALTER prices DROP DEFAULT;

修改数据表类型

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

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

ALTER TABLE text_alter ENGINE = MYISAM;SHOW TABLE STATUS LIKE 'text_alter'\G

修改表名

• 如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
• 尝试以下实例将数据表 text_alter 重命名为 text_alter_update

ALTER TABLE text_alter RENAME TO text_alter_update;

✍素引

1、索引说明

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

1、普通索引

普通索引这是最基本的索引，它没有任何限制。它有以下几种创建方式

1.1、创建索引

CREATE INDEX indexName ON table_name (column_name)

1.2、修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

1.3、创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

1.4、删除索引的语法

DROP INDEX [indexName] ON mytable; 

2、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式

2.1、创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

2.2、修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

2.3、创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

3、使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list):添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

表中添加索引：

ALTER TABLE text_alter ADD INDEX (prices);

也可以通过ALTER 命令使用 DROP 子句来删除索引：

ALTER TABLE text_alter DROP INDEX prices;

4、使用 ALTER 命令添加和删除主键

• 主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。

ALTER TABLE text_alter MODIFY ids INT NOT NULL;ALTER TABLE text_alter ADD PRIMARY KEY (ids);

• 也可以使用 ALTER 命令删除主键

ALTER TABLE text_alter DROP PRIMARY KEY;

5、显示索引信息

• 使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

SHOW INDEX FROM text_alter\G