Web REST API -GraphQL API Web GraphQL GraphQL GraphQL API API API

GraphQL

GraphQL API GraphQL Facebook 2012 2015 REST API

GraphQL GraphQL REST GraphQL

GraphQL GraphGraphQL API

GraphQL

GraphQL REST API RESTGraphQL

API REST GraphQL

GraphQL API

GraphQL

GraphQL API

GraphQL GraphQL API

GraphQL

Navicat GraphQL

GraphQL API GraphQL Navicat

Navicat GraphQL GraphQL PostgreSQLMySQLMongoDB Redis GraphQL Navicat GraphQL

GraphQL GraphQL API REST Navicat GraphQL

GraphQL API GraphQL API Navicat GraphQL API GraphQL

SQL

1000 SQL NoSQL Navicat

NoSQL

NoSQL

NoSQL NoSQL MongoDB JSON Neo4j

SQL NoSQL NoSQL MongoDB NoSQL

Navicat

Navicat MySQL PostgreSQL MongoDB NoSQL

Navicat

2024 10 28 Navicat On-Prem Server MySQL MariaDB Navicat - Navicat CloudNavicat Cloud Navicat Navicat On-Prem Server Navicat

Navicat Navicat On-Prem Server 3.0

• PostgreSQL Fujitsu Enterprise Postgres
• SQL

Navicat On-Prem Server 3.0 MySQLMariaDB PostgreSQL

3.0

Navicat On-Prem Server 3.0

• Navicat On-Prem Server
• Navicat On-Prem Server
• 3.0

  

PostgreSQL Fujitsu Enterprise Postgres

+ PostgreSQLFujitsuHighGo

Navicat

• SQL SQL

SQL

Navicat On-Prem Server 3.0 BI Navicat On-Prem Server 3.0 MySQLMariaDBPostgreSQL Fujitsu Enterprise PostgresFEP

Navicat On-Prem Server 3.0 WindowsmacOS Linux 14

PostgreSQL MySQL Redis

RAM

PostgreSQLMySQL Oracle

PostgreSQL ACID MySQL Oracle

RedisMemcached Apache Ignite Redis

Redis RDB AOF Redis

Navicat

Navicat

Navicat Redis Navicat

CPU

Datadog's Database Monitoring

SolarWinds Database Performance Analyzer

Percona Monitoring and Management MySQLPostgreSQL MongoDB

Oracle's Autonomous Database

Microsoft SQL Server Query Store SQL Server Azure Monitor

PostgreSQL pg_stat_statements pg_stat_activity

Navicat Monitor:

Navicat Monitor MySQLMariaDBPostgreSQLSQL Server

Navicat Monitor

GDPR

CryptDB SQL

Opaque enclaves

PrivateSQL

Microsoft's SEAL

PostgreSQL Anonymizer

Oracle Database

Microsoft SQL Server Always Encrypted

Amazon's Aurora

Navicat

Navicat

Navicat

Navicat

ID

• Amazon Web Services Amazon RDS Amazon Aurora DynamoDB
• Google Cloud Cloud Spanner Cloud Spanner
• MongoDB Atlas
• Microsoft Azure Cosmos DB

Navicat

Navicat

Navicat

Navicat SQL Navicat

Navicat

Navicat

Navicat 17.3 Navicat 17.3 PremiumSoft Navicat

17.3 Navicat Fujitsu Enterprise Postgres PostgreSQL Azure Cosmos DB for MongoDB

Navicat IvorySQL PostgreSQL PostgreSQL

Navicat

AI

Navicat 17.3 AI ChatGPTDeepseekGoogle GeminiOllama GrokClaude Navicat

AI AI AI AI SQL

AI

AI SQL SQL

Navicat 17.3

Navicat 17.3

Navicat 17.3 Navicat 17 Navicat 17.3

1%

Navicat

Navicat SQL

RSAECC Diffie-Hellman Shor

IBMGoogle Y2Q

PQC

NIST CRYSTALS-KyberCRYSTALS-DilithiumSPHINCS+

IBM DB2 CRYSTALS-Kyber CRYSTALS-Dilithium Oracle

Microsoft SQL Server NIST PostgreSQL Amazon RDS Aurora AWS Google Cloud SQL TLS Azure SQL Microsoft

CockroachDB MongoDB Atlas Apple FoundationDB TLS

NavicatQuantum

Navicat

Navicat

-

Navicat

• BigchainDB
• Hyperledger Fabric, Linux
• Amazon QLDB (Quantum Ledger Database)
• FlureeDB
• Blockstore key-value

• PostgreSQL pg_crypto
• MongoDB
• Oracle Blockchain Tables Oracle SQL
• Microsoft SQL Server Ledger
• Immudb

Navicat

Navicat Navicat

Navicat Navicat

Navicat

AIML AI/ML AI/ML

AI/ML

AI/ML

SQL

AI/ML

Microsoft SQL Server R Python Oracle's Autonomous Database PostgreSQL MADlib SQL

Amazon Redshift ML SQL Google BigQuery ML SQL Snowflake Snowpark

AI

AI Navicat AI Navicat AI 17.2 Navicat AI SQL AI Navicat AI ChatGPTGoogle GeminiDeepSeek Ollama

AI/ML AI/ML

AI/ML

Navicat

• Datomic datoms
• LMDB
• InfluxDB
• EventStoreDB
• BigchainDBAmazon Quantum Ledger Database QLDBHyperledger Fabric

• PostgreSQL pg_audit
• Microsoft SQL Server SQL Server 2016
• Oracle Flashback Query
• MongoDB Change Streams
• Amazon DynamoDB Point-in-Time Recovery, PITR35

Navicat

Navicat PostgreSQLMySQLMariaDBSQL ServerOracle MongoDB

Navicat Navicat

Navicat

--NoSQL SaaS

ETL

-- --

Navicat Navicat Navicat MySQLPostgreSQLSQL ServerOracle MariaDBNavicat

----

Git

Navicat

• Navicat
• Navicat SQL

Edge

Edge Databases

Edge Databases

Edge Database

Edge Databases

Edge Databases

Edge

• SQLite SQLite 600KB SQL
• CouchDB PouchDB
• RxDB
• Firebase Realtime Database
• Berkeley DB

Edge Databases

MySQLPostgreSQL SQL Server

Amazon DynamoDBGoogle Cloud Spanner Azure Cosmos DB

Edge Databases

Edge Databases

Navicat

Edge

SQL Navicat

/ IT

/

Navicat

Navicat Navicat SQL Visual Query Builder SQL

Navicat SQL Navicat SQL

Navicat SQL SQL

/

/Navicat

Data Vault 2.0 Data Vault 2.0

-Dan LinstedtKimballInmonData Vault 1.0 Data Vault 2.0 2013

Data Vault 2.0

Data Vault 2.0

• --

Data Vault 2.0

Data Vault 2.0

Data Vault 2.0 30-40%Data Vault 2.0

Data Vault 2.0

Navicat Data Modeler Data Vault 2.0

Navicat Data Modeler Data Vault 2.0 Data Vault 2.0 Navicat Data Modeler

Data Vault 2.0 Data Vault 2.0

RDBMS

Apache Kafka Kafka LinkedIn UberNetflix Airbnb Kafka Apache Flink

• PostgreSQL pg_stream
• MongoDB API oplog
• Oracle Oracle Stream Analytics
• Microsoft SQL Server Azure Stream Analytics

Navicat

DBaaS DBaaS

DBaaSAWS Aurora Azure SQL MongoDB Atlas

DBaaS Oracle Autonomous Database Microsoft Azure SQL Database

CockroachDBMongoDB Atlas DataStax Astra

DBaaS InfluxDB Cloud TimescaleDB Neo4j Aura Amazon Neptune Pinecone Weaviate

Navicat Amazon RDSAzure SQL Database Google Cloud SQL AWSAzureGCP

DBaaS DBaaS

TSDB

RDBMS

TSDB

TimescaleDB PostgreSQL PostgreSQL TimescaleDB RDBMSSQL

Microsoft Oracle Microsoft SQL Server Oracle Database RDBMS

TSDB MySQL Oracle RDBMSInfluxDBPrometheus Graphite TSDB ETL API

Amazon TimestreamAzure Data Explorer Google Cloud BigQuery SQL

Navicat

Navicat Navicat MySQLPostgreSQL SQL Server RDBMS SQL Navicat

Navicat

PostgreSQL

PostgreSQL

Oracle

Oracle TDEOracle

Microsoft SQL Server

SQL Server Always EncryptedDynamic Data MaskingRow-Level Security

MongoDB

MongoDB

Navicat Navicat

Navicat

ETL ETL ETL

ETL

ETL --

ETL Snowflake ETL Google BigQuery Redshift Aurora ETL

ETL

• PostgreSQL ETL Snowflake Amazon Redshift
• MySQL ETL Aurora MySQL Redshift ETL MySQL
• Atlas MongoDB ETL MongoDB
• Redis ETL ETL

ETL ETL ETL

ETL

Navicat ETL

ETL Navicat

OLTPOLAP/HTAP HTAP HTAP HTAP

HTAP

HTAP ETLHTAP

HTAP

SAP HANA MemSQL HTAP HTAP MongoDB HTAP MongoDB

PostgreSQL HTAP FDWPostgreSQL TimescaleDB PostgreSQL Citus PostgreSQL

MySQL NDB HTAPInnoDB MySQL HTAP

HTAP

HTAP Navicat Navicat HTAP

HTAP HTAP

HTAP HTAP

20248Navicat17.1Enhanced Query ExplainExpanded Database Connectivity17.2 Beta

• AI
  Navicat AI
• Snowflake
  Navicat Snowflake
• 
  
• BI
  

AI

AI Navicat AI AI Navicat SQL ChatGPTGoogle GeminiDeepSeek Ollama AI

AI AI

Snowflake

Snowflake AWSAzure Google Cloud

Navicat snowflake

BI

Navicat 17 BI

Navicat 17.2

• &
• MongoDB

Navicat 17.2 Navicat

Apache Parquet ACID

Delta LakeApache Hudi Apache Iceberg

Apache Spark TrinoSQLDatabricks SQL Snowflake

PostgreSQL ACID JSON

MongoDB

Redis

Navicat Navicat PostgreSQLMongoDBRedis

Navicat

PostgreSQL MongoDB

z

• PostgreSQL ACID PostgreSQL
• MongoDB JSON
• Redis Redis pub/sub
• Elasticsearch

Navicat

Navicat

--

SQL AI/ML

MongoDB PostgreSQL

PostgreSQL JSON

Navicat

Navicat MongoDB PostgreSQL

Navicat

2023 Stack Overflow PostgreSQL MySQL PostgreSQL

MySQL PlanetScale PostgreSQL Neon Navicat Navicat

PlanetScale MySQL

PlanetScale Vitess MySQL Vitess YouTube Git PlanetScale

NeonPostgreSQL n

Neon PostgreSQL Neon PostgreSQL

• PostgreSQL

PlanetScale Neon MySQL PlanetScale Git

Neon PostgreSQL -

PlanetScale Neon MySQL PostgreSQL

Stack Overflow PostgreSQL Navicat PlanetScale Neon Navicat

PostgreSQL PostgreSQL PostgreSQL MySQL Navicat Premium 17 PostgreSQL

PostgreSQL 10 20

PostgreSQL

Navicat Premium 17 DVD

PostgreSQL

• int4range
• int8rangebigint
• numrange
• tsrange:
• tstzrange
• daterange

DVD Rental

-- Creating a custom minutes range type with specific validationCREATE TYPE runtime_range AS RANGE (    subtype = integer,    subtype_diff = int4mi);CREATE TABLE film_runtime_categories (   category_name VARCHAR(50),   typical_runtime runtime_range,   CHECK (lower(typical_runtime) >l= 30 AND upper(typical_runtime) <= 240));-- Adding rows to the tableINSERT INTO film_runtime_categories VALUES    ('Short Film', '[30,45]');INSERT INTO film_runtime_categories VALUES    ('Feature Film', '[75,180]');

Navicat 17

Navicat Navicat Premium 17 Navicat for PostgreSQL 17

SQL int4

SQL Navicat

runtime_range

PostgreSQL runtime_rangefilm_runtime_categoriestypical_runtime

Navicat Premium 17 5

Navicat Premium 17 PostgreSQL 1 DVD

ENUMs

PostgreSQL CREATE TYPE

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending');

CREATE TYPE movie_rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');

CREATE TABLE films (  film_id SERIAL PRIMARY KEY,  title VARCHAR(255),  rating movie_rating);

Navicat 17

Navicat Navicat Premium 17 Navicat for PostgreSQL 17

G

SQL Navicat

film_rating

film_rating

PostgreSQL film_rating VARCHAR ENUM

Navicat filmTyperatingfilm_rating, film_rating

ENUM Navicat

Navicat Premium 17

Navicat Premium 17 PostgreSQL 1 DVD Rental database NOT NULL CHECK

PostgreSQL

CREATE TYPE

PostgreSQL CREATE TYPE

• intvarchar numeric PostgreSQL

CREATE TYPE

CREATE TYPE IDfilm_summary

CREATE TYPE film_summary AS (    film_id INT4,    title VARCHAR(255),    release_year CHAR(4));

Navicat 17

Navicat Premium 17 Navicat for PostgreSQL 17

film_summaryfilm :

SQL Navicat

film_summary

film_summary

film_summary Navicat

CREATE FUNCTION CREATE FUNCTION

CREATE FUNCTION get_film_summary (f_id INT4)  RETURNS film_summaryAS $BODY$  SELECT     film_id,    title,    release_year  FROM    film  WHERE    film_id = f_id;$BODY$  LANGUAGE SQL VOLATILE;

SQL

f_id

Navicat Premium 17 3

PostgreSQLPostgreSQLCREATE DOMAINCREATE TYPE Navicat Premium 17 DVD Rental database

CREATE DOMAIN CREATE TYPE

CREATE DOMAIN CREATE TYPE

• CREATE DOMAIN NOT NULLCHECK
• CREATE TYPE

NULL

CREATE DOMAIN email AS VARCHAR(255)CHECK (  VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');-- Use in a table definitionCREATE TABLE customer_contacts (  customer_id INT,  contact_email email);

Navicat Premium 17 Navicat for PostgreSQL 17 (

SQL

VARCHARBase Typepg_catalogvarchar VARCHAR 255

RegEx

SQL

CREATE DOMAIN SQL

dvdrentalstaff VARCHAR(Domain)publicemail

dvdrental 2 Navicat Premium 17

Navicat / CSVTXTXMLDBF Navicat Premium Lite 17 Navicat PostgreSQL "dvdrental" database MySQL 8

PostgreSQL DAT MySQL 8 film

Navicat macOS Ctrl-Click...

TXTCSVXML JSON .dat .txt.csv .dat

DAT 3061.dat

LFTAB"

DAT //"YMD" (-) (/) YYYY-MM-DD hh:mm:ss.ms2013-05-26 14:50:58.951

DAT last_update special_features

5 African Egg A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico 2006 1 6 2.99 130 22.99 G 2013-05-26 14:50:58.951 {"Deleted Scenes"} 'african':1 'chef':11 'dentist':14 'documentari':7 'egg':2 'fast':5 'fast-pac':4 'forens':19 'gulf':23 'mexico':25 'must':16 'pace':6 'pastri':10 'psychologist':20 'pursu':17

macOS Ctrl-Click last_update special_features

13 (f13)

Navicat INSERT

INSERT INTO `film` VALUES   (1, 'African Egg', 'A Fast-Paced...'),   (2, 'Rumble Royale', 'A historical drama...'),   (3, 'Catherine the Great', 'A new take on...'),   etc...

3 1003 1000

Navicat CSVTXTXMLDBFODBC

Navicat Premium Lite 17 WindowsmacOS Linux

Navicat Navicat On-Prem ServerBI Navicat On-Prem Server Navicat Premium 17

Navicat Premium 17 Navicat On-Prem

Navicat On-Prem Server Navicat On-Prem Server Navicat

On-Prem Server Navicat On-Prem Server IP

On-Prem ServerOn-Prem Server

DVDRental MySQL DB Navicat Premium customer - payment table join

On-Prem Server

On-Prem Server

(BI)

On-Prem Server

Navicat On-Prem Server BI

Navicat Premium 17 Navicat On-Prem Server Navicat 17 BIOn-Prem Server

Navicat Premium 17 14 On-Prem Server WindowsmacOS Linux

PostgreSQL SUMAVG COUNT SQL Navicat for PostgreSQL Navicat Premium GUINavicat pgSQL DVD Rental database

SQL SUMAVGCOUNTMIN MAX

SQL SQL

array_append_state()

Navicat

Navicat

CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text)  RETURNS text[] AS $BODY$BEGIN  RETURN array_append(current_state, new_value);END$BODY$  LANGUAGE 'plpgsql' VOLATILE;

array_to_comma_string()

CREATE FUNCTION "public"."array_to_comma_string" (state text[])  RETURNS text AS $BODY$BEGIN  RETURN array_append(state, ', ');END$BODY$  LANGUAGE 'plpgsql' VOLATILE;

comma_concat()

Navicat

text[]{}

SQL

CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")(  SFUNC = "public"."array_append_state",  STYPE = "pg_catalog"."text[]",  FINALFUNC = "public"."array_to_comma_string",  INITCOND = "{}",  PARALLEL = UNSAFE);ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";

Navicat

SELECT     c.name AS category,    comma_concat(f.title) AS moviesFROM category cJOIN film_category fc ON c.category_id = fc.category_idJOIN film f ON fc.film_id = f.film_idGROUP BY c.nameORDER BY c.name;

Navicat Premium PostgreSQL DVD

Navicat Premium 17 14 WindowsmacOS Linux

Navicat Navicat On-Prem Server BI On-Prem Server

dvdrental MySQL 8DVDRrental MySQL DB

On-Prem Server

• ...

s

99

DVDRrental MySQL DB

DVDRrental MySQL DB 0.99

-> -> Navicat On-Prem Server LDAP AD

Navicat On-Prem Server

Navicat On-Prem Server 14 WindowsmacOS Homebrew Linux

Navicat Navicat On-Prem Server Navicat Navicat On-Prem Server

On-Prem Server +

BI...

+

/

(BI)

+

""

Navicat On-Prem Server Navicat

Navicat Navicat Premium Lite SQL SQL Navicat-only Syntax MySQL Sakila Navicat Premium 17

SQL SQL SQL Navicat ()

Navicat-only Syntax

nn 1 2

Customize Result Tab Name Navicat

tab_nameStatement SQL

Navicat SELECT

Runtime Parameter

Navicat

Navicat -only Syntax Navicat Premium 17 Navicat Premium 17 14 WindowsmacOS Linux

Navicat On-Prem Server 100% Navicat On-Prem Server MySQL MariaDB

Windows 10 MySQL 8 Community Server Navicat On-Prem Server

1 Navicat On-Prem Server Navicat On-Prem Server http://: Navicat On-Prem Server 3030 URL "http://127.0.0.1:3030/"

Web URL IP IP IP Navicat On-Prem Server 0.0.0.0 IPv4 :: IPv4 IPv6

Navicat On-Prem Server /

ClickatellTwilio

SMTP

Navicat On-Prem Server

Windows 10 MySQL 8 Community Server Navicat On-Prem Server

Navicat On-Prem Server 14 WindowsmacOS Homebrew Linux

On-Prem Server

Navicat On-Prem Server Navicat Navicat On-Prem Sever Navicat Cloud Navicat Cloud Navicat Navicat On-Prem Server Linux 2 Docker Navicat On-Prem Server Windows 10 MySQL 8 Community Server Navicat On-Prem Server

Navicat On-Prem Server

Navicat On-Prem Server

Navicat On-Prem Server

Navicat On-Prem Server Navicat On-Prem Server http://<your_ip_address>:<port_number> Navicat On-Prem Server 3030 URL http://127.0.0.1:3030/

On-Prem Server Navicat On-Prem Server Navicat On-Prem Server

• 1
• On-Prem Server

Admin Navicat On-Prem Server

On-Prem Server

On-Prem Server On-Prem Server

Navicat

• MySQL
• MariaDB
• PostgreSQL
• SQL Server
• Amazon RDS

SSL TLS_RSA_WITH_AES_128_GCM_SHA256, TLS_RSA_WITH_AES_256_GCM_SHA384, TLS_AES_128_GCM_SHA256, TLS_AES_256_GCM_SHA384, TLS_CHACHA20_POLY1305_SHA256

Navicat On-Prem Server 1 Windows 10 MySQL 8 Community Server On-Prem Server

Navicat On-Prem Server 14 WindowsmacOS Homebrew Linux

Navicat BI Navicat Navicat Cloud Navicat On-Prem ServerNavicat Cloud Navicat Navicat On-Prem Server Navicat Navicat On-Prem Server MySQL MariaDB

Navicat On-Prem Server

Navicat On-Prem Server Navicat On-Prem Server Navicat

Navicat On-Prem Server MySQL MariaDB

Navicat On-Prem ServerBI

MySQL MariaDB

Navicat On-Prem Server

• SQL

MySQL MariaDB

Navicat On-Prem Server

Navicat On-Prem Server Navicat SQL

Navicat On-Prem Server MySQL MariaDB Navicat On-Prem Server SMS LDAP/AD Web

Navicat On-Prem Server 14 WindowsmacOS Homebrew Linux

SQL JOIN

PostgreSQL dvdrental Navicat Premium Lite 17

EXISTS

dvdrental SQL EXISTS NOT EXISTS

SELECT *FROM actor aWHERE NOT EXISTS (  SELECT * FROM film_actor fa  WHERE a.actor_id = fa.actor_id)

Navicat Premium Lite 17

NOT IN!

EXISTS IN NOT EXISTS NOT IN film_actoractor_id NOT NULL

actor_id NOT NULL NOT IN

actor_id

SELECT *FROM actorWHERE actor_id NOT IN (1, 2, 3, 4, 5, NULL)

SQL NULL actor_id predicateUNKNOWN

NOT IN NOT EXISTS DBA

IS NULL WHERE LEFT JOIN

SELECT a.*FROM actor a  LEFT JOIN film_actor fa  ON a.actor_id = fa.actor_idWHERE fa.actor_id IS NULL

/

SQL NOT EXISTS

Navicat Premium Lite 17 14 WindowsmacOS Linux

ANSI SQL SQL Semi Join Navicat Premium Lite 17 PostgreSQL dvdrental SELECT MySQL Sakila

ANSI SQL Cloudera Impala

SELECT *FROM actorLEFT SEMI JOIN film_actor USING (actor_id)

Semihalf SQL EXISTS IN

EXISTS

EXISTS

SELECT *FROM actor aWHERE EXISTS (  SELECT *   FROM film_actor fa  WHERE a.actor_id = fa.actor_id)

Navicat Premium Lite 17

EXISTS film_actor WHERE SEMI JOIN EXISTS()

IN

IN EXISTS SEMI JOIN EXISTS

SELECT *FROM actorWHERE actor_id IN (  SELECT actor_id FROM film_actor)

Navicat Premium Lite 17

EXISTS

ANSI SQL SEMIINNER JOIN

Navicat Premium Lite 17 14 WindowsmacOS Linux

SQL WHERE HAVING SELECT

Having

SQL GROUP BY

GROUP BY GROUP BY HAVING WHERE HAVING HAVING WHERE GROUP BY ORDER BY

SELECT column_listFROM table_nameWHERE where_conditionsGROUP BY column_listHAVING having_conditionsORDER BY order_expression

HAVING Sakila SELECT

HAVING 3

SELECT  c.customer_id,  c.first_name,  c.last_name,  COUNT(r.rental_id) AS total_rentalsFROM   customer AS c    LEFT JOIN rental AS r ON c.customer_id = r.customer_idGROUP BY c.customer_idHAVING total_rentals >= 3ORDER BY total_rentals DESC;

Navicat Premium

WHERE HAVING

GROUP BY ORDER BY WHERE HAVING WHERE

SELECT  c.customer_id,  c.first_name,  c.last_name,  COUNT(r.rental_id) AS total_rentalsFROM   customer AS c    LEFT JOIN rental AS r ON c.customer_id = r.customer_idWHERE r.rental_date BETWEEN '2005-01-01' AND '2005-06-30'GROUP BY c.customer_idHAVING total_rentals >= 3ORDER BY total_rentals DESC;

Navicat Premium

WHERE AND OR HAVING HAVING

HAVING total_rentals >= 3 AND total_rentals <= 10

HAVING

Navicat Premium 14

SQL EXISTS IN EXISTS EXISTS IN

EXISTS

EXISTS SELECTUPDATEINSERT DELETE SELECT

SELECT column_name(s) FROM table_nameWHERE EXISTS ( SELECT column_name(s)                FROM table_name               WHERE condition );

PostgreSQL Navicat for PostgreSQL

customer_id

SELECT *FROM customer CWHERE EXISTS ( SELECT *               FROM account A               WHERE C.customer_id = A.customer_id );

Navicat Premium

NOT EXISTS

EXISTS NOT NOT EXISTS

SELECT *FROM account AWHERE NOT EXISTS ( SELECT *                   FROM customer C                   WHERE A.customer_id = C.customer_id );

ID #4

Joins EXISTS

EXISTS LEFT JOIN EXISTS

SELECT C.*FROM customer C  LEFT JOIN account A ON C.customer_id = A.customer_id;

IN vs EXISTS

IN IN EXISTS

SELECT * FROM customer WHERE customer_id IN (SELECT customer_id FROM account);

SELECT *IN

IN EXISTS

EXISTS EXISTS IN

Navicat Premium 17 14 WindowsmacOS Linux

90 Sun Microsystems [] JavaJava SQL Java SQL

ANSI SQL

ANSIAmerican National Standards Institute HTMLCSS ECMAScript SQL /ANSI SQL

ANSI SQL SQL

SQL

SQL SQL SELECTWHEREGROUP BYORDER BY SUM()AVG()MIN() MAX() SQL ServerMySQLPostgreSQLSQLite Oracle

Select        c.customer_id,    c.customer_name,    SUM(p.amount) AS total_salesFROM customers AS c    LEFT JOIN purchases AS p    ON c.customers_id = p.customer_idWHERE    c.customer_location = 'Canada'GROUP BY    c.customer_name ASC;

SQL

SQLANSI SQLMySQLSQLMySQLMicrosoft SQL ServerMicrosoftSQLT-SQLSQL ServerSQLPostgreSQLSQLiteANSISQLite

WHERE name = 'Rob' Or WHERE name = 'rob':

MySQLPostgreSQL SQLite AS SELECT SUM(score) AS avg_score SQL Server SELECT SUM(score) AS avg_scoreSQL Server AS SELECT SUM(score) = avg_score

Date/Time

Navicat Premium

Navicat Premium SQL SQL

Navicat 17 14 WindowsmacOS Linux

Navicat BI Navicat BI dvdrental

/ODBC SQL Navicat SELECT

rental_date return_date return_date macOS Control ...

DATEDIFF() D

ID X Y customer_id X rental_duration (Average) Y X Y rental_duration

customer_id AGGCOUNT()

number_of_rentals

customer_id rental_duration

Navicat BI BI Navicat BI 14 . It's available for Windows, macOS, and Linux operating systems.

Navicat BI /ODBC

dvdrental

Rentals by Month

Navicat for PostgreSQL

Sales by Month

MacOS Control -> ...

Navicat BI 14 WindowsmacOS Linux Navicat BI Navicat Premium Navicat for MySQLOraclePostgreSQLSQLiteSQL Server MariaDB

Navicat Premium Navicat Premium Lite Navicat Premium Lite

Navicat Premium Lite Navicat Premium JSON

Navicat Premium Lite PostgreSQL

PostgreSQL

Navicat Premium Lite SQL

SQL

TEXT (.txt) CSVXML JSON Excel (.xls .xlsx)MS Access DBase

Navicat Navicat Cloud Navicat On-Prem Server Navicat Premium Lite Navicat Cloud

SSH SSL PAMX.509 GSSAPI

Navicat Premium Lite Navicat Premium Lite

MySQLRedisPostgreSQLSQL ServerOracleMariaDBSQLite MongoDBNavicat Premium LiteNavicat Premium Lite WindowsmacOS Linux

Navicat BI Navicat BI "dvdrental"

IT

SELECT Sum of Payments per Movie Category CASE GamesVideo Games

New Releases vs. Other Categories

Rentals by Category

Navicat BI macOS Control <>

Rentals by Category 1

New Releases vs. Other Categories

name macOS Control ...

mapped_category_names

New

• New
• New
• New Release

Null

• ...
• NULLUncategorized

  

Other Categories

mapped_category_names

Navicat BI New Releases vs. Other Categories

Navicat BI 14 WindowsmacOS Linux

5 12 Navicat Navicat PremiumNavicat BI Navicat Data Modeler Navicat Navicat for MySQL Navicat Premium Navicat for MySQL 17

Navicat Data Modeler 4Navicat for MySQL 17

Navicat 17 Navicat 17 Navicat

5 7 Navicat 17 Navicat Navicat for MySQL 17

Navicat 17

Navicat for MySQL

Navicat for MySQL 17

• Navicat URI URI
• BI Navicat BI

Navicat for MySQL 17

Navicat for MySQL 17 WindowsLinux macOS

Navicat BI 1 Navicat BI dvdrental

dvdrental concat()

Rentals by Category

IDComedy Comedy(5)

Rentals by Category

BI Rentals by Category

->

()

["Sales per Category".name] (["Sales per Category".category_id])

category_id_and_name

Average Sales by Date

Average Sales by Date BI

category_id_and_name

ID

Navicat BI

Navicat BI 14 WindowsmacOS Linux

Navicat 17 BI Navicat BI dvdrental

SELECT

SELECT  c.category_id,  c.name,  p.amount,  r.rental_dateFROM   payment as p    LEFT JOIN      rental AS r on p.rental_id = r.rental_id    LEFT JOIN      inventory AS i ON r.inventory_id = i.inventory_id    LEFT JOIN      film_category AS fc ON i.film_id = fc.film_id    LEFT JOIN      category AS c ON fc.category_id = c.category_idORDER BY c.category_id;

• rental_date

rental_date

rental_date "..."

rental_date Navicat "rental_date_no_time"

" "

DateTime

"DD MMM YYYY " "DD MMM YYYY "

Navicat BI

Navicat BI 14 WindowsmacOS Linux

Business IntelligenceBI Navicat BI BI Navicat BI

Total Sales Percentages by Category

Dvdrental Total Sales Percentages by Category

BI

Dvdrental

name sum

/

Total Sales Percentage by Category Dashboard

Navicat BI

Navicat BI 1

Navicat BI 14 WindowsmacOS Linux

Navicat BI Navicat BI

PostgreSQL "dvdrental"

Navicat BI Navicat Premium Navicat Premium 17

• Navicat BI

• 

BI

BI ODBC ExcelAccessCSV URL

Navicat dvdrental BI

SQL payment amount

SELECT  c.category_id,  c.name,  sum(p.amount)FROM   payment as p    LEFT JOIN      rental AS r on p.rental_id = r.rental_id    LEFT JOIN      inventory AS i ON r.inventory_id = i.inventory_id    LEFT JOIN      film_category AS fc ON i.film_id = fc.film_id    LEFT JOIN      category AS c ON fc.category_id = c.category_idGROUP BY c.category_id, c.nameORDER BY c.category_id;

Navicat Sum of Payments per Movie Category

BI

• BI
• Dvdrental PostgreSQL

  

• dvdrental PostgreSQL

  

PostgreSQL dvdrental

Navicat BI ///KPI/

Navicat BI 14 WindowsmacOS Linux

BIBI Navicat BI Navicat BI Navicat Chart Creator Navicat BI

Navicat BI MySQLPostgreSQLSQL ServerOracleSQLiteMariaDBMongoDB Snowflake Sybase DB2 ODBC

ExcelAccessCSV URL

Navicat BI ///KPI/

Navicat BI Navicat BI Navicat

Navicat BI Navicat BI

Navicat BI Navicat Premium 14 WindowsmacOS Linux

Navicat 17 Navicat Data Modeler Navicat BI Navicat Chart Creator Navicat Data Modeler 4 Navicat BI

Navicat Data Modeler 4

Navicat Data Modeler Navicat Data Modeler 4

Navicat Data Modeler 4

Navicat Data Modeler 4 Navicat Data Modeler 4

• /

  

PDF

Navicat Data Modeler 4

Navicat Data Modeler 4 14 WindowsmacOS Linux

Navicat 17 Navicat 17 URI URI

URI

URI Navicat URI Navicat

Navicat URI

Navicat mac Control ->

mac Control

mac Control

Navicat 17

Navicat 17 14 WindowsmacOS Linux

Navicat 17

dvdrental Navicat 17 rental

rental

rental_date

rental Navicat 17

rental ->->

->->

-> SQL

mac Control ...

Navicat 17 dvdrental rental

5 13 Navicat Premium 17

5 13 Navicat 17 GUI Navicat 17

PostgreSQL dvdrental

PostgreSQL dvdrental PostgreSQLDVD DVD

Navicat 17

->

Navicat

• /

PDF

Navicat

Navicat 17 5 13 Navicat Premium 17

Navicat 17 5 13 17 PostgreSQL dvdrental rental

rental 2006 rental_date 2006 1 1 00:00:00 2006 6 30 23:59:59

Navicat 17 dvdrental rental

5 13 Navicat Premium 17

PostgreSQL PostgreSQL "dvdrental"

1.

customers 18

ALTER TABLE customersADD CONSTRAINT check_age CHECK (age >= 18);    

2.

customer

ALTER TABLE customersALTER COLUMN email SET NOT NULL;    

3.

customer

ALTER TABLE customersADD CONSTRAINT unique_email UNIQUE (email);    

4.

customercustomer_id

ALTER TABLE customersADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);    

5.

rental

ALTER TABLE rentalADD CONSTRAINT fk_customer_idFOREIGN KEY (customer_id)REFERENCES customers(customer_id);    

6.

bookings

ALTER TABLE bookingsADD CONSTRAINT exclude_overlapping_bookingsEXCLUDE USING GIST (room_id WITH =, booking_range WITH &&);    

Navicat

Navicat for PostgreSQL 16 PostgreSQL

PostgreSQL

PostgreSQL Navicat 16 for PostgreSQL 14

Navicat 17 5 13 Navicat 16 Navicat 17 5 13

MySQLMariaDB PostgreSQL

Navicat URI

Navicat 17

BI

BI

MongoDB

Redis

Navicat RedisNavicat 17 Redis Redis Redis Redis

Navicat 17 5 13

PostgreSQL PostgreSQL DML Navicat for PostgreSQL 16 "dvdrental"

1. B B-Tree

B PostgreSQL paymentcustomer_id B

CREATE INDEX btree_customer_id_idx ON payment(customer_id);

Navicat btree_customer_id_idxcustomer_idB-Trees

btree_customer_id_idx

2.

Hashinventoryfilm_id DML

CREATE INDEX hash_film_id_idx ON inventory USING HASH(film_id);

Navicat

3. GiST

GiST

geometry GiST

CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);

4. SP-GiST

SP-GiST

tsvector SP-GiST

CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);

5. GIN

GINfilmtitle GIN

CREATE INDEX gin_title_idx ON film USING gin(to_tsvector('english', title));

Navicat film gin_title_idx

6. BRIN

BRINrentalrental_date BRIN

CREATE INDEX brin_rental_date_idx ON rental USING brin(rental_date);

Navicat brin_rental_date_idx

PostgreSQL Navicat for PostgreSQL 16

PostgreSQL PostgreSQL dvdrental

PostgreSQL

PostgreSQL

• CREATE RULE CREATE RULE
• SELECTINSERTUPDATEDELETE ALL
• SQL SELECTINSERTUPDATEDELETE
• WHERE

dvdrental

1

customer

CREATE TABLE customer_audit (    action_type VARCHAR(10),    customer_id INT,    audit_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Navicat for PostgreSQL 16

CREATE RULE log_customer_insert AS    ON INSERT TO customer    DO ALSO        INSERT INTO customer_audit (action_type, customer_id)        VALUES ('INSERT', NEW.customer_id);

Navicat log_customer_insert

2

CREATE RULE prevent_return_date_update AS    ON UPDATE TO rental    WHERE OLD.return_date IS NOT NULL AND NEW.return_date IS DISTINCT FROM OLD.return_date    DO INSTEAD NOTHING;

Navicat prevent_return_date_update

enforce_min_rental_duration

3

address

CREATE RULE transform_phone_number AS    ON INSERT TO address    DO ALSO        UPDATE address        SET phone = '+1-' || SUBSTRING(phone FROM 3)        WHERE address_id = NEW.address_id;

Where Definition Navicat transform_phone_number

PostgreSQL PostgreSQL

PostgreSQL PostgreSQL DVD Rental DatabaseDVD

PostgreSQL

PostgreSQL

PostgreSQL

PostgreSQL

1. • INSERTUPDATE DELETE


2. 
   
3. 
   

dvdrental

PostgreSQL dvdrental

CREATE RULE enforce_min_rental_duration AS    ON INSERT TO rental    WHERE (NEW.return_date - NEW.rental_date) < INTERVAL '1 day'    DO INSTEAD NOTHING;

Navicat INSTEADALSO Navicat

PostgreSQL

Navicat 16 for PostgreSQL 14

PostgreSQL PostgreSQL DVD Rental Database

CHECK

dvdrentalrentalrental_duration Navicat

rentals_with_rental_period

ALTER TABLE rentals_with_rental_periodADD CONSTRAINT rental_duration_checkCHECK (rental_duration > 0);    

In Navicat we can add a check constraint in the "Checks" tab of the Table Designer. We just need to supply an expression and optional name. Navicat will create a unique name for us if we don't supply one!

Navicat Navicat

rentals_with_rental_period

dvdrentalfilmGPGPG-13RNC-17

ALTER TABLE filmADD CONSTRAINT film_rating_checkCHECK (rating IN ('G', 'PG', 'PG-13', 'R', 'NC-17'));    

Navicat

film

NULL

NULL NULL filmrental_rate NULL

ALTER TABLE filmADD CONSTRAINT film_rental_rate_checkCHECK (rental_rate > 0 AND rental_rate IS NOT NULL);    

Navicat

PostgreSQL

PostgreSQL file_fdw_handlerfile_fdw_validatorpg_stat_statementspg_stat_statements_info pg_stat_statements_reset Navicat

PostgreSQL FDWXfile_fdw_handler file_fdw_validator

file_fdw_handler PostgreSQL SQL external_data data.csv CSV

        CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;        CREATE FOREIGN TABLE external_data (            id INT,            name TEXT,            age INT        ) SERVER file_server OPTIONS (filename '/path/to/data.csv');    

file_fdw_validator

        SELECT file_fdw_validator('filename', '/path/to/data.csv');    

PostgreSQL pg_stat_statements pg_stat_statementspg_stat_statements_info pg_stat_statements_reset

pg_stat_statements SQL pg_stat_statements, postgresql.conf shared_preload_libraries

shared_preload_libraries = 'pg_stat_statements'

PostgreSQL

        SELECT * FROM pg_stat_statements;    

pg_stat_statements_info pg_stat_statements

        SELECT * FROM pg_stat_statements_info;    

pg_stat_statements_reset pg_stat_statements

        SELECT pg_stat_statements_reset();    

Navicat PostgreSQL

Navicat for PostgreSQL Navicat Premium 16 :

PostgreSQL file_fdw_handler, file_fdw_validator, pg_stat_statements, pg_stat_statements_info, pg_stat_statements_reset PostgreSQL

DML/SQL Navicat for PostgreSQL 16

PostgreSQL

dvdrental sample databaserentals_with_rental_period

CREATE TABLE rentals_with_rental_period (    rental_id SERIAL PRIMARY KEY,    customer_id INT,    rental_duration INT,    rental_period DATERANGE);INSERT INTO rentals_with_rental_period (customer_id, rental_duration, rental_period)VALUES(1, 7, '[2024-02-01, 2024-02-08]'),(2, 5, '[2024-01-15, 2024-01-20]');    

Navicat

[) Navicat

rental_period @>

CREATE TABLE product_price (    product_id SERIAL PRIMARY KEY,    price_range NUMRANGE);INSERT INTO product_price (price_range)VALUES('[10.00, 20.00)'),('[20.00, 30.00)'),('[30.00, )');    

price_range @>

SELECT * FROM product_priceWHERE price_range @> 25.00;    

PostgreSQL

PostgreSQL Navicat 16 for PostgreSQL 14

PostgreSQL dvdrental sample database

Array

PostgreSQL films_with_actors

CREATE TABLE films_with_actors (    film_id SERIAL PRIMARY KEY,    title VARCHAR(255),    actors TEXT[]);INSERT INTO films_with_actors (title, actors) VALUES('Inception', ARRAY['Leonardo DiCaprio', 'Joseph Gordon-Levitt']),('The Shawshank Redemption', ARRAY['Tim Robbins', 'Morgan Freeman']);    

Navicat

[]Navicat 1

{} Navicat

actors[1]

Enum

EnumeratedEnum films_with_actorsrating DDL

CREATE TYPE rating AS ENUM ('G', 'PG', 'PG-13', 'R', 'NC-17');ALTER TABLE films_with_actors ADD COLUMN rating rating;    

Navicat CREATE TYPE rating Enum (Type) rating

rating

PostgreSQL PostgreSQL Range Range

PostgreSQL Navicat 16 for PostgreSQL 14

PostgreSQL

PostgreSQL PostgreSQL

PostgreSQL

PostgreSQL hstore JSON

ACID

PostgreSQL ACID

PostgreSQL 2.0 PostgreSQL

PostgreSQL

PostgreSQL PostgreSQL

PostgreSQL IT ACID

PostgreSQL Navicat 16 for PostgreSQL 14

DVD Rental Database PostgreSQL PostgreSQL DML Navicat GUI

REFRESH MATERIALIZED VIEW mv_category_revenue;

Navicat macOS control-click->

SELECT * FROM mv_category_revenue;

Navicat

DROP MATERIALIZED VIEW mv_category_revenue

DROP MATERIALIZED VIEW mv_category_revenue;

Navicat

macOS control-click

DML Navicat GUI

PostgreSQL DVD Rental Database PostgreSQL

2.0

DVD Rental

DVD Rental MySQL Sakila PostgreSQL PostgreSQL PostgreSQL Sample Database DVD Rental

ZIP dvdrental.zip, PostgreSQL dvdrental.tar .tar dvdrental pg_restore .tar dvdrental

        pg_restore -U postgres -d dvdrental D:\sampledb\postgres\dvdrental.tar    

dvdrental.tar

• PostgreSQL
• DML

CREATE MATERIALIZED VIEW mv_category_revenue ASSELECT    c.name AS category,    SUM(p.amount) AS total_revenueFROM    category c    JOIN film_category fc ON c.category_id = fc.category_id    JOIN film f ON fc.film_id = f.film_id    JOIN inventory i ON f.film_id = i.film_id    JOIN rental r ON i.inventory_id = r.inventory_id    JOIN payment p ON r.rental_id = p.rental_idGROUP BY    c.name;

DVD Rental

Navicat For PostgreSQL Navicat Premium16

• +

  

• SELECT

  

• 

• CREATE MATERIALIZED VIEW mv_category_revenue

  

• Navicat

  

PostgreSQL DVD Rental

SQLite RDBMS SQLite Chinook SQL

SQLite

Windows

• SQLite https://www.sqlite.org/download.html.
• Precompiled Binaries for Windows
• 32-bit 64-bit
• ZIP
• sqlite3.dll
• SQLite sqlite3.dll PATH

macOS

• macOS SQLite
• sqlite3 Enter SQLite shell

Linux:

• Linux SQLite
• Debian/Ubuntusudo apt-get install sqlite3
• Red Hat/Fedorasudo dnf install sqlite
• Arch Linux: sudo pacman -S sqlite
• sqlite3 SQLite shell

Navicat Chinook

• Chinook

  chinook.db Navicat

• ->->SQLite

  

• Chinook [] Chinook

  

• 

Chinook SQL

Chinook

Chinook

• Chinook
• -> main

Chinook SELECT SELECT * FROM artists;

SELECT trackid, name, composer FROM tracks WHERE composer = 'Ludwig van Beethoven';

UPDATE

INSERT Navicat

Navicat :

SQLite Chinook SQL SQLite Navicat for SQLite Navicat Premium16 SQLite

Navicat Monitor 3 Web Navicat Monitor I/O

Sakila

• +
• Average Payment<>:/|* Sakila payment The average payment in the Sakila payment table.
• MySQL
• MySQL
• NULL AVGMIN MAX AVGSELECT AVG(amount) FROM sakila.payment; Navicat

• 

• 
  

• Average Payment Alert

  

• 

• 

Navicat Monitor 3

1.

2.

PCI DSSPCI DSS

3. PII

PII

PII

Navicat 16

• User Authentication
• RBAC RBAC

• SSLTLS SSL TLS

Navicat Monitor 3 MySQLMariaDBPostgreSQL SQL Server Amazon RDSAmazon AuroraOracle Cloud Microsoft Azure Navicat Monitor 3

• Navicat Monitor 3 CPU I/O

Navicat Monitor 3

MySQL MySQL

MySQL InnoDB MyISAM

• InnoDB: MySQL InnoDB ACID
• MyISAM: MyISAM 2.0
• MRG_MyISAM: MyISAM
• MEMORY: RAM MEMORY
• Blackhole:
• CSV: CSV text CSV
• Performance_Schema:
• ARCHIVE:

InnoDB MyISAM

InnoDB MyISAM

InnoDB ACID MyISAM MyISAM InnoDB

InnoDB InnoDB MyISAMMyISAM

InnoDB MVCC MyISAM

Navicat

MySQL Navicat

InnoDB

MEMORY

MySQL

Navicat SQL SQL SQL Navicat Navicat SQL

Navicat SQL ->

Navicat SQL Navicat

SQL SQL

Navicat

Courier 12pt

Navicat

(MB): MB

• 5

Navicat

Navicat SQL Navicat SQL

SQL / SQL

SQL

1. COUNT()

COUNT()

        SELECT COUNT(*) AS total_records FROM employees;    

2. SUM()

SUM()

        SELECT SUM(salary) AS total_salary FROM employees;    

3. AVG()

AVG()

        SELECT AVG(age) AS average_age FROM students;    

4. MAX() MIN()

MAX() MIN()

        SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;    

GROUP BY

SQL GROUP BY

COUNT() )

        SELECT department, COUNT(*) AS employee_countFROM employeesGROUP BY department;    

AVR()

        SELECT department, AVG(salary) AS average_salaryFROM employeesGROUP BY department;    

HAVING

HAVING GROUP BY

        SELECT department, AVG(salary) AS average_salaryFROM employeesGROUP BY departmentHAVING AVG(salary) > 50000;    

SQL

        SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salaryFROM employeesGROUP BY department;    

Navicat

SQL Navicat Navicat Sigma (Σ)

SQL GROUP BY HAVING

MySQLPostgreSQLMicrosoft SQL Server

MySQL

SQL Profiler

        SET profiling = 1;-- Your SQL Query Goes HereSHOW PROFILES;            

Duration

SQL

        SELECT SUM(Duration) AS TotalDurationFROM information_schema.profilingWHERE Query_ID > 1;            

PostgreSQL

Timing

PostgreSQL Timing

        \timing-- Your SQL Query Goes Here            

pg_stat_statements

PostgreSQL pg_stat_statments SQL PostgreSQL

        SELECT total_time, calls, queryFROM pg_stat_statementsWHERE query = 'Your SQL Query Goes Here';            

Microsoft SQL Server

SET STATISTICS TIME

SQL Server SET STATISTICS TIME ON :

        SET STATISTICS TIME ON-- Your SQL Query Goes HereSET STATISTICS TIME OFF            

sys.dm_exec_query_stats

sys.dm_exec_query_stats

        SELECT total_elapsed_time, execution_count, textFROM sys.dm_exec_query_statsCROSS APPLY sys.dm_exec_sql_text(sql_handle)WHERE text LIKE 'Your SQL Query Goes Here%';            

Oracle

SQL*Plus AUTOTRACE

Oracle SQL*Plus AUTOTRACE SQL

        SET AUTOTRACE ON-- Your SQL Query Goes HereSET AUTOTRACE OFF            

CPU

V$SQL

V$SQL

        SELECT elapsed_time, executions, sql_textFROM V$SQLWHERE sql_text LIKE 'Your SQL Query Goes Here%';            

Navicat

Navicat

MySQLPostgreSQLMicrosoft SQL Server Oracle

Redis MySQL Redis MySQL SQL Server Oracle

Redis MySQL Redis MySQL

Redis

MySQL

Redis Redis RAM MySQL

Redis

//  Redis redisClient.get("user:123", (err, result) => {    const userData = JSON.parse(result);    console.log(userData);});

MySQL

-- MySQL  users SELECT * FROM users WHERE id = 123;

Redis Redis AOF MySQL

Redis

//  Redis  5 config set save "300 1";

Redis MySQL

Redis

//  Redis redis-trib.rb create --replicas 1 127.0.0.1:7000 127.0.0.1:7001 127.0.0.1:7002

MySQL

--  users -- user_idCREATE TABLE users_shard_1 SELECT * FROM users WHERE user_id % 3 = 1;CREATE TABLE users_shard_2 SELECT * FROM users WHERE user_id % 3 = 2;CREATE TABLE users_shard_3 SELECT * FROM users WHERE user_id % 3 = 0;

Redis MySQL

• Redis
  • Redis
• MySQL
  • MySQL ACID
  • MySQL

Redis

Redis Navicat for Redis GUI Redis

Redis MySQL

Navicat Navicat Premium

Navicat

->

Sakila Sakila last_update actor

Navicat MySQL YYYY-MM-DD hh:mm:ss2019-10-12 14:35:1824

SQL ODBC

YYYY-MM-DD hh:mm:ss SQL ODBC SQL SQL Navicat

• c -
• d - 1-31
• dd - 01-31
• ddd - Sun-Sat
• dddd - Sunday-Saturday
• ddddd -
• dddddd -
• m - 1-12 m h hh
• mm - 01-12 mm h hh
• mmm - Jan-Dec
• mmmm - January-December
• yy - 00-99
• yyyy - 0000-9999
• h - 00-23
• hh - 00-23
• n - 0-59
• nn - 00-59
• s - 0-59
• ss - 0-59
• t -
• tt -
• am/pm - h hh 12 'am' 'pm'am/pm
• a/p - hhh 12 'a' 'p'a/p
• ampm - h hh 12
• / -
• : -
• 'xx' "xx" -

Navicat AM PM 12

mmm d, yyyy hh:mm:ss AM/PM

actor last_update

classicmodels orders

Navicat Premium Navicat Navicat for MySQL Navicat for SQL Server

Navicat 16 100

Navicat

MySQL

Navicat 16 Navicat

Navicat 16 100

Navicat

MySQL

Navicat 16 Navicat

Redis Redis Navicat Redis

1. LPUSH RPUSH

Redis LPUSH RPUSH

> LPUSH my_list "element1"(integer) 1> RPUSH my_list "element2"(integer) 2> LRANGE my_list 0 -11) "element1"2) "element2"

2. LPOP RPOP

Redis LPOP RPOP RPOP

> LPOP my_list"element1"> RPOP my_list"element2"

3. SADD SMEMBERS

Redis SADD SMEMBERS

> SADD my_set "member1"(integer) 1> SADD my_set "member2"(integer) 1> SMEMBERS my_set1) "member1"2) "member2"

4. ZADD ZRANGE

Redis ZADD ZRANGE

> ZADD my_sorted_set 1 "element1"(integer) 1> ZADD my_sorted_set 2 "element2"(integer) 1> ZRANGE my_sorted_set 0 -1 WITHSCORES1) "element1"2) "1"3) "element2"4) "2"

5. HSET HGET

Redis HSET HGET

> HSET my_hash field1 "value1"(integer) 1> HSET my_hash field2 "value2"(integer) 1> HGET my_hash field1"value1"

6. PUBLISH SUBSCRIBE

Redis PUBLISH SUBSCRIBE

# Terminal 1> SUBSCRIBE my_channelReading messages... (press Ctrl-C to quit)1) "subscribe"2) "my_channel"3) (integer) 1# Terminal 2> PUBLISH my_channel "Hello, Redis!"(integer) 1

7. SCAN

SCAN Redis

> SCAN 01) "0"2) 1) "my_list"   2) "my_set"   3) "my_sorted_set"   4) "my_hash"   5) "my_channel"

Navicat 16 for Redis

Navicat Redis ->

SCAN

Redis

Redis Redis Navicat 16 for Redis

Redis NoSQL Redis JSON Redis JSON CLI macOS Navicat 16 for Redis JSON

Redis JSON

JSONJavaScript Redis 6.0 JSON JSON

Redis JSON Redis

CLI JSON

• JSON

  Redis JSON JSON.SET

  JSON.SET mykey . '{"name": "John Doe", "age": 30, "email": "john@example.com"}'

  JSON mykey

• JSON

  JSON.GET JSON

  JSON.GET mykey

  mykey JSON

• JSON

  JSON.SET JSON

  JSON.SET mykey . '{"name": "John Doe", "age": 31, "email": "john@example.com"}'

• JSON

  Redis path JSON.GET JSON

  JSON.GET mykey .name

  name

• JSON

  JSON

  DEL mykey

Navicat for Redis

Redis CLI JSON Navicat Navicat for RedismacOS16.2.6 JSON

Navicat for Redis (macOS) 16.2.6

• Navicat Redis

  • Navicat->Redis

• JSON

  Navicat Redis JSON

  • JSON

• JSON

  Navicat JSON JSON

Redis JSON

Redis JSON JSON Redis macOS Navicat 16 for Redis JSON JSON Navicat

RedisRemote Dictionary Server Redis Navicat for Redis

Redis Redis RAM Redis

Redis I/O

Redis

• StringStringinteger
  
  SET user:1 "John Doe"
• Listpushpop
  
  LPUSH mylist "item1"
• Set
  
  SADD tags "Redis" "Database" "NoSQL"
• Hash
  
  HSET user:1 username "johndoe" email "john@example.com"

Pub/Sub/

Redis /

Redis Pub/Sub

PUBLISH game:updates "New message: Hello, world!"

Lua

Redis Lua

Lua

local sender_balance = tonumber(redis.call('GET', KEYS[1]))local receiver_balance = tonumber(redis.call('GET', KEYS[2]))local amount = tonumber(ARGV[1])if sender_balance >= amount then    redis.call('DECRBY', KEYS[1], amount)    redis.call('INCRBY', KEYS[2], amount)    return "SUCCESS"else    return "INSUFFICIENT FUNDS"end

Navicat for Redis

Navicat for Redis GUI Redis Navicat for Redis

• Navicat for Redis Redis
• Navicat Redis
• Navicat
• Navicat for Redis

Redis

Redis Navicat for Redis Redis Navicat Redis Redis Navicat

2023 5 Navicat Navicat for Redis Redis JSON 16.3 Navicat Premium Navicat for Redis Redis Cluster Redis Navicat Redis Navicat Premium

Redis 101

Redis Redis Redis 3.0

Redis Redis

Redis

Redis

Redis

Navicat Premium 16.3

Navicat Premium 16.3 MongoDB MySQL

MongoDB 5.0

MySQL MySQL 8.0

Navicat 16.3 Redis MongoDB MySQL

Navicat Premium 16.3 Navicat for Redis 16.3 14 WindowsmacOS Linux

Redis String Redis text 512 Redis CLI Navicat for Redis

CLI

Redis CLI Redis CLI

1.

Redis SETKey

    SET my_key "Hello, Redis!"

my_keyHello,Redis!

2.

GET

    GET my_key

my_keyHello,Redis!

3.

APPEND Key

    APPEND my_key ", How are you?"

my_keyHello,Redis!,How are you?

4.

GETRANGE

    GETRANGE my_key 0 4

my_keyHello

Navicat for Redis

Navicat for Redis GUI Redis Navicat

1. Redis

Navicat Redis

2. Redis

Redis Redis

3.

String

4

Navicat

5.

Redis CLI Navicat for Redis Redis Navicat for Redis GUI

Navicat

Navicat

Navicat MySQLPostgreSQLSQL Server

CustomersOrders CustomerID Email

Customers Orders Email SQL

SELECT Customers.*, Orders.*FROM CustomersINNER JOIN Orders ON Customers.Email = Orders.CustomerEmail;

• Customers.* Orders.*
• INNER JOIN Email CustomerEmail

• Email
• email VARCHAR

Navicat SQL

Redis Redis redis-cli Navicat for Redis Redis

Redis EXPIRE SETEX EXPIRE SETEX

EXPIRE

127.0.0.1:6379> SET mykey "Hello, Redis"OK127.0.0.1:6379> EXPIRE mykey 60(integer) 1

SET mykey HelloRedis EXPIRE mykey 60 60

SETEX

127.0.0.1:6379> SETEX mykey 60 "Hello, Redis"OK

SETEX 60

TTL

TTL -2 -1

127.0.0.1:6379> TTL mykey(integer) 30

mykey 60 30 30

Redis Redis DEL

127.0.0.1:6379> DEL mykey(integer) 1

DEL mykey

Navicat

Navicat TTL

TTL TTL () () 60

Redis

1.

Redis

2.

Redis Web

3.

API Redis p>

4.

Redis

redis-cli Navicat for Redis Redis Redis Redis

Java Navicat Java

Java

Navicat Java

Java Navicat

Navicat Java Navicat

Java Navicat

Navicat Java Navicat

Navicat

Redis Hash-HashHash redis-cli Navicat for Redis Hash

Hash

Redis keyHashvalue-

vehicle make Toyota model Crown trim Platinum year 2023 color black

Hash HSET

HSET key field value

HSETRedis

• 1
• 0

HSET vehicle make "Toyota"   // 1HSET vehicle model "Crown"   // 1HSET vehicle trim "Platinum" // 1HSET vehicle year 2015       // 1HSET vehicle color "black"   // 1

year 2022HSET 0

HSET vehicle year 2022 // 0

Navicat Hash

Navicat for Redis []

Hash

HGET Hash

HGET key field

year 2022 2015

HGET vehicle year // 2022

HGETALL Hash

HGETALL key

HGETALL vehicle/* Returns:makeToyotamodelCrowntrimPlatinumyear2022colorblack*/

HGETALL

HDEL Hash HDEL HDEL 0

HDEL key field [field ...]

HDEL year color

HDEL vehicle year color // 2

Navicat [-]

Redis Hash redis-cli Navicat for Redis Hash

Navicat for Redis 14

Redis ListListSet Redis SetListSetSet redis-cli Navicat for Redis Set

Set

Redis SADD Setkey

SADD key member [member ...]

keySetkey

vehicles

SADD vehicles "Infiniti"         // 1SADD vehicles "Mazda"            // 1SADD vehicles "Ford" "Mercedes"    // 2SADD vehicles "Porsche" "Mercedes" // 1

SADD Mercedes

Navicat Set

Navicat for Redis Set []

Navicat

Set

SREM

SREM key member [member ...]

SREM vehicles "Mazda" "Mercedes" // 2SREM vehicles "Dodge" // 0

SADD SREM

Navicat [-]

SISMEMBER

SISMEMBER key member

1 0

SISMEMBER vehicles "Infiniti" // 1SISMEMBER vehicles "Alfa Romeo" // 0

Set

SMEMBERS

SMEMBERS key

vehicles

SMEMBERS vehicles// returns "Infiniti", "Ford", "Porsche"

Set

SUNION Set

SUNION key [key ...]

SUNION

more_vehiclesCorvetteAlfa Romeovehiclesmore_vehicles

SUNION vehicles more_vehicles// "Infiniti", "Ford", "Porsche", "Corvette", "Alfa Romeo"

Redis Set redis-cli Navicat for Redis Set

Navicat for Redis 14

2023 DBTA 2023 DBTA Readers' Choice AwardsNavicat DBA Navicat Data Modeler Navicat DBTADBTA IT

Navicat Navicat

Best DBA Solution Navicat Navicat , Navicat Premium MySQLPostgreSQLMariaDBMongoDBSQL ServerOracleSQLite, Redis Navicat 50% 500

Navicat CEOKen Lin DBTAKen Lin Navicat

Navicat

Navicat Navicat 16.2 Navicat 16.2 Redis

Navicat

Redis Redis List

Redis Listnext

List

Redis RPUSH LPUSH

RPUSH

RPUSH

RPUSH key value [value ...]

guitars

RPUSH guitars "Jackson" // 1RPUSH guitars "Fender"  // 2RPUSH guitars "Gibson"  // 3

Redis guitars

Jackson Fender Gibson

LPUSH

LPUSH RPUSH LPUSH

LPUSH key value [value ...]

LPUSH guitar

LPUSH guitars "Ibanez" //4

lbanez

Ibanez Jackson Fender Gibson

Navicat List

Navicat for Redis List []

LRANGE

LRANGE

LRANGE key start stop

0 -1

LRANGE guitars 0 -1 //returns Ibanez Jackson Fender Gibson

LRANGE guitars 0 1 //returns Ibanez Jackson

List

LPOP List RPOP List

LPOP guitars //returns Ibanez RPOP guitars //returns Gibson 

Navicat [-]

Redis List redis-cli Navicat for Redis

Navicat for Redis 14

Redis Redis DML redis-cli Navicat for Redis

GET Redis

GET key

Navicat for Redis auth service

auth0

Indiana_jones_episodes,WRONGTYPE Operation against a key holding the wrong kind of value Redis 6

• string -> GET <key>
• hash -> HGET or HMGET or HGETALL <key>
• list -> lrange <key> <start> <end>
• set -> smembers <key>
• sorted set -> ZRANGEBYSCORE <key> <min> <max>
• stream -> xread count <count> streams <key> <ID>.

Indiana_jones_episodes ZRANGEBYSCORE min max

Redis SET key Value

Navicat

Redis DEL

DEL key

auth service

DEL "auth service"

Redis

Navicat [-]

redis-cli Navicat for Redis redis-cli Navicat

Navicat for Redis 14

Redis Pub/Sub Navicat for Redis Redis Pub/Sub

Pub/Sub

Redis Pub/Sub

Redis

Redis Redis Redis Streams

Pub/Sub 10 1 Redis prod_mychanneltest_mychannel

Navicat for Redis

Navicat for RedisNavicat Premium Pub/Sub Pub/Sub

Redis Redis test_channel

Navicat

test_channel

Navicat

Navicat for Redis Redis Pub/Sub

Navicat for Redis 14

SQL Navicat Premium 16.2

SQL

SELECT   column_name [AS] alias_name,  column_name AS 'Alias Name' -- for names with spacesFROM table_name;

SELECT column_name(s)FROM table_name [AS] alias_name;

• AS

JOIN

Sakila

SELECT *FROM film f   INNER JOIN inventory i ON i.film_id = f.film_idWHERE i.store_id = 1 AND f.title = "Academy Dinosaur";

film inventory film_id

Navicat

• "so_no" "sales order number"
• "qty" "quantity"

SELECTinv_no AS invoice_no,amount,due_date AS 'Due date',cust_no 'Customer No'FROMinvoices;

SELECT SELECT WHERE

SELECT WHERE WHERE NewPrice

ORDER BY SELECT

FROM > SELECT > ORDER BY

Navicat

Navicat

Navicat Premium 16.2

Redis key-valuestringlistsetsorted sethash Redis

String

Redis Redis 512 MB

Navicat for Redis ->

• TTL

Hash

Redis hash hash 2^32 - 1 - 40

hash Navicat

List

Redis List Redis 2^32 - 1 4294967295 40

Navicat list [...]

Set

Redis Set Set list Set

Set Sorted Setscore

Navicat Set List

zset

Stream

Redis stream Redis 5.0 Stream

Navicat for Redis Stream

Redis

Redis Stream

Navicat for Redis 14

Redis C BSD Redis

Redis

Redis

• Redis
• Redis 110,000 SET 81,000 GET
• Redis
• Redis Redis
• Redis /
• Redis Web

Redis

Redis / 100 Redis

Redis fork CPU

Redis

Redis Redis Stack Redis UIRedis

• Linux Redis
• macOS Redis
• Windows Redis
• Redis Redis Stack RedisInsight
• Redis C libc

Redis Navicat for Redis

Redis CLI

Navicat for Redis

Navicat CLI CLI

Redis Redis

Navicat Premium 16.2 Redis Navicat Redis Navicat for Redis Redis Redis Redis Enterprise CloudAmazon ElastiCache Microsoft Azure Navicat for Redis

Redis Navicat for Redis Navicat

Windows

Navicat Navicat for Redis

Pub/Sub

Pub/Sub

Pub/Sub

Navicat for Redis Navicat Cloud Navicat Cloud

Navicat Cloud

Redis

Navicat for Redis Navicat for Redis WindowsmacOS Linux

ID

Navicat 16 for MySQL MySQL

film inventory film 1 inventory

film inventory

NULLinventory film_id film film_id inventory film_id

Navicat 16 for MySQL

Navicat

Navicat film

filminventoryfilm_id

MySQL

• CASCADE
• SET NULLNULL
• RESTRICTMySQL
• NO ACTIONRESTRICT
• SET DEFAULTMySQL InnoDB NDB

FK RESTRICT CASCADE

Navicat

Navicat 16 for MySQL SQL

Navicat Data Modeler

ups ups

3NF ID ID ups CSRcustomer vehicle ID ID

ID

Navicat 16 ->->...

SQL

SQL

ID

- SQL SQL CREATE VIEW

Untitled

Navicat 16

ID

PostgreSQL Multi-Version Concurrency Control MVCC MVCC MVCC

MVCC

MVCC

MVCC (a) (b)

• write-timestamp
• read-timestamp

MVCC Jan Hidders

51220 14 12

510 16 812 20 11 10 12 11 12 10 11 14 t=12 t=16

MVCC

• MVCC

PostgreSQL

MVCC

PostgreSQL Navicat 16 for PostgreSQL 14

Navicat Monitor 3

PostgreSQL statement_timeout Navicat 16 For PostgreSQL

statement_timeout

30 60 60

ALTER DATABASE mydatabase SET statement_timeout = '60s';

Navicat 16 For PostgreSQL >>PostgreSQL statement_timeout

statement_timeout

SHOW

ALTER ROLE statement_timeout

guest

ALTER ROLE

ALTER ROLE guest SET statement_timeout='5min';

pg_roles statement_timeout

rolconfig unnest

PostgreSQL

Navicat Monitor 3

PostgreSQL

statement_timeout

Navicat 16 For PostgreSQL 14

MySQL Sakila rental DVD

DDL rental

create table rental_audit_log( id int NOT NULL AUTO_INCREMENT, rental_id int NOT NULL, old_values varchar(255), new_values varchar(255), done_by varchar(255) NOT NULL, done_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id));

Navicat DDL

3 rental_audit_log rental DML INSERTUPDATE DELETE

AFTER INSERT

rental_insert_audit_trigger rental INSERT INSERT JSON_OBJECT Navicat

rental rental_audit_log

AFTER UPDATE

rental UPDATE rental_update_audit_trigger

rental_update_audit_trigger rental_audit_log robg rental_date 2005-05-25 17:17:042005-05-31 19:47:04

AFTER DELETE

rental DELETE rental_delete_audit_trigger

old_values rental_audit_log new_values

fsmith 2023-03-22 08:46:07 rental 1114

MySQL Sakila rental DML

SQL 2 1 1 1 3

DBA id_id Sakila

pk_pk_<table_name>

_id customer_idemployee_id

city country country_id

fk_fk_<target_table>_<source_table>

city fk_city_country

1

employeecustomercitycountry

country_namecountry_codecustomer_name customer_namecity_name order_customer_name city_of_residence_name

start_date end_date call_start_date call_end_date

SQL

_customer_name invoice_date customerName invoiceDate SQL SELECT customer_name, invoice_date FROM orders;

SQL ANSI SQL PostgreSQLSQL Server MySQL SQL DISTINCT SELECT

SQL DISTINCT SELECT / DISTINCT

SELECT DISTINCT columns/expressionsFROM tables[WHERE conditions];

/ SELECT DISTINCT NULL DISTINCT NULL NULLDISTINCT NULL

//Navicat Premium 16 classicmodels

DISTINCT SELECT

Navicat DISTINCT SQL Navicat

DISTINCT country

DISTINCT city country

DISTINCT Null

DISTINCT NULL NULL customers

DISTINCT NULL

SQL DISTINCT SELECT NULL GROUP BY

SQL 3 1 2

DBA

[]_[]_[]

• Insert
• Delete
• Update
• Select
• Get
• Validate

sp_ SQL Server master SQL Server master user SQL Serversp_ SQL Server

usp_

• usp_insert_person
• usp_delete_person
• usp_update_person
• usp_select_person
• usp_get_person
• usp_validate_person

SQL

fn_

[]_[]_[]

true false isare

• fn_count_string_instances
• fn_get_customer_balance
• fn_is_inventory_in_stock
• fn_get_column_type

v_vw_

[]_[]

• vw_actor_full_name
• vw_sales_by_store
• v_staff_list
• v_sales_by_product_category

SQL

1 2 3

SQL 2 1 1 3

DBA id_id Sakila

pk_pk_<table_name>

_id customer_idemployee_id

city country country_id

fk_fk_<target_table>_<source_table>

city fk_city_country

1

employeecustomercitycountry

country_namecountry_codecustomer_name customer_namecity_name order_customer_name city_of_residence_name

start_date end_date call_start_date call_end_date

SQL

_customer_name invoice_date customerName invoiceDate SQL SELECT customer_name, invoice_date FROM orders;

Navicat Monitor 3 PostgreSQL PostgreSQL SQL Navicat Monitor 3.0 PostgreSQL

• 5
• 5
• CPU

Navicat Monitor

X

Navicat Monitor []

Navicat Monitor 3 PostgreSQL

Navicat Monitor 3

Navicat Monitor 3 SQL

SQL PostgreSQLSQL

SQL +

SQL +

+ Navicat Monitor

• QueryID
• SQL

3

Navicat Monitor 3 PostgreSQL

Navicat Monitor 3 PostgreSQL

Navicat Monitor 3 2 3 PostgreSQL PostgreSQL SQL

Navicat Monitor 3.0 PostgreSQL

PostgreSQL

PostgreSQL +

PostgreSQL PostgreSQL

Navicat Monitor SSH

PostgreSQL

• IP
• TCP/IP
• Unix Windows

Navicat Monitor CPU

PostgreSQL >

PostgreSQL Test DB 1

I/O

SQL

Navicat Monitor 3.0 PostgreSQL

Navicat Monitor 3.0 PostgreSQL

Navicat Monitor 3.0 WindowsmacOS Homebrew Linux Navicat Monitor 3.0 14

Navicat Monitor 2.0 3.0

• PostgreSQL
• PostgreSQL SQL

Navicat Monitor

• SNMP

Navicat Monitor 3.0

Navicat Monitor

Navicat Monitor Navicat Monitor I/O

Navicat Monitor

PostgreSQL

2 SQL Server 3 PostgreSQL

PostgreSQL PostgreSQL PostgreSQL

SQL PostgreSQL

SQL 3

PDF

Navicat Monitor 3.0

Navicat Monitor 3.0 PostgreSQL

Navicat Monitor 3.0 WindowsmacOS Homebrew Linux Navicat Monitor 3.0 14

1 2 3

DB

MySQL classicmodels sakila

DBA usersrolesusers_have_rolesuser_has_role

employeecustomercitycountry

classicmodels ordersorder_details

order_detailsproduct_details

user_has_roleuser_roleSakila film_actorfilm_category

SQL

product invoice invoice_iteminvoice_productinvoice_contains_product

AI OpenAI ChatGPT ChatGPT DBMS ChatGPT Navicat

ChatGPT ChatGPT OUseful.Info racerresults ChatGPT

Treat the following as a tab separated dataset. Using just the first, third and fourth columns, treat the data as if it were a relational SQL database table called "racerresults" with columns "Race", "Driver" and "Team", and the "Race" column as a primary key column. Display a SQL statement that could create the corresponding table and populate it with the data.Bahrain20 Mar 2022Charles LeclercFERRARI571:37:33.584Saudi Arabia27 Mar 2022Max VerstappenRED BULL RACING RBPT501:24:19.293Australia10 Apr 2022Charles LeclercFERRARI581:27:46.548Emilia Romagna24 Apr 2022Max VerstappenRED BULL RACING RBPT631:32:07.986Miami08 May 2022Max VerstappenRED BULL RACING RBPT571:34:24.258Spain22 May 2022Max VerstappenRED BULL RACING RBPT661:37:20.475Monaco29 May 2022Sergio PerezRED BULL RACING RBPT641:56:30.265erc...

ChatGPT CREATE TABLE INSERT

ChatGPT

ChatGPT Navicat SQL Navicat macOS

ChatGPT SQL

ChatGPT ChatGPT SQL

ChatGPT

Navicat ChatGPT

ChatGPT ChatGPT ChatGPT

Navicat Premium 16

SELECTUPDATE DELETE SELECT

SELECT column1, column2, ....FROM table1 outerWHERE column1 operator  (SELECT column1, column2   FROM table2   WHERE expr1 = outer.expr2);

stackoverflow Sakila

SELECT

SELECT   F.title AS title,   CO.country_id AS country_id,  CO.country AS country_name,   count(F.film_id) as timesFROM customer C INNER JOIN address A ON C.address_id = A.address_idINNER JOIN city CI ON A.city_id = CI.city_idINNER JOIN country CO ON CI.country_id = CO.country_idINNER JOIN rental R ON C.customer_id = R.customer_idINNER JOIN inventory I ON R.inventory_id = I.inventory_idINNER JOIN film F ON I.film_id = F.film_idGROUP BY F.film_id, CO.country_id;

Navicat Premium 16

• GROUP_CONCAT(title ORDER BY times DESC SEPARATOR '|||')
• SUBSTRING_INDEX('...', '|||', 1) |||

Navicat Premium 16

DBA SQL REPLACE()

classicmodels products

Chef Anton "" 4

1. LIKE Chef Anton
4. SELECT UPDATE

Chef Anton Products

LIKE Chef Anton Chef Anton's '% Navicat Premium 16

LEN() Chef Anton's RIGHT()

SELECT REPLACE()

CONCAT()

SELECT CONCAT(         LEFT(ProductName, LENGTH('Chef Anton\'s ')),  '"', RIGHT(ProductName, LENGTH(ProductName)-LENGTH('Chef Anton\'s ')),  '"'       ) AS product_name FROM products WHERE ProductName LIKE 'Chef Anton\'s %'; 

SELECT UPDATE

SELECT UPDATE SELECT UPDATE UPDATE

products

SQL

SELECT

12 Essentials Navicat 16 Navicat Navicat SQL SQL 2018 3 14 Navicat

Windows

DLL

TAB Count()

Navicat 16 Essentials

SQL / Navicat Premium 16

• emp_no Employee Number
• qty quantity

column_name [AS] alias_name

AS

column_name AS 'Alias Name'

SELECT column1, column2....FROM table_name [AS] alias_nameWHERE [condition];

SELECT actor_id actors film_actor

Navicat

Navicat Premium 16

Navicat Premium 16 14

Navicat 16 3 Navicat Premium 16 MySQL 1

Navicat SQL ->

MySQL

• MySQL 5.7 InnoDB MySQL NDB Cluster 7.5 NDB
• .ibd
• MySQL 1024 1 MB
• MySQL 1024 1 MB KMGTP E

SQL SQL

Navicat CREATE TABLESPACE

Navicat

classicmodels

MySQL

Navicat 16

Navicat Premium 16

"What is it? It's it" - Epic, Faith No More

Navicat 16 1 Navicat 16

Oracle

Oracle SYSTEMSYSAUXUSERSUNDOTBS1 TEMP

• SYSTEM SYSAUX
• USERS
• UNDOTBS1
• TEMP

MySQL

InnoDB

• File-Per-Table
• Undo

DB2

• Catalog
• User
• Temporary

Navicat 16

Navicat 16 Navicat 16

/

CREATE TABLESPACE

CREATE TABLESPACE tbs1    DATAFILE 'tbs1_data.dbf'    SIZE 1m;

DB2

CREATE ALTER TABLESPACE

Navicat 16 Navicat 16

SQL UPDATE

UPDATE table1, table2, ...    SET column1 = value1,        column2 = value2,        ...[WHERE conditions]

join

UPDATE MySQL 8 Navicat Premium 16

• UPDATE
• ORDER BY LIMIT

UPDATE

UPDATE librarybook UPDATE

library

book

UPDATE library l, book b    SET l.book_count = l.book_count - 2,        b.book_count = b.book_count + 2WHERE l.id = b.book_idAND b.id = '1AG';

l.id = b.book_id b.id = '1AG' '1AG'

UPDATE SET UPDATE Navicat

'1AG' 103

UPDATE MySQL 8 Navicat Premium 16 UPDATE

MySQL Sakila SELECT SELECT

SELECT    co.Country,    COUNT(DISTINCT ci.city_id) AS city_cnt,    COUNT(a.city_id)           AS address_cntFROM country coINNER JOIN city ci    ON co.country_id = ci.country_idINNER JOIN address a    ON ci.city_id = a.city_idGROUP BY    co.country_id;SELECT     Co.Country,    (Select COUNT(1)  FROM City Ci  WHERE Ci.country_id=co.country_id) AS city_cnt,    (Select COUNT(1)  FROM Address A    INNER JOIN city c on a.city_id=c.city_id  WHERE C.country_id=co.country_id) AS address_cntFrom Country Co;

Navicat SQL 2 1

0.020

0.021

Navicat

1 3 SIMPLE

2 PRIMARY DEPENDENT SUBQUERY

2020 NULL NULL NULL

NULL NULL

NULL NULL NULL

SQL

NULL NULL SQL

Navicat Premium 16 NULL

Navicat NULL

name

5 4578 10 combo_count 5 NULL length() NULL

NULL NULL

NULL DBA NULL

DBA

MySQL mysqldump Navicat

Navicat SQL Navicat Premium MySQL SQL Server MongoDB NoSQL

>

/ SQL

SQL

/

MySQL mysqldump Navicat SQL

SELECT JOIN DB MySQL JOIN LEFTINNER RIGHT LEFT RIGHT INNER JOIN OUTER JOIN

LEFT

LEFT JOIN NULL

SELECT    select_listFROM    T1LEFT JOIN T2 ON    join_predicate;

VEN LEFT JOIN T1 T2

RIGHT

RIGHT JOIN NULL

SELECT     select_listFROM     T1RIGHT JOIN T2 ON join_predicate;

VEN RIGHT JOIN T1 T2

OUTER JOIN

MySQL FULL OUTER JOIN UNION ALL LEFT JOININNER JOIN RIGHT JOIN join_column IS NULL LEFT RIGHT

SQL MySQL

LEFT WHERE NULL

RIGHT WHERE

INNERJOIN

UNION ALL OUTER JOIN

LEFT RIGHT INNER JOIN OUTER JOIN ORDER BY / LIMIT filesort

Outer Join SQL

Outer Join

OUTER JOIN

A BOUTER JOIN FULL OUTER JOIN VEN OUTER JOIN

FULL OUTER JOIN Clave

OUTER JOIN

Northwind

SQL

SELECT Customers.CustomerName, Orders.OrderIDFROM CustomersFULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerIDORDER BY Customers.CustomerName;

OUTER JOIN Null Navicat Premium 16

Null ContactName Null

SQL

SELECT     m.name member,     p.title projectFROM     pm.members m    FULL OUTER JOIN pm.projects p         ON p.id = m.project_id;

Null Null

Jack Daniel

WHERE

IT ENUM

Java

public enum Day {    SUNDAY, MONDAY, TUESDAY, WEDNESDAY,    THURSDAY, FRIDAY, SATURDAY }

URL Java

public enum Environment {    PROD("https://prod.domain.com:1088/"),     SIT("https://sit.domain.com:2019/"),     CIT("https://cit.domain.com:8080/"),     DEV("https://dev.domain.com:21323/");     private String url;     Environment(String envUrl) {        this.url = envUrl;    }     public String getUrl() {        return url;    }}

public enum Suit {   Spade,   Heart,   Diamond,   Club }

Name          Suit------------  ----John Smith    2Ian Boyd      1Name          Suit------------  -------John Smith    DiamondIan Boyd      Heart

Unknown Suit

MySQL SQL Server ENUM ENUM

Navicat for MySQL MySQL SELECT

ENUM

Navicat for MySQL 14

PK 1 2

ISO VIN ID

GUID

GUIDGUID Globally Unique IDentifier 16

GUID SQL Server NEWID()

SELECT NEWID()

UNIQUEIDENTIFIER default NEWID()

USE EngDBGO CREATE TABLE EnglishStudents1(Id UNIQUEIDENTIFIER PRIMARY KEY default NEWID(),StudentName VARCHAR (50) )GO INSERT INTO EnglishStudents1 VALUES (default,'Shane')INSERT INTO EnglishStudents1 VALUES (default,'Jonny')

EngStudents1 NEWID() Id default Id

GUIDs vs. UUIDs

GUID Microsoft UUID RFC4122 UUID

UUID 128 UUID UUIDv4 UUID UUIDv4

Microsoft GUID GUID RFC4122 GUID UUID

GUID Microsoft UUID 16 128 Microsoft GUID UUID

GUIDS UUID varchar

Navicat

• &
• &

SQL

SQL SQL

Navicat 16.1

January J

Navicat

• MariaDB 10.2.1 CHECK
• PostgreSQL

MongoDB

MongoDB

SQL

• Ctrl+F

• -
• SSH/HTTP
• DDL - DDL

OceanBase

OceanBase Navicat Navicat

MongoDB

MongoDB

• MongoDB 1.16.2 1.21.1
• MongoDB Atlas Serverless
• 1.21.1 1.16.2

1

IT CHAR VARCHARBINARYVARBINARYBLOBTEXTENUM SET INTEGERSMALLINTDECIMAL NUMERIC FLOATREAL DOUBLE PRECISION

PK PRIMARY KEY

• NULL

Mysqltutorial.org MySQL

MySQL INTBIGINT

MySQL Oracle Oracle

SSNVIN

...

PK PK PK PK PK

Navicat Premium 16

productCode

GUID customerNumber PK

PK productCode

Navicat 16 14

SQL ORM Hibernate Entity Framework

ORM

SQL ORM ad-hocSQL Frans Bouma Stored Procedures are bad, m'kay?

SQL

SQL SQL SQL

create procedure GetStudents(@School nvarchar(50))asbegin    declare @sql nvarchar(100)    set @sql = 'SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ' + @School    exec @sqlend

SQL SQL PythonTypeScript Java

String sql = "SELECT STUDENT FROM SCHOOL WHERE SCHOOL LIKE ? ";PreparedStatement prepStmt = conn.prepareStatement(sql);prepStmt.setString(1, "Waterloo%");ResultSet rs = prepStmt.executeQuery();

SQL SQL

Object Relational Mapper ORM NHibernate Entity Framework

stored procedure procStructured Query LanguageSQL

• Oracle MySQL
• / O/R ORM
• DRYDon't repeat yourself/

ORM NHibernate Entity Framework

Count() GROUP BY HAVING

Sakila DVD filmactorfilm-actor filmstore rental inventory customer_id rental Navicat Premium 16 20

customer_id num_of_films_rented

ID customer LEFT JOIN

Navicat ...

2005

5

ORDER BY num_of_films_rented DESC 2005

Count() GROUP BY HAVING /

ORDER BY

Navicat Premium 16 classicmodels orders PKorderNumber

orderNumber

2 0

PostgreSQLMySQL Oracle MOD()

SELECT * FROM table_name WHERE mod(column_name,2) = 0;

SELECT * FROM table_name WHERE mod(column_name,2) <> 0;

SQL Server MOD %

SELECT *FROM table_name where column_name % 2 = 0;

This syntax will find rows where our target column has odd values:

SELECT *FROM table_name where column_name % 2 <> 0;

SQL SQL Server classicmodels orders

SQL Server MOD %

2

SQL

HeidiSQL Navicat PremiumSQL

HeidiSQL Windows Windows 32/64 SHA1 32 64 Wine HeidiSQLWineWine Is Not an Emulator LinuxmacOS BSD POSIX Windows Wine HeidiSQL Windows 8 10 Windows 7 11 4.0 Wine HeidiSQL

Navicat Premium Windows32 64 macOS64 Linux64 Navicat Navicat

HeidiSQL MySQL MariaDB MS SQL Server PostgreSQL

Navicat Premium MySQLMariaDBMongoDBSQL ServerOraclePostgreSQL SQLite Amazon RDSAmazon AuroraAmazon RedshiftMicrosoft AzureOracle Cloud MongoDB Atlas

SQL

Navicat HeidiSQL HeidiSQL Navicat Premium

SQL HeidiSQL Navicat Premium

HeidiSQL Navicat SQL

HeidiSQL

HeidiSQL Navicat Premium 3 SQL Navicat Navicat Cloud DBeaver Navicat Navicat Premium

HeidiSQL Navicat Premium Navicat Premium MySQLMariaDBMongoDBSQL ServerOraclePostgreSQL SQLiteNavicat Amazon RDSAmazon AuroraAmazon RedshiftMicrosoft AzureOracle Cloud MongoDB Atlas HeidiSQL MySQL/MariaDB

UI/UX HeidiSQL

HeidiSQL UI

Navicat 16 GUI

Navicat DDL

HeidiSQL

• - GNU GPL
• HeidiSQL
• SSH

• HeidiSQL
• Windows
• DPI DPI DPI

Navicat Premium

• SSH SSLSecure Sockets Layer
• Navicat Navicat MonitorNavicat Data ModelerNavicat Report Viewer Navicat Data Modeler Essentials

• PostgreSQLSQL Server SQLite Navicat Premium
• It's somewhat resource-intensive as it requires fairly high memory while running.

...

3 SQL

6 DBeaver Navicat Premium 15 HeidiSQL Navicat Premium 16

Ansgar Becker 2002 Becker HeidiSQL Heidi Klum Becker Heidi MariaDBMySQLMicrosoft SQLPostgreSQL SQLite

HeidiSQL 1999 MySQL MySQL-Front2004 Becker MySQL-Front Nils Hoyer2006 4 Becker SourceForge HeidiSQL

• 2011 3 7.0 Microsoft SQL Server
• 2014 3 9.0 PostgreSQL
• 2020 3 11.0 SQLite

HeidiSQL MariaDB MySQL 8.0 HeidiSQL 22

Navicat Premium Navicat /Navicat Premium

Navicat Ken Lin 2001 MySQL 2008 Navicat for MySQL ICT 2008 Navicat Premium 2009 Navicat

CSV Comma-Separated ValuesXML JSON CSV Microsoft Excel Google MySQL CSV

MySQL CSV CSV

secure-file-priv MySQL LOAD DATA SELECT INTO FILE MySQL

SHOW VARIABLES LIKE "secure_file_priv"  

TABLE tableName INTO OUTFILE 'path/outputFile.csv'FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'ESCAPED BY ''LINES TERMINATED BY '\n';

SELECT

SELECT columnName, ...FROM tableNameWHERE columnName = 'value'LIMIT 1000INTO OUTFILE 'path/outputFile.csv'FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'ESCAPED BY ''LINES TERMINATED BY '\n';

UNION

(SELECT 'columnHeading', ...)UNION(SELECT column, ...FROM tableNameINTO OUTFILE 'path-to-file/outputFile.csv'FIELDS ENCLOSED BY '"' TERMINATED BY ','ESCAPED BY '"'LINES TERMINATED BY '\n')

mysqldump

mysqldump MySQL

mysqldump -u [username] -p -t -T/path/to/directory [database] [tableName] --fields-terminated-by=,

Navicat

Navicat 16 for MySQL .xlsx.json .sql ->

Navicat

CSV CSV DBA

UPDATE UPDATE Data Manipulation LanguageDML

INSERTUPDATE DELETE

DB

explaindescribe

explain update ...;

Navicat EXPLAIN

autocommit COMMIT ROLLBACK

MySQL

SET autocommit=0OrSET autocommit = OFF

SQL Server

SET IMPLICIT_TRANSACTIONS OFF

-- 1. start a new transactionSTART TRANSACTION;-- 2. insert a new order for customer 145INSERT INTO orders(orderNumber,                   orderDate,                   requiredDate,                   shippedDate,                   status,                   customerNumber)VALUES(@orderNumber,       '2005-05-31',       '2005-06-10',       '2005-06-11',       'In Process',        145);        -- 3. then, after evaluating the results,--    rollback the changesROLLBACK;

SELECT

DML SELECT SELECT SELECT

INSERT INTO orders...BECOMESSELECT * FROM ORDERS...

DML

Navicat 16 14

for all the rows in outer table   for all the rows in the inner table     if outer_row and inner row satisfy the join condition       emit the rows   next inner next outer

nested-loop-over-index

for all the rows that pass the filter from the outer table   use join qualifier from outer table row on index on inner table     if row found using index lookup       emit the rows next outer 

/

FROM  Table1 [ join type ] JOIN Table2                 ON condition2 [ join type ] JOIN Table3                 ON condition3 

SQL ANSI

FROM  Table1 [ join type ] JOIN Table2 [ join type ] JOIN Table3                 ON condition3                 ON condition2 

FROM  Table1 [ join type ] JOIN ( Table2                     [ join type ] JOIN Table3                                     ON condition3 )                 ON condition2 

Table2 Table3 Table1 Table2 Table3 Table2 Table3

Navicat Premium

SQL Monty Python DBMT DBMT DBA DBMT

DBMS

DBMS IT PostgreSQL Amazon Amazon Amazon

DBMS DBMS Navicat Premium DBMS PC

DBMT DBMT

SSH SSH SSH

DBMT MySQL MariaDB PAM MongoDB Kerberos X.509 PostgreSQL GSSAPI Navicat

Navicat Navicat Cloud Amazon Simple Storage ServiceAmazon S3256 AES WindowsmacOSLinux iOS Navicat Cloud Navicat

Navicat Premium

3 SQL SQL

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT

FROM WHERE GROUP BY

WHERE

WHERE a=1 AND b=2 WHERE a=1

accounts SELECT Navicat 16

account_number account_number

account_number 50

Conversion failed when converting the varchar value 'ACFB' to data type int

CAST(account_number AS UNSIGNED INTEGER) > 50account_type LIKE 'Business%'

• CASE INTEGER

  

SQL SQL /

SQL SQL / SQL

SQL SELECTFROM WHERE

SELECT Navicat Premium 16 Sakila ALONE TRIP

ALONE TRIP= film_id

IN() actor_id

SELECT Navicat Data Modeler products factories

products factories sku

factory_id factory_id

factory

JOIN

...

column = (SELECT value FROM Table) IN() =

Outer JoinCartesian Product

SQL SQL / SQL

• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

2005 6 customers orders

SELECT C.customerName, count(O.customerNumber) AS 2005_ordersFROM customers AS CLEFT OUTER JOIN orders AS O  ON C.customerNumber = O.customerNumberWHERE O.orderDate >= '2005-05-01'GROUP BY C.customerNameORDER BY 2005_orders DESC;

Navicat Premium 16 13 122

customerNumbers NULL orders

WHERE

WHERE

WHERE

WHERE WHERE

SQL SQL SQL 3

TRUEFALSE UNKNOWN WHERE HAVING FROM

• LIKE
• BETWEEN
• IN
• EXISTS
• IS NULL (/INTEGER/DECIMAL/FLOAT...)

WHERE employee_salary > 100000 TRUEFALSE UNKNOWN

• =
• >
• <
• >=
• <=
• <>

expression_1 comparison_operator expression_2

expression2 FALSE

LIKE

SQL LIKE LIKE

BETWEEN

BETWEEN income BETWEEN 5000 AND 20000 5000 20000BETWEEN

IN

IN IN TRUE

EXISTS

EXISTS TRUE FALSE

IS NULL

IS NULL NULL = NULL

IS NULL

IS [NOT] NULL

x NULL x IS NULL TRUE

BOOLEAN IS UNKNOWN IS NULL

IS NOT NULL NULL

SQL WHERE HAVING FROM TRUEFALSE UNKNOWN SQL

2 SARGable

SQL SQL SQL

SQL SQL

varchar customerName

R LEFT() customerName

customerName

SARGable SARGable

Search ARGument ABLE SARGable DBMS SARGable SARGable SARGable SARGable

SARGable Like SARGable

SQL SARGable SARGable

Navicat 16 for MySQL 14

NOT IN NOT EXISTS

anti-pattern Andrew Koenig 1995

SQL

SQL MySQL SQL

NOT IN NOT EXISTS

SELECT Navicat for MySQL 16

NOT IN

blackgreen

products color NULL NOT IN

color NOT IN (Red, Blue, NULL)

NOT(color=Red OR color=Blue OR color=NULL)

color=NULL UNKNOWNNOT UNKNOWN UNKNOWN

NULL NULL NULL NULL

EXISTS IN

IN EXISTS IN SQL EXISTSSQL

SQL SELECT NOT IN

Navicat 16 for MySQL 14

MySQL SELECT

Datetime

DateTime Timestamp Sakila customer create_date Datetime

DATE() Datetime

MySQL DATEDIFF() Navicat for MySQL 16

DATEDIFF() 10

7

ROUND(DATEDIFF(end_date, start_date)/7, 0) AS weeksout

TIMESTAMPDIFF() TIMESTAMP DATETIME DATE MySQL MONTH

SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04')-- Outputs: 0SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05')-- Outputs: 1SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15')-- Outputs: 1SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16')-- Outputs: 7

DATEDIFF() DATEDIFF()

DATEDIFF() AVG() 1

• MySQL
• MySQL

MySQL MySQL

MySQL MySQL

MAKEDATE()

3 MAKEDATE() year dayofyearMAKEDATE(2021, 200) 2021-07-19 yearmonth day dayofyear MAKEDATE() DATE_ADD() DATEday 1 MAKEDATE() DATE DATE_ADD() year month

SELECT

MAKETIME()

TIME MAKETIME()

STR_TO_DATE()

DATETIME DATETIME STR_TO_DATE()

• DATE
• TIME
• DATETIME

str STR_TO_DATE() NULL

Navicat for MySQL 16

str str

0 0

MySQL DATE_FORMAT()

MAKEDATE()MAKETIME() STR_TO_DATE()

DATE TIME MAKEDATE() MAKETIME() STR_TO_DATE() DATETIME

MySQL MySQL MySQL SELECT

MySQL MySQL

2021 5 SQL Server GETDATE() MySQL NOW() Navicat for MySQL 16

2 TIMESTAMP DATETIME TIMESTAMP current_timestamp()

MySQL curdate() current_date() current_date YYYY-MM-DD

curtime() current_time() current_time MySQL HH:MM:SS

SQL Server DATEPART() MySQL EXTRACT() SQL Server EXTRACT() part date

EXTRACT(part FROM date)

part

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

2 EXTRACT() 2

43

part MySQL

DATE() TIME()

YEAR()MONTH() DAYOFMONTH() DAY()

HOUR()MINUTE() SECOND()

HOUR()MINUTE() SECOND()

MySQL MAKEDATE() year dayofyear

MySQL MySQL

TIMESTAMP YEAR

MySQL MySQL 1 DATETIME DATETIME TIMESTAMP YEAR

TIMESTAMP

TIMESTAMP MySQL DATETIME MySQL DATETIME TIMESTAMP 2009-11-01 14:35:001970-01-01 00:00:00 UTC 1248761460

TIMESTAMP 4 DATETIME 5 TIMESTAMP 14 TIMESTAMP(2)

TIMESTAMP

• TIMESTAMP(14): YYYY-MM-DD HH:MM:SS
• TIMESTAMP(12): YY-MM-DD HH:MM:SS
• TIMESTAMP(10): YY-MM-DD HH:MM
• TIMESTAMP(8): YYYY-MM-DD
• TIMESTAMP(6): YY-MM-DD
• TIMESTAMP(4): YY-MM
• TIMESTAMP(2): YY

Navicat 16

Navicat TIMESTAMP(14)

YEAR

DBA Interger MySQL YEAR YEAR 1 YEAR(2) YEAR(4) YEAR(4) YEAR(2)

• 4 MySQL YYYY YEAR 1901 2155 0000
• 2 MySQL 70 1970 2070 69 2069

Navicat

MySQL

DATETIME DATETIME

MySQL yyyy-mm-dd mm-dd-yyyy DATE_FORMAT MySQL DATETIME DATETIME MySQL

MySQL

DATE

MySQL 3 DATE DATE 1000-01-01 9999-12-31Strict ModeMySQL 2015-02-30 0000-00-00 Navicat 16 DATE

DATE

INSERT DATE

TIME

MySQL HH:MM:SS 24 MySQL 24 HHH:MM:SS

Navicat 16 TIME

Navicat TIME INPUT TIME

INSERT

DATETIME

MySQL DATETIME DATETIME 1000-01-01 00:00:00 9999-12-31 23:59:59 DATETIME MySQL YYYY-MM-DD HH:MM:SS DATETIME

DATETIME 5 DATETIME YYYY-MM-DD HH:MM:SS[.fraction] 2015-12-20 10:01:00.999999

DATETIME Navicat DATETIME INPUT DATE TIME

DATETIME T DATETIME

DATETIME DATETIME TIMESTAMP YEAR

2021 5 SQL Server SQL Server MySQL Navicat 16 for MySQL

SQL Server MySQL

SQL Server AbsRoundCeiling Floor MySQL

DBMS MySQL

AVG

45653

(4 + 5 + 6 + 5 + 3) / 5 = 4.6

10,000 MySQL AVG SQL Server

AVG(expression)

Sakila

GROUP BY category_id ActionDrama

AVG ROUND

17

( AVG( replacement_cost ) - AVG( rental_rate ) ) AS replace_sub_rental

MINMAX

rental_date MIN MAX

MIN MAX rent_dates Film rental_dates

Navicat 16 for MySQL MySQL AVGMIN MAX Navicat 16 for MySQL 14

Navicat 16 Navicat 16 MySQL

DB MySQL classicmodels Navicat Premium 16

Navicat

Navicat

• classicmodels_test

• 

  ...

• 

• ->...
• classicmodels_test

  

• Navicat

  

• SQL

  

• 

Navicat 16

Navicat 16

Navicat 16 14

Navicat 16 Navicat 16 Navicat Charts Creator Navicat Charts Creator Navicat Charts Creator

Navicat Charts Creator Navicat Charts Creator

-> ->

ODBC

• ODBC

  

+

Navicat Charts Creator 17

Navicat Charts Creator Navicat Charts Creator Charts Creator 14

Navicat Premium 16

count() Group By

SQL

• OVER()
• CTE

OVER()

OVER

OVER SQL

count(*) * 100.0 / sum(count(*)) over()

SQL

over()

OVER() Round()

count(*) * 100.0 / (select count(*) from <YourTable>)

CTE

With common_table_expression CTE sum()

CTE CTE

Navicat 16 Navicat 14

SQL SQL Exclusive OR XOR

OR XOR

XOR

MySQL XOR 2020 1 1

Navicat Premium 16 Sakila

2020-07-07 store_id 2 store_id 1

XOR OR 1 2020-01-01

TRUE OR XOR

XOR

XOR x XOR y

(x AND (NOT y)) OR ((NOT x) AND y)

SQL

(A OR B) AND NOT (A AND B)

SQL Server SQL Server XOR

XOR

WHERE   (ci.city = 'Lethbridge' OR c.create_date  > '2020-01-01')AND NOT (ci.city = 'Lethbridge' AND c.create_date > '2020-01-01')

SQL Server

XOR

Navicat 16 14

Navicat 16 Navicat UI

Navicat 16

• 

  

• + ->

Navicat 16

• null

classicmodels MySQL orders

SQL SQL

[...]

DML UPDATEINSERT INTODELETE CREATE

Navicat 16 Navicat Navicat

Navicat 16 Navicat 14

Navicat 16 Navicat Charts Creator Navicat Navicat 15 Navicat 16 Navicat Navicat 16

Navicat Charts Creator

• MySQLMariaDBPostgreSQLOracleSQLite SQL Server

• ExcelAccessCSV

• ODBC ODBC SybaseSnowflake DB2

• 

Navicat 16

• KPI

Navicat 16

Navicat 16

Navicat 16 Navicat Charts Creator

Navicat 16 Navicat 14

Navicat Navicat Cloud IT 2021 Navicat Navicat 16 Navicat Cloud Navicat On-Prem Server Navicat 16

Navicat Cloud

Navicat Cloud Navicat Navicat Cloud Navicat Cloud Navicat Cloud Navicat Cloud Navicat Cloud

Navicat 16 Navicat Cloud On-Prem Server Navicat

Navicat On-Prem Server

Navicat On-Prem Server Navicat Navicat On-Prem

Navicat Navicat On-Prem

Navicat On-Prem Server

Navicat 16

Navicat Cloud Navicat On-Prem Server Navicat WindowsmacOS Linux

Navicat 16

• On-Prem Server
• UI/UX

Windows Navicat Premium 16

SQLite chinook

SQLite

Chinook artistsalbumsmedia tracksinvoices customers Navicat Premium 16

...

Navicat 1000

Navicat

Navicat Artist.ArtistId UNIQUE

SQLite Chinook Navicat 16

Navicat 16 14

Booleantrue falseNULL NULL SQLNULL E. F. Codd SQL NULL /NULL MySQL PostgreSQL SQL Server Oracle

Enumerated Type EnumEnum Enum MySQL PostgreSQL Enum SQL Server Oracle

MySQL Enum

Enum MySQL CREATE TABLE Enum

CREATE TABLE shirts (  name VARCHAR(40),  size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));

From there, you can refer to an Enum using one of its string values:

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'),        ('t-shirt','medium'),       ('polo shirt','small');  SELECT name, size FROM shirts WHERE size = 'medium';UPDATE shirts SET size = 'small' WHERE size = 'large';

CREATE TABLE employee (  name VARCHAR(50),  security_clearance ENUM('enhanced', 'secret', 'none'));

Enum

PostgreSQL Enum

PostgreSQL Enum CREATE TYPE

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Enum

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');CREATE TABLE person (    name text,    current_mood mood);INSERT INTO person VALUES ('Moe', 'happy');SELECT * FROM person WHERE current_mood = 'happy'; name | current_mood ------+-------------- Moe  | happy(1 row)

MySQL PostgreSQL

INSERT UPDATE

10,000 1 shell

COMMIT

test123password123

......

SQL Server charvarcharSQL Server Unicode UnicodeUnicode ncharnvarchar ntext Unicode charvarchar/varchar (max) text

Unicode Unicode

Nchar NATIONAL CHARACTERnvarchar NATIONAL CHARACTER VARYINGntext NATIONAL TEXT ISO Unicode JIS VARCHAR ASCII NVARCHAR ASCII

Unicode GB JIS/SJIS BIG5 CNS Unicode VARCHAR UTF-8 NATIONAL CHARACTER

NVARCHARNATIONAL CHARACTER VARYING VARCHAR SQL Server

varchar nvarchar varchar 8 1 nvarchar 2 nvarchar 4000 SQL varchar

nvarchar 2 varchar 1 nvarchar(4000) varchar(8000) UNICODE nvarchar varchar ASCII VARCHAR NVARCHAR

SQL Server Unicode Unicode

SELECT WHERE 1=1 SQL WHERE TRUE WHERE WHERE 1=1

WHERE 1=1

WHERE 1=1 Navicat WHERE 1=1 SELECT

Sakila Lethbridge

0.004

WHERE 1=1

0.004 WHERE 1=1

...

WHERE 1=1 SQL

SQL

WHERE 1=1 AND

SQL

SQL WHERE 1=1 and customer.id=:custId IDand ...

stmt  = "SELECT * "stmt += "FROM TABLE "stmt += "WHERE 1=1 "if user chooses option a then stmt += "and A is not null "if user chooses option b then stmt += "and B is not null "if user chooses option b then stmt += "and C is not null "if user chooses option b then stmt += "and D is not null "

WHERE 1=1