先創(chuàng)建表
create database xue_xiao character set utf8 collate utf8_general_ci;
use xue_xiao;
create table xue_sheng(id int, xing_ming varchar(50), fen_shu int, xing_bie char(2), bj_id int);
insert into xue_sheng values(1,'張三',90,'男',1);
insert into xue_sheng values(2,'李四',70,'男',2);
insert into xue_sheng values(3,'李小紅',80,'女',1);
insert into xue_sheng values(4,'陳小明',80,'男',3);
create table ban_ji( id int, ban_ming varchar(15) );
insert into ban_ji values(1,'一年級(jí)(1)班');
insert into ban_ji values(2,'一年級(jí)(2)班');
insert into ban_ji values(3,'一年級(jí)(3)班');
一些比較常用的函數(shù)，一般配合 select 一起使用
sum , count , max , min , avg
分別顯示學(xué)生的 總成績(jī) 總數(shù)圾浅，最高疆股、最低分?jǐn)?shù)，還有平均分?jǐn)?shù)
mysql> select * from ban_ji;
+------+-----------------+
| id | ban_ming |
+------+-----------------+
| 1 | 一年級(jí)(1)班 |
| 2 | 一年級(jí)(2)班 |
| 3 | 一年級(jí)(3)班 |
+------+-----------------+
3 rows in set (0.00 sec)
mysql> select * from xue_sheng;
+------+-----------+---------+----------+-------+
| id | xing_ming | fen_shu | xing_bie | bj_id |
+------+-----------+---------+----------+-------+
| 1 | 張三 | 90 | 男 | 1 |
| 2 | 李四 | 70 | 男 | 2 |
| 3 | 李小紅 | 80 | 女 | 1 |
| 4 | 陳小明 | 80 | 男 | 3 |
+------+-----------+---------+----------+-------+
4 rows in set (0.00 sec)
mysql> select sum(fen_shu) from xue_sheng;
+--------------+
| sum(fen_shu) |
+--------------+
| 320 |
+--------------+
1 row in set (0.00 sec)
mysql> select max(fen_shu) from xue_sheng;
+--------------+
| max(fen_shu) |
+--------------+
| 90 |
+--------------+
1 row in set (0.00 sec)
mysql> select min(fen_shu) from xue_sheng;
+--------------+
| min(fen_shu) |
+--------------+
| 70 |
+--------------+
1 row in set (0.00 sec)
mysql> select avg(fen_shu) from xue_sheng;
+--------------+
| avg(fen_shu) |
+--------------+
| 80.0000 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(*) from xue_sheng;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
分組查詢 , 分別顯示男女總分?jǐn)?shù)
mysql> select xing_bie, sum(fen_shu) from xue_sheng group by xing_bie;
mysql> select xing_bie,sum(fen_shu) from xue_sheng group by xing_bie;
+----------+--------------+
| xing_bie | sum(fen_shu) |
+----------+--------------+
| 女 | 80 |
| 男 | 240 |
+----------+--------------+
同時(shí)查詢2張表盼樟， 查詢學(xué)生所在的班級(jí)
mysql> select xing_ming,ban_ming from xue_sheng,ban_ji;
別名的使用
mysql> select xing_ming,ban_ming from xue_sheng x,ban_ji b where x.bj_id=b.id;
下面使用 join on 表連接
mysql> select xing_ming,ban_ming from xue_sheng x join ban_ji b on x.bj_id=b.id;
+-----------+-----------------+
| xing_ming | ban_ming |
+-----------+-----------------+
| 張三 | 一年級(jí)(1)班 |
| 李四 | 一年級(jí)(2)班 |
| 李小紅 | 一年級(jí)(1)班 |
| 陳小明 | 一年級(jí)(3)班 |
+-----------+-----------------+
子查詢，又叫嵌套查詢 in() 或者 not in()
select * from xue_sheng where bj_id in( select id from ban_ji where ban_ming='一年級(jí)(1)班' );
分解一下：
select id from ban_ji where ban_ming='一年級(jí)(1)班';
得到的結(jié)果為 1
然后把第一個(gè)查詢的結(jié)果锈至，作為第二個(gè)查詢的條件
select * from xue_sheng where bj_id in( 1 );
