因为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)
估计最多就考这些吧。。。。那就先学到这
Comments | 1 条评论