6626070
2997924

PL00, SQL

Back to the previous page ｜ page management ｜SQL tutorial ｜ SQL pad
List of posts to read before reading this article

Contents

PostgreSQL

Setup

Installation
postgresql

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
$ sudo service --status-all
$ sudo service postgresql start

Enter

/etc/postgresql/13/mainpg_hba.conf : password

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

image image /etc/postgresql/13/postgresql.conf : port image reset password

$ passwd -d postgres
$ passwd postgres

access db

$ sudo -i -u postgres
$ psql

check data-directory

postgres=# show data_directory;

create datebase

postgres=# CREATE DATABASE [dbname];

restore

$ pg_restore --dbname=[dbname] -U postgres --verbose [dbfile.tar]




MySQL basic

Setup

Installation
URL mysql

$ sudo apt update
$ sudo apt install mysql-server
$ sudo mysql_secure_installation   # you can skip




Enter
mysql

$ sudo mysql -u root




Export/Import : https://www.interserver.net/tips/kb/import-export-databases-mysql-command-line/

$ mysqldump -uUSERNAME -p DB_NAME > exported.sql
$ mysql -uUSERNAME -p DB_NAME < import_file.sql




DB structure and basic command

  • Table
  • Database, Shema
  • Database Server




mysql> CREATE DATABASE [];
mysql> DROP DATABASE [];
mysql> SHOW DATABASES;
mysql> SHOW TABLES;
mysql> DESC [];
mysql> USE [];

mysql> CREATE TABLE [table_name]();
mysql> INSERT INTO [table_name] () VALUES();
mysql> SELECT * FROM [table_name];
mysql> SELECT [col_1|...|col_N] FROM [table_name];




CRUD

CREATE

MySQL data-type ｜ Syntax｜ Example｜

mysql> CREATE TABLE [table_name](
    ->  id INT(11) NOT NULL AUTO_INCREMENT,
    ->  title VARCHAR(100) NOT NULL,
    ->  description TEXT NULL,
    ->  created DATETIME NOT NULL,
    ->  author VARCHAR(30) NULL,
    ->  profile VARCHAR(100) NULL,
    ->  PRIMARY KEY(id));




INSERT

MySQL data-type｜ Syntax｜ Example｜




SELECT

MySQL data-type｜ Syntax｜ Example｜




UPDATE

MySQL data-type｜ Syntax｜ Example｜




DELETE

MySQL data-type｜ Syntax｜ Example｜




Relational database

Join




Workbench

Download

Internet & Database




List of posts followed by this article

Reference

OUTPUT