2009-05-31 MySQL 06-02 Learning Notes 04 03/31 Update SLTechnology News&Howtos

2009-05-31 MySQL 06-02 Learning Notes 04

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. Database name

In the file system, the datastore of MySQL represents the MySQL database as a directory, so the database name must be the same as the directory name. Database names that contain special characters or consist entirely of numbers or reserved words must be quoted with the symbol "`".

Mysql > create database `...`

Query OK, 1 row affected (0.01sec)

Mysql > create database `123456`

Query OK, 1 row affected (0.01sec)

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)

[@ more@] mysql > use...

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)

two。 Supplementary notes on NULL and null values

In MySQL, null values, unlike NULL, are not subject to NOT NULL constraints.

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

+-+

+-+

| | lname | varchar (15) | YES | | Li |

| | tel | smallint (11) unsigned | NO | | NULL |

+-+

3 rows in set (0.01sec)

Mysql > insert into namelist (fname) values ('Ning')

Query OK, 1 row affected, 1 warning (0.03 sec)

Mysql > select * from namelist

+-+

+-+

| | 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

But the null value here means that no value is set when the record is inserted, and this is not the case with an empty string specified with "'".

Mysql > insert into namelist values (',',')

Query OK, 1 row affected, 1 warning (0.03 sec)

Mysql > select * from namelist

+-+

+-+

| | Ning | Li | 0 | |

| | 0 |

+-+

2 rows in set (0.00 sec)

If a field does not specify a DEFAULT modifier, MySQL automatically sets the default value based on whether the field is NULL or NOT NULL. If the field can be NULL, the default value is NULL;. If the field specifies NOT NULL,MySQL to insert 0 for the numeric type (such as the tel column in the previous example), the string type inserts an empty string, the timestamp type inserts the current date and time, and the ENUM type inserts the first value of the allowed value collection.

As you can see from the following example, MySQL inserts an empty string for NOT NULL's varchar column fname, which is the same as the empty string specified with "'":

Mysql > insert into namelist (lname, tel) values (',')

Query OK, 1 row affected, 2 warnings (0.44 sec)

Mysql > select * from namelist

+-+

+-+

| | Ning | Li | 0 | |

| | 0 |

+-+

3 rows in set (0.00 sec)

Mysql > select * from namelist where fname=''

+-+

+-+

| | 0 |

+-+

2 rows in set (0.41 sec)

3.FULLTEXT index

FULLTEXT index is used for full-text retrieval. You can add this index to one or two plain string columns (called databases), which can be of type CHAR, VARCHAR, or TEXT, but not of type BLOB, and return records that contain parts similar to the search string when querying the index column. FULLTEXT indexes are only available for MyISAM tables, and the default type for creating tables is InnoDB, so you need to specify them explicitly with type.

The following is to create a table that stores everyday English language and insert several records into it. The table has two columns, questions and answers, on which the FULLTEXT index is built (personal understanding, that is, to determine the scope of full-text search, to generate the index as a whole):

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?',' Isimm 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)

It is faster to add an index to a table using CREATE FULLTEXT INDEX or ALTER TABLE statements after entering data than when an index is generated when the table is created.

The match () and against () functions return a value that represents similarity, for example:

Mysql > select match (ask,answer) against ('weather') as score, ask,answer from o

Raleng

+-+

+-+

+-+

7 rows in set (0.00 sec)

If you use it as a condition after where, you can return only records that contain parts similar to the search string:

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)

If the word appears more frequently, the value returned is smaller:

Mysql > select match (ask,answer) against ('fine') as score, ask,answer from oral

Eng

+-+

+-+

+-+

7 rows in set (0.00 sec)

If the search string contains two keywords, the value returned is approximately the sum of the values returned when searching for them separately:

Mysql > select match (ask,answer) against ('weather fine') as score, ask,answer f

Rom oraleng

+-+

+-+

+-+

7 rows in set (0.01 sec)

The MATCH () function calculates this value based on a number of column parameters, such as:

The number of words per line

The number of unique words per line

The total number of all words in the set

The number of lines containing a particular word

Not all words are added to the index, and some default full-text stop words such as from, the, what, you... Words that appear more than 50% in all records (that is, more than half of the total number of lines containing the word) will be ignored, and words that do not meet the requirements will also be ignored. The list of stop words, the minimum word length and the maximum word length can be controlled by modifying the server configuration variables ft_stopword_file,ft_min_word_len and ft_max_word_len, respectively. Their default values are as follows:

Mysql > select @ @ ft_stopword_file, @ @ ft_min_word_len, @ @ ft_max_word_len

+-+

+-+

| | (built-in) | 4 | 84 |

+-+

1 row in set (0.00 sec)

Among them, ft_stopword_file is designated as MySQL's built-in full-text stop word list by default, and its contents can be found in the MySQL reference manual.

After modifying these server variables, the MySQL server needs to be restarted for the changes to take effect, and the FULLTEXT index needs to be rebuilt. A faster way to rebuild is to use the REPAIR TABLE command with the QUICK parameter.

Version 4.0.1 or above, you can also perform a Boolean search of the FULLTEXT index:

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.01sec)

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.