mysql

Mr.WarawutMr.Warawut KhangkhanKhangkhanMr.WarawutMr.Warawut KhangkhanKhangkhanFacebookFacebook: : http://www.facebook.com/AjWarawuthttp://www.facebook.com/AjWarawut

Twitter: Twitter: http://twitter.com/awarawuthttp://twitter.com/awarawutEE--Mail: Mail: [email protected]@hotmail.com

Mobile: Mobile: 089089--461461--95919591

Mr.Warawut Khangkhan Chapter 6 MySQL 2

MySQLMySQL Command PromptCommand Prompt� XAMPP

c:c:c:c:\\\\xamppxamppxamppxampp\\\\mysqlmysqlmysqlmysql\\\\binbinbinbin

� �� mysql

mysqlmysqlmysqlmysql --------helphelphelphelpmysqlmysqlmysqlmysql --------helphelphelphelp

� �� Database Server

mysqlmysqlmysqlmysql ––––h h h h hosthosthosthost ––––u u u u user user user user ----ppppor

mysqlmysqlmysqlmysql ----u u u u useruseruseruser ----pppp

� �� Database Server

quitquitquitquit or exitexitexitexit

Mr.Warawut Khangkhan 3Chapter 6 MySQL

Data typeData type� Numeric

� Date and Time

� String


NumericData type Byte Signed Unsigned

TINYINT[(M)] 1 -128 �� 127 0 �� 255

SMALLINT[(M)] 2 -32768 �� 32767 0 �� 65535

MEDIUMINT[(M)] 3 -8388608 �� 0 �� 16777215MEDIUMINT[(M)] 3 -8388608 ��8388607

0 �� 16777215

INT[(M)],INTEER[(M)]

4 -2147483648 ��2147483647

0 �� 4294967295

BIGINT[(M)] 8 -9223372036854775808 ��9223372036854775807

0 ��18446744073709551615


NumericData type Byte Signed Unsigned

FLOAT[(M)] 4 -3.402823466E+38

��-1.175494351E-38

1.175494351E-38 �� 3.402823466E+383.402823466E+38

DOUBLE[(M, D)],DOUBLE,PRECISION[(M, D)],REAL[(M, D)]

8 -1.7976931348623157E+308

�� -2.2250738585072014E-308

2.2250738585072014E-308

��1.7976931348623157E+308

DECIMAL[(M[,D])],DEC[(M[,D])],NUMERIC[(M[,D])]

M+2 �� (M) �� char


Date and TimeData type Format Range

DATE YYYY-MM-DD 1000-01-01 �� 9999-

12-31

DATETIME YYYY-MM-DD 1000-01-01 00:00:00 DATETIME YYYY-MM-DD HH:MM:SS

1000-01-01 00:00:00 �� 9999-12-31

23:59:59

TIMESTAMP[(M)] YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD ��YYMMDD �� M �� 14, 12, 8 �� 6

1970-01-01 00:00:00 �� ! �.#. 2037


Date and TimeData type Format Range

TIME HH:MM:SS -838:59:59 ��838:59:59

YEAR[(2|4)] YYYY ��$�%&'�� 2 �(�� YEAR[(2|4)] YYYY ��$�%&'�� 2 �(�� ! �.#. ��)�'�*��*��1970 �� 2069 ��$�%&'�� 4 �(�� ! �.#. ��)�'�*��*�� 1901 �� 2155


String� �'��+��,��+ �� '��&�,� CHAR ��* VARCHAR

� �'��- ��'��)�� (Binary) �&�� '��+�� .�/ �� '��&�,� TEXT ��* BLOB

� �'��&�,�/,�#0��'�� /��/��%�� (2�� '��&�,�/,�#0��'�� /��/��%�� (2�� 3��(�� '��)�'%��$*+�'��,�) – ENUM ��* SET


StringData type Range

CHAR(M) 1 �� 255

VARCHAR(M) 1 �� 255

TINYBLOB TINYTEXT 1 �� 255TINYBLOB TINYTEXT 1 �� 255

BLOB TEXT 1 �� 65535

MEDIUMBLOGMEDIUMTEXT

1 �� 16777215

LONGBLOBLONGTEXT

1 �� 4294967295


�� CHAR �� VARCHAR

� �$*� ��'�� '��&�,� CHAR �*� ��'��/�'�� &��*�5 +��'��&�,� VARCHAR �*� ��6/�*�'�� 3��,��

� %��$*��%&'�'�� '��&�,� CHAR �*�(�� &��'��'�� %��$*��%&'�'�� '��&�,� CHAR �*�(�� &��'��'��

� /�� '��%&'+(�� '��&�,� CHAR �� *�5)�' +��'��&�,� VARCHAR �*%&'/�� ,��'��'� 1 byte �/��%&'+(�� '��


Create and Drop DatabaseCreate and Drop Database� Create Database

CREATE DATABASE [IF NOT EXISTS]

db_name

� Drop Database� Drop DatabaseDROP DATABASE [IF EXISTS] db_name


db_name +��%&'� �� 0�%�- ��)�' ��'� / \ . ��*&��'��)��,� 64 � �� 0�

Create TableCreate Tableformat:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS]

tbl_name[(create_definition, …)][(create_definition, …)]

[table_options]

[select_statement]


create_definition �*��'� 3 +��

� &�� 7 (89��7) ��)��,� 64 � �� 0�� &�,��'�� 7

� �'��(��- � �'��(��-


�� create_definition[NOT NULL | NULL]

[DEFAULT default_value]

[AUTO_INCREMENT]

[PRIMARY KEY] [reference_definition]

or PRIMARY KEY (index_col_name, …)or PRIMARY KEY (index_col_name, …)

or KEY [index_name] (index_col_name, …)

or INDEX [index_name] (index_col_name, …)

or UNIQUE [INDEX] [index_name] (index_col_name, …)

or FULLTEXT [INDEX] [index_name] (index_col_name, …)

or [CONSTRAINT symbol] FOREIGN KEY [index_name]

(index_col_name, …) [reference_definition]

or CHECK (expr)


�� 1: create table

create table if not exists saleorder

(OrderNo varchar(15) primary key,

CustomerNo varchar(20),

OrderDate datetime,OrderDate datetime,

PromiseDate date,

Note varchar(80));



create table saleorder_detail

(OrderNo varchar(15) not null,

SequenceNo int(3) not null,

ItemNo varchar(20),ItemNo varchar(20),

Qty double(10, 2),

primary key (OrderNo, SequenceNo));



create table saleorder_detail

(ID int auto_increment primary key,

OrderNo varchar(15) not null,

SequenceNo int(3) not null,SequenceNo int(3) not null,

ItemNo varchar(20),

Qty double(10, 2),

UnitPrice double(14, 4),

Amount double(14, 4),

OrderStatus char(1) default ‘A’);


Alter TableAlter Tableformat:

ALTER [IGNORE] TABLE tbl_namealter_specification [, alter_specification …]


alter_specificationalter_specification �� 3��5��(�+ �� 3��5��(�+ �� ADD, ALTER, ADD, ALTER,

CHANGE, MODIFY, DROP, RENAMECHANGE, MODIFY, DROP, RENAME

alter_specification��

ADD [COLUMN] create_definition [FIRST |

AFTER column_name]

�� !" �� !" ADD INDEX [index_name] (col_name, …)


alter table table_a add field0 varchar(10) first;

alter table table_a add field5 int after field4;

alter table table_a add index (field0);

alter_specification�� primary keyprimary key

ADD PRIMARY KEY (col_name, …)

alter table table_a add primary key (field0, field1);

�� unique indexunique index

ADD UNIQUE [index_name] (col_name, …)


alter table table_a add primary key (field0, field1);

alter_specification�� #�#�� #�#��

ALTER [COLUMN] col_name {SET DEFAULT

literal | DROP DEFAULT}


alter table table_a alter field2 set default ‘noname’;

alter table table_a alter field2 drop default;

alter_specification�� $�%�� $�%�� (($��(�� $��(�� 11))

CHANGE [COLUMN] col_namecreate_defintion

�� $�%�� $�%�� (($��(�� $��(�� 22))MODIFY create_defintion


alter table table_a change field2 field2_new tinyint(1);

alter_specification��

DROP [COLUMN] col_name

�� primary keyprimary key

DROP PRIMARY KEYDROP PRIMARY KEY

�� !" �� !" DROP INDEX index_name


alter_specification�� (�� (��

RENAME TABLE tbl_name TO new_tbl_name [,

tbl_name2 TO new_tbl_name, …]

�� (��(�� (��(��DROP TABLE [IF EXISTS] tbl_name [,

tbl_name , …]


Data OperatorData Operator�� ((�� INSERT INTO)INSERT INTO)

INSERT INTO tbl_name (col1, col2 ) VALUES (val1, val2)

��

RENAME TABLE tbl_name SET col_name = expression


Data OperatorData Operator�� ((�� DELETE)DELETE)

DELETE FROM tbl_name WHERE where_definition

�� $�%�� $�%�� ((�� UPDATE)UPDATE)�� $�%�� $�%�� ((�� UPDATE)UPDATE)

UPDATE tbl_name SET col_name = expression WHERE where_definition

�� ((�� SELECT)SELECT)

SELECT select_expression FROM table_nameWHERE where_definitionORDER BY col_name


Mr.Warawut Khangkhan 31Chapter 6 MySQL

BooksBooks� +�# ��,: 2&�& &5�,�5�. Insight PHP *��+ . ��5��/;: 2 ��,& ��,

2550. 568 ��'�.

� �,��, . �� <�*�5�. ��,�� PHP. (�� .�(�� 4). ��5��/;: ��/� ��/7 ��7 ��= ��7, 2547.��7 ��= ��7, 2547.


mysql

Education

warawut khangkhan f

definition f

warawut khangkhan chapter

f f ff64 fffmr

yyyymmdd yymmdd f f

f mdecm

defintionalter table

specification f fadd