Learning notes for MySQL

Take notes for MySQL

Chapter 01

SQL (pronounced S-Q-L or sequel) is short for structured query language.
SQL is a language specifically used to communicate with databases.

Chapter 02
Mysql-u user-P [Password] [-H host-P port]

Chapter 03
Show databases;
Use Database;
Show tables;

Desc table = describe table = show columns from table;

Show status;
Show create database;
Show create table table;
Show grants;
Show errors | warnings;
Help show;

Mysql> help show;
Name: 'show'
Description:
Show has Naming forms that provide information about databases, tables,
Columns, or status information about the server. This section describes
Those following:

Show authors
Show {binary | master} logs
Show BINLOG events [IN 'Log _ name'] [from POS] [limit [offset,] row_count]
Show Character Set [like_or_where]
Show collation [like_or_where]
Show [full] columns from tbl_name [from db_name] [like_or_where]
Show contributors
Show create database db_name
Show create event event_name
Show create function func_name
Show create procedure proc_name
Show create table tbl_name
Show create trigger trigger_name
Show create view view_name
Show databases [like_or_where]
Show engine engine_name {status | mutex}
Show [Storage] Engines
Show errors [limit [offset,] row_count]
Show events
Show Function Code func_name
Show function status [like_or_where]
Show grants for user
Show index from tbl_name [from db_name]
Show Master Status
Show open tables [from db_name] [like_or_where]
Show plugins
Show Procedure Code proc_name
Show procedure status [like_or_where]
Show privileges
Show [full] processlist
Show profile [Types] [for query N] [offset N] [limit N]
Show profiles
Show slave hosts
Show slave status
Show [Global | session] status [like_or_where]
Show table status [from db_name] [like_or_where]
Show [full] tables [from db_name] [like_or_where]
Show triggers [from db_name] [like_or_where]
Show [Global | session] variables [like_or_where]
Show warnings [limit [offset,] row_count]

Like_or_where:
Like 'pattern'
| Where expr

If the syntax for a given show statement between des a like 'pattern'
Part, 'pattern' is a string that can contain in the SQL "%" and "_"
Wildcard characters. The pattern is useful for restricting statement
Output to matching values.

Several show statements also accept a where clause that provides more
Flexibility in specifying which rows to display. See

Http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/show.html

Mysql5 adds database information-schema

Chapter 04 Data Retrieval
Limit 3, 4 = Limit 4 offset 3

Chapter 13 Group
Select
From
Where
Group
Having
Order
Limit

Chapter 14 use subquery
Subquery as calculated Field
Select cust_name,
Cust_state,
(Select count (*) from orders where orders. cust_id = Customer. cust_id) as orders
From MERs
Order by cust_name;

Chapter 15 join table
Select a. X, B. y from a, B where a. Xid = B. Xid
=
Select a. x B. Y from a inner join B on A. Xid = B. Xid recommended

Chapter 16 create advanced connections
Natural connection
Select a1.id, a1.name
From a as A1, A as A2
Where a1.xid = a2.xid
And a1.name = 'xx ';

External join left | right Outer Join

Chapter 17 Combined Query
Union combination to remove duplicate rows
Union all combines all rows without removing duplicates
Union sorting is OK as long as it is written to the end of the last union.

Chapter 18 full-text search
Condition:
Engine MyISAM;
Accept Fulltext

Create Table productnotes
{
Note_id int not null auto_increment,
Prod_id char (10) Not null,
Note_date datetime not null,
Note_text text null,
Primary Key (note_id ),
Fulltext (note_text)
) Engine = MyISAM;
Do not specify Fulltext when importing a table, and then alter table after importing the data;
Perform full-text search
Select note_text
From productnotes
Where match (note_text) against ('rabbit ');

Chapter 19 insert data
1. insert [low_priority] into table values (V1, v2...... VN );
2. insert [low_priority] into table (F1, F2. .. FN) values (V1, v2...... VN );
-- Suitable for inserting large amounts of data
3. insert [low_priority] into table (F1, F2. .. FN) Values
(V1, v2... VN ),
(V1, v2... VN ),
....
(V1, v2... VN );
4. insert into table (F1, F2. .. FN) Select F1, F2.. FN from Table1;