m1gin 135

Algoritma Tasarımı

Console.WriteLine($"x={x}, y={y}");

Console.WriteLine("{0:F2}", 3.1);
//output: 3.10

Console.WriteLine("{0,5} : {1,5}", 2, 4);
//output: ....2 : ....4

Console.WriteLine("\aBeep for this");
//beep sound

//
Console.BackgroundColor=...Red
Console.ForegroundColor=...White
Console.ResetColor();

//
for (int i=0; (i<5 && s==true); i++)

7.1.2 - StreamReader
7.1.7 - FileStream

8.1.5 Extension Methods
public static int KelimeSayisi(this string s) => s.Split().Length;
Usage:
string str="Extension for an existing class";
int sayi = str.KelimeSayisi();

8.1.8 - Override
8.1.11 - Virtual Methods
8.1.16 - Abstract Methods

virtual methods: optional to implement (override)
abstract methods: need to be implemented
sealed methods: not allowed to implement

8.1.17 - sealed classes and methods
8.1.18 - Summary

9.1.2 - Enumeration
9.1.3 - IEnumerable

 


Linux Sistem Yönetimi

 

ps aux #list processes

pstree #list process as tree

 

add system-wide parameters

cat /etc/environment

 

ls /etc/init.d/ # list services

 

run service command:

/etc/init.d/apache2 status

 

 

ls -li : list files and dirs with inode no

cd - : önceki dizine gider

file : shows file information

stat : shows detailed file information

 

cmd 2> log : hata yönlendirme.

 

!22 : runs 22nd item from history output

 

VIM

i : insert

Esc : command mode

:q : quit

:w : write

:q! : quit with force

:wq file.txt : save to file.txt and quit

 

 

su -l username # switch to user

cat /etc/group # list all groups

cat /etc/passwd #list users

cat /etc/shadow #list hashed pass

 

usermod -a -G group user # add user to group

 

chmod

4:read

2:write

1:execute

chmod 764 # means u=rwx,g=rw,o=r

 

chattr +i file #lock a file. even for root.,,

chattr -i file #unlock file

lsattr #list file's lock attributes


BASH Programlama Dili

 

tail -f file.txt # read last lines and keep following the file for changes

echo $$ #process id of the script

read -p "user:" user #ask for entry
read -sp "pass" pass #ask for password

let a=5+9
let "a=5+4"
let "b=$a*3"

expr 3 \* 4 #escape *

#until loop
c=0;
until [[ $c -gt 11 ]]; do
echo $c;
((c++));
done;

#Create a menu and do something based on the selected item:
names="option1 option2 option3 option4";
PS3="Seçiminiz:";
select name in $names;
do
echo $name;
done

 

var1="out"
test(){ local var1="in"; echo $var1; }
test
echo $var1

 

 

 

m1gin 0

DATEDIFF(HOUR, dt1, dt2) AS FarkSaat

CONVERT(DATE, dtrecord) AS Tarih

 

-----

Local Access:

Server Name: dot(.), local, localhost, sql instance name

 

Network Access:

Need to enable TCP/IP (Windows) and Named Pipes (Linux) protocols.

 

//get month number from a date

SELECT DATEPART(MONTH, dtrecord)

 

5.8 - Connecting to Excel

6.2 - Ondalıklı sayı veri tipleri

6.4  - Metin Veri Tipleri

 

decimal(18, 4): 4 decimal digits, total 18 digits 

float:  total17 digits with decimals. No control for the number decimal digits.

money: 4 decimal digits. always puts the decimals. Ex: 3.0000. It is the same as: decimal(18, 4)

 

8.4 - İkiden fazla tablo kullanımı

8.7 - Join Uygulama Giris

8.8 - Inner Join

8.9 - Left (Outer) Join

8.10 - Right Join

8.11 - Full Join

8.12 - Join Türleri (Uygulama)

9.6 - CASE WHEN ... THEN ...

10.1 -Sub Query

11.5 - string  islemleri

11.6 - CONCAT, CONCAT_WS

11.7 - FORMAT

11.8

11.9

11.10

11.11

 

 

m1gin 0

4.

5.

6.

7.

8.

9. shellshock

10

11.

12.

13.

14


----

nmap: scans open ports.


nmap 192.168.143.184 -p 0-65535


-Pn: starts scanning without sending ping


-sV: get information about the service


nmap -Pn -sV -p 22 IP


-A: Enable OS detection, version detection, script scanning, and traceroute


nmap IP -p 21,53,445 -A


----

nc — arbitrary TCP and UDP connections and listens


nc -lvp 1337 #listen the port 1337


----

nikto - Scan web server for known vulnerabilities


nikto --host 192.168.143.134

------

MEDUSA - Parallel Network Login Auditor


medusa -M ssh -h IP -U /test/userlist.txt -P /test/passwords.txt -t 10

--------

dirb - Web Content Scanner

#checks for directories in a server


dirb http://192.168.143.134


Kurs Dışı

Listen to a port (Server):

netcat -l 3333

connect to a port (Client):

netcat localhost 3333

After the successful connection, it is possible to send messages line by line by typing.

send a message to a port directly (Client):

echo hello | netcat localhost 3333

Do something when a message arrived (Server)

netcat -k -l 3333 | while read l; do echo "$l"; zenity --info --title "Test" --text "A message arrived: $l"; done


m1gin 0

WEB:
$ pip install django

$ django-admin help

Create a new project:
$ django-admin startproject myproject

Get Help:
$ python manage.py help

Run a web server
$ python manage.py runserver

Create a page app
$ python manage.py startapp mypages


--------------
Convert UI file prepared with QT 5 Designer to PY file to be used in Python.

pyuic5 MainWindow.ui -o MainWindow.py

--------
isinstance(obj, tuple) #check if the obj is a tuple


-----
Pandas DataFrame

import pandas as >pd

s1 = pd.Series([1,2,3,4])
s2 = pd.Series([10,20,30,40])
data = dict(apples = s1, oranges = s2)
df = pd.DataFrame(data)
print(df)
apples oranges
0 1 10
1 2 20
...

df = pd.DataFrame([["x", 10], ["y", 20], ["z", 30]], columns=["A", "B"], index=[3,4,5], dtype=float)
print(df)
A B
3 x 10.0
4 y 20.0
5 z 30.0

df["A"] #get column A
df[["A", "B"]] #column A, B

df.loc[4] #Get row with custom index 4
df.loc["row", "column"]
df.loc[:, ["B", "A"]] # get all rows with column B, A

df.iloc(2) #get row using its position, 0 based index

Add new column
df["C"] = pd.Series([100,200,300], [3,4,5])
df["D"] = df["C"] + df["B"]

df.drop("C")


df = pd.read_csv('sample.csv')
df = pd.read_json('sample.json', encoding="UTF-8")

import sqlite3
con = sqlite3.connect("sample.db")
df.read_sql_query("SELECT * FROM tbl", con)

-------
import pandas as pd

nums=[10, 20, 30, "abc"]
pdseri = pd.series(nums)
#creates key value pairs for items

pdseri = pd.Series([10, 20, 30], ["a", "b", "c"])
pdseri[0] #20
pdseri["a"] #20
pdseri[:1] #a 10
pdseri[-1:] #c 30
pdseri[["a","b", "x"]] # a 10 \n b 20 \n NaN
pdseri.sum() #60
pdseri.max() #30
pdseri.ndim #1 - dimension

pdseri + 5 # a 15 \n b 25 \n c 35

result = pdseri >=20
#result = a False \n b True \n c True

result = pdseri % 3 == 0
# result = a False \n b False \n c True
print(pdseri[pdseri % 2 == 0])
#a 10 \n b 20

pdseri2 = pd.Series([5, 15, 25], ["b", "c", "d"])
total = pdseri + pdseri2
#total= a NaN \n b 25 \n c 35 \n d NaN
total["c"] # 35

------
import numpy as np


arr1 = np.array([1,2,3,4])
arr2=arr1.reshape(2,2)
arr1.ndim #1 - get dimensions
arr2.ndim #2
arr1.shape #(4,)
arr2.shape #(2,2)
arr1.mean() #2.5 - average
np.arange(1,9,2) #[1,3,5,7]
np.random.randint(0,100, 3)
np.random.rand(3) #[0.44, 0.92, 0.788]
-------
pip install requests #install a library
pip list #lists installed packages

----
import json

print(json.__file__) #gets the path of the library


-----
names=["ali", "veli", "ahmet"]
name = random.choice(names)

#shuffle list items.
random.shuffle(names)

#get a small portion of list
nums=list(range(100))
parts = random.sample(nums, 3)


------
import math
print(dir(math)) #list all functions in a module
print(help(math)) #get help about all functions in a module
print(help(math.sin)) #get help about specific function

--
#Using alias:
import math as islem

a = islem.ceil(5.3) #6

-----
#using all functions directly
from random import * #imports all
from math import sqrt, sin, factorial, ceil

a = ceil(5.3) #6

b = randint(1,10)


-----
global x #to use the globally defined variable in a function

x=100
y=100

def test():
global x
x=200
y=200

test()
print(x) #200
print(y) #100


----
def checkEven(num): return num % 2 == 0

nums=[1,3,5,6,8,9]
result = list(filter(checkEven, nums))
#result=[6,8]


----
def square(num): return num ** 2

nums=[1, 4, 7, 9]

for item in map(square, nums):
print(item)
#1 - 16 - 49 - 81

result = list(map(square, nums))
#result = [1, 16, 49, 81]

...
result = list(map(lambda num: num ** 2, nums))
#result=[1, 16, 49, 81]

square2 = lambda num: num ** 2

result=square2(11)
#result= 121


-----
FUNCTIONS:

def myFunc(a, *args, **pdict):
print(a)
print(args)
print(pdict)

myFunc(5, 7, 8, key1='val 1', key2 = 'val 2')
#5 \n (7, 8) \n {'key1': 'val 1', 'key2': 'val 2'}


----
#args as dict
def displayUser(**args):
for key, value in args.items():
print(key, value)

displayUser(name='Ali', age=22, city='Ankara')

...
#params as tuple
def add(*params):
top=0
for p in params:
top+=p
return top

add(1,3,4,6,9)
....

def Topla(num1, num2, num3=0):
'''
DOCSTRING: Toplama yapar
INPUT: Sayi
OUTPUT: Sayilarin toplami
'''
return num1 + num2

a = Topla(3, 5)
b=Topla(2, 8, 11)


help(Topla)
#DOCSTRING: ....;

----------
nums =[(x,y) for x in range(3) for y in range(2)]
#nums=[(0, 0), (0, 1), (1, 0), (1, 1), (2, 0), (2, 1)]

results=[x if x%2==0 else 'TEK' for x in range(1,5)]
#results=['TEK', 2, 'TEK', 4]

years=[1983, 1999, 2000]
ages = [2019-year for year in years]
#ages=[36, 20, 19]

list1 = [letter for letter in "Hello"]
print(list1) # ["H", "e", "l", "l", "o"]


numbers = [x for x in range(5)]
#numbers=[0, 1, 2, 3, 4]

nums = [x**2 for x in range(4)]
#nums = [0, 1, 4, 9]

nums = [x*x for x in range(10) if x%3==0]
#nums=[0, 9, 36, 81]

-----------
list1= [1, 2, 3]
list2 = ["a", "b", "c"]

list(zip(list1, list2)) # [(1,'a'), (2, 'b'), (3, 'c')

----

for item in range(50, 100, 10):
print(item)
#50, 60, 70, 80, 90

list(range(5, 33, 10) # [5, 15, 25]

msg="Hello"
for index,letter in enumerate(msg):
print(index, letter)
#0 H, 1 e, 2 l, 3 l, 4 o
---
import datetime
dt1 = datetime.datetime(2020, 12, 30)
dtFark = datetime.datetime.now() - dt1
print(dtFark.days)

----
if (userName=="a"):
print("hello")
else:
print("no")

---
a=[1,2,3]
b=[1,2,3]
result = a == b # True
result = a is b # False
result = a is not b # True

name= "Ahmet"
print('h' in name) # True
print('h' not in name) # False

(x > 5) and (x <10)
(x > 5) or (x < 10)
result = not (x<10)
---

values= (1, 2, 3) #tuple

x, y, z = values
print(x, y, z) # 1, 2, 3

values = 1, 2, 3, 4, 5 #tuple
x, y, *z = values
print(x, y, z) # 1, 2, [3, 4, 5]


----
SETS

list1 = [1,2,3,1]
print(set(list1)) # [1,2,3] -- unique items.

fruits={"orange", "apple", "banana"}
fruits.add("carrot")
fruits.update(['mango', "grape"])

for item in fruits:
print(item)


---------
DICTIONARY

plakalar = ["adana": "01", "istanbul": "34"}
plakalar["adana"] #01

plakalar["ankara"]="06"
print(plakalar) # ["adana": "01", "istanbul": "34", "ankara", "06"}
...
users = {
"user1": {
"age": 33,
"email": "no",
"roles": [1, 2]
},
"user2": {
"age":11,
"email": "mail2",
"roles": [2,5]
}
}


users["user1"]["age"] #33
users["user2"]["email"] #mail2
users["user2"]["roles"][0] #2

#add more
userName="user3"
email="none"
roles=[1,2,5]

users.update(
{
userName: {
"age": 44,
"email": email,
"roles": roles
}
}
)


dic = {"k1": 1, "k2": 2}
for k,v in dic.items():
print(k, v)
#k1 1 \n k2 2

-------
t = (1, "iki", 3)
print(type(t)) #tuple

t[1]=2 #gives erorr.

tuple works like a list. but items cannot be changed individually.

tpl = [(1,2), (3,5), (2,7)]
for t in tpl:
print (t)
#(1,2) \n (3,5) \n (2,7)
for a,b in tpl:
print(a, b)
#1 2 \n 3 5 \n 2 7

-----
nums=[1,4,7,8,11, 7]
nums.index(7) #2
min(nums) #1
nums.append(44) #add item
nums.insert(-2,33) #insert
item = nums.pop(2) #gets and removes item
nums.remove(44) #removes item
nums.sort()
nums.reverse() #reverse items
numbers.count(7) #2 - gets the item count found in array
nums.clear()

--------
list1=["bir", 3]
print(list1[-2]) #"bir"
isExist= "bir" in list1 #true

list2=[True, 55.3]
list3=list1 + list2
#list3=["bir", 3, True, 55.3]
list3[0:3] #["bir", 3, True]
list3[-2:] = ["hello", 11]
#list3=["bir", 3, "hello", 11]
del list3[-1] #delete item
#list3=["bir", 3, "hello"]
list4=list3[::-1] #reverse items
#list4=["hello", 3, "bir"]

list5=[list1, list2]
#list5=[[l"bir", 3], [True, 55.3]]
print(list5[1][1]) #55.3

copy array values (create a new array)
cities=["Ankara", "İstanbul"]
sehirler=cities[:]

---
msg = "this is a test"
msg.replace("test", "food") # this is a food
msg.upper()
msg.lower()
msg.title() #This Is A Test
msg.capitalize() #This is a test
msg.strip() #trimming
arr = msg.split(' ') #splits.
print(arr[0]) # this
print('---'.join(arr)) #joins the array
#this---is---a---test
msg.find('is') #2 - get index of
msg.rfind('is') #5 - get last index of
msg.startswith("This")
msg.endswith("test")
print(msg.center(50,".")) #aligns the text
#...................This is a test..............
msg.ljust(40,"*")
msg.rjust(40,"*")
msg.count('is') #2 - get repeated count

---------
name="Ali"
surname="Veli"
print(''My name is {} {}'.format(name, surname)); #My name is Ali Veli
print(''My name is {1} {0}'.format(name, surname)); #My name is Veli Ali
print(''My name is {n} {s}'.format(n=name, s=surname)); #My name is Ali Veli
print(f"My name is {name}")

result = 2 / 7
print('the result is {r:8.3}'.format(r=result))
#____0.286


----
isim="Ahmet Ali"
print(isim[2]) #m
len(isim) #9
isim[-1] #i - get char from end.
isim[2:4] #me - get part of string
isim[4:] #t Ali - get the rest
isim[:4] #Ahmet - get the beginning part
isim[1:8:2] #he l - get every 2nd char in the range 1, 8
isim[::-1] #ilA temhA - reverse the text

print(isim*2) # Ahmet AliAhmet Ali


---
x=input("sayı1: ")
toplam = int(x) + 11

a=float(x)
print(type(a)) #float

b=str(x)
print(type(b)) #str

isOnline=True
print(type(isOnline)) #bool


Değişken tanımlanırken değer atanmalı.

Toplu değişken tanımlama:
x, y, name, isStudent = (1, 2.3, "Ahmet", True)


10//3 #3

**: pow
2**3 # 8

type(2) #int

number types: int, float


----------

8.2 - Class
8.3 - Class Methods
8.4 - Inheritance
8.5 - Özel metodlar
12.2 - Fonksiyondan fonksiyon döndürme
12.3 - Fonksiyonları Parametre Olarak Gönderme
12.4 - Decorator Fonksiyonlar
13.1_Pythonda Iterators
14.1_Pythonda Generators
19.3_Numpy Dizilerinin Indekslenmesi
19.4_Numpy Dizi Operasyonları
19.5_Uygulama - Python Numpy
20.8_Pandas DataFrame ile Groupby Kullanımı
20.9_Pandas ile Kayıp ve Bozuk Veri Analizi
20.10_Pandas ile String Fonksiyonları Kullanımı
20.11_Pandas ile Join ve Merge
20.12_Pandas ile DataFrame Metotları
20.14_Uygulama - Youtube İstatistik Verilerinin Analizi
21.2_Matplotlib ile Grafik Oluşturma Plot, SubPlot ve Axes
21.3_Matplotlib ile Figure Oluşturma

24.6_Pyqt5 Qt Designer Kullanımı
25.9_Tablolar
25.13_Formlar
25.16_Dropdown
25.20_Açılır Kutular
25.25_Izgara Sistemi
25.26_Responsive Tasarım
26.3_Pages Uygulamasını Projemize Ekleyelim
26.4_Pages Templates & Layout
26.5_Static Dosyalar
26.6_Bootstrap ile Tasarım
26.7_Movies Uygulamasının Eklenmesi
26.8_Admin Panelinin Aktif Edilmesi
26.9_Model Oluşturma ve Admin Paneline Ekleme
26.10_Admin Panelini Özelleştirelim
26.11_Movie Listeleme Sayfası
26.12_Movie Detay Sayfası
26.13_User Uygulaması
26.14_Register Kullanıcı Oluşturma Sayfası
26.15_Login Kullanıcı Giriş Sayfası
26.16_Django Mesajları
26.17_Nav Links & Logout

m1gin 0

Backup a database:

BACKUP DATABASE TESTDB TO DISK = N'D:\testdb.bak'; GO


---
Create a new user and add him to Admin group

CREATE LOGIN NewUser WITH PASSWORD=N'P@ss123';
ALTER SERVER ROLE [sysadmin] ADD MEMBER NewUser;

---
SERVER TRIGGERS

Create a trigger for database create, drop, alter

CREATE TRIGGER DB_Modify
ON ALL SERVER
FOR CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE
AS
BEGIN

IF [Conditions]
BEGIN
ROLLBACK
END

END

---
>>
Create a trigger when a new user added.

CREATE TRIGGER User_Create_Control
ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
DECLARE @msg AS VARCHAR(1000)
SET @msg=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(1000)')
@msg = @msg + ' IP: ' + CONVERT(VARCHAR, CONNECTIONPROPERTY('client_net_address'))  k
@msg = @msg + ' APP: ' + PROGRAM_NAME();
@msg = @msg + ' Username: ' + SUSER_NAME();
--Send email...
END

---


Create a trigger on a user login successfully.

CREATE TRIGGER Brute_Force_Logon_Control 
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @IP VARCHAR(22)

SELECT @IP=CONNECTIONPROPERTY('client_net_address')

IF [condition....]
BEGIN
    ROLLBACK --logout the user
END

END


Read Error Log:

EXEC XP_READERRORLOG

Copy the error log to a temp table

CREATE TABLE #log (logdate DATETIME, processinfo VARCHAR(100), text VARCHAR(MAX))

INSERT INTO #log 
EXEC XP_READERRORLOG

Detect if there is any attack in last 3 minutes:

SELECT * FROM #log WHERE text LIKE 'login failed%' AND logdate>=DATEADD(MINUTE, -3, GETDATE())


---
Get Time part from date as string:

SELECT CONVERT(VARCHAR, GETDATE(), 108)
-- 13:22:33


3.11_Brute Force Savunma 8

m1gin 0

Read a File a BLOB

SELECT * FROM OPENROWSET (BULK 'C:\test\img1.jpg', SINGLE_BLOB) T


---
GET DATA FROM A WEBSITE

DECLARE @URL AS VARCHAR(255)
SET @URL="http://tcmb.gov.tr/kurlar/today.xml";
DECLARE @Result AS INT
DECLARE @Obj AS INT

EXEC @Result = SP_OACREATE 'MSXML2.XMLHttp', @Obj  OUT

EXEC @Result = SP_OAMethod @Obj, 'open', NULL, 'GET', @URL, false

EXEC @Result = SP_OAMethod @Obj send, NULL, ''
EXEC @Result = SP_OAGetProperty @Obj 'responseXML.xml'

--
Process XML Data

DECLARE @xml AS XML
SELECT @xml=STRXML FROM tbl..
DECLARE @hdoc AS INT
EXEC SP_XML_PREPAREDOCUMENT @hdoc OUTPUT,  @xml

SELECT * FROM OPENXML(@hdoc, 'root/element1')
WITH
(unit float 'unit',
isim varchar(50) 'isim'>>>>>>
)


----
RUN Command Line apps:

xp_cmdshell 'dir C:\'

xp_cmdshell is disabled in default. To enable it:
SP_configure 'xp_cmdshell' 1
reconfigure with override

EXEC xp_cmdshell 'C:\test\test.exe'

----
Get Server Configs:

SP_configure

Change values
SP_configure 'show advanced options' 1
reconfigure with override


---
Access data from a table is currently used:

SELECT * FROM tbl WITH (NOLOCK)

----
send email:
msbd.dbo.sp_send_dbmail
@profile_name="profilename",
@receipients="emal@domail.com",
@body="This is an email test",
@subject="Subject line"

Sleep for some time

WAITFOR DELAY '00:00:00:010'


----
GET EXECUTED SQL QUERY

DBCC INPUTBUFFER(@@SPID)

Put the value in a temp table
CREATE #T (....)
INSERT INTO #t EXEC('DBCC INPUTBUFFER(' + CONVERT(VARCHAR, @@SPID) +')')

SET a variable from a temp table
SELECT @LOG_SQL = EVENT_INFO FROM #T

--
RUN SQL QUERY CREATED ON FLY

DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = 'INSERT INTO #T EXEC('' DBCC INPUTBUFFER(' + + CONVERT(VARCHAR, @@SPID) +') '')'
EXEC SP_EXECUTESQL @SQL


----

TRIGGERS


Create a log database and store> the old values.

CREATE TRIGGER ITEM_LOG_UPDATE ON tblTest
AFTER UPDATE
AS
BEGIN
INSERT INTO LogDB.tblTest(Columns....)
SELECT ID, Name, ...., , PROGRAM_NAME(), HOST_NAME(), GETDATE(),  SUSER_NAME()
FROM DELETED

END
--

create a trigger for delete and update at the same time:

CREATE TRIGGER ITEM_LOG_UPDATE ON tblTest
AFTER UPDATE, DELETE
AS
....


-----------

TRANSACTIONS
Do the operations on LOG database not the main database, until it committed.
In case some problems happen, it is possible to take all the opereations back by ROLLBACK.
If everything is alright, the changes can be aplied to the main database by COMMIT.
@@ERROR is not 0 it means here is an error.

BEGIN TRAN
INSERT tbl.....

IF @@ERROR > 0
BEGIN
ROLLBACK TRAN
RETURN
END

UPDATA tbl....

IF @@ERROR > 0
BEGIN
ROLLBACK TRAN
RETURN
END

IF @@ERROR = 0
COMMIT


-------
Get Last Inserted record ID
INSERT INTO tblTest(Name, Amount) VALUES ("Ali", 3)
SELECT @@IDENTITY


----
FUNCTION

CREATE FUNCTION Topla(@Sayi1 AS INTEGER, Sayi2 AS INTEGER)
RETURNS INTEGER
AS
BEGIN
DECLARE @Sonuc AS INT
SET @Sonuc=@Sayı1 + @Sayi2
RETURN @Sonuc
END

--Calling a function
SELECT Topla(3,5)  --8

-----

TABLE VALUED FUNCTION
Returns a table as a result.

CREATE FUNCTION Split(@Str AS VARCHAR(MAX), @Delimiter AS VARCHAR(10))
RETURNS @ResultTable TABLE(Item VARCHAR(MAX), Idx INT)
AS
BEGIN
DECLARE @Pos AS INT=1
DECLARE @I AS INT =1
WHILE @Pos>=1
BEGIN
SET @Pos = CHARINDEX(@Delimiter, @Str)
DECLARE @Item AS VARCHAR(MAX)
SET @Item = SUBSTRING(@Str, 0, @Pos)
SET @Str = SUBSTRING(@Str, @Pos+1, LEN(@Str)-@Pos)
IF @Pos=0
    SET @Item=@Str
INSERT INTO ResultTable(Item, Idx) VALUES (@Item, @I)
-- SELECT @I, @Pos, @Item, @Str
SET @I = @I + 1
END
RETURN
END


SELECT * FROM Split("Ali, Veli, Ahmet, Mehmet", ", ") WHERE Idx=2
-- Veli | 2


-----

Add a Login
CREATE LOGIN NewUser WITH PASSWORD = 'password123'

Add user to a role group
ALTER SERVER ROLE sysadmin ADD MEMBER NewUser;


@@ : Global variable.

CREATE PROC sp_barcode_info 
@ItemCode As VARCHAR(10)
AS
SELECT * FROM tblbarcode WHERE code=@ItemCode 
IF @@ROWCOUNT =0 
BEGIN
    RAISEERROR ('Barkod bulunamadı', 16, 1)
END

Get Info about the stored procedures
SELECT * FROM sys.dm_exec_procedure_statas


create a stored procedure:
CREATE PROC sp_Name AS ..
CREATE PROCEDURE sp_Name2 AS ...

Stored procedure with params
CREATE PROCCEDURE sp_satis
@ItemCode AS Varchar(100)='%',
@StartDate AS DateTime ='20170101'
AS SELECT * FROM Costumers WHERE ItemCode = @ItemCode, StartDate=@StartDate

-- Param @StartData has a with default value.

Calling the procedrure with params
sp_satis '1234', '20200101'
sp_satis @StartDate='20200101', @ItemCode='1234'
EXEC sp_satis '1234'

Calling stored procedure from another stored procedure:
CREATE PROC sp_musteri 
@ItemCode AS VARCHAR(10) = '%'
AS 
EXEC sp_satis @ItemCode


Update all statistics:
sp_updatestats

Update statistics for a table:
UPDATE STATISTICS tbl1

Get Tables: 
SELECT * FROM SYS.tables T

Get Indexes:
SELECT * FROM SYS.indexes


Get the space used for a table:
SP_SPACEUSED tbl1


List information of the tables and columns
SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.COLUMNS


Using # creates a temporary table in tembdb for that session.
CREATE TABLE #tbltest (isim varchar(100))

Using ## creates a temporary table in tembdb everyone can access for
CREATE TABLE ##tbltest (isim varchar(100))


1.2 - failover cluster
1.9 - Collation

2.1 - master db
2.2 - model db, msdb, tempdb
5.6_Index Uygulamasi Bir milyon Satir
5.9_Fragmantation
10.3_Trigger Degisen Kayit Loglama
16.3_DBMirroring
16.4_LogShipping
17.3_Klasördeki Resimleri Binary Kaydetme
17.4_Veritabanındaki Resimleri Dışarı Kaydetme
 

m1gin 0

CURSOR

DECLARE @name VARCHAR(50)
DECLARE @age INT
DECLARE @email VARCHAR(100)
DECLARE @msg VARCHAR(500)

DECLARE crs CURSOR FOR
    SELECT name, age, email FROM tbluser;
OPEN crs 
FETCH NEXT FROM crs INTO @name, @age, @email
WHILE @@FETCH_STATUS=0
BEGIN
    SELECT @name, @email, @age
    SET @msg = 'isim: ' + @name + ' yas: ' + @age;
    FETCH NEXT FROM crs INTO @name, @age, @email
END
CLOSE crs
DEALLOCATE crs

-----
Update the statistics of indexes
for all tables
SP_UPDATESTATS

Update stats for specific table
UPDATE STATISTICS  tbl1

----
Turn on the statistics (read page amount) for getting information about a query:

SET STATISTICS IO ON
SELECT * FROM Costumer WHERE Name="Ali"

Turn on the time statistics
SET STATISTICS TIME ON
SELECT ..... 

---
Create Index

CREATE NONCLUSTERED INDEX Idx1 ON tbl1 (NameSurname)


7.6_Included Column
 

Add to: