序
表中记录的相关操作一共四种:插入,更新,删除、查询。其中使用最多,也是最难的就是查询。
记录的插入
1. 插入完整数据(顺序插入)
语法一:
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二:
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法:
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
语法:
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
例子:
insert into db1 values(1, "aaa", 20, "male");
insert into db1 values(1, "aaa", 20, "male"), (2, "bbb", 22, "female");
记录的更新
UPDATE 表名 SET 字段1=值1, 字段2=值2, WHERE CONDITION;
例子:
update db1 set name = "ccc" where name = "aaa";
记录的删除
DELETE FROM 表名 WHERE CONITION;
例子:
delete from db1 where name ="xxx";
记录的查询
1.单表查询
格式:
select distinct 字段1,字段2,... from 表名
where 条件
group by 分组条件
having 对分组后的数据进行筛选的条件
order by 排序
limit 分组
关键字的执行优先级:
from 找到表:from
where 拿着where指定的约束条件,去表中取出一条条记录
group by 将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
having 将分组的结果进行having过滤
select 执行select
distinct 去重
order by 将结果按条件排序:order by
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
('aaa','male',18,'20170301','tracher',7300.33,401,1),
('bbb','male',78,'20150302','teacher',1000000.31,401,1),
('ccc','female',18,'20110211','teacher',9000,401,1),
('111','female',48,'20150311','sale',3000.13,402,2),
('222','female',38,'20101101','sale',2000.35,402,2),
('333','female',18,'20110312','sale',1000.37,402,2),
('xxx','male',28,'20160311','operation',10000.13,403,3),
('yyy','male',18,'19970312','operation',20000,403,3),
('zzz','female',18,'20130311','operation',19000,403,3);
1.1 where查询:
where查询:
查询所有员工的姓名和薪水
select name,salary from employee;
查询所有女性员工的姓名和薪水
select name,salary from employee where sex = 'female';
查询薪资在10000到30000之间的员工的姓名和薪水
select name,salary from employee where salary between 10000 and 30000;
查询薪资是19000或是7300.33员工的姓名和薪水
select name,salary from employee where salary in (19000, 7300.33);
匹配名称以a开头的员工的姓名和薪水(%:任意长度任意字符 _:一个任意字符)
select name,salary from employee where name like 'a%';
1.2 group by分组:
group by分组:
分组:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
注意:分组发生在where之后,即分组是基于where之后得到的记录而进行的
注意:可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
按照职位进行分组,查询职位的名称
select post from employee group by post;
按照职位进行分组,查询职位的名称和每一种职位上的员工个数,注意不能单独使用id字段,但是可以使用聚合函数
select post, count(id) from employee group by post;
1.3 聚合函数:
聚合函数:
注意:聚合函数聚合的是组的内容,若是没有分组,则默认一组
count() 计算组内个数
max() 计算组内最大值
min() 计算组内最小值
sum() 计算组内和
avg() 计算组内平均值
1.4 having:
having:
注意:Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
统计平均薪资超过10000的职位
select post, avg(salary) from employee group by post having avg(salary) > 10000;
1.5 select:
select:
注意:执行的是字段,字段也是可以进行计算的
查询所有员工的姓名和薪资
select name, salary from employee;
查询所有员工的姓名和年薪
select name, salary * 12 as nian_xing from employee;
1.6 distinct:
distinct:
注意:distinct后面接多个字段时,表示多个字段接续之后字符串不重复
1.7 order by:
order by:
按照薪资升序排列
select name, salary from employee order by salary asc;
按照薪资降序排列
select name, salary from employee order by salary desc;
1.8 limit:
limit:
按照薪资升序排列, 从第0开始,即先查询出第一条,然后包含这一条在内往后查3条
select name, salary from employee order by salary asc limit 0, 3;
按照薪资升序排列, 从第3开始,即先查询出第6条,然后包含这一条在内往后查3条
select name, salary from employee order by salary asc limit 3, 3;
1.9 正则表达式查询:
正则表达式查询:
查询所有名称以aa开头的员工
select * from employee where name regexp '^aa';
查询所有名称以aa结尾的员工
select * from employee where name regexp 'aa$';
查询所有名称以aa开头且以bb结尾的员工
select * from employee where name regexp '^aa.*[bb]$';
2.多表查询
2.1 表与表之间的关系
表与表之间的关系:
一对一 foreign key + unique
一对多 foreign key
多对多 新建第三张表,建立两个字段,每个字段分别是两张表的外键
判断表关系的方法:
先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)
再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)
总结:
多对一:
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表
多对多
如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
一对一:
如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可
2.2 查询格式
格式:
select 字段列表 from 表1 inner | left | right join 表2 on 表1.字段 = 表2.字段;
2.3 数据准备
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,'aaa'),
(201,'bbb'),
(202,'cc'),
(203,'dd');
(205,'ee');
insert into employee(name,sex,age,dep_id) values
('zhansan','male',18,200),
('lisi','female',48,201),
('wangwu','male',38,201),
('zhaoliu','female',28,202)
('chengyi','female',29,204);
2.4 查询方式
内连接:
不适用任何匹配条件。生成笛卡尔积
select * from employee inner join department;
根据部门ID相同过滤
select * from employee inner join department on employee.dep_id = department.id;
左连接:
将左表中没有匹配成功的内容也输出
select * from employee left join department on employee.dep_id = department.id;
右连接:
将右表中没有匹配成功的内容也输出
select * from employee right join department on employee.dep_id = department.id;
子查询:
子查询是将一个查询语句嵌套在另一个查询语句中
内层查询语句的查询结果,可以为外层查询语句提供查询条件
子查询中可以包含:in、any、all、exists等关键字
还可以包含比较运算符:= 、 !=、> 、<等
in:表示值存在子查询结果集中
any:表示满足子查询中的任意一个条件
all:表示满足所有子查询的条件
exists:子查询为真,外层循环正常执行;只循环为假,直接返回
查询平均年龄在25岁以上的部门名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
查询大于所有人平均年龄的员工名与年龄
select name,age from emp where age > (select avg(age) from emp);
查询为空,因为子查询返回false
select * from employee where exists (select id from department where id = 204);