Mysql/CheatSheet: Difference between revisions

From Wiki
mNo edit summary
 
Line 240: Line 240:
</pre>
</pre>
</blockquote>
</blockquote>
[[Category:Programming]]

Latest revision as of 15:14, 28 September 2024

Quick links


Databases

  • List all
SHOW DATABASES;
  • Create
CREATE DATABASE [IF NOT EXISTS] database_name;
  • Use
USE database_name;
  • Delete
DROP DATABASE [IF EXISTS] database_name;
  • Storage Engines
    • MyISAM (optimized for compression and speed. portable. not transaction safe. used to be default)
    • InnoDB (optimized for speed. portable. default)
    • MERGE
    • MEMORY (HEAP)
    • ARCHIVE
    • CSV
    • FEDERATED

Tables

  • list
SHOW TABLES;
  • create
CREATE TABLE [IF NOT EXISTS] table_name(
  column1 datatype constraint,
  column2 datatype constraint
);
CREATE TABLE IF NOT EXISTS cars(
  vehicle_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  first_seen DATE,
  description TEXT
);
  • show table information
DESCRIBE cars;

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| vehicle_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | varchar(255)| NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
  • delete
DROP TABLE [IF EXISTS] cars;

column definitions

NOT NULL
DEFAULT default_value
AUTO_INCREMENT
ZEROFILL
UNIQUE 
PRIMARY KEY   (NOT NULL and UNIQUE)
INVISIBLE
COMMENT 'string'
CHECK (expr)

data types

TINYINT	        (A very small integer: 0/255, 1 byte)
SMALLINT	(A small integer: 0/65535, 2 bytes)
MEDIUMINT	(A medium-sized integer: 0/16777215, 3 bytes)
INT	        (A standard integer: 0/4294967295 4 bytes)
BIGINT	        (A large integer: 0/18446744073709551615 8 bytes)
DECIMAL(M,D)    (A fixed-point number: M=total digits(max=65), D=decimals(max=30))
DEC             (=DECIMAL)
FLOAT[(M,D)]    (A single-precision float (4 bytes)(M=digits, D=decimals))
DOUBLE[(M,D)]   (A double-precision float (8 bytes))
BIT[(M)]	(A bit field)

CHAR[(M)]       (A fixed-length character string, M=0-255, default=1)
VARCHAR(M)      (A variable-length string, M=0-65532) 
BINARY(M)       (A fixed-length binary string, M=bytes)
VARBINARY(M)	(A variable-length binary string, M=bytes)

TINYTEXT	(A very small string, max 255 chars)
TEXT[(M)]       (A small  string, M=0-65532)
MEDIUMTEXT	(A medium-sized string, 16777215 chars)
LONGTEXT	(A large string, 4294967295 chars) 
JSON            (=LONGTEXT)

ENUM	        (Each column value may be assigned one ENUM member)
                (e.g. ENUM('v1','v2'))
SET	        (Each column value may be assigned zero or more SET members)
                (e.g. SET('v1,'v2'))

TINYBLOB	(A very small BLOB (binary large object)
BLOB     	(A small BLOB)
MEDIUMBLOB	(A medium-sized BLOB)
LONGBLOB	(A large BLOB)

DATE	        (A date value: YYYY-MM-DD, 3 bytes)
TIME	        (A time value: hh:mm:ss, 3 bytes)
DATETIME[x]	(A date and time value: YYYY-MM-DD hh:mm:ss, 8 bytes,
                 x=fractional second precision)
TIMESTAMP       (A timestamp value: YYYY-MM-DD hh:mm:ss, 4 bytes)
YEAR	        (A year value: YYYY, 1 byte)

  • data type attributes
SIGNED
UNSIGNED
ZEROFILL
UNSIGNED ZEROFILL
ZEROFILL UNSIGNED 

AUTO_INCREMENT
  • charsets and collations
utf8_general_ci      (maxlen=1)
latin1_swedish_ci    (maxlen=3)
_ci = case insensitive

Data

Insert

INSERT INTO employees VALUES ('peter', 'pan', 25000);
INSERT INTO employees ('first', 'last', 'pay') VALUES ('peter', 'pan', 25000);

Select

SELECT * FROM employees;

SELECT * FROM employees WHERE last='pan';

SELECT * FROM employees WHERE last='pan' ORDER BY DateTime DESC;

SELECT * FROM employees WHERE last='pan' ORDER BY DateTime DESC LIMIT 10;

Update

UPDATE employees SET first='Peter' WHERE first='peter';

Replace

Delete

DELETE FROM employees WHERE first='peter';

Docker CLI access

docker exec -it containername mysql -p

other

time based

time INT(13)