mysql ubuntu 应用记录 --- Head First SQL (六)

mysql ubuntu 应用记录 — Head First SQL (六)

page382

mysql -u root -p
SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
| cat_db |
| gregs_list |
| mysql |
| performance_schema |
| project |
| sys |
±-------------------+
8 rows in set (0.00 sec)

USE gregs_list;

# Dump of table movie_table
# ------------------------------------------------------------

CREATE TABLE `movie_table` (
  `movie_id` int(11) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `rating` varchar(5) NOT NULL,
  `drama` varchar(10) NOT NULL,
  `comedy` varchar(10) NOT NULL,
  `action` varchar(10) NOT NULL,
  `gore` varchar(10) NOT NULL,
  `scifi` varchar(10) NOT NULL,
  `for_kids` varchar(10) NOT NULL,
  `cartoon` varchar(10) NOT NULL,  
  `purchased` date NOT NULL,
  PRIMARY KEY  (`movie_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `movie_table`  VALUES ('1','Monsters, Inc.',        'G','F','T','F','F','F','F','F','2002-03-06');
INSERT INTO `movie_table`  VALUES ('2','The Godfather' ,        'R','F','F','T','T','F','F','F','2001-05-02');
INSERT INTO `movie_table`  VALUES ('3','Gone with the Wind',     'G','T','F','F','F','F','F','F','2001-02-05');
INSERT INTO `movie_table`  VALUES ('4','American Pie',           'R','F','T','F','F','F','F','F','2003-04-19');
INSERT INTO `movie_table`  VALUES ('5','Nightmare on Elm Street','R','F','F','T','T','F','F','F','2003-04-19');
INSERT INTO `movie_table`  VALUES ('6','Casablanca',            'PG','T','F','F','F','F','F','F','2001-05-02');

ALTER TABLE movie_table ADD COLUMN category VARCHAR(10) NOT NULL ;

select * from movie_table where drama = ‘T’;
UPDATE movie_table SET category = ‘drama’ where drama = ‘T’;

mysql> UPDATE movie_table SET category = 'comedy' where comedy = 'T';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE movie_table SET category = 'action' where action = 'T';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE movie_table SET category = 'horror' where gore = 'T';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE movie_table SET category = 'scifi' where scifi = 'T';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE movie_table SET category = 'family' where for_kids = 'T';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE movie_table SET category = 'family' where cartoon = 'T' and rating = 'G';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE movie_table SET category = 'misc' where cartoon = 'T' and rating <> 'G';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from movie_table ;
+----------+-------------------------+--------+-------+--------+--------+------+-------+----------+---------+------------+----------+
| movie_id | title                   | rating | drama | comedy | action | gore | scifi | for_kids | cartoon | purchased  | category |
+----------+-------------------------+--------+-------+--------+--------+------+-------+----------+---------+------------+----------+
|        1 | Monsters, Inc.          | G      | F     | T      | F      | F    | F     | F        | F       | 2002-03-06 | comedy   |
|        2 | The Godfather           | R      | F     | F      | T      | T    | F     | F        | F       | 2001-05-02 | horror   |
|        3 | Gone with the Wind      | G      | T     | F      | F      | F    | F     | F        | F       | 2001-02-05 | drama    |
|        4 | American Pie            | R      | F     | T      | F      | F    | F     | F        | F       | 2003-04-19 | comedy   |
|        5 | Nightmare on Elm Street | R      | F     | F      | T      | T    | F     | F        | F       | 2003-04-19 | horror   |
|        6 | Casablanca              | PG     | T     | F      | F      | F    | F     | F        | F       | 2001-05-02 | drama    |
+----------+-------------------------+--------+-------+--------+--------+------+-------+----------+---------+------------+----------+
6 rows in set (0.01 sec)

case 方法

mysql> UPDATE movie_table 
    -> set category = 
    -> case 
    -> when drama = 'T' THEN 'drama'
    -> when comedy = 'T' THEN 'comedy'
    -> when action = 'T' THEN 'action'
    -> when gore = 'T' THEN 'horror'
    -> when scifi = 'T' THEN 'scifi'
    -> when for_kids = 'T' THEN 'family'
    -> when cartoon = 'T' THEN 'family'
    -> ELSE 'misc'
    -> END ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 6  Changed: 2  Warnings: 0

ALTER TABLE movie_table DROP COLUMN scifi , DROP COLUMN for_kids , DROP COLUMN cartoon;

select first_name ,AVG(sales) FROM cookie_sales GROUP BY first_name;

select first_name ,SUM(sales) from cookie_sales GROUP BY first_name ORDER BY SUM(sales) DESC;

select SUM(sales) FROM cookie_sales where first_name = ‘Nicole’;
select first_name ,MAX(sales) from cookie_sales group BY first_name;

alter table my_contacts ADD COLUMN interest4 varchar(20);

UPDATE my_contacts SET interest1 = SUBSTRING_INDEX(interests,’,’,1);