Create a relational database

First, the database and table which exists:

USE dbname;
select * from Authentication;
+----+---------+--------------------+---------------+----------------------+
| id | userid  | password           | role          | email                |
+----+---------+--------------------+---------------+----------------------+
|  7 | user1   | ljkjkjhjhjdhfZn0mz | administrator | [email protected]       |
| 10 | user2   | gdgdgdgdgdg^4YU    | user          | [email protected]       |
| 11 | user3   | dsdsdsdsdsd8       | user          | [email protected]       |
+----+---------+--------------------+---------------+----------------------+

Now create the other table and its fields:

CREATE TABLE `Login` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `TimeLoggedIn` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `id_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

Now duplicate the field id in Login and populate it with existing data from table Authentication.

INSERT INTO `Login` (`id`) SELECT `id` FROM `Authentication`;

Now create relation:

ALTER TABLE `Authentication` ADD FOREIGN KEY (`id`) REFERENCES `Login`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

sqlite3 basic reference

C:\sqlite>sqlite3 store.db
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.

Remove a table:

sqlite> DROP TABLE punchlog;

Create a table:

sqlite> CREATE TABLE punchlog(_id INTEGER PRIMARY KEY, date TEXT, punchin TEXT, punchout TEXT, onduty INTEGER, forgotin INTEGER, forgotout INTEGER, latein INTEGER, lateout INTEGER);

List tables:

sqlite> .tables
punchlog

Schema of a table:

sqlite> .schema punchlog
CREATE TABLE punchlog(_id INTEGER PRIMARY KEY, date TEXT, punchin TEXT, punchout TEXT, onduty INTEGER, forgotin INTEGER, forgotout INTEGER, latein INTEGER, lateout INTEGER);

More details on table:

sqlite> PRAGMA TABLE_INFO(punchlog);
0|_id|INTEGER|0||1
1|date|TEXT|0||0
2|punchin|TEXT|0||0
3|punchout|TEXT|0||0
4|onduty|INTEGER|0||0
5|forgotin|INTEGER|0||0
6|forgotout|INTEGER|0||0
7|latein|INTEGER|0||0
8|lateout|INTEGER|0||0
sqlite>

Read data:

SELECT <cols> FROM <table>

You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.

MySQL Basic commands

Start mysql with:

mysql -u user -pPASSWORD

To choose a database:

USE mysql;

where mysql is the db name

To display all tables in the chosen database

SHOW TABLES;

To display all columns in a table:

SHOW COLUMNS FROM `user`;

where user is the table name

Or a single step command:

SHOW COLUMNS FROM `user` FROM `databasename`;[/code]
To display data in a coloumn or field:
SELECT `gamma` FROM `user`;

Where user is the table name and gamma is the field or coloumn name

To display two coloumns or fields side by side, you do:

mysql&gt; SELECT `user`,`password` FROM `user`;

where user and password are two columns in the table user.


You are reading this post on Joel G Mathew’s tech blog. Joel's personal blog is the Eyrie, hosted here.