Mysql基础学习三

最新推荐文章于 2022-04-27 19:00:00 发布

Wind_know

最新推荐文章于 2022-04-27 19:00:00 发布

阅读量175

点赞数

分类专栏：数据库 Mysql

本文链接：https://blog.csdn.net/Wind_know/article/details/106274394

版权

数据库同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

Mysql

3 篇文章 0 订阅

订阅专栏

Update

-- 更新用户名为4位的用户，让其已有年龄-3

UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

-- 更新前3条记录，让已有年龄+10
UPDATE cms_user SET age=age+10 LIMIT 3;

UPDATE cms_user SET age=age+10 LIMIT 0,3;

查询

-- 查询
SELECT * FROM cms_admin;

SELECT cms_admin.* FROM cms_admin;

-- 查询管理员编号和名称

SELECT id,username FROM cms_admin;

SELECT username,id,role FROM cms_admin;

-- 表来自于哪个数据库下db_name.tbl_name
SELECT id,username,role FROM cms.cms_admin;

-- 字段来自于哪张表

SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;

给表名起别名

SELECT id,username FROM cms_admin AS a;

SELECT id,username FROM cms_admin a;

SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;

给字段起别名

SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;

SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; 

SELECT id AS proId,proId AS id,username FROM cms_user;

SELECT 1,2,3,4,5,id,username FROM cms_user;

条件查询

WHERE条件

-- 查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1;

SELECT id,username,email FROM cms_user WHERE username='king';

-- 查询编号不为1的用户
SELECT  * FROM cms_user WHERE id!=1;

SELECT  * FROM cms_user WHERE id<>1;



-- 查询表中记录age值为NULL
SELECT * FROM cms_user WHERE age=NULL;'检测不到null值'

SELECT * FROM cms_user WHERE age<=>NULL;

SELECT * FROM cms_user WHERE age<=>18;

-- IS NULL 或者IS NOT NULL
SELECT * FROM cms_user WHERE age IS NULL;

-- 查询编号在3~10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;

-- 查询编号为1，3，5，7，9，11，13，100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);

-- 查询proId为1 和3的用户

SELECT * FROM cms_user WHERE proId IN(1,3);

-- 查询用户名为king,queen,张三，章子怡的记录
SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');

SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');

模糊查询

%:代表0个一个或者多个任意字符 _:代表1个任意字符

SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';

SELECT * FROM cms_user WHERE username LIKE '%';

-- 查询用户名为3位的用户

SELECT * FROM cms_user WHERE username LIKE '___';

--用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_I%';

SELECT * FROM cms_user WHERE username LIKE 'king';

SELECT * FROM cms_user WHERE username NOT LIKE '_I%';

分组查询

-- 按照用户所属身份分组proId
SELECT * FROM cms_user GROUP BY proId;

--按照用户性别分组
SELECT * FROM cms_user GROUP BY sex;

--按照字段位置分组
SELECT * FROM cms_user GROUP BY 7;

--按照多个字段分组

SELECT * FROM cms_user GROUP BY sex,proId;

-- 查询编号大于等于5的用户按照sex分组

SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;


-- 查询id,sex,用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

--查询proId，性别详情，注册时间详情，用户名详情 安装proId
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
FROM cms_user GROUP BY proId;

UPDATE cms_user SET age=11 WHERE id=1;
UPDATE cms_user SET age=21 WHERE id=2;
UPDATE cms_user SET age=33 WHERE id=3;
UPDATE cms_user SET age=44 WHERE id=4;
UPDATE cms_user SET age=25 WHERE id=5;
UPDATE cms_user SET age=77 WHERE id=6;
UPDATE cms_user SET age=56 WHERE id=7;
UPDATE cms_user SET age=88 WHERE id=8;
UPDATE cms_user SET age=12 WHERE id=9;
UPDATE cms_user SET age=32 WHERE id=10;
UPDATE cms_user SET age=65 WHERE id=11;

--查询编号,sex,用户名详情以及组中总人数按照sex分组

SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;



--查询性别sex,用户名详情,组中总人数，最大年龄，年龄总和,根据性别分组
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
GROUP BY sex;

COUNT(字段)

SELECT COUNT(age) AS totalUsers FROM cms_user;

GROUP_CONCAT

把相同一组拼接起来，也仅限于拼接后的结果小于255个字符，varchar(255)超过后并不会显示全。

--查询编号,性别,用户名详情，组中总人数，组中最大年龄，最小年龄，
-- 平均年龄，以及年龄总和按照性别分组

SELECT id,sex,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
GROUP BY sex;


-- 查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user 
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>60;

按序排序

order by 默认是升序

-- 按照id降序排列DESC 默认的是ASC
SELECT * FROM cms_user ORDER BY id ;

SELECT * FROM cms_user ORDER BY id ASC;

SELECT * FROM cms_user ORDER BY id DESC;

-- 按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;

SELECT * FROM cms_user ORDER BY 1 DESC;

UPDATE cms_user SET age=12 WHERE id=5;

-- 按照年龄升序，id降序排列

SELECT * FROM cms_user ORDER BY age ASC,id DESC;

SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age 
FROM cms_user 
WHERE id>=2 
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

LIMIT

LIMIT +单个数字表示显示前几条数据，LIMIT （*，*）前一个参数代表下标，后一个参数代表显示几条数据。

-- 查询表中前一条记录
SELECT * FROM cms_user LIMIT 1;

SELECT * FROM cms_user LIMIT 0,1;

SELECT * FROM cms_user LIMIT 1,1;

SELECT * FROM cms_user LIMIT 0,5;

SELECT id,sex,age,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;