استعلامات MySQL بشكل متقدم

UPDATE TABLE SET FIELD = "قيمة جديدة";

UPDATE TABLE SET FIELD = CONCAT("د." , FIELD) ;

UPDATE TABLE SET FIELD = FIELD + 1 ;

SELECT  REPLACE("www.Flasher.ws" , "Flasher" , "Abdulaziz")

UPDATE TABLE SET FIELD = REPLACE(FIELD , "Search" , "Replace")

CREATE TABLE `employee` (`name` VARCHAR (50), 

`id` INT (4) AUTO_INCREMENT, 

`salary` INT (4) DEFAULT '0', 

PRIMARY KEY(`id`));

CREATE TABLE `items` (`id` INT (4) AUTO_INCREMENT,

 `employee_id` INT (4),

 `data` TINYTEXT, PRIMARY KEY(`id`));

 

INSERT INTO `employee` (`name`,`id`,`salary`) VALUES ('Ahmed',NULL,0);

INSERT INTO `employee` (`name`,`id`,`salary`) VALUES ('Abdulaziz',NULL,0);

INSERT INTO `employee` (`name`,`id`,`salary`) VALUES ('Mohammed',NULL,0);

 

INSERT INTO `items` (`id`,`employee_id`,`data`) VALUES (NULL,1,'Labtop ')

INSERT INTO `items` (`id`,`employee_id`,`data`) VALUES (NULL,1,'Car ')

INSERT INTO `items` (`id`,`employee_id`,`data`) VALUES (NULL,2,'Desk ')

mysql> Select * from employee,items where employee.id = items.employee_id

    -> ;

+-----------+----+--------+----+-------------+---------+

| name      | id | salary | id | employee_id | data    |

+-----------+----+--------+----+-------------+---------+

| Ahmed     |  1 |      0 |  1 |           1 | Labtop  |

| Ahmed     |  1 |      0 |  2 |           1 | Car     |

| Abdulaziz |  2 |      0 |  3 |           2 | Desk    |

+-----------+----+--------+----+-------------+---------+

3 rows in set (0.00 sec)

SELECT employee.*,items.* FROM employee JOIN items ON  employee.id = items.employee_id

mysql> SELECT employee.*,items.* FROM employee LEFT JOIN items ON  employee.id =

 items.employee_id ;

+-----------+----+--------+------+-------------+---------+

| name      | id | salary | id   | employee_id | DATA    |

+-----------+----+--------+------+-------------+---------+

| Ahmed     |  1 |      0 |    1 |           1 | Labtop  |

| Ahmed     |  1 |      0 |    2 |           1 | Car     |

| Abdulaziz |  2 |      0 |    3 |           2 | Desk    |

| Mohammed  |  3 |      0 | NULL |        NULL | NULL    |

+-----------+----+--------+------+-------------+---------+

4 rows IN SET (0.00 sec)

mysql> SELECT employee.*,count(items.id) AS itemsNumber

      FROM employee LEFT JOIN items ON  employee.id = items.employee_id

      GROUP BY employee.id;

+-----------+----+--------+-------------+

| name      | id | salary | itemsNumber |

+-----------+----+--------+-------------+

| Ahmed     |  1 |      0 |           2 |

| Abdulaziz |  2 |      0 |           1 |

| Mohammed  |  3 |      0 |           0 |

+-----------+----+--------+-------------+

3 rows IN SET (0.00 sec)

SELECT employee.*,count(items.id) AS itemsNumber

      FROM employee LEFT JOIN items ON  employee.id = items.employee_id

      WHERE employee.salary > 1000

      GROUP BY employee.id;

ALTER TABLE `employee` ADD `manager_id` INT(4)

UPDATE `employee` SET `manager_id`=3 WHERE `id`=1;

UPDATE `employee` SET `manager_id`=3 WHERE `id`=2

mysql> SELECT employee.*,managers.name AS managerName FROM employee

    -> LEFT JOIN employee AS managers ON managers.id = employee.manager_id;

+-----------+----+--------+------------+-------------+

| name      | id | salary | manager_id | managerName |

+-----------+----+--------+------------+-------------+

| Ahmed     |  1 |      0 |          3 | Mohammed    |

| Abdulaziz |  2 |      0 |          3 | Mohammed    |

| Mohammed  |  3 |      0 |       NULL | NULL        |

+-----------+----+--------+------------+-------------+

3 rows IN SET (0.00 sec)