MySql例题入门

–创建dept，empty，salgrade三张表并插入数据
– Table structure for dept

---

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
deptno int(2) NOT NULL,
dname varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
loc varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (deptno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of dept

---

INSERT INTO dept VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept VALUES (40, ‘OPERATIONS’, ‘BOSTON’);

---

– Table structure for emp

---

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno int(4) NOT NULL,
ename varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
job varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
mgr int(4) NULL DEFAULT NULL,
hiredate date NULL DEFAULT NULL,
sal double(7, 2) NULL DEFAULT NULL,
comm double(7, 2) NULL DEFAULT NULL,
deptno int(2) NULL DEFAULT NULL,
PRIMARY KEY (empno) USING BTREE,
INDEX FK_DEPTNO(deptno) USING BTREE,
CONSTRAINT FK_DEPTNO FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of emp

---

INSERT INTO emp VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-07’, 800.00, NULL, 20);
INSERT INTO emp VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600.00, 300.00, 30);
INSERT INTO emp VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250.00, 500.00, 30);
INSERT INTO emp VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-02-04’, 2975.00, NULL, 20);
INSERT INTO emp VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250.00, 1400.00, 30);
INSERT INTO emp VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-01-05’, 2850.00, NULL, 30);
INSERT INTO emp VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450.00, NULL, 10);
INSERT INTO emp VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-04-19’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5000.00, NULL, 10);
INSERT INTO emp VALUES (7844, ‘TUR%NER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1500.00, 0.00, 30);
INSERT INTO emp VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-05-23’, 1100.00, NULL, 20);
INSERT INTO emp VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-03-12’, 950.00, NULL, 30);
INSERT INTO emp VALUES (7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-03-12’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300.00, NULL, 10);

---

– Table structure for salgrade

---

DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade (
grade int(11) NULL DEFAULT NULL,
losal int(11) NULL DEFAULT NULL,
hisal int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

---

– Records of salgrade

---

INSERT INTO salgrade VALUES (1, 700, 1200);
INSERT INTO salgrade VALUES (2, 1201, 1400);
INSERT INTO salgrade VALUES (3, 1401, 2000);
INSERT INTO salgrade VALUES (4, 2001, 3000);
INSERT INTO salgrade VALUES (5, 3001, 9999);

SET FOREIGN_KEY_CHECKS = 1;

– 查询薪水为5000的员工
SELECT empno,ename FROM emp WHERE sal = 5000;

– 查询 job 为 MANAGER 的员工
SELECT empno,ename,job FROM emp WHERE job =‘MANAGER’;

– 查询薪水不等于 5000 的员工
SELECT empno,ename,sal FROM emp WHERE sal != 5000;

– 查询工作岗位不等于MANAGER的员工
SELECT empno,ename,job FROM emp WHERE job !=‘MANAGER’;

– 查询薪水为1600到3000的员工（第一种方式：采用>=、<=）
SELECT empno,ename,sal FROM emp WHERE sal>=1600 AND sal<=3000;

– 查询薪水为1600到3000的员式（第二种方式：采用between…and…）
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 1600 AND 3000;

– （了解）between … and … 同样也可用在字符上，用在字符上区间为：前闭后开
SELECT ename FROM emp where ename BETWEEN ‘A’ and ‘Z’;

– 查诟津贴为空的员工
select ename FROM emp WHERE comm is NULL ;

– 查询津贴不为空的员工
select ename FROM emp WHERE comm is NOT NULL ;

– 查询工作岗位为“MANAGER”并且薪水大亍2500的员工
SELECT empno,ename,sal,job FROM emp WHERE job =‘MANAGER’ AND sal>2500;

– 查诟出 job 为 SALESMAN 和 job为MANAGER的员工
SELECT empno,ename FROM emp WHERE job =‘MANAGER’ OR job =‘SALESMAN’;

– 查询薪水大于1800，并且部门编号为20或30的员工
SELECT empno,ename,sal FROM emp WHERE (deptno =20 or deptno =30) AND sal>1800;

– 查询出Job为 SALESMAN 和 Job为 MANAGER 的员工
SELECT empno,ename,job FROM emp WHERE job in (‘MANAGER’,‘SALESMAN’);

– 查询出薪水为1600和3000的员工
SELECT empno,ename,sal FROM emp WHERE sal=1600 or sal=3000;
SELECT empno,ename,sal FROM emp WHERE sal in (1600,3000);

– 查询出薪水不是1600和3000的员工
SELECT empno,ename,sal FROM emp WHERE not (sal=1600 or sal=3000);
SELECT empno,ename,sal FROM emp WHERE sal!=1600 or sal!=3000;
SELECT empno,ename,sal FROM emp WHERE sal not in (1600,3000);

– 查询出津贴不为null的员工
SELECT empno,ename,comm FROM emp WHERE comm is NOT NULL;

– 查询以姓名以 M 开头的所有员工‘M%’
SELECT ename FROM emp WHERE ename LIKE ‘M%’;

– 查诟姓名以N结尾的所有员工‘%N’
SELECT ename FROM emp WHERE ename LIKE ‘%N’;

– 查诟姓名中包含O的所有员工‘%O%’
SELECT ename FROM emp WHERE ename LIKE ‘%O%’;

– 查询姓名中第二个字符为A的所有员工‘_A%’
SELECT ename FROM emp WHERE ename LIKE ‘_A%’;

– 查询姓名中倒数第二个字符为E的所有员工‘%E_’
SELECT ename FROM emp WHERE ename LIKE ‘%E_’;

– 查询姓名中第三个字符为N的所有员工姓名‘ R%’
SELECT ename FROM emp WHERE ename LIKE ‘__N%’;

– 按照薪水由小到大排序（系统默认由小到大）
select ename,sal from emp order by sal;

– 取得 job 为 MANAGER 的员工，按照薪水由小到大排序（系统默认由小到大）
SELECT empno,ename,job,sal FROM emp WHERE job=‘MANAGER’ ORDER BY sal;

– 手动指定按照薪水由小到大排序（升序关键字 asc）
select * from emp order by sal asc;

– 手动指定按照薪水由大到小排序（降序关键字desc）
select * from emp order by sal desc;

– 按照 job 和薪水倒序排序
select ename,job,sal from emp order by job desc,sal desc;

– 按照薪水升序排序（不建议采用此方法，采用数字含义不明确，可读性不强，程序不健壮）
select * from emp order by 6;

– 查询员工姓名，将员工姓名全部转换成小写
select lower(ename) as ename from emp;

– 查询员工姓名，将员工姓名全部转换为大写
select upper(ename) as ename from emp;

– 查询并显示所员工姓名的第二个字母
select substr(ename,2,1) from emp;

– 查询员工姓名中第二个字母为A的所有员工
select ename from emp where substr(ename,2,1) = ‘A’;

– 取得员工姓名长度
select ename,length(ename) as nameLength from emp;

– 查询员工姓名及补助，如果补助为Null设置为0；
select ename,ifnull(comm,0) from emp;

– 查询员工薪水不补助的和
select IFNULL(sal,0)+IFNULL(comm,0) from emp;

– 没有补助的员工，将每月补助100，求员工的年薪
select ename, (IFNULL(sal,0) + ifnull(comm,100)) * 12 yearsal from emp;

– 匹配工作岗位，当为MANAGER 时， 薪水上调10% ，当为
– SALESMAN时，薪水上调50%，其它岗位薪水不变
SELECT ename,sal,
CASE job
WHEN ‘MANAGER’ THEN
sal1.1
WHEN ‘SALESMAN’ THEN
sal1.5
ELSE
sal
END salplus
FROM emp;

– 取得工作岗位为manager的所有员工
select * from emp where job = trim(’ manager ');

– 保留整数位或不保留小数位： select round(125.18) ；或者
select round(665.66,0) good;

– 保留1位小数：
select round(666.56,1) good;

– 保留2位小数：
select round(666.656,2) good;

– 个数位四舍五入：
select round(656.66,-1) good;

– rand() 凼数：生成随机数
– 生成一个0≤ v ≤ 1.0的随机数;
SELECT rand();

– 生成一个0-100的随机数
select rand()*100;

– 生成一个0-100的随机整数
select round(rand()*100);

– 查诟出1981-12-03入职的员工
select ename,hiredate from emp where hiredate = ‘1981-06-09’;

– 查诟出02-20-1981入职的员工
select ename,hiredate from emp where hiredate = str_to_date(‘02-20-1981’,’%m-%d-%Y’);

– 查询员工的入职日期，以‘10-12-1980’的格式显示到窗口中；
select ename,hiredate,date_format(hiredate,’%m-%d-%Y’) newhiredate from emp;

– 查询员工的入职日期，以‘10/12/1980’的格式显示到窗口中；
select ename,hiredate,date_format(hiredate,’%m/%d/%Y’) newhiredate from emp;

– 求得薪水的合计
SELECT SUM(sal) FROM emp;

– 求得补助的合计
SELECT SUM(comm) FROM emp;

– 求得总共薪水（工资+补助）合计
SELECT SUM(IFNULL(sal,0)+ IFNULL(comm,0)) FROM emp;

– 求得平均薪水avg(sal)
SELECT AVG(sal) FROM emp;

– max(字段名)函数
SELECT max(sal) FROM emp;
SELECT ename,max(sal) FROM emp GROUP BY ename;

– 求得最晚入职的员工max(sal)
– 说明：日期类型也可以迚行大小比较
SELECT max(hiredate) FROM emp;

– 求得最低薪水min(sal)
SELECT MIN(sal) FROM emp;

– 求得最早入职的员工min(hiredate)
SELECT min(hiredate) FROM emp;

– 求得所有员工数count()
SELECT COUNT() FROM emp;
SELECT COUNT(1) FROM emp;

– 求得补助不为空的所有员工数count(comm)
SELECT COUNT(comm) FROM emp;

– 求得补助为空的员工数量 is null
select count(*) from emp WHERE comm is NULL;

– 可以将这些聚合凼数都放到 select 中一起使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

– 查询全公司有哪些工作岗位
SELECT distinct job FROM emp;

– 查询全公司工作岗位数量
SELECT job, COUNT(distinct job) ‘工作岗位数量’ FROM emp;

– 去除部门编号deptno和工作岗位job重复的记录
SELECT distinct job,deptno FROM emp;

– 找出每个工作岗位的最高薪水
SELECT job,max(sal) FROM emp GROUP BY job;

– 计算每个工作岗位的最高薪水，幵丏按照由低到高迚行排序
SELECT job,max(sal) FROM emp GROUP BY job ORDER BY sal;

– 计算每个部门的平均薪水
SELECT job,avg(sal) FROM emp GROUP BY job;

– 计算出不同部门不同岗位的最高薪水
SELECT deptno,job,avg(sal) FROM emp GROUP BY deptno,job;

– 找出每个工作岗位的最高薪水，除MANAGER之外
SELECT job,max(sal) FROM emp WHERE job!=‘MANAGER’ GROUP BY job ORDER BY sal;

– 找出每个工作岗位的平均薪水，要求显示平均薪水大亍2000的；
– where 是在 group by之前完成过滤；
– having 是在 group by 之后完成过滤；
SELECT job,avg(sal) FROM emp GROUP BY job HAVING AVG(sal)>2000;

– 查询每一个员工所在的部门名称，要求最终显示员工姓名和对应的部门名称（笛卡尔积）
SELECT emp.ename,dept.dname FROM emp , dept WHERE emp.deptno=dept.deptno;

– 查询每一个员工所在的部门名称，要求最终显示员工姓名和对应的部门名称（内连接：等值）
SELECT emp.ename,dept.dname FROM emp JOIN dept ON emp.deptno=dept.deptno;

– 找出每一个员工对应的工资等级，要求显示员工姓名、工资、工资等级（内连接：非等值）
SELECT emp.ename,emp.sal,salgrade.grade FROM emp JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal ORDER BY emp.sal DESC;

– 找出每一个员工的上级领导，要求显示员工姓名及对应的领导姓名（内连接：自连接）
SELECT a.ename ‘员工名’, e.ename ‘领导名’ FROM emp e RIGHT JOIN emp a ON e.empno=a.mgr;

– 找出每一个员工对应的部门名称，要求部门名称全部显示（左/右外连接）
SELECT emp.ename,dept.dname FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno;

– 找出每一个员工对应的领导名，要求显示所有员工（左外连接）
SELECT e.ename,a.ename FROM emp e LEFT JOIN emp a ON e.empno=a.mgr;

– 找出每一个员工对应的部门名称，以及该员工对应的工资等级，要求显示员工姓名、部门名称、工资等级
SELECT emp.ename,dept.dname,emp.sal,salgrade.grade FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal ORDER BY emp.sal DESC;

– (where后)找出薪水比公司平均薪水高的员工，要求显示员工名和薪水
SELECT ename,sal FROM emp WHERE sal>(SELECT avg(sal) FROM emp);

– （ from 后）找出每个部门的平均薪水，并且要求显示平均薪水的薪水等级
SELECT
e.dname,
avgsal,
salgrade.grade
FROM
(
SELECT
dept.dname,
avg( sal ) avgsal
FROM
emp
JOIN dept ON emp.deptno = dept.deptno
GROUP BY
emp.deptno,
dept.dname
) e
JOIN salgrade ON e.avgsal BETWEEN salgrade.losal
AND salgrade.hisal
ORDER BY
e.avgsal DESC;

– 查询出job为MANAGER和SALESMAN的员工
SELECT ename,job FROM emp WHERE job=‘MANAGER’
UNION
SELECT ename,job FROM emp WHERE job=‘SALESMAN’

select * from emp where job in(‘MANAGER’,‘SALESMAN’);

– 取前5个员工信息
SELECT * FROM emp LIMIT 0,5;

– 找出工资排名在前5的员工
SELECT * FROM emp ORDER BY sal DESC LIMIT 0,5;

– 找出工资掋名在[ 3-9 ]的员工
SELECT * FROM emp ORDER BY sal DESC LIMIT 2,7;

– 创建表格<t_student 学生信息表>
CREATE table t_student(
NO int(10),
NAME VARCHAR(32),
– 创建表格时给字段设置默认值:default 默认值
sex char(2) DEFAULT ‘男’,
birth date
);

SELECT * FROM t_student;
– 删除表格：
DROP TABLE t_student;

– 向 t_student 表格中插入数据
INSERT INTO t_student (no,NAME) VALUES(2,‘发货’);

– 表的复制
CREATE TABLE t_studenttwo as SELECT * FROM t_student;

– 将查询结果插入到某张表中
INSERT INTO t_student SELECT ename FROM emp WHERE sal=5000;

– 1 ） 新增：ALTER TABLE 表名 ADD 字段名 字段类型（长度）；
ALTER TABLE t_student ADD age int(10);

– 2 ） 修改：ALTER TABLE 表名 MODIFY 字段名 字段类型（长度）；
ALTER TABLE t_student MODIFY age int(20);

– 3 ） 删除：ALTER TABLE 表名 DROP 字段名;
ALTER TABLE t_student DROP age ;

DROP TABLE IF EXISTS emps;
CREATE TABLE emps(
eno int(10) PRIMARY KEY ,
ename VARCHAR(32) NOT NULL unique,
dno int(10) unique
);
INSERT into emps VALUES(1,‘zzy’,10);
INSERT into emps VALUES(2,‘wq’,20);
INSERT into emps VALUES(3,‘tmt’,30);

DROP TABLE IF EXISTS depts;
CREATE TABLE depts(
dno int(10) PRIMARY KEY ,
dname VARCHAR(32) NOT NULL unique
);
INSERT into depts VALUES(10,‘IG’);
INSERT into depts VALUES(20,‘RNG’);
INSERT into depts VALUES(30,‘EDG’);

– 创建t_student表和t_class表
DROP TABLE IF EXISTS t_class;
create table t_class (
cno int (10) PRIMARY key,
cname varchar(32)
);

DROP TABLE IF EXISTS t_student;
create table t_student (
sno int (10) PRIMARY key,
sname varchar(32),
classno int(10),
CONSTRAINT t_student_classno_fk FOREIGN key(classno) REFERENCES t_class(cno)
);