2009-05-31--06-02 MySQL学习笔记04
发表于：2025-02-01 作者：千家信息网编辑
千家信息网最后更新 2025年02月01日，1.数据库名在文件系统中，MySQL的数据存储区以目录方式表示MySQL数据库，所以数据库名必须与目录名一致。包含特殊字符或者全部由数字或保留字组成的数据库名必须用符号"`"引起来。mysql> cr
千家信息网最后更新 2025年02月01日2009-05-31--06-02 MySQL学习笔记04
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| ... |
+------------+
1 row in set (0.00 sec)
mysql> use 123456
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| 123456 |
+------------+
1 row in set (0.00 sec)
mysql> use database
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| database |
+------------+
1 row in set (0.00 sec)
mysql> drop database `...`;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database `123456`;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database `database`;
Query OK, 0 rows affected (0.00 sec)
2.关于NULL和空值的补充说明
在MySQL中，空值与NULL不同，它不受NOT NULL约束的限制。
mysql> create table namelist
-> (
-> fname varchar(15) not null,
-> lname varchar(15) default 'Li',
-> tel smallint(11) unsigned not null
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> desc namelist;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| fname | varchar(15) | NO | | NULL | |
| lname | varchar(15) | YES | | Li | |
| tel | smallint(11) unsigned | NO | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into namelist (fname) values ('Ning');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
+-------+-------+-----+
1 row in set (0.00 sec)
mysql> insert into namelist (fname) values (NUll);
ERROR 1048 (23000): Column 'fname' cannot be null
但这里说的空值是指插入记录时不值定任何值，用"''"指定的空字符串则不属于这种情况。
mysql> insert into namelist values ('', '', '');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
| | | 0 |
+-------+-------+-----+
2 rows in set (0.00 sec)
如果一个字段没有指定DEFAULT修饰符，MySQL会根据这个字段是NULL还是NOT NULL自动设置默认值。如果字段可以为NULL，默认值为NULL；如果字段指定指定了NOT NULL，MySQL对于数值类型插入0（如前面例子中的tel列），字符串类型插入空字符串，时间戳类型插入当前的日期和时间，ENUM类型插入允许值集合的第一个值。
从下面的例子可以看到，MySQL为NOT NULL的varchar列fname插入了空字符串，这和用"''"指定的空字符串相同：
mysql> insert into namelist (lname, tel) values ('', '');
Query OK, 1 row affected, 2 warnings (0.44 sec)
mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
| | | 0 |
| | | 0 |
+-------+-------+-----+
3 rows in set (0.00 sec)
mysql> select * from namelist where fname='';
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| | | 0 |
| | | 0 |
+-------+-------+-----+
2 rows in set (0.41 sec)
3.FULLTEXT索引
FULLTEXT索引用于全文检索。可以为一或两个纯文本字符串列（称为资料库）添加该索引，索引列可以是CHAR, VARCHAR或TEXT类型，但不能为BLOB类型，对索引列进行查询时返回含有与搜索字符串相似的部分的记录。FULLTEXT索引仅可用于MyISAM表，而创建表的默认类型为InnoDB，所以要用type明确指定。
下面创建一个存储英语日常用语的表并向其中插入若干条记录，该表有两个列，问句和答句，FULLTEXT索引建立在这两个列上（个人理解，即确定了全文检索的范围，将两列的内容作为一个整体来生成索引）：
mysql> create table oraleng
-> (
-> ask text,
-> answer text,
-> fulltext index (ask, answer)
-> )
-> type = myisam;
Query OK, 0 rows affected, 1 warning (0.15 sec)
mysql> insert into oraleng values ('How do you do?', 'How do you do?');
Query OK, 1 row affected (0.06 sec)
mysql> insert into oraleng values ('How are you?', 'Fine.Thank you.');
Query OK, 1 row affected (0.42 sec)
mysql> insert into oraleng values ('What's your name?', 'My name is Jack Sparro
w.');
Query OK, 1 row affected (0.41 sec)
mysql> insert into oraleng values ('Where are you from?', 'I'm from maldives.')
;
Query OK, 1 row affected (0.00 sec)
mysql> insert into oraleng values ('What's the weather like?', 'It's fine.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into oraleng values ('What time is it now?', 'It's seven o'clock
.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into oraleng values ('What day is it today?', 'It's Wednesday.');
Query OK, 1 row affected (0.00 sec)
mysql> select * from oraleng;
+--------------------------+--------------------------+
| ask | answer |
+--------------------------+--------------------------+
| How do you do? | How do you do? |
| How are you? | Fine.Thank you. |
| What's your name? | My name is Jack Sparrow. |
| Where are you from? | I'm from maldives. |
| What's the weather like? | It's fine. |
| What time is it now? | It's seven o'clock. |
| What day is it today? | It's Wednesday. |
+--------------------------+--------------------------+
7 rows in set (0.00 sec)
相对于在创建表时产生索引，输入数据后使用CREATE FULLTEXT INDEX或者ALTER TABLE语句向表中添加索引速度更快。
match()和against()函数返回一个代表相似度的值，例如：
mysql> select match(ask, answer) against ('weather') as score, ask,answer from o
raleng;
+------------------+--------------------------+--------------------------+
| score | ask | answer |
+------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 1.75147557258606 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+------------------+--------------------------+--------------------------+
7 rows in set (0.00 sec)
如果将其作为where后面的条件，就可以只返回含有与搜索字符串相似的部分的记录：
mysql> select ask, answer from oraleng where match (ask) against ('weather');
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
mysql> select ask, answer from oraleng where match (ask, answer) against ('weath
er');
+--------------------------+------------+
| ask | answer |
+--------------------------+------------+
| What's the weather like? | It's fine. |
+--------------------------+------------+
1 row in set (0.40 sec)
出现频率高的词，返回的值就较小：
mysql> select match(ask, answer) against ('fine') as score, ask,answer from oral
eng;
+-------------------+--------------------------+--------------------------+
| score | ask | answer |
+-------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0.905873239040375 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 0.895689904689789 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+-------------------+--------------------------+--------------------------+
7 rows in set (0.00 sec)
如果搜索字符串中包含两个关键词，返回的值大约为分别搜索它们时返回值的和：
mysql> select match(ask, answer) against ('weather fine') as score, ask,answer f
rom oraleng;
+-------------------+--------------------------+--------------------------+
| score | ask | answer |
+-------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0.905873239040375 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 2.64716553688049 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+-------------------+--------------------------+--------------------------+
7 rows in set (0.01 sec)
MATCH()函数是在一些列参数的基础上计算这个值的，如：
每一行词的个数
每一行唯一词的个数
集合中所有词的总个数
包含一个特定词的行数
并不是所有的词都会加入索引，一些默认的全文停止字如from, the, what, you...就会被忽略，在所有的记录中出现频率大于50%（即包含该词的行数超过总行数的一半）的词也会被忽略，另外长度不符合要求的词同样会被忽略。通过修改服务器配置变量ft_stopword_file，ft_min_word_len和ft_max_word_len可以分别控制停止词列表、最小单词长度和最大单词长度，它们的默认值如下：
mysql> select @@ft_stopword_file, @@ft_min_word_len, @@ft_max_word_len;
+--------------------+-------------------+-------------------+
| @@ft_stopword_file | @@ft_min_word_len | @@ft_max_word_len |
+--------------------+-------------------+-------------------+
| (built-in) | 4 | 84 |
+--------------------+-------------------+-------------------+
1 row in set (0.00 sec)
其中，ft_stopword_file默认指定为MySQL内置的全文停止字表，其内容可以在MySQL参考手册中查到。
修改这些服务器变量后，需要重启MySQL服务器使修改生效，而FULLTEXT索引也需要重建。一种较快的重建方法是使用带有QUICK参数的REPAIR TABLE命令。
4.0.1以上的版本，还可以执行FULLTEXT索引的布尔搜索：
mysql> select ask, answer from oraleng where match (ask, answer) against ('fine'
);
+--------------------------+-----------------+
| ask | answer |
+--------------------------+-----------------+
| How are you? | Fine.Thank you. |
| What's the weather like? | It's fine. |
+--------------------------+-----------------+
2 rows in set (0.01 sec)
mysql> select ask, answer from oraleng where match (ask, answer) against ('+fine
-weather' in boolean mode);
+--------------+-----------------+
| ask | answer |
+--------------+-----------------+
| How are you? | Fine.Thank you. |
+--------------+-----------------+
1 row in set (0.00 sec)
1.数据库名
在文件系统中，MySQL的数据存储区以目录方式表示MySQL数据库，所以数据库名必须与目录名一致。包含特殊字符或者全部由数字或保留字组成的数据库名必须用符号"`"引起来。
mysql> create database `...`;
Query OK, 1 row affected (0.01 sec)
mysql> create database `123456`;
Query OK, 1 row affected (0.01 sec)
mysql> create database `database`;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 123456 |
| ... |
| database |
| ggyy |
| mysql |
| test |
+--------------------+
7 rows in set (0.00 sec)
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| ... |
+------------+
1 row in set (0.00 sec)
mysql> use 123456
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| 123456 |
+------------+
1 row in set (0.00 sec)
mysql> use database
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| database |
+------------+
1 row in set (0.00 sec)
mysql> drop database `...`;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database `123456`;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database `database`;
Query OK, 0 rows affected (0.00 sec)
2.关于NULL和空值的补充说明
在MySQL中，空值与NULL不同，它不受NOT NULL约束的限制。
mysql> create table namelist
-> (
-> fname varchar(15) not null,
-> lname varchar(15) default 'Li',
-> tel smallint(11) unsigned not null
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> desc namelist;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| fname | varchar(15) | NO | | NULL | |
| lname | varchar(15) | YES | | Li | |
| tel | smallint(11) unsigned | NO | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into namelist (fname) values ('Ning');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
+-------+-------+-----+
1 row in set (0.00 sec)
mysql> insert into namelist (fname) values (NUll);
ERROR 1048 (23000): Column 'fname' cannot be null
但这里说的空值是指插入记录时不值定任何值，用"''"指定的空字符串则不属于这种情况。
mysql> insert into namelist values ('', '', '');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
| | | 0 |
+-------+-------+-----+
2 rows in set (0.00 sec)
如果一个字段没有指定DEFAULT修饰符，MySQL会根据这个字段是NULL还是NOT NULL自动设置默认值。如果字段可以为NULL，默认值为NULL；如果字段指定指定了NOT NULL，MySQL对于数值类型插入0（如前面例子中的tel列），字符串类型插入空字符串，时间戳类型插入当前的日期和时间，ENUM类型插入允许值集合的第一个值。
从下面的例子可以看到，MySQL为NOT NULL的varchar列fname插入了空字符串，这和用"''"指定的空字符串相同：
mysql> insert into namelist (lname, tel) values ('', '');
Query OK, 1 row affected, 2 warnings (0.44 sec)
mysql> select * from namelist;
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| Ning | Li | 0 |
| | | 0 |
| | | 0 |
+-------+-------+-----+
3 rows in set (0.00 sec)
mysql> select * from namelist where fname='';
+-------+-------+-----+
| fname | lname | tel |
+-------+-------+-----+
| | | 0 |
| | | 0 |
+-------+-------+-----+
2 rows in set (0.41 sec)
3.FULLTEXT索引
FULLTEXT索引用于全文检索。可以为一或两个纯文本字符串列（称为资料库）添加该索引，索引列可以是CHAR, VARCHAR或TEXT类型，但不能为BLOB类型，对索引列进行查询时返回含有与搜索字符串相似的部分的记录。FULLTEXT索引仅可用于MyISAM表，而创建表的默认类型为InnoDB，所以要用type明确指定。
下面创建一个存储英语日常用语的表并向其中插入若干条记录，该表有两个列，问句和答句，FULLTEXT索引建立在这两个列上（个人理解，即确定了全文检索的范围，将两列的内容作为一个整体来生成索引）：
mysql> create table oraleng
-> (
-> ask text,
-> answer text,
-> fulltext index (ask, answer)
-> )
-> type = myisam;
Query OK, 0 rows affected, 1 warning (0.15 sec)
mysql> insert into oraleng values ('How do you do?', 'How do you do?');
Query OK, 1 row affected (0.06 sec)
mysql> insert into oraleng values ('How are you?', 'Fine.Thank you.');
Query OK, 1 row affected (0.42 sec)
mysql> insert into oraleng values ('What's your name?', 'My name is Jack Sparro
w.');
Query OK, 1 row affected (0.41 sec)
mysql> insert into oraleng values ('Where are you from?', 'I'm from maldives.')
;
Query OK, 1 row affected (0.00 sec)
mysql> insert into oraleng values ('What's the weather like?', 'It's fine.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into oraleng values ('What time is it now?', 'It's seven o'clock
.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into oraleng values ('What day is it today?', 'It's Wednesday.');
Query OK, 1 row affected (0.00 sec)
mysql> select * from oraleng;
+--------------------------+--------------------------+
| ask | answer |
+--------------------------+--------------------------+
| How do you do? | How do you do? |
| How are you? | Fine.Thank you. |
| What's your name? | My name is Jack Sparrow. |
| Where are you from? | I'm from maldives. |
| What's the weather like? | It's fine. |
| What time is it now? | It's seven o'clock. |
| What day is it today? | It's Wednesday. |
+--------------------------+--------------------------+
7 rows in set (0.00 sec)
相对于在创建表时产生索引，输入数据后使用CREATE FULLTEXT INDEX或者ALTER TABLE语句向表中添加索引速度更快。
match()和against()函数返回一个代表相似度的值，例如：
mysql> select match(ask, answer) against ('weather') as score, ask,answer from o
raleng;
+------------------+--------------------------+--------------------------+
| score | ask | answer |
+------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 1.75147557258606 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+------------------+--------------------------+--------------------------+
7 rows in set (0.00 sec)
如果将其作为where后面的条件，就可以只返回含有与搜索字符串相似的部分的记录：
mysql> select ask, answer from oraleng where match (ask) against ('weather');
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
mysql> select ask, answer from oraleng where match (ask, answer) against ('weath
er');
+--------------------------+------------+
| ask | answer |
+--------------------------+------------+
| What's the weather like? | It's fine. |
+--------------------------+------------+
1 row in set (0.40 sec)
出现频率高的词，返回的值就较小：
mysql> select match(ask, answer) against ('fine') as score, ask,answer from oral
eng;
+-------------------+--------------------------+--------------------------+
| score | ask | answer |
+-------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0.905873239040375 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 0.895689904689789 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+-------------------+--------------------------+--------------------------+
7 rows in set (0.00 sec)
如果搜索字符串中包含两个关键词，返回的值大约为分别搜索它们时返回值的和：
mysql> select match(ask, answer) against ('weather fine') as score, ask,answer f
rom oraleng;
+-------------------+--------------------------+--------------------------+
| score | ask | answer |
+-------------------+--------------------------+--------------------------+
| 0 | How do you do? | How do you do? |
| 0.905873239040375 | How are you? | Fine.Thank you. |
| 0 | What's your name? | My name is Jack Sparrow. |
| 0 | Where are you from? | I'm from maldives. |
| 2.64716553688049 | What's the weather like? | It's fine. |
| 0 | What time is it now? | It's seven o'clock. |
| 0 | What day is it today? | It's Wednesday. |
+-------------------+--------------------------+--------------------------+
7 rows in set (0.01 sec)
MATCH()函数是在一些列参数的基础上计算这个值的，如：
每一行词的个数
每一行唯一词的个数
集合中所有词的总个数
包含一个特定词的行数
并不是所有的词都会加入索引，一些默认的全文停止字如from, the, what, you...就会被忽略，在所有的记录中出现频率大于50%（即包含该词的行数超过总行数的一半）的词也会被忽略，另外长度不符合要求的词同样会被忽略。通过修改服务器配置变量ft_stopword_file，ft_min_word_len和ft_max_word_len可以分别控制停止词列表、最小单词长度和最大单词长度，它们的默认值如下：
mysql> select @@ft_stopword_file, @@ft_min_word_len, @@ft_max_word_len;
+--------------------+-------------------+-------------------+
| @@ft_stopword_file | @@ft_min_word_len | @@ft_max_word_len |
+--------------------+-------------------+-------------------+
| (built-in) | 4 | 84 |
+--------------------+-------------------+-------------------+
1 row in set (0.00 sec)
其中，ft_stopword_file默认指定为MySQL内置的全文停止字表，其内容可以在MySQL参考手册中查到。
修改这些服务器变量后，需要重启MySQL服务器使修改生效，而FULLTEXT索引也需要重建。一种较快的重建方法是使用带有QUICK参数的REPAIR TABLE命令。
4.0.1以上的版本，还可以执行FULLTEXT索引的布尔搜索：
mysql> select ask, answer from oraleng where match (ask, answer) against ('fine'
);
+--------------------------+-----------------+
| ask | answer |
+--------------------------+-----------------+
| How are you? | Fine.Thank you. |
| What's the weather like? | It's fine. |
+--------------------------+-----------------+
2 rows in set (0.01 sec)
mysql> select ask, answer from oraleng where match (ask, answer) against ('+fine
-weather' in boolean mode);
+--------------+-----------------+
| ask | answer |
+--------------+-----------------+
| How are you? | Fine.Thank you. |
+--------------+-----------------+
1 row in set (0.00 sec)
索引
类型
字符
数据
搜索
两个
全文
字段
字符串
数据库
空字符
相似
个数
服务器
长度
服务
一行
例子
全文检索
内容
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
微信qq网络安全法
数据库技术的初步认识
网络安全行动计划实施方案
阵列信号软件开发
济南软件开发公司外包项目
网络安全执法机构
orical数据库 入门
安徽数据网络技术服务价格
浩渺网络技术有限公司
网络安全法开展经常性
用服务器搭建网站
服务器查本机ip
北京前端软件开发需要多少钱
为什么学校用宽带找不到服务器
河北ios软件开发信息推荐
服务器机柜过滤风扇批发价
php将数据库字段的值
贵州省计算机网络技术专升本
上海智能照明软件开发哪家好
贵州定制软件开发
中信网网络安全
神探迈克服务器怪照片
四年级上册网络安全周手抄报
网络安全作文30字以下
国家网络安全日的意义
软件开发哪里需要用钱
数据库没有响应
网络安全教育手抄报简便
网络技术学习好物分享
江苏思科网络技术
