前言
必备加薪神器，那么，一直说学习数据库、学 MySQL，到底是要学习它的哪些东西呢？一、如何快速掌握 MySQL?
MySQL5.7 还是 MySQL8.0 都不例外！二、技巧分享
1、MySQL 中如何使用特殊字符？
'，双引号 "，反斜线 \ 等符号，这些符号在 MySQL 中不能直接输入使用，否则会产生意料之外的结果。lucifer's dog，其中的单引号 ' 号，如果不做转义，则无法成功执行：mysql> create table lucifer (id int,name char(100));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into lucifer values (1,'lucifer's dog');
'>
'> mysql>
^C
mysql>
\ 开头，所以在使用单引号和双引号时应分别输入 \' 或者 \"，输入反斜线时应该输入 \\，其他特殊字符还有回车符 \r，换行符 \n，制表符 \tab，退格符 \b 等。mysql> create table lucifer (id int,name char(100));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into lucifer values (1,'lucifer\'s dog');
Query OK, 1 row affected (0.00 sec)
mysql> select * from lucifer;
+------+---------------+
| id | name |
+------+---------------+
| 1 | lucifer's dog |
+------+---------------+
1 row in set (0.00 sec)
mysql>
2、MySQL 中可以存储文件吗？
BLOB 和 TEXT 字段类型可以存储数据量较大的文件，可以使用这些数据类型 存储图像、声音或者是大容量的文本内容，例如网页或者文档。mysql> create table view(id int unsigned NOT NULL AUTO_INCREMENT, catid int,title varchar(256),picture MEDIUMBLOB, content TEXT,PRIMARY KEY (id));
Query OK, 0 rows affected (0.03 sec)
mysql> show fields from view;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| catid | int | YES | | NULL | |
| title | varchar(256) | YES | | NULL | |
| picture | mediumblob | YES | | NULL | |
| content | text | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
3、MySQL 中如何执行区分大小写的字符串比较？
不区分大小写 的，因此字符串比较函数也不区分大小写。mysql> select 'TRUE' from dual where 'DOG' = 'dog';
+------+
| TRUE |
+------+
| TRUE |
+------+
1 row in set (0.00 sec)
mysql> select 'TRUE' from dual where BINARY'DOG' = 'dog';
Empty set (0.00 sec)
mysql>
4、如何从日期时间值中获取年、月、日等部分日期或时间值？
mysql> create table lucifer(date date);
Query OK, 0 rows affected (0.04 sec)
mysql> show fields from lucifer;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| date | date | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into lucifer values (now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from lucifer;
+------------+
| date |
+------------+
| 2021-11-25 |
+------------+
1 row in set (0.00 sec)
2021-11-25，如果只需要获得年值，可以输入 LEFT(date, 4)，这样就获得了字符串左边开始长度为 4 的子字符串，即 YEAR 部分的值；mysql> select LEFT(date, 4) from lucifer;
+---------------+
| LEFT(date, 4) |
+---------------+
| 2021 |
+---------------+
1 row in set (0.00 sec)
MID(date,6,2)，字符串第 6 个字符开始，长度为 2 的子字符串正好为 date 中的月份值。同理，读者可以根据其他日期和时间的位置，计算并获取相应的值。mysql> select MID(date,6,2) from lucifer;
+---------------+
| MID(date,6,2) |
+---------------+
| 11 |
+---------------+
1 row in set (0.00 sec)
5、如何改变默认的字符集？
CONVERT() 函数改变指定字符串的默认字符集！修改配置文件。SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看当前字符集，以进行对比。mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> status
--------------
mysql Ver 8.0.26-0ubuntu0.21.04.3 for Linux on aarch64 ((Ubuntu))
Connection id: 10
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.26-0ubuntu0.21.04.3 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 36 min 55 sec
Threads: 2 Questions: 325 Slow queries: 0 Opens: 181 Flush tables: 3 Open tables: 69 Queries per second avg: 0.146
--------------
mysql>
my.cnf，该文件在 MySQL 的安装目录下面。修改配置文件中的 default-character-set 和 character-set-server 参数值，将其改为想要的字符集名称，如 gbk、gb2312、latinl 等，修改完之后重新启动 MySQL 服务，即可生效。## 找到 my.cnf 位置
root@modb:~# find /etc -iname my.cnf -print
/etc/alternatives/my.cnf
/etc/mysql/my.cnf
## 修改字符集
在[client ]下面加入
default-character-set=utf8
在[ mysqld ] 下面加
character_set_server=utf8
## 重启 mysql 生效
service mysql restart
SHOW VARIABLES LIKE 'character_set_%'; 或者 status 命令查看修改结果！6、DISTINCT 可以应用于所有的列吗？
DESC，这个关键字只能对其前面的列 进行降序排列。mysql> select * from lucifer;
+------+----------+
| id | name |
+------+----------+
| 1 | lucifer |
| 2 | lucifer1 |
| 3 | lucifer2 |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from lucifer order by id desc;
+------+----------+
| id | name |
+------+----------+
| 3 | lucifer2 |
| 2 | lucifer1 |
| 1 | lucifer |
+------+----------+
3 rows in set (0.00 sec)
DESC 关键字。mysql> select * from lucifer order by id desc,name desc;
+------+----------+
| id | name |
+------+----------+
| 3 | lucifer2 |
| 2 | lucifer1 |
| 1 | lucifer |
+------+----------+
3 rows in set (0.00 sec)
DISTINCT 不同，DISTINCT 不能部分使用。换句话说，DISTINCT 关键字应用于所有列而不仅是它后面的第一个指定列。mysql> select * from lucifer;
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoli | male | 20 |
| 1 | xiaoliu | female | 21 |
| 1 | xiaozhang | female | 21 |
| 1 | xiaowu | female | 21 |
+------+-----------+--------+------+
4 rows in set (0.00 sec)
mysql> select distinct sex,age from lucifer;
+--------+------+
| sex | age |
+--------+------+
| male | 20 |
| female | 21 |
+--------+------+
2 rows in set (0.00 sec)
mysql>
7、ORDER BY 可以和 LIMIT 混合使用吗？
LIMIT，则必须位于 ORDER BY 之后，如果子句顺序不正确，MySQL 将产生错误消息。mysql> select * from lucifer order by age desc limit 2,4;
+------+--------+--------+------+
| id | name | sex | age |
+------+--------+--------+------+
| 1 | xiaowu | female | 21 |
| 1 | xiaoli | male | 20 |
+------+--------+--------+------+
2 rows in set (0.00 sec)
mysql> select * from lucifer limit 2,4 order by age desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by age desc' at line 1
mysql>
8、什么时候使用引号？
mysql> select * from lucifer where sex = 'female';
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoliu | female | 21 |
| 1 | xiaozhang | female | 21 |
| 1 | xiaowu | female | 21 |
+------+-----------+--------+------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from lucifer where age = 20;
+------+--------+------+------+
| id | name | sex | age |
+------+--------+------+------+
| 1 | xiaoli | male | 20 |
+------+--------+------+------+
1 row in set (0.00 sec)
mysql>
9、在 WHERE子句中 AND 和 OR 必须使用圆括号吗？
AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确操作顺序。mysql> select * from lucifer where (age = 20 or sex = 'female') and name != 'xiaowu';
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoli | male | 20 |
| 1 | xiaoliu | female | 21 |
| 1 | xiaozhang | female | 21 |
+------+-----------+--------+------+
mysql> 3 rows in set (0.00 sec)
10、更新或者删除表时必须指定 WHERE子 句吗？
mysql> update lucifer set age = 22 where name = 'xiaoliu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from lucifer where name = 'xiaoliu';
+------+---------+--------+------+
| id | name | sex | age |
+------+---------+--------+------+
| 1 | xiaoliu | female | 22 |
+------+---------+--------+------+
1 row in set (0.00 sec)
mysql>
mysql> update lucifer set age = 22;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> select * from lucifer;
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoli | male | 22 |
| 1 | xiaoliu | female | 22 |
| 1 | xiaozhang | female | 22 |
| 1 | xiaowu | female | 22 |
+------+-----------+--------+------+
4 rows in set (0.00 sec)
mysql>
11、索引对数据库性能如此重要，应该如何使用它？
通过创建唯一索引可以保证数据库表中每一行数据的唯一性。 可以给所有的 MySQL 列类型设置索引。 可以大大加快数据的查询速度，这是使用索引最主要的原因。 在实现数据的参考完整性方面可以加速表与表之间的连接。 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间
创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。
12、尽量使用短索引（前缀索引）
mysql> select * from lucifer;
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoli | male | 22 |
| 1 | xiaoliu | female | 22 |
| 1 | xiaozhang | female | 22 |
| 1 | xiaowu | female | 22 |
+------+-----------+--------+------+
4 rows in set (0.00 sec)
mysql> create index idx_lucifer_name on lucifer (name(4));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from lucifer;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| lucifer | 1 | idx_lucifer_name | 1 | name | A | 1 | 4 | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
mysql>
13、MySQL 存储过程和函数有什么区别？
只能通过 return 语句返回单个值或者表对象； 限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等； 可以嵌入在 SQL 语句中使用，可以在 SELECT 语句中作为查询语句的一个部分调用；
不允许执行 return，但是可以通过 out 参数返回多个值； 限制相对就比较少； 一般是作为一个独立的部分来执行；
14、存储过程中的内容可以改变吗？
15、存储过程中可以调用其他存储过程吗？
16、存储过程的参数不要与数据表中的字段名相同。
17、存储过程的参数可以使用中文吗？
18、MySQL 中视图和表的区别以及联系是什么？
视图是已经编译好的 SQL 语句，是基于 SQL 语句的结果集的可视化的表，而表不是； 视图没有实际的物理记录，而基本表有； 表是内容，视图是窗口； 表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改； 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL 语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构； 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表； 视图的建立和删除只影响视图本身，不影响对应的基本表；
19、使用触发器时须特别注意！
mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> select * from lucifer;
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoli | male | 22 |
| 1 | xiaoliu | female | 22 |
| 1 | xiaozhang | female | 22 |
| 1 | xiaowu | female | 22 |
| 1 | lucifer | male | 20 |
| 1 | lucifer | male | 20 |
+------+-----------+--------+------+
6 rows in set (0.00 sec)
mysql> insert into lucifer values(1,'lucifer','male',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from lucifer;
+------+-----------+--------+------+
| id | name | sex | age |
+------+-----------+--------+------+
| 1 | xiaoli | male | 22 |
| 1 | xiaoliu | female | 22 |
| 1 | xiaozhang | female | 22 |
| 1 | xiaowu | female | 22 |
| 1 | lucifer | male | 20 |
| 1 | lucifer | male | 20 |
| 2 | lucifer | male | 20 |
+------+-----------+--------+------+
7 rows in set (0.00 sec)
BEFORE INSERT 触发器，那么如果对表 lucifer 再次创建一个 BEFORE INSERT 触发器，MySQL 将会报错，此时，只可以在表 lucifer 上创建 AFTER INSERT 或者 BEFORE UPDATE 类型的触发器。mysql> create trigger lucifer_tri before insert on lucifer for each row set NEW.id=NEW.id+1;
ERROR 1359 (HY000): Trigger already exists
mysql>
20、及时删除不再需要的触发器
mysql> drop trigger lucifer_tri;
Query OK, 0 rows affected (0.03 sec)
mysql>
21、应该使用哪种方法创建用户？（3种方式）
使用 CREATE USER 语句创建用户 在 mysql.user 表中添加用户 使用 GRANT 语句创建用户（仅限 MySQL 8 版本以下使用）
-- 使用 CREATE USER 语句创建用户
mysql> create user 'lucifer'@'localhost' identified by 'lucifer';
Query OK, 0 rows affected (0.01 sec)
mysql>
-- 在 mysql.user 表中添加用户
mysql> select MD5('lucifer');
+----------------------------------+
| MD5('lucifer') |
+----------------------------------+
| cae33a0264ead2ddfbc3ea113da66790 |
+----------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO mysql.user(Host, User, authentication_string, ssl_cipher, ssuex509_i09_sr, x5ubject) VALUES ('lohoscalt',uci 'lfer MD5('1',lucifer'), '', '',; '')
Query OK, 1 row affected (0.01 sec)
mysql>
-- 使用 GRANT 语句创建用户
mysql> GRANT SELECT ON*.* TO 'lucifer2'@localhost IDENTIFIED BY 'lucifer';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'lucifer'' at line 1
mysql>
22、mysqldump 备份的文件只能在 MySQL 中使用吗？
mysqldump 备份的文本文件实际是数据库的一个副本，使用该文件不仅可以在 MySQL 中恢复数据库，而且通过对该文件的简单修改，可以使用该文件在 SQL Server 或者 Sybase 等其他数据库中恢复数据库。root@modb:~# mysqldump -uroot -p hr > /root/hr.db
Enter password:
root@modb:~#
root@modb:~# ll hr.db
-rw-r--r-- 1 root root 25327 Nov 26 08:52 hr.db
23、如何选择备份工具？
热备（Hot Backup） 冷备（Cold Backup） 温备（Warm Backup）
表数据 二进制日志、InnoDB 事务日志 代码（存储过程、存储函数、触发器、事件调度器） 服务器配置文件
mysqldump：逻辑备份工具，适用于所有的存储引擎，支持温备、完全备份、部分备份、对于 InnoDB 存储引擎支持热备。 cp、tar 等归档复制工具：物理备份工具，适用于所有的存储引擎、冷备、完全备份、部分备份。 lvm2 snapshot：借助文件系统管理工具进行备份。 mysqlhotcopy：名不副实的一个工具，仅支持 MyISAM 存储引擎。 xtrabackup：一款由 percona 提供的非常强大的 InnoDB/XtraDB 热备工具，支持完全备份、增量备份。
24、平时应该打开哪些日志？
-- 检查是否开启慢查询
mysql> show variables like 'slow_query%';
+---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/modb-slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
-- 开启慢查询日志
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
-- 设置查询超过10秒就记录
mysql> set global long_query_time=10;
Query OK, 0 rows affected (0.00 sec)
-- 再次检查是否开启
mysql> show variables like 'slow_query%';
mysql> +---------------------+------------------------------+
| Variable_name | Value |
+---------------------+------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/modb-slow.log |
+---------------------+------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'general_log%';
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/modb.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)
-- 开启通用查询日志
mysql> SET GLOBAL general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general_log%';
+------------------+-------------------------+
| Variable_name | Value |
+------------------+-------------------------+
| general_log | ON |
| general_log_file | /var/lib/mysql/modb.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql>
25、如何使用二进制日志？
root@modb:/var/lib/mysql# ls binlog*
binlog.000001 binlog.000002 binlog.index
root@modb:/var/lib/mysql# mysqlbinlog binlog.000001 | mysql -u root -p
Enter password:
root@modb:/var/lib/mysql#
26、如何使用慢查询日志？
root@modb:/var/lib/mysql# cat /var/lib/mysql/modb-slow.log
/usr/sbin/mysqld, Version: 8.0.26-0ubuntu0.21.04.3 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
root@modb:/var/lib/mysql#
long_query_time 的值，可以灵活地掌握不同程度的慢查询语句。27、是不是索引建立得越多越好？
28、如何使用查询缓冲区？
queiy_cache_size 以调整查询缓冲区大小；修改 query_cache_type 以调整查询缓冲区的类型。my.cnf 中修改 query_cache_size 和 query_cache_type 的值如下所示：[mysqld]
query_cache_size= 512M
query_cache_type= 1
query_cache_type=1
SQL_NO_CACHE 关键字时，才不会使用查询缓冲区。可以使用 FLUSH QUERY CACHE 语句来刷新缓冲区，清理查询缓冲区中的碎片。来源：Lucifer三思而后行
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/94268/viewspace-3135951/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
