SQL

Card Set Information

Author:
dau108
ID:
144615
Filename:
SQL
Updated:
2012-04-01 12:15:17
Tags:
SQL
Folders:

Description:
SQL
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  1. SQL Commmands fall into different categories
    • 1. DDL (data definition language)
    • 2. DML (data manipulation language)
    • 3. DCL (data control language)
  2. USE
    Your database needs to be created only once, but you must select it for use each time you begin amysqlsession. You can do this by issuing a USE statement as shown in the example.
  3. SELECT DATABASE()
    You can see at any time which database is currently selected using SELECTDATABASE().
  4. SHOW TABLES
    Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:
  5. CREATE TABLE
    Use a CREATE TABLE statement to specify the layout of your table:

    • mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    • -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
  6. DESCRIBE
    DESCRIBE provides information about the columns in a table. It is a shortcut for SHOW COLUMNS FROM.
  7. INSERT INTO ...... VALUES()
    • INSERT INTO pet
    • -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
  8. SELECT
    The SELECT statement is used to pull information from a table. The general form of the statement is:

    • SELECT what_to_select
    • FROM which table
    • WHERE conditions_to_satisfy

    what_to_select indicates what you want to see. This can be a list of columns, or * to indicate “all columns.” which_table indicates the table from which you want to retrieve data. The WHERE clause is optional. If it is present, conditions_to_satisfy specifies one or more conditions that rows must satisfy to qualify for retrieval.
  9. UPDATE
    Fix only the erroneous record with an UPDATE statement:

    UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'
  10. SORTING ROWS
    ORDER BY ---> SELECT name, birth FROM pet ORDER BY birth;
  11. retrieve
    get back, recover
  12. NULL
    To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:

    ATTENTION using NULL
  13. PATTERN MATCHING
    _
    %
    LIKE
    NOT LIKE
    REGEXP
    SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. You do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

    SELECT * FROM pet WHERE name LIKE 'b%';

    SELECT * FROM pet WHERE name REGEXP '^b';
  14. COUNTING ROWS

    COUNT(*)
    GROUP BY
    Counting the total number of animals you have is the same question as “How many rows are in the pet table?” because there is one record per pet. COUNT(*) counts the number of rows, so the query to count your animals looks like this:

    SELECT COUNT(*) FROM pet;

    The preceding query uses GROUP BY to group all records for each owner. The use of COUNT() in conjunction with GROUP BY is useful for characterizing your data under various groupings. The following examples show different ways to perform animal census operations.

    • Number of animals per species :
    • SELECT species, COUNT(*) FROM pet GROUP BY species;
  15. Using More Than one Table
    • 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'
  16. Getting information about Databases and tables

    SHOW DATABASES
    DATABASE()
    SHOW TABLES;
    DESCRIBE
    You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

    SELECT DATABASE();

    • 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:
  17. maximum value for a column,
    “What is the highest item number?”
    MAX
    SELECT MAX(article) AS article FROM shop;
  18. row holding the maximum of a certain column:
    • SELECT article, dealer, price
    • FROM shop
    • WHERE price=(SELECT MAX(price) FROM shop);

What would you like to do?

Home > Flashcards > Print Preview