CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); //創建表
mysql> show tables; //查看數據庫中的表
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
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 | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
//導入txt文件中的數據
LOAD DATA LOCAL INFILE 'E:\\pet.txt' INTO TABLE pet; //導入txt文件中的數據
load data local infile 'E:\\data.txt' into table pet;
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); //一次導入一條數據
Query OK, 1 row affected (0.40 sec)
mysql> select * from pet where name='bowser'; //查詢name=XxxxX的數據
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| bowser | diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)
mysql> select * from pet where birth>'1998-1-1'; //查詢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 |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> select * from pet where species='dog' and sex='f'; //組合條件的使用
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| buffy | harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> select * from pet where species='dog' or species='bird'; //or操作符，滿足一個條件即可
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| 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 | | 1997-12-09 | NULL |
+----------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)
mysql> select * from pet where (species='cat' and sex='m') //and、or混用
-> 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 |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> select name,birth from pet; //輸出制定的字段內容
+----------+------------+
| name | birth |
+----------+------------+
| fluff | 1993-02-04 |
| claws | 1994-03-17 |
| buffy | 1989-05-13 |
| fang | 1990-08-27 |
| bowser | 1979-08-31 |
| chirpy | 1998-09-11 |
| whistler | 1997-12-09 |
| slim | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)
mysql> select distinct owner from pet; //對輸出的字段做去重處理
+--------+
| owner |
+--------+
| harold |
| gwen |
| benny |
| diane |
+--------+
4 rows in set (0.00 sec)
mysql> select name,birth from pet order by birth; //對birth排序，默認升序
+----------+------------+
| name | birth |
+----------+------------+
| bowser | 1979-08-31 |
| buffy | 1989-05-13 |
| fang | 1990-08-27 |
| fluff | 1993-02-04 |
| claws | 1994-03-17 |
| slim | 1996-04-29 |
| whistler | 1997-12-09 |
| chirpy | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)
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 |
| fluff | 1993-02-04 |
| fang | 1990-08-27 |
| buffy | 1989-05-13 |
| bowser | 1979-08-31 |
+----------+------------+
9 rows in set (0.00 sec)
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 |
| fluff | cat | 1993-02-04 |
| fang | dog | 1990-08-27 |
| buffy | dog | 1989-05-13 |
| bowser | dog | 1979-08-31 |
| Puffball | hamster | 1999-03-30 |
| slim | snake | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)
//計算年齡
mysql> select name,birth,curdate(), //當前日期
-> (year(curdate())-year(birth))-(right(curdate(),5)
-> from pet;
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| fluff | 1993-02-04 | 2016-10-30 | 23 |
| claws | 1994-03-17 | 2016-10-30 | 22 |
| buffy | 1989-05-13 | 2016-10-30 | 27 |
| fang | 1990-08-27 | 2016-10-30 | 26 |
| bowser | 1979-08-31 | 2016-10-30 | 37 |
| chirpy | 1998-09-11 | 2016-10-30 | 18 |
| whistler | 1997-12-09 | 2016-10-30 | 18 |
| slim | 1996-04-29 | 2016-10-30 | 20 |
| Puffball | 1999-03-30 | 2016-10-30 | 17 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
//針對rigth做的解釋案例
mysql> select curdate(); //當前日期，格式為2016-10-30
+------------+
| curdate() |
+------------+
| 2016-10-30 |
+------------+
mysql> select right(curdate(),5); //取後邊5個字符，如此可以讓年齡計算更加的精確
+--------------------+
| right(curdate(),5) |
+--------------------+
| 10-30 |
+--------------------+
mysql> select name,birth,curdate(),
-> (year(curdate())-year(birth))-(right(curdate(),5)
-> from pet
-> order by age; //按照年齡排序，age是字段的別稱
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2016-10-30 | 17 |
| chirpy | 1998-09-11 | 2016-10-30 | 18 |
| whistler | 1997-12-09 | 2016-10-30 | 18 |
| slim | 1996-04-29 | 2016-10-30 | 20 |
| claws | 1994-03-17 | 2016-10-30 | 22 |
| fluff | 1993-02-04 | 2016-10-30 | 23 |
| fang | 1990-08-27 | 2016-10-30 | 26 |
| buffy | 1989-05-13 | 2016-10-30 | 27 |
| bowser | 1979-08-31 | 2016-10-30 | 37 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
mysql必知必會，可參考：
分享到:
閱讀更多 思夢PHP 的文章
