Python全棧之學習MySQL(2)

# ### part 單表查詢
""" select ... from ... where ... group by ... having ... order by ... limit ...  """
# 一.where 條件的使用
	"""
		功能: 對表中的數據進行帥選和過濾
		語法:
			1.判斷的符號
			= (!= <>不等於) > >= < <=
			2.拼接不同的條件的關鍵字
			and or not 
			3.查詢對應的區間值
			between 小值 and 大值 [小值,大值]   查詢兩者之間的范圍值
			4.查詢具體在哪個范圍中
			in(1,21,333,444) 指定范圍
			5.模糊查詢 like % 通配符  _ 通配符
				like "%b"  匹配以b結尾的任意長度的字符串
				like "b%"  匹配以b開頭的任意長度的字符串
				like "%b%" 匹配字符串中含有b的任意長度的內容
				like "__b" 匹配總長度為3個字符,任意內容的字符串,並且以b結尾
				like "b_"  匹配總長度為2個字符,任意內容的字符串,並且以b開頭
	"""
	# 1. 查詢部門是sale的所有員工姓名:
	select emp_name from employee where post="sale";
	# 2. 部門是teacher , 收入大於10000的所有數據
	select * from employee where post = "teacher" and salary > 10000;
	# 3. 收入在1萬到2萬之間的所有員工姓名和收入
	select emp_name,salary from employee where salary between 10000 and 20000;
	# 4. 收入不在1萬到2萬之間的所有員工姓名和收入
	select emp_name,salary from employee where salary not between 10000 and 20000;
	# 5. 查看崗位描述為NULL的員工信息
	select emp_name from employee where post_comment = null;
	select emp_name from employee where post_comment = '';
	select emp_name from employee where post_comment is null;
	# 6. 查看崗位描述不為NULL的員工信息
	select emp_name from employee where post_comment is not null;
	# 7. 查詢收入是3000 ,4000 ,5000,8300 所有員工的姓名和收入
	select emp_name,salary from employee where salary in(3000,4000,5000,8300);
	select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300;
	# 8. 查詢收入不是3000 ,4000 ,5000,8300 所有員工的姓名和收入
	select emp_name,salary from employee where salary not in(3000,4000,5000,8300);
	# 9. 以on結尾的員工名搜一下
	select emp_name from employee where emp_name like "%on";
	select emp_name from employee where emp_name like "ji%";
	select emp_name from employee where emp_name like "_le_";
	# 10. 統計員工一年的年薪
	select concat(" 姓名: ",emp_name,"  收入:  ",salary) from employee;
	# 計算年薪,可以在mysql中使用四則運算符 + - * / 
	select concat(" 姓名: ",emp_name,"  收入:  ",salary * 12) from employee;
	select concat_ws("  :  ",emp_name,salary*12 ) from employee;
	# 11. 查詢部門的種類
	# distinct  返回唯一不同的值
	select distinct(post)  from employee;
	
# 二.group by 子句 分組分類
	"""group by 字段,對數據進行分類, by後面接什麼字段,select後面就搜什麼字段"""
	select sex from  employee group by sex;
	# group_concat 按照分組把對應字段拼在一起;
	select group_concat(emp_name),post from  employee group by post;
	# 聚合函數
		# count 統計總數 *所有
		select count(*) from employee;
		# max  統計最大值
		select max(salary) from employee;
		# min  統計最小值
		select min(salary) from employee;
		# avg  統計平均值
		select avg(salary) from employee;
		# sum  統計總和
		select sum(salary) from employee;
	# 1. 查詢部門名以及各部門的平均薪資
	select avg(salary),post from employee group by post;
	# 2. 查詢部門名以及各部門的最高薪資
	select max(salary),post from employee group by post;
	# 3. 查詢部門名以及各部門的最低薪資
	select min(salary),post from employee group by post;
	# 4. 查詢公司內男員工和女員工的個數
	select count(*),sex from employee group by sex;
	# 5. 查詢部門名以及部門包含的所有員工名字
	select group_concat(emp_name),post from employee group by post;
	# 6 可以group by 兩個字段,就可以同時搜索兩個字段
	select emp_name,post from employee group by post ,emp_name;

# 三.having 在數據分類分組之後,對數據進行二次過濾,一般配合group by來使用的;
	# 找出各部門平均薪資,並且大於10000
	select post , avg(salary) from  employee group by post having avg(salary) > 10000
	# 1.查詢各崗位內包含的員工個數小於2的崗位名、崗位內包含員工名字、個數
	select post , group_concat(emp_name), count(*) from employee group by post having count(*) < 2;
	# 2.查詢各崗位平均薪資小於10000的崗位名、平均工資
	select post , avg(salary) from employee group by post having avg(salary) < 10000
	# 3.查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資
	select post, avg(salary) from employee group by post having avg(salary) between 10000 and 20000
	select post, avg(salary) from employee group by post having avg(salary) > 10000 and  avg(salary) < 20000;
	
# 四.order by 排序 , 按照某字段排序
	order by age asc (升序) order by age desc (降序)
	# 按照年齡從小到大排序
	select * from employee order by age;
	# 按照年齡從大到小排序
	select * from employee order by age desc;
	# 1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序
	select * from employee order by age asc ,  hire_date desc;
	# 2. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資升序排列
	select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc
	# 3. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資降序排列
	select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 五.limit 限制查詢條數 (應用在分頁)
	""" limit m,n m代表從第幾條數據開始查, n 代表查幾條  m=0 代表的是第一條數據"""
	select * from employee limit 0,10   # 0代表的是第一條數據
	select * from employee limit 10,10  # 10代表的是第十一條數據
	select * from employee limit 20,10  # 20代表的是第二十一條數據
	# limit + num  num => 搜索的條數據
	select * from employee limit 1
	# 搜索這個表裡面最後一條數據
	select * from employee order by id desc limit 1
	# 搜索這個表裡面最後五條數據
	select * from employee order by id desc limit 5
	
# 六.mysql 當中可以使用正則表達式 (不推薦,效率低)
	select * from employee where  emp_name regexp ".*on$"; # mysql中無法識別?
	select * from employee where  emp_name regexp "^程.*";
	select * from employee where  emp_name regexp "^程.*金";
# `### part2  多表查詢
	# 1.內聯接 :  inner join  :  兩表或者多表之間,把滿足條件的所有數據查詢出來 (多表之間共同擁有的數據會被查詢出來)
		# 兩表聯查
		select 字段 from 表1 inner join 表2 on 必要的關聯條件
		# 多表聯查
		select 字段 from 表1 inner join 表2 on 必要的關聯條件1 inner join 表3 on 必要的關聯條件2 
	select * from employee inner join department on employee.dep_id = department.id;
	# as 起別名
	select * from employee as e inner join department as d on e.dep_id = d.id;
	# 也可以省略as (不推薦)	
	select * from employee e inner join department d on e.dep_id = d.id;
	# where 寫法默寫是內聯接( 等同於inner join )
	select * from employee,department where employee.dep_id = department.id;
	select * from employee as e ,department as d where e.dep_id = d.id;
	# 2.外聯接 :  left join左聯接  / right join 右聯接
	# (1)left  join左聯接 : 以左表為主,右表為輔,完整查詢左表所有數據,右表沒有的數據補null
	select * from employee left join department on employee.dep_id = department.id;
	# (2)right join右聯接 : 以右表為主,左表為輔,完整查詢右表所有數據,左表沒有的數據補null
	select * from employee right join department on employee.dep_id = department.id;
	# 3.全聯接 :  union
	select * from employee left join department on employee.dep_id = department.id
	union
	select * from employee right join department on employee.dep_id = department.id;

# ### part3 子查詢 
	"""
	子查詢: 嵌套查詢
		(1) sql語句當中又嵌套瞭另外一條sql,用括號()進行包裹,表達一個整體
		(2) 一般用在from子句,where子句... 身後,表達一個條件或者一個表
		(3) 速度快慢: 單表查詢 > 聯表查詢 > 子查詢;
	"""

	# 一.找出平均年齡大於25歲以上的部門
	# (1) where
	select 
		d.id,d.name
	from 
		employee as e ,department as d
	where
		e.dep_id = d.id
	group by 
		d.id,d.name
	having
		avg(e.age) > 25
	# (2) inner join 
	select 
		d.id,d.name
	from 
		employee as e inner join department as d on e.dep_id = d.id
	group by 
		d.id,d.name
	having
		avg(e.age) > 25
	# (3) 子查詢
	# 1.先找出平均年齡大於25歲的部門id
	select dep_id from employee group by employee.dep_id having avg(age)>25; # 201 202
	# 2.通過部門的id找部門的名字
	select name from department where id in (201,202);
	# 3.綜合拼接:
	select id , name from department where id in (select dep_id from employee group by employee.dep_id having avg(age)>25);
	# 二.查看技術部門員工姓名
	# (1) 普通的where 查詢
select 
	e.id,e.name
from
	employee as e,department as d
where
	e.dep_id = d.id
	and
	d.name = "技術"
	# (2) inner join 
select 
	e.id,e.name
from
	employee as e inner join department as d on e.dep_id = d.id 
where
	d.name = "技術"
	# (3)子查詢
	# (1) 找技術部門對應的id
	select id from department where name = "技術";
	# (2) 通過id找員工姓名
	select name from employee where dep_id = 200;
	# (3) 綜合拼接
	select id,name from employee where dep_id = (select id from department where name = "技術");
	# 三.查看哪個部門沒員工
	# 聯表寫法
	select
		d.id,d.name
	from
		department as d left join employee as e on d.id = e.dep_id
	where
		e.dep_id is null	
	
	# 1.找員工在哪些部門 (200  201  202 204)
	select dep_id from employee  group by dep_id
	# 2.把不在該部門的員工找出來
	select  id  from department where id not in (200,201,202,204);
	# 3.綜合拼接
	select  id,name  from department where id not in (select dep_id from employee  group by dep_id);
	department;
	+------+--------------+
	| id   | name         |
	+------+--------------+
	|  200 | 技術         |
	|  201 | 人力資源     |
	|  202 | 銷售         |
	|  203 | 運營         |
	+------+--------------+
	employee;
	+----+------------+--------+------+--------+
	| id | name       | sex    | age  | dep_id |avg(age) 
	+----+------------+--------+------+--------+
	|  1 | egon       | male   |   18 |    200 |  18
	|  2 | alex       | female |   48 |    201 |  43
	|  3 | wupeiqi    | male   |   38 |    201 |  43
	|  4 | yuanhao    | female |   28 |    202 |  28
	|  5 | liwenzhou  | male   |   18 |    200 |  18
	|  6 | jingliyang | female |   18 |    204 |  18
	+----+------------+--------+------+--------+
	# 四.查詢大於平均年齡的員工名與年齡
	# 假設已經知道瞭平均年齡;
	select name,age from employee where age > 30;
	# 計算平均年齡
	select avg(age) from employee;
	# 綜合拼接
	select name,age from employee where age > (select avg(age) from employee);
	
	# 五.把大於其本部門平均年齡的員工名和姓名查出來
	# 1.先計算本部門的平均年齡是多少
	select dep_id , avg(age) from employee  group by dep_id;	
	+--------+----------+
	| dep_id | avg(age) |
	+--------+----------+
	|    200 |  18.0000 |
	|    201 |  43.0000 |
	|    202 |  28.0000 |
	|    204 |  18.0000 |
	+--------+----------+
	# 2.把查詢的各部門平均年齡和employee進行聯表,變成一張大表,最後做單表查詢
	select 
		*
	from
		employee as t1 inner join (1號查詢出來的數據) as t2 on t1.dep_id = t2.dep_id
	# 3.綜合拼裝
select 
	*
from
	employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id
	# 4.最後做一次單表查詢,讓age > 平均值	
select 
	*
from
	employee as t1 inner join (select dep_id , avg(age) as avg_age from employee  group by dep_id) as t2 on t1.dep_id = t2.dep_id
where 
	age >avg_age
	
	# 六.查詢每個部門最新入職的那位員工  # 利用上一套數據表進行查詢;
	employee
	+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
	| id | emp_name   | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |    max_date
	+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
	|  1 | egon       | male   |  18 | 2017-03-01 | 辦事處外交大使                      |              |    7300.33 |    401 |         1 | 2017-03-01
	|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 | 2015-03-02
	|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 | 2015-03-02 
	|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 | 2015-03-02
	|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 | 2015-03-02
	|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 | 2015-03-02
	|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 | 2015-03-02
	|  8 | 成龍       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 | 2015-03-02
	|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 | 2017-01-27
	| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 | 2017-01-27
	| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 | 2017-01-27
	| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 | 2017-01-27
	| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 | 2017-01-27
	| 14 | 張野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 | 2016-03-11
	| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 | 2016-03-11
	| 16 | 程咬銀     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 | 2016-03-11
	| 17 | 程咬銅     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 | 2016-03-11
	| 18 | 程咬鐵     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 | 2016-03-11
	+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
	# 1.找各部門的最新入職的時間
	select post,max(hire_date) as max_date from employee group by post
	+-----------------------------------------+------------+
	| post                                    | max_date   |
	+-----------------------------------------+------------+
	| operation                               | 2016-03-11 |
	| sale                                    | 2017-01-27 |
	| teacher                                 | 2015-03-02 |
	| 辦事處外交大使             				  | 2017-03-01 |
	+-----------------------------------------+------------+
	# 2.把子查詢搜索出來的結果作為一張表和employee這個表做聯表,把max_date拼接在employee這個表中,變成一張大表,最後做一次單表查詢
	select 
		*
	from
		employee as t1 inner join (1號數據) as t2 on t1.post = t2.post
	where
		t1.hire_date = t2.max_date
	# 3.綜合拼裝
select 
	emp_name , max_date
from
	employee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post
where
	t1.hire_date = t2.max_date

	# 七.帶EXISTS關鍵字的子查詢
	"""
	exists 關鍵字 , 表達存在 , 應用在子查詢中
		如果內層sql , 能夠查到數據, 返回True ,  外層sql執行相應的sql語句
		如果內層sql , 不能查到數據, 返回False , 外層sql不執行sql語句
	"""
	select * from employee where exists (select * from employee where id = 1);
	select * from employee where exists (select * from employee where id = 100000);
	
	"""
	總結: 
		子查詢可以單獨作為臨時數據,作為一張表或者一個字段,通過()進行包裹,表達一個整體;
		一般用在from,where,select.子句的後面
		可以通過查詢出來的數據和另外的表做聯表變成更大一張表,
		最後做單表查詢,達到目的;
	"""

# 單表練習
#創建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一個部門一個屋子
depart_id int
);

#三個部門：教學，銷售，運營
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','辦事處外交大使',7300.33,401,1), #以下是教學部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龍','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('張野','male',28,'20160311','operation',10000.13,403,3), #以下是運營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;

# 練習:where
# 1. 查詢部門是sale的所有員工姓名:
# 2. 部門是teacher , 收入大於10000的所有數據
# 3. 收入在1萬到2萬之間的所有員工姓名和收入
# 4. 收入不在1萬到2萬之間的所有員工姓名和收入
# 5. 查看崗位描述為NULL的員工信息
# 5. 查詢收入是3000 ,4000 ,5000,8300 所有員工的姓名和收入
# 6. 以on結尾的員工名搜一下
# 7. 統計員工一年的年薪
# 8. 查詢部門的種類
#練習:group
# 1. 查詢部門名以及各部門的平均薪資
# 2. 查詢部門名以及各部門的最高薪資
# 3. 查詢部門名以及各部門的最低薪資
# 4. 查詢公司內男員工和女員工的個數
# 5. 查詢部門名以及部門包含的所有員工名字
#練習:having
# 1.查詢各崗位內包含的員工個數小於2的崗位名、崗位內包含員工名字、個數
# 2.查詢各崗位平均薪資小於10000的崗位名、平均工資
# 3.查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資
#練習:order by
# 1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序
# 2. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資升序排列
# 3. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資降序排列

# 多表練習:
#建表
create table department(
id int,
name varchar(20) 
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入數據
insert into department values
(200,'技術'),
(201,'人力資源'),
(202,'銷售'),
(203,'運營');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
# 查詢:
# 一.找出平均年齡大於25歲以上的部門
# 二.查看技術部門員工姓名
# 三.查看哪個部門沒員工
# 四.查詢大於平均年齡的員工名與年齡
# 五.把大於其本部門平均年齡的員工名和姓名查出來
# 六.查詢每個部門最新入職的那位員工  # 利用上一套數據表進行查詢;
# 七.帶EXISTS關鍵字的子查詢

# 1、創建表
# 創建班級表
create table class(
cid int primary key auto_increment,
caption varchar(32) not null
);
# 創建學生表
create table student(
sid int primary key auto_increment,
gender char(1) not null,
class_id int not null,
sname varchar(32) not null,
foreign key(class_id) references class(cid) on delete cascade on update cascade
);
# 創建老師表
create table teacher(
tid int primary key auto_increment,
tname varchar(32) not null
);
# 創建課程表
create table course(
cid int primary key auto_increment,
cname varchar(32) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid) on delete cascade on update cascade
);
# 創建成績表
create table score(
sid int primary key auto_increment,
student_id int not null,
course_id int not null,
num int not null,
foreign key(student_id) references student(sid) on delete cascade on update cascade,
foreign key(course_id) references course(cid) on delete cascade on update cascade
);

# 2、插入記錄
# 班級表插入記錄
insert into class values
('1', '三年二班'), 
('2', '三年三班'), 
('3', '一年二班'), 
('4', '二年一班');
# 學生表插入記錄
insert into student values
('1', '男', '1', '理解'), 
('2', '女', '1', '鋼蛋'), 
('3', '男', '1', '張三'), 
('4', '男', '1', '張一'), 
('5', '女', '1', '張二'), 
('6', '男', '1', '張四'), 
('7', '女', '2', '鐵錘'),
('8', '男', '2', '李三'), 
('9', '男', '2', '李一'), 
('10', '女', '2', '李二'), 
('11', '男', '2', '李四'), 
('12', '女', '3', '如花'), 
('13', '男', '3', '劉三'), 
('14', '男', '3', '劉一'), 
('15', '女', '3', '劉二'), 
('16', '男', '3', '劉四');
# 老師表插入記錄
insert into teacher values
('1', '張磊'), 
('2', '李平'), 
('3', '劉海燕'), 
('4', '朱雲海'), 
('5', '李春秋');
# 課程表插入記錄
insert into course values
('1', '生物', '1'), 
('2', '物理', '2'), 
('3', '體育', '3'), 
('4', '美術', '2');
# 成績表插入記錄
insert into score values
('1', '1', '1', '10'), 
('2', '1', '2', '9'), 
('3', '1', '3', '76'),
('5', '1', '4', '66'), 
('6', '2', '1', '8'), 
('8', '2', '3', '68'), 
('9', '2', '4', '99'), 
('10', '3', '1', '77'), 
('11', '3', '2', '66'), 
('12', '3', '3', '87'), 
('13', '3', '4', '99'), 
('14', '4', '1', '79'), 
('15', '4', '2', '11'), 
('16', '4', '3', '67'), 
('17', '4', '4', '100'), 
('18', '5', '1', '79'), 
('19', '5', '2', '11'), 
('20', '5', '3', '67'), 
('21', '5', '4', '100'), 
('22', '6', '1', '9'), 
('23', '6', '2', '100'), 
('24', '6', '3', '67'), 
('25', '6', '4', '100'), 
('26', '7', '1', '9'), 
('27', '7', '2', '100'), 
('28', '7', '3', '67'), 
('29', '7', '4', '88'), 
('30', '8', '1', '9'), 
('31', '8', '2', '100'), 
('32', '8', '3', '67'),
('33', '8', '4', '88'), 
('34', '9', '1', '91'), 
('35', '9', '2', '88'), 
('36', '9', '3', '67'), 
('37', '9', '4', '22'), 
('38', '10', '1', '90'), 
('39', '10', '2', '77'), 
('40', '10', '3', '43'), 
('41', '10', '4', '87'), 
('42', '11', '1', '90'), 
('43', '11', '2', '77'), 
('44', '11', '3', '43'), 
('45', '11', '4', '87'), 
('46', '12', '1', '90'), 
('47', '12', '2', '77'), 
('48', '12', '3', '43'), 
('49', '12', '4', '87'), 
('52', '13', '3', '87');

1、查詢所有的課程的名稱以及對應的任課老師姓名
2、查詢學生表中男女生各有多少人
3、查詢物理成績等於100的學生的姓名
4、查詢平均成績大於八十分的同學的姓名和平均成績
5、查詢所有學生的學號，姓名，選課數，總成績
6、 查詢姓李老師的個數
7、 查詢沒有報李平老師課的學生姓名
8、 查詢物理課程的分數比生物課程的分數高的學生的學號
9、 查詢沒有同時選修物理課程和體育課程的學生姓名
10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
11、查詢選修瞭所有課程的學生姓名
12、查詢李平老師教的課程的所有成績記錄
13、查詢全部學生都選修瞭的課程號和課程名
14、查詢每門課程被選修的次數
15、查詢隻選修瞭一門課程的學生學號和姓名
16、查詢所有學生考出的成績並按從高到低排序（成績去重）
17、查詢平均成績大於85的學生姓名和平均成績
18、查詢生物成績不及格的學生姓名和對應生物分數
19、查詢在所有選修瞭李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
20、查詢每門課程成績最好的課程id、學生姓名和分數
21、查詢不同課程但成績相同的課程號、學生號、成績 
22、查詢沒學過“李平”老師課程的學生姓名以及選修的課程名稱 
23、查詢所有選修瞭學號為2的同學選修過的一門或者多門課程的同學學號和姓名 
24、任課最多的老師中學生單科成績最高的課程id、學生姓名和分數