MYSQL基础知识的复习2

1.修改表中的数据

update 表名 set 要修改的字段 where 条件;-- 如果修改多个字段那么字段和字段之间用逗号隔开

2.查询（很重要）

1.查询表中部分字段：
select 字段名,字段名... from 表名;
2.查询所有字段：
select * from 表名;-- 遍历该表的结构才能知道该表具有哪些字段，所以性能慢。开发的时候列出所有字段名。
3.可以为查询的字段起别名：
select 字段名 as 别名,... from 表名;-- as可以省略，中间的空格不能省
select 字段名 别名,... from 表名;
4.去重：
select distinct 字段 from 表名;
select distinct 字段,字段,.... from 表名;-- 当后面的地段全部相同才算重复
5.排序（order by 字段）：
select 字段,字段... from 表名 order by 字段1,字段2...; 默认升序，如果需要降序那就在字段后面加上desc。
先根据字段1进行排序，如果字段一相同再根据字段2排序。
6.可以为查询的结果进行算术运算：

7.根据条件查询数据：
select * from 表名 where 条件; -- * 表示通配符。
8.mysql分页：currentPage：当前要显示的页码 pageSize：每页显示的记录数
limit 限制

• -- 第一页的内容，每页显示五条记录
  select 字段名 from 表名 limit 0,5;
• -- 第二页的内容，每页显示五条记录
  select 字段名 from 表名 limit 5,5;
• -- 第三页的内容，每页显示五条记录
  select 字段名 from 表名 limit 10,5;
  总结：select * from 表名 limit(currentPage-1)*pageSize,pageSize;
  顺序：where>order by>limit

练习：

CREATE DATABASE Company;

USE Company;

CREATE TABLE Department (
	dept_id INT PRIMARY KEY,
	dept_name CHAR (10) NOT NULL
);

CREATE TABLE Emp (
	e_id INT PRIMARY KEY,
	e_name CHAR (10) NOT NULL,
	e_sex CHAR (2) DEFAULT '男' CHECK ('男' OR '女'),
	e_birthday datetime,
	party CHAR (2) CHECK ('是' OR '否'),
	e_jointime datetime,
	dept INT,
	np CHAR (10)
);

CREATE TABLE Salary (
	emp_id INT,
	mon INT CHECK (mon BETWEEN 1 AND 12),
	base_salary DOUBLE CHECK (base_salary >= 0),
	allowance DOUBLE CHECK (allowance >= 0),
	subsidy DOUBLE CHECK (subsidy >= 0),
	bonus DOUBLE CHECK (bonus >= 0),
	deduct DOUBLE CHECK (deduct >= 0),
	revenue DOUBLE CHECK (revenue >= 0)
);

ALTER TABLE Emp ADD CONSTRAINT aa FOREIGN KEY (dept) REFERENCES Department (dept_id);

ALTER TABLE Salary ADD CONSTRAINT ab FOREIGN KEY (emp_id) REFERENCES Emp (e_id);
insert into department values(1,'财务处'),
(2,'人事处'),
(3,'市场部');
insert into emp values(1,'孙华','男','1952-01-03','是','1970-10-10',1,'天津市'),
(2,'陈明','男','1945-05-08','否','1965-01-01',2,'北京市'),
(3,'程西','女','1980-06-10','否','2002-07-10',1,'北京市'),
(4,'苏天气','女','1965-03-10','是','1987-10-10',2,'天津市'),
(5,'刘少文','男','1942-01-11','否','1960-08-10',2,''),
(6,'刘新','男','1952-10-08','否','1970-07-01',1,'上海市'),
(7,'余晖','女','1980-12-04','否','2001-07-10',3,'武汉市'),
(8,'王晓艳','女','1980-11-10','是','2001-07-10',2,'河北省'),
(9,'李玉','女','1964-02-12','是','1989-07-15',1,'天津市'),
(10,'周涛','男','1963-02-10','否','1983-07-20',3,'河北省'),
(11,'欧阳少兵','男','1965-04-19','否','1984-07-12',2,'北京市'),
(12,'张旗','男','1980-11-10','否','1999-09-18',3,'天津市');
insert into emp values(13,'李涵','女','1982-5-10','否','2002-02-14',3,'天津市'),
(14,'魏君','女','1977-3-23','是','1993-01-10',3,''),
(15,'李艺','男','1979-9-18','否','2001-07-20',3,'上海市');

insert into salary values(1,8,2800,1200,180,200,0,220),
(2,8,1600,1000,200,300,0,180),
(3,8,1880,880,200,260,50,120),
(4,8,2200,980,180,230,80,110),
(5,8,2300,880,210,0,0,125),
(6,8,2100,600,220,180,50,110),
(7,8,2200,800,180,200,120,120),
(8,8,2000,780,120,100,0,100),
(9,8,2500,870,130,150,0,120),
(10,8,2350,700,250,180,50,120),
(11,8,1900,800,260,130,0,100),
(12,8,1800,500,200,100,50,100),
(13,8,1580,500,200,100,0,100),
(14,8,1300,450,200,100,0,100),
(15,8,1380,450,200,100,0,10);

-- a)   查询所有职工的基本信息 
select * from emp;
-- d)   查询出所有男职工的基本信息 
select * from emp where e_sex = '男';
-- f)   查询出所有入职时间在2000 年以后的女职工的职工号、姓名和入职时间 
select e_id,e_name,e_jointime from emp where e_jointime > '2000-01-01' and e_sex = '女';
-- g)   查询出所有职工的职工号、姓名、入职时间
select e_id,e_name,e_jointime from emp;
-- h)   查询出所有姓李的职工的基本信息 
select * from emp where e_name like '李%';
-- i)   查询出财务处的所有的党员 
select e_name from emp where party = '是' and dept = 1;
-- j)   查询所有的女党员 
select e_name from emp where party = '是' and e_sex = '女';
-- k)   查询出所有出生年月在1960-1-1 以前的员工 
select e_name from emp where e_birthday < '1960-1-1';
-- l)  查询出所有职工的姓名和出生日期
select e_name,e_birthday from emp;
-- m)   查询出所有籍贯是北京市或天津市的职工信息（使用or 和in 分别查询）
select * from emp where np in('北京市','天津市'); 
select * from emp where np = '北京市' or np = '天津市'; 
-- n)   查询出所有籍贯不是天津市的职工信息（使用not查询） 
select * from emp where np not in('天津市');
-- o)   查询出所有籍贯不是天津市也不是北京市的员工信息（使用 and 和not in 分别
-- 查询） 
select * from emp where np not in ('天津市','北京市');
select * from emp where np != '天津市' and np != '北京市';
-- p)   查询出职工号在5-10 之间的员工信息（使用 and 和between 分别查询） 
select * from emp where e_id between 5 and 10;
select * from emp where e_id >=5 and e_id <= 10;
-- s)   查询出所有实际工资在3000-3500 之间的职工号和税收 
select emp_id,revenue from salary where (base_salary + allowance + subsidy + bonus - deduct - revenue) between 3000 and 3500;
-- t)   查询出所有籍贯为空的职工的基本信息
  select * from emp where np = '';
-- y)   现在每个职工都要拿出自己实际工资的百分之8 作为住房公积金使用，查询出
-- 所有职工的职工号和应拿出的公积金 
select (base_salary + allowance + subsidy + bonus - deduct - revenue) * 0.08 公积金 from salary;
-- 6.   针对上表，做如下操作： 
-- a)   新添加16 号员工的信息，其他字段值自定 
insert into emp(e_id,e_name) values(16,'刘德华');
-- b)   删除入职时间在1950 年以前的员工信息
delete from emp where e_jointime < 1950-1-1; 
-- c)   工资的调整： 
-- i.   1-5 号职工，基本工资增加200 
update salary set base_salary = base_salary + 200 where emp_id between 1 and 5;
-- ii.   6-10 号职工，基本工资增加百分之十，津贴增加 50 
update salary set base_salary = base_salary * 1.1 , allowance = allowance + 50 where emp_id between 6 and 10;
-- iii.  11 号以上职工，基本工资增加 150，津贴增加 30，补助增加20 
update salary set base_salary = base_salary + 150 , allowance = allowance + 30 , subsidy = subsidy + 20 where emp_id > 11;
-- iv.  所有职工奖金增加80 
update salary set  bonus = bonus + 80;
-- v.   有“扣除”项的职工，没有奖金
update salary set bonus = 0 where deduct > 0;

嵌套查询：把一个查询的结果作为另一个查询的条件值

例如：查询财务处员工的信息
select * from emp where dept = (select dept_id from department where dept_name = '财务处');
如果后面查询的条件返回多条那么 = 号 改为 in
什么情况下用嵌套？
查询的结果与查询的条件不在一张时用嵌套。

联表查询：联接多张表才可以查询出想要的字段

例如：查询员工的信息以及员工对应的部门名。
select emp.* ,dept_name from emp,department where emp.dept = department.dept_id; (内联的一种形式)
emp.dept = department.dept_id为联表条件，如果没有联表条件则会出现笛卡尔积
如果联表n张则至少需要n-1个联表条件

1.内联（inner join）：查询所有满足查询条件的记录

比如部门表中有一个研发部，而没有人在研发部，那么内联结果不会出现研发部
select emp.* ,dept_name from emp [inner] join department on emp.dept = department.dept_id;
例如：查询员工的信息、员工所在部门名称、员工的实际工资。
select emp.* ,dept_name,(base_salary + allowance + subsidy + bonus - deduct - revenue) 实际工资 from emp,department,salary
where emp.e_id = salary.emp_id and emp.dept = department.dept_id;

2.左联（left join）：查询出所有满足联表条件的记录以及左表未满足条件的记录

比如部门表中有一个研发部，而没有人在研发部，那么左联结果会出现研发部（部门表为左表）
select * from department left join emp on emp.dept = department.dept_id;

3.右联（right join）：查询出所有满足联表条件的记录以及右表未满足条件的记录

比如部门表中有一个研发部，而没有人在研发部，那么左联结果会出现研发部（部门表为右表）
select * from emp right join department on emp.dept = department.dept_id;

练习

create database emp;
use emp;
-- 创建部门表/
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);

-- 创建雇员表/
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);

-- 创建工资等级表
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);

INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

-- 列出在销售部工作的员工的姓名
select ename from emp where deptno = (select deptno from dept where dname = '销售部');
-- 列出与庞统从事相同工作的所有员工及部门名称。
select ename,dname from emp join dept on dept.deptno = emp.deptno where emp.job = (select job from emp where ename = '庞统') and ename != '庞统';
-- 列出薪金比关羽高的所有员工。
select ename from emp where sal > (select sal from emp where ename = '关羽');
-- 列出至少有一个员工的所有部门
select distinct dname from dept d,emp e where d.deptno = e.deptno;
-- 列出职位为文员的姓名及所在部门名
select ename,dname from emp join dept on emp.deptno = dept.deptno where job = '文员';
-- 列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门
select dname,emp.* from dept left join emp on dept.deptno = emp.deptno;

4.自联

查询的内容都在一张表时.(把一张表当做两张表看)
例子：查询员工姓名以及员工的领导姓名
select yg.ename 员工姓名,ld.ename 领导姓名 from emp yg,emp ld where yg.mgr = ld.empno;
查询刘备所属的工资等级以及工资
select grade,sal from emp,salgrade where sal between losal and hisal and ename = '刘备';

练习

-- 续上方emp数据库
-- 1.查询出部门编号为30的所有员工 
select ename from emp where deptno = 30;
-- 2.所有销售员的姓名、编号和部门编号。
select ename,empno,deptno from emp where job = '销售员';
-- 3.找出奖金高于工资的员工。 
select ename from emp where comm > sal;
-- 4.找出奖金高于工资60%的员工。 
select ename from emp where comm > sal * 0.6;
-- 5.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。 
select * from emp where (job = '经理' and deptno = 10)or(deptno = 20 and job = '销售员');
-- 6.找出部门编号为10中所有经理，部门编号为20中所有销售员， 
-- 还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
select * from emp where (deptno =10 and job = '经理')or(deptno = 20 and job = '销售员')or(job not in('经理','销售员') and sal >= 20000);
-- 7.有奖金的工种。 
select distinct job from emp where comm > 0;
-- 8.无奖金或奖金低于1000的员工。 
select ename from emp where comm between 0 and 1000 or comm is null;
-- 9.查询名字由三个字组成的员工。 
select ename from emp where ename like '___';
-- 10.查询2000年入职的员工。 
select ename from emp where hiredate between '2000-1-1' and '2000-12-31';
-- 11.查询所有员工详细信息，用编号升序排序 
select * from emp order by empno;
-- 12.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序 
select * from emp order by sal desc ,hiredate;
-- 13.返回员工姓名及其所在的部门名称。
select ename,dname from emp,dept where emp.deptno = dept.deptno;
-- 14.返回从事文员工作的员工姓名和所在部门名称
select ename,dname from emp,dept where emp.deptno = dept.deptno and job = '文员';
-- 15.返回与30部门员工工资相同的员工姓名与工资。
select ename,sal from emp where sal in (select sal from emp where deptno = 30) and deptno != 30;
-- 16.查出比经理薪水还高的员工信息
select * from emp where sal > (select sal from emp where job = '经理' order by sal limit 0,1);
-- 17.返回员工和所属上司的姓名 
select yg.ename,ss.ename from emp yg,emp ss where ss.empno = yg.mgr;
-- 18.返回雇员的雇佣日期早于其上司雇佣日期的员工及其上司姓名 
select yg.ename 雇员名称,ss.ename 上司名称 from emp yg,emp ss where ss.empno = yg.mgr and yg.hiredate < ss.hiredate;
-- 19.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。 
select yg.empno,yg.ename,dname from emp yg,emp ss,dept where ss.empno = yg.mgr and yg.deptno = dept.deptno and yg.hiredate < ss.hiredate;
-- 20.查询刘备所属的工资等级以及工资。 
select grade,sal from emp,salgrade where sal between losal and hisal and ename = '刘备';
-- 21.返回工资处于第四级别的员工的姓名。
select ename from emp where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4);
select ename from emp,salgrade where sal between losal and hisal and grade = 4;
-- 22.返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select ename,dname,losal,hisal from emp,salgrade,dept where sal between losal and hisal and grade = 2 and emp.deptno = dept.deptno;


create table city(cityID int auto_increment primary key,cityName varchar(20));
create table flight (flightID int auto_increment primary key,
StartCityID int references city(cityID),
endCityID int references city(cityID),
StartTime datetime);

insert into city values(null,'北京'),(null,'上海'),(null,'广州');

insert into flight values
(null,1,2,'2019-7-11 9:37:23'),(null,1,3,'2019-7-11 11:37:23'),(null,1,2,'2019-7-11 12:37:23'),(null,2,3,'2019-7-11 10:37:23');
-- 1、查询起飞城市是北京的所有航班，按到达城市的名字排序
select * from flight f,city q,city w where f.startcityid = q.cityid and f.endcityid = w.cityid and q.cityname = '北京' order by w.cityname;
-- 2. 查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）
select q.cityname 起飞城市,w.cityname 到达城市,starttime 起飞时间,flightid 航班号 from flight f,city q,city w where f.startcityid = q.cityid 
and f.endcityid = w.cityid and q.cityname = '北京' and w.cityname = '上海';
-- 3.查询具体某一天（2019-7-11）的北京到上海的的航班
select * from flight f,city q,city w where f.startcityid = q.cityid 
and f.endcityid = w.cityid and q.cityname = '北京' and w.cityname = '上海' order by w.cityname
and StartTime like '2019-7-11%';