MySQL_03-数据库CRUD语句

目录

一、Insert语句

二、Update语句

三、Delete删除语句

四、Select语句（重点）

基本使用

查询增强

分页查询

分组增强

---

一、Insert语句

二、Update语句

-- 修改值
UPDATE emp SET salary = salary + 1000 where user_name = '红孩儿'

三、Delete删除语句

四、Select语句（重点）

基本使用

-- 学生分数表
CREATE TABLE student(
	id INT,
	name VARCHAR(32),
	chinese double,
	math double,
	english double
	) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
	
	INSERT INTO student VALUES(1,'赵云',98,78,69),
															(2,'张飞',56,78,86),
															(3,'关羽',60,99,56),
															(4,'刘备',98,62,88);
	
	
-- 统计每个学生的分数
select name,(chinese+math+english) from student;

-- 将所有学生分数加10分
select name,(chinese+math+english+10) from student;

-- 使用别名
select name as '姓名',(chinese+math+english) as '总分' from student;

查询增强

案例：

-- 查询加强
CREATE TABLE emp(
  id INT,
  name VARCHAR(32),
  sex CHAR(1),
  birthday DATE,
  entry_date DATETIME,
  job VARCHAR(32),
  salary DOUBLE,
	resume TEXT) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO emp VALUES(10,'红孩儿','男','2002-10-01','2002-10-01 11:11:11','放火的',3000,'家里有背景，要吃唐僧肉')
INSERT INTO emp VALUES(10,'Bob','男','2002-10-01','2002-10-01 11:11:11','CLERK',3000,NULL,'家里有背景，要吃唐僧肉')
INSERT INTO emp VALUES(10,'Smith','男','2002-10-01','2002-10-01 11:11:11','MANAGER',3000,'200.00','家里有背景，要吃唐僧肉')
INSERT INTO emp VALUES(10,'alice','女','2002-10-01','2002-10-01 11:11:11','MANAGER',1000,'200.00','家里有背景，要吃唐僧肉',10);
INSERT INTO emp VALUES(10,'Json','男','2002-10-01','2002-10-01 11:11:11','MANAGER',2000,'200.00','家里有背景，要吃唐僧肉',20);
INSERT INTO emp VALUES(10,'Tbag','男','2002-10-01','2002-10-01 11:11:11','MANAGER',6000,'200.00','家里有背景，要吃唐僧肉',30);


-- 查询日期大于**入职的员工
select * from emp where entry_date>'2000-1-1'

-- 模糊查询：%表示多个任意字符
select * from emp where name like 'S%'

-- 模糊查询：_表示单个任意字符
select * from emp where name like 'Bo_'

-- 查询comm为空的员工
select * from emp where comm is null;


-- 查询表结构
desc emp;

-- 排序，asc升序，desc降序，默认升序
-- 按工资排序
select * from emp order by salary asc
select * from emp order by salary desc

-- 先按部门号降序，再按工资升序
select * from emp order by deptno desc,salary asc;

结果：

分页查询

-- 分页查询
-- 按编号升序排列，再每页取两条数据
-- 第一页
select * from emp order by id limit 0,2
-- 第二页
select * from emp order by id limit 2,2
-- 第三页
select * from emp order by id limit 4,2;

-- 推导一个公式
-- n:第几页，m:每页要查询的记录数
select * from emp order by id limit (n-1)*m,m;

分组增强

-- 分组增强
-- 显示每种岗位的总数，平均工资
select count(*),avg(salary),job from emp group by job;

-- 显示员工总数以及获得补助的员工数
select count(*),count(comm) from emp;

-- 显示员工数以及没获得补助的员工数
select count(*),count(if (comm is null,1,null)) from emp;
select count(*),count(*)-count(comm) from emp;

-- 显示雇员工资的最大差额
select max(salary) -min(salary) from emp;

-- 统计各个部门的平均工资，并且是大于2000，按照平均工资从高到低排序，取前两条记录
select deptno,avg(salary) as avg_salary from emp group by deptno having avg_salary >2000 order by avg_salary desc limit 0,2;