MySQL笔记

本文最后更新于：2024年8月11日 晚上

探索一个表

1
2
3
4
5
6
7
show databases;

show tables;

use <table_name>;

show full columns from <table_name>

修改密码

1
2
3
4
5
6
7
8
9
ROOT用户

set password for username@<localhost|'%'> = password(new_password)

mysqladmin -u<username> -p<old_password> password <new_password>

当前用户

set password = password('new_password')

创建用户

1
2
3
ROOt

create user 'user_name'@<'localhost'|'%'> identified by 'user_password';

创建数据库

1
create database <database_name>;

授权数据库给用户

1
2
grant ALL privileges on <database_name>.<table_name> to 'user_name'@<'localhost'|'%'> [with grant option]
# 在一些MYSQL版本中已经不再使用这个语句中的 indentified语句

回收权限

1
revoke privileges on <database_name>.<table_name> from <user_name>@'%';

这是Newbing

要修改MySQL用户密码，您可以登录到MySQL shell并使用以下命令1：

==flush privileges==表示刷新授权状态

1
2
ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;

root用户可以使用相同的命令来修改其他用户的密码。

要创建新用户，您可以在MySQL提示符下使用CREATE USER语句。语法如下2：

1
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

奇怪的符号的作用

  • %——任何主机都可以登录到用户名
  • host——由指定IP的主机登录，host是IP名
  • localhost——仅本地可以登录

查询用户关联主机

这是newbing

1
SELECT user, host FROM mysql.user WHERE user = 'user-name';

要更新用户关联的主机，您可以使用UPDATE语句1：

1
2
UPDATE mysql.user SET host = 'new-host' WHERE user = 'user-name';
FLUSH PRIVILEGES;

查询用户

您可以使用以下命令查询MySQL中的所有用户：

1
SELECT user, host FROM mysql.user;

这将显示所有用户及其关联的主机。

但是，MySQL并不记录用户是由哪位管理员创建的。因此，无法查询哪些用户是由特定管理员创建的。

登录状态切换用户

mysql -u<username> -p<password>

Challenge

coalesce

COALESCE(a,b)：如果A是NULL则返回B的值

查询日期的季度

QUATER(DATATIME)返回一个时间所属的季度(以1234指代)

SUM

注意，如果列全是合法的数值型，使用列名相加也许比SUM更好

==求一个订单和==

1
2
3
select sum(sale_number * price) as total
from order_table
where order_id = 114514

==分组求每个订单和==

1
2
3
select sum(sale_number * price) as total
from order_table
group by order_id

==再以订单额大小排序==

1
2
3
4
select sum(sale_number * price) as total
from order_table
group by order_id
order by sum(sale_number * price) DESC

==只显示订单额大于6000的订单==

1
2
3
4
5
select sum(sale_number * price) as total
from orer_table
group by order_id
having sum(sale_number * price) > 6000
order by sum(sale_number * price) DESC

字符串截取

==trim方法==

序号 函数 函数结果 备注
1 trim(‘ test ‘) ‘test’ 删除字符串前后空格
2 trim(both from ‘ test ‘) ‘test’ ‘both’参数表示同时去除字符串前后所指定的内容（默认情况下删除空格）
3 trim(trailing from ‘ test ‘) ‘ test’ ‘trailing’参数表示删除字符串尾部空格
4 trim(leading from ‘ test ‘) ‘test ‘ ‘leading’参数表示删除字符串头部空格
5 trim(‘x’ from ‘xxxtestxxx’) ‘test’ 删除字符串前后的字符’x’
6 trim(both ‘x’ from ‘xxxtestxxx’) ‘test’ 删除字符串前后的字符’x’
7 trim(trailing ‘xy’ from ‘xyxyxtestxxyxy’) ‘xyxyxtestx’ 删除字符串尾部的字符串’xy’
8 trim(leading ‘xy’ from ‘xyxyxtestxxyxy’) ‘xtestxxyxy’ 删除字符串头部的字符串’xy’
9 trim(leading ‘xy’ from trim(trailing ‘xy’ from ‘xyxtestxxy’)) ‘xtestx’ 删除字符串头尾的字符’xy’

==LEFT |RIGHT==方法

  • LEFT(STR,LEN)：从右往左==删去==长度为LEN的字符串
  • RIGHT(STR,LEN)：从左往右==删去==长度为LEN的字符串
  • SUBSTRING(STR,POS,LEN)：保留从POS开始到LEN位置的字符串

QS:截去字符串的头尾

有一个表table，里面只有一个字段s，存储字符型字符串，保证字符长都大于2

要求输出原字符串、删去头尾任意字符的字符串res

1
2
3
4
5
6
7
# me
select s,SUBSTRING(s,length(s)-2)
from table

# clever
select s,substring(s from '^.(.*).$') res
from table

字段重复

REPEAT(STR,TIMES)：使得一个字符串STR重复TIMES次

局部变量

局部变量==区分==大小写

有两种方法设置局部变量：

1
2
3
SET @avggrade = (SELECT AVG(grade) FROM studentcourse)

SELECT AVG(grade) INTO @avggrade FROM studentcourse;

检视局部变量

1
SELECT @avggrade

在自定义函数、定义触发器、定义过程的内部声明局部变量，需要使用==DECLARE==方法：

1
2
3
4
5
6
7
8
9
10
11
12
# 定义一个存储过程
# 因为内部需要使用分号分割语句，为了避免混淆先使用DELIMITER修改分割语句
DELIMITER //
CREATE PROCEDURE myProducer()
BEGIN
	DECLARE myVar INT DEFAULT 10;
	SELECT myVar;
END //
# 将分割符号改回来
DELIMITER
# 调用预编译的过程
CALL myProducer();

注意，声明局部变量时，默认值只能是具体的值，如果需要查询语句的值，需要再==初始化==

IF语句使用

基本用法：

1
2
3
4
5
6
7
IF expression THEN
	statements;
ELSEIF expression THEN
	statements;
ELSE
	statements;
END IF;

还有一类if是IF语句：

基本用法：

1
IF(Value,True,False)

示例：

1
2
3
4
5
# 如果性别值为1则为男，其余为女
SELECT IF(sex=1,'男','女') AS gender FROM student

# 如果姓名是空则默认为John
SELECT IF(name IS NULL,'John',name) AS NAME FROM student

While语句

基本用法：

1
2
3
WHILE expression
DO statements;
END WHILE;

可以用在存储过程、函数或触发器中。

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER //
CREATE PROCEDURE myProcedure()
BEGIN 
	DECLARE counter INT DEFAULT 0;
	WHILE counter < 5 DO
	SELECT counter;
	SET counter = counter + 1;
	END WHILE
END //
DELIMITER

CALL myProcedure();

函数

==什么时候使用函数==？

如果你希望返回一个确定的值，那么应该使用函数，如果你希望执行一系列SQL语句、查出表，而不需要返回值，那么应当使用存储过程而不是函数，函数必须有返回值。

函数返回值

函数特征

所有函数必须指定以下特征之一：

  • DETERMINISTIC：表示函数总是返回相同的结果，当给定相同的输入参数时。
  • NO SQL：表示函数不包含任何SQL语句。
  • READS SQL DATA：表示函数只包含读取数据的SQL语句

示例

定义一个函数，计算某个系的全部分数的平均分

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER \\
CREATE FUNCTION func3(dep VARCHAR(20))
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE avg INT;
    SET avg = (SELECT AVG(MID.`成绩`) FROM 
        (SELECT stucourse.`成绩` FROM stucourse JOIN student ON stucourse.`学号` = student.`学号` WHERE student.`专业名` = dep)
         AS MID);
    RETURN avg;
END \\
DELIMITER ;

常用函数

ROW_COUNT()：获取上一行执行的MYSQL语句影响的行数

CURDATE()：获取当前日期(年-月-日)

RAND()：随机生成0~1之间的随机小数

ROUND(num,point)：将一个数取到指定小数位

存储过程

删除存储过程

1
DROP PROCEDURE IF EXISTS procedureName

一个存储过程示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 扫描课程情况，如果学分总和不高于50分， 所有学分都加1
DELIMITER //
CREATE PROCEDURE plusplus()
BEGIN 
	DECLARE total INT;
	SELECT SUM(`学分`) INTO total FROM course_copy1;
	WHILE total < 50 DO
		UPDATE course_copy1 SET `学分` = `学分` + 1;
		SET total = (SELECT SUM(`学分`) FROM course_copy1);
	END WHILE;
END //
DELIMITER;

SELECT * FROM course_copy1;
CALL plusplus();
SELECT * FROM course_copy1;

一个存储过程示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER \\
CREATE PROCEDURE procedure2()
BEGIN 
	DECLARE passLine INT DEFAULT(60);
	SELECT MID.`姓名`,MID.`学号`,MID.`课程号`,course.`课程名`,MID.`成绩`
	FROM course JOIN 
		(SELECT student.`姓名`,student.`学号`,stucourse.`成绩`,stucourse.`课程号`
			FROM student JOIN stucourse ON student.`学号` = stucourse.`学号` WHERE `成绩`<passLine) AS MID
	ON MID.`课程号` = MID.`课程号`;
END \\
DELIMITER;

CALL procedure2();
笔记
#MySQL #笔记
MySQL笔记
https://qlozin.top/2023/03/10/MySQL/
作者
QLozin
发布于
2023年3月11日
更新于
2024年8月11日
许可协议