Links
TextQL - Allows you to easily execute SQL against structured text like CSV or TSV
arctype - The SQL client and database management tool that's fun to use
SQL Syntax and PDO Operations
Overview of SQL Commands and PDO Operations
Create Database
CREATE DATABASE IF NOT EXISTS database_name
Drop Database
DROP DATABASE IF EXISTS database_name
Create Table
CREATE TABLE IF NOT EXISTS table_name (
column_a Datatype Constraints,
column_b Datatype Constraints,
);
Datatypes
Constraints
Table vorhanden
SELECT EXISTS (
SELECT
TABLE_NAME
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA LIKE 'serienguide' AND
TABLE_TYPE LIKE 'BASE TABLE' AND
TABLE_NAME = 'watched_history'
) AS vorhanden;
Alter Table
ALTER TABLE table_a
ADD column_a DataType
ALTER COLUMN column_a DataType
DROP column_a
RENAME TO table_b
Drop Table
DROP TABLE IF EXISTS table_name
Select Rows
SELECT *, column_a, column_b, AggregateFunction(column_a)
AS Alias
FROM table_a
JOIN table_b
ON table_a.column_a = table_b.column_a
WHERE Condition
AND Condition
OR Condition
NOT Condition
GROUP BY column_a
HAVING Condition
ORDER BY column_a
ASC
DESC
LIMIT Count
OFFSET Count
Select Distint Rows
SELECT DISTINCT column_name
FROM table_name
Joins
Aggregate Functions
Conditions
Insert Rows
INSERT INTO table_name (column_a, column_b)
VALUES ("value_1", "value_2")
Update Rows
UPDATE table_name
SET column_a = "value_1"
column_b = "value_2"
WHERE Condition
Delete Rows
DELETE FROM table_name
WHERE Condition
PDO
Open Connection
$host = 'localhost';
$username = 'root';
$password = 'root';
$dbname = 'pdo';
$dsn = "mysql:host=$host;dbname=$dbname";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false
];
$connection = new PDO($dsn, $username, $password, $options);
Datatypes
Select Rows
$sql = "SELECT *
FROM users
WHERE location = :location";
$location = 'Chicago';
$statement = $connection->prepare($sql);
$statement->bindParam(':location', $location, PDO::PARAM_STR);
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
echo $row['location'];
}
Insert Row
$sql = "INSERT INTO users (username, email)
VALUES (:username, :email)";
$username = 'Tania';
$email = 'tania@example.com';
$statement = $connection->$prepare($sql);
$statement->bindValue(':username', $username, PDO::PARAM_STR);
$statement->bindValue(':email', $email, PDO::PARAM_STR);
$insert = $statement->execute();
Update Row
$user = [
'username' => 'Tania',
'email' => 'tania@example.com',
'location' => 'Chicago',
];
$sql = "UPDATE users
SET username = :username,
email = :email,
location = :location,
WHERE id = :id";
$statement = $connection->prepare($sql);
$statement->execute($user);
Delete Row
$sql = "DELETE FROM users
WHERE id = :id";
$statement = $connection->prepare($sql);
$statement->bindValue(':id', 5, PDO::PARAM_INT);
$delete = $statement->execute();
MySQL Dump
MySQL Dump mit praxisnahen Beispielen einfach erklärt
Export
mysqldump -uroot -p serienguide > serienguide.sql
Import
mysql -uroot -p serienguide < serienguide.sql
Prefix for all tables
How to add prefix of all tables in mysql
SELECT
concat('ALTER TABLE ',db,'.',tb,' RENAME ',db,'.',prfx,tb,';')
FROM
(SELECT table_schema db,table_name tb
FROM information_schema.tables WHERE
table_schema='serienguide') A,
(SELECT 'legacy_' prfx) B;