1
------------------------- Database ----------------------------------------
2
CREATE DATABASE [IF NOT EXISTS] db_name
3
DROP DATABASE [IF EXISTS] db_name
4
In both cases IF [NOT] EXISTS options prevent error,
5
if database alredy exists/doesn't exist
7
---------------------- CREATE TABLE ---------------------------------------
8
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db_name.]tbl_name
9
[(create_definition,...)] [TABLE_OPTIONS] [SELECT_STATMENT]
11
Silent Column Specification Changes:
12
1. VARCHAR(a) -> CHAR, if a<4
13
2. CHAR(a) -> VARCHAR, if any VARCHAR alredy present and a>3
16
1. col_name type[(length[,decimals)] [UNSIGNED] [ZEROFIL]
17
[NOT NULL | NULL] [DEFAULT default_value]
18
[AUTO_INCREMENT] [PRIMARY KEY]
19
[reference_definition]
20
2. PRIMARY KEY (col_name1[(length)],...)
21
3. KEY/INDEX [index_name] (col_name1[(length)],...)
22
4. UNIQUE [INDEX] [index_name] (col_name1[(length)],...)
23
5. FULLTEXT [INDEX] [index_name] (col_name1[(length)],...)
25
7. [CONSTRAIT symb] FOREIGN KEY index_name (col_name1[length],...) [REF_DEF]
28
TEMPORARY: Temporary table will automaticaly be deleted if a
29
connection dies. This means that two different
30
connections can both use use same temporary table name
31
without conflicting each other or with an existing
32
table of the same name. The existing table is hidden
33
until the temporary table is deleted.
34
DEFAULT: Value must be constant. Can't used function as NOW(),..
35
If default value not specified MySQL automaticaly
36
assigns one. NULL for NULL & AUTO_INCREMENT columns,
37
For ENUM first enumeration value, for other strings
38
types(including SET) "". For the first TIMESTAMP
39
column in table current date & time, 0 for other
40
date, time and numerical columns.
41
[NOT] NULL: If columns may have NULL value. It's default.
42
Cann't be specified for TIMESTAMP columns, there setting
43
column to NULL results in setting it to current time.
44
AUTO_INCREMENT: When inserting value of NULL(recommended) or 0
45
to AUTO_INCREMENT column, the column set to value+1,
46
where value is the largest value for the column
47
currently in the table.
48
When row with largest value deleted it will be reused
49
in ISAM and BDB table, but not in MyISAM and InnoDB.
50
Last row inserted may be found with:
51
SELECT * FROM tbl WHERE auto_col IS NULL
52
INDEX/KEY: Create index on specified row. If name not specifed
53
automaticaly used index_col_name[_#].
54
With col_name(length) syntax, can be specified index
55
that uses only part of CHAR or VARCHAR column.
56
Only MyISAM tables supports INDEXes on TEXT|BLOB columns
57
it this case length specification is required.
58
Only MyISAM tables supports INDEXes on NULL columns.
59
UNIQUE: Value of column in each row must differ
60
FULLTEXT: Only in MyISAM tables. For fulltext searches on
61
CHAR & VARCHAR columns.
62
PRIMARY_KEY: NOT NULL UNIQUE KEY ( table can have only one PK )
63
If PRIMARY key consist of only one, integer column
64
it can be reffered as _rowid
66
FOREIGN KEY: Do nothing ;))
69
REFERENCES tbl_name [(col_name[(length)],...)]
70
[MATCH FULL|MATCH PARTIAL]
71
ON DELETE REFERENCE_OPTION
72
ON UPDATE REFERENCE_OPTION
82
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM}
83
AUTO_INCREMENT = # The next AUTO_INCREMENT value to be used
84
AVG_ROW_LENGTH = # Approximation of the average row length.
85
Needed only for large tables with variable
86
size records. In bytes.
87
CHECKSUM = {0 | 1} Mantain or now checksum for each row.
88
COMMENT = "string" 60-character comment for a table
89
MAX_ROWS = # Maximum number of rows in table
90
MIN_ROWS = # Minum number of rows in table
91
PACK_KEYS = {0 | 1 | DEFAULT} Compress indexes, make faster searches and
92
slower updates. DEFAULT - only packs long
93
CHAR & VARCHAR columns. Only in ISAM & MyISAM.
94
PASSWORD = "string" Do nothing. May be latter will encrypt table
96
DELAY_KEY_WRITE = {0 | 1} If set, delays key table updates until the
97
table is closed. Only in MyISAM.
98
ROW_FORMAT = {default | dynamic | fixed | compressed }
99
Defines how row should be stored (MyISAM)
100
DEFAULT, COMPRESSED - do nothing
101
RAID_TYPE = {1|STRIPED|RAID0} RAID_CHUNKS=# RAID_CHUNKSIZE=#
102
For breaking 2GB barier and using some hard
103
drives. ( 1=STRIPED=RAID0 )
104
In this case MySQL will create RAID_CHUNKS
105
subdirectories 00,01,... in database directory.
106
When writing data to the data file, the RAID
107
handler will map the first RAID_CHUNKSIZE*1024
108
bytes to the first directory, and so on.
109
UNION = {table1_name,...}
110
Used then wanted to use collection of indentical
111
tables as one. Works only with MERGE tables.
112
Needed SELECT,UPDATE,DELETE privilages on the
113
tables mapped on MERGE table.
114
INSERT_METHOD={NO | FIRST | LAST}
115
Then performing insert in MERGE table, must be
116
specified with INSERT_METHOD, into which table
117
row should be inserted
118
DATA DIRECTORY="directory" Only for MyISAM
119
INDEX DIRECTORY="directory" Only for MyISAM
122
[IGNORE | REPLACE] SELECT ...
124
------------------------------- ALTER TABLE ---------------------------------
125
ALTER [IGNORE] [db_name.]tbl_name alter1_spec, alter2_spec, ...
128
01. ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
129
Default to add columns last position
130
02. ADD [COLUMN] create_definition1, create_definition2, ...
131
03. ADD PRIMARY KEY (col_name([length]),...)
132
04. ADD [INDEX|UNIQUE|FULLTEXT] [index_name] (col_name([length]),...)
133
05. ALTER [COLUMN] col_name {SET DEFAULT value | DROP DEFAULT}
134
06. CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER col_name]
135
If CHANGE or MODIFY to shorten column for
136
which index exist on part of column, there
137
are impossible to make column shorter than
138
the number of characters that are indexed.
139
07. MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
140
08. DROP [COLUMN] col_name
141
If table contains only 1 column it can't
142
be dropped. Corresponded indexes dropped
144
09. DROP [PRIMARY KEY|INDEX index_name]
145
If now PRIMARY INDEX exist dropped first
146
UNIQUE index in table.
147
10. DISABLE/ENABLE KEYS
148
Stops/starts updating of non-UNIQUE
149
indexes for MyISAM tables. ON ENABLE
150
missing indexes recreated.
151
11. RENAME [TO] new_tbl_name
156
IGNORE: Controls how ALTER TABLE works if there are duplicates
157
on unique keys in the new table. If IGNORE isn't
158
specified, ALTER TABLE aborted with error, in other
159
case for rows with duplicates on a unique key, only
160
first row is used, others deleted.
161
COLUMN: Is a pure noise ;))) May be omitted.
163
--------------------------------- Other ----------------------------------
164
RENAME TABLE tbl_name TO new_table_name, ...
165
As long as databases on one HDD, it possible to rename from one
167
The rename is done from left to right, so to swap table names:
168
RENAME TABLE t1 TO tb, t2 TO t1, tb TO t2;
170
DROP TABLE [IF EXISTS] tbl_name,... [RESTRICT|CASCADE]
171
RESTRICT Do nothing :)))
172
CASCADE Do nothing :)))
174
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],...)
175
Mapped on ALTER TABLE statment to create indexes.
177
DROP INDEX index_name ON tbl_name
178
Mapped on ALTER TABLE statment to drop indexes.
181
Select default database
183
---------------------------- Locking ----------------------------------------
184
LOCK TABLES tbl_name [AS alias] {READ [LOCAL]|[LOW_PRIORITY] WRITE}, ...
185
Released all locks obtained by previous LOCK TABLES statment!
186
Then connection to server closed automaticaly all LOCKs released.
187
If thread obtained READ lock, that thread (and all others) can only
188
read from table. If a WRITE lock obtained, then only the thread holding
189
the lock can READ and WRITE table.
190
If tables using multiple times in query (with aliases) it's nececary
191
to get a lock for each alias.
192
Be ware: INSERT DELAYED works under differnet thread
193
READ LOCAL: allows non-confilicting INSERT statments.
194
LOW_PRIORITY WRITE: By default WRITE locks have prioriy on READ locks
197
releases any locks held by the current thread
b'\\ No newline at end of file'