SQL

sql.md

SQL

Transact SQL.

[TOC]

Ustvarjanje baze

CREATE DATABASE

CREATE DATABASE database_name
GO

CREATE TABLE

CREATE TABLE table_name
(
	column_name1 data_type(size) constraint_name,
	column_name2 data_type(size) constraint_name,
	column_name3 data_type(size) constraint_name,
)

• Constraint ne rabimo napisati vedno

Constraints

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value for a column

FOREIGN KEY CONSTRAINT

CREATE TABLE table_name
(
	column_name1 data_type(size) PRIMARY KEY,
	column_name2 data_type(size) constraint_name,
	column_name3 data_type(size) FOREIGN KEY REFERENCES table_name(column_name),
)

Uporabimo "FOREIGN KEY REFERENCES table_name(column_name)"

Lahko tudi:

ALTER TABLE table_name ADD FOREIGN KEY (on_column) REFERENCES table_name(column_name)

--OR

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (on_column) REFERENCES table_name(column_name)

USE

USE table_name
GO

LIKE

WHERE column LIKE 'nekaj'

• % -kateri koli niz znakov
• _ -nek znak
• [a,b,c] -eden izmed teh znakov
  • [a-p] -katera koli črka od a do p

Stik

INNER JOIN Notranji stik

FROM table1_name INNER JOIN table2_name
	ON table1_name.id = table2_name.id

LEFT-RIGHT JOIN Zunanji stik

FROM table1_name [LEFT, RIGHT] JOIN table2_name
	ON table1_name.column = table2_name.column

Vse iz Leve-Desne tabele in pripadajoče iz nasprotne.

CROSS JOIN

FROM table1_name CROSS JOIN table2_name
--OR
FROM table1_name, table2_name

Kartezijski produkt -vse kombinacije.

UPDATE

Posodobi polje

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

DELETE

DELETE FROM table_name
WHERE some_column=some_value;

INSERT INTO

INSERT INTO table_name (column1, column2, ...)
	VALUES (value1, value2, ...)

Dodaj samo določene stolpce.

INSERT INTO table_name
	VALUES (value1, value2,...)

Dodaš vse stolpce

Gnezdene poizvedbe

WHERE column = (SELECT * FROM table_name)

Gnezdeno poizvedbo damo v oklepaj.

IN

WHERE column IN (value1, value2,...)

column more biti ena izmed vrednosti

ANY

WHERE column <= ANY (10, 20, 30)

Vsaj ena vrednost mora zadostiti primerjavi. Column mora biti manjši ali enak od vsaj ene od vrednosti.

ALL

WHERE column <> ALL (value1, value2,...)

Če vse vrednosti zadostijo primerjavi, ali pa notranja poizvedba (kar v oklepaju) ne vrne nobene vrednosti

EXISTS

WHERE EXISTS (SELECT * FROM table_name)

true če notranja poizvedba vrne vsaj eno vrstico

Poizvedba v FROM delu

FROM table1_name JOIN
	(SELECT * FROM table) AS 'ime'

Naredimo začasno tabelo in jo poimenujemo.

Poimenovanje stolpcev

SELECT ime AS 'Moje ime'

Stolpec ime bi imenovan Moje ime

Združevanje stolpcev

SELECT column1 + ' ' + column2

Konstante

SELECT 'ime:', column

Vse vrednosti v stolpcu bodo imele vrednost 'ime:'

__	column
ime:	Janez
ime:	Janko

Izračuni

SELECT column * 5

CAST-CONVERT pretvarjanje tipov

SELECT CAST(plača AS char)
--OR
SELECT CONVERT(char, plača)

Agregirane funkcije

SELECT AVG(plača)
FROM table

• AVG
• SUM
• COUNT
• MAX
• MIN

Zanemarijo NULL razen ene od oblik COUNT.

GROUP BY

SELECT izobrazba, SUM(plača)
FROM table
WHERE ...
GROUP BY izobrazba

Če želimo v SELECT poleg agregiranih funkcij izpisati še kaj, združimo z GROUP BY

HAVING

SELECT izobrazba, SUM(plača)
FROM table
WHERE ...
GROUP BY izobrazba
HAVING SUM(plača) > 100

Pogoji, ki veljajo za skupine (za GROUP BY).

UNION

SELECT *
FROM table
	UNION
	--OR
	UNION ALL
SELECT *
FROM table

• UNION -izpis brez dvojnikov
• UNION ALL -izpis z dvojniki

Združi podatke iz več poizvedb. V SELECT delu mora bi podobni parametri.

INTERSECT

SELECT *
FROM table
	INTERSECT
SELECT *
FROM table

Izpiše vrstice, ki so enake v obeh poizvedbah.

EXCEPT

SELECT *
FROM table
	EXCEPT
SELECT *
FROM table

• Izpiše tiste, ki se ne ponovijo v drugi poizvedbi.
• Iz prve poizvedbe odstrani tiste, ki so v drugi poizvedbi.

VIEW

CREATE VIEW view_name
AS
	SELECT *
	FROM table

CREATE, ALTER, DROP

Uporaba

SELECT *
FROM view_name

Variables

DECLARE @variable_name type

Na začetku mora imeti "@"

PROCEDURE

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

• CREATE, ALTER, DROP
• OUTPUT ali OUT
• RETURN -je število, ugotavlja za ugotavljanje uspešnosti izvedbe procedure (ponavadi 0-procedura je bila uspešna)

Zagon PROCEDURE

EXECUTE spMyProcedure @param1, @param2, @param3 OUTPUT

--OR

EXECUTE spMyProcedure @param2 = @myParam1, @param1 = @myParam2

EXECUTE @var1 = spMyProcedure @param1

V @var1 se bo shranil RETURN

TRIGGERS

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ]
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

• CREATE, ALTER, DROP
• FOR = AFTER -trigger se bo zgodil ko se nekaj konča
• INSTEAD OF -trigger se bo zgodil namesto (npr. namesto INSERT)
• inserted -začasna tabela v kateri so vrednosti, ki so bile napisane v INSERT stavku, ki je sprožil TRIGGER
• deleted -začasna tabela v kateri so vrednosti, ki so bile zbrisane (pri UPDATE imamo inserted in deleted tabelo)