MySQL学习笔记（4）查询数据2课堂演示版

--创建数据表，并添加数据
CREATE TABLE user(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user VALUES(1,'king',23,'男','北京',1,50000);
INSERT user(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
INSERT user SET username='imooc',age=31,sex='女',addr='北京',salary=40000;
INSERT user VALUES(NULL,'张三',38,'男','上海',0,15000),
(NULL,'张三风',38,'男','上海',0,15000),
(NULL,'张子轩',39,'女','北京',1,85000),
(NULL,'汪杨',42,'男','深圳',1,95000),
(NULL,'刘德凯',58,'男','广州',0,115000),
(NULL,'吴峰',28,'男','北京',0,75000),
(NULL,'浦丽',18,'女','北京',1,65000),
(NULL,'刘小明',36,'女','广州',0,15000);
-- 添加desc字段 VARCHAR(100)
ALTER TABLE user
ADD userDesc VARCHAR(100);

-- 更新id<=9的用户 userDesc='this is a test'

UPDATE user SET userDesc='this is a test'
WHERE id<=9;-- 测试分组
-- 按照性别分组sex
SELECT id,username,age,sex FROM user
GROUP BY sex;

-- 练习1：按照addr分组？


-- 按照性别分组，查询组中的用户名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user
GROUP BY sex;


-- 练习2：按照地址分组，查询组中的用户名有哪些？

-- 测试COUNT() 
--注意COUNT(*) COUNT(字段名) COUNT(DISTINCT 字段名)的区别
SELECT COUNT(*) FROM user;

SELECT COUNT(id) FROM user;

SELECT COUNT(usersDesc) FROM user;

SELECT COUNT(DISTINCT usersDesc) FROM user;

-- 按照sex分组，得到用户名详情，并且分别组中的总人数
SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user
GROUP BY sex;

-- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值和
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user
GROUP BY addr;

-- 练习3：按照sex分组，统计组中总人数、用户名详情，得到薪水总和，薪水最大值、最小值、平均值


--测试WITH ROLLUP
SELECT GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user
GROUP BY sex
WITH ROLLUP;

-- 按照字段的位置来分组
SELECT id,sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user
GROUP BY 2;

-- 查询age>=30的用户并且按照sex分组
SELECT sex,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user
WHERE age>=30
GROUP BY sex;

-- 按照addr分组，统计总人数
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user
GROUP BY addr;

-- 对于分组结果进行二次筛选，条件是组中总人数>=3
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user
GROUP BY addr
HAVING COUNT(*)>=3;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user
GROUP BY addr
HAVING totalUsers>=3;

-- 练习4：按照addr分组，统计用户名详情，总人数，薪水的最大值最小值平均值

-- 练习5：在1的基础上，要求平均薪水>=40000


-- 测试排序
-- 按照id降序排列
SELECT id,username,age
FROM user
ORDER BY id DESC;

-- 按照age升序
SELECT id,username,age
FROM user
ORDER BY age ;

-- 按照多个字段排序
SELECT id,username,age
FROM user
ORDER BY age ASC,id DESC;

-- 练习6：选择年龄大于等于30岁的用户id， username ，age按年龄降序排列测试条件+排序



-- 实现随机记录
SELECT id,username,age
FROM user
ORDER BY RAND();

-- 测试LIMIT语句
-- 显示结果集的前5条记录
SELECT id,username,age,sex
FROM user
LIMIT 5;

SELECT id,username,age,sex
FROM user
LIMIT 0,5;

-- 显示前3条记录
SELECT id,username,age,sex
FROM user
LIMIT 0,3;

SELECT id,username,age,sex
FROM user
LIMIT 3,3;

-- 练习 7更新前3条记录，将age+5
UPDATE user SET age=age+5 LIMIT 3;

-- 练习 8 按照id降序排列，更新前三条记录，将age-10


-- 练习 9 删除前三条记录



-- 测试完整SELECT 语句的形式
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2;


SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC;

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