Difference between revisions of "SELECT"

From SQLZOO
Jump to: navigation, search
 
(14 intermediate revisions by 5 users not shown)
Line 1: Line 1:
A SELECT statement gets data from a table. Each table contains rows and columns - you can SELECT some columns and ignore others
+
CREATE TABLE ejb_queue_data (
*The column names on the select line control which columns you get
+
  queue_id int(4) unsigned NOT NULL AUTO_INCREMENT,
*The FROM clause controls which table you access
+
  item_pk varchar(20) NOT NULL,
<div class=params>schema:scott</div>
+
  of_counsel_id int(3) unsigned NOT NULL,
The table <code>games</code> shows the year and the city hosting the Olympic Games.
+
  in_process_user int(6) unsigned DEFAULT NULL,
<table border='1'>
+
  in_process_flag tinyint(1) unsigned NOT NULL DEFAULT '0',
<caption>games</caption>
+
  in_process_datetime datetime DEFAULT NULL,
<tr> <th>yr</th> <th>city</th> <th>y1</th></tr>
+
  creation_datetime datetime NOT NULL,
<tr> <td align='right'>2000</td> <td>Sydney</td>  <td></td></tr>
+
  PRIMARY KEY (queue_id,item_pk,of_counsel_id),
<tr> <td align='right'>2004</td> <td>Athens</td> <td>2000</td></tr>
+
  KEY Index_2 (creation_datetime)
<tr> <td align='right'>2008</td> <td>Beijing</td><td>2004</td> </tr>
+
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=latin1;
<tr> <td align='right'>2012</td> <td>London</td><td>2008</td> </tr>
+
</table>
+
<div class=qu>
+
The SELECT statement returns results from a <i>table</i>.
+
In this example the table is <code>games</code> and the columns are
+
<code>yr</code> and <code>city</code>.
+
<div class=tidy>
+
DROP TABLE games;
+
</div>
+
<div class=setup>
+
CREATE TABLE games(yr INT, city VARCHAR(20),y1 INT);
+
INSERT INTO games(city,yr) VALUES ('Sydney',2000);
+
INSERT INTO games(city,yr,y1) VALUES ('Athens',2004,2000);
+
INSERT INTO games(city,yr,y1) VALUES ('Beijing',2008,2004);
+
INSERT INTO games(city,yr,y1) VALUES ('London',2012,2008);
+
</div>
+
<div class=def>
+
SELECT yr, city FROM games
+
</div>
+
</div>
+
  
===See also:===
+
 
*[[SELECT_basics | SELECT Tutorial]] - practice using the SELECT command
+
 
*[[SELECT_.._WHERE  |SELECT ... WHERE]] - the WHERE clause allows you to get some rows but not others
+
CREATE TABLE ejb_queue_data_trigger (
 +
  queue_id int(4) unsigned NOT NULL AUTO_INCREMENT,
 +
  item_pk varchar(20) NOT NULL,
 +
  of_counsel_id int(3) unsigned NOT NULL,
 +
  in_process_user int(6) unsigned DEFAULT NULL,
 +
  in_process_flag tinyint(1) unsigned NOT NULL DEFAULT '0',
 +
  in_process_datetime datetime DEFAULT NULL,
 +
  creation_datetime datetime NOT NULL,
 +
  PRIMARY KEY (queue_id,item_pk,of_counsel_id),
 +
  KEY Index_2 (creation_datetime)
 +
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=latin1;

Latest revision as of 09:59, 14 April 2014

CREATE TABLE ejb_queue_data (

 queue_id int(4) unsigned NOT NULL AUTO_INCREMENT,
 item_pk varchar(20) NOT NULL,
 of_counsel_id int(3) unsigned NOT NULL,
 in_process_user int(6) unsigned DEFAULT NULL,
 in_process_flag tinyint(1) unsigned NOT NULL DEFAULT '0',
 in_process_datetime datetime DEFAULT NULL,
 creation_datetime datetime NOT NULL,
 PRIMARY KEY (queue_id,item_pk,of_counsel_id),
 KEY Index_2 (creation_datetime)

) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=latin1;


CREATE TABLE ejb_queue_data_trigger (

 queue_id int(4) unsigned NOT NULL AUTO_INCREMENT,
 item_pk varchar(20) NOT NULL,
 of_counsel_id int(3) unsigned NOT NULL,
 in_process_user int(6) unsigned DEFAULT NULL,
 in_process_flag tinyint(1) unsigned NOT NULL DEFAULT '0',
 in_process_datetime datetime DEFAULT NULL,
 creation_datetime datetime NOT NULL,
 PRIMARY KEY (queue_id,item_pk,of_counsel_id),
 KEY Index_2 (creation_datetime)

) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=latin1;

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense