SQL 实验

标签：number t1 score 实验 student SQL where select

建库

create database mysql_test;
use mysql_test;

基础

建表

create table if not exists student_info(
	id int primary key auto_increment,
  number int not null unique,
  name varchar(100) not null
);

create table if not exists student_score(
	id int primary key auto_increment,
  number int not null,
  subject varchar(100) not null,
  score int
);

添加数据

insert into student_info(number,name) values
(1,'Alice'),(2,'Bob'),(3,'Tim');

insert into student_score (number, subject, score) values
(1,'Math',90),(1,'English',80),(1,'C Programming', 70),
(2,'Math',70),(2,'English',70),(2,'C Programing', NULL);

更新数据

select * from student_score;

update student_score set score = 80 where number = 2 and subject = 'Math';
update student_score set subject = 'C Programming' where number = 2 and score is null;

select * from student_score;

查询数据

单表查询

逻辑视角

select * from student_info where number = 2;
select * from student_info where number between 1 and 2;

select * from student_score where number = 1 and subject = 'Math';
select * from student_score where number = 1 or subject = 'Math';

集合视角

# 交集
select * from student_score where number = 1
intersect
select * from student_score where subject = 'Math';

# 并集
select * from student_score where number = 1
union
select * from student_score where subject = 'Math';

分组查询

select subject from student_score group by subject;

统计函数

select subject, count(score), max(score), min(score), avg(score) 
from student_score group by subject
order by avg(score) desc;

多表查询

子查询

# 无关子查询
select subject, score from student_score
where number = (select number from student_info where name = 'Alice');

# 相关子查询
select name, number from student_info where exists (select * from student_score where student_info.number = student_score.number);

连接查询

# 内连接
select * from student_info, student_score where name = 'Alice';

连接分类

内外连接唯一差异：对 on 子句的处理逻辑不通
- 内连接中，不符合 on 子句的记录会被抛弃，所以内连接中 where 语句等效于 on 语句
- 外连接中，驱动表中不符合 on 子句的记录仍会被保留
  
  驱动表：第一次被查询的表
  
  驱动表的查询结果会作为后续被查询的表的条件
  → 驱动表的查询结果数量直接影响被驱动表被查询的次数
  - 左边为驱动表 → 左连接
  - 右边为驱动表 → 右连接

# (内)连接
select * from student_info inner join student_score;
# 左(外)连接：si 表中不符合 si.number = ss.number 的记录被保留
select * from student_info as si left join student_score as ss on si.number = ss.number;
# 右(外)连接：ss 表中不符合 si.number = ss.number 的记录被保留
select * from student_info as si right join student_score as ss on si.number = ss.number;

# 对比样例

# 前情提要：假设 t1 t2 中不存在两条记录，使得 t1.m1 = t2.m2; 表中数据如下所示
select * from t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

select * from t2;
+------+------+
| m2   | n2   |
+------+------+
|    4 | c    |
|    5 | d    |
+------+------+
2 rows in set (0.00 sec)

# 内连接，但凡不符合 on 过滤条件的，都舍弃
select * from t1 inner join t2 on t1.m1 = t2.m2;
Empty set (0.00 sec)

# 外连接，驱动表中不符合 on 过滤条件的，仍然保留
# 左外连接 → 左边作为驱动表，保留左边
select * from t1 left join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
|    3 | c    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

# 外连接，驱动表中不符合 on 过滤条件的，仍然保留
# 右外连接 → 右边作为驱动表，保留右边
select * from t1 right join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
| NULL | NULL |    4 | c    |
| NULL | NULL |    5 | d    |
+------+------+------+------+

数据删除

select * from student_info;
delete from student_info where name = 'Alice';
select * from student_info;

表删除

drop table if exists student_info;
drop table if exists student_score;

进阶

数据准备

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  gender VARCHAR(10),
  grade VARCHAR(10),
  major VARCHAR(50),
  address VARCHAR(100)
);
INSERT INTO students (id, name, age, gender, grade, major, address) VALUES
(1, 'John Doe', 20, 'Male', 'Freshman', 'Computer Science', '123 Main St.'),
(2, 'Jane Smith', 19, 'Female', 'Sophomore', 'Engineering', '456 Elm St.'),
(3, 'Michael Johnson', 21, 'Male', 'Junior', 'Business Management', '789 Oak St.'),
(4, 'Emily Davis', 20, 'Female', 'Freshman', 'Psychology', '987 Maple Ave.'),
(5, 'Daniel Wilson', 22, 'Male', 'Senior', 'Political Science', '654 Pine St.'),
(6, 'Sophia Anderson', 19, 'Female', 'Sophomore', 'Biology', '321 Cedar St.'),
(7, 'David Martinez', 21, 'Male', 'Junior', 'Chemistry', '654 Birch Blvd.'),
(8, 'Olivia Thompson', 20, 'Female', 'Freshman', 'English Literature', '987 Oakwood Dr.'),
(9, 'William Rodriguez', 22, 'Male', 'Senior', 'Mathematics', '789 Elmwood Ave.'),
(10, 'Ava Taylor', 19, 'Female', 'Sophomore', 'Art History', '123 Pinecone Ln.');
CREATE TABLE student_grades (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  subject VARCHAR(50) NOT NULL,
  score INT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO student_grades (id, name, subject, score) VALUES (1, 'John Doe', 'Math', 95), (2, 'Jane Smith', 'English', 88), (3, 'Michael Johnson', 'Science', 75), (4, 'Emily Davis', 'History', 92), (5, 'Daniel Wilson', 'Geography', 82), (6, 'Sophia Anderson', 'Biology', 90), (7, 'David Martinez', 'Chemistry', 78), (8, 'Olivia Thompson', 'Art', 85), (9, 'William Rodriguez', 'Physics', 87), (10, 'Ava Taylor', 'Music', 80);

create table t1 (m1 int, n1 char(1));
insert into t1 (m1, n1) values (1, 'a'), (2, 'b'), (3, 'c');
create table t2 (m2 int, n2 char(1));
insert into t2 (m2, n2) values (4, 'c'), (5, 'd');

视图

定位

视图即别名：可以把视图看作是一个查询语句的别名，创建视图并不会保存结果集（类比 alias）
```
create view 视图名称 as 查询语句
```
视图即虚表：视图也可以称作虚拟表，我们可以像表一样对视图做增删改查操作
```
# 查表能显示视图
show tables;
```

示例

# 创建视图
create view male_students_view as select students.name, major, subject, score from students, student_grades where students.name = student_grades.name and gender = 'Male';

# 删除视图（像表一样）
drop view male_students_view;

存储程序

存储例程

存储函数

# 权限设置
set global log_bin_trust_function_creators = true;

# 将 MySQL 客户端分隔符从 ; 修改为 $，从而保证不会因为输入 ; 而导致客户端提前向服务端发送不完整的函数
delimiter $ 

# 创建函数
CREATE FUNCTION scaled_scores(name VARCHAR(50), subject VARCHAR(50))
RETURNS CHAR(1)
BEGIN
    DECLARE a INT;
    SET a = (
        SELECT score 
        FROM student_grades AS sg
        WHERE sg.name = name AND sg.subject = subject 
        ORDER BY score DESC 
        LIMIT 1
    );
    IF a >= 90 THEN RETURN 'A';
    ELSEIF a >= 80 THEN RETURN 'B';
    ELSEIF a >= 60 THEN RETURN 'C';
    ELSE RETURN 'D';
    END IF;
END;$

# 函数定义完成后复原分隔符
delimiter ; 

# 测试
select scaled_scores('John Doe', 'Math') ;
select scaled_scores('David Martinez', 'Chemistry');

# 删除
drop function scaled_scores；

存储过程

delimiter $
create procedure cursor_demo()
begin
	declare m_value int;
	declare n_value char(1);
	
	declare t1_record_cursor cursor for select m1, n1 from t1;
	
	open t1_record_cursor;
	
	fetch t1_record_cursor into m_value, n_value;
	select m_value,n_value;
	
	close t1_record_cursor;
end$
delimiter ;

call cursor_demo;
drop procedure cursor_demo;

delimiter $

create procedure cursor_demo()
begin
	declare m_value int;
	declare n_value char(1);
	declare done int default 0;
	
	declare t1_record_cursor cursor for select m1, n1 from t1;
	
	declare continue handler for not found set done = 1;
	
	open t1_record_cursor;
	
	flag: LOOP
		fetch t1_record_cursor into m_value, n_value;
		if done = 1 then
			leave flag;
		end if;
		select m_value, n_value;
	end LOOP flag;
	
	close t1_record_cursor;
end$

delimiter ;

call cursor_demo;
drop procedure cursor_demo;

触发器

delimiter $

create trigger bi_t1
before
insert
on t1
for each row
begin
	if new.m1 < 1 then
		set new.m1 = 1;
	elseif new.m1 > 10 then
	  set new.m1 = 10;
	end if;
end$

delimiter ;

select * from t1;
insert into t1 values(5, 'g'),(-1, 'h'),(100,'i');
select * from t1;

drop trigger bi_t1;

事件

delimiter $

create event insert_t1_event
on schedule
every 1 second
do
begin
  insert into t1 (m1, n1) values (6, 'k');
end$

delimiter ;
set GLOBAL event_scheduler = ON;
select * from t1;
# 间隔一段时间
select * from t1;
drop event insert_t1_event;

用户权限

用户

用户创建

select user, host from mysql.user;
create user 'user1'@'localhost' identified by '123456';
select user, host from mysql.user;

用户使用

mysql -uuser1 -hlocalhost -p123456

用户更新

alter user 'user1'@'localhost' identified by '1234567';
exit

mysql -uuser1 -hlocalhost -p123456
#mysql: [Warning] Using a password on the command line interface can be insecure.
#ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
mysql -uuser1 -hlocalhost -p1234567
exit

用户删除

drop user 'user1'@'localhost';

权限

权限查询

show grants for 'user1'@'localhost';

权限分配

grant select
on *.*
to 'user1'@'localhost'
with grant option;

权限收回

revoke select
on *.*
from 'user1'@'localhost';

删库

drop database mysql_test;

总结

模板整理

查询模板

SELECT [DISTINCT] 属性名
[FROM 表名]
[WHERE 表达式]
[GROUP BY 分组属性]
[HAVING 分组过滤条件]
[ORDER BY 排序属性]
[LIMIT 偏移量, 数量]

上述语句其实暗含了处理流程

通过 where 过滤记录
通过 group by 实现分组
通过 having 过滤分组结果
通过 order by 实现过滤后分组结果的排序
通过 limit 实现结果数量限制

存储函数模板

create function 函数名称(参数列表)
returns 返回值类型
begin
	函数体
	return 返回值
end

触发器模板

create trigger 触发器名称
{before | after}
{insert | delete | update}
on 表名
for each row
begin
	触发器内容
end

作用时间
- before：语句执行前执行触发器内容
- after：语句执行后执行触发器内容
作用范围：for each row
- insert：影响范围是即将插入的新纪录
- delete、update：影响范围是符合 where 子句过滤条件的记录
记录版本：MySQL 服务器会对某条语句影响的所有记录依次调用我们自定义的触发器内容，所以针对每一条受影响的记录，我们需要一种方式来访问该记录中的内容
- new：新纪录
  - insert 语义：准备插入的记录
  - delete 语义：无效
  - update 语义：修改后的记录
- old：旧记录
  - insert 语义：无效
  - delete 语义：准备删除的记录
  - update 语义：修改前的记录

如果触发器只包含一条语句，可以省略 begin 和 end

事件模板

create event 事件名
on schedule
{ at 时间点 ｜ every 期望的时间间隔[starts 开始日期和时间][end 结束日期和时间]}
do
begin
	具体的语句
end

参考书籍

《MySQL是怎样使用的：快速入门MySQL》小孩子4919

标签：number,t1,score,实验,student,SQL,where,select
From： https://www.cnblogs.com/ba11ooner/p/17815483.html

登入

建库

基础

建表