mysql数据库相关的sql

mysql数据库相关的sql

阅读前须知：①本文只记录了自己使用过程中容易忘，容易错，和一些场景sql，比较简单的这里不会记录。

目录

一、mysql设置

二、DDL语句

三、函数篇

四、DQL语句

五、dml语句

六、场景

---

一、mysql设置

#取消外键约束
set foreign_key_checks=0;         
#设置外键约束
set foreign_key_checks=1     
#查看数据库的端口号
show global variables like 'port'
#开启服务:        
net start mysql
#关闭服务
net stop mysql

#查看某个库下所有表使用的存储引擎
show table status from 库名；
#修改指定表的搜索引擎
alter table student engine=myisam;

#查看当前数据库的版本号
select version();

二、DDL语句

#显示所有数据库
show databases
#创建数据库
create database 数据库名
#使用数据库
use 数据库名
#删除数据库
drop database数据库名
#显示当前数据库下的表
show tables

 

#1 修改字段的长度
alter table `表名` modify column 字段名 varchar (64) default null comment '备注内容';
#2 添加字段
alter table `表名` add column 字段名 int (4) default null comment '备注内容';

 

三、函数篇

#字符串函数：
length(s)：返回字符串s的长度；
concat(s1,s2,...)：将字符串s1,s2等多个字符串合并为一个字符串;如果有任何参数为null，则函数返回null。如果参数是数字，则自动转换为字符串；
upper(s)：将字符串s的所有字母变成大写字母
lower(s)：将字符串s的所有字母变成小写字母
ltrim(s)：去掉字符串s开始处的空格
rtrim(s)：去掉字符串s结尾处的空格
trim(s)：去掉字符串s开始和结尾处的空格

字符串包含查询：查询的字符串 是否被name字段包含
 LOCATE(#{'查询字符串'},name)>0

 

#时间转换函数：
curdate(),current_date()：返回当前日期
crrtime(),current_time():返回当前时间
now()：返回当前日期和时间
year(d)：接受date参数，并返回日期的年份

时间格式化：注意，可传入的时间必须是date类型 或者 “2020-12-05”格式
    方式一：DATE_FORMAT(date,format)                        

                  如：DATE_FORMAT(now(),'%Y-%m-%d')        

                 其他格式参考：https://www.w3school.com.cn/sql/func_date_format.asp
  方式二：SELECT year(date),month(date), day(date)        如：year(NOW())

#数学函数:
round(x)：返回参数x的四舍五入的一个整数
round(x,d)：返回参数x的四舍五入值，d为小数的一个数字。
如果d为0，结果将没有小数点或小数部分
#控制流程函数:
if(test,arg1,arg2) ：如果test是真，返回t；否则返回f
ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2
case when then：类似switch...case
/* 语法1：当满足条件value = compare-value 时，返回对应的result，否则返回else后的result
        case value
                when [compare-value]
                    then result
                [when [compare-value]
                    then result ……]
                [else result ]
            end
        语法2：当满足条件condition时，返回对应的result，否则返回else后的result。mysql中，0表示false,1表示true;
        case
                when [condition]
                    then result
                [when[condition]
                    then result ……]
                [else result]
            end */

 

四、DQL语句

#多表查询：
隐式内连接(等值连接)：select <selectlist> from a ,b where a.列 = b.列
显示内连接(推荐写法)：select <selectlist> from a [inner] join b on a.列 = b.列
外连接：
        select    <selectlist>
        from a  left/right outer  join b
                on a.column_name = b.column_name;

五、dml语句

#插入
insert into 表名 (name,age) values('张三',1);
#修改
UPDATE 表名 SET sort = 1, updated_at = NOW()
#删除
DELETE FROM 表名 WHERE id = #{id}

 

六、场景

1、场景一：插入之前判断数据是否存在（若不存在则添加）

insert into test_table(name,age) select '刘星2', '19' from dual  
where not exists(select name from test_table where name = '刘星2')

2、场景二：先分组，再去重，再求总个数

SELECT
    IFNULL(SUM(total), 0)
FROM
    (
        SELECT
            state,
            name,
            count(DISTINCT(user_id)) AS total
        FROM xgx_unicom_order_record
        WHERE state = 1 AND name like '%名字%'
        GROUP BY name
    ) AS tt

3、场景三：两条sql的结果进行运算

select
(SELECT num from user where id  = 63) -
(select num from user where member_id = 63)as num