Podstawy mysql #db

0_linki.md

http://www.techonthenet.com/mysql/index.php

1_create.md

Syntax

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Full syntax

CREATE [ TEMPORARY ] TABLE [IF NOT EXISTS] table_name
( 
  column1 datatype [ NULL | NOT NULL ]
                   [ DEFAULT default_value ]
                   [ AUTO_INCREMENT ]
                   [ UNIQUE KEY | PRIMARY KEY ]
                   [ COMMENT 'string' ],

  column2 datatype [ NULL | NOT NULL ]
                   [ DEFAULT default_value ]
                   [ AUTO_INCREMENT ]
                   [ UNIQUE KEY | PRIMARY KEY ]
                   [ COMMENT 'string' ],
  ...

  | [CONSTRAINT [constraint_name]] PRIMARY KEY [ USING BTREE | HASH ] (index_col_name, ...)

  | [INDEX | KEY] index_name [ USING BTREE | HASH ] (index_col_name, ...)

  | [CONSTRAINT [constraint_name]] UNIQUE [ INDEX | KEY ] 
        [ index_name ] [ USING BTREE | HASH ] (index_col_name, ...)

  | {FULLTEXT | SPATIAL} [ INDEX | KEY] index_name (index_col_name, ...)

  | [CONSTRAINT [constraint_name]] 
        FOREIGN KEY index_name (index_col_name, ...)
        REFERENCES another_table_name (index_col_name, ...)
        [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
        [ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

  | CHECK (expression)

    {ENGINE | TYPE} = engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | [DEFAULT] CHARACTER SET = charset_name
  | CHECKSUM = {0 | 1}
  | [DEFAULT] COLLATE = collation_name
  | COMMENT = 'string'
  | DATA DIRECTORY = 'absolute path'
  | DELAY_KEY_WRITE = { 0 | 1 }
  | INDEX DIRECTORY = 'absolute path'
  | INSERT_METHOD = { NO | FIRST | LAST }
  | MAX_ROWS = value
  | MIN_ROWS = value
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | RAID_TYPE = { 1 | STRIPED | RAIDO }
       RAID_CHUNKS = value
       RAID_CHUNKSIZE = value
  | ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}
  | UNION = (table1, ... )
);

Examples

CREATE TABLE contacts
( 
  contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE TABLE suppliers
( 
  supplier_id INT(11) NOT NULL AUTO_INCREMENT,
  supplier_name VARCHAR(50) NOT NULL,
  account_rep VARCHAR(30) NOT NULL DEFAULT 'TBD',
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE IF NOT EXISTS `menu` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `parent_id` int(10) unsigned NOT NULL DEFAULT '1',
    `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_menu_menu` (`parent_id`),
    CONSTRAINT `FK_menu_menu` FOREIGN KEY (`parent_id`) REFERENCES `menu` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2_select.md

Syntax

SELECT expressions
FROM tables
WHERE conditions;

Full Syntax

SELECT [ ALL | DISTINCT | DISTINCTROW ]
       [ HIGH_PRIORITY ]
       [ STRAIGHT_JOIN ]
       [ SQL_SMALL_RESULT | SQL_BIG_RESULT ] [ SQL_BUFFER_RESULT ]
       [ SQL_CACHE | SQL_NO_CACHE ]
       [ SQL_CALC_FOUND_ROWS ]
expressions
FROM tables
WHERE conditions
[ GROUP BY expressions ]
[ HAVING condition ]
[ ORDER BY expression [ ASC | DESC ] ]
[ LIMIT [offset_value] number_rows | LIMIT number_rows OFFSET offset_value ]
[ PROCEDURE procedure_name ]
[ INTO [ OUTFILE 'file_name' options 
       | DUMPFILE 'file_name'
       | @variable1, @variable2, ... @variable_n ]
[ FOR UPDATE | LOCK IN SHARE MODE ];

Select all fields from one table

SELECT *
FROM order_details
WHERE quantity >= 10
ORDER BY quantity DESC;

Select individual fields from one table

SELECT order_id, quantity, unit_price
FROM order_details
WHERE quantity < 500
ORDER BY quantity ASC, unit_price DESC;

Select fields from multiple tables

SELECT order_details.order_id, customers.customer_name
FROM customers
INNER JOIN order_details
ON customers.customer_id = order_details.customer_id
ORDER BY order_id;

Write to a file

SELECT order_id, quantity, unit_price
FROM order_details
WHERE quantity < 500
ORDER BY quantity
INTO OUTFILE 'results.txt'
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n';

Using limit keyword

SELECT contact_id, last_name, first_name
FROM contacts
WHERE website = 'TechOnTheNet.com'
ORDER BY contact_id DESC
LIMIT 5;

3_update.md

Syntax

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE conditions;

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
WHERE conditions;

UPDATE table1, table2, ... 
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
AND conditions;

Full syntax

UPDATE [ LOW_PRIORITY ] [ IGNORE ]
table
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE conditions
[ ORDER BY column [ ASC | DESC ] ]
[ LIMIT number_rows ];

Update single column

UPDATE customers
SET last_name = 'Anderson'
WHERE customer_id = 5000;

Update multiple columns

UPDATE customers
SET state = 'California',
    customer_rep = 32
WHERE customer_id > 100;

Update table with data from another table

UPDATE customers
SET city = (SELECT city
            FROM suppliers
            WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id > 2000;

Update multiple tables

UPDATE customers, suppliers
SET customers.city = suppliers.city
WHERE customers.customer_id = suppliers.supplier_id;

4_insert.md

Syntax

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
...;

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
WHERE conditions;

Full syntax

INSERT [ LOW_PRIORITY | DELAYED | HIGH_PRIORITY ] [ IGNORE ]
INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
[ ON DUPLICATE KEY UPDATE 
    dup_column1 = dup_expression1,
    dup_column2 = dup_expression2,
    ... ];

Using values keyword

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Dell');

Using sub-select

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id < 5000;

5_delete.md

Syntax

DELETE FROM table
WHERE conditions;

Full syntax

DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] FROM table
WHERE conditions
[ ORDER BY column [ ASC | DESC ] ]
[ LIMIT number_rows ];

Examples

DELETE FROM contacts
WHERE last_name = 'Johnson';

DELETE FROM contacts
WHERE last_name = 'Johnson'
ORDER BY contact_id DESC
LIMIT 1;