SQL DML Data Manipulation Language

SELECT
SELECT *
FROM someTable
ORDER BY pk_id;

VARIABLES 
SELECT id, machine, user
FROM db.tableA varA
JOIN db.tableB varB ON (varA.user = varB.user)
WHERE id=500
ORDER BY id
LIMIT 10;

/*! so db.tableA can be referenced by varA and db.tableB by varB */ 

INSERT INTO
INSERT INTO 〈table name〉( 〈column 1〉, 〈column 2〉,... 〈column n〉 )
VALUES ( 〈value 〉, 〈value 2〉, ..., 〈value n〉);
Note column names do not require quotation marks, however values do.

INSERT INTO 〈table name〉
VALUES
      ( 〈value 〉, 〈value 2〉, ..., 〈value n〉)
      ( 〈value 〉, 〈value 2〉, ..., 〈value n〉)
      ( 〈value 〉, 〈value 2〉, ..., 〈value n〉); 
Note: We can omit the column names so long as the list of values match the number of columns AND are in the correct order.

UPDATE WHERE
UPDATE 〈table_name〉
SET column1 = value1, column2 = value2, ...
WHERE 〈condition〉;

DELETE FROM
DELETE FROM 〈table_name〉 WHERE 〈condition〉;
DELETE FROM table_name
Note: the latter command removes ALL data from the table. 

If we wish to list all different values in a column and count the number of instances of that value.
SELECT "names", COUNT("names") AS numberOfTimes
FROM mydf
GROUP BY "names"
ORDER BY numberOfTimes;

   

SQL DDL Data Definition Language

SQL in Jupyter Notebooks specifically PostgreSQL

Linux users and groups

users

1) useradd user

Create a user. 

2) passwd user
Create/Change password for user.

To create a new user, gennerally run 1, then 2 above, alternatively, you can use adduser user

which is a higher level command. 

adduser user group

As above but also adds user to group.

passwd -l user

Lock user, preventing logon using password.

passwd -u user 

Unlock user. 

passwd -S user  

Get Status for user. 

groups

groups username 

Shows a list of groups the current user is a member of.

id shows the numerical id's.

groupadd newgroup

Create a new group 

addgroup newgroup
Create a new group  

usermod -a -G group username

usermod -a -G group1,group2,group3 username 

append group(s) to username. Add username to group(s)

usermod -g groupname username 

Modify user's primary group (g = primary group G = secondary group)

useradd -G group username

Create a new user and assign it to group

getent group

List all groups on system.

groupdel group

Remove group 

groupmod --new-name NEW_GROUP_NAME OLD_GROUP_NAME

rename a group.  

Linux user log

To view the recent logon activity for a user, use the following;

last username

email forwarding

Assuming everything else is set up, users domains and such;

Add the following entry to aliases file;

user: forward@somewhere.com

Then update aliases file and restart sendmail.

You can also add a .forward file to the user's home folder like so;

forward1@somewhere.com
forward2@somewhere.com

The former method is preferred.

Removing LVM

# df -hP | grep -i data01

/dev/mapper/datavg-testlv  976M  2.6M  907M   1% /data01

# lvs

  LV     VG     Attr       LSize  Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert

  root   centos -wi-ao---- 17.47g

  swap   centos -wi-ao----  2.00g

  testlv datavg -wi-ao----  1.00g

1. Delete the entry of the mount point from the /etc/fstab :

# cat /etc/fstab

...

/dev/mapper/datavg-testlv            /data01              ext4    defaults        0 0

...

2. Unmount the mount point :

# umount /data01

3. Disable lvm :

# lvchange -an /dev/datavg/testlv

4. Delete lvm volume :

# lvremove /dev/datavg/testlv

5. Disable volume group :

# vgchange -an datavg

6. Delete volume group :

# vgremove datavg

7. Delete physical Volumes being used for the volume group “datavg” :

# pvremove /dev/sdb  /dev/sdc

Slackbuilds

A note on Slackbuilds.

I noticed during some issues with fail2ban that as I had run my build script while anaconda2 was installed, this meant that the installation package when run would put binaries in the /opt/anaconds2/bin directory as opposed to /usr/bin

Even after removing anaconda2 directory and also entries from root and user .bashrc files, running the installation package, still put the fail2ban-client file in /opt/anaconds2/bin as opposed to /usr/bin

This I'm guessing was down to the fact that the python location was in my PATH when the build script was run and of course after this was removed, I rebuilt the installation package and installed and all was as expected.

I need to verify this as I thought you could run a build script and then use the installation package on a different machine, obviously with the same version of slackware but this may not be the case. Perhaps I was thinking about regular compiling into binaries?

rc.local_shutdown

#!/bin/sh

#

# /etc/rc.d/rc.local_shutdown:  Local system shutdown script.

#

# Put any local shutdown commands in here.

# by H Nisbet 02 Nov 2018

if [ -x /etc/rc.d/rc.tm351vm ]

then

        /etc/rc.d/rc.tm351vm suspend # attempt suspend first

#       /etc/rc.d/rc.tm351vm stop

fi

# Stop fail2ban:

if [ -x /etc/rc.d/rc.fail2ban ]; then

        echo "Stopping fail2ban: /etc/rc.d/rc.fail2ban stop"

        /etc/rc.d/rc.fail2ban stop

fi

# Stop Webminin:

if [ -x /etc/rc.d/rc.webmin ]; then

        echo "Stopping Webmin:   /etc/rc.d/rc.webmin stop"

        /etc/rc.d/rc.webmin stop

fi

rc.local

#!/bin/sh

#

# /etc/rc.d/rc.local:  Local system initialization script.

#

# Put any local startup commands in here.  Also, if you have

# anything that needs to be run at shutdown time you can

# make an /etc/rc.d/rc.local_shutdown script and put those

# commands in there.

if [ -x /etc/rc.d/rc.tm351vm ]

then

        /etc/rc.d/rc.tm351vm start

fi

# Start fail2ban

if [ -x /etc/rc.d/rc.fail2ban ]; then

        echo "Starting fail2ban:   /etc/rc.d/rc.fail2ban"

        /etc/rc.d/rc.fail2ban start

fi

# Log boot time...

/bin/date >> /var/log/boot.log