SQL

数据库概念

在计算机中, 通过一定的结构,来组织,存储和管理数据的软件系统

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能

数据库分类

　　关系型数据库

　　非关系型数据库

约束

　　主键约束 primary key

• 主关键字(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
• 语法

　　　　创建主键

• • • 列级别

create table temp(id int primary key,name varchar(20));

• • • 表级别(联合主键)

create table temp(id int ,name varchar(20),pwd varchar(20),primary key(id, name));

删除主键

alter table temp drop primary key;

添加主键

alter table temp add primary key(id,name);

　　外键约束 foreign key 

• 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系
• 语法

创建外键

基本外键

-- 主表

create table temp(

id int primary key,

name varchar(20)

);

-- 副表

create table temp2(

id int,

name varchar(20),

classes_id int,

foreign key(id) references temp(id)

);

联合外键

多列外键组合，必须用表级别约束语法

-- 主表

create table classes(

id int,

name varchar(20),

number int,

primary key(name,number)

);

-- 副表

create table student(

id int auto_increment primary key,

name varchar(20),

classes_name varchar(20),

classes_number int,

/*表级别联合外键*/

foreign key(classes_name, classes_number) references classes(name, number)

);

删除外键

alter table student drop foreign key student_id;

增加外键

alter table student add foreign key(classes_name, classes_number) references classes(name, number);

唯一约束 unique

1. 唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
2. 唯一约束不允许出现重复的值，但是可以为多个null。
3. 同一个表可以有多个唯一约束，多个列组合的约束。
4. 在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
5. 唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
6. 语法

　　　　建表唯一

创建表时设置，表示用户名、密码不能重复

create table temp(

id int not null ,

name varchar(20),

password varchar(10),

unique(name,password)

);

添加唯一

alter table temp add unique (name, password);

删除唯一

alter table temp drop index name;

非空约束 not null

1. 非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
2. Null类型特征：所有的类型的值都可以是null，包括int、float 等数据类型
3. 语法

创建非空

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc

create table temp(

id int not null,

name varchar(255) not null default 'abc',

sex char null

)；

通过设置列来设置非空和默认

-- 增加非空约束

alter table temp

modify sex varchar(2) not null;

-- 取消非空约束

alter table temp modify sex varchar(2) null;

-- 取消非空约束，增加默认值

alter table temp modify sex varchar(2) default 'abc' null;

默认值 default

　　　　　　　　mysql 数据库四种模式

1. 1. 1. ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。
      2. TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。
      3. STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。只对支持事务的表有效。
      4. STRICT_ALL_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。对所有表都有效。

　　　　　　　　记录

　　　　　　数据库设计

　　　　Oracle，Microsoft SQL Server，MySQL，PostgreSQL，DB2，Microsoft Access， SQLite，Teradata等

 SQL

DDL(数据库定义语言)

DML(数据操纵语言)

DQL(数据查询语言)

DCL(数据管理语言)

 

DDL(数据库定义语言)

数据库

创建

CREATE DATABASE [IF NOT EXISTS] <数据库名> [CHARACTER SET utf8]

 

删除

DROP DATABASE <数据库名>

 

 

 

 

查看

　　SHOW DATABASES;

　　　　查看服务中心所有的数据库

　　SHOW CREATE DATABASE <数据库名>;

　　　　查看数据库创建细节

选择

　　USE <数据库名>

数据表

创建

CREATE TABLE `t` ( 
`id` int(11) NOT NULL auto_increment,
`n_id` int(10) unsigned NOT NULL, 
`L1` int(10) unsigned zerofill default NULL,
`L2` int(11) default NULL, 
PRIMARY KEY (`id`,`n_id`), 
KEY `f_t_b` (`L1`), 
CONSTRAINT `f_t_b` FOREIGN KEY (`L1`) REFERENCES `my` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改表

 

删除

DROP TABLE <表名>

 

 

 

 

查看

查看表结构  

查看创建语句

SHOW CREATE TABLE <表名>

DML(数据操纵语言)

添加记录

INSERT INTO table_name ( field1, field2,...fieldN )

VALUES

( value1, value2,...valueN );

 

INSERT INTO TABLE_NAME(F1,F2,F3) SELECT (E1,E2,E3) FROM TABLE WHERE.....

 

 

 

 

修改记录

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

删除记录

DELETE FROM table_name [WHERE Clause]

DQL(数据查询语言)

简单查询

完整语法

字段筛选

别名AS

重复数据合并(去除重复的查询结果) distinct

where

比较运算

1. 1. 不等于 <> !=
   2. null值等于 <=>
   3. 大于,小于,等于,大于等于,小于等于

逻辑运算

and

or

and 和or同时使用,and优先级高,可以使用小括号控制顺序

 

 

空值判断

is null

is not null

存在判断(判断子查询中是否有结果)

exists

not exists

SELECT * FROM 表名 WHERE
EXISTS (SELECT id FROM my WHERE id = 1111)

in

like

通配符%_

any和all

ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。

ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。

 

 

 

 

 

 

 

 

ANY运算符

 

order by

asc

desc

limit

分页应用

 　　union联合查询

　　union -- 默认去重

 

 

 

　　union all -- 不去重

　　group by

 　　聚合函数

COUNT MAX MIN SUM AVG

除COUNT函数外，其它聚合函数在执行计算时会忽略NULL值

COUNT

　　SELECT COUNT(*) FROM TABLE_NAME-- 查询表中数据的行数,无论是否有空值

　　SELECT COUNT(COL_NAME) FROM TABLE_NAME-- 查询COL_NAME列中的个数,会忽略掉空值

MAX

　　MAX()函数是用来返回指定列中的最大值

MIN

　　MIN()函数是用来返回指定列中的最小值

SUM

• SUM()是一个求总和的函数，返回指定列值的总和
• 如果在没有返回匹配行SELECT语句中使用SUM函数，则SUM函数返回NULL，而不是0；
• SUM函数忽略计算中的NULL值
• DISTINCT运算符允许计算集合中的不同值；
• SELECT SUM(DISTINCT <列名>) FROM <表名>

AVG

　　AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值

having

• 与 GROUP BY 配合使用，为聚合操作指定条件
• WHERE 子句只能指定行的条件，而不能指定组的条件
• WHERE 先过滤出行，然后 GROUP BY 对行进行分组，HAVING 再对组进行过滤，筛选出我们需要的组
• 其使用的要素是有一定限制的，能够使用的要素有 3 种： 常数 、 聚合函数 和 聚合键 ，聚合键也就是 GROUP BY 子句中指定的列名

子查询

　　在增删改查的SQL中, 包含了另一个查询语句

多表连查

 

 

 

　　交叉连接 CROSS JOIN

　　　　select * from 表1, 表2 where 连接条件

　　　　交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。需要注意的是，交叉连接产生的结果是笛卡尔积，并没有实际应用的意义。

　　　　

内连接 INNER JOIN

　　　　select * from 表1 inner join 表2 on 连接条件

外连接

左外连接

select * from 表1 left [outer] join 表2 on 连接条件

右外连接

select * from 表1 right join 表2 on 连接条件

行列转换

使用case when

 

 　　　　　　

 if (`字段名1`=‘字段值’，，)

 

 

DCL(数据控制语言)

用户

 

授权

数据库表授权

GRANT SELECT,INSERT ON *.* TO 'easy'@'%' WITH GRANT OPTION

GRANT UPDATE (L1, l2) ON st_goods.T TO 'easy'@'%' WITH GRANT OPTION

WITH 关键字后面带有一个或多个参数。这个参数有 5 个选项:

 

 

 查看用户授权

　　SHOW GRANTS FOR 'username'@'hostname';

常用库权限

 

 常用表权限

权限表

 

数据库工具

视图

1.MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

并不是一种结构,是一种查询,对视图上面的操作都会影响到表.

2.只保存SQL语句

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。

操作

创建视图

 

查看视图定义(和查看表类似)

1. DESCRIBE 视图名；
2. SHOW CREATE VIEW 视图名;(语句)

 

修改视图定义

ALTER VIEW <视图名> AS <SELECT语句>

修改视图名称

修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称触发器

删除视图

DROP VIEW IF EXISTS <视图名1> [ , <视图名2> …]

可以像表一样进行CURD操作, 但增删改的操作受限

单表操作,只要有表的权限即可

多表操作,可以将一条语句分成多个语句

INSERT INTO v (a_id, b_id, ta_id, v1, v2) VALUES (3, 5, 3, 30, 500);会报错可以拆分为

对视图的操作会作用到物理表上

用户可以通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟的表，没有数据。通过视图更新时转到基本表上进行更新，如果对视图增加或删除记录，实际上是对基本表增加或删除记录。INSERT INTO v (a_id, v1) VALUES (3, 30);和INSERT INTO v (b_id, ta_id, v2) VALUES (5, 3, 500);

 

 

 触发器

是嵌入到 MySQL 中的一段程序,通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（INSERT，DELETE 或 UPDATE）时就会激活它执行

触发时机

增删改

操作前Before,操作后After

new, old

 

创建触发器

• CREATE TRIGGER <触发器名> < BEFORE | AFTER ><INSERT | UPDATE | DELETE >ON <表名> FOR EACH Row BEGIN <触发器主体> END
• 注意:在命令行中要使用delimiter来重新定义结束符一般临时使用$$

 

删除触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

new old

 

 

MySQL变量的定义和赋值

可以使用 DECLARE 关键字来定义变量，定义后可以为变量赋值。

定义变量

• DECLARE my_sql INT DEFAULT 10;

为变量赋值

• SET my_sql=30;
• SELECT..INTO 语句为变量赋值
• SELECT id INTO my_sql FROM tb_student WEHRE id=2；

分类

局部变量

mysql局部变量，只能用在begin/end语句块中，比如存储过程中的begin/end语句块。

用户变量

mysql用户变量，mysql中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。

会话变量

 

 

全局变量

 

 

 

 

 

 

 

MySQL流程控制语句

在存储过程和自定义函数中可以使用流程控制语句来控制程序的流程

IF 语句

CASE 语句

LOOP 语句

LEAVE 语句

ITERATE 语句

REPEAT 语句

WHILE 语句

 

 

 

 

 

 

 

 

 

-- 流程控制
create function double_fun(num int,mul int)returns int
begin
if mul=1 then
    return num;
elseif mul=2 then
    return num*2;
elseif mul=3 then
    return num*3;
else return num*4;
end if;
end;

set @test=0;
create procedure p_loop()
begin
    -- LOOP循环
    testadd:loop
    set @test=@test+1;
    if @test=20 then
    leave testadd;
    end if;
    end loop;
end;

call p_loop();
select @test;

set @test=0;
create procedure p_repeat()
begin
    -- repeat
    repeat
    set @test=@test+1;
    until @test=200
    end repeat;
end;

call p_repeat();
select @test;

create PROCEDURE p_while()
begin
    while @test<300 do
    set @test=@test+1;
    end while;
end;
call p_while();
select @test;

函数和存储过程

函数

 

 

 存储过程

1. 存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。
2. 参数有in，out，inout三种
3. 可以返回参数和结果集

 

 

 

 

 

-- 函数
create function double_fun(num int)returns int
begin
    declare temp int;
    set temp = num * 2;
    return temp;
end;

select s_id,c_id,double_fun(s_score) from score where double_fun(s_score)>160;

-- 存储过程
-- 所有的存储过程都需要call来调用
-- 这就是和函数的不一样
create procedure p_del(in stu_name varchar(20),out num int)
begin
    select count(*) into num from del_student where s_name = stu_name;
    delete from del_student where s_name=stu_name;
end;

set @num=0;
call p_del('张三',@num);

-- 时间
-- 2022-02-17 15:06:42
select date_format(now(),'%Y-%m-%d %H:%i:%s');

 

索引

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引的原理

想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree， 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

索引的类型

1. 普通索引
2. 唯一索引
3. 主键索引
4. 组合索引
5. 全文索引

 

 

 

 

 

 

 

 

 

 

 

 索引的缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
• 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引的注意事项

 

 

 索引失效的情况

 

 

 没必要使用索引的情况

 

 

-- 索引
-- 并不是越多越好
-- 新增和修改的时候都需要花很大的代价来维护的
-- 因为索引的底层是实现了一棵树
-- 改变了值树也会变
-- 用1没有索引 用'01'有索引
desc select * from student where s_id=1;
desc select * from student where s_id='01';
-- 通过这个列来创建索引,并不是把这个索引加载这个列上
create index index_name on student(s_name (20));
-- 使用like 占位符%写在前面这样并不会创建索引,是全文索引,每个都得查一遍,写在后面会创建索引
desc select * from student where s_name like '%李云';
-- 不推荐使用null的列
desc select * from student where s_name is null;

 

事务

概念: 一组不可分割的数据库操作, 要么全执行, 要么全不执行

ACID:

 

执行事务的语法和流程

• 开始事务

BEGIN;或者START TRANSACTION;

• 提交事务

COMMIT;

• 回滚（撤销）事务

ROLLBACK; 

 

并发访问数据混乱

 

 

事务的隔离级别

 

 

 

 

 

 

• 代码:

-- 创建数据库
CREATE DATABASE IF NOT EXISTS hhr_data CHARACTER SET utf8;

-- 新建表
CREATE TABLE `user` (
    id INT(11) NOT NULL auto_increment PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex VARCHAR(5) not null DEFAULT '男',
    age INT
)DEFAULT CHARSET=utf8

-- 删除数据库
DROP DATABASE hhr_data;

-- 删除表
DROP TABLE user;

-- 查看创建SQL的语句
SHOW CREATE DATABASE hhr_data;

-- 查看创建表的语句
SHOW CREATE TABLE user;

-- 查看表信息
DESC user;

-- 修改表
ALTER TABLE user RENAME TO t_user;

-- 修改编码格式
ALTER TABLE t_user CHARACTER SET utf8;

-- 添加列
ALTER TABLE t_user ADD COLUMN CODE VARCHAR(30) DEFAULT '0';

-- 修改已经存在的列
-- MODIFY是重新定义,对之前所有的定义都要重新加上
-- 比如这句后面没加DEFAULT 那就没有默认值
ALTER TABLE t_user MODIFY CODE VARCHAR(20);

-- 删除列
ALTER TABLE t_user DROP COLUMN CODE;

-- 修改列名
ALTER TABLE t_user CHANGE CODE user_code VARCHAR(20) DEFAULT '110';

-- 在xxx后面添加一列
ALTER TABLE t_user ADD COLUMN weight INT DEFAULT 70 AFTER age;

-- 修改列位置
ALTER TABLE t_user MODIFY user_code VARCHAR(20) DEFAULT '110' AFTER id;

-- 添加记录
INSERT INTO t_user values(1 , '200' , '张三' , '男' , 22 , 45);

INSERT INTO t_user(id , user_code , name , sex , age , weight) values(2 , '200' , '李四' , '男' , 22 , 45);

INSERT INTO t_user values(null , '200' , '张三' , '男' , 22 , 45);

-- 查询表信息
select * from t_user

select s_id , s_name , s_sex from student;
-- 对查询结果起别名
select s_name as name from student;

-- 修改
update t_user set sex = '女' , weight = weight - 10;

update t_user set sex = '男' where name = '张三';

-- 删除数据
delete from t_user where id < 2;

-- 对结果集去重
select DISTINCT name , sex from t_user;

-- where 
select * from student where s_id = 1;
-- 不等于 != <>
select * from student where s_id <> 1;

-- 对null的判断
-- 用 = 是错的,要用is
select * from student where s_name = null;
select * from student where s_name is null;
select * from student where s_name is not null;

-- 逻辑运算符(AND优先级高于OR)
select * from student where s_id = 1 and s_name = '赵雷' or s_sex = '男';
-- 验证子查询中是否有结果
select * from student where exists (select * from student where s_id = 1);

-- in
select * from student where s_id in (1,2,3,4);

-- like
-- _代表一个占位符 %代表不限
select * from student where s_name like '赵_';

-- all
select * from score where s_score > all(select s_score from score where s_score < 60);

-- any
select * from score where s_score > any(select s_score from score where s_score < 60);

-- limit(从0开始查询5条)
select * from student limit 0 , 5;

-- order by(正序)
select * from score order by s_score;-- 默认 asc
select * from score order by s_score desc;-- 倒序

 

-- 分组查询
SELECT * FROM student;

-- 男同学女同学各多少人
-- GROUP BY
-- HAVING 对分组之后的结果再进行筛选
SELECT s_sex , count(*) FROM student GROUP BY s_sex HAVING COUNT(*)>4;
SELECT AVG(age) FROM t_user GROUP BY name;-- AVG不计算NULL值

-- 合并查询 UNION
-- 自动去重
SELECT * FROM student WHERE s_id = 01
UNION
SELECT * FROM student WHERE s_id = 05;
-- 链接所有的结果集,不会去重 UNION ALL
SELECT * FROM student WHERE s_id = 01
UNION ALL
SELECT * FROM student WHERE s_id = 05;

-- CASE WHEN THEN ELSE END
-- 所有CASE WHEN THEN 结束以后都要用END结为
SELECT s_name , case s_sex WHEN '男' THEN '小男孩' WHEN '女' THEN '小女孩' ELSE '未知' END AS 'sex' FROM student;

SELECT CASE WHEN s_name is null THEN '无名氏' ELSE s_name END AS 'name' FROM student;

CREATE TABLE t_info as
SELECT a.s_id,a.s_name,b.s_score,c.c_name from student a LEFT JOIN score b on a.s_id=b.s_id LEFT JOIN course c on c.c_id=b.c_id;

SELECT s_id , s_name,
sum(case c_name when '数学' then s_score end)as '数学',
sum(case c_name when '语文' then s_score end)as '语文',
sum(case c_name when '英语' then s_score end)as '英语'
from t_info group by s_id;

-- select @@global.sql_mode;
-- set @@global.sql_mode='ONLY_FULL_GROUP_BY';
-- set @@GLOBAL.sql_mode='';
-- set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

SELECT * FROM t_info;
select s_name from (select * from t_info where s_score >= 60)as a group by s_id;

select avg(s_score) from t_info;

-- Left JOIN(以左边的表为主表)
SELECT * from student a left join score b on a.s_id = b.s_id;

-- RIGHT JOIN(以右边的表为主表)
SELECT * from student a right join score b on a.s_id = b.s_id;

-- inner JOIN(只选择两个表都有的公共部分)
SELECT * from student a inner join score b on a.s_id = b.s_id;

-- 创建用户
CREATE USER 'easy'@'';

INSERT INTO mysql.user(Host, User,  password, ssl_cipher, x509_issuer, x509_subject) VALUES ('localhost', 'easy', PASSWORD('password'), '', '', '');

select * from mysql.user;

GRANT SELECT ON*.* TO 'easy'@'%' IDENTIFIED BY '123456';

-- 修改密码
set password for 'easy'@'localhost'=PASSWORD('abcdef');

grant SELECT , INSERT ,UPDATE ,DELETE on hhr_data.student to 'easy'@'localhost' with grant option;

grant update(s_id , s_name) on hhr_data.t_info to 'easy'@'localhost';

-- 打印出张三老师负责的科目信息
select c_id,c_name from course left join teacher on teacher.t_id = course.t_id where t_name = '张三';

-- 学过张三老师课程的学生信息
select s_name , s_birth , s_sex from student left join score on student.s_id = score.s_id left join course on course.c_id = score.c_id LEFT JOIN teacher on teacher.t_id = course.t_id where teacher.t_name = '张三'

-- 查询出平均成绩最高的学生信息
SELECT t1.* from student as t1 right JOIN(
select score.s_id , AVG(score.s_score) as avgscore from score GROUP BY score.s_id ORDER BY avgscore DESC limit 0,1
)as t2 on t1.s_id = t2.s_id;

-- 查询出学过所有课程的学生信息
select student.* from student,(
select s_id , COUNT(c_id) as coursecount from score GROUP BY s_id) as t1
where student.s_id = t1.s_id
and t1.coursecount = (select count(*) from course);

-- 查询出语文成绩比数学成绩高的学生信息
select * from student where s_id in(
select a.s_id from (
select * from score where c_id =(select c_id from course where c_name='语文')) as a
left join (
select * from score where c_id =(select c_id from course where c_name='数学')) as b
on a.s_id=b.s_id where a.s_score>b.s_score);

 

-- 创建视图
CREATE view v_studentscore as
select a.s_id,s_name,sum(s_score) from
student as a left join score as b 
on a.s_id=b.s_id group by s_id;

select * from v_studentscore;

create view v_stu_info as
select s_id,s_name,s_sex from student;

select * from v_stu_info;

INSERT INTO v_stu_info VALUES ('10','胡浩然','男');

create view v_teacher_course as
select a.t_id,b.t_id as id 
from teacher a join course b 
on a.t_id=b.t_id;

select * from v_teacher_course;

-- 在视图上删除数据的时候,单表可以直接删除,修改啥的
-- 但是如果是多表创建的视图的话,就不行了
-- 必须要一条一条的来
-- delete;

-- 变量
-- 用户变量
-- 作用域在我这个用户身上
set @test=0;
select @test;
-- 局部变量
-- 只能在begin 和 end 中间使用
-- declare
create trigger d_a_student 
after delete on student for each row
begin
declare old_name varchar(30);
    select s_name into old_name from student where s_id='10';
    set old_name='张三';
    insert into del_student values(old.s_id,old_name,old.s_birth,old.s_sex);
end;
-- 会话变量(了解就行)
show session variables;

-- 全局变量
show global variables;
-- 查询全局变量
select @@global.sql_warnings;
-- 一个@是用户变量
-- 两个@@是全局变量

-- 触发器
create trigger d_b_student 
before delete on student for each row
begin
    set @test=@test+1;
end;

delete from student where s_id='10';

create table del_student as
select * from student where 1>1;

select * from del_student;

create trigger d_a_student 
after delete on student for each row
begin
    insert into del_student values(old.s_id,old.s_name,old.s_birth,old.s_sex);
end;

INSERT INTO student VALUES ('10','胡浩然','2000-10-08','男');
delete from student where s_id='10';
select * from del_student;

-- 删除触发器
drop trigger d_a_student;

-- set @@global.log_bin_trust_function_creators=1;

-- 函数
create function double_fun(num int)returns int
begin
    declare temp int;
    set temp = num * 2;
    return temp;
end;

select s_id,c_id,double_fun(s_score) from score where double_fun(s_score)>160;

-- 存储过程
-- 所有的存储过程都需要call来调用
-- 这就是和函数的不一样
create procedure p_del(in stu_name varchar(20),out num int)
begin
    select count(*) into num from del_student where s_name = stu_name;
    delete from del_student where s_name=stu_name;
end;

set @num=0;
call p_del('张三',@num);

-- drop procedure p_del;
-- drop FUNCTION double_fun;
-- 流程控制
create function double_fun(num int,mul int)returns int
begin
if mul=1 then
    return num;
elseif mul=2 then
    return num*2;
elseif mul=3 then
    return num*3;
else return num*4;
end if;
end;

set @test=0;
create procedure p_loop()
begin
    -- LOOP循环
    testadd:loop
    set @test=@test+1;
    if @test=20 then
    leave testadd;
    end if;
    end loop;
end;

call p_loop();
select @test;

set @test=0;
create procedure p_repeat()
begin
    -- repeat
    repeat
    set @test=@test+1;
    until @test=200
    end repeat;
end;

call p_repeat();
select @test;

create PROCEDURE p_while()
begin
    while @test<300 do
    set @test=@test+1;
    end while;
end;
call p_while();
select @test;

-- 在student表中添加一个触发器
-- 在修改学生姓名时,将学生原来的姓名存到del_student中
create trigger u_b_student;
set @oo;
before update on student for each row
begin
    select s_name into @oo from student where s_name=old.s_name;
    insert into del_student VALUES(old.s_id,@oo,old.s_birth,old.s_sex);
end;
SELECT @oo;

-- 编写一个成绩等级函数
-- 返回及格和不及格
create function level_score (score int) returns varchar(30)
begin
    if score<60 then
    return '不及格';
    else
    return '及格';
    end if;
end;

-- 编写一个存储过程 ,传入一个数量
-- 在student中种添加对应数量的"张三",返回表中总数据量
drop PROCEDURE p_add;
create procedure p_add(in num int,out sum int)
begin
    declare a int;
    set a=10;
    while a<12 do
    set a=a+1;
    insert into student(s_id,s_name) values(a,'张三');
    end while;
    select count(*) into sum from student;
end;

set @sum = 0; 
call p_add(2,@sum);
SELECT @sum;

create trigger d_b_teacher before delete on teacher for each ROW
BEGIN
DECLARE num int;
select count(*) into num from teacher;
if num<=3 THEN
    signal sqlstate 'HY000'
    set message_text='最少保留三个老师';
end IF;
end;

-- 字符串连接
select concat(12,'34','56');

-- 插入
-- insert(原字符串,在哪个位置,替换掉几个字符,用什么替换)
-- 12ABC56789
-- 如果传入超出长度的数值返回原串
select insert('123456789' , 3 , 2 , 'ABC');
-- 从左侧截取
-- 1234
select left('123456789',4);
-- 从右侧截取
-- 6789
select right('123456789',4);
-- 去掉前后的空格
-- 123
select trim('   123   ');
-- 把所有的1都替换成A
-- A23456789
select replace('123456789' , '1' , 'A');
-- 截取子串
-- 从哪个pos开始截取
-- 23456789
select substr('123456789' from 2);
-- 2345
select substr('123456789' from 2 for 4);
-- 翻转
select reverse('123456789');

-- 时间
-- 2022-02-17 15:06:42
select date_format(now(),'%Y-%m-%d %H:%i:%s');

select if(1!=1,'AB','CD');
-- 事物
set autocommit=0;
update student set s_sex='女';
commit;

-- 查看隔离级别
select  @@transaction_isolation;

set session transaction isolatin level repeatable read;

-- 索引
-- 并不是越多越好
-- 新增和修改的时候都需要花很大的代价来维护的
-- 因为索引的底层是实现了一棵树
-- 改变了值树也会变
-- 用1没有索引 用'01'有索引
desc select * from student where s_id=1;
desc select * from student where s_id='01';
-- 通过这个列来创建索引,并不是把这个索引加载这个列上
create index index_name on student(s_name (20));
-- 使用like 占位符%写在前面这样并不会创建索引,是全文索引,每个都得查一遍,写在后面会创建索引
desc select * from student where s_name like '%李云';
-- 不推荐使用null的列
desc select * from student where s_name is null;