po解Navicat15中文版以上在下载资源,如有需要,自行下载。
一、数据库表的操作
1.增
单个增加
-- 只要没有;号结束语句,就能换行 --
insert into `stu`(`name`,age)
values('Tom',20);
多个增加
insert into `stu`(`name`,age)
values('cainiao',20),
('xinshou',20);
2.改
UPDATE stu SET `name`='laosiji'
WHERE id=12;
3.删
DELETE FROM stu
WHERE `name`='jimo';
4.单表查询(重点)
SELECT id,`name`,ismale,salary FROM employee;
对数据进行处理
SELECT id,`name`,
CASE ismale WHEN 1 THEN '男' ELSE '女' END sex,
salary FROM employee;
SELECT id,`name`,
CASE ismale WHEN 1 THEN '男' ELSE '女' END sex,
CASE
WHEN salary>=10000 then '高'
WHEN salary>=5000 then '中'
ELSE '低'
END `level`,
salary
FROM employee;
范围in
select * from department
where companyId in (1,2);-- companyId为1和2的 --
is
select * from employee
where location is null;
between
select * from employee
where salary between 10000 and 12000;-- salary在10000-12000之间的 --
like
select * from employee
where `name` like '%袁%';-- --
and、or
select * from employee
where `name` like '张%' and ismale=0 and salary>=12000;
order by
select *,case ismale
when 1 then '男'
else '女'
end sex from employee
order by sex asc, salary desc;-- 性别升序,薪资降序 --
limit
select * from employee
limit 2,3;-- 跳过2个,截取3个 --
练习登录
select * from `user`
where loginId = 'admin' and loginPwd = '123123';
-- 查询员工表,按照员工的入职时间降序排序,并且使用分页查询
-- 查询第3页,每页5条数据
-- limit (page-1)*pagesize, pagesize
select * from employee
order by employee.joinDate desc
limit 10,5;
5.联表查询
-- 1. 创建一张team表,记录足球队
-- 查询出对阵表
SELECT t1.name 主场, t2.name 客场
FROM team as t1, team as t2
WHERE t1.id != t2.id;
-- 2. 显示出所有员工的姓名、性别(使用男或女显示)、入职时间、薪水、所属部门(显示部门名称)、所属公司(显示公司名称)
SELECT e.`name` 员工姓名,
case ismale when 1 then '男' else '女' end 性别,
e.joinDate 入职时间,
e.salary 薪水,
d.`name` 部门名称,
c.`name` 公司名称
FROM employee e
inner join department d on e.deptId = d.id
inner join company c on d.companyId = c.id
-- 3. 查询腾讯和蚂蚁金服的所有员工姓名、性别、入职时间、部门名、公司名
SELECT e.`name` 员工姓名,
case ismale when 1 then '男' else '女' end 性别,
e.joinDate 入职时间,
e.salary 薪水,
d.`name` 部门名称,
c.`name` 公司名称
FROM employee e
inner join department d on e.deptId = d.id
inner join company c on d.companyId = c.id
WHERE c.`name` in ('腾讯科技', '蚂蚁金服')
博主开始运营自己的公众号啦,感兴趣的可以关注“飞羽逐星”微信公众号哦,拿起手机就能阅读感兴趣的文章啦!