子查询: 有的结果需要多次查询,一个查询结果是另一个查询的条件,这个条件有可能是一个数据,也有可能是单个字段,或者一张表 字段: all:将字段内的值,取出来进行比较,并用and进行连接 any/some:将字段内的值,取出来进行比较,并用or进行连接 单张表:查询的结果必须要取别名,不存在的字段也需要取别名 多表查询:需要查询的数据来自于多张表,需要找到表的连接关系 内连接:只会根据连接的条件,显示匹配的数据 inner join:也是内连接的一种,效率会比较高,只会根据连接的条件,显示匹配的数据 左连接:left join 左表为主,显示左表的所有数据,按匹配条件显示数据,右表没有数据显示null 右连接:right join 右表为主,显示右表的所有数据,按匹配条件显示数据,左表没有数据显示null 全连接:union 左连接 union 右连接 不等值连接:表与表之间的练习,连接的值不相等 >= <= != < > between and 例子:求每个月的总金额 先定义一张月份表 再定义一张总额表 SELECT * FROM mm as m1,mm as m2 WHERE m1.month_1<=m2.month_1(表的连接关系) SELECT m2.month_1,sum(m1.money) FROM mm as m1,mm as m2 WHERE m1.month_1<=m2.month_1 GROUP BY m2.month_1 例子:查询每个学生的成绩等级 SELECT * FROM `level` SELECT * FROM student SELECT * FROM student,`level` WHERE score BETWEEN low AND high 练习: 1.查询每个年级的平均分 SELECT grade,avg(score) from student,class where student.cid=class.cid GROUP BY grade SELECT grade,avg(score) FROM student INNER JOIN class ON student.cid=class.cid GROUP BY grade 2.查询一年级男生和女生数量 SELECT sex,count(*) from student,class where student.cid=class.cid and grade='一年级' group by sex SELECT sex,count(*) FROM student INNER JOIN class ON student.cid=class.cid WHERE grade='一年级' group by sex 3.查询每个年级及格的人数 select grade,count(*) from student,class where student.cid=class.cid and score>=60 GROUP BY grade 4.查询没有进行分类的商品 SELECT * from goods LEFT JOIN type on goods.tid=type.tid where type.tid is null SELECT * FROM goods SELECT * FROM type 5.查询空的仓库 SELECT * from type LEFT JOIN goods on goods.tid=type.tid where goods.tid is null 6.将1和2的查询内容用union连接起来 SELECT gname from goods LEFT JOIN type on goods.tid=type.tid where type.tid is null UNION SELECT storeroom from goods right JOIN type on goods.tid=type.tid where goods.tid is null 1到3题student 和class。 4到6用goods 表和type表 SHOW TABLES SELECT * FROM `level` SELECT * FROM student SELECT * FROM student,`level` WHERE score BETWEEN low AND high 求每个存款总额 SELECT * FROM mm as m1,mm as m2 WHERE m1.month_1<=m2.month_1 SELECT m2.month_1,sum(m1.money) FROM mm as m1,mm as m2 WHERE m1.month_1<=m2.month_1 GROUP BY m2.month_1 SELECT * FROM mm SELECT * FROM mm as m1,mm as m2 WHERE m1.month_1<=m2.month_1 #存储过程:执行一组特定功能的sql语句 delimiter // #定义结束符 CREATE PROCEDURE p1() #COMMENT '什么作用' #创建存储过程 BEGIN #开始 SELECT 'hello world'; SELECT * FROM dep; END // #结束 delimiter; #恢复结束符 SHOW PROCEDURE STATUS #显示所有存储过程 drop PROCEDURE test1 #删除存储过程 call p1() # 参数与变量 delimiter // #定义结束符 CREATE PROCEDURE p2(num1 INT,num2 CHAR(10)) #创建存储过程,并声明要传两个参数 #COMMENT '什么作用' BEGIN #开始 SELECT 'hello world'; SELECT * FROM dep WHERE id=num1 OR name=num2; END // #结束 delimiter; #恢复结束符 CALL p2(1,'李响') #形式参数:创建存储过程时,添加的参数,可以被sql语句使用 #实际参数:调用存储过程时,输入的实际参数,实际参数必须要按照形式参数格式输入,个数与形式参数的类型必须要对应 #变量 # 变量需要通过声明的形式创建,提供给sql语句使用 #DECLARE 变量名 数据类型(长度)。。。 delimiter // CREATE PROCEDURE p3() COMMENT '变量' #创建存储过程 BEGIN #开始 DECLARE num1,num2 INT DEFAULT 1; #添加默认值 SELECT num1,num2; END // #结束 delimiter; CALL p3() #修改标量的值 # SET 修改变量值 delimiter // CREATE PROCEDURE p4() COMMENT '变量' #创建存储过程 BEGIN #开始 DECLARE num1,num2 INT DEFAULT 1; #添加默认值 SET num1=10 ,num2=5; #修改变量的值 SELECT num1,num2; SELECT num1+num2; END // #结束 delimiter; CALL p4() #将查询出来的值赋值给变量 SELECT * FROM dep delimiter // CREATE PROCEDURE p5() #创建存储过程 BEGIN #开始 DECLARE num1 INT; #声明变量nul DECLARE str1 CHAR(10); #声明变量str1 SELECT id,name INTO num1,str1 FROM dep WHERE id=1; SELECT num1,str1; END // #结束 delimiter; CALL p5() SELECT * FROM dep #判断:根据调用存储过程时输入的参数,调用存储过程中的sql语句 delimiter // CREATE PROCEDURE p6(num1 int) #定义一个参数 BEGIN if num1<=5 THEN #进入判断,输入参数小于等于5时,执行下面的sql语句 SELECT * from dep where id BETWEEN 1 AND 5; ELSEIF num1>6 THEN SELECT * from dep where id >6; ELSE SELECT * from dep where id =6; END IF; #声明if判断判断结束 end // #声明存储过程结束 delimiter ; CALL p6(8) #CASE 判断:判断的时值 delimiter // CREATE PROCEDURE p7(num1 INT) BEGIN CASE num1 when 1 THEN #判断的是输入的实际参数时1 SELECT * FROM dep WHERE id=1; when 2 THEN SELECT * FROM dep WHERE id=2; ELSE SELECT * FROM dep; END CASE; #声明case判断结束 END // delimiter ; CALL p7(7) DROP PROCEDURE p7 #循环 #WHILE循环:当满足循环条件的时候,执行循环 delimiter // CREATE PROCEDURE p8() BEGIN DECLARE num1 int DEFAULT 17; WHILE num1 <=25 DO INSERT INTO dep(id,name) VALUES(num1,'while循环'); SET num1=num1+1; end WHILE; END // delimiter; CALL p8() SELECT * FROM dep DESC dep #repeat 循环:当达到条件的时候结束循环 delimiter // CREATE PROCEDURE p9() BEGIN DECLARE num1 int DEFAULT 27; REPEAT #声明是repeat循环 INSERT INTO dep(id,name) VALUES(num1,'repeat循环'); SET num1=num1+1; UNTIL num1>=35 END REPEAT; END // delimiter; CALL p9() #LOOP 循环:先要给循环设置一个循环名,当达到条件的时候结束循环名 delimiter // CREATE PROCEDURE p10() BEGIN DECLARE num1 int DEFAULT 35; loopxh:LOOP #定义一个循环名,声明是loop循环 INSERT INTO dep(id,name) VALUES(num1,'loop循环'); SET num1 =num1+1; IF num1 >45 THEN LEAVE LOOPxh; #离开循环名,离开循环 END IF; END LOOP; END // delimiter; call p10() select * FROM dep #1+2+3+100求和 delimiter // CREATE PROCEDURE p11() BEGIN DECLARE a INT DEFAULT 1; #定义一个多少开始的变量 DECLARE b INT DEFAULT 0; #定义一个求和的变量 WHILE a<=100 DO SET b=b+a; SET a=a+1; END WHILE; SELECT b; SELECT a; END // delimiter; CALL p11() #索引:就是添加在字段上的一个规则(了解:INDEX) #主键是唯一索引,也是一个特殊的索引 #一张表内最多只能添加16个索引 #优点:可以增加我们的查询效率 #缺点:会降低增删改的效率,额外占用磁盘空间 SELECT * FROM dep #创建索引:create index 索引名 on 表名(字段) CREATE INDEX index_name on dep(name) #联合索引:create index 索引名 on 表名(字段1,字段2.。) CREATE INDEX index_name on dep(name,job) #显示表中的索引:show index from 表名 show INDEX FROM dep #删除索引:drop index 索引名 on 表名 DROP INDEX index_name ON dep SELECT * FROM dep where name='李梅梅' or name='张越' 慢查询日志: 慢查询日志 1,可以查询运行程序的时候,一些耗时较长,查询比较慢的sql语句 show VARIABLES like 'slow_query_log' set GLOBAL slow_query_log=ON show VARIABLES like 'long_query_time' set GLOBAL long_query_time=1; 查询慢查询日志是否开启 show VARIABLES like 'slow_query_log' on/off状态 2.设置慢查询日志开启 set GLOBAL slow_query_log=ON 3,慢查询日志存放的目录 show VARIABLES like '%query_log%' 4,慢查询日志时间设置 show VARIABLES like 'long_query_time' 5,设置慢查询日志时间(设置完成后需要重新开启的我们的命令行) set GLOBAL long_query_time=1; SELECT SLEEP(2) #视图:个人认为就是一张临时表,视图中的数据会随着原表中的数据变化 CREATE VIEW view_dep as SELECT *FROM dep #显示所有试图 show TABLES SELECT * from view_dep WHERE id =1 SELECT * FROM view_dep,dep WHERE view_dep.id=dep.id CREATE VIEW view_dep1 as SELECT * FROM dep WHERE job='经理' SELECT * FROM view_dep1 UPDATE dep set salary=8000 WHERE id =8 #删除视图 drop VIEW view_dep1