SQL Commmands fall into different categories
- 1. DDL (data definition language)
- 2. DML (data manipulation language)
- 3. DCL (data control language)
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.
You can see at any time which database is currently selected using SELECTDATABASE().
Creating the database is the easy part, but at this point it is empty, as SHOW TABLES tells you:
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);
DESCRIBE provides information about the columns in a table. It is a shortcut for SHOW COLUMNS FROM.
INSERT INTO ...... VALUES()
- INSERT INTO pet
- -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
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.
Fix only the erroneous record with an UPDATE statement:
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'
ORDER BY ---> SELECT name, birth FROM pet ORDER BY birth;
get back, recover
To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:
ATTENTION using NULL
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';
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;
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'
Getting information about Databases and tables
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:
- 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
maximum value for a column,
“What is the highest item number?”
SELECT MAX(article) AS article FROM shop;
row holding the maximum of a certain column:
- SELECT article, dealer, price
- FROM shop
- WHERE price=(SELECT MAX(price) FROM shop);