mysql

Installation

1. VM ubuntu server
2. The default login is sudo mysql ie. only the superuser should be able to access the database
3. Create a user with admin priviletges and flush:

       CREATE USER 'admin'@'%' IDENTIFIED BY 'SuperSecretPassword!123';
       GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;
   

4. Need to allow remote access to server by changing the bind address to 0.0.0.0 in sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf and then restart mysql
5. Using VS Code with extension.

Digital Ocean Guide

1. install phymyadmin and then access thru browser. Save google sheet as .ods extension and can import using the phpmyadmin web interface. Make sure field names and data types are correct.

Queries

select * from table_name;
select * from table_name where column = "string";
select * from table_name where column like '%string%';
select count(*) from table_name where column like '%string%';

Primary Keys

Primary Keys

autoincrement int versus UUID Use this code:

    CREATE TABLE mytable (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    name VARCHAR(255)
    );
```<br>

``` { .sql .copy }
CREATE TABLE Students(
    ID int PRIMARY KEY AUTO_INCREMENT,
    FirstName varchar(25) NOT NULL,
    LastName varchar(25),
    Age int
);

concept of binary numbers

Importing data

From google sheets make sure all dates are YYYY-MM-DD and all fields match. Leave id field out so can auto fill on import.
To just export the sheet of interest alter the url of the sheet with: export?format=ods&gid ...

Resources

SQL cheat sheet