mysql> select name,birth,death,
-> (year(death)-year(birth))-(right(death,5)< right(birth,5)) //計算已死亡年數
-> as age
-> from pet where death is not null order by age; 抓取不為空的數據
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| bowser | 1979-08-31 | 1995-07-29 | 15 |
+--------+------------+------------+------+
1 row in set (0.00 sec)
mysql> select name,birth,month(birth) from pet; /出生月份
+----------+------------+--------------+
| name | birth | month(birth) |
+----------+------------+--------------+
| fluff | 1993-02-04 | 2 |
| claws | 1994-03-17 | 3 |
| buffy | 1989-05-13 | 5 |
| fang | 1990-08-27 | 8 |
| bowser | 1979-08-31 | 8 |
| chirpy | 1998-09-11 | 9 |
| whistler | 1997-12-09 | 12 |
| slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
9 rows in set (0.01 sec)
mysql> select name,birth from pet
-> where month(birth)=month(date_add(curdate(),interval 2 month));
//在當前月份的基礎上加2個月，得到月份是當前月份+2的數據
+----------+------------+
| name | birth |
+----------+------------+
| whistler | 1997-12-09 |
+----------+------------+
1 row in set (0.00 sec)
“_”匹配任何單個字符，
“%”匹配任意數目字符(包括零字符)
mysql> select * from pet where name like 'b%'; //查找name以字母b開頭的數據
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| buffy | harold | dog | f | 1989-05-13 | NULL |
| bowser | diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> select * from pet where name like '%fy'; //查找以fy結尾的數據
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| buffy | harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
1 row in set (0.02 sec)
mysql> select * from pet where name like '%w%'; //查找name中包含了w字母的數據
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| claws | gwen | cat | m | 1994-03-17 | NULL |
| bowser | diane | dog | m | 1979-08-31 | 1995-07-29 |
| whistler | gwen | bird | | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pet where name like '_____'; //查找name正好包含5個字母的數據
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| fluff | harold | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
3 rows in set (0.00 sec)
‘.’匹配任何單個的字符。
[...]：匹配在方括號內的任何字符。
例如，“[abc]”匹配“a”、“b”或“c”。為了命名字符的範圍，使用一個“-”。
[a-z]：匹配任何字母
[0-9]：匹配任何數字
“ * ”匹配零個或多個在它前面的字符。
例如，“x*”匹配任何數量的“x”字符，“[0-9]*”匹配任何數量的數字，而“.*”匹配任何數量的任何字符。
“^”：開頭
“$”：結尾
mysql> select * from pet where name regexp '^b'; //查找以模式字母b開始的數據
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| buffy | harold | dog | f | 1989-05-13 | NULL |
| bowser | diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql> select * from pet where name regexp 'fy$'; //查找以模式字母fy結尾的數據
+-------+--------+---------+------+------------+-------+
| 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 name regexp 'w'; //正則查找包含字母w的數據
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| claws | gwen | cat | m | 1994-03-17 | NULL |
| bowser | diane | dog | m | 1979-08-31 | 1995-07-29 |
| whistler | gwen | bird | | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from pet where name regexp '^.{5}$'; //查找name正好包含5個字母的數據
//正則的方式
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| fluff | harold | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
3 rows in set (0.00 sec)
mysql> select count(*) from pet; //計數行
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
mysql> select owner,count(*) from pet group by owner; //分組計數
+--------+----------+
| owner | count(*) |
+--------+----------+
| benny | 2 |
| diane | 2 |
| gwen | 3 |
| harold | 2 |
+--------+----------+
4 rows in set (0.00 sec)
mysql> select species,sex,count(*) from pet group by species,sex; //按照species和sex2個條件進行分組
+---------+------+----------+
| species | sex | count(*) |
+---------+------+----------+
| bird | | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
8 rows in set (0.00 sec)
mysql> create table event (name varchar(20),date date, //創建event表，是pet的附表
-> type varchar(15),remark varchar(255));
Query OK, 0 rows affected (0.60 sec)
//event.txt文件數據：
fluffy 1995/5/15 litter 4 kittens, 3 female, 1 male
buffy 1993/6/23 litter 5 puppies, 2 female, 3 male
buffy 1994/6/19 litter 3 puppies, 3 female
chirpy 1999/3/21 vet needed beak straightened
slim 1997/8/3 vet broken rib
bowser 1991/10/12 kennel
fang 1991/10/12 kennel
fang 1998/8/28 birthday Gave him a new chew toy
claws 1998/3/17 birthday Gave him a new flea collar
whistler 1998/12/9 birthday First birthday
mysql> LOAD DATA LOCAL INFILE 'E:\\event.txt' INTO TABLE event; //轉載數據
Query OK, 10 rows affected (0.20 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
//
mysql> select pet.name,
-> (year(date)-year(birth))-(right(date,5) -> remark -> from pet,event -> where pet.name=event.name and event.type='litter'; +-------+------+------------------------------+ | name | age | remark | +-------+------+------------------------------+ |buffy | 4 | 5 puppies, 2 female, 3 male | 5 | 3 puppies, 3 female +-------+------+------------------------------+ 2 rows in set (0.00 sec) mysql> select pet.name, -> remark -> from pet,event -> where pet.name=event.name and event.type='litter'; //聯合查找 type='litter'的數據 +-------+------------------------------+ | name | remark | +-------+------------------------------+ |buffy | 5 puppies, 2 female, 3 male |3 puppies, 3 female +-------+------------------------------+ 2 rows in set (0.00 sec) mysql> select database(); //查找當前所有數據庫，這裡特指正在使用的數據庫 +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> show tables; //查看所有表 +----------------+ | Tables_in_test | +----------------+ | event | | pet | +----------------+ 2 rows 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) mysql> create table shop( //創建shop數據庫 -> article int(4) unsigned zerofill default '0000' not null, //指定數字位數，位數不足自動用0補齊
-> dealer char(20) default '' not null,
-> price double(16,2) default '0.00' not null, //price保留2個小數點，16個字節，默認值0.00
-> primary key(article,dealer));
Query OK, 0 rows affected (0.62 sec)
mysql> insert into shop values //插入數據，一種新的插入數據的形式
-> (1,'a',3.45),
-> (1,'b',3.99),
-> (2,'a',10.99),
-> (3,'b',1045),
-> (3,'c',1.69),
-> (3,'d',1.25),
-> (4,'d',19.95);
Query OK, 7 rows affected (0.40 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from shop; //查看全部數據結果
+---------+--------+---------+
| article | dealer | price |
+---------+--------+---------+
| 0001 | a | 3.45 |
| 0001 | b | 3.99 |
| 0002 | a | 10.99 |
| 0003 | b | 1045.00 |
| 0003 | c | 1.69 |
| 0003 | d | 1.25 |
| 0004 | d | 19.95 |
+---------+--------+---------+
7 rows in set (0.00 sec)
mysql> select max(article) as '物品號最大的商品' from shop; //max
+------------------+
| 物品號最大的商品 |
+------------------+
| 4 |
+------------------+
1 row in set (0.02 sec)
mysql> select article,dealer,price //查找最貴商品的編號、銷售商和價格等數據
-> from shop
-> where price=
-> (select max(price) from shop); //從子查詢語句來實現
+---------+--------+---------+
| article | dealer | price |
+---------+--------+---------+
| 0003 | b | 1045.00 |
+---------+--------+---------+
1 row in set (0.00 sec)
mysql> select article,dealer,price //查找最貴商品的編號、銷售商和價格等數據
-> from shop //排序（降序）和limit方式綜合實現
-> order by price desc
-> limit 1;
+---------+--------+---------+
| article | dealer | price |
+---------+--------+---------+
| 0003 | b | 1045.00 |
+---------+--------+---------+
1 row in set (0.00 sec)
mysql> select article,max(price) as price //分組求每種商品的最大值
-> from shop
-> group by article;
+---------+---------+
| article | price |
+---------+---------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1045.00 |
| 0004 | 19.95 |
+---------+---------+
4 rows in set (0.35 sec)
mysql> select article,dealer,price //定義要查找的字段
-> from shop s1 //數據來源
-> where price= //定義數據需要滿足的條件
-> (select max(s2.price) from shop s2 where s1.article = s2.article);
+---------+--------+---------+
| article | dealer | price |
+---------+--------+---------+
| 0001 | b | 3.99 |
| 0002 | a | 10.99 |
| 0003 | b | 1045.00 |
| 0004 | d | 19.95 |
+---------+--------+---------+
4 rows in set (0.00 sec)
//論壇用戶訪問時間數據
mysql> create table t1 (year year(4),month int(2) unsigned zerofill, //創建t1表
-> day int(2) unsigned zerofill);
Query OK, 0 rows affected (0.65 sec)
mysql> insert into t1 values //插入數據
-> (2000,1,1),
-> (2000,2,20),
-> (2000,1,30),
-> (2000,2,2),
-> (2000,2,23),
-> (2000,2,23);
Query OK, 6 rows affected (0.40 sec)
Records: 6 Duplicates: 0 Warnings: 0
ysql> select year,month,bit_count(bit_or(1< -> from t1 -> group by year,month; +------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 2 | | 2000 | 02 | 3 | //本來是4次，為什麼是3次呢，做了去重處理 +------+-------+------+ 2 rows in set (0.36 sec) mysql> create table animals( //通過AUTO_INCREMENT屬性為新的行產生唯一的標識 -> id mediumint not null auto_increment, -> name char(20) not null, -> primary key(id) -> ); Query OK, 0 rows affected (0.26 sec) mysql> insert into animals (name) values -> ('dog'), -> ('cat'), -> ('penguin'), -> ('lax'), -> ('whale'), -> ('ostrich'); mysql> select * from animals; //通過查看結果可以看出表對name自動加了編號
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
mysql> alter table animals auto_increment=100; //定義數據的編號從100開始編號
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into animals (name) values //插入一條新的數據
-> ('cat1');
Query OK, 1 row affected (0.40 sec)
mysql> select * from animals; //查看發現最新插入的數據編號為100，而不是編號7
+-----+---------+
| id | name |
+-----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 100 | cat1 |
+-----+---------+
7 rows in set (0.00 sec)
閱讀更多 思夢PHP 的文章
