某花的SQL笔记(1)

因为web要用sql，而且期末考刚好要考，所以我就最近入门了一下SQL。。。

比如我想储存我电脑上的音乐的数据

mysql> create DATABASE Flower1;
mysql> use Flower1;
mysql> create table Artist (
    -> AID INT,
    -> NAME CHAR(30),
    -> PRIMARY KEY(AID)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_flower1 |
+-------------------+
| Artist            |
+-------------------+
1 row in set (0.00 sec)

mysql> desc Artist;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| AID   | int      | NO   | PRI | NULL    |       |
| NAME  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

多个插入要注意逗号隔开

mysql> insert into Artist (AID,NAME) VALUES (1,"Mayday");
Query OK, 1 row affected (0.00 sec)

mysql> insert into Artist (AID,NAME) VALUES (2,"XJapan"),(3,"The Gazette"),(4,"Jay Chou");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from artist
    -> ;
+-----+-------------+
| AID | NAME        |
+-----+-------------+
|   1 | Mayday      |
|   2 | XJapan      |
|   3 | The Gazette |
|   4 | Jay Chou    |
+-----+-------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO ARtist
    -> (AID,NAME)
    -> VALUES
    -> (5,"Hans Zimmer");
Query OK, 1 row affected (0.00 sec)

数据表名不区分大小写！

mysql> INSERT INTO ARtist
    -> (AID,NAME)
    -> VALUES
    -> (5,"Hans Zimmer");
Query OK, 1 row affected (0.00 sec)

嗯。。关于换行。。

mysql> INSERT INTo artist
    -> (AID,NAME)
    -> VALUES
    -> (6,
    -> "Luna S
    "> ea")
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from artist;
+-----+-------------+
| AID | NAME        |
+-----+-------------+
|   1 | Mayday      |
|   2 | XJapan      |
|   3 | The Gazette |
|   4 | Jay Chou    |
|   5 | Hans Zimmer |
|   6 | Luna S
ea   |
+-----+-------------+
6 rows in set (0.00 sec)

删除数据

mysql> delete from Artist
    -> where AID=6;
Query OK, 1 row affected (0.00 sec)

或者这样。。。

mysql> UPDATE Artist SET NAME="Luna Sea" where AID=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

现在再弄一个歌单～

mysql> create table Song( SID INT key auto_increment, AID INT, ALBUM CHAR(30), NAME CHAR(30))ENGINE=InnoDB auto_increment=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

key auto_increment表示设SID为主键，输入null会自动+1，为什么有warning啊QwQ

mysql> INSERT INTO song values (1,1,"2012","Second Round"), (null,2,"Forever Love","The Last Live"), (null,3,"Cassis","Nil"), (null,4,"Mojito","Mojito"), (null,5,"Time","Inception")
, (null,6,"Gravity","Never Sold Out 2");
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from song;
+-----+------+--------------+------------------+
| SID | AID  | ALBUM        | NAME             |
+-----+------+--------------+------------------+
|   1 |    1 | 2012         | Second Round     |
|   2 |    2 | Forever Love | The Last Live    |
|   3 |    3 | Cassis       | Nil              |
|   4 |    4 | Mojito       | Mojito           |
|   5 |    5 | Time         | Inception        |
|   6 |    6 | Gravity      | Never Sold Out 2 |
+-----+------+--------------+------------------+
6 rows in set (0.00 sec)

然后我发现我把ALBUM和NAME两列弄反了。。

mysql> alter table song
    -> change ALBUM NAME1 char(30),
    -> change NAME ALBUM char(30),
    -> change NAME1 NAME char(30);
ERROR 1054 (42S22): Unknown column 'NAME1' in 'song'

mysql> alter table song change ALBUM NAME1 char(30), change NAME ALBUM char(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table song change NAME1 NAME char(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from song;
+-----+------+--------------+------------------+
| SID | AID  | NAME         | ALBUM            |
+-----+------+--------------+------------------+
|   1 |    1 | 2012         | Second Round     |
|   2 |    2 | Forever Love | The Last Live    |
|   3 |    3 | Cassis       | Nil              |
|   4 |    4 | Mojito       | Mojito           |
|   5 |    5 | Time         | Inception        |
|   6 |    6 | Gravity      | Never Sold Out 2 |
+-----+------+--------------+------------------+
6 rows in set (0.00 sec)

忘了加counts字段了….

mysql> alter table song add counts int NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from song;
+-----+------+--------------+------------------+--------+
| SID | AID  | NAME         | ALBUM            | counts |
+-----+------+--------------+------------------+--------+
|   1 |    1 | 2012         | Second Round     |      0 |
|   2 |    2 | Forever Love | The Last Live    |      0 |
|   3 |    3 | Cassis       | Nil              |      0 |
|   4 |    4 | Mojito       | Mojito           |      0 |
|   5 |    5 | Time         | Inception        |      0 |
|   6 |    6 | Gravity      | Never Sold Out 2 |      0 |
+-----+------+--------------+------------------+--------+
6 rows in set (0.00 sec)

NOT NULL让初始值变成0

update更新一下～

mysql> update song set counts=counts+1;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

试试join

mysql> select a.name,b.name FROM Artist a Inner join Song b on a.AID=b.AID;
+-------------+--------------+
| name        | name         |
+-------------+--------------+
| Mayday      | 2012         |
| XJapan      | Forever Love |
| The Gazette | Cassis       |
| Jay Chou    | Mojito       |
| Hans Zimmer | Time         |
| Luna Sea    | Gravity      |
+-------------+--------------+
6 rows in set (0.01 sec)

mysql> select a.name,b.name FROM Artist a,Song b where a.AID=b.AID;
+-------------+--------------+
| name        | name         |
+-------------+--------------+
| Mayday      | 2012         |
| XJapan      | Forever Love |
| The Gazette | Cassis       |
| Jay Chou    | Mojito       |
| Hans Zimmer | Time         |
| Luna Sea    | Gravity      |
+-------------+--------------+
6 rows in set (0.00 sec)

mysql> insert into song values (null,2,"Scars","Dahlia",2), (null,2,"Drain","The Last Live",3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select a.name As Artist,b.name As Song FROM Artist a Inner join Song b on a.AID=b.AID order by b.counts desc;
+-------------+--------------+
| Artist      | Song         |
+-------------+--------------+
| XJapan      | Drain        |
| XJapan      | Scars        |
| Mayday      | 2012         |
| XJapan      | Forever Love |
| The Gazette | Cassis       |
| Jay Chou    | Mojito       |
| Hans Zimmer | Time         |
| Luna Sea    | Gravity      |
+-------------+--------------+
8 rows in set (0.00 sec)

顺便试了一下order by

mysql> select a.name As Artist,b.name As Song FROM Artist a Inner join Song b on a.AID=b.AID order by b.counts desc,a.name;
+-------------+--------------+
| Artist      | Song         |
+-------------+--------------+
| XJapan      | Drain        |
| XJapan      | Scars        |
| Hans Zimmer | Time         |
| Jay Chou    | Mojito       |
| Luna Sea    | Gravity      |
| Mayday      | 2012         |
| The Gazette | Cassis       |
| XJapan      | Forever Love |
+-------------+--------------+
8 rows in set (0.01 sec)

mysql> select a.name As Artist,b.name As Song FROM Artist a Inner join Song b on a.AID=b.AID order by b.counts,a.name desc;
+-------------+--------------+
| Artist      | Song         |
+-------------+--------------+
| XJapan      | Forever Love |
| The Gazette | Cassis       |
| Mayday      | 2012         |
| Luna Sea    | Gravity      |
| Jay Chou    | Mojito       |
| Hans Zimmer | Time         |
| XJapan      | Scars        |
| XJapan      | Drain        |
+-------------+--------------+
8 rows in set (0.00 sec)

然而mysql中不支持Full Outer Join…

需要用union

mysql> select name as Song,album as Album From Song
    -> union
    -> select * From Artist;
+--------------+------------------+
| Song         | Album            |
+--------------+------------------+
| 2012         | Second Round     |
| Forever Love | The Last Live    |
| Cassis       | Nil              |
| Mojito       | Mojito           |
| Time         | Inception        |
| Gravity      | Never Sold Out 2 |
| Scars        | Dahlia           |
| Drain        | The Last Live    |
| unnamed song | ?                |
| 1            | Mayday           |
| 2            | XJapan           |
| 3            | The Gazette      |
| 4            | Jay Chou         |
| 5            | Hans Zimmer      |
| 6            | Luna Sea         |
| 7            | Zilch            |
+--------------+------------------+
16 rows in set (0.01 sec)

mysql> select a.name as Artist,b.name as Song From Artist a Left Join Song b on a.AID=b.AID
    -> union
    -> select a.name as Artist,b.name as Song From Artist a RIGHT Join Song b on a.AID=b.AID
    -> ;
+-------------+--------------+
| Artist      | Song         |
+-------------+--------------+
| Mayday      | 2012         |
| XJapan      | Drain        |
| XJapan      | Scars        |
| XJapan      | Forever Love |
| The Gazette | Cassis       |
| Jay Chou    | Mojito       |
| Hans Zimmer | Time         |
| Luna Sea    | Gravity      |
| Zilch       | NULL         |
| NULL        | unnamed song |
+-------------+--------------+
10 rows in set (0.00 sec)

mysql> select a.name as Artist,b.name as Song From Artist a Left Join Song b on a.AID=b.AID where b.name is null
    -> union
    -> select a.name as Artist,b.name as Song From Artist a Right Join Song b on a.AID=b.AID where a.name is null
    -> ;
+--------+--------------+
| Artist | Song         |
+--------+--------------+
| Zilch  | NULL         |
| NULL   | unnamed song |
+--------+--------------+
2 rows in set (0.01 sec)

估计最多就考这些吧。。。。那就先学到这

Post Views: 12

Author: acheing

One Comment

Leave a Comment 取消回复

Author: acheing

Related Posts

One Comment

Leave a Comment 取消回复