2009-05-31--06-02 MySQL学习笔记04
发表于:2024-09-30 作者:千家信息网编辑
千家信息网最后更新 2024年09月30日,1.数据库名在文件系统中,MySQL的数据存储区以目录方式表示MySQL数据库,所以数据库名必须与目录名一致。包含特殊字符或者全部由数字或保留字组成的数据库名必须用符号"`"引起来。mysql> cr
千家信息网最后更新 2024年09月30日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安全错误
数据库的锁怎样保障安全
阿里云网站租用服务器价格
学校网络安全第一责任人是
网络安全中美差异
数据库备份只有一个文件
我谈网络安全论文800字
数据库中concat函数用法
工程网络技术是什么措施
如何往腾讯云服务器传文件
广东常用软件开发价格走势
15000转服务器
mes 软件开发工程师
网络安全 安全上网的手抄报
杨浦区威力网络技术解决方案
数据库有数据安全问题么
深圳人工智能软件开发收费报价表
国家高级网络安全工程师报名
国家安全之网络安全手抄报
济南博赛网络技术有限公司待遇
华为网络技术资源部NTD
丰弘互联网科技
中标软件开发合同
eve萨沙区域安全服务器
网络安全取名字
买主机好还是服务器
软件开发商套餐
狂清馨网络技术工作室
vps服务器租
新能源汽车网络技术研究院
如何以文本形式存储数据库
重大活动期间网络安全保障制度