- Details
- Category: Mysql
- Hits: 584
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
INNER JOIN should be used when you only want to pull data that exists in both of the tables.
OUTER JOIN should be used when you want to pull all of the matching data in the first table.
CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables.
LEFT JOIN keyword returns all rows from the left table 1, with the matching rows in the right table 2.
More Than one Table. The query uses an INNER JOIN to combine the tables.
SELECT pet.name,
(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
remark
FROM pet INNER JOIN event
ON pet.name = event.name
WHERE event.type = 'litter';
Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table.
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
In this query, we specify aliases for the table name to refer to the columns and keep straight which instance of the table each column reference is associated with.
- Details
- Category: Mysql
- Hits: 571
To find out which database is currently selected, use the DATABASE() function:
SELECT DATABASE();
To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this command:
SHOW TABLES;
If you want to find out about the structure of a table, the DESCRIBE statement is useful; it displays information about each of a table's columns:
DESCRIBE pet;
- Details
- Category: Mysql
- Hits: 680
Create a batch file by opening notepad and type:
select * from event;
Save it as a sql file
Open sql client and type:
source c:\mysql text\selectevent.sql
or you can use \.
\. c:\mysql text\selectevent.sql
- Details
- Category: Mysql
- Hits: 595
Create and populate the example table with these statements:
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
The Maximum Value for a Column
SELECT MAX(article) AS article FROM shop;
The Row Holding the Maximum of a Certain Column
Task: Find the number, dealer, and price of the most expensive article.
This is easily done with a subquery:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
Maximum of Column per Group
Task: Find the highest price per article.
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
The Rows Holding the Group-wise Maximum of a Certain Column
Task: For each article, find the dealer or dealers with the
This problem can be solved with a subquery like this one:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.10.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a LEFT JOIN.
Uncorrelated subquery:
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
LEFT JOIN:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
- Details
- Category: Mysql
- Hits: 588
You can employ MySQL user variables to remember results without having to store them in temporary variables in the client.
For example, to find the articles with the highest and lowest price you can do this:
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
- Details
- Category: Mysql
- Hits: 714
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
SELECT s.* FROM person p INNER JOIN shirt s
ON s.owner = p.id
WHERE p.name LIKE 'Lilliana%'
AND s.color <> 'white';
SHOW CREATE TABLE
SHOW CREATE TABLE shirt\G
- Details
- Category: Mysql
- Hits: 617
Using OR
Select sex,owner from pet where sex='m' or owner='Diane';
using UNION
Select sex,owner from pet where sex='m' union select sex,owner from pet where owner='Diane';
Expected 5 rows to be returned; however, only 4 rows are returned because all duplicate rows are removed.
using union all
Result when the 'all' keyword is used after the 'union' statement:
Select sex,owner from pet where sex='m' union all select sex,owner from pet where owner='Diane';
- Details
- Category: Mysql
- Hits: 571
The following example shows how you can use the bit group functions to calculate the number of days per month a user has visited a Web page.
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);
The example table contains year-month-day values representing visits by users to the page. To determine how many different days in each month these visits occur, use this query:
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;
- Details
- Category: Mysql
- Hits: 613
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;