turbo mysql

gistfile1.sql

1. MySQL
  
	A)
		mysql -u root -p

		SHOW databases;
		CREATE nazwa_bazy;
		USE tab_name;
		SHOW tables;

		CREATE TABLE tab_name (
			id int not null auto_increment,
			name varchar(30) default "Janusz",
			title varchar(40),
			CONSTRAINT id_pk PRIMARY KEY (id)
			CONSTRAINT otr_fk FOREIGN KEY (otr_id) REFERENCES otr_tab(id); 
			);

		INSERT INTO tab_name (title) VALUES ("Typek");
		INSERT INTO tab_name VALUES (1, "Janusz", "Typek");

		SELECT * FROM tab_name;
		SELECT title, imie FROM tab_name;

		int auto_increment
		decimal (<scope>, <precision>)
		text
		varchar
		date, time, datetime 
		bolean

	B)
		ALTER TABLE tab_name RENAME new_tab_name;
		ALTER TABLE tab_name ADD new_row int;
		ALTER TABLE tab_name DROP row_name;
		ALTER TABLE tab_name CHANGE old_row new_row varchar(30);
		
		DELETE FROM tab_name;
		DELETE FROM tab_name where id="1";
		DROP table tab_name;

		UPDATE tab_name SET row="Lorem";
		UPDATE tab_name SET row="Lorem" WHERE row="Ipsum";

	C)
		ALL
		NOT
		AND
		OR
		ANY
		SOME
		BETWEEN
		IN
		EXISTS
		LIKE

		SELECT * FROM tab_name LIMIT 3; // 1,2,3
		SELECT * FROM tab_name LIMIT 2 OFFSET 2; // 3,4
		SELECT DISTINCT wiersz FROM tab_name; // Bez duplikatow
		SELECT * FROM tab_name ORDER BY wiersz; // DESC ASC

		SELECT * FROM tab_name GROUP BY id;
		SELECT id, AVG(wiersz) FROM tab_name GROUP BY id;
		SELECT id, AVG(wiersz) AS avg_hours FROM tab_name GROUP BY id
		ORDER BY avg_hours;
		
////////////////////////////////////////////////////////////////////////////////

2. PDO

	a) Połaczenie z baza

		$pdo = new PDO('mysql:host=localhost;dbname=name', 'root', 'password');

		try {
			$pdo = new PDO('mysql:host=localhost;dbname=name', 'root', 'password');
		} 
		catch(PDOException $e)
		{
			echo 'Error: ' . $e->getMessage();
		}

	b)
		PDO::query() - funkcja uzywana w przypadku pobierania danych z bazy
		PDO::exec() - gdy chcemy wywołać zapytanie które nie zwraca zadnego rezultatu
		PDO::prepare() - funkcja wywoływana gdy uzywamy prepared statements 
						(wiecej na ten temat w nastepnych wpisach o PDO)


	c)
		$db = new PDO($dsn, $user, $password);
		$players = $db -> query('SELECT * FROM players');
		while($row = $players->fetch())
		{
			echo $row['login'];
		}
		 
		foreach($players->fetchAll() as $row)
		{
			echo $row['login'];
		}

	d)
		$db->exec('DELETE FROM players');