准备工作：

--添加测试用表
CREATE TABLE user1(
  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 user1 VALUES
(1,'song',23,'男','北京',1,50000),
(NULL,'queen',27,'女','上海',0,25000),
(NULL,'imooc',31,'女','北京',0,40000),
(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);

SELECT语句的基本形式

SELECT select_expr,… FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]

--查询表中所有记录
SELECT * FROM user1;

--username,addr,age
SELECT username,addr,age FROM user1;

--查询song数据库下user1表中所有数据
--好处是不需要打开imooc数据库就可以查询到
SELECT * FROM song.user1;--库名.表名

--给字段起别名
--查询user1表中的id 编号 username 用户名 sex 性别
SELECT id AS '编号',username AS '用户名',sex AS '性别'
FROM user1;

--给表起别名(多表联查的时候比较有用)
SELECT id,username FROM user1 AS u;

--测试表名.字段名(多表联查的时候比较有用)
SELECT user1.id,user1.username FROM user1;

--去掉字段的重复值
SELECT DISTINCT(username) FROM user2;

WHERE条件筛选记录

筛选出符合条件的记录

--测试WHERE 条件的比较运算符
SELECT id,username,age FROM user1
WHERE id=5;

--以下由于查询不到，显示结果为空
SELECT id,username,age FROM user1
WHERE id=50;

--添加desc字段 VARCHAR(100)
ALTER TABLE user1
ADD userDesc VARCHAR(100);

--更新id<=9的用户,userDesc='this is a test'
UPDATE user1 SET userDesc='this si a test' WHERE id<=9;

--查询userDesc为NULL的用户
--这样写的话，查询结果为空
SELECT id,username,age,userDesc FROM user1
WHERE userDesc=NULL;

--<=>检测NULL值
--这样写的话，就可以正确查询到userDesc是NULL的数据
SELECT id,username,age,userDesc FROM user1
WHERE userDesc<=>NULL;

--IS [NOT] NULL检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL;

--测试范围BETWEEN AND
--查询年龄在18~30之间的用户
SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;
-- WHERE age NOT BETWEEN 18 AND 30;

SELECT id,username,age,sex,salary FROM user1
WHERE salary BETWEEN 10000 AND 50000;

--测试指定集合 IN
--查询编号为1,3,5,7,9
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9);
-- WHERE id NOT IN(1,3,5,7,9);

--测试逻辑运算符
--查询性别为男并且年龄>=20的用户
SELECT id,username,age FROM user1
WHERE sex='男' AND age>=20;

--查询id=1 或者 用户名为queen
SELECT * FROM user1
WHERE id=1 OR username='queen';

--测试模糊查询(LIKE)
SELECT * FROM user1
WHERE username LIKE 'song';
-- WHERE username NOT LIKE 'song';

--要求用户名中包含三
-- %：任意长度的字符串
SELECT * FROM user1
WHERE username LIKE '%三%';

--用户名长度为三位的用户
-- _：任意一个字符串
SELECT * FROM user1
WHERE username LIKE '___';

GROUP BY 对记录进行分组

把值相同的记录放到一个组中，最终查询出的结果只会显示组中一条记录

--测试分组

--按照性别分组
SELECT * FROM user1
GROUP BY sex;

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

SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1
GROUP BY sex;

--测试COUNT()
--COUNT(字段)     =>    不会统计值为NULL的情况
--COUNT(*)        =>    会统计值为NULL的情况
SELECT COUNT(*) FROM user1;
SELECT COUNT(id) AS totalId FROM user1;

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

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

--WITH ROLLUP:末尾追加一条记录，是上面所有记录的总和
SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1
GROUP BY sex
WITH ROLLUP;

--按照字段位置分组
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM user1
GROUP BY 1;  --跟GROUP BY addr;效果一样

--homework
--查询age>=30的用户，查询用户名详情用userDetail命名，并且按照sex分组
SELECT sex,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age>=30
GROUP BY sex;


--测试HAVING

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

--对于分组结果进行二次删选，条件是组中总人数>=3
--效果类似于WHERE
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*)>=3;

SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING totalUsers>=3;--这里可以直接使用别名

ORDER BY 实现排序效果

--测试排序

--按照id降序排序
SELECT * FROM user1
ORDER BY id DESC;

--按照年龄升序
SELECT * FROM user1
ORDER BY age ASC;--ASC不写也是可以的，默认升序

--按照多个字段排序
--如果第一个字段的值相同，那么就按第二个字段进行排序
SELECT * FROM user1
ORDER BY age ASC,id DESC;


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

LIMIT限制结果集的显示条数

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

--从offset(0)开始，显示几条记录
--注意offset是从0开始的
SELECT id,username,age,sex
FROM user1
LIMIT 0,5;

测试完整SELECT 语句的形式

SELECT addr,
GROUP_CONCAT(username) AS userDetail,
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 userDetail,
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 userDetail,
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;

SELECT addr,
GROUP_CONCAT(username) AS userDetail,
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
LIMIT 0,2;