Guruh talabasi Abdig’opporov javoxir Mysql 1-2-amaliy ishlar

5.5 User Variables
In MySQL, you can define user variables via:

1. 
   @varname :=value in a SELECT command, or
   
2. 
   SET @varname := value or SET @varname = value command.
   

For examples,
mysql> SELECT @ali_dob := dateOfBirth FROM patients WHERE name = 'Ali';
mysql> SELECT name WHERE dateOfBirth < @ali_dob;
mysql> SET @today := CURDATE();
mysql> SELECT name FROM patients WHERE nextVisitDate = @today;
6. More on JOIN
6.1 INNER JOIN
In an inner join of two tables, each row of the first table is combined (joined) with every row of second table. Suppose that there are n1 rows in the first table and n2 rows in the second table, INNER JOIN produces all combinations of n1×n2 rows - it is known as Cartesian Product or Cross Product.
Example
mysql> DROP TABLE IF EXISTS t1, t2;
mysql> CREATE TABLE t1 (
id INT PRIMARY KEY,
`desc` VARCHAR(30)
);
-- `desc` is a reserved word - must be back-quoted
mysql> CREATE TABLE t2 (
id INT PRIMARY KEY,
`desc` VARCHAR(30)
);
mysql> INSERT INTO t1 VALUES
(1, 'ID 1 in t1'),
(2, 'ID 2 in t1'),
(3, 'ID 3 in t1');

mysql> INSERT INTO t2 VALUES

(2, 'ID 2 in t2'),
(3, 'ID 3 in t2'),
(4, 'ID 4 in t2');
mysql> SELECT * FROM t1;
+----+------------+
| id | desc |
+----+------------+
| 1 | ID 1 in t1 |
| 2 | ID 2 in t1 |
| 3 | ID 3 in t1 |
+----+------------+
mysql> SELECT * FROM t2;
+----+------------+
| id | desc |
+----+------------+
| 2 | ID 2 in t2 |
| 3 | ID 3 in t2 |
| 4 | ID 4 in t2 |
+----+------------+
mysql> SELECT *
FROM t1 INNER JOIN t2;
+----+------------+----+------------+
| id | desc | id | desc |
+----+------------+----+------------+
| 1 | ID 1 in t1 | 2 | ID 2 in t2 |
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 2 | ID 2 in t2 |
| 1 | ID 1 in t1 | 3 | ID 3 in t2 |
| 2 | ID 2 in t1 | 3 | ID 3 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
| 1 | ID 1 in t1 | 4 | ID 4 in t2 |
| 2 | ID 2 in t1 | 4 | ID 4 in t2 |
| 3 | ID 3 in t1 | 4 | ID 4 in t2 |
+----+------------+----+------------+
-- SELECT all columns in t1 and t2 (*)
-- INNER JOIN produces ALL combinations of rows in t1 and t2
You can impose constrain by using the ON clause, for example,
mysql> SELECT *
FROM t1 INNER JOIN t2 ON t1.id = t2.id;
+----+------------+----+------------+
| id | desc | id | desc |
+----+------------+----+------------+
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
+----+------------+----+------------+
Take note that the following are equivalent:
mysql> SELECT *
FROM t1 INNER JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
FROM t1 JOIN t2 ON t1.id = t2.id; -- default JOIN is INNER JOIN
mysql> SELECT *
FROM t1 CROSS JOIN t2 ON t1.id = t2.id; -- Also called CROSS JOIN
-- You can use USING clause if the join-columns have the same name
mysql> SELECT *
FROM t1 INNER JOIN t2 USING (id);
+----+------------+------------+
| id | desc | desc |
+----+------------+------------+
| 2 | ID 2 in t1 | ID 2 in t2 |
| 3 | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+
-- Only 3 columns in the result set, instead of 4 columns with ON clause
mysql> SELECT *
FROM t1 INNER JOIN t2 WHERE t1.id = t2.id; -- Use WHERE instead of ON
mysql> SELECT *