MySQL 笔记3

Love The Way You Lie 2023-06-16 15:52 15阅读 0赞

### MySQL注释的三种写法 ###

1.单行注释使用"--"  
需要特别注意！这种注释"--"后面要加一个空格！

2.单行注释使用"\#"

3.多行注释使用"/\*\*/"

\[例\]  
\-- I am a commnet.Attention:Please note the space ahead me!

\#I am a commnet.

/\*I am a commnet.  
I am another commnet.  
We are commnents\*/

### MySQL GROUP BY 语句 ###

语法:  
SELECT *column\_name, function(column\_name)*  
FROM *table\_name*  
WHERE *column\_name operator value*  
GROUP BY c*olumn\_name;*

\[例\] 以下数据表记录了一个查询系统的用户登录时长，一条记录代表某用户的一次登录

SET NAMES utf8;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    --  Table structure for `querysys_tbl`
    -- ----------------------------
    DROP TABLE IF EXISTS `querysys_tbl`;
    CREATE TABLE `querysys_tbl` (
      `id` int(11) NOT NULL,
      `userid` char(10) NOT NULL DEFAULT '',
      `logintime` datetime NOT NULL,
      `timespending` tinyint(4) NOT NULL DEFAULT '0' COMMENT '在线时长(单位:分钟)',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    --  Records of `querysys_tbl`
    -- ----------------------------
    BEGIN;
    INSERT INTO `employee_tbl` VALUES ('1', '小明', '2019-04-22 15:25:33', '1'), ('2', '小王', '2019-04-20 15:25:47', '3'), ('3', '小丽', '2019-04-19 15:26:02', '2'), ('4', '小王', '2019-04-07 15:26:14', '4'), ('5', '小明', '2019-04-11 15:26:40', '4'), ('6', '小明', '2019-04-04 15:26:54', '2');
    COMMIT;
    
    SET FOREIGN_KEY_CHECKS = 1;

注：例子改编自 *菜鸟教程 **[https://www.runoob.com/mysql/mysql-group-by-statement.html][https_www.runoob.com_mysql_mysql-group-by-statement.html]*

说明（可跳过，与GROUP BY无关）：

1）SET NAMES utf8;用于设置编码，可以在建库时设置，也可以在建表时设置，或只是对部分字段进行设置。  
*参考：MYSQL: set names utf8是什么意思? [https://www.cnblogs.com/yanzi-meng/p/9184139.html][https_www.cnblogs.com_yanzi-meng_p_9184139.html]*

2）在MySQL中删除一张表或一条数据的时候，可能会因为MySQL中设置了foreign key关联，造成无法更新或删除数据。  
可以通过设置 SET FOREIGN\_KEY\_CHECKS=0; 来禁用外键约束  
之后再用 SET FOREIGN\_KEY\_CHECKS=1; 来启动外键约束  
命令 SELECT  @@FOREIGN\_KEY\_CHECKS; 可以查看当前FOREIGN\_KEY\_CHECKS的值

3）反引号是为了区分 MySQL 关键字与普通字符而引入的符号，一般库名、表名与字段名使用反引号。

4）PRIMARY KEY关键字用于定义列为主键。可以使用多列来定义主键，列间以逗号分隔。  
5）ENGINE 设置存储引擎，CHARSET 设置编码。

6）BEGIN 开始一个事务,ROLLBACK 事务回滚,COMMIT 事务确认。详见MYSQL的事务处理。  
*参考：  
BEGIN...COMMIT和SET AUTOCOMMIT   [https://ask.csdn.net/questions/260117][https_ask.csdn.net_questions_260117]  
数据库sql中commit的重要性   [https://blog.csdn.net/leilei7407/article/details/100850395][https_blog.csdn.net_leilei7407_article_details_100850395]*

7）INSERT 插入多条数据  
INSERT INTO table\_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)...;

导入成功后，查询数据表如下

mysql> SELECT * FROM querysys_tbl;
    +----+--------+---------------------+--------------+
    | id | userid | logintime           | timespending |
    +----+--------+---------------------+--------------+
    |  1 | 小明   | 2019-04-22 15:25:33 |      1       |
    |  2 | 小王   | 2019-04-20 15:25:47 |      3       |
    |  3 | 小丽   | 2019-04-19 15:26:02 |      2       |
    |  4 | 小王   | 2019-04-07 15:26:14 |      4       |
    |  5 | 小明   | 2019-04-11 15:26:40 |      4       |
    |  6 | 小明   | 2019-04-04 15:26:54 |      2       |
    +----+--------+---------------------+--------------+
    6 rows in set (0.00 sec)
    mysql>

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

mysql> SELECT userid, COUNT(*) FROM querysys_tbl GROUP BY name;
    +--------+----------+
    | userid | COUNT(*) |
    +--------+----------+
    | 小丽   |        1 |
    | 小明   |        3 |
    | 小王   |        2 |
    +--------+----------+
    3 rows in set (0.01 sec)
    mysql>

**WITH ROLLUP** 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。  
例如我们将以上的数据表按用户名进行分组，再统计每个用户登录的时长：

mysql> SELECT userid, SUM(timespending) as timesp_total FROM  querysys_tbl GROUP BY userid WITH ROLLUP;
    +--------+----------------+
    | userid   | timesp_total |
    +----------+--------------+
    | 小丽     |            2 |
    | 小明     |            7 |
    | 小王     |            7 |
    | NULL     |           16 |
    +--------+----------------+
    4 rows in set (0.00 sec)
    mysql>

其中记录 NULL 表示所有用户的在线时长。  
可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：select coalesce(a,b,c);  
参数说明：如果a!=null,则选择a;如果a==null,则选择b;如果b==null,则选择c;如果都为nul,则返回为null。  
*具体的查询语句如下：*  
SELECT coalesce(userid, '总数'), SUM(timespending) as timesp\_total FROM  querysys\_tbl GROUP BY userid WITH ROLLUP;

**HAVING**：用于对WHERE和GROUP BY查询出来的分组经行过滤，查出满足条件的分组结果。HAVING 子句可以让我们筛选成组后的各种数据。WHERE 子句在聚合前先筛选记录，而 HAVING子句在聚合后对组记录进行筛选

执行顺序 select –>where –> group by–> having–>order by  
*参考：mysql having的用法 [https://blog.csdn.net/love\_xsq/article/details/42417917][https_blog.csdn.net_love_xsq_article_details_42417917]*

### MySQL NULL值处理 ###

不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。在 MySQL 中，NULL = NULL 返回false 。  
*三大运算符*  
IS NULL: 当列的值是 NULL,此运算符返回 true。  
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。  
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

### MySQL 正则表达式 ###

传送门-> 菜鸟教程 https://www.runoob.com/mysql/mysql-regexp.html

\[例\]

### MySQL 事务  ###

传送门-> 菜鸟教程 https://www.runoob.com/mysql/mysql-transaction.html  
InnoDB与MyISAM区别 *参考：  
https://blog.csdn.net/handoking/article/details/90107543  
https://blog.csdn.net/qq\_35642036/article/details/82820178*

B树在存储引擎中的实现方式*  https://my.oschina.net/u/3670641/blog/3045464（第6节）*  
**Myisam**  
非聚簇索引，数据和索引分别存储。  
索引文件xx.MYI  
数据文件xx.MYD  
叶子节点保存的是引用地址而非数据  
**InnoDB**  
聚簇索引，数据和索引保存在一起  
文件xx.ibd  
在叶子节点保存对应的所有数据  
以主键索引来组织数据，没有主键的话，会帮我们隐式创建主键索引  
辅助索引不存地址，存主键，这样便于维护

<table style="width:392px;"> 
 <tbody> 
  <tr> 
   <td style="width:96px;">引擎</td> 
   <td style="width:92px;">事务</td> 
   <td style="width:98px;">外键</td> 
   <td style="width:102px;">适合场景</td> 
  </tr> 
  <tr> 
   <td style="width:96px;">InnoDB</td> 
   <td style="width:92px;">支持</td> 
   <td style="width:98px;">支持</td> 
   <td style="width:102px;">增加、修改</td> 
  </tr> 
  <tr> 
   <td style="width:96px;">MyISAM</td> 
   <td style="width:92px;">不支持</td> 
   <td style="width:98px;">不支持</td> 
   <td style="width:102px;">查询</td> 
  </tr> 
 </tbody> 
</table>

、

### MySQL ALTER命令 ###

传送门-> 菜鸟教程 [https://www.runoob.com/mysql/mysql-alter.html][https_www.runoob.com_mysql_mysql-alter.html]

**查看表结构的两条命令**

SHOW COLUMNS FROM *table\_name*; 或   
DESC *table\_name*;

### MySQL索引 ###

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大则查询所花费的时间就越多。如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据。  
【例】有一张2W条记录的表，记录着2W个人的信息。其中Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。如果有索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。

索引分单列索引和组合索引：  
单列索引，即一个索引只包含单个列  
组合索引，即一个索引包含多个列

优点：加快数据的查询速度  
缺点：创建索引和维护索引要耗费时间，对表中的数据进行增加、删除、修改时，索引也需要动态的维护  
      索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值  
 =>  
对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引。  
数据量小的表最好不要使用索引，因为查询全部数据花费的时间可能比遍历索引的时间还要短。  
在取值少的列上(字段上)不要建立索引，比如"性别"字段上只有男，女两个不同值。在一个字段上不同值较多可以建立索引。

*参考：MySQL索引详细介绍  [https://www.jianshu.com/p/0d6c828d3c70][https_www.jianshu.com_p_0d6c828d3c70]  
命令参考菜鸟教程  [https://www.runoob.com/mysql/mysql-index.html][https_www.runoob.com_mysql_mysql-index.html]*

## ##

### 获取服务器元数据 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>SELECT VERSION( )</td> 
   <td>服务器版本信息</td> 
  </tr> 
  <tr> 
   <td>SELECT DATABASE( )</td> 
   <td>当前数据库名 (或者返回空)</td> 
  </tr> 
  <tr> 
   <td>SELECT USER( )</td> 
   <td>当前用户名</td> 
  </tr> 
  <tr> 
   <td>SHOW STATUS</td> 
   <td>服务器状态</td> 
  </tr> 
  <tr> 
   <td>SHOW VARIABLES</td> 
   <td>服务器配置变量</td> 
  </tr> 
 </tbody> 
</table>

[https_www.runoob.com_mysql_mysql-group-by-statement.html]: https://www.runoob.com/mysql/mysql-group-by-statement.html
[https_www.cnblogs.com_yanzi-meng_p_9184139.html]: https://www.cnblogs.com/yanzi-meng/p/9184139.html
[https_ask.csdn.net_questions_260117]: https://ask.csdn.net/questions/260117
[https_blog.csdn.net_leilei7407_article_details_100850395]: https://blog.csdn.net/leilei7407/article/details/100850395
[https_blog.csdn.net_love_xsq_article_details_42417917]: https://blog.csdn.net/love_xsq/article/details/42417917
[https_www.runoob.com_mysql_mysql-alter.html]: https://www.runoob.com/mysql/mysql-alter.html
[https_www.jianshu.com_p_0d6c828d3c70]: https://www.jianshu.com/p/0d6c828d3c70
[https_www.runoob.com_mysql_mysql-index.html]: https://www.runoob.com/mysql/mysql-index.html