Mysql语句手册大全-入门到进阶

军大君

已于 2022-10-12 23:22:01 修改

阅读量753

点赞数 2

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

于 2021-12-27 17:03:24 首次发布

本文链接：https://blog.csdn.net/weixin_42581660/article/details/121851888

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

原来视频地址在哔哩哔哩【【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！】

1.SELECT语句

--其中*可以替换成你需要的字段
--关键字的前后顺序是有规律的，不能随意编写【会报错】
--一些常用的条件
> 大余  < 小于  = 等于
>=大于等于  <=小于等于  !=不等于  <>不等于
and 和  or或  not否定条件 is null 是空的
in在其中 
between A and B 在A和B之间
like '%家%' 模糊查询  结果  12家12、1家1、家
like '__家' 模糊查询【这里有2个下划线】  结果   12家、34家
regexp '家'    模糊查询  包含[家]的
regexp '^家'   模糊查询  [家]开头的
regexp '家$'   模糊查询  [家]结尾的
regexp '家|好' 模糊查询  包含[家]和[好]的
regexp '[TY]Q' 模糊查询[不支持中文]  T或者Y在Q前的，这里只支持数字和英文，不支持中文
regexp '[A-F]Q' 模糊查询 A到F在Q前的
limit 20 查询20条数据 
limit 20,10 从第20条开始查询10条数据
---------------------------------------------------
--最简单的sql语句
select * from 表
--去重
select distinct * from  表
--加条件
select * from 表 where 条件
--排序
select * from 表 order by 字段A asc,字段B desc
--取别名
select 字段A as 字段A别名 from 表
--去重，加条件，排序，取别名
select distinct 
字段A as 字段A别名 
from 表 where 条件 
order by 字段A,字段B asc/desc
--SQL顺序
select * distinct <select_list>
from <left_table>
<join_type> join <right_table>
on <join_condition>
where <where_condition>
group by  <grout_by_list>
with {CUTE|ROLLUP}
having <having_condition>
order by <order_by_list>
limit <limit_number>

2.JOIN连接关键字

--内连接[默认内连接] 
--join = inner join
select * from 表A
join 表B on 表A.id = 表B.a_id
join 表C on 表A.id = 表C.a_id
--外连接 
注意:尽量避免使用右连接，全用左连接，如果左右连接同时使用的话会使代码可读性变差
--left join = left outer join
--right join = right outer join
[↓下面这个sql会显示A表中的所有记录，只显示B表中符合条件的记录]
select * from 表A
left join 表B on 表A.id = 表B.a_id
[↓下面这个sql会显示B表中的所有记录，只显示A表中符合条件的记录]
select * from 表A
right join 表B on 表A.id = 表B.a_id
---using关键字
[通过using关键字嘞join两个表，注意AB表中列名要一致才可以使用using]
select * from 表A
left join 表B using(列名A)
--natural join 数据库会更具2个表相同字段来求结果【不安全，不建议使用】
select * from 表A natural join 表B 
--cross join 交叉连接【AB表的乘积】
select * from 表A cross join 表B  【显式】
select * from 表A ,表B            【隐式】
--union 联合用来连接查询2个sql的结果[注意2个sql列的数量要一样，不然会报错]
select name from 表A 
union
select age from 表B

3.插入，复制，删除，更新

--insert 语句 
--单条插入
insert into 表A (id,name) values ('1','小明');
--批量插入
insert into 表A (id,name) values ('2','大明'),('3','Jack');
--分层插入数据，获取父表的id来插入子表数据，LAST_INSERT_ID()阔以获取上一条插入语句的逐渐id值
insert into 表爸 (name,age) values ('李爸','30');
insert into 表儿 (father_id,name,age) values 
(LAST_INSERT_ID(),李一，4),(LAST_INSERT_ID(),李二，3);
--复制表
create table 表A_COPY as select * from 表A;
--复制表A的一部分数据到表A_COPY
insert into 表A_COPY select * from 表A where name = '小明';
--更新语句
update 表A set name = '小哈',age = age-3 where id = '1'
--更新语句里面嵌套子查询
update 表A set name = '大哈' where
id in (select id from 表B where name = '大哈')
--删除语句
delete from 表A  where id = '1';
delete from 表A  where id in (select id from 表B where name = '大哈');

4.聚合函数

max()		找最大值
min()		找最小值
avg()		求平均值
sum()		求和
count()	    求条数【不计算空】
count(*)    求条数【会累加字段为null的】
select 
	max(age),
	min(age),
	avg(age),
	sum(age),
	count(age),
	count(distinct name),
	count(*) 
from 表A
---group by 按照字段分组，阔以填写多个字段
select id,sum(quantity) from 表A 
group by id;【这里默认会按照id分组后排序】
-----
select id,sum(quantity) from 表A 
group by name,age;【这里默认会按照id和那么分组】
--having  在group by语句后面
select id,sum(quantity) from 表A
where 条件语句
group by name
having 条件语句
--with rollup【这里会计算age的总和到最后一行】
select city,sum(age)
where 条件语句
group by city with rollup

5.复杂SQL的编写

--子查询
select * from 表A 
where age > (select age from 表A where name = '小红')
--in条件
select * from 表A 
where id in (select id from 表A where age <= 23)
--多条件
--查询A表中买了白菜的所有记录，B是关联表
select * from 表A
where id in (
select b.a_id from 表B b 
join 表C c using(b和c表的公共字段)
where c.product_name = '白菜');
--多条件【和上面的sql结果一致】
--查询A表中买了白菜的所有记录，B是关联表
select * from 表A a
join 表B b using(a和b表的公共字段)
join 表C c using(b和c表的公共字段)
where c.product_name = '白菜'
--all关键字
--查询年纪大于含有'明'字的人的年纪的记录
select * from 表A where
age > all(select age from 表A where name like '%明%');
select * from 表A where
age > all(12,13,13,32);
--any运算符【= any 和 in 效果一致】
select * from 表A where age = any(12,13,14);
select * from 表A where age in (12,13,14);
--exists 存在[效率比in好]
select * from 表A where
exists(select a_id from 表B where a_id = 表A.id);
select * from 表A where
id in (select distinct a_id from 表B where a_id = 表A.id);
--select 字句中的子查询
select                               id,
                                    列B,
   (select avg(列C)from 表A) as average,
   列B - (select average) as difference
                                       from 表A
--from子句中的子查询
select * from (select 子句)

6.函数

6.1数值函数

--数值函数
select round(5.1561,2)    --【5.16】四舍五入保留2位小数
select truncate(5.1561,2) -- 【5.15】截断
select ceiling(5.2)       --【6】向上取整
select floor(5.6)         --【5】向下取整
select abs(-5.6)          --【5.6】绝对值
select rand()             --0到1的随机值

6.2字符串函数

--大小写和长度
select length('abc')  -- 【3】输出字符串字符个数/长度（LENGTH）
select upper('abc')   -- 【ABC】转大写
select lower('AbC')   -- 【abc】转小写
--去掉空格
select ltrim('    abc') --去掉左边的空格
select rtrim('abc  ')   --去掉右边的空格
select trim('  abc  ')  --去掉左右的空格
--取值
-- 取左边，取右边，取中间
select left('abcdefg', 3)          --【abc】取左边3个字符
select right('abcdefg', 2)         --【fg】取右边2个字符
select substring('abcdefg', 3, 2)  --【cd】重第3个开始往右取2个
--定位
select locate('def', 'abcdefg')  --【4】 找到位置了
select locate('deq', 'abcdefg')  --【0】 没有找到元素所在，会返回0
--替换
select replace('我在这里','这里','老地方')--【我在老地方】
--拼接[常用来生成sql语句]
select concat('AA', 'BB', 'CC') AS full--【AABBCC】

6.3MySql中的日期函数

select now()      -- 【2021-12-27 14:41:47】当前日期和时间
select curdate()  -- 【2021-12-27】			当前日期
select curtime()  -- 【14:41:47】			当前时间
--提取日期对象的元素
select year(now())  -- 【2020】
year可以替换成month/day/hour/minute/second
select dayname(now())  -- 【Monday】
select monthname(now())  -- 【December】
标准SQL语句有一个类似的函数 extract()
select extract(year from now())
--格式化日期和时间
--这里的格式化字符串有很多,可以百度关键字【MYSQL日期格式字符串】
select date_format(now(), '%Y-%m-%d')  --【2021-12-27】
select time_format(now(), '%H:%i:%s')  --【15:08:18】
--增加或减少一定的天数、月数、年数、小时、分钟数等等
select date_add(now(), interval -1 hour)  --时间加1小时
select date_sub(now(), interval 1 year)   --时间减一年
select datediff('2019-01-05 09:00', '2019-01-01 08:00') -- 【4】相差4天，和时间无关
select time_to_sec('01:00') --【3600】计算0点到01:00的秒数

6.4ifnull和coalesce函数

--替换空值的函数
select ifnull(password,'123456') from 表A--【查询A表中，如果password有空值，就用123456去替换】
select id, coalesce(b_id, id, '123456') from 表A--【查询A表中，如果b_id为空就用id去替换，如果id也为空，就用123456去替换】

6.5if函数

if('条件语句','AAA','BBB') --条件为真返回AAA,为假返回BBB
select *,if(year(order_date) = year(now()),'结果1','结果2') 
from 表A

6.6case函数

--语法
case
    when…… then……
    when…… then……
    when…… then……
    ……
    [else……] （else子句是可选的）
end
--举例
select
    id,
    case
        when account = '1' then 'Active'
        when  ISNULL(account) then 'noAccount'
        else 'Future'  
    end as '列C'
from user

8.视图

--创建视图
create view view01 as select * from 表A
--编辑视图和删除视图
第一种编辑方法【先删除再创建】
drop view01,
create view view01 as select * from 表A
第二种编辑方法
create or replace view view01 as select * from 表A
注意：修改视图数据和修改表数据是相同的语句，视图数据修改了，原来的表里面的数据也会跟着修改
--whth option check 
在视图的原始查询语句最后加上 WITH CHECK OPTION 可以防止执行那些会让视图中某些行（记录）消失的修改语句
举例 
create view view01 as select * from 表A where money > 500 whth option check ;
update view01 set money = 400 where id =2;【执行出错，因为改了的话这条数据就不会在视图里面出现了，】
--视图的优点
-简化查询
-增加抽象层和减少变化的影响
-数据安全性

9.储存过程

储存过程三大作用
1.储存和管理sql代码
2.性能优化
3.数据安全
存储过程可以储存sql代码，用来对数据库进行增删改查的操作，加强数据安全；比如说，我们可以移除对原始表的访问权限，让各种增删改查都通过存储过程来完成，然后我们可以执行何种存储过程，用来限制永辉对我们数据的操作范围。
--创建一个存储过程
DELIMITER $$
-- delimiter n. 分隔符
    CREATE PROCEDURE 过程名()  
        BEGIN
            ……;
            ……;
        END$$
DELIMITER ;
--调用储存过程
call 过程名()
--删除储存过程
drop procedure 过程名
--带参数的创建索引
CREATE PROCEDURE 过程名
(
    参数1 数据类型,
    参数2 数据类型,
    ……
)
BEGIN
……
END
举个栗子1
DELIMITER $$
CREATE PROCEDURE get_user_by_password
(
    password varchar(10)  -- 参数的数据类型
)
BEGIN
    SELECT * FROM user表 c
    WHERE c.password = password;
END$$
DELIMITER ;
举个栗子2
DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    password varchar(10)  
)
BEGIN
    IF password IS NULL THEN 
        SET password = '123456';  
        /* 注意别忽略SET，
        SQL 里单个等号 '=' 是比较操作符而非赋值操作符
        '=' 与 SET 配合才是赋值 */
    END IF;
    SELECT * FROM user表 c
    WHERE c.password = password ;
END$$
DELIMITER ;
举个栗子3,if语句块实现
……
BEGIN
    IF password IS NULL THEN 
        SELECT * FROM user表 c;
    ELSE
        SELECT * FROM user表 c
        WHERE c.password = password ;
    END IF;    
END$$
……
举个栗子4，用ifnull替换空值函数实现
……
BEGIN
    SELECT * FROM user表 c
    WHERE c.password = IFNULL(password , c.password )
END$$
……
--入参校验
CREATE  PROCEDURE `make_payment`(
    password varchar(20),
    money DECIMAL(9, 2)
)
BEGIN   
    IF money <= 0 THEN
        SIGNAL SQLSTATE '22003' 
            SET MESSAGE_TEXT = 'error money input';    
    END IF;
    UPDATE user表 i
    SET 
        i.password = password ,
        i.money = money 
    WHERE i.invoice_id = invoice_id;
END
--输出参数
我们可以通过输出参数来获取结果集
CREATE  PROCEDURE `get_unpaid_invoices_for_client`(
        id INT,
        OUT money_all INT
        -- 默认是输入参数，输出参数要加【OUT】前缀
)
BEGIN
    SELECT  SUM(money)
    INTO money_all 
    -- SELECT后跟上INTO语句将SELECT选出的值传入输出参数（输出变量）中
    FROM 表A a
    WHERE 
        a.id = id ;
END
--变量
CREATE  PROCEDURE `get_risk_factor`()
BEGIN
    -- 声明三个本地变量，可设默认值
    DECLARE AA DECIMAL(9, 2) DEFAULT 0;
    DECLARE BB DECIMAL(9, 2);
    DECLARE CC INT;
    -- 用SELECT得到需要的值并用INTO传入invoices_total和invoices_count
    SELECT SUM(BB), COUNT(*)
    INTO BB , CC 
    FROM invoices;
    -- 用SET语句给risk_factor计算赋值
    SET AA = BB / CC * 5;
    -- 展示最终结果risk_factor
    SELECT AA ;        
END

10.函数

--删除函数
DROP FUNCTION [IF EXISTS] 函数名
--创建函数
CREATE  FUNCTION `函数名`
(
    client_id INT
) 
RETURNS INTEGER
-- DETERMINISTIC
READS SQL DATA
-- MODIFIES SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;

    SELECT SUM(invoice_total), COUNT(*)
    INTO invoices_total, invoices_count
    FROM invoices i
    WHERE i.client_id = client_id;
    -- 注意不再是整体risk_factor而是特定顾客的risk_factor

    SET risk_factor = invoices_total / invoices_count * 5;
    RETURN IFNULL(risk_factor, 0);       
END

11.触发器

触发器是在插入、更新或删除语句前后自动执行的一段SQL代码；
创建触发器的语法要点：命名三要素，触发条件语句和触发频率语句，主体中 OLD/NEW 的使用
--插入时调用触发器
DELIMITER $$
CREATE TRIGGER after_insert
    AFTER INSERT ON 表A
    FOR EACH ROW
BEGIN
    UPDATE 表B
    SET money_all= money_all+ NEW.money
    WHERE code= NEW.code;
END$$
DELIMITER ;
--删除时调用触发器
DELIMITER $$
CREATE TRIGGER after_delete
    AFTER DELETE ON 表A
    FOR EACH ROW
BEGIN
    UPDATE 表B
    SET money_all= money_all - OLD.money
    WHERE code= OLD.code;
END$$
DELIMITER ;
--查看触发器
SHOW TRIGGER 
SHOW TRIGGER  LIKE 'payments%'
--删除触发器
DROP TRIGGER [IF EXISTS] payments_after_insert
--使用触发器进行审计
即在原有的触发器中加入插入语句
--审计--插入时调用触发器
DELIMITER $$
CREATE TRIGGER after_insert
    AFTER INSERT ON 表A
    FOR EACH ROW
BEGIN
    UPDATE 表B
    SET money_all= money_all+ NEW.money
    WHERE code= NEW.code;
    INSERT INTO payments_audit
VALUES (NEW.client_id, NEW.date, NEW.amount, 'insert', NOW());
END$$
DELIMITER ;
--审计--删除时调用触发器
DELIMITER $$
CREATE TRIGGER after_delete
    AFTER DELETE ON 表A
    FOR EACH ROW
BEGIN
    UPDATE 表B
    SET money_all= money_all - OLD.money
    WHERE code= OLD.code;
    INSERT INTO payments_audit
VALUES (OLD.client_id, OLD.date, OLD.amount, 'delete', NOW());
END$$
DELIMITER ;

12.事件

事件是一段根据计划执行的代码，可以执行一次，或者按规律执行，比如每天早上10点或每月一次
SHOW VARIABLES;
SHOW VARIABLES LIKE 'event%';
-- 使用 LIKE 操作符查找以event开头的系统变量
-- 通常为了节约系统资源而默认关闭
--打开事件寻找器
SET GLOBAL event_scheduler = ON/OFF
--创建event
DELIMITER $$
CREATE EVENT yearly_delete
-- 设定事件的执行计划：
ON SCHEDULE  
    EVERY 1 YEAR [STARTS '2021-01-01'] [ENDS '2029-01-01']    
-- 主体部分：（注意 DO 关键字）
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END$$
DELIMITER ;
--【once】/hourly/daily/monthly/yearly 时间格式替换
--查看、删除、修改、启用、禁用 事件

军大君

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mysql语句手册大全-入门到进阶

1.SELECT语句--其中*可以替换成你需要的字段--关键字的前后顺序是有规律的，不能随意编写【会报错】--一些常用的条件> 大余 < 小于 = 等于>=大于等于 <=小于等于 !=不等于 <>不等于and 和 or或 not否定条件 is null 是空的in在其中 between A and B 在A和B之间like '%家%' 模糊查询结果 12家12、1家1、家like '__家' 模糊查询【这里有2个下划线】结果
复制链接

扫一扫