Basic SQL

MySQL aims to have full ANSI SQL support, although some considerations are made in the interest of performance and features. This first section of the appendix deals with SQL-specific standards, whereas the other sections refer to more MySQL-specific issues. To start, Table B.1 lists the most common SQL terms.

Table B.1. These are the eight most common SQL query types.

SQL Terminology
Term	Used For
ALTER	Changing the structure of a table
CREATE	Creating a table or database
DELETE	Deleting records from a table
DROP	Deleting entire tables or databases
INSERT	Adding records to a table
SELECT	Retrieving information from a database
SHOW	Displaying information about the structure of a database or table
UPDATE	Modifying a database entry

Here are some examples as to how these queries would be used:

• CREATE DATABASE databasename

  (Creates a new database.)

• CREATE TABLE tablename (columnname1 column_definition, columnname2 column_definition)

  (Creates a new table, structured according to the column definitions.)

• DELETE FROM tablename WHERE columnname = 'value'

  (Removes every record from the table where columnname is equal to value.)

• DROP TABLE tablename

  (Deletes the table and all of its columns, rows, and data.)

• DROP DATABASE databasename

  (Deletes the database and all of its tables, columns, rows, and data.)

• INSERT INTO tablename VALUES ('x', 'y', 'z')

  (Inserts the values x, y, and z into the three columns of a new row in tablename. This syntax will work only if the number of values specified exactly matches the number of columns.)

• INSERT INTO tablename (column1name, column3name) VALUES ('x', 'y')

  (Inserts the value x into the first and the value y into the third column of a new row. This syntax will work as long as the specified columns exist.)

• INSERT INTO tablename VALUES ('x', 'y', 'z'), ('a', 'b', 'c')

  (Inserts two rows into the table. This is a MySQL addition to the SQL standard and will not necessarily work in every database application.)

• SELECT * FROM tablename

  (Returns every column of every row.)

• SELECT column1name, column2name FROM tablename

  (Returns just the two columns for every row.)

• UPDATE tablename SET columnname = 'x'

  (Sets the value of columnname to x for every row in the table.)