MySQL must know note <2>

"English" Ben Forta

5

1.0 * Use extended query *

|---->select Note from table where Match (note) against (' Anl ');

|--->where Match (note) against (' Anl ' with QUERY EXPANSION);

|---> Returns the rows that are related to the first row (highest priority) by rank, and the greater the number of rows in the table, the better the result of using the extended query.

2.0 * About insert*

|---The >insert operation is time consuming and may reduce the performance of SELECT statements awaiting processing. Solution:

|--> inserting low_priority between insert and into (lower priority)

The |--> also applies to updatae, DELETE statements.

* Improved Insert Performance *------>MYSQL processing multiple inserts with a single INSERT statement is faster than using multiple INSERT statements. = = VALUES (), (), () ...

* Regarding the use of Insert Select, as the name implies, the contents of the SELECT statement as Insert part *

|--->eg:insert into table name 1 (column) Select column from table Name 2

3.0 * About Boolean text search * can be used even if no fulltext index is available.

Eg:select Note

From table

where Match (note) against (' heavy-ropt* ' in BOOLEAN MODE);

|---> to match a line that contains heavy, but does not contain a word starting with rope.

* Full Text Boolean operator *

----Several classic examples:

4.0 * About ignore*

An error occurs when an update is updated with more than one row.

|---> Solutions: UPDATE IGNORE ....

　　　　　* To remove a column's value, you can set it to null*

|---->delete Delete an entire row instead of a column, delete the column with the UPDATE statement.

|----> Delete rows, all rows, but does not delete the table itself.

* To delete all rows, TRUNCATE table (delete a table, recreate a table)

　　5.0

*mysql No Undo button *

|---The null value is no value, not an empty string, and the empty string has a valid value, not a value of none.

|---"auto_increment (only one such column is allowed per table, and he must be indexed)

Select Last_insert-id ()

|-----> Returns the last Auto_increment value.

6

1.0 * About mSQL engine *

|

|--->innodb create a table the last sentence is commonly used. ENGINE = InnoDB;

|----> Reliable transaction processing engine, does not support full-text search

|

| The--->memory function is equivalent to MyISAM, because the data is stored in memory and is relatively fast for temporary tables.

|

|--->myisam (most default engines) supports full-text search and does not support transactional processing.

Foreign keys cannot cross engine * * *

*alter TABLE commonly used to define foreign keys *

|----> Eg:alter table name

ADD CONSTRAINT Column

FOREIGN KEY () REFERENCE table (column)

2.0

* Views cannot be indexed or have associated triggers or default values *

Show CREATE view viewname;------> View statements for creating views

* For updates *----------> drop before use Create, you can also use Create or REPLACE VIEW

|------->insert Update Delete

If the following are not updated: | Groupings (group by have)

| Join

| Sub-query

| and

| aggregation function

| Distinct

| export columns

3.0

* About creating stored procedures *

| CREATE PROCEDURE Storage Name ()

BEGIN

SELECT statement

END;

Statements referencing stored procedures: Call Store name ();

|---> The query results for the SELECT statement will be displayed.

Delete statement: Drop PROCEDURE Storage name; (Error will occur if no deletion is present)

|---> Solutions: DROP PROCEDURE IF EXISTS;

4.0

MySQL Command line client delimiter

Deltmiter//

A

END//

Deltmiter;(except \ Symbol)

5.0

* Stored procedure Nature is a function *

Comment Statement:--

SHOW CREATE PROCEDURE Storage name;

|---> STATUS like ' Store name ';--------->like limit output

6.0

* About Cursors * is a database query stored on a MySQL server that is not a SELECT statement but a result set retrieved by the statement. Unlike most dbms,mysql cursors are only used for stored procedures (functions)

* Trigger *-------> Only tables are supported, views are not supported.

CREATE TRIGGER ....

For each ROW SELECT statement

|-----> is executed for each insert row.

7.0

Transaction processing is used to maintain database integrity.

START transactidn

When a commit or ROLLBACK statement executes, the transaction is automatically closed.

If you do not want the transaction to be committed automatically, you can SET autocommit = 0; (not autocommit)------------> is for a connection, not a server.

7

1.0 * About Security *

Create user Ben identified by ' [email protected] ';(creating a username)

|----> Specify password as plain text

RENAME USER ben to F;

Drop user Ben;

|---------> Delete account and related account permissions

SET PASSWORD for ben = PASSWORD (' n3w [email protected] ');------------> Change password. If not, you are changing yourself.

2.0 * Questions About Permissions *

GRANT and REVOKE

|----->grant All and revoke all, corresponding to the entire server

|----->on database.*; Corresponds to the entire database

|----->on DATABASE. Table; corresponds to a specific sheet

|-----> Other permissions see the table below

3.0 * About Database Maintenance *

* Use the command-line utility mysqldump to dump all database content to another file.

* Use the command line utility mysqlhotcopy to copy all data from a database.

*analyze table is used to check if the form is correct.

*check table is used to check tables against many issues.

4.0

To view logs:

The error log Hostname.err is located in the data directory. Use the--log-error command option to change

Query log: Hostname.log--log

Binary log: Hostname-bin--log-bin

Slow query log: Hostname-slow.log--log-slow-queries

* * Flush logs statements can be used to flush and restart all log files while using the log * *

5.0

*mysql variables must begin with @ *

An example of a stored procedure:

|---->create procedure name (out P1 decimal (8,2))-------> "Decimal 10 rounding"

| BEGIN SELECT MIN (column) into P1 end;

* strings are converted between character sets using the cast () or CONVERT () function *