mysql> Ready for new command.
-> Waiting for next line of multiple-line command.
'> Waiting for next line, waiting for completion of a string that began with a single quote (“'”).
"> Waiting for next line, waiting for completion of a string that began with a double quote (“"”). `> Waiting for next line, waiting for completion of an identifier that began with a backtick (“`”).
/*> Waiting for next line, waiting for completion of a comment that began with /*.
________________________________________________
________________________________________________
mysql> SELECT VERSION(), CURRENT_DA
+------------------+--------------+
VERSION() CURRENT_DATE
+------------------+--------------+
5.1.34-community 2009-05-16
+------------------+--------------+
1 row in set (0.00 sec)
________________________________________________
________________________________________________
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
SIN(PI()/4) (4+1)*5
+------------------+---------+
0.70710678118655 25
+------------------+---------+
1 row in set (0.02 sec
________________________________________________
________________________________________________
mysql> SELECT VERSION(); SELECT NOW();
+-----------------+
VERSION()
+-----------------+
5.1.34-community
+-----------------+
1 row in set (0.00 sec)
+---------------------+
NOW()
+---------------------+
2009-05-16 11:17:49
+---------------------+
1 row in set (0.00 sec)
________________________________________________
________________________________________________
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+---------------+--------------+
USER() CURRENT_DATE
+---------------+--------------+
root@localhost 2009-05-16
+---------------+--------------+
________________________________________________
________________________________________________
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age <>
'> `c
'> \c
'> '\c
mysql>
________________________________________________
mysql> SHOW DATABASES;
+----------+
Database
+----------+
mysql
test
tmp
+----------+
________________________________________________
________________________________________________
mysql> CREATE DATABASE menagerie;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> SHOW TABLES;
+---------------------+
Tables in menagerie
+---------------------+
pet
+---------------------+
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
Field Type Null Key Default Extra
+---------+-------------+------+-----+---------+-------+
name varchar(20) YES NULL
owner varchar(20) YES NULL
species varchar(20) YES NULL
sex char(1) YES NULL
birth date YES NULL
death date YES NULL
+---------+-------------+------+-----+---------+-------+
________________________________________________
________________________________________________
in text file use /N for NULL an TAB to space between values
name owner species sex birth death
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N
save to c:\temp\pet.txt
mysql> LOAD DATA LOCAL INFILE 'c:/temp/pet.txt' INTO TABLE pet;
Query OK, 9 rows affected, 10 warnings (0.02 sec)Records: 9
Deleted: 0 Skipped: 0 Warnings: 10
mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)
________________________________________________
________________________________________________
Retrieving Information from a Table
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.
________________________________________________
________________________________________________
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
name owner species sex birth death
+----------+--------+---------+------+------------+------------+
Fluffy Harold cat f 1993-02-04 NULL
Claws Gwen cat m 1994-03-17 NULL
Buffy Harold dog f 1989-05-13 NULL
Fang Benny dog m 1990-08-27 NULL
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 NULL
Whistler Gwen bird NULL 1997-12-09 NULL
Slim Benny snake m 1996-04-29 NULL
Puffball Diane hamster f 1999-03-30 NULL
+----------+--------+---------+------+------------+------------+
________________________________________________
________________________________________________
Delete info i table
mysql> DELETE FROM pet;
________________________________________________
________________________________________________
change value in Birth to 1989-08-31
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
________________________________________________
________________________________________________
mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
name owner species sex birth death
+--------+-------+---------+------+------------+------------+
Bowser Diane dog m 1989-08-31 1995-07-29
+--------+-------+---------+------+------------+------------+
________________________________________________
________________________________________________
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
name owner species sex birth death
+----------+-------+---------+------+------------+-------+
Chirpy Gwen bird f 1998-09-11 NULL
Puffball Diane hamster f 1999-03-30 NULL
+----------+-------+---------+------+------------+-------________________________________________________
________________________________________________
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
name owner species sex birth death
+-------+--------+---------+------+------------+-------+
Buffy Harold dog f 1989-05-13 NULL
+-------+--------+---------+------+------------+-------+
________________________________________________
________________________________________________
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
name owner species sex birth death
+----------+-------+---------+------+------------+-------+
Chirpy Gwen bird f 1998-09-11 NULL
Whistler Gwen bird NULL 1997-12-09 NULL
Slim Benny snake m 1996-04-29 NULL
+----------+-------+---------+------+------------+-------+
________________________________________________
________________________________________________
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
name owner species sex birth death
+-------+--------+---------+------+------------+-------+
Claws Gwen cat m 1994-03-17 NULL
Buffy Harold dog f 1989-05-13 NULL
+-------+--------+---------+------+------------+-------+
________________________________________________
________________________________________________
mysql> SELECT owner FROM pet;
+--------+
owner
+--------+
Harold
Gwen
Harold
Benny
Diane
Gwen
Gwen
Benny
Diane
+--------+
mysql> SELECT DISTINCT owner FROM pet;
+--------+
owner
+--------+
Benny
Diane
Gwen
Harold
+--------+
________________________________________________
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
name species birth
+--------+---------+------------+
Fluffy cat 1993-02-04
Claws cat 1994-03-17
Buffy dog 1989-05-13
Fang dog 1990-08-27
Bowser dog 1989-08-31
+--------+---------+------------+
________________________________________________
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
name birth
+----------+------------+
Buffy 1989-05-13
Bowser 1989-08-31
Fang 1990-08-27
Fluffy 1993-02-04
Claws 1994-03-17
Slim 1996-04-29
Whistler 1997-12-09
Chirpy 1998-09-11
Puffball 1999-03-30
+----------+------------+
________________________________________________
________________________________________________
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
name birth
+----------+------------+
Puffball 1999-03-30
Chirpy 1998-09-11
Whistler 1997-12-09
Slim 1996-04-29
Claws 1994-03-17
Fluffy 1993-02-04
Fang 1990-08-27
Bowser 1989-08-31
Buffy 1989-05-13
+----------+------------+
________________________________________________
________________________________________________
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
+----------+---------+------------+
name species birth
+----------+---------+------------+
Chirpy bird 1998-09-11
Whistler bird 1997-12-09
Claws cat 1994-03-17
Fluffy cat 1993-02-04
Fang dog 1990-08-27
Bowser dog 1989-08-31
Buffy dog 1989-05-13
Puffball hamster 1999-03-30
Slim snake 1996-04-29
+----------+---------+------------+
________________________________________________
________________________________________________
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> FROM pet;
+----------+------------+------------+------+
name birth CURDATE() age
+----------+------------+------------+------+
Fluffy 1993-02-04 2003-08-19 10
Claws 1994-03-17 2003-08-19 9
Buffy 1989-05-13 2003-08-19 14
Fang 1990-08-27 2003-08-19 12
Bowser 1989-08-31 2003-08-19 13
Chirpy 1998-09-11 2003-08-19 4
Whistler 1997-12-09 2003-08-19 5
Slim 1996-04-29 2003-08-19 7
Puffball 1999-03-30 2003-08-19 4
+----------+------------+------------+------+
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> FROM pet ORDER BY age;
name birth CURDATE() age
+----------+------------+------------+------+
Chirpy 1998-09-11 2003-08-19 4
Puffball 1999-03-30 2003-08-19 4
Whistler 1997-12-09 2003-08-19 5
Slim 1996-04-29 2003-08-19 7
Claws 1994-03-17 2003-08-19 9
Fluffy 1993-02-04 2003-08-19 10
Fang 1990-08-27 2003-08-19 12
Bowser 1989-08-31 2003-08-19 13
Buffy 1989-05-13 2003-08-19 14
+----------+------------+------------+------+
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)
-> FROM pet ORDER BY name;
+----------+------------+------------+------+
name birth CURDATE() age
+----------+------------+------------+------+
Bowser 1989-08-31 2003-08-19 13
Buffy 1989-05-13 2003-08-19 14
Chirpy 1998-09-11 2003-08-19 4
Claws 1994-03-17 2003-08-19 9
Fang 1990-08-27 2003-08-19 12
Fluffy 1993-02-04 2003-08-19 10
Puffball 1999-03-30 2003-08-19 4
Slim 1996-04-29 2003-08-19 7
Whistler 1997-12-09 2003-08-19 5
+----------+------------+------------+------+
________________________________________________
________________________________________________
mysql> SELECT name, birth, death,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
name birth death age
+--------+------------+------------+------+
Bowser 1989-08-31 1995-07-29 5
+--------+------------+------------+------+
________________________________________________
________________________________________________
MySQL provides several functions for extracting parts of dates, such as YEAR(), MONTH(), and DAYOFMONTH(). MONTH() is the appropriate function here.
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
name birth MONTH(birth)
+----------+------------+--------------+
Fluffy 1993-02-04 2
Claws 1994-03-17 3
Buffy 1989-05-13 5
Fang 1990-08-27 8
Bowser 1989-08-31 8
Chirpy 1998-09-11 9
Whistler 1997-12-09 12
Slim 1996-04-29 4
Puffball 1999-03-30 3
+----------+------------+--------------+
________________________________________________
________________________________________________
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
name owner species sex birth death
+--------+--------+---------+------+------------+------------+
Buffy Harold dog f 1989-05-13 NULL
Bowser Diane dog m 1989-08-31 1995-07-29
+--------+--------+---------+------+------------+------------+
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
name owner species sex birth death
+--------+--------+---------+------+------------+-------+
Fluffy Harold cat f 1993-02-04 NULL
Buffy Harold dog f 1989-05-13 NULL
+--------+--------+---------+------+------------+-------+
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
name owner species sex birth death
+----------+-------+---------+------+------------+------------+
Claws Gwen cat m 1994-03-17 NULL
Bowser Diane dog m 1989-08-31 1995-07-29
Whistler Gwen bird NULL 1997-12-09 NULL
+----------+-------+---------+------+------------+------------+
To find names containing exactly five characters, use five instances of the “_” pattern character: mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
name owner species sex birth death
+-------+--------+---------+------+------------+-------+
Claws Gwen cat m 1994-03-17 NULL
Buffy Harold dog f 1989-05-13 NULL
+-------+--------+---------+------+------------+-------+
To find names beginning with “b”, use “^” to match the beginning of the name:
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
To find names ending with “fy”, use “$” to match the end of the name:
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
________________________________________________
________________________________________________
mysql> SELECT COUNT(*) FROM pet;
+----------+
COUNT(*)
+----------+
9
+----------+
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
owner COUNT(*)
+--------+----------+
Benny 2
Diane 2
Gwen 3
Harold 2
+--------+----------+
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
species COUNT(*)
+---------+----------+
bird 2
cat 2
dog 3
hamster 1
snake 1
+---------+----------+
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
sex COUNT(*)
+------+----------+
NULL 1
f 4
m 4
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
species sex COUNT(*)
+---------+------+----------+
bird NULL 1
bird f 1
cat f 1
cat m 1
dog f 1
dog m 2
hamster f 1
snake m 1
+---------+------+----------+
________________________________________________
________________________________________________
________________________________________________
________________________________________________
________________________________________________
________________________________________________
Ingen kommentarer:
Legg inn en kommentar