mysql-

mysql - 其他

1. 索引
   当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍

   • 创建索引的原则：经常作为条件的列上适合创建索引
   • 不适合创建索引：经常改动的列；数据比较少
   • 使用索引：dbms决定是否使用索引
   • 系统会为primary key和unique自动创建索引 primary key
   1. 添加索引

   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)
   

   2. 删除索引

   alter table student drop index index_name
   alter table student drop PRIMARY KEY
   

   3. 查看设计SQL语句

   show CREATE TABLE student
   

2. 视图:虚表
   SQL支持关系数据库三级模式结构

    外模式:视图;模式:基本表;内模式:存储文件
   

   使用视图的理由

   1. 对视图内容的更新直接影响基本表。
   2. 当视图来自多个基本表、不允许删除、增加数据。
      建立学生成绩视图

   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;
   

3. 函数
   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('李大奎')
   

4. 存储过程

   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;
   

5. 定义变量

   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);
   

6. 事物

   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;