MySQL学习笔记

- having 子句，条件子句

- order by 子句，排序子句

- limit 子句，限制结果数量子句

- distinct, all 选项

mysql> status;
--------------
D:\MySQL\MySQL Server 5.0\bin\mysql.exe  Ver 14.12 Distrib 5.0.89, for Win32 (ia32)

Connection id:          3
Current database:       cookbook
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.89-community-nt MySQL Community Edition (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 49 min 34 sec

Threads: 2  Questions: 41  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 1  Queries per second avg: 0.014
--------------

:: 查看表结构
desc book;

:: 修改表book的author列的长度
alter table book modify author varchar(50); 

:: 删除price列
alter table book drop price;

:: 修改表名book 为 books
rename table book to books;

:: 修改表的字符集
alter table books character set GB2312;

-- 没有条件的内连接
select * from tb1 cross join tb2;

```sql
create table artist (
    a_id int unsigned not null auto_increment,
    name varchar(30) not null,
    primary key(a_id),
    unique(name)
);
insert into artist(name) values('Da vinci'),('Monet'),('Van Gogh'),('Picasso'),('Renoir');

create table painting (
    a_id int unsigned not null,
    p_id int unsigned not null auto_increment,
    title varchar(100) not null,
    state varchar(2) not null,
    price int unsigned,
    index(a_id),
    primary key(p_id)
);
insert into painting(a_id, p_id, title, state, price) values(1,1,'The Last Supper', 'IN', 34), (1,2,'The Mona Lisa', 'MI', 88), (3,3,'Starry Night', 'KY', 43), (3,4,'The Potato Eaters', 'KY', 64), (3,5,'The Rocks', 'IA', 90), (5,6,'Les Deux Soeurs', 'NE', 76);


-- 简单的连接2个表
mysql> select * from artist, painting where artist.a_id = painting.p_id;
mysql> select * from artist inner join painting on artist.a_id = painting.p_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name     | a_id | p_id | title             | state | price |
+------+----------+------+------+-------------------+-------+-------+
|    1 | Da vinci |    1 |    1 | The Last Supper   | IN    |    34 |
|    2 | Monet    |    1 |    2 | The Mona Lisa     | MI    |    88 |
|    4 | Picasso  |    3 |    4 | The Potato Eaters | KY    |    64 |
|    5 | Renoir   |    3 |    5 | The Rocks         | IA    |    90 |
|    3 | Van Gogh |    3 |    3 | Starry Night      | KY    |    43 |
+------+----------+------+------+-------------------+-------+-------+


mysql> select * from artist inner join painting on artist.a_id = painting.p_id where painting.state = 'KY';
+------+----------+------+------+-------------------+-------+-------+
| a_id | name     | a_id | p_id | title             | state | price |
+------+----------+------+------+-------------------+-------+-------+
|    4 | Picasso  |    3 |    4 | The Potato Eaters | KY    |    64 |
|    3 | Van Gogh |    3 |    3 | Starry Night      | KY    |    43 |
+------+----------+------+------+-------------------+-------+-------+


-- 哪幅画是Van Gogh的作品
mysql> select painting.title from artist inner join painting on artist.a_id=painting.a_id where artist.name='Van Gogh';
+-------------------+
| title             |
+-------------------+
| Starry Night      |
| The Potato Eaters |
| The Rocks         |
+-------------------+


-- 在Kentucky或Indiana州买了哪些画家的画作？
mysql> select distinct artist.name from artist inner join painting on artist.a_id=painting.a_id where painting.state in ('KY', 'IN');
+----------+
| name     |
+----------+
| Da vinci |
| Van Gogh |
+----------+



-- 找到对于每个画家你各收藏了多少作品？
mysql> select artist.name, count(*) as 'number of paintings' from artist inner join painting on artist.a_id=painting.a_id group by artist.name;
+----------+---------------------+
| name     | number of paintings |
+----------+---------------------+
| Da vinci |                   2 |
| Renoir   |                   1 |
| Van Gogh |                   3 |
+----------+---------------------+


-- 你为每个画家的作品花费了多少钱，包括总和以及每幅作品平均的花费
mysql> select artist.name, count(*) as 'number of paintings', sum(painting.price) as 'total price',
    -> avg(painting.price) as 'average price' from artist inner join painting on artist.a_id=painting.a_id
    -> group by artist.name;
+----------+---------------------+-------------+---------------+
| name     | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da vinci |                   2 |         122 |       61.0000 |
| Renoir   |                   1 |          76 |       76.0000 |
| Van Gogh |                   3 |         197 |       65.6667 |
+----------+---------------------+-------------+---------------+


mysql> select * from artist inner join painting on artist.a_id=painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name     | a_id | p_id | title             | state | price |
+------+----------+------+------+-------------------+-------+-------+
|    1 | Da vinci |    1 |    1 | The Last Supper   | IN    |    34 |
|    1 | Da vinci |    1 |    2 | The Mona Lisa     | MI    |    88 |
|    5 | Renoir   |    5 |    6 | Les Deux Soeurs   | NE    |    76 |
|    3 | Van Gogh |    3 |    3 | Starry Night      | KY    |    43 |
|    3 | Van Gogh |    3 |    4 | The Potato Eaters | KY    |    64 |
|    3 | Van Gogh |    3 |    5 | The Rocks         | IA    |    90 |
+------+----------+------+------+-------------------+-------+-------+
6 rows in set (0.00 sec)

mysql> select * from artist left join painting on artist.a_id=painting.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name     | a_id | p_id | title             | state | price |
+------+----------+------+------+-------------------+-------+-------+
|    1 | Da vinci |    1 |    1 | The Last Supper   | IN    |    34 |
|    1 | Da vinci |    1 |    2 | The Mona Lisa     | MI    |    88 |
|    2 | Monet    | NULL | NULL | NULL              | NULL  |  NULL |
|    4 | Picasso  | NULL | NULL | NULL              | NULL  |  NULL |
|    5 | Renoir   |    5 |    6 | Les Deux Soeurs   | NE    |    76 |
|    3 | Van Gogh |    3 |    3 | Starry Night      | KY    |    43 |
|    3 | Van Gogh |    3 |    4 | The Potato Eaters | KY    |    64 |
|    3 | Van Gogh |    3 |    5 | The Rocks         | IA    |    90 |
+------+----------+------+------+-------------------+-------+-------+
8 rows in set (0.06 sec)


mysql> select * from artist left join painting on artist.a_id=painting.a_id where painting.a_id is null;
+------+---------+------+------+-------+-------+-------+
| a_id | name    | a_id | p_id | title | state | price |
+------+---------+------+------+-------+-------+-------+
|    2 | Monet   | NULL | NULL | NULL  | NULL  |  NULL |
|    4 | Picasso | NULL | NULL | NULL  | NULL  |  NULL |
+------+---------+------+------+-------+-------+-------+
2 rows in set (0.00 sec)


mysql> select artist.* from artist left join painting on artist.a_id=painting.a_id where painting.a_id is null;
+------+---------+
| a_id | name    |
+------+---------+
|    2 | Monet   |
|    4 | Picasso |
+------+---------+
2 rows in set (0.00 sec)


-- 每个画家画了哪些作品？
mysql> select artist.name, painting.title from artist left join painting on artist.a_id=painting.a_id order by name, tit
le;
+----------+-------------------+
| name     | title             |
+----------+-------------------+
| Da vinci | The Last Supper   |
| Da vinci | The Mona Lisa     |
| Monet    | NULL              |
| Picasso  | NULL              |
| Renoir   | Les Deux Soeurs   |
| Van Gogh | Starry Night      |
| Van Gogh | The Potato Eaters |
| Van Gogh | The Rocks         |
+----------+-------------------+
-- 结果中title列为NULL的行对应了存在于artist表中，但是你没有收藏其作品的画家。



-- 在painting表中每个画家的作品有多少？
mysql> select artist.name as painter, count(painting.a_id) as count from artist inner join painting
    -> on artist.a_id = painting.a_id group by artist.name;
+----------+-------+
| painter  | count |
+----------+-------+
| Da vinci |     2 |
| Renoir   |     1 |
| Van Gogh |     3 |
+----------+-------+

-- 每个画家的作品有多少？
mysql> select artist.name as painter, count(painting.a_id) as count from artist left join painting
    -> on artist.a_id = painting.a_id group by artist.name;
+----------+-------+
| painter  | count |
+----------+-------+
| Da vinci |     2 |
| Monet    |     0 |
| Picasso  |     0 |
| Renoir   |     1 |
| Van Gogh |     3 |
+----------+-------+
-- count(*) 会对所有的连接行计数，甚至包含NULL值的行；
-- count(painting.a_id) 仅对非空值的行计数。 

> mysql -uroot -p
Enter password: ****
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

> net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。