# ### part 單表查詢
""" select ... from ... where ... group by ... having ... order by ... limit ...  """
# 一.where 條件的使用
	"""
		功能: 對(duì)表中的數(shù)據(jù)進(jìn)行帥選和過濾
		語法:
			1.判斷的符號(hào)
			= (!= <>不等于) > >= < <=
			2.拼接不同的條件的關(guān)鍵字
			and or not 
			3.查詢對(duì)應(yīng)的區(qū)間值
			between 小值 and 大值 [小值,大值]   查詢兩者之間的范圍值
			4.查詢具體在哪個(gè)范圍中
			in(1,21,333,444) 指定范圍
			5.模糊查詢 like % 通配符  _ 通配符
				like "%b"  匹配以b結(jié)尾的任意長度的字符串
				like "b%"  匹配以b開頭的任意長度的字符串
				like "%b%" 匹配字符串中含有b的任意長度的內(nèi)容
				like "__b" 匹配總長度為3個(gè)字符,任意內(nèi)容的字符串,并且以b結(jié)尾
				like "b_"  匹配總長度為2個(gè)字符,任意內(nèi)容的字符串,并且以b開頭
	"""
	# 1. 查詢部門是sale的所有員工姓名:
	select emp_name from employee where post="sale";
	# 2. 部門是teacher , 收入大于10000的所有數(shù)據(jù)
	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結(jié)尾的員工名搜一下
	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. 統(tǒng)計(jì)員工一年的年薪
	select concat(" 姓名: ",emp_name,"  收入:  ",salary) from employee;
	# 計(jì)算年薪,可以在mysql中使用四則運(yùn)算符 + - * / 
	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 字段,對(duì)數(shù)據(jù)進(jìn)行分類, by后面接什么字段,select后面就搜什么字段"""
	select sex from  employee group by sex;
	# group_concat 按照分組把對(duì)應(yīng)字段拼在一起;
	select group_concat(emp_name),post from  employee group by post;
	# 聚合函數(shù)
		# count 統(tǒng)計(jì)總數(shù) *所有
		select count(*) from employee;
		# max  統(tǒng)計(jì)最大值
		select max(salary) from employee;
		# min  統(tǒng)計(jì)最小值
		select min(salary) from employee;
		# avg  統(tǒng)計(jì)平均值
		select avg(salary) from employee;
		# sum  統(tǒng)計(jì)總和
		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. 查詢公司內(nèi)男員工和女員工的個(gè)數(shù)
	select count(*),sex from employee group by sex;
	# 5. 查詢部門名以及部門包含的所有員工名字
	select group_concat(emp_name),post from employee group by post;
	# 6 可以group by 兩個(gè)字段,就可以同時(shí)搜索兩個(gè)字段
	select emp_name,post from employee group by post ,emp_name;

# 三.having 在數(shù)據(jù)分類分組之后,對(duì)數(shù)據(jù)進(jìn)行二次過濾,一般配合group by來使用的;
	# 找出各部門平均薪資,并且大于10000
	select post , avg(salary) from  employee group by post having avg(salary) > 10000
	# 1.查詢各崗位內(nèi)包含的員工個(gè)數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個(gè)數(shù)
	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的崗位名、平均工資,結(jié)果按平均薪資升序排列
	select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc
	# 3. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資降序排列
	select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 五.limit 限制查詢條數(shù) (應(yīng)用在分頁)
	""" limit m,n m代表從第幾條數(shù)據(jù)開始查, n 代表查幾條  m=0 代表的是第一條數(shù)據(jù)"""
	select * from employee limit 0,10   # 0代表的是第一條數(shù)據(jù)
	select * from employee limit 10,10  # 10代表的是第十一條數(shù)據(jù)
	select * from employee limit 20,10  # 20代表的是第二十一條數(shù)據(jù)
	# limit + num  num => 搜索的條數(shù)據(jù)
	select * from employee limit 1
	# 搜索這個(gè)表里面最后一條數(shù)據(jù)
	select * from employee order by id desc limit 1
	# 搜索這個(gè)表里面最后五條數(shù)據(jù)
	select * from employee order by id desc limit 5
	
# 六.mysql 當(dāng)中可以使用正則表達(dá)式 (不推薦,效率低)
	select * from employee where  emp_name regexp ".*on$"; # mysql中無法識(shí)別?
	select * from employee where  emp_name regexp "^程.*";
	select * from employee where  emp_name regexp "^程.*金";
# `### part2  多表查詢
	# 1.內(nèi)聯(lián)接 :  inner join  :  兩表或者多表之間,把滿足條件的所有數(shù)據(jù)查詢出來 (多表之間共同擁有的數(shù)據(jù)會(huì)被查詢出來)
		# 兩表聯(lián)查
		select 字段 from 表1 inner join 表2 on 必要的關(guān)聯(lián)條件
		# 多表聯(lián)查
		select 字段 from 表1 inner join 表2 on 必要的關(guān)聯(lián)條件1 inner join 表3 on 必要的關(guān)聯(lián)條件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 寫法默寫是內(nèi)聯(lián)接( 等同于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.外聯(lián)接 :  left join左聯(lián)接  / right join 右聯(lián)接
	# (1)left  join左聯(lián)接 : 以左表為主,右表為輔,完整查詢左表所有數(shù)據(jù),右表沒有的數(shù)據(jù)補(bǔ)null
	select * from employee left join department on employee.dep_id = department.id;
	# (2)right join右聯(lián)接 : 以右表為主,左表為輔,完整查詢右表所有數(shù)據(jù),左表沒有的數(shù)據(jù)補(bǔ)null
	select * from employee right join department on employee.dep_id = department.id;
	# 3.全聯(lián)接 :  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語句當(dāng)中又嵌套了另外一條sql,用括號(hào)()進(jìn)行包裹,表達(dá)一個(gè)整體
		(2) 一般用在from子句,where子句... 身后,表達(dá)一個(gè)條件或者一個(gè)表
		(3) 速度快慢: 單表查詢 > 聯(lián)表查詢 > 子查詢;
	"""

	# 一.找出平均年齡大于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);
	# 二.查看技術(shù)部門員工姓名
	# (1) 普通的where 查詢
select 
	e.id,e.name
from
	employee as e,department as d
where
	e.dep_id = d.id
	and
	d.name = "技術(shù)"
	# (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 = "技術(shù)"
	# (3)子查詢
	# (1) 找技術(shù)部門對(duì)應(yīng)的id
	select id from department where name = "技術(shù)";
	# (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 = "技術(shù)");
	# 三.查看哪個(gè)部門沒員工
	# 聯(lián)表寫法
	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 | 技術(shù)         |
	|  201 | 人力資源     |
	|  202 | 銷售         |
	|  203 | 運(yùn)營         |
	+------+--------------+
	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
	+----+------------+--------+------+--------+
	# 四.查詢大于平均年齡的員工名與年齡
	# 假設(shè)已經(jīng)知道了平均年齡;
	select name,age from employee where age > 30;
	# 計(jì)算平均年齡
	select avg(age) from employee;
	# 綜合拼接
	select name,age from employee where age > (select avg(age) from employee);
	
	# 五.把大于其本部門平均年齡的員工名和姓名查出來
	# 1.先計(jì)算本部門的平均年齡是多少
	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進(jìn)行聯(lián)表,變成一張大表,最后做單表查詢
	select 
		*
	from
		employee as t1 inner join (1號(hào)查詢出來的數(shù)據(jù)) 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
	
	# 六.查詢每個(gè)部門最新入職的那位員工  # 利用上一套數(shù)據(jù)表進(jìn)行查詢;
	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.找各部門的最新入職的時(shí)間
	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.把子查詢搜索出來的結(jié)果作為一張表和employee這個(gè)表做聯(lián)表,把max_date拼接在employee這個(gè)表中,變成一張大表,最后做一次單表查詢
	select 
		*
	from
		employee as t1 inner join (1號(hào)數(shù)據(jù)) 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關(guān)鍵字的子查詢
	"""
	exists 關(guān)鍵字 , 表達(dá)存在 , 應(yīng)用在子查詢中
		如果內(nèi)層sql , 能夠查到數(shù)據(jù), 返回True ,  外層sql執(zhí)行相應(yīng)的sql語句
		如果內(nèi)層sql , 不能查到數(shù)據(jù), 返回False , 外層sql不執(zhí)行sql語句
	"""
	select * from employee where exists (select * from employee where id = 1);
	select * from employee where exists (select * from employee where id = 100000);
	
	"""
	總結(jié): 
		子查詢可以單獨(dú)作為臨時(shí)數(shù)據(jù),作為一張表或者一個(gè)字段,通過()進(jìn)行包裹,表達(dá)一個(gè)整體;
		一般用在from,where,select.子句的后面
		可以通過查詢出來的數(shù)據(jù)和另外的表做聯(lián)表變成更大一張表,
		最后做單表查詢,達(dá)到目的;
	"""

# 單表練習(xí)
#創(chuàng)建表
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, #一個(gè)部門一個(gè)屋子
depart_id int
);

#三個(gè)部門：教學(xué)，銷售，運(yùn)營
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','辦事處外交大使',7300.33,401,1), #以下是教學(xué)部
('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), #以下是運(yùn)營部門
('程咬金','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)
;

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

# 多表練習(xí):
#建表
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
);
#插入數(shù)據(jù)
insert into department values
(200,'技術(shù)'),
(201,'人力資源'),
(202,'銷售'),
(203,'運(yùn)營');
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歲以上的部門
# 二.查看技術(shù)部門員工姓名
# 三.查看哪個(gè)部門沒員工
# 四.查詢大于平均年齡的員工名與年齡
# 五.把大于其本部門平均年齡的員工名和姓名查出來
# 六.查詢每個(gè)部門最新入職的那位員工  # 利用上一套數(shù)據(jù)表進(jìn)行查詢;
# 七.帶EXISTS關(guān)鍵字的子查詢

# 1、創(chuàng)建表
# 創(chuàng)建班級(jí)表
create table class(
cid int primary key auto_increment,
caption varchar(32) not null
);
# 創(chuàng)建學(xué)生表
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
);
# 創(chuàng)建老師表
create table teacher(
tid int primary key auto_increment,
tname varchar(32) not null
);
# 創(chuàng)建課程表
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
);
# 創(chuàng)建成績表
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、插入記錄
# 班級(jí)表插入記錄
insert into class values
('1', '三年二班'), 
('2', '三年三班'), 
('3', '一年二班'), 
('4', '二年一班');
# 學(xué)生表插入記錄
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', '美術(shù)', '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、查詢所有的課程的名稱以及對(duì)應(yīng)的任課老師姓名
2、查詢學(xué)生表中男女生各有多少人
3、查詢物理成績等于100的學(xué)生的姓名
4、查詢平均成績大于八十分的同學(xué)的姓名和平均成績
5、查詢所有學(xué)生的學(xué)號(hào)，姓名，選課數(shù)，總成績
6、 查詢姓李老師的個(gè)數(shù)
7、 查詢沒有報(bào)李平老師課的學(xué)生姓名
8、 查詢物理課程的分?jǐn)?shù)比生物課程的分?jǐn)?shù)高的學(xué)生的學(xué)號(hào)
9、 查詢沒有同時(shí)選修物理課程和體育課程的學(xué)生姓名
10、查詢掛科超過兩門(包括兩門)的學(xué)生姓名和班級(jí)
11、查詢選修了所有課程的學(xué)生姓名
12、查詢李平老師教的課程的所有成績記錄
13、查詢?nèi)繉W(xué)生都選修了的課程號(hào)和課程名
14、查詢每門課程被選修的次數(shù)
15、查詢只選修了一門課程的學(xué)生學(xué)號(hào)和姓名
16、查詢所有學(xué)生考出的成績并按從高到低排序（成績?nèi)ブ兀?
17、查詢平均成績大于85的學(xué)生姓名和平均成績
18、查詢生物成績不及格的學(xué)生姓名和對(duì)應(yīng)生物分?jǐn)?shù)
19、查詢?cè)谒羞x修了李平老師課程的學(xué)生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學(xué)生姓名
20、查詢每門課程成績最好的課程id、學(xué)生姓名和分?jǐn)?shù)
21、查詢不同課程但成績相同的課程號(hào)、學(xué)生號(hào)、成績 
22、查詢沒學(xué)過“李平”老師課程的學(xué)生姓名以及選修的課程名稱 
23、查詢所有選修了學(xué)號(hào)為2的同學(xué)選修過的一門或者多門課程的同學(xué)學(xué)號(hào)和姓名 
24、任課最多的老師中學(xué)生單科成績最高的課程id、學(xué)生姓名和分?jǐn)?shù)