SQL/MySQL/Select Clause/Select

Материал из SQL эксперт
Перейти к: навигация, поиск

Choose specific columns

   <source lang="sql">

/* mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.01 sec) mysql> SELECT firstname,lastName from employee where title="Programmer"; +-----------+----------+ | firstname | lastName | +-----------+----------+ | Fred | King | | John | Mac | | Arthur | Sam | | Alok | Nanda | +-----------+----------+ 4 rows in set (0.01 sec)

  • /

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee; SELECT firstname,lastName from employee where title="Programmer";

      </source>
   
  


Define and use variable in select clause

   <source lang="sql">

/* mysql> SELECT * FROM report; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 4.45 | | 0001 | B | 5.45 | | 0002 | A | 16.67 | | 0003 | B | 6.12 | | 0003 | C | 2.78 | | 0003 | D | 2.34 | | 0004 | D | 21.29 | +---------+--------+-------+ 7 rows in set (0.02 sec) mysql> /* Using User Variables */ mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report; +------------------------+------------------------+ | @min_price:=MIN(price) | @max_price:=MAX(price) | +------------------------+------------------------+ | 2.34 | 21.29 | +------------------------+------------------------+ 1 row in set (0.00 sec)

  • /

Drop table report;

CREATE TABLE report (

      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 report VALUES (1,"A",4.45),

                       (1,"B",5.45),
                       (2,"A",16.67),
                       (3,"B",6.12),
                       (3,"C",2.78),
                       (3,"D",2.34),
                       (4,"D",21.29);
   

SELECT * FROM report; /* Using User Variables */ SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;

      </source>
   
  


Performing a Single Row INSERT

   <source lang="sql">

/* mysql> Select * from Professor; +-------------+---------------+ | ProfessorID | Name | +-------------+---------------+ | 0 | Snail at work | +-------------+---------------+ 1 row in set (0.00 sec)

  • /

/* Create the table */ Drop TABLE Professor; CREATE TABLE Professor (

  ProfessorID INT NOT NULL PRIMARY KEY,
  Name        VARCHAR(50) NOT NULL)

TYPE = InnoDB; /* Real command */ INSERT INTO Professor (ProfessorID, Name) VALUES (0, "Snail at work");

/* Check the result */ Select * from Professor;

      </source>
   
  


Performing Mathematics

   <source lang="sql">

/* Prepare the data */ DROP TABLE Exam; CREATE TABLE Exam (

  StudentID  INT NOT NULL,
  ExamID     INT NOT NULL,
  Mark       INT,
  IfPassed   SMALLINT

)TYPE = InnoDB; /* Insert data for testing */ INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (1,1,55,1); INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (1,2,73,0); INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed) VALUES (2,3,44,1); /* Real command */ SELECT StudentID, ExamID, Mark * IfPassed AS MarkIfPassed FROM Exam;

      </source>
   
  


Reference column in select command

   <source lang="sql">

/* mysql> Drop table Bird; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Bird (

   ->     name VARCHAR(20),
   ->     owner VARCHAR(20),
   ->     species VARCHAR(20),
   ->     sex CHAR(1),
   ->     birth DATE,
   ->     death DATE
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | | RedBird | Yin | Bus | m | 1979-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 2 rows in set (0.00 sec) mysql> SELECT name, birth FROM Bird; +----------+------------+ | name | birth | +----------+------------+ | BlueBird | 1999-03-30 | | RedBird | 1979-03-30 | +----------+------------+ 2 rows in set (0.01 sec)

  • /

Drop table Bird; CREATE TABLE Bird (

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL); INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-03-30",NULL);

select * from Bird;

SELECT name, birth FROM Bird;

      </source>
   
  


Retrieve all columns in a table

   <source lang="sql">

/* mysql> select * from student ORDER BY first_name, last_name ASC; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 5 | Anna | Wolff | | 4 | Bruce | Lee | | 3 | Emily | Scarlett | | 2 | Gary | Burton | | 1 | John | Jones | | 7 | Steve | Alaska | | 6 | Vic | Andrews | +-----------+------------+-----------+ 7 rows in set (0.02 sec)

  • /

Drop table Student; CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,
  first_name      VARCHAR(50) NOT NULL,
  last_name      VARCHAR(50) NOT NULL
  

)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (2,"Gary", "Burton"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (7,"Steve", "Alaska");

select * from student ORDER BY first_name, last_name ASC;


      </source>
   
  


Select clause with condition

   <source lang="sql">

/*mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> SELECT firstname, lastName from employee where age > 32; +-----------+----------+ | firstname | lastName | +-----------+----------+ | Mike | Harper | +-----------+----------+ 1 row in set (0.00 sec)

  • /

Drop table employee; CREATE TABLE employee (

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru"); select * from employee;

SELECT firstname, lastName from employee where age > 32;

      </source>
   
  


Simple Retrieval: Returning All Columns

   <source lang="sql">

/* mysql> CREATE TABLE Student (

   ->    StudentID INT NOT NULL PRIMARY KEY,
   ->    Name      VARCHAR(50) NOT NULL
   -> )TYPE = InnoDB;

Query OK, 0 rows affected, 1 warning (0.08 sec) mysql> /* Insert data for testing */ mysql> INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); Query OK, 1 row affected (0.06 sec) mysql> INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests"); Query OK, 1 row affected (0.04 sec) mysql> /* Real command */ mysql> SELECT * FROM Student; +-----------+-------------+ | StudentID | Name | +-----------+-------------+ | 1 | Joe Wang | | 2 | Cory But | | 3 | JJ Harvests | +-----------+-------------+ 3 rows in set (0.03 sec)

  • /

/* Prepare the data */ DROP TABLE Student; CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,
  Name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");

/* Real command */ SELECT * FROM Student;

      </source>
   
  


Simple Retrieval:Returning a Single Column

   <source lang="sql">

/* mysql> /* Prepare the data */ mysql> DROP TABLE Student; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE Student (

   ->    StudentID INT NOT NULL PRIMARY KEY,
   ->    Name      VARCHAR(50) NOT NULL
   -> )TYPE = InnoDB;

Query OK, 0 rows affected, 1 warning (0.11 sec) mysql> /* Insert data for testing */ mysql> INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); Query OK, 1 row affected (0.04 sec) mysql> INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests"); Query OK, 1 row affected (0.06 sec) mysql> /* Real command */ mysql> SELECT Name FROM Student; +-------------+ | Name | +-------------+ | Joe Wang | | Cory But | | JJ Harvests | +-------------+ 3 rows in set (0.01 sec)

  • /

/* Prepare the data */ DROP TABLE Student; CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,
  Name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");

/* Real command */ SELECT Name FROM Student;

      </source>
   
  


Simple Retrieval:Returning Multiple Columns

   <source lang="sql">

/* mysql> /* Prepare the data */ mysql> DROP TABLE Student; Query OK, 0 rows affected (0.07 sec) mysql> CREATE TABLE Student (

   ->    StudentID INT NOT NULL PRIMARY KEY,
   ->    Name      VARCHAR(50) NOT NULL
   -> )TYPE = InnoDB;

Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> /* Insert data for testing */ mysql> INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests"); Query OK, 1 row affected (0.05 sec) mysql> /* Real command */ mysql> SELECT StudentID, Name FROM Student; +-----------+-------------+ | StudentID | Name | +-----------+-------------+ | 1 | Joe Wang | | 2 | Cory But | | 3 | JJ Harvests | +-----------+-------------+ 3 rows in set (0.00 sec)

  • /

/* Prepare the data */ DROP TABLE Student; CREATE TABLE Student (

  StudentID INT NOT NULL PRIMARY KEY,
  Name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");

/* Real command */ SELECT StudentID, Name FROM Student;

      </source>
   
  


Use defined variable in new select clause

   <source lang="sql">

/* mysql> SELECT * FROM report; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | A | 4.45 | | 0001 | B | 5.45 | | 0002 | A | 16.67 | | 0003 | B | 6.12 | | 0003 | C | 2.78 | | 0003 | D | 2.34 | | 0004 | D | 21.29 | +---------+--------+-------+ 7 rows in set (0.00 sec) mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report; +------------------------+------------------------+ | @min_price:=MIN(price) | @max_price:=MAX(price) | +------------------------+------------------------+ | 2.34 | 21.29 | +------------------------+------------------------+ 1 row in set (0.00 sec) mysql> /* Using User Variables */ mysql> SELECT * FROM report WHERE price=@min_price OR price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 2.34 | | 0004 | D | 21.29 | +---------+--------+-------+ 2 rows in set (0.00 sec)

  • /

Drop table report;

CREATE TABLE report (

      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 report VALUES (1,"A",4.45),

                       (1,"B",5.45),
                       (2,"A",16.67),
                       (3,"B",6.12),
                       (3,"C",2.78),
                       (3,"D",2.34),
                       (4,"D",21.29);
   

SELECT * FROM report; SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;

/* Using User Variables */ SELECT * FROM report WHERE price=@min_price OR price=@max_price;


      </source>