加密函数
SELECT USER() FROM DUAL;--用户@ip地址
SELECT DATABASE();--查看当前数据库名称
SELECT MD5('hsp') FROM DUAL;--为字符串算出32字符串，常用加密
SELECT LENGTH(MD5('hsp')) FROM DUAL;
CREATE TABLE hsp_user(
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user VALUES (100,'韩顺平',MD5('hsp'));
SELECT * FROM hsp_user;
SELECT PASSWORD('hsp') FROM DUAL;--加密函数，用户密码就是password加密
SELECT * FROM mysql.user;
过程函数
SELECT IF(TRUE,'北京','上海') FROM DUAL;--如果expr1是真，则返回expr2，否则expr3
SELECT IFNULL(NULL,'韩顺平机械') FROM DUAL;--如果expr1是null，则返回expr2
statement_list
SELECT CASE
WHEN TRUE THEN 'jack' WHEN FALSE THEN 'tom' ELSE 'marry' END;
如果expr1是真就执行expr2，expr3是真则执行expr4否则执行marry
SELECT ename,IF(comm IS NULL,0.0,comm) FROM emp;查询emp表如果com是null，则显示0.0
判断是否为null用 is null，判断不为空使用is not
SELECT ename,IFNULL(comm,0.0) from emp;
SELECT ename,(SELECT CASE when job='CLERK' THEN '职员'
WHEN job='MANAGER' THEN '经理'
WHEN job='SALESMAN' then '销售人员'
ELSE job END) FROM emp;将英文队列改为中文名
语句简单使用
SELECT * from emp WHERE hiredate<='1992-01-01';查询1990.1.1后入职的员工
SELECT ename,sal FROM emp WHERE ename LIKE 'S%';查找首字母是s的
%表示多个任意字符 -表示单个任意字符
SELECT ename,sal FROM emp WHERE ename LIKE '__O%';查找第三个字母是大写o的员工
SELECT *FROM emp WHERE mgr IS NULL ;显示没有上级的员工
SELECT *FROM emp ORDER BY deptno ASC,sal DESC;
SELECT * FROM emp ORDER BY sal;默认工资从低到高
SELECT * FROM emp ORDER BY deptno ASC,sal DESC;按照部门号升序工资降序
分页查询
分页查询
按雇员的id号升序取出，每页显示三条记录分别显示第一二三页
SELECT *FROM emp ORDER BY empno LIMIT 0,3;第一页
SELECT *FROM emp ORDER BY empno LIMIT 3,3;第二页
SELECT *FROM emp ORDER BY empno LIMIT 6,3;第三页
显示每种岗位的雇员总数，平均工资
SELECT COUNT(*) ,AVG(sal),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 COUNT(DISTINCT mgr) FROM emp;去重
SELECT MAX(sal) -MIN(sal) FROM emp;求最大薪水的差额
多表查询
请统计各个部门group by的平均工资avg
并且是大于1000的having，并且按照平均工资从高到低排序，取出前二行记录
SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal>1000 ORDER BY avg_sal DESC LIMIT 0,2 分组，附加条件，排序，分页
SELECT *FROM emp,dept查询二个表，会造成笛卡尔集问题，所以必须筛选
SELECT *FROM dept;
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;通过限制条件防止笛卡尔集
SELECT *FROM emp;
显示部门号为10的部门名，员工名和工资
SELECT ename,sal,dname,emp.deptno FROM emp,dept
WHERE emp.deptno=dept.deptno AND emp.deptno=10
显示各个员工的姓名，工资，及工资的级别
SELECT ename,sal,grade FROM emp,salgrade WHERE sal BETWEEN losal AND hisal;
自连接
子查询
显示与smith同一部门的所有员工
SELECT* FROM emp WHERE deptno=( SELECT deptno FROM emp
WHERE ename='SMITH')
如何查询和部门10的工作相同的雇员的名字，岗位，工资，工资，部门号，但是不包括
10部门自己的雇员
第一步查询10号部门有哪些工作,把查询结果当做子查询使用
SELECT DISTINCT job FROM emp WHERE deptno=10;
SELECT ename,job,sal,deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10) AND deptno <>10;
子查询
-- 多表查询的 自连接
-- 思考题: 显示公司员工名字和他的上级的名字
-- 老韩分析： 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
-- 这里老师小结：
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名
SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
SELECT * FROM emp;
查找
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods;
要查询ECShop中类别价格最高的商品
SELECT cat_id,MAX(shop_price) from ecs_goods GROUP BY cat_id找出同种类然后选出最高价
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price FROM(
SELECT cat_id,MAX(shop_price) AS max_price FROM ecs_goods GROUP BY cat_id)temp,ecs_goods WHERE temp.cat_id=ecs_goods.cat_id
AND temp.max_price=ecs_goods.shop_price
all与any
SELECT ename,sal,deptno from emp WHERE sal>ALL(SELECT sal FROM emp
WHERE deptno=30) 显示工资比部门30的所有员工工资都高员工的姓名
也可写为
SELECT ename,sal,deptno FROM emp WHERE sal>(SELECT MAX(sal) FROM emp
WHERE deptno=30)
显示在部门员工的工资，姓名，部门号，比任意一个员工工资低就行
SELECT ename,sal,deptno from emp WHERE sal>ANY(SELECT sal FROM emp
WHERE deptno=30)
也可写为
SELECT ename,sal,deptno FROM emp WHERE sal>(SELECT MIN(sal) FROM emp
WHERE deptno=30)
查询特定信息
查询与smith部门和岗位完全相同所有雇员
第一步先得到smith的部门和岗位
SELECT deptno,job FROM emp WHERE ename='SMITH'
SELECT * FROM emp WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='SMITH') AND ename!='SMITH'
查询与宋江数学，英语，语文成绩完全相同的学生
SELECT *FROM student WHERE (math,english,chinese)=(SELECT math,english,chinese
FROM student WHERE `name`='宋江')
数据库练习
查询每个部门工资高于本部门平均工资的人的资料
SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno 先得到每个部门的部门号和对应的平均工资
SELECT ename,sal,temp.avg_sal,emp.deptno FROM emp,(SELECT deptno,AVG(sal) AS
avg_sal FROM emp GROUP BY deptno) temp WHERE emp.deptno=temp.deptno AND emp.sal>temp.avg_sal
查询每个部门工资最高的人的详细资料
SELECT ename,sal,temp.max_sal,emp.deptno FROM emp,(SELECT deptno,MAX(sal) AS
max_sal FROM emp GROUP BY deptno) temp WHERE emp.deptno=temp.deptno AND emp.sal=temp.max_sal
查询每个部门的信息包括部门号，编号，地址和人员数量，
1.部门号编号地址都来自dept
2.各个部门的人员数量构建一个临时表
SELECT COUNT(*),deptno FROM emp GROUP BY deptno;
SELECT dname,dept.deptno,loc,temp.per_num AS '人数' FROM dept,(SELECT COUNT(*)
AS per_num,deptno FROM emp GROUP BY deptno) temp WHERE temp.deptno=dept.deptno
1还有其他写法，表。*表示将该表所有列都显示出来
SELECT dname,temp.* ,loc FROM dept,(SELECT COUNT(*)
AS per_num,deptno FROM emp GROUP BY deptno) temp WHERE temp.deptno=dept.deptno
表的复制
--表的复制
CREATE TABLE my_tab01( id INT ,`name` VARCHAR(32),sal DOUBLE, job VARCHAR(32),deptno INT);
DESC my_tab01
SELECT *from my_tab01;
INSERT INTO my_tab01 (id,`name`,sal,job,deptno) SELECT empno,ename,sal,job,deptno FROM emp;先将emp表中的内容复制到my_tab01
INSERT INTO my_tab01 SELECT *FROM my_tab01;将my——tab01复制
SELECT COUNT(*) FROM my_tab01;
CREATE TABLE my_tab02 LIKE emp;演示删除表的重复记录
DESC my_tab02;
INSERT INTO my_tab02 SELECT *FROM emp;
SELECT * FROM my_tab02;考虑去重记录
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp SELECT DISTINCT *FROM my_tab02;
DELETE FROM my_tab02;
INSERT INTO my_tab02 SELECT * FROM my_tmp;
DROP TABLE my_tmp;
SELECT * FROM my_tab02
合并查询
合并查询
SELECT ename,sal,job FROM emp WHERE sal>2500
SELECT ename,sal,job FROM emp WHERE job='MANAGER'
现在用union all将二个查询结果合并不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER'
现在用union 将二个查询结果合并不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'
外连接
外连接1.左外连接（左侧的表完全显示是左外连接）2.右外连接（右侧的表显示是右连接）
CREATE TABLE stu(id INT ,`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT *FROM stu
CREATE TABLE exam(id int,grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,6)
SELECT *FROM exam;
使用左连接显示所有人成绩没成绩也显示姓名和id号，成绩显示为空
SELECT `name`,stu.id,grade FROM stu,exam WHERE stu.id=exam.id;
现在改为左连接
SELECT `name`,stu.id,grade FROM stu LEFT JOIN exam ON stu.id=exam.id;
现在使用右外连接显示所有成绩如果没有名字匹配显示空
SELECT `name`,stu.id,grade FROM stu RIGHT JOIN exam ON stu.id=exam.id;
列出部门名称和这些部门的员工信息，同时列出没有员工的部门名
使用左外连接
SELECT dname,ename,job FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
现在使用右外连接
SELECT dname,ename,job FROM emp RIGHT JOIN dept ON dept.deptno=emp.deptno
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
