mysql基础sql语句

最新推荐文章于 2022-08-01 22:20:02 发布

好无聊呦

最新推荐文章于 2022-08-01 22:20:02 发布

阅读量143

点赞数 1

分类专栏： sql

本文链接：https://blog.csdn.net/weixin_44245604/article/details/103999514

版权

sql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

整理出的MySQL的基础语句，算是再学，也算复习。

一、创建测试数据

create table boot(
id int not null auto_increment primary key comment 'ID',
name int(11) comment '名字',
size int(10) comment '大小',
type int(3) comment '类型',
money varchar(50) comment '价格'
)comment='图书表';
INSERT INTO book (name,size,type,money) VALUES ('1三国',1100,1,'52');
INSERT INTO book (name,size,type,money) VALUES ('水浒',1000,1,'52');
INSERT INTO book (name,size,type,money) VALUES ('斗罗大陆',1200,2,'40');
INSERT INTO book (name,size,type,money) VALUES ('将夜',1500,2,'39');
INSERT INTO book (name,size,type,money) VALUES ('十万个冷笑话',2000,3,'60');
INSERT INTO book (name,size,type,money) VALUES ('唐诗三百首',3000,4,'80');
INSERT INTO book (name,size,type,money) VALUES ('量子力学.',5000,5,NULL);

二、基本查询

#查询所有
SELECT * FROM book ;
#查询书名
SELECT name FROM book ;
#查询前三条
SELECT name FROM book LIMIT 3;
#从第二行开始的三条数据(包含第二行，行数从0开始)
#从第二行开始，包含第二行，取三条数据
SELECT name FROM book LIMIT 1,3;
#方式二
SELECT name FROM book LIMIT 3 OFFSET 1;
#限制数据库
SELECT name FROM tset_book.book ;
#限制表名
SELECT book.name FROM book ;
#限制数据库和表名
SELECT book.name FROM tset_book.book ;
#排序  无序 (默认升序)
SELECT name FROM book ORDER BY money ASC;
#降序
SELECT name FROM book ORDER BY money DESC;
#多字段排序 (价格降序，名字升序) 
SELECT name,money FROM book ORDER BY money DESC,name;
#找出最贵的
SELECT name,money FROM book ORDER BY money DESC LIMIT 1;
#根据条件筛选 
#= 等于
SELECT name,money FROM book WHERE money = 40;
#<>   不等于
SELECT name,money FROM book WHERE money <> 40;
#!= 不等于
SELECT name,money FROM book WHERE money != 40;
#< 小于
SELECT name,money FROM book WHERE money < 50;
#<= 小于等于
SELECT name,money FROM book WHERE money <= 40;
#> 大于
SELECT name,money FROM book WHERE money > 40;
#>= 大于等于
SELECT name,money FROM book WHERE money >= 40;
#between 区间范围 (包含40和60)
SELECT name,money FROM book WHERE money BETWEEN 40 AND 60;
#空值检查
SELECT name,money FROM book WHERE money is NULL;
#where多条件过滤数据 AND(和的意思)  and拼接条件都满足才行
SELECT name,money FROM book WHERE name = '将夜' AND money >= 40;
#or（或的意思） or满足一个即可
SELECT name,money FROM book WHERE money = 40 OR name = '将夜';
#and 和 or 合用
#因为and的优先级比or的优先级高，导致处理逻辑不对
SELECT name,money,type FROM book WHERE type = 2 OR type = 3 AND money >= 40;
#可使用（）来解决 ()的优先级高
SELECT name,money,type FROM book WHERE (type = 2 OR type = 3) AND money >= 40;
#in操作符  和or一样
SELECT name,money,type FROM book WHERE type IN (2,3) AND money >= 40;
#not 排除指定条件
SELECT name,money,type FROM book WHERE type NOT IN (2,3) AND money >= 40;
#LIKE 模糊查询 %号代表任意字符出现的任意次数
SELECT name,money FROM book WHERE name LIKE '将%';
SELECT name,money FROM book WHERE name LIKE '%夜';
SELECT name,money FROM book WHERE name LIKE '%三%';
SELECT name,money FROM book WHERE name LIKE '%';
#_下划线代表一个字符
SELECT name,money FROM book WHERE name LIKE '_夜';
#匹配十万个 因为前面是十万两个字符 所以无法匹配
SELECT name,money FROM book WHERE name LIKE '_个';

三、正则表达式使用

#使用正则
SELECT name,money FROM book WHERE name REGEXP '三';
#他们区别在于like要通配符配合，如果没有通配符则是全部符合才会有数据
#正则是只要有符合的就显示
#正则or匹配
SELECT name,money FROM book WHERE name REGEXP '三|十';
#匹配多个 意思为子力学三个字前面是三或者十或者量
SELECT name,money FROM book WHERE name REGEXP '[三|十|量]子力学';
#匹配多个 意思为除了子力学三个字前面是三或者十或者将
SELECT name,money FROM book WHERE name REGEXP '[^三|十|将]子力学';
#正则范围匹配 字母也可以  和正常的正则一样
SELECT name,money FROM book WHERE money REGEXP '[1-9]0';
#如果匹配特殊字符  需要\\转义
SELECT name,money FROM book WHERE name REGEXP '\\.';
#字符类
#[:alnum:]  任意字母和数字 同[A-Za-z0-9]
#[:alpha:]  任意字符  同[A-Za-z]
#[:blank:]  空格和制表  同[\\t]
#[:cntrl:]  ASCⅡ控制字符  同（ASCⅡ0到31和127）
#[:digit:]  任意数字 同[0-9]
#[:graph:]  与[:print:]相同不包括空格
#[:lower:]  任意小写字母
#[:print:]  任意可打印字符
#[:punct:]  既不在[:alnum:]又不在[:cntrl:]中的任意字符
#[:space:]  包括空格在内的任意空白字符
#[:upper:]  任意大写字符
#[:xdigit:]  任意十六进制同[a-fA-F0-9]
#重复元字符
#* 0个或者多个匹配
#+ 1个或者多个匹配
#？ 0个或者1个匹配
#{n} 指定数目的匹配
#{n,} 不少于指定数目的匹配
#{n,m} 匹配数目范围
#定位元字符
#^   文本开始
#$   文本结束
#[[:<:]]  词的开始
#[[:>:]]  次的结束
#正则查询  有小括号要用\\转义
SELECT name,money FROM book WHERE name REGEXP '[0-9]?三';
#[:digit:] 表示任意数字    ｛2｝表示数字重复出现的次数
SELECT name,money FROM book WHERE money REGEXP '[[:digit:]]{2}';
#查找.或者包含.   结束位置  开始位置在正则最前面使用^
SELECT name,money FROM book WHERE name REGEXP '[0-9\\.]$';
#四、多字段拼接
#数据多字段拼接  书名和金额拼接
SELECT CONCAT(name,'(',money,')') FROM book ;
#给拼接数据别名
SELECT CONCAT(name,'(',money,')') AS nameMoney FROM book ;
#普通计算
SELECT name,money,size FROM book where type = 2;
SELECT name,money,size,money*size AS moneySize FROM book where type = 2;

五、聚合函数

#平均值
SELECT AVG(size) AS avgSize FROM book ;
#使用distinct  会排除相同的
SELECT AVG(DISTINCT size) AS avgSize FROM book ;
#总行数
#统计所有size数量
SELECT COUNT(size) AS countSize FROM book ;
#统计所有行 是否是空都统计
SELECT COUNT(*) AS countSize FROM book ;
#最大值
SELECT MAX(size) AS maxSize FROM book ;
#最小值
SELECT MIN(size) AS minSize FROM book ;
#求和
SELECT SUM(size) AS sumSize FROM book ;
#可同时使用
SELECT COUNT(*) AS countSize,MAX(size) AS maxSize,MIN(size) AS minSize,SUM(size) AS sumSize,AVG(size) AS avgSize FROM book ;
#分组  返回的字段在分组时一定要出现  
SELECT name,COUNT(*) AS countSize FROM book GROUP BY name;
#分组  汇总
SELECT name,COUNT(*) AS countSize FROM book GROUP BY name WITH ROLLUP;
#过滤分组
SELECT name,COUNT(*) AS countSize FROM book GROUP BY name HAVING COUNT(*) > 1;
#where在分组前过滤   HAVING在之后过滤
SELECT name,COUNT(*) AS countSize FROM book WHERE size > 2000 GROUP BY name HAVING COUNT(*) > 1;
#分组排序
SELECT name,COUNT(*) AS countSize FROM book GROUP BY name HAVING COUNT(*) > 1 ORDER BY countSize;

六、子查询

SELECT name FROM book where type = 2;
SELECT id FROM book where name IN ('斗罗大陆','将夜');
#结合
SELECT id FROM book where name IN (SELECT name FROM book where type = 2);
SELECT id,name,(SELECT COUNT(*) AS countSize FROM book WHERE type = 3) AS countSize FROM book ORDER BY id;

七、多表联查

create table boot_type1(
id int not null auto_increment primary key comment 'ID',
type_name varchar(50) comment '名字'
)comment='图书类型表';
INSERT INTO boot_type (type_name) VALUES ('名著');
INSERT INTO boot_type (type_name) VALUES ('网络小说');
INSERT INTO boot_type (type_name) VALUES ('文学');
INSERT INTO boot_type (type_name) VALUES ('笑话');
INSERT INTO boot_type (type_name) VALUES ('科学');

SELECT b.id,b.name,b.money,t.type_name FROM book AS b LEFT JOIN boot_type AS t on b.type = t.id ;
SELECT b.id,b.name,b.money,t.type_name,COUNt(b.id) AS count FROM book AS b LEFT JOIN boot_type AS t on b.type = t.id GROUP BY b.id;
#组合查询  使用UNION 返回的列和函数要一样  默认去重
SELECT id,name FROM book AS b where type = 2 UNION SELECT id,name AS t FROM book where id = 5;
SELECT id,name FROM book AS b where type = 2 UNION ALL SELECT id,name AS t FROM book where id = 5 ORDER BY id;

八、批量添加

#多条添加语句以分号结尾，可以执行多条，或者使用↓↓↓↓↓↓↓↓
INSERT INTO boot_type (type_name) VALUES ('悬疑'),('百科');
#九、更新 和 删除 where 不要省略  省略就是更新所有和删除所有了
UPDATE book SET money = 50 WHERE money < 40;
Update book Set name = (select name from boot_type b where b.id = 1) where id = 3;

目前就是整理这么点了吧，这些是直接操作数据库的SQL，在代码中使用可能需要修改，如有不对，欢迎指正。

好无聊呦

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql基础sql语句

整理出的MySQL的基础语句，算是再学，也算复习。一、创建测试数据create table boot(id int not null auto_increment primary key comment 'ID',name int(11) comment '名字',size int(10) comment '大小',type int(3) comment '类型',money var...
复制链接

扫一扫