SQL · Theory

Connection

Theory

7/439

Connection

A connection is a login to the server: host + port + user + password + database. Default host is 127.0.0.1 (this computer), port 3306. From PHP/Java/Node you pass the same four things in a connection string or DSN.

CLI: mysql -u asha -p -h 127.0.0.1 college. Workbench: New Connection, same fields. If it fails, the error is usually wrong password, server not running, or user not allowed from that host.

Apps should use a normal user with rights only on that database — not root. Close the connection when the request ends (or use a pool). Leaving 500 open connections hits ‘too many connections’.

Trap: ‘localhost’ vs 127.0.0.1 can use a socket vs TCP on Linux — different user grants. If one works and the other doesn’t, check mysql.user host column.

On the example next to this theory: Connection: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Four fields: host, user, password, database. Port 3306.

Connection — sample query

-- Connection
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Connection: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

Defhost, port 3306, user, password, database.
Rulemysql -u user -p -h 127.0.0.1 dbname
TrapUsing root from the app. too many connections.

SQL · Theory

Connection

← All stacks

Theory

7/439

Connection

CLI: mysql -u asha -p -h 127.0.0.1 college. Workbench: New Connection, same fields. If it fails, the error is usually wrong password, server not running, or user not allowed from that host.

Apps should use a normal user with rights only on that database — not root. Close the connection when the request ends (or use a pool). Leaving 500 open connections hits ‘too many connections’.

Trap: ‘localhost’ vs 127.0.0.1 can use a socket vs TCP on Linux — different user grants. If one works and the other doesn’t, check mysql.user host column.

On the example next to this theory: Connection: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Four fields: host, user, password, database. Port 3306.

Connection — sample query

-- Connection
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Connection: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

Defhost, port 3306, user, password, database.
Rulemysql -u user -p -h 127.0.0.1 dbname
TrapUsing root from the app. too many connections.

Connection

Connection — sample query

Short notes

Questions

Connection

Connection — sample query

Short notes

Questions