VTYS 2012

1. VTYS 2012 Mehmet Emin KORKUSUZ Ders - 06

2. DCL DATA CONTROL LANGUAGE

3. CREATE USER CREATE USER user_specification [, user_specification] ... user_specification: user [IDENTIFIED BY [PASSWORD] 'password' | IDENTIFIED WITH auth_plugin [AS 'auth_string'] ]

4. CREATE USER CREATE USER ‘emin'@'localhost' IDENTIFIED BY ‘sifre'; CREATEUSER‘superadmin’@’%’ IDENTIFIED ‘zorsifre’; CREATEUSER‘kullanici’@’%.balikesir.edu.tr’ IDENTIFIED ‘sifre’; INSERTINTOuser(host,user,password) VALUES('localhost',‘kullanici',PASSWORD(‘sifre’));

5. DROP USER DROP USER ‘emin’@‘localhost’;

6. RENAME USER RENAME USER ‘emin’@'localhost' TO ‘mek'@'127.0.0.1';

7. SET PASSWORD SET PASSWORD FOR ‘can'@'%.edu.tr' = PASSWORD(‘sifre'); UPDATE mysql.user SET Password=PASSWORD(‘sifre') WHERE User=‘can' AND Host='%.edu.tr'; FLUSH PRIVILEGES; GRANT USAGE ON *.* TO ‘can'@'%.edu.tr' IDENTIFIED BY ‘sifre';

8. YETKİLENDİRME Kullanıcıya yetki vermek. kullanıcıyı yetkilendirmek Grant Revoke Kullanıcının yetkilerinikaldırmak

9. GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...]

10. GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION]

11. object_type: TABLE | FUNCTION | PROCEDURE priv_level: * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name

12. with_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count

13. GRANT ALL Select İnsert Drop Verilebilecek Yetkiler Update Delete Create USAGE

14. GRANT Genel Kullanımı GRANT YETKILER ON VERITABANI_ADI TO KULLANICI IDENTIFIED BY Istenen_Sifre ; grant all on * todilek identified by 'sifrem' with grant option;

15. GRANT Örnek:Birden çok kullanıcıya hem veri tabanı hem de tablo oluşturma izni vermek istersek; GRANT CREATE DATABASE, CREATE TABLE TOkul1,kul2;

16. GRANT Eğer hosttan iletişimi kontrol etmek istiyorsak; GRANT YETKILER ON VERITABANI_ADI TO ‘KULLANICI’@’Host Adı/ID Numarası’ IDENTIFIED BY Istenen_Sifre ; Örneğin; grant allon *to 'dilek'@'%' identified by 'bote';

17. GRANT Örnek:dilek kullanicisina deneme veritabani uzerinde sifre1 parolasi ile 192.168.1.37 ip_adresli makineden veritabanı oluşturma izni ile baglanabilmesi icin; GRANT CREATE DATABASEON deneme.* TO 'dilek'@'192.168.1.37' IDENTIFIED BY 'sifre1';

18. GRANT CREATE USER ‘can'@'localhost' IDENTIFIED BY ‘sifre'; GRANT ALL ON db1.* TO‘can'@'localhost'; GRANT SELECT ON db2.ogrenci TO‘can'@'localhost'; GRANT USAGE ON *.* TO‘can'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

19. GRANT GRANT ALL ON *.* TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost'; GRANT ALL ON mydb.* TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

20. GRANT GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost'; GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost'; GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost'; GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost'; GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

21. GRANT SSL GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SSL;

22. REVOKE • REVOKE priv_type[(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... • REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... • REVOKE PROXY ON user FROM user [, user] …

23. REVOKE REVOKE INSERT ON *.* FROM ‘CAN'@'localhost'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

25. DISTINCT Tekrarlı sonuçları eler. Groupby gibi çalışır.

26. ORDER BY Sonuçları sıralamak için kullanılır. ASC artan sıralama DESC azalan sıralama SELECT college, region, seed FROM tournament ORDER BY region, seed;

27. WHERE Sorguya şart ekler. Select * fromogrenciwhere yas<20; Select * fromogrenciwhereyas<20 and boy>150; Select * fromogrenciwhere yas<20 andboy>150 or isim like ‘a%’;

28. WHERE SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;

29. IN Bir alanın değeri bir küme içinden herhangi bir değere eşitse sonucu gösterir. SELECT * FROM adres WHERE ülkekodIN (‘TR’,‘US’,‘UK’,‘GB’,‘CN’)

30. BETWEEN … AND … İki değer arasını göstermenin kısa yoludur. SELECT * FROM ogrenci WHERE vize>= 90AND vize<= 100; SELECT * FROM ogrenci WHERE vize BETWEEN 90 AND 100;

31. LIKE % _ [harf] [^harf] [A-Z]

32. LIKE SELECT * FROM `haber` WHERE baslik like '%arama%'; SELECT * FROM `haber` WHERE baslik like 'a%';

33. LIKE SELECT * FROM maas WHERE kstLIKE '%1740 %' OR kstLIKE '%1938 %' OR kstLIKE '%1940 %‘; SELECT * from maaswhere kstREGEXP '1740|1938|1940';

34. CAST | CONVERT Tip dönüşümü yapmak için kullanılır. SELECT CONVERT('abc' USING utf8); SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name; SET @str = BINARY 'New York'; SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));

35. SELECT CAST(1-2 AS UNSIGNED);18446744073709551615 • SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED); -1