Skip to content

Latest commit

 

History

History
372 lines (309 loc) · 11.7 KB

MySQL.md

File metadata and controls

372 lines (309 loc) · 11.7 KB
Raw

MySQL

Install MySQL Server on the Ubuntu operating system

Installation

sudo apt-get update
sudo apt-get install mysql-server

Start service

sudo systemctl start mysql

Check version

mysql -V

Start shell

mysql -u root -p

Database

Show databases:

SHOW DATABASES;

Create database:

CREATE DATABASE mydb;

Table

First we need to select the database to use:

USE mydb;

Show tables:

SHOW TABLES;

Show description of a table:

DESC[RIBE] mytable;

Create table:

CREATE TABLE mytable(phone char(11) primary key, buy_time timestamp);

Copy table:

SQL Cloning Tables

CREATE TABLE mytable2 SELECT * FROM mytable;

Delete table:

DROP TABLE [IF EXISTS] mytable;

Rename table:

RENAME TABLE mytable2 TO mytable;

Show columns:

SHOW COLUMNS FROM mytable;

Set column(s) as primary key:

MySQL Primary Key

ALTER TABLE mytable ADD PRIMARY KEY(phone, buy_time);

Add column:

ALTER TABLE mytable ADD COLUMN sex BOOLEAN;

Change column's data type:

How do I change the data type for a column in MySQL?

ALTER TABLE mytable MODIFY embedding_1 INTEGER [NOT NULL];

For a timestamp column, Remove on update CURRENT_TIMESTAMP and default CURRENT_TIMESTAMP:

How do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column?

-- to remove "on update CURRENT_TIMESTAMP" from Extra
ALTER TABLE mytable CHANGE COLUMN buy_time buy_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- to remove "CURRENT_TIMESTAMP" from Default
ALTER TABLE mytable ALTER COLUMN buy_time DROP DEFAULT;

To allow zero date in MySQL:

Invalid default value for 'create_date' timestamp field

SET SQL_MODE='ALLOW_INVALID_DATES';

This solves ERROR 1067 (42000): Invalid default value for 'buy_time'.

Query

Count

SELECT COUNT(*) from mytable;

Max

Get the max value of buy_time:

SELECT MAX(buy_time) AS latest FROM mytable;

Get the row containing the max value of buy_time(MySQL - How to select rows with max value of a field):

SELECT phone, buy_time FROM mytable WHERE buy_time = (SELECT MAX(buy_time) FROM mytable);

Operation on columns

SELECT width * height AS area FROM rectangle;

Concat

SET @a="abc";
CALL DebugMessage(TRUE, CONCAT(@a, "def")); -- abcdef
-- note that the variable 'b' is not declared
CALL DebugMessage(TRUE, CONCAT(@a, "def", @b)); -- NULL

To ignore NULL value automatically(MySQL CONCAT returns NULL if any field contain NULL):

CALL DebugMessage(TRUE, CONCAT(@a, "def", COALESCE(@b,""))); -- abcdef

Insert

INSERT INTO mytable VALUES (12345678901, FROM_UNIXTIME(1578635060), 3.000000, 3.000000);

Delete

DELETE FROM mytable WHERE phone=12345678901;

Delete all:

DELETE FROM mytable;

How to delete a MySQL record after a certain time

DELETE FROM mytable WHERE buy_time < (CURDATE() - INTERVAL 2 DAY);

Delete by some order(mysql deleting oldest record in a table), this delete the earliest buying record of "9876543210":

DELETE FROM mytable WHERE phone = 9876543210 ORDER BY buy_time ASC LIMIT 1;

Delete all records with phone = 99999999999 but remain the one with largest buy_time(Delete all Duplicate Rows except for One in MySQL? [duplicate]):

DELETE r1 FROM mytable r1, mytable r2 where r1.buy_time < r2.buy_time and r1.phone = r2.phone and r1.phone = 99999999999;

Update

UPDATE mytable SET phone=09876543210 WHERE phone=12345678901;
UPDATE mytable SET sex=TRUE WHERE phone=12345678901;
UPDATE mytable SET sex=0 WHERE phone=12345678902;

Multiply every entry in column by a fixed number in SQL

UPDATE mytable SET embedding_1=embedding_1*1000;

DISTINCT

SELECT DISTINCT phone FROM mytable WHERE embedding_1 < 0.01;

GROUP BY

Note that it's GROUP BY, not GROUPBY.

SELECT sex, COUNT(*) FROM mytable GROUP BY sex;

Time

Date:

SELECT CURRENT_DATE();

Time:

SELECT CURRENT_TIME();

Timestamp(Date + Time):

SELECT CURRENT_TIMESTAMP();

Use as subquery:

INSERT INTO mytable VALUES("12345678902", CURRENT_TIMESTAMP());

SubQuery

MySQL SubQuery Tutorial with Examples

Query from 2 different tables:

SELECT phone, buy_time FROM mytable WHERE (phone, buy_time) IN 
    (SELECT phone, buy_time FROM mytable2 WHERE sex =0);

Get the record count of the phones who contain at least one record of embedding_1 < 0.01.

SELECT phone, COUNT(*) AS ALL_COUNT FROM mytable WHERE phone IN 
    (SELECT DISTINCT phone FROM mytable WHERE embedding_1 < 0.01)
GROUP BY phone;

Variable

Set variable

How to declare a variable in MySQL?

SET @a="abc";

How to store Query Result in variable using mysql

Store the query result into a variable.

SELECT @latest := MAX(buy_time) FROM mytable;
SELECT @latest;

Derived table(Subquery in the FROM clause)

An Essential Guide to MySQL Derived Table and Using A Subquery in the FROM clause

First find the phones containing at least one record meeting embedding_1 < 0.01, and then calculate the ratio of records meeting this requirement for each phone.

SELECT T1.phone, T1.VALID_COUNT/T2.ALL_COUNT AS RATIO FROM 
    (SELECT phone, COUNT(*) AS VALID_COUNT FROM mytable WHERE (phone, buy_time) IN (SELECT phone, buy_time FROM mytable WHERE embedding_1 < 0.01) GROUP BY phone) T1,  
    (SELECT phone, COUNT(*) AS ALL_COUNT FROM mytable WHERE phone in (SELECT DISTINCT phone FROM mytable WHERE embedding_1 < 0.01) GROUP BY phone) T2 
WHERE T1.phone = T2.phone;

Temporary table

Create a temporary table in a SELECT statement without a separate CREATE TABLE

Store the query result into a temp table, this can be used to simplify nested query.

CREATE TEMPORARY TABLE IF NOT EXISTS T1 AS (<another_query>);

Using temporary table, the example in Derived table can be simplified as:

CREATE TEMPORARY TABLE IF NOT EXISTS T1 AS (SELECT phone, count(*) AS VALID_COUNT FROM mytable WHERE embedding_1 < 0.01 GROUP BY phone);
CREATE TEMPORARY TABLE IF NOT EXISTS T2 AS (SELECT phone, COUNT(*) AS ALL_COUNT FROM mytable WHERE phone in (SELECT DISTINCT phone FROM mytable WHERE embedding_1 < 0.01) GROUP BY phone);
SELECT T1.phone, T1.VALID_COUNT/T2.ALL_COUNT AS RATIO FROM T1, T2 WHERE T1.phone = T2.phone;

Stored procedure

This stored procedure add 128 columns named embedding_1 to embedding_128 to mytable. (Adapted from Add columns to mySQL table with loops and MySQL REPEAT Loop and Dynamic add column with loop and How To have Dynamic SQL in MySQL Stored Procedure).

About DELIMITER $$, please check: MySQL Delimiter.

Edit addcolumns.sql:

DELIMITER $$
DROP PROCEDURE IF EXISTS AddColumns$$
CREATE PROCEDURE AddColumns()
BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(100) DEFAULT '';

    REPEAT
        SET result = '';
        SET result = CONCAT(result,'embedding_',counter);
        SET counter = counter + 1;
        SET @sql = CONCAT('ALTER TABLE mytable ADD ',result,' float');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    UNTIL counter > 128
    END REPEAT;

    -- display result
    SELECT result;
END
$$
DELIMITER ;

Edit the stored procedure used for debugging debug_msg.sql:

How do you debug MySQL stored procedures?

DELIMITER $$
DROP PROCEDURE IF EXISTS `DebugMessage`$$
CREATE PROCEDURE DebugMessage(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN
    select concat('** ', msg) AS '** DEBUG:';
  END IF;
END $$
DELIMITER ;

Edit the stored procedure used for looping through columns loop_columns.sql and multiply each column by 1000:

mysql, iterate through column names and MySQL REPEAT Loop and MYSQL WHILE LOOP CONTINUE is not recognized

DELIMITER $$
DROP PROCEDURE IF EXISTS LoopColumns$$
CREATE PROCEDURE LoopColumns()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE col_name VARCHAR(100) DEFAULT '';
DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'mytable'
  ORDER BY ordinal_position;
-- https://stackoverflow.com/questions/4955231/cursor-not-open-error
OPEN col_names;

the_loop: REPEAT
    FETCH col_names INTO col_name;

    -- the first 2 column of mytable is phone and buy_time, last 128s are embedding_x
    IF i < 3 THEN
        SET i = i + 1;
        -- "ITERATE" acts as "continue;" in C/C++
        ITERATE the_loop;
    END IF;
    SET @s = CONCAT('UPDATE mytable SET ', COALESCE(col_name, ''), '=', COALESCE(col_name, ''), '*1000');

    PREPARE stmt FROM @s;
    EXECUTE stmt;

    SET i = i + 1;
UNTIL i > 130
END REPEAT;
CLOSE col_names;
END
$$
DELIMITER ;

To run it, from How to create a mysql stored procedure through linux terminal:

mysql -u root -p<password> <mydb> < <sql_file_name>.sql

To run stored procedure in mysql shell(Execute MySQL Stored Procedure using Command Line):

CALL DebugMessage(TRUE, "ABC");

This sample use 128 columns to store a vector, for an alternative way, try How do I create a field with a vector type in MySQL?.