Författare:
Per Stenebo
Skapad:
2011-12-16 15:25:32
Ändrad:
2019-10-07 09:46:38
sv

SQL

General guidelines

IBM; Five common PHP database problems

MySQL; database normalization

databasteknik.se -webbkurs -ER

General tips

An inner join discards all rows from the result table that don’t have corresponding rows in
both source tables.

An outer join preserves unmatched rows.

Outer joins come in three types: The left outer join, the right outer join, and the full outer join.

In a query that includes a join, the left table is the one that precedes the keyword JOIN, and the right table is the one that follows it. The left outer join preserves unmatched rows from the left table but discards unmatched rows from the right table.

You can abbreviate the left outer join language as LEFT JOIN because there’s no such thing as a left inner join.

MySQL

External resources

MySQL .com | .se | dev |
Docs | 5.7 | 5.5 Replication | data types |

MySQL manual: End-User Guidelines for Password Security

Tutorials: | php.net/mysql |

Live Webinars:
http://www.mysql.com/news-and-events/web-seminars/
http://dev.mysql.com/news-and-events/web-seminars

On-Demand Webinars:
http://www.mysql.com/news-and-events/on-demand-webinars/

innoDB FOREIGN KEY Constraints: | 5.1 |

Artiklar: | techrepublic |

Internal resources

| Ubuntu/MySQL | PHP/MySQL |

Referera alias

SELECT 2-1 AS one, 1+1 AS two, (2-1)+(1+1) AS three OK: 1, 2 ,3

SELECT 2-1 AS one, 1+1 AS two, one+two AS three
#1054 - Unknown column 'one' in 'field list'

SELECT 2-1 AS one, 1+1 AS two, `one`+`two` AS three 
#1054 - Unknown column 'one' in 'field list'

SELECT 2-1 AS 'one', 1+1 AS 'two', `one`+`two` AS three 
#1054 - Unknown column 'one' in 'field list'

Det verkar inte vara möjligt att referera beräknade alias i (samma?) SELECT.

MySQL terminal

MySQL Terminalkommandon

SME Server: Logga in till klienten mysql (som root):

mysql

Logga in till klienten mysql (övriga):

mysql --user="username" --password="password"

eller:

mysql -u root -p

Lista databaser:

SHOW databases;

Skapa databas:

CREATE DATABASE dbName CHARACTER SET utf8 COLLATE utf8_swedish_ci;

Radera databas:

DROP database dbName;

Byt databas:

USE dbName;

Lista tabeller i aktuell databas: SHOW TABLES;

Skapa användare och lösenord med lokal tillgång till en databas:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON dbName.*
TO 'username'@'localhost'
IDENTIFIED BY 'password';

Radera användare:

DROP USER 'username'@'localhost';

Ladda in kommandon från fil:

source /root/filename.sql;

Säkerhetskopiera databas till dumpfil. Lägg till växeln -d för att bara dumpa strukturen:

mysqldump -h localhost -u username -p"password" dbName > dumpfile.sql;

Säkerhetskopiera en tabell i en databas:

mysqldump dbName tablename > dumpfile.sql;

Säkerhetskopiera med options:

mysqldump -h localhost -u myuser -p"mypass" --hex-blob --where="colname = 0 AND ts > '2015-09-29 06:00:00'" dbName tblTest > tblTest_20150929.sql;

Återställa tabell (tom/full/finns ej):

mysql dbName < tblTest.sql;

Återställa databas från dumpfil:

mysql -h localhost -u username -p"password" dbname < dumpfile.sql

Kopiera data från en kolumn till en annan i samma tabell:

UPDATE mytable SET targetCol=sourceCol WHERE targetCol LIKE 0 AND sourceCol NOT LIKE 0;

Uppdatera tidstämpel, typ DATETIME, 1 timme tillbaka:

UPDATE mytable SET ts = SUBTIME(ts, '01:00:00') WHERE id BETWEEN 457042 AND 457360;

Alternativ metod:

UPDATE mytable SET ts = DATE_ADD(ts, INTERVAL -1 HOUR) WHERE id BETWEEN 520291 AND 520669;

 

Avsluta klienten mysql med:

\q eller quit;

 

MSSQL

| Wikipedia | | | | | |

SQL Server 2005 Express Edition: | Hem | MSDN | Tutor | |

Data types: | 1 | msdn | T-SQL | |

Foreign key: | msdn | | |

Transact-SQL

. MSDN tutorial .

CREATE DATABASE TestData
GO

USE TestData
GO

CREATE TABLE dbo.Products
   (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription text NULL)
GO

INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50
GO

 

SQuirreL

| Wikipedia | Hemsida |

sudo java -jar squirrel-sql-3.0.1-install.jar

Default install path: /usr/local/SQuirreL SQL Client

Om inte startikoner skapades automatisk, skapa programstartare med sökväg till squirrel-sql.sh:

"/usr/local/SQuirreL SQL Client/squirrel-sql.sh"

Drivrutiner

| MySQL | MS SQL Server |

Ladda ner och packa upp drivrutinen.
Öppna SQuirreL, fliken drivers, Extra class path. Leta rätt på drivern med knappen Add.

Left Join

SELECT a.DispatchID, a.TimecodeID, a.OrderNo, a.ItemNo, a.PlanNr, a.ManNr, a.Sortnr, a.Comment,
tblTimecode.Code, tblPricegroup.Price, tblItem.LocalName, tblItem.BuarName
FROM tblDispatch AS a
LEFT JOIN tblTimecode ON a.TimecodeID = tblTimecode.id
LEFT JOIN tblPricegroup ON a.OrderNo = tblPricegroup.OrderNo
LEFT JOIN tblItem ON a.ItemNo = tblItem.ItemNumber
WHERE a.ResourceID = '$ResourceID_set'
AND a.Shift = '$Shift_set'
AND a.PlanDate = '$PlanDate_set'
AND a.Sortnr BETWEEN '$sortnr_start' AND '$Sortnr_end'
ORDER BY a.Sortnr $Order_set
LIMIT 0 , 100 ";

MySQL

Konfigurera rättigheterna för aktuell MySQL-användare att tillåta kontakt utanför servern (% = överallt).

SQL Server

Tillåt extern kontakt i Surface Area Configuration.

Kontaktproblem: | Web moshpit | meetandplay |

Autentisering: | kb 322336 |

Kommentarer till sidan SQL