Table of Contents

1 Install Mysql80-server and Mysql80-client in FreeBSD.

cd /usr/ports/databases/mysql80-server && make install clean    
cd /usr/ports/databases/mysql80-client && make install clean    

2 MySql Command line, run shell command

  • login to mysqld
mysql -u root                                                                 
# show database                                                               
show databases;                                                               
# select database                                                             
use mydb;                                                                     
# show all tables;                                                            
show tables;                                                                  
# show scheme                                                                 
desc person_table;                                                            
# run shell command inside mysqld                                             
\! pwd    <- there is a space between \! and pwd                              
\! ls                                                                         
  • MySql Int Type
Integer Type description Minimum value signed Maximum value unsigned Example
TINYINT 1 Byte 0 21 - 1  
SMALLINT 2 Bytes 0 22 - 1  
MEDIUMINT 3 Bytes 0 23 - 1  
INT 4 Bytes 0 24 - 1  
BIGINT 8 Bytes 0 28 - 1  
  • MySql Date Type
DATE date part but no time part, no time '9999-12-31'
DATETIME date part and time part, range: '1000-01-01 00:00:00' ⇒ '9999-12-31 23:59:59' '9999-12-31 00:00:00'
TIMESTAMP date part and time part, range: '1970-01-01 00:00:01' ⇒ '2038-01-19 03:14:07' UTC '9999-12-31 00:00:00'

3 MySql Java Example compile and build in Intellij Java MySql Example

  • Add $b/javalib8/jar/AronLib.jar to libraries in Intellij.
  • Add $b/javalib8/classfile to libraries in Intellij.
  • Add $b/javalib8/jar/mysql-connector-java-8.0.15.jar in Intellij.

add_jdbc_to_intellij.png

4 MySql Login, Create user and grant permission: mysql 8.x

  • login to mysql server.

    mysql -u root                                                               
    
  • Create user called cat with password

    create user 'cat'@'localhost' identified by 'mypassword';                    
    # if the user can be accessed from remote.                              
    create user 'cat'@'%' identified by 'mypassword';                     
    
  • Update user password

    ALTER user 'cat'@'localhost' identified by 'mypassword';                  
    FLUSH PRIVILEGES;                                                           
    # exit                                                                      
    # login with new password                                                   
    mysql -u cat -p                                                             
    
    # remote user                                                               
    ALTER user 'cat'@'%' identified by 'mypassword';                       
    # exit                                                                      
    # login with new remote password                                            
    
  • Grant user to particular table permission.
  • Grant user to access to all tables in a database: mydb.

    # grant user1 to access to all tables in a database mydb.
    # mysql -u root
    # show database;
    GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';
    

5 Create user from remote access in FreeBSD.

6 Mysql Create table, Insert data into table.

CREATE TABLE IF NOT EXISTS customer;
CREATE TABLE IF NOT EXISTS address;
CREATE TABLE IF NOT EXISTS order;           

CREATE TABLE customer(
  c_id INT NOT NULL auto_increment PRIMARY KEY,
  user VARCHAR(100) NOT NULL,
  password TEXT NOT NULL,
  name TEXT NOT NULL,
  phone TEXT NOT NULL,
  address TEXT NOT NULL,                    
  email TEXT NOT NULL,
  a_id INT NOT NULL,
  foreign key addr(a_id)
  references  address(a_id)
  on update CASCADE
  on delete RESTRICT
)

CREATE TABLE address(
  a_id INT NOT NULL auto_increment PRIMARY KEY,
  country TEXT NOT NULL,
  states  TEXT NOT NULL,
  city    TEXT NOT NULL,
  streetname TEXT NOT NULL,
  addr_num INT NOT NULL
)

CREATE TABLE order(
  ord_id INT NOT NULL auto_increment PRIMARY KEY,
  item_name TEXT NOT NULL,
  price DECIMAL,
  c_id INT NOT NULL,
  foreign key ord(c_id) references customer(c_id)
  on update CASCADE
  on delete RESTRICT
  a_id INT NOT NULL,
  foreign key addr(a_id) references address(a_id)
  on update CASCADE
  on delete RESTRICT
)

7 Use Sequel Pro sql client to connect your localhost Mysql server, download Sequel Pro Sequel Pro

  • Add password authentication to your my.cnf

    # /usr/local/etc/my.cnf
    default_authentication_plugin=mysql_native_password
    
  • After I spend over an hour on the installation Sequel Pro on my MacOS, it crashes when I click around the UI.

8 Java MySql Connection Manager

  • Create Connection Manager in Java
  • Connect to MySql inside my MacOS.
  • Database: test1
  • Table: snippet /Users/cat/myfile/bitbucket/database/snippet.sql
  • user: aron
  • passwd: 1234
  • Java Sql Example Intellij Project
  • /Users/cat/myfile/bitbucket/java/SqlExample
class DBManager{
    final static String user = "aron";
      final static String pass = "1234";
      final static String db = "test1";
      final static String timezone = "&serverTimezone=UTC";

      /**
       * Create connection with Mysql on MacOS. with JDBC driver under $b/javalib/jar/mysql-connector-java
       * https://mvnrepository.com/artifact/mysql/mysql-connector-java
       * @return
       */
      public static Connection connect(){
          Connection connect = null;
          try {
              Class.forName("com.mysql.cj.jdbc.Driver");
              connect = DriverManager.getConnection(
                  "jdbc:mysql://localhost:3306/" + db + "?useSSL=false" + timezone + "&requireSSL=false", user, pass
                  );
          }catch(Exception e){
              e.printStackTrace();
          }finally{
              p("Clean up");
          }
          return connect;
      }
  }

9 Design a payment table

  • payment table
pid INT autoincrement PRIMARY KEY
paymenttype INT NOT NULL
customerid INT NOT NULL
  • Payment type table

    • Paypal method table
    ppid INT autoincrement PRIMARY KEY
    ppemail VARCHAR(200) NOT NULL
    custid FOREIGN KEY (custid) REFERENCES (custid)
    createdate DATETIME NOT NULL
    • Credit Card table

Author: cat

Created: 2019-08-12 Mon 14:34

Validate