mysql基础语法

– 选择指定的库
use test0706;
– 显示表结构
desc article;
– 查询表达式
select 900*2;
– 查询函数
select VERSION();
– 去重
select DISTINCT card from class;
– + 号的作用
select 100+90; – 都是数字做加法
select ‘join’ + 90; – 一方是字符，则将字符转换成数字，成功做加法，失败字符就变成0做加法
select null + 90; – 一方是null结果为null
– 转义字符 \ 可以自定义转义字符 ESCAPE ‘$’
– 安全等于 <=> 可以判断 null 也可以判断普通值

– 常用函数
– CONCAT 做字段连接
select concat(author_id,’_’,category_id,title) as lianjie from article;
– IFNULL 字段为空给默认值
select IFNULL(title,0) from article;
– LENGTH(str) 获取字段长度 字节数
select length(‘字段长度数’) ;
– 大小写转换 UPPER(str) LOWER(str)
select upper(‘ddd’);
select LOWER(‘DDD’);
– 字符串截取
select SUBSTR(‘abcde’,2); – 第二个字符开始往后截取，包含第二个
select SUBSTR(‘abcdefggt’,2,3); – 第二个字符开始往后截取3个字符，包含第二个
select SUBSTRING(‘abcdefggt’,2,3); – 第二个字符开始往后截取3个字符，包含第二个
– INSTR(str,substr) 返回子串在主串中第一次出现的索引，没有返回0
select INSTR(‘abfdscd’,‘dsc’);
– TRIM([remstr FROM] str) 去掉前后空格
select trim(’ add ‘);
select trim(‘c’ from ‘cccccaddccccc’); – 去掉指定字符
– LPAD(str,len,padstr) 用指定字符左填充制定长度
select lpad(‘fff’,8,‘p’);
select RPAD(‘fff’,8,‘p’); – 用指定字符右填充制定长度
– 字符替换 REPLACE(str,from_str,to_str)
select REPLACE(‘AAAADDDVVVDDD’,‘DDD’,‘OOOOOO’);
– 四舍五入 ROUND(X)
select ROUND(1.89);
SELECT ROUND(1.567,2); – 保留两位小数
– 向上取整 CEIL(X)
select CEIL(1.1);
– 向下取整 FLOOR(X)
select FLOOR(1.1);
– TRUNCATE(X,D) 数字截断 截取小数后位数
SELECT TRUNCATE(1.8786,2);
– MOD(N,M) 取余
select MOD(10,3);
– NOW() 当前系统日期带时分秒
select NOW();
– CURDATE() 当前系统日期不带时分秒
select CURDATE();
– CURTIME() 当前系统时间不带日期
select CURTIME();
– 获取日期中指定部分
select YEAR(NOW());
select MONTH(NOW());
select DAY(NOW());
select HOUR(NOW());
select MINUTE(NOW());
select SECOND(NOW());
– 字符转日期 STR_TO_DATE(str,format)
SELECT STR_TO_DATE(‘2021-07-10 10:43:50’,’%Y-%m-%d %H:%i:%s’);
– %Y 4位年份 %y 2位年份 %m 01,02月份 %c 1 2月份 %d 日
– %H 24小时 %h 12 小时 %i 分钟 %s 秒
– 日期转字符 DATE_FORMAT(date,format)
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%s’);
– 查看当前版本号 VERSION()
SELECT VERSION();
– 查看当前数据库 DATABASE()
SELECT DATABASE();
– 查看当前用户 USER()
SELECT USER();

– if
SELECT IF(10<4,‘Y’,‘N’);
– CASE
select case author_id
when 1 then 90
when 2 then 91
when 3 then 92
ELSE 93
end as caseName
from article;

select case
when author_id =1 then 90
when author_id=2 then 91
when author_id=3 then 92
ELSE 93
end as caseName
from article;

– 下面分组函数 忽略 null 可以和 DISTINCT 搭配使用
– SUM 只处理数字类型
SELECT SUM(author_id) from article;
– AVG 只处理数字类型 忽略 null
SELECT AVG(author_id) from article;
– MIN 可以处理任何类型 忽略 null
SELECT MIN(author_id) from article;
– MAX 可以处理任何类型 忽略 null
SELECT MAX(author_id) from article;
– COUNT 可以处理任何类型 忽略 null
SELECT COUNT(author_id) from article;

– EXISTS 有数据返回1 没有数据返回0
SELECT EXISTS(select * from article);
SELECT EXISTS(select * from article where author_id=9);

– LIMMIT 分页查询 起始行号，行数
select * from class c limit 0,10;

– 修改库的字符集
ALTER DATABASE test0706 CHARACTER utf8mb3;

SELECT * FROM article2;
desc article2;
– 修改列名
ALTER TABLE article CHANGE COLUMN create_date CHANGE_DATE DATETIME;
– 修改列的类型
ALTER TABLE article modify COLUMN CHANGE_DATE int(10) ;
– 添加列
ALTER TABLE article add COLUMN create_date DATETIME;
– 删除列
ALTER TABLE article drop COLUMN CHANGE_DATE;
– 修改表名
ALTER TABLE article RENAME to article2;

– 删除表
drop table if EXISTS article2;
– 复制表结构 目标表 like 源表
CREATE table article like article2;
select * from article;
– 复制结构加数据
CREATE table article3 select * from article2;
select * from article3;

– 查看全局变量
show GLOBAL VARIABLES;
– 查看部分全局变量
show GLOBAL VARIABLES like ‘%char%’;
– 查看会话变量
SHOW SESSION VARIABLES
– 查看指定变量的值
select @@global.autocommit;

– 自定义变量当前会话有效
– 声明并初始化
set @a=1;
set @a:=1;
– 查看变量值
select @a;
– 局部变量声明赋值，作用域是 BEGIN END 内
DECLARE aa int ;
DECLARE aa int DEFAULT 1;

– 存储过程和函数
– 无参
create PROCEDURE BOOK1_insert1()
BEGIN
insert into BOOK1 (card) values (66);
insert into BOOK1 (card) values (69);
END
call BOOK1_insert1();
SELECT * FROM BOOK1;
– in 入参
create PROCEDURE BOOK1_insert2(in val int )
BEGIN
insert into BOOK1 (card) values (val);
insert into BOOK1 (card) values (val+9);
END
call BOOK1_insert2(100);
SELECT * FROM BOOK1;
– out 出参
create PROCEDURE BOOK1_select1(in val int,out re int)
BEGIN
select bookId FROM BOOK1 where card=val;
end

call BOOK1_select1(100,@re);
select @re;

– inout
CREATE PROCEDURE BOOK1_select2(INOUT a int ,INOUT b int )
BEGIN
set a=a2;
set b=b2;
END

set @b=100;
set @a=200;
call BOOK1_select2(@a,@b);

select @a,@b;

– 查看存储过程
SHOW CREATE PROCEDURE BOOK1_select2;

– 函数
– 1 DETERMINISTIC 确定的
– 2 NO SQL 没有SQl语句，当然也不会修改数据
– 3 READS SQL DATA 只是读取数据，当然也不会修改数据
– 4 MODIFIES SQL DATA 要修改数据
– 5 CONTAINS SQL 包含了SQL语句
– you might want to use the less safe log_bin_trust_function_creators variable
– 是由于开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用
– 可以设置set global log_bin_trust_function_creators=1;
– 设置函数结尾标识 DELIMITER //
CREATE FUNCTION myf1() RETURNS int
READS SQL DATA
BEGIN
DECLARE a int DEFAULT 0;
SELECT count(1) into a from article2;
return a;
END

select myf1();
– 设置结尾标识并带有参数
DELIMITER $$CREATEFUNCTIONmyf2(bint)RETURNSintREADSSQLDATABEGINDECLAREaintDEFAULT0;SELECTcount(1)intoafromarticle2;returna+b;END$$

select myf2(90)$$;

CREATE FUNCTION myf3(soure int ) RETURNS char
READS SQL DATA
BEGIN
IF soure >=90 and soure<=100 then return ‘A’;
ELSEIF soure>=80 then return ‘B’;
elseIF soure >=60 then return ‘C’;
end if;
END

select myf3(87);
– 循环结构
truncate table book1;
select * from book1;

create procedure testInsert1(in incount int )
begin
declare i int DEFAULT 1;
while i<incount do
insert into book1 (card) values (FLOOR(1+(RAND()*100)));
set i=i+1;
end while;
END
call testInsert1(100);

– 添加leave 跳出循环
truncate table book1;
select * from book1;

create procedure testInsert2(in incount int )
begin
declare i int DEFAULT 1;
r:while i<=incount do
insert into book1 (card) values (FLOOR(1+(RAND()*100)));
if i>=20 then leave r;
end if ;
set i=i+1;
end while r;
END
call testInsert2(100);

– ITERATE 跳过当前循环进入下次
truncate table book1;
select * from book1;

create procedure testInsert3(in incount int )
begin
declare i int DEFAULT 1;
r:while i<=incount do
set i=i+1;
if mod(i,2)!=0 then ITERATE r ;
end if ;
insert into book1 (card) values (i);
end while r;
END
call testInsert3(100);

– repeat
truncate table book1;
select * from book1;

create procedure testInsert4(in incount int )
begin
declare i int DEFAULT 1;
r:repeat
insert into book1 (card) values (i);
set i=i+1;
UNTIL i>incount
end repeat r;
END
call testInsert4(100);