Set the Default Database

When working with the default database, you don't need to reference it explicitly in statements. To see which database is currently the default, use the SHOW DATABASE statement (note the singular form):

Insert Rows into a Table

To insert a row into a table, use INSERT INTO followed by the table name and then the column values listed in the order in which the columns appear in the table:

copy

icon/buttons/copy

>INSERTINTOaccountsVALUES(1,10000.50);

If you want to pass column values in a different order, list the column names explicitly and provide the column values in the corresponding order:

copy

icon/buttons/copy

>INSERTINTOaccounts(balance,id)VALUES(25000.00,2);

To insert multiple rows into a table, use a comma-separated list of parentheses, each containing column values for one row:

copy

icon/buttons/copy

>INSERTINTOaccountsVALUES(3,8100.73),(4,9400.10);

Defaults values are used when you leave specific columns out of your statement, or when you explicitly request default values. For example, both of the following statements would create a row with balance filled with its default value, in this case NULL:

Create an Index

Indexes help locate data without having to look through every row of a table. They're automatically created for the primary key of a table and any columns with a Unique constraint.

To create an index for non-unique columns, use CREATE INDEX followed by an optional index name and an ON clause identifying the table and column(s) to index. For each column, you can choose whether to sort ascending (ASC) or descending (DESC).

copy

icon/buttons/copy

>CREATEINDEXbalance_idxONaccounts(balanceDESC);

You can create indexes during table creation as well; just include the INDEX keyword followed by an optional index name and the column(s) to index:

If a table has a primary key, you can use that in the WHERE clause to reliably update specific rows; otherwise, each row matching the WHERE clause is updated. When there's no WHERE clause, all rows in the table are updated.

Delete Rows in a Table

To delete rows from a table, use DELETE FROM followed by the table name and a WHERE clause identifying the rows to delete:

Just as with the UPDATE statement, if a table has a primary key, you can use that in the WHERE clause to reliably delete specific rows; otherwise, each row matching the WHERE clause is deleted. When there's no WHERE clause, all rows in the table are deleted.