MySQL数据库～～～～～查询行(文件的内容)
1. 单表查询
- 语法
select distinct 字段 from 库名.表名
where 条件
group by 字段 # 分组
having 筛选 # 过滤
order by 字段 # 排序
limit 限制条件
- 关键字的执行优先级
1. from :找到表
2. where:拿着where指定的约束条件,去文件/表中取出一条条记录
3. group by:将取出的一条条记录进行分组,如果没有 group by则整体作为一组
4. having:将分组的结果进行having过滤
5. select:执行select
6. distinct:去重
7. order by:将结果按条件排序
8. limit:限制结果的显示条数
- 查询操作
创建一个表:
create table employee(
id int not null unique auto_increment,
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(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','北京办事处外交大使',7300.33,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)
;
ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
1. distinct 去重:
select distinct post from employee;
distinct 必须写在所有查询字段的前面
2. 四则运算查询:
select name,salary*12 from employee;
除了乘法外,加减乘除都可以
3. 自定义显示格式 concat 用法
select concat ('姓名:',name,'年薪:',salary*12) as Annual_salary from employee;
as + 新字段名，就是起一个别名的意思
concat_ws() # 第一个参数为分隔符来进行字符串拼接
select concat_ws(':',name,salary*12) as Annual_salary from employee;
4. where 约束:
1. 比较运算符:> < >= <= <> !=
select name from employee where post = 'sale';
2. between 10 and 15 # id值在10到15之间
select * from employee where id between 10 and 15;
3. in(1,3,6) # id值是 1,3,6
select * from employee where id in(1,3,6);
4. like 'egon%'
pattern可以是 % 或 _
% 表示任意多个字符:
select * from employee where name like 'wu%';
_ 表示一个字符:
select * from employee where name like 'al_';结果空
select * from employee where name like 'al__';结果alex
5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
select * from employee id > 10 and name like 'al%';
select * from employee not id > 10; not 取反
5. group by 分组:
select post,max(salary) from employee group by post;
统计每个岗位的名称及最高工资
select post from employee group by post,id;
分组时可以跟多个条件,那么这么多个条件同时重复才算是一组,group by 后面多条件用逗号分隔
ONLY_FULL_GROUP_BY 模式:
set global sql_mode = 'ONLY_FULL_GROUP_BY';
如果设置了这个模式,那么select后面只能写 group by 后面的分组依据字段和聚合函数统计结果
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
select post,group_concat(name) from employee group by post;
# 按照岗位分组，并查看组内所有成员名，通过逗号拼接在一起
SELECT post,GROUP_CONCAT(name,':',salary) as emp_members FROM employee GROUP BY post;
# 按照岗位分组，并查看组内所有成员名:薪资，通过逗号拼接在一起
聚合函数:聚合函数聚合的是组的内容，若是没有分组，则默认一组
count() # 统计个数
max()
min()
avg()
sum()
6. having 分组再进行过滤:
select post,max(salary) from employee group by post having max(salary) > 2000;
having 过滤后面的条件可以使用聚合函数,where不行
7. order by 排序:
升序:
select * from employee order by age;
select * from employee order by age asc;
降序:
select * from employee order by age desc;
多条件排序:
select * from employee order by age asc,salary desc;
按照age字段升序,age相同的数据,按照salary降序排列
8. limit 限制查询的记录数:
select * from employee order by salary desc
limit 3; # 默认初始位置为0,从第一条开始顺序取出3条
limit 0,5 # 从位置0(第一条)开始往后查询5条
limit 5,5 # 从位置5(第六条)开始往后查5条
9. 使用正则表达式查询:
select * from employee where name regexp '^ale';
select * from employee where name regexp 'on$';
select * from employee where name regexp 'm{2}';
2. 多表查询
-
多表查询
笛卡尔积: 将两表所有的数据一一对应生成一张大表.
select * from dep,emp; # 将两个表拼一起
select * from dep,emp where dep.id = emp.dep_id; # 找到两表之间对应的关系记录
select * from dep,emp where dep.id = emp.dep_id and dep.name = '技术'; # 筛选部门名称为技术的大表中的记录
select emp.name from dep,emp where dep.id = emp.dep_id and dep.name = '技术'; # 拿到筛选后的记录的员工姓名字段数据
-
连表查询
- inner join 内连接
第一步: 连表 select * from dep inner join emp on dep.id = emp.dep_id; 第二步: 过滤 select * from dep inner join emp on dep.id = emp_id where dep.name = '技术'; 第三步: 找到对应字段数据 select emp.name from dep inner join emp on dep.id =emp.dep_id where dep.name = '技术';- left join 左连接(left join左边的表为主表,主表记录必须全部显示,辅表没办法对应上的,就通过null来补全)
select * from dep left join emp on dep.id = emp.dep_id;- right join 右连接
select * from dep right join emp on dep.id = emp.dep_id;- union 全连接
select * from dep left join emp on dep.id = emp.dep_id union select * from dep right join emp on dep.id = emp.dep_id;- 子查询: (一个查询结果集作为另一个查询的条件)
select name from emp where dep_id = (select id from dep where name = '技术');1. 子查询是将一个查询语句嵌套在另一个查询语句中. 2. 内层查询语句的查询结果,可以为外层查询语句提供查询条件. 3. 子查询中可以包含: in,not in,any,all,exists,not exists等关键字. 4. 还可以包含比较运算符: = , != , < , > 等.
