MySQL Шпаргалка по командам

# Comment

---------------------------------------------------------------------------------------------------------

# WORK WITH DATABASE

CREATE DATABASE db_name; (CREATE DATABASE IF NOT EXIST db_name CHARACTER SET utf8;)

SHOW DATABASES;

USE db_name; (ALTER DATABASE db_name CHARACTER SET utf8;)

DROP DATABASE db_name;

---------------------------------------------------------------------------------------------------------

# CREATE TABLE

CREATE TABLE  IF NOT EXISTS tb_name (
    id int(50) NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    date DATE,
    time time,
    PRIMARY KEY (id),
    FOREIGN KEY (name),
    REFERENCES tb_name2 (col1, col2)
);

---------------------------------------------------------------------------------------------------------

# SHOW TABLE

SHOW TABLES;

SHOW COLUMNS FROM tb_name; (SHOW FIELDS FROM tb_name;)

DESCRIBE tb_name;

---------------------------------------------------------------------------------------------------------

# DROP TABLE

DROP TABLE tb_name; (DROP TABLE IF EXISTS tb_name;)

---------------------------------------------------------------------------------------------------------

# SELECT FROM TABLE

SELECT * FROM tb_name;

SELECT col1, col2, col3 FROM tb_name WHERE col1 = 'Bob' AND col2 != '33' AND col3 LIKE '%Tom%' ORDER BY col3 DESC LIMIT 1, 10;

SELECT COUNT(*) FROM tb_name;

---------------------------------------------------------------------------------------------------------

# JOIN TABLE

SELECT * FROM alpha LEFT JOIN beta USING (id);

---------------------------------------------------------------------------------------------------------

# INSERT INTO TABLE

INSERT INTO tb_name (col1, col2) VALUES ('Bob', 'Tom');

---------------------------------------------------------------------------------------------------------

# UPDATE TABLE

UPDATE tb_name SET col1 = 'Tom' WHERE col1 = 'Bob';

---------------------------------------------------------------------------------------------------------

# DELETE FROM TABLE

DELETE FROM tb_name WHERE col1 = 'Bob';

---------------------------------------------------------------------------------------------------------

# ALTER TABLE

ALTER TABLE tb_name ADD COLUMN col1 VARCHAR (20) FIRST; (ALTER TABLE tb_name ADD PRIMARY KEY (id);)

ALTER TABLE tb_name CHANGE old_col new_col VARCHAR (50) AFTER col2; (ALTER TABLE tb_name CHANGE old_col new_col VARCHAR (50) FIRST;)

ALTER TABLE tb_name MODIFY col1 VARCHAR (50) FIRST; (ALTER TABLE tb_name MODIFY col1 VARCHAR (20) AFTER col2;)

ALTER TABLE tb_name DROP COLUMN col1;

---------------------------------------------------------------------------------------------------------

# USER PRIVILEGES

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password';

SET PASSWORD FOR 'bob'@'localhost' = PASSWORD('new_pass');

GRANT ALL PRIVILEGES ON db_name.* TO 'bob'@'localhost' WITH GRANT OPTION;
GRANT SELECT, INSERT, DELETE ON db_name.* TO 'bob'@'localhost' WITH GRANT OPTION;

REVOKE ALL PRIVILEGES ON db_name.* FROM 'bob'@'localhost';

DROP USER 'bob'@'localhost';

FLUSH PRIVILEGES;

---------------------------------------------------------------------------------------------------------

# COMMON COLUMN TYPES

INT            (–2147483648 / 2147483647) (0 / 4294967295)
TINYINT     (–128 / 127) (0 / 255)
FLOAT       (–3.402823466E+38 / –1.175494351E-38) (1.175494351E-38 / 3.402823466E+38)
DECIMAL, NUMERIC ()
CHAR        (1 / 255)
VARCHAR (1 / 255)
DATE            ('1000-01-01’ / ‘9999-12-31’) (‘YYYY-MM-DD’ format)
DATETIME   (‘1000-01-01 00:00:00’ / ‘9999-12-31 23:59:59’) (‘YYYY-MM-DD HH:MM:SS’ format)
TIMESTAMP  (‘1970-01-01 00:00:00’ / year 2037) (YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD format)
TIME  (‘-838:59:59’ / ‘838:59:59’) (‘HH:MM:SS’ format)
YEAR (1901 / 2155) (YYYY format)
TEXT, BLOB (65535 characters)
ENUM (‘val1’, ‘val2’)
SET     (‘value1’, ‘value2’)

---------------------------------------------------------------------------------------------------------

# OTHER COLUMN TYPES

SMALLINT    (32768 / 32767) (0 / 65535)
MEDIUMINT (–8388608 / 8388607) (0 / 16777215)
BIGINT          (–9223372036854775808 / 9223372036854775807) (0 / 18446744073709551615)

DOUBLE, DOUBLE PRECISION, REAL (-1.7976931348623157E+308 / -2.2250738585072014E-308)

TINYTEXT, TINYBLOB             (255 characters)
MEDIUMTEXT, MEDIUMBLOB (16777215 characters)
LONGTEXT, LONGBLOB          (4294967295 characters)