mysql 07.16

添加数据

mysql> create table student_man(
    -> name char(10),
    -> gender int);
Query OK, 0 rows affected (0.26 sec)

mysql> insert into student_man select *from student where gender = 1;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

1.select关键字

select [distinct] *字段名|四则运算|函数 from table_name
where 比较运算符 逻辑运算符 成员运算符 区间 between...and...
	模糊匹配 like exists
group by 分组
having 通常跟聚合函数 count sum max min avg
order by
limit a,b

where条件

select *from table_name where
1.比较运算符 > < >= <= = !=
2.成员运算符 in |not in 后面是一个set
3.逻辑运算符 and or not 
4.模糊查询 like % 任意个数的任意字符|_ 表示任意一个字符

distinct 去除重复记录

select distinct *from stu;
# 注意仅当查询结果中所有字段全都相同时 才算重复的记录

指定字段

1.*表示所有字段
2.可以手动指定需要查询的字段
3.可以添加四则运算
4.也可以是聚合函数

取别名

select name,math+english as 总分 from stu where name = '赵云'
as 可省略

mysql> create table stu (id int primary key auto_increment,
    -> name char(10),
    -> math float,
    -> english float);
Query OK, 0 rows affected (0.24 sec)

mysql> insert into stu(name,math,english) values('赵云',90,30),
    -> ('小乔',90,60),
    -> ('小乔',90,60),
    -> ('大乔',10,70),
    -> ('李清照',100,100),
    -> ('铁拐李',20,55),
    -> ('小李子',20,55);
Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

1.查询英语分数在 80－90之间的同学。
mysql> select name from stu where english between 80 and 90;
2.查询数学分数为89,90,91的同学。
select name from stu where math = 89 and math = 90 and math = 91;
3.查询所有姓李的学生成绩。
select name,math,english from stu where name like '李%';
4.查询所有姓名带有李的学生成绩。
select name,math,english from stu where name like '%李%';
5.查询数学分>80并且英语分>80的同学。
select name from stu where math > 80 and english >80;
6.英语及格的人的 平均分。
select name,(math+english)/2 平均分 from stu where english >=60;
7.查询赵云的英语和数学总分。
select name,math+english as 总分 from stu where name = '赵云'；

统计函数（聚合函数）

求和 sum（字段名）； 平均数 avg（字段名）; 最大值 max（字段名）； 最小值 min（字段名）;  个数 count（字段名）

group by

group 是分组的意思,即将一个整体按照某个特征或依据来分为不同的部分
select xxx from table_name group by 字段名称

having

用于过滤，但是与where不同的是，having使用在分组之后

order

根据某个字段排序
select *from table_name order by 字段名称；
默认按升序排序
select *from table_name order by 字段名称 desc; 降序排序

# 多个字段  第一个相同在按照第二个    asc 表示升序
select * from table_name order by 字段名称1 desc,字段名称2 asc;

limit

用于限制要显示的记录数量
select *from table_name limit 个数；
select *from table_name limit 起始位置，个数；

子查询

将一个查询语句的结果作为另一个查询语句的条件或是数据来源

in 关键字子查询

当内层查询 (括号内的) 结果会有多个结果时, 不能使用 = 必须是in  ,另外子查询必须只能包含一列数据

exists 关键字子查询

当内层查询 有结果时 外层才会执行

mysql> create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into emp values
    -> (1,"刘备","男","市场","总监",5800),
    -> (2,"张飞","男","市场","员工",3000),
    -> (3,"关羽","男","市场","员工",4000),
    -> (4,"孙权","男","行政","总监",6000),
    -> (5,"周瑜","男","行政","员工",5000),
    -> (6,"小乔","女","行政","员工",4000),
    -> (7,"曹操","男","财务","总监",10000),
    -> (8,"司马懿","男","财务","员工",6000);
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

1.查询所有人的平均工资 
select avg(salary) from emp;
2.统计每个性别有几个人
select sex,count(*) from emp group by sex;
3.求出平均工资大于500的部门信息 
select dept,avg(salary) from emp group by dept having avg(salary)>5000;
4.查询 部门人数少于3的 部门名称 人员名称 人员个数
select group_concat(name),count(*) from emp group by dept having count(*)<3;
5.排序
select *from emp order by salary desc,id asc;
6.查询前三条 
select *from emp limit 3;
7从第三条开始 查询3条
select *from emp limit 2,3;

create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);

insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");
1.查询出 平均年龄 大于25的部门编号
select  dept_id from emp  group by dept_id having avg(age) > 25;
select name from  dept where id in (select dept_id from emp  group by  dept_id  having avg(age) > 25);

select* from dept where exists (select * from dept where id = 1);

2.笛卡尔积查询

select *from table1,table2...

# 笛卡尔积查询的结果会出现大量的错误数据即,数据关联关系错误!
添加过滤条件 从表外键值 等于 主表的主键值

# 并且会产生重复的字段信息  例如员工里的 部门编号  和 部门表的id字段 
在select 后指定需要查询的字段名称 

内连接查询：
select * from table1 inner join table2;

左连接查询
左边的表无论是否能够匹配都要完整显示，右边的仅展示匹配上的记录 

右外连接查询
右边的表无论是否能够匹配都要完整显示 ，左边的仅展示匹配上的记录 

全连接查询 
无论是否匹配成功 两边表的数据都要全部显示
mysql 不支持全连接查询
# union将自动去除重复的记录  
# union all 不去重复 

1.
select  dept.name 部门 ,dept.id 部门编号,emp0.name 姓名,emp0.id 员工编号,sex from emp0 ,dept where dept.id = dept_id;
2.
select *from emp0 join dept where dept_id=dept.id;
3.
select * from emp0 right join dept on dept_id = dept.id;
4.
select * from emp0 left join dept on dept_id = dept.id
union
select * from emp0 right join dept on dept_id = dept.id;
5.
select sex,name from emp0 
union 
select * from dept;

create table stu1(id int primary key auto_increment,name char(10));

create table tea2(id int primary key auto_increment,name char(10));

create table tsr1(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(t_id) references tea(id));

insert into stu1 values(null,"张三"),(null,"李四");
insert into tea1 values(null,"egon"),(null,"wer");
insert into tsr1 values(null,1,1),(null,1,2),(null,2,2);

#egon老师教过哪些人? 
select tea2.name,stu1.name from tea2 join tsr1 join stu1 
on
tea1.id =t_id and stu.id = s_id
where tea.name = 'egon';

# 子查询实现 
select * from stu where id in (select s_id from tsr where t_id = (select id from tea where name = "egon"));