SQL-1

MySQL SQL
MySQL
发布日期:   2021-10-28
文章字数:   2.9k
阅读时长:   11 分
阅读次数:  

SQL 基础

主要关键字的执行顺序：

from
on
join
where
group by
having
select
distinct
union
order by

基本命令

create table if not exists student(studentno char(11) not null auto_increment,primary key (studentno));
show create table student;
alter table student add address varchar(30) not null after studentno;
alter table student rename to student2;
alter table student modify studentname varchar;
alter table student drop address;
alter table student add constraint fname foreign key(studentno) references student(studentno);
delete * from student where 1=1;
truncate table;
drop table if exists student;
select studentno,sum(daily*0.3+final*0.7) as '总分' from score where final>-75 group by studentno having cont(*)>=3;
select studentno,sum(final) 总分,avg(final) 平均分 from score group by studentno having sum(final)>270;
select courseno,avg(final) from score group by courseno with rollup;#查询所有课的平均值，和所有成绩的平均值

多表连接

Inner Join:结果只包含满足条件的列。
Left Outer Join:结果包含满足条件，左侧表全部行。
Right Outer Join:结果包含满足条件，右侧全部行。
Cross Join:结果包含两个表所有行。
select s.Name,C.Cname from student_course as sc left join student as s on s.Sno=sc.Sno left join course as c on c.Cno=sc.Cno
其中outer join 的执行过程分为4步
1、先对两个表执行交叉连接(笛卡尔积)
2、应用on筛选器
3、添加外部行
4、应用where筛选器

union

合并多个结果集，将多个select语句的返回结果组合到一个结果集中。
所有查询中的列数和列的顺序必须相同且数据类型必须兼容。
select studentno,sname,phone from student01 where phone like ‘%131%’ union select studentno,sname,phone from teaching.student where phone like ‘%132%’;

子查询

子查询就是一个嵌套在select,insert,update,delete语句或其他子查询中的查询。

  1. 利用子查询做表达式：
    此时子查询必须返回一个值或单个列值列表，此时的子查询可以替换where子句中包含in关键字的表达式。
    select studentno,sname,entrance,(select avg(entrance) from student) 平均成绩,entrance -(select avg(entrance) from student) 分差 from student where studentno=’18121212’;

  2. 利用子查询生成派生表
    用于替代from子句中的数据源表
    select studentno,courseno from (select * from score where final>85) where final>90;

  3. where子句中的子查询
    select studentno,courseno,final from score as a where final < (select avg(final) from score where courseno=courseno group by courseno);

  4. 带in关键字的子查询
    select studentno,sname from student where studetno in (select studentno from score where final>99);

正则表达式模糊查询

^匹配文本开始字符 ^b
$匹配文本结束字符 b$
.匹配单个字符 b.b
*匹配零个或多个在它面前的字符 *n
+匹配前面的字符1次或多次 如ba+ b开头后面紧跟至少一个a：bay,baa,bare,battle
<字符串>匹配包含指定的字符串的文本 fa:字符串要包含fa,如fafff
[字符集合]匹配字符集合中任何一个字符

索引

索引是为了加速对数据进行检索而创建的一种分散的，物理的数据结构。利用索引可以快速查询数据库表中的特定记录信息。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引放在索引页面上。
索引一旦创建，由数据库自动管理和维护，比如插入更新记录时，数据库会自动在索引中做出相应修改。
使用索引优点：

  1. 加速数据检索：索引能以一列或多列值为基础实现快速查找
  2. 优化查询；索引能加速连接，排序和分组等。
  3. 强制实施的唯一性：创建唯一索引，可以保证表中的数据不重复。

联合索引：
从左到右使用索引中的字段，一个查询可以只使用索引中的一部分，只能最左侧部分。
比如电话簿，知道姓，很有用。知道姓和名，很有用。只知道名，不起作用。

create index phone_index on student(phone asc)
create index sc_index on score(studentno,courseno);
drop index mark on teacher;

视图

视图作用：

  1. 简化数据查询和处理。视图可以集中多个表数据，简化对数据查询和处理。
  2. 屏蔽数据库的复杂性。不必了解复杂的数据库中的表结构。
  3. 安全性。只让用户访问视图权限，不授予访问表权限，提高数据库安全性。
create view stu_score1 as select student.no,sname from score left join student on student.no = score.no where ...
create view teach_view(教师号,教师名) as select * from teacher;
alter view teach_view as select teacherno,tname from teach where ...

使用视图修改表数据，基表的数据也进行了修改。

MYSQL编程

变量：
局部变量，用户会话变量：@开头，系统会话变量两个@开头。

  1. 会话变量：
    MySQL服务器在内存中为每一个会话开辟独立的会话连接空间，不同的会话空间互不干扰，会话结束，会话空间释放。
    set @name = ‘sss’
    select @name := ‘sss’
    select ‘sss’ into @name
    :=能够产生结果集，into赋值方式仅仅用于定义与赋值，不会产生结果集。
    例：
    set @sname = (select sname from student where studentno = '111');
select studetnno,sname,birthdate from student where sname = @sname;
  2. 局部变量：
    局部变量必须先定义，然后使用set或者select语句赋值。局部变量定义在begin…and语句块之间。并且必须指定局部变量的数据类型。
    局部变量作为存储过程或者函数的参数使用。
    declare myvar int default 100;
    set myvar = 77;
  3. 局部变量与会话变量区别:
    会话变量定义时不需要指定类型，局部变量需要。会话变量不能使用declare定义。
    会话变量作用范围和生存周期大于局部变量。会话变量在本次会话期间一直有效，知道关闭服务器连接。局部变量若作为存储过程或者函数的参数，此时在整个存储过程或函数内有效。

定界符delimiter

delimiter //

预处理

使用步骤：

  1. 创建预处理语句
  2. 执行预处理语句
  3. 释放预处理语句
    set @a=2;
prepare STMT from "select studetnno,sname from student limit?";
execute STMT using @a;

注释

use teaching; –打开数据库
‘#’打开数据库
use teaching;
/打开数据库/

自定义函数

delimiter //
create function my_area(long1 int,wide1 int) returns int
begin return long1 * wide1; end //
delimiter ;
select my_area(5,4);
--查看当前数据库中所有的自定义函数信息
show function status;            --较少
show function status like 模式;  --较多
--查看指定数据库中所有自定义函数名
select name from MySQL.proc where db = 'teaching' and type = 'function';
--详细信息
show create function func_name;
drop function func_name;

控制流语句

  1. if
    if x = 10 then set x = 1;
elseif x = 20 then set x = 2;
else set x = 3;
end if;
  2. case
    case x
when 10 then set x = 1;
when 20 then set x = 2;
else set x = 3;
end case;
  3. while
    while m < n do
set sum = sum + n;
end while;
  4. loop
    loop_label:loop
set sum = sum + k
if k > n then
leave loop_label;
end if;
end loop;
  5. repeat
    repeat
set sum = sum + k;
until p > n
end repeat;

存储过程

利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。
优势：

  1. 提高处理复杂任务的能力。
  2. 增强代码复用性和共享性。
  3. 减少网络中数据的流量。
  4. 存储过程在服务器注册。
  5. 加强了系统的安全性。
    delimiter //
create procedure proc_stu()
reads sql data  --整个过程中所有的SQL语句仅需要读记录，不对记录进行写的操作。
begin
select studentno,sname from student where phone like '%131%';
end//
delimiter ;
call proc_stu();
show procedure status like 'proc_%';
show create procedure proc_stu;
--将读写权限改为modifies sql data,并指明调用者可以执行:
alter procedure do_insert
modifies sql data sql 
security invoker;
    在mysql存储过程出现的同时，用户权限也增加了5种，其中和存储过程有关的权限有三种：
    ALTER ROUTINE 编辑或删除存储过程
    CREATE ROUTINE 建立存储过程
    EXECUTE 运行存储过程

SQL SECURITY INVOKER 只是让其他用户能运行 没有select、 update、delete权限只有CREATE ROUTINE权限的用户 所创建的存储过程
drop procedure if exists do_insert;

存储过程与函数比较

相同：

  1. 存储过程和函数都可以重复使用，减少开发人员工作量。
  2. 存储过程和函数可以增强数据的安全访问控制，可以设定只有某些数据库用户才具有某些存储过程和函数的执行权。

不同：

  1. 函数有且只有一个返回值，且必须指定返回值为字符串，数值两种。
    存储过程返回值任意个，也可以没有。返回值需要使用out,inout参数定义。
  2. 函数可以使用select…into语句为某个变量赋值，但不能使用select语句返回结果集。
    存储过程甚至可以返回多个结果集。
  3. 函数可以直接嵌入到SQL语句或MySQL表达式，还可以扩充标准的SQL语句。
    存储过程一般需要单独调用，使用call。

游标

有时需要访问select结果集中的具体数据行，对结果集中每条记录进行处理。游标就是用于解决此类问题。
游标在MySQL中是一种对select语句结果集进行访问的机制。

--声明游标后select没有执行，内存中不存在结果集
declare teach_cursor cursor
for select teacherno,tname from teacher;
--打开游标，select执行，内存存放结果集
open teach_cursor
--提取数据，需要fetch，只能顺序从前往后读。
fetch_loop:LOOP
fetch teach_cursor into v_no,v_name;
if @dovar = 1 then leave fetch_loop;
else select v_no,v_name;
end if;
end LOOP fetch_loop;
close teach_cursor;

触发器

触发器是一种特殊的存储过程，只要满足一定条件，对数据insert,update,delete，数据库会自动执行触发器中定义的程序语句。
优点：

  1. 触发器自动执行，在表的数据做了任何修改之后立即激活。
  2. 可以强制限制，这些限制比用check约束所定义的更复杂，与check不同的是触发器可以引用其他表中的列。
    --更改course中某门课的课程号时，同时将score表课程号全部更新。
create trigger cno_update after update
on course for each row
begin update score set courseno = new.courseno
where courseno = old.courseno;
end
查看:
show triggers;
select * from information_schema.triggers;

注意事项：

  1. 触发程序如果包含select语句，select语句不能返回结果集。
  2. 同一个表不能创建两个相同触发时间，事件的触发程序。
  3. 批量更新操作是，触发器会导致操作性能降低。
  4. InnoDB支持事务，触发程序和更新操作是在同一个事务中完成。
  5. MySQL触发程序不能对本表使用更新语句例如update，可以使用set代替，否则可能出现错误信息。
  6. 添加触发器后一定要测试。
    drop trigger stu_score;

事件及其应用

MySQL中的事件又称事件调度器，是一种定时任务机制，可以用于定时执行任务。可以精确到每秒执行一个任务。
开启事件调度器：

  1. set @@global.event_scheduler = true;
  2. 配置文件my.ini:加上一行：event_scheduler = 1,重启

查看事件调度器：

show variables like 'event_scheduler';
--5秒后创建一个表test：
create event direct
on schedule at current_timestamp + interval 5 second
do
create table test(timeline timestamp);
--每秒插入一条记录到数据库test中：
create event test_insert
on schedule every 1 second
do insert into test values(current_timestamp);

查看事件：
show events;
show events\G

修改：
alter event test_insert disable;

MySQL SQL
  目录