mysql常用语法

user2025

已于 2024-08-17 14:36:42 修改

阅读量401

点赞数

分类专栏： MySQL数据库文章标签： mysql 数据库

于 2020-09-13 23:10:42 首次发布

本文链接：https://blog.csdn.net/user2025/article/details/108570053

版权

MySQL数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

重新初始化表

TRUNCATE TABLE newtab;

给已有表添加新增自增长主键id

ALTER TABLE t ADD COLUMN id int(11)  NOT NULL AUTO_INCREMENT,ADD primary KEY(id)

字符串中提取数字

#提取当我和字符串
SELECT -(-'10g'),RIGHT('10g',CHAR_LENGTH(-(-'10g'))) #10,g
SELECT cast('-10g' as signed)  # -10

SELECT 1='1.1g'  #0
SELECT 1='1.0g'  #1

字符串转数字

#数字和字符串相加，字符串会被强转为数字
SELECT '1'+0  #结果为：1
SELECT '5.1克'+0 #结果为：5.1
SELECT 1+'10.1g'+0 #结果为：11.1

SELECT 'a'+'b' #结果为0；
SELECT 1+'a'  #结果为1
SELECT concat('a','b') #结果为ab

日期转字符串和字符串转日期

字符串转日期函数：STR_TO_DATE

SELECT STR_TO_DATE('2017-01-06 10:20:30','%Y-%m-%d %H:%i:%s') AS result;

日期转字符串函数：DATE_FORMAT(date, format)

SELECT DATE_FORMAT(date_column, '%Y年%m月%d日') AS formatted_date FROM table_name;

注意：
%Y：代表4位的年份
%y：代表2为的年份

%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)
%k：代表小时,格式为(0……23)
%h：代表小时,格式为(01……12)
%I：代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)

%i：代表分钟, 格式为(00……59) 【只有这一个代表分钟，大写的I 不代表分钟代表小时】

%r：代表时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表时间,格式为24 小时(hh:mm:ss)

%S：代表秒,格式为(00……59)
%s：代表秒,格式为(00……59)

convert和cast的区别

CAST(value as type);
CONVERT(value, type);

MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。CAST和CONVERT函数可以转换的类型是有限制的：

二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL(整数位数和小数位数之和，小数部分精度)
整数 : SIGNED
无符号整数 : UNSIGNED

类型转换和SQL Server一样,就是类型参数有点点不同 : CAST(xxx AS 类型) , CONVERT(xxx,类型)，类型必须用下列的类型：
可用的类型：　
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED

常用的信息函数

# 连接id
SELECT CONNECTION_ID(); 
#查询当前数据库
SELECT DATABASE();
#查看表结构
DESC saas_goods;
#最后插入记录的id
SELECT LAST_INSERT_ID();
# 返回多行插入记录中的第一条记录的自增id，仅适用于AUTO_INCREMENT自增产生的id，人为设置的id不会被返回
INSERT INTO cmm_role(role_name) VALUES ('达到1'),('达到2');
#查询当前数据库用户
SELECT USER();
#查询当前数据库版本
SELECT VERSION();

系统变量

系统变量分为：全局变量和会话变量

全局变量在不同的会话中【跨会话连接】依然有效

mysql服务器重启会给所有全局系统变量赋值，会话变量只在当前会话连接中有效

全局变量在重启后会恢复为配置文件中的默认值

#查看所有系统变量
show variables;
#查看所有全局变量
show global variables;
#查看所有会话变量
show session variables;
#模糊查询指定系统变量
show variables like '%time_zone';

MySQL的@与@@区别：@x 是用户自定义的变量，@@x 是 global或session变量

#查询会话变量【默认不写和session为前缀】
select @@变量；
select @@session.变量
#查询全局变量
select @@global.变量
#设置全局系统变量：【两种方式均可】
SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;
#设置会话变量【默认不写和session为前缀】
SET SESSION sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL'

#Mysql 8 提供了持久化全局系统变量到配置文件的sql语句方式，但之前的版本不支持
#set persist更改运行时变量值，将变量设置写入mysqld-auto.cnf数据目录中指定的选项文件 。数据库启动时，会首先读取其它配置文件my.cnf，最后才读取mysqld-auto.cnf文件。

#将全局系统变量持久化到 mysqld-auto.cnf文件（并设置运行时值）：
SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;
#将全局系统变量持久化到 mysqld-auto.cnf文件（不设置运行时值）：
SET PERSIST_ONLY back_log = 1000;
SET @@PERSIST_ONLY.back_log = 1000;

自定义变量

自定义变量分为：用户变量和局部变量

用户变量：仅在当前会话（连接）有效【作用域与会话变量相同】

#设置或更新用户变量
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;

#当查询只有一个值时，可以通过select into给用户变量名赋值
select 字段1,字段2 into @用户变量名1,@用户变量名2 from 表;
#查询用户变量名的值
select @用户变量名;

#java中需要在url中添加：serverTimezone=Asia/Shanghai

局部变量：仅在定义局部变量的begin end范围内有效

#声明局部变量
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

#赋值
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT @局部变量名 :=值;  【特殊】

SELECT 字段1,字段2 INTO 局部变量1，局部变量2 FROM 表;

#查询局部变量的值【仅在begin end范围内有效】
SELECT 局部变量名;

设置数据库系统参数有两种方式

(1) 通过sql语句设置系统参数，如设置开启定时任务命令为：set global event_scheduler =1;

(2) 通过修改配置文件设置系统参数，修改配置文件my.cnf或my.ini

解决MySQL8.0时区的问题

#查看当前时间
select now();

#system_time_zone 表示操作系统使用的时区，其中CST表示：“中国标准时间+8:00区”
#time_zone 表示 MySQL 采用的是系统的时区。如果在连接时没有设置时区信息，就会采用这个时区配置。
show variables like '%time_zone%'; 

#查询全局系统时间和当前会话时区
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

# 方法一：使用命令（优点：设置后立即生效，缺点：重启mysql服务器后失效）
# 仅修改当前会话的时区，会话结束失效【navicat查询窗口关闭就失效】
set time_zone = '+8:00';
# 修改全局的时区配置，重启失效
set global time_zone = '+8:00';
flush privileges; #立即生效

# 方法二：修改my.ini配置文件（优点：永久保存设置，缺点：需重启MySQL服务）
# 设置默认时区【重启生效】 
default-time_zone='+8:00'; 【放在配置文件的mysqld后面的任意位置即可】

# 可以直接在navicat的查询窗口输入 restart重启mysql服务器
restart;

设置Mysql服务器端字符集

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4

[mysqld]
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

事务隔离级别

mysql8 默认的事务隔离级别：REPEATABLE-READ【重复读】

mysql8 默认的事务隔离级别：REPEATABLE-READ【重复读】

#查询事务隔离级别
SELECT @@transaction_isolation
#查询是否自动提交事务
SELECT @@autocommit

#模糊查询
show variables like '%time_zone%';

删除表

drop table if exists 表名;
CREATE TABLE IF NOT EXISTS [tableName] SELECT...

Delimiter的作用

Delimiter的作用: 声明sql语句的结束符是什么，告诉mysql解释器，该命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。如输入下面的语句mysql> select * from test_table;然后回车，那么MySQL将立即执行该语句

delimiter &&
create table t1(
    id int,
    name varchar(20)
)&&
delimiter ;   #最后一行delimiter;表示将mysql的结束符重新置为分号

存储过程

存储过程的参数列表由三部分构成：参数模式参数名参数类型

参数模式分为三种：in out inout

in：以in修饰的参数名作为输入，表示调用存储过程需要传入的参数【如果没有参数模式，默认是in模式】

out：以out修饰的参数名作为输出，表示调用存储过程的返回值

inout：以inout修饰的参数名既可以作为输入，又可以作为输出【即为入参，也为返回值】

#创建存储过程
create procedure 存储过程名(参数列表)
begin
   存储过程体
end
#(1)当存储过程体重只有一句话时，begin end可以省略
#(2)存储过程体中的每条sql语句必须以分号结尾
#(3)存储过程的结尾可以使用delimiter重新设置

drop PROCEDURE 存储过程名;
drop PROCEDURE if exists 存储过程名;

#调用存储过程
CALL 存储过程名(实参列表);

#查看存储过程实现
show create procedure 存储过程名;

空参列表的存储过程

#创建存储过程
CREATE PROCEDURE customer()
BEGIN
DECLARE a INT DEFAULT 1;
SELECT a;
END
#调用存储过程
CALL customer();

带输入的存储过程

CREATE PROCEDURE test1(IN id INT)  
BEGIN
	SELECT * FROM saas_goods WHERE goods_id=id;
END;

CALL test1(1);

#当参数名与表的字段名相同时，字段名前需要加表名以示区别
CREATE PROCEDURE test1(IN goods_id INT)  
BEGIN
	SELECT * FROM saas_goods WHERE saas_goods.goods_id=goods_id;
END;

带输入、输出的存储过程

CREATE PROCEDURE test1(IN goods_id INT,OUT num INT,OUT avger INT)  
BEGIN
	SELECT count(*),sum(goods_id) INTO num,avg FROM saas_goods WHERE saas_goods.goods_id=goods_id;
END;
# 调用存储过程
CALL test1(1,@num,@avg);   #可以先声明一个用户变量来接收，也可以直接使用
# 查询临时变量值
SELECT @num,@avg

inout模式的存储过程

CREATE PROCEDURE test2(IN goods_name VARCHAR(20),INOUT a INT,INOUT b INT)
BEGIN
	SET b=a*b;
	SELECT count(*) INTO a FROM saas_goods WHERE saas_goods.goods_name=goods_name;
END;

SET @a=20;
SET @b=10;
CALL test2('当归',@a,@b);

SELECT @a,@b;

存储过程和函数的区别

函数必须返回一个变量，而存储过程可以返回0个或多个；
函数的限制比较多，不能用临时表，只能用表变量，有些函数不能用，存储过程限制少
存储过程可以执行修改表的操作，但是函数不能执行一组修改全局数据库状态的操作，建议只用于数据的查询和处理

函数

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

#（1）参数列表由两部分组成：参数名 参数类型
#（2）没有return语句也不会报错，但不建议

#调用函数
select 函数名(参数列表);

#设置信任函数的创建者，不会创建写入二进制日志的存储函数，即不会修改数据库数据
# 二进制日志(binary log)：记录数据库里的数据被修改。（insert,update,delete,create,drop,alter）的相关语句；
#创建函数必须开启对函数创建者的信任，只有这样才能创建自定义函数，开启后实际上也可以做修改操作，但不建议
SET GLOBAL log_bin_trust_function_creators = 1;

#创建函数
CREATE FUNCTION test(goodsname VARCHAR(20)) RETURNS INT
begin
	DECLARE num int;
	SELECT COUNT(*) into num FROM saas_goods WHERE goods_name=goodsname;
	RETURN num;
END;

#查看函数
show create function 函数名;
#删除函数
drop function 函数名;

循环

mysql的循环分为三类：while、repeat、loop

repeat 相当于dowhile loop相当于死循环

控制循环的关键词：leave、iterate，使用leave、iterate时，必须给循环设置循环标签。

leave相当于break，iterate相当于continue。

#循环语句(无循环控制关键词)
CREATE PROCEDURE customer_procedure(IN num INT)
	BEGIN
		DECLARE i INT DEFAULT 0;
		WHILE i<num DO
		INSERT INTO newtab(tenant_alias) VALUES(CONCAT('test',i));
		SET i=i+1;
	END WHILE;
END;
CALL customer_procedure(10);

#循环语句(带循环控制关键词leave)
CREATE PROCEDURE customer_procedure( IN num INT ) 
BEGIN
	DECLARE i INT DEFAULT 0;
	flag: WHILE i < num DO 
			INSERT INTO newtab ( tenant_alias ) VALUES ( CONCAT( 'test', i ));
			IF i > 5 THEN LEAVE flag;
			END IF;	
			SET i = i + 1;
	END WHILE flag;
END;
CALL customer_procedure(10);

#循环语句（使用iterate）
CREATE PROCEDURE customer_procedure ( IN num INT ) 
BEGIN
	DECLARE i INT DEFAULT 0;
	flag: WHILE i < num DO 
			IF i=5 THEN SET i=i+1;
			ITERATE flag;
			END IF;
			INSERT INTO newtab ( tenant_alias ) VALUES ( CONCAT( 'test', i ));
			SET i=i+1;
	END WHILE flag;
END;
CALL customer_procedure(10);

#repeat循环语句
CREATE PROCEDURE test_repeat(IN num int)
BEGIN
	DECLARE i INT DEFAULT 0;
	REPEAT
	INSERT INTO newtab ( tenant_alias ) VALUES ( CONCAT( 'test', i ));
	SET i=i+1;
	UNTIL i>5 END REPEAT;
END
CALL test_repeat(10);

#loop循环语句
CREATE PROCEDURE test_loop(IN num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
	flag: LOOP
	  INSERT INTO newtab ( tenant_alias ) VALUES ( CONCAT( 'test', i ));
		SET i=i+1;
	IF i>=num THEN LEAVE flag; 
	END IF; 
	END LOOP flag;
END;
CALL test_loop(5);

IF语句

# 查询id>=50的数量和查询id<50的数量
select sum(IF(id>=50,1,0)) ,sum(IF(id<50,1,0)) from xiaozu_shop;
# 统计
select count(IF(id>=50,1,null)) ,count(IF(id<50,1,null)) from xiaozu_shop;

&& ||

and or 用于where 条件中拼接条件

&& || 用于 if 逻辑判断

SELECT  (1=1 || 1<0)

行转列【一般用if函数和case when】

user_id	user_name	num
1	八戒	2
2	孙悟空	5
3	沙僧	7
4	孙悟空	1
5	沙僧	3
6	孙悟空	6

切记转换后：此时八戒、孙悟空、沙僧是字段名

SELECT
	SUM(IF( user_name = '孙悟空', num, 0 )) `孙悟空`,
	SUM(IF( user_name = '八戒', num, 0 )) `八戒`,
	SUM(IF( user_name = '沙僧', num, 0 )) `沙僧` 
FROM
	`user`;
-------------------------------------------------------------------------------------
SELECT
	sum( CASE user_name WHEN '孙悟空' THEN num ELSE 0 END) `孙悟空`,
	sum( CASE user_name WHEN '八戒' THEN num ELSE 0 END) `八戒`,
	sum( CASE user_name WHEN '沙僧' THEN num ELSE 0 END) `沙僧` 
FROM
	`user`;
--------------------------------------------------------------------------------------
SELECT
	sum( CASE WHEN user_name='孙悟空' THEN num ELSE 0 END) `孙悟空`,
	sum( CASE WHEN user_name='八戒' THEN num ELSE 0 END) `八戒`,
	sum( CASE WHEN user_name='沙僧' THEN num ELSE 0 END) `沙僧` 
FROM
	`user`

列转行【一般用union】

user_name	语文	数学	英语
张三	82	88	90
李四	98	87	65
王五	78	65	45

SELECT user_name,'语文' course,`语文` score FROM chengji
UNION 
SELECT user_name,'数学' course,`数学` score FROM chengji
UNION 
SELECT user_name,'英文' course,`英文` score FROM chengji
ORDER BY CONVERT(user_name USING GBK),score

判断重复数据用：GroupBy 和Having 搭配使用 ----------- —重复数据的删除

DELETE 别名 FROM 表名称 别名 WHERE 列名称 = 值

#删除重复数据，并保留id最大的
DELETE a
FROM
	`user` a INNER JOIN ( SELECT MAX( user_id ) user_id, user_name FROM `user` GROUP BY user_name HAVING COUNT(*)> 1 ) b ON a.user_name = b.user_name 
WHERE
	a.user_id < b.user_id