Note, in June 2021 we migrated from MySQL to mariadb. However every instance of the use of MySQL (in any case mix) can be used in mariadb. You will likely see a mix of MySQL and mariadb, and should be considered interchangeable.

Be sure to pay attention to the prompt in these examples. Sometimes it is the Linux Bash prompt, and other times it is the mysql prompt. Also note that you will often be prompted for the Linux root password (due to sudo) followed by the mysql root user account password.

Remember that everything between /* and */ is considered a comment and can span any number of lines.

Also remeber that all SQL commands must end with a simicolon (;).

The material within the fieldsets is all in SQL format.

The actual MySQL files are located in /var/lib/mysql/

Good MySQL information: https://www.a2hosting.com/kb/developer-corner/mysql

Connecting the MySQL using Perl: https://www.a2hosting.com/kb/developer-corner/mysql/connect-to-mysql-using-perl

systemctl {start|stop|restart|status} mysqld


/* Login. You will first be prompted for the Linux root password,
and then for the mysql root account password */
$ sudo mysql -u root -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE mysql;
Database changed

/* Exit the MySQL interrupter */
mysql> QUIT;

/* Login and provide an SQL script to be run and capture
    output (from Bash command line) */
$ mysql -u username -p db_name < mysqlscript.txt > output.txt

/* Create a database on the sql server */
mysql> create database [databasename];

/* List all databases on the sql server */
mysql> show databases;

/* Switch to a database */
mysql> use [db name];

/* To see all the tables in the db */
mysql> show tables;

/* To see database's field formats */
mysql> describe [table name];

/* To delete a db */
mysql> drop database [database name];

/* To delete a table */
mysql> drop table [table name];

/* Show all data in a table */
mysql> SELECT * FROM [table name];

/* Returns the columns and column information pertaining to the designated table */
mysql> show columns from [table name];

/* Show certain selected rows with the value "whatever" */
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

/* Show unique records */
mysql> SELECT DISTINCT [column name] FROM [table name];

/* Show selected records sorted in an ascending (asc) or descending (desc) */
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

/* Count rows */
mysql> SELECT COUNT(*) FROM [table name];

/* Switch to the mysql db. Create a new user */
mysql> INSERT INTO [table name] (Host,User,Password)
        VALUES('%','user',PASSWORD('password'));

/* To update info already in a table */
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',
        Update_priv = 'Y' where [field name] = 'user';

/* Delete a row(s) from a table */
mysql> DELETE from [table name] where [field name] = 'whatever';

/* Delete a column */
mysql> alter table [table name] drop column [column name];

/* Add a new column to db */
mysql> alter table [table name] add column [new column name] varchar (20);

/* Change column name */
mysql> alter table [table name] change [old column name]
        [new column name] varchar (50);

/* Make a unique column so you get no dupes */
mysql> alter table [table name] add unique ([column name]);

/* Make a column bigger */
mysql> alter table [table name] modify [column name] VARCHAR(3);

/* Load a CSV file into a table */
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name]
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

/* Dump all databases for backup. Backup file is sql commands to
   recreate all db's */
mysql> [mysql dir] />
    <META HTTP-EQUIV="Pragma" CONTENT="no-cache" />/bin/mysqldump
    -u root -ppassword --opt >/tmp/alldatabases.sql

/* Dump just the inventory database */
sudo mysqldump -u root -p --databases inventory >~/bkup/mysql/inventoryDB.sql

/* Dump one database for backup */
mysql> [mysql dir]/bin/mysqldump -u username -ppassword
    --databases databasename >/tmp/databasename.sql

/* Change a user password */

SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark;
or
ALTER USER dbadmin@localhost IDENTIFIED BY 'littlewhale';


/* In the following example of exporting a table (or select) to 
   a csv formatted file, make sure to change the datetime portion
   of the output filename, otherwise this command will refuse
   to output the file
*/
/* Export the entire Inventory table to CSV format */
/* Note that you can export only into the '/var/lib/mysql-files/' directory */
select id, ifnull(ip,''),ifnull(mac,''),ifnull(name,''),
ifnull(location,''),ifnull(switch,''),ifnull(ports,''),
ifnull(type,''),ifnull(sn,''),ifnull(os,''),ifnull(firmware,''),
ifnull(dante,''),ifnull(ssid,''),ifnull(channel,''),ifnull(notes,''),
ifnull(media,''),ifnull(lastipseen,''),
ifnull(redflag,''),ifnull(user,''),ifnull(lastseen,'') from inventory into 
outfile '/var/lib/mysql-files/inventory.csv.export.063021.1220.csv'
fields enclosed by '"' terminated by ';' escaped by '"'
lines terminated by '\r\n';



/* This a an actual production example */
/* ben@benb:~/bkup/mysql$ sudo mysqldump -u root -p --databases
    inventory > inventoryDB.10.05.2020-13:21:49.sql */


/* Dump a table from a database */
mysql> [mysql dir]/bin/mysqldump -c -u username -ppassword
    databasename tablename > /tmp/databasename.tablename.sql

/* Restore database (or database table) from backup */
mysql> [mysql dir]/bin/mysql -u username -ppassword
    databasename < /tmp/databasename.sql

/* Copy values from one column into another. */
mysql> update inventory set band=channel;


Create Table Example 1.
    CREATE TABLE [table name]
        (firstname VARCHAR(20),
        middleinitial VARCHAR(3),
        lastname VARCHAR(35),
        suffix VARCHAR(3),
        officeid VARCHAR(10),
        userid VARCHAR(15),
        username VARCHAR(8),
        email VARCHAR(35),
        phone VARCHAR(25),
        groups VARCHAR(15),
        datestamp DATE,
        timestamp time,
        pgpemail VARCHAR(255));

    Create Table Example 2.
        create table [table name]
        (personid int(50) not null auto_increment primary key,
        firstname varchar(35),
        middlename varchar(50),
        lastname varchar(50) default 'bato');

References:
http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm
https://www.computerhope.com/unix/mysql.htm

mysql> create database inventory;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use inventory;
Database changed

mysql> create table inventory
(
    id int not null auto_increment primary key,
    ip varchar(15),
    mac varchar(18),
    name varchar(96),
    location varchar(124),
    type varchar(96),
    sn varchar(124),
    os varchar(124),
    firmware varchar(96),
    ssid varchar(96),
    channel varchar(4),
    frequency varchar(10),
    band varchar(8),
    encryption varchar(24),
    notes varchar(1024)
);

mysql> describe inventory;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| ip         | varchar(15)   | YES  |     | NULL    |                |
| mac        | varchar(18)   | YES  |     | NULL    |                |
| name       | varchar(96)   | YES  |     | NULL    |                |
| location   | varchar(124)  | YES  |     | NULL    |                |
| type       | varchar(96)   | YES  |     | NULL    |                |
| sn         | varchar(124)  | YES  |     | NULL    |                |
| os         | varchar(124)  | YES  |     | NULL    |                |
| firmware   | varchar(96)   | YES  |     | NULL    |                |
| ssid       | varchar(96)   | YES  |     | NULL    |                |
| channel    | varchar(4)    | YES  |     | NULL    |                |
| frequency  | varchar(10)   | YES  |     | NULL    |                |
| band       | varchar(8)    | YES  |     | NULL    |                |
| encryption | varchar(24)   | YES  |     | NULL    |                |
| notes      | varchar(1024) | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)

/* Load a CSV file into a table. Save the sheet as csv, remove the title line, and save */


# Identify duplicate macs
SELECT mac, COUNT(mac) c FROM inventory GROUP BY mac HAVING c > 1;


# Identify duplicate ips
SELECT ip, COUNT(*) c FROM inventory GROUP BY ip HAVING c > 1;


mysql> LOAD DATA LOCAL
    INFILE '~/CCCC/Tech/database/CCCC.Network.Inventory.csv'
    replace INTO TABLE inventory FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n' (ip, mac, name, location,
    type, sn, os, firmware, ssid, channel, frequency,
    band, encryption, notes);

/* If both of the following commands give the same total, there are no
    duplicate mac addresses in the table. Note that there are duplicate
    IP addresses in the table, and there should be. */

mysql> select mac from inventory where mac != '';

mysql> select distinct mac from inventory;

mysql> select ip,os from inventory where os like '%win%7%';
+--------------+-----------------------------------------------------+
| ip           | os                                                  |
+--------------+-----------------------------------------------------+
| 10.32.10.4   | Windows Server 2008 R2 Standard 7601 Service Pack 1 |
| 10.32.10.137 | Win 7 Home Premium Service Pack 1                   |
| 10.32.10.170 | Win 7 Pro                                           |
| 10.32.10.175 | Win 7                                               |
| 10.32.10.214 | Win 7 Pro                                           |
| 10.32.10.235 | Win 7 Pro                                           |
+--------------+-----------------------------------------------------+
6 rows in set (0.00 sec)

/* Dump the schema of a database. In the following example you can
append ' > filename.sql' (no quotes) to capture the outpout to
a file */
$ sudo mysqldump -d -u root -p inventory

-- MySQL dump 10.13  Distrib 5.7.30, for Linux (x86_64)
--
-- Host: localhost    Database: inventory
-- ------------------------------------------------------
-- Server version   5.7.30-0ubuntu0.18.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table 'inventory'
--

DROP TABLE IF EXISTS 'inventory';
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE 'inventory' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'ip' varchar(15) DEFAULT NULL,
  'mac' varchar(18) DEFAULT NULL,
  'name' varchar(96) DEFAULT NULL,
  'location' varchar(124) DEFAULT NULL,
  'type' varchar(96) DEFAULT NULL,
  'sn' varchar(124) DEFAULT NULL,
  'os' varchar(124) DEFAULT NULL,
  'firmware' varchar(96) DEFAULT NULL,
  'ssid' varchar(96) DEFAULT NULL,
  'channel' varchar(4) DEFAULT NULL,
  'frequency' varchar(10) DEFAULT NULL,
  'band' varchar(8) DEFAULT NULL,
  'encryption' varchar(24) DEFAULT NULL,
  'notes' varchar(1024) DEFAULT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-05-13 13:28:10

/* Ref for backup and restores: https://linuxize.com/post/
how-to-back-up-and-restore-mysql-databases-with-mysqldump/ */

/* Backup a database */
$ sudo mysqldump -u root -p inventory
    > mysql-inventory-$(date +%F)-backup.sql

/* Restore a database */
$ mysql inventory < mysql-inventory-backup.sql


GROUP BY
select mac,count(*) as Total from inventory group by mac order by Total desc;

/* Create a MySQL user account */
mysql> CREATE USER 'parth'@'localhost' IDENTIFIED BY 'MyPassword';

/* List all MySQL user accounts */
mysql> SELECT User,Host FROM mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

/* Delete a MySQL user account */
mysql> drop user parth@localhost;

/* Here is a short list of commonly used permissions :
   ALL - Allow complete access to a specific database.
   If a database is not specified, then allow complete access to the
   entirety of MySQL.
   CREATE - Allow a user to create databases and tables.
   DELETE - Allow a user to delete rows from a table.
   DROP - Allow a user to drop databases and tables.
   EXECUTE - Allow a user to execute stored routines.
   GRANT OPTION - Allow a user to grant or remove another user's privileges.
   INSERT - Allow a user to insert rows from a table.
   SELECT - Allow a user to select data from a database.
   SHOW DATABASES- Allow a user to view a list of all databases.
   UPDATE - Allow a user to update rows in a table.
*/

/* Grant ALL permissions to a user for a database */
mysql> GRANT ALL PRIVILEGES ON inventory.* TO 'username'@'localhost';

/* After changing permissions, save them */
mysql> FLUSH PRIVILEGES;

/* Display permissions for a user */
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

/* Show privileges for all users */
mysql> SELECT User, Host, Super_priv, Create_user_priv,
    Grant_priv from mysql.user WHERE Create_user_priv = 'Y'
    AND Grant_Priv = 'Y';
+------------------+-----------+------------+------------------+------------+
| User             | Host      | Super_priv | Create_user_priv | Grant_priv |
+------------------+-----------+------------+------------------+------------+
| root             | localhost | Y          | Y                | Y          |
| debian-sys-maint | localhost | Y          | Y                | Y          |
+------------------+-----------+------------+------------------+------------+
2 rows in set (0.00 sec)

/* Select statements */

/* Ben, add lots of select examples here */

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

mysqlcheck uses the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE in a convenient way for the user.

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are checked.

Ref. https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html

To delete duplicate rows based on multipul columns;

ALTER IGNORE TABLE your_table ADD UNIQUE (field1,field2,field3);

You can also try this:

ALTER IGNORE TABLE users
    ADD UNIQUE(email, alt_email, mobile and alt_mobile);

This will go through the table, copy it over, ignoring any dups. I am, however, hesitant to say that it will properly handle NULLs in those columns.

Simple insert a new row

INSERT INTO person (first_name, last_name) VALUES ('John', 'Doe');

Comparing two tables

Compare the mac columns from two tables, inventory and inventory2:

select id,ip,mac from inventory
       where mac in
       (select mac from inventory2);

In the above query, we select records from inventory whose mac column value is present in the list of mac column values obtained from inventory2 using a subquery.

Similarly, if you want to compare two columns and select records that do not match then update the query above by adding a NOT keyword before IN, as shown below.

select id,ip,mac from inventory
            where mac NOT in
            (select mac from inventory2);

select id,ip,mac from inventory2
            where mac NOT in
            (select mac from inventory);

Now check the ip values:

select id,ip,mac from inventory
            where ip NOT in
            (select ip from inventory2);

select id,ip,mac from inventory2
            where ip NOT in
            (select ip from inventory);

The following are some perl notes. These need to be moved to their own page.

Install cpanm to make installing other modules easier (you'll thank us later).
You need to type these commands into a Terminal emulator (Mac OS X, Win32, Linux)

sudo cpan App::cpanminus

Now install any module you can find.

sudo cpanm Module::Name

$ sudo cpan

install Net::CGI
install Net::DBI

or

$ sudo cpan Net::DNS

Cpanminus or cpanm is a cpan client to get, unpack, build and install
modules from CPAN repository. It is a standalone, dependency-free script
that requires zero-configuration. Many experienced Perl developers prefer
cpanm over cpan.

The Contacts Database

The following was used to build the contacts database and table.

mysql> create database contacts;

mysql> use inventory;

mysql> CREATE TABLE contacts (
    id int not null auto_increment primary key,
    fname varchar(32),
    mi varchar(12),
    lname varchar(32),
    suffix varchar(12),
    email varchar(64),
    email2 varchar(64),
    textnum varchar(32),
    phone varchar(32),
    phone2 varchar(32),
    street varchar(64),
    city varchar(64),
    state varchar(25),
    zip varchar(16),
    groups varchar(1024),
    comments varchar(2048),
    datestamp date,
    timestamp time
);