第三章 MySQL数据库

1.mysql的常用的数据类型：

2.创建表：

部门表(部门编号(deptno)；部门名称(dname)；部门地址(loc))
CREATE TABLE 表名(
字段 字段类型， …)
CREATE TABLE dept(
deptno INT,
dname VARCHAR(30),
loc VARCHAR(100));

2.1.查看表结构：
desc表名：DESC dept;
2.2.查询 表中的 所有数据
SELECT * FROM dept;
2.3.添加
INSERT INTO 表名(字段1，字段2…) VALUES(值1，值2…);
2.4.所有的字段都添加

INSERT INTO 表名 VALUES(值1，值2…);
INSERT INTO dept(deptno,dname,loc) VALUES(10,’董事会’,’北京海淀区中关村’);
INSERT INTO dept VALUES(20,’人事部’,’云南丽江’);
INSERT INTO dept VALUES(30,’研发部’,’山西太原’);
INSERT INTO dept VALUES(40,’测试部’,’广东深圳’);
INSERT INTO dept VALUES(50,’后勤部’,’四川成都’);

2.5.删除表中 所有的数据
DELETE FROM dept;
2.6.指定删除数据 where：条件语句
DELETE FROM dept WHERE deptno=50;
SELECT * FROM dept;
3.添加数据的时候 暂时不要把 编号 重复

员工表(emp)：员工编号(empno) INT：员工姓名(ename) VARCHAR(30)：职位(job) VARCHAR(20)：领导编号(mgr) INT：入职日期(hiredate) DATE：工资(sal) DOUBLE奖金(bonus) DOUBLE：部门编号(deptno) INT
CREATE TABLE emp(
empno INT,
ename VARCHAR(30),
job VARCHAR(20),
mgr INT,
hiredate DATE,
salDOUBLE,
bonus DOUBLE,
deptno INT
);

3.1.查看表结构
DESC emp;
3.2.查看表中数据
SELECT * FROM emp;
3.3.删除表中数据
DELETE FROM emp;
3.4.删除表
语法结构：drop table 表名，如：DROP TABLE emp;
3.6.插入数据

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,bonus,deptno)
VALUES(1001,’rose’,’BOOS’,NULL,’2010-10-01′,100000,0,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,bonus,deptno)
VALUES(1001,’张三丰’,’BOOS’,NULL,’2010-10-01′,100000,0,10);
INSERT INTO emp VALUES(1002,’妲己’,’后勤经理’,1001,’2010-12-11′,25000,100,20);
INSERT INTO emp VALUES(1003,’张无忌’,’研发经理’,1002,’2010-12-15′,25000,500,30);
INSERT INTO emp VALUES(1004,’杨过’,’人事经理’,1003,’2010-12-12′,20000,100,40);
INSERT INTO emp VALUES(1005,’小龙女’,’测试经理’,1004,’2010-12-18′,15000,300,50);
INSERT INTO emp VALUES(1006,’周芷若’,’销售经理’,1005,’2010-12-12′,50000,700,60);
INSERT INTO emp VALUES(1007,’花木兰’,’保卫经理’,1006,’2010-12-13′,10000,100,70);
INSERT INTO emp VALUES(1008,’后裔’,’秘书经理’,1007,’2010-12-10′,30000,500,80);
INSERT INTO emp VALUES(1009,’程咬金’,’人力资源’,1003,’2012-01-01′,4000,0,40);
INSERT INTO emp VALUES(1010,’蔡文姬’,’人力资源’,1003,’2012-01-05′,4500,0,40);
INSERT INTO emp VALUES(1012,’典韦’,’程序猿’,1002,’2012-01-01′,20000,5000,30);
INSERT INTO emp VALUES(1012,’橘右京’,’程序猿’,1002,’2012-01-02′,18000,2000,30);
INSERT INTO emp VALUES(1012,’韩信’,’测试专员’,1004,’2012-02-01′,8000,100,50);

4.需求：
4.1、查询员工的姓名、入职日期、工资
SELECT ename,sal,bonus FROM emp;
4.2、查询薪资大于8000 的员工有哪些人，并显示他们的姓名、薪资、奖金。
条件语句查询 where：SELECT ename,sal,bonus FROM emp WHERE sal>8000;
4.3、查询薪资是15k的员工有哪些，显示这些人的员工编号，姓名，薪资，入职日期。
SELECT empno,ename,sal,hiredate FROM emp WHERE sal= 15000;
并且and或者or
4.4、查询薪资大于8k并且小于20k的员工有哪些？
SELECT * FROM emp WHERE sal >8000 AND sal <=20000;
或者：SELECT * FROM emp WHERE sal BETWEEN 8000 AND 20000;
4.5、薪资不在8000到20k之间的员工有哪些？
SELECT * FROM emp WHERE sal BETWEEN 9000 AND 20000;
或者 ：SELECT *FROM emp WHERE sal= 20000;
4.6、查询年薪为15k或者9k的员工有哪些？
SELECT *FROM emp WHERE sal=15000 OR sal=9000;
或者：SELECT *FROM emp WHERE sal IN (15000,9000);
4.7、查询职位是程序猿和测试专员的员工的姓名、薪资、职位
SELECT ename,sal,job FROM emp WHERE job=’程序猿’ OR job=’测试专员’;
或者：SELECT ename,sal,job FROM emp WHERE job IN (‘程序猿’,’测试专员’);
4.8、职位是程序猿或者测试专员的员工，并且薪资在5k-10k之间的员工有哪些？
SELECT * FROM emp WHERE job IN (‘程序猿’,’测试专员’)
AND sal BETWEEN 5000 AND 10000;
4.9、查询2010年入职的员工有哪些？
SELECT * FROM emp WHERE hiredate BETWEEN’2010-01-01′ AND ‘2010-12-31’;
4.10、显示每个员工的年薪。
SELECT ename,sal,sal1*12 year_sal FROM emp;
4.11、显示每一个员工每年赚多少钱
SELECT ename,sal,bonus,(sal+bonus)*12 year_sal FROM emp;
处理null：SELECT ename,sal,bonus,(sal+IFNULL (bonus,0))*12 year_sal FROM emp;
4.12、按照薪资高低对员工进行排序查询
SELECT * FROM empORDER BY sal ASC;
4.13、按照奖金进行员工进行排序，奖金高的在前面
SELECT * FROM empORDER BY bonus DESC;
4.14、按照入职日期对员工排序，入职最晚的在前面
SELECT * FROM empORDER BY bonus DESC;
4.15、查询老板的名字
SELECT ename form emp WHERE job=’Boss’;
4.16、部门20的员工统一涨薪20%
SELECT ename,sal,sal*1.2 new_seal FROM emp WHERE deptno=20;
4.17、部门30的员工统一涨薪20%
SELECT ename,sal,sal*1.2 new_seal FROM emp WHERE deptno=30;
4.18、部门40的员工统一涨薪20%

SELECT ename,sal,sal*1.2 new_seal FROM emp WHERE deptno=40;
SELECT ename,sal,deptno,
CASE
WHEN deptno=20 THEN sal*1.2
WHEN deptno=30 THEN sal*1.3
WHEN deptno=40 THEN sal*1.4
ELSE sal
END new_sal FROM emp;

4.19、获取当前时间 now（）

SELECT NOW();

SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT HOUR(NOW());

4.20、查询每名员工入职多少年
SELECT ename,hiredate,YEAR(NOW())-YEAR(hiredate) FROM emp;
4.21、模糊查询

%：表示一个字符
查询员工姓名中有‘张’这个字符的员工信息
SELECT *FROM emp WHERE ename LIKE ‘%张%’;
查询第三个字符是‘姬’这个字符的员工
SELECT *FROM emp WHERE ename LIKE’__姬’;

4.22、查询职位中地三个字符为‘猿’的员工，并且薪资在5k-18k之间

按照他们的入职日期排序（降序）
SELECT * FROM emp WHERE job LIKE’__猿’ANDsal BETWEEN 5000 AND 18000ORDER BY hiredate DESC
聚合函数 count() sum() avg() max（） min()

4.23、显示当前emp表中有多少条记录
SELECT COUNT(empno) FROM emp;
SELECT COUNT(bonus) FROM emp;– 注意 0和null是有区别的
4.24、显示所有员工的薪资总和
SELECT SUM(sal) FROM emp;
4.25、查询公司每月支付员工的金额
SELECT SUM(sal)+SUM(bonus) FROM emp;
4.26、、查询部门20的薪资总和
SELECT SUM(sal) FROM emp WHERE deptno=20;
4.27、查询该公司员工的平均工资
SELECT AVG(sal) FROM emp;
4.28、查询该公司最高薪资是多少
SELECT MAX(sal) FROM emp;
4.29子查询：该公司最高工资的员工姓名

注意：统计函数不可以作为where的条件
step1:
SELECT MAX(sal) FROM emp
step2:
SELECT ename FROM emp WHERE sal =(SELECT MAX(sal) FROM emp);

那些员工的薪资低于平均工资

step1:
SELECT AVG(sal) FROM emp;
step2:
SELECT *FROM emp WHERE sal < (SELECT AVG(sal) FROM emp)

4.30、查询程序猿的平均工资 以及 工资总和
SELECT AVG(sal),SUM(sal) FROM emp WHERE job=’程序猿’
4.31、查询与“关羽”图标吗的员工有哪些人

step1:
SELECT deptno FROM emp WHERE ename =’周芷若’
step2:
SELECT *FROM emp WHERE deptno =(SELECT deptno FROM emp WHERE ename =’周芷若’)

4.32.分组查询 group by

每个部门的平均薪资格式多少 10 20 30
注意：查询的字段，处理聚合函数 只能是group by 后面的字段
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno
查询每种职位的薪资总和
SELECT job,SUM(sal) FROM emp GROUP BY job
having
部门人数等于3 的部门编号，以及部门人数，注意：where条件后面不可以加聚合函数
聚合函数的条件只能放在having后面
SELECT deptno,COUNT(*) FROM empGROUP BY deptno HAVING COUNT(*)>=3

4.33、查询职位不是程序猿的员工，部门的平均薪资大于等于10000，并且按照平均薪资降序排列
SELECT deptno,AVG(sal) FROM emp
WHERE job !=’程序猿’
GROUP BY deptno
HAVING AVG(sal)>=10000
ORDER BY AVG(sal) DESC

多表查询 语法结构
SELECT * FROM emp;
SELECT 字段1，字段2….from表名
where普通条件1 AND 普通条件2
GROUP BY 分组字段
HAVING 聚合函数
ORDER BY 排序字段1，排序字段2 DESC

4.34、查询薪资咋6000-20000之间的部门，并且部门人数大于等于3，按照降序排列
SELECT deptno,COUNT(*) FROM emp
WHERE sal BETWEEN 4000 AND 26000
GROUP BY deptno
HAVING COUNT(*)>=3
ORDER BY COUNT(*)>=3 DESC
4.35、查询员工详细详细，把偶偶员工姓名、薪资、入职日期、部门编号、部门名称、部门所在地

SELECT e.ename,e.sal,e.hiredate,e.deptno,d.dname,d.loc* FROM emp e,dept d WHERE e.deptno=d.deptno
join on语法
SELECT e.ename,e.sal,e.hiredate,e.deptno,d.dname,d.loc* FROM emp e JOIN dept d ON e.deptno=d.deptno

4.36、查询员工姓名，以及所在的部门名称和部门所在地

查询部门人数>=3的部门、并且按照部门人数降序排列
SELECT d.deptno,d.dname,COUNT(e.empno) ct FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname
HAVING COUNT(e.empno)>=3
ORDER BY COUNT(e.empno) DESC
查询员工姓名以及该员工领导的姓名
SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno;
查询部门平均工资薪资>10k的部门名称，并且按照平均薪资有高到底排序
SELECT d.deptno,d.dname,AVG(e.sal) FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname
HAVING AVG(sal)>10000
ORDER BY AVG(e.sal) DESC

建表 user步骤：1.右键创建表输入表名字段；2.约束：主键约束（存储的内容是唯一的+不能为空）；3.非空约束（存储的内容不能为空）；4.自增 自动增加序列
添加：INSERT INTO USER(id,NAME,pwd) VALUE(1001,’admin’,’123′);
不用添加id字段，自增功能 会自动增加序列
INSERT INTO USER(NAME,pwd) VALUE(‘rose’,’123′);
违反主键约束（primary），id 不能够重复
INSERT INTO USER(id,NAME,pwd) VALUE(1002,’jack’,’789′);
违反非空约束（字段不能为null）
INSERT INTO USER(NAME,pwd) VALUE(NULL,’123′);
注；一般创建对象 都需要给 主键约束
删除：delete from user where id = 1002;
修改:update 表名 set字
UPDATE USER SET NAME =’rose1′,pwd=’888’WHERE id=1003;
分页limit 1,21参：从这个下标开始 2参：记录数
SELECT *FROM emp LIMIT 5,5;
查询公司里最高工资的前三人
SELECT *FROM emp ORDER BY sal DESC LIMIT 0,3