数据库笔记

sql语言分类

多表查询需要联结，查n张表至少联结n-1个条件，表与表之间必须要有关系（主表从表/主键外键）才能联结
having后可跟分组函数
where 不可以直接跟，where后使用分组函数必须要用子查询
加了分组函数默认在group by之后执行

where

模糊查询

事务隔离级别

- 查询student表的所有记录
SELECT * FROM student;
-- 4.查询student表的第2条到4条记录
SELECT * FROM student LIMIT 1,3;
 5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
 SELECT id,name,department FROM student;
 6.从student表中查询计算机系和英语系的学生的信息
 SELECT * FROM student WHERE department = '计算机系' OR department = '英语系';
 SELECT * from student WHERE department IN ('计算机系','英语系');
 7.从student表中查询年龄1985~1990年份的学生信息
 SELECT * FROM student where birth BETWEEN 1985 AND 1990;
 SELECT * FROM student where birth >=1985 AND birth <=1990;
 8.从student表中查询每个院系有多少人
 SELECT COUNT(department) FROM student GROUP BY department;
 SELECT department,count(*) FROM student GROUP BY department;

-- 9.从score表中查询每个科目的最高分

-- SELECT c_name,max(greade) FROM score GROUP BY c_name;

-- 10.查询李四的考试科目（c_name）和考试成绩（grade）

-- SELECT c_name,greade,name FROM score AS c,student AS s
-- WHERE s.id = c.stu_id AND s.`name` = '李四';

-- 11.用连接的方式查询所有学生的信息和考试信息

-- SELECT * FROM score AS c LEFT JOIN student AS s ON c.stu_id  = s.id;

-- 12.计算每个学生的总成绩

-- SELECT name,sum(greade) 
-- FROM score AS s right JOIN student AS t 
-- ON s.stu_id = t.id 
-- GROUP BY stu_id;

13.计算每个考试科目的平均成绩

 SELECT c_name,SUM(greade)/COUNT(s.id) 
 FROM score AS c,student AS s 
 WHERE c.stu_id = s.id
 GROUP BY c_name;
 SELECT COUNT(s.id)
 FROM score as c,student AS s
 WHERE c.stu_id = s.id
 GROUP BY c_name;

-- 14.查询计算机成绩低于95的学生信息

-- SELECT student.* 
-- FROM student,score 
-- WHERE student.id = score.stu_id
-- AND score.greade<95 AND c_name = '计算机';

-- 15.将计算机考试成绩按从高到低进行排序

-- SELECT greade 
-- FROM score 
-- WHERE c_name = '计算机' 
-- ORDER BY greade DESC;

-- 16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

-- SELECT name,department,c_name,greade
-- FROM student AS s,score AS c
-- WHERE s.id = c.stu_id 
-- AND (name LIKE '张%' OR name LIKE '王%' ); 

-- 17.查询都是北京的学生的姓名、年龄、院系和考试科目及成绩

-- SELECT name,YEAR(birth)-YEAR(NOW()),department,c_name,greade
-- FROM student AS s,score AS c
-- where s.id = c.stu_id AND (s.address LIKE '北京%');



-- SELECT DISTINCT stu_id FROM score;
-- SELECT student.*,greade from score,student WHERE score.stu_id = student.id HAVING MAX(greade);
-- SELECT * FROM emp;
-- SELECT * FROM dept;

-- 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
-- SELECT d.*,COUNT(e.empno)'部门人数'
-- FROM emp AS e,dept AS d
-- WHERE e.deptno = d.deptno
-- GROUP BY d.deptno;

-- 2. 列出所有员工的姓名及其直接上级的姓名。
-- SELECT e1.ename'员工姓名',e2.ename'上级姓名'
-- FROM emp AS e1 LEFT JOIN emp AS e2
-- ON e1.mgr = e2.empno;

-- 3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
-- SELECT e1.empno,e1.ename,d.dname
-- FROM emp AS e1 LEFT JOIN emp AS e2 ON e1.mgr = e2.empno,dept AS d
-- WHERE e1.deptno = d.deptno 
-- AND e1.hiredate < e2.hiredate;
-- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
-- SELECT d.dname,e.* 
-- FROM dept AS d LEFT JOIN emp AS e
-- ON d.deptno = e.deptno;
-- 5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
-- SELECT job,count(job)'员工人数'
-- FROM emp
-- GROUP BY job
-- HAVING MIN(sal)>15000;

-- 6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
 SELECT e.ename
 FROM emp AS e,dept AS d
 WHERE e.deptno = d.deptno
 AND e.deptno = (SELECT deptno FROM dept WHERE dname = '销售部');
 
-- 7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
 SELECT e1.*,d.dname,e2.ename,s.GRADE
 FROM dept AS d,emp AS e1,emp AS e2,salgrade AS s
 WHERE d.deptno = e1.deptno 
 AND e1.mgr = e2.empno
 AND e1.sal BETWEEN s.LOwSAL AND s.HISAL
 AND e1.sal>(SELECT AVG(sal) FROM emp);
 8.列出与庞统从事相同工作的所有员工及部门名称。
SELECT e.ename,e.job,d.dname
 FROM emp AS e,dept AS d
 WHERE e.deptno = d.deptno
 AND e.job = (SELECT job from emp WHERE ename = '庞统');
 9.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称。
SELECT
	e.sal,
	e.ename,
	d.dname 
 FROM
	emp AS e,
	dept AS d 
 WHERE
 	e.deptno = d.deptno 
	AND e.sal >(
	SELECT
 		MAX( sal ) 
	FROM
-- 		emp AS e 
-- 	WHERE
-- 		deptno = 30 
-- 	);
10.查出年份、利润、年度增长比。
 SELECT 
p1.*,(p1.zz-p2.zz)/p2.zz
 FROM
 profit AS p1 LEFT JOIN profit p2
ON p1.year = p2.year+1

分组函数不能直接使用在where后面
分完组后如果想对数据进行进一步过滤可以使用having 子句
having不能单独使用，不能代替where

每个部门最高薪资超过3000的

SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal)>3000

缺点：效率较低，更好的思路是先找出薪资在3000以上的信息，然后再根据部门编号分组

SELECT deptno,sal
FROM emp
WHERE sal>3000
GROUP BY deptno

去重

distinct 后可以跟多个字段，意思是联合去重

SELECT DISTINCT job,deptno FROM emp

子查询

SELECT * FROM emp
WHERE sal>(SELECT MIN(sal) FROM emp)

子查询先执行

SELECT s.GRADE,job
FROM (SELECT job,AVG(sal) a FROM emp GROUP BY job) e,salgrade s
WHERE e.a BETWEEN s.LOSAL AND s.HISAL

需要的数据从临时表中来

约束

保证表中的数据有效

source+文件路径 批量执行sql文件

主键约束

primary key

唯一且不能为空，是每行记录的唯一标识,任何一张表都应该有主键，没有主键这张表无效

复合主键

主键建议使用：

自然主键和业务主键

业务主键：有自己特殊意义，可能会随着功能发生变动的主键

外键

可以让两张表产生关联，被引用的表是父表，引用的表是子表

关系（通常）外键不一定必须是主键：

1对1 任意一张表的主键充当另一张表的外键 （外键唯一）
1对多 1的一方的主键充当另一张表的外键，1对多，两张表，多的表加外键。
多对多 可以使用中间（关系表）表外键通常是这两张表的主键
（eg:一个老师有多个学生，一个学生有多个老师）
只有一对一要求外键唯一

存储引擎

指定数据存储方式

myisam

myisam不支持事务机制

innodb

memory

内存永远比硬盘快

索引

select查询是根据条件字段的每个值从前向后扫描，索引相当于一本书的目录，一眼扫过去立刻得到自己要的数据，缩小了扫描的范围。效率更高。

注意：
1.主键和unique约束的字段都会自动创建索引
2.数据库表中的每条记录在硬盘上都有一个十六进制的物理编号（eg:0x99）
3.无论是什么方式的存储引擎，索引都以树的形式存在（遵循左大右小，中序遍历）

执行原理：

索引按照左大右小的方式将数据按照二叉树形式排列。查询数据时从头节点开始比较（根），如果查询条件比根节点小去左子树。反之去右子树，找到物理编号（记录）。提高了查询效率

查找需要比较，所以主键提倡用定长（可比较）数据

创建和删除

查看是否有索引

EXPLAIN SELECT * from emp WHERE ename='smith'

结果：row:14 type:all
是全表扫描，并没有使用索引

创建索引后

CREATE INDEX a on emp(ENAME)

结果： type:ref row：1

索引失效

模糊查询时查询条件以 % 开始，只能全表查询

左联 右连 外连 内连

数据库设计

1.每个表必须有主键。每个字段必须遵循原子性不可再分（每个字段分到不能再分割，是一个单独整体，比如一个字段下面尽量不要既有邮箱又有手机号，分开列字段）
2.所有非主键字段必须完全依赖主键，不要产生部分依赖（针对多对多关系来说）
3.在第二范式的基础上，非主键字段不要产生传递依赖

与实体类的关系

多对1

以员工和部门为例
一个员工对应一个部门：员工类中应当包含一个部门对象（员工表可以通过索引查询到一个具体的部门）
一个部门对应多个员工：部门类中应当包含一个员工集合
对1 对应的是一个对象
对多 对应的是一个集合