mysql - 其他
-
索引
当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍- 创建索引的原则:经常作为条件的列上适合创建索引
- 不适合创建索引:经常改动的列;数据比较少
- 使用索引:dbms决定是否使用索引
- 系统会为primary key和unique自动创建索引 primary key
- 添加索引
ALTER TABLE student INDEX index_name(sname) create index index_name on student(sname) create unique index index_name on student(sname) #唯一索引不能随便用 create fulltext indext full_sdept on student(sdept)
- 删除索引
alter table student drop index index_name alter table student drop PRIMARY KEY
- 查看设计SQL语句
show CREATE TABLE student
-
视图:虚表
SQL支持关系数据库三级模式结构外模式:视图;模式:基本表;内模式:存储文件
使用视图的理由
- 对视图内容的更新直接影响基本表。
- 当视图来自多个基本表、不允许删除、增加数据。
建立学生成绩视图
CREATE VIEW view_stugrade AS SELECT student.sno,student.sname,course.cname,sc.grade FROM student INNER JOIN sc ON student.sno=sc.sno INNER JOIN course ON course.cno=sc.cno;
查询李丽的C语言成绩
SELECT student.sno,student.sname,course.cname,sc.grade from student INNER JOIN sc on student.sno=sc.sno INNER JOIN course on course.cno=sc.cno where student.sname='李丽' and course.cname='c++'
在视图中查找李丽的C语言成绩
SELECT * FROM view_stugrade WHERE sname='李丽' AND cname ='c';
把李丽的C语言成绩改成80
UPDATE view_stugrade SET grade=80 WHERE sname='李丽' AND cname ='c'; UPDATE sc set grade=90 WHERE id=6;
查询和李丽的C语言成绩相同的学生信息
SELECT * FROM view_stugrade WHERE grade=(SELECT grade from view_stugrade WHERE sname='李丽' AND cname='c') AND sname NOT LIKE '李丽';
查询成绩比李丽最低分要高的学生信息-1
SELECT * FROM view_stugrade WHERE grade>(SELECT min(grade) FROM view_stugrade WHERE sname='李丽')
查看所有表和视图
SHOW TABLES;
删除视图
DROP VIEW view_stugrade;
-
函数
1、必须有返回值2、返回值指定类型 RETURNS
3、返回值通过 return
SET GLOBAL log_bin_trust_function_creators = 1; delimiter && CREATE FUNCTION getAddressByName(myname VARCHAR(12)) RETURNS VARCHAR(12) BEGIN return(select sprovince from student where sname=myname); END && SELECT getAddressByName('李大奎')
-
存储过程
delimiter && CREATE PROCEDURE getAddressByNamePro(in myname VARCHAR(12),out myprovince VARCHAR(12)) BEGIN select sprovince into myprovince from student where sname=myname; end && set @name='李大奎'; set @add=''; call getAddressByNamePro(@name,@add); SELECT @add;
-
定义变量
delimiter && CREATE PROCEDURE getScoreLevel(in myname VARCHAR(12),INOUT cname_res VARCHAR(12)) BEGIN DECLARE score int DEFAULT(-1); DECLARE sid VARCHAR(12) DEFAULT(''); DECLARE cid VARCHAR(12) DEFAULT(''); SELECT sno INTO sid from student where sname=myname; SELECT cno INTO cid from course where cname=cname_res; SELECT grade INTO score from sc where cno=cid and sno=sid; if score<60 and score>=0 THEN set cname_res='不及格'; ELSEIF score>=60 and score<80 THEN set cname_res='及格'; ELSEIF score>=80 and score<100 THEN set cname_res='优秀'; ELSE set cname_res=''; END if; end && set @name='李丽'; set @res='c++'; call getScoreLevel(@name,@res); SELECT @res
delimiter && CREATE PROCEDURE setAllPass(INOUT num int) BEGIN DECLARE min_score int DEFAULT(-1); SELECT min(grade) into min_score from sc; if min_score>=60 THEN SELECT '没有人不及格'; ELSE WHILE min_score<60 DO UPDATE sc set grade=grade+num; SELECT min(grade) into min_score from sc; end WHILE; update sc set grade=100 where grade>100; end if; SELECT '更新成功'; end && set @num=4; call setAllPass(@num);
-
事物
CREATE PROCEDURE pro_delstudent(in strname VARCHAR(12)) BEGIN DECLARE num VARCHAR(12) DEFAULT ''; SELECT sno INTO num FROM student WHERE sname=strname; DELETE FROM sc WHERE sno=num; DELETE FROM student WHERE sno=num; SELECT '删除成功'; END select * from user; start TRANSACTION; UPDATE user set money=money+1000 WHERE id=1; UPDATE user set money=money-1000 where id=2; ROLLBACK; COMMIT;