1:查询tb_door表中的所有记录:
1.1#select 列名 from 表名;SELECT*FROM tb_door;#查询所有列SELECT dname FROM tb_door;#查询时使用字段名代替了*SELECT 一列 二列 FROM emp;#二列显示的内容被一列替换SELECT dname,loc FROM tb_door;#查询多个字段的值时用逗号隔开SELECT empno a FROM emp;#设置别名为a,列名后用空格隔开SELECT ename 匹配 FROM emp;1.2:查询tb_door表中的总记录数
selectcount(*)from tb_door;selectcount(1)from tb_door;SELECTCOUNT(*)FROM emp WHERE job='员工';SELECT1FROMtableWHERE job='员工'LIMIT1;#改用LIMIT 1,让数据库查询时遇到一条就返回,不要再继续查 1.3:条件查询
1.3.1:去重distinct
mysql>SELECTDISTINCT loc FROM dept;#查询部门的地址+-------+| loc |+-------+| 一区 || 一区1|+-------+1.3.2:where链接查询的条件
#select 字段名1 from 表名2 where 字段名=字段值3;#1.查询编号为1的部门信息SELECT*FROM dept WHERE deptno=1;#等于SELECT*FROM dept WHERE deptno>1;#大于SELECT*FROM dept WHERE deptno<2;#小于SELECT*FROM dept WHERE deptno!=1;#不等SELECT*FROM dept WHERE deptno<>1;#表示!=,同上#统计2019年以前入职的员工信息SELECT*FROM emp WHERE hiredate<'2019-1-1';SELECT*FROM emp WHEREYEAR(hiredate)<2019;#2.查询名称为一号的部门编号SELECT deptno FROM dept WHERE dnane='一号';#3.查询部门地址为二区,编号为3的部门名称SELECT dname FROM dept WHERE loc='二区'AND deptno=3#并且关系SELECT dname FROM dept WHERE loc='二区'OR deptno=3#或者关系#3.1查询工资范围在5000~10000内的员工信息SELECT*FROM emp WHERE sal>5000AND sal<10000#(5000,10000)SELECT*FROM emp WHERE sal BETWEEN5000AND10000#[5000,10000]#4.查询部门名称为一号2,一号3的部门地址SELECT loc FROM dept WHERE dname IN('一号2','一号3');SELECT*FROM dept WHERE deptno NOTIN(10,20,30);1.3.3:like模糊查询
通配符-->%:代表0-n个字符
通配符-->_(下划线):代表1个字符#查询名字包含a的员工信息#查询名字包含a的员工信息SELECT*FROM emp WHERE ename LIKE'a%';#以a开头,查询效率最高SELECT*FROM emp WHERE ename LIKE'%a%';#中间包含aSELECT*FROM emp WHERE ename LIKE'%a';#以a结尾SELECT*FROM emp WHERE ename LIKE'a_';#a后面只有一个字符1.3.4:between..and:查询
SELECT*FROM emp WHERE hiredate BETWEEN'2017-1-1'AND'2019-12-31';SELECT*FROM emp WHEREYEAR(hiredate)>=2017ANDYEAR(hiredate)<=2019;1.4:limit:分页,限制数据展示的条目
SELECT*FROM emp LIMIT n;#显示前n条数据SELECT*FROM emp LIMIT n,m;#n-->从第n+1条开始,m-->查询几条数据#展示两条员工信息SELECT*FROM emp LIMIT2#取前几条SELECT*FROM emp LIMIT2,3#从n+1条数据开始展示,总共展示的条数1.5:orderby:排序,默认升序asc,降序DESC--字典顺序SELECT*FROM emp ORDERBY sal ASC#按照sal排序,默认升序SELECT*FROM emp ORDERBY sal DESC#降序SELECT*FROM emp ORDERBY ename #按照字母升序顺序SELECT*FROM emp ORDERBY hiredate #按照数字大小升序排SELECT*FROM emp ORDERBY job;#汉字排序时会查utf8里对应的数字,按照数字升序排序
2.修改tb_door表中id为1的记录
update tb_door set tel=555where id=1;
3.删除tb_door表中id为2的数据
Deletefrom tb_door where id=2;DELETEFROMUSERWHERE id BETWEEN1AND5;#删除表中1到5行的记录DELETEFROMUSERWHERE id DESCLIMIT5;#倒叙排序后删除前五条的记录DELETEFROMUSERWHERE id ASCLIMIT5;#正序排序后删除前五条记录
2.多表联查(重点):
1.笛卡尔积即直集,用逗号隔开多张表
查询过程中,先在内存中构建一个大大的结果集,然后再进行数据的过滤。那这个
构建过程,和所使用的内存资源,包括过滤时的判断,都是既耗费资源,又浪费时间,所以一般都是禁止3张表以上的联查。
SELECT*FROM dept,emp;SELECT*FROM dept,emp
WHERE dept.deptno=emp.deptno;#描述两个表的关系#1.查询部门名称和员工表的所有数据SELECT dname 部门名称,emp.*FROM dept,emp
WHERE dept.deptno=emp.deptno;#2.查询部门的所有和员工的名字,只要部门的名称叫一号SELECT dept.*,emp.ename FROM dept,emp
WHERE dept.deptno=emp.deptno
AND dept.dname='一号';#3.查询所有部门和员工的数据,条件是部门编号>1SELECT*FROM dept,emp
WHERE dept.deptno=emp.deptno #表关系AND emp.deptno>1;#业务条件#4.查询课程表和老师表的所有数据,只要王老师的数据SELECT*FROM courses,teachers
WHERE courses.tno=teachers.tno
AND teachers.tname LIKE'王%';#5.查询计算机导论课程的总分SELECTSUM(degree)FROM scores,courses
WHERE courses.cno=scores.cno
AND courses.cname='计算机导论';-- 2.连接查询,用join连接多张表SELECT*FROM dept JOIN emp;SELECT*FROM dept JOIN emp
ON dept.deptno=emp.deptno;#1.查询部门名称和员工表的所有数据SELECT dname 部门名称,emp.*FROM dept JOIN emp
ON dept.deptno=emp.deptno;#2.查询部门的所有和员工的名字,只要部门的名称叫一号SELECT dept.*,emp.ename FROM dept JOIN emp
ON dept.deptno=emp.deptno
WHERE dept.dname='一号';#3.查询所有部门和员工的数据,条件是部门编号>1SELECT*FROM dept JOIN emp
ON dept.deptno=emp.deptno #表关系WHERE emp.deptno>1;#业务条件#4.查询课程表和老师表的所有数据,只要王老师的数据SELECT*FROM courses,teachers
ON courses.tno=teachers.tno
WHERE teachers.tname LIKE'王%';#5.查询计算机导论课程的总分SELECTSUM(degree)FROM scores,courses
ON courses.cno=scores.cno
WHERE courses.cname='计算机导论';
测试 三种连接的区别????
有两种连接查询:内连接和外连接(左外连接,右外连接)innerjoin:取两张表的交集
leftjoin:左边表的所有和右边满足条件的,不满足的添加nullrightjoin:右边表的所有和左边满足条件的,不满足的添加null
连接查询的效率:小表驱动大表,
把结构简单或者数据量少的表放在前面作为左表
因为左表会查所有数据,右表只查满足了条件的那些数据
-- 2.1.左连接(lift join)#取左边的所有内容和右边满足条件的,不满足的添加nullSELECT*FROM dept LEFTJOIN emp
ON dept.deptno=emp.deptno;-- 2.2.右链接(right join)#取右边表的所有内容和左边表满足条件的,不满足的添加null。SELECT*FROM emp RIGHTJOIN dept
ON dept.deptno=emp.deptno;-- 2.3.内连接(inner join)#取交集,取左右表都满足的条件SELECT*FROM dept INNERJOIN emp
ON dept.deptno=emp.deptno;-- 子查询(subquery): #概念:子查询是指嵌入在其他select语句中的select语句,也叫嵌套查询。#子查询执行效率低慎用。记录少时效率影响不大、图方便直接使用,记录多时最好使用其它方式替代。-- 1.单行子查询用= ;多行子查询:in#子查询:第一次要查的内容,第二次要查的内容-- 第一次查询的结果作为第二次的条件# 1.查询计算机导论课程的总分#第一次查:根据cname查到cno为3-105SELECT cno FROM courses WHERE cname='计算机导论';#第二次查:根据cno查该课程的总分SELECTSUM(degree)FROM scores WHERE cno='3-105';#改为子查询即嵌套查询SELECTSUM(degree)FROM scores WHERE cno=(SELECT cno FROM courses WHERE cname='计算机导论');#2.查询易天老师能讲的课程名称SELECT tno FROM teachers WHERE tname='易天';SELECT cname FROM courses WHERE tno=804;SELECT cname FROM courses WHERE tno=(SELECT tno FROM teachers WHERE tname='易天');#3.查询accounting部门员工的名字SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='accounting');#4.查询在二区办公的员工名字-- SQL错误(1242):Subquery returns more than 1 row;-- 即子查询返回多于1行:这时需要用inSELECT ename FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc='二区');#5.查询高于平均工资的员工信息。SELECT*from emp WHERE sal>(SELECTAVG(sal)FROM emp #平均工资);#总合#1.查询 research部门的所有的员工和姓名-- 1.笛卡尔积SELECT emp.ename,emp.sal FROM dept,emp
where emp.deptno=dept.deptno
and dept.dname='research';-- 2.链接查询SELECT emp.ename,emp.sal FROM emp INNERJOIN dept
on emp.deptno=dept.deptno
where dept.dname='research';-- 3.子查询SELECT emp.ename,emp.sal FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='research');#2:查询李军的平均分-- 1.笛卡尔积SELECTAVG(scores.degree)FROM scores,students
WHERE students.sno=scores.sno
AND students.sname='李军';-- 2.连接查询SELECTAVG(scores.degree)FROM scores join students
ON students.sno=scores.sno
WHERE students.sname='李军';-- 3.子查询:SELECTAVG(scores.degree)FROM scores WHERE sno=(SELECT sno from students WHERE sname='李军');#3:查询陈冰能讲的课程名-- 1.笛卡尔积SELECT courses.cname FROM courses,teachers
WHERE courses.tno= teachers.tno
AND teachers.tname='陈冰';-- 2.连接查询SELECT courses.cname FROM courses join teachers
on courses.tno= teachers.tno
where teachers.tname='陈冰';-- 3.子查询SELECT cname FROM courses WHERE tno =(SELECT tno FROM teachers WHERE tname='陈冰');
表关联 association
定义:关联表代表了表与表之间的关系,它们之间形成了自己的一个小圈子。
表的关系分类:
一对一(one to one):公民与身份证号;
一对多(one to many):最常见,部门和员工;
多对一(mang to one):员工和部门;
多对多(mang to many):老师和学生。
设计方式:表以s结尾去标识复数;字段多以表的首字母为开头,在多联表查询时方便查询出和了解是那个表的字段。
#索引#步骤:1.创建索引 2.使用索引。-- 1.查看索引SHOWINDEXFROM 表名;SHOWINDEXFROM emp;-- 2.创建索引#创建单值索引CREATEINDEX 索引名 ON 表名(字段名);CREATEINDEX ename_index ON emp(ename);#创建唯一索引:一个索引包含一列,值要唯一CREATEUNIQUEINDEX 索引名 ON 表名(字段名);CREATEUNIQUEINDEX dname_index ON dept(dname);#复合索引:#一个索引包含多个字段,用时要遵循最左原则,否则复合索引失效#失效的情况:按照 2 3 23 ,没有包含最左边的,应该为(1 12 13 123)createindex 索引名 on 表名(字段名1,字段名2,字段名3…);CREATEINDEX fuheindex ON emp(ename,job,deptno);EXPLAINSELECT*FROM emp WHERE ename='jack'#生效EXPLAINSELECT*FROM emp WHERE job='总监'#失效EXPLAINSELECT*FROM emp WHERE ename='jack'AND job='副总'#生效EXPLAINSELECT*FROM emp WHERE job='副总'AND ename='jack'#生效-- 3.使用索引,发起select语句SELECT*FROM emp WHERE ename='jack1';-- 4.观察是否使用了索引,检查SQL的执行效率,--使用(explain),查看possible_key的字段的值EXPLAINSELECT*FROM emp WHERE ename='jack1';-- 5.删除索引altertable 表名 dropINDEX 索引名;ALTERTABLE emp DROPINDEX jobindex;