20220721课堂笔记

20220721课堂笔记

上午

一、case when then

SELECT Cid,Cname, CASE` 
`WHEN Tid='01' THEN '张三'`
`WHEN Tid='02' THEN '李四'`
`WHEN Tid='03' THEN '王五'`
`END AS tea FROM course

 

SELECT Cid,Cname,CASE
Tid WHEN'01' then'张三'
WHEN'02' THEN '李四'
WHEN'03' THEN '王五'
end as b
FROM course

 

 

SELECT Sname,Cname,score,
CASE WHEN score >=80 THEN '优秀'
WHEN score >=60 then '及格'
ELSE '不及格'
END
FROM student as a LEFT JOIN score as b on a.Sid=b.Sid
LEFT JOIN course as c on b.Cid=c.Cid

二、if语句

if (`字段名1`=‘字段值’，，)
    select uname,uid, sum(  if(`course`='英语',score,0)  ) '英语', sum(if(`course`='物理',score,0)) '物理', sum(if(`course`='化学',score,0)) '化学' from course group by uname

 

三、行列转换

 

select name ,
sum(CASE when Subject='语文' THEN Fraction end )as 'chinese',
sum(CASE when Subject='数学' THEN Fraction end )AS 'math',
sum(CASE when Subject='英语' THEN Fraction end )AS 'english',
AVG(fraction ) as '平均成绩',
sum(fraction ) as '总成绩'
FROM t_score 
GROUP BY name

 

 

四、用户管理

 

（一）、添加

1. CREATE:

   CREATE   USER   'easy' @ '%'
   CREATE   USER   'easy'@'%'  IDENTIFIED   BY   '123'

    

2. INSERT :

   INSERT INTO mysql.user(Host, User,  authentication_string, ssl_cipher, x509_issuer, x509_subject) VALUES ('hostname', 'username', PASSWORD('password'), '', '', '');

    

3. GRANT:

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

   “.” 表示所有数据库下的所有表。Easy用户对所有表都有查询（SELECT）权限

（二）、修改密码

1. 1.SET PASSWORD(必须先登录)

   SET PASSWORD FOR 'easy'@'%' = PASSWORD ('123') 

   MYSQLADMIN:

   mysqladmin -u用户名 -p旧密码 password 新密码

2. UPDATE:

   UPDATE mysql. USER SET PASSWORD = PASSWORD ('123456')WHERE	USER = 'easy'AND HOST = '%';

    

3. ALTER

   alter user 'root'@'%' identified with mysql_native_password by'password';

    

 

（三）、用户重命名

RENAME USER 'Easy'@'%' TO 'easy'@'%'
update mysql.user set

 

（四）、删除

DROP USER 'easy'@'localhost'
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

 

 

五、数据库权限

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

    

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

    

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

1. GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户

2. MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；

3. MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；

4. MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;

5. MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

查看用户权限：SHOW GRANTS FOR 'username'@'hostname';

下午

六、视图

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

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。

（一）、创建视图

CREATE   VIEW   <视图名>   AS   <SELECT语句>
CREATE   VIEW   <视图名>   AS   SELECT   id,name   FROM   <表名>

 

（二）、查看视图定义

1. DESCRIBE  <视图名>

2. SHOW   CREATE   VIEW   <视图名>

（三）、 修改视图定义

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

    

（四）、修改视图名称

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

（五）、删除视图

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

 

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

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

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

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

 

视图的优点  ： 

1. 定制用户数据，聚焦特定的数据 不同的用户可能对不同的数据有不同的要求.

2. 简化数据操作 在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。

3. 提高数据的安全性 视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。

4. 共享所需数据 通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。

5. 更改数据格式 通过使用视图，可以重新格式化检索出的数据

要注意区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

七、触发器

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

（一）、触发时机

1. 增删改

2. 操作前 BEFORE 操作后 AFTER

3. new,old

（二）、优缺点：

优点：

1. 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。

2. 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

3. 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

缺点：

1. 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。

2. 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性

3. 如果需要变动的数据量较大时，触发器的执行效率会非常低

（三）、创建触发器

1. CREATE  TRIGGER <触发器名> < BEFORE | AFTER ><INSERT | UPDATE | DELETE >ON <表名> FOR EACH Row BEGIN  <触发器主体>  END

注意:在命令行中要使用delimiter来重新定义结束符一般临时使用$$

（四）、删除触发器

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

 

八、SQL变量的定义和赋值

（一）、局部变量

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

（二）、用户变量

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

（三）、会话变量

1. mysql会话变量，服务器为每个连接的客户端维护一系列会话变量,其作用域仅限于当前连接，即每个连接中的会话变量是独立的

2. 显示会话变量

   show session variables

    

3. 查询会话变量

   	select @@auto_increment_increment;
   	select @@session.auto_increment_increment;
   	show session variables like '%auto_increment_increment%';        -- session关键字可省略

    

4. 设置汇话变量

   	set session auto_increment_increment=1;set @@session.auto_increment_increment=2;set auto_increment_increment=3;        -- 当省略session关键字时，默认缺省为session，即设置会话变量的值
   	-- 关键字session也可用关键字local替代
   set @@local.auto_increment_increment=1;
   select @@local.auto_increment_increment;

    

（四）、全局变量

mysql全局变量，全局变量影响服务器整体操作，当服务启动时，它将所有全局变量初始化为默认值。要想更改全局变量，必须具有super权限。

1. 显示全局变量

show global variables;

 

2. 设置全局变量

   	set global sql_warnings=ON;        -- global不能省略
   	set @@global.sql_warnings=OFF;

 

3. 查询全局变量

   	-- 查询全局变量的值的两种方式
   	select @@global.sql_warnings;
   	show global variables like '%sql_warnings%';

 

 

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

DECLARE my_sql INT DEFAULT 10;

（六）、为变量赋值

1. SET my_sql=30;

2. SELECT..INTO 语句为变量赋值

3. SELECT id INTO my_sql FROM tb_student WEHRE id=2；

 

 

九、函数

1. 一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用

2. 参数只能是输入参数

3. 返回值只能是单一值

4. CREATE FUNCTION myselect5 (NAME VARCHAR(15)) RETURNS INT BEGIN DECLARE c INT; SELECT 	id INTO c FROM 	class WHERE 	cname = NAME ; RETURN c; END;

 

mysq数值常用函数

 

 

 

 

 

 

mysql字符串常用函数

 

 

 

 

 

 

 

 

<font color='red'>有人欺负我  是谁我不说<\font>