Info about running some SQL statements.
I'm using adminer as administration view of my DB's http://adminer:8088/
CREATE DATABASE `web_app_db` COLLATE 'utf8_general_ci';
then you can use the DB
use web_app_db;
CREATE DATABASE pagila WITH TEMPLATE = template0 ENCODING = 'UTF8'
then you can use the DB
\c web_app_db;
SELECT PASSWORD('webappuser');
CREATE USER 'webappuser'@'%' IDENTIFIED BY PASSWORD '*CD072209A54E1593E96681B097425DB5A5A453DD';
GRANT ALL PRIVILEGES ON web_app_db.* TO 'webappuser'@'%';
GRANT GRANT OPTION ON `web_app_db`.* TO 'webappuser'@'%';
DO $$
BEGIN
CREATE USER myusers_in_lovercase WITH
LOGIN
ENCRYPTED PASSWORD 'mysupersimplepassword';
EXCEPTION WHEN duplicate_object THEN
RAISE NOTICE 'DID NOT CREATE USER replica -- it already exists';
END
$$;
Selecting and viewing data in your tables
select * from app_events_event limit 100;
#-Or
SELECT * FROM customer WHERE first_name like '%th%' LIMIT 50;
SELECT * FROM customer WHERE first_name = last_name;
#-Or
SELECT * FROM customer WHERE first_name not like '%a%' LIMIT 50;
Counting how many rows/data in your table
select count(*) from app_events_event;
find avarage rate
select AVG(rental_rate) from film limit 50;
select film_id, title, rental_rate from film where rental_rate > 2.98;
Using subqueries from the above statement
select film_id, title, rental_rate
from film where rental_rate > (
select avg(rental_rate) from film);
Order by
select film_id from inventory
ORDER BY film_id ASC limit 100;
Remove duplicates
select DISTINCT(film_id) from inventory
ORDER BY film_id ASC limit 100;
Count payments by staff_id
SELECT staff_id,
COUNT(payment_id) FROM payment
GROUP BY staff_id;
Having clause
SELECT customer_id,
SUM(amount) FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 200;
SELECT
datname
FROM
pg_database;
#---------------only the chosen one--------
SELECT
*
FROM
pg_database
WHERE datname='avs';
\dn
OR
select nspname
from pg_catalog.pg_namespace;
SELECT
*
FROM
pg_catalog.pg_tables
WHERE
schemaname != 'pg_catalog'
AND schemaname != 'information_schema';
-OR-
\dt
Get more details about a single table:
SELECT
column_name,data_type, column_default
FROM
information_schema.COLUMNS
WHERE
TABLE_NAME = 'payment';
-OR-
\d "paymentTable"
USE [my_database];
GO
select schema_name(t.schema_id) as schema_name,
t.name as table_name,
t.create_date,
t.modify_date
from sys.tables t
order by schema_name,
table_name;
GO
show tables;
| Tables_in_web_app_db |
|---|
| app_events_event |
| auth_group |
| auth_group_permissions |
| auth_permission |
| auth_user |
| auth_usergroups |
| auth_user_user_permissions |
| django_admin_log |
| django_content_type |
| django_migrations |
| django_session |
get more info about a single table
desc app_events_event
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | NULL | auto_increment |
| slug | varchar(50) | NO | UNI | NULL | |
| title | varchar(255) | NO | NULL | ||
| description | longtext | NO | NULL | ||
| date | date | NO | NULL | ||
| number_of_participants | int(10) | unsigned | NO | NULL |
CREATE TABLE `lc_limit_rule` (
`tnt_inst_id` varchar(8) NOT NULL COMMENT 'The tenantID',
`rule_id` varchar(64) NOT NULL COMMENT 'The rulesID',
`expression` varchar(2048) NOT NULL COMMENT 'Match the current limit rules of dynamic business expression',
`amount_range` varchar(256) DEFAULT NULL COMMENT 'Credit amount range',
`count_range` varchar(256) DEFAULT NULL COMMENT 'Line frequency range',
`cumulate_code` varchar(256) DEFAULT NULL COMMENT 'Tired of code',
`cumulate_properties` varchar(256) DEFAULT NULL COMMENT 'Audit the dimension',
`biz_amount_formula` varchar(256) DEFAULT NULL COMMENT 'Calculation expression',
`overlimit_name` varchar(64) DEFAULT NULL COMMENT 'Transfinite template name,The correspondinglc_limit_cfg_overlimitIn the tablename',
`env` varchar(16) NOT NULL COMMENT 'Pre-release or release',
`status` varchar(16) NOT NULL COMMENT 'Effective or ineffective',
`version` varchar(16) NOT NULL COMMENT 'Any changes will add a version number',
`gmt_modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Modify the time',
`gmt_create` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Creation time',
`memo` varchar(512) DEFAULT NULL COMMENT 'Rules of the note',
`group_id` bigint DEFAULT NULL COMMENT 'Publishing group,The reserved field',
`operator` varchar(64) DEFAULT NULL COMMENT 'operator',
`threshold` varchar(256) DEFAULT NULL COMMENT 'threshold',
PRIMARY KEY (`tnt_inst_id`,`rule_id`,`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='snappy_1.0'
Another Example postgresql:
CREATE TABLE customers (
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
username varchar(200) NOT NULL CHECK (username <> ''),
birthdate date DEFAULT NULL,
city varchar(100) NULL
);
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;